一.多表关系
概念:在项目的开发中进行的数据库结构设计时,根据业务的需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以以各表结构之间存在着各种联系。
表联系分类:
- 一对多:主要有部门与员工之间的关系,主要通过多的一方建立外键,直向一的一方的主键。
- 多对多:主要有学生与课程关系(一个学生可以选修多门课程,一门课程也可以供多个学生选择),主要通过建立第三张中间表,中间表至少包括两个外键,分别关联两方主键。
- 一对一:用户与用户详情的关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中。主要通过在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(unique)。
二.多表查询
概念:指从多张表中查询数据
笛卡尔积:笛卡尔乘积是指在数学中,两个集合A集合和B集合的所有组合情况(多表查询需要消除无效的笛卡尔积)
多表查询分类:
连接查询:1.内连接:相当于查询A,B交集部分数据。
2.外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据。
右外连接:查询右表所有数据,以及两张表交集部分数据。
3.自连接:当前表与自身的连接查询,自连接必须使用表别名。
子查询:SQL语句中嵌套的select语句,称嵌套查询又子查询。
内连接查询语法:
- 隐式内连接:select 字段列表 from 表1,表2 where 条件
--查询每一个员工及所属的部门
--表结构:emp2,dept
--连接条件:emp2.dept_id = dept.id
select emp2.name ,dept.name from emp2 , dept where emp2.dept_id = dept.id;
- 显式内连接:select 字段列表 from 表1 [inner] join 表2 on 连接条件....;
--inner join...on....
select e.name, d.name from emp2 e inner join dept d on e.dept_id = d.id
结果:
外连接查询语法:
- 左外连接:select 字段列表 from 表1 left [outer] join 表2 on 条件......(相当于查询表1(左表)的所有数据和表1和表2 交集的数据)
用于将员工表和部门表连接在一起
--查询emp2表所有数据,和对应部门直接的信息
--表结构:emp2,dept
--连接条件:emp2.dept_id = dept.id
select e.*, d.name from emp2 e left join dept d on e.dept_id = d.id;
- 右外连接:select 字段列表 from 表1 right [outer] join 表2 on 条件.....(相当于查询表1(右表)的所有数据和表1和表2 交集的数据)
--查询emp2表所有数据,和对应部门直接的信息(右外连接)
--表结构:emp2,dept
--连接条件:emp2.dept_id = dept.id
select d.*, e.* from emp2 e right join dept d on e.dept_id = d.id;
自连接的语法结构:
- 自连接:select 字段列表 from 表A 别名A join 表A 别名B on 条件......
--自连接
--查询员工及所属领导的名字(将一张员工表看作两张表相互连接)
--表结构:emp2
select a.name, b.name from emp2 a, emp2 b where a.managerid = b.id;
联合查询union(去重)-union all(会将查询到的所有数据全部合并)
- 联合查询:就是把多次查询的结果合并起来,形成一个新的查询结果集。(查询的多张表的列数必须保持一致,字段类型也需要保持一致)
select 字段列表 from 表a union [all] select 字段列表 from 表b
子查询:嵌套查询
- 语法:select * from t1 where column = (select column 1 from t2);-----外部语句可以是insert/update/delete/select的任意一个
- 查询结果不同:标量子查询(单个数据),列子查询(查询一列),行子查询(查询一行),表子查询(查询多行多列)。
- 根据子查询位置,分为:where之后,from之后,select之后。
标量子查询:返回的结果是单个值(数字,字符串,日期等)。
--1.查询‘销售部’的所有员工
--首先查询‘销售部’部门id
select id from dept where name = '销售部';
--根据部门id查询员工信息
select * from emp2 where dept_id = 4;/
select * from emp2 where dept_id =(select id from dept where name = '销售部');/一样的/
--2.查询员工入职信息
--查询相应员工入职日期
select entrydate from emp2 where name = '猪八戒';
--查询指定入职时间后入值的员工
select * from emp2 where entrydate > (select entrydate from emp2 where name = '猪八戒');
列子查询:返回的数据是一列,可以是多行。
--列子查询
--1.查询‘销售部’和‘技术部’的所有员工信息
--首先查询部门id
select id from dept where name = '销售部' or name = '技术部';
--根据id查询员工信息
select * from emp2 where dept_id in (select id from dept where name = '销售部' or name = '技术部');
--2.查询研发所有人工资都高的员工信息
--首先查询研发部id
select id from dept where name = '研发部';
--查询财务部工资
select salary from emp2 where dept_id = (select id from dept where name = '研务部');
--比研发部工资高的员工
select * from emp2 where salary > all(select salary from emp2 where dept_id = (select id from dept where name = '研务部'));
行子查询:返回数据为一行
--3.查询唐僧薪资和直属领导
--首先查询唐僧相关信息
select salary, managerid from emp2 where name = '唐僧';
--查询和唐僧相同的员工信息
select * from emp2 where (salary,managerid) = (select salary, managerid from emp2 where name = '唐僧');
表子查询:返回结果多行多列