多表查询
多表关系
-
概述
项目开发中,在进行数据库表结构设计时,会根据业务需求以及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本分为三种:
多表查询概述
-
概述:指从多张表中查询数据
-
笛卡尔积
-
多表查询分类
-
连接查询
内连接:相当于查询A\B交集部分数据
外连接:
-
左外连接:查询左表所有数据、以及两张表交集部分数据
-
有外连接:查询右表所有数据,以及两张表交集部分数据
自连接:当前表与自身的连接查询,自连接必须使用表别名
-
-
子查询
-
内连接
内连接查询的时两张表交集的部分(交集)
内连接查询语法:
-
隐式内连接
select 字段列表 from 表1,表2 where 连接条件 ...;
-
显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;
外连接
外连接查询语法:
-
左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据
-
右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据
自连接
自连接查询语法:
select 字段列表 from 表A 别名A join 表A 别名B on 条件 ...;
自连接查询,可以是内连接查询,也可以是外连接查询。
联合查询-union,union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集
-- 将表A和表B 合并成一个新的查询结果
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- union all会将全部的数据直接合并在一起,union会对合并之后的数据去重。
子查询
-
概念:SQL语句中嵌套select语句,成为嵌套查询,又称子查询。
select * from t1 where column1=(select column1 from t2);
子查询外部的语句可以是
insert / update / delete / select
的任何一个 -
根据子查询结果不同,分为:
- 标量子查询 (子查询结果为单个值)
- 列子查询 (子查询结果为一列)`
- 行子查询 (子查询结果为一行)
- 表子查询 (子查询结果为多行多列)
-
根据子查询位置,分为:
where
之后、from
之后、select
之后。以下表为例,创建 emp表
-- 创建emp表 create table emp( id int auto_increment primary key comment 'ID', name varchar(10) not null comment '姓名', age int check ( age>0 && age<=120 ) not null comment '年龄', job char(10) comment '工作', salary int unsigned comment '薪水', entryDate date not null comment '入职日期', managerId int comment '直属领导', dept_id int not null comment '部门id', dept_name char(10) comment '部门名' ) comment '员工表'; -- 插入数据 insert into emp (id, name, age, job, salary, entryDate, managerId, dept_id, dept_name) values (null,'ztt',20,'总裁',20000,'2002-05-01',null,1,'人事部'), (null,'zs',20,'总裁',20000,'2002-05-10',null,1,'人事部'), (null,'nxl',20,'项目经理',19000,'2009-05-01',1,1,'人事部'), (null,'xcc',20,'项目经理',19000,'2009-05-01',1,2,'财务部'), (null,'ls',20,'职员',2000,'2022-05-01',3,2,'财务部'); -- --------------------------------------------------------------------- -- 创建部门表 create table dept( id int primary key comment 'ID', name char(10) comment '部门名' ) comment '部门表'; -- 插入数据 insert into dept values (1,'人事部'),(2,'财务部'),(3,'销售部');
标量子查询
-
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询成为标量子查询。
-
常用的操作符:= <> > >= < <=
示例:
-- 标量子查询 -- 1.查询“销售部”的所有员工信息 -- ---a.查询“销售部”部门ID select id from dept where name = '销售部'; -- ---b.根据销售部部门ID,查询员工信息 select * from emp where debt_id=(select id from dept where name = '销售部');
列子查询
-
子查询返回的结果时一列(可以是多行),这种子查询成为列子查询。
-
常用的操作符:in、not in、any、some、all
操作符 描述 in 在指定的集合范围之内,多选一 not in 不在指定的集合范围之内 any 子查询返回列表中,又任意一个满足即可 some 与any等同,使用some的地方都可以使用any all 子查询返回列表的所有值都必须满足
示例:
-- 列子查询 -- 例 1 -- 1.查询”销售部“和”市场部“的所有员工信息 -- ---a.查询“人事部” 和”财务部“的部门ID select id from dept where name='人事部' || name='财务部'; -- ---b.根据部门ID,查询员工信息 select * from emp where dept_id in (select id from dept where name='人事部' || name='财务部'); -- ---------------------------------------------------------------------- -- 例 2 -- 2.查询比财务部所有人工资都高的人员信息 -- ---a.查询所有 财务部 人员工资 select id from dept where name='财务部'; select salary from emp where dept_id = (select id from dept where name='财务部'); -- ---b.比 财务部所有人工资都高的员工信息 select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name='财务部')); /***************************************************** *其中 salary > all() 指的是 比all() 中的所有 salary 都高 * ******************************************************/
行子查询
- 子查询返回的结果是一行(可以是多列),这种子查询成为行子查询。
- 常用的操作符:=、<>、in、not in
示例:
-- 查询与”nxl“的薪资及直属领导相同的员工信息 -- ---a.查询 ”nxl“的薪资以及直属领导 select salary,managerid from emp where name='nxl'; -- ---b.查询 查询与”nxl“的薪资及指数领导相同的员工信息 select * from emp where (salary,managerid)=(select salary,managerid from emp where name='nxl');
表子查询
-
子查询返回的结果是多行多列,这种子查询成为表子查询。
-
常用的符号:in
示例:
-- 表子查询 -- 1.查询与”nxl“和”ztt“的职位和薪资相同的员工信息 -- ---a.查询”nxl“和”ztt“的职位和薪资 select job,salary from emp where name='nxl' || name='ztt'; -- ---b.查询与”nxl“和”ztt“的职位和薪资相同的员工信息 select * from emp where (job,salary) in (select job,salary from emp where name='nxl' || name='ztt');
-