黑马程序园--SQL基础复习（二）
---------------------- .Net培训、WP7培训、期待与您交流！----------------------
此篇是接着SQL基础复习（一）写的。
主要介绍几个常用的关键字。
（1）GROUP BY 按组分类。
（2）WHERE 子句中不能出现聚合函数（Count、Max....)
WHERE 子句应该放在GROUP BY 的前面。
（3）HAVING 子句则放在GROUP 子句的后面。
HAVING 子句可以用聚合函数，对分组后的组进行过滤。
HAVING 子句里的字段必须包含在GROUP BY 语句中。
下面是示例代码：
View Code
1 --分组GROUP BY
2 --SELECT 后的字段一定要再GROUP BY 里。
3 --COUNT 求出的是每组的个数。
4 SELECT Age,AVG(Salary),COUNT(*)FROM T_Employees GROUP BY Age;
5
6 SELECT Age,AVG(Salary),COUNT(*)FROM T_Employees WHERE Salary > 3000 GROUP BY Age;
7
8 SELECT Age,AVG(Salary),COUNT(*)FROM T_Employees GROUP BY Age HAVING Age > 38;
（4）TOP 限制结果集数量。用于分页。
View Code
1 --限制结果集数量 TOP 必须是紧跟在SELECT后面
2 --工资前五名
3 SELECT TOP 5 * FROM T_Employees ORDER BY Salary DESC;
4
5 --子查询
6 --工资除了3人外，紧接着的4名
7 SELECT TOP 4 * FROM T_Employees WHERE ID NOT IN
8 (SELECT TOP 3 ID FROM T_Employees ORDER BY Salary DESC)
9 ORDER BY Salary DESC;
（5）DISTINCT 去掉重复数据，和分组不同
对现有表添加了连个字段
View Code
1 --去掉重复数据Distinct
2 SELECT DISTINCT Salary FROM T_Employees ORDER BY Salary DESC;
3
4 ALTER TABLE T_Employees ADD Department nvarchar(20);
5 ALTER TABLE T_Employees ADD SubCompany nvarchar(20);
6
7 SELECT * FROM T_Employees;
8
9 UPDATE T_Employees SET Department = 'SALES' where ID in ('SALE001','SALE002','SALE003');
10 UPDATE T_Employees SET SubCompany ='ChongQing' where ID in ('DEV004');
11
12 SELECT DISTINCT SubCompany FROM T_Employees;
13
14 SELECT AVG (Salary),SubCompany FROM T_Employees GROUP BY SubCompany ORDER BY AVG(Salary) DESC;
DISTINCT 是对整条记录过滤的，只要有一个字段不相同，就不会过滤。
View Code
1 SELECT SubCompany,Department FROM T_Employees;
2 SELECT DISTINCT SubCompany,Department FROM T_Employees;
（6）UNION 关键字很少使用，只需注意合并时字段类型必须一致或者兼容
不想把重复列过滤掉就用UNION ALL
View Code
1 --UNION ALL 使用
2 SELECT '最大年龄' as Description, MAX(Age)as Details FROM T_Employees
3 UNION ALL
4 SELECT '最小年龄' , MIN(Age) FROM T_Employees
5 UNION ALL
6 SELECT '平均年龄' , AVG(Age) FROM T_Employees
7 UNION ALL
8 SELECT '最高工资' , MAX(Salary) FROM T_Employees
9 UNION ALL
10 SELECT '最低工资' , MIN(Salary) FROM T_Employees
11 UNION ALL
12 SELECT '平均工资' , AVG(Salary) FROM T_Employees;
13
14 SELECT ID,Salary FROM T_Employees
15 UNION ALL
16 SELECT '工资总额',SUM(Salary) FROM T_Employees;
---------------------- .Net培训、WP7培训、期待与您交流！----------------------
