多表查询的应用

多表查询

多表关系

• 一对多(多对一)

• 多对多

• 一对一

  一对多

  案例: 部门 与 员工的关系

  关系: 一个部门对应多个员工，一个员工对应一个部门

  实现: 在多的一方建立外键，指向一方的主键

  多对多

  案例: 学生 与 课程的关系

  关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择

  实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

  一对一

  案例: 用户 与 用户详情的关系

  关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情

  字段放在另一张表中，以提升操作效率

  实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

  SELECT * FROM goods,category;
  

  笛卡尔乘积现象

  表查询中的笛卡尔乘积现象：多行表在查询时，如果定义了无效连接或者漏写了连接

  条件，就会产生笛卡尔乘积现象，所谓的笛卡尔乘积及时每个表的每一行都和其他表

  的每一行组合。

  笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。

等值连接查询

通常是在存在主键外键关联关系的表之间的连接进行，使用"="连接相关的表

n个表进行等值连接查询，最少需要n-1个等值条件来约束。

select s.id,s.name,sum(score) from student s,score sc where s.id=sc.student_id group by s.id,s.name;

表的别名:

①. tableA as 别名1 , tableB as 别名2 ;

②. tableA 别名1 , tableB 别名2 ;

注：一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

自连接查询

多表查询不仅可以在多个表之间进行查询，也可以在一个表之中进行多表查询

select distinct s.*,sc1.score as 'score1',sc2.score as 'score2',sc3.score as 'score3' 
from student s,score sc,score sc1,score sc2,score sc3 
where s.id=sc.student_id
and s.id=sc1.student_id and sc1.course_id=1
and s.id=sc2.student_id and sc2.course_id=2
and s.id=sc3.student_id and sc3.course_id=3;

内连接查询

内连接查询使用 inner join 关键字实现， inner 可以省略。内连接查询时，条件用

on 连接，多个条件使用 () 将其括起来。

 select distinct s.*,sc1.score as 'score1',sc2.score as 'score2',sc3.score as 'score3'
 from student s
 join score sc on s.id=sc.student_id
 join score sc1 on s.id=sc1.student_id and sc1.course_id=1
 join score sc2 on s.id=sc2.student_id and sc2.course_id=2
 join score sc3 on s.id=sc3.student_id and sc3.course_id=3;

注：

1. 内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据)
2. 和等值查询差不多

外连接查询

外连接分为左外连接（ left outer join ） 和右外连接（ right outer join ）其

值 outer 可以省略。外连接查询时，条件用 on 连接，多个条件使用 () 将其括起来.

左外连接表示以左表为主表，右外连接表示以右表为主表。查询时将主表信息在从表

中进行匹配。

左外连接

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

select distinct s.*,sc1.score as 'score1',sc2.score as 'score2',sc3.score as 'score3' 
from student s 
left join score sc on s.id=sc.student_id
left join score sc1 on s.id=sc1.student_id and sc1.course_id=1
left join score sc2 on s.id=sc2.student_id and sc2.course_id=2
left join score sc3 on s.id=sc3.student_id and sc3.course_id=3;

右外连接

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

**注：**左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

子查询**[应用]**

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。相对于子查询来说，在外部直接执行的查询语句被称作主查询。

子查询分为：

单列子查询: 返回单行单列数据的子查询

单行子查询: 返回单行多列数据的子查询

多行子查询: 返回数据是多行单列的数据

关联子查询: 子查询中如果使用了外部主SQL中的表或列，就说这个子查询跟外

部SQL是相关的。

单行子查询：

单行单列

select * from course
where course_id=(select course_id from course where course_name='语文');

注：

1. 子查询的结果作为父查询条件的值。
2. 父查询的查询条件是=时，子查询的结果集是单行单列数据。

多列子查询:

单行多列

select * from teacher t where (t.course_id,t.teacher_id) = (select c.course_id,c.teacher_id from course c where c.course_name='语文');

多行子查询

如果子查询返回了多行记录，则称这样的嵌套查询为多行子查询，多行子查询就需要

用到多行记录的操作符

如： in , all , any , not in

IN : 在指定的集合范围之内，多选一

NOT IN : 不在指定的集合范围之内

ANY : 子查询返回列表中，有任意一个满足即可

ALL : 子查询返回列表的所有值都必须满足

-- 统计所有的员工分布在那些部门的信息
select * from category where category.no in (select category_no
from goods)
-- 查询公司中比任意一个员工的工资高的所有员工
select * from emp e1 where e1.sal > any (select e1.sal from emp
e2);
-- 查询公司中比所有的 Stock Clerk 工资高但不在 33部门 的员工
select * from emp e1 where e1.sal > all(select e2.sal from e2.emp
where w2.joblike '%助理');

-- 查询有成绩的所有学生信息
select * from student
where id in(select distinct student_id from score where score is not null);

-- 查询没有选课的学生信息
select * from student
where id not in(select distinct student_id from score);

-- 查询成绩表中比任意一个学生课程2成绩高的所有学生
select * from score sc1
where sc1.course_id=2 and sc1.score> any(select sc2.score from score sc2 where course_id=2);

注：

1. 当子查询的结果有多个值时，可以使用any关键字。
2. ‘>’any 表示大于子查询中的任意一个值，即大于最小的值。

-- 查询成绩表中所有比学生课程2成绩高的学生
select * from score sc1
where sc1.course_id=2 and sc1.score> all(select sc2.score from score sc2 where course_id=2);

注：

1. 当子查询的结果有多个值时，可以使用all关键字。
2. ‘>’all 表示大于子查询中的所有值，即大于最大的值。

关联子查询

关联子查询与外部查询（主查询）之间存在联系，并且内部子查询的结果依赖于外部查询的值。换句话说，内部子查询的执行取决于外部查询的每一行。

-- 查询学生姓名及课程2的成绩
select name,
(select score from score where course_id=2 and student.id=score.student_id) as '数学'
from student;

综合查询

 统计选课没有选全的每个学生的课程数量，按照总成绩降序展示，展示第一页，每页显示3条数据
 select s.id,s.name,count(*),sum(score) 
 from student s,score sc 
 where s.id=sc.student_id 
 group by s.id,s.name 
 having count(*)<3 
 order by sum(score) desc 
 limit 0,3;

书写顺序

select–>from–>where–>group by–>having–>order by–>limit

执行顺序

from --> on --> join --> where --> group by --> having --> select --> distinct–

> order by–> limit

)❤️
order by sum(score) desc
limit 0,3;

[外链图片转存中...(img-LEJE6vb8-1690709284897)]

>**书写顺序**
>
>select-->from-->where-->group by-->having-->order by-->limit
>
>**执行顺序**
>
>from --> on --> join --> where --> group by --> having --> select --> distinct--
>
>\> order by--> limit