SQL必知必会笔记（二）

最新推荐文章于 2024-08-14 14:34:24 发布

原创

最新推荐文章于 2024-08-14 14:34:24 发布 · 359 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL #数据分析

本文深入探讨了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;

#创建联结