百度网站名称_深圳餐饮设计公司排名_新闻今天_搜狗登录入口

 > 作者：დ旧言~
> 座右铭：松树千年终是朽，槿花一日自为荣。

> 目标：知道表的增删查改语法并熟练掌握。

> 毒鸡汤：有些事情，总是不明白，所以我不会坚持。早安!

> 专栏选自：带你玩转MySQL

> 望小伙伴们点赞👍收藏✨加关注哟💕💕

​​​

一、前言

想必大家在学校也学习过MySQL，可能学的懵懵懂懂，这个板块我们从入门开始，从最新的安装MySQL到学习MySQL语句，一步一步开始，一切都是新的，新的板块新的开始，大家一起努力，一起进步！！！

​

  二、主体

学习【MySQL】表的增删查改咱们按照下面的图解：

CRUD是数据库中常用的术语，表示对数据进行增、删、查、改的操作，下面看一下所谓的 'C'、'R'、'U'、'D' 所表示的意思：

• C: Create(创建)，当然数据库的表插入是insert，创建是Create
• R: Retrieve(查询)，就是select
• U: Update(更新)，就是Update
• D: Delete(删除)，就是Delete

2.1 Create操作

Create表示新增数据的意思，一般是创建表的关键字

语法：

INSERT [INTO] table_name[(column [, column] ...)]VALUES (value_list) [, (value_list)] ...value_list: value, [, value] ...

举个栗子：

-- 创建一张学生表
create table students(id int unsigned primary key auto_increment,sn int not null unique coment '学号',name vachar(20) not null,qq vachar(20)
);

2.1.1 单行数据全列插入

使用：

mysql> insert into students values (100, 10000, '唐三藏', null);
Query OK, 1 row affected (0.00 sec)mysql> insert into students values (101, 10001, '孙悟空', '11111');
Query OK, 1 row affected (0.00 sec)-- 查看插入结果
mysql> select * from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏    | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
+-----+-------+-----------+-------+
2 rows in set (0.00 sec)mysql> 

注意事项：

• 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致。
• 这里在插入的时候，也可以不用指定id (当然，那时候就需要明确插入数据到那些列了)，那么mysql会使用默认的值进行自增。

2.1.2 多行数据指定列插入

使用：

mysql> insert into students (id, sn, name) values-> (102, 20001, '曹孟德'),-> (103, 20002, '孙仲谋');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0-- 查看插入结果
mysql> select * from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏    | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
| 102 | 20001 | 曹孟德    | NULL  |
| 103 | 20002 | 孙仲谋    | NULL  |
+-----+-------+-----------+-------+
4 rows in set (0.01 sec)mysql> 

注意事项：

• 插入两条记录，value_list 数量必须和指定列数量及顺序一致。
• 插入的多条记录之间使用逗号隔开，并且插入记录时可以只指定某些列进行插入。

 2.1.3 插入否则更新

由于 主键 或者 唯一键 对应的值已经存在而导致插入失败：

-- 主键冲突
mysql> insert into students (id, sn, name) values (100, 10010, '鲨鱼');
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'-- 唯一键冲突
mysql> insert into students (sn, name) values (20001, '雨果');
ERROR 1062 (23000): Duplicate entry '20001' for key 'sn'
mysql> 

这时可以选择性的进行同步更新操作：

• 如果表中没有冲突数据，则直接插入数据。
• 如果表中有冲突数据，则将表中的数据进行更新。

语法：

insert... on duplicate key update
column = value [, column = value] ..
-- on duplicate key 当发生重复key的时候,进行更新

举个栗子：

mysql> insert into students (id, sn, name) values (100, 10010, '.玄奘 ') on duplicate key update sn = 10010, name='.玄奘 ';
Query OK, 2 rows affected (0.00 sec)mysql> select * from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10010 | 唐玄奘    | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
| 102 | 20001 | 曹孟德    | NULL  |
| 103 | 20002 | 孙仲谋    | NULL  |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

执行插入否则更新的SQL后，可以通过受影响的数据行数来判断本次数据的插入情况：

• 0 rows affected：表中有冲突数据，但冲突数据的值和指定更新的值相同。
• 1 row affected：表中没有冲突数据，数据直接被插入。
• 2 rows affected：表中有冲突数据，并且数据已经被更新。

2.1.4 替换数据

replace用于替换，其原则是：

• 主键 或者 唯一键 没有冲突，则直接插入。
• 主键 或者 唯一键 如果冲突，则删除后再插入。

举个栗子：

mysql> replace into students (sn, name) values (20001, '不求人');
Query OK, 2 rows affected (0.00 sec)mysql> select * from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10010 | 唐玄奘    | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
| 103 | 20002 | 孙仲谋    | NULL  |
| 106 | 20001 | 不求人    | NULL  |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)mysql> 

执行替换数据的SQL后，也可以通过受影响的数据行数来判断本次数据的插入情况：

• 1 row affected：表中没有冲突数据，数据直接被插入。
• 2 rows affected：表中有冲突数据，冲突数据被删除后重新插入。

2.2 Retrieve操作

Retrieve表示查找数据意思，一般是查找表中的数据。

2.2.1 SELECT 操作

语法：

select
[distinct] {* | {column [, column] ...} #去重
[from table_name]
[where ...] #查询条件
[order by column [asc | desc], ...] #升序/降序
limit ...#显式几行数据记录

解释语法使用：

• SQL中大写的表示关键字，[ ]中代表的是可选项。
• { }中的 | 代表的是选择左侧的语句或右侧的语句。

创建表来解释查询语句：

-- 创建表结构
CREATE TABLE exam_result (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL COMMENT '同学姓名',chinese float DEFAULT 0.0 COMMENT '语文成绩',math float DEFAULT 0.0 COMMENT '数学成绩',english float DEFAULT 0.0 COMMENT '英语成绩'
);-- 插入测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES('唐三藏', 67, 98, 56),('孙悟空', 87, 78, 77),('猪悟能', 88, 98, 90),('曹孟德', 82, 84, 67),('刘玄德', 55, 85, 45),('孙权', 70, 73, 78),('宋公明', 75, 65, 30);

2.2.1.1 全列查询

举个栗子：

mysql> select * from exam_result;+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

解释说明：（通常情况下不建议使用*进行全列查询）

1. 查询的列越多，意味着需要传输的数据量越大。
2. 可能会影响到索引的使用。

2.2.1.2 指定列查询

指定列的顺序不需要按定义表的顺序来：

mysql> select id,name,english from exam_result;
+----+-----------+---------+
| id | name      | english |
+----+-----------+---------+
|  1 | 唐三藏    |      56 |
|  2 | 孙悟空    |      77 |
|  3 | 猪悟能    |      90 |
|  4 | 曹孟德    |      67 |
|  5 | 刘玄德    |      45 |
|  6 | 孙权      |      78 |
|  7 | 宋公明    |      30 |
+----+-----------+---------+
7 rows in set (0.00 sec)mysql> 

2.2.1.3 为查询结果指定别名

语法：

SELECT column [AS] alias_name [...] FROM table_name;

表达式不包含字段：

mysql> select id,name, 10 from exam_result;
+----+-----------+----+
| id | name      | 10 |
+----+-----------+----+
|  1 | 唐三藏    | 10 |
|  2 | 孙悟空    | 10 |
|  3 | 猪悟能    | 10 |
|  4 | 曹孟德    | 10 |
|  5 | 刘玄德    | 10 |
|  6 | 孙权      | 10 |
|  7 | 宋公明    | 10 |
+----+-----------+----+
7 rows in set (0.00 sec)

表达式包含一个字段：

mysql> select id,name, english + 10 from exam_result;
+----+-----------+--------------+
| id | name      | english + 10 |
+----+-----------+--------------+
|  1 | 唐三藏    |           66 |
|  2 | 孙悟空    |           87 |
|  3 | 猪悟能    |          100 |
|  4 | 曹孟德    |           77 |
|  5 | 刘玄德    |           55 |
|  6 | 孙权      |           88 |
|  7 | 宋公明    |           40 |
+----+-----------+--------------+
7 rows in set (0.00 sec)mysql> 

表达式包含多个字段：

mysql> select id,name, chinese + math + english from exam_result;
+----+-----------+--------------------------+
| id | name      | chinese + math + english |
+----+-----------+--------------------------+
|  1 | 唐三藏    |                      221 |
|  2 | 孙悟空    |                      242 |
|  3 | 猪悟能    |                      276 |
|  4 | 曹孟德    |                      233 |
|  5 | 刘玄德    |                      185 |
|  6 | 孙权      |                      221 |
|  7 | 宋公明    |                      170 |
+----+-----------+--------------------------+
7 rows in set (0.00 sec)mysql> 

2.2.1.4 为查询结果指定别名

语法：

SELECT column [AS] alias_name [...] FROM table_name;- - 其中 AS 可以省略

举个栗子：将每一条记录中的三科成绩相加，然后将相加结果对应的列指定别名为“总分”

mysql> select id,name, chinese + math + english as 总分 from exam_result;
+----+-----------+--------+
| id | name      | 总分   |
+----+-----------+--------+
|  1 | 唐三藏    |    221 |
|  2 | 孙悟空    |    242 |
|  3 | 猪悟能    |    276 |
|  4 | 曹孟德    |    233 |
|  5 | 刘玄德    |    185 |
|  6 | 孙权      |    221 |
|  7 | 宋公明    |    170 |
+----+-----------+--------+
7 rows in set (0.00 sec)mysql> 

2.2.1.5 结果去重

查询到成绩时指定查询数学成绩对应的列，可以看到数学成绩中有重复的分数。如果想要对查询结果进行去重操作，可以在SQL中的select后面带上distinct：

mysql> select distinct math from exam_result;

使用结果：

mysql> select math from exam_result;
+------+
| math |
+------+
|   98 |
|   78 |
|   98 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+
7 rows in set (0.00 sec)----去重后mysql> select distinct  math from exam_result;
+------+
| math |
+------+
|   98 |
|   78 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+
6 rows in set (0.00 sec)mysql> 

2.2.2 WHERE 条件

添加where子句的区别：

• 如果在查询数据时没有指定where子句，那么会直接将表中所有的记录作为数据源来依次执行select语句。
• 如果在查询数据时指定了where子句，那么在查询数据时会先根据where子句筛选出符合条件的记录，然后将符合条件的记录作为数据源来依次执行select语句。

where子句中可以指明一个或多个筛选条件，各个筛选条件之间用逻辑运算符AND或OR进行关联，下面给出了where子句中常用的比较运算符和逻辑运算符。

2.2.2.1 比较与逻辑运算符

比较运算符：

逻辑运算符：

2.2.2.2 查询案例

1. 查询英语不及格的同学及其英语成绩：

select name, english from exam_result where english<60;

mysql> select name, english from exam_result where english < 60;
+-----------+---------+
| name      | english |
+-----------+---------+
| 唐三藏    |      56 |
| 刘玄德    |      45 |
| 宋公明    |      30 |
+-----------+---------+
3 rows in set (0.00 sec)mysql> 

2. 查询语文成绩在80到90分的同学及其语文成绩：

①：使用 and 进行条件连接

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

②：使用 between ... and ... 条件

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

3. 查询数学成绩是58或59或98或99分的同学及其数学成绩：

①：使用 or 进行条件连接

②：使用 in 条件

分别查询性孙的同学和孙某同学：

①：查询姓孙的同学（通过模糊匹配来判断当前同学是否性孙，需要用到 % 来匹配多个字符）

select name from exam_result where name like '孙%';

②：查询孙某同学（通过模糊匹配来判断当前同学是否性孙，需要用到 _ 来匹配多个字符）

select name from exam_result where name like '孙_';

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

select name,chinese+math+english 总分 from exam_result where chinese+math+english<200;

查询总成绩在120分以下的同学：

select name,chinese+math+english 总分 from exam_result where chinese+math+english<200;

需要注意的是，在where子句中不能使用select中指定的别名：

• 查询数据时是先根据where子句筛选出符合条件的记录。
• 然后再将符合条件的记录作为数据源来依次执行select语句。

查询语文成绩大于80分并且不姓孙的同学：

select name,chinese from exam_result where chinese>80 and name not like '孙%';

查询孙某同学，否则要求总成绩大于200分并且语文成绩小于数学成绩并且英语成绩大于80分：

mysql> select name,chinese,math,english, chinese+math+english 总分 from exam_result-> where (name like '孙_') or (chinese+math+english>200 and chinese<math and english>80);

2.2.2.2.1 NULL的查询

这里用之前的学成表来演示NULL查询，学生表中的内容如下：

查询QQ号已知的同学：

select name,qq from students where qq is not null;

查询QQ号未知的同学：

select name,qq from students where qq<=>null;

注意事项：

• 需要注意的是，在与NULL值作比较的时候应该使用 <==> 运算符，使用=运算符无法得到正确的查询结果。
• = 运算符是NULL不安全的，使用 = 运算符将任何值与NULL作比较，得到的结果都是NULL。
•  <=> 运算符是NULL安全的，使用<=>运算符将NULL和NULL作比较得到的结果为TRUE(1)，将非NULL值与NULL作比较得到的结果为FALSE(0)。

2.2.2.3 结果排序

语法：

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

细节说明：

• SQL中大写的表示关键字，[ ] 中代表的是可选项。
• ASC 和 DESC 分别代表的是排升序和排降序，默认为ASC (升序) 。
• 如果查询SQL中没有order by子句，那么返回值的顺序是非定义的。

1.查询同学及其数学成绩，按数学成绩升序显示：

mysql> select name, math from exam_result order by math asc;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   65 |
| 孙权      |   73 |
| 孙悟空    |   78 |
| 曹孟德    |   84 |
| 刘玄德    |   85 |
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
7 rows in set (0.00 sec)mysql> 

2.查询同学及其QQ号，按QQ号排序显示：

-- NULL 视为比任何值都小，升序出现在最上面
mysql> select name, qq from students order by qq;
+-----------+-------+
| name      | qq    |
+-----------+-------+
| 唐玄奘    | NULL  |
| 孙仲谋    | NULL  |
| 曹阿瞒    | NULL  |
| 孙悟空    | 11111 |
+-----------+-------+
4 rows in set (0.00 sec)-- NULL 视为比任何值都小，降序出现在最下面
mysql> select name, qq from students order by qq desc;
+-----------+-------+
| name      | qq    |
+-----------+-------+
| 孙悟空    | 11111 |
| 唐玄奘    | NULL  |
| 孙仲谋    | NULL  |
| 曹阿瞒    | NULL  |
+-----------+-------+
4 rows in set (0.00 sec)mysql> 

3.查询同学的各门成绩，依次按数学降序、英语升序、语文升序显示：

-- 多字段排序，排序优先级随书写顺序
mysql> select name, chinese, math, english from exam_result order by math desc, english, chinese;
+-----------+---------+------+---------+
| name      | chinese | math | english |
+-----------+---------+------+---------+
| 唐三藏    |      67 |   98 |      56 |
| 猪悟能    |      88 |   98 |      90 |
| 刘玄德    |      55 |   85 |      45 |
| 曹孟德    |      82 |   84 |      67 |
| 孙悟空    |      87 |   78 |      77 |
| 孙权      |      70 |   73 |      78 |
| 宋公明    |      75 |   65 |      30 |
+-----------+---------+------+---------+
7 rows in set (0.00 sec)mysql> 

• order by子句中可以指明按照多个字段进行排序，每个字段都可以指明按照升序或降序进行排序，各个字段之间使用逗号隔开，排序优先级与书写顺序相同。
• 比如上述SQL中，当两条记录的数学成绩相同时就会按照英语成绩进行排序，如果这两条记录的英语成绩也相同就会继续按照语文成绩进行排序，以此类推。

4.查询同学及其总分，按总分降序显示：

-- ORDER BY 中可以使用表达式
mysql> SELECT name, chinese + english + math FROM exam_result-> ORDER BY chinese + english + math DESC;
+-----------+--------------------------+
| name      | chinese + english + math |
+-----------+--------------------------+
| 猪悟能    |                      276 |
| 孙悟空    |                      242 |
| 曹孟德    |                      233 |
| 唐三藏    |                      221 |
| 孙权      |                      221 |
| 刘玄德    |                      185 |
| 宋公明    |                      170 |
+-----------+--------------------------+
7 rows in set (0.00 sec)-- ORDER BY 子句中可以使用列别名mysql> SELECT name, chinese + english + math 总分 FROM exam_result-> ORDER BY 总分 DESC;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 猪悟能    |    276 |
| 孙悟空    |    242 |
| 曹孟德    |    233 |
| 唐三藏    |    221 |
| 孙权      |    221 |
| 刘玄德    |    185 |
| 宋公明    |    170 |
+-----------+--------+
7 rows in set (0.00 sec)mysql> -- 原因在于最后排序是根据查询出来的结果进行排序的，即 select 比 order by 先执行

• 查询数据时是先根据where子句筛选出符合条件的记录。
• 然后再将符合条件的记录作为数据源来依次执行select语句。
• 最后再通过order by子句对select语句的执行结果进行排序。

5.姓孙的同学或姓曹的同学及其数学成绩，按数学成绩降序显示：

-- 结合 WHERE 子句 和 ORDER BY 子句
mysql> select name, math from exam_result -> where name like '孙%' or name like '曹%' -> order by math desc;
+-----------+------+
| name      | math |
+-----------+------+
| 曹孟德    |   84 |
| 孙悟空    |   78 |
| 孙权      |   73 |
+-----------+------+
3 rows in set (0.00 sec)mysql> 

2.2.2.4筛选分页结果

语法：

-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

解释说明：

• 查询SQL中各语句的执行顺序为：where、select、order by、limit。
• limit子句在筛选记录时，记录的下标从0开始。
• 对未知表进行查询时最好在查询SQL后加上 limit 1，避免在查询全表数据时因为表中数据过大而导致数据库卡死。

1.按id进行分页，每页3条记录，分别显示第1、2、3页：

-- 第 1 页
mysql> select id, name, chinese, math, english from exam_result order by id limit 3 offset 0;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)-- 第 2 页
mysql> select id, name, chinese, math, english from exam_result order by id limit 3 offset 3;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)-- 第 3 页，如果结果不足 3 个，不会有影响
mysql> select id, name, chinese, math, english from exam_result order by id limit 3 offset 6;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)mysql> 

2.3 Update 操作

语法：

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

解释说明：

• SQL中的column=expr，表示将记录中列名为column的值修改为expr。
• 在修改数据之前需要先找到待修改的记录，update语句中的where、order by和limit就是用来定位数据的。

1.将孙悟空同学的成绩修改为80分：

-- 更新值为具体值-- 查看原数据
mysql> select name, math from exam_result where name = '孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   78 |
+-----------+------+
1 row in set (0.00 sec)-- 数据更新
mysql> update exam_result set math = 80 where name = '孙悟空';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0-- 查看更新后数据
mysql> select name, math from exam_result where name = '孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   80 |
+-----------+------+
1 row in set (0.00 sec)

2.将曹孟德同学的数学成绩修改为60分，语文成绩修改为70分：

-- 一次更新多个列-- 查看原数据
mysql> select name, math, chinese from exam_result where name = '曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   84 |      82 |
+-----------+------+---------+
1 row in set (0.00 sec)-- 数据更新
mysql> update exam_result set math = 60, chinese = 70 where name = '曹孟德';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0-- 查看更新后数据
mysql> select name, math, chinese from exam_result where name = '曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   60 |      70 |
+-----------+------+---------+
1 row in set (0.00 sec)

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

-- 更新值为原值基础上变更-- 查看原数据
-- 别名可以在ORDER BY中使用
mysql> select name, math, chinese + math + english 总分 from exam_result order by 总分 limit 3;
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 宋公明    |   65 |    170 |
| 刘玄德    |   85 |    185 |
| 曹孟德    |   60 |    197 |
+-----------+------+--------+
3 rows in set (0.00 sec)-- 数据更新，不支持 math += 30 这种语法
mysql> update exam_result set math = math + 30 order by chinese + math + english limit 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0-- 查看更新后数据
-- 注意：这里不能在按照总分前三来查看更新后的数据，因为排名已经发生变化
mysql> select name, math, chinese + math + english 总分 from exam_result where name in ('宋公明', '刘玄德', '曹孟德');
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 曹孟德    |   90 |    227 |
| 刘玄德    |  115 |    215 |
| 宋公明    |   95 |    200 |
+-----------+------+--------+
3 rows in set (0.00 sec)

4.将所有同学的语文成绩修改为原来的2倍：

-- 没有 WHERE 子句，则更新全表-- 查看原数据
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   80 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      70 |   90 |      67 |
|  5 | 刘玄德    |      55 |  115 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   95 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)-- 数据更新
mysql> update exam_result set chinese = chinese * 2;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0-- 查看更新后数据
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  2 | 孙悟空    |     174 |   80 |      77 |
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  5 | 刘玄德    |     110 |  115 |      45 |
|  6 | 孙权      |     140 |   73 |      78 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

2.4 Delete 操作

---

2.4.1删除数据

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

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

-- 查看原数据
mysql> select * from exam_result where name = '孙悟空';
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  2 | 孙悟空    |     174 |   80 |      77 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)-- 删除数据
mysql> delete from exam_result where name = '孙悟空';
Query OK, 1 row affected (0.00 sec)-- 查看删除结果
mysql> select * from exam_result where name = '孙悟空';
Empty set (0.00 sec)

2.删除整张表数据：

-- 准备测试表
CREATE TABLE for_delete (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20)
);-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');-- 查看测试数据
mysql> SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)-- 删除整表数据
mysql> DELETE FROM for_delete;
Query OK, 3 rows affected (0.00 sec)-- 查看删除结果
mysql> SELECT * FROM for_delete;
Empty set (0.00 sec)-- 再插入一条数据，自增 id 在原值上增长
mysql> INSERT INTO for_delete (name) VALUES ('D');
Query OK, 1 row affected (0.01 sec)mysql> SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
|  4 | D    |
+----+------+
1 row in set (0.00 sec)-- 查看表结构，会有 AUTO_INCREMENT=n 项
mysql> SHOW CREATE TABLE for_delete\G
*************************** 1. row ***************************Table: for_delete
Create Table: CREATE TABLE `for_delete` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

• 当我们向表中再插入一些数据时，在插入数据时不指明自增长字段的值，这时你会发现插入数据对应的自增长id值是在之前的基础上继续增长的。
• 因此，当通过delete语句删除整张表数据时，不会重置AUTO_INCREMENT=n字段，因此删除整张表数据后插入数据对应的自增长id值会在原来的基础上继续增长。

2.4.2截断表

语法：

TRUNCATE [TABLE] table_name

解释：

• truncate只能对整表操作，不能像delete一样针对部分数据操作。
• truncate实际上不对数据操作，所以比delete更快。
• truncate在删除数据时不经过真正的事务，所以无法回滚。
• truncate会重置AUTO_INCREMENT=n字段。

举个栗子：

-- 准备测试表
CREATE TABLE for_truncate (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20)
);-- 插入测试数据
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');-- 查看测试数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
TRUNCATE for_truncate;
Query OK, 0 rows affected (0.10 sec)-- 查看删除结果
SELECT * FROM for_truncate;
Empty set (0.00 sec)-- 再插入一条数据，自增 id 在重新增长
INSERT INTO for_truncate (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)-- 查看数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | D |
+----+------+
1 row in set (0.00 sec)-- 查看表结构，会有 AUTO_INCREMENT=2 项
SHOW CREATE TABLE for_truncate\G
*************************** 1. row ***************************Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

总结：

• 在truncate语句中只指明要删除数据的表名，这时便会删除整张表的数据，但由于truncate实际不对数据操作，因此执行truncate语句后看到影响行数为0。
• 再向表中插入一些数据，在插入数据时不指明自增长字段的值，这时会发现插入数据对应的自增长id值是重新从1开始增长的。
• 因此当通过truncate语句删除整张表数据时，会重置 AUTO_INCREMENT 字段， 因此截断表后插入数据对应的自增长id值会重新从1开始增长。

2.5插入查询结果

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

解释说明：

• SQL的作用是将筛选出来的记录插入到指定的表当中。
• SQL中的column表示将筛选出的记录的各个列插入到表中的哪一列

举个栗子：删除表中的的重复复记录，重复的数据只能有一份

-- 创建原数据表
mysql> CREATE TABLE duplicate_table (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)-- 插入测试数据
mysql> INSERT INTO duplicate_table VALUES-> (100, 'aaa'),-> (100, 'aaa'),-> (200, 'bbb'),-> (200, 'bbb'),-> (200, 'bbb'),-> (300, 'ccc');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
mysql> CREATE TABLE no_duplicate_table LIKE duplicate_table;
Query OK, 0 rows affected (0.01 sec)-- 将 duplicate_table 的去重数据插入到 no_duplicate_table
mysql> INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0-- 通过重命名表，实现原子的去重操作
mysql> RENAME TABLE duplicate_table TO old_duplicate_table, no_duplicate_table TO duplicate_table;
Query OK, 0 rows affected (0.01 sec)-- 查看最终结果
mysql> SELECT * FROM duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

2.6聚合函数

聚合函数分类：

1.统计班级共有多少同学：

-- 使用 * 做统计，不受 NULL 影响
mysql> select count(*) from students;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)-- 使用表达式做统计
mysql> select count(1) from students;
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

2.统计班级收集的QQ号有多少个：

-- NULL 不会计入结果
mysql> select count(qq) from students;
+-----------+
| count(qq) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

3.统计本次考试数学成绩的分数个数：

-- COUNT(math) 统计的是全部成绩
mysql> select count(math) from exam_result;
+-------------+
| count(math) |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)-- COUNT(DISTINCT math) 统计的是去重成绩数量
mysql> select count(distinct math) from exam_result;
+----------------------+
| count(distinct math) |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.00 sec)

4.统计数学成绩总分：

mysql> select sum(math) from exam_result;
+-----------+
| sum(math) |
+-----------+
|       569 |
+-----------+
1 row in set (0.00 sec)-- 不及格 < 60 的总分，没有结果，返回 NULL
mysql> select sum(math) from exam_result where math < 60;
+-----------+
| sum(math) |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

5.统计平均总分：

mysql> select avg(chinese + math + english) 平均总分 from exam_result;
+--------------+
| 平均总分     |
+--------------+
|        297.5 |
+--------------+
1 row in set (0.00 sec)

6.返回英语最高分：

mysql> select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
|           90 |
+--------------+
1 row in set (0.00 sec)

7.返回70分以上的英语最低分：

mysql> select min(math) from exam_result where math > 70;
+-----------+
| min(math) |
+-----------+
|        73 |
+-----------+
1 row in set (0.00 sec)

2.7 group by 子句（分组查询）

语法：

SELECT column1 [, column2], ... FROM table_name [WHERE ...] GROUP BY column [, ...] [order by ...] [LIMIT ...];

解释说明：

• 查询SQL中各语句的执行顺序为：where、group by、select、order by、limit。
• group by后面的列名，表示按照指定列进行分组查询。

举个栗子：（来自oracle 9i的经典测试表）

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;USE `scott`;DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (`deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',`dname` varchar(14) DEFAULT NULL COMMENT '部门名称',`loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',`ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',`job` varchar(9) DEFAULT NULL COMMENT '雇员职位',`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',`hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',`sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (`grade` int(11) DEFAULT NULL COMMENT '等级',`losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',`hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

①显示每个部门的平均工资和最高工资：

select deptno,avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno;

②显示每个部门的每种岗位的平均工资和最低工资：

select avg(sal),min(sal),job, deptno from EMP group by deptno, job;

2.7.1 HAVING 条件

语法：

SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] [order by ...] [LIMIT ...];

解释说明：

• having子句中可以指明一个或多个筛选条件。

having和where子句的区别：

• where子句放在表名后面，而having子句必须搭配group by子句使用，放在group by子句的后面。
• where子句是对整表的数据进行筛选，having子句是对分组后的数据进行筛选。
• where子句中不能使用聚合函数和别名，而having子句中可以使用聚合函数和别名

SQL中各语句的执行顺序：（查询数据而定）

1. 根据where子句筛选出符合条件的记录。
2. 根据group by子句对数据进行分组。
3. 将分组后的数据依次执行select语句。
4. 根据having子句对分组后的数据进行进一步筛选。
5. 根据order by子句对数据进行排序。
6. 根据limit子句筛选若干条记录进行显示。

1.显示平均工资低于2000的部门和它的平均工资：

select deptno,avg(sal) from emp group by deptno having < 2000;

三、结束语 

       今天内容就到这里啦，时间过得很快，大家沉下心来好好学习，会有一定的收获的，大家多多坚持，嘻嘻，成功路上注定孤独，因为坚持的人不多。那请大家举起自己的小手给博主一键三连，有你们的支持是我最大的动力💞💞💞，回见。

​​​​