MySQL数据库：表的增删查改

目录

一. 向表中添加数据

1.1 insert插入

1.2 主键（唯一键）冲突问题

二. 表的查询

2.1 select指定列查询

2.2 where条件筛选

2.3 结果排序

2.4 分页显示结果

2.5 向表中插入查询数据

三. 更改表的数据

四. 删除表中数据

4.1 delete删除

4.2 表截断

五. 聚合函数

六. 分组查询

七. 总结

---

一. 向表中添加数据

1.1 insert插入

语法：insert into 表名称 (字段1, 字段2, ... ...)

           values (值11, 值12, ... ...), values (值21, 值22, ... ...);

解释：如果不显示指出字段，那么就是对表中全部字段都进行插入，如果需要多行插入，那么就在values后面给出多组数据即可。

以下面的students表为例，演示通过insert插入单行和多行数据的方法。

mysql> create table students (
    -> id int unsigned primary key,
    -> name varchar(20) not null,
    -> qq varchar(20)
    -> );
Query OK, 0 rows affected (0.20 sec)

insert into 插入单行数据：

mysql> insert into students values (1, '孙权', '11111');
Query OK, 1 row affected (0.04 sec)

mysql> insert into students values (2, '刘备', NULL);
Query OK, 1 row affected (0.04 sec)

insert into 同时插入多行数据：

mysql> insert into students (id, name) values
    -> (3, '曹操'), (4, '吕布');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

通过select * from students指令查看插入结果，可见成功插入了4行数据：

mysql> select * from students;
+----+--------+-------+
| id | name   | qq    |
+----+--------+-------+
|  1 | 孙权   | 11111 |
|  2 | 刘备   | NULL  |
|  3 | 曹操   | NULL  |
|  4 | 吕布   | NULL  |
+----+--------+-------+
4 rows in set (0.01 sec)

1.2 主键（唯一键）冲突问题

直接使用insert into向表中插入数据，如果发生了主键（唯一键）冲突，那么数据就会插入失败。

mysql> insert into students values (3,'曹植',22222);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

如果不改变主键（唯一键）值，有两种方式可以在主键冲突的情况下插入或修改数据：

• 通过on duplicate判断，在发生冲突时更改相应行的内容。
• 通过replace指令，发生主键或唯一键冲突的时候，先删除发生冲突的行，再重新插入。

使用duplicate在发生主键或唯一键冲突时更新字段值： 

语法：insert into ...... on duplicate key update 字段1=值1, 字段2=值2, ...... ;

解释：如果发生冲突，那么就将发生冲突的行的相应字段值更新；如果没有发生冲突，那么就相当于正常的insert into插入操作。

在不同的情况下，duplicate影响的行数有所不同：

• 如果不发生冲突，那么单纯的进行插入，影响1行数据。
• 如果发生冲突，但update的新值与原来的表中数据一样，那么影响0行数据。
• 如果发生冲突，update的新值与原来表中的数据不同，那么影响2行数据。

mysql> insert into students values (3,'曹操',22000);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> insert into students values (3,'曹操',22000)
    -> on duplicate key update name='曹操',qq='22000';
Query OK, 2 rows affected (0.01 sec)

使用replace在发生冲突时进行整行替换：

语法：replace into 表名称 (字段1, 字段2, ......) values (值1,值2, ......);

解释：如果主键（唯一键）冲突，那么删除原始行后更新，如果不冲突直接插入数据。

在不同的情况下，replace影响的行数也有所不同：

• 如果发生冲突，那么影响2行数据。
• 如果不发送冲突，那么影响1行数据。

mysql> replace into students values(3,'曹阿瞒',22200);  -- 主键冲突
Query OK, 2 rows affected (0.04 sec)

mysql> replace into students values(5,'袁绍',33333);  -- 不冲突
Query OK, 1 row affected (0.03 sec)

row_count函数：查看上次操作受影响的行数。

语法：select row_count();

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

二. 表的查询

本章使用下面代码创建的exam_result表，插入多行数据来演示表的查询操作。

mysql> create table exam_result (
    -> id int primary key auto_increment,
    -> name varchar(10) not null,
    -> math float(4,2) comment '数学成绩',
    -> chinese float(4,2) comment '语文成绩',
    -> english float(4,2) comment '英语成绩'
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into exam_result (name,math,chinese,english) values
    -> ('周瑜',99,82,85),
    -> ('孙策',78,87,95),
    -> ('孙仲谋',64,88,58),
    -> ('刘备',59,73,45),
    -> ('曹操',87,76,90),
    -> ('吕布',77,84,62);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

2.1 select指定列查询

全列查询：

在实际项目中一般不采用全列查询，因为一般生产实践中的数据库会存储海量数据，并且数据库为远端网络服务，进行全列查询容易因资源不足而造成卡死。 

语法：select * from TableName;

解释：*为通配符，会显示表中全部的内容，

mysql> select * from exam_result;
+----+-----------+-------+---------+---------+
| id | name      | math  | chinese | english |
+----+-----------+-------+---------+---------+
|  1 | 周瑜      | 99.00 |   82.00 |   85.00 |
|  2 | 孙策      | 78.00 |   87.00 |   95.00 |
|  3 | 孙仲谋    | 64.00 |   88.00 |   58.00 |
|  4 | 刘备      | 59.00 |   73.00 |   45.00 |
|  5 | 曹操      | 87.00 |   76.00 |   90.00 |
|  6 | 吕布      | 77.00 |   84.00 |   62.00 |
+----+-----------+-------+---------+---------+
6 rows in set (0.00 sec)

指定列查询：

在select后面指定要进行查询的列，在不同列之间通过逗号进行分隔。

语法：select 列1, 列2, ...... from 表名称;

mysql> select id,name,math from exam_result;  -- 查询学号、姓名和数学成绩
+----+-----------+-------+
| id | name      | math  |
+----+-----------+-------+
|  1 | 周瑜      | 99.00 |
|  2 | 孙策      | 78.00 |
|  3 | 孙仲谋    | 64.00 |
|  4 | 刘备      | 59.00 |
|  5 | 曹操      | 87.00 |
|  6 | 吕布      | 77.00 |
+----+-----------+-------+
6 rows in set (0.00 sec)

可以查询用户自定义的表达式：

mysql> select 10, english+10, math+chinese+english from exam_result;
+----+------------+----------------------+
| 10 | english+10 | math+chinese+english |
+----+------------+----------------------+
| 10 |      95.00 |               266.00 |
| 10 |     105.00 |               260.00 |
| 10 |      68.00 |               210.00 |
| 10 |      55.00 |               177.00 |
| 10 |     100.00 |               253.00 |
| 10 |      72.00 |               223.00 |
+----+------------+----------------------+
6 rows in set (0.00 sec)

在向前端界面打印查询结果时，对用户自定义的表达式进行重命名：

语法：select 表达式 [as] 新名称 from 表名称;

解释：as 可以省略，重命名是在对表中数据查询完成后，向前端打印的时候才执行的。

下面的代码打印出students中所有学生的总成绩(math + chinese + english)并重命名为total。

mysql> select id,name,math+english+chinese total from exam_result;
+----+-----------+--------+
| id | name      | total  |
+----+-----------+--------+
|  1 | 周瑜      | 266.00 |
|  2 | 孙策      | 260.00 |
|  3 | 孙仲谋    | 210.00 |
|  4 | 刘备      | 177.00 |
|  5 | 曹操      | 253.00 |
|  6 | 吕布      | 223.00 |
+----+-----------+--------+
6 rows in set (0.00 sec)

去重查询：

语法：select distinct 字段 from 表名称;

解释：from执行优先级高于distinct，先完成筛选再去重输出。

示例：对数学成绩去重输出

mysql> update exam_result set math=78 where id=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select math from exam_result;
+-------+
| math  |
+-------+
| 99.00 |
| 78.00 |
| 64.00 |
| 59.00 |
| 87.00 |
| 78.00 |
+-------+
6 rows in set (0.00 sec)

mysql> select distinct math from exam_result;
+-------+
| math  |
+-------+
| 99.00 |
| 78.00 |
| 64.00 |
| 59.00 |
| 87.00 |
+-------+
5 rows in set (0.00 sec)

2.2 where条件筛选

where筛选条件，一般通过 算数运算符 和 逻辑运算符 来指定。表1和表2为where条件筛选中常用的算数和逻辑运算符。

表1 算数运算符

运算符	含义
>  >=  <  <=	大于、大于等于、小于、小于等于
=	相等比较，NULL不安全，若NULL=NULL会被判断为假
<=>	相等比较，NULL安全，NULL<=>NULL为真
!=、<>	不等于比较，含义相同，NULL!=NULL和NULL<>NULL均为假
in(选项1, 选项2, ... ...)	如果为选项中任意一个，那么为真
between a0 and a1	判断val是否满足 a0 <= val <= a1
is null	是NULL
is not null	不是NULL
like	模糊匹配，%可以匹配任意个字符，包括0个，_匹配1个字符

表2 逻辑运算符

运算符	含义
and	如果多个条件都为真，那么返回真，否则返回假
or	如果多个条件之一为真，那么返回真，否则返回假
not	如果条件为真，返回假；条件为假，返回真

下面通过实际的案例，来熟悉每个操作符的使用方法：

案例：筛选总分(math + chinese + english) 大于 240 分的学生

注意：通过运算符进行条件筛选，不能使用别名，因为别名是在筛选完成后，向前端输出的时候，才会被重命名出来的。

mysql> select id,name,math+english+chinese total from exam_result where total > 240;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'
mysql> select id,name,math+english+chinese total from exam_result 
    -> where math + chinese + english > 240;
+----+--------+--------+
| id | name   | total  |
+----+--------+--------+
|  1 | 周瑜   | 266.00 |
|  2 | 孙策   | 260.00 |
|  5 | 曹操   | 253.00 |
+----+--------+--------+
3 rows in set (0.00 sec)

案例：筛选数学成绩为99分或59分的学生

可以通过等于 运算符 + 逻辑或 来达到目的，也可以使用 in(option,...) 进行筛选。

mysql> select id,name,math from exam_result where math=59 or math=99;
+----+--------+-------+
| id | name   | math  |
+----+--------+-------+
|  1 | 周瑜   | 99.00 |
|  4 | 刘备   | 59.00 |
+----+--------+-------+
2 rows in set (0.00 sec)

mysql> select id,name,math from exam_result where math in(59,99);
+----+--------+-------+
| id | name   | math  |
+----+--------+-------+
|  1 | 周瑜   | 99.00 |
|  4 | 刘备   | 59.00 |
+----+--------+-------+
2 rows in set (0.00 sec)

案例：筛选出英语成绩 大于等于80 && 小于等于95 的学生

可以使用 比较运算符 + and，和between ... and ... 两种方式来实现。

mysql> select id,name,english from exam_result where english>=80 and english<=95;
+----+--------+---------+
| id | name   | english |
+----+--------+---------+
|  1 | 周瑜   |   85.00 |
|  2 | 孙策   |   95.00 |
|  5 | 曹操   |   90.00 |
+----+--------+---------+
3 rows in set (0.00 sec)

mysql> select id,name,english from exam_result where english between 80 and 95;
+----+--------+---------+
| id | name   | english |
+----+--------+---------+
|  1 | 周瑜   |   85.00 |
|  2 | 孙策   |   95.00 |
|  5 | 曹操   |   90.00 |
+----+--------+---------+
3 rows in set (0.00 sec)

案例：筛选出孙某同学和孙姓同学的全部成绩

通过name like '孙%' 筛选孙姓同学，通过name like '孙_' 筛选孙某同学。

mysql> select * from exam_result where name like '孙%';
+----+-----------+-------+---------+---------+
| id | name      | math  | chinese | english |
+----+-----------+-------+---------+---------+
|  2 | 孙策      | 78.00 |   87.00 |   95.00 |
|  3 | 孙仲谋    | 64.00 |   88.00 |   58.00 |
+----+-----------+-------+---------+---------+
2 rows in set (0.00 sec)

mysql> select * from exam_result where name like '孙_';
+----+--------+-------+---------+---------+
| id | name   | math  | chinese | english |
+----+--------+-------+---------+---------+
|  2 | 孙策   | 78.00 |   87.00 |   95.00 |
+----+--------+-------+---------+---------+
1 row in set (0.00 sec)

案例：查询1.1章节表students中，qq为空和不为空的学生

mysql> select * from students where qq is null;
+----+--------+------+
| id | name   | qq   |
+----+--------+------+
|  2 | 刘备   | NULL |
|  4 | 吕布   | NULL |
+----+--------+------+
2 rows in set (0.00 sec)

mysql> select * from students where qq is not null;
+----+-----------+-------+
| id | name      | qq    |
+----+-----------+-------+
|  1 | 孙权      | 11111 |
|  3 | 曹阿瞒    | 22200 |
|  5 | 袁绍      | 33333 |
+----+-----------+-------+
3 rows in set (0.00 sec)

案例：对NULL进行 = 和 <=> 运算符比较测试

验证了=运算符对NULL不安全，<=>运算符对NULL安全，NULL和0并不相等。

mysql> select NULL=NULL,NULL=0,NULL=1;
+-----------+--------+--------+
| NULL=NULL | NULL=0 | NULL=1 |
+-----------+--------+--------+
|      NULL |   NULL |   NULL |
+-----------+--------+--------+
1 row in set (0.00 sec)

mysql> select NULL<=>NULL, NULL<=>0, NULL<=>1;
+-------------+----------+----------+
| NULL<=>NULL | NULL<=>0 | NULL<=>1 |
+-------------+----------+----------+
|           1 |        0 |        0 |
+-------------+----------+----------+
1 row in set (0.00 sec)

案例：对NULL进行!=和<>比较测试

NULL!=NULL和NULL<>NULL的比较结果均为假，证明!=和<>没有区别。

mysql> select NULL!=NULL,NULL!=0,NULL!=1;
+------------+---------+---------+
| NULL!=NULL | NULL!=0 | NULL!=1 |
+------------+---------+---------+
|       NULL |    NULL |    NULL |
+------------+---------+---------+
1 row in set (0.00 sec)

mysql> select NULL<>NULL,NULL<>0,NULL<>1;
+------------+---------+---------+
| NULL<>NULL | NULL<>0 | NULL<>1 |
+------------+---------+---------+
|       NULL |    NULL |    NULL |
+------------+---------+---------+
1 row in set (0.00 sec)

2.3 结果排序

语法：select ... from 表名称 [where ...] order by 排序参数 [desc/asc]

解释：

• desc为降序，asc为升序，如果不显示指定默认排升序
• order by的参数可以使用重命名后的参数名，因为order by是在全部输出结果都被确定下来的之后才进行排序的。
• NULL默认比任何值都小。

案例：将学生总成绩从高到低进行排序（排降序）

mysql> select id,name,math+chinese+english total from exam_result
    -> order by total desc;
+----+-----------+--------+
| id | name      | total  |
+----+-----------+--------+
|  1 | 周瑜      | 266.00 |
|  2 | 孙策      | 260.00 |
|  5 | 曹操      | 253.00 |
|  6 | 吕布      | 224.00 |
|  3 | 孙仲谋    | 210.00 |
|  4 | 刘备      | 177.00 |
+----+-----------+--------+
6 rows in set (0.00 sec)

案例：将学生成绩按照数学降序、语文升序、英语升序的顺序排序

结论：对于多参数排序，排序的优先级按照从前到后的顺序书写。

mysql> select * from exam_result 
    -> order by math desc, chinese, english;
+----+-----------+-------+---------+---------+
| id | name      | math  | chinese | english |
+----+-----------+-------+---------+---------+
|  1 | 周瑜      | 99.00 |   82.00 |   85.00 |
|  5 | 曹操      | 87.00 |   76.00 |   90.00 |
|  6 | 吕布      | 78.00 |   84.00 |   62.00 |
|  2 | 孙策      | 78.00 |   87.00 |   95.00 |
|  3 | 孙仲谋    | 64.00 |   88.00 |   58.00 |
|  4 | 刘备      | 59.00 |   73.00 |   45.00 |
+----+-----------+-------+---------+---------+
6 rows in set (0.00 sec)

案例：将1.1章节中定义的表，按照qq升序排序

验证了NULL比任何值都小的结论。 

mysql> select * from students order by qq asc;
+----+-----------+-------+
| id | name      | qq    |
+----+-----------+-------+
|  2 | 刘备      | NULL  |
|  4 | 吕布      | NULL  |
|  1 | 孙权      | 11111 |
|  3 | 曹阿瞒    | 22200 |
|  5 | 袁绍      | 33333 |
+----+-----------+-------+
5 rows in set (0.00 sec)

案例：where和order by结合使用 -- 查询不姓孙的同学的数学成绩，并将数学成绩排降序

order by的优先级低于where，应当先通过where筛选条件，然后在order by进行排序。

mysql> select id,name,math from exam_result
    -> where not name like '孙%'
    -> order by math desc;
+----+--------+-------+
| id | name   | math  |
+----+--------+-------+
|  1 | 周瑜   | 99.00 |
|  5 | 曹操   | 87.00 |
|  6 | 吕布   | 78.00 |
|  4 | 刘备   | 59.00 |
+----+--------+-------+
4 rows in set (0.00 sec)

2.4 分页显示结果

语法：select ... from 表名称 [where ...] [order by ...] limit s, n

有3种方式，可以实现使用limit进行分页显示的功能。

• limit n -- 从下标为0的行开始，显示n行信息；
• limit s,n -- 从下标s处开始，显示n行信息，起始下标为0，不足n行信息就显示到末尾；
• limit n offset s -- 从下标s处开始，显示n行信息，这种方式更加直观，推荐使用，但是此时s不能给定0，否则报错。

案例：筛选出总分>=210分的学生，排升序，从下标1开始显示4行数据。

mysql> select id, name, math + chinese + english total from exam_result
    -> where math + chinese + english >= 210 order by total
    -> limit 3 offset 1;
+----+--------+--------+
| id | name   | total  |
+----+--------+--------+
|  6 | 吕布   | 224.00 |
|  5 | 曹操   | 253.00 |
|  2 | 孙策   | 260.00 |
+----+--------+--------+
3 rows in set (0.00 sec)

案例：查看全部学生的数学成绩，将成绩按照升序排序，从下标0开始显示4行数据。

mysql> select id,name,math from exam_result order by math asc limit 4;
+----+-----------+-------+
| id | name      | math  |
+----+-----------+-------+
|  4 | 刘备      | 59.00 |
|  3 | 孙仲谋    | 64.00 |
|  6 | 吕布      | 78.00 |
|  2 | 孙策      | 78.00 |
+----+-----------+-------+
4 rows in set (0.00 sec)

2.5 向表中插入查询数据

语法：insert into 目标表名称 select ... from 表名称 [where ...] [order by ...] [limit ...]

通过将表中数据去重，来演示向表中插入查询数据的方法，表去重可以按照以下步骤实现：

• 创建一张与源表结构相同的表。
• 将表中去重查询后的数据插入到刚才创建的新表中去。
• 将源表和存放去除数据的表进行重命名。

创建结构相同的表：create table 新建表名称 like 源表名;

重命名表：rename table 原表名1 to 新表名1, 原表名2 to 新表名2;

mysql> create table duplicate (
    -> id int unsigned not null,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.21 sec)

// 向duplicate表中插入有重复的测试数据
mysql> insert into duplicate values
    -> (1,'aaa'),(1,'aaa'),
    -> (2,'bbb'),(2,'bbb'),
    -> (3,'ccc'),(4,'ddd');
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

// 创建与duplicate表具有相同结构的新表no_duplicate
mysql> create table no_duplicate like duplicate;
Query OK, 0 rows affected (0.27 sec)

// 向no_duplicate中插入表duplicate的去重查询数据
mysql> insert into no_duplicate select distinct * from duplicate;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

// 重命名表
mysql> rename table duplicate to old_duplicate,
    -> no_duplicate to duplicate;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from duplicate;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
|  4 | ddd  |
+----+------+
4 rows in set (0.00 sec)

三. 更改表的数据

通过update指令，可以更改表中的数据。

语法：update 表名称 set 字段名=值  [where ...] [order by ...] [limit ...]  

解释：

• 更改的范围限于通过where、order by 和 limit 筛选出来的内容
• 如果不指定条件，那么会更改全部内容

案例：将孙姓同学的英语成绩全部改为80分。

mysql> select id,name,english from exam_result where name like '孙%';
+----+-----------+---------+
| id | name      | english |
+----+-----------+---------+
|  2 | 孙策      |   95.00 |
|  3 | 孙仲谋    |   58.00 |
+----+-----------+---------+
2 rows in set (0.00 sec)

mysql> update exam_result set english=80 where name like '孙%';
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select id,name,english from exam_result where name like '孙%';
+----+-----------+---------+
| id | name      | english |
+----+-----------+---------+
|  2 | 孙策      |   80.00 |
|  3 | 孙仲谋    |   80.00 |
+----+-----------+---------+
2 rows in set (0.00 sec)

案例：将总分后3名的学生的英语成绩+10分

mysql> select id,name,english,math+chinese+english total from exam_result order by total asc;
+----+-----------+---------+--------+
| id | name      | english | total  |
+----+-----------+---------+--------+
|  4 | 刘备      |   45.00 | 177.00 |
|  6 | 吕布      |   62.00 | 224.00 |
|  3 | 孙仲谋    |   80.00 | 232.00 |  -- 刘备、吕布、孙仲谋的英语成绩应当+10
|  2 | 孙策      |   80.00 | 245.00 |
|  5 | 曹操      |   90.00 | 253.00 |
|  1 | 周瑜      |   85.00 | 266.00 |
+----+-----------+---------+--------+
6 rows in set (0.01 sec)

mysql> update exam_result set english=english+10 order by math+chinese+english limit 3;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select id,name,english,math+chinese+english total from exam_result order by total asc;
+----+-----------+---------+--------+
| id | name      | english | total  |
+----+-----------+---------+--------+
|  4 | 刘备      |   55.00 | 187.00 |
|  6 | 吕布      |   72.00 | 234.00 |
|  3 | 孙仲谋    |   90.00 | 242.00 |
|  2 | 孙策      |   80.00 | 245.00 |
|  5 | 曹操      |   90.00 | 253.00 |
|  1 | 周瑜      |   85.00 | 266.00 |
+----+-----------+---------+--------+
6 rows in set (0.00 sec)

案例：将所有同学的英语成绩+5

只需要update，不用通过where筛选条件。

mysql> select id,name,english from exam_result;
+----+-----------+---------+
| id | name      | english |
+----+-----------+---------+
|  1 | 周瑜      |   85.00 |
|  2 | 孙策      |   80.00 |
|  3 | 孙仲谋    |   90.00 |
|  4 | 刘备      |   55.00 |
|  5 | 曹操      |   90.00 |
|  6 | 吕布      |   72.00 |
+----+-----------+---------+
6 rows in set (0.01 sec)

mysql> update exam_result set english=english+5;
Query OK, 6 rows affected (0.03 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select id,name,english from exam_result;
+----+-----------+---------+
| id | name      | english |
+----+-----------+---------+
|  1 | 周瑜      |   90.00 |
|  2 | 孙策      |   85.00 |
|  3 | 孙仲谋    |   95.00 |
|  4 | 刘备      |   60.00 |
|  5 | 曹操      |   95.00 |
|  6 | 吕布      |   77.00 |
+----+-----------+---------+
6 rows in set (0.00 sec)

四. 删除表中数据

4.1 delete删除

语法：delete from 表名称 [where ...] [order by ...] [limit ...]

解释：delete执行按行删除，如果不指定条件，那么全表数据都会被删除。

案例：删除总成绩后2名的学生信息 

mysql> select id, name, math + chinese + english total from exam_result order by total;
+----+-----------+--------+
| id | name      | total  |
+----+-----------+--------+
|  4 | 刘备      | 192.00 |
|  6 | 吕布      | 239.00 |
|  3 | 孙仲谋    | 247.00 |
|  2 | 孙策      | 250.00 |
|  5 | 曹操      | 258.00 |
|  1 | 周瑜      | 271.00 |
+----+-----------+--------+
6 rows in set (0.00 sec)

mysql> delete from exam_result order by math + chinese + english asc limit 2;
Query OK, 2 rows affected (0.04 sec)

mysql> select id, name, math + chinese + english total from exam_result order by total;
+----+-----------+--------+
| id | name      | total  |
+----+-----------+--------+
|  3 | 孙仲谋    | 247.00 |
|  2 | 孙策      | 250.00 |
|  5 | 曹操      | 258.00 |
|  1 | 周瑜      | 271.00 |
+----+-----------+--------+
4 rows in set (0.00 sec)

案例：delete删除表中全部数据

因为全表删除是一种十分不推荐的行为，因此，单独创建for_delete表，其中包含自增长主键，并插入3行数据，直接通过delete from for_delete删除表中全部数据。

mysql> create table for_delete (
    -> id int unsigned primary key auto_increment,
    -> name varchar(10) not null
    -> );
Query OK, 0 rows affected (0.24 sec)

mysql> insert into for_delete (name) values ('zhang'),('wang'),('li');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from for_delete;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  2 | wang  |
|  3 | li    |
+----+-------+
3 rows in set (0.00 sec)

mysql> delete from for_delete;
Query OK, 3 rows affected (0.04 sec)

mysql> select * from for_delete;
Empty set (0.00 sec)

当使用delete删除表中全部数据时，再次插入数据，如果不显示给定自增主键值，那么就会采用被删除之前自增主键的最大值+1，并不会回归初始值。

mysql> insert into for_delete (name) values ('zhou'),('zheng');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from for_delete;
+----+-------+
| id | name  |
+----+-------+
|  4 | zhou  |
|  5 | zheng |
+----+-------+
2 rows in set (0.00 sec)

4.2 表截断

语法：truncate 表名称;

表截断操作truncate能够实现删除整张表数据的操作，但是却区别于delete：

• truncate只能对整张表操作，不能筛选条件，而delete能够筛选条件。
• truncate实际上不对数据进行操作，执行“删除”操作不经过事务，删除效率高于delete。
• truncate会初始化自增主键的值，而delete不会。

下面代码创建了for_truncate表，在中插入测试数据，通过truncate删除全部数据，然后再次插入数据，可以观察到truncate将auto_increment值初始化的作用，并且truncate操作受影响和行数为0。

-- 创建truncate测试用表
mysql> create table for_truncate (
    -> id int unsigned primary key auto_increment,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.17 sec)

-- 插入测试数据
mysql> insert into for_truncate (name) values ('A'),('B'),('D');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | D    |
+----+------+
3 rows in set (0.01 sec)

-- 通过truncate操作删除表中全部数据
mysql> truncate for_truncate;
Query OK, 0 rows affected (0.19 sec)

-- 输出表中全部数据，确定数据被删除
mysql> select * from for_truncate;
Empty set (0.00 sec)

-- 再次插入新数据
mysql> insert into for_truncate (name) values ('D');
Query OK, 1 row affected (0.03 sec)

-- 自增主键值被初始化，从1开始
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
|  1 | D    |
+----+------+
1 row in set (0.00 sec)

五. 聚合函数

表3 聚合函数

函数名	功能
count([distinct] 参数)	获取查询到的数据量，NULL不进行统计
sum([distinct] 参数)	获取查询到的数据的总和，不是数值没有意义
avg([distinct] 参数)	获取查询到的数据的平均值，不是数值没有意义
min([distinct] 参数)	获取查询到的数据的最小值，不是数值没有意义
max([distinct] 参数)	获取查询到的数据的最大值，不是数值没有意义

对于表中的函数，如果声明distinct，则表示对查询到的数据去重后再操作。 

案例：count函数的使用，查询学生数量

mysql> select count(*) from students;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

案例：查询表students中qq数量（结果去除NULL）

mysql> select count(qq) from students;
+-----------+
| count(qq) |
+-----------+
|         3 |
+-----------+
1 row in set (0.02 sec)

案例：统计所有学生语文成绩总分

mysql> select sum(chinese) from exam_result;
+--------------+
| sum(chinese) |
+--------------+
|       333.00 |
+--------------+
1 row in set (0.00 sec)

案例：统计所有学生总分平均分

mysql> select avg(math+chinese+english) from exam_result;
+---------------------------+
| avg(math+chinese+english) |
+---------------------------+
|                256.500000 |
+---------------------------+
1 row in set (0.00 sec)

案例：获取语文成绩最低分

mysql> select min(chinese) from exam_result;
+--------------+
| min(chinese) |
+--------------+
|        76.00 |
+--------------+
1 row in set (0.00 sec)

案例：获取英语成绩最高分

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

六. 分组查询

通过group by语句，可以实现分组进行查询。

语法：select ... from 表名称 group by 字段;

以oracle 9i中的经典雇员信息测试表（scott数据库）为例，演示group by的使用方法。其中包含三个子表，分别为：dept（部门信息表）、emp（雇员信息表）、salgrade（薪资等级表）。下面为这三张表的结构。

mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
+-----------------+
3 rows in set (0.00 sec)

mysql> desc dept;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| deptno | int(2) unsigned zerofill | NO   |     | NULL    |       |
| dname  | varchar(14)              | YES  |     | NULL    |       |
| loc    | varchar(13)              | YES  |     | NULL    |       |
+--------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc emp;
+----------+--------------------------+------+-----+---------+-------+
| Field    | Type                     | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| empno    | int(6) unsigned zerofill | NO   |     | NULL    |       |
| ename    | varchar(10)              | YES  |     | NULL    |       |
| job      | varchar(9)               | YES  |     | NULL    |       |
| mgr      | int(4) unsigned zerofill | YES  |     | NULL    |       |
| hiredate | datetime                 | YES  |     | NULL    |       |
| sal      | decimal(7,2)             | YES  |     | NULL    |       |
| comm     | decimal(7,2)             | YES  |     | NULL    |       |
| deptno   | int(2) unsigned zerofill | YES  |     | NULL    |       |
+----------+--------------------------+------+-----+---------+-------+
8 rows in set (0.02 sec)

mysql> desc salgrade;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| grade | int(11) | YES  |     | NULL    |       |
| losal | int(11) | YES  |     | NULL    |       |
| hisal | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

下面通过案例来演示group by操作的使用方法。

案例：统计不同部门(deptno)雇员的平均薪资、最高薪资和最低薪资。

mysql> select avg(sal),max(sal),min(sal),deptno from emp group by deptno;
+-------------+----------+----------+--------+
| avg(sal)    | max(sal) | min(sal) | deptno |
+-------------+----------+----------+--------+
| 2916.666667 |  5000.00 |  1300.00 |     10 |
| 2175.000000 |  3000.00 |   800.00 |     20 |
| 1566.666667 |  2850.00 |   950.00 |     30 |
+-------------+----------+----------+--------+
3 rows in set (0.00 sec)

案例：按照部门(deptno)和工种(job)统计平均薪资、最高薪资和最低薪资。

mysql> select deptno,job,avg(sal),max(sal),min(sal) from emp group by deptno,job;
+--------+-----------+-------------+----------+----------+
| deptno | job       | avg(sal)    | max(sal) | min(sal) |
+--------+-----------+-------------+----------+----------+
|     10 | CLERK     | 1300.000000 |  1300.00 |  1300.00 |
|     10 | MANAGER   | 2450.000000 |  2450.00 |  2450.00 |
|     10 | PRESIDENT | 5000.000000 |  5000.00 |  5000.00 |
|     20 | ANALYST   | 3000.000000 |  3000.00 |  3000.00 |
|     20 | CLERK     |  950.000000 |  1100.00 |   800.00 |
|     20 | MANAGER   | 2975.000000 |  2975.00 |  2975.00 |
|     30 | CLERK     |  950.000000 |   950.00 |   950.00 |
|     30 | MANAGER   | 2850.000000 |  2850.00 |  2850.00 |
|     30 | SALESMAN  | 1400.000000 |  1600.00 |  1250.00 |
+--------+-----------+-------------+----------+----------+
9 rows in set (0.00 sec)

案例：查看平均薪资小于2300的部门的平均薪资

通过having，可以实现对group by后的结果进行过滤，但是不可以使用where过滤group by的结果，因为having的执行优先级低于group by，where高于group by。

mysql> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal < 2300;
+--------+-------------+
| deptno | avg_sal     |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
2 rows in set (0.00 sec)

七. 总结

• 通过insert into指令，可以实现按行插入数据。如果发生主键或唯一键冲突，可以使用on duplicate或replace在发生主键或唯一键冲突时替换原始数据。
• 通过select可以查看行，通过where可以按条件筛选查看，order by可以对结果进行升序或降序显示，limit可以实现分页显示。
• update可以指定行来更改表中数据。
• delete和truncate都可以实现对表中数据的删除，但是delete可以指定条件删除，truncate只能对整表操作，truncate相比于delete更快，它不走事务，不实际对数据进行操作。
• 通过聚合函数可以实现对平均值、和、最大值、最小值等的计算，group by可以指定字段进行分组查询。