mysql学习笔记-多表查询

多表查询


一、多表关系

-- 1、一对多
-- 2、多对多
-- 3、一对一
-- 一对多 --
use itlte;

数据准备

一个员工属于一个部门，而一个部门有多个员工
create table dept(
        id int auto_increment primary key comment'主键ID',
        name varchar(50) not null comment'部门名称'
)comment'部门表';
insert into dept (id,name) values(null,'研发部'),(null,'市场部'),(null,'财务部'),(null,'销售部'),(null,'总经办');


create table emp(
        id int auto_increment primary key comment'主键ID',
        name varchar(10) not null comment '姓名',
        age int comment'年龄',
        job varchar(50) comment'职位',
        salary int comment'薪酬',
        entrydate date comment'入职时间',
        managerid int comment'直属领导ID',
        dept_id int comment'部门ID'
)comment'员工表';
# 添加外键
alter    table emp add constraint fk_emp foreign key (dept_id) references dept(id);

insert into emp (id,name,age,job,salary,entrydate,managerid,dept_id) values
        (1,'曹操',98,'老板',9000,'1998-09-03',1,5),
        (2,'刘备',67,'蜀国老板',3600,'1995-01-09',2,3),
        (3,'关羽',36,'马弓手',9000,'1995-09-08',3,2),
        (4,'张飞',37,'刀斧手',8800,'1997-08-04',3,2),
        (5,'赵云',28,'前锋',12000,'1994-09-06',3,3),
        (6,'刘禅',8,'辅助',1100,'2003-09-12',1,1);


select database();
use itlte;
-- 多对多 --
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, '林黛玉', '2022090101'),(null, '贾宝玉', '2022090102'),(null, '袭人', '2022090103'),(null, '薛宝钗', '2022090104');


create table course(
        id int auto_increment primary key comment'主键ID',
        name varchar(10) comment'课程名称'
)comment'课程表';

insert into course values(null,'篮球'),(null,'足球'),(null,'排球'),(null,'乒乓球');


create table student_course(
        id int auto_increment primary key comment'主键ID',
        studentid int not null comment'学生ID',
        courseid  int not null comment'课程ID'
)comment'学生课程中间表';
# 添加外键
    alter    table student_course add constraint fk_courseid foreign key (courseid) references course(id);
    alter    table student_course add constraint fk_studentid foreign key (studentid) references student(id);
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'性别',
        phone char(11) comment'手机号'
)comment'用户基本信息表';

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'小学',
        middlesschool varchar(50) comment'中学',
        university    varchar(50) comment'大学',
        userid        int unique comment'用户ID',
        constraint fk_userid foreign key (userid) references tb_user(id)
)comment'用户教育信息表';
-- alter    table  tb_user_edu add constraint fk_userid foreign key (userid) references tb_user(id);
insert into tb_user(id,name,age,gender,phone)values
        (null,'朱元璋','19','1','19800001111'),
        (null,'张士诚','36','1','19800002222'),
        (null,'郭子兴','28','1','19800003333'),
        (null,'陈友谅','41','1','19800004444'),
        (null,'韩灵儿','18','2','19800005555');
        
insert into tb_user_edu(id,degree,major,primaryschool,middlesschool,university,userid)values
        (null,'本科','造反','凤阳小学','凤阳中学','凤阳大学','1'),
        (null,'硕士','起义','江苏小学','江苏中学','江苏大学','2'),
        (null,'本科','国防','杭州市第一小学','杭州市第一中学','杭州大学','3'),
        (null,'本科','厚黑学','湖北小学','湖北中学','湖北省大学','4'),
        (null,'本科','舞蹈','凤阳小学','凤阳中学','凤阳大学','5');
                

二、多表查询
1、简介
从多表查询数据 笛卡尔积（数学中，A集合和B集合的所有组成情况）
select * from emp,dept; # 返回一个笛卡尔积

select * from emp,dept where emp.dept_id=dept.id; # 消除无效笛卡尔积


2、多表查询分类
        a、连接查询
                a1)内连接：相当于查询A,B交集部分数据
                        隐式内连接：
                        语法 select 字段列表 from 表1,表2 where 条件……;
                        显式内连接：
                        语法 select 字段列表 from 表1 [inner] join 表2 ON 连接条件……;
                a2)外连接：
                            左外连接：查询左表所有数据，以及两张表交集部分数据
                            语法 select 字段列表 from 表1 left[outer] join 表2  on 条件……;
                            右外连接：查询右表所有数据，以及两张表交集部分数据
                            语法 select 字段列表 from 表1 right[outer] join 表2  on 条件……;
                a3)自连接：当前表与自身的连接查询，自连接必须使用表别名
                        语法 select 字段列表 from 表A 别名A join 表B 别名B ON 条件……;
        b、联合查询 - union，union all
            1、对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
            2、多张表的列数必须保持一致，字段类型也要保持一致。
            3、union all 会将查询到的数据直接合并在一起，union会进行重复数据去重
        select 字段列表 from 表A……
        union all
        select 字段列表 from 表B……;
        
        c、子查询
            标量子查询
            子查询返回的结果是单个值（数字，字符串，日期等），最简单的形式，这种查询为 标量子查询
            常用的操作符：= <> > >= < <=
        d、列子查询
            子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
            常用的操作符：IN 在指定的集合范围之内, 
                                        NOT IN 不在指定的集合范围, 
                                        ANY 子查询返回列表中，有任意一个满足即可, 
                                        SOME与any等同，使用some的地方都可以使用any,  
                                        ALL 子查询返回列表的所有值都必须满足
        e、行子查询
            子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
            常用的操作符：=、<>、IN、NOT IN
        f、表子查询
            子查询返回的结果是多行多列，这种子查询称为表子查询
            常用的操作符：IN
            
            
        
-- A、内连接练习
-- 1、查询每一个员工的姓名，及关联的部门名称（隐式内连接）
-- 表结构 emp ,dept
-- 连接条件 emp.dept_id=dept.id
select emp.name,dept.name as '部门名称' from emp,dept where emp.dept_id=dept.id;
# 表起别名
select e.name,d.name as '部门名称' from emp as e ,dept as d  where e.dept_id=d.id;

-- 2、查询每一个员工的姓名，及关联的部门名称（显式内连接）
select e.name,d.name as '部门名称' from emp as e   INNER JOIN   dept as d  ON e.dept_id = d.id;
# a1、内连接 inner可省略
select e.name,d.name as '部门名称' from emp as e         JOIN   dept as d  ON e.dept_id = d.id;

# a2、外连接练习
-- 表结构 emp ,dept
-- 连接条件 emp.dept_id=dept.id
-- 1、查询emp表的所有数据，和对应的部门信息（左外连接）
select * from emp LEFT outer JOIN dept on emp.dept_id=dept.id;
-- 2、查询emp表的所有数据，和对应的部门信息（右外连接）
select * from emp right outer JOIN dept on emp.dept_id=dept.id;


# a3、自连接练习
-- 表结构 emp
-- 连接条件 emp.dept_id=dept.id
语法 select 字段列表 from 表A 别名A join 表B 别名B ON 条件……;
use itlte;
select *from emp;
select a.name '员工', b.name 领导 from emp as a left join emp as b on a.managerid=b.id;


# b、联合查询 union union all
-- 1、将工资低于5000的员工，和年龄大于50岁的鱼啊弄个全部查询出来

select * from emp where salary <5000
union all
select * from emp where age>50;

-- 去重
select * from emp where salary <5000
union
select * from emp where age>50;



# c、标量子查询
-- 1、查询"财务部"的所有员工信息
-- a 查询销售部的id
select id from dept where name='财务部';
-- b 根据财务部的id查询员工信息
select * from emp where dept_id =3;

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


-- 2、查询关羽入职之后的员工信息
-- select entrydate from emp where name= '关羽';
select * from emp where entrydate > (select entrydate from emp where name= '关羽');


# d、列子查询
-- 1、查询销售部和市场部的所有员工信息
use itlte;
select id from dept where name='财务部' or name = '市场部';

select * from emp where dept_id in (2,3);

select * from emp where dept_id in (select id from dept where name='财务部' or name = '市场部');
-- 2、查询比财务部所有人工资都高的员工信息
select id from dept where name ='财务部';

select max(salary) from emp where managerid = (select id from dept where name ='财务部');

select * from emp where salary>  all (select salary from emp where managerid = (select id from dept where name ='财务部'));

-- 3、查询比研发部其中任意一个人工资高的员工信息
select min(salary) from emp where managerid = (select id from dept where name ='研发部');
select * from emp where salary>  any (select salary from emp where managerid = (select id from dept where name ='研发部'));
select * from emp where salary>  some (select salary from emp where managerid = (select id from dept where name ='研发部'));

# e、行子查询
-- 1、查询与 赵云 的薪资及直属领导相同的员工信息
-- a、查询赵云薪资及领导
select salary,managerid from emp where name='赵云';

select * from emp where (salary,managerid) = (9000,3);

select * from emp where (salary,managerid) = (select salary,managerid from emp where name='赵云');

select * from emp where (salary,managerid) in (select salary,managerid from emp where name='赵云');

select * from emp where (salary,managerid) not in (select salary,managerid from emp where name='赵云');


# f、表子查询
-- 1、查询和关羽、张飞职位薪资相同的员工信息

select job,salary from emp where name in ('关羽','张飞'); 

select * from emp where (job,salary) in (select job,salary from emp where name in ('关羽','张飞'));

-- 2、查询入职1995-01-01之后入职的员工信息，及部门信息
select * from (select * from emp where entrydate > '1995-01-01')as p left join dept on p.dept_id=dept.id;


-- 案例
create table salgrade(
            grade int,
            losal int,
            hisal int
            )comment'薪资等级表';
            
insert into salgrade values 
            (1,0,3000),
            (2,3001,5000),
            (3,5001,8000),
            (4,8001,10000),
            (5,10001,15000),
            (6,15001,20000),
            (7,20001,25000),
            (8,25001,50000)

-- 1、查询员工的姓名、年龄、职位、部门信息。
use itlte;
select * from dept;
select name,age,job from emp;

select emp.name,emp.age,emp.job,dept.name as 部门信息 from emp left join dept on emp.dept_id =dept.id ;


-- 2、查询年龄小于30岁的员工姓名，年龄，职位，部门信息。
select emp.name,emp.age,emp.job,dept.name as 部门信息 from emp left join dept on emp.dept_id =dept.id where emp.age<30; 

-- 3、查询拥有员工的部门ID，部门名称
SELECT DISTINCT 字段列表 FROM 表名;
# 查询去重后的 部门id
select distinct dept_id from emp where dept_id is not null;

# 部门表 列子查询 去重的部门id
select * from dept where id in (select distinct dept_id from emp where dept_id is not null);

-- 4、查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门也要展示出来
select * from emp where age >40;
# 表子查询
select * from (select * from emp where age >40) as a left join dept on a.dept_id=dept.id;
# 左外连接
select * from emp left join dept on emp.dept_id=dept.id where emp.age >40;

-- 5、查询所有员工的工资等级。
-- 表：emp salgrade
-- 连接条件 emp.salary>=salgrade.losal and emp.salary<=salgrade.hisal
select * from emp,salgrade where emp.salary>=salgrade.losal and emp.salary<=salgrade.hisal;
select * from emp,salgrade where  emp.salary BETWEEN salgrade.losal and salgrade.hisal;

-- 6、查询 研发部 所有员工的信息及工资等级
-- 表 emp，salgrade，dept
-- 连接条件 emp.salary>=salgrade.losal and emp.salary<=salgrade.hisal，emp.dept_id=dept.id
-- 查询条件 dept.name
select e.*,s.grade from emp e ,dept d ,salgrade s where e.dept_id=d.id and e.salary between s.losal and s.hisal and d.name='研发部';

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

-- 8、查询工资比 刘备 高的员工信息
-- 表 emp
select salary from emp where name = '刘备';
select * from emp where salary < (select salary from emp where name = '刘备');
-- 9、查询比平均薪资高的员工信息
-- 平均工资
select avg(salary) from emp;

select * from emp where salary>(select avg(salary) from emp);

-- 10、查询低于本部门平均工资的员工信息
-- 查询部门平均工资
select avg(salary) from emp as e1 where e1.dept_id = 1;
select avg(salary) from emp as e1 where e1.dept_id = 2;
-- 查询低于本部门工资员工信息
select *,(select avg(e1.salary) from emp as e1 where e1.dept_id = e2.dept_id)as 平均 from emp as e2 where e2.salary<(select avg(e1.salary) from emp as e1 where e1.dept_id = e2.dept_id);

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

select count(*) from emp where dept_id =1;
-- 12、查询所有的学生的选课情况，展示出学生名称，学号，课程名称
-- 表 student,coures,student_coures
-- 连接条件 student.id = student_course.studentid     course.id = student_course.couresid

select s.name,s.no,c.name '课程' from student s,student_course sc, course c where s.id=sc.studentid and c.id = sc.courseid;