多表查询
1.多表关系
一对多(多对一):
案例 部门和员工的关系
关系 一个部门可有多个员工 一个员工对应一个部门
实现 在多的一方建立外键 指向一的一方的主键
多对多:
案例 学生与课程的关系
关系 一个学生可以选秀多门课程 一门课程也可以提供给多个学生选择
实现 建立第三张中间表 中间表至少包涵两个外键 分别关联两方主键
一对一:
案例 用户与用户详情的关系
关系 一对一关系 多用于单表拆分 将一张表的基础字段放在一张表中 其他详情 字段放在另一张表以一声操作效率
实现 在任意一方加入外键 关联另外一方的主键 并且 设置外键为唯一的(unique)
多表查询的概述
概述 从多张表中查询数据
多表查询语法 ----笛卡尔积
消除无效的笛卡尔积
select * from emp ,dept where emp.dept_id=dept.id;
多表查询的分类
连接查询
内连接:
隐式内连接 :select 字段列表 from 表1,表2 where 条件;
显示内连接 :select 字段列表 from 表1[inner] join 表2 on 连接条件...;
1.查询每一个员工的姓名 及其关联部门的名称 (隐式内连接)
表 结构 :emp ,dept
连接条件:emp.dept_id=dept.id
select emp.name,dept.name from emp, dept where emp.dept_id=dept.id;
2..查询每一个员工的姓名 及其关联部门的名称
select e.name,d.name from emp.e join dept d on e.dept_id=d.id;
外连接:
左外连接:查询左表所有数据 以及两个表交集部分数据
select 字段列表 from left [outer] join 表2 on 条件....;
先当于查询表1(左表)的所有数据 包含 表1和表2交集的部分数据
右外连接:查询右表所有数据 以及两个表交集部分数据
select 字段列表 from right [outer] join 表2 on 条件....;
先当于查询表2(右表)的所有数据 包含 表1和表2交集的部分数据
例子:
查询emp表的所有数据 和对应的部门的信息(左外连接)
select e.*,d.name from emp e left outer join dept p on e.dept_id=p.id;
查询dept表的所有数据 和对应的员工的信息(右外连接)
select d.*, e.* from emp e right outer join dept p on e.dept_id=p.id;
自连接:当前表与自身的连接查询,自连接必须使用表别名
查询语法:
select 字段列表 from 表A 别名A join 表A 别名B on 条件...;
自连接就是可内可外
例子:
1. 查询员工和直属领导的名字
表结构 emp
select a.name ,b.name from emp a,emp.b where a.managerid=b.id;
联合查询 -union,union all
对于union 查询 就是把多次查询的结果合并起来 形成一个新的查询结果集
select 字段列表 from 表A ....
union [all]
select 字段列表 from 表B....;
查询薪资低于5000 和年龄大于50的查询出
select * from emp where salary<5000
union
select * from emp where age>50;
子查询 :
概念 SQL语句中嵌套select 语句 称为 嵌套查询 又称子查询
select * from t1 where column1=( select coumn1 from t2);
子查询外部的语句可以是 insert /update/delete/select 的任意一个。
根据子查询的结果不同 分为:
标量子查询 (子查询结果为单个值)
列子查询 (子查询结果为一列)
行子查询 (子查询结果为一行)
表子查询 (子查询结果为多行多列)
根据子查询的位置 分为 where 之后,from之前 select之后。
标量子查询 (子查询结果为单个值 单行单列),最简单的形式 这种子查询的值可以是日期 时间等等
例:
根据部门id 查询员工信息
select id from dept where name='销售部';
select * from emp where dept_id=4;
拆分成两条语句
select * from emp where dept_id=(select id from dept where name='销售部')
列子查询 (子查询结果为一列)
常用操作符 :in, not in ,any ,some.all
例:
查询 销售部和市场部的所有员工信息
a.select id from dept where name ='销售部' or name='市场部';
根据部门id 查询员工信息
b.select * from emp where dept_id in (2,4);
合并:select * from emp where dept_id in (select id from dept where name ='销售部' or name='市场部');
行子查询 (子查询结果为一行)
常用的操作符 := <> in not in
例:
查询emp表中与 "张无忌" 的薪资及直属领导相同的员工信息;
拆分
1. 查询与张无忌 的薪资及直属领导
select salary, managerid from emp where name='张无忌';
2.查询与 张无忌 的薪资及直属领导相同的员工信息
select * from emp where (salary ,managerid)=(12500,1);
3.合并
select * from emp where (salary ,managerid)=(select salary, managerid from emp where name='张无忌');
表子查询 (子查询结果为多行多列)