MySQL 连接查询

时间：2024-04-12 13:25:15

目录

连接查询

命令格式：

内连接：

等值连接：

格式：

非等值连接：

格式：

外连接：

左连接：

格式:

结果：

右连接：

格式:

结果：

全外连接：

格式：

练习

表联系图：

连接查询

连接查询也叫多表查询，常用于查询表头来自于多张表，通过不同连接方式把多张表重新组成一张新的临时表，再对临时表做数据处理。

命令格式：

SELECT 表头名 FROM 表1 as 别名 INNERJOIN 表2 as 别名 ON 连接条件
[WHERE 筛选条件][GROUP BY 分组]|[HAVING 分组后筛选]|[ORDER BY排序]|[limit 分页];

内连接：

等值连接：

使用相等判断做连接条件：表里必须有存储相同数据的表头

格式：

SELECT 表头名列表 FROM 表1 as别名 INNERJOIN 表2 as别名 ON 连接条件                   [WHERE 筛选条件] | [GROUP BY 分组]|[HAVING 分组后筛选] | [ORDER BY 排序列表]   

非等值连接：

不使用相等判断做连接条件据的表头时使用：适合表里没有存储相同类

格式：

SELECT 字段列表 FROM 表1 as别名 INNERJOIN 表2 as别名 ON 连接条件                                  [WHERE 筛选条件] | [GROUP BY 分组 [HAVING 分组后筛选] | [ORDER BY 排序列表

外连接：

左连接：

格式:

表名 LEFT JOIN 表名ON 连接条件

结果：

左表表头记录全显示，右表表头只显示与条件匹配的记录，右表比左表少的记录使用NULL匹配

右连接：

格式:

表名 RIGHT JOIN 表名ON 连接条件

结果：

右表表头记录全显示，左表表头只显示与条件匹配的记录，左表比右表少的记录使用NULL匹配

全外连接：

也称联合查询，用来合并查询结果可以合并同一张的表的查询记录(不同表的查询记录也可合并要求查询时，多个select语句查询表头个数必须一样UNION关键字默认去重，可以使用UNI0N ALL包含重复项

格式：

(SELECT语句) UNION (SELECT语句); 去重

(SELECT语句) UNION  ALL (SELECT语句); 不去重

练习

表联系图：

1.编写 SQL 语句以从 Customers 表中返回客户名称 （cust_name） 并从 Orders 表中返回相关订单号 （order_num），然后按客户名称和订单号对结果进行排序。尝试两次，一次使用简单的 equijoin 语法，一次使用 INNER JOIN。

mysql> SELECT cust_name, order_num
    -> FROM customers, orders
    -> WHERE customers.cust_id = orders.cust_id
    -> ORDER BY cust_name, order_num;
+----------------+-----------+
| cust_name      | order_num |
+----------------+-----------+
| Coyote Inc.    |     20005 |
| Coyote Inc.    |     20009 |
| E Fudd         |     20008 |
| Wascals        |     20006 |
| Yosemite Place |     20007 |
+----------------+-----------+
5 rows in set (0.00 sec)

mysql> SELECT cust_name, order_num
    -> FROM customers INNER JOIN orders
    ->  ON customers.cust_id = orders.cust_id
    -> ORDER BY cust_name, order_num;
+----------------+-----------+
| cust_name      | order_num |
+----------------+-----------+
| Coyote Inc.    |     20005 |
| Coyote Inc.    |     20009 |
| E Fudd         |     20008 |
| Wascals        |     20006 |
| Yosemite Place |     20007 |
+----------------+-----------+
5 rows in set (0.00 sec)

2.除了返回客户名称和订单号外，还可以添加名为 OrderTotal 的第三列，其中包含每个订单的总价。有两种方法可以做到这一点，可以使用 OrderItems 表上的子查询创建 OrderTotal 列，也可以将 OrderItems 表联接到现有表并使用聚合函数。这里有一个提示，请注意需要使用完全限定列名称的位置。 

mysql> SELECT cust_name,
    ->        order_num,
    ->        (SELECT Sum(item_price*quantity)
    ->        FROM orderitems
    ->        WHERE orders.order_num=orderitems.order_num) AS ordertotal
    -> FROM customers, orders
    -> WHERE customers.cust_id = orders.cust_id
    -> ORDER BY cust_name, order_num;
+----------------+-----------+------------+
| cust_name      | order_num | ordertotal |
+----------------+-----------+------------+
| Coyote Inc.    |     20005 |     149.87 |
| Coyote Inc.    |     20009 |      38.47 |
| E Fudd         |     20008 |     125.00 |
| Wascals        |     20006 |      55.00 |
| Yosemite Place |     20007 |    1000.00 |
+----------------+-----------+------------+
5 rows in set (0.00 sec)

mysql> SELECT cust_name,
    ->        orders.order_num,
    ->        Sum(item_price*quantity) AS ordertotal
    -> FROM customers, orders, orderitems
    -> WHERE customers.cust_id = orders.cust_id
    ->  AND orders.order_num = orderitems.order_num
    -> GROUP BY cust_name, orders.order_num
    -> ORDER BY cust_name, order_num;
+----------------+-----------+------------+
| cust_name      | order_num | ordertotal |
+----------------+-----------+------------+
| Coyote Inc.    |     20005 |     149.87 |
| Coyote Inc.    |     20009 |      38.47 |
| E Fudd         |     20008 |     125.00 |
| Wascals        |     20006 |      55.00 |
| Yosemite Place |     20007 |    1000.00 |
+----------------+-----------+------------+
5 rows in set (0.00 sec)

 3.编写一个 SQL 语句，用于检索订购产品 BR01 的日期，但这次使用 join 和简单的 equijoin 语法。

mysql> SELECT cust_id, order_date
    -> FROM orders, orderitems
    -> WHERE orders.order_num = orderitems.order_num
    ->  AND prod_id = 'TNT2'
    -> ORDER BY order_date;
+---------+---------------------+
| cust_id | order_date          |
+---------+---------------------+
|   10001 | 2023-09-01 00:00:00 |
|   10004 | 2023-09-30 00:00:00 |
+---------+---------------------+
2 rows in set (0.00 sec)

相关文章