葵花宝典之SQL篇(2)

27 1

28 3

              5用多个分组来实现更精细的数据统计

                  SELECT FSubCompany,FAge,COUNT(*) AS CountOfThisSubCompAge FROMT_Employee

GROUP BY FSubCompany,FAge

                  结果：FSubCompany FAge CountOfThisAge

ShenZhen 22 1

Beijing 23 2

Beijing 25 2

ShenZhen 27 1

Beijing 28 2

ShenZhen 28 1

 

 

6为了更容易的按照每个分公司进行查看，我们可以使用 ORDER BY

SELECT FSubCompany,FAge,COUNT(*) AS CountOfThisSubCompAge FROMT_Employee

GROUP BY FSubCompany,FAge

ORDER BY FSubCompany

结果：

                FSubCompany FAge CountOfThisSubCompAge

Beijing 23 2

Beijing 25 2

Beijing 28 2

ShenZhen 22 1

ShenZhen 27 1

ShenZhen 28 1

 

7统计每个公司中的工资的总值：

   SELECT FSubCompany,SUM(FSalary) AS FSalarySUM FROM T_Employee

GROUP BY FSubCompany

结果：

FSubCompany FSalarySUM

Beijing 30801.24

ShenZhen 6300.80

8以统计每个垂直部门中的工资的平均值

SELECT FDepartment,SUM(FSalary) AS FSalarySUM FROM T_Employee

GROUP BY FDepartment

               结果：

FDepartment FSalarySUM

Development 10600.80

HumanResource 7401.24

InfoTech 6700.00

Sales 12400.00

               9统计每个垂直部门中员工年龄的最大值和最小值

                  SELECT FDepartment,MIN(FAge) AS FAgeMIN,MAX(FAge) AS FAgeMAX FROMT_EmployeeGROUP BY FDepartment

                       结果：FDepartment FAgeMIN FAgeMAX

Development 25 28

HumanResource 23 25

InfoTech 27 28

Sales 22 28

（2009-7-3 23：50  今天就到这了，好累，睡觉了）

12、HAVING 语句

             聚合函数不能在WHERE中使用，必须用HAVING来代替

             如：下面语句会报错

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee

GROUP BY FAge

WHERE COUNT(*)>1

   正确的应该这样写：

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee

GROUP BY FAge

HAVING COUNT(*)>1

★HAVING语句中也可以像WHERE语句一样使用复杂的过滤条件，比如下面的SQL用来检索人数为1个或者3个的年龄段，可以使用下面的SQL：

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee

GROUP BY FAge

HAVING COUNT(*) =1 OR COUNT(*) =3

              结果：

                                       FAge CountOfThisAge

22 1

27 1

28 3

13、限制结果集行数

                 检索从第六名开始的一共三个人的信息（MSSQL2000）

                SELECT top 3 * FROM T_Employee

WHERE FNumber NOT IN

(SELECT TOP 5 FNumber FROM T_Employee ORDER BY FSalary DESC)

ORDER BY FSalary DESC

14、数据库分页

15、抑制数据重复

               DISTINCT关键字是用来进行重复数据抑制的最

简单的功能，而且所有的数据库系统都支持DISTINCT，DISTINCT的使用也非常简单，只要在SELECT之后增加DISTINCT即可

SELECT DISTINCT FDepartment FROM T_Employee

☆      DISTINCT是对整个结果集进行数据重复抑制的，而不是针对每一个列

例如：SELECT DISTINCT FDepartment,FSubCompany FROM T_Employee

得到的结果是：

FDepartment FSubCompany

Development Beijing

Development ShenZhen

HumanResource Beijing

InfoTech Beijing

InfoTech ShenZhen

Sales Beijing

Sales ShenZhen

很显然，FDepartment有重复的，但是整个结果集是没有重复的

16、计算字段

            例如：SELECT FNumber,FName,FAge * FSalary FROM T_Employee

                             FAge * FSalary并不是原来就有的，而是计算出来的

  函数

17、函数数据处理

          1 SELECT FName, LEN(FName) AS namelength FROM T_Employee

WHERE FName IS NOT NULL

                        结果：

                        FName namelength

Tom 3

Jerry 5

Jane 4

Tina 4

Smith 5

John 4

Kerry 5

Stone 5

         2 SELECT FName, SUBSTRING(FName,2,3) FROM T_Employee

WHERE FName IS NOT NULL

                 结果：

                         FName namelength

Tom om

Jerry er

Jane an

Tina in

Smith mi

John oh

Kerry er

Stone to

          3 多个函数还可以嵌套使用：

           SELECT FName,FAge, SIN(FAge) , ABS(SIN(FAge)) FROM T_Employee

18、字符串的拼接

         

 

 

备注：

（   以上学习用到的表  ：

CREATE TABLE T_Employee (FNumber VARCHAR(20),FName VARCHAR(20),FAge INT,FSalary

NUMERIC(10,2),PRIMARY KEY (FNumber)

 向表中插入数据：

INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)

VALUES('DEV001','Tom',25,8300);

INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('DEV002','Jerry',28,2300.80);

INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES001','John',23,5000);

INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES002','Kerry',28,6200);

INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES003','Stone',22,1200);

INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('HR001','Jane',23,2200.88);

INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('HR002','Tina',25,5200.36);

INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)

VALUES('IT001','Smith',28,3900);

 

ALTER TABLE T_Employee ADD FSubCompany VARCHAR(20);

ALTER TABLE T_Employee ADD FDepartment VARCHAR(20);

 

 

 

UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='Development'

WHERE FNumber='DEV001';

UPDATE T_Employee SET FSubCompany='ShenZhen',FDepartment='Development'

WHERE FNumber='DEV002';

UPDATE T_Employee SET

FSubCompany='Beijing',FDepartment='HumanResource'

WHERE FNumber='HR001';

UPDATE T_Employee SET

FSubCompany='Beijing',FDepartment='HumanResource'

WHERE FNumber='HR002';

UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='InfoTech'

WHERE FNumber='IT001';

UPDATE T_Employee SET FSubCompany='ShenZhen',FDepartment='InfoTech'

WHERE FNumber='IT002';

UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='Sales'

WHERE FNumber='SALES001';

UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='Sales'

WHERE FNumber='SALES002';

UPDATE T_Employee SET FSubCompany='ShenZhen',FDepartment='Sales'

WHERE FNumber='SALES003';

 

）

评论

2 楼 bianku 2009-07-10  

zywang 写道

兄弟格式有点乱啊

没弄格式,呵呵,将就看吧

1 楼 zywang 2009-07-10  

兄弟格式有点乱啊