第4章数据库的基本应用(4.2-4.3)

任课人：cws第4章

数据库的基本应用章节内容4.1管理表4.2数据查询4.3数据查询任务实现4.4数据更新4.5数据更新任务实现4.6课堂实践4.2数据查询4.2.1单表无条件查询4.2.2单表有条件查询4.2.3聚集函数的使用4.2.4分组与排序4.2.5多表连接查询4.2.6嵌套查询4.2.7集合查询4.2数据查询学生成绩管理系统中，肯定设计到包括学生基本信息浏览和查询、成绩查询、各种数据的统计等内容，如：这些操作应该如何实现呢？4.2数据查询数据查询是数据库中最常见的操作。SQL语言是通过SELECT语句来实现数据查询的；基本语法如下：4.2.1单表无条件查询1.语法格式SELECT[ALL|DISTINCT][TOPN[PERCENT]]<选项>[AS<显示列名>][,<选项>[AS<显示列名>][,...]]FROM<表名|视图名>2.说明(1)

ALL：表示输出所有记录，包括重复记录。默认值为ALL。

DISTINCT：表示在查询结果中去掉重复值。(2)

TOPN：返回查询结果集中的前N行。

加[PERCENT]：返回查询结果集中的前N%行。N的取值范围是0～100(3)选项：查询结果集中的输出列。可为字段名、表达式或函数。用“*”表示表中的所有字段。若选项为表达式或函数，输出的列名系统自动给出，不是原字段名，故用AS重命名。(4)显示列名：在输出结果中，设置选项显示的列名。用引号定界或不定界。(5)表名：要查询的表。表不需打开，到当前路径下寻找表所对应的文件。4.2.1单表无条件查询1)查询指定列【例4-6】查询全体学生的学号和姓名。【例4-7】查询全体学生的姓名、学号、所在系。SELECTsno,snameFROMstudentSELECTsname,sno,sdeptFROMstudent4.2.1单表无条件查询1)查询指定列【例4-8】查询选修了课程的学生学号(本例是对表4-12成绩表sc的查询）SELECTDISTINCTsnoFROMsc4.2.1单表无条件查询2)查询全部列【例4-9】查询全体学生的详细记录。【例4-10】输出学生表中的前10条记录。SELECT*FROMstudentSELECTTOP10*FROMstudent思考：若是查询前10%的记录呢？4.2.1单表无条件查询3)查询经过计算的列【例4-11】查询全体学生的姓名及其年龄SELECTsname,YEAR(GETDATE())-YEAR(sbirthday)AS年龄FROMstudent4.2.1单表无条件查询3)查询经过计算的列【例4-12】查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名，同时为姓名列指定别名为NAME，出生年份所在列指定别名为年份，系别所在列指定别名为系别。SELECTsnameNAME,'出生年份:'AS

生日,年份=YEAR(sbirthday),LOWER(sdept)系别FROMstudent注意：指定列名的3种方法，教材有误！4.2.1单表无条件查询3)查询经过计算的列【例4-13】将sc表中的学生成绩增加20%后输出。SELECTsno,cno,degree*1.2as成绩FROMSC4.2.1单表无条件查询4.查询结果的输出Transact-SQL提供了一个扩展特性，允许用户使用SELECT语句查询得到的结果记录来创建一个新的数据表，创建新表使用INTO子句。INTO子句不能单独使用，它包含在SELECT语句中。一般用于创建一个临时表。INTO子句的语法格式如下：INTO<新表名>4.2.1单表无条件查询4.查询结果的输出【例4-14】使用INTO子句创建一个新表，存放student表中的姓名和系别两列。SELECTsname,sdept

INTOstudtempFROMStudent4.2.2单表有条件查询1.语法格式SELECT[ALL|DISTINCT]<选项>[AS<显示列名>][,<选项>[AS<显示列名>][,...]]FROM<表名|视图名>WHERE<条件表达式>4.2.2单表有条件查询2.WHERE条件中的运算符4.2.2单表有条件查询1)比较运算符使用比较运算符实现对查询条件进行限定，其语法格式如下：WHERE

表达式1比较运算符表达式2【例4-15】查询所有男生的信息。SELECT*FROMstudentWHEREssex='男'4.2.2单表有条件查询1)比较运算符使用比较运算符实现对查询条件进行限定，其语法格式如下：WHERE

表达式1比较运算符表达式2【例4-16】查询所有成绩大于80分的学生的学号和成绩。SELECTsnoAS'学号',degree'成绩'FROMscWHEREdegree>804.2.2单表有条件查询1)比较运算符【例4-17】查询所有男生的学号、姓名、系别及出生日期。【例4-18】查询计算机系全体学生的名单。SELECTsno,sname,sdept,sbirthFROMstudentWHEREssex='男'SELECTsnameFROMstudentWHEREsdept='计算机系'4.2.2单表有条件查询1)比较运算符【例4-19】查询考试成绩不及格的学生的学号。SELECTDISTINCTsnoFROMscWHEREdegree<604.2.2单表有条件查询2)逻辑运算符WHERE子句中可以使用逻辑运算符AND、OR和NOT，这3个逻辑运算符可以混合使用。其语法格式如下：WHERENOT逻辑表达式|逻辑表达式1逻辑运算符逻辑表达式4.2.2单表有条件查询2)逻辑运算符【例4-20】查询计算机系女生的信息。【例4-21】查询成绩在90分以上或不及格的学生学号和课号信息。SELECT*FROMstudentWHEREsdept='计算机系'ANDssex='女'SELECTsno,cnoFROMscWHEREdegree>90ordegree<604.2.2单表有条件查询2)逻辑运算符【例4-22】查询非计算机系的学生信息。SELECT*FROMstudentWHERENOTsdept='计算机系'或：SELECT*FROMstudentWHEREsdept<>'计算机系’4.2.2单表有条件查询课堂练习：4.2.2单表有条件查询3)范围运算符(BETWEENAND)在WHERE子句中使用BETWEEN关键字查找在某一范围内的数据，也可以使用NOTBETWEEN关键字查找不在某一范围内的数据。其语法格式如下：WHERE

表达式[NOT]BETWEEN

初始值AND

终止值4.2.2单表有条件查询3)范围运算符(BETWEENAND)【例4-23】查询成绩在60～70分之间的学生学号及成绩。SELECTsno,degreeFROMscWHEREdegreeBETWEEN60AND70条件表达式的另一种表示方法是：degree>=60ANDdegree<=704.2.2单表有条件查询4)字符匹配符(LIKE)在WHERE子句中使用字符匹配符LIKE或NOTLIKE可以把表达式与字符串进行比较，从而实现对字符串的模糊查询。其语法格式如下：WHERE

表达式[NOT]LIKE'字符串'[ESCAPE'换码字符']通配符：%和_ESCAPE：字符串中本身含有%和_时，对通配符进行转义。4.2.2单表有条件查询4)字符匹配符(LIKE)通配符说明：4.2.2单表有条件查询4)字符匹配符(LIKE)【例4-24】查询所有姓李的学生的个人信息【例4-25】查询生源地不是山东省的所有学生信息。SELECT*FROMstudentWHEREsnameLIKE'李%'SELECT*FROMstudentWHEREsaddressNOTLIKE'%山东省%'4.2.2单表有条件查询4)字符匹配符(LIKE)【例4-26】查询名字中第2个字为“阳”字的学生的姓名和学号。【例4-27】查询学号为“20050101”的学生姓名和性别。SELECTsname,snoFROMstudentWHEREsnameLIKE'_阳%‘SELECTsname,ssexFROMstudentWHEREsnoLIKE'20080322'4.2.2单表有条件查询4)字符匹配符(LIKE)【例4-28】查询DB_Design课程的课程号SELECTcnoFROMcourseWHEREcnameLIKE'DB\_Design'ESCAPE'\’4.2.2单表有条件查询5)列表运算符在WHERE子句中，如果需要确定表达式的取值是否属于某一列表值之一时，就可以使用关键字IN或NOTIN来限定查询条件。其语法格式如下：WHERE

表达式[NOT]IN值列表4.2.2单表有条件查询5)列表运算符【例4-29】查询信息系、软件系和计算机系学生的姓名和性别。SELECTsname,ssexFROMstudentWHEREsdeptIN('计算机系','软件系','信息系')4.2.2单表有条件查询6)涉及空值的查询当数据表中的值为NULL时，可以使用ISNULL关键字的WHERE子句进行查询，反之要查询数据表的值不为NULL时，可以使用ISNOTNULL关键字。基本语法格式为：WHERE

字段IS[NOT]NULL4.2.2单表有条件查询6)涉及空值的查询【例4-30】某些学生选修课程后没有参加考试，所以有选修记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。【例4-31】查询所有有成绩的学生学号和课程号。SELECTsno,cnoFROMscWHEREdegreeISNULLSELECTsno,cnoFROMscWHEREdegreeISNOTNULL4.2.3聚集函数的使用SQLServer的聚集函数是综合信息的统计函数，也称为聚合函数或集函数，包括计数、求最大值、求最小值、求平均值和求和等。聚集函数可作为列标识符出现在SELECT子句的目标列或HAVING子句的条件中。4.2.3聚集函数的使用SQLServer的聚集函数在SQL查询语句中，如果有GROUPBY子句，则语句中的函数为分组统计函数；否则，语句中的函数为全部结果集的统计函数。4.2.2单表有条件查询【例4-32】查询学生总数。【例4-33】查询选修了课程的学生人数。SELECTCOUNT(*)FROMstudentSELECTCOUNT(DISTINCTsno)FROMsc4.2.2单表有条件查询【例4-34】计算C01号课程的学生平均成绩。【例4-35】查询选修了C01号课程的学生最高分和最低分。SELECTAVG(degree)FROMscWHEREcno='C01'SELECTMAX(degree)最高分,MIN(degree)最低分FROMscWHEREcno='C01'4.2.2单表有条件查询【例4-36】查询学号为“20050101”的学生的总成绩及平均成绩。【例4-37】查询有考试成绩的学生人数。SELECTSUM(degree)AS总成绩,AVG(degree)AS平均成绩

FROMscWHEREsno='20050101‘SELECTCOUNT(DISTINCTsno)FROMscWHEREdegreeISNOTNULL4.2.4分组与排序1.对查询结果集进行分组使用GROUPBY子句可以将查询结果按照某一列或多列数据值进行分类，换句话说，就是对查询结果的信息进行归纳，以汇总相关数据。其语法格式如下:[GROUPBY列名清单][HAVING

条件表达式]

GROUPBY子句把查询结果集中的各行按列名清单进行分组，在这些列上，对应值都相同的记录分在同一组。若无HAVING子句，则各组分别输出；若有HAVING子句，只有符合HAVING条件的组才输出4.2.4分组与排序【例4-38】统计各系学生数。【例4-39】统计student表中男、女学生人数SELECTsdept,COUNT(*)FROMstudentGROUPBYsdeptSELECTssex,COUNT(*)FROMstudentGROUPBYssex4.2.4分组与排序【例4-40】统计各系男、女生人数。【例4-41】统计各系女生人数。SELECTsdept，ssex，COUNT(*)FROMstudentGROUPBYsdept,ssexSELECTsdept,COUNT(*)FROMstudentWHEREssex='女'GROUPBYsdeptSELECTsdept,COUNT(*)FROMstudentGROUPBYsdept,ssexHAVINGssex=‘女’4.2.4分组与排序【例4-42】查询选修了3门以上课程的学生学号。SELECTsnoFROMscGROUPBYsnoHAVINGCOUNT(*)>4.2.4分组与排序2.对查询结果集进行排序用户可以利用ORDERBY子句对查询结果按照一个或多个字段进行升序(ASC)或降序(DESC)排序，默认值为升序。语句语法为：[ORDERBY<列名1>[ASC|DESC][,<列名2>[ASC|DESC][,…]4.2.4分组与排序2.对查询结果集进行排序【例4-43】查询选修了C03号课程的学生的学号及其成绩，查询结果按分数的降序排列。SELECTsno,degreeFROMscWHEREcno='C03'ORDERBYdegreeDESC4.2.4分组与排序2.对查询结果集进行排序【例4-44】查询全体学生情况，查询结果按所在系升序排列，同一系中的学生按出生日期降序排列。SELECT*FROMstudentORDERBYsdeptASC,sbirthDESC4.2.5多表连接查询多表连接查询指查询同时涉及两个或两个以上的表；分为：交叉连接、内连接、自连接和外连接。4.2.5多表连接查询广义笛卡儿积：两个分别为n目(即关系具有n个属性)和m目的关系R和S的广义笛卡儿积是一个(n+m)列的元组的集合。记作：R×S=｛

│trRtsS｝R×S元组的前n列是关系R的一个元组，后m列是关系S的一个元组。若R有k1个元组，S有k2个元组，则关系R和关系S的广义笛卡儿积有kl×k2个元组。4.2.5多表连接查询广义笛卡儿积：

R×SR.AR.BR.CS.AS.BS.Ca1b1c1a1b2c2a1b1c1a1b3c2a1b1c1a2b2c1a1b2c2a1b2c2a1b2c2a1b3c2a1b2c2a2b2c1a2b2c1a1b2c2a2b2c1a1b3c2a2b2c1a2b2c1ABCABCa1b1c1a1b2c2a1b2c2a1b3c2a2b2c1a2b2c14.2.5多表连接查询1.交叉连接交叉连接又称笛卡儿连接，是指两个表之间做笛卡儿积操作，得到结果集的行数是两个表的行数的乘积。命令的一般格式如下:SELECT[ALL|DISTINCT][别名.]<选项1>[AS<显示列名>][,[别名.]<选项2>[AS<显示列名>]...]FROM<表名1>[别名1],<表名2>[别名2]需要连接查询的表名在FROM子句中指定，表名之间用英文逗号隔开；如果表名指定了别名，在该命令中，都必须用别名代替表名。4.2.5多表连接查询1.交叉连接【例4-45】成绩表(sc)和课程名称表(course)进行交叉连接。SELECTA.*,B.*FROMcourseA,scB4.2.5多表连接查询2.内连接内连接命令的一般格式如下：SELECT[ALL|DISTINCT][别名.]<选项1>[AS<显示列名>][,[别名.]<选项2>[AS<显示列名>][,...]]FROM<表名1>[别名1],<表名2>[别名2][,…]WHERE<连接条件表达式>[AND<条件表达式>]或者为：SELECT[ALL|DISTINCT][别名.]<选项1>[AS<显示列名>][,[别名.]<选项2>[AS<显示列名>][,...]]FROM<表名1>[别名1]INNERJOIN<表名2>[别名2]ON<连接条件表达式>[WHERE<条件表达式>]4.2.5多表连接查询2.内连接连接条件表达式的一般格式为：[<表名1>]<别名1.列名><比较运算符><表名2><别名2.列名>比较运算符：使用等号“=”：等值连接使用不等号：不等值连接4.2.5多表连接查询2.内连接【例4-46】查询每个学生及其选修课的情况学生的基本情况存放在student表中，选课情况存放在sc表中，所以查询过程涉及上述两个表。这两个表是通过公共字段sno实现内连接的。SELECTA.*,B.*FROMstudentA,scBWHEREA.sno=B.sno4.2.5多表连接查询自然连接：若在等值连接中把目标列中的重复字段去掉，则称为自然连接。【例4-47】用自然连接完成查询:查询每个学生及其选修课的情况。SELECTstudent.sno,sname,ssex,sbirth,sdept,cno,degreeFROMstudent,scWHEREstudent.sno=sc.sno4.2.5多表连接查询【例4-48】输出所有女学生的学号、姓名、课号及成绩。SELECTA.sno,sname,cno,degreeFROMstudentA,scBWHEREA.sno=B.snoANDssex='女’SELECTA.sno,sname,cno,degreeFROMstudentAINNERJOINscBONA.sno=B.snoWHEREssex='女’4.2.5多表连接查询【例4-49】输出计算机系学生的学号、姓名、课程号及成绩。SELECTA.sno,sname,cno,degreeFROMstudentA,scBWHEREA.sno=B.snoANDsdept=‘CS‘SELECTA.sno,sname,cno,degreeFROMstudentAINNERJOINscBONA.sno=B.snoWHEREsdept=‘CS’4.2.5多表连接查询3.自连接:将同一个表的不同行连接起来在自连接中，必须为表指定两个别名，使之在逻辑上成为两张表。自连接的命令的一般格式如下:SELECT[ALL|DISTINCT][别名.]<选项1>[AS<显示列名>][,[别名.]<选项2>[AS<显示列名>][,...]]FROM<表名1>[别名1],<表名1>[别名2][,…]WHERE<连接条件表达式>[AND<条件表达式>]4.2.5多表连接查询3.自连接:【例4-50】查询同时选修了C01和C04课程的学生学号。【例4-51】查询与刘晨在同一个系学习的学生的姓名和所在系。SELECTsnoFROMstudentA,studentBWHEREA.sno=B.snoANDA.cno='C01'ANDB.cno='C04'SELECTB.sname,B.sdeptFROMstudentA,studentBWHEREA.sdept=B.sdeptANDA.sname='刘晨'ANDB.sname!='刘晨'4.2.5多表连接查询4.外连接在自然连接中，只有在两个表中匹配的行才能在结果集中出现。而在外连接中可以只限制一个表，而对另外一个表不加限制(所有的行都出现在结果集中)。外连接分为左外连接、右外连接和全外连接。4.2.5多表连接查询4.外连接外连接命令的一般格式如下:SELECT[ALL|DISTINCT][别名.]<选项1>[AS<显示列名>][,[别名.]<选项2>[AS<显示列名>][,...]]FROM<表名1>LEFT|RIGHT|FULL[OUTER]JOIN<表名2>ON<表名1.列1>=<表名2.列2>4.2.5多表连接查询4.外连接【例4-52】利用左外连接查询改写例4-46(查询每个学生及其选修课的情况)。SELECTstudent.sno,sname,ssex,sbirth,sdept,cno,degreeFROMstudentLEFTJOINscONstudent.sno=sc.sno4.2.6嵌套查询在SQL语言中，一个SELECT—FROM—WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING子句的条件中称为嵌套查询或子查询。例如：SELECTsnameFROMstudentWHEREsnoIN(SELECTsnoFROMscWHEREcno='C02')4.2.6嵌套查询注意问题：SQL语言允许多层嵌套查询；子查询的SQL语句必须用括号定界；子查询不能使用ORDERBY子句，ORDERBY子句只能对最终查询结果排序；嵌套查询的求解方法是由内向外处理；子查询一般分为两种：嵌套子查询和相关子查询。4.2.6嵌套查询1.嵌套子查询（不相关子查询）嵌套子查询的执行不依赖于外部嵌套。嵌套子查询的执行过程为：首先执行子查询，子查询得到的结果集不被显示出来，而是传给外部查询，作为外部查询的条件使用，然后执行外部查询，并显示查询结果。子查询可以多层嵌套。嵌套子查询一般也分为两种：子查询返回单个值和子查询返回一个值列表。4.2.6嵌套查询1）返回单个值【例4-53】查询所有年龄大于平均年龄的学生姓名。SELECTsnameFROMstudentWHEREyear(getdate())-year(sbirthday)>(SELECTAVG(year(getdate())-year(sbirthday))FROMstudent)4.2.6嵌套查询1）返回单个值【例4-54】查询与刘晨在同一个系学习的学生。SELECTsno,sname,sdeptFROMstudentWHEREsdept=(SELECTsdeptFROMstudentWHEREsname='刘晨')4.2.6嵌套查询2)返回一个值列表（1）使用IN操作符的嵌套查询。【例4-55】用IN操作符改写例4-54——查询与刘晨在同一个系学习的学生。SELECTsno,sname,sdeptFROMstudentWHEREsdeptIN(SELECTsdeptFROMstudentWHEREsname='刘晨')4.2.6嵌套查询2)返回一个值列表（1）使用IN操作符的嵌套查询。【例4-56】查询没有选修数学的学生学号和姓名。SELECTsno,snameFROMstudentWHEREsnoNOTIN(SELECTsnoFROMscWHEREcnoIN(SELECTcnoFROMcourseWHEREcname='数学'))4.2.6嵌套查询(2)带有ANY或ALL操作符的子查询。ANY和ALL操作符在使用时必须和比较运算符一起使用。其格式为：<字段><比较符>[ANY|ALL]<子查询>。4.2.6嵌套查询(2)带有ANY或ALL操作符的子查询。4.2.6嵌套查询(2)带有ANY或ALL操作符的子查询。【例4-57】查询其他系中比计算机系某一学生年龄小的学生姓名和年龄。SELECTsname,sageFROMstudentWHEREsage<ANY(SELECTsageFROMstudentWHEREsdept='计算机系')ANDsdept<>‘计算机系’//该句为父查询中的一个条件4.2.6嵌套查询(2)带有ANY或ALL操作符的子查询。【例4-58】查询其他系中比计算机系学生年龄都小的学生。SELECT*FROMstudentWHEREsage<ALL(SELECTsageFROMstudentWHEREsdept='计算机系')ANDsdept<>'计算机系'4.2.6嵌套查询(2)带有ANY或ALL操作符的子查询。【例4-58】查询其他系中比计算机系学生年龄都小的学生。一般聚集函数实现的子查询效率高于ANY或ALL查询。SELECT*FROMstudentWHEREsage<(SELECTMIN(sage)FROMstudentWHEREsdept='计算机系')ANDsdept<>'计算机系'4.2.6嵌套查询2.相关子查询(CorrelatedSubquery)在相关子查询中，子查询的执行依赖于外部查询，即子查询的查询条件依赖于外部查询的某个属性值。相关子查询的子查询需要重复被执行，而嵌套子查询的子查询只需被执行一次!在相关子查询中，经常要用到EXISTS操作符，EXISTS只产生逻辑真值“true”和逻辑假值“false”,不返回任何实际数据。4.2.6嵌套查询2.相关子查询(CorrelatedSubquery)【例4-59】查询所有选修了C01号课程的学生姓名。SELECTsnameFROMstudentWHEREEXISTS(SELECT*FROMscWHEREsno=student.snoANDcno='C01')4.2.6嵌套查询2.相关子查询(Corre