MySQL基本查询

最新推荐文章于 2024-08-23 17:37:15 发布

「已注销」

最新推荐文章于 2024-08-23 17:37:15 发布

阅读量112

点赞数 2

分类专栏： mysql 文章标签： mysql android 数据库 centos linux

本文链接：https://blog.csdn.net/2301_79852992/article/details/133345386

版权

mysql 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

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

一、Create(添加数据)

语法：

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

value_list: value, [, value] ...

我们先创建一张表：

mysql> create table students(
    -> id int unsigned primary key auto_increment,
    -> sn int unsigned unique key,
    -> name varchar(20) NOT NULL,
    -> qq varchar(32) unique key
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc students;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| sn    | int(10) unsigned | YES  | UNI | NULL    |                |
| name  | varchar(20)      | NO   |     | NULL    |                |
| qq    | varchar(32)      | YES  | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

1、插入

单行数据 + 指定列插入

mysql> insert into students (sn,name,qq) values (123,'张飞','4567890');
Query OK, 1 row affected (0.01 sec)

mysql> select * from students;
+----+------+--------+---------+
| id | sn   | name   | qq      |
+----+------+--------+---------+
|  1 |  123 | 张飞   | 4567890 |
+----+------+--------+---------+
1 row in set (0.00 sec)

单行数据 + 全列插入

mysql> insert into students values (10,124,'关羽','123456');
Query OK, 1 row affected (0.00 sec)

mysql> insert students values (11,125,'刘备','123490');
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+------+--------+---------+
| id | sn   | name   | qq      |
+----+------+--------+---------+
|  1 |  123 | 张飞   | 4567890 |
| 10 |  124 | 关羽   | 123456  |
| 11 |  125 | 刘备   | 123490  |
+----+------+--------+---------+
3 rows in set (0.00 sec)

注意，“into”可以被省略掉。

多行数据 + 全列插入

mysql> insert into students values (12,126,'曹操','1023456');
Query OK, 1 row affected (0.00 sec)

mysql> insert into students values (13,127,'许攸','10423456'),(14,128,'许褚','12333445'),(15,129,'诸葛亮','3213456');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from students;
+----+------+-----------+----------+
| id | sn   | name      | qq       |
+----+------+-----------+----------+
|  1 |  123 | 张飞      | 4567890  |
| 10 |  124 | 关羽      | 123456   |
| 11 |  125 | 刘备      | 123490   |
| 12 |  126 | 曹操      | 1023456  |
| 13 |  127 | 许攸      | 10423456 |
| 14 |  128 | 许褚      | 12333445 |
| 15 |  129 | 诸葛亮    | 3213456  |
+----+------+-----------+----------+
7 rows in set (0.00 sec)

多行数据 + 指定列插入

mysql> insert into students (sn,name,qq) values (130,'孙权','6543456'),(131,'吕布','342343242');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from students;
+----+------+-----------+-----------+
| id | sn   | name      | qq        |
+----+------+-----------+-----------+
|  1 |  123 | 张飞      | 4567890   |
| 10 |  124 | 关羽      | 123456    |
| 11 |  125 | 刘备      | 123490    |
| 12 |  126 | 曹操      | 1023456   |
| 13 |  127 | 许攸      | 10423456  |
| 14 |  128 | 许褚      | 12333445  |
| 15 |  129 | 诸葛亮    | 3213456   |
| 16 |  130 | 孙权      | 6543456   |
| 17 |  131 | 吕布      | 342343242 |
+----+------+-----------+-----------+
9 rows in set (0.00 sec)

2、插入否则更新

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

主键冲突

唯一键冲突

直接插入的话，会产生报错，我们想更新数据的话，可以用下面的操作：

INSERT ... ON DUPLICATE KEY UPDATE

column = value [, column = value] ...

举例：

mysql> insert into students values (13,132,'xuyou','111111') on duplicate key update sn=132, name='xuyou',qq='111111';
Query OK, 2 rows affected (0.00 sec)

该操作最开始先检查要插入的数据是否合法，合法将直接插入，如果不合法的话，才会用到后面的更新：

mysql> insert into students values (18,134,'貂蝉','11111122') on duplicate key update sn=134, name='貂蝉',qq='1133333';
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+------+-----------+-----------+
| id | sn   | name      | qq        |
+----+------+-----------+-----------+
|  1 |  123 | 张飞      | 4567890   |
| 10 |  124 | 关羽      | 123456    |
| 11 |  125 | 刘备      | 123490    |
| 12 |  126 | 曹操      | 1023456   |
| 13 |  132 | xuyou     | 111111    |
| 14 |  128 | 许褚      | 12333445  |
| 15 |  129 | 诸葛亮    | 3213456   |
| 16 |  130 | 孙权      | 6543456   |
| 17 |  131 | 吕布      | 342343242 |
| 18 |  134 | 貂蝉      | 11111122  |
+----+------+-----------+-----------+
10 rows in set (0.00 sec)

mysql> insert into students values (18,134,'貂蝉','11111122') on duplicate key update sn=134, name='貂蝉',qq='1133333';
Query OK, 2 rows affected (0.00 sec)

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

mysql> select * from students;
+----+------+-----------+-----------+
| id | sn   | name      | qq        |
+----+------+-----------+-----------+
|  1 |  123 | 张飞      | 4567890   |
| 10 |  124 | 关羽      | 123456    |
| 11 |  125 | 刘备      | 123490    |
| 12 |  126 | 曹操      | 1023456   |
| 13 |  132 | xuyou     | 111111    |
| 14 |  128 | 许褚      | 12333445  |
| 15 |  129 | 诸葛亮    | 3213456   |
| 16 |  130 | 孙权      | 6543456   |
| 17 |  131 | 吕布      | 342343242 |
| 18 |  134 | 貂蝉      | 1133333   |
+----+------+-----------+-----------+
10 rows in set (0.00 sec)

我们通过 MySQL 函数 select row_count() 获取受到影响的数据行数：

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

mysql> insert into students values (18,134,'貂蝉','11111122') on duplicate key update sn=134, name='貂蝉',qq='1133333';
Query OK, 0 rows affected (0.00 sec)

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

3、替换

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

举例：

mysql> select * from students;
+----+------+-----------+-----------+
| id | sn   | name      | qq        |
+----+------+-----------+-----------+
|  1 |  123 | 张飞      | 4567890   |
| 10 |  124 | 关羽      | 123456    |
| 11 |  125 | 刘备      | 123490    |
| 12 |  126 | 曹操      | 1023456   |
| 13 |  132 | xuyou     | 111111    |
| 14 |  128 | 许褚      | 12333445  |
| 15 |  129 | 诸葛亮    | 3213456   |
| 16 |  130 | 孙权      | 6543456   |
| 17 |  131 | 吕布      | 342343242 |
| 18 |  134 | 貂蝉      | 1133333   |
| 19 |  140 | 许攸      | 321456213 |
+----+------+-----------+-----------+
11 rows in set (0.00 sec)

mysql> replace into students (sn,name,qq) values (140,'xu','321456213');
Query OK, 2 rows affected (0.01 sec)

mysql> select * from students;
+----+------+-----------+-----------+
| id | sn   | name      | qq        |
+----+------+-----------+-----------+
|  1 |  123 | 张飞      | 4567890   |
| 10 |  124 | 关羽      | 123456    |
| 11 |  125 | 刘备      | 123490    |
| 12 |  126 | 曹操      | 1023456   |
| 13 |  132 | xuyou     | 111111    |
| 14 |  128 | 许褚      | 12333445  |
| 15 |  129 | 诸葛亮    | 3213456   |
| 16 |  130 | 孙权      | 6543456   |
| 17 |  131 | 吕布      | 342343242 |
| 18 |  134 | 貂蝉      | 1133333   |
| 20 |  140 | xu        | 321456213 |
+----+------+-----------+-----------+
11 rows in set (0.00 sec)

我们看到，使用replace后， 2 rows affected，表明数据有冲突并且已更新，这里的更新是指删除原数据后再重新插入。所以id号19变成了20。

二、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.02 sec)

插入数据：

mysql> INSERT INTO exam_result (name, chinese, math, english) VALUES
    -> ('唐三藏', 67, 98, 56),
    -> ('孙悟空', 87, 78, 77),
    -> ('猪悟能', 88, 98, 90),
    -> ('曹孟德', 82, 84, 67),
    -> ('刘玄德', 55, 85, 45),
    -> ('孙权', 70, 73, 78),
    -> ('宋公明', 75, 65, 30);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

1、select 列

全列查询：

-- 通常情况下不建议使用 * 进行全列查询
-- 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)

指定列查询：

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

查询字段为表达式：

-- 表达式不包含字段
mysql> select name,math,10 from exam_result;
+-----------+------+----+
| name      | math | 10 |
+-----------+------+----+
| 唐三藏    |   98 | 10 |
| 孙悟空    |   78 | 10 |
| 猪悟能    |   98 | 10 |
| 曹孟德    |   84 | 10 |
| 刘玄德    |   85 | 10 |
| 孙权      |   73 | 10 |
| 宋公明    |   65 | 10 |
+-----------+------+----+
7 rows in set (0.00 sec)

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

为查询结果指定别名：

语法：

SELECT column [AS] alias_name [...] FROM table_name;

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

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

以上代码所表示，as 关键字可以省略掉。

结果去重(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)

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

查询语文成绩在 [80, 90] 分的同学及语文成绩：

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

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

查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩：

mysql> select name,math from exam_result where math=58 or math=59 or math=60;
Empty set (0.00 sec)

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

查询姓孙的同学及孙某同学：

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

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

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

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

查询总分在 200 分以下的同学：

mysql> select name,chinese + math + english 总分 from exam_result where chinese + math + english < 200;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 刘玄德    |    185 |
| 宋公明    |    170 |
+-----------+--------+
2 rows in set (0.00 sec)

-- 别名不能用在 WHERE 条件中

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

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

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

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

还有很多查询种类，可以自己尝试一下。

3、结果排序

语法：

-- ASC 为升序（从小到大）

-- DESC 为降序（从大到小）

-- 默认为 ASC

SELECT ... FROM table_name [WHERE ...]

ORDER BY column [ASC|DESC], [...];

注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

举例：

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

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

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

mysql> select name,qq from students order by qq;
+-----------+-----------+
| name      | qq        |
+-----------+-----------+
| 曹操      | 1023456   |
| xuyou     | 111111    |
| 貂蝉      | 1133333   |
| 许褚      | 12333445  |
| 关羽      | 123456    |
| 刘备      | 123490    |
| 诸葛亮    | 3213456   |
| xu        | 321456213 |
| 吕布      | 342343242 |
| 张飞      | 4567890   |
| 孙权      | 6543456   |
+-----------+-----------+
11 rows in set (0.00 sec)

mysql> select name,qq from students order by qq desc;
+-----------+-----------+
| name      | qq        |
+-----------+-----------+
| 孙权      | 6543456   |
| 张飞      | 4567890   |
| 吕布      | 342343242 |
| xu        | 321456213 |
| 诸葛亮    | 3213456   |
| 刘备      | 123490    |
| 关羽      | 123456    |
| 许褚      | 12333445  |
| 貂蝉      | 1133333   |
| xuyou     | 111111    |
| 曹操      | 1023456   |
+-----------+-----------+
11 rows in set (0.00 sec)

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

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

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

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

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

mysql> select name,math from exam_result where name like '孙%' or name like'曹%' order by math desc;
+-----------+------+
| name      | math |
+-----------+------+
| 曹孟德    |   84 |
| 孙悟空    |   78 |
| 孙权      |   73 |
+-----------+------+
3 rows in set (0.00 sec)

4、筛选分页结果

-- 起始下标为 0

-- 从 s 开始，筛选 n 条结果

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;

-- 从 0 开始，筛选 n 条结果

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

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

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

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)

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

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)

mysql> select id,name,math,english,chinese from exam_result order by id limit 3,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)

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)

mysql> select id,name,math,english,chinese from exam_result order by id limit 6,3;
+----+-----------+------+---------+---------+
| id | name      | math | english | chinese |
+----+-----------+------+---------+---------+
|  7 | 宋公明    |   65 |      30 |      75 |
+----+-----------+------+---------+---------+
1 row in set (0.00 sec)

三、Update(更新数据)

语法：

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

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

举例：

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

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> update exam_result set math=60,chinese=70 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 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      70 |   60 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

将孙悟空同学的数学成绩变更为 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 位同学的数学成绩加上 30 分：

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)

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

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)

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

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

mysql> update exam_result set chinese=chinese*2;
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0

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

四、Delete(删除数据)

语法：

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

案例：

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

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

mysql> delete from exam_result where name='孙悟空';
Query OK, 1 row affected (0.01 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)

删除整张表数据：

mysql> CREATE TABLE for_delete (
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

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 |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)
-- 准备测试表

mysql> delete from for_delete;
Query OK, 3 rows affected (0.00 sec)
-- 删除整表数据
mysql> select * from for_delete;
Empty set (0.00 sec)

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

mysql> SELECT * from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | D    |
+----+------+
1 row in set (0.00 sec)
-- id从4开始，代表auto_increment并没有被清空
mysql> SHOW CREATE TABLE for_delete\G
*************************** 1. row ***************************
       Table: for_delete
Create Table: CREATE TABLE `for_delete` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

截断表：

语法：

TRUNCATE [TABLE] table_name

注意：这个操作慎用

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；

2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚

3. 会重置 AUTO_INCREMENT 项

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.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
-- 插入数据
mysql> SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)
-- 查看数据

mysql> truncate for_truncate;
Query OK, 0 rows affected (0.11 sec)
-- 删除数据
mysql> select * from for_truncate;
Empty set (0.00 sec)
-- 查看删除结果
mysql> SHOW CREATE TABLE for_truncate\G
*************************** 1. row ***************************
       Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
-- auto_increment被清为0

五、插入查询结果

语法：

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: 0
--插入数据

思路：

mysql> CREATE TABLE no_duplicate_table LIKE duplicate_table;
Query OK, 0 rows affected (0.03 sec)
-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样

mysql> INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
-- 将 duplicate_table 的去重数据插入到 no_duplicate_table

mysql> RENAME TABLE duplicate_table TO old_duplicate_table,
    -> no_duplicate_table TO duplicate_table;
Query OK, 0 rows affected (0.02 sec)
-- 通过重命名表，实现原子的去重操作

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