【mysql】基本查询_mysql行列查询-优快云博客

本文链接：https://blog.youkuaiyun.com/Tangcan2/article/details/148640781

0.准备工作

//先创建一张学生表
 create table students(
    -> id int unsigned primary key auto_increment,
    -> sn int not null unique comment'学号',
    -> name varchar(20) not null,
    -> qq varchar(20)
    -> );
    
mysql> desc students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| sn    | int          | NO   | UNI | NULL    |                |
| name  | varchar(20)  | NO   |     | NULL    |                |
| qq    | varchar(20)  | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

1.基本的inset

1.1 单行数据+全列插入

//插入第一条数据
insert into students values (100, 10000, '唐三藏', NULL);
//插入第二条数据
insert into students values(101, 10001, '孙悟空', '11111');

mysql> select * from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏    | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
+-----+-------+-----------+-------+
2 rows in set (0.00 sec)

注意：value_list 数量必须和定义表的列的数量及顺序一致

1.2 多行数据+指定列插入

insert into students (id, sn, name) values
    -> (102, 20001, '曹孟德'),
    -> (103, 20002, '孙仲谋');

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.00 sec)

注意：value_list 数量必须和指定列数量及顺序一致

1.3 插入替换1

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

语法：
insert... on duplicate key update column = value [, column = value] ...

//主键冲突
mysql> insert into students (id,sn,name) values (100,10010,'唐大师');
ERROR 1062 (23000): Duplicate entry '100' for key 'students.PRIMARY'
//唯一键冲突
mysql> insert into students (id,sn,name) values (104,10001,'唐大师');
ERROR 1062 (23000): Duplicate entry '10001' for key 'students.sn'
//改进
insert into students (id,sn,name) values (100,10010,'唐大师')
    -> on duplicate key update sn=10010,name='唐大师';

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)

在这里插入图片描述

1.4 插入替换2

主键/唯一键没有冲突，则直接插入；
主键/唯一键如果有冲突，则删除后再插入；

REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');

mysql> select* from students;//该表中已经没有曹孟德了
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10010 | 唐大师    | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
| 103 | 20002 | 孙仲谋    | NULL  |
| 104 | 30003 | 刘备      | NULL  |
| 105 | 20001 | 曹阿瞒    | NULL  | 
+-----+-------+-----------+-------+
5 rows in set (0.00 sec)

2.where 子句

//创建表结构
mysql> 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 '英语成绩'
    -> );
Query OK, 0 rows affected (0.03 sec)
//插入测试数据
mysql> 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);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

2.1 select 列

2.1.1 全列查询（*）

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)

2.1.2 指定列查询

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)

2.1.3 表达式包含多个字段

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

2.1.4 为查询结果指定别名

select id,name, english+math+chinese 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)

2.1.5 结果去重（distinct）

//查询数学成绩
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.01 sec)

2.2 where 条件

比较运算符

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …)	如果是 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)

2.3 where 案例

//英语不及格的同学及英语成绩 ( < 60 )
mysql> select name ,english from exam_result where english<60;
+-----------+---------+
| name      | english |
+-----------+---------+
| 唐三藏    |      56 |
| 刘玄德    |      45 |
| 宋公明    |      30 |
+-----------+---------+
3 rows in set (0.00 sec)

//语文成绩在 [80, 90] 分的同学及语文成绩
mysql> select name,chinese from exam_result where chinese>=80 and chinese<=90;//使用and进行条件连接
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
3 rows in set (0.00 sec)

mysql> select name,chinese from exam_result where chinese between 80 and 90;//使用between A and B连接
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
3 rows in set (0.00 sec)

//数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
mysql> select name,math from exam_result where math=58 or math=59 or math=98 or math=99;//使用or进行条件连接
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)

mysql> select name,math from exam_result where math in(58,59,98,99);//使用in条件
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)

//姓孙的同学 及 孙某同学
mysql> select name from exam_result where name like '孙%';//% 匹配任意多个任意字符
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙权      |
+-----------+
2 rows in set (0.00 sec)

mysql> select name from exam_result where name like '孙_';//_ 严格匹配一个任意字符
+--------+
| name   |
+--------+
| 孙权   |
+--------+
1 row in set (0.00 sec)

mysql> select name from exam_result where name like '孙%' or '孙_';
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙权      |
+-----------+
2 rows in set, 1 warning (0.00 sec)

//语文成绩好于英语成绩的同学
mysql> select name,chinese,english from exam_result where chinese>english;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 唐三藏    |      67 |      56 |
| 孙悟空    |      87 |      77 |
| 曹孟德    |      82 |      67 |
| 刘玄德    |      55 |      45 |
| 宋公明    |      75 |      30 |
+-----------+---------+---------+
5 rows in set (0.00 sec)

//总分在 200 分以下的同学
mysql> select name,english+math+chinese as 总分 from exam_result where 总分 <200;
ERROR 1054 (42S22): Unknown column '总分' in 'where clause'
//执行顺序：
//1. from exam_result
//2.where 总分 <200
//3.select name,english+math+chinese as 总分 
//根据该执行顺序可知，执行2时，是不认识总分的
mysql> select name,english+math+chinese as 总分 from exam_result where english+math+chinese <200;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 刘玄德    |    185 |
| 宋公明    |    170 |
+-----------+--------+
2 rows in set (0.00 sec)

//语文成绩 > 80 并且不姓孙的同学
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)

//孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
mysql> select name,chinese,math,english,chinese+math+english as total from exam_result where ((english+math+chinese>200) and (chinese<math) and (english>80)) 
    -> or name like '孙_';
+-----------+---------+------+---------+-------+
| name      | chinese | math | english | total |
+-----------+---------+------+---------+-------+
| 猪悟能    |      88 |   98 |      90 |   276 |
| 孙权      |      70 |   73 |      78 |   221 |
+-----------+---------+------+---------+-------+
2 rows in set (0.00 sec)

//NULL的查询
mysql> select * from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10010 | 唐大师    | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
| 103 | 20002 | 孙仲谋    | NULL  |
| 104 | 30003 | 刘备      | NULL  |
| 105 | 20001 | 曹阿瞒    | NULL  |
+-----+-------+-----------+-------+
5 rows in set (0.00 sec)

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

3.结果排序

语法

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [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;//order by 默认是升序
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   65 |
| 孙权      |   73 |
| 孙悟空    |   78 |
| 曹孟德    |   84 |
| 刘玄德    |   85 |
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
7 rows in set (0.00 sec)

//同学及 qq 号，按 qq 号排序显示
mysql> select name,qq from students order by qq;//NULL 视为比任何值都小，升序出现在最上面
+-----------+-------+
| name      | qq    |
+-----------+-------+
| 唐大师    | NULL  |
| 孙仲谋    | NULL  |
| 刘备      | NULL  |
| 曹阿瞒    | NULL  |
| 孙悟空    | 11111 |
+-----------+-------+
5 rows in set (0.00 sec)

//查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
mysql> select name,chinese,math,english from exam_result order by math desc,english asc,chinese asc;
+-----------+---------+------+---------+
| 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> select name,english+math+chinese as total from exam_result order by total desc;
+-----------+-------+
| name      | total |
+-----------+-------+
| 猪悟能    |   276 |
| 孙悟空    |   242 |
| 曹孟德    |   233 |
| 唐三藏    |   221 |
| 孙权      |   221 |
| 刘玄德    |   185 |
| 宋公明    |   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)

4.筛选分页结果

语法

//下标从0开始
//从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

案例

//第一页
mysql> select * from exam_result 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.01 sec)
//第二页
mysql> select * from exam_result 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)
//第三页
mysql> select * from exam_result limit 3 offset 6;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)

5.update（慎用）

语法

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

案例

//将孙悟空同学的数学成绩变更为 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)

//将曹孟德同学的数学成绩变更为 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 位同学的数学成绩加上 30 分
mysql> select name,english+math+chinese as total from exam_result order by total limit 3;//查看原数据
+-----------+-------+
| name      | total |
+-----------+-------+
| 宋公明    |   170 |
| 刘玄德    |   185 |
| 曹孟德    |   197 |
+-----------+-------+
3 rows in set (0.00 sec)

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

mysql> select name,english+math+chinese as total from exam_result order by total limit 3;//查看更新后的结果
+-----------+-------+
| name      | total |
+-----------+-------+
| 宋公明    |   200 |
| 刘玄德    |   215 |
| 唐三藏    |   221 |
+-----------+-------+
3 rows in set (0.00 sec)

//将所有同学的语文成绩更新为原来的 2 倍
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)

6.delete（慎用）

语法：

DELETE FROM table_name [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)

//删除整张表数据
delete from exam_result;

7.插入查询结果

语法：

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

案例：

//创建一张测试表
mysql> create table duplicate_table(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)
//向表中插入数据
mysql> insert into duplicate_table values(100,'aaa'),(100,'aaa'),(200,'bbb'),(200,'bbb'),(300,'ccc'),(300,'ccc');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
//查看插入的数据
mysql> select * from duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  100 | aaa  |
|  200 | bbb  |
|  200 | bbb  |
|  300 | ccc  |
|  300 | ccc  |
+------+------+
6 rows in set (0.00 sec)
//去重
mysql> select distinct * from duplicate_table
    -> ;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)
//创建一张和duplicate_table表结构相同的表
mysql> create table no_duplicate_table like duplicate_table;
Query OK, 0 rows affected (0.02 sec)
//将去重后的数据插入表中
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> select * from no_duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)
//对标进行重命名
mysql> rename table duplicate_table to  old_duplicate_table;
Query OK, 0 rows affected (0.01 sec)
mysql> rename 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)

8.聚合统计

函数	说明
count	返回查询到的数据的数量
sum	返回查询到的数据的总和，不是数字没有意义
avg	返回铲鲟到的数据的平均值，不是数字没有意义
max	返回查询到的数据的最大值，不是数字没有意义
min	返回查询到的数据的最小值，不是数字没有意义

案例：

//统计班级共有多少同学
mysql> select count(*) from students;//使用*做统计，不受null值影响
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.02 sec)

//统计班级收集的 qq 号有多少
mysql> select count(qq) from students;//null不会计入结果
+-----------+
| count(qq) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

//统计本次考试的数学成绩分数个数
mysql> select count(math) from exam_result;//不去重
+-------------+
| count(math) |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)
mysql> select count(distinct math) from exam_result;//去重
+----------------------+
| count(distinct math) |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.00 sec)

//统计数学成绩总分
mysql> select sum(math) from exam_result;
+-----------+
| sum(math) |
+-----------+
|       569 |
+-----------+
1 row in set (0.00 sec)

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

//返回英语最高分
mysql> select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
|           90 |
+--------------+
1 row in set (0.01 sec)

//返回 > 70 分以上的数学最低分
mysql> select min(math) from exam_result where math>70;
+-----------+
| min(math) |
+-----------+
|        73 |
+-----------+
1 row in set (0.00 sec)

9.group by 子句

语法：

select column1, column2, .. from table group by column;

案例：

//员工表
mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

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

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

//显示平均工资低于2000的部门和它的平均工资
mysql> select deptno,avg(sal) from emp group by deptno;//每个部门的平均工资
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)