Mysql高级（下）

二、多表查询

多个表之间是有关系的，那么关系靠谁来维护?
多表约束：外键列

1、多表的关系

1)、一对多/多对一关系

例如：客户和订单，分类和商品，部门和员工
一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键

2)、多对多关系

例如：学生和课程
多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键

3)、一对一关系

在实际的开发中应用不多.因为一对一可以创建成一张表.
两种建表原则：
唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique.
主键对应：让一对一的双方的主键进行建立关系.

2、多表查询

多表查询有如下几种：

1. 合并结果集:UNION 、 UNION ALL
2. 连接查询

• 2.1内连接 [INNER] JOIN ON
• 2.2外连接 OUTER JOIN ON
  左外连接 LEFT [OUTER] JOIN
  右外连接 RIGHT [OUTER] JOIN
  全外连接（MySQL不支持）FULL JOIN
• 2.3 自然连接 NATURAL JOIN
  3.子查询

2-1合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起！
合并结果集有两种方式：
l UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；
l UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。
注意：被合并的两个结果：列数、列类型必须相同。

2-2连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。
连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),
(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过
条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。
示例 1：现有两张表
emp-员工表，dept-部门表

CREATE TABLE dept1(
 deptno    int primary key,
 dname    varchar(14),
 loc    varchar(13)
);
insert into dept1 values(10,'服务部','北京');
insert into dept1 values(20,'研发部','北京');
insert into dept1 values(30,'销售部','北京');
insert into dept1 values(40,'主管部','北京');
CREATE TABLE emp1(
 empno    int,
 ename    varchar(50),
 job    varchar(50),
 mgr    int,
 hiredate  date,
 sal    double,
 comm    double,
 deptno    int
);
insert into emp1 values(1001,'张三','文员',1006,'2019-1-1',1000,2010,10);
insert into emp1 values(1002,'李四','程序员',1006,'2019-2-1',1100,2000,20);
insert into emp1 values(1003,'王五','程序员',1006,'2019-3-1',1020,2011,20);
insert into emp1 values(1004,'赵六','销售',1006,'2019-4-1',1010,2002,30);
insert into emp1 values(1005,'张猛','销售',1006,'2019-5-1',1001,2003,30);
insert into emp1 values(1006,'谢娜','主管',1006,'2019-6-1',1011,2004,40);

指定要查询的列：

SELECT emp.ename,emp.sal,emp.comm,dept.dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;

结果：

2-3 内连接

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！
语法：

select 列名
   from 表1
   inner join 表2
   on 表1.列名=表2.列名 //外键列的关系
   where.....

等价于：

select 列名    
 from 表1,表2    
 where 表1.列名=表2.列名 and ...(其他条件)

注:<1>表1和表2的顺序可以互换
<2>找两张表的等值关系时,找表示相同含义的列作为等值关系。
<3>点操作符表示“的”,格式:表名.列名
<4>可以使用as,给表名起别名,注意定义别名之后,统一使用别名
示例：

//查询学生表中的学生姓名和分数表中的分数
     select name,score
     from student as s
     inner join scores as c
     on s.studentid=c.stuid
    等价于：
    select name,score
    from student as s,scores as c
    where s.studentid=c.stuid

三表联查：
语法：

select 列名 from 表1
inner join 表2 on 表1.列名=表2.列名
inner join 表3 on 表1或表2.列名=表3.列名 where

等价于：

select 列名 from 表1,表2,表3
where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名

SQL标准的内连接为：

SELECT *
FROM emp e
INNER JOIN dept d
ON e.deptno=d.deptno;

内连接的特点：查询结果必须满足条件。

2-4 外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。
– 显示还没有员工的部门名称?
– 外联查询
– 左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
– 1.主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null
– 2.主表和次表不能随意调换位置
使用场景:一般会作为子查询的语句使用

select depname,name from
(select e.*,d.depname from department d left join employee e
on e.depid=d.depid
) aa where aa.name is null;

– 右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名
a.左外连接

SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的
显示NULL。

insert into emp values(1007,'何炅','主管',1006,'2019-6-1',1011,2004,50);

我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部
门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为
emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示
NULL。

b.右外连接
右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在
dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相
应的员工信息为NULL。

insert into dept values(60,'颜值部','成都');
SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;

3 自然连接

自然连接（NATURAL INNER JOIN）：自然连接是一种特殊的等值连接，他要求两个关系表中进行连
接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。.下面给
出几个例子。
语句：

select * from emp e natural join dept d;

4 子查询

一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么
就是子查询语句了。
l 子查询出现的位置：
a. where后，作为条为被查询的一条件的一部分；
b. from后，作表；
l 当子查询出现在where后作为条件时，还可以使用如下关键字：
a. any
b. all
l 子查询结果集的形式：
a. 单行单列（用于条件）
b. 单行多列（用于条件）
c. 多行单列（用于条件）
d. 多行多列（用于表）
子查询作为条件
l 子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）