资源简介

(共70张PPT)
教学单元1.7
第7章 SELECT数据查询(基本)
案例1-7 教务管理SELECT数据查询
SELECT数据查询 学习导航SELECT数据查询 知识框架单元1.7SELECT数据查询（基本）能力目标能够充分认识到SQL数据查询是数据库的重要技术，是数据库的主要应用目的。熟练掌握各种查询为数据库应用系统的开发奠定基础能够根据数据库应用系统功能需求对表进行投影查询、连接查询、选择查询、分组统计查询、限定查询、排序查询、保存查询能够阅读并熟练书写SELECT查询语句和有关参数（英文）单元1.7SELECT数据查询（基本）知识目标SELECT查询语句的语法格式和应用投影、连接和选择查询分组统计、限定、排序和保存查询素质目标通过大量的训练，培养积极、自信和严谨的工作作风通过复杂的训练，培养克服困难、开动脑筋、积极解决问题的能力单元1.7SELECT数据查询（基本）案例1-7教务管理SELECT数据查询根据教务管理系统的功能需求，应用T-SQL的SELECT查询语句对数据库“EDUC”中所创建的表进行数据查询（检索）。工作任务单元1.7SELECT数据查询（基本）用户可以使用SELECT语句从数据库中按照功能需求查询出数据信息实现对关系的投影、选择和连接的专门关系运算可以得到经过分类、统计和排序处理后的查询结果T-SQL完全支持SQL-92标准的SELECT语句SELECT语句是数据库应用最广泛和最重要的语句之一区别起见，对于实现查询功能的SELECT语句我们称之为SELECT查询语句SELECT数据查询语句的功能与特点一、SELECT查询语句结构SELECT表达式[,...n] --投影(计算统计)[INTO新表名] --保存FROM表名[,...n] --连接[WHERE逻辑表达式] --选择[GROUP BY表达式[,...n]] --分组统计[HAVING逻辑表达式] --限定分组统计[ORDER BY表达式[,...n] ] --排序SELECT…FROM一、SELECT查询语句结构功能与说明：对一个或多个表（基表或视图）按一定的条件和需求进行查询，产生出一个新表（即查询结果），该新表可被显示出来、作为表（子查询）再应用或者被命名保存起来。SELECT语句中的子句顺序非常重要，可以省略任选子句，但这些子句在使用时必须按规定的顺序出现。SELECT查询语句中的表达式由*|列名、常量、变量、函数和运算符构成。很多情况下表达式仅为表的列名。SELECT…FROM一、SELECT查询语句结构功能与说明：SELECT查询语句作为嵌入式语言，可以嵌入在各种高级语言中实现对数据库的访问。SELECT查询语句作为自含式语言，可以使用SSMS的【查询编辑器】进行编辑、编译、执行和保存。SELECT…FROM二、SELECT子句投影查询SELECT [ALL|DISTINCT] [TOPn]表达式[,...n]功能说明：SELECT子句主要是一个用逗号分隔的表达式列表，用于对查询结果集进行投影操作。SELECT子句中的表达式主要为要投影的表的列名，也可以由其他常量、变量、函数和运算符构成。此外，还有一些参数可以根据需要进行选择，下面将通过实例分别讲解它们的应用。SELECT…语法二、SELECT子句投影查询语法：*|列名[,...n]说明：当表达式为通配符“*” 时，按表中所有列名的原有顺序进行投影查询。当表达式为一个或多个列名时，按列名的顺序对表进行投影查询。1.投影某些列的值二、SELECT子句投影查询1.投影某些列的值【例7-1】从课程表“Course”中查询出所有数据信息。代码SELECT * FROM Course --通配符“*”代表投影所有列查询结果二、SELECT子句投影查询1.投影某些列的值【例7-2】从课程表“Course”中查询出课程号“CID”、学分“Credit”和课程名“Cname”的数据信息。代码SELECTCID,Credit,Cname--投影列CID,Credit,CnameFROM Course --查询表Course查询结果二、SELECT子句投影查询语法：TOPn[PERCENT]说明：如果未指定关键字PERCENT，则返回查询结果集的前n行数据。如果指定了关键字PERCENT，n就是查询返回结果集行的百分比。2.TOP限制返回行数二、SELECT子句投影查询【例7-3】从学生表“Student”中查询出前3行数据。代码：SELECT TOP 3SID,Sname,Sex,Birthdate--返回前3行数据FROM Student查询结果：2.TOP限制返回行数二、SELECT子句投影查询【例7-4】从学生表“Student”中查询出前20%行数据。代码：SELECT TOP 20 PERCENTSID,Sname,Sex,Birthdate--返回前20%行的数据FROM Student查询结果：21行数据，21*20%=4.2，取整数52.TOP限制返回行数二、SELECT子句投影查询语法：All|DISTINCT功能说明：指定All（缺省）关键字将保留查询结果集中的全部数据行。当对表进行投影操作之后，在查询结果集中可能会出现重复的数据行，使用DISTINCT关键字可消除查询结果集中的重复数据行。3．DISTINCT消除重复行二、SELECT子句投影查询【例7-5】从学生表“Student”中查询出学校各专业的名称。可以用DISTINCT关键字消除重复的专业名称。代码：SELECT DISTINCT Specialty --使用DISTINCT消除重复数据行FROM Student查询结果：3．DISTINCT消除重复行二、SELECT子句投影查询语法：表达式[,...n]说明：在SELECT子句的表达式中可以使用加（+）、减（ ）、乘（*）、除（/）、取模（%）和字符连接（+）等运算符及各种函数进行运算，通过对表达式的计算来获取查询结果的列值。值得注意的是，对表中列的计算只是影响查询结果，并不改变表中的数据。4．投影表达式的值二、SELECT子句投影查询【例7-6】从课程表“Course” 中查询出学分对应的课程学时（假设每18学时计1学分），显示前5行。代码（表达式含运算符）SELECT TOP 5CID,Cname,Credit,Credit*18FROM Course查询结果：4．投影表达式的值二、SELECT子句投影查询【例7-6】从课程表“Course” 中查询出学分对应的课程学时（假设每18学时计1学分），显示前5行。代码（表达式含函数）SELECT TOP 5CID,Cname,Credit,STR(Credit*18,3,0)+'学时'FROM Course查询结果：4．投影表达式的值二、SELECT子句投影查询【例7-7】从课程表“Course”中查询出学分和对应的课程学时，并显示前5行。代码（表达式为常量）SELECT TOP 5CID,Cname,Credit,'学分',STR(Credit*18,3,0),'学时’FROM Course查询结果：4．投影表达式的值二、SELECT子句投影查询语法1：‘指定的列标题’ =表达式语法2：表达式AS '指定的列标题'说明：自定义列标题后，在查询结果的标题位置将显示指定的列标题而不再是表中定义的列名。指定的列标题如果不是规则标识符（如含有空格等）的字符串，可以用单引号括起来，否则也可以不用。关键字AS也可以省略。在默认的情况下，数据查询结果显示的列标题就是在创建表时用的列名。对于通过表达式计算出来的列（如以上几个例子），系统不指定列标题，而以“无列名”标识，这样的情况就可以为查询结果重新指定列标题。5．自定义列标题二、SELECT子句投影查询【例7-8】对例7-6中得到的查询结果用中文显示列标题。代码（自定义列标题）：SELECT TOP 5 CID AS '课 程 号',Cname AS '课 程 名’,Credit学分,学时= STR(Credit*18,3,0)FROM Course查询结果：6．自定义列标题二、SELECT子句投影查询语法：函数名([ALL|DISTINCT]表达式|*)说明：与其他函数不同，聚合函数的参数一般为列名或者包含列名的表达式，主要功能是对表在指定列名表达式的值上进行纵向统计和计算，所以也称之为列函数。聚合函数的参数中，ALL关键字表示函数对指定列的所有值进行统计和计算，DISTINCT关键字说明函数仅对指定列的唯一值（不计重复值）进行统计和计算，ALL为默认设置。6．投影聚合函数的值二、SELECT子句投影查询语法：函数名([ALL|DISTINCT]表达式|*)常用函数：COUNT：统计列中选取的项目个数或查询输出的行数。SUM：计算指定的数值型列名表达式的总和。AVG：计算指定的数值型列名表达式的平均值。MAX：求出指定的数值、字符或日期型列名表达式的最大值。MIN：求出指定的数值、字符或日期型列名表达式的最小值。6．投影聚合函数的值二、SELECT子句投影查询【例7-9】从学生表“Student”中统计出男生的人数。代码（表达式含有函数） ：SELECTCOUNT(*)AS人数--统计表中满足条件的行数FROM StudentWHERE Sex='男' --选择性别为男的行查询结果（以文本格式输出）：人数-----------8(1行受影响)6．聚合函数（COUNT()）二、SELECT子句投影查询【例7-10】从学生表“Student”中统计出专业个数。注意，DISTINCT关键字的作用是消除重复行，即每个专业只计一次。代码（表达式含有函数） ：--统计表“Student”中名称不重复的专业个数SELECTCOUNT(DISTINCTSpecialty) AS专业个数FROM Student查询结果（以文本格式输出）：专业个数-----------4(1行受影响)6．聚合函数（COUNT(DISTINCT)）二、SELECT子句投影查询【例7-11】从学生表“Student”中统计出学生的总数、录取分数“AScores”的最高分、最低分、总分（无实际意义，仅为举例）和平均分。代码（表达式含有函数）：SELECTCOUNT(*) AS总人数,--统计总数MAX(AScores) AS最高分,MIN(AScores) AS最低分,--求最大和最小SUM(AScores) AS总分,--求和STR(AVG(AScores),5,1) AS平均分--求平均FROM Student查询结果：6．聚合函数（COUNT,MAX,SUM,AVG）三、FROM子句连接查询语法：FROM｛表名|虚表名｝[,...n]说明：指定要查询的基表或视图（虚表）。如果指定了一个以上的基表或视图，则计算它们之间的笛卡儿积，与WHERE子句等值条件配合实现连接查询。有关连接查询的ANSI语法及应用将在后续课程中专门介绍。FROM…语法三、FROM子句连接查询【例7-12】从教务管理数据库“EDUC”中查询出学生的学号、姓名、所选课程名和成绩信息。代码（多表连接） ：SELECTStudent.SID,Sname,Cname,Scores--投影各表的列FROMStudent,SC,Course--三个表进行笛卡儿积WHEREStudent.SID=SC.SIDANDSC.CID=Course.CID--等值连接条件查询结果：1．指定基表三、FROM子句连接查询语法：表名[AS]别名说明：其中AS关键字可以不选。别名可以简化表名，此外还可以实现自连接。2．为基表指定临时别名三、FROM子句连接查询【例7-13】同上例，为基表“Student”、“Course”和“SC”指定别名为x、y和z以简化表名。代码：SELECT x.SID,Sname,Cname,Scores--投影各表的列FROM Student AS x,SC AS y,Course AS z--为三个表指定别名WHERE x.SID=y.SID AND y.CID=z.CID--等值条件连接2．为基表指定临时别名三、FROM子句连接查询【例7-14】从选课表“SC”中查询出选了至少两门课程的学生的学号。代码：第一步SELECT x.SID,x.CID,y.CIDFROM SC x,SC y --为表指定别名，实现自连接WHERE x.SID=y.SID AND x.CID<>y.CID2．为基表指定临时别名三、FROM子句连接查询查询结果：说明：连接查询结果的各行中，对于某学生的学号列“SID”，第二列课程号“x.CID”和第三列课程号“y.CID”不同，即说明这些学生选了至少两门课程。2．为基表指定临时别名三、FROM子句连接查询【例7-14】对于此例在SELECT子句中对学号进行投影，并加上DISTINCT参数，则会消除查询中重复的行。代码：SELECTDISTINCTx.SID AS学号FROM SC x,SC yWHERE x.SID=y.SID AND x.CID<>y.CID查询结果：2．为基表指定临时别名四、WHERE子句选择查询语法：WHERE逻辑表达式说明：WHERE子句用于选择操作，逻辑表达式用于描述查询条件。当数据行的数据满足查询条件（逻辑表达式为真）时，向SELECT查询结果集提供数据，否则，其中的数据将不被采用。WHERE子句还用在DELETE和UPDATE语句中选择表中要被删除和修改的行。逻辑表达式：由列名、常量、变量、函数、子查询以及比较运算符或逻辑运算符等组成，其值为真（1，'True'）或假（0，'False'）。WHERE逻辑表达式四、WHERE子句选择查询比较运算符：＝（等于）、<>（不等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）【例7-15】从学生表“Student”中查询出学生赵成刚的信息。代码：SELECT * FROM StudentWHERE Sname=‘赵成刚’--比较运算查询结果：1.比较运算符四、WHERE子句选择查询【例7-16】从学生表“Student”中查询出软件技术专业学生的信息。代码：SELECT * FROM StudentWHERE Specialty=‘软件技术’ --比较运算查询结果：1.比较运算符四、WHERE子句选择查询【例7-17】从学生表“Student”中查询出到2023年满或大于22岁的学生信息。代码（系统日期2023年）：SELECT *,DATEPART(year,GETDATE())-DATEPART(year,Birthdate)年龄FROM StudentWHERE DATEPART(year,GETDATE())-DATEPART(year,Birthdate)>=22查询结果：1.比较运算符四、WHERE子句选择查询AND（与）运算符【例7-18】从学生表“Student”中查询出到2023年年满或大于22岁的女生信息。代码（系统日期2023年）：SELECT *,DATEPART(year,GETDATE())-DATEPART(year,Birthdate)年龄FROM StudentWHEREDATEPART(year,GETDATE())-DATEPART(year,Birthdate)>=22ANDSex='女' --逻辑运算查询结果：2.逻辑运算符（NOT,AND,OR）四、WHERE子句选择查询NOT（非）运算符【例7-19】从学生表“Student”中查询出到2023年不满22岁的男生信息。代码（系统日期2023年）：SELECT *,DATEPART(year,GETDATE())-DATEPART(year,Birthdate)年龄FROM StudentWHERENOT(DATEPART(year,GETDATE())-DATEPART(year,Birthdate)>=22)AND NOT(Sex='女') --逻辑运算查询结果：2.逻辑运算符（NOT,AND,OR）四、WHERE子句选择查询OR（或）运算符【例7-20】从学生表“Student”中查询学号为2020216007和2021216089的学生信息。代码：SELECT * FROM StudentWHERE SID = '2014216007'ORSID = '2015216089' --逻辑运算查询结果：2.逻辑运算符（NOT,AND,OR）四、WHERE子句选择查询语法：表达式[NOT] BETWEEN开始值AND结束值说明：指表达式的值是否在开始值和结束值之间，其中表达式可以为表中的列名。表达式BETWEEN开始值AND结束值等价于（表达式>=开始值AND表达式<=结束值）表达式NOT BETWEEN开始值AND结束值等价于（表达式<开始值OR表达式>结束值）2.逻辑运算符（BETWEEN...AND）四、WHERE子句选择查询【例7-21】从学生表“Student”中查询出入学录取分数在350分到360分之间的学生信息。代码：SELECT * FROM StudentWHERE AScoresBETWEEN350AND360 --范围运算查询结果：2.逻辑运算符（范围运算符）四、WHERE子句选择查询语法：表达式[NOT] LIKE字符串--含通配符说明：表达式的值（NOT，不）与给定符串（含通配符）相似时，逻辑表达式的值为真。通配符“_”代表一个任意字符，通配符“%”代表任意多个任意字符字符。模式匹配运算符LIKE可以实现对表的模糊查询。2.逻辑运算符（LIKE（模式匹配））四、WHERE子句选择查询【例7-22】从学生表“Student”中查询出“李”姓的学生信息。代码：SELECT * FROM StudentWHERE SnameLIKE'李%' --模式匹配模糊查询查询结果：2.逻辑运算符（LIKE（模式匹配）） ）四、WHERE子句选择查询【例7-23】从课程表“Course”中查询出有关程序设计方面的课程。代码：SELECT * FROM CourseWHERE CnameLIKE'%程序设计%' --模式匹配模糊查询查询结果：2.逻辑运算符（LIKE（模式匹配）） ）四、WHERE子句选择查询语法：表达式〔NOT〕IN(列表|子查询)其中列表为：表达式[,...n]说明：表达式的值（NOT，不）与列表中任何表达式的值相等，则逻辑表达式的值为真。有关IN子查询的应用将在后续课程中进一步介绍。2.逻辑运算符（IN（列表））四、WHERE子句选择查询【例7-24】从学生表“Student”中查询学号为2020216007和2021216089的学生信息。代码：SELECT * FROM StudentWHERE SIDIN(‘2020216007’,‘2021216089’)--列名判断运算--等价 “SID=‘202016007’ OR SID=‘2020216089’”查询结果：2.逻辑运算符（IN（列表））四、WHERE子句选择查询语法：表达式IS [NOT] NULL说明：在数据库的表中，除了必须具有值的列不允许为空外，许多列可以没有输入值，这时该列的值为空（NULL）。表达式的值（NOT，不）为空（NULL）时，则逻辑表达式的值为真。其中表达式可以为表中的列名，用于确定指定的列名值是否为NULL。3.谓词运算符(IS NULL)四、WHERE子句选择查询【例7-25】从教务管理数据库“EDUC”中查询出考试成绩没有登记的学生信息。可先把表“SC”的某行的列“Scores”值改为空值“NULL（大写，自动倾斜）”,注意不要改为0，0也是分数。代码：SELECT Student.SID,Sname,Cname,ScoresFROM Student,SC,CourseWHERE Student.SID=SC.SID AND SC.CID=Course.CIDAND ScoresIS NULL --空值判断运算查询结果：3.谓词运算符(IS NULL)五、GROUP BY子句分组统计查询语法：GROUP BY列表达式[,...n]说明：与列表达式（含有列名的表达式）或聚合函数配合实现分组统计。实际应用中往往需要根据某列的值进行分组统计与汇总。如需要从学生表中统计出各专业的学生总数，统计时用“GROUP BY专业名称”进行分组，然后计算聚合函数的值。如需要从选课表中计算出每位学生的总成绩等，统计时用 “GROUP BY学号”来进行分组，然后计算聚合函数的值。注意：在SELECT子句中投影的列表达式必须包含在聚合函数中或者出现在相应的GROUP BY后的列表达式之中。GROUP BY列表达式五、GROUP BY子句分组统计查询例7-26】从学生表“Student”中查询出各专业学生入学录取平均分。代码：SELECT Specialty AS专业,'平均分'=str(AVG(AScores),5,1)--求各专业入学录取平均分FROM StudentGROUP BY Specialty--根据专业名称分组查询结果：GROUP BY列名表五、GROUP BY子句分组统计查询【例7-27】从学生表“Student”中查询出各专业的学生总数，要求查询结果显示专业名称和人数两个列。代码：SELECT '专业'=Specialty,'人数'=COUNT(*)--统计各专业学生人数FROM StudentGROUP BY Specialty--根据专业名称分组查询结果：GROUP BY列名表五、GROUP BY子句分组统计查询【例7-28】从选课表“SC”中统计出每位学生的总成绩，要求查询结果显示出学生的学号、姓名和总成绩。代码：SELECTSC.SID,Student.Sname,'总成绩'=SUM(Scores)--求每位学生成绩的总和FROMSC,StudentWHERESC.SID=Student.SID--按照学号SID分组GROUP BYSC.SID,Student.Sname查询结果：说明：由于Student.Sname是投影列名，所以必须写在GROUP BY子句列名表中。GROUP BY列名表六、HAVING子句限定查询语法：HAVING逻辑表达式说明：与GROUP BY参数配合筛选（选择）统计结果。对于以上使用GROUP BY子句分组统计的结果，还可以根据HAVING子句中逻辑表达式指定的条件进行筛选。HAVING子句的逻辑表达式通常包含聚合函数，值得注意的是聚合函数不能放在WHERE子句的逻辑表达式中。HAVING逻辑表达式六、HAVING子句限定查询【例7-29】从选课表“SC”和学生表“Student”中查询总成绩超过150分的学生的学号、姓名和总成绩。代码：SELECT SC.SID,Student.Sname,'总成绩'=SUM(Scores)--求每位学生成绩的总和FROM SC,StudentWHERE SC.SID=Student.SIDGROUP BY SC.SID,Student.Sname--按照学号SID分组HAVINGSUM(Scores)>150--对学生总成绩进行筛选HAVING逻辑表达式六、HAVING子句限定查询代码：SELECT SC.SID,Student.Sname,'总成绩'=SUM(Scores)--求每位学生成绩的总和FROM SC,Student WHERE SC.SID=Student.SIDGROUP BY SC.SID,Student.Sname --按照学号SID分组HAVINGSUM(Scores)>150 --对学生总成绩进行筛选查询结果：说明：WHERE SUM(Scores)>150是完全错误的，因为聚合函数SUM(Scores)不能放在WHERE子句的逻辑表达式中。HAVING逻辑表达式七、ORDER BY子句排序查询语法：ORDER BY｛表达式[ASC/DESC]｝[,...n]说明：通常表达式为表的列名。按一列或多列（最多8060个字节）对查询结果进行升序（ASC：默认）或降序（DESC）排序。如果ORDER BY子句后是一个表达式表，则系统将根据各列表达式的次序决定排序的优先级，然后排序。ORDER BY无法对数据类型为varchar(max)、nvarchar(max)、varbinary(max)或xml的列使用，并只能在外查询中使用。如果指定了SELECT DISTINCT（消除重复行），那么ORDER BY子句中的列名就必须出现在SELECT子句的列表中。ORDER BY列名七、ORDER BY子句排序查询【例7-30】从选课表“SC”和学生表“Student”中统计出每位学生的总成绩，并将结果按照总成绩降序排序。代码：SELECT SC.SID,Student.Sname,'总成绩'=SUM(Scores)FROM SC,StudentWHERE SC.SID=Student.SIDGROUP BY SC.SID,Student.SnameORDERBYSUM(SCORES)DESC --按照总成绩降序排序ORDER BY列名七、ORDER BY子句排序查询代码：SELECT SC.SID,Student.Sname,'总成绩'=SUM(Scores)FROM SC,StudentWHERE SC.SID=Student.SIDGROUP BY SC.SID,Student.SnameORDERBYSUM(SCORES) DESC --按照总成绩降序排序查询结果：ORDER BY列名七、ORDER BY子句排序查询【例7-31】从教务管理数据库“EDUC”中查询出每个学生的选课门数并按选课门数的多少进行升序排序。代码：SELECT Student.SID AS学号,Student.Sname AS姓名,COUNT(*) AS选课门数FROM Student,SCWHERE Student.SID=SC.SIDGROUP BY Student.SID,Student.SnameORDER BYCOUNT(*) --按选课门数升序排序ORDER BY列名七、ORDER BY子句排序查询代码：SELECT Student.SID AS学号,Student.Sname AS姓名,COUNT(*) AS选课门数FROM Student,SCWHERE Student.SID=SC.SIDGROUP BY Student.SID,Student.SnameORDER BYCOUNT(*) --按选课门数升序排序查询结果：ORDER BY列名七、ORDER BY子句排序查询【例7-32】从学生表“Student”中查询出软件技术专业学生信息，并按照性别升序和入学录取成绩降序排列。代码：SELECT *FROM StudentWHERE Specialty='软件技术'ORDER BYSex ASC,AScores DESC--按照性别升序，再按照入学录取成绩降序排列ORDER BY列名七、ORDER BY子句排序查询代码：SELECT * FROM Student WHERE Specialty='软件技术'ORDER BYSex ASC,AScores DESC--按照性别升序，再按照入学录取成绩降序排列查询结果：ORDER BY列名八、INTO子句保存查询语法：INTO新表名说明：INTO子句指定使用结果集来创建新表。查询结果往往需要保存下来以便使用，使用INTO子句可以将查询结果存储在一个新建的基本表中，这种方式常用于创建表的副本。INTO新表名八、INTO子句保存查询【例7-33】从选课表“SC”中将学号和课程号的内容保存为新表student_course。代码：SELECT SID,CIDINTOstudent_course --创建新表FROM SC查询结果：INTO新表名SELECT数据查询（基本）小结

展开更多......

收起↑