mysql进阶查询

1 进阶查询

1.1 子查询

• 定义
  • 一个sql语句中有2个及以上的select语句
  • 一个select语句的结果作为另一个select语句的条件

# ******01 子查询*****************
# *****01.1单表子查询
# *****01.1.1单行子查询：子查询的结果返回的值只有一行，才可以用> = < >= <= !=号
# 语法:select 结果 from 表 where 列 >/</= (select语句)
# 案例:查询工资跟张三工资一样的员工信息,不显示张三
# 分析
# 1.查询工资="张三工资"的员工信息
# 结果:*
# 条件:sal = '张三工资' and ename != '张三'
# 表:emp
select * from emp where sal = '张三工资'and ename != '张三';  -- 伪代码

# 2.查询张三的工资
# 结果:sal
# 条件:ename = '张三'
# 表:emp
select sal from emp where ename = '张三';

# 3.组合最终语句
select * from emp where sal = (select sal from emp where ename = '张三')and ename != '张三';





#练习:查询工资比张三工资高的员工信息
# 1.查询sal>"张三的工资"的员工信息
# 结果:*
# 条件:sal > '张三的工资'
# 表:emp
select * from emp where sal > '张三的工资';

# 2.查询张三的工资
# 结果:sal
# 条件:ename = '张三'
# 表:emp
select sal from emp where ename = '张三';

# 3.组合最终语句
select * from emp where sal > (select sal from emp where ename = '张三');


#练习:查询工资比张三工资高,且跟张三是同一个部门的员工信息
# 1.查询sal>"张三的工资"and deptno = "张三的部门"的员工信息
# 结果:*
# 条件:sal>"张三的工资"and deptno = "张三的部门"
# 表:emp
select * from emp where sal>"张三的工资"and deptno = "张三的部门";

# 2.查询 张三的工资
# 结果:sal
# 条件:ename = '张三'
# 表:emp
select sal from emp where ename = '张三';

# 3.查询 张三的部门
# 结果:deptno
# 条件:ename = '张三'
# 表:emp
select deptno from emp where ename = '张三';

# 4.组合最终语句
select * from emp where sal>(select sal from emp where ename = '张三')
and deptno = (select deptno from emp where ename = '张三');

# ******01.1.2多行子查询：如果子查询的结果返回多行数据，此时可以in
# 语法:select 结果 from 表 where 列 in (select语句); 
# 案例:查询工资跟部门20员工工资相同的员工信息
# 分析
# 1.查询工资="部门20员工工资"的员工信息
# 结果:*
# 条件:sal = "部门20员工工资"
# 表:emp
select * from emp where sal = "部门20员工工资";

# 2.查询部门20员工的工资
# 结果:sal
# 条件:deptno = 20
# 表:emp
select sal from emp where deptno = 20;

# 3.组合成最终语句
select * from emp where sal in (select sal from emp where deptno = 20);


# 练习:查询岗位和10部门员工岗位一致的员工信息
# 1.查询job = '10部门员工岗位'的员工信息
# 结果:*
# 条件:job = '10部门员工岗位'
# 表emp:
select * from emp where job = '10部门员工岗位';
# 2.查询 10部门员工岗位
# 结果:job
# 条件:deptno = 10
# 表:emp
select job from emp where deptno = 10;
# 3 把1和2整合成最终的语句
select * from emp where job in (select job from emp where deptno = 10);

# 练习:查询部门编号和名字包含有'鲤'字部门编号一样的员工姓名
# 1.查询deptno = "名字包含有'鲤'字"的员工姓名
# 结果:ename
# 条件:deptno = "名字包含有'鲤'字"
# 表:emp
select ename from emp where deptno = "名字包含有'鲤'字";
# 2.查询 "名字包含有'鲤'字"的部门编号
# 结果:deptno
# 条件:ename like '%鲤%'
# 表:emp
select deptno from emp where ename like '%鲤%';
# 3.整合1和2步骤的语句
select ename from emp where deptno in (select deptno from emp where ename like '%鲤%');


# ********01.2多表子查询
# 语法:select 结果 from 表 where 多表共同列 in (select 多表共同列 from 表 后续有条件就加);
# 案例:查询工作地点在北京的员工信息
# 1.查询工作地点在北京的员工信息
# 结果:*
# 条件:工作地点在北京
# 表:emp
select * from emp where deptno in(工作地点在北京的共同列);

# 2.查询 工作地点在北京的共同列
# 结果:deptno
# 条件:loc = '北京'
# 表:dept
select deptno from dept where loc = '北京';
# 3.整合1和2语句
select * from emp where deptno in(select deptno from dept where loc = '北京');

#练习:查询工作地点在北京且工资高于2000的员工信息
# 1.查询    部门编号和工作地点在北京的部门编号一致 且 工资高于2000的  员工信息
# 结果:*
# 表:emp
# 条件:deptno in (工作地点在北京的部门编号) and sal>2000
select * from emp where deptno in (工作地点在北京的部门编号) and sal>2000;
# 2.查询 工作地点在北京的部门编号
# 结果:deptno
# 条件:loc = '北京'
# 表:dept
select deptno from dept where loc = '北京';
# 3.把1和2整合

select * from emp where deptno in (select deptno from dept where loc = '北京') and sal>2000;
#练习:查询工作地点在北京且工资高于张三工资的员工信息
# 1. 查询 两表共同列 in (工作地点在北京的两边共同列) and sal >(张三的工资) 的员工信息
# 结果:*
# 条件:两表共同列 in (工作地点在北京的两边共同列) and sal >(张三的工资)
# 表:emp
select * from emp where deptno in (工作地点在北京的两边共同列) and sal >(张三的工资);
# 2.查询 工作地点在北京的两边共同列 
# 结果:deptno
# 表:dept
# 条件:loc = '北京'
select deptno from dept where loc = '北京';
# 3.查询张三的工资
# 结果:sal
# 表:emp
# 条件:ename = '张三'
select sal from emp where ename = '张三';
# 4 整合1和2和3
select * from emp where deptno in (select deptno from dept where loc = '北京') 
and sal >(select sal from emp where ename = '张三');

#案例:查询工作地点为北京或者上海的员工信息
# 1.查询 共同列 in (工作地点为北京的共同列) or 共同列 in (工作地点在上海的共同列)  的员工信息
# 结果:*
# 条件:deptno in (工作地点为北京的共同列) or deptno in (工作地点在上海的共同列)
# 表:emp
select * from emp where deptno in (工作地点为北京的共同列) or deptno in (工作地点在上海的共同列);
#2 查询工作地点为北京的共同列
# 结果:deptno
# 条件:loc = '北京'
# 表:dept
select deptno from dept where loc = '北京';--10
# 3 查询工作地点在上海的共同列
# 结果:deptno
# 条件:loc = '上海'
# 表:dept
select deptno from dept where loc = '上海';--20
# 4 整合1和2和3
select * from emp where deptno in (select deptno from dept where loc = '北京') or
 deptno in (select deptno from dept where loc = '上海');
# 练习:查询姓名为三个字符的员工的部门名称
# 1.两边共同列 in (姓名为三个字符的员工的共同列) 的部门名称
# 结果:dname
# 条件:deptno in (姓名为三个字符的员工的共同列)
# 表:dept
select dname from dept where deptno in (姓名为三个字符的员工的共同列);
# 2.查询姓名为三个字符的员工的共同列
# 结果:deptno
# 条件:ename like '___'
# 表:emp
select deptno from emp where ename like '___';
# 3 整合1和2
select dname from dept where deptno in (select deptno from emp where ename like '___');


# 练习:查询有员工的部门名称
# 先分解:判断是否是用多表子查询
# 1 查询两表共同列 in (有员工的共同列) 的部门名称
# 结果:dname
# 条件:deptno in (有员工的共同列)
# 表:dept
select dname from dept where deptno in (有员工的共同列);
# 2 查询有员工的共同列
# 结果:deptno
# 条件:有员工
# 表:emp
select deptno from emp;
# 3整合 1和2
select dname from dept where deptno in (select deptno from emp);

# 练习:查询没有员工的部门名称
select dname from dept where deptno NOT in (select deptno from emp);

# 知识点1:   where sal is not null  /is null 

# 案例:查询比所有推销员工资都高的员工信息
# 1.查询工资> 所有推销员工资 的员工信息
# 结果:*
# 条件:sal > (所有推销员工资)
# 表:emp
select * from emp where sal > (所有推销员工资);
# 2.查询推销员的工资
# 结果:sal
# 条件:job = '推销员'
# 表:emp
select sal from emp where job = '推销员';
# 3整合1和2
select * from emp where sal > (select max(sal) from emp where job = '推销员');
# 1.工资大于推销员里最大的工资
# 2.使用all
select * from emp where sal > all(select sal from emp where job = '推销员');

# 练习1:查询高于其中某一个推销员工资的员工信息,使用2种方法写(any)
select * from emp where sal > (select min(sal) from emp where job = '推销员');
select * from emp where sal > any(select sal from emp where job = '推销员');
# 练习2:查询高于推销员最高工资的员工信息,使用2种方法写
# 练习3:查询低于推销员最低工资的员工信息,使用2种方法写 
select * from emp where sal < any(select sal from emp where job = '推销员');
select * from emp where sal < all(select sal from emp where job = '推销员');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244

1.2 组合查询

• 定义
  • 可以把两张表的数据加起来
  • 组合规则：列数相同，数据类型要相同
• 语法
  • select a,b from 表1 union select a,b from 表2
  • select a,b from 表1 union all select a,b from 表2

# *****02 组合查询************
# 语法: sql语句 union sql语句
# 案例:把emp表的empno和ename这两列和dept表的deptno和loc放一张表里面
select empno,ename from emp union select deptno,loc from dept;
# 适用:两个表的列的数据类型一致
# union默认去重
# union all 不去重
# 案例:把dept表上下连接起来
select * from dept union all select * from dept;

1
2
3
4
5
6
7
8
9
10

1.3 多表查询

• 定义
  • 当查询的结果在多个表中时，需要从多个表中选择列进行输出

1.3.1 笛卡尔积查询

• 笛卡尔积查询： 表1,表2

# *****03 笛卡尔积查询********
# 语法:select 结果 from 表1,表2,表3... where 表1.共同列=表2.共同列 and 表2.共同列= 表3.共同列...;
#	案例:查询员工姓名和其所在的部门名称


-- - 练习:查询工资大于2000的员工姓名和部门名称
-- - 练习:查询员工姓名,工资,工作地点
-- - 练习:查询员工的姓名以及员工领导姓名
-- - 练习:查询员工姓名以及员工领导姓名及部门名称

1
2
3
4
5
6
7
8
9
10

1.3.2 连接查询

• 分类
  • 内连接查询
    • select * from 表1 [inner] join 表2 on 条件;
    • 查询效率比笛卡尔积高
    • 把两张表内联，结果为符合条件的数据
  • 外连接查询
    • 左外联查询：A表左外联B表，符合条件的数据正常显示，A表中不符合查询条件的数据也显示出来，同时B表对应的列以空值替代
    • 语法:select 结果 from 表1 left join 表2 on 表1.共同列=表2.共同列 left join 表3 on 表2.共同列=表3.共同列;
    • 右外联查询：A表右外联B表，符合条件的数据正常显示，B表中不符合查询条件的数据也显示出来，同时A表对应的列以空值替代
      • 语法:select 结果 from 表1 rightjoin 表2 on 表1.共同列=表2.共同列 rightjoin 表3 on 表2.共同列=表3.共同列;

# ****04 连接查询*****
# ****04.1 内连接*****
# 语法:select 结果 from 表1 join 表2 on 表1.共同列=表2.共同列; 
# 语法:select 结果 from 表1,表2 where 条件  笛卡尔积语法  
# 案例:查询姓刘的员工所在的部门名称



# ****04.2 左外联*****
# 语法:select 结果 from 表1 left join 表2 on 条件 
# 案例:查询部门名称以及员工姓名，保留没有员工的部门名称

# ****04.3 右外联*****
# 语法:select 结果 from 表1 right join 表2 on 条件 
# 案例:查询部门名称以及员工姓名，保留没有员工的部门名称


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• 练习题
  • 1.查询老师姓名、课程名称，没有课程的老师也显示出来
  • 2.查询学生姓名、课程编号，没有课程的学生姓名也显示出来
  • 3.查询学生姓名、课程名称、学生分数，只显示有课程和分数的信息
  • 4.查询学生姓名、课程名称、学生分数，没有课程的学生也显示出来

# 数据准备
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL,
  `cname` varchar(30) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('3001', '语文', '4');
INSERT INTO `course` VALUES ('3002', '数学', '2');
INSERT INTO `course` VALUES ('3003', '英语', '1');
INSERT INTO `course` VALUES ('3004', '物理', '3');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `sid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('101', '3001', '90');
INSERT INTO `sc` VALUES ('102', '3001', '85');
INSERT INTO `sc` VALUES ('103', '3001', '76');
INSERT INTO `sc` VALUES ('105', '3001', '87');
INSERT INTO `sc` VALUES ('106', '3001', '66');
INSERT INTO `sc` VALUES ('108', '3001', '96');
INSERT INTO `sc` VALUES ('101', '3002', '92');
INSERT INTO `sc` VALUES ('102', '3002', '81');
INSERT INTO `sc` VALUES ('103', '3002', '93');
INSERT INTO `sc` VALUES ('104', '3002', '73');
INSERT INTO `sc` VALUES ('105', '3002', '65');
INSERT INTO `sc` VALUES ('108', '3002', '96');
INSERT INTO `sc` VALUES ('101', '3003', '96');
INSERT INTO `sc` VALUES ('102', '3003', '85');
INSERT INTO `sc` VALUES ('103', '3003', '76');
INSERT INTO `sc` VALUES ('104', '3003', '63');
INSERT INTO `sc` VALUES ('105', '3003', '59');
INSERT INTO `sc` VALUES ('106', '3003', '56');
INSERT INTO `sc` VALUES ('107', '3003', '91');
INSERT INTO `sc` VALUES ('108', '3003', '86');
INSERT INTO `sc` VALUES ('101', '3004', '100');
INSERT INTO `sc` VALUES ('102', '3004', '83');
INSERT INTO `sc` VALUES ('103', '3004', '75');
INSERT INTO `sc` VALUES ('104', '3004', '69');
INSERT INTO `sc` VALUES ('105', '3004', '50');
INSERT INTO `sc` VALUES ('106', '3004', '52');
INSERT INTO `sc` VALUES ('107', '3004', '87');
INSERT INTO `sc` VALUES ('108', '3004', '78');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL,
  `sname` varchar(30) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL,
  `ssex` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('101', '龙大', '18', '男');
INSERT INTO `student` VALUES ('102', '熊二', '19', '男');
INSERT INTO `student` VALUES ('103', '张三', '18', '男');
INSERT INTO `student` VALUES ('104', '李四', '19', '女');
INSERT INTO `student` VALUES ('105', '王五', '20', '男');
INSERT INTO `student` VALUES ('106', '李华', '19', '男');
INSERT INTO `student` VALUES ('107', '李红', '19', '女');
INSERT INTO `student` VALUES ('108', '李明', '20', '男');
INSERT INTO `student` VALUES ('109', '贝贝', '19', '女');
INSERT INTO `student` VALUES ('110', '娜娜', '20', '女');
INSERT INTO `student` VALUES ('111', '赵五月', '23', '女');
INSERT INTO `student` VALUES ('112', '五月', '24', '男');
INSERT INTO `student` VALUES ('113', '五', '25', '男');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL,
  `tname` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '叶平');
INSERT INTO `teacher` VALUES ('2', '李龙');
INSERT INTO `teacher` VALUES ('3', '李逍遥');
INSERT INTO `teacher` VALUES ('4', '朱钊');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110