sql数据库数据的查询汇总统计和分析.docx

sql数据库数据的查询汇总统计和分析.docx

《sql数据库数据的查询汇总统计和分析.docx》由会员分享，可在线阅读，更多相关《sql数据库数据的查询汇总统计和分析.docx（29页珍藏版）》请在冰豆网上搜索。

sql数据库数据的查询汇总统计和分析

8.数据的查询、汇总、统计和分析

本章主题

SELECT语句

简单的SELECT查询

设置查询结果的字段名

关键字ALL和DISTINCT的使用

查询结果的输出目的地

WHERE子句的条件搜索功能

SELECT的通配符

字符和通配符冲突时的解决方法

连接条件设置

统计运算的高手：

聚合函数

数据分组小计

HAVING子句的使用

ORDERBY子句的使用

查询名列前茅或落后者

活用子查询（SubQuery）

精彩内容不容错过！

这一章是本课程的重点！

！

★★★★★

本章将深入剖析SELECT命令。

8.1.SELECT命令

SELECT是一个用来从一个或多个表中获取数据的SQL命令。

8.2.简单的SELECT查询

如：

USENorthwindSQL

SELECT身份证号码,姓名,电话号码--这里是字段列表

FROM飞狐工作室

SELECT命令至少包含：

要出现在查询结果中的字段列表，如：

身份证号码，姓名，电话号码

字段来自哪些表，如：

FROM飞狐工作室

字段列表也可以是由字段、常量和函数组成的表达式

要列出所有字段，只须用*号

8.3.设置查询结果的字段名

查询需求

请从数据库pubs的authors表，查询出所有作者的代号、姓名、电话号码及住址，而且请使用中文文字作为查询结果的各字段名。

解答

/*脚本文件名:

*/

USEpubs

SELECT作者代号=au_id,

姓名=au_fname+au_lname,

电话号码=phone,

住址=address

FROMauthors

或（看看你更喜欢哪一种格式）

/*脚本文件名:

*/

USEpubs

SELECTau_idAS作者代号,

au_fname+au_lnameAS姓名,

phoneAS电话号码,

addressAS住址

FROMauthors

注意：

如果您设置的的字段名包含空格，则须加上单引号

SELECTau_fname+au_lnameAS‘NameofAuthor’

FROMauthors

任务：

查询teacher表的姓名、性别和生日，列名用中文表示；查询class表的所有班级信息；查询teacher表的教师姓名和年龄。

8.4.关键字ALL和DISTINCT的使用

1.查询需求

请列出“飞狐工作室”表中所有员工的雇用日期，但是日期相同者只列出一次即可。

USENorthwindSQL

SELECTDISTINCT雇用日期FROM飞狐工作室

2.查询需求

请列出“飞狐工作室”表中有哪些部门。

USENorthwindSQL

SELECTDISTINCT部门FROM飞狐工作室

ALL为默认项，显示所有查询到的记录，包括重复项。

DISTINCT，对指定字段的内容相同的，仅显示一项。

每个SELECT表达式只能有一个DISTINCT关键字。

这意味着，DISTINCT是限制整条数据记录都重复者，只显示其中一条，而不是针对单一字段来处理。

任务：

请列出“章立民工作室”表中有哪些部门。

列出teacher表中的教师职称。

8.5.查询结果的输出目的地

为什么要讨论查询结果的输出目的地

答：

可能希望将查询结果输出到某个存储处以便进行进一步的处理。

举例说明INTO子句的用法：

将查询结果存储到当前数据库中的新表MyTmpTable中

USENorthwindSQL

SELECT*INTOMyTmpTableFROM飞狐工作室

注：

INTO子句会生成相应的表，如果表已存在则提示错误“表已存在”。

任务：

把teacher表中的教师职称保存到新表教师职称表中。

查询员工表中的职称，并存储到新表员工职称表中。

8.6.WHERE子句的条件搜索功能

1.查询需求

请列出“飞狐工作室”表中目前薪资大于60000的员工。

/*脚本文件名:

*/

USENorthwindSQL

SELECT姓名,目前薪资FROM飞狐工作室

WHERE目前薪资>60000

2.查询需求

请列出“飞狐工作室”表在信息部、行销部和业务部等3个部门任职的员工姓名。

/*脚本文件名:

*/

USENorthwindSQL

SELECT姓名,部门FROM飞狐工作室

WHERE部门IN（'资讯部','行销部','业务部'）

3.查询需求

请列出“飞狐工作室”表在信息部、行销部和业务部等3个部门以外任职的员工姓名。

/*脚本文件名:

*/

USENorthwindSQL

SELECT姓名,部门FROM飞狐工作室

WHERE部门NOTIN（'资讯部','行销部','业务部'）

4.查询需求

请列出“飞狐工作室”表中，本月出生的员工姓名和出生日期。

/*脚本文件名:

*/

USENorthwindSQL

SELECT姓名,出生日期FROM飞狐工作室

WHEREMONTH（出生日期）=MONTH（GETDATE（））

5.查询需求

请列出“飞狐工作室”表中，年龄大于20岁的每一位员工的姓名。

/*脚本文件名:

*/

USENorthwindSQL

SELECT姓名

FROM飞狐工作室

WHEREDATEDIFF（year,出生日期,GETDATE（））>20

任务：

查询章立民工作室中已婚的员工信息；查询“飞狐工作室”表中行销部的员工信息；查询06010111班或者07010211班的学生信息；查询成绩在80分以下的学生选课信息；查询年龄超过50岁的女教师信息；查询出生日期在1970到1980年之间的教师信息。

8.7.SELECT的通配符

共有5个通配符。

*（星号）

用于字段列表，代表源表中的所有的字段

/*脚本文件名:

*/

USENorthwindSQL

--连接两张表“客户”表和“订货主档”表

SELECT*

FROM客户INNERJOIN订货主档

ON客户.客户编号=订货主档.客户编号

WHERE订货主档.订单日期BETWEEN'08/01/1996'AND'08/31/1996'

/*脚本文件名:

*/

USENorthwindSQL

SELECT客户.公司名称,订货主档.*

FROM客户INNERJOIN订货主档

ON客户.客户编号=订货主档.客户编号

WHERE订货主档.订单日期BETWEEN'08/01/1996'AND'08/31/1996'

%（百分号）

只能用在WHERE子句中，代表0个或0个以上的字符。

如：

ABC%代表ABC开头的字符串。

百分号通常与运算符LIKE搭配使用。

/*脚本文件名:

*/

USENorthwindSQL

SELECT姓名FROM飞狐工作室

WHERE姓名LIKE'%光%'--表示查询姓名中包含“光”字的记录

任务：

查询“飞狐工作室”表中住在北京市的的员工信息；查询教师表中姓杜的教师信息；查询professional表中专业名称包含计算机的专业信息。

_（下划线）

只能用在WHERE子句中，代表1个字符。

如：

_A%代表第二个字符为A的字符串。

下划线通常与运算符LIKE搭配使用。

USENorthwindSQL

SELECT姓名FROM飞狐工作室

WHERE姓名LIKE'_建_'

［］（中括号）

只能用在WHERE子句中，用来限定任何一个单个字符介于指定的范围或集合中。

通常与运算符LIKE搭配使用。

/*脚本文件名:

*/

USEpubs

SELECTau_fname,au_lnameFROMauthors

WHEREau_lnameLIKE'[P-Z]inger'

--表示第一个字符为P~Z之间且后五个字符为inger

/*脚本文件名:

*/

USENorthwindSQL

SELECT姓名FROM飞狐工作室

WHERE姓名LIKE'[ACD张李]%'--表示什么

ORDERBY姓名

任务：

查询学号尾号为1、3、5、7、9的学生信息。

［^］（中括号中包含^号）

只能用在WHERE子句中，用来限定任何一个单个字符不介于指定的范围或集合中。

通常与运算符LIKE搭配使用。

/*脚本文件名:

*/

USEpubs

SELECTau_fname,au_lnameFROMauthors

WHEREau_fnameLIKE'[^H-K]ichel'

--首字母不介于H~K之间且后为ichel

/*脚本文件名:

*/

USENorthwindSQL

SELECT身份证号码,姓名FROM飞狐工作室

WHERE身份证号码LIKE'[^ALM]%'--这个表示什么意思

任务：

查询学号尾号不为1、3、5、7、9的学生信息。

8.8.字符和通配符冲突时的解决方法

可以使用ESCAPE子句通知SQLServer哪一个字符是常量字符而并非通配符。

用专业术语来说，ESCAPE子句所指定的字符称为“转义符”。

/*脚本文件名:

*/

USENorthwindSQL

SELECT姓名,家庭地址FROM飞狐工作室

WHERE家庭地址LIKE'%\_%'ESCAPE'\'

--通知符号\后的字符并非通配符，即查询住址中带下划线的记录

8.9.连接条件设置

本节内容太重要了！

看如下代码，如果TableA有M条记录，TableB有N条记录，则查询结果共有M×N条记录：

SELECT*FROMTableA，TableB

或

SELECT*FROM　TableACROSSJOINTableB

为了避免出现上述情况，有4种连接类型可以选择：

INNERJOIN

特点：

查询结果仅包含连接表中彼此相对应的数据记录。

/*脚本文件名:

本例用于查看每一位客户的订货情况

但是，并未下订单的客户不会出现在查询结果中！

*/

USENorthwindSQL

SELECT客户.客户编号,客户.公司名称,客户.联系人,客户.电话,

订货主档.订单号码,订货主档.订单日期,订货主档.要货日期,

订货主档.送货日期,订货主档.送货方式,订货主档.运费,

订货主档.收货人,订货主档.送货地址

FROM客户INNERJOIN订货主档

ON客户.客户编号=订货主档.客户编号

更复杂的连接3个表的例子：

/*脚本文件名:

*/

USENorthwindSQL

SELECT客户.客户编号,客户.公司名称,订货主档.订单号码,

订货主档.订单日期,订货明细.产品编号,订货明细.单价,

订货明细.数量,订货明细.折扣

FROM客户INNERJOIN订货主档

ON客户.客户编号=订货主档.客户编号

INNERJOIN订货明细

ON订货主档.订单号码=订货明细.订单号码

任务：

查询下了订单的客户信息：

客户编号、公司名称、订单号码、订单日期；查询产品信息：

产品编号、类别名称；查询学生信息：

学号、姓名、班级名称；查询班级信息：

班级编号、班级名称、专业名称；查询专业信息：

专业编号、专业名称、系名称；列出计算机工程系的专业信息；查询孙晓龙的所有选修课成绩；查询选修了“3dsmax”课程的学生姓名和课程成绩。

LEFTOUTERJOIN（左外连接）

特点：

查询结果将包含位于关键字LEFTOUTERJOIN左侧源表中的所有数据记录，但是仅包含右侧源表中相应的数据记录。

/*脚本文件名:

本例用于查看每一位客户的订货情况

但是希望并未下订单的客户也出现在查询结果中！

*/

USENorthwindSQL

SELECT客户.客户编号,

客户.公司名称,

客户.连络人,

订货主档.订单号码,

订货主档.收货人

FROM客户LEFTOUTERJOIN订货主档

ON客户.客户编号=订货主档.客户编号

（加上条件限制试试WHERE订货主档.订单号码isNULL）

RIGHTOUTERJOIN（右外连接）

特点：

查询结果将包含位于关键字RIGHTOUTERJOIN右侧源表中的所有数据记录，但是仅包含左侧源表中相应的数据记录。

/*脚本文件名:

查看每一种产品的销售情况

但是希望那些没有人订购的产品数据也在查询结果中出现

*/

USENorthwindSQL

SELECT订货明细.订单号码,订货明细.单价,订货明细.数量,

订货明细.折扣,产品资料.产品编号,产品资料.产品

FROM订货明细RIGHTOUTERJOIN产品资料

ON订货明细.产品编号=产品资料.产品编号

（加上条件限制试试WHERE订货明细.订单号码isNULL）

FULLOUTERJOIN（全外连接）

特点：

查询结果将包含位于关键字FULLOUTERJOIN左右两侧源表中的所有数据记录。

/*脚本文件名:

给表指定别名*/

USENorthwindSQL

SELECTa.客户编号,a.公司名称,a.连络人,a.电话,

b.订单号码,b.订单日期,b.要货日期,b.送货日期,

b.送货方式,b.运费,b.收货人,b.送货地址

FROM客户aINNERJOIN订货主档b

ONa.客户编号=b.客户编号

小技巧：

给表指定一个较短的别名，最大的好处是缩减了SELECT语句的长度。

范例：

1.查询在1996年7月份采购的北京市客户的基本数据、订单数据、订货明细。

/*脚本文件名:

*/

USENorthwindSQL

SELECTa.客户编号,a.公司名称,a.地址,a.连络人,a.电话,

b.订单号码,b.订单日期,b.运费,b.收货人,

c.产品编号,c.单价,c.数量,c.折扣

FROM客户aINNERJOIN订货主档b

INNERJOIN订货明细c

ONb.订单号码=c.订单号码

ONa.客户编号=b.客户编号

WHEREa.地址LIKE'%北京市%'AND

b.订单日期BETWEEN'07/01/1996'AND'07/31/1996'

2.查询出所有曾经在1996年订货的客户公司名称和所订购的产品明细

/*脚本文件名:

*/

USENorthwindSQL

SELECTa.公司名称,b.订单日期,d.*

FROM客户aINNERJOIN订货主档b

INNERJOIN订货明细c

INNERJOIN产品资料d

ONd.产品编号=c.产品编号

ONb.订单号码=c.订单号码

ONa.客户编号=b.客户编号

WHEREYEAR（b.订单日期）=1996

3.假如ERNSH公司是我们的忠实客户，我们要查询出该公司在1998年所下的订单，以及负责处理这些客户订单的员工基本数据：

/*脚本文件名:

*/

USENorthwindSQL

SELECTa.公司名称,b.订单号码,b.订单日期,c.*

FROM客户aINNERJOIN订货主档b

INNERJOIN员工c

ONc.员工编号=b.员工编号

ONa.客户编号=b.客户编号

WHEREa.客户编号='ERNSH'AND

YEAR（b.订单日期）=1998

8.10.统计运算的高手：

聚合函数

聚合函数专用于SELECT语句中。

COUNT

主要用于计算查询结果中的数据条数，通常用COUNT（*）的形式。

事实上，COUNT（）是唯一允许使用通配符作为参数的聚合函数。

1.计算出“飞狐工作室”表中的数据记录条数

/*脚本文件名:

*/

USENorthwindSQL

SELECTCOUNT（*）FROM飞狐工作室

2.计算出“飞狐工作室”表中有多少员工住在北京市

/*脚本文件名:

*/

USENorthwindSQL

SELECTCOUNT（*）FROM飞狐工作室

WHERE住址LIKE'%北京市%'

3.计算出“飞狐工作室”表中有年龄介于30～40之间的员工有多少

/*脚本文件名:

*/

USENorthwindSQL

SELECTCOUNT（*）FROM飞狐工作室

WHEREDATEDIFF（yy,出生日期,GETDATE（））BETWEEN30AND40

4.查询出在1996年7月份下订单的北京市客户有多少位

/*脚本文件名:

*/

USENorthwindSQL

SELECTCOUNT（*）AS客户数目

FROM客户aINNERJOIN订货主档b

ONa.客户编号=b.客户编号

WHEREa.地址LIKE'%北京市%'AND

b.订单日期BETWEEN'07/01/1996'AND'07/31/1996'

MIN

能够计算出最小值。

1.查询出“飞狐工作室”表中，最低的目前薪资是多少。

/*脚本文件名:

*/

USENorthwindSQL

SELECTMIN（目前薪资）AS最低薪资

FROM飞狐工作室

注意：

利用MIN查询出某一字段的最小值时，并无法同时得知该字段为最小值的数据记录的其他字段内容。

2.查询出“飞狐工作室”表中，年龄最大者是几岁（提供了三种方法）。

/*脚本文件名:

*/

USENorthwindSQL

SELECTYEAR（GETDATE（））-YEAR（MIN（出生日期））AS最高年龄

FROM飞狐工作室

SELECTDATEDIFF（yyyy,MIN（出生日期）,GETDATE（））AS最高年龄

FROM飞狐工作室

SELECTMAX（DATEDIFF（yyyy,出生日期,GETDATE（）））AS最高年龄

FROM飞狐工作室

MAX

能够计算出最大值。

AVG

能够计算出平均值。

1.计算出“飞狐工作室”表中员工的平均年龄

/*脚本文件名:

*/

USENorthwindSQL

SELECTAVG（DATEDIFF（yyyy,出生日期,GETDATE（）））AS平均年龄

FROM飞狐工作室

SUM

求和。

1.计算出所有客户的采购总金额

/*脚本文件名:

*/

USENorthwindSQL

SELECT客户采买总金额=

SUM（单价*数量*（1-折扣））

FROM订货明细

聚合函数注意事项：

可以将查询结果存储到一个变量中

/*脚本文件名:

*/

USENorthwindSQL

DECLARE@AverageSalarymoney

--将计算所得的平均薪资储存至变数@AverageSalary中

SELECT@AverageSalary=AVG（目前薪资）

FROM飞狐工作室

/*STR（float_expression[,length[,decimal]]）,返回由数字数据转换来的字符数据。

*/

PRINT'薪资大于平均薪资'+STR（@AverageSalary,10,4）+'的员工:

'

PRINTREPLICATE（'-',35）

SELECT姓名,目前薪资FROM飞狐工作室

WHERE目前薪资>@AverageSalary

同一SELECT语句中，可以分别使用不同的聚合函数

/*脚本文件名:

*/

USENorthwindSQL

SELECTMAX（目前薪资）AS最高薪资,--看看这种设定列标题的方式

MIN（目前薪资）AS最低薪资,

AVG（目前薪资）AS平均薪资,

SUM（目前薪资）AS薪资总额

FROM飞狐工作室

聚合函数中允许包含DISTINCT关键字，用于排除重复行。

1.计算出1996年8月份的发票开给了几位客户。

/*脚本文件名:

*/

USENorthwindSQL

SELECTCOUNT（DISTINCT客户编号）AS客户数目

FROM订货主档

WHERE订单日期BETWEEN'08/01/1996'AND'08/31/1996'

任务：

统计teacher表中职称为讲师的教师人数；查询学生的入学平均分数、最低分、最高分；查询学号为'01'的学生选课门数；查询学生'张劲'的选课门数；查询学生'张劲'选修课程的最高分、最低分、平均分。

8.11.数据分组小计

利用GROUPBY子句，可以根据一个或多个组的值将查询中的数据记录分组。

1.计算出员工“飞狐工作室”表中，各个部门的薪资最高值、薪资最小值、薪资平均值及人数。

/*脚本文件名:

*/

USENorthwindSQL

SELECT部门,

COUNT（*）AS部门员工人数,

MAX（目前薪资）AS部门最高薪资,

MIN（目前薪资）AS部门最低薪资,

AVG（目前薪资）AS部门平均薪资

FROM飞狐工作室

GROUPBY部门

任务：

统计teacher表中各类职称的教师人数；统计各课程的最高分、最低分、平均分。

2.计算出各个客户的采购次数和采购总金额。

/*脚本文件名:

*/

USENorthwindSQL

SELECTa.公司名称,

COUNT（DISTINCTb.订单号码）AS采购次数,

SUM（c.单价*c.数量*（1-c.折扣））AS采购总金额

FROM客户a

INNERJOIN订货主档b

INNERJOIN订货明细c

ONb.订单号码=c.订单号码

ONa.客户编号=b.客户编号

GROUPBYa.客户编号,a.公司名称

3.查询出每一位业务人员的销售总数量。

/*脚本文件名:

*/

USENorthwindSQL

SELECTa.员工编号,a.姓名,

SUM（c.数量）AS销售总数

FROM员工a

INNERJOIN订货主档b

INNERJOIN订货明细c

ONb.订单号码=c.订单号码

ONa.员工编号=b.员工编号

GROUPBYa.员工编号,a.姓名

4.查询出每一位业务人员的销售总数，同时列出那些比他或她销售还多的业务员数及平均销售数目。

/*脚本文件名:

*/

EXECsp_dboption'NorthwindSQL','selectinto/bulkcopy','TRUE'

--设为true能加快selecti