Mysql 复习笔记- 基础篇4 [子查询&&联结表&&左连接&&右链接&&内连接]

子查询&&联结表&&左连接&&右链接&&内连接

子查询

下列是关于子查询使用

利用子查询进行过滤

列出够物品RGAN01的所有客户。

检索包含物品RGAN01的所有订单编号。

检索具有前一步骤列出的订单编号所有客户ID。

检索前一步骤返回的所有客户ID的客户信息。

 SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01';

解释: 列出所有RGAN01订单物品

 SELECT cust_id FROM Orders WHERE order_num IN (20007, 20008);

把上面两个查询组合成子查询

SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01');

• 子查询是从内向外处理
• 先执行 SELECT order_num FROM OrderItems WHERE prod_id = ‘RGAN01’
• 把返回的订单号，20007，20008两个值以IN操作符用逗号格式传递给外部查询，
• 再用SELECT cust_id FROM orders WHERE order_num IN (20007,20008)

检索客户的ID

SELECT cust_name, cust_contact FROM Customers WHERE cust_id IN (SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'));

• 解释:

  • SELECT order_num FROM OrderItems WHERE prod_id = ‘RGAN01’ 查询返回订 单列表

  • 把上面的查询结果，给予 SELECT cust_id FROM Orders， 返回客户ID

  • 拿到返回的客户ID给予外层WHERE子句查询。

    注意: 子查询只能返回单个列。

作为计算字段使用子查询

例子：计算Customers中每个客户订单总数。

• 从Customers 表中检索客户列表
• 对检索出来的每个客户，统计其在Orders表中的订单数目。

单个客户查询

 SELECT COUNT(*) AS orders FROM Orders WHERE cust_id ='1000000001';

对每个客户执行COUNT(*)

SELECT cust_name,cust_state, (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers ORDER BY cust_name;

解释: 对Customers表返回三列， cust_name , cust_state , orders .orders 是计算字

段，由 (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) 建 立。每检索一个客户，执行一次计算，

Orders.cust_id = Customers.cust_id , 其中的句号，表示指定限定表名跟列，如果不具体指定

表名，列名，将返回Orders 表中的订单总数。如下:

SELECT cust_name, cust_state,(SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id) AS orders FROM Customers ORDER BY cust_name;

联结表

关系表

例子: 一个包含目录的数据库，其中每种类型物品占用一行，每种物品要存储的信息包括产品描述 和价格，以及生产该产品的供应商信息。

有一个供应商生产多种物品，何处存储供应商的信息(地址，电话等)，如何分开存储。

1. 同一个供应商存储的信息都是相同的，每种产品重复信息，浪费空间
2. 如果供应商信息改变，执行改一次。
3. 重复数据，难保证每次储存信息一致，不一致信息难管理，利用。

关系数据库设计:

• 避免相同数据出现多次
• 信息被分解成一种数据，一个表
• 各表通过某些常用值相互关联

上面的例子，设计两个表，一个存储供应商信息，一个存储产品信息。

• Vendors 表包含所有供应商信息，供应商的primary key 唯一的标识值vend_id。
• Products表只存储产品信息，与供应商的primary key vend_id 表关联，利用供应商的 ID从Vendors表中找出相应的供应商详细信息。

这样的设计刚好符合上面3点。

关系数据库优点可伸缩性(scale),能够适应不断增加的工作量。

使用联结的好处

分解多个表方便存储，方便处理，可伸缩性强。

使用链接可以用一条SELECT中关联多个表返回一组输出。

注意:在设计关系数据库，避免在另一个关系表中插入非法的ID，可以设置关系表中值，只出现合法的值

创建联结

链接多个表

mysql> SELECT vend_name, prod_name, prod_price
    -> FROM Vendors, Products
    -> WHERE Vendors.vend_id = Products.vend_id;
+-----------------+---------------------+------------+
| vend_name       | prod_name           | prod_price |
+-----------------+---------------------+------------+
| Bears R Us      | 8 inch teddy bear   |       5.99 |
| Bears R Us      | 12 inch teddy bear  |       8.99 |
| Bears R Us      | 18 inch teddy bear  |      11.99 |
| Doll House Inc. | Fish bean bag toy   |       3.49 |
| Doll House Inc. | Bird bean bag toy   |       3.49 |
| Doll House Inc. | Rabbit bean bag toy |       3.49 |
| Doll House Inc. | Raggedy Ann         |       4.99 |
| Fun and Games   | King doll           |       9.49 |
| Fun and Games   | Queen doll          |       9.49 |
+-----------------+---------------------+------------+
9 rows in set (0.00 sec)

解释:

• SELECT vend_name, prod_name, prod_price 指定检索的列，prod_name, prod_price 在同一个表。vend_name 在另外一个表
• From 指定联结两个表Vendors, Products
• WHERE子句限定 Vendors.vend_id = Products.vend_id 完全限定名。

WHERE子句的重要

• 笛卡儿积(cartesian product) 由没有联结的条件表关系返回的结果，

保证所有联结都有WHERE子句，否则返回比的数据会比想要的数据多很多。

mysql> SELECT vend_name, prod_name, prod_price
    -> FROM Vendors, Products;
+-----------------+---------------------+------------+
| vend_name       | prod_name           | prod_price |
+-----------------+---------------------+------------+
| Bear Emporium   | Fish bean bag toy   |       3.49 |
| Bears R Us      | Fish bean bag toy   |       3.49 |
| Doll House Inc. | Fish bean bag toy   |       3.49 |
| Fun and Games   | Fish bean bag toy   |       3.49 |
| Furball Inc.    | Fish bean bag toy   |       3.49 |
| Jouets et ours  | Fish bean bag toy   |       3.49 |
| Bear Emporium   | Bird bean bag toy   |       3.49 |
| Bears R Us      | Bird bean bag toy   |       3.49 |
| Doll House Inc. | Bird bean bag toy   |       3.49 |
| Fun and Games   | Bird bean bag toy   |       3.49 |
| Furball Inc.    | Bird bean bag toy   |       3.49 |
| Jouets et ours  | Bird bean bag toy   |       3.49 |
| Bear Emporium   | Rabbit bean bag toy |       3.49 |
| Bears R Us      | Rabbit bean bag toy |       3.49 |
| Doll House Inc. | Rabbit bean bag toy |       3.49 |
|