MySQL之单表查询、多表查询

怼烎@ 2022-01-30 02:47 397阅读 0赞

**一、单表查询：**

**单个表的查询方法及语法顺序需要通过实际例子来熟悉**

1.  mysql> create database singe\_t1; \# 建个数据库singe\_t1
2.  Query OK, 1 row affected (0.01 sec)
3.   
4.  mysql> use singe\_t1 \# 进入数据库singe\_t1
5.  Database changed
6.  mysql> create table emp( \# 创建表emp
7.  \-> id int not null unique auto\_increment,
8.  \-> name varchar(20) not null,
9.  \-> sex enum('male','female') not null default 'male',
10. \-> age int(3) unsigned not null default 28,
11. \-> hire\_date date not null,
12. \-> post varchar(50),
13. \-> post\_comment varchar(100),
14. \-> salary int unsigned,
15. \-> office int,
16. \-> depart\_id int
17. \-> );
18. Query OK, 0 rows affected (0.05 sec)
19. mysql> desc emp; \# 查看创建的表结构
20. \+--------------+-----------------------+------+-----+---------+----------------+
21. | Field | Type | Null | Key | Default | Extra |
22. \+--------------+-----------------------+------+-----+---------+----------------+
23. | id | int(11) | NO | PRI | NULL | auto\_increment |
24. | name | varchar(20) | NO | | NULL | |
25. | sex | enum('male','female') | NO | | male | |
26. | age | int(3) unsigned | NO | | 28 | |
27. | hire\_date | date | NO | | NULL | |
28. | post | varchar(50) | YES | | NULL | |
29. | post\_comment | varchar(100) | YES | | NULL | |
30. | salary | int(10) unsigned | YES | | NULL | |
31. | office | int(11) | YES | | NULL | |
32. | depart\_id | int(11) | YES | | NULL | |
33. \+--------------+-----------------------+------+-----+---------+----------------+
34. \# 开始插入表记录
35. mysql> insert into emp(name,sex,age,hire\_date,post,salary,office,d
36. \-> ('jason','male',18,'20170301','外交部',6600,401,1), \#以下是教学部
37. \-> ('egon','male',78,'20150302','教学部',1000000,401,1),
38. \-> ('kevin','male',81,'20130305','教学部',8300,401,1),
39. \-> ('tank','male',73,'20140701','教学部',3500,401,1),
40. \-> ('owen','male',28,'20121101','教学部',2100,401,1),
41. \-> ('jerry','female',18,'20110211','教学部',9000,401,1),
42. \-> ('nick','male',18,'19000301','教学部',30000,401,1),
43. \-> ('sean','male',48,'20101111','教学部',10000,401,1),
44. \->
45. \-> ('歪歪','female',48,'20150311','销售部',3000,402,2),\#以下是销售部门
46. \-> ('丫丫','female',38,'20101101','销售部',2000,402,2),
47. \-> ('丁丁','female',18,'20110312','销售部',1000,402,2),
48. \-> ('星星','female',18,'20160513','销售部',3000,402,2),
49. \-> ('格格','female',28,'20170127','销售部',4000,402,2),
50. \->
51. \-> ('张野','male',28,'20160311','运营部',10000,403,3), \#以下是运营部门
52. \-> ('程咬金','male',18,'19970312','运营部',20000,403,3),
53. \-> ('程咬银','female',18,'20130311','运营部',19000,403,3),
54. \-> ('程咬铜','male',18,'20150411','运营部',18000,403,3),
55. \-> ('程咬铁','female',18,'20140512','运营部',17000,403,3)
56. \-> ;
57. Query OK, 18 rows affected (0.01 sec)
58. Records: 18 Duplicates: 0 Warnings: 0
59. \# 最终结果：
60. mysql> select \* from emp;
61. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
62. | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
63. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
64. | 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
65. | 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
66. | 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
67. | 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
68. | 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
69. | 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
70. | 7 | nick | male | 18 | 1900-03-01 | 教学部 | NULL | 30000 | 401 | 1 |
71. | 8 | sean | male | 48 | 2010-11-11 | 教学部 | NULL | 10000 | 401 | 1 |
72. | 9 | 歪歪 | female | 48 | 2015-03-11 | 销售部 | NULL | 3000 | 402 | 2 |
73. | 10 | 丫丫 | female | 38 | 2010-11-01 | 销售部 | NULL | 2000 | 402 | 2 |
74. | 11 | 丁丁 | female | 18 | 2011-03-12 | 销售部 | NULL | 1000 | 402 | 2 |
75. | 12 | 星星 | female | 18 | 2016-05-13 | 销售部 | NULL | 3000 | 402 | 2 |
76. | 13 | 格格 | female | 28 | 2017-01-27 | 销售部 | NULL | 4000 | 402 | 2 |
77. | 14 | 张野 | male | 28 | 2016-03-11 | 运营部 | NULL | 10000 | 403 | 3 |
78. | 15 | 程咬金 | male | 18 | 1997-03-12 | 运营部 | NULL | 20000 | 403 | 3 |
79. | 16 | 程咬银 | female | 18 | 2013-03-11 | 运营部 | NULL | 19000 | 403 | 3 |
80. | 17 | 程咬铜 | male | 18 | 2015-04-11 | 运营部 | NULL | 18000 | 403 | 3 |
81. | 18 | 程咬铁 | female | 18 | 2014-05-12 | 运营部 | NULL | 17000 | 403 | 3 |
82. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+

①、语法执行顺序

1.  \# 最基本的查询语句：
2.  mysql> select \* from emp where id >= 3 and id <= 6;
3.  \+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
4.  | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
5.  \+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
6.  | 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
7.  | 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
8.  | 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
9.  | 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
10. \+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
11. \# 基本语句语法的解析：（先后顺序）
12. from------------> where----------------> select
13. 找到表 查询数据的条件依据 找到数据形成虚拟表

②、where约束条件的使用

1.  \# 1.查询id大于等于3小于等于6的数据
2.  mysql> select \* from emp where id >= 3 and id <= 6;
3.  mysql> select \* from emp where id between 3 and 6;
4.  \+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
5.  | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
6.  \+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
7.  | 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
8.  | 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
9.  | 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
10. | 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
11. \+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
12. \# 2.查询薪资是20000或者18000或者17000的数据
13. mysql> select \* from emp where salary in (20000,18000,17000);
14. mysql> select \* from emp where salary=20000 or salary=18000 or salary=17000;
15. \+----+-----------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
16. | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
17. \+----+-----------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
18. | 15 | 程咬金 | male | 18 | 1997-03-12 | 运营部 | NULL | 20000 | 403 | 3 |
19. | 17 | 程咬铜 | male | 18 | 2015-04-11 | 运营部 | NULL | 18000 | 403 | 3 |
20. | 18 | 程咬铁 | female | 18 | 2014-05-12 | 运营部 | NULL | 17000 | 403 | 3 |
21. \+----+-----------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
22. \# 3.查询员工姓名中包含o字母的员工姓名和薪资
23. mysql> select name,salary from emp where name like '%o%';
24. \+-------+---------+
25. | name | salary |
26. \+-------+---------+
27. | jason | 6600 |
28. | egon | 1000000 |
29. | owen | 2100 |
30. \+-------+---------+
31. \# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
32. mysql> select name,salary from emp where name like '\_\_\_\_'; \# like语句里面下划线代表单个通配字符
33. mysql> select name,salary from emp where char\_length(name)=4; \# 或者通过计算name字段的长度为4来查询
34. \+------+---------+
35. | name | salary |
36. \+------+---------+
37. | egon | 1000000 |
38. | tank | 3500 |
39. | owen | 2100 |
40. | nick | 30000 |
41. | sean | 10000 |
42. \+------+---------+
43. \# 5.查询id小于3或者大于6的数据
44. mysql> select \* from emp where id not between 3 and 6; \# 运用between语句
45. mysql> select \* from emp where id < 3 or id >6; \# 运用or语句
46. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
47. | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
48. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
49. | 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
50. | 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
51. | 7 | nick | male | 18 | 1900-03-01 | 教学部 | NULL | 30000 | 401 | 1 |
52. | 8 | sean | male | 48 | 2010-11-11 | 教学部 | NULL | 10000 | 401 | 1 |
53. | 9 | 歪歪 | female | 48 | 2015-03-11 | 销售部 | NULL | 3000 | 402 | 2 |
54. | 10 | 丫丫 | female | 38 | 2010-11-01 | 销售部 | NULL | 2000 | 402 | 2 |
55. | 11 | 丁丁 | female | 18 | 2011-03-12 | 销售部 | NULL | 1000 | 402 | 2 |
56. | 12 | 星星 | female | 18 | 2016-05-13 | 销售部 | NULL | 3000 | 402 | 2 |
57. | 13 | 格格 | female | 28 | 2017-01-27 | 销售部 | NULL | 4000 | 402 | 2 |
58. | 14 | 张野 | male | 28 | 2016-03-11 | 运营部 | NULL | 10000 | 403 | 3 |
59. | 15 | 程咬金 | male | 18 | 1997-03-12 | 运营部 | NULL | 20000 | 403 | 3 |
60. | 16 | 程咬银 | female | 18 | 2013-03-11 | 运营部 | NULL | 19000 | 403 | 3 |
61. | 17 | 程咬铜 | male | 18 | 2015-04-11 | 运营部 | NULL | 18000 | 403 | 3 |
62. | 18 | 程咬铁 | female | 18 | 2014-05-12 | 运营部 | NULL | 17000 | 403 | 3 |
63. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
64. \# 6.查询薪资不在20000，18000，17000范围的数据
65. mysql> select \* from emp where salary not in (20000,18000,17000);
66. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
67. | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
68. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
69. | 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
70. | 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
71. | 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
72. | 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
73. | 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
74. | 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
75. | 7 | nick | male | 18 | 1900-03-01 | 教学部 | NULL | 30000 | 401 | 1 |
76. | 8 | sean | male | 48 | 2010-11-11 | 教学部 | NULL | 10000 | 401 | 1 |
77. | 9 | 歪歪 | female | 48 | 2015-03-11 | 销售部 | NULL | 3000 | 402 | 2 |
78. | 10 | 丫丫 | female | 38 | 2010-11-01 | 销售部 | NULL | 2000 | 402 | 2 |
79. | 11 | 丁丁 | female | 18 | 2011-03-12 | 销售部 | NULL | 1000 | 402 | 2 |
80. | 12 | 星星 | female | 18 | 2016-05-13 | 销售部 | NULL | 3000 | 402 | 2 |
81. | 13 | 格格 | female | 28 | 2017-01-27 | 销售部 | NULL | 4000 | 402 | 2 |
82. | 14 | 张野 | male | 28 | 2016-03-11 | 运营部 | NULL | 10000 | 403 | 3 |
83. | 16 | 程咬银 | female | 18 | 2013-03-11 | 运营部 | NULL | 19000 | 403 | 3 |
84. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
85. \# 7.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
86. mysql> select name,post from emp where post\_comment is null;
87. \+-----------+-----------+
88. | name | post |
89. \+-----------+-----------+
90. | jason | 外交部 |
91. | egon | 教学部 |
92. | kevin | 教学部 |
93. | tank | 教学部 |
94. | owen | 教学部 |
95. | jerry | 教学部 |
96. | nick | 教学部 |
97. | sean | 教学部 |
98. | 歪歪 | 销售部 |
99. | 丫丫 | 销售部 |
100. | 丁丁 | 销售部 |
101. | 星星 | 销售部 |
102. | 格格 | 销售部 |
103. | 张野 | 运营部 |
104. | 程咬金 | 运营部 |
105. | 程咬银 | 运营部 |
106. | 程咬铜 | 运营部 |
107. | 程咬铁 | 运营部 |
108. \+-----------+-----------+

③、group by 分组

1.  \# 数据有时候都有相似性，这个相似性有时候就是我们查询同一类数据的依据，大部分情况下，数据的相似可以说就是数据分组的意义所在，比如每个部门，男女、每个地方、、、等等。
2.  \# 按部门分组
3.  mysql> select \* from emp group by post;
4.  \+----+--------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
5.  | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
6.  \+----+--------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
7.  | 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
8.  | 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
9.  | 14 | 张野 | male | 28 | 2016-03-11 | 运营部 | NULL | 10000 | 403 | 3 |
10. | 9 | 歪歪 | female | 48 | 2015-03-11 | 销售部 | NULL | 3000 | 402 | 2 |
11. \+----+--------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
12. mysql> select id,name,sex from emp group by post;
13. \+----+--------+--------+
14. | id | name | sex |
15. \+----+--------+--------+
16. | 1 | jason | male |
17. | 2 | egon | male |
18. | 14 | 张野 | male |
19. | 9 | 歪歪 | female |
20. \+----+--------+--------+
21. 可以看出以上按部门分组取出的数据都是能找到的部门分组数据的第一条，这样做肯定是不合理的，因为我只是要查询分组的数据，你给我每个分组的第一条id的数据有何用。
22. 所以需要设置sql\_model 为only\_full\_group\_by,这样意味着以后但凡分组，只能取到分组的依据，不应该去取组里面的单个元素的值。
23. mysql> set global sql\_mode='strict\_trans\_tables,only\_full\_group\_by'; \# 设置分组严格模式
24. mysql> exit \# 退出才能生效
25. Bye
26. mysql -uroot -p
27. Enter password: \*\*\*\*\*\*
28. ......
29. mysql> use singe\_t1
30. Database changed
31. mysql> show variables like '%sql\_mode%'; \# 查看是否生效
32. \+---------------+----------------------------------------+
33. | Variable\_name | Value |
34. \+---------------+----------------------------------------+
35. | sql\_mode | ONLY\_FULL\_GROUP\_BY,STRICT\_TRANS\_TABLES |
36. \+---------------+----------------------------------------+
37. \# 此时如果按照以前的分组查询方式就会报错
38. mysql> select \* from emp group by post;
39. ERROR 1055 (42000): Expression \#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'singe\_t1.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by
40. \# 必须 按分组查询只显示post字段名才行：
41. mysql> select post from emp group by post;
42. \+-----------+
43. | post |
44. \+-----------+
45. | 外交部 |
46. | 教学部 |
47. | 运营部 |
48. | 销售部 |
49. \+-----------+
50. \# 强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名
51. \-------------------------------------------------------------------------------
52. 接下来开始用实例来熟悉分组的运用：
53. \# 2.获取每个部门的最高工资
54. \# 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
55. \# 每个部门的最高工资
56. mysql> select post,max(salary) from emp group by post;
57. \+-----------+-------------+
58. | post | max(salary) |
59. \+-----------+-------------+
60. | 外交部 | 6600 |
61. | 教学部 | 1000000 |
62. | 运营部 | 20000 |
63. | 销售部 | 4000 |
64. \+-----------+-------------+
65. \# 每个部门的最低工资
66. mysql> select post,min(salary) from emp group by post;
67. \+-----------+-------------+
68. | post | min(salary) |
69. \+-----------+-------------+
70. | 外交部 | 6600 |
71. | 教学部 | 2100 |
72. | 运营部 | 10000 |
73. | 销售部 | 1000 |
74. \+-----------+-------------+
75. \# 每个部门的平均工资
76. mysql> select post,avg(salary) from emp group by post;
77. \+-----------+-------------+
78. | post | avg(salary) |
79. \+-----------+-------------+
80. | 外交部 | 6600.0000 |
81. | 教学部 | 151842.8571 |
82. | 运营部 | 16800.0000 |
83. | 销售部 | 2600.0000 |
84. \+-----------+-------------+
85. \# 每个部门的工资总和
86. mysql> select post,sum(salary) from emp group by post;
87. \+-----------+-------------+
88. | post | sum(salary) |
89. \+-----------+-------------+
90. | 外交部 | 6600 |
91. | 教学部 | 1062900 |
92. | 运营部 | 84000 |
93. | 销售部 | 13000 |
94. \+-----------+-------------+
95. \# 每个部门的人数
96. mysql> select post,count(id) from emp group by post;
97. \+-----------+-----------+
98. | post | count(id) |
99. \+-----------+-----------+
100. | 外交部 | 1 |
101. | 教学部 | 7 |
102. | 运营部 | 5 |
103. | 销售部 | 5 |
104. \+-----------+-----------+
105. \-------------------------------------------------------------------------------
106. \# 3.查询分组之后的部门名称和每个部门下所有的学生姓名
107. \# group\_concat（分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用
108. mysql> select post,group\_concat(name) from emp group by post;
109. \+-----------+------------------------------------------------+
110. | post | group\_concat(name) |
111. \+-----------+------------------------------------------------+
112. | 外交部 | jason |
113. | 教学部 | egon,kevin,tank,owen,jerry,nick,sean |
114. | 运营部 | 张野,程咬金,程咬银,程咬铜,程咬铁 |
115. | 销售部 | 歪歪,丫丫,丁丁,星星,格格 |
116. \+-----------+------------------------------------------------+
117.  
118. mysql> select post,group\_concat(name) as 部门全部人员 from emp group by post;
119. \+-----------+------------------------------------------------+
120. | post | 部门全部人员 |
121. \+-----------+------------------------------------------------+
122. | 外交部 | jason |
123. | 教学部 | egon,kevin,tank,owen,jerry,nick,sean |
124. | 运营部 | 张野,程咬金,程咬银,程咬铜,程咬铁 |
125. | 销售部 | 歪歪,丫丫,丁丁,星星,格格 |
126. \+-----------+------------------------------------------------+
127. mysql> select post,group\_concat(name,'\_\_') from emp group by post;
128. \+-----------+----------------------------------------------------------+
129. | post | group\_concat(name,'\_\_') |
130. \+-----------+----------------------------------------------------------+
131. | 外交部 | jason\_\_ |
132. | 教学部 | egon\_\_,kevin\_\_,tank\_\_,owen\_\_,jerry\_\_,nick\_\_,sean\_\_ |
133. | 运营部 | 张野\_\_,程咬金\_\_,程咬银\_\_,程咬铜\_\_,程咬铁\_\_ |
134. | 销售部 | 歪歪\_\_,丫丫\_\_,丁丁\_\_,星星\_\_,格格\_\_ |
135. \+-----------+----------------------------------------------------------+
136. mysql> select post,group\_concat(name,':',salary) from emp group by post;
137. \+-----------+------------------------------------------------------------------------------+
138. | post | group\_concat(name,':',salary) |
139. \+-----------+------------------------------------------------------------------------------+
140. | 外交部 | jason:6600 |
141. | 教学部 | egon:1000000,kevin:8300,tank:3500,owen:2100,jerry:9000,nick:30000,sean:10000 |
142. | 运营部 | 张野:10000,程咬金:20000,程咬银:19000,程咬铜:18000,程咬铁:17000 |
143. | 销售部 | 歪歪:3000,丫丫:2000,丁丁:1000,星星:3000,格格:4000 |
144. \+-----------+------------------------------------------------------------------------------+
145. mysql> select post,group\_concat(salary) as 薪资 from emp group by post;
146. \+-----------+-----------------------------------------+
147. | post | 薪资 |
148. \+-----------+-----------------------------------------+
149. | 外交部 | 6600 |
150. | 教学部 | 1000000,8300,3500,2100,9000,30000,10000 |
151. | 运营部 | 10000,20000,19000,18000,17000 |
152. | 销售部 | 3000,2000,1000,3000,4000 |
153. \+-----------+-----------------------------------------+
154. \----------------------------------------------------------------------------
155. \# 4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法使用(concat\_ws(':',字段1，字段2，字段3)用冒号拼接后面字段）
156. mysql> select name as 姓名,salary as 薪资 from emp where post='教学部';
157. \+--------+---------+
158. | 姓名 | 薪资 |
159. \+--------+---------+
160. | egon | 1000000 |
161. | kevin | 8300 |
162. | tank | 3500 |
163. | owen | 2100 |
164. | jerry | 9000 |
165. | nick | 30000 |
166. | sean | 10000 |
167. \+--------+---------+
168. mysql> select concat('姓名:',name) 姓名,concat('工资:',salary) as 薪资 from emp where post='教学部';
169. \+--------------+----------------+
170. | 姓名 | 薪资 |
171. \+--------------+----------------+
172. | 姓名:egon | 工资:1000000 |
173. | 姓名:kevin | 工资:8300 |
174. | 姓名:tank | 工资:3500 |
175. | 姓名:owen | 工资:2100 |
176. | 姓名:jerry | 工资:9000 |
177. | 姓名:nick | 工资:30000 |
178. | 姓名:sean | 工资:10000 |
179. \+--------------+----------------+
180. \# 补充as语法 即可以给字段起别名也可以给表起
181. mysql> create table t2(id int,name char(16)); \# 建个t2表
182. Query OK, 0 rows affected (0.03 sec)
183. mysql> show tables; \# 该数据库内有2个表
184. \+--------------------+
185. | Tables\_in\_singe\_t1 |
186. \+--------------------+
187. | emp |
188. | t2 |
189. \+--------------------+
190. mysql> select emp.id,emp.name from emp as t1; \# 如果将emp起名为t1，那么前面的emp也得改为起名的t1才能找到id和name，否则报错
191. ERROR 1054 (42S22): Unknown column 'emp.id' in 'field list'
192. mysql> select t1.id,t1.name from emp as t1 where id between 1 and 6;
193. \+----+-------+
194. | id | name |
195. \+----+-------+
196. | 1 | jason |
197. | 2 | egon |
198. | 3 | kevin |
199. | 4 | tank |
200. | 5 | owen |
201. | 6 | jerry |
202. \+----+-------+
203. 小测试：当把表名起名为库中已有的表t2呢？是否会报错？
204. mysql> select t2.id,t2.name from emp as t2 where id between 1 and 6;
205. \+----+-------+
206. | id | name |
207. \+----+-------+
208. | 1 | jason |
209. | 2 | egon |
210. | 3 | kevin |
211. | 4 | tank |
212. | 5 | owen |
213. | 6 | jerry |
214. \+----+-------+
215. 结果是不会报错
216. 分析：查询表应该是将结果放进内存中然后显示的，这样起名只是临时性的，显示的结果也是临时的，所以和数据库中实际表名没有关系。只在查询那段语句中起作用。互不影响。
217. \--------------------------------------------------------------------------------
218. \# 查询四则运算
219. \# 查询每个人的年薪
220. mysql> select name as 员工,salary\*12 as 年薪 from emp where id between 1 and 6;
221. \+--------+----------+
222. | 员工 | 年薪 |
223. \+--------+----------+
224. | jason | 79200 |
225. | egon | 12000000 |
226. | kevin | 99600 |
227. | tank | 42000 |
228. | owen | 25200 |
229. | jerry | 108000 |
230. \+--------+----------+
231. 友情提醒：上述的起名语句中的as可以省略，但是省略后会减弱查询语句的可读性，建议还是不省略好。

④、having （类似于where，但是必须在group by语句后使用）

having的语法格式与where一致，只不过having是在分组之后进行的过滤，即where虽然不能用聚合函数，但是having可以！

1.  1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
2.  mysql> select post,avg(salary) from emp where age > 30 group by post having avg(salary)>10000;
3.  \+-----------+-------------+
4.  | post | avg(salary) |
5.  \+-----------+-------------+
6.  | 教学部 | 255450.0000 |
7.  \+-----------+-------------+

5、distinct 去重

对有重复的展示数据进行去重操作

1.  mysql> create table tt1(id int,name char(16));
2.  mysql> insert into tt1 values (1,'张三'),(2,'王五'),(2,'王五'),(3,'张三'),(1,'赵六');
3.  mysql> select \* from tt1;
4.  \+------+--------+
5.  | id | name |
6.  \+------+--------+
7.  | 1 | 张三 |
8.  | 2 | 王五 |
9.  | 2 | 王五 |
10. | 3 | 张三 |
11. | 1 | 赵六 |
12. \+------+--------+
13. mysql> select distinct \* from tt1;
14. \+------+--------+
15. | id | name |
16. \+------+--------+
17. | 1 | 张三 |
18. | 2 | 王五 |
19. | 3 | 张三 |
20. | 1 | 赵六 |
21. \+------+--------+
22. mysql> select distinct name from tt1;
23. \+--------+
24. | name |
25. \+--------+
26. | 张三 |
27. | 王五 |
28. | 赵六 |
29. \+--------+
30. mysql> select distinct id from tt1;
31. \+------+
32. | id |
33. \+------+
34. | 1 |
35. | 2 |
36. | 3 |
37. \+------+

6、order by (排序）

1.  mysql> select \* from emp order by salary; \# 按照工资升序排序
2.  \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
3.  | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
4.  \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
5.  | 11 | 丁丁 | female | 18 | 2011-03-12 | 销售部 | NULL | 1000 | 402 | 2 |
6.  | 10 | 丫丫 | female | 38 | 2010-11-01 | 销售部 | NULL | 2000 | 402 | 2 |
7.  | 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
8.  | 9 | 歪歪 | female | 48 | 2015-03-11 | 销售部 | NULL | 3000 | 402 | 2 |
9.  | 12 | 星星 | female | 18 | 2016-05-13 | 销售部 | NULL | 3000 | 402 | 2 |
10. | 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
11. | 13 | 格格 | female | 28 | 2017-01-27 | 销售部 | NULL | 4000 | 402 | 2 |
12. | 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
13. | 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
14. | 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
15. | 8 | sean | male | 48 | 2010-11-11 | 教学部 | NULL | 10000 | 401 | 1 |
16. | 14 | 张野 | male | 28 | 2016-03-11 | 运营部 | NULL | 10000 | 403 | 3 |
17. | 18 | 程咬铁 | female | 18 | 2014-05-12 | 运营部 | NULL | 17000 | 403 | 3 |
18. | 17 | 程咬铜 | male | 18 | 2015-04-11 | 运营部 | NULL | 18000 | 403 | 3 |
19. | 16 | 程咬银 | female | 18 | 2013-03-11 | 运营部 | NULL | 19000 | 403 | 3 |
20. | 15 | 程咬金 | male | 18 | 1997-03-12 | 运营部 | NULL | 20000 | 403 | 3 |
21. | 7 | nick | male | 18 | 1900-03-01 | 教学部 | NULL | 30000 | 401 | 1 |
22. | 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
23. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
24. mysql> select \* from emp order by age,salary; \# 首选排序规则为age，当排完序的age中有相同的时，在相同age的几个记录中进行salary排序。
25. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
26. | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
27. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
28. | 11 | 丁丁 | female | 18 | 2011-03-12 | 销售部 | NULL | 1000 | 402 | 2 |
29. | 12 | 星星 | female | 18 | 2016-05-13 | 销售部 | NULL | 3000 | 402 | 2 |
30. | 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
31. | 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
32. | 18 | 程咬铁 | female | 18 | 2014-05-12 | 运营部 | NULL | 17000 | 403 | 3 |
33. | 17 | 程咬铜 | male | 18 | 2015-04-11 | 运营部 | NULL | 18000 | 403 | 3 |
34. | 16 | 程咬银 | female | 18 | 2013-03-11 | 运营部 | NULL | 19000 | 403 | 3 |
35. | 15 | 程咬金 | male | 18 | 1997-03-12 | 运营部 | NULL | 20000 | 403 | 3 |
36. | 7 | nick | male | 18 | 1900-03-01 | 教学部 | NULL | 30000 | 401 | 1 |
37. | 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
38. | 13 | 格格 | female | 28 | 2017-01-27 | 销售部 | NULL | 4000 | 402 | 2 |
39. | 14 | 张野 | male | 28 | 2016-03-11 | 运营部 | NULL | 10000 | 403 | 3 |
40. | 10 | 丫丫 | female | 38 | 2010-11-01 | 销售部 | NULL | 2000 | 402 | 2 |
41. | 9 | 歪歪 | female | 48 | 2015-03-11 | 销售部 | NULL | 3000 | 402 | 2 |
42. | 8 | sean | male | 48 | 2010-11-11 | 教学部 | NULL | 10000 | 401 | 1 |
43. | 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
44. | 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
45. | 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
46. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
47. \# 默认为升序，如果想要降序，在后面加上desc。
48. mysql> select \* from emp order by salary desc;
49. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
50. | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
51. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
52. | 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
53. | 7 | nick | male | 18 | 1900-03-01 | 教学部 | NULL | 30000 | 401 | 1 |
54. | 15 | 程咬金 | male | 18 | 1997-03-12 | 运营部 | NULL | 20000 | 403 | 3 |
55. | 16 | 程咬银 | female | 18 | 2013-03-11 | 运营部 | NULL | 19000 | 403 | 3 |
56. | 17 | 程咬铜 | male | 18 | 2015-04-11 | 运营部 | NULL | 18000 | 403 | 3 |
57. | 18 | 程咬铁 | female | 18 | 2014-05-12 | 运营部 | NULL | 17000 | 403 | 3 |
58. | 8 | sean | male | 48 | 2010-11-11 | 教学部 | NULL | 10000 | 401 | 1 |
59. | 14 | 张野 | male | 28 | 2016-03-11 | 运营部 | NULL | 10000 | 403 | 3 |
60. | 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
61. | 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
62. | 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
63. | 13 | 格格 | female | 28 | 2017-01-27 | 销售部 | NULL | 4000 | 402 | 2 |
64. | 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
65. | 9 | 歪歪 | female | 48 | 2015-03-11 | 销售部 | NULL | 3000 | 402 | 2 |
66. | 12 | 星星 | female | 18 | 2016-05-13 | 销售部 | NULL | 3000 | 402 | 2 |
67. | 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
68. | 10 | 丫丫 | female | 38 | 2010-11-01 | 销售部 | NULL | 2000 | 402 | 2 |
69. | 11 | 丁丁 | female | 18 | 2011-03-12 | 销售部 | NULL | 1000 | 402 | 2 |
70. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
71. \# 来个复杂的：
72. \# 统计各部门年龄在25岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
73. mysql> select post,avg(salary) from emp
74. \-> where age>25
75. \-> group by post
76. \-> having avg(salary)>1000
77. \-> order by avg(salary) desc;
78. \+-----------+-------------+
79. | post | avg(salary) |
80. \+-----------+-------------+
81. | 教学部 | 204780.0000 |
82. | 运营部 | 10000.0000 |
83. | 销售部 | 3000.0000 |
84. \+-----------+-------------+

7、limit （限制显示条数）

1.  \# 限制展示条数 limit 参数1 limit 参数1，参数2
2.  mysql> select \* from emp limit 5; \# 单个数字5代表从最前面开始显示5条
3.  \+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+
4.  | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
5.  \+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+
6.  | 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
7.  | 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
8.  | 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
9.  | 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
10. | 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
11. \+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+
12. 5 rows in set (0.01 sec)
13.  
14. mysql> select \* from emp limit 2,6; \# 从起始行数为2开始往后显示6行，这里不包含其实行数2。
15. \+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
16. | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
17. \+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
18. | 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
19. | 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
20. | 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
21. | 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
22. | 7 | nick | male | 18 | 1900-03-01 | 教学部 | NULL | 30000 | 401 | 1 |
23. | 8 | sean | male | 48 | 2010-11-11 | 教学部 | NULL | 10000 | 401 | 1 |
24. \+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
25. 6 rows in set (0.00 sec)

8、正则（没错，表查询也能用正则匹配）

1.  \# 查询记录，其中匹配名字条件为以j开头，n或者y结尾的名字
2.  mysql> select \* from emp where name regexp '^j.\*(n|y)$';
3.  \+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
4.  | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
5.  \+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
6.  | 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
7.  | 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
8.  \+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+

**二、多表查询**

**多个表之间的查询一般都是在 表之间存在某种逻辑关联的情况下进行的查询，这种逻辑上的关联其实就是表中某个字段名和另外一个表中的字段名存在一个一一对应的关系或者关联。**

1.  先创建2张表作为示例
2.  mysql> \#建表
3.  mysql> create table dep(
4.  \-> id int,
5.  \-> name varchar(20)
6.  \-> );
7.  Query OK, 0 rows affected (0.04 sec)
8.   
9.  mysql>
10. mysql> create table emp(
11. \-> id int primary key auto\_increment,
12. \-> name varchar(20),
13. \-> sex enum('male','female') not null default 'male',
14. \-> age int,
15. \-> dep\_id int
16. \-> );
17. Query OK, 0 rows affected (0.04 sec)
18. mysql> \#插入数据
19. mysql> insert into dep values
20. \-> (200,'技术'),
21. \-> (201,'人力资源'),
22. \-> (202,'销售'),
23. \-> (203,'运营');
24. Query OK, 4 rows affected (0.01 sec)
25. Records: 4 Duplicates: 0 Warnings: 0
26.  
27. mysql> insert into emp(name,sex,age,dep\_id) values
28. \-> ('jason','male',18,200),
29. \-> ('egon','female',48,201),
30. \-> ('kevin','male',38,201),
31. \-> ('nick','female',28,202),
32. \-> ('owen','male',18,200),
33. \-> ('jerry','female',18,204)
34. \-> ;
35. Query OK, 6 rows affected (0.01 sec)
36. Records: 6 Duplicates: 0 Warnings: 0
37.  
38. mysql> select \* from dep;
39. \+------+--------------+
40. | id | name |
41. \+------+--------------+
42. | 200 | 技术 |
43. | 201 | 人力资源 |
44. | 202 | 销售 |
45. | 203 | 运营 |
46. \+------+--------------+
47. 4 rows in set (0.00 sec)
48.  
49. mysql> select \* from emp;
50. \+----+-------+--------+------+--------+
51. | id | name | sex | age | dep\_id |
52. \+----+-------+--------+------+--------+
53. | 1 | jason | male | 18 | 200 |
54. | 2 | egon | female | 48 | 201 |
55. | 3 | kevin | male | 38 | 201 |
56. | 4 | nick | female | 28 | 202 |
57. | 5 | owen | male | 18 | 200 |
58. | 6 | jerry | female | 18 | 204 |
59. \+----+-------+--------+------+--------+
60. 6 rows in set (0.00 sec)

多表查询示例：

1.  \# 笛卡尔积 多表查询
2.  mysql> select \* from emp,dep;
3.  \+----+-------+--------+------+--------+------+--------------+
4.  | id | name | sex | age | dep\_id | id | name |
5.  \+----+-------+--------+------+--------+------+--------------+
6.  | 1 | jason | male | 18 | 200 | 200 | 技术 |
7.  | 1 | jason | male | 18 | 200 | 201 | 人力资源 |
8.  | 1 | jason | male | 18 | 200 | 202 | 销售 |
9.  | 1 | jason | male | 18 | 200 | 203 | 运营 |
10. | 2 | egon | female | 48 | 201 | 200 | 技术 |
11. | 2 | egon | female | 48 | 201 | 201 | 人力资源 |
12. | 2 | egon | female | 48 | 201 | 202 | 销售 |
13. | 2 | egon | female | 48 | 201 | 203 | 运营 |
14. | 3 | kevin | male | 38 | 201 | 200 | 技术 |
15. | 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
16. | 3 | kevin | male | 38 | 201 | 202 | 销售 |
17. | 3 | kevin | male | 38 | 201 | 203 | 运营 |
18. | 4 | nick | female | 28 | 202 | 200 | 技术 |
19. | 4 | nick | female | 28 | 202 | 201 | 人力资源 |
20. | 4 | nick | female | 28 | 202 | 202 | 销售 |
21. | 4 | nick | female | 28 | 202 | 203 | 运营 |
22. | 5 | owen | male | 18 | 200 | 200 | 技术 |
23. | 5 | owen | male | 18 | 200 | 201 | 人力资源 |
24. | 5 | owen | male | 18 | 200 | 202 | 销售 |
25. | 5 | owen | male | 18 | 200 | 203 | 运营 |
26. | 6 | jerry | female | 18 | 204 | 200 | 技术 |
27. | 6 | jerry | female | 18 | 204 | 201 | 人力资源 |
28. | 6 | jerry | female | 18 | 204 | 202 | 销售 |
29. | 6 | jerry | female | 18 | 204 | 203 | 运营 |
30. \+----+-------+--------+------+--------+------+--------------+
31. \# 将所有的数据都对应了一遍进行合并，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据
32. \# 查询员工及所在部门的信息
33. mysql> select dep.name,emp.name from emp,dep where emp.dep\_id=dep.id;
34. \+--------------+-------+
35. | name | name |
36. \+--------------+-------+
37. | 技术 | jason |
38. | 人力资源 | egon |
39. | 人力资源 | kevin |
40. | 销售 | nick |
41. | 技术 | owen |
42. \+--------------+-------+
43. \# 查询部门为技术部的员工及部门信息
44. mysql> select \* from emp,dep where emp.dep\_id=dep.id and dep.name='技术';
45. \+----+-------+------+------+--------+------+--------+
46. | id | name | sex | age | dep\_id | id | name |
47. \+----+-------+------+------+--------+------+--------+
48. | 1 | jason | male | 18 | 200 | 200 | 技术 |
49. | 5 | owen | male | 18 | 200 | 200 | 技术 |
50. \+----+-------+------+------+--------+------+--------+

1.  多表查询分为内连接、左连接、右连接、全连接，在产生连接后 如需跟条件此时就不能用到where，只能使用on作为替代。
2.  \# 将2张表关联到一起的操作，有专门的方法
3.  \# 1、内连接（inner join）：只取两张表有对应关系的记录
4.  mysql> select \* from emp inner join dep on emp.dep\_id = dep.id;
5.  \+----+-------+--------+------+--------+------+--------------+
6.  | id | name | sex | age | dep\_id | id | name |
7.  \+----+-------+--------+------+--------+------+--------------+
8.  | 1 | jason | male | 18 | 200 | 200 | 技术 |
9.  | 2 | egon | female | 48 | 201 | 201 | 人力资源 |
10. | 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
11. | 4 | nick | female | 28 | 202 | 202 | 销售 |
12. | 5 | owen | male | 18 | 200 | 200 | 技术 |
13. \+----+-------+--------+------+--------+------+--------------+
14.  
15. \# 2、左连接（left join）：在内连接的基础上保留左表满足条件的全部内容，右表没有对应上的内容用null表示
16. mysql> select \* from emp left join dep on emp.dep\_id = dep.id;
17. \+----+-------+--------+------+--------+------+--------------+
18. | id | name | sex | age | dep\_id | id | name |
19. \+----+-------+--------+------+--------+------+--------------+
20. | 1 | jason | male | 18 | 200 | 200 | 技术 |
21. | 5 | owen | male | 18 | 200 | 200 | 技术 |
22. | 2 | egon | female | 48 | 201 | 201 | 人力资源 |
23. | 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
24. | 4 | nick | female | 28 | 202 | 202 | 销售 |
25. | 6 | jerry | female | 18 | 204 | NULL | NULL |
26. \+----+-------+--------+------+--------+------+--------------+
27. \# 3 右连接（right join）：在内连接的基础上，保留右表满足条件的全部内容，左表没有对应上的内容用null表示
28. mysql> select \* from emp right join dep on emp.dep\_id = dep.id;
29. \+------+-------+--------+------+--------+------+--------------+
30. | id | name | sex | age | dep\_id | id | name |
31. \+------+-------+--------+------+--------+------+--------------+
32. | 1 | jason | male | 18 | 200 | 200 | 技术 |
33. | 2 | egon | female | 48 | 201 | 201 | 人力资源 |
34. | 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
35. | 4 | nick | female | 28 | 202 | 202 | 销售 |
36. | 5 | owen | male | 18 | 200 | 200 | 技术 |
37. | NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
38. \+------+-------+--------+------+--------+------+--------------+
39. \# 4 全连接（用union将左连接和右连接联合起来）：在内连接的基础上保留左右表没有对应
40. mysql> select \* from emp left join dep on emp.dep\_id = dep.id
41. \-> union
42. \-> select \* from emp right join dep on emp.dep\_id = dep.id;
43. \+------+-------+--------+------+--------+------+--------------+
44. | id | name | sex | age | dep\_id | id | name |
45. \+------+-------+--------+------+--------+------+--------------+
46. | 1 | jason | male | 18 | 200 | 200 | 技术 |
47. | 5 | owen | male | 18 | 200 | 200 | 技术 |
48. | 2 | egon | female | 48 | 201 | 201 | 人力资源 |
49. | 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
50. | 4 | nick | female | 28 | 202 | 202 | 销售 |
51. | 6 | jerry | female | 18 | 204 | NULL | NULL |
52. | NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
53. \+------+-------+--------+------+--------+------+--------------+

**三、子查询：**

**\# 就是将一个查询语句的结果用括号括起来当做另一个查询语句的条件去用**

1.  \# 接着上面的表：
2.  mysql> select \* from emp;
3.  \+----+-------+--------+------+--------+
4.  | id | name | sex | age | dep\_id |
5.  \+----+-------+--------+------+--------+
6.  | 1 | jason | male | 18 | 200 |
7.  | 2 | egon | female | 48 | 201 |
8.  | 3 | kevin | male | 38 | 201 |
9.  | 4 | nick | female | 28 | 202 |
10. | 5 | owen | male | 18 | 200 |
11. | 6 | jerry | female | 18 | 204 |
12. \+----+-------+--------+------+--------+
13. mysql> select \* from dep;
14. \+------+--------------+
15. | id | name |
16. \+------+--------------+
17. | 200 | 技术 |
18. | 201 | 人力资源 |
19. | 202 | 销售 |
20. | 203 | 运营 |
21. \+------+--------------+
22. \# 1.查询部门是技术或者人力资源的员工信息
23. mysql> \# 先将技术和人力资源部门的id号先找出来：
24. mysql> select id from dep where name='技术' or name='人力资源';
25. \+------+
26. | id |
27. \+------+
28. | 200 |
29. | 201 |
30. \+------+
31.  
32. mysql> \# 在将上述查询到的id号作为条件，进行再一次查询：
33. mysql> select \* from emp where dep\_id in (select id from dep where name='技术' or name='人力资源');
34. \+----+-------+--------+------+--------+
35. | id | name | sex | age | dep\_id |
36. \+----+-------+--------+------+--------+
37. | 1 | jason | male | 18 | 200 |
38. | 2 | egon | female | 48 | 201 |
39. | 3 | kevin | male | 38 | 201 |
40. | 5 | owen | male | 18 | 200 |
41. \+----+-------+--------+------+--------+
42. \--------------------------------------------------------------------------------------------------------------------------------
43. \# 2.每个部门最新入职的员工
44. 有这张表emp：
45. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
46. | id | name | sex | age | hire\_date | post | post\_comment | salary | office | depart\_id |
47. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
48. | 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 0 |
49. | 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
50. | 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
51. | 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
52. | 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
53. | 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
54. | 7 | nick | male | 18 | 1900-03-01 | 教学部 | NULL | 30000 | 401 | 1 |
55. | 8 | sean | male | 48 | 2010-11-11 | 教学部 | NULL | 10000 | 401 | 1 |
56. | 9 | 歪歪 | female | 48 | 2015-03-11 | 销售部 | NULL | 3000 | 402 | 2 |
57. | 10 | 丫丫 | female | 38 | 2010-11-01 | 销售部 | NULL | 2000 | 402 | 2 |
58. | 11 | 丁丁 | female | 18 | 2011-03-12 | 销售部 | NULL | 1000 | 402 | 2 |
59. | 12 | 星星 | female | 18 | 2016-05-13 | 销售部 | NULL | 3000 | 402 | 2 |
60. | 13 | 格格 | female | 28 | 2017-01-27 | 销售部 | NULL | 4000 | 402 | 2 |
61. | 14 | 张野 | male | 28 | 2016-03-11 | 运营部 | NULL | 10000 | 403 | 3 |
62. | 15 | 程咬金 | male | 18 | 1997-03-12 | 运营部 | NULL | 20000 | 403 | 3 |
63. | 16 | 程咬银 | female | 18 | 2013-03-11 | 运营部 | NULL | 19000 | 403 | 3 |
64. | 17 | 程咬铜 | male | 18 | 2015-04-11 | 运营部 | NULL | 18000 | 403 | 3 |
65. | 18 | 程咬铁 | female | 18 | 2014-05-12 | 运营部 | NULL | 17000 | 403 | 3 |
66. \+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
67.  
68. \# ①先将入职日期最大的员工查询处理起名为表t2
69. \# (select post,max(hire\_date) as max\_date from emp group by post) as t2
70. \# ②为了易读性，将emp表起名为t1，将需要查询的结果字段名写在select后（此时包括t2中的字段，因为select是最后执行的语句）
71. \# ③ 用内连接将t1和t2连接起来，通过t1.hire\_date=t2.max\_date
72. 最终结果：
73. mysql> select t1.id,t1.name,t1.sex,t1.age,t1.salary,t1.office,t2.\* from emp as t1
74. \-> inner join (select post,max(hire\_date) as max\_date from emp group by post) as t2 where
75. \-> t1.hire\_date=t2.max\_date;
76. \+----+--------+--------+-----+---------+--------+-----------+------------+
77. | id | name | sex | age | salary | office | post | max\_date |
78. \+----+--------+--------+-----+---------+--------+-----------+------------+
79. | 1 | jason | male | 18 | 6600 | 401 | 外交部 | 2017-03-01 |
80. | 2 | egon | male | 78 | 1000000 | 401 | 教学部 | 2015-03-02 |
81. | 13 | 格格 | female | 28 | 4000 | 402 | 销售部 | 2017-01-27 |
82. | 14 | 张野 | male | 28 | 10000 | 403 | 运营部 | 2016-03-11 |
83. \+----+--------+--------+-----+---------+--------+-----------+------------+
84.   
    **记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询**

额外题：

部门中薪资超过部门平均薪资的员工姓名及薪资

1.  mysql> select t1.name,t1.salary,t1.post,t2.avg\_salary from emp t1
2.  \-> inner join (select post,avg(salary) as avg\_salary from emp group by post) as t2
3.  \-> where t1.post=t2.post and t1.salary>t2.avg\_salary;
4.  \+-----------+---------+-----------+-------------+
5.  | name | salary | post | avg\_salary |
6.  \+-----------+---------+-----------+-------------+
7.  | egon | 1000000 | 教学部 | 151842.8571 |
8.  | 歪歪 | 3000 | 销售部 | 2600.0000 |
9.  | 星星 | 3000 | 销售部 | 2600.0000 |
10. | 格格 | 4000 | 销售部 | 2600.0000 |
11. | 程咬金 | 20000 | 运营部 | 16800.0000 |
12. | 程咬银 | 19000 | 运营部 | 16800.0000 |
13. | 程咬铜 | 18000 | 运营部 | 16800.0000 |
14. | 程咬铁 | 17000 | 运营部 | 16800.0000 |
15. \+-----------+---------+-----------+-------------+