SQL-多表查询

1、多表关系

一对多、多对一：在多的一方建立外键，指向一的一方。

多对多：至少两个外键，通过中间表维护。

一对一

2、多表查询概述

3、内连接

4、外连接

5、自连接

6、联合查询

7、子查询

8、多表查询案例

# 1、多表关系
# 多对多
# 建立学生表和课程表进行多对多连接
create table student_score(
    id int auto_increment primary key  comment '主键',
    studentid int not null comment '学生Id',
    courseid int not null comment '课程Id',
    constraint  fk_courseid foreign key (courseid) references  course(id),
    constraint  fk_studentid foreign key (studentid) references  student (id)
)comment '学生课程中间表';
insert  into student_score values(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

create table  student (
    id int auto_increment primary key comment '主键id',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
)comment '学生表';
insert into student values(null,'momo1','2019001'),(null,'momo2','2019002'),(null,'momo3','2019003'),(null,'momo4','2019004');

create table course(
    id int auto_increment primary key  comment '主键id',
    name varchar(10) comment '课程名称'
)comment '课程表';
insert into course values(null,'c'),(null,'r'),(null,'sql'),(null,'hadoop');


# 一对一
create table edu (
    id int auto_increment primary key comment '主键id',
    degree varchar(20) comment '学历',
    major varchar(20) comment  '专业',
    primaryschool varchar(20) comment '小学',
    middleschool varchar(20) comment '中学',
    university varchar(20) comment '大学'
)comment '学生教育背景表';
insert into edu values(2019001,'本科','医学','翻斗小学','翻斗中学','翻斗大学'),(2019002,'本科','教育学','翻斗小学','翻斗中学','翻斗大学'),
                      (2019003,'本科','物理学','翻斗小学','翻斗中学','翻斗大学'),(2019004,'本科','数学','翻斗小学','翻斗中学','翻斗大学');

alter table  student
    modify no int unique comment '学号';
alter table  student
    add constraint fk_no foreign key (no) references edu(id);




# 2、多表查询概述
select * from dept,user;# 笛卡尔积,返回全部组合情况
select * from dept,user where dept.id =user.dept_id;# 消除无效笛卡尔积.若id为null，不满足条件，不会被删选出来




# 3、内连接
# 内连接
# 隐式内连接
select user.name , dept.name from user,dept where dept.id =user.dept_id;
select u.name , d.name from user u,dept d where d.id =u.dept_id;
# 显示内连接
select user.name , dept.name from user inner join dept on dept.id =user.dept_id;
select user.name , dept.name from user join dept on dept.id =user.dept_id;
select u.name , d.name from user u join dept  d on d.id =u.dept_id;




# 4、外连接
# 左连接
select u.* ,d.name from user u left outer join dept d on d.id = u.dept_id;
select u.* ,d.name from user u left  join dept d on d.id = u.dept_id;# outer可省略
# 右连接
select d.*,u.*  from user u right join dept d on d.id = u.dept_id;
select d.*,u.*  from dept d left join user u on d.id = u.dept_id;# 等价上一个语句




# 5、自连接
ALTER TABLE user
    add managerid int comment '上级id';
select u1.name,u2.name from user u1 join user u2 on u1.id=u2.managerid;# 无领导不显示
select u1.name '员工',u2.name '领导' from user u1 left join user u2 on u1.id=u2.managerid;# 无领导也显示





# 6、联合查询
select * from user where age>=19
union all
select * from user where gender='男';# 直接拼接，含有重复数据

select * from user where age>=19
union
select * from user where gender='男';# 去重





# 7、子查询
# 标量子查询
select  id from  dept where name='人事部';
select  * from user where dept_id=(select  id from  dept where name='人事部');# 人事部的员工信息
select  * from employee where entrydate>(select  entrydate from  employee where name='人1');# 人1入职之后入职的人

# 列子查询
select  * from user where dept_id in (select  id from  dept where name='人事部'or name='业务部' );# 人事部和业务部的员工信息
select  name,age
from user where age > all (select age from  user where dept_id=(select id from dept where name='人事部') );# 比人事部年龄都大的员工信息
select  name,age
from user where age > any (select age from  user where dept_id=(select id from dept where name='业务部') );# 比业务部任意一个年龄大的员工信息


# 行子查询
select age,managerid from user where name='Tom4';
select * from user where (age,managerid)=(select age,managerid from user where name='Tom4');#与Tom4同年龄同领导的员工

# 表子查询
select age,managerid from user where name='Tom4'or name='Tom5' ;
select * from user where (age,managerid) in (select age,managerid from user where name='Tom4'or name='Tom5');# 与这两个人同年龄同领导的员工

select * from user where age>20;
select * from (select * from user where age>20) e ,dept d where e.dept_id=d.id;# 查询大于20岁的员工和部门信息，不显示null
select * from (select * from user where age>20) e left join dept d on e.dept_id=d.id;# 显示null





# 7、多表查询案例ing