SQL必知必会笔记（二）

#过滤数据（where  having）
SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price >=4
GROUP BY vend_id
HAVING COUNT(*) >=2;

#过滤数据（group by  having）
SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >=2;

#排序

SELECT order_num,COUNT(*) AS items
FROM orderItems
GROUP BY order_num
HAVING COUNT(*) >=3;

SELECT order_num,COUNT(*) AS items
FROM orderItems
GROUP BY order_num
HAVING COUNT(*) >=3
ORDER BY items,order_num;

/*
select 子句顺序
select
from
where
group by
having
order by
limit

select 子句执行顺序
from
where
group by
having
select
order by
limit
*/

#子查询
SELECT order_num
FROM orderitems
WHERE prod_id = 'RGAN01';

SELECT cust_id
FROM orders
WHERE order_num IN (20007,20008);

#从内向外
SELECT cust_id
FROM orders
WHERE order_num IN (
SELECT order_num
FROM orderitems
WHERE prod_id = 'RGAN01'
);

SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN ('1000000004','1000000005');

#三个子查询

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'
));

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

SELECT cust_name,cust_state,
(SELECT COUNT(*) FROM orders 
WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers ORDER BY cust_name;

#创建联结
SELECT vend_name,prod_name,prod_price
FROM ven