SQL Server编程必知必会 -- （37-57点总结）
----------------------------汇总数据-----------------------------聚集函数用来汇总数据。由多种方法来返回所需的结果，高效，快速获得结果；
-- 37. 返回products 表中所有产品的平均价格
SELECT AVG(prod_price) AS avg_price
FROM products
-- 38. 返回products 表中供应商ID=1003所有产品的平均价格
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id =1003
-- 39. 返回customers表中客户的总数
SELECT COUNT(*) as num_cust
FROM customers
-- 40. 返回customers表中有电子邮件地址的客户计数
SELECT COUNT(cust_email) as num_cust
FROM customers
-- 41. 返回products表中最贵的物品的价格
SELECT MAX(prod_price) AS max_price
FROM products
-- 42. 返回products表中最便宜的物品的价格
SELECT MIN(prod_price) AS max_price
FROM products
-- 43. 返回订单号=20005的所有物品数量之和，
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num=20005;
-- 44. 返回订单号=20005的所有物品价钱之和，
SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num=20005;
-- 45. 聚集不同值，默认对所有行计算，可以指定ALL参数或不给参数
-- 对供应商ID=1003,并且只对有不同价格的商品进行平均计算
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id =1003
-- 46. 组合聚集函数，可以包含多个聚集函数
-- 返回products表中物品的数目，产品价格的最高、最低以及平均值。
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM products
------------------------分组数据-----------------------
-- 47. 数据分组，并创建分组。 返回每个供应商提供的产品数目
SELECT vend_id, count(*) AS num_prods
FROM products
GROUP BY vend_id;
-- 结果
vend_id num_prods
1001 3
1002 2
1003 7
1005 2
-- 48.GROUP BY子句的一些重要的规定
-- GROUP BY 子句可以包含任意数目的列；
SELECT vend_id,count(*) AS num_prods
FROM products
GROUP BY vend_id;
SELECT vend_id,prod_id, count(*) AS num_prods
FROM products
GROUP BY vend_id,prod_id;
-- GROUP BY 子句可以指定多个分组，数据将在最后指定的分组上进行汇总；
SELECT vend_id,prod_id, count(*) AS num_prods
FROM products
GROUP BY vend_id,prod_id;
-- GROUP BY 子句列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数），下面表达式是错误的；
SELECT vend_id,prod_id, count(*) AS num_prods
FROM products
GROUP BY vend_id,prod_id，num_prods;
-- GROUP BY 子句，除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出；
SELECT vend_id,prod_id, count(*) AS num_prods
FROM products
GROUP BY vend_id,prod_id;
-- GROUP BY 子句，分组列中国具有NULL 值，则NULL将作为一个分组返回。如果列种有多行NULL值，它们将分为一组；
-- GROUP BY 子句，必须出现在WHERE子句之后，ORDER BY 子句之前；
SELECT vend_id,prod_id, count(*) AS num_prods
FROM products
GROUP BY vend_id,prod_id
ORDER BY vend_id,prod_id DESC
-- 49.过滤分组,规定包括哪些分组，排除哪些分组；
--列出至少有两个订单的所有顾客
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >=2
--列出具有两个以上、价格为10以上的产品的供应商：
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) > = 2
-- 50.分组和排序,规定包括哪些分组，排除哪些分组；
ORDER BY: 排序产生的输出，任意列都可以使用（甚至未选择的列也可以使用），不一定需要
GROUP BY: 分组行。但输出可能不是分组的顺序，只可能使用选择列或表达式列，而且必须使用每个选择列表达式
--检索总计订单价格大于等于50的订单的订单号和总计订单价格
SELECT order_num,SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price)>=50
order_num ordertotal
20005149.87
2000655.00
200071000.00
20008125.00
--检索总计订单价格大于等于50的订单的订单号和总计订单价格，按照总计订单价格排序输出
SELECT order_num,SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price)>=50
ORDER BY ordertotal
order_num ordertotal
2000655.00
20008125.00
20005149.87
200071000.00
--SELECT 子句及其顺序
--SELECT 要返回的列或表达式,
--FROM 从中检索数据的表
--WHERE 行级过滤
--GROUP BY 分组说明
--HAVING 组级过滤
--ORDER BY 输出排序顺序
------------------------使用子查询-----------------------
-- 51. 检索返回订购物品TNT2的所有客户的ID
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id ='TNT2')
--等价于
--检索包含物品TNT2的所有订单的编号
SELECT order_num
FROM orderitems
WHERE prod_id ='TNT2'
--检索具有订单编号为20005和20007的所有客户的ID
SELECT cust_id
FROM orders
WHERE order_num IN (20005,20007)
-- 52. 检索返回订购物品TNT2的所有客户的ID的客户信息
SELECT cust_name,cust_contact
FROM customers
WHERE cust_idIN ( SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id ='TNT2')
)
--结果：
--cust_name cust_contact
--Coyote Inc. Y Lee
--Yosemite Place Y Sam
-- 53 作为计算字段使用子查询
-- 对客户10001的订单进行计数
SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001
-- 从customers表中检索客户列表，对检索出的每个客户，统计其在orders表中的订单数目
SELECT cust_name,
cust_state,
--下面SELECT 语句作为计算字段使用子查询
(SELECT COUNT(*)
FROM orders
where orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name
-- 54. 用子查询检查存在性，EXIST关键字,处理要比IN 语句快
-- 检索200509下订单的顾客的名字和ID
SELECT cust_id, cust_name
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE DATEDIFF(MONTH,order_date,'2005-09-01')=0
AND customers.cust_id=orders.cust_id)
--解释：先找出200509下订单的顾客ID,然后再根据这些顾客ID从customers表选择所需顾客；
--等价于：
SELECT cust_id, cust_name
FROM customers
WHERE EXISTS (
SELECT cust_id
--SELECT *
FROM orders
WHERE DATEDIFF(MONTH,order_date,'2005-09-01')=0
AND customers.cust_id=orders.cust_id)
-- 55. 检索除了200509下订单的顾客的名字和ID
SELECT customers.cust_id, customers.cust_name,orders.order_date
FROM customers
LEFT JOIN orders
on customers.cust_id=orders.cust_id
WHERE EXISTS (
SELECT cust_id
--SELECT *
FROM orders
WHERE DATEDIFF(MONTH,order_date,'2005-09-01')=0
AND customers.cust_id=orders.cust_id)
-- 子查询: 尽量不要嵌套太多的子查询，可以使用操作符IN,=,,尽量不要用SELECT *
--56.内部联结:检索200509下订单的顾客的名字和ID
SELECT customers.cust_id,customers.cust_name
FROM customers
INNER JOIN orders
ON customers.cust_id = orders.cust_id
WHERE DATEDIFF(MONTH,order_date,'2005-09-01')=0
SELECT customers.cust_id,customers.cust_name
FROM customers,orders
WHERE DATEDIFF(MONTH,order_date,'2005-09-01')=0
AND customers.cust_id = orders.cust_id
-- 子查询： 检索200509下订单的顾客的名字和ID
SELECT cust_id, cust_name
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE DATEDIFF(MONTH,order_date,'2005-09-01')=0
AND customers.cust_id=orders.cust_id)
-- 57.内部联结: 检索返回订购物品TNT2的所有客户的ID的客户信息
SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHEREcustomers.cust_id = orders.cust_id
AND orderitems.order_num= orders.order_num
AND prod_id = 'TNT2'
--子查询: 检索返回订购物品TNT2的所有客户的ID的客户信息
SELECT cust_name,cust_contact
FROM customers
WHERE cust_idIN ( SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id ='TNT2')
)
-- 不要联结不必要的表，联结的表越多，性能下降越厉害；
-- 通过为外键列有效地创建索引可以明显地改善这种性能下降；
页:
[1]