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
　　20005  149.87
　　20006  55.00
　　20007  1000.00
　　20008  125.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
　　20006  55.00
　　20008  125.00
　　20005  149.87
　　20007  1000.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_id  IN ( 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
　　WHERE  customers.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_id  IN ( SELECT cust_id
　　FROM orders
　　WHERE order_num IN (SELECT order_num
　　FROM orderitems
　　WHERE prod_id ='TNT2')
　　)
　　-- 不要联结不必要的表，联结的表越多，性能下降越厉害；
　　-- 通过为外键列有效地创建索引可以明显地改善这种性能下降；