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

1、表的约束性规则表的约束性规则表的创建表的创建表数据的维护表数据的维护列名列名数据类型数据类型说明说明EmpIDintPrimary KeyProjIDintPrimary Key,参照Project表中ProjID字段EmpNamevarchar(20)NOT NULLDepIDintNOT NULL，参照完整性Cityvarchar(20)只能是上海，南京，苏州三个城市解决方案：解决方案：create table Employee(EmpID int ProjID int constraint fkProjID foreign key references Project(ProjID)co

2、nstraint pkEmpProj Primary key(EmpID,ProjID),EmpName varchar(20)not null,DepID int not null constraint fkDepID foreign key references Department(DepID),City varchar(20)constraint chkCity check(City in(上上海海,南京南京,苏州苏州)第四章第四章表的管理表的管理单表查询单表查询带有带有Where子句的查询子句的查询根据条件对表记录排序根据条件对表记录排序对记录分组显示对记录分组显示对记录分组统计对记

3、录分组统计基本查询语句基本查询语句使用使用SELECT语句实现查询语句实现查询基本语法：基本语法：SELECT子句子句 INTO 子句子句 FROM 子句子句 WHERE 子句子句 GROUP BY 子句子句 HAVING 子句子句 ORDER BY 子句子句 COMPUTE 子句子句有关系模式：有关系模式：Student(StuID,StuName,StuAge,StuAddress,DepID)显示格式为：显示格式为：StuIDStuID StuNameStuAge1200112001 MaryMary21211200212002TomTom2020语法：语法：SELECT ALL|DIS

4、TINCT select_column_list FROM table_nameStuIDStuID StuNameStuAge1200112001 MaryMary21211200212002TomTom2020显示特定字段：显示特定字段：SELECT StuID,StuName,StuAge FROM StudentStudent(StuID,StuName,StuAge,StuAddress,DepID)显示格式为：显示格式为：StuIDStuID StuNameStuAgeStuAddressDepID1200112001 MaryMary2121NanJingNanJing1 112

5、00212002TomTom2020BeiJingBeiJing2 2显示所有字段：显示所有字段：SELECT*FROM StudentStudent(StuID,StuName,StuAge,StuAddress,DepID)显示格式为：显示格式为：学号学号 姓名姓名年龄年龄1200112001 MaryMary21211200212002TomTom2020方法方法1:SELECT 别名别名1=列名列名1,别名别名2=列名列名2,别名别名n=列名列名n FROM table_name方法方法2：SELECT 列名列名1 AS 别名别名1,列名列名2 AS 别名别名2,列名列名n AS 别名

6、别名n FROM table_name学号学号 姓名姓名年龄年龄1200112001 MaryMary21211200212002TomTom2020SELECT 学号学号=StuID,姓名姓名=StuName,年龄年龄=StuAgeFROM studentStuIDStuIDStuNameStuAge1200112001 MaryMary21211200212002TomTom20201200312003JackJack2121注：默认情况为注：默认情况为ALL。为。为ALL时，时，SELECT StuAge FROM Student有有3条记录；为条记录；为DISTINCT时，时，SELE

7、CT DISTINCT StuAge FROM Student有有2条记录。条记录。语法：语法：SELECT ALL|DISTINCT select_column_list FROM table_name聚合函数用于对数据库表中的一列或几列数据进行统计汇总，常用于查询语聚合函数用于对数据库表中的一列或几列数据进行统计汇总，常用于查询语句中。句中。聚合函数聚合函数功能功能AVG(AVG(表达式表达式)返回表达式返回表达式(含列名含列名)的平均值。的平均值。COUNT(COUNT(表达式表达式)对表达式指定的列值进行计数，忽对表达式指定的列值进行计数，忽略空值。略空值。COUNT(COUNT(*)

8、对表或组中的所有行进行计数，包对表或组中的所有行进行计数，包含空值。含空值。MAX(MAX(表达式表达式)表达式中最大的值。表达式中最大的值。MIN(MIN(表达式表达式)表达式中最小的值表达式中最小的值SUM(SUM(表达式表达式)表达式值的合计。表达式值的合计。【例例】设某学生数据库中有一个设某学生数据库中有一个“SC”表，该表包含的列有：表，该表包含的列有：StuID、CourseID、Grade。其中，。其中，Grade列为列为int类型，使用聚合函数实现以下各功能。类型，使用聚合函数实现以下各功能。（1）求所有学生的成绩的平均值。）求所有学生的成绩的平均值。（2）求选修）求选修“2”

9、号课程的学生的总成绩。号课程的学生的总成绩。（3）求选修）求选修“2”号课程的学生最高成绩，最低成绩号课程的学生最高成绩，最低成绩（4）统计选修）统计选修“2”号课程的学生总人数号课程的学生总人数解决方案：解决方案：（1）SELECT AVG(Grade)FROM SC（2）SELECT SUM(Grade)FROM SC WHERE CourseID=2（3）SELECT MAX(Grade),MIN(Grade)FROM SC WHERE CourseID=2（4）SELECT COUNT(*)FROM SC WHERE CourseID=2 CASE函数可以根据指定表达式的值返函数可以根

10、据指定表达式的值返回多个可能的结果之一回多个可能的结果之一CASE命令的基本语法如下：命令的基本语法如下：CASE 条件表达式条件表达式 WHEN 值值1 THEN 结果结果1 .ELSE 其他结果其他结果 END 示例：对示例：对“Student”表中表中“StuSex”列的值进行处理，当值等于列的值进行处理，当值等于“女女”时返回时返回0，当值等于，当值等于“男男”时返回时返回1。解决方案：解决方案：SELECT StuName,SexValue=CASE StuSex WHEN 女女 THEN 0 ELSE 1 END FROM Student多数字符串函数用于对字符串参数值执行操作，返

11、回结果为字多数字符串函数用于对字符串参数值执行操作，返回结果为字符串或数字值。符串或数字值。示例示例返回值返回值UPPER(Abcd)ABCDLOWER(HELLO)helloLTRIM(how are you)how are youRTRIM(how are you )how are youHello+SPACE(3)+ZhangHello ZhangREPLICATE(ab,3)abababSTUFF(abcdef,2,3,ijklmn)aijklmnefREVERSE(abc)cba示例示例返回值返回值ASCII(A)ASCII(Abc)6565CHAR(65)ASTR(3.141592

12、6,8,4)STR(3.1415926,5)3.1416 3LEN(abc)LEN(abc )33RIGHT(hello,3)lloLEFT(hello,3)helSUBSTRING(hello,3,2)SUBSTRING(hello,3,5)llllo示例示例返回值返回值CHARINDEX(cd,abcdabcd)CHARINDEX(cd,abcdabcd,4)CHARINDEX(dc,abcdabcd)370REPLACE(abcdefghicde,cde,xxx)abxxxfghixxx【例】【例】将将Student表中姓名的左右空格去掉后显示出来。表中姓名的左右空格去掉后显示出来。用于

13、对数字表达式进行数学运算并返回运算结果。用于对数字表达式进行数学运算并返回运算结果。示例示例返回值返回值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.7182818284590451示例示例返回值返回值R

14、OUND(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的值，并将该值打印出来。的值，并将该值打印出来。用于对日期和时间数据进行各种不同的处理或运算，并返回一用于对日期和时间数据进行各种不同的处理或运算，并返回一个字符串、数字值或日期和时间值。个字符串、数字值或日

15、期和时间值。示例示例返回值返回值GETDATE()2006-08-21 21:46:38.320DATEADD(DAY,1,1780-11-01)DATEADD(MONTH,5,1780-11-01)11 2 1780 12:00AM04 1 1781 12:00AMDATEDIFF (MONTH,1780-1-11,1780-11-01)DATEDIFF (YEAR,1790-1-11,1780-11-01)10-10【例】【例】有关系模式有关系模式Employee(EmpID,EmpName,EmpBirthDate)显示学生的年龄。显示学生的年龄。示例示例返回值返回值DATENAME(m

16、onth,getdate()08(设当前为设当前为8月份月份)DAY(03/12/1998)12DATEPART(datepart,date)Datepart(yy,2006-08-21)返回返回2006SELECT datediff(yy,EmpBirthDate,getdate()AS 年龄年龄FROM Employee隐式转换隐式转换显式转换：显式转换：CAST或或CONVERT1.CAST函数函数 用于将某种数据类型的表达式显式转换为另一种数据类型。用于将某种数据类型的表达式显式转换为另一种数据类型。格式格式:CAST(表达式表达式 AS 数据类型数据类型)2.CONVERT函数函数

17、用于将某种数据类型的表达式显式转换为另一种数据类型，在将日期时间用于将某种数据类型的表达式显式转换为另一种数据类型，在将日期时间类型的数据转换为字符类型的数据时，可以指定转换后的字符样式。类型的数据转换为字符类型的数据时，可以指定转换后的字符样式。格式格式:CONVERT(数据类型数据类型(长度长度),表达式表达式,样式样式)【例】在查询分析器中执行以下语句：【例】在查询分析器中执行以下语句：DECLARE date_var1 datetimeSET date_var1=GETDATE()PRINT 当前时间为当前时间为+CAST(date_var1 as varchar(50)PRINT 当

18、前时间为当前时间为+CONVERT(varchar(50),date_var1)显示结果为：显示结果为：当前时间为当前时间为16 2014 8:20AM当前时间为当前时间为16 2014 8:20AM WHERE子句用于指定返回的行的搜索子句用于指定返回的行的搜索条件。相当于选择运算。条件。相当于选择运算。它的基本语法如下：它的基本语法如下：WHERE 条件表达式条件表达式条件表达式中可包含：条件表达式中可包含：逻辑运算符逻辑运算符(NOT,AND,OR)比较运算符比较运算符(,=20 AND StuAge 90 SELECT DepID,MAX(StuScore)FROM student W

19、HERE MAX(StuScore)90 GROUP BY DepID 聚合不应出现在聚合不应出现在WHERE子句中，除非该聚合位于子句中，除非该聚合位于HAVING子句或选择列表所子句或选择列表所包含的子查询包含的子查询中。中。解释解释下列查询语句代表的含义：下列查询语句代表的含义：SELECT Title,avg(VacationHours)FROM Employee WHERE VacationHours30 GROUP BY TitleHaving avg(VacationHours)55解决方案：对度假时间超过解决方案：对度假时间超过30小时的员工按照职称进行分组，小时的员工按照职称

20、进行分组，将平均度假时间超过将平均度假时间超过55小时的组的职称及平均度假时间显示小时的组的职称及平均度假时间显示出来。出来。使用使用COMPUTE子句可以在结果集的最后生成附子句可以在结果集的最后生成附加的汇总行，因此既可以查看明细行，又可以查看加的汇总行，因此既可以查看明细行，又可以查看汇总行。汇总行。语法：语法：COMPUTE AVG|COUNT|MAX|MIN|SUM(表达式表达式),.n BY 表达式表达式 ,.n 当当COMPUTE不带不带BY子句时，查询结果包含：子句时，查询结果包含：第一个结果集是包含查询结果的所有明细行。第一个结果集是包含查询结果的所有明细行。第二个结果集有一

21、行，其中包含第二个结果集有一行，其中包含COMPUTE子句中所指子句中所指定的聚合函数的合计。定的聚合函数的合计。当当COMPUTE与与BY一起使用时，一起使用时，COMPUTE子句可以对子句可以对结果集进行分组并在每一组之后附加汇总行，符合查结果集进行分组并在每一组之后附加汇总行，符合查询条件的每个组都包含：询条件的每个组都包含：每个组的第一个结果集是明细行集。每个组的第一个结果集是明细行集。每个组的第二个结果集有一行，其中包含该组的每个组的第二个结果集有一行，其中包含该组的COMPUTE子句中所指定的聚合函数的小计。子句中所指定的聚合函数的小计。解决方案：解决方案：SELECT StuNa

22、me,StuScore FROM Student COMPUTE SUM(StuScore)1.查询所有学生的成绩，并统计总成绩。查询所有学生的成绩，并统计总成绩。明细行明细行汇总汇总 COMPUTE与与BY一起使用时，必须结合使用一起使用时，必须结合使用ORDER BY子句，并且子句，并且COMPUTE子句中的表达式必须与在子句中的表达式必须与在ORDER BY后列出的子句相同或是其子集，并且必后列出的子句相同或是其子集，并且必须按相同的序列。例如，如果须按相同的序列。例如，如果ORDER BY 子句是：子句是：ORDER BY a,b,c 则则COMPUTE子句可以是：子句可以是：COMP

23、UTE BY a,b,c COMPUTE BY a,b COMPUTE BY a1.按性别分组显示该组所有学生的姓名及成绩，并按性别分组显示该组所有学生的姓名及成绩，并统计每组的总成绩。统计每组的总成绩。解决方案：解决方案：SELECT StuName,StuScore FROM Student ORDER BY StuSex COMPUTE SUM(StuScore)BY StuSex男同学的明细行男同学的明细行女同学的明细行女同学的明细行男同学汇总信息男同学汇总信息女同学汇总信息女同学汇总信息解释以下查询语句代表的含义：解释以下查询语句代表的含义：SELECT Title,Vacation

24、Hours,SickLeaveHours FROM Employee WHERE Title in(Recruiter,Stocker)ORDER BY Title,VacationHours,SickLeaveHoursCompute sum(VacationHours),sum(SickLeaveHours)by Title解决方案：对职称为解决方案：对职称为Recruiter和和Stocker的职工按组先的职工按组先显示职称，度假时间，病假时间的明细信息，再按组显示每显示职称，度假时间，病假时间的明细信息，再按组显示每类职称的总度假时间及病假时间。类职称的总度假时间及病假时间。Stude

25、ntInfo(StuID,StuName,StuAge,StuSex,StuScore,DepID,Marriage).1.查找计算机系查找计算机系(假设系号为假设系号为5)女同学的信息。女同学的信息。2.显示所有姓名包含显示所有姓名包含“李李”同时是计算机系同时是计算机系(假设系号为假设系号为5)的的学生信息。学生信息。3.显示婚姻状况为未知的计算机系显示婚姻状况为未知的计算机系(假设系号为假设系号为5)学生。学生。4.按性别分组显示男女同学的平均年龄。按性别分组显示男女同学的平均年龄。5.按系分组显示系总人数超过按系分组显示系总人数超过100人的组的系号和系总人数。人的组的系号和系总人数。

26、6.将成绩低于将成绩低于60分的学生按系分组，显示不及格人数超过分的学生按系分组，显示不及格人数超过10人的组的系名和不及格人数。人的组的系名和不及格人数。7.按总计方式显示所有学生的平均年龄。按总计方式显示所有学生的平均年龄。8.按小计方式以系分组显示学生的平均年龄。按小计方式以系分组显示学生的平均年龄。1.Select*from Student where DepID=5 and StuSex=女女2.Select*from Student where StuName like%李李%and DepID=53.Select*from Student where Marriage is nu

27、ll and DepID=54.Select StuSex,avg(StuAge)from Student group by StuSex5.Select DepID,count(*)from Student group by DepID having count(*)=100 6.Select DepID,Count(*)from Student where StuScore=107.Select*from Student compute avg(StuAge)8.Select*from Student order by DepID compute avg(StuAge)by DepID1.查询同时选修了查询同时选修了1号和号和2号课程的学生学号。号课程的学生学号。2.查询选修了查询选修了1号或号或2号课程的学生学号。号课程的学生学号。3.查询选修了查询选修了1号而未选修号而未选修2号课程的学生学号。号课程的学生学号。4.查询未同时选修查询未同时选修1号和号和2号课程的学生学号。号课程的学生学号。WHERE子句子句对记录排序对记录排序GROUP BY子句子句HAVING子句子句COMPUTE与与COMPUTE BY查询语句初步查询语句初步