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

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

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

3、_id, 姓名 = au_fname+au_lname, 电话号码 = phone, 住址 = address FROM authors或 （看看你更喜欢哪一种格式）/* 脚本文件名: */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任务：查询teacher表的姓名、性别和生日，列名用中文表

4、示；查询class表的所有班级信息；查询teacher表的教师姓名和年龄。8.4. 关键字ALL和DISTINCT的使用1.查询需求请列出“飞狐工作室”表中所有员工的雇用日期，但是日期相同者只列出一次即可。USE NorthwindSQLSELECT DISTINCT 雇用日期 FROM 飞狐工作室2.查询需求请列出“飞狐工作室”表中有哪些部门。USE NorthwindSQLSELECT DISTINCT 部门 FROM飞狐工作室ALL为默认项，显示所有查询到的记录，包括重复项。DISTINCT，对指定字段的内容相同的，仅显示一项。每个SELECT表达式只能有一个DISTINCT关键字。这意

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

6、称表中。查询员工表中的职称，并存储到新表员工职称表中。8.6. WHERE子句的条件搜索功能1.查询需求请列出“飞狐工作室”表中目前薪资大于60000的员工。/* 脚本文件名: */USE NorthwindSQLSELECT 姓名,目前薪资 FROM 飞狐工作室 WHERE 目前薪资 600002.查询需求请列出“飞狐工作室”表在信息部、行销部和业务部等3个部门任职的员工姓名。/* 脚本文件名: */USE NorthwindSQLSELECT 姓名,部门 FROM 飞狐工作室 WHERE 部门 IN (资讯部,行销部,业务部)3.查询需求请列出“飞狐工作室”表在信息部、行销部和业务部等3个

7、部门以外任职的员工姓名。/* 脚本文件名: */USE NorthwindSQLSELECT 姓名,部门 FROM 飞狐工作室 WHERE 部门 NOT IN (资讯部,行销部,业务部)4.查询需求请列出“飞狐工作室”表中，本月出生的员工姓名和出生日期。/* 脚本文件名: */USE NorthwindSQLSELECT 姓名,出生日期 FROM 飞狐工作室 WHERE MONTH(出生日期) = MONTH(GETDATE()5.查询需求请列出“飞狐工作室”表中，年龄大于20岁的每一位员工的姓名。/* 脚本文件名: */USE NorthwindSQLSELECT 姓名 FROM 飞狐工作室

8、 WHERE DATEDIFF(year,出生日期,GETDATE()20任务：查询章立民工作室中已婚的员工信息；查询“飞狐工作室”表中行销部的员工信息；查询06010111班或者07010211班的学生信息；查询成绩在80分以下的学生选课信息；查询年龄超过50岁的女教师信息；查询出生日期在1970到1980年之间的教师信息。8.7. SELECT的通配符共有5个通配符。*（星号） 用于字段列表，代表源表中的所有的字段/* 脚本文件名: */USE NorthwindSQL-连接两张表“客户”表和“订货主档”表SELECT * FROM 客户 INNER JOIN 订货主档 ON 客户.客户编

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

10、windSQLSELECT 姓名 FROM 飞狐工作室 WHERE 姓名 LIKE %光% -表示查询姓名中包含“光”字的记录任务：查询“飞狐工作室”表中住在北京市的的员工信息；查询教师表中姓杜的教师信息；查询professional表中专业名称包含计算机的专业信息。_（下划线） 只能用在WHERE子句中，代表1个字符。如：_A%代表第二个字符为A的字符串。 下划线通常与运算符LIKE搭配使用。 USE NorthwindSQL SELECT 姓名 FROM 飞狐工作室 WHERE 姓名 LIKE _建_（中括号） 只能用在WHERE子句中，用来限定任何一个单个字符介于指定的范围或集合中。 通

11、常与运算符LIKE搭配使用。/* 脚本文件名: */USE pubsSELECT au_fname,au_lname FROM authors WHERE au_lname LIKE P-Zinger -表示第一个字符为PZ之间且后五个字符为inger/* 脚本文件名: */USE NorthwindSQLSELECT 姓名 FROM 飞狐工作室 WHERE 姓名 LIKE ACD张李% -表示什么 ORDER BY 姓名任务：查询学号尾号为1、3、5、7、9的学生信息。（中括号中包含号） 只能用在WHERE子句中，用来限定任何一个单个字符不介于指定的范围或集合中。 通常与运算符LIKE搭配使

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

13、字符称为“转义符”。/* 脚本文件名: */USE NorthwindSQLSELECT 姓名, 家庭地址 FROM 飞狐工作室 WHERE家庭地址 LIKE %_% ESCAPE -通知符号后的字符并非通配符，即查询住址中带下划线的记录8.9. 连接条件设置本节内容太重要了！看如下代码，如果TableA有M条记录，TableB有N条记录，则查询结果共有MN条记录：SELECT * FROM TableA，TableB或SELECT * FROM TableA CROSS JOIN TableB为了避免出现上述情况，有4种连接类型可以选择： INNER JOIN特点：查询结果仅包含连接表中彼此

14、相对应的数据记录。/* 脚本文件名: 本例用于查看每一位客户的订货情况但是，并未下订单的客户不会出现在查询结果中！*/USE NorthwindSQLSELECT 客户.客户编号, 客户.公司名称, 客户.联系人, 客户.电话, 订货主档.订单号码, 订货主档.订单日期, 订货主档.要货日期, 订货主档.送货日期, 订货主档.送货方式, 订货主档.运费, 订货主档.收货人,订货主档.送货地址 FROM 客户 INNER JOIN 订货主档 ON 客户.客户编号 = 订货主档.客户编号更复杂的连接3个表的例子：/* 脚本文件名: */USE NorthwindSQLSELECT 客户.客户编号,

15、客户.公司名称,订货主档.订单号码, 订货主档.订单日期,订货明细.产品编号,订货明细.单价, 订货明细.数量, 订货明细.折扣 FROM 客户 INNER JOIN 订货主档 ON 客户.客户编号 = 订货主档.客户编号 INNER JOIN 订货明细 ON 订货主档.订单号码 = 订货明细.订单号码任务：查询下了订单的客户信息：客户编号、公司名称、订单号码、订单日期；查询产品信息：产品编号、类别名称；查询学生信息：学号、姓名、班级名称；查询班级信息：班级编号、班级名称、专业名称；查询专业信息：专业编号、专业名称、系名称；列出计算机工程系的专业信息；查询孙晓龙的所有选修课成绩；查询选修了“3

16、dsmax”课程的学生姓名和课程成绩。 LEFT OUTER JOIN（左外连接）特点：查询结果将包含位于关键字LEFT OUTER JOIN左侧源表中的所有数据记录，但是仅包含右侧源表中相应的数据记录。/* 脚本文件名: 本例用于查看每一位客户的订货情况但是希望并未下订单的客户也出现在查询结果中！*/USE NorthwindSQLSELECT 客户.客户编号, 客户.公司名称, 客户.连络人, 订货主档.订单号码, 订货主档.收货人 FROM 客户 LEFT OUTER JOIN 订货主档 ON 客户.客户编号 = 订货主档.客户编号（加上条件限制试试 WHERE订货主档.订单号码 is

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

18、 is NULL） FULL OUTER JOIN（全外连接）特点：查询结果将包含位于关键字FULL OUTER JOIN左右两侧源表中的所有数据记录。/* 脚本文件名: 给表指定别名*/USE NorthwindSQLSELECT a.客户编号, a.公司名称, a.连络人, a.电话, b.订单号码, b.订单日期, b.要货日期, b.送货日期, b.送货方式, b.运费, b.收货人, b.送货地址 FROM 客户 a INNER JOIN 订货主档 b ON a.客户编号 = b.客户编号小技巧：给表指定一个较短的别名，最大的好处是缩减了SELECT语句的长度。范例：1.查询在199

19、6年7月份采购的北京市客户的基本数据、订单数据、订货明细。/* 脚本文件名: */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.查询

20、出所有曾经在1996年订货的客户公司名称和所订购的产品明细/* 脚本文件名: */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年所下的订单，以及负责处理这些客户订单的员工基本数据：/* 脚本文件名: *

21、/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语句中。 COUNT 主要用于计算查询结果中的数据条数，通常用COUNT(*)的形式。 事实上，COUNT()是唯一允许使用通配符作为参数的聚合函数。1.计算出“飞狐工作室”表中的数据记录

22、条数/* 脚本文件名: */USE NorthwindSQLSELECT COUNT(*) FROM 飞狐工作室2.计算出“飞狐工作室”表中有多少员工住在北京市/* 脚本文件名: */USE NorthwindSQLSELECT COUNT(*) FROM 飞狐工作室 WHERE 住址 LIKE %北京市%3.计算出“飞狐工作室”表中有年龄介于3040之间的员工有多少/* 脚本文件名: */USE NorthwindSQLSELECT COUNT(*) FROM 飞狐工作室 WHERE DATEDIFF(yy,出生日期,GETDATE() BETWEEN 30 AND 404.查询出在1996

23、年7月份下订单的北京市客户有多少位/* 脚本文件名: */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 能够计算出最小值。1.查询出“飞狐工作室”表中，最低的目前薪资是多少。/* 脚本文件名: */USE NorthwindSQLSELECT MIN(目前薪资) AS 最低薪资 FROM 飞狐工作室注意：利用MIN查询出某一字段

24、的最小值时，并无法同时得知该字段为最小值的数据记录的其他字段内容。2.查询出“飞狐工作室”表中，年龄最大者是几岁（提供了三种方法）。/* 脚本文件名: */USE NorthwindSQLSELECT YEAR(GETDATE() - YEAR(MIN(出生日期) AS 最高年龄 FROM 飞狐工作室SELECT DATEDIFF(yyyy,MIN(出生日期),GETDATE() AS 最高年龄 FROM 飞狐工作室SELECT MAX(DATEDIFF(yyyy,出生日期,GETDATE() AS 最高年龄 FROM 飞狐工作室 MAX 能够计算出最大值。 AVG 能够计算出平均值。1.计算

25、出“飞狐工作室”表中员工的平均年龄/* 脚本文件名: */USE NorthwindSQLSELECT AVG(DATEDIFF(yyyy,出生日期,GETDATE() AS 平均年龄 FROM 飞狐工作室 SUM 求和。1.计算出所有客户的采购总金额/* 脚本文件名: */USE NorthwindSQLSELECT 客户采买总金额 = SUM(单价*数量*(1-折扣) FROM 订货明细聚合函数注意事项： 可以将查询结果存储到一个变量中/* 脚本文件名: */USE NorthwindSQLDECLARE AverageSalary money- 将计算所得的平均薪资储存至变数 Avera

26、geSalary 中SELECT AverageSalary = AVG(目前薪资) FROM 飞狐工作室/* STR (float_expression , length ,decimal ),返回由数字数据转换来的字符数据。*/PRINT 薪资大于平均薪资 +STR(AverageSalary,10,4)+ 的员工:PRINT REPLICATE(-,35)SELECT 姓名,目前薪资 FROM 飞狐工作室 WHERE 目前薪资 AverageSalary 同一SELECT语句中，可以分别使用不同的聚合函数/* 脚本文件名: */USE NorthwindSQLSELECT MAX(目前薪

27、资) AS 最高薪资, -看看这种设定列标题的方式 MIN(目前薪资) AS 最低薪资, AVG(目前薪资) AS 平均薪资, SUM(目前薪资) AS 薪资总额 FROM 飞狐工作室 聚合函数中允许包含DISTINCT关键字，用于排除重复行。1.计算出1996年8月份的发票开给了几位客户。/* 脚本文件名: */USE NorthwindSQLSELECT COUNT(DISTINCT 客户编号) AS 客户数目 FROM 订货主档 WHERE 订单日期 BETWEEN 08/01/1996 AND 08/31/1996任务：统计teacher表中职称为讲师的教师人数；查询学生的入学平均分数

28、、最低分、最高分；查询学号为01的学生选课门数；查询学生张劲的选课门数；查询学生张劲选修课程的最高分、最低分、平均分。8.11. 数据分组小计利用GROUP BY子句，可以根据一个或多个组的值将查询中的数据记录分组。1.计算出员工“飞狐工作室”表中，各个部门的薪资最高值、薪资最小值、薪资平均值及人数。/* 脚本文件名: */USE NorthwindSQLSELECT 部门, COUNT(*) AS 部门员工人数, MAX(目前薪资) AS 部门最高薪资, MIN(目前薪资) AS 部门最低薪资, AVG(目前薪资) AS 部门平均薪资 FROM 飞狐工作室 GROUP BY 部门任务：统计t

29、eacher表中各类职称的教师人数；统计各课程的最高分、最低分、平均分。2.计算出各个客户的采购次数和采购总金额。/* 脚本文件名: */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.客户编号,a.公司名称3.查询出每一位业务人员的销售总数量。/* 脚本文件名: */USE NorthwindSQLSELECT a.员工编号,a.姓名, SUM(c.数量) AS 销售总数 FROM 员工 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c ON b.订单号码 = c.订单号码 ON a.员工编号 = b.员工编号 GROUP BY a.员工编号,a.姓名4.查询出每一位业务人员的销售总数，同时列出那些比他或她销售还多的业务员数及平均销售数目。/* 脚本文件名: */EXEC sp_dboption NorthwindSQL,select into/bulkcopy,TRUE -设为true能加快select i