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） l 自连接（Self-Joins） l 合并查询结果 l 本章命令一缆表
精彩内容不容错过！这一章内容最重要了！！★★★ |
本章将深入剖析SELECT命令。
准备工作
准备工作：在示例代码ch8目录中，数据库(NorthwindSQL)
文件名：NorthwindSQL.mdf、NorthwindSQL.ldf
8.1. SELECT命令
SELECT是一个用来从一个或多个表中获取数据的SQL命令。
8.2. 简单的SELECT查询
如：
USE NorthwindSQL
SELECT身份证号码，姓名，电话号码 // ß这里是字段列表
FROM飞狐工作室
SELECT命令至少包含：
Ø 要出现在查询结果中的字段列表，如：身份证号码，姓名，电话号码
Ø 字段来自哪些表，如：FROM飞狐工作室
Ø 字段列表也可以是由字段、常量和函数组成的表达式
Ø 要列出所有字段，只须用 * 号
8.3. 设置查询结果的字段名
查询需求
请从数据库pubs的authors表，查询出所有作者的代号、姓名、电话号码及住址，而且请使用中文文字作为查询结果的各字段名。
解答
/* 档案名称: Demo91.sql */
USE pubs
SELECT 作者代号 = au_id,
姓名 = au_fname+au_lname,
电话号码 = phone,
住址 = address
FROM authors
或（看看你更喜欢哪一种格式？）
/* 档案名称: Demo92.sql */
USE pubs
SELECT au_id AS 作者代号,
au_fname+au_lname AS 姓名,
phone AS 电话号码,
address AS 住址
FROM authors
注意：
Ø 如果您设置的的字段名包含空格，则须加上单引号
SELECT au_fname+au_lname AS ‘Name of Author’
FROM authors
8.4. 关键字ALL和DISTINCT[w1] 的使用
1. 查询需求
请列出“飞狐工作室”表中所有员工的雇用日期，但是日期相同者只列出一次即可。
USE NorthwindSQL
SELECT DISTINCT 雇用日期 FROM飞狐工作室
2. 查询需求
请列出“飞狐工作室”表中有哪些部门。
USE NorthwindSQL
SELECT DISTINCT 部门 FROM飞狐工作室
――一个问题，下面语句代表什么意思？？？
SELECT DISTINCT 姓名，部门 FROM飞狐工作室
8.5. 查询结果的输出目的地
为什么要讨论查询结果的输出目的地？
答：可能希望将查询结果输出到某个存储处以便进行进一步的处理。
举例说明INTO子句的用法：
Ø 将查询结果存储到当前数据库中的新表MyTmpTable中
USE NorthwindSQL
SELECT * INTO MhyTmpTable FROM 飞狐工作室
8.6. WHERE子句的条件搜索功能
1. 查询需求
请列出“飞狐工作室”表中目前薪资大于60000的员工。
/* 档案名称: Demo93.sql */
USE NorthwindSQL
SELECT姓名,目前薪资 FROM飞狐工作室
WHERE 目前薪资 > 60000
2. 查询需求
请列出“飞狐工作室”表在信息部、行销部和业务部等3个部门任职的员工姓名。
/* 档案名称: Demo94.sql */
USE NorthwindSQL
SELECT 姓名,部门 FROM 飞狐工作室
WHERE 部门 IN ('资讯部','行销部','业务部')
3. 查询需求
请列出“飞狐工作室”表在信息部、行销部和业务部等3个部门以外任职的员工姓名。
/* 档案名称: Demo95.sql */
USE NorthwindSQL
SELECT 姓名,部门 FROM 飞狐工作室
WHERE 部门 NOT IN ('资讯部','行销部','业务部')
4. 查询需求
请列出“飞狐工作室”表中，本月出生的员工姓名和出生日期。
/* 档案名称: Demo910.sql */
USE NorthwindSQL
SELECT 姓名,出生日期 FROM 飞狐工作室
WHERE MONTH(出生日期) = MONTH(GETDATE())
5. 查询需求
请列出“飞狐工作室”表中，每一位员工的姓名和年龄。
/* 档案名称: Demo911.sql */
USE NorthwindSQL
SELECT 姓名,
年龄 = DATEDIFF(year,出生日期,GETDATE())
FROM 飞狐工作室
8.7. SELECT的通配符
共有5个通配符。
*（星号）
Ø 用于字段列表，代表源表中的所有的字段
/* 档案名称: Demo913.sql */
USE NorthwindSQL
--连接两张表“客户”表和“订货主档”表
SELECT *
FROM 客户 INNER JOIN订货主档
ON 客户.客户编号 = 订货主档.客户编号
WHERE 订货主档.订单日期 BETWEEN '08/01/1996' AND '08/31/1996'
/* 档案名称: Demo914.sql */
USE NorthwindSQL
SELECT 客户.公司名称,订货主档.*
FROM 客户 INNER JOIN 订货主档
ON 客户.客户编号 = 订货主档.客户编号
WHERE 订货主档.订单日期 BETWEEN '08/01/1996' AND '08/31/1996'
%（百分号）
Ø 只能用在WHERE子句中，代表0个或0个以上的字符。
如：ABC%代表ABC开头的字符串。
Ø 百分号通常与运算符LIKE搭配使用。
/* 档案名称: Demo915.sql */
USE NorthwindSQL
SELECT 姓名 FROM 飞狐工作室
WHERE 姓名 LIKE '%光%' --表示查询姓名中包含“光”字的记录
_（下划线）
Ø 只能用在WHERE子句中，代表1个字符。
如：_A%代表第二个字符为A的字符串。
Ø 下划线通常与运算符LIKE搭配使用。
［］（中括号）
Ø 只能用在WHERE子句中，用来限定任何一个单个字符介于指定的范围或集合中。
Ø 通常与运算符LIKE搭配使用。
/* 档案名称: Demo923.sql */
USE pubs
SELECT au_fname,au_lname FROM authors
WHERE au_lname LIKE '[P-Z]inger'
--表示第一个字符为P~Z之间且后五个字符为inger
/* 档案名称: Demo924.sql */
USE NorthwindSQL
SELECT 姓名 FROM 飞狐工作室
WHERE 姓名 LIKE '[ACD张李]%' --表示什么？
ORDER BY 姓名
［^］（中括号中包含^号）
Ø 只能用在WHERE子句中，用来限定任何一个单个字符不介于指定的范围或集合中。
Ø 通常与运算符LIKE搭配使用。
/* 档案名称: Demo927.sql */
USE pubs
SELECT au_fname,au_lname FROM authors
WHERE au_fname LIKE '[^H-K]ichel'
--首字母不介于H~K之间且后为ichel
/* 档案名称: Demo930.sql */
USE NorthwindSQL
SELECT 身份证字号,姓名 FROM 飞狐工作室
WHERE 身份证字号 LIKE '[^ALM]%' --这个表示什么意思？
8.8. 字符和通配符冲突时的解决方法
可以使用ESCAPE子句来SQL Server哪一个字符是常量字符而并非通配符。
用专业术语来说，ESCAPE子句所指定的字符称为“转义符”。
/* 档案名称: Demo920.sql */
USE NorthwindSQL
SELECT 姓名,住址 FROM 飞狐工作室
WHERE 住址 LIKE '%\_%' ESCAPE '\'
--通知符号\后的字符并非通配符，即查询住址中带下划线的记录
8.9. 连接条件设置
本节内容太重要了！
看如下代码，如果TableA有M条记录，TableB有N条记录，则查询结果共有M×N条记录：
SELECT * FROM TableA，TableB
或
SELECT * FROM TableA CROSS JOIN TableB
为了避免出现上述情况，有4种连接类型可以选择：
Ø INNER JOIN
特点：查询结果仅包含连接表中彼此相对应的数据记录。
/* 档案名称: Demo933.sql 本例用于查看每一位客户的订货情况
但是，并未下订单的客户不会出现在查询结果中！
*/
USE NorthwindSQL
SELECT客户.客户编号, 客户.公司名称, 客户.连络人, 客户.电话,
订货主档.订单号码, 订货主档.订单日期, 订货主档.要货日期,
订货主档.送货日期, 订货主档.送货方式, 订货主档.运费,
订货主档.收货人,订货主档.送货地址
FROM 客户 INNER JOIN订货主档
ON 客户.客户编号 = 订货主档.客户编号
更复杂的连接3个表的例子：
/* 档案名称: Demo934.sql */
USE NorthwindSQL
SELECT 客户.客户编号,客户.公司名称,订货主档.订单号码,
订货主档.订单日期,订货明细.产品编号,订货明细.单价,
订货明细.数量, 订货明细.折扣
FROM 客户 INNER JOIN订货主档
INNER JOIN 订货明细
ON 订货主档.订单号码 = 订货明细.订单号码
ON 客户.客户编号 = 订货主档.客户编号
Ø LEFT OUTER JOIN
特点：查询结果将包含位于关键字LEFT OUTER JOIN左侧源表中的所有数据记录，但是仅包含右侧源表中相应的数据记录。
/* 档案名称: Demo935.sql 本例用于查看每一位客户的订货情况
但是希望并未下订单的客户也出现在查询结果中！
*/
USE NorthwindSQL
SELECT 客户.客户编号,
客户.公司名称,
客户.连络人,
订货主档.订单号码,
订货主档.收货人
FROM 客户 LEFT OUTER JOIN订货主档
ON 客户.客户编号 = 订货主档.客户编号
Ø RIGHT OUTER JOIN
特点：查询结果将包含位于关键字RIGHT OUTER JOIN右侧源表中的所有数据记录，但是仅包含左侧源表中相应的数据记录。
/* 档案名称: Demo936.sql 查看每一种产品的销售情况
但是希望那些没有人订购的产品数据也在查询结果中出现
*/
USE NorthwindSQL
SELECT 订货明细.订单号码,
订货明细.单价,
订货明细.数量,
订货明细.折扣,
产品资料.产品编号,
产品资料.产品
FROM 订货明细 RIGHT OUTER JOIN产品资料
ON 订货明细.产品编号 = 产品资料.产品编号
Ø FULL OUTER JOIN
特点：查询结果将包含位于关键字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月份采购的北京市客户的基本数据、订单数据、订货明细。
/* 档案名称: Demo939.sql */
USE NorthwindSQL
SELECT 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/1996'
2. 查询出所有曾经在1996年订货的客户公司名称和所订购的产品明细
/* 档案名称: Demo940.sql */
USE NorthwindSQL
SELECT 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.订单日期) = 1996
3. 假如ERNSH公司是我们的忠实客户，我们要查询出该公司在1998年所下的订单，以及负责处理这些客户订单的员工基本数据：
/* 档案名称: Demo941.sql */
USE NorthwindSQL
SELECT a.公司名称,b.订单号码,b.订单日期,c.*
FROM 客户 a INNER JOIN 订货主档 b
INNER JOIN 员工 c
ON c.员工编号 = b.员工编号
ON a.客户编号 = b.客户编号
WHERE a.客户编号 = 'ERNSH' AND
YEAR(b.订单日期) = 1998
8.10. 统计运算的高手：聚合函数
聚合函数专用于SELECT语句中。
Ø COUNT
n 主要用于计算查询结果中的数据条数，通常用COUNT(*)的形式。
n 事实上，COUNT()是唯一允许使用通配符作为参数的聚合函数。
1. 计算出“飞狐工作室”表中的数据记录条数
/* 档案名称: Demo942.sql */
USE NorthwindSQL
SELECT COUNT(*) FROM 飞狐工作室
2. 计算出“飞狐工作室”表中有多少员工住在北京市
/* 档案名称: Demo943.sql */
USE NorthwindSQL
SELECT COUNT(*) FROM 飞狐工作室
WHERE 住址 LIKE '%北京市%'
3. 计算出“飞狐工作室”表中有年龄介于30～40之间的员工有多少
/* 档案名称: Demo944.sql */
USE NorthwindSQL
SELECT COUNT(*) FROM 飞狐工作室
WHERE DATEDIFF(yy,出生日期,GETDATE()) BETWEEN 30 AND 40
4. 查询出在1996年7月份下订单的北京市客户有多少位
/* 档案名称: Demo945.sql */
USE NorthwindSQL
SELECT 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. 查询出“飞狐工作室”表中，最低的目前薪资是多少。
/* 档案名称: Demo946.sql */
USE NorthwindSQL
SELECT MIN(目前薪资) AS 最低薪资
FROM 飞狐工作室
注意：
利用MIN查询出某一字段的最小值时，并无法同时得知该字段为最小值的数据记录的其他字段内容。
2. 查询出“飞狐工作室”表中，年龄最大者是几岁（提供了三种方法）。
/* 档案名称: Demo947.sql */
USE NorthwindSQL
SELECT YEAR(GETDATE()) - YEAR(MIN(出生日期)) AS 最高年龄
FROM 飞狐工作室
SELECT DATEDIFF(yyyy,MIN(出生日期),GETDATE()) AS 最高年龄
FROM 飞狐工作室
SELECT MAX(DATEDIFF(yyyy,出生日期,GETDATE())) AS 最高年龄
FROM 飞狐工作室
Ø MAX
n 能够计算出最大值。
Ø AVG
n 能够计算出平均值。
1. 计算出“飞狐工作室”表中员工的平均年龄
/* 档案名称: Demo951.sql */
USE NorthwindSQL
SELECT AVG(DATEDIFF(yyyy,出生日期,GETDATE())) AS 平均年龄
FROM 飞狐工作室
Ø SUM
n 求和。
1. 计算出所有客户的采购总金额
/* 档案名称: Demo952.sql */
USE NorthwindSQL
SELECT客户采买总金额 =
SUM(订货明细.单价*订货明细.数量*(1-订货明细.折扣))
FROM 订货明细
聚合函数注意事项：
Ø 可以将查询结果存储到一个变量中
/* 档案名称: Demo953.sql */
USE NorthwindSQL
DECLARE @AverageSalary money
-- 将计算所得的平均薪资储存至变数 @AverageSalary 中
SELECT @AverageSalary = AVG(目前薪资) //这就是我为什么不喜欢
//使用＝号来设定列标题的原因！
FROM 飞狐工作室
PRINT '薪资大于平均薪资 '+STR(@AverageSalary,10,4)+' 的员工如下:'
PRINT REPLICATE('-',35)
SELECT姓名,目前薪资 FROM飞狐工作室
WHERE 目前薪资 > @AverageSalary
Ø 同一SELECT语句中，可以分别使用不同的聚合函数
/* 档案名称: Demo954.sql */
USE NorthwindSQL
SELECT MAX(目前薪资) AS 最高薪资, //看看这种设定列标题的方式
MIN(目前薪资) AS 最低薪资,
AVG(目前薪资) AS 平均薪资,
SUM(目前薪资) AS 薪资总额
FROM 飞狐工作室
Ø 聚合函数中允许包含DISTINCT关键字，用于排除重复行。
1. 计算出1996年8月份的发票开给了几位客户。
/* 档案名称: Demo955.sql */
USE NorthwindSQL
SELECT COUNT(DISTINCT 客户编号) AS 客户数目
FROM 订货主档
WHERE 订单日期 BETWEEN '08/01/1996' AND '08/31/1996'
8.11. 数据分组小计
利用GROUP BY子句，可以根据一个或多个组的值将查询中的数据记录分组。
1. 计算出员工“飞狐工作室”表中，各个部门的薪资最高值、薪资最小值、薪资平均值及人数。
/* 档案名称: Demo956.sql */
USE NorthwindSQL
SELECT部门,
COUNT(*) AS 部门员工人数,
MAX(目前薪资) AS 部门最高薪资,
MIN(目前薪资) AS 部门最低薪资,
AVG(目前薪资) AS 部门平均薪资
FROM 飞狐工作室
GROUP BY 部门
2. 计算出每一位客户的采购次数和采购总金额。
/* 档案名称: Demo957.sql */
USE NorthwindSQL
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.公司名称
3. 查询出每一位业务人员的销售总数。
/* 档案名称: Demo958.sql */
USE NorthwindSQL
SELECT a.员工编号,a.姓名,
SUM(c.数量) AS 销售总数
FROM 员工 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 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, aMount b
WHERE a.销售总数 < b.销售总数
GROUP BY a.员工编号,a.姓名,a.销售总数
EXEC sp_dboption 'NorthwindSQL','select into/bulkcopy','FALSE'
DROP TABLE aMount
5. 为“飞狐工作室”表的各部门计算出“女性未婚”、“女性已婚”、“男性未婚”、“男性已婚”员工的最低薪资、最高薪资和平均薪资。
/* 档案名称: Demo960.sql */
USE NorthwindSQL
--本例中，性别、婚姻状况是bit数据类型，SQL Server不允许使用bit类型的字
--段作为分组，这里变通了一下。
SELECT部门,
性别 = CASE 性别
WHEN 0 THEN '女'
WHEN 1 THEN '男'
END,
婚姻状况 = CASE 婚姻状况
WHEN 0 THEN '未婚'
WHEN 1 THEN '已婚'
END,
最低薪资 = MIN(目前薪资),
最高薪资 = MAX(目前薪资),
平均薪资 = AVG(目前薪资)
FROM 飞狐工作室
GROUP BY 部门,
CASE 性别
WHEN 0 THEN '女'
WHEN 1 THEN '男'
END,
CASE 婚姻状况
WHEN 0 THEN '未婚'
WHEN 1 THEN '已婚'
END
8.12. HAVING 子句的使用
HAVING搜索条件子句通常与GROUP BY子句合用，用来设置查询结果中“组”所需符合的条件。在这里我们自然会想为什么不用where因为where 不可以用聚合函数
即对要列出哪几个分组进行了限定。
1. 计算出员工“飞狐工作室”表中，部门平均薪资大于30000的各个部门的薪资最高值、薪资最小值、薪资平均值及人数。
/* 档案名称: Demo961.sql */
USE NorthwindSQL
SELECT部门,
AVG(目前薪资) AS 部门的平均薪资,
COUNT(*) AS 各部门之人数,
MAX(目前薪资) AS 部门中的最高薪,
MIN(目前薪资) AS 部门中的最低薪
FROM 飞狐工作室
GROUP BY 部门
HAVING AVG(目前薪资) > 30000
2. 查询出“飞狐工作室”表中，有哪些员工的姓名是跟别人相同的，而且同样采用某一姓名者又有多少人。
/* 档案名称: Demo962.sql */
USE NorthwindSQL
SELECT姓名 AS 重复的姓名,
COUNT(*) AS 重复的人数
FROM 飞狐工作室
GROUP BY 姓名
Having COUNT(*) > 1
3. 计算出订单超过10张的各家客户的订单数目，及其采购总金额。
/* 档案名称: Demo963.sql */
USE NorthwindSQL
SELECTa.公司名称,
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.订单号码) > 10
8.13. ORDER BY 子句的使用
利用ORDER BY子句，可以根据一个或多个字段的数据排序查询出的结果。
1. 按照薪资所得的高低顺序，列出“飞狐工作室”表中所有员工的姓名、性别和薪资。
/* 档案名称: Demo964.sql */
USE NorthwindSQL
SELECT姓名,
性别 = CASE 性别
WHEN 0 THEN '女'
WHEN 1 THEN '男'
END,
目前薪资
FROM 飞狐工作室
ORDER BY 目前薪资 DESC //可用ORDER BY 3 DESC
2. 将“飞狐工作室”表中各部门的平均薪资由低到高列出来。
/* 档案名称: Demo965.sql */
USE NorthwindSQL
SELECT部门,
AVG(目前薪资) AS 平均薪资
FROM 飞狐工作室
GROUP BY 部门
ORDER BY 2
3. 列出“飞狐工作室”表中所有员工的隶属部门、员工姓名、性别和薪资，按部门、性别（降序）、目前薪资（降序）来排列。
/* 档案名称: Demo966.sql */
USE NorthwindSQL
SELECT部门,
姓名,
性别 = CASE 性别
WHEN 0 THEN '女'
WHEN 1 THEN '男'
END,
目前薪资
FROM 飞狐工作室
ORDER BY 部门,性别 DESC,目前薪资 DESC
4. 计算出每一位客户的采购次数和采购总金额。先按照采购次数由多到少排列，而采购次数相同者，则再按照采购总金额由低到高排列。
/* 档案名称: Demo967.sql */
USE NorthwindSQL
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.公司名称
ORDER BY 2 DESC, 3
8.14. 查询名列前茅或落后者
先利用ORDER BY排序，然后利用关键字TOP n [PERCENT]来取出从头算起的前几名或从头算起的多少百分比。
1. 查询出“飞狐工作室”中，薪资最低员工的姓名及其薪资。
/* 档案名称: Demo968.sql */
USE NorthwindSQL
SELECT TOP 1
姓名 AS 薪资最低之员工姓名,
目前薪资
FROM 飞狐工作室
ORDER BY 目前薪资
2. 查询出“飞狐工作室”中，薪资最高员工的姓名及其薪资。
3. 查询出“飞狐工作室”中，薪资最高的前10名员工的姓名及其薪资。
4. 查询出“飞狐工作室”中，薪资倒数前10名员工的姓名及其薪资。
5. 查询出“飞狐工作室”中，部门的平均薪资最高的前3个部门。
/* 档案名称: Demo972.sql */
USE NorthwindSQL
SELECTTOP 3
部门 AS 平均薪资最高的前三个部门,
AVG(目前薪资) AS 平均薪资
FROM 飞狐工作室
GROUP BY 部门
ORDER BY 2 DESC
6. 查询出“飞狐工作室”中，目前薪资最高的前10％的员工。
7. 查询出销售业绩最佳的前3名业务员。
/* 档案名称: Demo974.sql */
USE NorthwindSQL
SELECTTOP 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 DESC
8. 查询出在1996年采购总金额最高的客户。
/* 档案名称: 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.公司名称
ORDER BY 3 DESC
8.15. 活用子查询(SubQuery)
n 子查询：就是包含在某一个SELECT、INSERT、UPDATE、DELETE命令中的SELECT查询。
n 在SELECT、INSERT、UPDATE、DELETE命令中凡是允许是一个表达式的地方均可以包含子查询。
n 子查询甚至可以再包含一个子查询。
如：蓝色部分为子查询
/* 档案名称: Demo976.sql */
USE NorthwindSQL
SELECT 订货主档.订单号码,订货主档.订单日期,
(SELECT MAX(订货明细.单价)
FROM 订货明细
WHERE 订货主档.订单号码 = 订货明细.订单号码) AS 最高单价
FROM 订货主档
1. 本例以子查询的返回值作为WHERE子句的条件值，以便查询哪些员工的薪资和公司中最低薪资人员相同。
/* 档案名称: Demo977.sql */
USE NorthwindSQL
SELECT a.姓名,
a.目前薪资
FROM 飞狐工作室 a
WHERE a.目前薪资 =
(SELECT MIN(b.目前薪资) FROM 飞狐工作室 b)
2. 本例通过子查询得知有哪些员工的薪资比公司的平均薪资还高出50％
/* 档案名称: Demo978.sql */
USE NorthwindSQL
SELECT a.姓名,
a.目前薪资
FROM 飞狐工作室 a
WHERE a.目前薪资 >
(SELECT AVG(b.目前薪资) * 1.50 FROM 飞狐工作室 b)
ORDER BY a.目前薪资
3. 本例通过子查询查询出人数比业务部门还多的各部门员工的平均薪资。
/* 档案名称: Demo982.sql */
USE NorthwindSQL
SELECT部门,COUNT(*) AS 部门人数,AVG(目前薪资) AS 平均薪资
FROM 飞狐工作室
GROUP BY 部门
HAVING COUNT(*) >
(SELECT COUNT(*) FROM 飞狐工作室 WHERE 部门 = '业务部')
注：
n 子查询必须包含在一对小括号内。
“EXISTS”子查询的测试
n 使用EXISTS关键字，可检查是否至少有一条数据记录存在于子查询的查询结果中。
n 在子查询的SELECT语句选择列表中通常使用通配符星号*。
1. 查询出哪些公司并没有采购。
/* 档案名称: Demo980.sql */
USE NorthwindSQL
SELECT客户.客户编号,客户.公司名称
FROM 客户
WHERE NOT EXISTS
(SELECT *
FROM 订货主档
WHERE 订货主档.客户编号 = 客户.客户编号)
“IN”子查询的测试
1. 查询出哪些公司并没有采购。
/* 档案名称: Demo983.sql */
USE NorthwindSQL
SELECTa.公司名称
FROM 客户 a
WHERE a.客户编号 NOT IN
(SELECT b.客户编号 FROM 订货主档 b)
2. 查询出哪些公司曾经采购过。（代码略）
附注：其实用EXISTS和IN可以达到相同的效果。
8.16. 本章命令一缆表
1．一条简单的SQL语句
Select 字段名/常量/SQL表达式 ――select 子句
From 表名列表 ――from 子句
2．查询结果
与数据库中的表一样，SQL查询的结果总是一个数据表。它也许只有一个字段、也许只有一条记录、也许只有一条记录一个字段的小表，当然也可以是一张空表。
SQL查询总是生成一个数据表的事实是非常重要的，它意味着查询结果可以作为一个表存回到数据库中，也也意味着两个类似查询结果可以组合以形成一个较大的查询结果表。最后，它意味着查询结果本身可以是进一步查询的目标。因此，关系数据库的表格式结构对SQL的关系查询功能具有协同增强的关系。表可以被查询，查询可以生成表。
3．简单查询
Select 子句命名必需的字段，from 子句命名包含它们的表。选择所有的字段（select*）,
* 星号作为“所有字段”的缩写。
4．重复的记录（distint）
Distinct用于清除重复的记录。
5．选择记录（where）
使用where来指定你想要检索的记录。
本质上，对于表中的每一条记录，搜索条件起到过滤器的作用。
6．搜索条件
比较测试（＝，<,<,>,<=,>,>=）：
把一个表达式的值和另一个表达式得值进行比较。
在SQL的3值逻辑下，一个搜索条件可以产生True，False或Null值。只有其搜索条件产生True的那些记录才被包括在查询结果中。
范围测试（between……and）：
测试一个表达式的值是否是在一个指定的值的范围内。
三个表达式的数据类型必须是可比较的。
组成员测试（in/not in）：
检查一个表达式的值是否匹配一组值中的一个。
模式匹配测试(Like)：
检查包含字符串数据的字段的值是否匹配一个指定的模式。
课件中说的比较详细，可参考。
Null值测试：
检查是否一个字段有一个Null（未知）值。
|
字段名 is |
|
not |
|
NULL |
|
NULL值测试（is Null）语法图 |
复合搜索条件（AND ,OR和NOT）
7．排序查询结果(order by 子句)
建议使用段号select sol表达式………
8．多表查询（连接）
课件详细。
9．汇总查询
SUM() 计算字段的总和。
AVG() 计算字段的平均值。
MIN() 查找字段中的最小值。
MAX()查找字段中的最大值。
COUNT()计算字段中值的数目。
COUNT(*)计算查询结果的记录数。
DISTINCT /ALL 消除重复记录。
SELECT COUNT(DISTINCT 客户编号) AS 客户数目
FROM 订货主档
WHERE 订单日期 BETWEEN '08/01/1996' AND '08/31/1996'
|
字段3 |
10．分组查询（group by子句）
GROUP BY 字段1，字段2，字段3
|
字段1 |
|
字段3
|
|
字段3 |
|
字段2 |
|
字段3
|
|
字段2 |
Select 分组字段/聚合函数 ――select 后面只能是。
11．分组搜索条件（having by 子句）
Having by 后面的条件字段可以是那些字段？
12．子查询
外部引用
Select city
From offices
Where target >(select sum(quota)
From salesaeps
Where rep_office=office)
子查询搜索条件
子查询比较测试（＝，>=,<,<=,>,<>）
组成员测试（in）
可不可以
Select
Where 一个子段 in (select 两个字段)
|
第8章 结束 |
[w1]All 和 distinct是修饰词，数据实际还是存在的，它存在于内存中。
