【MySQL基本查询】Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

表的增删查改

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

1.Create

语法：

INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...

value_list: value, [, value]

案例：

– 创建一张学生表

CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20) UNIQUE	
);

mysql> desc students;
+-------+------------------+------+-----+---------+----------------+
| 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(20)      | YES  | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

1.1 单行数据 + 全列插入

– 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致

–全列插入时，column可以省略

mysql> insert into students (id,sn,name,qq) values (10,123,'张三','12345@qq.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into students values (12,1234,'李四','123456@qq.com');
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+------+--------+---------------+
| id | sn   | name   | qq            |
+----+------+--------+---------------+
| 10 |  123 | 张三   | 12345@qq.com  |
| 12 | 1234 | 李四   | 123456@qq.com |
+----+------+--------+---------------+
2 rows in set (0.00 sec)

1.2 多行数据 + 指定列插入

– 插入两条记录，value_list 数量必须和指定列数量及顺序一致并且指定列不可以省略，但是不一定成功

–例如下面的name在创建表时就设置为了not null,因此在指定列插入时如果不填name及其对应的value值，则会报错。

–同理，在本表中，id,sn,name均不可为空

mysql> insert into students (id,sn,name) values (14,1235,'王五');
Query OK, 1 row affected (0.00 sec)

mysql> insert into students (id,sn) values (14,1235);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

mysql> select * from students;
+----+------+--------+---------------+
| id | sn   | name   | qq            |
+----+------+--------+---------------+
| 10 |  123 | 张三   | 12345@qq.com  |
| 12 | 1234 | 李四   | 123456@qq.com |
| 14 | 1235 | 王五   | NULL          |
+----+------+--------+---------------+
3 rows in set (0.00 sec)

我们也可以一次性多行插入:

mysql> insert into students (id,sn,name) values (15,12344,'111'),(16,123456,'2222');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from students;
+----+--------+--------+---------------+
| id | sn     | name   | qq            |
+----+--------+--------+---------------+
| 10 |    123 | 张三   | 12345@qq.com  |
| 12 |   1234 | 李四   | 123456@qq.com |
| 14 |   1235 | 王五   | NULL          |
| 15 |  12344 | 111    | NULL          |
| 16 | 123456 | 2222   | NULL          |
+----+--------+--------+---------------+
5 rows in set (0.00 sec)

1.3 插入否则更新

由于主键或者唯一键对应的值已经存在而导致插入失败 ,也就是主键或者唯一键冲突。

mysql> select * from students;
+----+--------+--------+---------------+
| id | sn     | name   | qq            |
+----+--------+--------+---------------+
| 10 |    123 | 张三   | 12345@qq.com  |
| 12 |   1234 | 李四   | 123456@qq.com |
| 14 |   1235 | 王五   | NULL          |
| 15 |  12344 | 111    | NULL          |
| 16 | 123456 | 2222   | NULL          |
+----+--------+--------+---------------+
5 rows in set (0.00 sec)

mysql> insert into students (id,sn,name) values (16,1234567,'1111');
ERROR 1062 (23000): Duplicate entry '16' for key 'PRIMARY' //主键(id)冲突
mysql> insert into students (id,sn,name) values (17,123456,'1111');
ERROR 1062 (23000): Duplicate entry '123456' for key 'sn'  //唯一键(sn)冲突
mysql>

如何避免冲突呢？以上表场景为例：

以‘王五’为例，我们在初始插入name为‘王五’这列数据时，并没有指定qq,而是设置为NULL

假设我们现在想给‘王五’加上qq的话，那么我们应该怎么写sql语句呢

mysql> insert into students (id,sn,name,qq) values (,14,1235,'王五','1111@qq.com');

我们本应该按上面这样写，为什么呢？
mysql> insert into students (id,qq) values (14,'1111@qq.com');
ERROR 1364 (HY000): Field 'sn' doesn't have a default value
如果我们单纯使用insert通过定位id来插入qq,因为id,sn,name本身不可以为空,所以实现不了，因此我们按理需要按上面这样写。

但是很显然，会造成主键或者唯一键冲突，如果我们想避免冲突，我们可以选择性的进行同步更新操作。

语法：

INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...

mysql> select * from students;
+----+--------+--------+---------------+
| id | sn     | name   | qq            |
+----+--------+--------+---------------+
| 10 |    123 | 张三   | 12345@qq.com  |
| 12 |   1234 | 李四   | 123456@qq.com |
| 14 |   1235 | 王五   | NULL          |
| 15 |  12344 | 111    | NULL          |
| 16 | 123456 | 2222   | NULL          |
+----+--------+--------+---------------+
5 rows in set (0.00 sec)

mysql> insert into students (id,sn,name,qq) values (14,1234,'王五','1122334455@qq.com') on duplicate key update qq='1122334455@qq.com', name='王五他爸';
Query OK, 2 rows affected (0.01 sec)

mysql> select * from students;
+----+--------+--------------+-------------------+
| id | sn     | name         | qq                |
+----+--------+--------------+-------------------+
| 10 |    123 | 张三         | 12345@qq.com      |
| 12 |   1234 | 李四         | 123456@qq.com     |
| 14 |   1235 | 王五他爸     | 1122334455@qq.com |
| 15 |  12344 | 111          | NULL              |
| 16 | 123456 | 2222         | NULL              |
+----+--------+--------------+-------------------+
5 rows in set (0.00 sec)

我们可以看到，这次虽然有冲突，但依旧完成qq的插入，并且可以对其他属性进行修改更新。

需要注意的是，我们在更新完成后，显示一条下面的语句:

Query OK, 2 rows affected (0.01 sec) -->表示有一行冲突数据。

什么意思呢？

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

– 通过 MySQL 函数获取受到影响的数据行数
SELECT ROW_COUNT();  

1.4 替换

– 主键或者唯一键没有冲突，则直接插入；

– 主键或者唯一键如果冲突，则删除后再插入

mysql> select * from students;
+----+--------+--------------+-------------------+
| id | sn     | name         | qq                |
+----+--------+--------------+-------------------+
| 10 |    123 | 张三         | 12345@qq.com      |
| 12 |   1234 | 李四         | 123456@qq.com     |
| 14 |   1235 | 王五他爸     | 1122334455@qq.com |
| 15 |  12344 | 111          | NULL              |
| 16 | 123456 | 2222         | NULL              |
+----+--------+--------------+-------------------+
5 rows in set (0.00 sec)

mysql> replace into students (sn,name,qq) values(0000,'客服1','0000@qq.com');
Query OK, 1 row affected (0.01 sec)

mysql> replace into students (sn,name,qq) values(123,'客服1','0000@qq.com');
Query OK, 3 rows affected (0.01 sec)

mysql> select * from students;
+----+--------+--------------+-------------------+
| id | sn     | name         | qq                |
+----+--------+--------------+-------------------+
| 12 |   1234 | 李四         | 123456@qq.com     |
| 14 |   1235 | 王五他爸     | 1122334455@qq.com |
| 15 |  12344 | 111          | NULL              |
| 16 | 123456 | 2222         | NULL              |
| 19 |    123 | 客服1        | 0000@qq.com       |
+----+--------+--------------+-------------------+
5 rows in set (0.00 sec)

我们看到，如果主键或唯一键冲突，则会删除整行数据，重新进行插入。

2.Retrieve

语法：

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

案例

我们创建一张学生成绩表：

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

– 插入测试数据

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);

2.1 SELECT 列

特别注意：select操作都对原始数据不做修改，只是对筛选结果显示做了修改。

2.1.1 全列查询

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

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)

2.1.2 指定列查询

– 指定列的顺序不需要按定义表的顺序来

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

2.1.3 查询字段为表达式

– 表达式不包含字段

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

可以看到，我们在查询的时候可以带表达式。

– 表达式包含一个字段

mysql> select id,name,math+10 from exam_result;
+----+-----------+---------+
| id | name      | math+10 |
+----+-----------+---------+
|  1 | 唐三藏    |     108 |
|  2 | 孙悟空    |      88 |
|  3 | 猪悟能    |     108 |
|  4 | 曹孟德    |      94 |
|  5 | 刘玄德    |      95 |
|  6 | 孙权      |      83 |
|  7 | 宋公明    |      75 |
+----+-----------+---------+
7 rows in set (0.00 sec)

我们也可以对查询的字段和表达式结合使用。

– 表达式包含多个字段

mysql> select id,name,math+chinese+english from exam_result;
+----+-----------+----------------------+
| id | name      | math+chinese+english |
+----+-----------+----------------------+
|  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 column [AS] alias_name [...] FROM table_name;  
//as可以被省略

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

对查询结果求和后，可以重新指定名字。

2.1.5对指定字段进行排序

既然我们得到了总分，那么我们可以对总分进行一个排序：

ascending order（asc）–升序

descending order(desc)–降序

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

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

如果不指定升序还是降序，默认是升序

2.1.5 结果去重

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

我们选取math列看到 98 分重复了，我们可以用 SELECT DISTINCT math FROM exam_result; 进行去重。

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

可以看到98分只剩下一个。

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)

注意，mysql中，'' 、 0、 NULL 这三者是有差别的，'' 和0表示没有值或为0值，但是依旧可以通过=条件进行判断查询，但是NULL则代表物理意义上的不存在(不占空间)，是无法通过=判断查询的，而是需要用<=>或者is null来查询。

mysql> insert into students (sn,name,qq) values (11111,'判空','');
Query OK, 1 row affected (0.00 sec)

mysql> insert into students (sn,name,qq) values (11112,'判空1','0');
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+--------+--------------+-------------------+
| id | sn     | name         | qq                |
+----+--------+--------------+-------------------+
| 12 |   1234 | 李四         | 123456@qq.com     |
| 14 |   1235 | 王五他爸     | 1122334455@qq.com |
| 15 |  12344 | 111          | NULL              |
| 16 | 123456 | 2222         | NULL              |
| 19 |    123 | 客服1        | 0000@qq.com       |
| 20 |  11111 | 判空         |                   |
| 21 |  11112 | 判空1        | 0                 |
+----+--------+--------------+-------------------+
7 rows in set (0.00 sec)

mysql> select id,name,qq from students where qq=NULL;
Empty set (0.00 sec)

mysql> select id,name,qq from students where qq<=>NULL;
+----+------+------+
| id | name | qq   |
+----+------+------+
| 15 | 111  | NULL |
| 16 | 2222 | NULL |
+----+------+------+
2 rows in set (0.00 sec)

mysql> select id,name,qq from students where qq is null;
+----+------+------+
| id | name | qq   |
+----+------+------+
| 15 | 111  | NULL |
| 16 | 2222 | NULL |
+----+------+------+
2 rows in set (0.00 sec)

下面,基于逻辑和比较运算符我们进行一些综合查询练习：

语文成绩好于英语成绩的同学

– WHERE 条件中比较运算符两侧都是字段

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 分以下的同学
– 注意别名不能用在 WHERE 条件中

mysql> SELECT name, chinese + math + english as 总分 FROM exam_result WHERE 总分 < 200;
ERROR 1054 (42S22): Unknown column '总分' in 'where clause'
mysql> SELECT name, chinese + math + english as 总分 FROM exam_result WHERE chinese+math+english < 200;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 刘玄德    |    185 |
| 宋公明    |    170 |
+-----------+--------+
2 rows in set (0.00 sec)

为什么别名不能用在 WHERE 条件中呢？

其实我们要明白，一条sql语句的执行逻辑是先按照where后面的条件筛选数据，然后按照要求进行计算数据。那么很显然，在where条件中，别名还没有被定义，识别不了，所以别名不能用在 WHERE 条件中。

顺便一提，对于下面这条涉及到排序的语句，为什么别名可以在后面使用呢？

mysql> select id,name,chinese+math+english 总分 from exam_result order by 总分 desc;

+----+------------- +--------+
| id | name      | 总分  |
+----+--------------+--------+
|  3 | 猪悟能    |    276 |
|  2 | 孙悟空    |    242 |
|  4 | 曹孟德    |    233 |
|  1 | 唐三藏    |    221 |
|  6 | 孙权        |    221 |
|  5 | 刘玄德    |    185 |
|  7 | 宋公明    |    170 |
+----+-----------+------------+
7 rows in set (0.03 sec)

其实很好理解，你要排序，那么你的数据肯定已经根据条件全部筛选完了，并且排序本身就是计算，所以排序之前就已经将数据进行筛选完毕并且别名已经进行了定义，因此这里可以使用别名。

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

– AND 与 NOT 的使用

mysql> select name,chinese from exam_result where chinese>80 and name not like '孙%';
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
2 rows in set (0.00 sec)

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

SELECT name, chinese, math, english, chinese + math + english 总分
FROM exam_result
WHERE name LIKE '孙_' OR (
chinese + math + english > 200 AND chinese < math AND english > 80
);

+-----------+---------+------+---------+--------+
| name      | chinese | math | english | 总分   |
+-----------+---------+------+---------+--------+
| 猪悟能    |      88 |   98 |      90 |    276 |
| 孙权      |      70 |   73 |      78 |    221 |
+-----------+---------+------+---------+--------+
2 rows in set (0.00 sec)

2.3 结果排序

语法：

SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

– ASC 为升序
– DESC 为降序
– 默认为 ASC

举例：（以下面两张表为例）

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

mysql> select * from students;;
+----+--------+--------------+-------------------+
| id | sn     | name         | qq                |
+----+--------+--------------+-------------------+
| 12 |   1234 | 李四         | 123456@qq.com     |
| 14 |   1235 | 王五他爸     | 1122334455@qq.com |
| 15 |  12344 | 111          | NULL              |
| 16 | 123456 | 2222         | NULL              |
| 19 |    123 | 客服1        | 0000@qq.com       |
| 20 |  11111 | 判空         |                   |
| 21 |  11112 | 判空1        | 0                 |
+----+--------+--------------+-------------------+
7 rows in set (0.00 sec)

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

SELECT name, math FROM exam_result ORDER BY math;

同学及 qq 号，按 qq 号排序显示

– NULL 视为比任何值都小，升序出现在最上面

–空字符串（‘ ’）第二小

SELECT name, qq FROM students ORDER BY qq;
+--------------+-------------------+
| name         | qq                |
+--------------+-------------------+
| 111          | NULL              |
| 2222         | NULL              |
| 判空         |                   |
| 判空1        | 0                 |
| 客服1        | 0000@qq.com       |
| 王五他爸     | 1122334455@qq.com |
| 李四         | 123456@qq.com     |
+--------------+-------------------+
7 rows in set (0.00 sec)

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

– 多字段排序，排序优先级随书写顺序

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

查询同学及总分，由高到低

SELECT name, chinese + english + math 总分 FROM exam_result
ORDER BY 总分 DESC;  
+-----------+--------------------------+
| name      | chinese + english + math |
+-----------+--------------------------+
| 猪悟能    |                      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)

2.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;

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死

案例：

按 id 进行分页，每页 3 条记录，分别显示第 1、2、3 页

-- 第 1 页
mysql> SELECT id, name, math, english, chinese FROM exam_result
    -> ORDER BY id LIMIT 3 OFFSET 0;  
+----+-----------+------+---------+---------+
| id | name      | math | english | chinese |
+----+-----------+------+---------+---------+
|  1 | 唐三藏    |   98 |      56 |      67 |
|  2 | 孙悟空    |   78 |      77 |      87 |
|  3 | 猪悟能    |   98 |      90 |      88 |
+----+-----------+------+---------+---------+
3 rows in set (0.00 sec)

– 第 2 页

mysql> SELECT id, name, math, english, chinese FROM exam_result
    -> ORDER BY id LIMIT 3 OFFSET 3; 
+----+-----------+------+---------+---------+
| id | name      | math | english | chinese |
+----+-----------+------+---------+---------+
|  4 | 曹孟德    |   84 |      67 |      82 |
|  5 | 刘玄德    |   85 |      45 |      55 |
|  6 | 孙权      |   73 |      78 |      70 |
+----+-----------+------+---------+---------+
3 rows in set (0.00 sec)

– 第 3 页，如果结果不足 3 个，不会有影响

mysql> SELECT id, name, math, english, chinese FROM exam_result
    -> ORDER BY id LIMIT 3 OFFSET 6;  
+----+-----------+------+---------+---------+
| id | name      | math | english | chinese |
+----+-----------+------+---------+---------+
|  7 | 宋公明    |   65 |      30 |      75 |
+----+-----------+------+---------+---------+
1 row in set (0.00 sec)

3.Update

语法：

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

对查询到的结果进行列值更新

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

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

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

更新值为原值基础上变更

1.先看看总成绩倒数前三的 3 位同学的数学成绩

mysql> SELECT name, math, chinese + math + english 总分 FROM exam_result
    -> ORDER BY 总分 LIMIT 3;
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 宋公明    |   65 |    170 |
| 刘玄德    |   85 |    185 |
| 曹孟德    |   60 |    197 |
+-----------+------+--------+
3 rows in set (0.00 sec)

2.将数学成绩加上 30 分

mysql> UPDATE exam_result SET math = math + 30
    -> ORDER BY chinese + math + english LIMIT 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT name, math, chinese + math + english 总分 FROM exam_result
    -> WHERE name IN ('宋公明', '刘玄德', '曹孟德');
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 曹孟德    |   90 |    227 |
| 刘玄德    |  115 |    215 |
| 宋公明    |   95 |    200 |
+-----------+------+--------+
3 rows in set (0.00 sec)

3.按总成绩排序后查询结果

mysql> SELECT name, math, chinese + math + english 总分 FROM exam_result
    -> ORDER BY 总分 LIMIT 3;  
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 宋公明    |   95 |    200 |
| 刘玄德    |  115 |    215 |
| 唐三藏    |   98 |    221 |
+-----------+------+--------+
3 rows in set (0.00 sec)

我们看到将总成绩倒数前三的 3 位同学中曹孟德变成了唐三藏。

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

注意：更新全表的语句慎用！

UPDATE exam_result SET chinese = chinese * 2 WHERE 1=1;

也可以不加 WHERE 子句

4.Delete

4.1 删除数据

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

举例：

删除整张表数据

1.先创建新表并插入数据

mysql> create table for_delete (
    -> id int unsigned primary key auto_increment,
    -> name varchar(16) not null
    -> )engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> desc for_delete;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(16)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');  
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)

2.删除整表数据

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 ('D');  
Query OK, 1 row affected (0.00 sec)

mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | D    |
+----+------+
1 row in set (0.00 sec)

我们发现自增 id 在原值上增长，并没有从1开始。我们查看一下创建表的表结构信息

mysql> show create table for_delete\G;
*************************** 1. row ***************************
       Table: for_delete
Create Table: CREATE TABLE `for_delete` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

我们看到AUTO_INCREMENT=5这一项，也就是说这一项记录着表的id自增情况。

那么如果我想在清空的同时将自增id重置，也就是说下次插入数据时，id重新从1开始自增，该如何做呢?

这就要提到另一种删除数据的方式：截断表

4.2 截断表

语法：

TRUNCATE [TABLE] table_name

注意：这个操作慎用

只能对整表操作，不能像 DELETE 一样针对部分数据操作；
会重置 AUTO_INCREMENT 项

接下来我们进行验证是否truncate操作会在清空数据的同时将自增id重置

mysql> INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C'); 
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | D    |
|  5 | A    |
|  6 | B    |
|  7 | C    |
+----+------+
4 rows in set (0.00 sec)

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

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

mysql> show create table for_delete\G;
*************************** 1. row ***************************
       Table: for_delete
Create Table: CREATE TABLE `for_delete` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

我们看到此时表结构信息种并没有AUTO_INCREMENT=n这一项了。

– 再插入一条数据，发现自增 id 在重新增长

mysql> INSERT INTO for_delete (name) VALUES ('E');
Query OK, 1 row affected (0.01 sec)

mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  1 | E    |
+----+------+
1 row in set (0.00 sec)

TRUNCATE和 DELETE还有别的地方的差别，这里就不赘述了。

5.插入查询结果

语法：

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

案例：删除表中的的重复复记录，重复的数据只能有一份

– 创建原数据表

mysql> create table if not exists duplicate_tb(
    -> id int,
    -> name varchar(16)
    -> );
Query OK, 0 rows affected (0.02 sec)

– 插入测试数据

mysql> insert into duplicate_tb values (10,'aa'),(10,'aa'),(20,'bb'),(20,'bb'),(20,'bb'),(30,'cc');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

思路：

– 创建一张空表 duplicate_table_bak，结构和 duplicate_table 一样

mysql> create table duplicate_tb_bak like duplicate_tb;
Query OK, 0 rows affected (0.04 sec)

– 将 duplicate_table 的去重数据插入到 duplicate_table_bak

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

mysql> select * from duplicate_tb_bak;
+------+------+
| id   | name |
+------+------+
|   10 | aa   |
|   20 | bb   |
|   30 | cc   |
+------+------+
3 rows in set (0.00 sec)

现在duplicate_tb_bak里存的是去重后的数据。

我们现在就不需要duplicate_tb了，将它进行备份：

mysql> RENAME TABLE duplicate_tb TO old_duplicate_table;
Query OK, 0 rows affected (0.01 sec)

– 通过重命名表，实现原子的去重操作

mysql> RENAME TABLE duplicate_tb_bak TO duplicate_tb;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from duplicate_tb;
+------+------+
| id   | name |
+------+------+
|   10 | aa   |
|   20 | bb   |
|   30 | cc   |
+------+------+
3 rows in set (0.00 sec)

到这里，我们实现了删除表中的的重复复记录操作。本操作的逻辑是将去重的数据先插入一个表结构和原表相同的新空表，然后将原表备份（重命名为别的表）并且将新表重命名为原表名字。

6.聚合函数

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

案例：

6.1 统计班级共有多少同学

– 使用 * 做统计，不受 NULL 影响

mysql> select * from students;
+----+--------+--------------+-------------------+
| id | sn     | name         | qq                |
+----+--------+--------------+-------------------+
| 12 |   1234 | 李四         | 123456@qq.com     |
| 14 |   1235 | 王五他爸     | 1122334455@qq.com |
| 15 |  12344 | 111          | NULL              |
| 16 | 123456 | 2222         | NULL              |
| 19 |    123 | 客服1        | 0000@qq.com       |
| 20 |  11111 | 判空         |                   |
| 21 |  11112 | 判空1        | 0                 |
+----+--------+--------------+-------------------+
7 rows in set (0.00 sec)

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

共7行数据

6.2 统计班级收集的 qq 号有多少

– NULL 不会计入结果，但是空串会计入结果

mysql> select * from students;
+----+--------+--------------+-------------------+
| id | sn     | name         | qq                |
+----+--------+--------------+-------------------+
| 12 |   1234 | 李四         | 123456@qq.com     |
| 14 |   1235 | 王五他爸     | 1122334455@qq.com |
| 15 |  12344 | 111          | NULL              |
| 16 | 123456 | 2222         | NULL              |
| 19 |    123 | 客服1        | 0000@qq.com       |
| 20 |  11111 | 判空         |                   |
| 21 |  11112 | 判空1        | 0                 |
+----+--------+--------------+-------------------+
7 rows in set (0.00 sec)

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

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

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)

– COUNT(math) 统计的是全部成绩

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

– COUNT(DISTINCT math) 统计的是去重成绩数量

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

6.4 统计数学成绩总分

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

6.4 统计平均总分

mysql> SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;
+--------------------+
| 平均总分           |
+--------------------+
| 229.14285714285714 |
+--------------------+
1 row in set (0.00 sec)

6.5 返回英语最高分

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

6.6 返回 > 70 分以上的数学最低分

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

the end

秒客网