SQL自学，mysql从入门到精通 --- 第 7 天，表的联合

Mr.L-OAM

已于 2023-09-12 16:00:26 修改

阅读量76

点赞数

分类专栏： SQL 文章标签： sql mysql 数据库

于 2023-09-07 17:13:37 首次发布

本文链接：https://blog.csdn.net/weixin_46153337/article/details/132742281

版权

SQL 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

表的联合

在一个SELECT 语句中使用多个表

-- 将table1和table2的每一行都接合了起来
root@mysqldb 15:22:  [d1]> SELECT * FROM project, project2;
+------+---------------------+---------------------+------+------------+------------+
| task | StartTime           | endtime             | task | StartTime  | endtime    |
+------+---------------------+---------------------+------+------------+------------+
| AA   | 2023-07-15 10:00:00 | 2023-08-01 00:00:00 | AA   | 2023-07-15 | 2023-08-01 |
| BB   | 2023-07-20 10:00:00 | 2023-08-02 10:00:00 | AA   | 2023-07-15 | 2023-08-01 |
| CC   | 2023-08-16 00:00:00 | 2023-08-25 00:00:00 | AA   | 2023-07-15 | 2023-08-01 |
| DD   | 2023-09-02 00:00:00 | 2023-09-06 00:00:00 | AA   | 2023-07-15 | 2023-08-01 |
| EE   | 2023-09-01 10:00:00 | 2023-09-05 00:00:00 | AA   | 2023-07-15 | 2023-08-01 |
| AA   | 2023-07-15 10:00:00 | 2023-08-01 00:00:00 | BB   | 2023-07-20 | 2023-08-02 |
| BB   | 2023-07-20 10:00:00 | 2023-08-02 10:00:00 | BB   | 2023-07-20 | 2023-08-02 |
| CC   | 2023-08-16 00:00:00 | 2023-08-25 00:00:00 | BB   | 2023-07-20 | 2023-08-02 |
| DD   | 2023-09-02 00:00:00 | 2023-09-06 00:00:00 | BB   | 2023-07-20 | 2023-08-02 |
| EE   | 2023-09-01 10:00:00 | 2023-09-05 00:00:00 | BB   | 2023-07-20 | 2023-08-02 |
| AA   | 2023-07-15 10:00:00 | 2023-08-01 00:00:00 | CC   | 2023-08-16 | 2023-08-25 |
| BB   | 2023-07-20 10:00:00 | 2023-08-02 10:00:00 | CC   | 2023-08-16 | 2023-08-25 |
| CC   | 2023-08-16 00:00:00 | 2023-08-25 00:00:00 | CC   | 2023-08-16 | 2023-08-25 |
| DD   | 2023-09-02 00:00:00 | 2023-09-06 00:00:00 | CC   | 2023-08-16 | 2023-08-25 |
| EE   | 2023-09-01 10:00:00 | 2023-09-05 00:00:00 | CC   | 2023-08-16 | 2023-08-25 |
| AA   | 2023-07-15 10:00:00 | 2023-08-01 00:00:00 | DD   | 2023-09-02 | 2023-09-06 |
| BB   | 2023-07-20 10:00:00 | 2023-08-02 10:00:00 | DD   | 2023-09-02 | 2023-09-06 |
| CC   | 2023-08-16 00:00:00 | 2023-08-25 00:00:00 | DD   | 2023-09-02 | 2023-09-06 |
| DD   | 2023-09-02 00:00:00 | 2023-09-06 00:00:00 | DD   | 2023-09-02 | 2023-09-06 |
| EE   | 2023-09-01 10:00:00 | 2023-09-05 00:00:00 | DD   | 2023-09-02 | 2023-09-06 |
| AA   | 2023-07-15 10:00:00 | 2023-08-01 00:00:00 | EE   | 2023-09-01 | 2023-09-05 |
| BB   | 2023-07-20 10:00:00 | 2023-08-02 10:00:00 | EE   | 2023-09-01 | 2023-09-05 |
| CC   | 2023-08-16 00:00:00 | 2023-08-25 00:00:00 | EE   | 2023-09-01 | 2023-09-05 |
| DD   | 2023-09-02 00:00:00 | 2023-09-06 00:00:00 | EE   | 2023-09-01 | 2023-09-05 |
| EE   | 2023-09-01 10:00:00 | 2023-09-05 00:00:00 | EE   | 2023-09-01 | 2023-09-05 |
+------+---------------------+---------------------+------+------------+------------+
25 rows in set (0.00 sec)

下面创建三张表，客户表，产品表，订单表，并写入数据，如下：

-- 客户表
root@mysqldb 16:17:  [d1]> CREATE TABLE Customers (  
    -> customer_id INT PRIMARY KEY,  
    -> customer_name VARCHAR(50),  
    -> customer_email VARCHAR(50)  
    -> ); 
Query OK, 0 rows affected (0.01 sec)

root@mysqldb 16:52:  [d1]> INSERT INTO Customers (customer_id, customer_name, customer_email)  
    -> VALUES  
    -> (1, 'John Doe', 'john.doe@example.com'),  
    -> (2, 'Jane Smith', 'jane.smith@example.com'),  
    -> (3, 'David Johnson', 'david.johnson@example.com');  
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- 产品价格表
root@mysqldb 16:52:  [d1]> CREATE TABLE Products (  
    -> product_id INT PRIMARY KEY,  
    -> product_name VARCHAR(50),  
    -> price DECIMAL(10, 2)  
    -> ); 
Query OK, 0 rows affected (0.01 sec)

root@mysqldb 16:53:  [d1]> INSERT INTO Products (product_id, product_name, price)  
    -> VALUES  
    -> (1, 'Product A', 19.99),  
    -> (2, 'Product B', 29.99),  
    -> (3, 'Product C', 39.99);  
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0


-- 订单表
root@mysqldb 16:54:  [d1]> CREATE TABLE Orders (  
    -> order_id INT PRIMARY KEY,  
    -> customer_id INT,  
    -> product_id INT,  
    -> quantity INT,  
    -> FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),  
    -> FOREIGN KEY (product_id) REFERENCES Products(product_id)  
    -> );
Query OK, 0 rows affected (0.02 sec)


root@mysqldb 16:54:  [d1]> INSERT INTO Orders (order_id, customer_id, product_id, quantity)  
    -> VALUES  
    -> (1, 1, 1, 2),  
    -> (2, 2, 2, 1),  
    -> (3, 3, 3, 3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

LEFT JOIN 左联合

root@mysqldb 08:58:  [d1]> SELECT c.customer_name, p.product_name, o.quantity  
    -> FROM Customers  c
    -> LEFT JOIN Orders o ON c.customer_id = o.customer_id  
    -> LEFT JOIN Products p ON o.product_id = p.product_id;
+---------------+--------------+----------+
| customer_name | product_name | quantity |
+---------------+--------------+----------+
| John Doe      | Product A    |        2 |
| Jane Smith    | Product B    |        1 |
| David Johnson | Product C    |        3 |
+---------------+--------------+----------+
3 rows in set (0.17 sec)

RIGHT JOIN 右联合

root@mysqldb 17:05:  [d1]> SELECT c.customer_name, p.product_name, o.quantity  
    -> FROM Customers  c
    -> RIGHT JOIN Orders o ON c.customer_id = o.customer_id  
    -> RIGHT JOIN Products p ON o.product_id = p.product_id;
+---------------+--------------+----------+
| customer_name | product_name | quantity |
+---------------+--------------+----------+
| John Doe      | Product A    |        2 |
| Jane Smith    | Product B    |        1 |
| David Johnson | Product C    |        3 |
+---------------+--------------+----------+
3 rows in set (0.00 sec)

等值联合

root@mysqldb 09:00:  [d1]> SELECT c.customer_name, p.product_name, o.quantity  
    -> FROM Customers  c
    -> JOIN Orders o ON c.customer_id = o.customer_id  
    -> JOIN Products p ON o.product_id = p.product_id;
+---------------+--------------+----------+
| customer_name | product_name | quantity |
+---------------+--------------+----------+
| John Doe      | Product A    |        2 |
| Jane Smith    | Product B    |        1 |
| David Johnson | Product C    |        3 |
+---------------+--------------+----------+
3 rows in set (0.00 sec)

不等值联合

root@mysqldb 17:02:  [d1]> SELECT c.customer_name, p.product_name, o.quantity  
    -> FROM Customers  c
    -> JOIN Orders o ON c.customer_id = o.customer_id  
    -> JOIN Products p ON o.product_id = p.product_id  
    -> WHERE o.quantity >= 2;
+---------------+--------------+----------+
| customer_name | product_name | quantity |
+---------------+--------------+----------+
| John Doe      | Product A    |        2 |
| David Johnson | Product C    |        3 |
+---------------+--------------+----------+
2 rows in set (0.00 sec)