MYSQL--多表查询 事务

一、多表查询
1 多表关系
1.1 一对多（多对一）
案例：部门与员工 关系：一个部门对应多个员工，一个员工对应一个部门 实现：在多的一方建立外键，指向一的一方的主键
1.2 多对多
案例：学生与课程 关系：一个学生可以选多门课程，一门课程也可以供多个学生选修 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
1.3 一对一
案例：用户与用户详情 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率 实现：任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

2 查询
笛卡尔积：表1和表2的笛卡尔积等于表1的每一行和表2的所有行合并和的新的表，假设表1有m行，表2有n行，则笛卡尔积有mn行
select * from 表1, 表2;

多个表的笛卡尔积就是所有的可能结合构成的表，笛卡尔积的行数是各个表行数的乘积
一般笛卡尔积是没有意义的，会加上WHERE条件筛选

3 内连接查询
内连接查询的是两张表交集的部分
3.1 隐式内连接：
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

3.2 显式内连接：
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;

如果已经为表起了别名，执行顺序之后的语句只能通过表的别名访问表，不能通过表名访问表
隐式内连接和显式内连接仅仅是代码上的区别，功能上完全一致

4 外连接查询
4.1 左外连接
查询左表所有数据，以及两张表交集部分数据
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;
相当于查询表1的所有数据，包含表1和表2交集部分数据

4.2 右外连接
查询右表所有数据，以及两张表交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;
左外连接可以查询到左边表的null字段，右外连接可以查询到右边表的null字段

5 自连接查询
当前表与自身的连接查询，自连接必须使用表别名
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

注：自连接查询，可以是内连接查询，也可以是外连接查询

6 联合查询-union, union all
把多次查询的结果合并，形成一个新的查询集
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...

注: UNION ALL 会有重复结果，UNION 不会
联合查询多张表的查询列要一致，否则会出错
联合查询比使用or效率高，不会使索引失效

8.7 子查询

SQL语句中嵌套SELECT语句，称谓嵌套查询，又称子查询

SELECT * FROM t1 WHERE 列名 = ( SELECT 列名 FROM t2);

• 子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

8.7.1 子查询分类

1.根据子查询结果可以分为：

• 标量子查询（子查询结果为单个值）

• 列子查询（子查询结果为一列）

• 行子查询（子查询结果为一行）

• 表子查询（子查询结果为多行多列）

2.根据子查询位置可分为：

• WHERE 之后

• FROM 之后

• SELECT 之后

8.7.2 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等）

常用操作符：= <> > >= < <=

8.7.3 列子查询

返回的结果是一列（可以是多行）

常用操作符：

操作符	描述
IN	在指定的集合范围内，多选一
NOT IN	不在指定的集合范围内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

8.7.4 行子查询

返回的结果是一行（可以是多列）

常用操作符：= < > IN NOT IN

8.7.5 表子查询

返回的结果是多行多列

常用操作符：IN

示例：

-- 查询与xxx1，xxx2的职位和薪资相同的员工
select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2');
-- 查询入职日期是2006-01-01之后的员工，及其部门信息
select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e left join dept as d on e.dept = d.id;