0


【sql】常用命令三多表查询

多表查询,也称为关联查询,指两个或更多个表一起完成查询操作。

项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:一对多(多对一)、多对多、一对一

多表查询方式主要分为两类:
1 连接查询。连接查询有以下几种方式:
内连接:相当于查询A、B交集部分数据
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据
右外连接:查询右表所有数据,以及两张表交集部分数据
自连接:当前表与自身的连接查询,自连接必须使用表别名
2 子查询

准备数据:
创建两张表:部门表、雇员表,部门和雇员表是一对多的关系。雇员表中包含部门id,两张表靠这个字段关联起来。

内连接:内连接的语法分为两种: 隐式内连接、显式内连接。
场景1:查询每一个员工的姓名 , 及关联的部门的名称。
隐式内连接语法:

SELECT emp.name,dept.name FROM emp ,dept where emp.dept_id = dept.id 

显示内连接语法:

SELECT emp.name,dept.name FROM emp INNERJOIN dept ON emp.dept_id = dept.id 

外连接:
场景2:查询emp表的所有数据, 和对应的部门信息。
左外连接:

SELECT emp.*,dept.name FROM emp LEFTJOIN dept ON emp.dept_id = dept.id

查询结果包含emp表中没有部门属性的雇员数据

在这里插入图片描述
右外连接写法:

select emp.*,dept.name from dept RIGHTJOIN emp on emp.dept_id = dept.id

场景3:
查询dept表的所有数据, 和对应的员工信息
左外连接写法:

SELECT dept.*,emp.*from dept LEFTJOIN emp on emp.dept_id = dept.id

右外连接写法:

select dept.*, emp.*from emp RIGHTJOIN dept on emp.dept_id = dept.id;
注意:左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺序就可以了。而我们在日常开发使用时,更偏向于左外连接。

自连接
自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。而对于自连接查询,可以是内连接查询,也可以是外连接查询。
场景4:查询员工及其所属领导的名字

SELECT a.name AS empName ,b.name AS leaderName FROM emp AS a JOIN emp AS b ON a.managerid = b.id

在这里插入图片描述
场景5:查询所有员工及其领导的名字, 如果员工没有领导, 也需要查询出来

SELECT a.name AS empName ,b.name AS leaderName FROM emp AS a LEFTJOIN emp AS b ON a.managerid = b.id

在这里插入图片描述

注意:在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底是哪一张表的字段。

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

注意:
1 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
2 union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。

场景6:将薪资低于 5000 的员工 , 和年龄大于 50 岁的员工全部查询出来。
当前对于这个需求,我们可以直接使用多条件查询,使用逻辑运算符 or 连接即可。 也可以通过 union / union all 来联合查询。

SELECT*FROM emp WHERE salary>5000UNIONALLSELECT*FROM emp WHERE age >50
注意:union all查询出来的结果,仅仅进行简单的合并,并未去重。

在这里插入图片描述

SELECT*FROM emp WHERE salary>5000UNIONSELECT*FROM emp WHERE age >50

union 联合查询,会对查询出来的结果进行去重处理。
在这里插入图片描述
子查询
根据子查询结果不同,分为:
1 标量子查询(子查询结果为单个值)
2 列子查询 (子查询结果为一列)
3 行子查询 (子查询结果为一行)
4 表子查询 (子查询结果为多行多列)
根据子查询位置,分为:
1 WHERE之后
2 FROM之后
3 SELECT之后

标量子查询:子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。常用的操作符:= <> > >= < <=
场景7:查询 “销售部” 的所有员工信息

SELECT*FROM emp WHERE dept_id =(SELECT id FROM dept WHERE name='销售部')

列子查询:子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL
场景8:查询 “销售部” 和 “市场部” 的所有员工信息

SELECT*FROM emp WHERE dept_id in(SELECT id FROM dept WHERE name='销售部'OR name='市场部')

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

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

行子查询:子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。常用的操作符:= 、<> 、IN 、NOT IN
场景10:查询与 “安小鸟” 的薪资和直属领导都相同的员工信息 ;

select*from emp where(salary,managerid)=(select salary, managerid from emp where name ='安小鸟');

表子查询:子查询返回的结果是多行多列,这种子查询称为表子查询。常用的操作符:IN
场景11: 查询与 “眉庄” , “年世兰” 的职位和薪资相同的员工信息

SELECT*from emp where(job,salary )in(select job,salary from emp where name='眉庄'or name ='年世兰')

场景12:查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息

select e.*, d.*from(select*from emp where entrydate >'2006-01-01') e leftjoin dept d on e.dept_id = d.id ;
标签: sql 数据库

本文转载自: https://blog.csdn.net/ximianyang/article/details/140665108
版权归原作者 嚯嚯啾咪 所有, 如有侵权,请联系我们删除。

“【sql】常用命令三多表查询”的评论:

还没有评论