mysql学习笔记-多表查询
多表查询
一、多表关系
-- 1、一对多
-- 2、多对多
-- 3、一对一
-- 一对多 --数据准备
一个员工属于一个部门，而一个部门有多个员工 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);
-- 多对多 --数据准备
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、简介和语法
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……; 3、子查询 a、标量子查询 子查询返回的结果是单个值（数字，字符串，日期等），最简单的形式，这种查询为 标量子查询 常用的操作符：= <> > >= < <= b、列子查询 子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。 常用的操作符：IN 在指定的集合范围之内, NOT IN 不在指定的集合范围, ANY 子查询返回列表中，有任意一个满足即可, SOME与any等同，使用some的地方都可以使用any, ALL 子查询返回列表的所有值都必须满足 c、行子查询 子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。 常用的操作符：=、<>、IN、NOT IN d、表子查询 子查询返回的结果是多行多列，这种子查询称为表子查询 常用的操作符：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;
B、外连接练习
-- 表结构 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;
C、自连接练习
-- 表结构 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;
D、联合查询
# 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;
三、子查询
1、标量查询
# 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= '关羽');
2、列子查询
# 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 ='研发部'));
3、行子查询
# 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='赵云');
4、表子查询
# 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;
