【MySQL】多表查询

多表查询

一对多(多对一)

多对多

一对一

create table tb_user(
	id int auto_increment primary key comment '主键ID!',
	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 PRIMARY KEY 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(id, name, age, gender,phone) values
(null,'黄渤',45,1'18800001111')
(null,'冰冰',35,2'18800002222')
(null,'码云',55,1'18800008888')
(null,'李彦宏',50，1'，18800009999');

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

多表查询概述

多表查询分类

内连接

隐式内连接

select 字段列表 from 表1,表2 where 条件...;

显示内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）

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

查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）

select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件...;

右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件...;

查询emp表的所有数据，和对应的部门信息(左外连接)

select e.*,d.name from emp e left join dept d on e.dept_id=d.id;

查询emp表的所有数据，和对应的部门信息(右外连接)

select e.*,d.name from emp e right join dept d on e.dept_id=d.id;

自连接

查询员工 及其 所属领导的名字

select a.name,b.name from emp a,emp b where a.managerid=b.id;

查询所有员工 emp 及其领导的名字 emp，如果员工没有领导也要查出来

select a.name,b.name from emp a left join emp b where a.managerid=b.id;

联合查询-union,union all

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

将薪资低于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;

union会去重。

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

子查询

标量子查询

根据销售部部门ID，查询员工信息

select * from emp where dept_id=(select id from dept where name='销售部');

查询 方东白 的入职日期

select * from emp where entrydate > (select entrydate from emp where name='方东白');

列子查询

根据部门ID，查询员工信息

select * from emp where dept_id in (select id from dept where name ='销售部' or name='市场部');

查询比财务部所有人工资都高的员工信息

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

行子查询

查询与 张无忌 的薪资和直属领导相同的员工信息

select * from emp where (salary,managerid) = (select salary,managerid from emp where name='张无忌');

表子查询

select * from emp where (job,salary) in (select job,salary from emp where name = '鹿杖客' or name = '宋远桥');