MySQL_增删改查基础

前言

增删改查(CRUD)，是数据库非常基础的部分，也是后端开发日常工作中，最主要的一项工作

1. 新增

insert into 表名 values(值,值……);

此处的值的个数和类型要和表结构相匹配

使用单引号或者双引号来表示字符串，SQL没有字符这个类型

1.1 指定列插入

insert into 表名(列名,列名……) values(值，值……);

insert into student (name) values ('lisi');

此时的值就要和前面的列相匹配
在指定列插入的时候，未被填写的其他列就会被填充为默认值

1.2 一次插入多行

insert into 表名 values(值，值……),(值,值……);

insert into student values (3, 'wangwu'), (4, 'lisi');

一次插入多行记录，相比于一次插入一行，分多次插入要快

datetime类型如何插入呢？
可以使用一个固定格式的字符串，来表示时间 举例：‘2000-01-01 12:00:00’

create table student (id int, name varchar(20), birthday datetime);
insert into student values (1, 'zhangsan', '2000-01-01 12:00:00');

如果想填写的时间日期就是当前时刻。sql提供了一个现成的函数，now()

2. 查询

sql中的增删改都非常简单，查询比较多样

2.1 全列查询

select * from 表名;

把表中的所有行和所有列都查询出来。

*表示通配符，可以代指所有的列
这里查询出来之后，服务器通过网络，把这些数据返回给客户端，并且在客户端以表格的形式打印出来

select * 操作也是一个危险的操作！
mysql是一个客户端-服务器结构的程序。客户端的操作都会通过请求发送给服务器，服务器查询的结果也会通过响应返回。
当数据量很大的时候，这个操作可能把硬盘IO跑满，也可能把网卡宽带跑满，会非常慢，导致客户端无法顺利访问到数据库，进一步对整个系统造成影响

2.2 指定列查询

select 列名,列名…… from 表名;

一个表的列数可能是非常多的，某些场景下，只需要关注其中的几个列
先创建一个表

 create table exam_result (
     id int,
     name varchar(20),
     chinese decimal(3,1),
     math decimal(3,1),
     english decimal(3,1)
     );
 insert into exam_result (id, name, chinese, math, english) values
     (1,'唐三藏', 67, 98, 56),
     (2,'孙悟空', 87.5, 78, 77),
     (3,'猪悟能', 88, 98.5, 90),
     (4,'曹孟德', 82, 84, 67),
     (5,'刘玄德', 55.5, 85, 45),
     (6,'孙权', 70, 73, 78.5),
     (7,'宋公明', 75, 65, 30);

select name, chinese from exam_result;

2.3 表达式查询

就是一遍查询一遍进行计算。在查询的时候，写作由列名构成的表达式，把这一列中的所有行都代入到表达式中参与运算

举例：想查询所有同学语文减十分后的结果

select name, chinese - 10 from exam_result;

这个操作不会修改数据库服务器上的原始数据，只是在最终响应里的临时结果中做了计算。
mysql中进行查询的时候，是把服务器这里的数据读出来，返回个客户端，并且以临时表的形式进行展示
举例2：查询所有同学总分

select name, chinese + math + english from exam_result;

表达式查询，是列 和 列之间的运算，会把每一行都代入到这样的运算中。并不是行 和 行之间的
当然如果这个表达式比较复杂，此时查询结果不容易理解这个表达式到底是啥意思，为了解决这个问题，就引入了别名

2.4 带别名的查询

查询的时候给 列/表达式 指定别名（表也能指定别名）
这列的as是可以省略的，但是并不建议这么做

select 表达式/列 as 别名 from 表名;

2.5 去重

查询时使用distinct修饰某个列/多个列，值相同的行就只会保留一个

select distinct 行名, 行名 from 表名;

修饰多个列的时候，只有在多个列的行都相同的情况下，才会去重

2.6 查询的时候进行排序

查询时候将行进行排序

需要指出

1. 针对那个列作为排序的规则
2. 排序的时候是升序还是降序

根据某个列来排序

select 列名 from 表名 order by 列名 asc/desc;

select后面的列名表示要查询的列（待会将展示出来的列）
order by后面的列是指定某个列作为排序规则，即使不展示出来也不影响排序
asc代表升序，desc代表降序。如果不写排序规则，默认是升序排序
排序是以行为单位的

把排序请求发给服务器后，服务器进行查询数据，并且把查询到的结果进行排序之后，在组织成响应数据返回给客户端
如果一个sql不加order by此时查询的结果数据的顺序是“不确定的”/“无序的”，
如果不加order by代码就不应该依赖上序顺序展开一些逻辑，数据库没有承诺过这个数据是有顺序的。

根据表达式来排序

order by还可以根据表达式来进行排序

select name, math + chinese + english as total from exam_result
 order by total desc;

根据多个列来进行排序

order by后面可以写多个列，使用，分开

select * from exam_result order by math desc, chinese desc ;

这段代码的意思是先根据math来进行排序，如果math相同，则根据chinese来进行排序。当有多个列的时候，写在越前面的优先级越高。

2.7 条件查询

会指定具体的条件，按照条件针对数据进行筛选

select 列名 from 表名 where 条件;

条件会遍历这个表的每一行记录，把每一行的数据分别代入到条件中，如果条件成立，这个记录就会被放入结果集合中。
如果条件不成立，这个记录就pass

比较运算符

1. sql中没有==，是使用=表示相等。null参与运算的结果仍然是null，所以说null不安全。于是提供了<=>
2. 不等于写 != 就行了，<>是上古时期的写法了
3. between and是闭区间，并不是左闭右开区间
4. 模糊查询不理解可以看下文的例子

逻辑运算符

逻辑与、或、非，在sql中是and、or、not

where条件可以使用表达式，但不能使用别名。
sql中and的优先级大于or，但是不建议大家记这个优先级，同事遇到时可以手动加（）

举例

基本查询就不说了，简单举几个运算符的例子

-- and 和 or
-- 查询语文成绩大于80，且英语成绩大于80分的同学
select * from exam_result where chinese > 80 and english > 80;

-- 查询语文成绩大于80分，或英语成绩大于80分的同学
select * from exam_result where chinese > 80 or english > 80;

-- between and 
-- 查询语文成绩在[80,90]分的同学及语文成绩
select name, chinese from exam_result where chinese between 80 and 90;

-- 使用in来表示一个离散的集合
select name, math from exam_result where math in (58,76,87);

模糊查询
通配符就是一些特殊的字符，能够表示特定的含义
%：代表任意个数任意字符（0个字符也可以）
_：代指一个任意字符

-- 模糊查询% 查询姓孙的同学（名字就叫孙也能查出来）
select name from exam_result where name like '孙%';

-- 模糊查询_ 查询姓孙的两个字同学名
select name from exam_result where name like '孙_';
select name from exam_result where name like '孙__';
-- 这样写就是查询姓孙的三个字同学名

2.8 分页查询

之前提到使用select * 这种方式查询是比较危险的，所以要保证一次查询不要查出来太多
limit 可以限制这次查询最多能查出来多少个结果
有的时候，数据非常多，一次全部显示出来，不但会影响效率也不方便用户去看。一般来说limit 是写到整个sql语句的最后

select * from 表名 limit N offset M;

N表示这次查询查出几个记录
offset是偏移量，M表示这次查询的这N个数据，是从第几个下标开始算

上述写法都属于最基础的查询操作

3. 修改

update 表名 set 列名 = 值, 列名 = 值 where 条件;

使用update 是可以一次修改多个列也可以只修改一个列
=出现在update里意思就是赋值，出现在where中就是相等

举例：将总成绩倒数前三的 3 位同学的数学成绩减 30 分
 update exam_result set math = math - 30 
 order by chinese + math + english limit 3;

4. 删除

delete from 表名 where 条件/ order by/ limit;

where条件和order by以及limit都是可以搭配的
不指定任何条件就是删除整张表的记录

这里的delete和drop table 不太一样
drop table是删除了表同时也删除了表里的记录
delete是只删除了表里的记录，表是还在的，只不过是一张空的表

delete和update 都是很危险的操作
修改和删除操作时会影响到数据库服务器硬盘中的数据

注释

sql中可以通过 – 表示注释（两横线一个空格）

warnings

有问题但是不严重就是警告

show warnings;
-- 展示警告