【MySQL基础篇】多表查询

1、多表关系

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

一对多（多对一）

多对多

一对一

 · 一对多（多对一）

案例：部门与员工的关系（一个部门对应多个员工，一个员工对应一个部门）

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

· 多对多

案例：学生与课程的关系（一个学生可以选修多门课程，一门课程可以共多个学生选择）

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

create table student(
    id int primary key auto_increment 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 primary key auto_increment 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 primary key auto_increment comment '主键ID',
    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_course values(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

·  一对一

案例：用户与用户详情的关系（一对一关系多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率）

实现：在任一方加入外键，关联另一方的主键，并且设置外键为唯一的（UNIQUE）

create table tb_user(
    id int primary key auto_increment comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender varchar(1) comment '性别',
    phone char(11) comment '电话'
)comment '用户基本信息表';
create table tb_user_edu(
    id int primary key auto_increment 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 values
                        (null,'黄渤',45,'1','18903944771'),
                        (null,'冰冰',32,'2','18903955771'),
                        (null,'马云',55,'1','17719224870'),
                        (null,'李彦宏',50,'1','15538655111');
insert into tb_user_edu values
                            (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
                            (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
                            (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
                            (null,'本科','应用数学','阳泉区第一小学','阳泉区第一中学','清华大学',4);

2、多表查询概述 

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

案例：我们用员工与所属部门来作为案例

select * from emp,dept;

 但问题是我们明明只有五条数据却显示了25行信息，这种现象称为笛卡尔积。

笛卡尔积：在数学中两个集合，A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

select * from emp,dept where emp.dept_id=dept.id;

 多表查询分类

· 连接查询

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

        外连接：

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

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

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

· 子查询

3、内连接查询 

内连接查询语法：

隐式内连接：

 SELECT 字段列表 FROM 表1，表2 WHERE 条件 ...;

显示内连接： 

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;

内连接查询的是两张表的交集部分 

#内连接演示
#1、查询每一个员工的姓名，及关联部门的名称（隐式内连接实现）
#连接条件：emp.dept_id=dept.id
select emp.name,dept.name from emp,dept where emp.dept_id=dept.id;
#2、查询每一个员工的姓名，及关联部门的名称（显示内连接实现）--关键字：INNER JOIN ... ON ...
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;

4、外连接查询 

外连接查询语法：

左外连接：

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

查询的是左表的所有数据包含两表交集部分的数据 

右外连接： 

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

查询的是右表的所有数据包含两表交集部分的数据 

#左外连接和右外连接
#1、查询emp表的所有数据，和对应部门的信息（左外连接）
select * from emp left outer join dept on emp.dept_id = dept.id;
#2、查询dept表的所有数据，和对应员工信息（右外连接）
select * from dept right outer join emp on dept.id = emp.dept_id;

5、自连接查询

自连接查询语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...; 

自连接查询，可以是内连接查询，也可以是外连接查询。 

#自连接
#1、查询员工及其所属领导的名字
select a.name,b.name from emp a,emp b where a.managerid=b.id;
#2、查询所有员工 emp 及其领导的名字 emp，如果员工没有领导也要查询出来
select a.name,b.name from emp a left join emp b on a.managerid=b.id;

6、联合查询 

 联合查询-union，union all

对于联合查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...

UNION [ALL]

SELECT 字段列表 FROM 表B ...;

#联合查询
#将薪资低于10000的员工，和年龄大于50岁的员工全部查询出来
select * from emp where salary<10000
union all
select * from emp where age>50;
#对查询结果去重
select * from emp where salary<10000
union 
select * from emp where age>50;

 总结：对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致；

union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

7、子查询

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

SELECT * FROM t1 WHERE column1=(SELECT column1 FROM t2);

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT 的任何一个。

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

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

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

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

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

根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。

标量子查询 

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

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

#标量子查询
#1、查询研发部所有员工信息
#第一步：查询研发部部门ID
select id from dept where name='研发部';
#第二步：根据研发部部门ID查询员工信息
select * from emp where dept_id=1;
select * from emp where dept_id=(select id from dept where name='研发部');
#2、查询在”杨逍“入职之后的员工信息
#第一步：查询”杨逍“的入职日期
select entrydate from emp where name='杨逍';
#第二步：查询指定日期之后入职员工的信息
select * from emp where entrydate > '2000-11-03';
select * from emp where entrydate >(select entrydate from emp where name='杨逍');

列子查询 

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用操作符：IN、NOT IN、ANY、SOME、ALL

#列子查询
#1、查询”总经办“和”研发部“所有员工的信息
#第一步：查询总经办和研发部的部门ID
select id from dept where name='总经办'||name='研发部';
#第二步：根据部门ID，查询员工信息
select * from emp where dept_id in (5,1);
select * from emp where dept_id in(select id from dept where name='总经办'||name='研发部');
#2、查询比研发部所有员工工资都高的员工信息
#第一步：查询研发部部门工资
select id from dept where name='研发部';
select salary from emp where dept_id=1;
select salary from emp where dept_id=(select id from dept where name='研发部');
#第二步：比研发部所有员工工资都高的员工信息
select * from emp where salary > all(select salary from emp where dept_id=(select id from dept where name='研发部'));
#3、查询比研发部其中任意一人工资高的员工信息
#第一步：查询研发部所有员工的工资
select salary from emp where dept_id=(select id from dept where name='研发部');
#第二步：比研发部其中任意一人工资高的信息
select * from emp where salary > any(select salary from emp where dept_id=(select id from dept where name='研发部'));

行子查询 

 子查询的结果是一行（可以是多列），这种查询就称行子查询。

常用操作符：= 、<>、IN、NOT IN

#行子查询
#1、查询与”张无忌“的薪资及所属领导相同的员工信息
#第一步：查询张无忌的薪资及直属领导
select salary,emp.managerid from emp where name='张无忌';
#第二步：查询与”张无忌“的薪资及所属领导相同的员工信息
select * from emp where salary=(select salary from emp where name='张无忌')&& emp.managerid=(select managerid from emp where name='张无忌');
#上面这种方法是标量子查询
select * from emp where (salary,managerid)=(select salary,emp.managerid from emp where name='张无忌');

表子查询 

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用操作符：IN

#表子查询
#1、查询与杨逍与韦一笑的职位和薪资相同的员工信息
#第一步：查询与杨逍与韦一笑的职位和薪资
select job,salary from emp where name='杨逍'||name='韦一笑';
#第二步：查询与杨逍与韦一笑的职位和薪资相同的员工信息
select * from emp where (job,salary) in(select job,salary from emp where name='杨逍'||name='韦一笑');
#2、查询入职日期是”2002-01-01“之后的员工信息，及其部门信息
#第一步：入职信息是”2002-01-01“之后的员工信息
select * from emp where entrydate > '2002-01-01';
#第二步：查询这部分员工，对应的部门信息
select e.*,d.* from (select * from emp where entrydate > '2002-01-01') e left join dept d on e.dept_id=d.id;

 多表查询案例

#多表查询案例
#1、查询员工的姓名、年龄、职位、部门信息。(隐式内连接）
select emp.name,emp.age,emp.job,dept.name from emp,dept where emp.dept_id=dept.id;
#2、查询年龄小于30岁的员工姓名、年龄、职位、部门信息（显示内连接）
select emp.name,emp.age,emp.job,dept.name from emp inner join dept on emp.dept_id=dept.id where emp.age<30;
#3、查询拥有员工的部门ID、部门名称
select distinct dept.id,dept.name from emp,dept where emp.dept_id=dept.id;
#4、查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来
select emp.*,dept.name from emp left outer join dept on emp.dept_id = dept.id where emp.age>40;
#5、查询所有员工的工资等级
#准备工作
create table salarygrade(
    grade int,
    losal int,
    hisal int
)comment'工资等级表';
insert into salarygrade values(1,0,3000),
                              (2,3001,5000),
                              (3,5001,8000),
                              (4,8001,10000),
                              (5,10001,15000),
                              (6,15001,20000);
select emp.name,salarygrade.grade from emp,salarygrade where emp.salary>=salarygrade.losal and emp.salary<=salarygrade.hisal;
select emp.name,salarygrade.grade from emp,salarygrade where emp.salary between salarygrade.losal and salarygrade.hisal;
#6、查询研发部所有员工信息及工资等级
select e.*,s.grade from emp e,salarygrade s,dept d where e.dept_id=d.id and e.salary between s.losal and s.hisal and d.name='研发部';
#7、查询研发部员工的平均工资
select avg(emp.salary) from emp,dept where emp.dept_id=dept.id and dept.name='研发部';
#8、查询工资比韦一笑高的员工信息
select * from emp where salary>(select salary from emp where name='韦一笑');
#9、查询比平均薪资高的员工信息
select * from emp where salary >(select avg(salary) from emp where id);
#10、查询低于研发部门平均工资的员工信息
select * from emp where salary<(select avg(salary) from emp,dept where dept.name='研发部' and emp.dept_id=dept.id);
#11、查询所有部门信息，并统计部门员工人数
select d.id,d.name,(select count(*) from emp e where e.dept_id=d.id)'人数' from dept d;