目录
1. INSERT（插入）
语法：INSERT [INTO] table_name [(column [, column] ...)] VALUES (value_list) [, (value_list)] ... value_list: value, [, value] ...
1.1 全列插入
说明：使用全列插入时，value_list的数量和定义表的列的数量及顺序一致。
测试：
// 单行数据插入
mysql> insert student values(1,001,'jack',NULL);
Query OK, 1 row affected (0.01 sec)
mysql> insert student values(2,002,'jacl',1111);
Query OK, 1 row affected (0.00 sec)
// 多行数据插入
mysql> insert student values(3,003,'jach',1111),(4,004,'jacg',1111),(5,005,'jacf',1111);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select*from student;
+----+----+------+------+
| id | sn | name | qq |
+----+----+------+------+
| 1 | 1 | jack | NULL |
| 2 | 2 | jacl | 1111 |
| 3 | 3 | jach | 1111 |
| 4 | 4 | jacg | 1111 |
| 5 | 5 | jacf | 1111 |
+----+----+------+------+
5 rows in set (0.00 sec)
1.2 指定列插入
测试：
//单行数据插入
mysql> insert student (sn,name,qq)values(6,'jacd',1111);
Query OK, 1 row affected (0.00 sec)
mysql> insert student (sn,name,qq)values(7,'jacs',1111);
Query OK, 1 row affected (0.01 sec)
//多行数据插入
mysql> insert student (sn,name,qq)values(8,'jaca',1111),(9,'javl',1111),(10,'javk',1111);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select *from student ;
+----+----+------+------+
| id | sn | name | qq |
+----+----+------+------+
| 1 | 1 | jack | NULL |
| 2 | 2 | jacl | 1111 |
| 3 | 3 | jach | 1111 |
| 4 | 4 | jacg | 1111 |
| 5 | 5 | jacf | 1111 |
| 6 | 6 | jacd | 1111 |
| 7 | 7 | jacs | 1111 |
| 8 | 8 | jaca | 1111 |
| 9 | 9 | javl | 1111 |
| 10 | 10 | javk | 1111 |
+----+----+------+------+
1.3 插入更新
说明：由于主键或者唯一键对应的值已经存在而导致插入数据失败，那么可以选择性的进行同步更新操作。
语法：INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...
测试：
//发生唯一键冲突导致无法插入数据
mysql> insert student (sn,name,qq)values(7,'jacs',1111);
ERROR 1062 (23000): Duplicate entry '7' for key 'student.sn'
//插入时同步更新
mysql> insert student (sn,name,qq)values(7,'jacs',1111)on duplicate key update name='ssss',qq=124334;
Query OK, 2 rows affected (0.00 sec)
mysql> select*from student;
+----+----+------+--------+
| id | sn | name | qq |
+----+----+------+--------+
| 1 | 1 | jack | NULL |
| 2 | 2 | jacl | 1111 |
| 3 | 3 | jach | 1111 |
| 4 | 4 | jacg | 1111 |
| 5 | 5 | jacf | 1111 |
| 6 | 6 | jacd | 1111 |
| 7 | 7 | ssss | 124334 |
| 8 | 8 | jaca | 1111 |
| 9 | 9 | javl | 1111 |
| 10 | 10 | javk | 1111 |
+----+----+------+--------+
10 rows in set (0.00 sec)
说明：-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等。
-- 1 row affected: 表中没有冲突数据，数据被插入。
-- 2 row affected: 表中有冲突数据，并且数据已经被更新。
测试：
//-- 2 row affected: 表中有冲突数据，并且数据已经被更新
mysql> insert student (sn,name,qq)values(11,'jadd',1111);
Query OK, 1 row affected (0.00 sec)
//-- 1 row affected: 表中没有冲突数据，数据被插入
mysql> insert student (sn,name,qq)values(7,'jacs',1111)on duplicate key update name='ssss',qq=124335;
Query OK, 2 rows affected (0.00 sec)
//-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
mysql> insert student (sn,name,qq)values(7,'jacs',1111)on duplicate key update name='ssss',qq=124335;
Query OK, 0 rows affected (0.00 sec)
1.4 插入替换
说明：主键或者唯一键没有冲突，则直接插入；主键或者唯一键如果冲突，则删除后再插入。
语法：REPLACE[INTO] table_name [(column [, column] ...)] VALUES (value_list) [, (value_list)] ... value_list: value, [, value] ... //和insert语法基本一致
测试：
//1 row affected: 表中没有冲突数据，数据被插入
mysql> replace student (sn,name,qq)values(12,'jass',1111);
Query OK, 1 row affected (0.00 sec)
//2 row affected: 表中有冲突数据，删除后重新插入
mysql> replace student (sn,name,qq)values(12,'jass',2222);
Query OK, 2 rows affected (0.00 sec)
mysql> select *from student;
+----+----+------+--------+
| id | sn | name | qq |
+----+----+------+--------+
| 1 | 1 | jack | NULL |
| 2 | 2 | jacl | 1111 |
| 3 | 3 | jach | 1111 |
| 4 | 4 | jacg | 1111 |
| 5 | 5 | jacf | 1111 |
| 6 | 6 | jacd | 1111 |
| 7 | 7 | ssss | 124335 |
| 8 | 8 | jaca | 1111 |
| 9 | 9 | javl | 1111 |
| 10 | 10 | javk | 1111 |
| 13 | 11 | jadd | 1111 |
| 17 | 12 | jass | 2222 |
+----+----+------+--------+
12 rows in set (0.00 sec)
2. SELECT 查询
2.1 SELECT列
2.1.1 全列查询
说明：通常情况下不建议使用 * 进行全列查询查询的列越多，意味着需要传输的数据量越大；同时可能会影响到索引的使用。
语法：select *from table_name
测试：
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 |
+----+-----------+---------+------+---------+
2.1.2 指定列查询
说明：指定列的顺序不需要按定义表的顺序来。
语法：select column from table_name
测试：
mysql> select id,name,math from exam_result;
+----+-----------+------+
| id | name | math |
+----+-----------+------+
| 1 | 唐三藏 | 98 |
| 2 | 孙悟空 | 78 |
| 3 | 猪悟能 | 98 |
| 4 | 曹孟德 | 84 |
| 5 | 刘玄德 | 85 |
| 6 | 孙权 | 73 |
| 7 | 宋公明 | 65 |
+----+-----------+------+
2.1.3 查询字段为表达式
说明：当查询的字段为表达式时，表达式可以不包含字段，同时也可以包含一个或者多个字段。
测试：
//表达式不包含字段
mysql> select id,name,520 from exam_result;
+----+-----------+-----+
| id | name | 520 |
+----+-----------+-----+
| 1 | 唐三藏 | 520 |
| 2 | 孙悟空 | 520 |
| 3 | 猪悟能 | 520 |
| 4 | 曹孟德 | 520 |
| 5 | 刘玄德 | 520 |
| 6 | 孙权 | 520 |
| 7 | 宋公明 | 520 |
+----+-----------+-----+
//表达式包含一个字段
mysql> select id ,name,math+50 from exam_result;
+----+-----------+---------+
| id | name | math+50 |
+----+-----------+---------+
| 1 | 唐三藏 | 148 |
| 2 | 孙悟空 | 128 |
| 3 | 猪悟能 | 148 |
| 4 | 曹孟德 | 134 |
| 5 | 刘玄德 | 135 |
| 6 | 孙权 | 123 |
| 7 | 宋公明 | 115 |
+----+-----------+---------+
//表达式包含多个字段
mysql> select id,name,chinese+math+english as 总分 from exam_result;
+----+-----------+--------+
| id | name | 总分 |
+----+-----------+--------+
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+--------+
2.1.4 为查询结果指定别名
语法： SELECT column [AS] alias_name [...] FROM table_name;
测试：
mysql> select id,name 名字,chinese 中文,math 数学, english 英语 from exam_result;
+----+-----------+--------+--------+--------+
| id | 名字 | 中文 | 数学 | 英语 |
+----+-----------+--------+--------+--------+
| 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 |
+----+-----------+--------+--------+--------+
2.1.5 查询结果去重
语法：select distinct column from table_name
测试：
mysql> select distinct math from exam_result;
+------+
| math |
+------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+
2.2 WHERE查询
2.2.1 比较运算查询
说明：WHERE 条件中可以比较运算符两侧都是字段。
测试：查询数学成绩大于英语成绩
mysql> select id,name,chinese,math,english from exam_result where math>english;
+----+-----------+---------+------+---------+
| 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 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
2.2.1.1 >, >=, <, <= 运算符
说明：大于，大于等于，小于，小于等于 。
测试：查询语文成绩和在75分上下的
mysql> select id,name,chinese from exam_result where chinese <75;
+----+-----------+---------+
| id | name | chinese |
+----+-----------+---------+
| 1 | 唐三藏 | 67 |
| 5 | 刘玄德 | 55 |
| 6 | 孙权 | 70 |
+----+-----------+---------+
3 rows in set (0.00 sec)
mysql> select id,name,chinese from exam_result where chinese <=75;
+----+-----------+---------+
| id | name | chinese |
+----+-----------+---------+
| 1 | 唐三藏 | 67 |
| 5 | 刘玄德 | 55 |
| 6 | 孙权 | 70 |
| 7 | 宋公明 | 75 |
+----+-----------+---------+
4 rows in set (0.00 sec)
mysql> select id,name,chinese from exam_result where chinese >75;
+----+-----------+---------+
| id | name | chinese |
+----+-----------+---------+
| 2 | 孙悟空 | 87 |
| 3 | 猪悟能 | 88 |
| 4 | 曹孟德 | 82 |
+----+-----------+---------+
3 rows in set (0.00 sec)
mysql> select id,name,chinese from exam_result where chinese >=75;
+----+-----------+---------+
| id | name | chinese |
+----+-----------+---------+
| 2 | 孙悟空 | 87 |
| 3 | 猪悟能 | 88 |
| 4 | 曹孟德 | 82 |
| 7 | 宋公明 | 75 |
+----+-----------+---------+
4 rows in set (0.00 sec)
2.2.1.2 ＝运算符
说明：等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL。
测试：查询语文成绩=75的
mysql> select id,name,chinese from exam_result where chinese =75;
+----+-----------+---------+
| id | name | chinese |
+----+-----------+---------+
| 7 | 宋公明 | 75 |
+----+-----------+---------+
2.2.1.3 <=>运算符
说明： 等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1) 。
测试：
mysql> select NULL<=>NULL;
+-------------+
| NULL<=>NULL |
+-------------+
| 1 |
+-------------+
2.2.1.4 !=, <>运算符
说明：不等于 。
测试：查询语文成绩不等于75的
mysql> select id,name,chinese from exam_result where chinese !=75;
+----+-----------+---------+
| id | name | chinese |
+----+-----------+---------+
| 1 | 唐三藏 | 67 |
| 2 | 孙悟空 | 87 |
| 3 | 猪悟能 | 88 |
| 4 | 曹孟德 | 82 |
| 5 | 刘玄德 | 55 |
| 6 | 孙权 | 70 |
+----+-----------+---------+
2.2.1.5 BETWEEN a0 AND a1运算符
说明：范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)。
测试：查询语文成绩在60到80之间的
mysql> select id,name,chinese from exam_result where chinese between 60 and 80;
+----+-----------+---------+
| id | name | chinese |
+----+-----------+---------+
| 1 | 唐三藏 | 67 |
| 6 | 孙权 | 70 |
| 7 | 宋公明 | 75 |
+----+-----------+---------+
2.2.1.6 IN (option, ...)运算符
说明：如果是 option 中的任意一个，返回 TRUE(1)。
测试：查询语文成绩是67，70，100，120的
mysql> select id,name,chinese from exam_result where chinese in(70,67,100,120);
+----+-----------+---------+
| id | name | chinese |
+----+-----------+---------+
| 1 | 唐三藏 | 67 |
| 6 | 孙权 | 70 |
+----+-----------+---------+
2.2.1.7 IS NULL运算符
说明：是NULL值。
测试：查询英语成绩为NULL的
mysql> select id,name,chinese,math,english from exam_result where english is null;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 8 | 诸葛亮 | 100 | 100 | NULL |
| 9 | 大乔 | 100 | 60 | NULL |
+----+-----------+---------+------+---------+
2.2.1.8 IS NOT NULL运算符
说明：不是NULL值。
测试：查询英文成绩不为空的
mysql> select id,name,chinese,math,english from exam_result where english is not null;
+----+-----------+---------+------+---------+
| 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 |
| 10 | 小乔 | 100 | NULL | 100 |
+----+-----------+---------+------+---------+
2.2.1.9 LIKE运算符
说明：模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符
测试：
mysql> select id,name,chinese,math,english from exam_result where english like '7_';
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 2 | 孙悟空 | 87 | 78 | 77 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)
mysql> select id,name,chinese,math,english from exam_result where english like '1%';
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 10 | 小乔 | 100 | NULL | 100 |
+----+--------+---------+------+---------+
1 row in set (0.00 sec)
mysql> select id,name,chinese,math,english from exam_result where name like '孙%';
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 2 | 孙悟空 | 87 | 78 | 77 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)
mysql> select id,name,chinese,math,english from exam_result where name like '孙%__';
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 2 | 孙悟空 | 87 | 78 | 77 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)
mysql> select id,name,chinese,math,english from exam_result where name like '孙%_';
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 2 | 孙悟空 | 87 | 78 | 77 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)
说明：%是匹配任意多个（包括0个）任意字符，_严格匹配的一个任意字符。
2.2.2 逻辑运算查询
2.2.2.1 AND运算符
说明：多个条件必须都为true，结果才能是true。
测试：查询成绩大于75小于99的
mysql> select id,name,chinese,math,english from exam_result where math>75 and math<99;
+----+-----------+---------+------+---------+
| 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 |
+----+-----------+---------+------+---------+
5 rows in set (0.00 sec)
mysql> select id,name,chinese,math,english from exam_result where math>80 and english<60;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 5 | 刘玄德 | 55 | 85 | 45 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)
2.2.2.2 OR运算符
说明：任意一个条件为true，结果都为true。
测试：
mysql> select id,name,chinese,math,english from exam_result where math>80 or english<60;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 7 | 宋公明 | 75 | 65 | 30 |
| 8 | 诸葛亮 | 100 | 100 | NULL |
+----+-----------+---------+------+---------+
6 rows in set (0.00 sec)
mysql> select id,name,chinese,math,english from exam_result where math=85 or english=90;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 3 | 猪悟能 | 88 | 98 | 90 |
| 5 | 刘玄德 | 55 | 85 | 45 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)
2.2.2.3 NOT运算符
说明：条件为true，结果为false
测试：
mysql> select id,name,chinese,math,english from exam_result where math>80 and name not like'唐%';
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 8 | 诸葛亮 | 100 | 100 | NULL |
+----+-----------+---------+------+---------+
4 rows in set (0.00 sec)
2.2.3 综合查询
测试： 孙某同学，否则要求总成绩 > 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)
语文成绩 > 80 并且不姓孙的同学
mysql> SELECT name, chinese FROM exam_result
-> WHERE chinese > 80 AND name NOT LIKE '孙%';
+-----------+---------+
| name | chinese |
+-----------+---------+
| 猪悟能 | 88 |
| 曹孟德 | 82 |
| 诸葛亮 | 100 |
| 大乔 | 100 |
| 小乔 | 100 |
+-----------+---------+
5 rows in set (0.00 sec)
2.2.4 order排序
说明：ASC 为升序（从小到大）；DESC 为降序（从大到小）；默认为 ASC。
语法：SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];
注意：没有order by的查询语句，返回的顺序是未定义的，有时候可能会有序，所以不要以来这个顺序。
测试：查询数据并按照英语成绩升序排列
mysql> select id,name,chinese,math,english from exam_result order by english asc;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 8 | 诸葛亮 | 100 | 100 | NULL |
| 9 | 大乔 | 100 | 60 | NULL |
| 7 | 宋公明 | 75 | 65 | 30 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 1 | 唐三藏 | 67 | 98 | 56 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 6 | 孙权 | 70 | 73 | 78 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 10 | 小乔 | 100 | NULL | 100 |
+----+-----------+---------+------+---------+
注意：NULL视为比任何人都小！！！
说明：多字段排序，排序的优先级随书写排序。
测试：
mysql> select id ,name ,chinese,math,english ,chinese+math+english as total from exam_result where
name like'孙%' or name like'%乔'order by total asc;
+----+-----------+---------+------+---------+-------+
| id | name | chinese | math | english | total |
+----+-----------+---------+------+---------+-------+
| 9 | 大乔 | 100 | 60 | NULL | NULL |
| 10 | 小乔 | 100 | NULL | 100 | NULL |
| 6 | 孙权 | 70 | 73 | 78 | 221 |
| 2 | 孙悟空 | 87 | 78 | 77 | 242 |
+----+-----------+---------+------+---------+-------+
2.2.5 筛选分页结果
说明：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死 按 id 进行分页，每页 3 条记录，分别显示第 1、2、3 页。
语法：起始下标为 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;
测试：
mysql> select id,name,math,chinese,english from exam_result order by id limit 5
-> ;
+----+-----------+------+---------+---------+
| id | name | math | chinese | english |
+----+-----------+------+---------+---------+
| 1 | 唐三藏 | 98 | 67 | 56 |
| 2 | 孙悟空 | 78 | 87 | 77 |
| 3 | 猪悟能 | 98 | 88 | 90 |
| 4 | 曹孟德 | 84 | 82 | 67 |
| 5 | 刘玄德 | 85 | 55 | 45 |
+----+-----------+------+---------+---------+
5 rows in set (0.00 sec)
mysql> select id,name,math,chinese,english from exam_result order by id limit 5 offset 5;
+----+-----------+------+---------+---------+
| id | name | math | chinese | english |
+----+-----------+------+---------+---------+
| 6 | 孙权 | 73 | 70 | 78 |
| 7 | 宋公明 | 65 | 75 | 30 |
| 8 | 诸葛亮 | 100 | 100 | NULL |
| 9 | 大乔 | 60 | 100 | NULL |
| 10 | 小乔 | NULL | 100 | 100 |
+----+-----------+------+---------+---------+
5 rows in set (0.00 sec)
mysql> select id,name,math,chinese,english from exam_result order by id limit 2,5;
+----+-----------+------+---------+---------+
| id | name | math | chinese | english |
+----+-----------+------+---------+---------+
| 3 | 猪悟能 | 98 | 88 | 90 |
| 4 | 曹孟德 | 84 | 82 | 67 |
| 5 | 刘玄德 | 85 | 55 | 45 |
| 6 | 孙权 | 73 | 70 | 78 |
| 7 | 宋公明 | 65 | 75 | 30 |
+----+-----------+------+---------+---------+
5 rows in set (0.00 sec)
3. Update
语法：UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]
测试：
// 更新所有人的数学成绩为原来的十倍
mysql> update exam_result set math=math*10;
Query OK, 9 rows affected (0.00 sec)
Rows matched: 10 Changed: 9 Warnings: 0
// 更新所有人的数学成绩为原来的十倍
mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 980 | 56 |
| 2 | 孙悟空 | 87 | 780 | 77 |
| 3 | 猪悟能 | 88 | 980 | 90 |
| 4 | 曹孟德 | 82 | 840 | 67 |
| 5 | 刘玄德 | 55 | 850 | 45 |
| 6 | 孙权 | 70 | 730 | 78 |
| 7 | 宋公明 | 75 | 650 | 30 |
| 8 | 诸葛亮 | 100 | 1000 | NULL |
| 9 | 大乔 | 99 | 1000 | 100 |
| 10 | 小乔 | 100 | NULL | 100 |
+----+-----------+---------+------+---------+
10 rows in set (0.00 sec)
// 更新所有人的数学成绩为原来的十倍
mysql> update exam_result set math=math/10;
Query OK, 9 rows affected (0.00 sec)
Rows matched: 10 Changed: 9 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 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
| 8 | 诸葛亮 | 100 | 100 | NULL |
| 9 | 大乔 | 99 | 100 | 100 |
| 10 | 小乔 | 100 | NULL | 100 |
+----+-----------+---------+------+---------+
10 rows in set (0.00 sec)
// 更新小乔的数学成绩为65
mysql> update exam_result set math =65 where name like'小%';
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 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
| 8 | 诸葛亮 | 100 | 100 | NULL |
| 9 | 大乔 | 99 | 100 | 100 |
| 10 | 小乔 | 100 | 65 | 100 |
+----+-----------+---------+------+---------+
10 rows in set (0.00 sec)
//更新语文成绩前三名的成绩加上50分
mysql> select id,name,chinese from exam_result;
+----+-----------+---------+
| id | name | chinese |
+----+-----------+---------+
| 1 | 唐三藏 | 67 |
| 2 | 孙悟空 | 87 |
| 3 | 猪悟能 | 88 |
| 4 | 曹孟德 | 82 |
| 5 | 刘玄德 | 55 |
| 6 | 孙权 | 70 |
| 7 | 宋公明 | 75 |
| 8 | 诸葛亮 | 100 |
| 9 | 大乔 | 99 |
| 10 | 小乔 | 100 |
+----+-----------+---------+
10 rows in set (0.00 sec)
mysql> select id,name,chinese from exam_result order by chinese asc;
+----+-----------+---------+
| id | name | chinese |
+----+-----------+---------+
| 5 | 刘玄德 | 55 |
| 1 | 唐三藏 | 67 |
| 6 | 孙权 | 70 |
| 7 | 宋公明 | 75 |
| 4 | 曹孟德 | 82 |
| 2 | 孙悟空 | 87 |
| 3 | 猪悟能 | 88 |
| 9 | 大乔 | 99 |
| 8 | 诸葛亮 | 100 |
| 10 | 小乔 | 100 |
+----+-----------+---------+
10 rows in set (0.00 sec)
mysql> update exam_result set chinese=chinese+50 order by chinese asc limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select id,name,chinese from exam_result order by chinese asc;
+----+-----------+---------+
| id | name | chinese |
+----+-----------+---------+
| 7 | 宋公明 | 75 |
| 4 | 曹孟德 | 82 |
| 2 | 孙悟空 | 87 |
| 3 | 猪悟能 | 88 |
| 9 | 大乔 | 99 |
| 8 | 诸葛亮 | 100 |
| 10 | 小乔 | 100 |
| 5 | 刘玄德 | 105 |
| 1 | 唐三藏 | 117 |
| 6 | 孙权 | 120 |
+----+-----------+---------+
4. Delete
4.1 delete
说明：delete是删除表中的数据，对于表的结构没有影响
语法：DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
测试：
mysql> select *from student
-> ;
+----+----+------+--------+
| id | sn | name | qq |
+----+----+------+--------+
| 1 | 1 | jack | NULL |
| 2 | 2 | jacl | 1111 |
| 3 | 3 | jach | 1111 |
| 4 | 4 | jacg | 1111 |
| 5 | 5 | jacf | 1111 |
| 6 | 6 | jacd | 1111 |
| 7 | 7 | ssss | 124335 |
| 8 | 8 | jaca | 1111 |
| 9 | 9 | javl | 1111 |
| 10 | 10 | javk | 1111 |
| 13 | 11 | jadd | 1111 |
| 17 | 12 | jass | 2222 |
+----+----+------+--------+
12 rows in set (0.01 sec)
mysql> select id ,sn,name from student order by sn desc;
+----+----+------+
| id | sn | name |
+----+----+------+
| 17 | 12 | jass |
| 13 | 11 | jadd |
| 10 | 10 | javk |
| 9 | 9 | javl |
| 8 | 8 | jaca |
| 7 | 7 | ssss |
| 6 | 6 | jacd |
| 5 | 5 | jacf |
| 4 | 4 | jacg |
| 3 | 3 | jach |
| 2 | 2 | jacl |
| 1 | 1 | jack |
+----+----+------+
12 rows in set (0.00 sec)
//删除名字是jack的数据
mysql> delete from student where name='jack';
Query OK, 1 row affected (0.01 sec)
//删除sn数最大的三个数据
mysql> delete from student order by sn desc limit 3;
Query OK, 3 rows affected (0.01 sec)
mysql> select *from student;
+----+----+------+--------+
| id | sn | name | qq |
+----+----+------+--------+
| 2 | 2 | jacl | 1111 |
| 3 | 3 | jach | 1111 |
| 4 | 4 | jacg | 1111 |
| 5 | 5 | jacf | 1111 |
| 6 | 6 | jacd | 1111 |
| 7 | 7 | ssss | 124335 |
| 8 | 8 | jaca | 1111 |
| 9 | 9 | javl | 1111 |
+----+----+------+--------+
8 rows in set (0.00 sec)
说明：delete也可以删除整张表中的数据， 删除整表的操作要慎用！！！
语法：DELETE FROM TABLE_NAME for_delete;
测试：
mysql> select*from test;
+----+----+------+--------+
| id | sn | name | qq |
+----+----+------+--------+
| 2 | 2 | jacl | 1111 |
| 3 | 3 | jach | 1111 |
| 4 | 4 | jacg | 1111 |
| 5 | 5 | jacf | 1111 |
| 6 | 6 | jacd | 1111 |
| 7 | 7 | ssss | 124335 |
| 8 | 8 | jaca | 1111 |
| 9 | 9 | javl | 1111 |
+----+----+------+--------+
8 rows in set (0.00 sec)
mysql> delete from test for_delete;
Query OK, 8 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| exam_result |
| student |
| test |
+---------------+
3 rows in set (0.00 sec)
mysql> select *from test;
Empty set (0.00 sec)
注意：如果表中含有AUTO_INCREMENT项，那么删除整张表数据后，auto_increment=n的数据是不会改变的，任然会从删除数据前的n开始。
4.2 截断表
说明：这个操作慎用
1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事 物，所以无法回滚
3. 会重置 AUTO_INCREMENT 项
语法： TRUNCATE [TABLE] table_name
测试：
mysql> truncate table test;
Query OK, 0 rows affected (0.02 sec)
mysql> select*From test;
Empty set (0.00 sec)
5. 插入查询结果
语法：INSERT INTO table_name [(column [, column ...])] SELECT ...
测试：
mysql> select*From test;
Empty set (0.00 sec)
mysql> insert test select *from student;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select*From test;
+----+----+------+--------+
| id | sn | name | qq |
+----+----+------+--------+
| 2 | 2 | jacl | 1111 |
| 3 | 3 | jach | 1111 |
| 4 | 4 | jacg | 1111 |
| 5 | 5 | jacf | 1111 |
| 6 | 6 | jacd | 1111 |
| 7 | 7 | ssss | 124335 |
| 8 | 8 | jaca | 1111 |
| 9 | 9 | javl | 1111 |
+----+----+------+--------+
8 rows in set (0.00 sec)
6. 聚合函数
函数：COUNT([DISTINCT] expr) 说明：返回查询到的数据的数量
测试：
mysql> select count(*)from exam_result where chinese<80
-> ;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> select chinese from exam_result where chinese<80;
+---------+
| chinese |
+---------+
| 75 |
+---------+
1 row in set (0.00 sec)
函数：SUM([DISTINCT] expr) 说明：返回查询到的数据的总和，不是数字没有意义
测试：
mysql> select sum(math) from exam_result where math <80;
+-----------+
| sum(math) |
+-----------+
| 281 |
+-----------+
1 row in set (0.00 sec)
mysql> select math,name,id from exam_result where math<80;
+------+-----------+----+
| math | name | id |
+------+-----------+----+
| 78 | 孙悟空 | 2 |
| 73 | 孙权 | 6 |
| 65 | 宋公明 | 7 |
| 65 | 小乔 | 10 |
+------+-----------+----+
4 rows in set (0.00 sec)
函数：AVG([DISTINCT] expr) 说明：返回查询到的数据的平均值，不是数字没有意义
测试：
mysql> select avg(chinese+math+english)total from exam_result;
+--------------------+
| total |
+--------------------+
| 251.33333333333334 |
+--------------------+
1 row in set (0.00 sec)
mysql> select avg(chinese)total from exam_result;
+-------+
| total |
+-------+
| 97.3 |
+-------+
1 row in set (0.00 sec)
函数：MAX([DISTINCT] expr) 说明：返回查询到的数据的最大值，不是数字没有意义
测试：
mysql> select max(chinese)from exam_result;
+--------------+
| max(chinese) |
+--------------+
| 120 |
+--------------+
1 row in set (0.00 sec)
mysql> select *from exam_result order by chinese desc;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 6 | 孙权 | 120 | 73 | 78 |
| 1 | 唐三藏 | 117 | 98 | 56 |
| 5 | 刘玄德 | 105 | 85 | 45 |
| 8 | 诸葛亮 | 100 | 100 | NULL |
| 10 | 小乔 | 100 | 65 | 100 |
| 9 | 大乔 | 99 | 100 | 100 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
函数：MIN([DISTINCT] expr) 说明：返回查询到的数据的 最小值，不是数字没有意义
测试：
mysql> select min(chinese)from exam_result;
+--------------+
| min(chinese) |
+--------------+
| 75 |
+--------------+
1 row in set (0.00 sec)
mysql> select *from exam_result order by chinese asc;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 7 | 宋公明 | 75 | 65 | 30 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 9 | 大乔 | 99 | 100 | 100 |
| 8 | 诸葛亮 | 100 | 100 | NULL |
| 10 | 小乔 | 100 | 65 | 100 |
| 5 | 刘玄德 | 105 | 85 | 45 |
| 1 | 唐三藏 | 117 | 98 | 56 |
| 6 | 孙权 | 120 | 73 | 78 |
+----+-----------+---------+------+---------+
7. group by 分组查询
说明：在select中使用group by 子句可以对指定列进行分组查询。分组的目的是为了进行分组之后，方便聚合统计。可以理解为，把一组按照条件拆成多个组，然后各自进行组内的统计。
语法：select column1, column2, .. from table group by column;
测试：如何显示每个部门的平均工资和最高工资
mysql> select deptno,avg(sal),max(sal)from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal) | max(sal) |
+--------+-------------+----------+
| 20 | 2175.000000 | 3000.00 |
| 30 | 1566.666667 | 2850.00 |
| 10 | 2916.666667 | 5000.00 |
+--------+-------------+----------+
3 rows in set (0.00 sec)
说明：分组的条件是deptno，那么组内一定是相同的可以被压缩聚合的。
测试：显示每个部门的每种岗位的平均工资和最低工资
mysql> select avg(sal),min(sal),job,deptno from emp group by deptno,job;
+-------------+----------+-----------+--------+
| avg(sal) | min(sal) | job | deptno |
+-------------+----------+-----------+--------+
| 950.000000 | 800.00 | CLERK | 20 |
| 1400.000000 | 1250.00 | SALESMAN | 30 |
| 2975.000000 | 2975.00 | MANAGER | 20 |
| 2850.000000 | 2850.00 | MANAGER | 30 |
| 2450.000000 | 2450.00 | MANAGER | 10 |
| 3000.000000 | 3000.00 | ANALYST | 20 |
| 5000.000000 | 5000.00 | PRESIDENT | 10 |
| 950.000000 | 950.00 | CLERK | 30 |
| 1300.000000 | 1300.00 | CLERK | 10 |
+-------------+----------+-----------+--------+
9 rows in set (0.00 sec)
说明： having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。
having和where的区别是：having是对分组聚合之后的结果进行条件筛选，where是对具体的任意列进行条件筛选。
测试：显示平均工资低于2000的部门和它的平均工资
//首先统计各个部门的平均工资
mysql> select avg(sal)from emp group by deptno;
+-------------+
| avg(sal) |
+-------------+
| 2175.000000 |
| 1566.666667 |
| 2916.666667 |
+-------------+
//having和group by配合使用，对group by结果进行过滤
mysql> select deptno,avg(sal)as myavg from emp group by deptno having myavg<2000;
+--------+-------------+
| deptno | myavg |
+--------+-------------+
| 30 | 1566.666667 |
+--------+-------------+