程序员经验分享-hwhale

MySQL必知必会——第十六章创建高级联结

2023-11-04

创建高级联结

本章将讲解另外一些联结类型（包括它们的含义和使用方法），介绍如何对被联结的表使用表别名和聚集函数。

使用表别名

第十章（MySQL必知必会——第十章创建计算字段）介绍了如何使用别名引用表列：

mysql> SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
    -> FROM vendors
    -> ORDER BY vend_name;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+
6 rows in set (0.00 sec)

别名除了用于表列和计算字段外，SQL还允许给表名取别名，优点：

  • 缩短SQL语句。
  • 允许在单条SELECT语句中多次使用同一张表。

对前一章（MySQL必知必会——第十五章联结表）的例子使用表别名：

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        |
+----------------+--------------+
2 rows in set (0.03 sec)

FROM子句中的三个表全部都具有表别名。这使得能省写而不是写全名。表别名不仅能用于WHERE子句，还可以用于SELECT的列表，ORDER BY子句及其他语句部分。

表别名只在查询执行中使用，不返回到客户机。

使用不同类型的联结

到目前我们使用的都是称为内部联结或等值联结（equijoin）的简单联结。还其他的三种联结，分别为自联结、自然联结和外部联结。

自联结

前面所说，别名的一个优点是允许在单条SELECT语句中多次使用同一张表。

假如发现某件物品（如ID为DTNTR）存在问题，因此想知道该物品生产商的其他物品是否同样存在问题。下面是一种查找该物品生产商的所有物品的方法：

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) |
+---------+----------------+
7 rows in set (0.01 sec)

这是第一种方法，使用子查询（MySQL必知必会——第十四章使用子查询）。首先查出供应商的ID，再根据供应商的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) |
+---------+----------------+
7 rows in set (0.00 sec)

此查询中的两个表是同一个表。但因为有二义性，所以我们要使用表别名才能正常使用两张表。

  • 用自联结而不用子查询 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然结果相同，但有时联结的速度比子查询快得多。

自然联结

对表进行联结，应该保证至少有一个列出现在不止一个表中（被联结的列）。标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

但，系统不完成这项工作，这由你自己完成。自然联结一般通过对表使用通配符（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 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
2 rows in set (0.01 sec)

这个例子中，没有重复的列。

目前我们建立的联结都是自然联结，以后也可能都碰不到非自然联结。

外部联结

许多联结将一张表的行与另一张表的行相关联。但有时我们需要没有关联的行。例如：

  • 对每个客户下了多少订单进行计数，包括没下订单的客户。
  • 列出所有产品以及订购数量，包括没人订购的产品。
  • 计算平均销售规模，包括未下订单的客户。

联结包含在相关表中没有关联行的行，称为外部联结。

检索所有订单和客户的内部联结：

mysql> 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.01 sec)

使用外部联结，检索所有客户，包括未下订单的客户：

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 |
+---------+-----------+
6 rows in set (0.00 sec)

此SELECT语句使用OUTER JOIN关键字来指定联结的类型，而非WHERE中指定。与内部联结不同的是，外部联结包含了客户10002，而客户10002并未与任何订单相关联。在使用OUTER JOIN语法时，需要使用LEFT或RIGHT指定需要包含所有行的表，LEFT指定关键字左边的表，RIGHT指定关键字右边的表。

选定右边表的所有行：

mysql> SELECT customers.cust_id, orders.order_num
    -> FROM customers RIGHT OUTER 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)
  • 没有*=操作符 MySQL不支持简化字符*=和=*的使用。
  • 外部联结的类型 存在两种基本的外部联结形式：左外部联结和右外部联结。它们唯一差别是所关联的表顺序不同。左外部联结可通过颠倒表的顺序转换为右外部联结。

使用带聚集函数的联结

聚集函数（MySQL必知必会——第十二章汇总数据）用来汇总数据。虽然目前的例子都是从单张表中汇总数据，但可以在联结中使用汇总函数。

检索所有客户及客户下单数：

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 |
+----------------+---------+---------+
4 rows in set (0.01 sec)

聚集函数也可以方便地与其他联结一起使用：

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 |
+----------------+---------+---------+
5 rows in set (0.01 sec)

此例子使用外部联结显示所有的客户，包括没有订单的客户。

使用联结和联结条件

关于联结及其使用的要点：

  • 注意所使用的联结类型。一般使用內部联结，但有时使用外部联结。
  • 保证使用正确的联结条件，否则将返回不正确的数据。
  • 应该总是提供联结条件，否则会得出笛卡儿积。
  • 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。但应该对每个联结进行单独测试，从而方便故障的排除。
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

MYSQL必知必会

mysql

数据库

database

sql

MySQL必知必会——第十六章创建高级联结 的相关文章

  • SQL参数化查询不显示结果

    我的 DataAcess 类中有以下函数 但它没有显示任何结果 我的代码如下 public List

  • 获取家庭成员

    假设以下家庭 其构建架构是 create table PersonConn child int parent int insert into PersonConn values 1 2 insert into PersonConn valu

  • 根据表sql中的行替换字符串中的字符

    我需要用一些映射的字符替换字符串中的字符列表 我有一个表 dbo CharacterMappings 有 2 列 CharacterToFilter 和 ReplacementCharacter 假设这个表中有3条记录 Filter Rep

  • Scrapy - 持续从数据库中获取要爬取的url

    我想不断地从数据库中获取要爬行的网址 到目前为止 我成功地从基地获取了 url 但我希望我的蜘蛛继续从该基地读取 因为该表将由另一个线程填充 我有一个管道 一旦爬行 工作 就会从表中删除 url 换句话说 我想使用我的数据库作为队列 我尝试

  • SQLite HAVING 比较错误

    我有一个测试 SQLite 表 用于存储带有值的报告数据 CREATE TABLE IF NOT EXISTS test fact daily revenue date TEXT revenue NUMERIC product TEXT I

  • 如何在Oracle中使用Timestamp_to_scn和Scn_to_timestamp？

    我的查询结果是这样的 select cast to date a start time mm dd yyyy hh mi ss pm as timestamp date of call ora rowscn from calling tab

  • 数据库分片和 Rails

    在 Rails 中处理分片数据库的最佳方法是什么 分片应该在应用层 活动记录层 数据库驱动层 代理层还是其他层处理 各自的优点和缺点是什么 FiveRuns 有一个名为的 gem数据结构 https github com bpot data

  • 如何使用PostGIS将多边形数据转换为线段

    我在 PostgreSQL PostGIS 中有一个多边形数据表 现在我需要将此多边形数据转换为其相应的线段 谁能告诉我如何使用 PostGIS 查询进行转换 提前致谢 一般来说 将多边形转换为线可能并不简单 因为没有一对一的映射 http

  • 查找 PostgreSQL 中所有范围集合的所有交集

    我正在寻找一种有效的方法来查找时间戳范围集之间的所有交集 它需要与 PostgreSQL 9 2 配合使用 假设这些范围代表一个人可以见面的时间 每个人都可以有一个或多个空闲时间范围 我想找到all可以召开会议的时间段 即所有人都有空的时间

  • 当sql连接中存在两个同名列时，如何从一个表列中获取值

    当我连接两个具有相同名称列的表时 我目前面临着尝试获取值的问题 例如 table1 date和table2 date 每个表中的日期不同 我将如何获取 日期 本例中的表1 我目前正在跑步 while row mysqliquery gt f

  • 截断 Mysql 表 Cron 作业？

    我在如何使用 cron 作业截断 Mysql 表时遇到了一些麻烦 无论我尝试什么 我似乎都无法让数据库清除表格 感谢您的帮助 mysql uderp example pexample hlocalhost Dexample e TRUNCA

  • 第三个下拉菜单不从数据库填充

    我有以下 Index php

  • Mysql 检索所有有限制的行

    我想检索特定用户的所有行 限制为 0 x 所以我只是想问是否有任何方法可以检索 mysql 中的所有行 而不调用返回 x 的 count id 的方法 而不重载现有函数 该函数在查询中根本没有限制 与我们的 string Relace 功能

  • 如何在Mysql中仅将不同的值从一个表复制到另一个表？

    我有一个大约 2 5GB 的 MySql 数据库 表 A 具有以下列 anoid query date item rank url 我刚刚创建了另一个仅包含列的表 b query and date 我想在查询列中插入所有不同的记录 及其各自

  • 在一个数据访问层中处理多个连接字符串

    我有一个有趣的困境 我目前有一个数据访问层 它必须与多个域一起使用 并且每个域都有多个数据库存储库 具体取决于所调用的存储过程 目前 我只需使用 SWITCH 语句来确定应用程序正在运行的计算机 并从 Web config 返回适当的连接字

  • 将古吉拉特语文本插入 MySQL 表会产生垃圾字符和不可读的文本

    我有三个 MySQL 表 我正在向其中插入古吉拉特语内容 当我插入两个表时 它们插入得很好并且可读 但在一个表中 它显示垃圾字符 不可读的文本 我怎样才能解决这个问题 MySQL 有每个表的字符集设置 http dev mysql com

  • 了解 SSMS 2008 中关系的更新和删除规则

    当我们定义外键约束时 我对 SQL Server 2008 Management Studio 中的更新和删除规则的含义感到困惑 我也没有找到相关的帮助文档 例如F1帮助 这是屏幕快照 如果有人能描述它们的含义并推荐一些相关文档来阅读 我将

  • 对多个数据库执行 SQL 查询

    我知道我的帖子与该论坛中的其他帖子的标题非常相似 但我真的找不到我需要的答案 这是我的问题 我的 Windows Server 上运行着 SQL Server 在我的 SQL Server 中 我有大约 30 个数据库 它们都具有相同的表和

  • Mysql 创建定义器

    我创建了一个在 CentOS Web 服务器上运行的 Intranet Web 应用程序 该应用程序使用另一个本地服务器 始终是 CentOS 作为 MySQL 数据库 在数据库内部我创建了例程 这些例程总是这样开始 CREATE DEFI

  • MySQL 查询计算上个月

    我想计算上个月的订单总额 我收到了从当前日期获取当月数据的查询 SELECT SUM goods total AS Total Amount FROM orders WHERE order placed date gt date sub c

随机推荐

热门标签