mysql 必知必会

供应商表 vendor:主键(vendor_id),供应商名称(vendor_name),供应商地址(vendor_address),供应商省份(vendor_province),供应商城市(vendor_city) 

商品表 products :主键 (prod_id),供应商ID (vendor_id),商品名称 (prod_name),商品价钱 (prod_price),商品描述 (prod_desc)

客户表 customers:主键(cust_id),客户名称(cust_name),客户地址(cust_address),客户省份(cust_province),客户城市(cust_city),客户邮箱(cust_email)

订单表 orders:主键(order_id),订单时间(order_date),客户ID(cust_id)

1.列出订单数大于2的客户ID和订单数

select cust_id,count(*)  as order_num from  orders  group by cust_id having order_num>2;

2.列出具有2个以上价格为100的商品的供应商

select vendor_id,count(*) as prod_num from products where prod_price=100 group by vendor_id having prod_num>2;

3.查询每个客户的订单数

select cust_name,(select count(*) from orders where orders.cust_id=custombers.cust_id) as order_num from custombers order by order_num;

4.列出每个供应商所供应的商品及价钱

select vendor_name,prod_name,prod_price from vendor,products where vendor.vendor_id=products.vendor_id order by vendor_name,prod_name;

5.查找一个商品的供应商的所有商品

select a.prod_id,a.prod_name from products as a ,products as b where a.vendor_id=b.vendor_id and b.prod_id=1;

6.所有客户的订单（包括没有订单的客户）

select custombers.cust_name,orders.order_id from custombers left outer join orders on custombers.cust_id = orders.cust_id;

7.所有客户每个客户的订单数（包括没有订单的客户）

select custombers.cust_name,count(orders.order_id) as order_num  from custombers left join orders on custombers.cust_id = orders.cust_id group by orders.cust_id order by order_num desc ;