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

1、.8. 数据的查询、汇总 、统计和分析本章主题l select语句l 简单的select查询l 设置查询结果的字段名l 关键字all和distinct的使用l 查询结果的输出目的地l where子句的条件搜索功能l select的通配符l 字符和通配符冲突时的解决方法l 连接条件设置l 统计运算的高手：聚合函数l 数据分组小计l having子句的使用l order by子句的使用l 查询名列前茅或落后者l 活用子查询（subquery）精彩内容不容错过！这一章是本课程的重点！本章将深入剖析select命令。8.1. select命令select是一个用来从一个或多个表中获取数据的sql命令。

2、8.2. 简单的select查询如：use northwindsql精品.select 身份证号码,姓名,电话号码 - 这里是字段列表from 飞狐工作室select命令至少包含： 要出现在查询结果中的字段列表，如：身份证号码，姓名，电话号码 字段来自哪些表，如：from 飞狐工作室 字段列表也可以是由字段、常量和函数组成的表达式 要列出所有字段，只须用 * 号8.3. 设置查询结果的字段名查询需求请从数据库pubs的authors表，查询出所有作者的代号、姓名、电话号码及住址，而且请使用中文文字作为查询结果的各字段名。解答/* 脚本文件名: demo91.sql */use pubssele

3、ct 作者代号 = au_id, 姓名 = au_fname+au_lname, 电话号码 = phone, 住址 = address from authors或 （看看你更喜欢哪一种格式？）/* 脚本文件名: demo92.sql */use pubsselect au_id as 作者代号, au_fname+au_lname as 姓名,精品. phone as 电话号码, address as 住址 from authors注意： 如果您设置的的字段名包含空格，则须加上单引号select au_fname+au_lname as name of authorfrom authors任务

4、：查询teacher表的姓名、性别和生日，列名用中文表示；查询class表的所有班级信息；查询teacher表的教师姓名和年龄。8.4. 关键字all和distinct的使用1. 查询需求请列出“飞狐工作室”表中所有员工的雇用日期，但是日期相同者只列出一次即可。use northwindsqlselect distinct 雇用日期 from 飞狐工作室2. 查询需求请列出“飞狐工作室”表中有哪些部门。use northwindsqlselect distinct 部门 from飞狐工作室all为默认项，显示所有查询到的记录，包括重复项。distinct，对指定字段的内容相同的，仅显示一项。每

5、个select表达式只能有一个distinct关键字。这意味着，distinct是限制整条数据记录都重复者，只显示其中一条，而不是针对单一字段来处理。精品.任务：请列出“章立民工作室”表中有哪些部门。列出teacher表中的教师职称。8.5. 查询结果的输出目的地为什么要讨论查询结果的输出目的地？答：可能希望将查询结果输出到某个存储处以便进行进一步的处理。举例说明into子句的用法： 将查询结果存储到当前数据库中的新表mytmptable中use northwindsqlselect * into mytmptable from 飞狐工作室注：into子句会生成相应的表，如果表已存在则提示错误

6、“表已存在”。任务：把teacher表中的教师职称保存到新表教师职称表中。查询员工表中的职称，并存储到新表员工职称表中。8.6. where子句的条件搜索功能1. 查询需求请列出“飞狐工作室”表中目前薪资大于60000的员工。/* 脚本文件名: demo93.sql */use northwindsqlselect 姓名,目前薪资 from 飞狐工作室 where 目前薪资 600002. 查询需求请列出“飞狐工作室”表在信息部、行销部和业务部等3个部门任职的员工姓名。精品./* 脚本文件名: demo94.sql */use northwindsqlselect 姓名,部门 from 飞狐工

7、作室 where 部门 in (资讯部,行销部,业务部)3. 查询需求请列出“飞狐工作室”表在信息部、行销部和业务部等3个部门以外任职的员工姓名。/* 脚本文件名: demo95.sql */use northwindsqlselect 姓名,部门 from 飞狐工作室 where 部门 not in (资讯部,行销部,业务部)4. 查询需求请列出“飞狐工作室”表中，本月出生的员工姓名和出生日期。/* 脚本文件名: demo910.sql */use northwindsqlselect 姓名,出生日期 from 飞狐工作室 where month(出生日期) = month(getdate(

8、)5. 查询需求请列出“飞狐工作室”表中，年龄大于20岁的每一位员工的姓名。/* 脚本文件名: demo911.sql */use northwindsqlselect 姓名 from 飞狐工作室 where datediff(year,出生日期,getdate()20任务：查询章立民工作室中已婚的员工信息；查询“飞狐工作室”表中行销部的员工信息；查询06010111班或者07010211班的学生信息；查询成绩在80分以下的学生选课信息；查询年龄超过50岁的女教师信息；查询出生日期在1970到1980年之间的教师信息。精品.8.7. select的通配符共有5个通配符。*（星号） 用于字段列表

9、，代表源表中的所有的字段/* 脚本文件名: demo913.sql */use northwindsql-连接两张表“客户”表和“订货主档”表select * from 客户 inner join 订货主档 on 客户.客户编号 = 订货主档.客户编号 where 订货主档.订单日期 between 08/01/1996 and 08/31/1996/* 脚本文件名: demo914.sql */use northwindsqlselect 客户.公司名称,订货主档.* from 客户 inner join 订货主档 on 客户.客户编号 = 订货主档.客户编号 where 订货主档.订单日期

10、 between 08/01/1996 and 08/31/1996%（百分号） 只能用在where子句中，代表0个或0个以上的字符。如：abc%代表abc开头的字符串。 百分号通常与运算符like搭配使用。/* 脚本文件名: demo915.sql */精品.use northwindsqlselect 姓名 from 飞狐工作室 where 姓名 like %光% -表示查询姓名中包含“光”字的记录任务：查询“飞狐工作室”表中住在北京市的的员工信息；查询教师表中姓杜的教师信息；查询professional表中专业名称包含计算机的专业信息。_（下划线） 只能用在where子句中，代表1个字符

11、。如：_a%代表第二个字符为a的字符串。 下划线通常与运算符like搭配使用。 use northwindsql select 姓名 from 飞狐工作室 where 姓名 like _建_（中括号） 只能用在where子句中，用来限定任何一个单个字符介于指定的范围或集合中。 通常与运算符like搭配使用。/* 脚本文件名: demo923.sql */use pubsselect au_fname,au_lname from authors where au_lname like p-zinger -表示第一个字符为pz之间且后五个字符为inger/* 脚本文件名: demo924.sql

12、*/use northwindsql精品.select 姓名 from 飞狐工作室 where 姓名 like acd张李% -表示什么？ order by 姓名任务：查询学号尾号为1、3、5、7、9的学生信息。（中括号中包含号） 只能用在where子句中，用来限定任何一个单个字符不介于指定的范围或集合中。 通常与运算符like搭配使用。/* 脚本文件名: demo927.sql */use pubsselect au_fname,au_lname from authors where au_fname like h-kichel -首字母不介于hk之间且后为ichel/* 脚本文件名: de

13、mo930.sql */use northwindsqlselect 身份证号码,姓名 from 飞狐工作室 where 身份证号码 like alm% -这个表示什么意思？任务：查询学号尾号不为1、3、5、7、9的学生信息。8.8. 字符和通配符冲突时的解决方法可以使用escape子句通知sql server哪一个字符是常量字符而并非通配符。精品.用专业术语来说，escape子句所指定的字符称为“转义符”。/* 脚本文件名: demo920.sql */use northwindsqlselect 姓名, 家庭地址 from 飞狐工作室 where家庭地址 like %_% escape -

14、通知符号后的字符并非通配符，即查询住址中带下划线的记录8.9. 连接条件设置本节内容太重要了！看如下代码，如果tablea有m条记录，tableb有n条记录，则查询结果共有mn条记录：select * from tablea，tableb或select * from tablea cross join tableb为了避免出现上述情况，有4种连接类型可以选择： inner join特点：查询结果仅包含连接表中彼此相对应的数据记录。/* 脚本文件名: demo933.sql 本例用于查看每一位客户的订货情况但是，并未下订单的客户不会出现在查询结果中！*/use northwindsqlselec

15、t 客户.客户编号, 客户.公司名称, 客户.联系人, 客户.电话, 订货主档.订单号码, 订货主档.订单日期, 订货主档.要货日期, 订货主档.送货日期, 订货主档.送货方式, 订货主档.运费,精品. 订货主档.收货人,订货主档.送货地址 from 客户 inner join 订货主档 on 客户.客户编号 = 订货主档.客户编号更复杂的连接3个表的例子：/* 脚本文件名: demo934.sql */use northwindsqlselect 客户.客户编号,客户.公司名称,订货主档.订单号码, 订货主档.订单日期,订货明细.产品编号,订货明细.单价, 订货明细.数量, 订货明细.折扣

16、from 客户 inner join 订货主档 on 客户.客户编号 = 订货主档.客户编号 inner join 订货明细 on 订货主档.订单号码 = 订货明细.订单号码任务：查询下了订单的客户信息：客户编号、公司名称、订单号码、订单日期；查询产品信息：产品编号、类别名称；查询学生信息：学号、姓名、班级名称；查询班级信息：班级编号、班级名称、专业名称；查询专业信息：专业编号、专业名称、系名称；列出计算机工程系的专业信息；查询孙晓龙的所有选修课成绩；查询选修了“3dsmax”课程的学生姓名和课程成绩。 left outer join（左外连接）特点：查询结果将包含位于关键字left oute

17、r join左侧源表中的所有数据记录，但是仅包含右侧源表中相应的数据记录。/* 脚本文件名: demo935.sql 本例用于查看每一位客户的订货情况但是希望并未下订单的客户也出现在查询结果中！*/use northwindsql精品.select 客户.客户编号, 客户.公司名称, 客户.连络人, 订货主档.订单号码, 订货主档.收货人 from 客户 left outer join 订货主档 on 客户.客户编号 = 订货主档.客户编号（加上条件限制试试 where订货主档.订单号码 is null） right outer join（右外连接）特点：查询结果将包含位于关键字right o

18、uter join右侧源表中的所有数据记录，但是仅包含左侧源表中相应的数据记录。/* 脚本文件名: demo936.sql 查看每一种产品的销售情况但是希望那些没有人订购的产品数据也在查询结果中出现*/use northwindsqlselect 订货明细.订单号码, 订货明细.单价, 订货明细.数量, 订货明细.折扣, 产品资料.产品编号, 产品资料.产品 from 订货明细 right outer join 产品资料 on 订货明细.产品编号 = 产品资料.产品编号（加上条件限制试试 where 订货明细.订单号码 is null） full outer join（全外连接）特点：查询结果

19、将包含位于关键字full outer join左右两侧源表中的所有数据记录。/* 脚本文件名: demo938.sql 给表指定别名*/use northwindsql精品.select a.客户编号, a.公司名称, a.连络人, a.电话, b.订单号码, b.订单日期, b.要货日期, b.送货日期, b.送货方式, b.运费, b.收货人, b.送货地址 from 客户 a inner join 订货主档 b on a.客户编号 = b.客户编号小技巧：给表指定一个较短的别名，最大的好处是缩减了select语句的长度。范例：1. 查询在1996年7月份采购的北京市客户的基本数据、订单数

20、据、订货明细。/* 脚本文件名: demo939.sql */use northwindsqlselect a.客户编号,a.公司名称, a.地址,a.连络人,a.电话, b.订单号码,b.订单日期, b.运费,b.收货人, c.产品编号,c.单价, c.数量,c.折扣 from 客户 a inner join 订货主档 b inner join 订货明细 c on b.订单号码 = c.订单号码 on a.客户编号 = b.客户编号 where a.地址 like %北京市% and b.订单日期 between 07/01/1996 and 07/31/19962. 查询出所有曾经在199

21、6年订货的客户公司名称和所订购的产品明细/* 脚本文件名: demo940.sql */use northwindsqlselect a.公司名称,b.订单日期,d.*精品. from 客户 a inner join 订货主档 b inner join 订货明细 c inner join 产品资料 d on d.产品编号 = c.产品编号 on b.订单号码 = c.订单号码 on a.客户编号 = b.客户编号 where year(b.订单日期) = 19963. 假如ernsh公司是我们的忠实客户，我们要查询出该公司在1998年所下的订单，以及负责处理这些客户订单的员工基本数据：/* 脚

22、本文件名: demo941.sql */use northwindsqlselect a.公司名称,b.订单号码,b.订单日期,c.* from 客户 a inner join 订货主档 b inner join 员工 c on c.员工编号 = b.员工编号 on a.客户编号 = b.客户编号 where a.客户编号 = ernsh and year(b.订单日期) = 19988.10. 统计运算的高手：聚合函数聚合函数专用于select语句中。 countn 主要用于计算查询结果中的数据条数，通常用count(*)的形式。n 事实上，count()是唯一允许使用通配符作为参数的聚合函

23、数。1. 计算出“飞狐工作室”表中的数据记录条数精品./* 脚本文件名: demo942.sql */use northwindsqlselect count(*) from 飞狐工作室2. 计算出“飞狐工作室”表中有多少员工住在北京市/* 脚本文件名: demo943.sql */use northwindsqlselect count(*) from 飞狐工作室 where 住址 like %北京市%3. 计算出“飞狐工作室”表中有年龄介于3040之间的员工有多少/* 脚本文件名: demo944.sql */use northwindsqlselect count(*) from 飞狐工

24、作室 where datediff(yy,出生日期,getdate() between 30 and 404. 查询出在1996年7月份下订单的北京市客户有多少位/* 脚本文件名: demo945.sql */use northwindsqlselect count(*) as 客户数目 from 客户 a inner join 订货主档 b on a.客户编号 = b.客户编号 where a.地址 like %北京市% and b.订单日期 between 07/01/1996 and 07/31/1996 min精品.n 能够计算出最小值。1. 查询出“飞狐工作室”表中，最低的目前薪资是

25、多少。/* 脚本文件名: demo946.sql */use northwindsqlselect min(目前薪资) as 最低薪资 from 飞狐工作室注意：利用min查询出某一字段的最小值时，并无法同时得知该字段为最小值的数据记录的其他字段内容。2. 查询出“飞狐工作室”表中，年龄最大者是几岁（提供了三种方法）。/* 脚本文件名: demo947.sql */use northwindsqlselect year(getdate() - year(min(出生日期) as 最高年龄 from 飞狐工作室select datediff(yyyy,min(出生日期),getdate() as

26、 最高年龄 from 飞狐工作室select max(datediff(yyyy,出生日期,getdate() as 最高年龄 from 飞狐工作室 maxn 能够计算出最大值。 avgn 能够计算出平均值。精品.1. 计算出“飞狐工作室”表中员工的平均年龄/* 脚本文件名: demo951.sql */use northwindsqlselect avg(datediff(yyyy,出生日期,getdate() as 平均年龄 from 飞狐工作室 sumn 求和。1. 计算出所有客户的采购总金额/* 脚本文件名: demo952.sql */use northwindsqlselect 客

27、户采买总金额 = sum(单价*数量*(1-折扣) from 订货明细聚合函数注意事项： 可以将查询结果存储到一个变量中/* 脚本文件名: demo953.sql */use northwindsqldeclare averagesalary money- 将计算所得的平均薪资储存至变数 averagesalary 中select averagesalary = avg(目前薪资) from 飞狐工作室/* str (float_expression , length ,decimal ),返回由数字数据转换来的字符数据。*/print 薪资大于平均薪资 +str(averagesalary,

28、10,4)+ 的员工:精品.print replicate(-,35)select 姓名,目前薪资 from 飞狐工作室 where 目前薪资 averagesalary 同一select语句中，可以分别使用不同的聚合函数/* 脚本文件名: demo954.sql */use northwindsqlselect max(目前薪资) as 最高薪资, -看看这种设定列标题的方式 min(目前薪资) as 最低薪资, avg(目前薪资) as 平均薪资, sum(目前薪资) as 薪资总额 from 飞狐工作室 聚合函数中允许包含distinct关键字，用于排除重复行。1. 计算出1996年8月

29、份的发票开给了几位客户。/* 脚本文件名: demo955.sql */use northwindsqlselect count(distinct 客户编号) as 客户数目 from 订货主档 where 订单日期 between 08/01/1996 and 08/31/1996任务：统计teacher表中职称为讲师的教师人数；查询学生的入学平均分数、最低分、最高分；查询学号为0601011101的学生选课门数；查询学生张劲的选课门数；查询学生张劲选修课程的最高分、最低分、平均分。精品.8.11. 数据分组小计利用group by子句，可以根据一个或多个组的值将查询中的数据记录分组。1.

30、计算出员工“飞狐工作室”表中，各个部门的薪资最高值、薪资最小值、薪资平均值及人数。/* 脚本文件名: demo956.sql */use northwindsqlselect 部门, count(*) as 部门员工人数, max(目前薪资) as 部门最高薪资, min(目前薪资) as 部门最低薪资, avg(目前薪资) as 部门平均薪资 from 飞狐工作室 group by 部门任务：统计teacher表中各类职称的教师人数；统计各课程的最高分、最低分、平均分。2. 计算出各个客户的采购次数和采购总金额。/* 脚本文件名: demo957.sql */use northwindsql

31、select a.公司名称, count(distinct b.订单号码) as 采购次数, sum(c.单价 * c.数量 * (1 - c.折扣) as 采购总金额 from 客户 a inner join 订货主档 b inner join 订货明细 c on b.订单号码 = c.订单号码精品. on a.客户编号 = b.客户编号 group by a.客户编号,a.公司名称3. 查询出每一位业务人员的销售总数量。/* 脚本文件名: demo958.sql */use northwindsqlselect a.员工编号,a.姓名, sum(c.数量) as 销售总数 from 员工

32、a inner join 订货主档 b inner join 订货明细 c on b.订单号码 = c.订单号码 on a.员工编号 = b.员工编号 group by a.员工编号,a.姓名4. 查询出每一位业务人员的销售总数，同时列出那些比他或她销售还多的业务员数及平均销售数目。/* 脚本文件名: demo959.sql */exec sp_dboption northwindsql,select into/bulkcopy,true -设为true能加快select into的处理速度use northwindsql-首先，生成一张amount表，包含每一位业务人员的销售总数select

33、 a.员工编号,a.姓名,sum(c.数量) as 销售总数 into amount from 员工 a inner join 订货主档 b inner join 订货明细 c on b.订单号码 = c.订单号码 on a.员工编号 = b.员工编号精品. group by a.员工编号,a.姓名 select a.员工编号, a.姓名,a.销售总数, count(b.员工编号) as 业绩比此人好之业务员的人数, avg(b.销售总数) as 业绩比此人好之业务员的平均销售总数 from amount a inner join amount b on a.销售总数 30000-不可以用ha

34、ving 部门的平均薪资 30000任务：查询选修了选修4门以上（包括4门）选修课的学生；查询订单数查过10张的客户。2. 查询出“飞狐工作室”表中，有哪些员工的姓名是跟别人相同的，而且同样采用某一姓名者又有多少人。/* 脚本文件名: demo962.sql */use northwindsqlselect 姓名 as 重复的姓名, count(*) as 重复的人数 from 飞狐工作室 group by 姓名 having count(*) 13. 计算出订单超过10张的各家客户的订单数目，及其采购总金额。/* 脚本文件名: demo963.sql */use northwindsqlse

35、lect a.公司名称, count(distinct b.订单号码) as 订单张数, sum(c.单价 * c.数量 * (1 - c.折扣) as 采购总金额 from 客户 a inner join 订货主档 b inner join 订货明细 c on b.订单号码 = c.订单号码 on a.客户编号 = b.客户编号 group by a.公司名称精品. having count(distinct b.订单号码) 108.13. order by 子句的使用利用order by子句，可以根据一个或多个字段的数据排序查询出的结果。1. 按照薪资所得的高低顺序，列出“飞狐工作室”表中

36、所有员工的姓名、性别和薪资。/* 脚本文件名: demo964.sql */use northwindsqlselect 姓名, 性别 = case 性别 when 0 then 女 when 1 then 男 end, 目前薪资 from 飞狐工作室 order by 目前薪资 desc /可用order by 3 desc2. 将“飞狐工作室”表中各部门的平均薪资由低到高列出来。/* 脚本文件名: demo965.sql */use northwindsqlselect 部门, avg(目前薪资) as 平均薪资 from 飞狐工作室 group by 部门 order by 2 -可以是

37、order by 平均薪资 -也可以是order by avg(目前新资)3. 列出“飞狐工作室”表中所有员工的隶属部门、员工姓名、性别和薪资，按部门、性别（降序）、目前薪资（精品.升序）来排列。/* 脚本文件名: demo966.sql */use northwindsqlselect 部门, 姓名, 性别 = case 性别 when 0 then 女 when 1 then 男 end, 目前薪资 from 飞狐工作室 order by 部门,性别 desc,目前薪资 asc任务：查询入学成绩在450分以上的学生信息，并且按照学生入学成绩从高到低排序；查询选修了3dsmax课程的学生姓名

38、和成绩，并按照成绩降序排列。4. 计算出每一位客户的采购次数和采购总金额。先按照采购次数由多到少排列，而采购次数相同者，则再按照采购总金额由低到高排列。/* 脚本文件名: demo967.sql */use northwindsqlselect a.公司名称, count(distinct b.订单号码) as 采购次数, sum(c.单价 * c.数量 * (1 - c.折扣) as 采购总金额 from 客户 a inner join 订货主档 b inner join 订货明细 c on b.订单号码 = c.订单号码 on a.客户编号 = b.客户编号精品. group by a.公

39、司名称 order by采购次数desc, 采购总金额8.14. 查询名列前茅或落后者先利用order by排序，然后利用关键字top n percent来取出从头算起的前几名或从头算起的多少百分比。1. 查询出“飞狐工作室”中，薪资最低员工的姓名及其薪资。/* 脚本文件名: demo968.sql */use northwindsqlselect top 1 姓名 as 薪资最低之员工姓名, 目前薪资 from 飞狐工作室 order by 目前薪资2. 查询出“飞狐工作室”中，薪资最高员工的姓名及其薪资。3. 查询出“飞狐工作室”中，薪资最高的前10名员工的姓名及其薪资。4. 查询出“飞狐

40、工作室”中，薪资倒数前10名员工的姓名及其薪资。5. 查询出“飞狐工作室”中，部门的平均薪资最高的前3个部门。/* 脚本文件名: demo972.sql */use northwindsqlselect top 3 部门 as 平均薪资最高的前三个部门, avg(目前薪资) as 平均薪资 from 飞狐工作室 group by 部门 order by 2 desc6. 查询出“飞狐工作室”中，目前薪资最高的前10的员工。精品.use northwindsqlselect top 10 percent 姓名 ,目前薪资 from 飞狐工作室 order by 目前薪资任务：查询入学成绩排名前1

41、0的学生。7. 查询出销售业绩最佳的前3名业务员。/* 脚本文件名: demo974.sql */use northwindsqlselect top 3 a.员工编号, a.姓名 as 业务员姓名, sum(c.单价 * c.数量*(1 - c.折扣) as 销售总金额 from 员工 a inner join 订货主档 b inner join 订货明细 c on b.订单号码 = c.订单号码 on a.员工编号 = b.员工编号 group by a.员工编号,a.姓名 order by 3 desc8. 查询出在1996年采购总金额最高的客户（并显示客户的采购次数）。/* 脚本文件名

42、: demo975.sql */use northwindsql精品.select top 1 a.公司名称, count(distinct b.订单号码) as 采购次数, sum(c.单价 * c.数量 * (1 - c.折扣) as 采购总金额 from 客户 a inner join 订货主档 b inner join 订货明细 c on b.订单号码 = c.订单号码 on a.客户编号 = b.客户编号 where year(b.订单日期) = 1996 group by a.客户编号,a.公司名称 order by 3 desc8.15. 活用子查询(subquery)（高级内容）n 子查询：就是包含在某一个select、insert、update、delete命令中的select查询。n 在select、insert、update、delete命令中凡是允许是一个表达式的地方均可以包含子查询。n 子查询甚至可以再包含一个子查询。如：蓝色部分为子查询/* 脚本文件名: demo976.sql */use northwindsqlselect 订货主档.订单号码,订货主