第十三章 分组数据

use lianxi
show tables

创建分组

select vend_id,count(*) as num_prods from products group by vend_id;
select vend_id,prod_price,count(*) as num_prods from products group by vend_id,prod_price;  #可以对分组进行嵌套，先按照供应商再按照价格分组计数#

select vend_id,count(*) as num_prods from products group by vend_id with rollup;
select vend_id,prod_price,count(*) as num_prods from products group by vend_id,prod_price with rollup; 

过滤分组

select cust_id,count(*) as orders from orders group by cust_id having count(*)>=2;
#where在数据分组前进行过滤，having在数据分组后进行过滤#
select vend_id ,count(*) as num_price from products where prod_price>=10 group by vend_id having count(*)>=2;
select vend_id,count(*) as num_price from products group by vend_id having count(*)>=2;

分组和排序

select order_num,sum(quantity*item_price) as price_total from orderitems group by order_num having sum(quantity*item_price)>=50;
select order_num,sum(quantity*item_price) as price_total from orderitems group by order_num having sum(quantity*item_price)>=50 order by price_total;
#select 子句及其顺序 select from where groupby having orderby limit#

第十四章 使用子查询

select order_num 
   from orderitems 
   where prod_id="TNT2";
select cust_id 
   from orders 
   where order_num in(
                      select order_num 
                      from orderitems 
                      where prod_id="TNT2");
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="TNT2"));

作为计算字段使用子查询

select count(*) as orders 
   from orders
   where cust_id=10001;   #对10001的客户进行订单计数# 
select cust_name,cust_state,(select count(*)  
                                from orders
                                where orders.cust_id=customers.cust_id) as orders
from customers
order by cust_name;

第十五章 联结表

创建联结

use lianxi
select vend_name,prod_name,prod_price 
from vendors,products
where vendors.vend_id=products.vend_id  #这里的where子句将vendors表中的vend_id与products表中的vend_id作为联结条件，对两个表中的每一行进行过滤匹配#
order by vend_name,prod_name;
#--where子句的重要性--#
select vend_name,prod_name,prod_price 
from vendors,products
                            #这里没有where子句的话，第一个表中的每个行将会对第二个表中的每个行进行配对，而不管他们逻辑上是否匹配#
order by vend_name,prod_name;

内部联结 join on

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

联结多个表(显示编号为20005的订单中的物品)

select prod_name,vend_name,prod_price,quantity
 from orderitems,products,vendors
 where products.vend_id=vendors.vend_id
 and products.prod_id=orderitems.prod_id
 and order_num=20005;

(显示订购产品TNT2的客户列表)

 select cust_name,CUST_CITY,cust_contact,cust_email,prod_id
 from customers,orders,orderitems
 where customers.cust_id=orders.cust_id
 and orderitems.order_num=orders.order_num
 and prod_ID="TNT2";

第十六章 创建高级联结

给列起别名

 select concat(rtrim(vend_name),"(",rtrim(vend_country),")") as vend_title
 from vendors order by vend_name;

给表起别名

 select cust_name,cust_contact
 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="tnt2"

使用不同类型的联结——自联结、自然联结、外部联结

#——自联结（找出生产物品DTNTR的供应商生产的其他物品）——#
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"
#——自然联结（排除多次出现，使每个列只返回一次）——#
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";
#——外部联结（将一个表中的行与另一个表中的行相关联）#
select customers.cust_id,orders.order_num
from customers left outer join orders     #left指出的是从outer join的左边表中选择所有行#
on customers.cust_id=orders.cust_id

select customers.cust_id,orders.order_num
from customers right outer join orders    #right指出的是从outer join的右边表中选择所有行#
on customers.cust_id=orders.cust_id

使用带聚集函数的联结

#——检索所有客户及每个客户所下的订单数——#
select customers.cust_name,
   customers.cust_id,
   count(orders.order_num) as num_ord
from customers inner join orders on customers.cust_id=orders.cust_id
group by customers.cust_id;

select customers.cust_name,
   customers.cust_id,
   count(orders.order_num) as num_ord
from customers left join orders on customers.cust_id=orders.cust_id
group by customers.cust_id;