MySQL联结表与创建高级联结表.md

最新推荐文章于 2023-04-07 23:08:29 发布
最新推荐文章于 2023-04-07 23:08:29 发布
阅读量486 收藏
点赞数 1
分类专栏： 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/limao693/article/details/73253291
版权

15章 联结表

15.1 联结

15.1.1 关系表

外键(foreign key)：外键为某个表中的一列，包含另一个表的主键值，定义了两个表的关系。
可伸缩性(scale)：能够适应不断增加的工作量而不失败。

15.1.2 为何使用联结

如果数据存储在多个表中，想要用单条SELECT语句检索数据？ 使用联结。

15.2 创建联结

mysql> 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 |
+-------------+----------------+------------+

分析：与前面语句检索区别在于两个列在其中一个表中，而另外一列(vend_name)在另外一个表中。
FROM语句，列出两个表，分别是vendors 和 products，这里需要使用 完全限定列名。
完全限定列名：再次强调，出现二义性时，必须使用。

mysql> SELECT vend_name, prod_price FROM vendors, products ORDER BY vend_name, prod_name;
+----------------+------------+
| vend_name      | prod_price |
+----------------+------------+
| ACME           |       5.99 |
| ACME           |       9.99 |
| ACME           |      14.99 |
| ACME           |      10.00 |
| ACME           |       2.50 |
| ACME           |      13.00 |
| ACME           |       3.42 |
| ACME           |      35.00 |
| ACME           |      55.00 |
| ACME           |       8.99 |
| ACME           |      50.00 |
| ACME           |       4.49 |
| ACME           |       2.50 |
| ACME           |      10.00 |
| Anvils R Us    |       5.99 |
| Anvils R Us    |       9.99 |
| Anvils R Us    |      14.99 |
| Anvils R Us    |      10.00 |
| Anvils R Us    |       2.50 |
| Anvils R Us    |      13.00 |
| Anvils R Us    |       3.42 |
| Anvils R Us    |      35.00 |
| Anvils R Us    |      55.00 |
| Anvils R Us    |       8.99 |
| Anvils R Us    |      50.00 |
| Anvils R Us    |       4.49 |
| Anvils R Us    |       2.50 |
| Anvils R Us    |      10.00 |
| Furball Inc.   |       5.99 |
| Furball Inc.   |       9.99 |
| Furball Inc.   |      14.99 |
| Furball Inc.   |      10.00 |
| Furball Inc.   |       2.50 |
| Furball Inc.   |      13.00 |
| Furball Inc.   |       3.42 |
| Furball Inc.   |      35.00 |
| Furball Inc.   |      55.00 |
| Furball Inc.   |       8.99 |
| Furball Inc.   |      50.00 |
| Furball Inc.   |       4.49 |
| Furball Inc.   |       2.50 |
| Furball Inc.   |      10.00 |
| Jet Set        |       5.99 |
| Jet Set        |       9.99 |
| Jet Set        |      14.99 |
| Jet Set        |      10.00 |
| Jet Set        |       2.50 |
| Jet Set        |      13.00 |
| Jet Set        |       3.42 |
| Jet Set        |      35.00 |
| Jet Set        |      55.00 |
| Jet Set        |       8.99 |
| Jet Set        |      50.00 |
| Jet Set        |       4.49 |
| Jet Set        |       2.50 |
| Jet Set        |      10.00 |
| Jouets Et Ours |       5.99 |
| Jouets Et Ours |       9.99 |
| Jouets Et Ours |      14.99 |
| Jouets Et Ours |      10.00 |
| Jouets Et Ours |       2.50 |
| Jouets Et Ours |      13.00 |
| Jouets Et Ours |       3.42 |
| Jouets Et Ours |      35.00 |
| Jouets Et Ours |      55.00 |
| Jouets Et Ours |       8.99 |
| Jouets Et Ours |      50.00 |
| Jouets Et Ours |       4.49 |
| Jouets Et Ours |       2.50 |
| Jouets Et Ours |      10.00 |
| LT Supplies    |       5.99 |
| LT Supplies    |       9.99 |
| LT Supplies    |      14.99 |
| LT Supplies    |      10.00 |
| LT Supplies    |       2.50 |
| LT Supplies    |      13.00 |
| LT Supplies    |       3.42 |
| LT Supplies    |      35.00 |
| LT Supplies    |      55.00 |
| LT Supplies    |       8.99 |
| LT Supplies    |      50.00 |
| LT Supplies    |       4.49 |
| LT Supplies    |       2.50 |
| LT Supplies    |      10.00 |
+----------------+------------+
15.2.1 WHERE子句的重要性

笛卡尔积(cartesian product)：在没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目是将第一个表中的行数* 第二表中的行数。
WHERE子句：应当确保所有联结都是WHERE子句，否则MySQL将会返回比想象的数据多的多。
叉联结：有时我们会听到返回称为叉联结（cross join）的笛卡尔积的联结类型。

15.2.2 内部联结

目前所有的联结都是等值联结(equijion)，此联结也称为内部联结，修改豫剧来明确指定联结的类型。

mysql> SELECT vend_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id ORDER BY vend_name;
+-------------+------------+
| vend_name   | prod_price |
+-------------+------------+
| ACME        |      13.00 |
| ACME        |      10.00 |
| ACME        |       2.50 |
| ACME        |      50.00 |
| ACME        |       4.49 |
| ACME        |       2.50 |
| ACME        |      10.00 |
| Anvils R Us |       5.99 |
| Anvils R Us |       9.99 |
| Anvils R Us |      14.99 |
| Jet Set     |      35.00 |
| Jet Set     |      55.00 |
| LT Supplies |       3.42 |
| LT Supplies |       8.99 |
+-------------+------------+

分析：FROM子句中，以INNER JOIN指定，联结条件使用特定的 ON子句而不是WHERE传出。

15.2.3 联结多个表

-方式：首先列出所有表，然后定义表之间的关系。

mysql> 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;
+----------------+-------------+------------+----------+
| prod_name      | vend_name   | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil   | Anvils R Us |       5.99 |       10 |
| 1 ton anvil    | Anvils R Us |       9.99 |        3 |
| TNT (5 sticks) | ACME        |      10.00 |        5 |
| Bird seed      | ACME        |      10.00 |        1 |
+----------------+-------------+------------+----------+

分析：显示编号为20005的订单物品。通过三个联结条件过滤出所需要的信息。
注意：性能问题，联结表越多，性能下降越厉害。

同样的14章中的例子可以使用联结表方式

mysql> SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num AND prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

分析： 这里没有使用嵌套子查询，而是使用两个联结。

总结

联结表称得上SQL最重要的特征，有效使用联结需要对**关系数据库**设计有基本了解。

16章 创建高级联结

本章讲解另一些联结类型，介绍对联结的表使用表别名和聚集函数。

16.1 使用表别名

给表名起别名的优点：
1. 缩短SQL语句
2. 允许单条SELECT语句中多次使用相同的表

与14章中相比，下文中更改了表名称

mysql> SELECT cust_name, cust_contact FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

分析：FROM子句中三个表名全部具有别名。注意，表别名只能在查询执行中使用，与列名不同，表别名不返回客户机。

16.2 使用不同类型的联结

此前一直使用的联结为内部联结或等值联结(equijoin)，现有三种其他形式联结，*自联结*、**自然联结**和**外部联结**。
16.2.1 自联结

目的：因很多情况下，在一条SELECT子句中不止一次引用相同表格，为避免繁琐，引入自联结。

假如需要通过通过某物品，检索所有此供应商的物品。需要首先查找供应商→检索所有物品

mysql> 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) |
+---------+----------------+

分析：此方法利用 子查询(嵌套查询)，缺点：多次输入vend_id。相应的联结查询方式为：

mysql> 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) |
+---------+----------------+

分析：纵使使用同一个表，也需要使用 完全限定。同时使用自联结，用外部语句替代相同表中检索数据时使用的子查询语句。性能上，比子查询快。

16.2.2 自然联结
  • 宗旨：无论何时对表联结，至少有一个列出现在不止一个表中（被联结的列）。
  • 特点：自然联结排除多次出现，每个列只出现一次。
  • 工作方式：只能选择唯一的列，通常使用通配符(SELECT *)，对所有其他表的列使用明确子集完成。
mysql> SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB';
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| cust_id | cust_name   | cust_address   | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email      | order_num | order_date          | prod_id | quantity | item_price |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20005 | 2005-09-01 00:00:00 | FB      |        1 |      10.00 |
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20009 | 2005-10-08 00:00:00 | FB      |        1 |      10.00 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
16.2.3 外部联结

有时需要包含没有关联行进行关联。例如可能需要使用联结来完成如下工作：
1. 对每个客户订单进行计数，包括至今未下订单的客户。（此时订单表中不包含未下订单客户）
2. 列出所有产品及订购数量，包括没人订购的产品。

如下，检索所有客户和订单：

mysql> SELECT customers.cust_id, orders.order_num FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id;(优先使用此种方式)
mysql> SELECT customers.cust_id, orders.order_num FROM customers, orders WHERE customers..cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

为了检索没有订单的客户，需要进行如下(外部联结)操作：

mysql> SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER 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 |
+---------+-----------+
  • 分析：使用关键字OUTER JOIN来指定联结类型（不是在WHERE中指定）。与INNER JOIN不同地方在于，还包括为包含的关联行。
  • 注意：使用OUTER JOIN时，必须指明RIGHT，LEFT关键字（RIGHT指出，OUTER JOIN右侧的表）。customers LEFT OUTER JOIN orders从左侧customers表中选择所有行。

16.3 使用带聚集函数的联结

聚集函数用来汇总函数。可以同联结一起使用。

如要检索所有客户及每个客户所下订单数，使用COUNT()完成：

mysql> SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
  • 注意：函数COUNT()需要同 GROUP BY 一起使用，进行分组计算。同时聚集函数也可以用在如下外部联结：
mysql> SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers LEFT OUTER 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 |
+----------------+---------+---------+

16.4 使用联结和联结条件

  1. 注意联结类型。一般为内部联结，有时需要外部联结。
  2. 保证使用正确联结条件。
  3. 应该总是提供联结条件，否则产生笛卡尔积。
  4. 联结中可能包含多个表。测试前需要分别测试单个联结。
木工当道
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
【Unity3D进阶4-14】Unity3D 连接MySQL数据库
CSDN博客专家，Unity3D高级软件工程师，《Unity 3D从入门到实战》作者。
06-22 3306
一、目录 【Unity3D从入门到进阶】文章目录及设置这个专栏的初衷
Mysql数据库基础
weixin_44219435的博客
03-15 360
Mysql数据库 1.创建库表 例:创建学生表 表内字段为 学生id,学生姓名,性别,年龄 创建课程表 字段为 课程id 课程名 教师id 创建教师表 字段为 教师id,教师姓名 创建成绩表 字段为 学生id 课程id 分数 CREATE TABLE IF NOT EXISTS '表名'( 'sid' INT(4)NOT NULL AUTO_INCREMENT COMMENT '学号', 'sname'VARCHAR(30)NOT NULL DEFAULT'匿名'COMMENT '姓名', 'ssex'
MySQL联结表
知行知的专栏
07-18 2924
联结 SQL最强大的功能之一就是能在数据检索查询的执行中联结表。而联结表是基于关系表。理解关系表如下，假如由同一供应商生产的多种物品，在数据库中需要存储产品信息，而在产品信息的这一行不存储供应商信息，因为这些产品的供应商是同一个，所以在产品信息这一行里记录一个唯一的供应商id就可以，供应商信息单独的存储一个表，并且也用为一个的id记录。这样，产品表与供应商表就通过 共同的供应商id连接起来了。如
MySQL（七）联结表
weixin_33866037的博客
02-18 153
一、联结表基础知识 1、关系表 把信息分解成多个表，一类数据一个表，各表通过某些常用值（即关系设计中的关系（relational））互相关联； 2、外键（foreign key）：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系 3、联结表的优点： ①数据信息不重复，从而不浪费时间和空间 ②如果某个数据信息变动，可以只更新该表中的某个记录，相关表数据不用变更 ③数...
Mysql联结表
Oligizzz
03-01 212
一、关系表 假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。 对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供 应商信息。现在，假如有由同一供应商生产的多种物品，那么在何处存储供应 商信息(如，供应商名、地址、联系方法等)呢?将这些数据与产品信 息分开存储的理由如下。 因为同一供应商生产的每个产品的供应商信息都是相同的，对每 个产品重复此信息既浪费时间又浪费存储空间。 如果供应商信息改变(例如，供应商搬家或电话号码变动)，只需 改动一次即可。 如果有重复数据(即每种产品都存储供
【MySQL】联结表
卡兹戴尔
04-23 715
MySQL使用联结。
MySQL学习笔记2-高级查询与存储.md
最新发布
08-22
### MySQL高级查询与存储过程详解 #### 高级查询 在数据库管理中，高级查询能够帮助我们更加灵活地检索和处理数据。以下介绍几种常用的高级查询技术及其应用场景： ##### 子查询 子查询指的是在一个查询语句内部...
mysql学习笔记3：表联结，union，mysql的函数，视图，中文的乱码问题
shanshuisheng的博客
08-14 472
    表联结  两个表全相行，行的角度看，就是两个表的每一行的笛卡尔积。而从列的角度看，就是列的拼接。 全相乘的结果集，可以从中取出需要的部分列，这里的公共的列要使用全名：表名.列名 。 ambiguous：模糊不清的。 一个连接的例子：两个表相乘联查效率比较低 select goods_id,minigoods.cat_id,goods_name,category.cat_id...
读书笔记 高性能mysql
m0_74958466的博客
04-07 181
主键的上界会成为“热点”。有很多这样的 事故，在数据库耗尽磁盘空间之前，过度的日志记录或者对某些数据集的插入模式的变化 这样的情况都没有被检测到。在不同的会话在读取聚簇主键索引纪录时，innodb会将该纪录的事务id与该会话的读取视图进行比较，如果不可见，那么（更改它的事务尚未提交）那么undo日志将会被跟踪并应用，直到会话达到一个符合可见条件的事务id（写成这样文绉绉的是不是表示，某个视图的行会根据undo表，以一个事务的所有undo纪录为级别，修改视图的内容，知道符合之前设置的可见条件如可重复读之类?
MySQL数据库——联结表
qq_36120793的博客
12-19 1056
本节，我们将介绍SQL最强大的功能之一，就是在数据检索查询的执行中联结表。联结是利用SQL的SELECT能执行的最重要的操作。 part 1 关系型数据 和 查询他的外部联结 但是在开始之前，我们要先了解一些知识，作为储备！我们先要介绍一下，关系型数据库： 想一想如下情况，我们有一堆产品，他的信息有，价格，产品供应商名。 那我们应该如何存储这些信息呢？ 我们应该将这些数据和产品信息分开存储！因
MYSQL 的表联结
g1607058603的专栏
06-14 259
                                                                 a表idname1 张三2 李四3 王五                                                                b表idageparent_id12312342334z注：a的id和b的parent_id关联。(1...
MySQL之表联结
baidu_32542573的博客
02-26 226
1. 简单联结 ①内部联结 内部联结又称为等值联结。ANSI SQL规范首选INNER JOIN语法 。 内部联结分为隐式内联结和显式内联结，二者查询效果相同，仅仅是语法不一样而已。 隐式内联结：WHERE子句 FROM tb1_name, tb2_name, tb3_name WHERE condition_1 AND condition_2 显式内联结：INNER JOIN语法 FROM ...
MySQL学习——联结表
m0_46363470的博客
06-27 294
欢迎关注微信公众号：摸鱼汪的杂货铺 十五、联结表 15.1 联结 SQL最强大的功能之一就是能在数据检索查询的执行中联结(join)表。联结是利用SQL的SELECT能执行的最重要的操作。 15.1.1 关系表 现在，假如有由一供应商生产的多种物品，那么在何处存储供应商信息（如，供应商名、地址、联系方式等）呢？将这些数据与产品信息分开存储的理由如下： 1.因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间。 2.如果供应商信息改变（例如，供应商搬家或电话号码变.
mysql中表联结_MySQL -- 表联结
weixin_35693423的博客
01-28 222
创建联结:(使用WHERE联结)SELECTvend_name,prod_name,prod_priceFROMvendors,productsWHEREvendors.vend_id=products.vend_idORDERBYvend_name,prod_name;(保证所有联结都有WHERE子句,不然查询到的结果是两个表的笛卡联结:一种机制,用来在一条SELECT语句中关联表,因此称之为联...
【MySQL】4.表联结
qq_40314507的博客
03-04 219
1.MySQL别名 1.1列别名 别名是一个字段或值的替换名。别名用AS关键字赋予。比如： SELECT PTRIM(vend_name)+'("PTRIM(vend_country)+')' AS vend_title FROM Vendors ORDER BY vend_name; 1.2表别名 SQL除了可以对列名和计算字段使用别名，还允许给表名起别名。这样做主要...
MySQL 创建联结
cc_fys的博客
06-04 496
建表在这里呢我们先来建立两张有外键关联的张表。CREATE DATABASE db0206; USE db0206; CREATE TABLE `db0206`.`tbl_dept`( `id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30), `locAdd` VARCHAR(40), PRIMARY KEY ...
MYSQL必知必会笔记：第十五章联结表
xugenyin的博客
02-05 135
理 MYSQL必知必会笔记：第十五章联结表
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值