目录
多表关系
- 一对多(多对一):员工与部门,一个部门可以对应多个员工
一般在多的一方创建外键,指向一的那一方,即员工表建立外键指向部门表 - 多对多:学生与课程,一个学生可以选多个课,一个课包含多个学生
一般会建立第三张表,表中至少包含两个外键,分别指向两张表的主键 - 一对一:人与身份信息,一个人有一个身份证号
可以在任意一方加入外键,关联另一方的主键,并且设置外键为唯一(unique)
多表查询之笛卡尔积
笛卡尔积:两个表(集合)中的每条数据进行两两组合的结果。
在多表查询时会产生笛卡尔积,要通过添加条件消除笛卡尔积
表一:部门表
drop table if exists dept;
CREATE TABLE dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept(name) value('研发部'),('市场部'),('财务部'),('销售部'),('总经办')
表二:员工表
drop table if exists emp;
CREATE TABLE emp(
id int primary key auto_increment,
name varchar(20),
age int,
job varchar(20),
salary int,
entrydate date,
managerid int,
dept_id int
);
insert into emp values(1,'金庸',66,'总裁',20000,'2000-01-01',null,5);
insert into emp values(2,'张无忌',66,'项目经理',12500,'2005-12-05',1,1);
insert into emp values(3,'杨晓',66,'开发',8400,'2000-11-03',2,1);
insert into emp values(4,'韦一笑',66,'开发',11000,'2002-02-05',2,1);
insert into emp values(5,'陈玉存',66,'开发',10500,'2004-09-07',3,1);
insert into emp values(6,'小昭',66,'程序员鼓励师',6600,'2004-10-12',2,1);
查询产生笛卡尔积的结果:
select * from emp, dept ;
消除办法:添加约束条件
select * from emp, dept where emp.dept_id=dept.id;
多表查询分类
多表查询一般分为:连接查询,子查询,联合查询
连接查询
连接查询一般分为:内连接、外连接、自连接
内连接
内连接:相当于查询A,B交集部分数据
内连接查询语法:
# 隐式内连接
select 字段列表 from 表1,表2 where 条件...;
# 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
# 查询每一个员工的姓名及关联的部门的名称
select emp.name, dept.name from emp, dept where emp.dept_id=dept.id;
select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;
外连接
外连接分为:左外连接、右外连接
# 左外连接:查询左表所有数据,以及两张表交集部分数据
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
# 右外连接:查询右表所有数据,以及两张表交集部分数据
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
# 查询emp表的所有数据,和应于的部门信息(左)
select emp.*, dept.* from emp left outer join dept on emp.dept_id = dept.id;
# 查询dept表的所有数据,和对于的员工信息(右)
select dept.*, emp.* from emp right outer join dept on emp.dept_id = dept.id;
自连接
自连接查询可以是内连接查询也可以是外连接查询
# 语法格式
select 字段列表 from 表a 别名a join 表a 别名b on 条件;
# 查询员工及其所属领导的名字
# 自连接可以看成两张一样的表进行连接查询
select a.name, b.name from emp a join emp b on a.managerid=b.id;
子查询
- 子查询:SQL语句中嵌套select语句,又称嵌套查询;
- 可使用子查询的语句:insert、update、delete、select;
- 子查询使用位置:where后、from后、select后;
- 根据子查询结构不同,可分为:标量子查询、列子查询、行子查询、表子查询
标量子查询
- 概念:子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式
- 常用符号:= !=(<>) > >= < <=
# 根据销售部门的id查询员工信息
# 先分开查询
# 查询销售部门的id
select id from dept where name='销售部'; #id为4
# 查询销售部门中员工的信息
select * from emp where dept_id=4;
# 合并为一个查询
select * from emp where dept_id=(select dept.id from dept where dept.name='销售部' );
列子查询
- 概念:子查询的结果为一列(可以是多行)
- 常用字段:IN、NOT IN、ANY、SOME、ALL
# 列子查询
# 查询销售部和市场部的所有员工信息
# 查询销售部和市场部的id
select id from dept where name='销售部' or name='市场部'; #id为2 4
# 查询两个部门的所有员工
select * from emp where dept_id in (2,4);
# 合并
select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部');
# any all some 解读
# All:只有当其所有数据都满足条件时,条件才成立
# Any:只要有一条数据满足条件,条件就成立
# Some:其中存在一些数据满足条件,作用和Any大致相同 常规的使用中看作一致即可
select * from emp where dept_id=all(select id from dept where name='研发部' )
select * from emp where dept_id=any(select id from dept where name='销售部' or name='市场部' or name='研发部' )
select * from emp where dept_id=some(select id from dept where name='销售部' or name='市场部' or name='研发部' )
行子查询
- 子查询返回的结果是一行(可以是多列)
- 常用操作符:=、<>、IN、NOT IN
# 查询与张无忌的薪资及直属领导相同的员工信息
# 查询张无忌的薪资和直属领导
select salary, managerid from emp where name='张无忌';
# 查询与张无忌的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid)=(select salary, managerid from emp where name='张无忌');
表子查询
- 子查询的结果是多行多列的查询
- 常用操作符:in
# 查询与鹿杖客和宋远桥的职位和薪资相同的员工信息
select * from emp where (job, salary) in ( select job, salary from emp where name in ('鹿杖客', '宋远桥'));
# 查询入职日期是’2006-01-01‘之后的员工信息和部门信息
# 先查询出入职在’2006-01-01‘之后员工的所有信息
# 与部门表左连接
select e.*, dept.* from (select * from emp where entrydate>'2006-01-01') e left outer join dept on e.dept_id=dept.id;
联合查询
- 概念:把多次查询的结果合并起来,形成一个新的查询结果集
- 方法:union、union all
- union all会将全部的数据直接合并在一起,union会对合并之后的数据去重
# 格式:
select 字段列表 from 表a
union [all]
select 字段列表 from 表b
# 将薪资低于5000的员工和年龄大于50的员工查询出来
select * from emp where salary>5000
union all
select * from emp where age>50;
# 没有all重复满足条件的只出现一次
# 将薪资低于5000的员工和年龄大于50的员工查询出来
select * from emp where salary>5000
union
select * from emp where age>50;