【MySQL】07.表内容的操作

1. insert

我们先创建一个表结构，这部分操作我们使用这张表完成我们的操作：

1. mysql> create table student(
2.     -> id int primary key auto_increment,
3.     -> name varchar(20) not null,
4.     -> qq varchar(20) unique
5.     -> );
6. Query OK, 0 rows affected (0.04 sec)

复制代码

示例演示： 

1. -- 单行插入
2. mysql> insert into student values(100, '张三', '11111');
3. Query OK, 1 row affected (0.00 sec)
5. mysql> insert into student values(101, '李四', NULL);
6. Query OK, 1 row affected (0.01 sec)
8. -- 多行插入
9. mysql> insert into student(name,qq) values('王五', '22222'),('赵六',' ');
10. Query OK, 2 rows affected (0.00 sec)
11. Records: 2  Duplicates: 0  Warnings: 0
13. mysql> select * from student;
14. +-----+--------+-------+
15. | id  | name   | qq    |
16. +-----+--------+-------+
17. | 100 | 张三   | 11111 |
18. | 101 | 李四   | NULL  |
19. | 102 | 王五   | 22222 |
20. | 103 | 赵六   |       |
21. +-----+--------+-------+
22. 4 rows in set (0.00 sec)
24. -- 插入冲突更新
25. mysql> insert into student(id,name,qq) values(102,'周七', '33333');
26. ERROR 1062 (23000): Duplicate entry '102' for key 'student.PRIMARY'
28. mysql> insert into student(id,name,qq) values(102,'周七', '33333') on duplicate key update name = '周七', qq = '33333';
29. Query OK, 2 rows affected (0.00 sec)
31. mysql> select * from student;
32. +-----+--------+-------+
33. | id  | name   | qq    |
34. +-----+--------+-------+
35. | 100 | 张三   | 11111 |
36. | 101 | 李四   | NULL  |
37. | 102 | 周七   | 33333 |
38. | 103 | 赵六   |       |
39. +-----+--------+-------+
40. 4 rows in set (0.00 sec)
42. -- 替换
43. mysql> replace into student (id, name) VALUES (100, '田八');
44. Query OK, 2 rows affected (0.00 sec)
46. mysql> select * from student;
47. +-----+--------+-------+
48. | id  | name   | qq    |
49. +-----+--------+-------+
50. | 100 | 田八   | NULL  |
51. | 101 | 李四   | NULL  |
52. | 102 | 周七   | 33333 |
53. | 103 | 赵六   |       |
54. +-----+--------+-------+
55. 4 rows in set (0.00 sec)

复制代码

2. update

1. -- 将张三同学的数学成绩变更为 100 分
2. mysql> update exam_result set math = 100 where name = '张三';
3. Query OK, 1 row affected (0.00 sec)
4. Rows matched: 1  Changed: 1  Warnings: 0
6. mysql> select * from exam_result;
7. +----+--------+---------+------+---------+
8. | id | name   | chinese | math | english |
9. +----+--------+---------+------+---------+
10. |  2 | 张三   |      67 |  100 |      56 |
11. |  3 | 李四   |      87 |   78 |      77 |
12. |  4 | 王五   |      88 |   98 |      90 |
13. |  5 | 赵六   |      82 |   84 |      67 |
14. |  6 | 田八   |      70 |   73 |      78 |
15. |  7 | 刘九   |      75 |   65 |      30 |
16. +----+--------+---------+------+---------+
17. 6 rows in set (0.00 sec)
19. -- 将张三同学的数学成绩变更为 60 分，语文成绩变更为 70 分
20. mysql> update exam_result set math = 60,chinese = 70 where name = '张三';
21. Query OK, 1 row affected (0.01 sec)
22. Rows matched: 1  Changed: 1  Warnings: 0
24. mysql> select * from exam_result;
25. +----+--------+---------+------+---------+
26. | id | name   | chinese | math | english |
27. +----+--------+---------+------+---------+
28. |  2 | 张三   |      70 |   60 |      56 |
29. |  3 | 李四   |      87 |   78 |      77 |
30. |  4 | 王五   |      88 |   98 |      90 |
31. |  5 | 赵六   |      82 |   84 |      67 |
32. |  6 | 田八   |      70 |   73 |      78 |
33. |  7 | 刘九   |      75 |   65 |      30 |
34. +----+--------+---------+------+---------+
35. 6 rows in set (0.00 sec)
37. -- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
38. mysql> update exam_result set math = math + 30 order by chinese + math + english limit 3;
39. Query OK, 3 rows affected (0.01 sec)
40. Rows matched: 3  Changed: 3  Warnings: 0
42. mysql> select * from exam_result;
43. +----+--------+---------+------+---------+
44. | id | name   | chinese | math | english |
45. +----+--------+---------+------+---------+
46. |  2 | 张三   |      70 |   90 |      56 |
47. |  3 | 李四   |      87 |   78 |      77 |
48. |  4 | 王五   |      88 |   98 |      90 |
49. |  5 | 赵六   |      82 |   84 |      67 |
50. |  6 | 田八   |      70 |  103 |      78 |
51. |  7 | 刘九   |      75 |   95 |      30 |
52. +----+--------+---------+------+---------+
53. 6 rows in set (0.00 sec)
55. -- 将所有同学的语文成绩更新为原来的 2 倍
56. mysql> update exam_result set chinese = chinese * 2;
57. Query OK, 6 rows affected (0.00 sec)
58. Rows matched: 6  Changed: 6  Warnings: 0
60. mysql> select * from exam_result;
61. +----+--------+---------+------+---------+
62. | id | name   | chinese | math | english |
63. +----+--------+---------+------+---------+
64. |  2 | 张三   |     140 |   90 |      56 |
65. |  3 | 李四   |     174 |   78 |      77 |
66. |  4 | 王五   |     176 |   98 |      90 |
67. |  5 | 赵六   |     164 |   84 |      67 |
68. |  6 | 田八   |     140 |  103 |      78 |
69. |  7 | 刘九   |     150 |   95 |      30 |
70. +----+--------+---------+------+---------+
71. 6 rows in set (0.00 sec)

复制代码

3. delete

1. -- 删除张三同学的考试成绩
2. mysql> delete from exam_result where name = '张三';
3. Query OK, 1 row affected (0.00 sec)
5. mysql> select * from exam_result;
6. +----+--------+---------+------+---------+
7. | id | name   | chinese | math | english |
8. +----+--------+---------+------+---------+
9. |  3 | 李四   |     174 |   78 |      77 |
10. |  4 | 王五   |     176 |   98 |      90 |
11. |  5 | 赵六   |     164 |   84 |      67 |
12. |  6 | 田八   |     140 |  103 |      78 |
13. |  7 | 刘九   |     150 |   95 |      30 |
14. +----+--------+---------+------+---------+
15. 5 rows in set (0.00 sec)
17. -- 删除整张表
18. delete from for_delete;
19. Query OK, 3 rows affected (0.00 sec)
21. SELECT * FROM for_delete;
22. Empty set (0.00 sec)
24. -- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
25. truncate for_truncate;
26. Query OK, 0 rows affected (0.10 sec)
28. SELECT * FROM for_truncate;
29. Empty set (0.00 sec)
31. -- delete 和 truncate 的其中一个区别是truncate会清空auto_increment。

复制代码

4. select

这部分使用下面的表进行操作：

1. mysql> create table exam_result(
2.     -> id int primary key auto_increment,
3.     -> name varchar(20) not null,
4.     -> chinese float default 0,
5.     -> math float default 0,
6.     -> english float default 0
7.     -> );
8. Query OK, 0 rows affected (0.03 sec)
10. mysql> insert into exam_result(name,chinese,math,english) values('张三',67,98,56),
11.     -> ('李四',87,78,77);
12. Query OK, 2 rows affected (0.01 sec)
13. Records: 2  Duplicates: 0  Warnings: 0
15. mysql> insert into exam_result(name,chinese,math,english) values('王五', 88, 98, 90), ('赵六', 82, 84, 67),('田八', 70, 73, 78),('刘九', 75, 65, 30);
16. Query OK, 4 rows affected (0.01 sec)
17. Records: 4  Duplicates: 0  Warnings: 0

复制代码

4.1 根本查询 

4.1.1 简单查询

1. -- 全列查询
2. mysql> select * from exam_result;
3. +----+--------+---------+------+---------+
4. | id | name   | chinese | math | english |
5. +----+--------+---------+------+---------+
6. |  2 | 张三   |      67 |   98 |      56 |
7. |  3 | 李四   |      87 |   78 |      77 |
8. |  4 | 王五   |      88 |   98 |      90 |
9. |  5 | 赵六   |      82 |   84 |      67 |
10. |  6 | 田八   |      70 |   73 |      78 |
11. |  7 | 刘九   |      75 |   65 |      30 |
12. +----+--------+---------+------+---------+
13. 6 rows in set (0.00 sec)
15. -- 查询部分列
16. mysql> select id,name,chinese from exam_result;
17. +----+--------+---------+
18. | id | name   | chinese |
19. +----+--------+---------+
20. |  2 | 张三   |      67 |
21. |  3 | 李四   |      87 |
22. |  4 | 王五   |      88 |
23. |  5 | 赵六   |      82 |
24. |  6 | 田八   |      70 |
25. |  7 | 刘九   |      75 |
26. +----+--------+---------+
27. 6 rows in set (0.00 sec)
29. -- 查询字段为表达式
30. mysql> select id,name,chinese+math+english from exam_result;
31. +----+--------+----------------------+
32. | id | name   | chinese+math+english |
33. +----+--------+----------------------+
34. |  2 | 张三   |                  221 |
35. |  3 | 李四   |                  242 |
36. |  4 | 王五   |                  276 |
37. |  5 | 赵六   |                  233 |
38. |  6 | 田八   |                  221 |
39. |  7 | 刘九   |                  170 |
40. +----+--------+----------------------+
41. 6 rows in set (0.00 sec)
43. -- 为查询列指定别名
44. mysql> select id,name,chinese+math+english 总分 from exam_result;
45. +----+--------+--------+
46. | id | name   | 总分   |
47. +----+--------+--------+
48. |  2 | 张三   |    221 |
49. |  3 | 李四   |    242 |
50. |  4 | 王五   |    276 |
51. |  5 | 赵六   |    233 |
52. |  6 | 田八   |    221 |
53. |  7 | 刘九   |    170 |
54. +----+--------+--------+
55. 6 rows in set (0.00 sec)
57. -- 去重查询
58. mysql> select distinct math from exam_result;
59. +------+
60. | math |
61. +------+
62. |   98 |
63. |   78 |
64. |   84 |
65. |   73 |
66. |   65 |
67. +------+
68. 5 rows in set (0.01 sec)

复制代码

4.1.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是 NULLIS NOT NULL不是 NULLLIKE模糊匹配。% 表示任意多个（包罗 0 个）任意字符；_ 表示任意一个字符 逻辑运算符：
运算符说明AND多个条件必须都为 TRUE(1)，效果才是 TRUE(1)OR任意一个条件为 TRUE(1), 效果为 TRUE(1)NOT条件为 TRUE(1)，效果为 FALSE(0) 示例演示：

1. -- 英语不及格的同学及英语成绩 ( < 60 )
2. mysql> select name,english from exam_result where english < 60;
3. +--------+---------+
4. | name   | english |
5. +--------+---------+
6. | 张三   |      56 |
7. | 刘九   |      30 |
8. +--------+---------+
9. 2 rows in set (0.00 sec)
11. -- 语文成绩在 [80, 90] 分的同学及语文成绩
12. mysql> select name,chinese from exam_result where chinese between 80 and 90;
13. +--------+---------+
14. | name   | chinese |
15. +--------+---------+
16. | 李四   |      87 |
17. | 王五   |      88 |
18. | 赵六   |      82 |
19. +--------+---------+
20. 3 rows in set (0.00 sec)
22. mysql> select name,chinese from exam_result where chinese >= 80 and chinese <= 90;
23. +--------+---------+
24. | name   | chinese |
25. +--------+---------+
26. | 李四   |      87 |
27. | 王五   |      88 |
28. | 赵六   |      82 |
29. +--------+---------+
30. 3 rows in set (0.00 sec)
32. -- 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
33. mysql> select name,math from exam_result where math = 58 or math = 59 or math = 98 or math = 99;
34. +--------+------+
35. | name   | math |
36. +--------+------+
37. | 张三   |   98 |
38. | 王五   |   98 |
39. +--------+------+
40. 2 rows in set (0.00 sec)
42. mysql> select name,math from exam_result where math in(58,59,98,99);
43. +--------+------+
44. | name   | math |
45. +--------+------+
46. | 张三   |   98 |
47. | 王五   |   98 |
48. +--------+------+
49. 2 rows in set (0.01 sec)
51. -- 姓张的同学 及 赵某同学
52. mysql> select name from exam_result where name like '张%' or name like '赵_';
53. +--------+
54. | name   |
55. +--------+
56. | 张三   |
57. | 赵六   |
58. +--------+
59. 2 rows in set (0.00 sec)
61. -- 语文成绩好于英语成绩的同学
62. mysql> select name,chinese,english from exam_result where chinese > english;
63. +--------+---------+---------+
64. | name   | chinese | english |
65. +--------+---------+---------+
66. | 张三   |      67 |      56 |
67. | 李四   |      87 |      77 |
68. | 赵六   |      82 |      67 |
69. | 刘九   |      75 |      30 |
70. +--------+---------+---------+
71. 4 rows in set (0.00 sec)
73. -- 总分在 200 分以下的同学
74. mysql> select name,chinese+math+english from exam_result where chinese + math + english < 200;
75. +--------+----------------------+
76. | name   | chinese+math+english |
77. +--------+----------------------+
78. | 刘九   |                  170 |
79. +--------+----------------------+
80. 1 row in set (0.00 sec)
82. -- 语文成绩 > 80 并且不姓张的同学
83. mysql> select name,chinese from exam_result where chinese > 80 and name not like '张%';
84. +--------+---------+
85. | name   | chinese |
86. +--------+---------+
87. | 李四   |      87 |
88. | 王五   |      88 |
89. | 赵六   |      82 |
90. +--------+---------+
91. 3 rows in set (0.00 sec)
93. -- 张某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
94. mysql> select * from exam_result where name like '张%' or (chinese + math + english > 200 and chinese < math and english >80);
95. +----+--------+---------+------+---------+
96. | id | name   | chinese | math | english |
97. +----+--------+---------+------+---------+
98. |  2 | 张三   |      67 |   98 |      56 |
99. |  4 | 王五   |      88 |   98 |      90 |
100. +----+--------+---------+------+---------+
101. 2 rows in set (0.00 sec)

复制代码

4.1.3 order by

1. -- 同学及数学成绩，按数学成绩升序显示
2. mysql> select name,math from exam_result order by math;
3. +--------+------+
4. | name   | math |
5. +--------+------+
6. | 刘九   |   65 |
7. | 田八   |   73 |
8. | 李四   |   78 |
9. | 赵六   |   84 |
10. | 张三   |   98 |
11. | 王五   |   98 |
12. +--------+------+
13. 6 rows in set (0.01 sec)
15. -- 同学及总分，按总分排序显示
16. mysql> select name,chinese + math + english 总分 from exam_result order by 总分;
17. +--------+--------+
18. | name   | 总分   |
19. +--------+--------+
20. | 刘九   |    170 |
21. | 张三   |    221 |
22. | 田八   |    221 |
23. | 赵六   |    233 |
24. | 李四   |    242 |
25. | 王五   |    276 |
26. +--------+--------+
27. 6 rows in set (0.00 sec)
29. -- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
30. mysql> select * from exam_result order by math desc,english,chinese;
31. +----+--------+---------+------+---------+
32. | id | name   | chinese | math | english |
33. +----+--------+---------+------+---------+
34. |  2 | 张三   |      67 |   98 |      56 |
35. |  4 | 王五   |      88 |   98 |      90 |
36. |  5 | 赵六   |      82 |   84 |      67 |
37. |  3 | 李四   |      87 |   78 |      77 |
38. |  6 | 田八   |      70 |   73 |      78 |
39. |  7 | 刘九   |      75 |   65 |      30 |
40. +----+--------+---------+------+---------+
41. 6 rows in set (0.00 sec)
43. -- 查询姓张的同学或者姓刘的同学数学成绩，结果按数学成绩由高到低显示
44. mysql> select * from exam_result where name like '张%' or name like '刘%' order by math desc;
45. +----+--------+---------+------+---------+
46. | id | name   | chinese | math | english |
47. +----+--------+---------+------+---------+
48. |  2 | 张三   |      67 |   98 |      56 |
49. |  7 | 刘九   |      75 |   65 |      30 |
50. +----+--------+---------+------+---------+
51. 2 rows in set (0.00 sec)

复制代码

4.1.4 limit offset

对查询效果进行分页处置处罚：

1. mysql> select * from exam_result limit 3 offset 0;
2. +----+--------+---------+------+---------+
3. | id | name   | chinese | math | english |
4. +----+--------+---------+------+---------+
5. |  2 | 张三   |      67 |   98 |      56 |
6. |  3 | 李四   |      87 |   78 |      77 |
7. |  4 | 王五   |      88 |   98 |      90 |
8. +----+--------+---------+------+---------+
9. 3 rows in set (0.00 sec)
11. mysql> select * from exam_result limit 3 offset 3;
12. +----+--------+---------+------+---------+
13. | id | name   | chinese | math | english |
14. +----+--------+---------+------+---------+
15. |  5 | 赵六   |      82 |   84 |      67 |
16. |  6 | 田八   |      70 |   73 |      78 |
17. |  7 | 刘九   |      75 |   65 |      30 |
18. +----+--------+---------+------+---------+
19. 3 rows in set (0.00 sec)
21. mysql> select * from exam_result limit 3 offset 6;
22. Empty set (0.00 sec)

复制代码

4.1.5 insert into

1. mysql> insert into son_table select * from exam_result;
2. Query OK, 5 rows affected (0.01 sec)
3. Records: 5  Duplicates: 0  Warnings: 0
5. mysql> select * from son_table;
6. +----+--------+---------+------+---------+
7. | id | name   | chinese | math | english |
8. +----+--------+---------+------+---------+
9. |  3 | 李四   |     174 |   78 |      77 |
10. |  4 | 王五   |     176 |   98 |      90 |
11. |  5 | 赵六   |     164 |   84 |      67 |
12. |  6 | 田八   |     140 |  103 |      78 |
13. |  7 | 刘九   |     150 |   95 |      30 |
14. +----+--------+---------+------+---------+
15. 5 rows in set (0.00 sec)

复制代码

4.1.6 group by

在select中使用group by 子句可以对指定列进行分组查询

1. -- 显示每个部门的平均工资和最高工资
2. select deptno,avg(sal),max(sal) from EMP group by deptno;
4. -- 每个部门的每种岗位的平均工资和最低工资
5. select avg(sal),min(sal),job, deptno from EMP group by deptno, job;

复制代码

4.2 复合查询

4.2.1 多表连接

接下来我们使用下面两张表来进行查询：

• 表现雇员名、雇员工资以及所在部门的名字

1. select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno = DEPT.deptno;

复制代码

• 表现部门号为10的部门名，员工名和工资

1. select ename, sal,dname from EMP, DEPT where EMP.deptno = DEPT.deptno and DEPT.deptno = 10;

复制代码

• 表现各个员工的姓名，工资，及工资级别

1. select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;

复制代码

4.2.2 自连接

• 表现员工FORD的上级领导的编号和姓名

1. -- 使用的子查询
2. select empno,ename from emp where emp.empno = (select mgr from emp where ename = 'FORD');
4. -- 使用多表查询（自查询）
5. --from emp leader, emp worker，给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识别
6. select leader.empno,leader.ename from emp leader, emp worker where
7.     leader.empno = worker.mgr and worker.ename='FORD';

复制代码

4.2.3 子查询

• 表现SMITH同一部门的员工

1. select * from EMP WHERE deptno = (select deptno from EMP where ename = 'smith');

复制代码

• 查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

1. select ename,job,sal,deptno from emp where
2. job in (select distinct job from emp where deptno = 10) and deptno<>10;

复制代码

• 表现工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

1. select ename, sal, deptno from EMP
2. where sal > all(select sal from EMP where deptno = 30);

复制代码

• 表现工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

1. select ename, sal, deptno from EMP
2. where sal > any(select sal from EMP where deptno = 30);

复制代码

• 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

1. select ename from EMP
2. where (deptno, job) = (select deptno, job from EMP where ename = 'SMITH') and ename <> 'SMITH';

复制代码

• 表现每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

1. select ename, deptno, sal, format(asal,2) from EMP,
2. (select avg(sal) asal, deptno dt from EMP group by deptno) tmp
3. where EMP.sal > tmp.asal and EMP.deptno = tmp.dt;

复制代码

• 查找每个部门工资最高的人的姓名、工资、部门、最高工资

1. select EMP.ename, EMP.sal, EMP.deptno, ms from EMP,
2. (select max(sal) ms, deptno from EMP group by deptno) tmp
3. where EMP.deptno=tmp.deptno and EMP.sal=tmp.ms;

复制代码

4.2.4 归并查询

• 将工资大于2500或职位是MANAGER的人找出来

1. select ename, sal, job from EMP
2. where sal > 2500
3. union
4. select ename, sal, job from EMP
5. where job = 'MANAGER';--去掉了重复记录

复制代码

• 将工资大于25000或职位是MANAGER的人找出来

1. select ename, sal, job from EMP
2. where sal > 2500
3. union all
4. select ename, sal, job from EMP
5. where job = 'MANAGER';

复制代码

4.3 表里连接

4.3.1 内连接

内连接实际上就是使用where子句对两种表形成的笛卡儿积进行筛选。
• 表现SMITH的名字和部门名称

1. -- 用前面的写法
2. select ename, dname from EMP, DEPT
3. where EMP.deptno = DEPT.deptno and ename = 'SMITH';
4. -- 用标准的内连接写法
5. select ename, dname from EMP inner
6. join DEPT on EMP.deptno = DEPT.deptno and ename = 'SMITH';

复制代码

4.3.2 外连接

左外连接
如果连合查询，左侧的表完全表现我们就说是左外连接
• 查询所有门生的成绩，如果这个门生没有成绩，也要将门生的个人信息表现出来

1. select * from stu left join exam on stu.id = exam.id;

复制代码

右外连接
如果连合查询，右侧的表完全表现我们就说是右外连接。
• 对stu表和exam表连合查询，把所有的成绩都表现出来，即使这个成绩没有门生与它对应，也要表现出来

1. select * from stu right join exam on stu.id = exam.id;

复制代码