一. 插入语句 — insert
语法：
insert into 表名 (需要插入的字段列表) values (第一行数据), (第二行数据), ... ,(第n行数据);
举例：
创建一张Student表，用来存储学生的身份信息：
id：代表学号，设为自增长的主键。 name：学生姓名，不能为空。 qq：学生的qq号，可以为空但不能重复。 mysql> create table if not exists Student( -> id int unsigned primary key auto_increment, -> name varchar(20) not null, -> qq varchar(30) unique -> ); Query OK, 0 rows affected (0.03 sec) mysql> desc Student; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | qq | varchar(30) | YES | UNI | NULL | | +-------+------------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec)
1. 指定 or 全字段插入
在插入时，我们可以在表名后的括号中指定具体需要插入那些字段；如果不指定就默认是全字段都要插入：
// 1、指定字段插入一行数据，没有指定的字段就使3用默认值 mysql> insert into Student (name, qq) values ('张三', '123456'); Query OK, 1 row affected (0.01 sec) // 2、全字段插入数据 mysql> insert into Student values (20204912, '李四', '456789'); Query OK, 1 row affected (0.01 sec) mysql> select * from Student; +----------+--------+--------+ | id | name | qq | +----------+--------+--------+ | 1 | 张三 | 123456 | | 20204912 | 李四 | 456789 | +----------+--------+--------+ 2 rows in set (0.00 sec)
2. 多行数据插入
在插入时，我们可以在values后面进行多行数据的插入，每一个空格内就是一行数据，空格之间用逗号分隔：
// 向学生表中同时插入两行数据 mysql> insert into Student (name, qq) values ('朱五', '111111'), ('赵六', '222222'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from Student; +----------+--------+--------+ | id | name | qq | +----------+--------+--------+ | 1 | 张三 | 123456 | | 20204912 | 李四 | 456789 | | 20204913 | 朱五 | 111111 | | 20204914 | 赵六 | 222222 | +----------+--------+--------+ 4 rows in set (0.00 sec)
3. key不冲突直接插入，冲突的话就修改
在插入时经常会出现由于主键或者唯一键数据冲突而导致插入失败的情况，解决办法是删除之前冲突的那行记录然后重新插入新的。此外还有另一种办法，我们在insert插入时搭配使用on duplicate key可以完成没有key冲突时正常插入、有key冲突时进行数据更新的工作，具体语法如下：
insert into ... on duplicate key update 字段1=更新后的值,...,字段n=更新后的值；
下面依然用Stdent表做演示，注意Student表中的id字段是自增长主键、qq字段是唯一键，而name就是一个普通的字段。
0 row affected：表中有冲突的主键或唯一键，但冲突数据的值和update的值相等 mysql> select * from Student; +----------+--------+--------+ | id | name | qq | +----------+--------+--------+ | 1 | 张三 | 123456 | | 20204912 | 李四 | 456789 | | 20204913 | 朱五 | 111111 | | 20204914 | 赵六 | 222222 | +----------+--------+--------+ 4 rows in set (0.00 sec) // 因为key冲突那行记录的数据和update的值相等，所以相当于没有更新 // 最终显示结果：0 rows affected (0.00 sec) mysql> insert into Student values (1, '张三', '123456') -> on duplicate key update name='张三'; Query OK, 0 rows affected (0.00 sec) mysql> select * from Student; +----------+--------+--------+ | id | name | qq | +----------+--------+--------+ | 1 | 张三 | 123456 | | 20204912 | 李四 | 456789 | | 20204913 | 朱五 | 111111 | | 20204914 | 赵六 | 222222 | +----------+--------+--------+ 4 rows in set (0.00 sec)
1 row affected：表中没有冲突的主键或唯一键，数据正常插入 mysql> select * from Student; +----------+--------+--------+ | id | name | qq | +----------+--------+--------+ | 1 | 张三 | 123456 | | 20204912 | 李四 | 456789 | | 20204913 | 朱五 | 111111 | | 20204914 | 赵六 | 222222 | +----------+--------+--------+ 4 rows in set (0.00 sec) // 没有key冲突，数据正常插入 // 最终显示结果：1 row affected (0.00 sec) mysql> insert into Student values (2, '王二麻子', '333333') -> on duplicate key update name='王二麻子2'; Query OK, 1 row affected (0.00 sec) mysql> select * from Student; +----------+--------------+--------+ | id | name | qq | +----------+--------------+--------+ | 1 | 张三 | 123456 | | 2 | 王二麻子 | 333333 | | 20204912 | 李四 | 456789 | | 20204913 | 朱五 | 111111 | | 20204914 | 赵六 | 222222 | +----------+--------------+--------+ 5 rows in set (0.00 sec)
2 row affected：表中有冲突的主键或唯一键，并且其他字段的数据被更新 mysql> select * from Student; +----------+--------------+--------+ | id | name | qq | +----------+--------------+--------+ | 1 | 张三 | 123456 | | 2 | 王二麻子 | 333333 | | 20204912 | 李四 | 456789 | | 20204913 | 朱五 | 111111 | | 20204914 | 赵六 | 222222 | +----------+--------------+--------+ 5 rows in set (0.00 sec) // 因为主键或唯一键冲突的原因导致插入失败，但是我们更新了冲突行的其他字段数据 // 最终显示结果：2 rows affected (0.00 sec) mysql> insert into Student values (1, '张三', '123456') -> on duplicate key update name='木头老七', qq='444444'; Query OK, 2 rows affected (0.00 sec) mysql> select * from Student; +----------+--------------+--------+ | id | name | qq | +----------+--------------+--------+ | 1 | 木头老七 | 444444| | 2 | 王二麻子 | 333333 | | 20204912 | 李四 | 456789 | | 20204913 | 朱五 | 111111 | | 20204914 | 赵六 | 222222 | +----------+--------------+--------+ 5 rows in set (0.00 sec)
4. 替换 ---- replace
replace相当于insert和duplicate关键字封装出来的一个新关键字，它有如下特点：
1 row affected：主键 或者 唯一键 没有冲突，则直接插入。
2 row affected：主键 或者 唯一键 如果冲突，则删除后再插入。
PS：没有0 row affected的情况了，如果key冲突且其他字段数据一模一样的已经存在则归于2 row affected的情况，先删除再重新插入。
语法：
replace into 表名 (字段列表) values (第一行数据),...,(第n行数据);
举例：
mysql> select * from Student; +----------+--------------+--------+ | id | name | qq | +----------+--------------+--------+ | 1 | 木头老七 | 444444 | | 2 | 王二麻子 | 333333 | | 20204912 | 李四 | 456789 | | 20204913 | 朱五 | 111111 | | 20204914 | 赵六 | 222222 | +----------+--------------+--------+ 5 rows in set (0.00 sec) // 1、key冲突且替换数据一模一样的已经存在，删除后重新插入 mysql> replace into Student values (1, '木头老七', '444444'); Query OK, 2 rows affected (0.01 sec) // 2、替换数据发生主键或唯一键冲突，则删除后再插入 mysql> replace into Student values (1, '公孙离', '444444'); Query OK, 2 rows affected (0.01 sec) // 3、替换数据没有发生主键或唯一键冲突，直接插入表中 mysql> replace into Student values (3, '诸葛亮', '55555'); Query OK, 1 row affected (0.01 sec) mysql> select * from Student; +----------+--------------+--------+ | id | name | qq | +----------+--------------+--------+ | 1 | 公孙离 | 444444 | | 2 | 王二麻子 | 333333 | | 3 | 诸葛亮 | 55555 | | 20204912 | 李四 | 456789 | | 20204913 | 朱五 | 111111 | | 20204914 | 赵六 | 222222 | +----------+--------------+--------+ 6 rows in set (0.00 sec)
二. 查询语句 — select
select语句可以完成表数据的查询，搭配筛选语句使得select的查询更加灵活。
为了方便举例，在这里新建一张学生成绩表，后面的文章统一使用这张表举例。它包括如下5个字段：
id：学生的学号，设为自增长主键。
name：学生姓名，不允许为空。
chinese：语文成绩，可以为空，默认0.0分。
math：数学成绩，可以为空，默认0.0分。
english：英语成绩，可以为空，默认0.0分。
mysql> create table if not exists TestScores( -> id int unsigned primary key auto_increment, -> name varchar(20) not null, -> chinese float default 0.0, -> math float default 0.0, -> english float default 0.0 -> ); Query OK, 0 rows affected (0.02 sec) mysql> desc TestScores; +---------+------------------+------+-----+---------+----------------+ | 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> insert into TestScores(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.01 sec) Records: 7 Duplicates: 0 Warnings: 0
1. 全字段查询
语法：
select * from 表名;
说明：
这里 * 号的作用类似于Linux中的通配符，表示全部的意思。
通常情况下不建议使用 * 进行全列查询，原因如下：
查询的字段越多，意味着需要传输的数据量越大。
可能会影响到索引的使用。
举例：
查询TestCsores表中所有字段的数据：
mysql> select * from TestScores; +----+-----------+---------+------+---------+ | 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. 指定字段查询
我们也可以查询表中特定字段的数据，其中指定字段的顺序不需要按定义表的顺序来：
语法：
select后面直接跟需要查询的字段即可，如果有多个就用逗号分隔且不用加括号。
举例：
查询TestScores表中学生的数学成绩：
mysql> select name, math from TestScores; +-----------+------+ | name | math | +-----------+------+ | 曹操 | 98 | | 孙权 | 78 | | 孙策 | 98 | | 刘备 | 84 | | 程咬金 | 85 | | 孙尚香 | 73 | | 诸葛亮 | 65 | +-----------+------+ 7 rows in set (0.00 sec)
3. 查询表达式结果
select还有计算表达式的功能：
mysql> select 1+1; +-----+ | 1+1 | +-----+ | 2 | +-----+ 1 row in set (0.00 sec)
这个表达式还可以由表中的字段组合而成：
mysql> select name, math, chinese, english, math+chinese+english from TestScores; +-----------+------+---------+---------+----------------------+ | name | math | chinese | english | math+chinese+english | +-----------+------+---------+---------+----------------------+ | 曹操 | 98 | 67 | 56 | 221 | | 孙权 | 78 | 87 | 77 | 242 | | 孙策 | 98 | 88 | 90 | 276 | | 刘备 | 84 | 82 | 67 | 233 | | 程咬金 | 85 | 55 | 45 | 185 | | 孙尚香 | 73 | 70 | 78 | 221 | | 诸葛亮 | 65 | 75 | 30 | 170 | +-----------+------+---------+---------+----------------------+ 7 rows in set (0.00 sec)
在表达式后面加上as还可以对表达式重命名：
mysql> select name, math, chinese, english, math+chinese+english as total from TestScores; +-----------+------+---------+---------+-------+ | name | math | chinese | english | total | +-----------+------+---------+---------+-------+ | 曹操 | 98 | 67 | 56 | 221 | | 孙权 | 78 | 87 | 77 | 242 | | 孙策 | 98 | 88 | 90 | 276 | | 刘备 | 84 | 82 | 67 | 233 | | 程咬金 | 85 | 55 | 45 | 185 | | 孙尚香 | 73 | 70 | 78 | 221 | | 诸葛亮 | 65 | 75 | 30 | 170 | +-----------+------+---------+---------+-------+ 7 rows in set (0.00 sec)
4. 查询结果去重
在表中，可能会包含重复值。这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值，distinct关键字可以帮助我们完成去重工作。
语法：
select distinct 字段名称 from表名称;
举例：
// 1、查询表中学生的数学成绩，发现98分重复了 mysql> select math from TestScores; +------+ | math | +------+ | 98 | | 78 | | 98 | | 84 | | 85 | | 73 | | 65 | +------+ 7 rows in set (0.00 sec) // 2、使用distinct去重 mysql> select distinct math from TestScores; +------+ | math | +------+ | 98 | | 78 | | 84 | | 85 | | 73 | | 65 | +------+ 6 rows in set (0.00 sec)
查询结果插入
如果我们要求表中重复的数据只能有一份的话，应该怎么办呢？首先想到的是：删除表中的重复记录，那就需要先找到那些行重复了，然后逐一删除，这样做的成本太高了还容易出错。这里有个表较好的方法是先使用distinct查询出原表中去重后的数据，然后把这些找到的数据全部插入到一张新表中，最后删除旧表。
第一步：创建一张和旧表一样结构但数据为空的新表，这里要用到like字句：
// 只拷贝结果，不拷贝数据
create table 新表名 like 旧表名;
1
2
第二步：下面是向新表中插入所有旧表去重后的查询结果的语句：
insert into 新表名 select * distinct from 旧表名;
三. 筛选字句 — where
1. 介绍
where子句用于规定选择的标准，通常需要搭配其他语句使用。下面是 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)
注意：在写SQL时，语（子）句之间用空格隔开，字段之间用逗号隔开。
2. 举例
1、查询英语不及格的同学及他的英语成绩 （<60）
mysql> select name, english from TestScores where english<60; +-----------+---------+ | name | english | +-----------+---------+ | 曹操 | 56 | | 程咬金 | 45 | | 诸葛亮 | 30 | +-----------+---------+ 3 rows in set (0.00 sec)
2、语文成绩在 [80, 90] 分的同学及他的语文成绩
// 方法一：使用 AND 进行条件连接 mysql> select name, chinese from TestScores where chinese>=80 and chinese<=90; +--------+---------+ | name | chinese | +--------+---------+ | 孙权 | 87 | | 孙策 | 88 | | 刘备 | 82 | +--------+---------+ 3 rows in set (0.00 sec) // 方法二：使用 BETWEEN ... AND ... 条件进行连接 mysql> select name, chinese from TestScores where chinese between 80 and 90; +--------+---------+ | name | chinese | +--------+---------+ | 孙权 | 87 | | 孙策 | 88 | | 刘备 | 82 | +--------+---------+ 3 rows in set (0.00 sec)
3、数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
// 方法一：使用 OR 进行条件连接 mysql> select name, math from TestScores where math=58 or math=59 or math=98 or math=99; +--------+------+ | name | math | +--------+------+ | 曹操 | 98 | | 孙策 | 98 | +--------+------+ 2 rows in set (0.00 sec) // 方法二：使用 IN 进行条件筛选 mysql> select name, math from TestScores where math in (58, 59, 98, 99); +--------+------+ | name | math | +--------+------+ | 曹操 | 98 | | 孙策 | 98 | +--------+------+ 2 rows in set (0.00 sec)
4、查询姓孙的同学 及 孙某同学
// %匹配任意多个（包括 0 个）字符 mysql> select name from TestScores where name like '孙%'; +-----------+ | name | +-----------+ | 孙权 | | 孙策 | | 孙尚香 | +-----------+ 3 rows in set (0.00 sec) // _严格匹配的一个字符 mysql> select name from TestScores where name like '孙_'; +--------+ | name | +--------+ | 孙权 | | 孙策 | +--------+ 2 rows in set (0.00 sec)
5、语文成绩好于英语成绩的同学
mysql> select name, chinese, english from TestScores where chinese > english; +-----------+---------+---------+ | name | chinese | english | +-----------+---------+---------+ | 曹操 | 67 | 56 | | 孙权 | 87 | 77 | | 刘备 | 82 | 67 | | 程咬金 | 55 | 45 | | 诸葛亮 | 75 | 30 | +-----------+---------+---------+ 5 rows in set (0.00 sec)
6、查询语文成绩比英语成绩好30分以上的同学
mysql> select name, chinese, english from TestScores where chinese > english+30; +-----------+---------+---------+ | name | chinese | english | +-----------+---------+---------+ | 诸葛亮 | 75 | 30 | +-----------+---------+---------+ 1 row in set (0.00 sec)
7、总分在 200 分以下的同学
注意where字句中只能识别表的字段，但不能识别表达式的别名，因为where字句是和select查询同步进行的，表达式的计算是在查询到结果之后进行的。
// error mysql> select name, chinese+math+english as total from TestScores where total < 200; ERROR 1054 (42S22): Unknown column 'total' in 'where clause' // succeed mysql> select name, chinese+math+english as total from TestScores where chinese+math+english < 200; +-----------+-------+ | name | total | +-----------+-------+ | 程咬金 | 185 | | 诸葛亮 | 170 | +-----------+-------+ 2 rows in set (0.00 sec)
8、语文成绩 > 80 并且不姓孙的同学
AND与NOT一起使用即可：
mysql> select name, chinese from TestScores where chinese>80 and not name like '孙%'; +--------+---------+ | name | chinese | +--------+---------+ | 刘备 | 82 | +--------+---------+ 1 row in set (0.00 sec)
9、孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
mysql> select name, chinese, math, english, chinese+math+english as total from TestScores -> where name like '孙%' -> or (chinese+math+english > 200 and chinese < math and english > 80); +-----------+---------+------+---------+-------+ | name | chinese | math | english | total | +-----------+---------+------+---------+-------+ | 孙权 | 87 | 78 | 77 | 242 | | 孙策 | 88 | 98 | 90 | 276 | | 孙尚香 | 70 | 73 | 78 | 221 | +-----------+---------+------+---------+-------+ 3 rows in set (0.00 sec)
10、NULL 的查询
NULL和其他数据比较时：
=下的NULL是不安全的，比较结果全是NULL。
<=>下的NULL是安全的，比较结果符合逻辑。
NULL比较时，建议使用 is null 和 is not null。
对于!=和<>而言，NULL都是不安全的。所以涉及到NULL的比较时，尽量用 is null 和 is not null。
四. 排序字句 — order by
1. 介绍
ORDER BY 语句可以根据指定的字段对结果集进行排序，且该语句默认按照升序对记录进行排序，当然我们也可以显示指定排序的方式：
asc：即ascending，为升序（从小到大）。
desc：即descending，为降序（从大到小）。
PS：在MySQL中，NULL视为比任何值都小。
2. 举例
1、查询同学及数学成绩，按数学成绩升序显示
// 1、未排序的结果 mysql> select name, math from TestScores; +-----------+------+ | name | math | +-----------+------+ | 曹操 | 98 | | 孙权 | 78 | | 孙策 | 98 | | 刘备 | 84 | | 程咬金 | 85 | | 孙尚香 | 73 | | 诸葛亮 | 65 | +-----------+------+ 7 rows in set (0.00 sec) // 2、order by字句默认就是升序 mysql> select name, math from TestScores order by math; +-----------+------+ | name | math | +-----------+------+ | 诸葛亮 | 65 | | 孙尚香 | 73 | | 孙权 | 78 | | 刘备 | 84 | | 程咬金 | 85 | | 曹操 | 98 | | 孙策 | 98 | +-----------+------+ 7 rows in set (0.00 sec) // 3、当然也可以手动加上asc指定升序排序 mysql> select name, math from TestScores order by math asc; +-----------+------+ | name | math | +-----------+------+ | 诸葛亮 | 65 | | 孙尚香 | 73 | | 孙权 | 78 | | 刘备 | 84 | | 程咬金 | 85 | | 曹操 | 98 | | 孙策 | 98 | +-----------+------+ 7 rows in set (0.00 sec)
2、查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
多字段排序时，排序优先级随order by后面的书写顺序而定：
mysql> select name, math, english, chinese from TestScores -> 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)
3、查询同学及总分，由高到低
与where字句不同的是，order by子句可以识别别名，因为多语句组合使用时的执行逻辑如下：
先根据where字句给出的条件筛选出原表格中满足条件的数据。
依据这些筛选出来的数据完成表达式的计算和一些重命名工作。
然后执行order by字句对上面的数据进行排序。
最后执行limit分页（这个后面讲解）。
// ORDER BY 子句中可以使用列别名 mysql> select name, chinese+math+english as total from TestScores -> order by total desc; +-----------+-------+ | name | total | +-----------+-------+ | 孙策 | 276 | | 孙权 | 242 | | 刘备 | 233 | | 曹操 | 221 | | 孙尚香 | 221 | | 程咬金 | 185 | | 诸葛亮 | 170 | +-----------+-------+ 7 rows in set (0.00 sec)
4、查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示
结合 where子句和order by子句，注意它们的执行顺序：要先筛选出数据然后才能排序：
mysql> select name, math from TestScores -> where name like '孙%' or name like '曹%' -> order by math desc; +-----------+------+ | name | math | +-----------+------+ | 曹操 | 98 | | 孙策 | 98 | | 孙权 | 78 | | 孙尚香 | 73 | +-----------+------+ 4 rows in set (0.00 sec)
五. 分页字句 — limit
1. 配合查询时的介绍
从第0行记录开始，查询n条结果：
SELECT ... FROM 表名 [WHERE ...] [ORDER BY ...] LIMIT n;
从第s行记录开始，筛选n条结果（用法类似于std::string::substr(…)）：
SELECT ... FROM 表名 [WHERE ...] [ORDER BY ...] LIMIT s, n;
从第s行记录开始，筛选n条结果，比第二种用法更明确，建议使用（其中offset是偏移量的意思）:
SELECT ... FROM 表名 [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
使用建议：对未知表进行查询时，最好加一条limit 1来先观察表的基本数据和结构；避免因为表中数据过多，查询全表数据时导致MySQL卡死。
2. 举例
按 id 进行分页，每页 3 条记录，然后分别显示 第 1、2、3 页：
// 第1页 mysql> select name, chinese, math, english from TestScores limit 0, 3; +--------+---------+------+---------+ | name | chinese | math | english | +--------+---------+------+---------+ | 曹操 | 67 | 98 | 56 | | 孙权 | 87 | 78 | 77 | | 孙策 | 88 | 98 | 90 | +--------+---------+------+---------+ 3 rows in set (0.00 sec) // 第2页 mysql> select name, math, english from TestScores limit 3, 3; +-----------+------+---------+ | name | math | english | +-----------+------+---------+ | 刘备 | 84 | 67 | | 程咬金 | 85 | 45 | | 孙尚香 | 73 | 78 | +-----------+------+---------+ 3 rows in set (0.00 sec) // 第3页，如果结果不足3个，不会有影响 mysql> select name, math, english from TestScores limit 6, 3; +-----------+------+---------+ | name | math | english | +-----------+------+---------+ | 诸葛亮 | 65 | 30 | +-----------+------+---------+ 1 row in set (0.01 sec)
PS：这种分页且每页固定有n条数据的场景还是很常见的，比如我们在搜索引擎上搜索一个关键字时，整个页面就是一种分页的效果显示出来的：
六. 修改语句 — update
1. 介绍
update使用时通常需要结合where字句去筛选出特定的某一行数据然后再对其中具体某个字段的值做修改；如果不筛选的话，update默认修改的的是整个表格的数据。
语法：
update 表名称 set 列名称=新值 [其他语句];
PS：为了更灵活筛选，update还可以配合where、order by和limit一起使用，具体看下面的例子。
2. 举例
1、将曹操同学的语文成绩变更为 80 分
//1、查看修改前曹操同学的语文成绩 mysql> select name, chinese from TestScores where name='曹操'; +--------+---------+ | name | chinese | +--------+---------+ | 曹操 | 67 | +--------+---------+ 1 row in set (0.00 sec) // 2、修改曹操同学的语文成绩为80分 mysql> update TestScores set chinese=80 where name='曹操'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 // 3、查看修改后曹操同学的语文成绩 mysql> select name, chinese from TestScores where name='曹操'; +--------+---------+ | name | chinese | +--------+---------+ | 曹操 | 80 | +--------+---------+ 1 row in set (0.00 sec)
2、将曹操同学的数学成绩变更为 60 分，语文成绩变更为 70 分
// 1、修改前 mysql> select name, math, chinese from TestScores where name='曹操'; +--------+------+---------+ | name | math | chinese | +--------+------+---------+ | 曹操 | 98 | 80 | +--------+------+---------+ 1 row in set (0.00 sec) // 2、修改ing mysql> update TestScores set math=60, chinese=70 where name='曹操'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 // 3、修改后 mysql> select name, math, chinese from TestScores where name='曹操'; +--------+------+---------+ | name | math | chinese | +--------+------+---------+ | 曹操 | 60 | 70 | +--------+------+---------+ 1 row in set (0.00 sec)
3、将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
PS：数据更新，不支持 math += 30 这种语法，只能math = math + 30。
// 1、查看原数据 mysql> select name, math from TestScores order by chinese+math+english limit 3; +-----------+------+ | name | math | +-----------+------+ | 诸葛亮 | 65 | | 程咬金 | 85 | | 曹操 | 60 | +-----------+------+ 3 rows in set (0.00 sec) // 2、修改数据 mysql> update TestScores 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 // 3、查看修改后的数据 mysql> select name, math from TestScores where name in ('诸葛亮', '程咬金', '曹操'); +-----------+------+ | name | math | +-----------+------+ | 曹操 | 90 | | 程咬金 | 115 | | 诸葛亮 | 95 | +-----------+------+ 3 rows in set (0.00 sec)
七. 删除语句 — delete
1. 介绍
delete语句用于删除表中的某行记录。
具体要删除那一行，可以通过where、order by、limit等字句筛选出来，语法如下：
delete from 表名称 [其他语句];
2. 举例
1、删除曹操同学的考试成绩
// 1、查看原数据 mysql> select name from TestScores where name='曹操'; +--------+ | name | +--------+ | 曹操 | +--------+ 1 row in set (0.00 sec) // 2、删除name='曹操'那行的所有数据 mysql> delete from TestScores where name='曹操'; Query OK, 1 row affected (0.01 sec) // 3、查看删除结果 mysql> select name from TestScores where name='曹操'; Empty set (0.00 sec)
3. 清空表数据
为了测试清空表的操作，我们新建一张测试表ForDelete用来举例：
mysql> create table if not exists ForDelete( -> id int unsigned primary key auto_increment, -> name varchar(20) -> ); Query OK, 0 rows affected (0.03 sec) mysql> desc ForDelete; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+------------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
接下来向表中，插入三行数据：
mysql> insert into ForDelete (name) values ('a'), ('b'), ('c'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from ForDelete; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+ 3 rows in set (0.01 sec)
接下来介绍两种清空表数据的方法。
方法一：delete from 表名
// 1、使用delete语句来清空表数据 mysql> delete from ForDelete; Query OK, 3 rows affected (0.00 sec) // 2、向表中重新插入三条数据 mysql> insert into ForDelete (name) values ('d'), ('e'), ('f'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 // 3、观察新插入的数据情况 mysql> select * from ForDelete; +----+------+ | id | name | +----+------+ | 4 | d | | 5 | e | | 6 | f | +----+------+ 3 rows in set (0.00 sec)
发现使用delete清空表数据之后，新插入数据它的自增长值依然是在原表基础上递增的，说明delete清空表时并没有清除表的自增长记录值。我们查看该表的创建语句，发现确实还记录着auto_increment的值：
方法二：truncate table 表名
truncate 有截断的意思，该语句有如下三点需要注意：
该关键字只能对整张表操作，不能像 DELETE 一样针对部分数据操作。
截断时 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚。
该操作会重置表的 AUTO_INCREMENT 项。
举例：还是上面的测试表ForDelete，这次我们使用truncate来清空表的数据。
// 1、清空之前表中的数据 mysql> select * from ForDelete; +----+------+ | id | name | +----+------+ | 4 | d | | 5 | e | | 6 | f | +----+------+ 3 rows in set (0.00 sec) // 2、使用truncate清空表中的数据 mysql> truncate table ForDelete; Query OK, 0 rows affected (0.05 sec) // 3、清空完成 mysql> select * from ForDelete; Empty set (0.00 sec)
接下来向这张已经被清空的表中重新插入三组数据，观察自增长值是怎么分配的：
mysql> insert into ForDelete (name) values ('g'), ('h'), ('i'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from ForDelete; +----+------+ | id | name | +----+------+ | 1 | g | | 2 | h | | 3 | i | +----+------+ 3 rows in set (0.00 sec)
观察新插入的三行数据，发现原来表的自增长值被重置了。最后我们来看看该表的创建语句是否还记录着auto_increment的值：
八. DML使用总结
语（子）句之间用空格隔开，字段和()之间用逗号隔开。
查询时的执行顺序：where子句 + 其他语句 —> 表达式计算+重命名 —> order by字句 —> limit字句。
where字句中不能识别重命名，其他子句可以。
