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

数据库 专栏收录该内容
313 篇文章 2 订阅

1、子查询

  • 多个select语句,可以查询一个表,也可以查询多个表

子查询就是指在一个select语句中嵌套另一个select语句。同时,子查询必须包含括号。
MySQL 5.6.x 版本之前,MySQL的子查询性能较差,但是从5.6开始,不存在性能差的问题。

select a from t1 where a > any(select a from t2);
  1. select a from t1 是外部查询(outer query)
  2. (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关键词必须与一个比较操作符一起使用: =, >, <, >=, <=, <> (这个是!=的意思)

子查询中SOMEANY是同一个意思

(2)IN

inANY的一种特殊情况:"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、组合查询

  1. UNION 的作用是将两个查询的结果集进行合并。
  2. UNION必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔。
  3. 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'在字段列表中重复,其实就是两张表有相同的字段,但是使用时表字段的名称前没有加表名,导致指代不明
     解决:前面加上前缀就没问题了。
    
  • 0
    点赞
  • 0
    评论
  • 2
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:黑客帝国 设计师:我叫白小胖 返回首页

打赏作者

OceanStar的学习笔记

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值