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

《SQL数据库数据的查询、汇总 、统计和分析.pdf》由会员分享，可在线阅读，更多相关《SQL数据库数据的查询、汇总 、统计和分析.pdf（32页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、8.8.数据的查询、汇总数据的查询、汇总、统计和分析、统计和分析本章主题SELECT 语句简单的 SELECT 查询设置查询结果的字段名关键字 ALL 和 DISTINCT 的使用查询结果的输出目的地WHERE 子句的条件搜索功能SELECT 的通配符字符和通配符冲突时的解决方法连接条件设置统计运算的高手：聚合函数数据分组小计HAVING 子句的使用ORDER BY 子句的使用查询名列前茅或落后者活用子查询（SubQuery）精彩内容不容错过！这一章是本课程的重点！精彩内容不容错过！这一章是本课程的重点！本章将深入剖析 SELECT 命令。8.1.8.1.SELECTSELECT 命令命令SE

2、LECT 是一个用来从一个或多个表中获取数据的 SQL 命令。1沙洲职业工学院电子信息工程系第 1 页 共 32页v1.0 可编辑可修改8.2.8.2.简单的简单的 SELECTSELECT 查询查询如：USE NorthwindSQLSELECT 身份证号码,姓名,电话号码-FROM 飞狐工作室SELECT 命令至少包含：要出现在查询结果中的字段列表，如：身份证号码，姓名，电话号码字段来自哪些表，如：FROM 飞狐工作室字段列表也可以是由字段、常量和函数组成的表达式由字段、常量和函数组成的表达式要列出所有字段，只须用要列出所有字段，只须用*号号这里是字段列表8.3.8.3.设置查询结果的字段

3、名设置查询结果的字段名查查 询询 需需 求求请从数据库 pubs 的 authors 表，查询出所有作者的代号、姓名、电话号码及住址，而且请使用中文文字作为查询结果的各字段名。解解 答答/*脚本文件名:*/USE pubsSELECT 作者代号作者代号=au_id,姓名姓名=au_fname+au_lname,电话号码电话号码=phone,住址住址=address FROM authors2沙洲职业工学院电子信息工程系第 2 页 共 32页v1.0 可编辑可修改或或（看看你更喜欢哪一种格式）（看看你更喜欢哪一种格式）/*脚本文件名:*/USE pubsSELECT au_idASAS 作者代号

4、作者代号,au_fname+au_lnameASAS 姓名姓名,phoneASAS 电话号码电话号码,addressASAS 住址住址 FROM authors注意：如果您设置的的字段名包含空格字段名包含空格，则须加上单引号SELECTSELECT au_fname+au_lnameASASName of AuthorName of AuthorFROMFROM authors任务：任务：查询 teacher 表的姓名、性别和生日，列名用中文表示；查询 class 表的所有班级信息；查询 teacher 表的教师姓名和年龄。8.4.8.4.关键字关键字 ALLALL 和和 DISTINCTDI

5、STINCT 的使用的使用1.查询需求请列出“飞狐工作室”表中所有员工的雇用日期，但是日期相同者只列出一次只列出一次即可。USEUSE NorthwindSQLSELECTSELECT DISTINCTDISTINCT 雇用日期 FROMFROM 飞狐工作室2.查询需求请列出“飞狐工作室”表中有哪些部门。3沙洲职业工学院电子信息工程系第 3 页 共 32页v1.0 可编辑可修改USEUSE NorthwindSQLSELECTSELECT DISTINCTDISTINCT 部门 FROM 飞狐工作室ALLALL 为默认项，显示所有查询到的记录，包括重复项。为默认项，显示所有查询到的记录，包括重

6、复项。DISTINCTDISTINCT，对指定字段的内容相同的，仅显示一项。每个，对指定字段的内容相同的，仅显示一项。每个 SELECTSELECT 表达式只能有一个表达式只能有一个DISTINCTDISTINCT 关键字。这意味着，关键字。这意味着，DISTINCTDISTINCT 是限制整条数据记录都重复者，只显示其中一条，是限制整条数据记录都重复者，只显示其中一条，而不是针对单一字段来处理。而不是针对单一字段来处理。任务：任务：请列出“章立民工作室”表中有哪些部门。列出 teacher 表中的教师职称。8.5.8.5.查询结果的输出目的地查询结果的输出目的地为什么要讨论查询结果的输出目的

7、地为什么要讨论查询结果的输出目的地答：可能希望将查询结果输出到某个存储处以便进行进一步的处理。举例说明 INTO 子句的用法：将查询结果存储到当前数据库中的新表 MyTmpTable 中USEUSE NorthwindSQLSELECTSELECT *INTOINTO MyTmpTableFROMFROM 飞狐工作室注：INTO 子句会生成相应的表，如果表已存在则提示错误“表已存在”。任务：任务：把 teacher 表中的教师职称保存到新表教师职称表中。查询员工表中的职称，并存储到新表员工职称表中。4沙洲职业工学院电子信息工程系第 4 页 共 32页8.6.8.6.WHEREWHERE 子句的

8、条件搜索功能子句的条件搜索功能1.查询需求请列出“飞狐工作室”表中目前薪资大于 60000 的员工。/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT 姓名,目前薪资 FROMFROM 飞狐工作室WHEREWHERE 目前薪资 600002.查询需求请列出“飞狐工作室”表在信息部、行销部和业务部等 3 个部门任职的员工姓名。/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT 姓名,部门 FROMFROM 飞狐工作室 WHERE WHERE 部门部门 IN(IN(资讯部资讯部,行销部行销部,业务部业务部)3.查询需求请列出“飞狐工作

9、室”表在信息部、行销部和业务部等 3 3 个部门以外个部门以外任职的员工姓名。/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT 姓名,部门 FROMFROM 飞狐工作室 WHERE WHERE 部门部门 NOT IN(NOT IN(资讯部资讯部,行销部行销部,业务部业务部)4.查询需求请列出“飞狐工作室”表中，本月出生的员工姓名和出生日期。/*脚本文件名:*/5沙洲职业工学院电子信息工程系第 5 页 共 32页v1.0 可编辑可修改USE NorthwindSQLSELECT 姓名,出生日期 FROM 飞狐工作室 WHERE MONTH(WHERE MONTH

10、(出生日期出生日期)=MONTH(GETDATE()=MONTH(GETDATE()5.查询需求请列出“飞狐工作室”表中，年龄大于 20 岁的每一位员工的姓名。/*脚本文件名:*/USE NorthwindSQLSELECT 姓名 FROM 飞狐工作室WHEREWHERE DATEDIFF(year,DATEDIFF(year,出生日期出生日期,GETDATE()20,GETDATE()20任务：任务：查询章立民工作室中已婚的员工信息；查询“飞狐工作室”表中行销部的员工信息；查询 06010111 班或者 07010211 班的学生信息；查询成绩在 80 分以下的学生选课信息；查询年龄超过 5

11、0 岁的女教师信息；查询出生日期在 1970 到 1980 年之间的教师信息。8.7.8.7.SELECTSELECT 的通配符的通配符共有 5 个通配符。*（星星 号号）用于字段列表，代表源表中的所有的字段代表源表中的所有的字段/*脚本文件名:*/USE NorthwindSQL-连接连接两张表“客户”表和“订货主档”表SELECT*6沙洲职业工学院电子信息工程系第 6 页 共 32页v1.0 可编辑可修改 FROM 客户客户 INNERINNER JOINJOIN 订货主档订货主档ONON 客户.客户编号=订货主档.客户编号 WHERE 订货主档.订单日期 BETWEEN 08/01/19

12、96 AND 08/31/1996/*脚本文件名:*/USE NorthwindSQLSELECT 客户.公司名称,订货主档订货主档.*.*FROM 客户客户 INNERINNER JOINJOIN 订货主档订货主档ONON 客户.客户编号=订货主档.客户编号 WHERE 订货主档.订单日期 BETWEEN 08/01/1996 AND 08/31/1996%（百百 分分 号号）只能用在 WHERE 子句中，代表代表 0 0 个或个或 0 0 个以上的字符个以上的字符。如：如：ABC%ABC%代表代表 ABCABC 开头的字符串。开头的字符串。百分号通常与运算符百分号通常与运算符 LIKELI

13、KE 搭配使用搭配使用。/*脚本文件名:*/USE NorthwindSQLSELECT 姓名 FROM 飞狐工作室WHEREWHERE 姓名姓名 LIKE%LIKE%光光%-表示查询姓名中包含“光”字的记录任务：任务：查询“飞狐工作室”表中住在北京市的的员工信息；查询教师表中姓杜的教师信息；查询 professional 表中专业名称包含计算机的专业信息。_ _（下下 划划 线线）7沙洲职业工学院电子信息工程系第 7 页 共 32页v1.0 可编辑可修改只能用在 WHERE 子句中，代表代表 1 1 个字符个字符。如：如：_A%_A%代表第二个字符为代表第二个字符为 A A 的字符串。的字符

14、串。下划线通常与运算符下划线通常与运算符 LIKELIKE 搭配使用搭配使用。USE NorthwindSQLSELECT 姓名 FROM 飞狐工作室 WHERE 姓名 LIKE _建_ （中中 括括 号号）只能用在 WHERE 子句中，用来限定任何一个单个字符介于指定的范围或集合中。用来限定任何一个单个字符介于指定的范围或集合中。通常与运算符 LIKE 搭配使用。/*脚本文件名:*/USE pubsSELECT au_fname,au_lname FROM authors WHERE au_lname LIKE P-ZingerLIKE P-Zinger-表示第一个字符为 PZ 之间且后五个

15、字符为 inger/*脚本文件名:*/USE NorthwindSQLSELECT 姓名 FROM 飞狐工作室 WHERE 姓名 LIKE ACDLIKE ACD 张李张李%-表示什么 ORDER BY 姓名任务：任务：查询学号尾号为 1、3、5、7、9 的学生信息。8沙洲职业工学院电子信息工程系第 8 页 共 32页v1.0 可编辑可修改 （中中 括括 号号 中中 包包 含含 号号）只能用在 WHERE 子句中，用来限定任何一个单个字符不介于指定的范围或集合中用来限定任何一个单个字符不介于指定的范围或集合中。通常与运算符 LIKE 搭配使用。/*脚本文件名:*/USE pubsSELECT

16、au_fname,au_lname FROM authors WHERE au_fname LIKE H-Kichel LIKE H-Kichel-首字母不介于 HK 之间且后为 ichel/*脚本文件名:*/USE NorthwindSQLSELECT 身份证号码,姓名 FROM 飞狐工作室 WHERE 身份证号码 LIKE ALM%LIKE ALM%-这个表示什么意思任务：任务：查询学号尾号不为 1、3、5、7、9 的学生信息。8.8.8.8.字符和通配符冲突时的解决方法字符和通配符冲突时的解决方法可以使用 ESCAPE 子句通知 SQL Server 哪一个字符是常量字符而并非通配符。用

17、专业术语来说，ESCAPEESCAPE 子句子句所指定的字符称为“转义符转义符”。/*脚本文件名:*/USE NorthwindSQL9沙洲职业工学院电子信息工程系第 9 页 共 32页v1.0 可编辑可修改SELECT 姓名,家庭地址 FROM 飞狐工作室 WHERE 家庭地址 LIKE%_%ESCAPE LIKE%_%ESCAPE-通知符号后的字符并非通配符，即查询住址中带下划线带下划线的记录8.9.8.9.连接条件设置连接条件设置本节内容太重要了！本节内容太重要了！看如下代码，如果 TableA 有 M 条记录，TableB 有 N 条记录，则查询结果共有 MN 条记录：SELECT*F

18、ROM TableA，TableB或SELECT*FROMTableACROSS JOINCROSS JOIN TableB为了避免出现上述情况，有为了避免出现上述情况，有 4 4 种连接类型可以选择：种连接类型可以选择：INNER JOIN特点：特点：查询结果仅包含连接表中彼此相对应彼此相对应的数据记录。/*脚本文件名:本例用于查看每一位客户的订货情况但是，并未下订单的客户不会出现在查询结果中！*/USE NorthwindSQLSELECTSELECT 客户.客户编号,客户.公司名称,客户.联系人,客户.电话,订货主档.订单号码,订货主档.订单日期,订货主档.要货日期,订货主档.送货日期,

19、订货主档.送货方式,订货主档.运费,订货主档.收货人,订货主档.送货地址FROMFROM 客户 INNER JOININNER JOIN 订货主档ONON 客户.客户编号=订货主档.客户编号10沙洲职业工学院电子信息工程系第 10 页 共 32页v1.0 可编辑可修改更复杂的连接更复杂的连接3 3 个表的例子：个表的例子：/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT 客户.客户编号,客户.公司名称,订货主档.订单号码,订货主档.订单日期,订货明细.产品编号,订货明细.单价,订货明细.数量,订货明细.折扣FROMFROM 客户 INNER JOININNER

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

21、R JOIN 左侧源表中的所有数据左侧源表中的所有数据记录，但是仅仅包含右侧源表中相应的数据记录包含右侧源表中相应的数据记录。/*脚本文件名:本例用于查看每一位客户的订货情况但是希望并未下订单的客户也出现在查询结果中！*/USEUSE NorthwindSQL11沙洲职业工学院电子信息工程系第 11 页 共 32页v1.0 可编辑可修改SELECTSELECT 客户.客户编号,客户.公司名称,客户.连络人,订货主档.订单号码,订货主档.收货人FROMFROM 客户 LEFT OUTER JOINLEFT OUTER JOIN 订货主档ONON 客户.客户编号=订货主档.客户编号（加上条件限制试

22、试（加上条件限制试试 WHERE WHERE 订货主档订货主档.订单号码订单号码 is NULL is NULL）RIGHT OUTER JOIN（右外连接）特点：特点：查询结果将包含位于关键字 RIGHT OUTER JOIN 右侧源表中的所有数据右侧源表中的所有数据记录，但是仅包含仅包含左侧源表中相应的数据记录左侧源表中相应的数据记录。/*脚本文件名:查看每一种产品的销售情况但是希望那些没有人订购的产品数据也在查询结果中出现*/USEUSE NorthwindSQLSELECTSELECT 订货明细.订单号码,订货明细.单价,订货明细.数量,订货明细.折扣,产品资料.产品编号,产品资料.产

23、品FROMFROM 订货明细 RIGHT OUTER JOINRIGHT OUTER JOIN 产品资料ONON 订货明细.产品编号=产品资料.产品编号（加上条件限制试试（加上条件限制试试 WHERE WHERE 订货明细订货明细.订单号码订单号码 is NULL is NULL）FULL OUTER JOIN（全外连接）特点：特点：查询结果将包含位于关键字 FULL OUTER JOIN 左右两侧源表中的所有数据记录左右两侧源表中的所有数据记录。12沙洲职业工学院电子信息工程系第 12 页 共 32页/*脚本文件名:给表指定别名给表指定别名*/USEUSE NorthwindSQLSELEC

24、TSELECT a.客户编号,a.公司名称,a.连络人,a.电话,b.订单号码,b.订单日期,b.要货日期,b.送货日期,b.送货方式,b.运费,b.收货人,b.送货地址FROMFROM 客户 a aINNERINNER JOINJOIN 订货主档 b bONONa.a.客户编号=b.b.客户编号小技巧：给表指定一个较短的别名，最大的好处是缩减了 SELECT 语句的长度。范例：范例：1.查询在 1996 年 7 月份采购的北京市客户的基本数据、订单数据、订货明细。/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT a.客户编号,a.公司名称,a.地址,a.连络

25、人,a.电话,b.订单号码,b.订单日期,b.运费,b.收货人,c.产品编号,c.单价,c.数量,c.折扣 FROM FROM 客户 aINNER JOININNER JOIN 订货主档 bINNER JOININNER JOIN 订货明细 cONON b.订单号码=c.订单号码ONON a.客户编号=b.客户编号WHEREWHERE a.地址 LIKELIKE%北京市%ANDAND b.订单日期 BETWEENBETWEEN 07/01/1996ANDAND 07/31/199613沙洲职业工学院电子信息工程系第 13 页 共 32页2.查询出所有曾经在 1996 年订货的客户公司名称和所订

26、购的产品明细/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT a.公司名称,b.订单日期,d.*FROM FROM 客户 a INNER JOIN INNER JOIN 订货主档 bINNER JOININNER JOIN 订货明细 cINNER JOININNER JOIN 产品资料 dONON d.产品编号=c.产品编号ONON b.订单号码=c.订单号码ONON a.客户编号=b.客户编号WHEREWHERE YEAR(b.订单日期)=19963.假如 ERNSH 公司是我们的忠实客户，我们要查询出该公司在 1998 年所下的订单，以及负责处理这些客户订

27、单的员工基本数据：/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT a.公司名称,b.订单号码,b.订单日期,c.*FROMFROM 客户 aINNER JOININNER JOIN 订货主档 bINNER JOININNER JOIN 员工 cONON c.员工编号=b.员工编号ONON a.客户编号=b.客户编号WHEREWHERE a.客户编号=ERNSHANDAND YEAR(b.订单日期)=199814沙洲职业工学院电子信息工程系第 14 页 共 32页v1.0 可编辑可修改8.10.8.10.统计运算的高手：聚合函数统计运算的高手：聚合函数聚合函数

28、专用于聚合函数专用于 SELECTSELECT 语句中。语句中。COUNT主要用于计算查询结果中的数据条数，通常用 COUNT(*)的形式。事实上，COUNT()是唯一允许使用通配符作为参数的聚合函数。1.计算出“飞狐工作室”表中的数据记录条数/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECTCOUNTCOUNT(*)FROMFROM 飞狐工作室2.计算出“飞狐工作室”表中有多少员工住在北京市/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECTCOUNTCOUNT(*)FROMFROM 飞狐工作室WHEREWHERE 住址 LIKE

29、LIKE%北京市%3.计算出“飞狐工作室”表中有年龄介于 3040 之间的员工有多少/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECTCOUNTCOUNT(*)FROM FROM 飞狐工作室WHEREWHERE DATEDIFF(yy,出生日期,GETDATE()BETWEEN 30 AND 404.查询出在 1996 年 7 月份下订单的北京市客户有多少位15沙洲职业工学院电子信息工程系第 15 页 共 32页v1.0 可编辑可修改/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT COUNTCOUNT(*)ASAS 客户数目F

30、ROMFROM 客户 aINNER JOININNER JOIN 订货主档 bONON a.客户编号=b.客户编号WHEREWHERE a.地址 LIKELIKE%北京市%ANDAND b.订单日期 BETWEENBETWEEN 07/01/1996 ANDAND 07/31/1996MIN能够计算出最小值。1.查询出“飞狐工作室”表中，最低的目前薪资是多少。/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECTMINMIN(目前薪资)ASAS 最低薪资FROMFROM 飞狐工作室注意：利用 MIN 查询出某一字段的最小值时，并无法同时得知该字段为最小值的数据记录的

31、其他字段内容。2.查询出“飞狐工作室”表中，年龄最大者是几岁（提供了三种方法）（提供了三种方法）。/*脚本文件名:*/USEUSE NorthwindSQL16沙洲职业工学院电子信息工程系第 16 页 共 32页v1.0 可编辑可修改SELECTSELECT YEAR(GETDATE()-YEAR(MINMIN(出生日期)ASAS 最高年龄FROMFROM 飞狐工作室SELECTSELECT DATEDIFF(yyyy,MINMIN(出生日期),GETDATE()ASAS 最高年龄FROMFROM 飞狐工作室SELECTSELECT MAXMAX(DATEDIFF(yyyy,出生日期,GETD

32、ATE()ASAS 最高年龄FROMFROM 飞狐工作室MAX能够计算出最大值。AVG能够计算出平均值。1.计算出“飞狐工作室”表中员工的平均年龄/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECTAVGAVG(DATEDIFF(yyyy,出生日期,GETDATE()ASAS 平均年龄FROMFROM飞狐工作室SUM求和。1.计算出所有客户的采购总金额/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT 客户采买总金额=17沙洲职业工学院电子信息工程系第 17 页 共 32页v1.0 可编辑可修改SUMSUM(单价*数量*(1-折扣)

33、FROMFROM 订货明细聚合函数注意事项：聚合函数注意事项：可以将查询结果存储到一个变量中/*脚本文件名:*/USEUSE NorthwindSQLDECLAREDECLARE AverageSalary money-将计算所得的平均薪资储存至变数 AverageSalary 中SELECTSELECT AverageSalary=AVGAVG(目前薪资)FROMFROM 飞狐工作室/*STR(float_expression ,length ,decimal ),STR(float_expression ,length ,decimal ),返回由数字数据转换来的字符数据。*/PRINTP

34、RINT 薪资大于平均薪资+STRSTR(AverageSalary,10,4)+的员工:PRINTPRINT REPLICATEREPLICATE(-,35)SELECTSELECT 姓名,目前薪资 FROMFROM 飞狐工作室WHEREWHERE 目前薪资 AverageSalary同一 SELECT 语句中，可以分别使用不同的聚合函数/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT MAX(目前薪资)ASAS 最高薪资,-看看这种设定列标题的方式MIN(目前薪资)ASAS 最低薪资,18沙洲职业工学院电子信息工程系第 18 页 共 32页v1.0 可编辑

35、可修改AVG(目前薪资)ASAS 平均薪资,SUM(目前薪资)ASAS 薪资总额FROMFROM 飞狐工作室聚合函数中允许包含 DISTINCTDISTINCT 关键字，用于排除重复行。1.计算出 1996 年 8 月份的发票开给了几位客户。/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT COUNT(DISTINCTDISTINCT 客户编号)ASAS 客户数目FROMFROM 订货主档WHEREWHERE 订单日期 BETWEENBETWEEN 08/01/1996 ANDAND 08/31/1996任务：任务：统计 teacher 表中职称为讲师的教师人

36、数；查询学生的入学平均分数、最低分、最高分；查询学号为01的学生选课门数；查询学生张劲的选课门数；查询学生张劲选修课程的最高分、最低分、平均分。8.11.8.11.数据分组小计数据分组小计利用 GROUP BYGROUP BY 子句，可以根据一个或多个组的值将查询中的数据记录分组。1.计算出员工“飞狐工作室”表中，各个部门的薪资最高值、薪资最小值、薪资平均值及人数。/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT 部门部门,COUNTCOUNT(*)AS 部门员工人数,19沙洲职业工学院电子信息工程系第 19 页 共 32页v1.0 可编辑可修改MAXMAX(

37、目前薪资)ASAS 部门最高薪资,MINMIN(目前薪资)ASAS 部门最低薪资,AVGAVG(目前薪资)ASAS 部门平均薪资FROMFROM 飞狐工作室GROUPGROUP BYBY 部门任务：任务：统计 teacher 表中各类职称的教师人数；统计各课程的最高分、最低分、平均分。2.计算出各个客户的采购次数和采购总金额。/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT a.公司名称,COUNTCOUNT(DISTINCTDISTINCT b.订单号码)ASAS 采购次数,SUMSUM(c.单价*c.数量*(1-c.折扣)ASAS 采购总金额FROMFRO

38、M 客户 aINNER JOININNER JOIN 订货主档 bINNERINNER JOINJOIN 订货明细 cONON b.订单号码=c.订单号码ONON a.客户编号=b.客户编号GROUPGROUP BYBY a.客户编号,a.公司名称3.查询出每一位业务人员的销售总数量。/*脚本文件名:*/USEUSE NorthwindSQLSELECTSELECT a.员工编号,a.,a.姓名姓名,20沙洲职业工学院电子信息工程系第 20 页 共 32页 SUM(c.数量)ASAS 销售总数FROMFROM 员工 aINNERINNER JOINJOIN 订货主档 bINNERINNER J

39、OINJOIN 订货明细 cONON b.订单号码=c.订单号码ONON a.员工编号=b.员工编号GROUPGROUP BYBY a.员工编号,a.,a.姓名姓名4.查询出每一位业务人员的销售总数，同时列出那些比他或她销售还多的业务员数及及平均销售数目。/*脚本文件名:*/EXECEXEC sp_dboption NorthwindSQL,select into/bulkcopy,TRUE-设为 true 能加快 select into 的处理速度USEUSE NorthwindSQL-首先，生成一张 aMount 表，包含每一位业务人员的销售总数SELECTSELECT a.员工编号,a.

40、姓名,SUM(c.数量)AS 销售总数 INTO aMountINTO aMountFROMFROM 员工 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c ON b.订单号码=c.订单号码 ON a.员工编号=b.员工编号GROUPGROUP BYBY a.员工编号,a.姓名SELECTSELECT a.员工编号,a.a.姓名姓名,a.,a.销售总数销售总数,COUNT(b.员工编号)AS 业绩比此人好之业务员的人数,21沙洲职业工学院电子信息工程系第 21 页 共 32页 AVG(b.销售总数)AS 业绩比此人好之业务员的平均销售总数FROMFROM aMount

41、 a inner joininner join aMount bONON a.销售总数 30000-不可以用 HAVINGHAVING 部门的平均薪资部门的平均薪资 30000任务：任务：查询选修了选修 4 门以上（包括 4 门）选修课的学生；查询订单数查过 10 张的客户。2.查询出“飞狐工作室”表中，有哪些员工的姓名是跟别人相同的，而且同样采用某一姓名者又有多少人。/*脚本文件名:*/USE NorthwindSQLSELECTSELECT 姓名 AS 重复的姓名,COUNT(*)AS 重复的人数FROMFROM 飞狐工作室GROUPGROUP BYBY 姓名 Having COUNT(*

42、)1 Having COUNT(*)13.计算出订单超过 10 张的各家客户的订单数目，及其采购总金额。/*脚本文件名:*/USE NorthwindSQLSELECTSELECT a.公司名称,COUNT(DISTINCT b.订单号码)AS 订单张数,SUM(c.单价*c.数量*(1-c.折扣)AS 采购总金额FROMFROM 客户 aINNERINNER JOIN 订货主档 b24沙洲职业工学院电子信息工程系第 24 页 共 32页INNERINNER JOIN 订货明细 cONON b.订单号码=c.订单号码ONON a.客户编号=b.客户编号GROUPGROUP BYBY a.公司名

43、称HAVING COUNT(DISTINCT b.HAVING COUNT(DISTINCT b.订单号码订单号码)10)108.13.8.13.ORDER BYORDER BY 子句的使用子句的使用利用 ORDER BY 子句，可以根据一个或多个字段的数据排序查询出的结果。1.按照薪资所得的高低顺序，列出“飞狐工作室”表中所有员工的姓名、性别和薪资。/*脚本文件名:*/USE NorthwindSQLSELECTSELECT 姓名,性别=CASE 性别 WHEN 0 THEN 女 WHEN 1 THEN 男 END,目前薪资FROMFROM 飞狐工作室ORDERORDER BYBY 目前薪资

44、 DESCDESC司名称,COUNT(DISTINCTDISTINCT b.订单号码)AS 采购次数采购次数,SUM(c.单价*c.数量*(1-c.折扣)AS 采购总金额采购总金额FROMFROM 客户 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c ON b.订单号码=c.订单号码25沙洲职业工学院电子信息工程系第 25 页 共 32页 ON a.客户编号=b.客户编号GROUPGROUP BYBY a.公司名称ORDERORDER BYBY 采购次数采购次数 DESC,采购总金额采购总金额8.14.8.14.查询名列前茅或落后者查询名列前茅或落后者先利用 ORD

45、ER BYORDER BY排序，然后利用关键字 TOP n PERCENTTOP n PERCENT来取出从头算起的前几名或从头算起的多少百分比。1.查询出“飞狐工作室”中，薪资最低员工的姓名及其薪资。/*脚本文件名:*/USE NorthwindSQLSELECTSELECT TOP 1TOP 1姓名 AS 薪资最低之员工姓名,目前薪资FROMFROM 飞狐工作室ORDERORDER BYBY 目前薪资2.查询出“飞狐工作室”中，薪资最高员工的姓名及其薪资。3.查询出“飞狐工作室”中，薪资最高的前 10 名员工的姓名及其薪资。4.查询出“飞狐工作室”中，薪资倒数前 10 名员工的姓名及其薪资

46、。5.查询出“飞狐工作室”中，部门的平均薪资最高的前 3 个部门。/*脚本文件名:*/USE NorthwindSQLSELECTSELECT TOP 3部门 AS 平均薪资最高的前三个部门,AVG(目前薪资)AS 平均薪资26沙洲职业工学院电子信息工程系第 26 页 共 32页v1.0 可编辑可修改FROMFROM 飞狐工作室GROUPGROUP BYBY 部门ORDERORDER BYBY 2 DESC6.查询出“飞狐工作室”中，目前薪资最高的前 10的员工。USE NorthwindSQLSELECT TOP 10 PERCENT姓名 ,目前薪资 FROM 飞狐工作室 ORDER BY

47、目前薪资任务：任务：查询入学成绩排名前 10 的学生。7.查询出销售业绩最佳的前 3 名业务员。/*脚本文件名:*/USE NorthwindSQLSELECTSELECT TOP 3 a.员工编号,a.姓名 AS 业务员姓名,SUM(c.单价*c.数量*(1-c.折扣)AS 销售总金额FROMFROM 员工 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c ON b.订单号码=c.订单号码27沙洲职业工学院电子信息工程系第 27 页 共 32页v1.0 可编辑可修改 ON a.员工编号=b.员工编号GROUPGROUP BYBY a.员工编号,a.姓名ORDEROR

48、DER BYBY 3 DESC8.查询出在 1996 年采购总金额最高的客户（并显示客户的采购次数）。/*脚本文件名:*/USE NorthwindSQLSELECTSELECT TOP 1 a.公司名称,COUNT(DISTINCT b.订单号码)AS 采购次数,SUM(c.单价*c.数量*(1-c.折扣)AS 采购总金额FROMFROM 客户 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c ON b.订单号码=c.订单号码 ON a.客户编号=b.客户编号WHEREWHERE YEAR(b.订单日期)=1996GROUPGROUP BYBY a.客户编号,a.公

49、司名称ORDERORDER BYBY 3 DESC8.15.8.15.活用子查询活用子查询(SubQuery)(SubQuery)（高级内容）（高级内容）子查询：就是包含在某一个 SELECT、INSERT、UPDATE、DELETE 命令中的 SELECT 查询。在 SELECT、INSERT、UPDATE、DELETE 命令中凡是允许是一个表达式的地方凡是允许是一个表达式的地方均可以包含子查询。子查询甚至可以再包含一个子查询。如：蓝色部分为子查询/*脚本文件名:*/28沙洲职业工学院电子信息工程系第 28 页 共 32页USE NorthwindSQLSELECT 订货主档.订单号码,订货

50、主档.订单日期,(SELECT MAX(SELECT MAX(订货明细订货明细.单价单价)FROM FROM 订货明细订货明细 WHERE WHERE 订货主档订货主档.订单号码订单号码=订货明细订货明细.订单号码订单号码)ASAS 最高单价最高单价 FROM 订货主档1.本例以子查询的返回值作为 WHERE 子句的条件值，以便查询哪些员工的薪资和公司中最低薪资人员相同。/*脚本文件名:*/USE NorthwindSQLSELECT a.姓名,a.目前薪资FROM 飞狐工作室 aWHERE a.目前薪资=(SELECT MIN(b.(SELECT MIN(b.目前薪资目前薪资)FROM)FR