《数据库》课件第4章-单表查询(带答案)

1回顾表的约束性规则表的创建表数据的维护2练习：创建表Employee列名数据类型说明EmpIDintPrimaryKeyProjIDintPrimaryKey,参照Project表中ProjID字段EmpNamevarchar(20)NOTNULLDepIDintNOTNULL，参照完整性Cityvarchar(20)只能是上海，南京，苏州三个城市解决方案：createtableEmployee(EmpIDintProjIDintconstraintfkProjIDforeignkeyreferencesProject(ProjID)constraintpkEmpProjPrimarykey(EmpID,ProjID),EmpNamevarchar(20)notnull,DepIDintnotnullconstraintfkDepIDforeignkeyreferencesDepartment(DepID),Cityvarchar(20)constraintchkCitycheck(Cityin(‘上海’,’南京’,’苏州’)))3第四章

表的管理—单表查询4目标带有Where子句的查询根据条件对表记录排序对记录分组显示对记录分组统计基本查询语句5单表的查询使用SELECT语句实现查询基本语法：

SELECT子句

[INTO子句]

FROM子句

[WHERE子句][GROUPBY子句][HAVING子句][ORDERBY子句][COMPUTE子句]6简单的查询语句有关系模式：Student(StuID,StuName,StuAge,StuAddress,DepID)显示格式为：StuID

StuNameStuAge12001

Mary2112002Tom207简单的查询语句语法：SELECT[ALL|DISTINCT]select_column_list

FROM{table_name}StuID

StuNameStuAge12001

Mary2112002Tom20显示特定字段：SELECTStuID,StuName,StuAgeFROMStudent8简单的查询语句Student(StuID,StuName,StuAge,StuAddress,DepID)显示格式为：StuID

StuNameStuAgeStuAddressDepID12001

Mary21NanJing112002Tom20BeiJing2显示所有字段：SELECT*FROMStudent9简单的查询语句Student(StuID,StuName,StuAge,StuAddress,DepID)显示格式为：学号姓名年龄12001

Mary2112002Tom2010简单的查询语句方法1:SELECT'别名1'=列名1,'别名2'=列名2,…'别名n'=列名nFROMtable_name方法2：SELECT列名1AS'别名1',列名2AS'别名2',…列名nAS'别名n'FROMtable_name学号姓名年龄12001

Mary2112002Tom20SELECT'学号'

=StuID,'姓名'

=StuName,'年龄'=StuAgeFROMstudent11简单的查询语句StuIDStuNameStuAge12001

Mary2112002Tom2012003Jack21注：默认情况为ALL。为ALL时，SELECTStuAgeFROMStudent有3条记录；为DISTINCT时，SELECTDISTINCTStuAgeFROMStudent有2条记录。语法：SELECT[ALL|DISTINCT]select_column_list

FROM{table_name}124.2.2聚合函数聚合函数用于对数据库表中的一列或几列数据进行统计汇总，常用于查询语句中。聚合函数功能AVG(表达式)返回表达式(含列名)的平均值。COUNT(表达式)对表达式指定的列值进行计数，忽略空值。COUNT(*)对表或组中的所有行进行计数，包含空值。MAX(表达式)表达式中最大的值。MIN(表达式)表达式中最小的值SUM(表达式)表达式值的合计。134.2.2聚合函数【例】设某学生数据库中有一个“SC”表，该表包含的列有：StuID、CourseID、Grade。其中，Grade列为int类型，使用聚合函数实现以下各功能。（1）求所有学生的成绩的平均值。（2）求选修“2”号课程的学生的总成绩。（3）求选修“2”号课程的学生最高成绩，最低成绩（4）统计选修“2”号课程的学生总人数解决方案：（1）SELECTAVG(Grade)FROMSC（2）SELECTSUM(Grade)FROMSCWHERECourseID=2（3）SELECTMAX(Grade),MIN(Grade)FROMSCWHERECourseID=2（4）SELECTCOUNT(*)FROMSCWHERECourseID=2144.4.2使用CASE函数CASE函数可以根据指定表达式的值返回多个可能的结果之一CASE命令的基本语法如下：

CASE条件表达式

WHEN值1THEN结果1......ELSE其他结果

END示例：对“Student”表中“StuSex”列的值进行处理，当值等于

“女”时返回0，当值等于“男”时返回1。解决方案：

SELECTStuName,‘SexValue’=CASEStuSexWHEN'女'THEN0ELSE1ENDFROMStudent154.2.2字符串函数多数字符串函数用于对字符串参数值执行操作，返回结果为字符串或数字值。示例返回值UPPER('Abcd')'ABCD'LOWER('HELLO')'hello'LTRIM('howareyou')'howareyou'RTRIM('howareyou')'howareyou''Hello'+SPACE(3)+'Zhang''HelloZhang'REPLICATE('ab',3)'ababab'STUFF('abcdef',2,3,'ijklmn')'aijklmnef'REVERSE('abc')'cba'164.2.2字符串函数示例返回值ASCII('A')ASCII('Abc')6565CHAR(65)'A'STR(3.1415926,8,4)STR(3.1415926,5)'3.1416''3'LEN('abc')LEN('abc')33RIGHT('hello',3)'llo'LEFT('hello',3)'hel'SUBSTRING('hello',3,2)SUBSTRING('hello',3,5)'ll''llo'174.2.2字符串函数示例返回值CHARINDEX('cd','abcdabcd')CHARINDEX('cd','abcdabcd',4)CHARINDEX('dc','abcdabcd')370REPLACE('abcdefghicde','cde','xxx')'abxxxfghixxx'【例】将Student表中姓名的左右空格去掉后显示出来。184.2.2数学函数用于对数字表达式进行数学运算并返回运算结果。示例返回值ABS(-1.0)1.0SQRT(2)1.4142135623730951SQUARE(2)4.0POWER(2,6)64SIN(30*3.1416/180)0.50000106036260283COS(30*3.1416/180)0.86602479158293899tan(45*3.1416/180)1.0000036732118496LOG(2.7182)0.99996989653910984LOG10(10)1.0EXP(1)2.7182818284590451194.2.2数学函数示例返回值ROUND(123.9994,3)ROUND(748.58,-2)123.9990700.00CEILING(123.45)CEILING(-123.45),124.00-123.00FLOOR(123.45)FLOOR(-123.45)123-124PI()3.1415926535897931RAND(7)0.71370379104047277【例】声明变量保存根号2的值，并将该值打印出来。204.2.2日期和时间函数

用于对日期和时间数据进行各种不同的处理或运算，并返回一个字符串、数字值或日期和时间值。示例返回值GETDATE()2006-08-2121:46:38.320DATEADD(DAY,1,'1780-11-01')DATEADD(MONTH,5,'1780-11-01')112178012:00AM041178112:00AMDATEDIFF(MONTH,'1780-1-11','1780-11-01')DATEDIFF(YEAR,'1790-1-11','1780-11-01')10-10214.2.2日期和时间函数【例】有关系模式Employee(EmpID,EmpName,EmpBirthDate)显示学生的年龄。示例返回值DATENAME(month,getdate())08(设当前为8月份)DAY('03/12/1998')12DATEPART(datepart,date)Datepart(yy,’2006-08-21’)返回2006SELECTdatediff(yy,EmpBirthDate,getdate())AS'年龄'FROMEmployee224.2.2转换函数隐式转换显式转换：CAST或CONVERT234.2.2转换函数1.CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。格式:CAST(表达式AS数据类型)2.CONVERT函数用于将某种数据类型的表达式显式转换为另一种数据类型，在将日期时间类型的数据转换为字符类型的数据时，可以指定转换后的字符样式。格式:CONVERT(数据类型[(长度)],表达式[,样式])244.2.2转换函数【例】在查询分析器中执行以下语句：DECLARE@date_var1datetimeSET@date_var1=GETDATE()PRINT'当前时间为'+CAST(@date_var1asvarchar(50))PRINT'当前时间为'+CONVERT(varchar(50),@date_var1)显示结果为：当前时间为1620148:20AM当前时间为1620148:20AM254.4.3设置查询条件WHERE子句用于指定返回的行的搜索条件。相当于选择运算。它的基本语法如下：

WHERE条件表达式条件表达式中可包含：逻辑运算符(NOT,AND,OR)比较运算符(>,<=…)范围运算符（BETWEEN）列表运算符（IN,LIKE…）NULL关键字26使用NOT,AND和OR运算符语法：SELECT列名列表FROM表名WHERE表达式1{AND/OR}[NOT]表达式2示例1：使用SELECT语句查询“Student”表中所有111051班的女生。示例2：使用SELECT语句查询“Student”表中所有111051和111053班的学生。解决方案：1.SELECT*FROMStudentWHEREStuClass='111051'ANDStuSex='女生'2.SELECT*FROMStudentWHEREStuClass='111051'ORStuClass='111053'27比较运算符示例：选择年龄介于20-25岁且是5系的学生解决方案：SELECT*FROMstudentWHEREStuAge>=20ANDStuAge<=25ANDDepID=528使用BETWEEN运算符在WHERE子句中使用BETWEEN运算符可以查询指定范围的记录。示例:使用SELECT语句查询“Student”表中所有StuScore在60到80之间的学生记录。解决方案：

SELECT*FROMStudentWHEREStuScoreBETWEEN60AND8029使用IN（NOTIN）运算符解决方案：SELECT*FROMstudentWHEREStuCityIN('Beijing','Shanghai')当要筛选的值在有限个可数的范围之内时，可使用IN关键字。语法：SELECT列名列表FROM表名WHERE表达式{IN|NOTIN}(‘值列表‘)示例:查找来自于上海，北京城市的学生信息30使用LIKE运算符可以在WHERE子句中使用LIKE运算符进行模糊查询,用LIKE运算符指定匹配条件的语法如下：

[NOT]LIKE'匹配串'示例:查询“Student”表中所有姓名中包含“李”记录。解决方案：SELECT*FROMStudentWHEREStuNameLIKE'%李%'31选择包含未知信息的列示例:查询“Student”表中来自城市为未知的学生。解决方案：SELECT*FROMStudentWHEREStuCityISNULL324.4.4对查询结果排序使用ORDERBY子句可以对查询结果进行排序。基本语法：

ORDERBY{列名[ASC|DESC]}[,...n]示例:使用SELECT语句查询表“Student”所有记录，按照Score字段从高到低排序。解决方案：

SELECT*FROMStudentORDERBYStuScoreDESC

SELECT

Sex,max(StuScore)

FROMstudent

WHEREStuSex=‘女’GROUPBY

ALLStuSex

—1.执行where语句筛选所有女同学信息2.按Sex进行分组统计3.女同学可以获取最高分，男同学信息没被where筛选中，故统计最高分时无此项信息，用NULL替代。334.4.5对查询结果分组当SELECT子句中包含聚合函数时，可以使用GROUPBY子句对查询结果进行分组统计，计算每组记录的汇总值。GROUPBY子句的基本语法如下：

GROUPBY[ALL]分组表达式[,...n]示例：在“Student”表中按性别统计所有学生的最高成绩。解决方案：

SELECTStuSex,max(StuScore)

FROMstudent

GROUPBYStuSex

—先分组再执行聚合函数其中ALL表示不满足where条件的所有组也显示在结果中，通常由NULL表示。

SELECTStuSex,max(StuScore)

FROMstudent

WHEREStuSex=‘女’GROUPBY

ALLStuSex

—1.执行where语句筛选所有女同学信息2.按Sex进行分组统计3.女同学可以获取最高分，男同学信息没被where筛选中，故统计最高分时无此项信息，用NULL替代。344.4.5对查询结果分组3女92…IDSexScore…1女90…2男85…3女92…35注意

在使用GROUPBY子句时，SELECT子句中每一个非聚合表达式内的所有列都应包含在GROUPBY列表中。否则将会返回错误信息。示例：

SELECTStuSex,StuClass,MAX(StuScore)FROMStudentGROUPBYStuSex执行上面的语句会因为GROUPBY表达式与SELECT子句不匹配而产生错误。改成：

SELECTStuSex,StuClass,MAX(StuScore)FROMStudentGROUPBYStuSex,StuClass364.4.6指定组或聚合的搜索条件HAVING子句的功能是指定组或聚合的搜索条件。HAVING通常与GROUPBY子句一起使用。如果不使用GROUPBY子句，HAVING的作用与WHERE子句一样。HAVING与WHERE的区别在于：1.WHERE子句搜索条件在进行分组操作之前应用；而HAVING搜索条件在进行分组操作之后应用。2.HAVING语法与WHERE语法类似，但在HAVING子句中可以包含聚合函数,而WHERE子句不能包含聚合函数。37示例示例：统计最高成绩超过90的院系及最高入学成绩信息。

解决方案：SELECTDepID,MAX(StuScore)FROMstudentGROUPBYDepIDHAVINGMAX(StuScore)>90SELECTDepID,MAX(StuScore)FROMstudentWHEREMAX(StuScore)>90GROUPBYDepID聚合不应出现在WHERE子句中，除非该聚合位于HAVING子句或选择列表所包含的子查询中。38练习：

解释下列查询语句代表的含义：SELECTTitle,avg(VacationHours)FROMEmployeeWHEREVacationHours>30GROUPBYTitleHavingavg(VacationHours)>55解决方案：对度假时间超过30小时的员工按照职称进行分组，将平均度假时间超过55小时的组的职称及平均度假时间显示出来。394.4.7生成汇总行使用COMPUTE子句可以在结果集的最后生成附加的汇总行，因此既可以查看明细行，又可以查看汇总行。语法：COMPUTE{{AVG|COUNT|MAX|MIN|SUM}(表达式)}[,...n][BY表达式[,...n]]40COMPUTE与COMPUTEBY当COMPUTE不带BY子句时，查询结果包含：第一个结果集是包含查询结果的所有明细行。第二个结果集有一行，其中包含COMPUTE子句中所指定的聚合函数的合计。当COMPUTE与BY一起使用时，COMPUTE子句可以对结果集进行分组并在每一组之后附加汇总行，符合查询条件的每个组都包含：每个组的第一个结果集是明细行集。每个组的第二个结果集有一行，其中包含该组的COMPUTE子句中所指定的聚合函数的小计。41示例:COMPUTE解决方案：SELECTStuName,StuScoreFROMStudentCOMPUTESUM(StuScore)1.查询所有学生的成绩，并统计总成绩。明细行汇总42COMPUTEBYCOMPUTE与BY一起使用时，必须结合使用ORDERBY子句，并且COMPUTE子句中的表达式必须与在ORDERBY后列出的子句相同或是其子集，并且必须按相同的序列。例如，如果ORDERBY子句是：

ORDERBYa,b,c

则COMPUTE子句可以是：

COMPUTEBYa,b,cCOMPUTEBYa,bCOMPUTEBYa43示例：COMPUTEBY1.按性别分组显示该组所有学生的姓名及成绩，并统计每组的总成绩。解决方案：

SELECTStuName,StuScoreFROMStudent

ORDER

BYStuSexCOMPUTESUM(StuScore)BYStuSex男同学的明细行女同学的明细行男同学汇总信息女同学汇总信息44练习解释以下查询语句代表的含义：SELECTTitle,VacationHours,SickLeaveHoursFROMEmployeeWHERETitlein('Recruiter','Stocker')ORDERBYTitle,VacationHours,SickLeaveHoursCompute

sum(VacationHours),

sum(SickLeaveHours)

byTitle解决方案：对职称为Recruiter和Stocker的职工按组先显示职