SQL：子查询&&组合查询&&联结查询

最新推荐文章于 2024-04-10 16:25:41 发布

OceanStar的学习笔记

最新推荐文章于 2024-04-10 16:25:41 发布

阅读量1.1k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/zhizhengguan/article/details/86157524

版权

数据库专栏收录该内容

289 篇文章 17 订阅

订阅专栏

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]出现问题，想知道供应这种产品的供应商供应的其他产品是否也有问题：
SELECT prod_name FROM products WHERE vend_id = (
SELECT vend_id FROM products WHERE prod_id = ‘DTNTR’); #方法1：子查询
SELECT b.prod_name FROM products AS a, products AS b
WHERE a.prod_id = ‘DTNTR’ AND a.vend_id = b.vend_id; #方法2：自联结

SELECT o.order_num, o.order_date FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id AND oi.order_num = oi.order_num AND prod_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子句，否则会出现笛卡尔积现象。

笛卡尔乘积现象：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接。也就是表1有n行,表2有m行，结果=m*n行

联结多个表

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 排序列表】
join含义：sql join子句用于把来自两个或者多个表的行结合起来，基于这些表之间的共同字段。
分类：
内连接： 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'在字段列表中重复，其实就是两张表有相同的字段，但是使用时表字段的名称前没有加表名，导致指代不明
 解决：前面加上前缀就没问题了。