多表查询也叫关联查询,把多张相关联的表通过关联关系联系起来,并通过关联关系(外键)去查询需要的数据。
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
一对多(多对一)
案例: 部门与员工的关系
关系: 一个部门对应多个员工,一个员工对应一个部门。
实现: 在多的一方建立外键,指向一的一方的主键。
多对多
案例: 学生与课程的关系。
关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择。
实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键。
create table student_course(id int auto_increment comment '主键' primary key,
studentid int not null comment '学生ID',
courseid int not null comment '课程ID',
constraint fk_courseid foreign key (courseid) references course (id),
constraint fk_studentid foreign key (studentid) references student (id)
)comment '学生课程中间表';
一对一
案例: 用户与用户详情的关系
关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另 一张表中,以提升操作效率
实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
笛卡尔积: 笛卡尔乘积是指在数学中,两个集合A集合 和 B集合的所有组合情况。
在SQL语句中,如何来去除无效的笛卡尔积呢? 我们可以给多表查询加上连接查询的条件即可。
select * from dept,emp where dept.id = emp.dept_id;
连接查询
内连接:相当于查询A、B交集部分数据
内连接的语法分为两种: 隐式内连接、显式内连接。
隐式内连接
select d.name,e.name from dept as d ,emp as e where d.id = e.dept_id;
显式内连接
select d.name,e.name from dept as d join emp e on d.id = e.dept_id;
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据
select e.*,d.name from emp as e left join dept d on d.id = e.dept_id;
右外连接:查询右表所有数据,以及两张表交集部分数据
select e.*,d.* from emp as e right join dept d on d.id = e.dept_id;
注意事项: 左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺 序就可以了。而我们在日常开发使用时,更偏向于左外连接。
自连接:当前表与自身的连接查询,自连接必须使用表别名
内连接查询
select e.name'领导',m.name from emp as e join emp as m on e.id = m.managerid;
外连接查询
select e.name,m.name from emp as e left join emp as m on e.id = m.managerid;
子查询
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
子查询外部的语句可以是INSERT增 /DELETE删 /UPDATE改 / SELECT查 的任何一个。
根据子查询结果不同,分为:
A. 标量子查询(子查询结果为单个值)(单人员单条件)
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。
常用的操作符:= <> > >= < <=
select id from dept where name = '研发部';
select * from emp where dept_id = (select id from dept where name = '研发部');
B. 列子查询(子查询结果为一列)(多人员单条件)
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL
select id from dept where name = '销售部' or name = '研发部';
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '研发部');
C. 行子查询(子查询结果为一行)(单人员多条件)
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用的操作符:= 、<> 、IN 、NOT IN
select salary, managerid from emp where name = '张无忌';
select * from emp where (salary,managerid) = (select salary, managerid from emp
where name = '张无忌');
D. 表子询(子查询结果为多行多列)(多条件多人员)
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符:IN
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
select * from emp where (job,salary) in (select job, salary from emp where name = '鹿杖客' or name = '宋远桥');
多表查询实现的方式很多,只要是方便快捷,都可以尝试。
页面更新:2024-03-05
本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828
© CopyRight 2008-2024 All Rights Reserved. Powered By bs178.com 闽ICP备11008920号-3
闽公网安备35020302034844号