【MySQL】表的基本操作

表的增删改查
- Insert
- - 单行数据 + 指定列插入 + 全列插入
  - 多行数据 + 指定列插入 + 全列插入
  - 插入否则更新（duplicate key)
  - 替换（replace）
- select
- - 全队列查询
  - 指定列查询
  - 查询字段可以是表达式
  - 为查询结果
  - 结果去重
where条件
排序（order by）
筛选分页结果
Updata
Delete
- - 截断表
  - 插入查询结果
- 聚合函数
- group by分组字句的使用
- - having

表的增删改查

CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

Insert

语法：

insert [into] table_name [(column [, column] ...)] values (value_list) [, (value_list)] ...value_list: value, [, value] ...

创建一个学生表：

mysql> create table student(-> id int unsigned primary key auto_increment,-> name varchar(20) not null,-> mail varchar(20) unqiue-> );mysql> insert into student (id,name,mail) values(1,'小乔','123');
Query OK, 1 row affected (0.01 sec)

单行数据 + 指定列插入 + 全列插入

在上面的insert中的中括号里的是可以进省略的。

指定列插入

mysql> insert into student (id,name,mail) values(1,'小乔','123');
Query OK, 1 row affected (0.01 sec)

全列插入

当省略指定插入的列的时候，默认就是全部字段都要进行插入，这个就叫做全列插入

mysql> insert into student values(4,'周瑜','654');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+--------+------+
| id | name   | mail |
+----+--------+------+
|  1 | 小乔   | 123  |
|  2 | 大桥   | 321  |
|  4 | 周瑜   | 654  |
+----+--------+------+
3 rows in set (0.00 sec)

多行数据 + 指定列插入 + 全列插入

当进行多行插入时，只需要使用逗号隔开即可。

指定列插入

mysql> insert into student (name,mail) values('赵云','1234'),('张飞','45623'),('马超','59632');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

全列插入

mysql> insert into student values(10,'关羽','12394'),(11,'刘备','458623'),(12,'刘邦','519632');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+--------+--------+
| id | name   | mail   |
+----+--------+--------+
|  1 | 小乔   | 123    |
|  2 | 大桥   | 321    |
|  4 | 周瑜   | 654    |
|  5 | 赵云   | 1234   |
|  6 | 张飞   | 45623  |
|  7 | 马超   | 59632  |
| 10 | 关羽   | 12394  |
| 11 | 刘备   | 458623 |
| 12 | 刘邦   | 519632 |
+----+--------+--------+
9 rows in set (0.00 sec)

插入否则更新（duplicate key)

有时候我们插入数据时候，可能受到了主键或者唯一键的约束，但是我们就是想插入这个新的值。所以就可以使用duplicate，如果插入的数据不冲突直接插入，如果插入的数据冲突冲突了就进行更新。

语法：

insert... on duplicate key dpdate column = value [, column = value] ...

示例：

mysql> insert into student values (2,'大乔','321') on duplicate key update name='大乔',mail=96532;
Query OK, 2 rows affected (0.00 sec)mysql> select * from student;
+----+--------+--------+
| id | name   | mail   |
+----+--------+--------+
|  1 | 小乔   | 123    |
|  2 | 大乔   | 96532  |
|  4 | 周瑜   | 654    |
|  5 | 赵云   | 1234   |
|  6 | 张飞   | 45623  |
|  7 | 马超   | 59632  |
| 10 | 关羽   | 12394  |
| 11 | 刘备   | 458623 |
| 12 | 刘邦   | 519632 |
+----+--------+--------+
9 rows in set (0.00 sec)

mysql> insert into student values (2,'大乔','321') on duplicate key update name='大乔',mail=96532;
Query OK, 0 rows affected (0.00 sec)mysql> insert into student values (13,'吕布','22322') on duplicate key update name='大乔',mail=96532;
Query OK, 1 row affected (0.01 sec)mysql> insert into student values (2,'大乔','321') on duplicate key update name='大乔',mail=96532;
Query OK, 2 rows affected (0.00 sec)-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，并且数据已经被更新

替换（replace）

上面的duplicate的只要有冲突了，就直接在当前的所属插入的地方直接进行更新，但是替换就不一样了。替换是只要遇到冲突了，就是直接把冲突的数据删除，然后在插入新的数据，如果没有冲突了效果和inset是一样的。

语法：

replace的语法和insert的语法是一样的，只是把insert换成了replacereplace [into] table_name [(column [, column] ...)] values (value_list) [, (value_list)] ...

示例：

mysql> select * from student;
+----+--------+--------+
| id | name   | mail   |
+----+--------+--------+
|  1 | 小乔   | 123    |
|  2 | 大乔   | 96532  |
|  4 | 周瑜   | 654    |
|  5 | 赵云   | 1234   |
|  6 | 张飞   | 45623  |
|  7 | 马超   | 59632  |
| 10 | 关羽   | 12394  |
| 11 | 刘备   | 458623 |
| 12 | 刘邦   | 519632 |
| 13 | 吕布   | 22322  |
+----+--------+--------+
10 rows in set (0.00 sec)mysql> replace into student values (14,'吕布',22322);
Query OK, 2 rows affected (0.01 sec)mysql> select * from student;
+----+--------+--------+
| id | name   | mail   |
+----+--------+--------+
|  1 | 小乔   | 123    |
|  2 | 大乔   | 96532  |
|  4 | 周瑜   | 654    |
|  5 | 赵云   | 1234   |
|  6 | 张飞   | 45623  |
|  7 | 马超   | 59632  |
| 10 | 关羽   | 12394  |
| 11 | 刘备   | 458623 |
| 12 | 刘邦   | 519632 |
| 14 | 吕布   | 22322  |
+----+--------+--------+
10 rows in set (0.00 sec)

mysql> replace into student values (15,'貂蝉',22255);
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+----+--------+--------+
| id | name   | mail   |
+----+--------+--------+
|  1 | 小乔   | 123    |
|  2 | 大乔   | 96532  |
|  4 | 周瑜   | 654    |
|  5 | 赵云   | 1234   |
|  6 | 张飞   | 45623  |
|  7 | 马超   | 59632  |
| 10 | 关羽   | 12394  |
| 11 | 刘备   | 458623 |
| 12 | 刘邦   | 519632 |
| 14 | 吕布   | 22322  |
| 15 | 貂蝉   | 22255  |
+----+--------+--------+
11 rows in set (0.00 sec)

-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

select

语法：

SELECT [DISTINCT] {* | {column [, column] ...} [FROM table_name] [WHERE ...] [ORDER BY column [ASC | DESC], ...] LIMIT ...

案例：

----创建表结构
mysql> insert into exam (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;
+----+-----------+---------+------+---------+
| 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)

全队列查询

语法：

select * from 表名
select 列举全部 from 表名

通常情况下不建议使用 * 进行全队列查询
因为查询的列越多，就意味着要传输的数据量越大，可能会影响到索引的使用。


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

查询字段可以是表达式

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

mysql> select name,math,math+chinese from exam;
+-----------+------+--------------+
| name      | math | math+chinese |
+-----------+------+--------------+
| 唐三藏    |   98 |          165 |
| 孙悟空    |   78 |          165 |
| 猪悟能    |   98 |          186 |
| 曹孟德    |   84 |          166 |
| 刘玄德    |   85 |          140 |
| 孙权      |   73 |          143 |
| 宋公明    |   65 |          140 |
+-----------+------+--------------+
7 rows in set (0.01 sec)

为查询结果

语法：

select column [as] alias_name [...] from table_name;

# as 可以省略
mysql> select name,math,math+chinese+english as total from exam;
+-----------+------+-------+
| name      | math | total |
+-----------+------+-------+
| 唐三藏    |   98 |   221 |
| 孙悟空    |   78 |   242 |
| 猪悟能    |   98 |   276 |
| 曹孟德    |   84 |   233 |
| 刘玄德    |   85 |   185 |
| 孙权      |   73 |   221 |
| 宋公明    |   65 |   170 |
+-----------+------+-------+
7 rows in set (0.00 sec)

结果去重

语法：

select distinct ……

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

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)

查找语文不及格了人

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

查看语文成绩在【60，90】的人

使用andmysql> select name,chinese from exam where chinese>=60 and chinese<=90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |      67 |
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
| 孙权      |      70 |
| 宋公明    |      75 |
+-----------+---------+
6 rows in set (0.00 sec)

使用betweenmysql> select name,chinese from exam where chinese between 60 and 90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |      67 |
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
| 孙权      |      70 |
| 宋公明    |      75 |
+-----------+---------+
6 rows in set (0.01 sec)

查看数学成绩是 65 或者 73或者 98 或者 99 分的同学及数学成绩

使用or进行条件连接mysql> select name,math from exam where math=65 or math=73 or math=98 or math=99;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
| 孙权      |   73 |
| 宋公明    |   65 |
+-----------+------+
4 rows in set (0.01 sec)

使用 in 条件mysql> select name,math from exam where math in(65,73,98,99);
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
| 孙权      |   73 |
| 宋公明    |   65 |
+-----------+------+
4 rows in set (0.00 sec)

模糊查询

查看所有性孙的

-- % 匹配任意多个（包括 0 个）任意字符 mysql> select name from exam where name like '孙%';
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙权      |
+-----------+
2 rows in set (0.00 sec)

查看所有性孙某的，两个姓名的

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

查看语文成绩比英语成绩好的

mysql> select name,chinese,english from exam where chinese>english;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 唐三藏    |      67 |      56 |
| 孙悟空    |      87 |      77 |
| 曹孟德    |      82 |      67 |
| 刘玄德    |      55 |      45 |
| 宋公明    |      75 |      30 |
+-----------+---------+---------+
5 rows in set (0.00 sec)

总分在200分以下的

-- where 条件中使用表达式 
-- 别名不能用在 where 条件中mysql> select name,math+chinese+english as total from exam where math+chinese+english<200;
+-----------+-------+
| name      | total |
+-----------+-------+
| 刘玄德    |   185 |
| 宋公明    |   170 |
+-----------+-------+
2 rows in set (0.00 sec)# a.这里看似是可以的，但是这里的指向顺序是先执行from，在执行where，随后在执行select的，所有这里一定不能写成total<200;
mysql> select name,math+chinese+english as total from exam where total<200;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'

文成绩 > 80 并且不姓孙的同学

mysql> select name,chinese from exam where chinese<80 and name not like '孙%';
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |      67 |
| 刘玄德    |      55 |
| 宋公明    |      75 |
+-----------+---------+
3 rows in set (0.00 sec)

孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

mysql> select name,chinese,math,english,chinese+math+english total from exam where name like '孙_' or(chinese+math+english>200 and chinese<math and english>8
80);
+-----------+---------+------+---------+-------+
| name      | chinese | math | english | total |
+-----------+---------+------+---------+-------+
| 猪悟能    |      88 |   98 |      90 |   276 |
| 孙权      |      70 |   73 |      78 |   221 |
+-----------+---------+------+---------+-------+
2 rows in set (0.00 sec)

排序（order by）

语法：

-- ASC 为升序（从小到大） 
-- DESC 为降序（从大到小） 
-- 默认为 ASC SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];
注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

根据order by进行对语文成绩进行升序和降序

-- 升序操作mysql> select name,chinese from exam order by chinese asc;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 刘玄德    |      55 |
| 唐三藏    |      67 |
| 孙权      |      70 |
| 宋公明    |      75 |
| 曹孟德    |      82 |
| 孙悟空    |      87 |
| 猪悟能    |      88 |
+-----------+---------+
7 rows in set (0.00 sec)

-- 降序操作mysql> select name,chinese from exam order by chinese desc;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 猪悟能    |      88 |
| 孙悟空    |      87 |
| 曹孟德    |      82 |
| 宋公明    |      75 |
| 孙权      |      70 |
| 唐三藏    |      67 |
| 刘玄德    |      55 |
+-----------+---------+
7 rows in set (0.00 sec)

查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

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

这里可以起别名进行排序
原因是这里的执行顺序是先执行的select，后执行的order by
mysql> select name,chinese+math+english as total  from exam order by total desc;
+-----------+-------+
| name      | total |
+-----------+-------+
| 猪悟能    |   276 |
| 孙悟空    |   242 |
| 曹孟德    |   233 |
| 唐三藏    |   221 |
| 孙权      |   221 |
| 刘玄德    |   185 |
| 宋公明    |   170 |
+-----------+-------+
7 rows in set (0.00 sec)

筛选分页结果

语法：

-- 起始下标为 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;

mysql> select * from exam limit 3;
+----+-----------+---------+------+---------+
| 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 limit 2,3;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)mysql> select * from exam limit 2 offset 3;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)

Updata

对查询到的列值做更新。
语法：

upadat 表名称 set column = expr [, column = expr ...]  [WHERE ...] [ORDER BY ...] [LIMIT ...]

将孙悟空同学的数学成绩变更为 80 分

---原来数据
mysql> select name,math from exam where name='孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   78 |
+-----------+------+---进行数据更新
mysql> update exam 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 where name='孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   80 |
+-----------+------+
1 row in set (0.00 sec)

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

---原来数据
mysql> select name,math,chinese from exam where name='曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   84 |      82 |
+-----------+------+---------+
1 row in set (0.00 sec)---对数据进行更新
mysql> update exam 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 where name='曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   60 |      70 |
+-----------+------+---------+
1 row in set (0.00 sec)

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

mysql> select name,math,math+chinese+english total from exam order by total asc limit 3;
+-----------+------+-------+
| name      | math | total |
+-----------+------+-------+
| 宋公明    |   65 |   170 |
| 刘玄德    |   85 |   185 |
| 曹孟德    |   60 |   197 |
+-----------+------+-------+
3 rows in set (0.00 sec)# a.注意mysql中不支持maht += 30这种语法，只能是math = math + 30；
mysql> update exam set math=math+30 order by chinese+math+english asc limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0mysql> select name,math,math+chinese+english total from exam order by total asc limit 3;
+-----------+------+-------+
| name      | math | total |
+-----------+------+-------+
| 宋公明    |   95 |   200 |
| 刘玄德    |  115 |   215 |
| 唐三藏    |   98 |   221 |
+-----------+------+-------+
3 rows in set (0.00 sec)

将所有同学的语文成绩更新为原来的 2 倍

mysql> select name,chinese from exam;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |      67 |
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      70 |
| 刘玄德    |      55 |
| 孙权      |      70 |
| 宋公明    |      75 |
+-----------+---------+
7 rows in set (0.00 sec)mysql> update exam set chinese=chinese*2;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0mysql> select name,chinese from exam;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |     134 |
| 孙悟空    |     174 |
| 猪悟能    |     176 |
| 曹孟德    |     140 |
| 刘玄德    |     110 |
| 孙权      |     140 |
| 宋公明    |     150 |
+-----------+---------+
7 rows in set (0.00 sec)

Delete

语法：

delete from 表名称 [WHERE ...] [ORDER BY ...] [LIMIT ...]

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

mysql> select * from exam where name='孙悟空';
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  2 | 孙悟空    |     174 |   80 |      77 |
+----+-----------+---------+------+---------+
1 row in set (0.01 sec)mysql> delete from exam where name='孙悟空';
Query OK, 1 row affected (0.00 sec)mysql> select * from exam;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  5 | 刘玄德    |     110 |  115 |      45 |
|  6 | 孙权      |     140 |   73 |      78 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+
6 rows in set (0.00 sec)

删除考试成绩在最后一名的学生

mysql> select name,math,chinese,english,math+chinese+english total from exam order by total asc;
+-----------+------+---------+---------+-------+
| name      | math | chinese | english | total |
+-----------+------+---------+---------+-------+
| 刘玄德    |  115 |     110 |      45 |   270 |
| 宋公明    |   95 |     150 |      30 |   275 |
| 唐三藏    |   98 |     134 |      56 |   288 |
| 孙权      |   73 |     140 |      78 |   291 |
| 曹孟德    |   90 |     140 |      67 |   297 |
| 猪悟能    |   98 |     176 |      90 |   364 |
+-----------+------+---------+---------+-------+
6 rows in set (0.01 sec)mysql> delete from exam order by chinese+math+english asc limit 1;
Query OK, 1 row affected (0.01 sec)mysql> select name,math,chinese,english,math+chinese+english total from exam order by total asc;
+-----------+------+---------+---------+-------+
| name      | math | chinese | english | total |
+-----------+------+---------+---------+-------+
| 宋公明    |   95 |     150 |      30 |   275 |
| 唐三藏    |   98 |     134 |      56 |   288 |
| 孙权      |   73 |     140 |      78 |   291 |
| 曹孟德    |   90 |     140 |      67 |   297 |
| 猪悟能    |   98 |     176 |      90 |   364 |
+-----------+------+---------+---------+-------+
5 rows in set (0.00 sec)

删除整张表中的数据

语法：

delete from 表名称;

---测试表
mysql> create table for_delete(-> id int primary key auto_increment,-> name varchar(20)-> );
Query OK, 0 rows affected (0.04 sec)---向表中插入数据
mysql> insert into for_delete (name) values ('a'),('b'),('c');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)---查看表结构,并注意auto_increment的值
mysql> show create table for_delete\G
*************************** 1. row ***************************Table: for_delete
Create Table: CREATE TABLE `for_delete` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)---删除整张表的数据
mysql> delete from for_delete;
Query OK, 3 rows affected (0.01 sec)mysql> select * from for_delete;
Empty set (0.00 sec)---再次插入一条数据
mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | d    |
+----+------+
1 row in set (0.01 sec)

所以上面的测试我们可以发现，delete只是删除了表的内容，并不会对计数器进行清空。

截断表

这个也是用于来删除整张表的数据的。
注意：这个操作慎用

只能对整表操作，不能像 DELETE 一样针对部分数据操作；
实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚
会重置 AUTO_INCREMENT 项
语法：

truncate [TABLE] 表名称;

---创建新表，并插入数据
mysql> create table for_truncate( id int primary key auto_increment, name varchar(20) );
Query OK, 0 rows affected (0.02 sec)mysql> insert into for_truncate (name) values ('a'),('b'),('c');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)---查看表结构
mysql> show create table for_truncate\G
*************************** 1. row ***************************Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # 这里AUTO_INCREMENT=4
1 row in set (0.00 sec)---使用truncate删除数据，并再次查看表结构
mysql> truncate table for_truncate;
Query OK, 0 rows affected (0.03 sec)mysql> show create table for_truncate\G
*************************** 1. row ***************************Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci# 这里AUTO_INCREMENT=default了
1 row in set (0.00 sec)---再次添加数据
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
|  1 | d    |
+----+------+
1 row in set (0.00 sec)

插入查询结果

其实就是insert和select的一个结合操作
语法：

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'),   
(200, 'bbb'),   
(300, 'ccc');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0mysql> select * from duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  100 | aaa  |
|  200 | bbb  |
|  200 | bbb  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
6 rows in set (0.00 sec)# 创建一个和duplicate_table一样的新表
mysql> create table no_duplicate_table like duplicate_table;
Query OK, 0 rows affected (0.03 sec)mysql> desc duplicate_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> desc no_duplicate_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)# 使用插入查询结构
mysql> insert into no_duplicate_table select distinct * from duplicate_table;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> 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 new_duplicate_table, no_duplicate_table to duplicate_table;
Query OK, 0 rows affected (0.04 sec)mysql> select * from duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

统计班级共有多少同学

-- 使用 * 做统计，不受 NULL 影响
mysql> select count(*) from exam;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 se

统计本次考试数学成绩的个数

---可以进行重命名
mysql> select count(math) as count from exam;
+-------+
| count |
+-------+
|     5 |
+-------+
1 row in set (0.01 sec)---去重操作
mysql> select math from exam;
+------+
| math |
+------+
|   98 |
|   98 |
|   90 |
|   73 |
|   95 |
+------+
5 rows in set (0.00 sec)mysql> select count(distinct math) from exam;
+----------------------+
| count(distinct math) |
+----------------------+
|                    4 |
+----------------------+
1 row in set (0.00 sec)

统计数学成绩的总分

mysql> select sum(math) from exam;
+-----------+
| sum(math) |
+-----------+
|       454 |
+-----------+
1 row in set (0.00 sec)

统计数学分数小于60的总分

--- 如果有返回结果，如果没有返回NULL
mysql> select sum(math) from exam where math<60;
+-----------+
| sum(math) |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

统计平均分

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

返回语文最高分和最低分

mysql> select max(chinese),min(chinese) from exam;
+--------------+--------------+
| max(chinese) | min(chinese) |
+--------------+--------------+
|          176 |          134 |
+--------------+--------------+
1 row in set (0.00 sec)

返回 > 70数学的最高分和最低分

mysql> select max(math),min(math) from exam where math > 70;
+-----------+-----------+
| max(math) | min(math) |
+-----------+-----------+
|        98 |        73 |
+-----------+-----------+
1 row in set (0.00 sec)# 这里要注意了，因为这里select 查找name是整张表的，而max和min只有一个，不匹配，所以要使用分组查询
mysql> select name,max(math),min(math) from exam where math > 70;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test_db.exam.name'; this is incompatible with sql_mode=only_full_group_by

group by分组字句的使用

语法：

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

案例：

准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）
EMP员工表
DEPT部门表
SALGRADE工资等级表

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_c    i;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);

如何显示每个部门的平均工资和最高工资

mysql> select max(sal) 最高,avg(sal) 平均 from emp group by deptno;
+---------+-------------+
| 最高    | 平均        |
+---------+-------------+
| 3000.00 | 2175.000000 |
| 2850.00 | 1566.666667 |
| 5000.00 | 2916.666667 |
+---------+-------------+
3 rows in set (0.01 sec)

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

mysql> select deptno,job, avg(sal) ,min(sal) from emp group by deptno,job order by deptno asc;
+--------+-----------+-------------+----------+
| deptno | job       | avg(sal)    | min(sal) |
+--------+-----------+-------------+----------+
|     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 |   800.00 |
|     20 | MANAGER   | 2975.000000 |  2975.00 |
|     30 | CLERK     |  950.000000 |   950.00 |
|     30 | MANAGER   | 2850.000000 |  2850.00 |
|     30 | SALESMAN  | 1400.000000 |  1250.00 |
+--------+-----------+-------------+----------+
9 rows in set (0.00 sec)

having

having是对聚合后的数据进行条件筛选。有点类似与where，但是group by不能是where搭配，group by是跟having一起搭配使用的。

having与where的区别

MySQL中的HAVING和WHERE子句的主要区别在于它们的作用时机、‌使用位置、‌以及可以使用的表达式类型。‌

作用时机和位置：‌

WHERE子句在数据聚合之前应用，‌用于过滤查询结果中的行。‌它在FROM子句之后、‌GROUP BY和HAVING子句之前执
行。‌WHERE子句中的条件表达式可以是列名，‌不能包含聚合函数。‌
HAVING子句在数据聚合之后应用，‌用于过滤聚合后的结果集。‌它通常与GROUP BY子句一起使用，‌出现在GROUP BY子句之后。‌HAVING子句可以用聚合函数（‌如SUM、‌COUNT、‌AVG、‌MAX和MIN）‌，‌而WHERE子句不可以。‌

使用场景：‌

WHERE子句用于过滤行级别的条件，‌确保只有满足特定条件的行被包括在查询结果中。‌
HAVING子句用于过滤聚合后的组，‌例如，‌当你想要找出订单数量超过一定数量的产品时，‌你会使用HAVING子句来过滤这些组。‌

小结：‌

WHERE是查询返回结果之前进行过滤的，‌用于过滤行级别的数据。‌
HAVING是查询返回结果之后，‌对结果进行过滤的，‌用于过滤聚合后的数据。‌

综上所述，‌选择使用WHERE还是HAVING取决于你的查询需求：‌如果你需要在数据聚合之前过滤特定的行，‌应使用WHERE；‌如果你需要对聚合后的数据进行过滤，‌例如使用聚合函数并过滤出满足特定条件的组，‌则应使用HAVING子句

------来自百度百科

显示平均工资低于2000的部门和它的平均工资
统计各个部门的平均工资

mysql> select deptno,avg(sal) deptavg from emp group by deptno order by deptno asc;
+--------+-------------+
| deptno | deptavg     |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

having和group by配合使用，对group by结果进行过滤

mysql> select deptno, avg(sal) deptavg from emp group by deptno having deptavg<2000 order by deptno asc;
+--------+-------------+
| deptno | deptavg     |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)--having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。

显示平均工资低于2000的部门和它的平均工资除了SMITH

mysql> select deptno,job,avg(sal) myavg from emp where ename!='SMITH' group by deptno,job having myavg<2000 order by deptno asc;
+--------+----------+-------------+
| deptno | job      | myavg       |
+--------+----------+-------------+
|     10 | CLERK    | 1300.000000 |
|     20 | CLERK    | 1100.000000 |
|     30 | CLERK    |  950.000000 |
|     30 | SALESMAN | 1400.000000 |
+--------+----------+-------------+
4 rows in set (0.00 sec)