首页 > 代码库 > SQL 查询基础(2)-实例

SQL 查询基础(2)-实例

2024-07-02 02:15:18   228人阅读

上次说到SQL查询语句的逻辑执行过程，现在来用一个实例说明一下逻辑执行的过程。

前提：我们有三个表，分别记住客户信息、订单信息和产品信息

客户信息表：Customer

ID，Name，Adress，PhoneNumber

ID Name Adress PhoneNumber
1 CompanyA No.1 Street 123456
2 CompanyB No.2 Street 23453
3 CompanyC No.3 Street 45321
4 CompanyD No.4 Street 4567890
5 CompanyE No.5 Street 123890

订单信息表：OrderInfo 

ID，Cus_ID，Product_ID，OrderDate，Qua

ID Cus_ID Product_ID OrderDate Qua
1 2 2 1/1/2012 1000
2 4 1 5/2/2012 500
3 1 5 9/10/2013 3069

产品信息表：Product

ID，Name，Price

ID Name Price
1 ABC 10
2 DEF 16
3 GHI 18
4 JKL 9
5 MNO 100
 1 SELECT 
 2        C.Name AS CustomerName, 
 3        O.OrderDate AS OrderDate, 
 4        O.Qua,
 5        P.Name AS ProductName,
 6        P.Price AS Price
 7   FROM Customer C
 8        INNER JOIN OrderInfo O
 9             ON C.ID = O.Cus_ID
10       INNER JOIN Product P
11           ON O.Product_ID = P.ID

现在我们根据上一篇文章中的逻辑执行顺序分析一下查询的结果。

1) FROM Customer 表作为主表 T1

2) JOIN OrderInfo 表作为表 T2，这时将 T1和T2做笛卡尔乘积。得到的虚拟表如下：

ID Name Adress PhoneNumber ID Cus_ID Product_ID OrderDate Qua
1 CompanyA No.1 Street 123456 1 2 2 1/1/2012 1000
2 CompanyB No.2 Street 23453 1 2 2 1/1/2012 1000
3 CompanyC No.3 Street 45321 1 2 2 1/1/2012 1000
4 CompanyD No.4 Street 4567890 1 2 2 1/1/2012 1000
5 CompanyE No.5 Street 123890 1 2 2 1/1/2012 1000
1 CompanyA No.1 Street 123456 2 4 1 5/2/2012 500
2 CompanyB No.2 Street 23453 2 4 1 5/2/2012 500
3 CompanyC No.3 Street 45321 2 4 1 5/2/2012 500
4 CompanyD No.4 Street 4567890 2 4 1 5/2/2012 500
5 CompanyE No.5 Street 123890 2 4 1 5/2/2012 500
1 CompanyA No.1 Street 123456 3 1 5 9/10/2013 3069
2 CompanyB No.2 Street 23453 3 1 5 9/10/2013 3069
3 CompanyC No.3 Street 45321 3 1 5 9/10/2013 3069
4 CompanyD No.4 Street 4567890 3 1 5 9/10/2013 3069
5 CompanyE No.5 Street 123890 3 1 5 9/10/2013 3069

 执行 ON 后边的条件：C.ID = O.Cus_ID，保留结果为 TRUE 的记录，结果如下：

ID Name Adress PhoneNumber ID Cus_ID Product_ID OrderDate Qua Result
1 CompanyA No.1 Street 123456 1 2 2 1/1/2012 1000 FALSE
2 CompanyB No.2 Street 23453 1 2 2 1/1/2012 1000 TRUE
3 CompanyC No.3 Street 45321 1 2 2 1/1/2012 1000 FALSE
4 CompanyD No.4 Street 4567890 1 2 2 1/1/2012 1000 FALSE
5 CompanyE No.5 Street 123890 1 2 2 1/1/2012 1000 FALSE
1 CompanyA No.1 Street 123456 2 4 1 5/2/2012 500 FALSE
2 CompanyB No.2 Street 23453 2 4 1 5/2/2012 500 FALSE
3 CompanyC No.3 Street 45321 2 4 1 5/2/2012 500 FALSE
4 CompanyD No.4 Street 4567890 2 4 1 5/2/2012 500 TRUE
5 CompanyE No.5 Street 123890 2 4 1 5/2/2012 500 FALSE
1 CompanyA No.1 Street 123456 3 1 5 9/10/2013 3069 TRUE
2 CompanyB No.2 Street 23453 3 1 5 9/10/2013 3069 FALSE
3 CompanyC No.3 Street 45321 3 1 5 9/10/2013 3069 FALSE
4 CompanyD No.4 Street 4567890 3 1 5 9/10/2013 3069 FALSE
5 CompanyE No.5 Street 123890 3 1 5 9/10/2013 3069 FALSE

由于用的是 INNER JOIN,所以不考虑 T1(T2)中存在而T2(T1)中不存在的情况，继续执行笛卡尔乘积和 ON 条件筛选，得到结果如下表：

ID Name Adress PhoneNumber ID Cus_ID Product_ID OrderDate Qua ID Name Price
4 CompanyD No.4 Street 4567890 2 4 1 5/2/2012 500 1 ABC 10
2 CompanyB No.2 Street 23453 1 2 2 1/1/2012 1000 2 DEF 16
1 CompanyA No.1 Street 123456 3 1 5 9/10/2013 3069 5 MNO 100

由于没有用到 WHERE 条件，没有 GROUP BY，没有 HAVING 之类的操作。直接执行 SELECT 中需要选中的列，返回数据查询结果。

CustomerName OrderDate Qua ProductName Price
CompanyD 5/2/2012 500 ABC 10
CompanyB 1/1/2012 1000 DEF 16
CompanyA 9/10/2013 3069 MNO 100

至此，SQL 的逻辑执行过程处理完毕。


联系
我们