表的增删改查

先创建一张student表

Database changed
mysql> create table student(
    -> id int unsigned primary key auto_increment comment '主键',
    -> sn int not null unique comment '学号',
    -> name varchar(20) not null,
    -> qq varchar(16)
    -> )engine=InnoDB default charset=utf8;
mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| sn    | int(11)          | NO   | UNI | NULL    |                |
| name  | varchar(20)      | NO   |     | NULL    |                |
| qq    | varchar(16)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

一、增（Create）

语法（括号内的都可以省略）：

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

1.单行插入

insert student(sn,name,qq) values (123,'猪八戒','123456');
Query OK, 1 row affected (0.01 sec)

mysql> select *from student;
+----+-----+-----------+--------+
| id | sn  | name      | qq     |
+----+-----+-----------+--------+
|  1 | 123 | 猪八戒    | 123456 |
+----+-----+-----------+--------+

1
2
3
4
5
6
7
8
9
10

2.多行插入

mysql> insert student(sn,name,qq) values (136,'孙悟空','123987'),(145,'沙僧','')598764mysq','123987'),(145,'沙僧','256489');
mysql> select *from student;
+----+-----+-----------+--------+
| id | sn  | name      | qq     |
+----+-----+-----------+--------+
|  1 | 123 | 猪八戒    | 123456 |
|  2 | 136 | 孙悟空    | 123987 |
|  3 | 145 | 沙僧      | 256489 |
+----+-----+-----------+--------+

1
2
3
4
5
6
7
8
9
10

3.指定列插入

mysql> insert student(sn,name) values (142,'唐三藏');
Query OK, 1 row affected (0.01 sec)

mysql> select *from student;
+----+-----+-----------+--------+
| id | sn  | name      | qq     |
+----+-----+-----------+--------+
|  1 | 123 | 猪八戒    | 123456 |
|  2 | 136 | 孙悟空    | 123987 |
|  3 | 145 | 沙僧      | 256489 |
|  4 | 142 | 唐三藏    | NULL   |
+----+-----+-----------+--------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

4.插入否则更新

表中已经存在的，更新信息，不存在则插入
（1）已经存在

mysql> select *from student;
+----+-----+-----------+--------+
| id | sn  | name      | qq     |
+----+-----+-----------+--------+
|  1 | 123 | 猪八戒    | 123456 |
|  2 | 136 | 孙悟空    | 123987 |
|  3 | 145 | 沙僧      | 256489 |
|  4 | 142 | 唐三藏    | NULL   |
+----+-----+-----------+--------+
4 rows in set (0.00 sec)

mysql> insert into student values (1,123,'猪悟能','2468135');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into student values (1,123,'猪悟能','2468135') ON duplicate key update name='猪悟能',qq='2468135';
Query OK, 2 rows affected (0.00 sec)

mysql> select *from student;
+----+-----+-----------+---------+
| id | sn  | name      | qq      |
+----+-----+-----------+---------+
|  1 | 123 | 猪悟能    | 2468135 |
|  2 | 136 | 孙悟空    | 123987  |
|  3 | 145 | 沙僧      | 256489  |
|  4 | 142 | 唐三藏    | NULL    |
+----+-----+-----------+---------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

（2）不存在

mysql> select *from student;
+----+-----+-----------+---------+
| id | sn  | name      | qq      |
+----+-----+-----------+---------+
|  1 | 123 | 猪悟能    | 2468135 |
|  2 | 136 | 孙悟空    | 123987  |
|  3 | 145 | 沙僧      | 256489  |
|  4 | 142 | 唐三藏    | NULL    |
+----+-----+-----------+---------+
4 rows in set (0.00 sec)

mysql> insert into student values (10,150,'猪悟能1','3578192') ON duplicate key update namme='猪悟能1',qq='3578192';
Query OK, 1 row affected (0.01 sec)

mysql> select *from student;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  1 | 123 | 猪悟能     | 2468135 |
|  2 | 136 | 孙悟空     | 123987  |
|  3 | 145 | 沙僧       | 256489  |
|  4 | 142 | 唐三藏     | NULL    |
| 10 | 150 | 猪悟能1    | 3578192 |
+----+-----+------------+---------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

注意：
– 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
– 1 row affected: 表中没有冲突数据，数据被插入
– 2 row affected: 表中有冲突数据，并且数据已经被更新

通过 MySQL 函数获取受到影响的数据行数

SELECT ROW_COUNT();
1

5.替换

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

mysql> select *from student;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  1 | 123 | 猪悟能     | 2468135 |
|  2 | 136 | 孙悟空     | 123987  |
|  3 | 145 | 沙僧       | 256489  |
|  4 | 142 | 唐三藏     | NULL    |
| 10 | 150 | 猪悟能1    | 3578192 |
+----+-----+------------+---------+
5 rows in set (0.00 sec)

mysql> replace into values (3,145,'蜘蛛精','980567');
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 'values (3,145,'蜘蛛精','980567')' at line 1
mysql> replace into student values (3,145,'蜘蛛精','980567');
Query OK, 2 rows affected (0.00 sec)

mysql> select *from student;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  1 | 123 | 猪悟能     | 2468135 |
|  2 | 136 | 孙悟空     | 123987  |
|  3 | 145 | 蜘蛛精     | 980567  |
|  4 | 142 | 唐三藏     | NULL    |
| 10 | 150 | 猪悟能1    | 3578192 |
+----+-----+------------+---------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

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

二、删（Delete）

1.删除数据

（1）删除孙悟空的考试成绩

mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  2 | 孙悟空    |     174 |   90 |      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)

mysql> delete from exam_result where name='孙悟空';
Query OK, 1 row affected (0.00 sec)

mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| 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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

（2）删除总分前三名的同学

mysql> select name,chinese+math+english total from exam_result order by total;
+-----------+-------+
| name      | total |
+-----------+-------+
| 刘玄德    |   270 |
| 宋公明    |   275 |
| 唐三藏    |   288 |
| 孙权      |   291 |
| 曹孟德    |   297 |
| 猪悟能    |   364 |
+-----------+-------+
6 rows in set (0.00 sec)

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

mysql> select name,chinese+math+english total from exam_result order by total;
+-----------+-------+
| name      | total |
+-----------+-------+
| 孙权      |   291 |
| 曹孟德    |   297 |
| 猪悟能    |   364 |
+-----------+-------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

2.清空数据

（1）删表，继续插入数据仍会继续增长（自增的累加值不会清空）

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

mysql> select *from for_delete;
Empty set (0.00 sec)
mysql> insert into for_delete (name) values ('x'),('y'),('z');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | x    |
|  5 | y    |
|  6 | z    |
+----+------+
3 rows in set (0.00 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

（2）截断表（truncate）：会重置 AUTO_INCREMENT 项

mysql> select *from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | x    |
|  5 | y    |
|  6 | z    |
+----+------+
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)

mysql> truncate for_delete;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into for_delete (name) values ('x'),('y'),('z');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *from for_delete;
+----+------+
| id | name |
+----+------+
|  1 | x    |
|  2 | y    |
|  3 | z    |
+----+------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

三、查(Retrieve)

先创建表

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

mysql> -- 插入测试数据
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
mysql> desc exam_result;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)      | NO   |     | NULL    |                |
| chinese | float            | YES  |     | 0       |                |
| math    | float            | YES  |     | 0       |                |
| english | float            | YES  |     | 0       |                |
+---------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

1.select列

（1）全列查询

利用*进行全列查询，不建议，数据越多，传输的数据量越大

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

（2）限制列查询

mysql> select *from exam_result limit 3;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

（3）指定列查询

Ⅰ、指定列

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14

Ⅱ、自定义的列

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14

求总成绩

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14

（4）为查询结果指定别名（as可省略）

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

2.where

(1)运算符

运算符	说明
<，> ,>=, <=	大于，大于等于，小于，小于等于
=	等于，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）查询实例

a.范围between and

mysql> select name,math from exam_result where math between 80 and 90;
+-----------+------+
| name      | math |
+-----------+------+
| 曹孟德    |   84 |
| 刘玄德    |   85 |
+-----------+------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

b.in/or的使用(数学成绩为58或59或98或99的名字及数学成绩)

mysql> select name,math from exam_result where math=58 or math=59 or math=98 or math=99;
+-----------+------+
| 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);
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

c.查询数学成绩为98的名字

mysql> select name,math from exam_result where math=98;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)

mysql> select name,math from exam_result where math<=>98;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

d.查询姓孙的

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

1
2
3
4
5
6
7
8
9

e.查询两个字的名字且姓孙

mysql> select name from exam_result where  name like '孙_';
+--------+
| name   |
+--------+
| 孙权   |
+--------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8

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

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)

1
2
3
4
5
6
7
8
9
10
11
12

g.查询语文成绩好于英语成绩再加30的同学

mysql> select name,chinese,english from exam_result where chinese > english+30;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 宋公明    |      75 |      30 |
+-----------+---------+---------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8

h.总分在200以下的同学

where后续的字句本身在select期间要进行作为条件筛选

mysql> select name,math+chinese+english as total from exam_result where math+chinese+engliish< 200;
+-----------+-------+
| name      | total |
+-----------+-------+
| 刘玄德    |   185 |
| 宋公明    |   170 |
+-----------+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

错误写法：

mysql> select name,math+chinese+english as total from exam_result where total < 200;
1

i.语文成绩>80且不姓孙的同学

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

mysql> select name,chinese from exam_result where chinese > 80 and name like '孙%';
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
+-----------+---------+
1 row in set (0.00 sec)

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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

j.孙某同学，否则总成绩>200且语文成绩<数学成绩且英语成绩>80

mysql> select name,english,math,chinese from exam_result where name like '孙_';
+--------+---------+------+---------+
| name   | english | math | chinese |
+--------+---------+------+---------+
| 孙权   |      78 |   73 |      70 |
+--------+---------+------+---------+
1 row in set (0.00 sec)

mysql> select name,english,math,chinese,english+math+chinese as total from exam_result wheere name like '孙_';
+--------+---------+------+---------+-------+
| name   | english | math | chinese | total |
+--------+---------+------+---------+-------+
| 孙权   |      78 |   73 |      70 |   221 |
+--------+---------+------+---------+-------+
1 row in set (0.00 sec)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

k.NULL表查询

mysql> select *from student;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  1 | 123 | 猪悟能     | 2468135 |
|  2 | 136 | 孙悟空     | 123987  |
|  3 | 145 | 蜘蛛精     | 980567  |
|  4 | 142 | 唐三藏     | NULL    |
| 10 | 150 | 猪悟能1    | 3578192 |
+----+-----+------------+---------+
5 rows in set (0.00 sec)

mysql> select name from student where qq<=>NULL;
+-----------+
| name      |
+-----------+
| 唐三藏    |
+-----------+
1 row in set (0.00 sec)

mysql> select name from student where not qq<=>NULL;
+------------+
| name       |
+------------+
| 猪悟能     |
| 孙悟空     |
| 蜘蛛精     |
| 猪悟能1    |
+------------+
4 rows in set (0.00 sec)

mysql> select name from student where not qq is not NULL;
+-----------+
| name      |
+-----------+
| 唐三藏    |
+-----------+
1 row in set (0.00 sec)

mysql> select name from student where  qq is not NULL;
+------------+
| name       |
+------------+
| 猪悟能     |
| 孙悟空     |
| 蜘蛛精     |
| 猪悟能1    |
+------------+
4 rows in set (0.00 sec)

mysql> select name from student where  qq is  NULL;
+-----------+
| name      |
+-----------+
| 唐三藏    |
+-----------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

3.结果排序

语法：

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

（1）同学及数学成绩，按数学成绩升序显示

mysql> select name,math from exam_result order by math;
+-----------+------+
| 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 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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

（2）同学及 qq 号，按 qq 号排序显示

mysql> select *from student where sn is not null;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  1 | 123 | 猪悟能     | 2468135 |
|  2 | 136 | 孙悟空     | 123987  |
|  3 | 145 | 蜘蛛精     | 980567  |
|  4 | 142 | 唐三藏     | NULL    |
| 10 | 150 | 猪悟能1    | 3578192 |
+----+-----+------------+---------+
5 rows in set (0.00 sec)

mysql> select *from student where sn is not null order by qq;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  4 | 142 | 唐三藏     | NULL    |
|  2 | 136 | 孙悟空     | 123987  |
|  1 | 123 | 猪悟能     | 2468135 |
| 10 | 150 | 猪悟能1    | 3578192 |
|  3 | 145 | 蜘蛛精     | 980567  |
+----+-----+------------+---------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

（4）查询同学及总分，并从高到低

要排序，是先把数据准备好，才可以order by，所以可以直接order by total

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

（5）查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

mysql> select name,math from exam_result where name like '孙%' or name like '曹%';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   78 |
| 曹孟德    |   84 |
| 孙权      |   73 |
+-----------+------+
3 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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

4.筛选分页结果

语法：

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

(1)limit 3,2:从第3条记录开始，选2条(0,1,2,3……)

mysql> select *from exam_result limit 3,2;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)


1
2
3
4
5
6
7
8
9
10

（2）limit 2 offset 3:从第3条记录开始，选2条(0,1,2,3……)

mysql> select *from exam_result limit 2 offset 3;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

（3）分页


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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

四.改（Update）

1.将孙悟空同学的数学成绩变更为 90 分（若不加条件，默认所有人数学成绩改为90）

mysql> update exam_result set math=90 where name='孙悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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

mysql> select name,math,chinese from exam_result where name='曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   84 |      82 |
+-----------+------+---------+
1 row in set (0.00 sec)

mysql> update exam_result set math=60,chinese=70 where name='曹孟德';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select name,math,chinese from exam_result where name='曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   60 |      70 |
+-----------+------+---------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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

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

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

mysql> select name from exam_result order by english+math+chinese limit 3;
+-----------+
| name      |
+-----------+
| 宋公明    |
| 刘玄德    |
| 曹孟德    |
+-----------+
3 rows in set (0.00 sec)

mysql> select name,math from exam_result where math+english+chinese order by math+english+chinese limit 3;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   65 |
| 刘玄德    |   85 |
| 曹孟德    |   60 |
+-----------+------+
3 rows in set (0.00 sec)

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

mysql> select name,math from exam_result where math+english+chinese order by math+english+chinese limit 3;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   95 |
| 刘玄德    |  115 |
| 唐三藏    |   98 |
+-----------+------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

4.将所有同学的语文成绩更新为原来的 2 倍（更新全表的慎用）

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.01 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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

五、插入查询结果

1.去重后的结果插入到新表
2.重命名旧表—表2，新表—旧表
3.表2中数据为旧表未去重的数据

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

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

mysql> create table if not exists no_dup_t like dup_t;
Query OK, 0 rows affected (0.04 sec)

mysql> desc no_dup_t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into no_dup_t select distinct *from dup_t;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

mysql> rename table dup_t to dup_t_bak,no_dup_t to dup_t;
Query OK, 0 rows affected (0.03 sec)

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

六、聚合函数

1.函数

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

2.案例

（1）统计多少条记录

mysql> select *from dup_t;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.01 sec)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

（2）统计qq号有多少

mysql> select count(qq) from student;
+-----------+
| count(qq) |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

mysql> select *from student;
+----+-----+------------+---------+
| id | sn  | name       | qq      |
+----+-----+------------+---------+
|  1 | 123 | 猪悟能     | 2468135 |
|  2 | 136 | 孙悟空     | 123987  |
|  3 | 145 | 蜘蛛精     | 980567  |
|  4 | 142 | 唐三藏     | NULL    |
| 10 | 150 | 猪悟能1    | 3578192 |
+----+-----+------------+---------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

（3）统计数学成绩的个数（去重的）

mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  6 | 孙权      |     140 |   73 |      78 |
|  8 | 孙悟空    |      87 |   98 |      95 |
+----+-----------+---------+------+---------+
4 rows in set (0.00 sec)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

（4）数学成绩求和

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

1
2
3
4
5
6
7
8

（5）求英语成绩的平均分

mysql> select avg(english) from exam_result;
+--------------+
| avg(english) |
+--------------+
|         82.5 |
+--------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

（6）找英语成绩>70分的最小值

mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  6 | 孙权      |     140 |   73 |      78 |
|  8 | 孙悟空    |      87 |   98 |      95 |
+----+-----------+---------+------+---------+
4 rows in set (0.00 sec)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3.group by:对指定列进行分组查询

凡是在select后面的列名称，如果后续要进行group by分组，只要是在select中出现的原表中的列名称，也必须在group by 中出现；
group by 是一个分组函数，要筛查的数据列，要考虑分组的时候，当前分组条件相同，分组依据是什么呢？
要同步
having 后面只能是聚合函数
where是最开始筛选数据，having是分完组后的结尾工作，再筛选（对分组结果进行过滤）

相关阅读:
指纹采集技术
 uint 与 int 相加，事与愿违？
惊了惊了，三分钟学会七个Python小技巧（建议收藏）
【WebRTC】【Unity】局域网UDP通信为何不通
 linux常用小知识点记录
 01 邂逅typescript,环境搭建
 配置管理（总结）
如何创建rpm包
 音频处理库性能对比：计算mel频谱的速度哪个更快？
《可信计算技术最佳实践白皮书》发布，龙蜥助力可信计算技术应用推广（可下载）
原文地址：https://blog.csdn.net/weixin_41446512/article/details/125121293