MySQL子查询和联结表笔记

子查询

MySQL4.1引入了对子查询的支持，所以必须使用MySQL4.1或更高的版本

一、嵌套子查询

   1、子查询表示， 一个SELECT语句嵌套在另一个SELECT语句中

     1.1语法：

      select <Column Name> form <table>  -----父查询

      where <Column Name> <操作符>  ------ 操作符 in = != >  <   >=  <=

      select <Column Name> form <table> where <Column Name> = <内容>


    2.2 案例及分析：

     我的数据库crashcourse ，订单存储在两个表中，

     orders（订单表）表

            存储了订单号、客户ID、订单日期

                  order_num  cust_id   order_date

               订单表与客户表的关系表示为：一个客户可以有多个订单，是一对多的关系。

               所以在该表的设计是客户ID作为了订单表的外键，因为订单号是主键，且唯一。

               表记录的是一个订单对应一个客户

 
     orderitems（订单详情表）表  

             存储了订单号、订单详情号、物品编号、数量、价格

                    order_num order_item  prod_id  quantity  item_price

                该表的设计是一个双主键设计，即订单号、订单详情号 都是主键，外品编号是外键。

                表示:以订单详情号和订单号共同来确保记录唯一，而不是单单的以订单详情号或订单号

                因为，一个详情内可以有多个订单，一对多的关系，一个订单又有多个物品，

                那么它们对应的关系就是 （物品编号ANV01、ANV02同属于一个订单号20005，但它是两个不同的详情）

                记录： 订单详情号   订单号   物品编号

                          1          20005    ANV01
                                
                          1          20006    JP2000
 
                          2          20005    ANV02

                
      需求1.：列出订购物品TNT2的所有客户信息

              步骤：1.查询包含物品TNT2的所有订单的编号

                    select order_num from orderitems where prod_id='TNT2'

                    结果：order_num

                     20005

                     20007

                   2.根据上一步列出订单号20005、20007对应的客户ID

                     select cust_id from orders where order_num in (20005,20007)

                    结果：cust_id

                    10001

                    10004

                  3.上面两步是两个单独的查询，现在我们把第一步的查询（返回订单号的那个） 变为子查询组合两个查询

                  select cust_id from orders where order_num in (select order_num from orderitems where prod_id='TNT2')


                   分析:
           
                       子查询总是从内向外处理，在处理上面的SELECT语句时，MySQL实际上执行了两个操作。

                       1.它执行步骤1的查询：

                         返回两个订单号：20005和20007 然后，这两个值以IN操作符要
                           
                         求的逗号分隔的格式传递给外部查询的WHERE子句。

                       2.外部查询变成步骤2的查询：
             
                         可以看到，输出是正确的并且与前面硬编码WHERE子句所返回的值相同。


                 4.以上步骤得到了订购物品编号TNT2的所有客户ID(10001,10004)后，现在要得到这些客户的信息

                     select * from customers WHERE cust_id in (10001,10004)

                     将其中的where 条件变为子查询而不是硬编码

                    

           select * from customers WHERE cust_id in (  select cust_id                                                                                                                                    from orders                                                                  where order_num in (select order_num                                                                  from orderitems                                                                  where prod_id='TNT2'))

                
                 分析：  最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句。

                         外面的子查询返回客户ID列表，此客户ID列表用于最外层查询的WHERE子句。

                         最外层查询确实返回所需的数据。

     
               建议：我们写子查询，较为复杂时，为了便于阅读，把子查询分解为多行并且适当的进行缩进。


  二、子查询做为计算字段

      1.需求：

            需要显示customers（客户表）表中每个客户的订单总数。

            我们的订单与相应的客户ID是存储在orders表中，为了执行这个操作，要完成下面步骤：


         步骤：

             1. 从customers表中检索客户列表。

                select customers.cust_id,cust_name,cust_address,cust_email from customers ORDER BY customers.cust_id asc
             
             2. 对于检索出的每个客户，统计其在orders表中的订单数目。

                select count(1) from orders where cust_id = 10001

                为了对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查询

               

           select customers.cust_id,cust_name,cust_address,cust_email,( select count(1)                                                    from orders                                                    where orders.cust_id = customers.cust_id ) as ordersCount                from customers ORDER BY customers.cust_id asc

         分析：

              ordersCount 是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。

              注意：子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名，

              所谓完全限定名，我理解的是，两个表中有相同字段，当在where里进行匹配，要使用其表名（或表的别名）.其字段

              以示区分，如果不使用完全限定列名，MySQL将假定你是对orders表中的cust_id进行自身比较，其统计在以上需求中

              统计结果就不对。

     

  联结表

        一、为什么要使用联结

   如果数据存储在多个表中，怎样用单条SELECT语句查询出数据?
   
   那么使用联结，简单地说，联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。

   使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

   包含：内联结、外联结（左、右）、自联结


   1.创建联结

    

    select vend_name,prod_name,prod_price     from vendors,products     where vendors.vend_id = products.vend_id     order by vend_name,prod_name

     
     笛卡尔积：由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行的数目

               将是第一个表中的行数乘以第二个表中的行数

        select vend_name,prod_name,prod_price
   
        from vendors,products

               这不是我们想要的结果，这个是告诉你当没有联结条件的表关系就会如此，就是笛卡尔积。


     1.1 内部联结 inner join

         基于两个表之间的相等测试，这种联结也称为内部联结

          

           select vend_name,prod_name,prod_price           from vendors inner join products           on vendors.vend_id = products.vend_id

           分析：

              1.这里，两个表之间的关系是FROM子句的组成部分，以INNER  JOIN指定。

              2.在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。

              3.传递给ON的实际条件与传递给WHERE的相同。


           这种查询方式，查询的结果是两个表中有关联的数据，记录数相同。

    
      1.2 外联结

         许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联到行的那些行

          我们用一个内部联结  和  一个外部联结 来分别看一下查询结果

          内部联结的写法：  

          

           select customers.cust_id,orders.order_num           from customers inner join orders           on customers.cust_id = orders.cust_id

           结果：

                cust_id    order_num

                10001      20005
                
                10001      20009

                10003      20006

                10004      20007

                10005      20008

           
           左外联结写法：（LEFT OUTER JOIN）

           有左外联结，就有右外联结，至于是用左还是右，根据你要以哪个为主要显示的表

          

           select customers.cust_id,orders.order_num           from customers LEFT OUTER JOIN  orders           on customers.cust_id = orders.cust_id

           结果：
               
               
                cust_id    order_num

                10001      20005
                
                10001      20009

                10002      null

                10003      20006

                10004      20007

                10005      20008
               

         分析：

             1.与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。

             2.在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字
               
              指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。
      
     1.3 自联结

          需求：

              假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。

              此查询要求

               1.首先找到生产ID为DTNTR的物品的供应商

               2.然后找出这个供应商生产的其他物品。

               子查询的写法：

                  

           select prod_id,prod_name                   from products                   where vend_id = (select vend_id                                    from products                                    where prod_id = 'DTNTR'                                    )

              自联结的写法：

               

                  select p1.prod_id,p1.prod_name                   from products as p1,products as p2,                   where p1.vend_id = p2.vend_id                     and p2.prod_id = 'DTNTR'

                                   
              注意：列名引用要指定其表别名或者其表名

         

     用自联结而不用子查询，虽然最终的结果是相同的，但有时候处理联结远比处理子查询快很多
           
 

二、组合查询 (UNION)

     有两种基本情况，需要使用组合查询

     1）.在单个查询中从不同的表返回类似结构的数据；

     2）.对单个表执行多个查询，按单个查询返回数据；


    1.需求：

           需要价格小于等于5的所有物品的一个列表，

           而且还想包括供应商1001和1002生产的所有物品（不考虑价格），这里使用UNION完成


         

         1.select vend_id,prod_id,prod_price from products            where prod_price <=5          2.select vend_id,prod_id,prod_price from products            where vend_id in (1001,1002)

      2.分析:

             第一条select查询价格不高于5的所有物品

             第二条select使用in找出供应商1001和1002生产的所有物品

             组合这两条语句：

             

              select vend_id,prod_id,prod_price from products              where prod_price <=5              union              select vend_id,prod_id,prod_price from products              where vend_id in (1001,1002)

              这条语句由前面的两条SELECT语句组成，语句中用UNION关键字分隔。

              UNION指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集。


           where语句的参考写法

             select vend_id,prod_id,prod_price from products

              where prod_price <=5  or vend_id in (1001,1002)
            

         使用UNION可能比使用WHERE子句更为复杂,但对于更复杂的过滤条件，

         或者从多个表（而不是单个表）中检索数据的情形，使用UNION可能会使处理更简单。


    3.UNION规则：

           1.必须由两条或两条以上的select语句组成,语句之间用关键字UNION分隔

           2.UNION中每个查询必须包含相同的列、表达式或聚集函数

           3.列数据类型必须兼容


    4.包含或取消重复的行

      使用UNION ALL，MySQL不取消重复的行。