《MySQL必知必会》读书笔记 —— 第14章使用子查询、第15章联结表、第16章创建高级联结、第17章组合查询

最新推荐文章于 2024-09-26 14:19:34 发布

2401_85764651

最新推荐文章于 2024-09-26 14:19:34 发布

阅读量569

点赞数 5

文章标签： mysql adb android

本文链接：https://blog.csdn.net/2401_85764651/article/details/139758802

版权

文章目录

第14章使用子查询
- 14.1 子查询
14.2 利用子查询进行过滤
14.3 作为计算字段使用子查询
第15章联结表
- 15.1 联结
- 15.1.1 关系表
15.1.2 为什么要使用联结
15.2 创建联结
- 15.2.1 WHERE子句的重要性
15.2.2 内部联结（等值联结）
15.2.3 联结多个表
第16章创建高级联结
- 16.1 使用表别名
16.2 使用不同类型的联结
- 16.2.1 自联结
16.2.2 自然联结
16.2.3 外部联结
16.3 使用带聚集函数的联结
16.4 使用联结和联结条件
第17章组合查询
- 17.1 组合查询
17.2 创建组合查询

这里可以说是MySQL查询的精华部分了。

先做些准备工作。

介绍需要用到的几个表，以及它们的结构，建库建表的SQL脚本在文章末尾给出。

供应商表

其中vend_id是主键，自增

mysql> describe vendors;

±-------------±------------±-----±----±--------±---------------+

±-------------±------------±-----±----±--------±---------------+

| vend_id | int | NO | PRI | NULL | auto_increment |

±-------------±------------±-----±----±--------±---------------+

产品表

prod_id作为主键，vend_id作为外键连接到vendors表。

供应商到产品属于一对多的关系。

mysql> describe products;

±-----------±-------------±-----±----±--------±------+

±-----------±-------------±-----±----±--------±------+

| vend_id | int | NO | | NULL | |

±-----------±-------------±-----±----±--------±------+

客户表

这张表比较简单，就是用来记录客户信息的。

mysql> describe customers;

±-------------±------------±-----±----±--------±---------------+

±-------------±------------±-----±----±--------±---------------+

| cust_id | int | NO | PRI | NULL | auto_increment |

±-------------±------------±-----±----±--------±---------------+

订单表

注意，订单表本身并不存储商品的信息。

使用cust_id连接到客户表，order_num连接到订单商品表。

所以这张表实际上是张关系表。

mysql> describe orders;

±-----------±------------±-----±----±--------±---------------+

±-----------±------------±-----±----±--------±---------------+

| order_num | int | NO | PRI | NULL | auto_increment |

±-----------±------------±-----±----±--------±---------------+

订单商品表

一个客户在某个日期下了一个订单，可能同时买了若干个产品。

比如订单号为 20012，订了3件商品，商品编号分别为1、2、3。

所以这张表，使用order_num和order_item作为主键、prod_id作为外键连接到产品表。

mysql> describe orderitems;

±-----------±-------------±-----±----±--------±------+

±-----------±-------------±-----±----±--------±------+

| order_num | int | NO | PRI | NULL | |

| order_item | int | NO | PRI | NULL | |

| quantity | int | NO | | NULL | |

±-----------±-------------±-----±----±--------±------+

商品描述表

这张表存在的意义是，不是所有产品都有描述，并且每件产品的描述个数不同。

所以商品表到描述表实际上就是一对多的关系。

mysql> describe productnotes;

±----------±-------------±-----±----±--------±------+

±----------±-------------±-----±----±--------±------+

| note_id | int | NO | PRI | NULL | |

±----------±-------------±-----±----±--------±------+

第14章使用子查询

==========================================================================

14.1 子查询

查询（query）任何SQL语句都是查询。但此术语一般指SELECT语句。

子查询（subquery），即嵌套在其他查询中的查询。

子查询最常见的用途：

WHERE子句的IN操作符中
用来填充计算列（计算字段）。

14.2 利用子查询进行过滤

假如需要列出订购物品TNT2的所有客户，应该怎样检索？

检索包含物品TNT2的所有订单的编号。
检索具有前一步骤列出的订单编号的所有客户的ID。
检索前一步骤返回的所有客户ID的客户信息。

包含子查询的SELECT语句难以阅读和调试，特

别是它们较为复杂时更是如此。如上所示把子查询分解为多行

并且适当地进行缩进，能极大地简化子查询的使用。

SELECT cust_id,

cust_name

FROM customers

WHERE cust_id IN (SELECT cust_id

FROM orders

WHERE order_num IN(SELECT order_num

FROM orderitems

WHERE prod_id = ‘TNT2’));

子查询会出现所谓N+1的查询问题。

也就是一条SQL语句查询出N个结果，然后依次对着N个结果再执行一条SQL语句，最终相当于一共执行了N+1条SQL语句。

子查询的性能 这里给出的代码有效并获得所需的结果。但是，使用子查询并不总是执行这种类型的数据检索的最有效的方法。

14.3 作为计算字段使用子查询

(这部分内容其实更好地反映N+1查询的问题。)

假如需要显示customers表中每个客户的订单总数,怎么做？订单与相应的客户ID存储在orders表中。

SELECT cust_id,

cust_name,

(SELECT Count(*)

FROM orders

WHERE orders.cust_id = customers.cust_id) AS order_number

FROM customers.

相关子查询

涉及外部查询的子查询，叫做相关子查询，这时必须使用全限定名来区分有歧义的列名。

不止一种解决方案正如本章前面所述，虽然这里给出的样

例代码运行良好，但它并不是解决这种数据检索的最有效的

方法。

第15章联结表

========================================================================

15.1 联结

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）

表。联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结

及其语法是学习SQL的一个极为重要的组成部分。

15.1.1 关系表

举一个例子。

一个老师有很多学生，一个学生有很多科目的老师。

比如，有两张表，学生表和老师表，存储着每个班上的学生以及他们的语文老师的信息。

那么实际上就是一个一对多的关系——一个老师对应着多个学生。

学生有学生的ID，老师有老师的ID，分别作为两张表的主键。

如何将两张表联系起来呢？

将老师表的主键作为学生表的一列，也就是外键，这样更改老师表里某个ID的老师信息，ta的学生的信息里对应的老师的信息就全都修改了。

外键（foreign key）外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

这样做的好处：

不存储重复的信息，不浪费时间和空间；
由于数据无重复，显然数据是一致的，这使得处理数据更简单。
关系数据库的**可伸缩性（能够适应不断增加的工作量而不失败）**远比非关系数据库要好。

15.1.2 为什么要使用联结

联结是一种机制，用来在**一条SELECT

语句中关联表**，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

15.2 创建联结

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。

创建联结两种语法：

（这是也等值联结）

SELECT vend_name,

prod_name,

prod_price

FROM vendors,products

WHERE vendors.vend_id = products.vend_id;

SELECT vend_name,

prod_name,

prod_price

FROM vendors

INNER JOIN products ON vendors.vend_id = products.vend_id;

推荐使用第二种，因为这是一种同一且规范的写法。

完全限定列名 在引用的列可能出现二义性时，必须使用完

全限定列名（用一个点分隔的表名和列名）。如果引用一个

没有用表名限制的具有二义性的列名，MySQL将返回错误。

15.2.1 WHERE子句的重要性

请记住，在一条SELECT语句中联结几个表时，相应的关系是

在运行中构造的。在数据库表的定义中不存在能指示MySQL如何对表进

行联结的东西。你必须自己做这件事情。

构造这种联结的过程实际上相当于两个嵌套的for循环：

①将第一个表中的每一行与第二个表中的每一行配对。

②WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。

可以知道，如果没有where去过滤，得到的实际上是两个表的笛卡尔积（cartesian product）。如果第一个表有m行，第二表有n行，那么就一返回m*n行。

15.2.2 内部联结（等值联结）

使用这种语法，联结条件使用ON子句。

两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。

SELECT vend_name,

prod_name,

prod_price

FROM vendors

INNER JOIN products ON vendors.vend_id = products.vend_id;

15.2.3 联结多个表

SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结

的基本规则也相同。首先列出所有表，然后定义表之间的关系。

此例子显示编号为20005的订单中的物品。

SELECT prod_name,

vend_name,

prod_price,

quantity

FROM orderitems, products, vendors

WHERE products.vend_id = vendors.vend_id

AND orderitems.prod_id = products.prod_id

AND order_num = 20005;

这里的FROM子句列出了3个表，而WHERE子句定义了这两个联结条件，而第三个联结条件用来过滤出订单20005中的物品。

性能考虑

MySQL在运行时关联指定的每个表以处理联结。

这种处理可能是非常耗费资源的，因此应该仔细，不要联结

不必要的表。联结的表越多，性能下降越厉害。

第16章创建高级联结

===========================================================================

16.1 使用表别名

SQL还允许给表名起别名。这样做有两个主要理由：

缩短SQL语句；
允许在单条SELECT语句中多次使用相同的表（强制）。

举例：

SELECT p.prod_id,

p.prod_name,

p.prod_price

FROM products AS p;

16.2 使用不同类型的联结

15章介绍了内部联结（等值联结）。

16.2.1 自联结

如前所述，使用表别名的主要原因之一是能在单条SELECT语句中不止一次引用相同的表。

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

使用子查询

SELECT prod_id,prod_name

FROM products

WHERE vend_id = (SELECT vend_id FROM products where prod_id = ‘DTNTR’);

使用联结

SELECT p1.prod_id,prod_name

FROM products AS p1, produtcs AS p2

WHERE p1.vend_id = p2.vend_id AND p2.prod_id = ‘DINTR’;

用自联结而不用子查询 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

16.2.2 自然联结

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。

自然联结排除多次出现，使每个列只返回一次。

不过这项工作只能由我们自己完成，MYSQL不解决。

16.2.3 外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。

有如下应用场景：

对每个客户下了多少订单进行计数，包括那些至今尚未下订单的

客户；

列出所有产品以及订购数量，包括没有人订购的产品；
计算平均销售规模，包括那些至今尚未下订单的客户。

外部联结 ：联结包含了那些在相关表中没有关联行的行。

外部联结使用：OUTER JOIN (不过实际上，我们不加上OUTER)。

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表。

一般情况下使用LEFT JOIN即可。

SELECT c.cust_id, o.order_num

FROM customers AS c

LEFT JOIN orders AS o ON c.cust_id = o.cust_id;

16.3 使用带聚集函数的联结

SELECT c.cust_name

c.cust_id,

COUNT(o.order_num) AS num_ord

FROM c AS c

INNER JOIN orders AS o c.cust_id = c.cust_id

GROUP BY c.cust_id;

如果也要显示那些没有从没有下过订单的用户。

SELECT c.cust_name

c.cust_id,

COUNT(o.order_num) AS num_ord

FROM c AS c

LEFT JOIN orders AS o c.cust_id = c.cust_id

GROUP BY c.cust_id;

16.4 使用联结和联结条件

汇总一下关于联结及其使用的某些要点

注意所使用的联结类型。是内部联结还是外部联结
应该总是提供联结条件，否则会得出笛卡儿积。
保证使用正确的联结条件，否则将返回不正确的数据。

第17章组合查询

=========================================================================

17.1 组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语

句。MySQL也允许执行多个查询（多条SELECT语句），并**将结果作为单个

查询结果集**返回。

有两种基本情况，其中需要使用组合查询：

在单个查询中从不同的表返回类似结构的数据；

比如有两个表，它们有一些交集列（比如，学生表、教师表，都有姓名、学工号），

这个时候可以从这两张表查询出相同数据类型的列（准确来讲是类似的结构）。

对单个表执行多个查询，按单个查询返回数据。

组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的

工作相同。

17.2 创建组合查询

UNION的使用很简单。所需做的只是给出每条SELECT语句，在各条语

句之间放上关键字UNION。

具体的有如下规则：

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以

隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。