【MySQL基础篇重点】六、表的增删查改

insert [into] table_name
[(列名, 列名, ...)]
values (value_list) [, (value_list), ...] 

其中：
	value_list: value, [, value, ...] 

-- 主键冲突
insert into students (id, sn, name) values (1, 10010, 'lirendada');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

-- 唯一键冲突
insert into students (sn, name) values (102, 'james');
ERROR 1062 (23000): Duplicate entry '20001' for key 'sn'

insert ... on DUPLICATE key update column = value [, column = value, ...] 

replace into 表名 (要插入的列字段) values (列字段对应的值);

select
    [distinct] 								# 去重
    {* 或者 {列名 [, 列名] ...}       
    [from] 表名							  # 要检索的表名
    [where ...]								# 用于指定条件来过滤结果
    [order by column [asc | desc], ...]		# 排序
    [limit ...]								# 限定筛选条数

-- 创建表结构
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);

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)

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

-- 表达式不包含字段
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,10+math from exam_result;
+----+-----------+---------+
| id | name      | 10+math |
+----+-----------+---------+
|  1 | 唐三藏    |     108 |
|  2 | 孙悟空    |      88 |
|  3 | 猪悟能    |     108 |
|  4 | 曹孟德    |      94 |
|  5 | 刘玄德    |      95 |
|  6 | 孙权      |      83 |
|  7 | 宋公明    |      75 |
+----+-----------+---------+
7 rows in set (0.00 sec)

-- 表达式包含多个字段
mysql> select id,name,10+math+chinese+english from exam_result;
+----+-----------+-------------------------+
| id | name      | 10+math+chinese+english |
+----+-----------+-------------------------+
|  1 | 唐三藏    |                     231 |
|  2 | 孙悟空    |                     252 |
|  3 | 猪悟能    |                     286 |
|  4 | 曹孟德    |                     243 |
|  5 | 刘玄德    |                     195 |
|  6 | 孙权      |                     231 |
|  7 | 宋公明    |                     180 |
+----+-----------+-------------------------+
7 rows in set (0.00 sec)

select 列字段 [as] alias_name [...] from 表名;

mysql> select id,name,math+chinese+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> select math+chinese+english as 总分 from exam_result;
+--------+
| 总分   |
+--------+
|    221 |
|    242 |
|    276 |
|    233 |
|    185 |
|    221 |
|    170 |
+--------+
7 rows in set (0.00 sec)

-- 去重后
mysql> select distinct math+chinese+english as 总分 from exam_result;
+--------+
| 总分   |
+--------+
|    221 |
|    242 |
|    276 |
|    233 |
|    185 |
|    170 |
+--------+
6 rows in set (0.00 sec)

-- 筛选前
mysql> select name,english as '英语' from exam_result;
+-----------+--------+
| name      | 英语   |
+-----------+--------+
| 唐三藏    |     56 |
| 孙悟空    |     77 |
| 猪悟能    |     90 |
| 曹孟德    |     67 |
| 刘玄德    |     45 |
| 孙权      |     78 |
| 宋公明    |     30 |
+-----------+--------+
7 rows in set (0.00 sec)

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

-- 筛选前
mysql> select name,chinese as '语文' from exam_result;
+-----------+--------+
| name      | 语文   |
+-----------+--------+
| 唐三藏    |     67 |
| 孙悟空    |     87 |
| 猪悟能    |     88 |
| 曹孟德    |     82 |
| 刘玄德    |     55 |
| 孙权      |     70 |
| 宋公明    |     75 |
+-----------+--------+
7 rows in set (0.00 sec)


-- 使用between and筛选
mysql> select name,chinese as '语文' from exam_result where chinese between 80 and 90;
+-----------+--------+
| name      | 语文   |
+-----------+--------+
| 孙悟空    |     87 |
| 猪悟能    |     88 |
| 曹孟德    |     82 |
+-----------+--------+
3 rows in set (0.00 sec)

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


-- 使用or筛选
mysql> select name,math as '数学' from exam_result where math=58 or math=59 or math=98 or math=99;
+-----------+--------+
| name      | 数学   |
+-----------+--------+
| 唐三藏    |     98 |
| 猪悟能    |     98 |
+-----------+--------+
2 rows in set (0.00 sec)

-- 使用in筛选，更加的优雅！
mysql> select name,math as '数学' from exam_result where math in(58, 59, 98, 99);
+-----------+--------+
| name      | 数学   |
+-----------+--------+
| 唐三藏    |     98 |
| 猪悟能    |     98 |
+-----------+--------+
2 rows in set (0.00 sec)

-- 筛选前
mysql> select name from exam_result;
+-----------+
| name      |
+-----------+
| 唐三藏    |
| 孙悟空    |
| 猪悟能    |
| 曹孟德    |
| 刘玄德    |
| 孙权      |
| 宋公明    |
+-----------+
7 rows in set (0.00 sec)

-- 通过like以及两个通配符来筛选，两者用or连接
-- % 表示匹配任意多个（包括0个）任意字符
-- _ 表示匹配严格的一个任意字符
mysql> select name from exam_result where name like '孙%' or name like '孙_';
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙权      |
+-----------+
2 rows in set (0.00 sec)

-- 筛选前
mysql> select name,chinese as '语文',math as '数学' from exam_result;
+-----------+--------+--------+
| name      | 语文   | 数学   |
+-----------+--------+--------+
| 唐三藏    |     67 |     98 |
| 孙悟空    |     87 |     78 |
| 猪悟能    |     88 |     98 |
| 曹孟德    |     82 |     84 |
| 刘玄德    |     55 |     85 |
| 孙权      |     70 |     73 |
| 宋公明    |     75 |     65 |
+-----------+--------+--------+
7 rows in set (0.00 sec)

-- 筛选后
mysql> select name,chinese as '语文',math as '数学' from exam_result where chinese > math;
+-----------+--------+--------+
| name      | 语文   | 数学   |
+-----------+--------+--------+
| 孙悟空    |     87 |     78 |
| 宋公明    |     75 |     65 |
+-----------+--------+--------+
2 rows in set (0.00 sec)

-- 筛选前
mysql> select name, chinese+math+english '总分' from exam_result;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 唐三藏    |    221 |
| 孙悟空    |    242 |
| 猪悟能    |    276 |
| 曹孟德    |    233 |
| 刘玄德    |    185 |
| 孙权      |    221 |
| 宋公明    |    170 |
+-----------+--------+
7 rows in set (0.00 sec)

-- 筛选后
mysql> select name, chinese+math+english '总分' from exam_result where chinese+math+english > 200;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 唐三藏    |    221 |
| 孙悟空    |    242 |
| 猪悟能    |    276 |
| 曹孟德    |    233 |
| 孙权      |    221 |
+-----------+--------+
5 rows in set (0.00 sec)

-- 筛选前
mysql> select name, chinese from exam_result where chinese>80;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
3 rows in set (0.00 sec)

-- 通过and和not配合达到筛选目的
mysql> select name, chinese from exam_result where chinese>80 and name not like '孙%';
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
2 rows in set (0.00 sec)

mysql> select name,chinese,math,english,chinese+math+english '总分' from exam_result where name like '猪%' and 				
						chinese+math+english>200 and chineese<math and english>80;
+-----------+---------+------+---------+--------+
| name      | chinese | math | english | 总分   |
+-----------+---------+------+---------+--------+
| 猪悟能    |      88 |   98 |      90 |    276 |
+-----------+---------+------+---------+--------+
1 row in set (0.00 sec)

-- 查询 students 表
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10010 | 唐大师    | NULL   |
| 101 | 10001 | 孙悟空    | 11111  |
| 103 | 20002 | 孙仲谋    | NULL   |
| 104 | 20001 | 曹阿瞒    | NULL   |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

-- 查询 qq 号已知的同学姓名
select name, qq from students where qq is not NULL;
+-----------+-------+
| name      | qq 	|
+-----------+-------+
| 孙悟空 	  | 11111 |
+-----------+-------+
1 row in set (0.00 sec)

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

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

select 列名 from 表名 order by 列名 [asc|desc];

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

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

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

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

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

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

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)

-- order by子句要放在where条件的后面！
mysql> select name,math from exam_result order by math desc where name like '孙%' or name like '曹%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where name like '孙%' or name like '曹%'' at line 1

# 从 0 开始，筛选 n 条结果
select 列名 from 表名 [where ...] [order by ...] limit n;

# 从 s 开始，筛选 n 条结果
select 列名 from 表名 [where ...] [order by ...] limit s, n;

# 从 s 开始，筛选 n 条结果，比第二种用法更明确，更建议使用!
select 列名 from 表名 [where ...] [order by ...] limit n offset s;

mysql> select id,name from exam_result limit 3 offset 0;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 唐三藏    |
|  2 | 孙悟空    |
|  3 | 猪悟能    |
+----+-----------+
3 rows in set (0.00 sec)

mysql> select id,name from exam_result limit 3 offset 3;
+----+-----------+
| id | name      |
+----+-----------+
|  4 | 曹孟德    |
|  5 | 刘玄德    |
|  6 | 孙权      |
+----+-----------+
3 rows in set (0.00 sec)

mysql> select id,name from exam_result limit 3 offset 6;
+----+-----------+
| id | name      |
+----+-----------+
|  7 | 宋公明    |
+----+-----------+
1 row in set (0.00 sec)

update 表名 set column1=value1 [, column2=value2, ...] [where 条件] [order by ...] [limit ...];

-- 查看原数据
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)

-- 一次更新多个列

-- 查看原数据
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)

-- 更新值为原值基础上变更

-- 查看原数据
mysql> select name,math+chinese+english total from exam_result order by total asc limit 3;
+-----------+-------+
| name      | total |
+-----------+-------+
| 宋公明    |   170 |
| 刘玄德    |   185 |
| 曹孟德    |   197 |
+-----------+-------+
3 rows in set (0.00 sec)

-- 数据更新，注意mysql不支持math += 30这种语法
mysql> update exam_result set math=math+30 order by math+chinese+english limit 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

-- 按总成绩排序后查询结果
mysql> select name,math+chinese+english total from exam_result order by total asc limit 3;
+-----------+-------+
| name      | total |
+-----------+-------+
| 宋公明    |   200 |
| 刘玄德    |   215 |
| 唐三藏    |   221 |
+-----------+-------+
3 rows in set (0.00 sec)

-- 没有 WHERE 子句，则更新全表

-- 查看原数据
mysql> select name,chinese from exam_result;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |      67 |
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      70 |
| 刘玄德    |      55 |
| 孙权      |      70 |
| 宋公明    |      75 |
+-----------+---------+
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 name,chinese from exam_result;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |     134 |
| 孙悟空    |     174 |
| 猪悟能    |     176 |
| 曹孟德    |     140 |
| 刘玄德    |     110 |
| 孙权      |     140 |
| 宋公明    |     150 |
+-----------+---------+
7 rows in set (0.00 sec)

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

-- 查看原数据
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)

-- 准备测试表
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('liren');
Query OK, 1 row affected (0.01 sec)

-- 查看数据
mysql> select * from for_delete;
+----+-------+
| id | name  |
+----+-------+
|  4 | liren |
+----+-------+
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)

truncate table 表名;

-- 准备测试表
create table for_truncate (
    id int primary key auto_increment,
    name varchar(20)
    );
Query OK, 0 rows affected (0.02 sec)

-- 插入测试数据
insert into for_delete (name) values ('A'), ('B'), ('C');

-- 查看测试数据
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)

-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
mysql> truncate table for_truncate;
Query OK, 0 rows affected (0.01 sec)

-- 查看删除结果
mysql> select * from for_truncate;
Empty set (0.00 sec)

-- 再插入一条数据，自增 id 在重新增长
mysql> insert into for_truncate (name) values('liren');
Query OK, 1 row affected (0.01 sec)

-- 查看数据
mysql> select * from for_truncate;
+----+-------+
| id | name  |
+----+-------+
|  1 | liren |
+----+-------+
1 row in set (0.00 sec)

-- 查看表结构，会有 AUTO_INCREMENT=2 项，这是因为我们重新插入了一条数据后的，说明auto_increment被重新设置了
mysql> 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.01 sec)

insert into 表名 [(列名 [, 列名 ...])] select ...

-- 创建原数据表
CREATE TABLE duplicate_table (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)

-- 插入测试数据
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.00 sec)

-- 将 duplicate_table 的去重数据插入到 no_duplicate_table
mysql> insert into no_duplicate_table select distinct * from duplicate_table;
Query OK, 3 rows affected (0.00 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.02 sec)

-- 查看最终结果
mysql> select * from duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from old_duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  100 | aaa  |
|  200 | bbb  |
|  200 | bbb  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
6 rows in set (0.00 sec)

-- 最好使用 * 做统计，不受 NULL 影响

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

-- NULL 不会计入结果

mysql> select count(math) from exam_result;
+-------------+
| count(math) |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)

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

-- 可以使用别名
mysql> select count(distinct math) 数学 from exam_result;
+--------+
| 数学   |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

mysql> select sum(math) 数学总分 from exam_result;
+--------------+
| 数学总分     |
+--------------+
|          581 |
+--------------+
1 row in set (0.00 sec)

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

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

-- 注意不能select无关的列字段
mysql> select name, max(english) 英语 from exam_result;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'testdb.exam_result.name'; this is incompatible with sql_mode=only_full_group_by

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

select 列名1, 列名2, ... 列名n from 表名 [where 条件] group by 列名1, 列名2, ... 列名n;

mysql> select avg(sal) 平均工资, max(sal) 最高工资 from emp;
+--------------+--------------+
| 平均工资     | 最高工资     |
+--------------+--------------+
|  2073.214286 |      5000.00 |
+--------------+--------------+
1 row in set (0.00 sec)

-- 分组前
mysql> select deptno 部门 from emp;
+--------+
| 部门   |
+--------+
|     20 |
|     30 |
|     30 |
|     20 |
|     30 |
|     30 |
|     10 |
|     20 |
|     10 |
|     30 |
|     20 |
|     30 |
|     20 |
|     10 |
+--------+
14 rows in set (0.00 sec)

-- 分组后
mysql> select deptno 部门 from emp group by deptno;
+--------+
| 部门   |
+--------+
|     10 |
|     20 |
|     30 |
+--------+
3 rows in set (0.00 sec)

mysql> select deptno 部门,avg(sal) 平均工资,max(sal) 最高工资 from emp group by deptno;
+--------+--------------+--------------+
| 部门   | 平均工资     | 最高工资     |
+--------+--------------+--------------+
|     10 |  2916.666667 |      5000.00 |
|     20 |  2175.000000 |      3000.00 |
|     30 |  1566.666667 |      2850.00 |
+--------+--------------+--------------+
3 rows in set (0.00 sec)

select 列名 from 表名 [where 条件] group by 列名 having 条件

select customer_id sum(order_count) total 
	from orders 
	group by customer_id 
	having total>1000;

mysql> select deptno 部门,job 岗位,avg(sal) 平均工资,max(sal) 最高工资 from emp group by deptno,job;
+--------+-----------+--------------+--------------+
| 部门   | 岗位      | 平均工资     | 最高工资     |
+--------+-----------+--------------+--------------+
|     10 | CLERK     |  1300.000000 |      1300.00 |
|     10 | MANAGER   |  2450.000000 |      2450.00 |
|     10 | PRESIDENT |  5000.000000 |      5000.00 |
|     20 | ANALYST   |  3000.000000 |      3000.00 |
|     20 | CLERK     |   950.000000 |      1100.00 |
|     20 | MANAGER   |  2975.000000 |      2975.00 |
|     30 | CLERK     |   950.000000 |       950.00 |
|     30 | MANAGER   |  2850.000000 |      2850.00 |
|     30 | SALESMAN  |  1400.000000 |      1600.00 |
+--------+-----------+--------------+--------------+
9 rows in set (0.01 sec)

mysql> select deptno 部门,avg(sal) 平均工资 from emp group by deptno having 平均工资<2000;
+--------+--------------+
| 部门   | 平均工资     |
+--------+--------------+
|     30 |  1566.666667 |
+--------+--------------+
1 row in set (0.00 sec)

mysql> select deptno 部门,avg(sal) 平均工资 from emp where sal<2000 group by deptno;
+--------+--------------+
| 部门   | 平均工资     |
+--------+--------------+
|     10 |  1300.000000 |
|     20 |   950.000000 |
|     30 |  1310.000000 |
+--------+--------------+
3 rows in set (0.00 sec)