读书笔记--SQL必知必会11--使用子查询

来源：互联网发布：yy挂机软件免费下载编辑：程序博客网时间：2024/05/22 04:55

11.1 子查询

查询（query），任何SQL语句都是查询。但此术语一般指SELECT语句。
SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。
作为子查询的SELECT语句只能查询单个列，否则会返回错误。

11.2 利用子查询进行过滤

在SELECT语句中，子查询总是从内向外处理。

MariaDB [sqlbzbh]> SELECT * FROM OrderItems;+-----------+------------+---------+----------+------------+| order_num | order_item | prod_id | quantity | item_price |+-----------+------------+---------+----------+------------+|     20005 |          1 | BR01    |      100 |       5.49 ||     20005 |          2 | BR03    |      100 |      10.99 ||     20006 |          1 | BR01    |       20 |       5.99 ||     20006 |          2 | BR02    |       10 |       8.99 ||     20006 |          3 | BR03    |       10 |      11.99 ||     20007 |          1 | BR03    |       50 |      11.49 ||     20007 |          2 | BNBG01  |      100 |       2.99 ||     20007 |          3 | BNBG02  |      100 |       2.99 ||     20007 |          4 | BNBG03  |      100 |       2.99 ||     20007 |          5 | RGAN01  |       50 |       4.49 ||     20008 |          1 | RGAN01  |        5 |       4.99 ||     20008 |          2 | BR03    |        5 |      11.99 ||     20008 |          3 | BNBG01  |       10 |       3.49 ||     20008 |          4 | BNBG02  |       10 |       3.49 ||     20008 |          5 | BNBG03  |       10 |       3.49 ||     20009 |          1 | BNBG01  |      250 |       2.49 ||     20009 |          2 | BNBG02  |      250 |       2.49 ||     20009 |          3 | BNBG03  |      250 |       2.49 |+-----------+------------+---------+----------+------------+18 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT * FROM Orders;+-----------+---------------------+------------+| order_num | order_date          | cust_id    |+-----------+---------------------+------------+|     20005 | 2012-05-01 00:00:00 | 1000000001 ||     20006 | 2012-01-12 00:00:00 | 1000000003 ||     20007 | 2012-01-30 00:00:00 | 1000000004 ||     20008 | 2012-02-03 00:00:00 | 1000000005 ||     20009 | 2012-02-08 00:00:00 | 1000000001 |+-----------+---------------------+------------+5 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01';+-----------+| order_num |+-----------+|     20007 ||     20008 |+-----------+2 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT cust_id FROM Orders WHERE order_num IN (20007,20008);+------------+| cust_id    |+------------+| 1000000004 || 1000000005 |+------------+2 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01');+------------+| cust_id    |+------------+| 1000000004 || 1000000005 |+------------+2 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT * FROM Customers;+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com || 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green     | NULL                  || 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones          | jjones@fun4all.com    || 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com || 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard         | NULL                  |+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+5 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT cust_name,cust_contact FROM Customers WHERE cust_id IN ('1000000004','1000000005');+---------------+--------------------+| cust_name     | cust_contact       |+---------------+--------------------+| Fun4All       | Denise L. Stephens || The Toy Store | Kim Howard         |+---------------+--------------------+2 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT cust_name,cust_contact FROM Customers WHERE cust_id IN (SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'));+---------------+--------------------+| cust_name     | cust_contact       |+---------------+--------------------+| Fun4All       | Denise L. Stephens || The Toy Store | Kim Howard         |+---------------+--------------------+2 rows in set (0.00 sec)MariaDB [sqlbzbh]>

11.3 作为计算字段使用子查询

在这种构造中，必须严格限制有歧义的列。可通过完全限定列名的方法实现。
完全限定列名，就是同时指定表名和列名，并用一个句点分隔。
推荐的方式：如果SELECT语句中操作多个表，使用完全限定列名来避免歧义。

MariaDB [sqlbzbh]> SELECT cust_name, cust_state, (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers ORDER BY cust_name;+---------------+------------+--------+| cust_name     | cust_state | orders |+---------------+------------+--------+| Fun4All       | IN         |      1 || Fun4All       | AZ         |      1 || Kids Place    | OH         |      0 || The Toy Store | IL         |      1 || Village Toys  | MI         |      2 |+---------------+------------+--------+5 rows in set (0.00 sec)MariaDB [sqlbzbh]>

阅读全文

0 0