1、子查询
- 多个select语句，可以查询一个表，也可以查询多个表
子查询就是指在一个select语句中嵌套另一个select语句。同时，子查询必须包含括号。
MySQL 5.6.x版本之前，MySQL的子查询性能较差，但是从5.6开始，不存在性能差的问题。
select a from t1 where a > any(select a from t2);-
select a from t1是外部查询(outer query) -
(select a from t2)是子查询
一般说来，子查询嵌套于外部查询中，可以将两个或两个以上的子查询进行嵌套
1.1、 子查询的使用
(1) ANY / SOME
如果外部查询的列的结果和子查询的列的结果比较得到为True的话，则返回比较值为True的（外查询）的记录
mysql> create table t1(a int);
mysql> create table t2(a int);
mysql> insert into t1 values(10),(4);
mysql> insert into t2 values(12),(13),(5);
mysql> select a from t1 where a > any(select a from t2);
+------+
| a |
+------+
| 10 | -- 10 比 5 大为True，则返回该值，4比t2中所有的a值小，为False
+------+
mysql> select a from t1 where a > some(select a from t2);
+------+
| a |
+------+
| 10 |
+------+推测，如果在t1中插入15，则15也会返回
ANY关键词必须与一个比较操作符一起使用：=,>,<,>=,<=,<>(这个是!=的意思)子查询中
SOME和ANY是同一个意思
(2)IN
in是ANY的一种特殊情况："in" equals "= any"
mysql> insert into t1 values(5); -- 向t1中插入一个t2中存在的值 5
mysql> select a from t1 where a = any(select a from t2); -- t1.a==t2.a 的只有5
+------+
| a |
+------+
| 5 |
+------+
mysql> select a from t1 where a in (select a from t2); -- in的结果等同于 =any 的结果
+------+
| a |
+------+
| 5 |
+------+
select a from s1 where a in (select a in t2);是用的比较多的一种语法
(3)ALL
mysql> truncate t1; -- 清空t1
mysql> truncate t2; -- 清空t2
mysql> insert into t1 values(10),(4);
mysql> insert into t2 values(3),(4),(5);
mysql> select a from t1 where a > all(select a from t2);
+------+
| a |
+------+
| 10 | -- (10 > 5, 4, 3 为 True) 而 (4 >5, 4, 3 为 False)
+------+
ALL关键词必须与一个比较操作符一起使用NOT IN是<> ALL的别名
1.2、子查询的分类
- 独立子查询
- 不依赖外部查询而运行的子查询
mysql> select a from t1 where a in (1,2,3,4,5);- 相关子查询
- 引用了外部查询列的子查询
-- 在这个例子中，子查询中使用到了外部的列t2.a
mysql> select a from t1 where a in (select * from t2 where t1.a = t2.a);1.3、 子查询的优化
- MySQL5.6之前
在MySQL5.6之前，优化器会把子查询重写成exists的形式
select a from t1 where a in (select a from t2); -- 这个是一条独立的子查询，时间复杂度 O(M+N)
--
-- 经过优化器重写后
--
select a from t1 where exists (select 1 from t2 where t1.a = t2.a); -- 这是相关子查询，复杂度O(M*N + M)所以在MySQL 5.6之前，部分的子查询需要重写成join的形式 （注意表的大小）
mysql> select t1.a from t1 join t2 on t1.a = t2.a;
+------+
| a |
+------+
| 4 |
+------+- MySQL 5.6之后
在MySQL 5.6之后，优化器不会将子查询重写成exists的形式，而是自动优化，性能有了大幅提升
可通过
explain extended来查看子查询优化的结果。待续
EXISTS不管返回值是什么，而是看是否有行返回，所以EXISTS中子查询都是select *、select 1等，因为只关心返回是否有行（结果集）
2、组合查询
-
UNION的作用是将两个查询的结果集进行合并。 - UNION必须由
两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔。 - UNION中的每个查询必须包含相同的列（
类型相同或可以隐式转换）、表达式或聚集函数。
mysql> create table test_union_1(a int, b int);
Query OK, 0 rows affected (0.18 sec)
mysql> create table test_union_2(a int, c int);
Query OK, 0 rows affected (0.15 sec)
mysql> insert into test_union_1 values(1, 2), (3, 4), (5, 6), (10, 20);
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into test_union_2 values(10, 20), (30, 40), (50, 60);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test_union_1;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
| 10 | 20 | -- test_union_1 中的10, 20
+------+------+
4 rows in set (0.00 sec)
mysql> select * from test_union_2;
+------+------+
| a | c |
+------+------+
| 10 | 20 | -- test_union_2 中的10, 20
| 30 | 40 |
| 50 | 60 |
+------+------+
3 rows in set (0.00 sec)
mysql> select a, b as t from test_union_1
-> union
-> select * from test_union_2;
+------+------+
| a | t |
+------+------+
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
| 10 | 20 | -- 只出现了一次 10, 20，union会去重
| 30 | 40 |
| 50 | 60 |
+------+------+
6 rows in set (0.00 sec)
mysql> select a, b as t from test_union_1
-> union all -- 使用 union all 可以不去重
-> select * from test_union_2;
+------+------+
| a | t |
+------+------+
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
| 10 | 20 | -- test_union_1 中的10, 20
| 10 | 20 | -- test_union_2 中的10, 20
| 30 | 40 |
| 50 | 60 |
+------+------+
7 rows in set (0.00 sec)
mysql> select a, b as t from test_union_1 where a > 2
-> union
-> select * from test_union_2 where c > 50; -- 使用where过滤也可以
+------+------+
| a | t |
+------+------+
| 3 | 4 |
| 5 | 6 |
| 10 | 20 |
| 50 | 60 |
+------+------+
4 rows in set (0.00 sec)- 规则：
- UNION从结果查询中默认自动去除了重复行。如果想不去除重复行，请用UNION ALL
- 如果知道数据本身具有唯一性，没有重复，则建议使用
union all，因为union会做去重操作，性能会比union all要低 - UNION ALL可以完成WHERE完成不了的工作：当需要每个条件匹配全部出现包括重复行，用UNION ALL而不是WHERE
3、多表查询
- 用单条select语句从多个表中检索出数据
①自联结[关联子查询]
#已经某产品[
prod_id为DTNTR]出现问题，想知道供应这种产品的供应商供应的其他产品是否也有问题：
SELECTprod_nameFROMproductsWHEREvend_id= (
SELECTvend_idFROMproductsWHEREprod_id= ‘DTNTR’); #方法1：子查询
SELECT b.prod_nameFROMproductsAS a,productsAS b
WHERE a.prod_id= ‘DTNTR’ AND a.vend_id= b.vend_id; #方法2：自联结SELECT o.
order_num, o.order_dateFROMcustomersAS c,ordersAS o,orderitemsAS oi
WHERE c.cust_id= o.cust_idAND oi.order_num= oi.order_numANDprod_id= ‘FB’;
②内联结
联结一个表
#等值联结，也叫做内联结
SELECT
e.emp_no,
CONCAT(last_name, ' ', first_name) AS emp_name,
title
FROM
employees AS e,
titles AS t
WHERE
e.emp_no = t.emp_no
LIMIT 5;SELECT
e.emp_no,
CONCAT(last_name, ' ', first_name) AS emp_name,
title
FROM
employees AS e
INNER JOIN
titles AS t ON e.emp_no = t.emp_no
LIMIT 5;
-- 两种语句在效率上其实是一样的，只是语法上的区别,
-- INNER可以省略应该保证所有联结都有正确的where子句，否则会出现笛卡尔积现象。联结多个表
SELECT
gender, salary
FROM
employees,
salaries,
titles
WHERE
employees.emp_no = salaries.emp_no
AND salaries.emp_no = titles.emp_no
AND title = 'Senior Engineer';注意：不要联结不必要的表，因为联结表越多，性能下降越厉害总结：
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
或者
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
注：inner可以省略
拿表1去匹配表2
③外联结
>#检索下了订单的客户和相关订单
SELECT `cust_name`, `order_num` FROM `customers` AS c INNER JOIN `orders` AS o ON c.`cust_id` = o.`cust_id`;
> ***
>#检索所有客户的订单，包括没有下订单的客户[左外联结&右外联结]
SELECT `cust_name`, `order_num`
FROM `customers` AS c
LEFT OUTER JOIN `orders` AS o
ON c.`cust_id` = o.`cust_id`;
>***
>SELECT `cust_name`, `order_num`
>FROM `orders` AS o
>RIGHT OUTER JOIN `customers` AS c
>ON c.`cust_id` = o.`cust_id`;总结：
左外连接
@应用场景：用于查询一个表中有另一个表中没有的记录
@特点：外连接的查询结果为主表中的所有记录，
如果从表中有和它的匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
或：
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。
注释：LEFT JOIN 关键字从左表返回所有的行，即使右表中没有匹配。没有匹配的用null填充。
右外连接
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
或：
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。
注释：RIGHT JOIN 关键字从右表返回所有的行，即使左表中没有匹配。没有匹配的用null填充。
全外连接
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
注：
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.key = table2.key
WHERE table2.key IS NULL;
SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.key = table2.key
WHERE table1.key IS NULL OR table2.key IS NULL;
④带聚集函数的联结
>#检索所有客户以及每个客户下的订单数
SELECT COUNT(o.`order_num`), c.`cust_name`
FROM `customers` AS c
LEFT OUTER JOIN `orders` AS o
ON c.`cust_id` = o.`cust_id`
WHERE c.`cust_id` IS NOT NULL
GROUP BY c.`cust_id`;总结：
- 语法：
select 查询列表
from 表1 别名 【连接查询】
join 表2 别名
on 连接条件
【where 筛选条件】
【groud by 分组】
【having by 排序列表】 - 分类：
内连接： inner
外连接：
左外：left【outer】
右外：right【outer】
全外：full 【outer】
交叉连接：cross【就是一个笛卡尔乘积】 - 连接的结果可以再逻辑上看作是由select语句指定的列组成的新表
左连接和右连接的左右指定是以两张表中的哪一张为基准，他们都是外连接.
外连接就好像为非基准添加了一行全为空值的万能行，用来与基准表中找不到匹配的行进行匹配。假设两个没有空值的表进行左连接，左边是基准表，左边的所有行都出现在结果中，右表则可能因为无法与基准表匹配而出现是空值的字段。) - 总结分类：
按照年代分类：
sql92标准【仅仅支持内连接,也就是等值,非等值,自连接】
sql99标准【推荐】按照功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接：SELECT girl.`name`, boy.`name` FROM girl, boy;
SELECT `name`, `name` FROM girl, boy;
ERROR 1052 (23000): Column 'name' in field list is ambiguous
原因：列'ID'在字段列表中重复，其实就是两张表有相同的字段，但是使用时表字段的名称前没有加表名，导致指代不明
解决：前面加上前缀就没问题了。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
