MySQL —— CRUD

CRUD

CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。
我们常说增删查改，增删改查…

这里我们的增删查改是对表格的数据行进行操作的~~

新增

1.1.1 单行数据 + 全列插入

插入一行新数据行，使用 insert into table_name [(字段1, 字段2...)] values(数值1,数值2...);

中括号括起来的可以省略，如果省略，要想全列插入，要按顺序填入数据。

演示：
这里以学生表为例：

---

如果省略了中括号，但是没有按顺序填入数据就会发生下面的错误，并且该行数据无法写入表中

---

1.1.2 多行数据 + 全列插入

使用 insert into table_name [(字段1, 字段2...)] values(数值1,数值2...),(数值1,数值2...),...;

---

1.2.1 单行数据 + 指定列插入

使用 insert into table_name (指定字段1, 指定字段2...) values(数值1,数值2...);

没有指定的列会被置为默认值。

---

1.2.2 多行数据 + 指定列插入

使用 insert into table_name (指定字段1, 指定字段2...) values(数值1,数值2...),(数值1,数值2...),...;

---

一次插入一条数据和一次插入多条数据，哪个效率高？
答：一次插入多条数据在一定可控范围之内会比一次只插入一条数据的效率要高一些

原因：
1.执行所有的sql 与酒都会有网络开销
2.写入数据时还会有磁盘 IO 的开销
3.每执行一条sql 语句都会开启一个事务，事务的开启和关闭是需要消耗系统资源的。

查询

全列查询

语法：select * from table_name;

这个在上面大家已经见过：

在开发中，不建议使用全列查询，这是一个很危险的事情，如果数据有几亿条，可能会导致服务器的崩溃，并且会极大程度占用很大的资源和开销。

指定列查询

语法：select 指定列名1, 指定列名2 ... from table_name;

制定别名

语法：select 列名 [as] 别名 from table_name;

中括号的 as 可以省略

如果别名之间有空格的话，需要加单引号，否则会报错；如果没有的话，可以不加单引号，你要加也可以~~

查询字段为表达式

数据列可以进行运算并返回结果。

以下面的 exam 表举例：

你可以通过 chinese + math + ebglish 来显示总分：

---

你还可以使用 chinese + 10 来让每一个人的语文成绩加 10 分：

---

NULL 不管与什么数值进行运算，最后的结果都是 NULL

---

如果你想加多一列默认值，select 列名1, 列名2, 默认值 from table_name;

去重 distinct

语法：select distinct 列名 from table_name;

演示：

我们看到上面有两个 98 ，通过去重操作，我们可以只保留一个 98：

---

只有当每一个数据行是一样的时候，使用distinct 才能起到去重。

如果我们的数据是这样的：

即使有两个98， 但是它们的 id 不一样，MySQL 不认为这是重复的数据，所以经过去重后，结果集还是一样：

排序 oreder by

排序规则 ：
升序 ASC(asc) 降序 DESC(desc)
默认排序规则为 升序 ASC(asc)

这里的desc 的英文是 descent（下降）

在开发过程中，变量名要起的有意义，避免向上面出现歧义

语法：select 列名 from table_name order by 列名 [ASC | DESC];

中括号[ASC | DESC]不写的话，就会默认升序排序，建议如果真的需要排序大家还是不要省略~~
没有使用 order by 子句使，永远不雅依赖默认排序返回结果集顺序，默认MySQL根据什么字段进行排序是不确定的，总之，要想得到排序的结果集就老老实实的写，不要省略

---

如果排序中存在 NULL，因为在比较中NULL会被认为，在任何数值中都是最小的值，所以在升序中 null 是第一个，在降序中 null 排在最后一个。

---

可以对多个字段进行排序，排序的优先级与书写顺序有关。

先对数学进行升序，再对语文进行升序，最后对英语进行降序：

表：

排序后：

这里转化成 sql 语句，当数学成绩一样时，就按语文升序排序，如果语文成绩还一样，就会按照英语成绩降序进行排序。

---

条件查询(where)

语法格式：select ... from table_name where 条件

运算符	说明
>、>=、<、<=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 结果还是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 结果为 true(1)
!=、<>	不等于
between a and b	范围匹配，[a,b] 如果 a <= x <= b,返回 true(1)
in(oprion,…)	如果是 option 中的任意一个数值，返回 true(1)
is NULL	是 NULL
is not NULL	不是 NULL
like	模糊匹配，% 表示任意多个(包括 0 个) 任意字符；_ 表示任意一个字符
and	所有条件都为 true(1)，结果才是 true(1)
or	任意一个条件为 true(1)，结果才是true(1)
not	条件为 true(1)，结果为false(0)

需要使用等于的时候记得是单个等号，【如果有NULL 参与等于或者不等于的操作可以使用 is null ，is not null 或者 <=>，但是不能使用 = 、!=、<>，因为返回值都是 null，而判断的返回值要求是true(1) 或者 false(0)】
and 类比&& ，or 类比 ||，not 类比 ！

这里不演示and or 与 not

演示：

注意 1 表示 true , 0 表示 false ,类比C语言

---

in(option,…) 里面可以包含参数，如果设置的参数没有在 in里面的参数列表时，就会返回false(0),否则返回true(1)

---

between … and …，注意范围的取值是闭区间

---

模糊查询

like 有两个配对的符号（一个是 %，另一个是 _），也可以不配对符号

还可以这样写：

% 是可以大范围的查找：
如果是类似这种形式 %name 只要是后面只有 name 前面随便的数据都能被查找出来：

以下面的表格作为例表来举例：

注意是后面只有你的标记词，上面曹孟德和刘玄德后面都是只有德，所以被查询出来了，但是 孙德崖的德后面还有崖，所以无法匹配成功。

---

如果是类似 name% 这种形式进行查询，那么只要前面只有 name ，那就能匹配成功。

---

如果是类似 %name% 这种形式，那么只要包含name 就可以匹配成功：

---

而 _ 是表示一个字符的空位，如果是 name_ ，说明要查找的是前面为 name 并且后面必须有一个字符的存在，才能匹配成功。

同理，类似 _name _ ，说明要查找得到必须前后各有一个字符在name 旁边。

---

其他的类比推理即可：

---

查询的执行顺序

首先先确定是执行的是那种表，也就是先执行 from table_name
然后根据查询条件来筛选数据，也就是执行 where 子句
接着执行 select 后面指定的列，将这些列加入到最终的结果集里面
最后执行排序操作，也就是 order by 子句

这个顺序是由于MySQL 的实现有关，如果感兴趣，可以自行去阅读MySQL的源码

所以为什么下面的会报错？

因为执行 where 子句的时候，还没有 total 这个概念，需要等到select 子句执行完后才会出现 total ，select 子句执行的顺序是在where 的后面

实践

现在来练习查询的sql 代码：以 exam 表为例

1.查询英语不及格的同学及英语成绩 ( < 60 )

select name, english from exam where english < 60;

2.查询语文成绩好于英语成绩的同学

select name, chinese, english from exam where chinese > english;

3.查询总分在 200 分以下的同学

select name, chinese + math + english 总分 from exam where (chinese + math + english) < 200;

这个括号可以不加，我这里担心优先级的问题，避免先运算 < ，所以我这里加了小括号

4.查询语文成绩大于80分，且英语成绩大于80分的同学

select name, chinese, english from exam where chinese > 80 and english > 80;

5.查询语文成绩大于80分，或英语成绩大于80分的同学

select name, chinese, english from exam where chinese > 80 or english > 80;

6.查询语文成绩在 [80, 90] 分的同学及语文成绩

select name, chinese from exam where chinese >= 80 and chinese <= 90;

select name, chinese from exam where chinese between 80 and 90;

7.询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

select name, math from exam where math in(58,59,98,99);

select name, math from exam where math = 58 or math = 59 or math = 98 or math = 99;

select name, math from exam where math <=> 58 or math <=> 59 or math <=> 98 or math <=> 99;

8.查询 exam表中 已知的同学姓名

select name from exam where name is not null;

9.查询 exam表中 未知的同学姓名

select name from exam where name is null;

分页查询：limit

语法1：select ... from table_name [where...] [order by] limit n; 从0开始，筛选 n 条数据

---

语法2：select ... from table_name [where...] [order by] limit s, n; s表示start ，从 s 开始，筛选 n 条数据

注意是不包括 s 这条数据，从s 开始向后筛选 n 条数据。

---

语法3：select ... from table_name [where...] [order by] limit n offset s; 从 s 开始 筛选 n 条数据，offset 是偏移量

---

实践：现在给你一张 exam 表，把它分成 4 页：

修改

语法：update table_name set column = expr [, column = expe...] [where ...] [order by...] [limit...];

update 更新【关键字】
set 设置
column 列名
expr 表示新的数值

演示：原先孙德崖的成绩：

进行修改：

query ok 表示成功，1 row affected 说明一行数据被修改了

修改之后的成绩：

---

update 操作的时候，如果不加 where 子句进行限制的话，那修改的就是整张表的所有的记录，这是十分危险的操作。

update exam set english = 99;

如果上面这条语句一执行，那么 exam 所有人的英语成绩都变成了 99

---

只要找到符合条件的数据行，就会一次性把符合条件的数据行全部修改！！！

验证：
我现在在插入一个还是叫孙德崖的数据：

然后进行修改：

你会发现有两条数据被修改（2 rows affected）

最终结果：

---

练习

写出下面的 sql 代码：以 exam 表为例表

1.将孙德崖同学的数学成绩变更为 80 分

update exam set math = 80 where name = '孙德崖';

2.将孙悟空同学的数学成绩变更为 60 分，语文成绩变更为 70 分

update exam set math = 60, chinese = 70 where name = '孙悟空';

3.将总成绩倒数前三的 3 位同学的数学成绩加上 10 分

update exam set math = math + 10 order by (chinese + math + english) asc limit 3;

注意：这里和编程语法不同，不能使用 += 这些操作符，在 sql 只能使用类似 math = math + 10 来赋值。

4.将所有同学的语文成绩更新为原来的 二分之一

update exam set chinese = chinese / 2;

删除

语法: delete from table_name [where...] [order by...] [limit...];

演示：以 exam 表为例：

删除徐盛的成绩：

删除后表的记录如下：

如果删除没有加任何条件就是把整张表的所有数据删除掉，就是这个 sql 语句delete from exam;
这个操作要小心谨慎

总结

1.新增 insert into from table_name [列名[,列名][,列名]...] values (值(,值)(,值)) [,(值(,值)(,值))...];

2.查询
2.1 全列查询 select * from table_name;
2.2 指定列查询 select 列名[,列名][,列名]... from table_name;
2.3 列名使用表达式（表达式可以为列名运算式子或者常数）select 表达式 from table_name;
2.4 使用别名 select 列名/表达式 [as] 别名 from table_name; 当别名存在空格要加单引号
2.5 去重查询 select distinct 列名[,列名][,列名]... from table_name;
2.6 排序 select */列名[,列名][,列名].../表达式 from table_name order by 列名/表达式 asc | desc;
2.7 条件查询 select * from table where 列名/表达式 条件; where 子句不能使用别名
2.8 模糊查询： % _
2.9 分页查询 select * from table_name limit n; select * from table_name limit s, n; select * from table_name limit n offset s;

综合查询总结：select ... from table_name [where...] [order by...] [limit...];

3.修改/更新 update table_name set 列名=值 [,列名=值...] [where...] [order by...] [limit n];

4.删除 delete from table_name [where...] [order by...] [limit...];