多表查询（整理）

一、多表关系

概述：项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间互相关联，所以各个表结构之间也存在着各种联系。

基本上有三种：一对多（多对一），多对多，一对一。

一对多（多对一）

多对多

一对一

辅助建表内容：

-- 多表查询 --
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,'黛绮丝','2000100101'),(null,'谢逊','2000100102'),(null,'殷天正','2000100103'),(null,'韦一笑','2000100104');create table course(id int auto_increment primary key comment '主键id',name varchar(10) comment '课程名称'
)comment '课程表';
insert into course values (null,'Java'),(null,'PHP'),(null,'MySQL'),(null,'Hadoop');create table student_course(id int auto_increment primary key comment '主键id',studentid int not null comment '学生id',courseid int not null comment '课程id',constraint fk_studentid foreign key (studentid) references student(id),constraint fk_courseid foreign key (courseid) references course(id)
)comment '学生课程中间表';
insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);create table tb_user(id int auto_increment primary key comment '主键id',name varchar(10) comment '姓名',age int comment '年龄',gender char(1) comment '1: 男 , 2: 女 ',phone char(11) comment '手机号'
)comment '用于多表查询的用户基本信息表';
insert into tb_user(id,name,age,gender,phone)values (null,'英语',45,'1','18800001111'),(null,'冰冰',35,'2','18800002222'),(null,'码云',55,'1','18800008888'),(null,'李彦宏',50,'1','18800009999');create table tb_user_edu(id int auto_increment primary key comment '主键id',degree varchar(20) comment '学历',major varchar(50) comment '专业',primaryschool varchar(50) comment '小学',middleschool varchar(50) comment '中学',university varchar(50) comment '大学',userid int unique comment '用户id',constraint fk_userid foreign key (userid) references tb_user(id)
)comment '用于多表查询的用户教育信息表';
insert into tb_user_edu(id,degree,major,primaryschool,middleschool,university,userid)values (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈院',1),(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),(null,'本科','应用数学','阳泉第一小学','阳泉第一中学','清华大学',4);

二、多表查询概述

概述：指从多张表中查询数据

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

[注]：在多表查询时，需要消除无效的笛卡尔积

三、多表查询分类

概述：

连接查询

内连接：相当于查询A、B交集部分数据

外连接：

左外连接：查询左表所有数据，以及两张表交集部分数据

右外连接：查询右表所有数据，以及两张表交集部分数据

自连接：当前表与自身的连接查询，自连接必须使用表别名

子查询

辅助建表内容：

create table mtdept(id int auto_increment comment '编号' primary key ,name varchar(50) not null comment '部门名称'
)comment '用于练习多表查询的部门表';insert into mtdept(id, name) values (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办'),(6,'人事部');create table mtemp(id int auto_increment primary key comment '编号',name varchar(50) not null comment '姓名',age int comment '年龄',job varchar(20) comment '职位',salary int comment '薪资',entrydate date comment '入职时间',managerid int comment '直属领导id',dept_id int comment '部门id'
)comment '用于练习多表查询的员工表';insert into mtemp (id,name,age,job,salary,entrydate,managerid,dept_id)values  (1,'金庸',66,'总裁',20000,'2000-01-01',null,5),(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),(3,'杨逍',33,'开发',8400,'2000-11-03',2,1),(4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),(5,'常遇春',43,'开发',10500,'2004-09-07',3,1),(6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1),(7,'灭绝',60,'财务总监',8500,'2002-09-12',1,3),(8,'周芷若',19,'会计',4800,'2006-06-02',7,3),(9,'丁敏君',23,'出纳',5250,'2009-05-13',7,3),(10,'赵敏',20,'市场部总监',12500,'2004-10-12',1,2),(11,'鹿杖客',56,'职员',3750,'2006-10-03',10,2),(12,'鹤笔翁',19,'职员',3750,'2007-05-09',10,2),(13,'方东白',19,'职员',5500,'2009-02-12',10,2),(14,'张三丰',88,'销售总监',14000,'2004-10-12',1,4),(15,'俞莲舟',38,'销售',4600,'2004-10-12',14,4),(16,'宋远桥',40,'销售',4600,'2004-10-12',14,4),(17,'陈友谅',42,null,2000,'2011-10-12',1,null);

①连接查询-内连接

隐式内连接

select 字段列表 from 表1，表2 where 条件....;

显式内连接

select 字段列表 from 表1 [inner] jion 表2 on 连接条件...;

[注]：内连接查询的是两张表交集的部分

②连接查询-外连接

左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件....;#相当于查询表1（左表）的所有数据，包含表1和表2交集部分的数据

右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件....;#相当于查询表2（右表）的所有数据，包含表1和表2交集部分的数据

[注]：我的理解是当进行多表查询的时候会有一些是没有数据的，我们给的条件会自动筛除掉，如果我们需要完完全全的展示出来就可以使用左（右）外连接。然后这两个其实就是谁先谁后，用的时候完全可以只用左外连接或者右外连接，就像我们大多数人习惯右手使用东西一样，一般我们会更习惯用左外连接。

③多表查询-自连接（可以是内连接查询，也可以是外连接查询）

select 字段列表 from 表A 别名a jion 表A 别名b on 条件...;

④联合查询-union,union all（就是把多次查询的结果合并起来，形成一个新的查询结果集）

select 字段列表 from 表A...
union [all]
select 字段列表 from 表B...;

[注]：加不加all只有一个区别，union all 会有重复的内容，union 会把重复的内容筛除。

⑤子查询

概念：SQL语句中嵌套select语句，称为嵌套查询，又称子查询

select *from t1 where column1 = (select column1 from t2);

[注]：子查询外部的语句可以是 insert / update / delete / select 的任意一个

根据子查询结果不同，分为：

标量子查询（子查询结果为单个值）

列子查询（子查询结果为一列）

行子查询（子查询结果为一行）

表子查询（子查询结果为多行多列）

根据子查询位置，分为：where之后、from之后、select之后。

-- 标量子查询 --

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式。

常用的操作符：= ，<>，>，>=，<，<=

-- 列子查询 --

子查询返回的结果是一列（可以是多行）。

常用的操作符：in , not in , any , some , all

操作符	描述
in	在指定的合集范围之内，多选一
not in	不在指定的集合范围之内
any	子查询返回列表中，有任意一个满足即可
some	与any等同，使用some的地方都可以使用any
all	子查询返回列表的所有值都必须满足

-- 行子查询 --

子查询返回结果是一行（可以是多列）。

常用的操作符：= ，<> ，in ，not in

-- 表子查询 --

子查询返回的结果是多行多列。

常用的操作符：in

案例练习：

-- 1.查询员工的姓名，年龄，职位，部门信息

-- 2.查询年龄小于30岁的员工姓名，年龄，职位，部门信息

-- 3.查询拥有员工的部门id，部门名称

-- 4.查询所有年龄大于40岁的员工，及其归属的部门名称，如果员工没有分配部门也需要展示出来

-- 5.查询所有员工的工资等级

-- 6.查询“研发部”所有员工的信息及工资等级

-- 7.查询“研发部”员工的平均工资

-- 8.查询工资比“灭绝”高的员工信息

-- 9.查询比平均薪资高的员工信息

-- 10.查询低于本部门平均工资的员工信息

-- 11.查询所有的部门信息，并统计部门的员工人数

-- 12.查询所有学生的选课情况，展示出学生名称，学号，课程名称

辅助建表内容：

create table salgrade(grade int,losal int,hisal int
)comment '用于多表查询的薪资登记表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);