本文主要是介绍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;
这篇关于mysql学习笔记-多表查询的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!