CRUD是对数据库中的记录/数据行进行基本的增删改查操作：

• Create(创建) 
• Retrieve(读取)
• Update(更新)
• Delete(删除)

1.Create 新增

Insert

语法：

insert into 表名 [字段1，字段2...] values(字段值，字段值...);
--             表示定义表时的列名/字段名       按照前面字段名定义的顺序，设置对应的值 

单行数据全列插入

单行数据全列插入：就是字段值的数量必须与定义表时定义的列的数量及顺序一致。

示例：创建一个数据库，并在该数据库中创建一个student表：

现在要根据student表的列新增一条数据行：注意 - 列和值要一一对应。

简写方式：不用在表名之后指定列名，而是在values列表中按表中定义的字段的顺序设置对应的值：

--简写
insert into 表名 values(字段值，字段值...);

示例：

简写的方式，默认是全列插入的，要注意与列一一对应，否则写入失败：

单行数据指定列插入

单行数据指定列插入：就是字段值的数量必须与指定的列的数量与顺序一致。

示例：指定只插入列为 name 的数据行，那么没有被指定的那个列就会使用默认的值去填充，这个默认值就是NULL：

多行数据指定列插入

多行数据全列/指定列插入：就是在一条 insert 语句中可以指定多个value列值，实现一次插入多行数据。

语法：

insert into 表名[(指定列...)] values(列值[,列值,列值...]);
--               可以省略

示例：

2.Retrieve 检索

语法：

SELECT
 [DISTINCT]
 select_expr [, select_expr] ...
 [FROM table_references]
 [WHERE where_condition]
 [GROUP BY {col_name | expr}, ...]
 [HAVING where_condition]
 [ORDER BY {col_name | expr } [ASC | DESC], ... ]
 [LIMIT {[offset,] row_count | row_count OFFSET offset}]

• DISTINCT 的作用是去除查询结果中的重复行，只保留唯一的记录
• select_expr [, select_expr] ...（要查询的列 / 表达式）
• select_expr 是你要查询的具体内容，可以是：
  • 表的列名（如 username、age）；
  • 通配符 *（表示查询表中所有列，如 select * from users;）；
  • 表达式（如 age+1 计算年龄加 1，CONCAT(username, '-', age) 拼接字符串）；
  • 聚合函数（如 COUNT(*) 统计行数、SUM(score) 求和）；
  • [, select_expr] ... 表示可以同时查询多个列 / 表达式，用逗号分隔；
  • 示例：SELECT username, age, age+5 FROM users; —— 查询用户名、年龄，以及年龄加 5 的结果。
• [FROM table_references]（数据来源表）：指定查询数据的来源表
• [WHERE where_condition]（行级筛选）：对原始数据行进行筛选，只保留满足 where_condition（条件表达式）的行；
• [GROUP BY {col_name | expr}, ...]（分组）：按照指定的列（col_name）或表达式（expr）将查询结果分组。
• [HAVING where_condition]（分组后筛选）：对分组后的结果进行筛选，只保留满足条件的分组。
• [ORDER BY {col_name | expr } [ASC | DESC], ... ]（排序）：对最终的查询结果排序；
  ASC 是升序（默认，可省略），DESC 是降序。
• [LIMIT {[offset,] row_count | row_count OFFSET offset}]（限制结果行数）：限制查询结果返回的行数，常用于分页。两种写法含义相同：
  • LIMIT [偏移量], 行数：offset 是偏移量（从 0 开始，省略则默认 0），row_count 是要返回的行数；
  • LIMIT 行数 OFFSET 偏移量；

在展示查询语句的用法前，先构造数据：

Select

全列查询

• 查询所有记录：语法：

select * from 表名;
-- *：通配符，表示查询表中所有列的值
--完整写法：select id,name,chinese,math,english from exam;

示例：

指定列查询

• 查询想要查询的列，可以是⼀个也可以是多个，中间用逗号隔开
• 指定列的顺序与表结构中的列的顺序无关

语法：

select 指定的列名,... from 表名;

示例：只查询 id ，name，语文成绩：

查询的结果为表达式

• 就是查询的结果中的列名可以为一个表达式。

语法：

select 表达式 from 表名;

• 常量表达式或常量运算

如以下示例，让所有的列中都包含一个表达式的值，但是它本身并不在我们真实的表中：

• 把所有学⽣的语文成绩加10分

• 计算所有学生语文、数学和英语成绩的总分(列与列之间可以参与运算)

像上述的让语文成绩+10，或者是计算总分的数据行，它的列名/字段名就是我们要指定的操作，但是这样查询的结果并不好看，于是我们可以为查询结果指定一个列名。

为查询结果中的表达式指定别名

语法：

select 表达式 [AS] 别名 from 表名;

• AS可以省略，但是别名如果包含空格必须用单引号包裹

示例：

注意：我们在创建表时，并没有总分这一列的，所以通过表达式查询出来的结果集是通过一个临时表返回给我们的，执行完之后临时表就删除了。(在MySQL中，所有的查询结果都会通过临时表返回给用户。)

为结果集中的字段/列指定别名

语法：

select 列名 [as] 别名,列名 [as] 别名,... from 表名;

示例：

DISTINCT 结果去重查询

语法：

select distinct 列名 from 表名; 

查询当前所有的数学成绩，发现有重复的记录，即有两个分数为98的：

那么我们可以使用 DISTINCT 关键字对该列数据进行去重，去重后，重复记录只保留一条：

使⽤DISCTINCT去重时，只有查询列表中所有列的值(数据行与数据行之间，也就是两条记录完全一致)都相同才会判定为重复，例如，以下的示例查询结果中的所有列并不相同，只是 math 数据行中有重复的记录，这样并不能去重：

例如以下的情况才可以去重：有所有列相同的记录：

注意：

• 查询时不加限制条件会返回表中所有结果，如果表中的数据量过大，会把服务器的资源消耗殆尽 • 在生产环境不要使用不加限制条件的查询，否则不安全。

Order By 排序

用这个 order by 子句，使得查询结果根据我们指定的规则去对结果排序。

语法：

select 列名 from 表名 order by 列名 [ASC | DESC];
-- ASC 升序，默认
-- DESC 降序

• 按语文成绩从高到低排序(降序)

• 按数学成绩从低到高排序(升序)

也可以不写 asc，因为默认是升序。

• 按英语成绩从高到低排序(降序)

思考：没有 order by 子句时，返回的结果按哪个字段进行排序？

没有ORDER BY子句时，返回结果的顺序是“不确定的”或“未定义的”。

• NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面。

为了进一步证明，可以再添加一行包含负数的数据：

通过以下的示例，可以清晰的看到 NULL 数据排序，比任何值都小：

• 使用表达式及别名排序

但是，上述计算总分的结果有问题，如以下图中，孙大圣的语文和数学有成绩，只有NULL，但是计算结果却是NULL：

这说明：MySQL中的NULL：

1. 不论和什么值进行运算，返回的值都是NULL。
2. NULL始终被判定为false。
3. NULL的值不是我们以前学习过的其他编程语言中的0，在MySQL中它就是NULL。

• 对多个字段进行排序，排序的优先级与书写顺序相关

可以对每个字段指定不同的排序规则：先按数学降序排序，再按语文升序排序，再按英语升序排序：即再数学降序排序的基础上，对语文成绩进行升序排序，然后英语成绩在前两个基础上进行升序排序：

Where 条件查询

语法：

select */列名[,列名...] from 表名 where 列名/表达式 运算符 条件;

在学习 Where 查询子句需要结合SQL的运算符，即比较运算符和逻辑运算符。

1】比较运算符

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，对于NULL的比较不安全，如NULL=NULL的结果还是NULL(在MySQL中，判断相等和赋值都是用=)
<=>	等于，对于NULL的比较是安全的，如NULL<=>NULL结果是TRUE(1)
!=,<>	都表示不等于
value between a and b	范围匹配，[a,b]，如果a<=value<=b，返回true或1，not between 则取反
value IN(option,...)	如果value 在optoin列表中，则返回TRUE(1)，NOT IN则取反
is NUll	是 NULL
is not NULL	不是 NULL
LIKE	模糊匹配，%表示任意多个(包括0个)任意字符；_表示任意一个字符

• 示例1：= 和 <=> 比较符比较NULL时的结果：用 = 比较时结果是false/NULL，使用<=> 比较结果是 true/1：

我们再次查询一下 exam 表，其中有一个人的英语成绩为NULL，使用 = 查询时因为是 false，所以在表中查询不到，是空表，但是使用 <=> 就可以查询出来：

• 示例2：两种不等于写法：

• 示例3： IN 子句及is null/is not null 子句：1 在列表 (1,2,3)中，返回的是1/true，而 5 不在列表中，返回的是0/false:

• 示例4：LIKE —— 模糊匹配：% 表示的是任意多个字符，例如以下的命令行，'孙%'就可以将符合的情况全部列出来，即把所有姓孙的全部列出来，而如果是'孙_' ，_ 表示的是任意的一个字符，也就是说 _ 相当于一个占位符，有几个占位符就表示有多少个字符，因此只能列出名字只有两个字的姓孙的人：

如果想要用 '孙_' 将所有姓孙的且名字是三个字的表示出来，则要这样 '孙__'，用两个 _ 来表示后边可以跟的字符个数：

2】逻辑运算符

运算符	说明
AND	多个条件必须都为true(1)，才表示为true，相当于&&
OR	任意一个条件为true(1)，结果就表示为true，相当于||
NOT	条件为true(1)，结果为false(0)，相当于 ！

基本查询

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

结果集中没有 NULL ，说明自动过滤掉了值为 NULL 的列。

• 查询语文成绩高于英语成绩的同学

说明在一行数据中的两个列是可以进行比较的，但是不可以跨行比较，也就是说，列与列之间的比较只能是同一数据行的列与列之间比较。

• 总分在200分以下的同学

当我们想要给表达式起一个别名再用该别名进行条件判断/条件过滤时，不像在排序时一样可以正确通过：

这说明 where 子句不能用别名去当作过滤条件，也就是说，当where条件中使用了表达式，就要把表达式完整的写在where子句中，不能使用别名：

出现这种情况与MySQL内部的实现有关，就是与MySQL执行SQL语句的顺序有关：

1. 如果要在数据中查询某些数据，首先要确定数据来自哪一张表，即先执行 from
2. 在查询的过程中要根据指定的查询条件把符合条件的数据过滤出来，这时候执行的就是where子句
3. 然后再执行select后面的指定的列，这些列是需要加入到最终的结果集中的
4. 最后再执行排序，根据order by 子句中指定的列名和排序规则进行最后的排序

通过上述的执行顺序可知，where子句的执行要早于select后面指定的列，也就是说，where子句在对指定的列指定别名之前就执行了，因此此时的where并不知道别名total是谁，即total此时是未定义的，它在exam表中找不到total列，而排序order by子句在最后执行，因此可以使用别名排序。

AND和OR

• 查询语文成绩大于80分 且 英语成绩大于80分的同学

• 查询语文成绩大于80分 或 英语成绩大于80分的同学

• 观察AND和OR的优先级

根据返回的结果集可以得出一个结论：AND的优先级大于OR，整体的优先级顺序和Java中一样，NOT>AND>OR。

范围查询

• 语文成绩在 [80,90] 分的同学及语文成绩

对数据进行范围过滤，用以下的两种方式都可以，注意左右是闭区间。

• 数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩

使用 IN 实现：

使用 OR 实现：

模糊查询

• 查询所有姓孙的同学

• 查询姓孙且姓名共有两个字同学

%、_ 都是通配符，在使用的时候要用单引号引起来，且要注意通配符的位置要符合预期，例如以上的 '孙%' 表示的是以 孙 开头的任意多个字符，如果是 '%孙' 表示的是以 孙 结尾的任意多个字符，在查询的时候在表中无法找到这个列：

NULL的查询

• 查询英语成绩为NULL的记录

• 查询英语成绩不为NULL的记录

总结

• WHERE条件中可以使用表达式，但不能使用别名
• AND的优先级高于OR，在同时使用时，建议使用小括号()包裹优先执行的部分
• 过滤NULL时不要使用等于号(=)与不等于号(!=,<>)
• NULL与任何值运算结果都为NULL

Limit 分页查询

分页查询 LIMIT 作用：限制查询结果集中的条数。

之前我们学习 select * from 表名; 的时候，说过不加限制记录条数的查询是不安全的，那么分页查询可以解决这个问题。

分页查询在项目中运行的非常多，只要查询的是一个记录的集合(多条记录)都在使用分页查询

如上图，就是一个分页查询的例子，通过分页查询可以有效的控制一次查询出来的结果集中的记录的条数，可以有效减少数据库服务器的压力，同时对于用户也比较友好。

语法1：从0开始查询n条记录

--起始下标为 0
--从0开始，查询 n 条结果
select ... from 表名 [where...] [order by...] limit n; 

• limit - 关键字，表示限制要查询的条数
• n - 一次查询出来的记录条数
• 该语法的意思：从第0条开始，往后读取n条记录。

示例：从0(下标)开始查询2条记录 (也就是从第0条开始查询2条记录)

语法2：从 s 开始，查询 n 条记录

select ... from 表名 [where...] [order by...] limit s,n;

• s 表示从第几条记录开始查询，如果s=0，那么可以缩写成 limit n; 即语法1。
• n 表示读取/查询多少条记录
• 该语法的意思：从指定的条数开始，往后读取n条记录。

示例：从第2条(下标)记录开始查询2条记录

当 s=0 时：

如果指定的起始位置已经超出整个结果集的范围，也是可以执行的，只不过是一个空结果集：

语法3：从 s 开始，查询 n 条记录，比语法2的用法更明确，建议使用

select ... from 表名 [where...] [order by...] limit n offset s;

• offset : 关键字，表示偏移量，也就是从第s条开始查询的意思
• 与语法2相同，但建议使用语法3的表示方式

示例：从第2条开始查询2条记录，即偏移量为2，读取2条记录

我们在看回到以下的这个图片：

• 10条/页：表示的是每页包含多少条记录，这里是每页10条记录，也就是意味着 limit 中的 n 是多少，这里n=10
• 那么要查询的是第一页的10条记录，s 应该取的值是0，因为要从第一条开始查询，而第一条的下标是0。
• 以此类推，查询第二页的10条记录，那么就应该跳过之前查询第一页时显示的前10条，即查询第二页10条记录，应该从下标为10的位置上的记录开始查询。
• 公式：查询第几页的记录，s应该取值：s = (当前页号 - 1) * 每页显示的记录条数

3.Update 修改

语法：

update 表名 set colum = expr[,colum = expr...] [where...] [order by...] [limit...];

• update：修改操作的关键字
• set：设置，关键字
• colum = expr：表示要修改哪个列的值，即 哪个列 = 哪个值，可以一次修改多个列，中间用逗号隔开。
• where、order by、limit子句与查询语句的相同

示例1：将孙悟空同学的数学成绩变更为 80 分

问题：再插入一条学生姓名为孙悟空的数据行，然后再执行同样的修改操作，会有什么结果？

• 1.插入数据：

• 2.修改

可以看到修改成功，但是虽然匹配到了2条符合数据，不过只修改了需要修改的数据行，因为 id 为2 的孙悟空的数学成绩已经是80了。

• 3.如果将上述的两个孙悟空的数学成绩都改为90，那么就会全部被修改

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

注意：如果在进行 update 操作时，不加where条件限制，修改的是整张表中的记录，是非常危险的操作，在使用update语句时，要记得加条件限制。

示例2：将曹孟德同学的数学成绩变更为 60 分，语文绩变更为 70 分

示例3：将总成绩倒数前三的同学的数学成绩加上 30 分

该题我们需要做的：

1. 表达式运算，计算总分
2. 要求的是倒数前三的成绩，那么让总成绩进行升序排序，再取前3条记录
3. 在数学成绩的基础上加上30。

首先先查看原始数据中倒数前三的总成绩和数学成绩：

然后开始修改这三位同学的数学成绩以及查看修改结果：

修改后的倒数前三的同学的总成绩以及数学成绩：

示例4：将语文成绩小于50的同学的语文成绩更新为原来的2倍

注意：

• 以原值的基础上做变更时，不能使⽤math += 30这样的语法，MySQL不支持。
• 不加where条件时，会导致全表数据被列新，谨慎操作。

4.Delete 删除

语法：

delete from 表名 [where...] [order by...] [limit...];

示例1：删除孙悟空同学的考试成绩

示例2：删除英语成绩倒数前三的同学的所有数据

首先需要做的是对英语成绩进行升序排序，然后删除前三条记录，即英语成绩倒数前三的同学。

注意：执行 Delete 时不加条件会删除整张表的数据，即 delete from 表名; ，谨慎操作。

在生产环境中一般不去使用delete操作，一般会在表中加一个deleteState字段/列，用来表示这条记录是否删除，0表示正常，没有删除，而1表示已经删除；用update操作去更新deleteState字段，就可以实现删除功能，这条被删除的数据并没有实质上删除而是始终存在于数据库中。

5.总结

到这里，增删查改操作学习完成，现在我们来做一个总结。

（a）新增insert

insert into 表名[(列名[,列名][,列名]...)] values(值[,值][,值]...);

• 插入时，列名与值的个数一一对应。

（b）查询select

1】全列查询：查询表中所有的列，如果不加条件限制，会把表中所有的记录全部查询出来

select * from 表名;

2】指定列查询：按实际需要指定要查询的列

select 列名[,列名][,列名]... from 表名;

3】列名为表达式：表达式可以是常量，也可以是多个列的运算

select 列名/表达式 from 表名;

4】查询中使用别名：as可以省略，别名可以是任意的字符串，如果字符串中包含空格，一定要用单引号引起来

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

5】去重查询：如果查询多个列，去重时，所有列都相同才可以被判定为两行数相同

select distinct 列名[,列名][,列名]... from 表名;

6】排序：asc 升序,desc 降序

select */列名/表达式 [as 别名] from 表名 order by 列名/表达式/别名 asc|desc;

7】条件查询：where 中只能写列名或者表达式，不能使用别名

select * from 表名 where 列名/表达式 比较|逻辑运算符 条件 [order by...];

8】区间查询：等价于 开始条件<=列名<=结束条件；列名>=开始条件 and 列名<=结束条件

select * from 表名 where 列名 between 开始条件 and 结束条件;

9】模糊查询：% 可以匹配0个或者任意多个字符，_ 只能匹配一个字符

select * from 表名 where 列名 like '%值'|'_值';

10】分页查询：

查询结果集中从0开始的前n条数据行/记录：

select * from 表名 [where...] [order by...] limit n;

从第s条开始，向后查询n条记录：

--写法一：
select * from 表名 [where...] [order by...] limit s,n;
--写法二：
select * from 表名 [where...] [order by...] limit n offset s;

（c）修改update

update 表名 set 列名=值[,列名=值][,列名=值]... where 条件 order by 列名 asc|desc limit n; 

• 如果不加where条件，那么会导致表中所有的记录都被更新，危险操作。

（d）删除delete

delete from 表名 where 条件 order by 列名 asc|desc limit n;

• 如果不加where条件，那么会导致表中所有的记录都被删除，危险操作。