数据库—2.数据库高级

一、⼦查询与表连接

建表语句：https://blog.csdn.net/weixin_43372836/article/details/107815785

1. ⼦查询(嵌套sql)

SELECT语句是SQL的查询。迄今为⽌我们所看到的所有SELECT语句都是简单查询，即从单个数据 库表中检索数据的单条语句。

SQL还允许创建⼦查询（subquery），即嵌套在其他查询中的查询。

1). 利⽤⼦查询进⾏过滤

订单存储在两个表中。对于包含订单号、客户ID、订单⽇期的每个订单，orders表存储⼀⾏。 各订单的 物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。

实际的客户信息存储在customers表中。

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

--(1) 检索包含物品TNT2的所有订单的编号。
select order_num from orderitems where prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+
--(2) 检索具有前⼀步骤列出的订单编号的所有客户的ID
select cust_id from orders where order_num IN (20005,20007);
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
--(3) 检索前⼀步骤返回的所有客户ID的客户信息。
select cust_name,cust_contact from customers where cust_id in (10001,10004);
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+

可以把其中的WHERE⼦句转换为⼦查询⽽不是硬编码这些SQL返回的数据：

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 = 'TNT2'));
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
--为了执⾏上述SELECT语句，MySQL实际上必须执⾏3条SELECT语句。
--最⾥边的⼦查询返回订单号列表，此列表⽤于其外⾯的⼦查询的WHERE⼦句。
--外⾯的⼦查询返回客户ID列表，此客户ID列表⽤于最外层查询的WHERE⼦句。
--最外层查询确实返回所需的数据。

这⾥给出的代码有效并获得所需的结果。

但是，使⽤⼦查询并不总是执⾏这种类型的数据检索的最有效的⽅法。

2). 作为计算字段使⽤⼦查询

使⽤⼦查询的另⼀⽅法是创建计算字段。

-- 假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。
-- (1) 从customers表中检索客户列表。
select cust_id,cust_name from customers ;
+---------+----------------+
| cust_id | cust_name |
+---------+----------------+
| 10001 | Coyote Inc. |
| 10002 | Mouse House |
| 10003 | Wascals |
| 10004 | Yosemite Place |
| 10005 | E Fudd |
+---------+----------------+
-- (2) 对于检索出的每个客户，统计其在orders表中的订单数⽬。
select count(*) as orders from orders where cust_id = 10001;
+--------+
| orders |
+--------+
| 2 |
+--------+

为了对每个客户执⾏COUNT()计算，应该将COUNT()作为⼀个⼦查询。

select cust_id,cust_name,
 (select count(*)
 from orders
 where orders.cust_id = customers.cust_id
) as orders
from customers
order by cust_name;
+---------+----------------+--------+
| cust_id | cust_name | orders |
+---------+----------------+--------+
| 10001 | Coyote Inc. | 2 |
| 10005 | E Fudd | 1 |
| 10002 | Mouse House | 0 |
| 10003 | Wascals | 6 |
| 10004 | Yosemite Place | 1 |
+---------+----------------+--------+

orders是⼀个计算字段，它是由圆括号中的⼦查询建⽴的。该⼦查询对检索出的每个客户执⾏⼀ 次。在此例⼦中，该⼦查询执⾏了5次，因为检索出了5个客户。

注意:

⼦查询中的WHERE⼦句与前⾯使⽤的WHERE⼦句稍有不同，因为它使⽤了完全限定列名 这种类型的⼦查询称为相关⼦查询。任何时候只要列名可能有多义性，就必须使⽤这种语法 （表名和列名由⼀个句点分隔）。因为有两个cust_id列，⼀个在customers中，另⼀个在 orders中，需要⽐较这两个列以正确地把订单与它们相应的顾客匹配。如果不完全限定列名， MySQL将假定你是对orders表中的cust_id进⾏⾃身⽐较。

2. 表关系

1). 什么是关系表

SQL最强⼤的功能之⼀就是能在数据检索查询的执⾏中联结（join）表。 在能够有效地使⽤联结前，必须了解关系表以及关系数据库设计的⼀些基础知识。

--假如有⼀个包含产品⽬录的数据库表，其中每种类别的物品占⼀⾏。
--对于每种物品要存储的信息包括产品描述和价格，以及⽣产该产品的供应商信息。
产品表：
 产品，描述，价格，供应商名称，供应商地址,供应商联系⽅式
 A6 ... ... 奥迪 ... ....
 520li .. .... 宝⻢ ... ...
 ...

--现在，假如有由同⼀供应商⽣产的多种物品，那么在何处存储供应
--商信息（如，供应商名、地址、联系⽅法等）呢？
 产品，描述，价格，供应商名称，供应商地址,供应商联系⽅式
 A6 ... ... 奥迪 ... ....
 520li .. .... 宝⻢ ... ...
 A8 .. ... 奥迪 ... ...

相同数据出现多次决不是⼀件好事，此因素是关系数据库设计的基础。

关系表的设计就是要保证把信息分解成多个表，⼀类数据⼀个表。

各表通过某些常⽤的值（即关系设计中的关系（relational））互相关联。

在这个例⼦中，可建⽴两个表，⼀个存储供应商信息，另⼀个存储产品信息。

-- vendors表包含所有供应商信息
|vend_id | vend_name | vend_address| vend_city ....
-- products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。
prod_id | vend_id | prod_name | prod_price | prod_desc

vendors表的主键⼜叫作products的外键，它将vendors表与products表关联，利⽤供应商ID能

从vendors表中找出相应供应商的详细信息。 这样做的好处如下：

• 供应商信息不重复，从⽽不浪费时间和空间；
• 如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不⽤改动；
• 由于数据⽆重复，显然数据是⼀致的，这使得处理数据更简单

关系数据可以有效地存储和⽅便地处理。因此，关系数据库的可伸缩性远⽐⾮关系数据库要好。

2). 一对一关系与外键

一对一：就是在一个表中的数据，对应着另外一张表中的一个数据，只能有一个

外键：就是在一个表中的字段，这个字段中存储的数据是另一张表中的主键。就是在一个表中的字段，代表着这个数据属于谁。

了解：外键实现的方式，有两种：物理外键、逻辑外键

• 物理外键：
  • 就是在创建表时，就指定这个表中的字段是一个外键，并且强关联某个表中的某个字段。
  • 需要在定义表时，使用sql语句来实现
• 逻辑外键：
  • 就是在表中创建一个普通的字段，没有强关联关系，需要通过程序逻辑来实现

员工表：id，姓名，年龄，籍贯，联系方式，学历，工龄...

由上面的一个表，拆分成两个表

员工表：id，姓名，年龄，联系方式，工龄

详情表：性别，籍贯，学历...yid

上面的表关系就是一对一的关系，通过详情表中的yid这个字段来标记员工表中的逐渐。

一个员工表有着一个对应的详情信息，存储在详情表中，

3). 一对多关系

一对多：

• 就是在一个表中的数据，对应着另外一张表中的多条数据
• 或者一张表中的多条数据，对应着另外一张表中的一条数据

4). 多对多

多对多：

• 就是在一个表中的数据，对应着另外一张表中的多条数据
• 并且另外一张表中的一条数据对应着这张表中的多条数据

 

3. 表联结

1). 表联结where与join的用法

如果数据存储在多个表中，怎样⽤单条SELECT语句检索出数据？

答案是使⽤联结。简单地说，联结是⼀种机制，⽤来在⼀条SELECT语句中关联表，因此称之为联结。

使⽤特殊的语法，可以联结多个表返回⼀组输出，联结在运⾏时关联表中正确的⾏。

例如：我们需要查询出所有的商品及对应的供应商信息怎么办？where方法

-- 联结的创建⾮常简单，规定要联结的所有表以及它们如何关联即可。
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id = products.vend_id
order by vend_name,prod_name;
+-------------+----------------+------------+
| vend_name | prod_name | prod_price |
+-------------+----------------+------------+
| ACME | Bird seed | 10.00 |
| ACME | Carrots | 2.50 |
| ACME | Detonator | 13.00 |
| ACME | Safe | 50.00 |
| ACME | Sling | 4.49 |
| ACME | TNT (1 stick) | 2.50 |
| ACME | TNT (5 sticks) | 10.00 |
| Anvils R Us | .5 ton anvil | 5.99 |
| Anvils R Us | 1 ton anvil | 9.99 |
| Anvils R Us | 2 ton anvil | 14.99 |
| Jet Set | JetPack 1000 | 35.00 |
| Jet Set | JetPack 2000 | 55.00 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | Oil can | 8.99 |
+-------------+----------------+------------+
14 rows in set (0.00 sec)
--这两个表⽤WHERE⼦句正确联结，WHERE⼦句指示MySQL匹配vendors表中的vend_id和products表
中的vend_id。
--可以看到要匹配的两个列以 vendors.vend_id 和 products. vend_id指定。这⾥需要这种完全限
定列名，因为如果只给出vend_id，则MySQL不知道指的是哪⼀个（它们有两个，每个表中⼀个）。
--在引⽤的列可能出现⼆义性时，必须使⽤完全限定列名（⽤⼀个点分隔的表名和列名）。

在联结两个表时，你实际上做的是将第⼀个表中的每⼀⾏与第⼆个表中的每⼀⾏配对。

WHERE⼦句作为过滤条件，它只包含那些匹配给定条件（这⾥是联结条件）的⾏。

你能想象上⾯的sql如果没有where条件时会怎样吗？

select vend_name,prod_name,prod_price from vendors,products

如果没有where条件，第⼀个表中的每个⾏将与第⼆个表中的每个⾏配对，⽽不管它们逻辑上是 否可以配在⼀起 由没有联结条件的表关系返回的结果为笛卡⼉积。检索出的⾏的数⽬将是第⼀个表中的⾏数乘以 第⼆个表中的⾏数。

不要忘了WHERE⼦句

应该保证所有联结都有WHERE⼦句，否则MySQL将返回⽐想要的数据多得多的数据。 同理，应该保证WHERE⼦句的正确性。不正确的过滤条件将导致MySQL返回不正确的数据

其实，对于这种联结可以使⽤稍微不同的语法来明确指定联结的类型 join...on...方法

select vend_name,prod_name,prod_price from vendors inner join products on
vendors.vend_id = products.vend_id;

--可以不写inner
select vend_name,prod_name,prod_price from vendors join products on
vendors.vend_id = products.vend_id;

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

在使⽤这种语法时，联结条件⽤特定的ON⼦句⽽不是WHERE⼦句给出。

传递给ON的实际条件与传递给WHERE的相同。

SQL规范⾸选INNER JOIN语法。

2). 联结多个表

SQL对⼀条SELECT语句中可以联结的表的数⽬没有限制。

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

where方法

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;

改写 join 的语法

select prod_name,vend_name,prod_price,quantity
from orderitems
join products on orderitems.prod_id = products.prod_id
join vendors on products.vend_id = vendors.vend_id
where order_num = 20005;

MySQL在运⾏时关联指定的每个表以处理联结。 这种处理可能是⾮常耗费资源的，因此应该仔 细，不要联结不必要的表。联结的表越多，性能下降越厉害。

 

3).使⽤表别名 AS

别名除了⽤于列名和计算字段外，SQL还允许给表名起别名。

这样做有两个主要理由：

• 缩短SQL语句；
• 允许在单条SELECT语句中多次使⽤相同的表

应该注意，表别名只在查询执⾏中使⽤。与列别名不⼀样，表别名不返回到客户机

4). ⾃联结

自联结：当前这个表与自己这个表 做联结（join)

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

⼦查询(嵌套查询)

-- 使⽤⼦查询(嵌套查询)
select prod_id,prod_name
from products
where vend_id = (select vend_id from products where prod_id = 'DTNTR');
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+

自联结

-- 使⽤联结的相同查询：
select p1.prod_id,p1.prod_name
from products as p1
join products as p2 
on p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';

--改成where语句
select p1.prod_id,p1.prod_name
from products as p1,products as p2
where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';

+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+

-- 此查询中需要的两个表实际上是相同的表，因此products表在FROM⼦句中出现了两次。虽然这是完全
合法的，但对products的引⽤具有⼆义性，因为MySQL不知道你引⽤的是products表中的哪个实例。
-- 为解决此问题，使⽤了表别名。products的第⼀次出现为别名p1，第⼆次出现为别名p2。现在可以将
这些别名⽤作表名。
--例如，SELECT语句使⽤p1前缀明确地给出所需列的全名。如果不这样，MySQL将返回错误，因为分别存
在两个名为prod_id、prod_name的列。MySQL不知道想要的是哪⼀个列（即使它们事实上是同⼀个
列）。WHERE（通过匹配p1中的vend_id和p2中的vend_id）⾸先联结两个表，然后按第⼆个表中的
prod_id过滤数据，返回所需的数据

⽤⾃联结⽽不⽤⼦查询 ⾃联结通常作为外部语句⽤来替代从相同表中检索数据时使⽤的⼦查询语 句。 虽然最终的结果是相同的，但有时候处理联结远⽐处理⼦查询快得多。⼦查询(嵌套查询)是目前可明确知道的sql运行效率最低的一种方式，尽可能不使用嵌套语句

5). 外部联结-left join与right join

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

例如，可能需要使⽤联结来完成以下⼯作：

• 对每个客户下了多少订单进⾏计数，包括那些⾄今尚未下订单的客户；
• 列出所有产品以及订购数量，包括没有⼈订购的产品；
  • 计算平均销售规模，包括那些⾄今尚未下订单的客户

在上述例⼦中，联结包含了那些在相关表中没有关联⾏的⾏。这种类型的联结称为外部联结。

-- 内部联结。它检索所有客户及其订单：只能对两个表中相关联的数据进行查询
select customers.cust_id,orders.order_num 
from customers inner join orders 
on customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
5 rows in set (0.00 sec)

什么是外部联结？

left join: 是以 left join 左侧表为基准，去关联右侧的表进行联结，如果有未关联的数据 ，那么结果为空

right join: 是以 right join 右侧表为基准，去关联左侧的表进行联结，如果有未关联的数据 ，那么结果为空

--外部联结语法类似。检索所有客户，包括那些没有订单的客户
select customers.cust_id,orders.order_num 
from customers left join orders 
on customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10002 | NULL |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
6 rows in set (0.00 sec)

聚集函数也可以⽅便地与其他联结⼀起使⽤。

如果要检索所有客户及每个客户所下的订单数，下⾯使⽤了COUNT()函数的代码可完成此⼯作

包含那些没有任何下订单的客户。

--对每个客户下了多少订单进行计数，包括那些至今尚未下了订单的客户
select customers.cust_id,count(orders.order_num) as nums
from customers left join orders 
on customers.cust_id = orders.cust_id
group by customers.cust_id;
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc. | 10001 | 2 |
| Mouse House | 10002 | 0 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+---------+

保证使⽤正确的联结条件，否则将返回不正确的数据。
应该总是提供联结条件，否则会得出笛卡⼉积。
在⼀个联结中可以包含多个表，甚⾄对于每个联结可以采⽤不同的联结类型。虽然这样做是
合法的，⼀般也很有⽤，但应该在⼀起使用它们前，分别测试每个联结。这将使故障排除更
为简单。

总结：表联结

内部联结：where，inner join(join)

自联结：是在一个sql中，用当前的表，联结自己这个表进行关联查询

外部联结：left join，right join

4. 组合查询 UNION

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

这些组合查询通常称为并（union）或复合查询（compound query）。

UNION规则

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