SQL数据库数据的查询汇总统计和分析.docx
- 文档编号:24173907
- 上传时间:2023-05-25
- 格式:DOCX
- 页数:30
- 大小:25.16KB
SQL数据库数据的查询汇总统计和分析.docx
《SQL数据库数据的查询汇总统计和分析.docx》由会员分享,可在线阅读,更多相关《SQL数据库数据的查询汇总统计和分析.docx(30页珍藏版)》请在冰豆网上搜索。
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表,查询出所有作者的代号、、及住址,而且请使用中文文字作为查询结果的各字段名。
解答
/*脚本文件名:
Demo91.sql*/
USEpubs
SELECT作者代号=au_id,
=au_fname+au_lname,
=phone,
住址=address
FROMauthors
或(看看你更喜欢哪一种格式?
)
/*脚本文件名:
Demo92.sql*/
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的员工。
/*脚本文件名:
Demo93.sql*/
USENorthwindSQL
SELECT,目前薪资FROM飞狐工作室
WHERE目前薪资>60000
2.查询需求
请列出“飞狐工作室”表在信息部、行销部和业务部等3个部门任职的员工。
/*脚本文件名:
Demo94.sql*/
USENorthwindSQL
SELECT,部门FROM飞狐工作室
WHERE部门IN('资讯部','行销部','业务部')
3.查询需求
请列出“飞狐工作室”表在信息部、行销部和业务部等3个部门以外任职的员工。
/*脚本文件名:
Demo95.sql*/
USENorthwindSQL
SELECT,部门FROM飞狐工作室
WHERE部门NOTIN('资讯部','行销部','业务部')
4.查询需求
请列出“飞狐工作室”表中,本月出生的员工和出生日期。
/*脚本文件名:
Demo910.sql*/
USENorthwindSQL
SELECT,出生日期FROM飞狐工作室
WHEREMONTH(出生日期)=MONTH(GETDATE())
5.查询需求
请列出“飞狐工作室”表中,年龄大于20岁的每一位员工的。
/*脚本文件名:
Demo911.sql*/
USENorthwindSQL
SELECT
FROM飞狐工作室
WHEREDATEDIFF(year,出生日期,GETDATE())>20
任务:
查询章立民工作室中已婚的员工信息;查询“飞狐工作室”表中行销部的员工信息;查询06010111班或者07010211班的学生信息;查询成绩在80分以下的学生选课信息;查询年龄超过50岁的女教师信息;查询出生日期在1970到1980年之间的教师信息。
8.7.SELECT的通配符
共有5个通配符。
*(星号)
Ø用于字段列表,代表源表中的所有的字段
/*脚本文件名:
Demo913.sql*/
USENorthwindSQL
--连接两表“客户”表和“订货主档”表
SELECT*
FROM客户INNERJOIN订货主档
ON客户.客户编号=订货主档.客户编号
WHERE订货主档.订单日期BETWEEN'08/01/1996'AND'08/31/1996'
/*脚本文件名:
Demo914.sql*/
USENorthwindSQL
SELECT客户.公司名称,订货主档.*
FROM客户INNERJOIN订货主档
ON客户.客户编号=订货主档.客户编号
WHERE订货主档.订单日期BETWEEN'08/01/1996'AND'08/31/1996'
%(百分号)
Ø只能用在WHERE子句中,代表0个或0个以上的字符。
如:
ABC%代表ABC开头的字符串。
Ø百分号通常与运算符LIKE搭配使用。
/*脚本文件名:
Demo915.sql*/
USENorthwindSQL
SELECTFROM飞狐工作室
WHERELIKE'%光%'--表示查询中包含“光”字的记录
任务:
查询“飞狐工作室”表中住在市的的员工信息;查询教师表中姓杜的教师信息;查询professional表中专业名称包含计算机的专业信息。
_(下划线)
Ø只能用在WHERE子句中,代表1个字符。
如:
_A%代表第二个字符为A的字符串。
Ø下划线通常与运算符LIKE搭配使用。
ØUSENorthwindSQL
Ø
ØSELECTFROM飞狐工作室
ØWHERELIKE'_建_'
[](中括号)
Ø只能用在WHERE子句中,用来限定任何一个单个字符介于指定的围或集合中。
Ø通常与运算符LIKE搭配使用。
/*脚本文件名:
Demo923.sql*/
USEpubs
SELECTau_fname,au_lnameFROMauthors
WHEREau_lnameLIKE'[P-Z]inger'
--表示第一个字符为P~Z之间且后五个字符为inger
/*脚本文件名:
Demo924.sql*/
USENorthwindSQL
SELECTFROM飞狐工作室
WHERELIKE'[ACD]%'--表示什么?
ORDERBY
任务:
查询学号尾号为1、3、5、7、9的学生信息。
[^](中括号中包含^号)
Ø只能用在WHERE子句中,用来限定任何一个单个字符不介于指定的围或集合中。
Ø通常与运算符LIKE搭配使用。
/*脚本文件名:
Demo927.sql*/
USEpubs
SELECTau_fname,au_lnameFROMauthors
WHEREau_fnameLIKE'[^H-K]ichel'
--首字母不介于H~K之间且后为ichel
/*脚本文件名:
Demo930.sql*/
USENorthwindSQL
SELECT,FROM飞狐工作室
WHERELIKE'[^ALM]%'--这个表示什么意思?
任务:
查询学号尾号不为1、3、5、7、9的学生信息。
8.8.字符和通配符冲突时的解决方法
可以使用ESCAPE子句通知SQLServer哪一个字符是常量字符而并非通配符。
用专业术语来说,ESCAPE子句所指定的字符称为“转义符”。
/*脚本文件名:
Demo920.sql*/
USENorthwindSQL
SELECT,家庭地址FROM飞狐工作室
WHERE家庭地址LIKE'%\_%'ESCAPE'\'
--通知符号\后的字符并非通配符,即查询住址中带下划线的记录
8.9.连接条件设置
本节容太重要了!
看如下代码,如果TableA有M条记录,TableB有N条记录,则查询结果共有M×N条记录:
SELECT*FROMTableA,TableB
或
SELECT*FROM TableACROSSJOINTableB
为了避免出现上述情况,有4种连接类型可以选择:
ØINNERJOIN
特点:
查询结果仅包含连接表中彼此相对应的数据记录。
/*脚本文件名:
Demo933.sql本例用于查看每一位客户的订货情况
但是,并未下订单的客户不会出现在查询结果中!
*/
USENorthwindSQL
SELECT客户.客户编号,客户.公司名称,客户.联系人,客户.,
订货主档.订单,订货主档.订单日期,订货主档.要货日期,
订货主档.送货日期,订货主档.送货方式,订货主档.运费,
订货主档.收货人,订货主档.送货地址
FROM客户INNERJOIN订货主档
ON客户.客户编号=订货主档.客户编号
更复杂的连接3个表的例子:
/*脚本文件名:
Demo934.sql*/
USENorthwindSQL
SELECT客户.客户编号,客户.公司名称,订货主档.订单,
订货主档.订单日期,订货明细.产品编号,订货明细.单价,
订货明细.数量,订货明细.折扣
FROM客户INNERJOIN订货主档
ON客户.客户编号=订货主档.客户编号
INNERJOIN订货明细
ON订货主档.订单=订货明细.订单
任务:
查询下了订单的客户信息:
客户编号、公司名称、订单、订单日期;查询产品信息:
产品编号、类别名称;查询学生信息:
学号、、班级名称;查询班级信息:
班级编号、班级名称、专业名称;查询专业信息:
专业编号、专业名称、系名称;列出计算机工程系的专业信息;查询晓龙的所有选修课成绩;查询选修了“3dsmax”课程的学生和课程成绩。
ØLEFTOUTERJOIN(左外连接)
特点:
查询结果将包含位于关键字LEFTOUTERJOIN左侧源表中的所有数据记录,但是仅包含右侧源表中相应的数据记录。
/*脚本文件名:
Demo935.sql本例用于查看每一位客户的订货情况
但是希望并未下订单的客户也出现在查询结果中!
*/
USENorthwindSQL
SELECT客户.客户编号,
客户.公司名称,
客户.连络人,
订货主档.订单,
订货主档.收货人
FROM客户LEFTOUTERJOIN订货主档
ON客户.客户编号=订货主档.客户编号
(加上条件限制试试WHERE订货主档.订单isNULL)
ØRIGHTOUTERJOIN(右外连接)
特点:
查询结果将包含位于关键字RIGHTOUTERJOIN右侧源表中的所有数据记录,但是仅包含左侧源表中相应的数据记录。
/*脚本文件名:
Demo936.sql查看每一种产品的销售情况
但是希望那些没有人订购的产品数据也在查询结果中出现
*/
USENorthwindSQL
SELECT订货明细.订单,订货明细.单价,订货明细.数量,
订货明细.折扣,产品资料.产品编号,产品资料.产品
FROM订货明细RIGHTOUTERJOIN产品资料
ON订货明细.产品编号=产品资料.产品编号
(加上条件限制试试WHERE订货明细.订单isNULL)
ØFULLOUTERJOIN(全外连接)
特点:
查询结果将包含位于关键字FULLOUTERJOIN左右两侧源表中的所有数据记录。
/*脚本文件名:
Demo938.sql给表指定别名*/
USENorthwindSQL
SELECTa.客户编号,a.公司名称,a.连络人,a.,
b.订单,b.订单日期,b.要货日期,b.送货日期,
b.送货方式,b.运费,b.收货人,b.送货地址
FROM客户aINNERJOIN订货主档b
ONa.客户编号=b.客户编号
小技巧:
给表指定一个较短的别名,最大的好处是缩减了SELECT语句的长度。
例:
1.查询在1996年7月份采购的市客户的基本数据、订单数据、订货明细。
/*脚本文件名:
Demo939.sql*/
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年订货的客户公司名称和所订购的产品明细
/*脚本文件名:
Demo940.sql*/
USENorthwindSQL
SELECTa.公司名称,b.订单日期,d.*
FROM客户aINNERJOIN订货主档b
INNERJOIN订货明细c
INNERJOIN产品资料d
ONd.产品编号=c.产品编号
ONb.订单=c.订单
ONa.客户编号=b.客户编号
WHEREYEAR(b.订单日期)=1996
3.假如ERNSH公司是我们的忠实客户,我们要查询出该公司在1998年所下的订单,以及负责处理这些客户订单的员工基本数据:
/*脚本文件名:
Demo941.sql*/
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.计算出“飞狐工作室”表中的数据记录条数
/*脚本文件名:
Demo942.sql*/
USENorthwindSQL
SELECTCOUNT(*)FROM飞狐工作室
2.计算出“飞狐工作室”表中有多少员工住在市
/*脚本文件名:
Demo943.sql*/
USENorthwindSQL
SELECTCOUNT(*)FROM飞狐工作室
WHERE住址LIKE'%市%'
3.计算出“飞狐工作室”表中有年龄介于30~40之间的员工有多少
/*脚本文件名:
Demo944.sql*/
USENorthwindSQL
SELECTCOUNT(*)FROM飞狐工作室
WHEREDATEDIFF(yy,出生日期,GETDATE())BETWEEN30AND40
4.查询出在1996年7月份下订单的市客户有多少位
/*脚本文件名:
Demo945.sql*/
USENorthwindSQL
SELECTCOUNT(*)AS客户数目
FROM客户aINNERJOIN订货主档b
ONa.客户编号=b.客户编号
WHEREa.地址LIKE'%市%'AND
b.订单日期BETWEEN'07/01/1996'AND'07/31/1996'
ØMIN
⏹能够计算出最小值。
1.查询出“飞狐工作室”表中,最低的目前薪资是多少。
/*脚本文件名:
Demo946.sql*/
USENorthwindSQL
SELECTMIN(目前薪资)AS最低薪资
FROM飞狐工作室
注意:
利用MIN查询出某一字段的最小值时,并无法同时得知该字段为最小值的数据记录的其他字段容。
2.查询出“飞狐工作室”表中,年龄最大者是几岁(提供了三种方法)。
/*脚本文件名:
Demo947.sql*/
USENorthwindSQL
SELECTYEAR(GETDATE())-YEAR(MIN(出生日期))AS最高年龄
FROM飞狐工作室
SELECTDATEDIFF(yyyy,MIN(出生日期),GETDATE())AS最高年龄
FROM飞狐工作室
SELECTMAX(DATEDIFF(yyyy,出生日期,GETDATE()))AS最高年龄
FROM飞狐工作室
ØMAX
⏹能够计算出最大值。
ØAVG
⏹能够计算出平均值。
1.计算出“飞狐工作室”表中员工的平均年龄
/*脚本文件名:
Demo951.sql*/
USENorthwindSQL
SELECTAVG(DATEDIFF(yyyy,出生日期,GETDATE()))AS平均年龄
FROM飞狐工作室
ØSUM
⏹求和。
1.计算出所有客户的采购总金额
/*脚本文件名:
Demo952.sql*/
USENorthwindSQL
SELECT客户采买总金额=
SUM(单价*数量*(1-折扣))
FROM订货明细
聚合函数注意事项:
Ø可以将查询结果存储到一个变量中
/*脚本文件名:
Demo953.sql*/
USENorthwindSQL
DECLAREAverageSalarymoney
--将计算所得的平均薪资储存至变数AverageSalary中
SELECTAverageSalary=AVG(目前薪资)
FROM飞狐工作室
/*STR(float_expression[,length[,decimal]]),返回由数字数据转换来的字符数据。
*/
PRINT'薪资大于平均薪资'+STR(AverageSalary,10,4)+'的员工:
'
PRINTREPLICATE('-',35)
SELECT,目前薪资FROM飞狐工作室
WHERE目前薪资>AverageSalary
Ø同一SELECT语句中,可以分别使用不同的聚合函数
/*脚本文件名:
Demo954.sql*/
USENorthwindSQL
SELECTMAX(目前薪资)AS最高薪资,--看看这种设定列标题的方式
MIN(目前薪资)AS最低薪资,
AVG(目前薪资)AS平均薪资,
SUM(目前薪资)AS薪资总额
FROM飞狐工作室
Ø聚合函数中允许包含DISTINCT关键字,用于排除重复行。
1.计算出1996年8月份的发票开给了几位客户。
/*脚本文件名:
Demo955.sql*/
USENorthwindSQL
SELECTCOUNT(DISTINCT客户编号)AS客户数目
FROM订货主档
WHERE订单日期BETWEEN'08/01/1996'AND'08/31/1996'
任务:
统计teacher表中职称为讲师的教师人数;查询学生的入学平均分数、最低分、最高分;查询学号为'0601011101'的学生选课门数;查询学生'劲'的选课门数;查询学生'劲'选修课程的最高分、最低分、平均分。
8.11.数据分组小计
利用GROUPBY子句,可以根据一个或多个组的值将查询中的数据记录分组。
1.计算出员工“飞狐工作室”表中,各个部门的薪资最高值、薪资最小值、薪资平均值及人数。
/*脚本文件名:
Demo956.sql*/
USENorthwindSQL
SELECT部门,
COUNT(*)AS部门员工人数,
MAX(目前薪资)AS部门最高薪资,
MIN(目前薪资)AS部门最低薪资,
AVG(目前薪资)AS部门平均薪资
FROM飞狐工作室
GROUPBY部门
任务:
统计teacher表中各类职称的教师人数;统计各课程的最高分、最低分、平均分。
2.计算出各个客户的采购次数和采购总金额。
/*脚本文件名:
Demo957.sql*/
USENorthwindSQL
SELECTa.公司名称,
COUNT(DISTINCTb.订单)AS采购次数,
SUM(c.单价*c.数量*(1-c.折扣))AS采购总金额
FROM客户a
INNERJOIN订货主档b
INNERJOIN订货明细c
ONb.订单=c.订单
ONa.客户编号=b.客户编号
GROUPBYa.客户编号,a.公司名称
3.查询出每一位业务人员的销售总数量。
/*脚本文件名:
Demo958.sql*/
USENorthwindSQL
SELECTa.员工编号,a.,
SUM(c.数量)AS销售总数
FROM员
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 数据库 数据 查询 汇总 统计 分析