SQL进阶指南：存储过程与事务处理-优快云博客

本文链接：https://blog.youkuaiyun.com/zhangzhangchenchu/article/details/104108920

本文深入探讨了SQL中的高级话题，包括如何使用存储过程进行复杂操作，以及如何有效地管理事务处理，确保数据的一致性和完整性。此外，还预告了后续将要学习的游标等内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#过滤数据（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

SQL必知必会笔记（二）