《mysql必知必会》学习_第16章

第16章：创建高级联结。

P106

select concat(RTrim(vend_name),'(',RTrim(vend_country),')') as vend_title from vendors order by vend_name;   #concat()函数合并两列，并重新命名合并的列为vend_name。#

 select cust_name,cust_contact from customers as c,orders as o,orderitems as oi where c.cust_id =0.cust_id and oi.order_num = o.order_num and prod_id ='tnt2'  # from之后重新命名了表customers为c ，命名表orders为0，命名orderitems为oi ，在where语句后面使用新命名的表名，这就减少了表名太长和需要多次键入表名的麻烦#

 使用不同类型的联结，联结分为：自联结（A和A自身联结），／／自然联结，外部联结 （不理解）／

P107 自联结

select prod_id,prod_name from products where vend_id =(select vend_id from products where prod_id ='dtntr') ; #满足 条件表products中prod_id=dtnr所对应的vend_id ，这些vend_id在products中所对应的prod_id,prod_name #  ##只是在表products里面兜，其实相当先检索vend_id=1003,vend_id=1003时，prod_id，prod_name有下面图一那些。其实就是图二的转换顺序红色--绿色--黄色（红色箭头方向不正确）##

 

 P108

select p1.prod_id,p1.prod_name from products as p1 ,products as p2 where p1.vend_id =p2.vend_id and p2.vend_id ='dtntr' ; #where之后的条件其实是vend_id=1003，和上面的语句表达的意思一样#

P109 自然联结 #多个表联结，表的每个列只返回一次，不像自联结在同一个表的列不断的兜来兜去（我理解的）#

select c.* ,o.order_num,o.order_date ,oi.prod_id ,oi.quantity ,oi.item_price from customers as c,orders as o,orderitems as oi where c.cust_id = o.cust_id and oi.order_num =o.order_num and prod_id ='fb';  #主要是仔细看好重新命名的名称，这个语句逻辑关系很清晰#

P109 外部联结

P110 内部联结：select customers.cust_id ,orders.order_num from customers inner join orders on customers.cust_id =orders.cust_id ;

 

P 110外部联结：select customers.cust_id ,orders.order_num from customers left outer join orders on customers.cust_id =orders.cust_id ; #outer join指定了联结类型为外部联结，left表示 outer join 左边的表，由左边的表去对应右边的表，比如上面的语句,customer.cust_id存在等于10002，但是orders.cust_id不存在10002，所以显示order_num=null ,但是如果是右联结，就不会存在cust_Id=10002,因为orders.cust_id不存在10002#

 

P111 select customers.cust_name ,customers.order_id,count(orders.order_num) as num_ord from customers innter join orders on customers.cust_id =orders.cust_id group by customers.cust_id ;  # 条件：customers.cust_id =orders.cust_id group by customers.cust_id  ，其他的对应上就是了#

 

  select customers.cust_name ,customers.order_id,count(orders.order_num) as num_ord from customers left outer join orders on customers.cust_id =orders.cust_id group by customers.cust_id ;  # 条件：customers left outer join orders，左边的表对应右边的表，左边的表里面的存在cust_id=10002，所以得到的cust_id里面有cust_id=10002;#