1. insert
我们先创建一个表结构，这部分操作我们使用这张表完成我们的操作：
mysql> create table student(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> qq varchar(20) unique
-> );
Query OK, 0 rows affected (0.04 sec)
示例演示：
-- 单行插入
mysql> insert into student values(100, '张三', '11111');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(101, '李四', NULL);
Query OK, 1 row affected (0.01 sec)
-- 多行插入
mysql> insert into student(name,qq) values('王五', '22222'),('赵六',' ');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+-----+--------+-------+
| id | name | qq |
+-----+--------+-------+
| 100 | 张三 | 11111 |
| 101 | 李四 | NULL |
| 102 | 王五 | 22222 |
| 103 | 赵六 | |
+-----+--------+-------+
4 rows in set (0.00 sec)
-- 插入冲突更新
mysql> insert into student(id,name,qq) values(102,'周七', '33333');
ERROR 1062 (23000): Duplicate entry '102' for key 'student.PRIMARY'
mysql> insert into student(id,name,qq) values(102,'周七', '33333') on duplicate key update name = '周七', qq = '33333';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from student;
+-----+--------+-------+
| id | name | qq |
+-----+--------+-------+
| 100 | 张三 | 11111 |
| 101 | 李四 | NULL |
| 102 | 周七 | 33333 |
| 103 | 赵六 | |
+-----+--------+-------+
4 rows in set (0.00 sec)
-- 替换
mysql> replace into student (id, name) VALUES (100, '田八');
Query OK, 2 rows affected (0.00 sec)
mysql> select * from student;
+-----+--------+-------+
| id | name | qq |
+-----+--------+-------+
| 100 | 田八 | NULL |
| 101 | 李四 | NULL |
| 102 | 周七 | 33333 |
| 103 | 赵六 | |
+-----+--------+-------+
4 rows in set (0.00 sec)
2. update
对查询到的结果进行列值更新：
-- 将张三同学的数学成绩变更为 100 分
mysql> update exam_result set math = 100 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 67 | 100 | 56 |
| 3 | 李四 | 87 | 78 | 77 |
| 4 | 王五 | 88 | 98 | 90 |
| 5 | 赵六 | 82 | 84 | 67 |
| 6 | 田八 | 70 | 73 | 78 |
| 7 | 刘九 | 75 | 65 | 30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)
-- 将张三同学的数学成绩变更为 60 分，语文成绩变更为 70 分
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 |
+----+--------+---------+------+---------+
| 2 | 张三 | 70 | 60 | 56 |
| 3 | 李四 | 87 | 78 | 77 |
| 4 | 王五 | 88 | 98 | 90 |
| 5 | 赵六 | 82 | 84 | 67 |
| 6 | 田八 | 70 | 73 | 78 |
| 7 | 刘九 | 75 | 65 | 30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)
-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
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 * from exam_result;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 70 | 90 | 56 |
| 3 | 李四 | 87 | 78 | 77 |
| 4 | 王五 | 88 | 98 | 90 |
| 5 | 赵六 | 82 | 84 | 67 |
| 6 | 田八 | 70 | 103 | 78 |
| 7 | 刘九 | 75 | 95 | 30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)
-- 将所有同学的语文成绩更新为原来的 2 倍
mysql> update exam_result set chinese = chinese * 2;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 140 | 90 | 56 |
| 3 | 李四 | 174 | 78 | 77 |
| 4 | 王五 | 176 | 98 | 90 |
| 5 | 赵六 | 164 | 84 | 67 |
| 6 | 田八 | 140 | 103 | 78 |
| 7 | 刘九 | 150 | 95 | 30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)3. delete
-- 删除张三同学的考试成绩
mysql> delete from exam_result where name = '张三';
Query OK, 1 row affected (0.00 sec)
mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 3 | 李四 | 174 | 78 | 77 |
| 4 | 王五 | 176 | 98 | 90 |
| 5 | 赵六 | 164 | 84 | 67 |
| 6 | 田八 | 140 | 103 | 78 |
| 7 | 刘九 | 150 | 95 | 30 |
+----+--------+---------+------+---------+
5 rows in set (0.00 sec)
-- 删除整张表
delete from for_delete;
Query OK, 3 rows affected (0.00 sec)
SELECT * FROM for_delete;
Empty set (0.00 sec)
-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
truncate for_truncate;
Query OK, 0 rows affected (0.10 sec)
SELECT * FROM for_truncate;
Empty set (0.00 sec)
-- delete 和 truncate 的其中一个区别是truncate会清空auto_increment。4. select
这部分使用下面的表进行操作：
mysql> create table exam_result(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> chinese float default 0,
-> math float default 0,
-> english float default 0
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into exam_result(name,chinese,math,english) values('张三',67,98,56),
-> ('李四',87,78,77);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into exam_result(name,chinese,math,english) values('王五', 88, 98, 90), ('赵六', 82, 84, 67),('田八', 70, 73, 78),('刘九', 75, 65, 30);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
4.1 简单查询
-- 全列查询
mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 67 | 98 | 56 |
| 3 | 李四 | 87 | 78 | 77 |
| 4 | 王五 | 88 | 98 | 90 |
| 5 | 赵六 | 82 | 84 | 67 |
| 6 | 田八 | 70 | 73 | 78 |
| 7 | 刘九 | 75 | 65 | 30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)
-- 查询部分列
mysql> select id,name,chinese from exam_result;
+----+--------+---------+
| id | name | chinese |
+----+--------+---------+
| 2 | 张三 | 67 |
| 3 | 李四 | 87 |
| 4 | 王五 | 88 |
| 5 | 赵六 | 82 |
| 6 | 田八 | 70 |
| 7 | 刘九 | 75 |
+----+--------+---------+
6 rows in set (0.00 sec)
-- 查询字段为表达式
mysql> select id,name,chinese+math+english from exam_result;
+----+--------+----------------------+
| id | name | chinese+math+english |
+----+--------+----------------------+
| 2 | 张三 | 221 |
| 3 | 李四 | 242 |
| 4 | 王五 | 276 |
| 5 | 赵六 | 233 |
| 6 | 田八 | 221 |
| 7 | 刘九 | 170 |
+----+--------+----------------------+
6 rows in set (0.00 sec)
-- 为查询列指定别名
mysql> select id,name,chinese+math+english 总分 from exam_result;
+----+--------+--------+
| id | name | 总分 |
+----+--------+--------+
| 2 | 张三 | 221 |
| 3 | 李四 | 242 |
| 4 | 王五 | 276 |
| 5 | 赵六 | 233 |
| 6 | 田八 | 221 |
| 7 | 刘九 | 170 |
+----+--------+--------+
6 rows in set (0.00 sec)
-- 去重查询
mysql> select distinct math from exam_result;
+------+
| math |
+------+
| 98 |
| 78 |
| 84 |
| 73 |
| 65 |
+------+
5 rows in set (0.01 sec)
4.2 where
比较运算符：
| 运算符 | 说明 |
|---|---|
| >, >=,<,<= | 大于，大于等于，小于，小于等于 |
| = | 等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL |
| <=> | 等于，NULL 安全，例如 NULL NULL 的结果是 TRUE(1) |
| !=, <> | 不等于 |
| BETWEEN a0 AND a1 | 范围匹配，[a0, a1]，如果 a0 <= values <=a1，返回 TRUE(1) |
| IN (option, ...) | 如果是 option 中的任意一个，返回 TRUE(1) |
| IS NULL | 是 NULL |
| IS NOT NULL | 不是 NULL |
| LIKE | 模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符 |
逻辑运算符：
| 运算符 | 说明 |
|---|---|
| AND | 多个条件必须都为 TRUE(1)，结果才是 TRUE(1) |
| OR | |
| NOT |
示例演示：
-- 英语不及格的同学及英语成绩 ( < 60 )
mysql> select name,english from exam_result where english < 60;
+--------+---------+
| name | english |
+--------+---------+
| 张三 | 56 |
| 刘九 | 30 |
+--------+---------+
2 rows in set (0.00 sec)
-- 语文成绩在 [80, 90] 分的同学及语文成绩
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)
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)
-- 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
mysql> select name,math from exam_result where math = 58 or math = 59 or math = 98 or math = 99;
+--------+------+
| name | math |
+--------+------+
| 张三 | 98 |
| 王五 | 98 |
+--------+------+
2 rows in set (0.00 sec)
mysql> select name,math from exam_result where math in(58,59,98,99);
+--------+------+
| name | math |
+--------+------+
| 张三 | 98 |
| 王五 | 98 |
+--------+------+
2 rows in set (0.01 sec)
-- 姓张的同学 及 赵某同学
mysql> select name from exam_result where name like '张%' or name like '赵_';
+--------+
| name |
+--------+
| 张三 |
| 赵六 |
+--------+
2 rows 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 |
| 刘九 | 75 | 30 |
+--------+---------+---------+
4 rows in set (0.00 sec)
-- 总分在 200 分以下的同学
mysql> select name,chinese+math+english from exam_result where chinese + math + english < 200;
+--------+----------------------+
| name | chinese+math+english |
+--------+----------------------+
| 刘九 | 170 |
+--------+----------------------+
1 row in set (0.00 sec)
-- 语文成绩 > 80 并且不姓张的同学
mysql> select name,chinese from exam_result where chinese > 80 and name not like '张%';
+--------+---------+
| name | chinese |
+--------+---------+
| 李四 | 87 |
| 王五 | 88 |
| 赵六 | 82 |
+--------+---------+
3 rows in set (0.00 sec)
-- 张某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
mysql> select * from exam_result where name like '张%' or (chinese + math + english > 200 and chinese < math and english >80);
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 67 | 98 | 56 |
| 4 | 王五 | 88 | 98 | 90 |
+----+--------+---------+------+---------+
2 rows in set (0.00 sec)
4.3 order by
-- 同学及数学成绩，按数学成绩升序显示
mysql> select name,math from exam_result order by math;
+--------+------+
| name | math |
+--------+------+
| 刘九 | 65 |
| 田八 | 73 |
| 李四 | 78 |
| 赵六 | 84 |
| 张三 | 98 |
| 王五 | 98 |
+--------+------+
6 rows in set (0.01 sec)
-- 同学及总分，按总分排序显示
mysql> select name,chinese + math + english 总分 from exam_result order by 总分;
+--------+--------+
| name | 总分 |
+--------+--------+
| 刘九 | 170 |
| 张三 | 221 |
| 田八 | 221 |
| 赵六 | 233 |
| 李四 | 242 |
| 王五 | 276 |
+--------+--------+
6 rows in set (0.00 sec)
-- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
mysql> select * from exam_result order by math desc,english,chinese;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 67 | 98 | 56 |
| 4 | 王五 | 88 | 98 | 90 |
| 5 | 赵六 | 82 | 84 | 67 |
| 3 | 李四 | 87 | 78 | 77 |
| 6 | 田八 | 70 | 73 | 78 |
| 7 | 刘九 | 75 | 65 | 30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)
-- 查询姓张的同学或者姓刘的同学数学成绩，结果按数学成绩由高到低显示
mysql> select * from exam_result where name like '张%' or name like '刘%' order by math desc;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 67 | 98 | 56 |
| 7 | 刘九 | 75 | 65 | 30 |
+----+--------+---------+------+---------+
2 rows in set (0.00 sec)4.4 limit offset
对查询结果进行分页处理：
mysql> select * from exam_result limit 3 offset 0;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 67 | 98 | 56 |
| 3 | 李四 | 87 | 78 | 77 |
| 4 | 王五 | 88 | 98 | 90 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 3 offset 3;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 5 | 赵六 | 82 | 84 | 67 |
| 6 | 田八 | 70 | 73 | 78 |
| 7 | 刘九 | 75 | 65 | 30 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 3 offset 6;
Empty set (0.00 sec)
4.5 insert into
mysql> insert into son_table select * from exam_result;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from son_table;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 3 | 李四 | 174 | 78 | 77 |
| 4 | 王五 | 176 | 98 | 90 |
| 5 | 赵六 | 164 | 84 | 67 |
| 6 | 田八 | 140 | 103 | 78 |
| 7 | 刘九 | 150 | 95 | 30 |
+----+--------+---------+------+---------+
5 rows in set (0.00 sec)4.6 聚合函数
| 函数 | 说明 |
|---|---|
| COUNT([DISTINCT] expr) | 返回查询到的数据的数量 |
| SUM([DISTINCT] expr) | 返回查询到的数据的 总和，不是数字没有意义 |
| AVG([DISTINCT] expr) | 返回查询到的数据的 平均值，不是数字没有意义 |
| MAX([DISTINCT] expr) | 返回查询到的数据的 最大值，不是数字没有意义 |
| MIN([DISTINCT] expr) | 返回查询到的数据的 最小值，不是数字没有意义 |
-- 统计表中的人数
-- 使用 * 做统计，不受 NULL 影响
mysql> select count(*) 人数 from exam_result;
+--------+
| 人数 |
+--------+
| 5 |
+--------+
1 row in set (0.01 sec)
-- 使用表达式做统计
mysql> select count(name) 人数 from exam_result;
+--------+
| 人数 |
+--------+
| 5 |
+--------+
1 row in set (0.00 sec)
-- 统计数学成绩总分
mysql> select sum(math) 数学总分 from exam_result;
+--------------+
| 数学总分 |
+--------------+
| 458 |
+--------------+
1 row in set (0.00 sec)
-- 统计平均总分
mysql> select avg(math + chinese + english) 平均分 from exam_result;
+-----------+
| 平均分 |
+-----------+
| 320.8 |
+-----------+
1 row in set (0.00 sec)
-- 返回最高总分
mysql> select max(math + chinese + english) 最高总分 from exam_result;
+--------------+
| 最高总分 |
+--------------+
| 364 |
+--------------+
1 row in set (0.00 sec)
-- 返回 > 70 分以上的数学最低分
mysql> select min(math) 数学低分 from exam_result where math > 70;
+--------------+
| 数学低分 |
+--------------+
| 78 |
+--------------+
1 row in set (0.00 sec)
-- having 结构
mysql> select math + chinese + english .总分 from exam_result having 总分 > 320;
+--------+
| 总分 |
+--------+
| 329 |
| 364 |
| 321 |
+--------+
3 rows in set (0.00 sec)
4.7 group by
在select中使用group by 子句可以对指定列进行分组查询
-- 显示每个部门的平均工资和最高工资
select deptno,avg(sal),max(sal) from EMP group by deptno;
-- 每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),job, deptno from EMP group by deptno, job;