MySQL操作汇总（多表查询）

作者: HaloZhang | 来源:发表于2021-03-10 15:31 被阅读0次

MySQL操作汇总（多表查询）
2018-03-20
MySQL 多表操作
mysql数据库-多表查询
SQLAlchemy（四）
深入浅出MySQL(五)
Mysql-多表查询as索引
MySQL基础——事务
4/30day45_MySql多表
MySQL学习笔记二之单表查询与多表查询

简介

在上一篇文章MySQL操作汇总（单表创建查询）中，我们介绍了在MySQL中如何创建单张表，并进行相关的查询、更新、删除等操作。但实际上一个数据库中往往包含多张表，因此往往需要对多张表进行联结（join）查询。联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结及其语法是学习SQL的一个极为重要的组成部分。

多张表

在上一篇文章MySQL操作汇总（单表创建查询）中，我们引入了一张名为products的表，记录了产品信息。如下：

products表
产品一般都包含对应的供应商。如果每个产品只有一个供应商，每个供应商又只提供一种商品（即一对一的关系）。我们可以单独添加一些供应商的信息（名称、地址、电话号码）到prodcuts表中。
假设每个产品只有一个供应商，但是一个供应商可以提供0个或者多个商品（即一对多关系）。那么此时把供应商的信息添加到products表中，就会导致数据重复。这是因为一个供应商可能提供多件商品，因此，同一个供应商的信息可能会出现在products表中的多行。这不仅会导致空间的浪费，还很容易导致不一致。如果一个产品包含多个供应商，并且每个供应商同时提供多种商品，那么情况还会变得更加复杂，即多对多的关系。

一对多关系

假设每个产品只有一个供应商，并且每个供应商提供一种或多种商品。那么我们可以创建一张名为suppliers的表来存储供应商的信息（比如，名称、地址、电话等）。我们创建名为supplierID的一列来区别每一个供应商，这一列的数据是唯一的。另外，我们设置supplierID作为表suppliers的主键来保证唯一性和加快查询速度。
为了将表suppliers与表products关联起来，我们在products表中添加一个新列，名为supplierID。然后设置表products的supplierID列为外键，它引用了表suppliers中的主键列supplierID，由此来定义两个表之间的关系。

表suppliers

表products 我们首先需要创建suppliers表，因为表products需要引用表suppliers。使用以下命令来创建：

mysql> USE southwind;
Database changed
mysql> DROP TABLE IF EXISTS suppliers;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> CREATE TABLE suppliers (
    ->          supplierID  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    ->          name        VARCHAR(30)   NOT NULL DEFAULT '',
    ->          phone       CHAR(8)       NOT NULL DEFAULT '',
    ->          PRIMARY KEY (supplierID)
    ->        );
Query OK, 0 rows affected (0.10 sec)

mysql> DESC suppliers;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| supplierID | int unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(30)  | NO   |     |         |                |
| phone      | char(8)      | NO   |     |         |                |
+------------+--------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> INSERT INTO suppliers VALUE
    ->           (501, 'ABC Traders', '88881111'),
    ->           (502, 'XYZ Company', '88882222'),
    ->           (503, 'QQ Corp', '88883333');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM suppliers;
+------------+-------------+----------+
| supplierID | name        | phone    |
+------------+-------------+----------+
|        501 | ABC Traders | 88881111 |
|        502 | XYZ Company | 88882222 |
|        503 | QQ Corp     | 88883333 |
+------------+-------------+----------+
3 rows in set (0.00 sec)

然后我们使用ALERT TABLE指令来给表products添加supplierID列，并且修改products表，添加对应的supplierID数据：

mysql> DELETE FROM products;
Query OK, 5 rows affected (0.01 sec)

mysql> INSERT INTO products VALUES (2001, 'PEC', 'Pencil 3B', 500, 0.52),
    ->                             (NULL, 'PEC', 'Pencil 4B', 200, 0.62),
    ->                             (NULL, 'PEC', 'Pencil 5B', 100, 0.73),
    ->                             (NULL, 'PEC', 'Pencil 6B', 500, 0.47);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO products VALUES (2001, 'PEC', 'Pencil 3B', 500, 0.52, 501),
    -> (NULL, 'PEC', 'Pencil 4B', 200, 0.62, 501),
    -> (NULL, 'PEC', 'Pencil 5B', 100, 0.73, 501),
    -> (NULL, 'PEC', 'Pencil 6B', 500, 0.47, 502);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+------------+
| productID | productCode | name      | quantity | price | supplierID |
+-----------+-------------+-----------+----------+-------+------------+
|      2001 | PEC         | Pencil 3B |      500 |  0.52 |        501 |
|      2002 | PEC         | Pencil 4B |      200 |  0.62 |        501 |
|      2003 | PEC         | Pencil 5B |      100 |  0.73 |        501 |
|      2004 | PEC         | Pencil 6B |      500 |  0.47 |        502 |
+-----------+-------------+-----------+----------+-------+------------+
4 rows in set (0.00 sec)

接下来我们给products表的supplierID列添加一个外键约束。

mysql> ALTER TABLE products
    ->        ADD FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>  DESC products;
+-------------+--------------+------+-----+----------+----------------+
| Field       | Type         | Null | Key | Default  | Extra          |
+-------------+--------------+------+-----+----------+----------------+
| productID   | int unsigned | NO   | PRI | NULL     | auto_increment |
| productCode | char(3)      | NO   |     |          |                |
| name        | varchar(30)  | NO   |     |          |                |
| quantity    | int unsigned | NO   |     | 0        |                |
| price       | decimal(7,2) | NO   |     | 99999.99 |                |
| supplierID  | int unsigned | NO   | MUL | NULL     |                |
+-------------+--------------+------+-----+----------+----------------+
6 rows in set (0.00 sec)

JOIN

SELECT命令可以用来从两张相关的表中查询数据。举个例子，罗列产品的名称和供应商的名称，我们可以通过公共的supplierID来将两张表联合起来查询：

mysql> SELECT products.name, price, suppliers.name
    -> FROM products
    -> JOIN suppliers ON products.supplierID = suppliers.supplierID
    -> WHERE price < 0.6;
+-----------+-------+-------------+
| name      | price | name        |
+-----------+-------+-------------+
| Pencil 3B |  0.52 | ABC Traders |
| Pencil 6B |  0.47 | XYZ Company |
+-----------+-------+-------------+
2 rows in set (0.01 sec)

-- Join via WHERE clause (lagacy and not recommended)
mysql> SELECT products.name, price, suppliers.name 
       FROM products, suppliers 
       WHERE products.supplierID = suppliers.supplierID
          AND price < 0.6;
+-----------+-------+-------------+
| name      | price | name        |
+-----------+-------+-------------+
| Pencil 3B |  0.52 | ABC Traders |
| Pencil 6B |  0.47 | XYZ Company |
+-----------+-------+-------------+

涉及到多张表的时候，合理地使用别名是一个很好的选择，可以简化SQL语句的编写：

mysql> SELECT p.name AS `Product Name`, p.price, s.name AS `Supplier Name`
    ->        FROM products AS p
    ->           JOIN suppliers AS s ON p.supplierID = s.supplierID
    ->        WHERE p.price < 0.6;
+--------------+-------+---------------+
| Product Name | price | Supplier Name |
+--------------+-------+---------------+
| Pencil 3B    |  0.52 | ABC Traders   |
| Pencil 6B    |  0.47 | XYZ Company   |
+--------------+-------+---------------+
2 rows in set (0.00 sec)

数据库示意图如下，这连接代表products和suppliers之间的关系：

多对多关系

假设一个产品有多个供应商，一个供应商同时也提供多种商品，那么这就是所谓的多对多关系。那么此时就不能将supplierID插入到表products中了，因为你无法决定供应商的数量，所以对应的supplierID的列数也无法确定。同样，也无法将productID包含在suppliers表中，因为你也无法决定product的数量。
为了解决这个问题，我们需要创建一张新的表，来提供products和suppliers之间的联系，如下图：

我们先来创建出products_suppliers表。这张表的主键包含productID和supplierID两列，它们之间的组合唯一确定了一行。主键的定义就是用来保证唯一性。定义了两个外键以将约束设置到两张父表上。如下：

mysql> CREATE TABLE products_suppliers (
    ->          productID   INT UNSIGNED  NOT NULL,
    ->          supplierID  INT UNSIGNED  NOT NULL,
    ->                      -- Same data types as the parent tables
    ->          PRIMARY KEY (productID, supplierID),
    ->                      -- uniqueness
    ->          FOREIGN KEY (productID)  REFERENCES products  (productID),
    ->          FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID)
    ->        );
Query OK, 0 rows affected (0.07 sec)

mysql> DESCRIBE products_suppliers;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| productID  | int unsigned | NO   | PRI | NULL    |       |
| supplierID | int unsigned | NO   | PRI | NULL    |       |
+------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO products_suppliers VALUES (2001, 501), (2002, 501),
    ->        (2003, 501), (2004, 502), (2001, 503);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM products_suppliers;
+-----------+------------+
| productID | supplierID |
+-----------+------------+
|      2001 |        501 |
|      2002 |        501 |
|      2003 |        501 |
|      2004 |        502 |
|      2001 |        503 |
+-----------+------------+
5 rows in set (0.00 sec)

接下来，我们删除掉products表中的supplierID列，在这种情况下，我们不再需要这一列了。在删除这一列之前，我们需要先删除这一列的外键。要移除一个键，我们需要知道它的名称，这是由系统生成的。我们可以使用指令SHOW CREATE TABLE products，然后找到外键约束的名字，然后先删除掉外键，再删除对应的列。

mysql> SHOW CREATE TABLE products \G
Create Table: CREATE TABLE `products` (
  `productID`   int(10) unsigned  NOT NULL AUTO_INCREMENT,
  `productCode` char(3)           NOT NULL DEFAULT '',
  `name`        varchar(30)       NOT NULL DEFAULT '',
  `quantity`    int(10) unsigned  NOT NULL DEFAULT '0',
  `price`       decimal(7,2)      NOT NULL DEFAULT '99999.99',
  `supplierID`  int(10) unsigned   NOT NULL DEFAULT '501',
  PRIMARY KEY (`productID`),
  KEY `supplierID` (`supplierID`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`supplierID`) 
     REFERENCES `suppliers` (`supplierID`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=latin1
 
mysql> ALTER TABLE products DROP FOREIGN KEY products_ibfk_1;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE products DROP supplierID;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc products;
+-------------+--------------+------+-----+----------+----------------+
| Field       | Type         | Null | Key | Default  | Extra          |
+-------------+--------------+------+-----+----------+----------------+
| productID   | int unsigned | NO   | PRI | NULL     | auto_increment |
| productCode | char(3)      | NO   |     |          |                |
| name        | varchar(30)  | NO   |     |          |                |
| quantity    | int unsigned | NO   |     | 0        |                |
| price       | decimal(7,2) | NO   |     | 99999.99 |                |
+-------------+--------------+------+-----+----------+----------------+
5 rows in set (0.00 sec)

同样，我们可以使用SELECT和JOIN来同时从3张表中查询数据，例子如下：

mysql> SELECT products.name AS `Product Name`, price, suppliers.name AS `Supplier Name`
    ->        FROM products_suppliers
    ->           JOIN products  ON products_suppliers.productID = products.productID
    ->           JOIN suppliers ON products_suppliers.supplierID = suppliers.supplierID
    ->        WHERE price < 0.6;
+--------------+-------+---------------+
| Product Name | price | Supplier Name |
+--------------+-------+---------------+
| Pencil 3B    |  0.52 | ABC Traders   |
| Pencil 3B    |  0.52 | QQ Corp       |
| Pencil 6B    |  0.47 | XYZ Company   |
+--------------+-------+---------------+
3 rows in set (0.00 sec)

mysql> SELECT p.name AS `Product Name`, s.name AS `Supplier Name`
       FROM products_suppliers AS ps 
          JOIN products AS p ON ps.productID = p.productID
          JOIN suppliers AS s ON ps.supplierID = s.supplierID
       WHERE p.name = 'Pencil 3B';
+--------------+---------------+
| Product Name | Supplier Name |
+--------------+---------------+
| Pencil 3B    | ABC Traders   |
| Pencil 3B    | QQ Corp       |
+--------------+---------------+

mysql> SELECT p.name AS `Product Name`, s.name AS `Supplier Name`
       FROM products AS p, products_suppliers AS ps, suppliers AS s
       WHERE p.productID = ps.productID
          AND ps.supplierID = s.supplierID
          AND s.name = 'ABC Traders';
+--------------+---------------+
| Product Name | Supplier Name |
+--------------+---------------+
| Pencil 3B    | ABC Traders   |
| Pencil 4B    | ABC Traders   |
| Pencil 5B    | ABC Traders   |
+--------------+---------------+

表之间的关系如下图，通过联结表支持多对多关系。

一对一关系

假设有一些产品有可选的数据，比如图片、评论等，不要把这些可选数据也放在products表中，更加高效的方式是创建一个单独的表，然后将它与products表联结起来。

例子：

mysql> CREATE TABLE product_details (
          productID  INT UNSIGNED   NOT NULL,
                     -- same data type as the parent table
          comment    TEXT  NULL,
                     -- up to 64KB
          PRIMARY KEY (productID),
          FOREIGN KEY (productID) REFERENCES products (productID)
       );

mysql> DESCRIBE product_details;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| productID | int(10) unsigned | NO   | PRI | NULL    |       |
| comment   | text             | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+

mysql> SHOW CREATE TABLE product_details \G
*************************** 1. row ***************************
       Table: product_details
Create Table: CREATE TABLE `product_details` (
  `productID`  int(10) unsigned  NOT NULL,
  `comment`    text,
  PRIMARY KEY (`productID`),
  CONSTRAINT `product_details_ibfk_1` FOREIGN KEY (`productID`) REFERENCES `products` (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

子查询

一个查询的结果可以被用在另外一个SQL语句中。当需要查询超过一张表的时候，子查询是很有用的。
比如我们需要找到名字为QQ开头的供应商都提供哪些商品：

mysql> SELECT ps.productID from products_suppliers ps  WHERE ps.supplierID IN (SELECT s.supplierID from suppliers s WHERE s.name LIKE 'QQ%');
+-----------+
| productID |
+-----------+
|      2001 |
+-----------+
1 row in set (0.00 sec)

参考

MySQL必知必会
https://www3.ntu.edu.sg/home/ehchua/programming/sql/MySQL_Beginner.html

MySQL操作汇总（多表查询）
简介在上一篇文章MySQL操作汇总（单表创建查询）[https://www.jianshu.com/p/7629...
2018-03-20
MYSQL查询语句 MYSQL复杂操作语句 MYSQL多表查询方法函数部分
MySQL 多表操作
day07-多表操作今日任务完成对MYSQL数据库的多表查询及建表的操作教学目标掌握MYSQL中多表的创建...
mysql数据库-多表查询
今日任务完成对MYSQL数据库的多表查询及建表的操作教学目标掌握MYSQL中多表的创建及多表的查询掌握MY...
SQLAlchemy（四）
知识要点： 1.多表查询 2.原生SQL的查询多表查询在MySQL中我们讲了多表查询，在SQLAlchemy中...
深入浅出MySQL(五)
多表查询 MySQL中的多表联查 MySQL中多表查询分为三种形式：笛卡尔积的形式内连接的形式外连接的形式 ...
Mysql-多表查询as索引
1、Mysql多表查询2、information_schema 虚拟库3、索引 1、多表查询方法(1) 根据需求...
MySQL基础——事务
上篇文章学习了MySQL基础——多表查询，这篇文章学习MySQL基础——事务。事务事务是一组操作的集合，它是一...
4/30day45_MySql多表
回顾 MySQL多表查询&权限今日目标一多表查询【重点...】同时查询多张表获取到需要的数据组成完整的信息...
MySQL学习笔记二之单表查询与多表查询
title: MySQL学习笔记二之单表查询与多表查询tags: MySQL 数据库categories: MyS...