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 studentmodify no int unique comment '学号';
alter table studentadd 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 useradd 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