MySQL多表查询详解

2021-02-16 11:23:31 浏览数 (1)

多表查询

1. 表与表之间的关系

<1> 一对一

  • 用户表和身份信息表，用户表是主表
  • 男人表、女人表
代码语言：txt复制
	create table man(
代码语言：txt复制
		mid int primary key auto_increment,
代码语言：txt复制
		mname varchar(32),
代码语言：txt复制
		wid int unique
代码语言：txt复制
	);
代码语言：txt复制
	create table woman(
代码语言：txt复制
		wid int primary key auto_increment,
代码语言：txt复制
		wname varchar(32)
代码语言：txt复制
	);

<2> 一对多

  • 最常见得表关系，用户表和订单表
  • 员工表、部门表 create table emp( empno int primary key auto_increment, ename varchar(32) deptno int );
代码语言：txt复制
	create teble dept(
代码语言：txt复制
		deptno int primary key auto_increment,
代码语言：txt复制
		dname varchar(32)
代码语言：txt复制
	);

<3> 多对多

  • 学生表和课程表，通常情况都是将多对多的关系拆分为一对多或者多对一的关系
  • 至少需要三张表
代码语言：txt复制
	create table student(
代码语言：txt复制
		cid int primary key auto_increment,
代码语言：txt复制
		sname varchar(32)
代码语言：txt复制
	);
代码语言：txt复制
	insert into student (sname) values('小张');
代码语言：txt复制
	insert into student (sname) values('小李');
代码语言：txt复制
	insert into student (sname) values('小王');
代码语言：txt复制
	create table course(
代码语言：txt复制
		sid int primary key auto_increment,
代码语言：txt复制
		cname varchar(32)
代码语言：txt复制
	);
代码语言：txt复制
	insert into course (cname) values('语文');
代码语言：txt复制
	insert into student (sname) values('数学');
代码语言：txt复制
	insert into student (sname) values('英语');
代码语言：txt复制
	insert into student (sname) values('化学');
代码语言：txt复制
	create table s_c(
代码语言：txt复制
		cid int,
代码语言：txt复制
		sid int
代码语言：txt复制
	);
代码语言：txt复制
	insert into s_c(sid, cid) values (1, 1);
代码语言：txt复制
	insert into s_c(sid, cid) values (1, 2);
代码语言：txt复制
	insert into s_c(sid, cid) values (1, 3);
代码语言：txt复制
	insert into s_c(sid, cid) values (1, 4);
代码语言：txt复制
	insert into s_c(sid, cid) values (2, 2);
代码语言：txt复制
	insert into s_c(sid, cid) values (2, 4);
代码语言：txt复制
	insert into s_c(sid, cid) values (3, 1);
代码语言：txt复制
	insert into s_c(sid, cid) values (3, 3);

2. 为什么要使用多张表

  • 避免出现大量的数据的冗余
  • 并不是表拆的越多越好，需要根据实际情况进行拆分

3. 概念

  • 同时查询多张表

4. 多表查询的分类

<1> 合并查询

  • union, union all
  • 合并结果集，就是把两个select语句的查询结果合并到一起。（相当于并集）
  • 合并的两个结果，列数和列的顺序，类型需要一致
代码语言：txt复制
	create table emp(
代码语言：txt复制
		empno int primary key auto_increment,
代码语言：txt复制
		ename varchar(32)
代码语言：txt复制
	);
代码语言：txt复制
	create table dept(
代码语言：txt复制
		depeno int primary key auto_increment,
代码语言：txt复制
		dname varchar(32)
代码语言：txt复制
	);
代码语言：txt复制
	select * from emp union select * from dept;
代码语言：txt复制
	select * from emp union all select * from dept;

<2> 连接查询

代码语言：txt复制
-- 员工表
代码语言：txt复制
create table emp(
代码语言：txt复制
	empno int primary key auto_increment,  # 员工编号
代码语言：txt复制
	ename varchar(32),  # 员工姓名
代码语言：txt复制
	job varchar(32),  	# 员工职位
代码语言：txt复制
	mgr int,  						# 上级编号
代码语言：txt复制
	hiredate date,  		# 入职时间
代码语言：txt复制
	sal double,  				# 薪资
代码语言：txt复制
	comm double,  			# 奖金
代码语言：txt复制
	deptno int  				# 员工所属部门
代码语言：txt复制
);
代码语言：txt复制
-- 部门表
代码语言：txt复制
create table dept(
代码语言：txt复制
	deptno int primary key auto_increment,  # 部门编号
代码语言：txt复制
	dname varchar(32),  # 部门名称
代码语言：txt复制
	loc varchar(32)  		# 部门地址
代码语言：txt复制
);
  • 内连接 - inner join .... on 、 join , , - inner join 是一个比较运算符，只返回符合条件的行 - 例如： - select from emp inner join dept on emp.deptno=dept.deptno; - select from emp e, dept d where e.deptno = d.deptno; - select * from emp e join dept d where e.deptno = d.deptno;
  • 外连接 - 左外连接 LEFT OUTER JOIN | left join .... on - 代表查询，左边行的全部，右边没有则null - select from emp e LEFT OUTER JOIN dept d on e.deptno = d.deptno; - 右外连接 right join | right outer join .... on - 有连接包含right join 右表所有的行，如果左表中某行在右表没有匹配，则结果中对应的左表的部分全部为空(null) - select from emp e RIGHT OUTER JOIN dept d on e.deptno = d.deptno;
  • 自连接 - 自连接就是说，在同一个数据表中，看作是两个表，表示查找每个人的领导，如果没有领导，则显示无领导 - 把一张表看作成两张表，一张员工表，一张领导表，都是emp表 - select e.ename, el.ename from emp e left join emp el on e.mgr = el.empno;
  • 自然连接: natural join (join) | natural left join (同 left join) | natural right join (同 right join) - 自然连接会自动判断，以两个表中相同的字段为连接条件，返回查询结果。 - 注意： 内连接不写连接条件会出现笛卡尔积的结果，应该避免这种情况，而外连接不写连接条件会报错 - select from emp natural join dept; - select from emp NATURAL left join dept; - select * from emp NATURAL right join dept;

<3> 子查询 (ANY子查询、IN子查询、SOME子查询、ALL子查询)

  • 子查询解决的问题： - 谁的薪资比张三高 - select sal from emp where ename='张三'
  • 定义 - 子查询允许把一个查询嵌套在另一个查询当中 - 子查询又叫做内部查询，相当于内部查询，包含内部查询的就成为外部查询，子查询的结果被主查询所使用。
  • 注意的问题： - 括号 - 可以在主查询的where select having from 后面，都可以使用子查询 - 不可以在group by 后面使用子查询 - 主查询和子查询可以不是同一张表；只有子查询返回的值，主查询可以使用 需求：查询部门名称是人力部的员工信息 -- 第一种方式：利用子查询 select from emp where deptno=(select deptno from dept where dname='人力部') -- 第二种方式：利用关联查询 select from emp e, dept d where e.deptno = d.deptno and d.dname = '人力部';
代码语言：txt复制
		SQL优化: 尽量使用多表查询
代码语言：txt复制
		         绝大部分的子查询在最终执行的时候都是转换成一个多表查询来执行的。 通过SQL的执行计划可以看出来
代码语言：txt复制
				 通过SQL执行计划会发现两种方式执行的一样的。
代码语言：txt复制
- from 后面的子查询
		需求：
			查询员工号  姓名  薪资
			select empno, ename, sal from emp;
- 一般不在子查询中进行排序
- 一般先执行子查询，再去执行主查询
ANY 关键字
代码语言：txt复制
假设any内部的查询返回结果个数是三个，如: result1, result2, result3, 那么
代码语言：txt复制
select .... from .... where a > any(....)
代码语言：txt复制
->
代码语言：txt复制
select .... from .... where a > result1 or a > result2 or a > result3
代码语言：txt复制
需求：
代码语言：txt复制
	查询工资比1号部门中任意一个员工高的员工信息
代码语言：txt复制
	select * from emp where sal > any(select sal from emp where deptno = 1);
ALL 关键字
  • ALL 关键字与 ANY 关键字类似，只不过把上面的or 改成 and
代码语言：txt复制
	select .... from .... where a > all(....)
代码语言：txt复制
	->
代码语言：txt复制
	select .... from .... where a > result1 and a > result2 and a > result3
代码语言：txt复制
	需求：
代码语言：txt复制
		查询工资比1号部门中所有员工高的员工信息
代码语言：txt复制
	select * from emp where sal > any(select sal from emp where deptno = 1);
代码语言：txt复制
	select * from emp where sal > all(select sal from emp where deptno = 1);
SOME 关键字
代码语言：txt复制
some 关键字和 any 关键字是一样的功能，所以：
代码语言：txt复制
select .... from .... where a > any(....)
代码语言：txt复制
->
代码语言：txt复制
select .... from .... where a > result1 or a > result2 or a > result3
IN 关键字
代码语言：txt复制
In 运算符用于where 表达式中，以列表项的形式支持多个选择，语法如下：
代码语言：txt复制
	where column in (v1, v2, v3 ....);
代码语言：txt复制
	where column not in (v1, v2, v3 ....);
代码语言：txt复制
	当in 前面加上not 运算符时，表示与in 相反的意思，即不在这些列表项中选择。
代码语言：txt复制
案例：
代码语言：txt复制
	查询部门名称是人力部和研发部的员工
代码语言：txt复制
select * from emp where deptno in (select deptno from dept where dname = '人力部' or dname = '研发部');
nat 数据库 sql NAT网关

0 人点赞