多表连接查询
Join 和 Key
有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。就需要执行 join。
数据库中的表可通过键将彼此联系起来。主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。
请看 “Persons” 表：
| Id_P | LastName | FirstName | Address | City |
| — | — | — | — | — |
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
| 3 | Carter | Thomas | Changan Street | Beijing |
请注意，“Id_P” 列是 Persons 表中的的主键。这意味着没有两行能够拥有相同的 Id_P。即使两个人的姓名完全相同，Id_P 也可以区分他们。
接下来请看 “Orders” 表：
| Id_O | OrderNo | Id_P |
| — | — | — |
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 65 |
请注意，“Id_O” 列是 Orders 表中的的主键，同时，“Orders” 表中的 “Id_P” 列用于引用 “Persons” 表中的人，而无需使用他们的确切姓名。
请留意，“Id_P” 列把上面的两个表联系了起来。
引用两个表
我们可以通过引用两个表的方式，从两个表中获取数据：
谁订购了产品，并且他们订购了什么产品？
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P
结果集：
| LastName | FirstName | OrderNo |
| — | — | — |
| Adams | John | 22456 |
| Adams | John | 24562 |
| Carter | Thomas | 77895 |
| Carter | Thomas | 44678 |
SQL JOIN - 使用 Join
除了上面的方法，我们也可以使用关键词 JOIN 来从两个表中获取数据。
如果我们希望列出所有人的定购，可以使用下面的 SELECT 语句：
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
结果集：
| LastName | FirstName | OrderNo |
| — | — | — |
| Adams | John | 22456 |
| Adams | John | 24562 |
| Carter | Thomas | 77895 |
| Carter | Thomas | 44678 |
不同的 SQL JOIN
除了我们在上面的例子中使用的 INNER JOIN（内连接），我们还可以使用其他几种连接。
下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。
JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行
2、交叉连接（也称迪卡尔积）
笛卡尔连接或交叉连接从两个或多个连接表返回笛卡尔乘积的记录。因此，它相当于一个内部联接，其中联接条件始终计算为真或者联接条件是从语句中空缺。
语法
笛卡尔连接的基本语法如下：
select * from a cross join b;
select * from a join b;
例子： 
3、内连接
SQL INNER JOIN 关键字
在表中存在至少一个匹配时，INNER JOIN 关键字返回行。
INNER JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
**注释：**INNER JOIN 与 JOIN 是相同的。
原始的表 (用在例子中的)：
“Persons” 表：
| Id_P | LastName | FirstName | Address | City |
| — | — | — | — | — |
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
| 3 | Carter | Thomas | Changan Street | Beijing |
“Orders” 表：
| Id_O | OrderNo | Id_P |
| — | — | — |
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 65 |
内连接（INNER JOIN）实例
现在，我们希望列出所有人的定购。
您可以使用下面的 SELECT 语句：
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果集：
| LastName | FirstName | OrderNo |
| — | — | — |
| Adams | John | 22456 |
| Adams | John | 24562 |
| Carter | Thomas | 77895 |
| Carter | Thomas | 44678 |
INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 “Persons” 中的行在 “Orders” 中没有匹配，就不会列出这些行。
4、外连接
一、左连接
SQL LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
LEFT JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
**注释：**在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。
原始的表 (用在例子中的)：
“Persons” 表：
| Id_P | LastName | FirstName | Address | City |
| — | — | — | — | — |
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
| 3 | Carter | Thomas | Changan Street | Beijing |
“Orders” 表：
| Id_O | OrderNo | Id_P |
| — | — | — |
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 65 |
左连接（LEFT JOIN）实例
现在，我们希望列出所有的人，以及他们的定购 - 如果有的话。
您可以使用下面的 SELECT 语句：
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果集：
| LastName | FirstName | OrderNo |
| — | — | — |
| Adams | John | 22456 |
| Adams | John | 24562 |
| Carter | Thomas | 77895 |
| Carter | Thomas | 44678 |
| Bush | George | |
LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。
二、右连接
SQL RIGHT JOIN 关键字
RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
RIGHT JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
**注释：**在某些数据库中， RIGHT JOIN 称为 RIGHT OUTER JOIN。
原始的表 (用在例子中的)：
“Persons” 表：
| Id_P | LastName | FirstName | Address | City |
| — | — | — | — | — |
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
| 3 | Carter | Thomas | Changan Street | Beijing |
“Orders” 表：
| Id_O | OrderNo | Id_P |
| — | — | — |
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 65 |
右连接（RIGHT JOIN）实例
现在，我们希望列出所有的定单，以及定购它们的人 - 如果有的话。
您可以使用下面的 SELECT 语句：
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果集：
| LastName | FirstName | OrderNo |
| — | — | — |
| Adams | John | 22456 |
| Adams | John | 24562 |
| Carter | Thomas | 77895 |
| Carter | Thomas | 44678 |
| | | 34764 |
RIGHT JOIN 关键字会从右表 (Orders) 那里返回所有的行，即使在左表 (Persons) 中没有匹配的行。
三、全连接
SQL FULL JOIN 关键字
只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。
FULL JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
**注释：**在某些数据库中， FULL JOIN 称为 FULL OUTER JOIN。
原始的表 (用在例子中的)：
“Persons” 表：
| Id_P | LastName | FirstName | Address | City |
| — | — | — | — | — |
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
| 3 | Carter | Thomas | Changan Street | Beijing |
“Orders” 表：
| Id_O | OrderNo | Id_P |
| — | — | — |
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 65 |
全连接（FULL JOIN）实例
现在，我们希望列出所有的人，以及他们的定单，以及所有的定单，以及定购它们的人。
您可以使用下面的 SELECT 语句：
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果集：
| LastName | FirstName | OrderNo |
| — | — | — |
| Adams | John | 22456 |
| Adams | John | 24562 |
| Carter | Thomas | 77895 |
| Carter | Thomas | 44678 |
| Bush | George | |
| | | 34764 |
FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 “Persons” 中的行在表 “Orders” 中没有匹配，或者如果 “Orders” 中的行在表 “Persons” 中没有匹配，这些行同样会列出。
5、自连接
自连接是连接的一种用法,但并不是连接的一种类型,因为他的本质是把一张表当成两张表来使用。
mysql有时在信息查询时需要进行对自身连接（自连接），所以我们需要为表定义别名。
我们举例说明，下面是商品采购表，我们需要找到采购价格比小张高的所有信息。 
一般情况我们看到这张表我们第一时间用语句进行操作：
SELECT * FROM shoping WHERE price>2
分步查询
SELECT price FROM shopping WHERE name=‘小张’ //得出price查询结果为2
SELECT * FROM shopping WHERE price>2
我们可以获取到如下表信息：
与采用自连接的方式相比，这种方法需要对中间结果进行人工干预，显然不利于程序中的自动处理操作。
自连接方式：
SELECT b.*
from shopping as a,shopping as b
where a.name=‘小张’
and a.price<b.price
获取到如下表信息：
注意点：
别名 a,b虽然名称不同，但是同一个表，定义别名的目的是更方便在自身进行删选。
6、等值连接与非等值连接
等值连接条件
select * from a join b on a.id=b.id; 2条记录
非等值连接条件
select * from a join b on a.id>b.id; 4条记录
select * from a join b on a.id<b.id; 3条记录
以上 a.id=b.id;a.id>b.id;a.id<b.id三种查询记录数加起来是9条
等于2个表交叉连接的数量
还有其他的写法，根据业务需求定，有实际意义最重要
select * from a join b on a.id between 1 and 3 and b.id<4;;
…
7、多表连接查询总结
INNER JOIN（内连接）
SELECT
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key
LEFT JOIN（左连接）
SELECT
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
RIGHT JOIN（右连接）
SELECT
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
OUTER JOIN（外连接）
SELECT
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
LEFT JOIN EXCLUDING INNER JOIN（左连接-内连接）
SELECT
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL
RIGHT JOIN EXCLUDING INNER JOIN（右连接-内连接）
SELECT
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL
OUTER JOIN EXCLUDING INNER JOIN（外连接-内连接）
SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL
二、多表连接示例1
显示学生的学号，姓名，性别，班号，班级名称以及他们的班主任姓名，按照班号和学号排序
mysql> select s.sno 学号,s.sname 姓名,s.sex 性别,s.cno 班号,c.cname 班名,c.teacher 班主任 from stu s inner join class c on s.cno=c.cno order by s.cno,s.sno;
显示全部学生的学号，姓名，性别，班号，班级名称以及他们的班主任姓名，无班主任的显示‘暂无’，按照班号和学号排序
mysql> select s.sno 学号,s.sname 姓名,s.sex 性别,s.cno 班号,c.cname 班名,ifnull(c.teacher,‘暂无’) 班主任 from stu s left join class c on s.cno=c.cno order by s.cno,s.sno;
三、多表连接查询示例2
显示所有学生的学号，姓名，性别，身高，体重，班号，BMI指数（体重/身高^2）,BMI表中对应的体态，低值，高值和性别
mysql> select s.sno 学号,s.sname 姓名,s.sex 性别,s.height 身高,s.weight 体重,s.cno 班号,round(weight/(height/100height/100),2) BMI指数,b.lval 低值,b.hval 高值,bname 体态,b.sex 性别 from stu s left join bmi b on s.sex=b.sex and round(weight/(height/100height/100),2) between b.lval and b.hval;
基于例3的结果，把肥胖的同学和他们的班主任找出来
mysql> select s.sno 学号,s.sname 姓名,s.sex 性别,s.height 身高,s.weight 体重,s.cno 班号,round(weight/(height/100height/100),2) BMI指数,b.lval 低值,b.hval 高值,bname 体态,b.sex 性别,c.cname 班名,c.teacher 班主任 from stu s left join bmi b on s.sex=b.sex and round(weight/(height/100height/100),2) between b.lval and b.hval left join class c on s.cno=c.cno where b.bname=‘肥胖’;
8、复合查询
一、并级运算
SQL UNION 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
**注释：**默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
下面的例子中使用的原始表：
Employees_China:
| E_ID | E_Name |
| — | — |
| 01 | Zhang, Hua |
| 02 | Wang, Wei |
| 03 | Carter, Thomas |
| 04 | Yang, Ming |
Employees_USA:
| E_ID | E_Name |
| — | — |
| 01 | Adams, John |
| 02 | Bush, George |
| 03 | Carter, Thomas |
| 04 | Gates, Bill |
使用 UNION 命令
实例
列出所有在中国和美国的不同的雇员名：
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
结果
| E_Name |
| — |
| Zhang, Hua |
| Wang, Wei |
| Carter, Thomas |
| Yang, Ming |
| Adams, John |
| Bush, George |
| Gates, Bill |
**注释：**这个命令无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。
UNION ALL
UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。
SQL Statement 1
UNION ALL
SQL Statement 2
使用 UNION ALL 命令
实例：
列出在中国和美国的所有的雇员：
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA
结果
| E_Name |
| — |
| Zhang, Hua |
| Wang, Wei |
| Carter, Thomas |
| Yang, Ming |
| Adams, John |
| Bush, George |
| Carter, Thomas |
| Gates, Bill |
二、复合查询示例
创建示例表
mysql> create table class1 as select * from class where cno=1;
mysql> insert into class1 values(5,‘5班’,‘董卓’);
mysql> select * from class;
mysql> select * from class1;
求表class和class1的并集，重复记录只显示一次
mysql> select * from class union select * from class1;
默认排序为增序，顺序列1、列2、列3…
也可以自定义排序，order by写在语句的最后：
select * from class union select * from class1 order by 2 desc,3,1;
select * from class order by cname union select * from class1 order by 2 desc,3,1;
求表class和class1的并集，重复记录重复显示
mysql> select * from class union all select * from class1;
结果默认不排序
求表class和class1的交集
mysql无intersect集合运算符，交集运算可以通过多表连接实现
mysql> select c.cno,c.cname,c.teacher from class c join class1 c1 on c.cno=c1.cno;
求表class和class1的差集，即显示class表中在class1表中没有的行记录
mysql无minus集合运算符，差集运算可以通过多表连接实现
1.mysql> select * from class c left join class1 c1 on c.cno=c1.cno;
2.mysql> select * from class c left join class1 c1 on c.cno=c1.cno where c1.cno is null;
3.mysql> select c.cno,c.cname,c.teacher from class c left join class1 c1 on c.cno=c1.cno where c1.cno is null;
求表class1和class的差集，即显示class1表中在class表中没有的行记录
mysql> select c1.cno,c1.cname,c1.teacher from class c right join class1 c1 on c.cno=c1.cno where c.cno is null;
或者
mysql> select c1.cno,c1.cname,c1.teacher from class1 c1 left join class c on c1.cno=c.cno where c.cno is null;
左右连接是相对的，记一种就够了
9、子查询
一、概述
SELECT 语句结构
SELECT 列1，列2 … fromTABLE_NAME WHERE 列 = 值GROUP BY 分组列 HAVING 分组列 = 值 ORDER BY 列
上面标红的位置还可以嵌套额外的SELECT语句（子查询），与外部SELECT语句（主查询）结合起来使用，用一个查询语句实现更为复杂的任务。那些嵌套的SELECT语句往往被称为子查询；子查询需要用括号( )括起来。
分类
非关联子查询：子查询可以单独于主查询执行，仅执行1次，效率较高
关联子查询：子查询不能单独于主查询执行，如果主查询有N行，子查询将执行N次，效率相对较低，但灵活度高
二、非关联子查询
查询学生中哪些人比张飞的体重重？
mysql> select * from stu where weight>(select weight from stu where sname=‘张飞’);
2班3班中哪些同学的身高比1班的平均身高高？
mysql> select * from stu where cno in (2,3) and height>(select avg(height) from stu where cno=1);
每个班的高考状元都是谁？
mysql> select * from stu where (cno,score) in (select cno,max(score) from stu group by cno) and cno is not null;
哪些同学的体重比所有班的平均体重都重？
mysql> select * from stu where weight>all(select avg(weight) from stu where cno is not null group by cno) and cno is not null;
>ALL运算符：比所有的值都大;
>ANY运算符：比最小的那个大就行；
哪些同学的身高高于本班的平均身高？
mysql> select * from stu s,(select cno,avg(height) havg from stu where cno is not null group by cno) a where s.cno=a.cno and height>havg and s.cno is not null;
FROM后面的子查询也叫内联视图，MySQL中这个内联视图子查询必须起个表别名，否则报错
不用多表连接方式，列出3班学生姓名和3班的班主任
mysql> select sname 学生姓名,(select teacher from class where cno=3) 班主任 from stu o where cno=3;
三、关联子查询
不用多表连接方式，列出每个学生的班号，姓名和其所在班的班主任
mysql> select o.cno 班号,o.sname 姓名,(select teacher from class i where i.cno=o.cno) 班主任 from stu o where o.cno is not null order by 1; 
特点：子查询不能单独执行，主查询必须起个别名
单独执行：select teacher from class i where i.cno=o.cno; 报错，不知道o.cno的o表是什么，o是外部主查询的别名，脱离了外部查询，子查询执行不了 
不用多表连接方式，根据学生赵云的性别、身高和体重，查看他的bmi指标是否合适？
mysql> select sname 姓名,sex 性别,height 身高,weight 体重,(select bname from bmi i where o.weight/(o.height/100*o.height/100) between i.lval and i.hval and i.sex=o.sex) 体态 from stu o where o.sname=‘赵云’; 
使用关联子查询，在已分班学生中列出身高高于本班平均身高的学生。
mysql> select * from stu o where o.cno is not null and o.height>(select avg(height) from stu i where i.cno=o.cno); 
