MySQL（三）

## 十、连接查询

### 10.1连接查询概念

通过连接运算符实现多表查询

内连接：把两张表相同的地方查询出来

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hIFGPYR0-1649423312404)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649210965822.png)]

左连接：包括了内连接同时还查询左表特有的内容

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PiOolyJB-1649423312406)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649210980922.png)]

右连接：包括内连接同时还查询右表特有的部分

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Gk3ilVFN-1649423312407)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649313670064.png)]

### 10.2、内连接

语法一：

```sql
select *from 表1 inner join 表2 on 表1.字段=表2.字段;

• 内连接最重要找到相同的关联字段

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GQHsQdwF-1649423312409)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649313888653.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Wu1z1oPm-1649423312410)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649313920876.png)]

用内连接查询两张表

select * from a inner join b on a.id = b.id ;

• 已经创建students表和scores表

select * from students ;
select * from scores;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Je3Whhhw-1649423312412)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649315168611.png)]

students表和scores表通过studentNo内连接查询

select * from students inner join scores on students.studentNo =scores.studentNo;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g8kemFlE-1649423312413)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649315669181.png)]

语法二：隐式内连接

select * from 表1，表2 where 表1.字段=表2.字段；

10.2.1、内连接显示指定字段

students表与scores表内连接，只显示name 课程号 成绩

select name, couseNo, score, from students 
inner join scores on students.studentNo = scores.studentNo;

10.2.2、表的别名在查询中的使用

select name, couseNo, score, from students as st
inner join scores sc on st.studentNo = sc.studentNo;

10.2.3、带有where 条件的内连接

语法：

select * from 表1 inner join 表2 on 表1.字段 =表2.字段 where 条件；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kU5h180b-1649423312415)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649317318939.png)]

select name, couseNo,score from students s1 
inner join scores s2 on s1.studentNo = s2.studentNo
where s1.name ='王昭君'；

10.2.4、带有and逻辑运算符的内连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-C5awEOx9-1649423312416)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649317792039.png)]

select name,score from students s1 
inner join scores s2 on s1.studentNo = s2.studentNo
where s1.name ='王昭君' and s2.score <90；

10.2.5、多表内连接查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-otDSFSr0-1649423312417)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649319360915.png)]

select * from students inner join scores on students.studentNo = scores.studentNo

inner join  coures on scores.courseNo =courses.courseNo;

10.2.6、带有order by 的内连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YLrY9x31-1649423312419)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649320738063.png)]

select name,score,courseName from students inner join scores
on students.studentNo = scores.studentNo
inner join courses on scores.courseNo = course.courseNo
where sex = '男'
order by score desc limit 1 ;

10.3、sql三步法

1. 搭框架

   基本的select语句框架搭建起来，如果有多表，把相应的多表也联合起来

2. 看条件

   决定where后面的具体条件

3. 显示的字段

   select后面到底要显示什么字段

   每一步执行以下，防止有错误

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jeZmYIxp-1649423312421)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649320136923.png)]

select name,score,courseName from students inner join scores
on students.studentNo = scores.studentNo
inner join courses on scores.courseNo = course.courseNo
where courseName = 'linux';

10.4、左连接

语法： 左表有右没有，用左连接，不存在用null填充

select * from 表1 left join 表2 on 表1.字段 =表2.字段；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BBH7NV6C-1649423312423)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649322543683.png)]

select * from students left join scores on students.studentNo = scores.studentNo；

10.5、右连接

语法：

select * from 表1 right join 表2 on 表1.字段 =表2.字段；

右表有数据 左表不存在，用右连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vlRwAmOS-1649423312425)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649322930444.png)]

select * from scores right join courses on score.courseNo = course.courseNo;

10.6、多表联合查询同名字段处理方式

如果一条select要用到多个表，表中有同名字段，就需要 表名.字段 加以区分

select students.studentNo from students 
inner join scores on students.studentNo =scores.studentNo;

十一、自关联

1. 自关联，是同一张表做连接查询
2. 自关联下，一定找到同一张表可关联的不同字段

select count (*) from areas where pid is null;                 查询总共多少个省

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rCcmeMAo-1649423312426)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649330604065.png)]

select * from areas a1 inner join areas a2 on a1.id =a2.pid where areas = '广东省'；

十二、子查询

select语句中嵌入了另外一个select语句，被嵌入的select语句是子查询

1. 子查询是嵌套到主查询里，子查询作为主查询的条件

2. 子查询独立执行可运行

3. 主查询不能独立运行，依赖子查询结果

12.1、标量子查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2lfS3jGE-1649423312428)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649331081946.png)]

select * from students where  age > select avg(age) from students ;

子查询返回结果只有一行一列, 一般用比较运算符

12.2、列子查询

返回一般一列多行，一般用in

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8MlYxiLj-1649423312430)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649331716522.png)]

select studentNo from students where age =30;

select * from scores where studentNo in ('001','003','011');

子查询实现

select * from scores where studentNo in (select studentNo from students where age =30);

还可以用内连接方式

12.3、表级子查询

子查询返回多行多列

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ALkIB6nG-1649423312432)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649332236567.png)]

内连接：

select * from students inner join
scores on students.studentNo =scores. studentNo
where sex ='女'；

表级子查询
select * from (select * from students where sex='女') stu 
inner join scores sc on stu.studentNo =sc.studentNo;

12.4、练习

数据:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-a6D9biou-1649423312433)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649333531213.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GDeIBvU0-1649423312435)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649333722259.png)]

select sex,count(*) from employees group by sex;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hluESD6X-1649423312437)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649334030498.png)]

select count (*) from employees where politicalsta != '党员'；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NmZop5QB-1649423312439)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649334126679.png)]

select empid,empname,depatname from depatments de 
inner join employees em on de.deptid =em.deptid;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pvnzhrEh-1649423312441)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649334489749.png)]

select e.empid,empname,salary from employees e 
inner join salary s on e.empid = s.empid;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NjfRbMVE-1649423312442)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649334797862.png)]

select empname,depatname from depatments d 
inner join employees e on d.deptid= e.deptid
where leader is null;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vyE0RtmL-1649423312444)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649335179541.png)]

select deptid,count(*) from employees group by deptid having count(*)>4;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QidGy1tA-1649423312446)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649335462942.png)]

select e.deptid,deptname from employees e inner join 
departments d on e.deptid = d.deptid 
group by e.deptid having count(*)>4;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dU7ARtg7-1649423312447)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649335694730.png)]

select emptid,empname from deptments d 
inner join employees e on d.deptid e.deptid 
where deptname in ('开发部','测试部');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3buBnOaa-1649423312448)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649335990345.png)]

0)]

select studentNo from students where age =30;

select * from scores where studentNo in ('001','003','011');

子查询实现

select * from scores where studentNo in (select studentNo from students where age =30);

还可以用内连接方式

12.3、表级子查询

子查询返回多行多列

[外链图片转存中…(img-ALkIB6nG-1649423312432)]

内连接：

select * from students inner join
scores on students.studentNo =scores. studentNo
where sex ='女'；

表级子查询
select * from (select * from students where sex='女') stu 
inner join scores sc on stu.studentNo =sc.studentNo;

12.4、练习

数据:

[外链图片转存中…(img-a6D9biou-1649423312433)]

[外链图片转存中…(img-GDeIBvU0-1649423312435)]

select sex,count(*) from employees group by sex;

[外链图片转存中…(img-hluESD6X-1649423312437)]

select count (*) from employees where politicalsta != '党员'；

[外链图片转存中…(img-NmZop5QB-1649423312439)]

select empid,empname,depatname from depatments de 
inner join employees em on de.deptid =em.deptid;

[外链图片转存中…(img-pvnzhrEh-1649423312441)]

select e.empid,empname,salary from employees e 
inner join salary s on e.empid = s.empid;

[外链图片转存中…(img-NjfRbMVE-1649423312442)]

select empname,depatname from depatments d 
inner join employees e on d.deptid= e.deptid
where leader is null;

[外链图片转存中…(img-vyE0RtmL-1649423312444)]

select deptid,count(*) from employees group by deptid having count(*)>4;

[外链图片转存中…(img-QidGy1tA-1649423312446)]

select e.deptid,deptname from employees e inner join 
departments d on e.deptid = d.deptid 
group by e.deptid having count(*)>4;

[外链图片转存中…(img-dU7ARtg7-1649423312447)]

select emptid,empname from deptments d 
inner join employees e on d.deptid e.deptid 
where deptname in ('开发部','测试部');

[外链图片转存中…(img-3buBnOaa-1649423312448)]

select empname,politicalsta  from employees e  
inner join salary s on e.empid =s.empid 
where departname='市场部' and sex ='女'；