MySQL--多表查询

1、多表关系

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

• 一对多（多对一）

• 多对多

 用可视化界面来展示多对多关联表的关系

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 comment '主键' primary key,
    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);

• 一对一

-- 一对一关系，常用于 将一个表拆分成两个一一对应的表的情况

create table tb_user(
    id int auto_increment comment '主键' primary key,
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '1:男,2:女',
    phone char(11) comment '手机号'
    )comment '用户基本信息表';

create table tb_user_edu(
    id int auto_increment comment '主键' primary key,
    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','18812345678'),
                           (null,'冰冰',35,'2','18834565678'),
                           (null,'马云',55,'1','18812345678'),
                           (null,'李彦宏',50,'1','18815678678');

insert into tb_user_edu values  (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
                                (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
                                (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
                                (null,'本科','应用数学','阳泉区第一小学','阳泉区第一中学','清华大学',4);

2、多表查询概述

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

会产生笛卡尔积 

消除笛卡尔积，去除多余的项前提是两张表要建立连接，然后同时访问两张表，让两张表的外键相同

3、内连接

-- 要求：所在部门的员工名称   例如：张三--研发部
-- 隐式内连接  表1 ，表2 where 连接条件;
select emp.name,dept.name from emp,dept where emp.dept_id=dept.id;
/*由于emp表中有一个数据外键值为null  所以查询不到*/
-- 使用别名进行简化
select e.name ,d.name from emp e,dept d where e.dept_id=d.id;

-- 显式内连接  表1 [inner] join 表2 on 连接条件;
select *from emp e inner join dept d on e.dept_id = d.id;
select e.name , d.name from emp e  join dept d on e.dept_id = d.id;

4、外连接

-- 左外连接 表1 left[outer] join 表2 on 连接条件;
-- 查询 emp表中所有数据，和dpet对应的（部门信息）
select *from emp e left join dept d on d.id = e.dept_id
select d.*,e.* from dept d left join emp e on d.id = e.dept_id

-- 右外连接 表1 right[outer] join 表2 on 连接条件;
-- 查询 dept表中所有数据，和emp对应的（员工信息）
select d.* ,e.* from dept d right join emp e on d.id = e.dept_id
select e.* ,d.* from dept d right join emp e on d.id = e.dept_id
-- 通常用左连接，只用调换表的位置即可

select*from dept;

5、自连接

-- 自连接  同一个表中自己的数据进行组合
-- 1、查询员工--和对应的领导名字
-- 内连接无法查询null的数据  例如：金庸——null
select a.name,b.name from emp a,emp b where a.managerid=b.id;

-- 2、使用外连接，查询员工--和对应的领导名字
select a.name as '员工',b.name as '领导' from emp a left join emp b on a.managerid=b.id

内连接查不出null值

解决办法：使用外连接  表1 left [inner] join 表2 on 条件

联合查询 -union，union all   

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

union相当于or

-- union all 将列数相同的两个表拼接在一起
select*from emp where age>30
union all
select *from emp where salsry>9000;
-- union  将列数相同的两个表拼接在一起并去除重复数据
select*from emp where age>30
union
select *from emp where salsry>9000;

select *from emp where salsry>9000 || age>30;

6、子查询

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

标量子查询

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

-- 1、标量子查询
-- 查询工作为开发的员工姓名
select emp.salsry from emp where name='韦一笑';
-- 查询做工资高于的韦一笑的员工
select emp.name ,emp.salsry from emp where salsry>(select emp.salsry from emp where name='韦一笑');

列子查询

 any可以用some来替换

-- 2、列子查询
-- 查询比销售部 所有人工资都高的员工信息
select id from dept where name='财务部';

select emp.name,emp.salary from emp where dept_id=(select id from dept where name='财务部');

-- 查询比研发部任意员工工资都高的员工
-- ①先查询研发部所有员工id
select id from dept where name ='研发部';
-- ②在查询研发部所有员工的工资
select emp.name,emp.salary from emp where dept_id=(select id from dept where name ='研发部');
-- ③在用all
select *from emp where  salary> all(select emp.salary from emp where dept_id=(select id from dept where name ='研发部'));
select *from emp where  salary> any (select emp.salary from emp where dept_id=(select id from dept where name ='研发部'));

行子查询

-- 3、行子查询
-- 查询 佐山爱 及其领导
select salary,managerid from emp where name ='张无忌';
-- 查询 佐山爱及其领导的工资
select*from emp where  emp.salary=12500 and emp.managerid=1;
select * from emp where  (salary,managerid)=(select salary,managerid from emp where name ='张无忌');

表子查询 

7、多表查询案例

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);

-- 1、查询员工姓名、年龄、职位、部门信息(隐式链接)
select *
from emp e,
     dept d
where e.dept_id = d.id;
-- 2、查询年龄小于30员工姓名、年龄、职位、部门信息(显式链接)
select *
from emp e
         join dept d on d.id = e.dept_id
where age < 30;
-- 3、查询拥有员工ID的部门名称  (内连接)
select distinct d.id, d.name
from emp e,
     dept d
where d.id = e.dept_id;


-- 4、查询所有年龄大于40的员工，及其所属部门名称；如果没有分配部门，也需要显示出来名。(外连接)
select e.name, d.name
from emp e
         left join dept d on d.id = e.dept_id
where age > 40;



-- 5、查询所有员工的工资等级
-- 涉及到emp、salgrade
-- salary>=salgrade.losal and salary<=salgrade.hisal
select e.name as '员工姓名', s.grade as '工资等级'
from emp e,
     salgrade s
where e.salary >= s.losal
  and e.salary <= s.hisal;
select e.name as '员工姓名', s.grade as '工资等级'
from emp e,
     salgrade s
where e.salary between s.losal and s.hisal;

-- 6、查询“研发部”所有员工信息，及其工资等级
-- 先将emp和dept信息组合起来
-- 然后根据salgrade来分工资等级
select e.*, d.name, s.grade as '工资等级'
from emp e,
     dept d,
     salgrade s
where d.id = e.dept_id
  and e.salary between s.losal and s.hisal
  and d.name = '研发部';

-- 7、查询“研发部” 员工的平均工资
select d.name, avg(e.salary)
from emp e,
     dept d
where d.id = e.dept_id
  and d.name = '研发部';


-- 8、查询工资比灭绝高的员工
select e.salary as '灭绝的工资'
from emp e
where e.name = '灭绝';
select *
from emp
where salary > (select e.salary as '灭绝的工资' from emp e where e.name = '灭绝');


-- 9、查询比平均工资高的员工信息
select *
from emp
where salary > (select avg(e.salary)
                from emp e,
                     dept d
                where d.id = e.dept_id
                  and d.name = '研发部');

-- 10、查询低于本部门平均工资的员工
-- 第一个部门的平均工资
select avg(e1.salary)
from emp e1
where e1.dept_id = 1;
-- 第二个部门的平均工资
select avg(e1.salary)
from emp e1
where e1.dept_id = 2;

select yg.*, (select avg(e1.salary) from emp e1 where e1.dept_id = yg.dept_id) as '员工所在部门的平均工资'
from emp yg
where yg.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = yg.dept_id);

-- 11、查询所有部门信息，并统计所有部门员工人数
select d.id,d.name,(select count(*)from emp e where e.dept_id=d.id)'员工数'
from  dept d;



-- 12、查询所有学生的选课情况，展示出学生，名称、课程表
select s.name,c.name
from course c,student s,student_course sc where c.id=sc.courseid and s.id=sc.studentid;