目录

多表关系

多表查询之笛卡尔积

多表查询分类

连接查询

内连接

外连接

自连接

子查询

标量子查询

列子查询

行子查询

表子查询

联合查询

---

多表关系

• 一对多（多对一）：员工与部门，一个部门可以对应多个员工
  一般在多的一方创建外键，指向一的那一方，即员工表建立外键指向部门表
• 多对多：学生与课程，一个学生可以选多个课，一个课包含多个学生
  一般会建立第三张表，表中至少包含两个外键，分别指向两张表的主键
• 一对一：人与身份信息，一个人有一个身份证号
  可以在任意一方加入外键，关联另一方的主键，并且设置外键为唯一(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;