数据库原理与应用锦州师范高等专科学校计算机系省名师优质课赛课获奖课件市赛课百校联赛优质课一等奖课件

数据库原理与应用

锦州师范高等专科学校

计算机系1/5910/5/1第三章数据库查询和试图3.1SELECT语句查询3.1.1选择列3.1.2选择行3.1.3FROM子句3.1.4数据汇总3.1.5排序3.1.6SELECT语句其它子句3.2视图3.2.1视图建立3.2.2视图修改3.2.3视图删除2/5910/5/第三章数据库查询和试图

Transact-SQLSELECT语句从一个或者多个表或视图中检索统计，使用查询分析器输入一条SELECT语句，查询情况将在结果窗口中显示。能够编辑或打印这些结果，而且把它们保留在非数据库文件中。3.1SELECT语句查询3.1.1选择列●SELECT语句中SELECT子句指定要返回列；WHERE子句指定选择条件来限制返回结果中行。1.指定列

语法形式以下：SELECT列名1[，…n]FROM表名1[，…n]

3/5910/5/第三章数据库查询和试图【例3-1】选择XSQK表中学号、姓名和专业名三列。SELECT学号,姓名,专业名FROMXSQK2.使用通配符（*）【例3-2】选择XSQK表全部列。SELECT*FROMXSQK3.使用列表示式●在SELECT子句中能够使用算术运算符对数字型数据列进行（+、-、*、/和%）。●注意：取模运算时，%两边必须是整数型数据。4/5910/5/第三章数据库查询和试图【例3-3】查询XS_KC表，并增加了一个计算列，使其为成绩列加5。SELECT学号,课程号,成绩,成绩+5FROMXS_KC4.改变查询结果中列名①第一个方法：“=”法。【例3-4】为【例3-3】中计算列指定了一个列名“调整后成绩”。SELECT学号,课程号,成绩,调整后成绩=成绩+5FROMXS_KC②第二种方法：“AS”法。【例3-5】为【例3-4】中成绩列指定了一个新列名“原成绩”。5/5910/5/第三章数据库查询和试图

SELECT学号,课程号,成绩AS原成绩,调整后成绩=成绩+5FROMXS_KC5.增加说明列●目标：增加查询结果可读性。【例3-6】在查询结果中备注列前增加了一个说明列。SELECT学号,姓名,’职务是‘,备注FROMXSQK【例3-7】说明文字串中有单引号情况。SELECT学号,姓名,’其’’职务’’是‘,备注FROMXSQK6/5910/5/第三章数据库查询和试图6.使用系统函数●包含：聚合函数、数学函数、字符串函数、日期和时间函数以及其它一些系统函数。①AVG函数：返回指定列算术平均值。【例3-8】计算XS_KC表中成绩列算术平均值，并为列指定列名为“平均值”SELECTAVG(成绩)AS平均FROMXS_KC②COUNT函数：返回指定列数据统计行数，不包含全部为NULL值统计行。【例3-9】选择计算XSQK表中学生统计行数。SELECTCOUNT(学号)FROMXSQK7/5910/5/第三章数据库查询和试图【例3-10】计算XS_KC表中成绩不及格课程统计总数，并指定列名。SELECTCOUNT(学号)AS‘不及格课程总数’FROMXS_KC

WHERE成绩<60●DISTINCT：指定返回是不一样统计行数。【例3-11】计算XS_KC表中不及格学生统计总数。SELECTCOUNT(DISTINCT学号)AS‘不及格课程总数’FROMXS_KC

WHERE成绩<60③MAX函数：返回指定列最大值。【例3-12】计算XS_KC表中成绩列最大值。8/5910/5/第三章数据库查询和试图SELECTMAX(成绩)AS最高分FROMXS_KC④MIN函数：返回指定列最小值。【例3-13】计算XS_KC表中成绩列最小值。SELECTMIX(成绩)AS最低分FROMXS_KC⑤SUM函数：返回指定列总和。【例3-14】用于计算XS_KC表中成绩列总和值，并指定列名。SELECTSUM(成绩)AS总分FROMXS_KC9/5910/5/第三章数据库查询和试图3.1.2选择行●语法形式：SELECT列名1[，…n]FROM表名1[，…n]WHERE查询条件1.使用比较运算符【例3-15】查询XS_KC表成绩不及格学生统计。SELECT学号,课程号,成绩FROMXS_KCWHERE成绩<60●比较运算符不但可用于比较数字类型列，也可用于比较字符类型列，因为SQLServer字符都用二进制ASCII码表示，故能够比较大小。运算符描述=等于>大于<小于>=大于等于<=小于等于<>,!=不等于!>小于!<大于表3.1比较运算符10/5910/5/第三章数据库查询和试图【例3-16】查询XS_KC表中学号小于“01”学生统计。SELECT学号,课程号,成绩FROMXS_KCWHERE成绩<‘01’●注意：系统不区分大小写。2.使用字符串含糊匹配在大多数情况下，LIKE子句与通配符配合使用，以满足复杂查询条件。SQLServer提供了4种通配符，百分号、下划线、[]和字符^。11/5910/5/第三章数据库查询和试图●语法形式：SELECT列名1[，…n]FROM表名1[，…n]WHERE列名LIKE’匹配字符串‘12/5910/5/第三章数据库查询和试图运算符描述%代表任意长度字符串_代表任意一个字符[]指定某个字符取值范围[^]指定某个字符要排除取值范围表5.2匹配符含义【例3-17】查询全部陈姓学生统计。SELECT学号,姓名FROMXSQKWHERE姓名LIKE‘陈%’13/5910/5/第三章数据库查询和试图【例3-18】查询全部书名为“SQLServer”字符串书。SELECT书号,书名FROMpublishWHERE书名LIKE‘SQLServer%’【例3-19】查询全部陈姓单名学生统计。SELECT学号,姓名FROMXSQKWHERE姓名LIKE‘陈_’【例3-20】查询全部姓介于“岑”到“方”或者介于“薛”到“张”学生统计。SELECT学号,姓名FROMXSQKWHERE姓名LIKE‘[岑-方，薛-张]%’14/5910/5/第三章数据库查询和试图●在【例3-20】中，汉字字符在比较时是比较其拼音序列。【例3-21】查询全部姓不介于“岑”到“方”或者介于“薛”到“张”学生统计。SELECT学号,姓名FROMXSQKWHERE姓名LIKE‘[^岑-方，薛-张]%’●注意：使用了LIKE关键字，会造成SQLServer不使用与指定表索引。所以，不推荐在大型表上使用含糊匹配，查询时间会很长。3.使用逻辑运算符●包含：与AND，或OR，非NOT语法格式：15/5910/5/第三章数据库查询和试图SELECT列名1[，…n]FROM表名1[，…n]WHERE[NOT]查询条件|AND|OR|[NOT]查询条件[…n]【例3-22】查询XSQK表中备注列不为空统计。SELECT学号,姓名,备注AS职务FROMXSQKWHERE备注LIKE‘%’【例3-23】查询XS_KC表中“101”号课程成绩介于80~89统计。SELECT学号,课程号,成绩FROMXS_KCWHERE课程号=‘101’AND成绩>=80AND成绩<=8916/5910/5/第三章数据库查询和试图【例3-24】查询XS_KC表中不满足“101”号课程成绩大于等于60统计。SELECT学号,课程号,成绩FROMXS_KCWHERE课程号=‘101’ANDNOT成绩>=60【例3-25】查询XS_KC表中不满足“101”号课程成绩介于60~70统计。SELECT学号,课程号,成绩FROMXS_KCWHERE课程号=‘101’ANDNOT（成绩>=60AND成绩<=70）17/5910/5/第三章数据库查询和试图4.使用查询范围除了比较运算符，还有另一个指定列值范围方法。●语法格式为：SELECT列名1[，…n]FROM表名1[，…n]WHERE列名BETWEEN取值范围下界AND取值范围上界

●将【例3-23】改成以下形式，查询结果不变。SELECT学号,课程号,成绩FROMXS_KCWHERE课程号=‘101’AND成绩BETWEEN80AND8918/5910/5/第三章数据库查询和试图5.使用查询集合（关键字IN）●语法格式：SELECT列名1[，…n]FROM表名1[,…n]WHERE列名IN（列值1[，…n]）【例3-26】查询XS_KC表中课程号“101”、“103”或“107”学生成绩统计。SELECT学号,课程号,成绩FROMXS_KCWHERE课程号IN（’101’,’103’,’107’）19/5910/5/第三章数据库查询和试图【例3-27】把【例3-26】改成用OR运算符连接形式，查询结果不变。SELECT学号,课程号,成绩FROMXS_KCWHERE课程号=‘101’OR课程号=‘103’OR课程号=‘107’●关键字IN经常使用于SELCET语句嵌套形式中。【例3-28】使用IN嵌套查询实现最少有一门课程不及格学生名单。SELECT学号,姓名,联络电话FROMXSQKWHERE学号IN（SELECTDISTINCT学号FROMXS_KCWHERE成绩<60）20/5910/5/第三章数据库查询和试图3.1.3FROM子句●FROM子句语法格式：FROM{表名|视图名}[，…n]【例3-29】显示了为XSQK表指定了别名“学生情况表”，并在WHERE子句中使用了该别名。SELECT学号,姓名,联络电话FROMXSQK学生情况表WHERE学生情况表.学号=‘01’3.1.4数据汇总1.使用聚合函数AVG、MAX、MIN、SUM、COUNT和COUNT（*）●注意：列值为NULL数据统计不包含在聚合函数运算中。21/5910/5/第三章数据库查询和试图2.使用分组汇总子句●语法格式：SELECT列名1[,…n]FROM表名1[，…n]GROUPBY列名1[,…n]HAVING逻辑表示式【例3-30】按课程分组汇总每一组课程平均成绩。SELECT课程号,AVG(成绩)AS平均分FROMXS_KCGROUPBY课程号●注意：假如GROUPBY后指定了多个列，则先按第一列进行分组并按升序排列，再按第二列进行，依这类推。22/5910/5/第三章数据库查询和试图【例3-31】按班级、学号分组汇总全部课程平均分。SELECT班级,AVG(成绩)AS平均分FROMXS_KCGROUPBY班级,学号【例3-32】按学号、班级分组汇总全部课程平均分。SELECT班级,AVG(成绩)AS平均分FROMXS_KCGROUPBY学号,班级【例3-33】用HAVING选项，保留平均分大于70且小于80组。SELECT班级,学号,AVG(成绩)AS平均分FROMXS_KCGROUPBY学号,班级HAVINGAVG(成绩)>70ANDAVG(成绩)<8023/5910/5/第三章数据库查询和试图【例3-34】统计1班各课程不及格人数。SELECT班级,课程号,COUNT(成绩)AS不及格人数FROMXS_KCWHERE成绩<60GROUPBY班级,课程号HAVING班级=13.使用WITHROLLUP和WITHCUBE选项●使用WITHROLLUP和WITHCUBE选项能够对汇总结果再汇总，生成超级组。【例3-35】数据汇总时不使用WITHROLLUP和WITHCUBE选项。SELECT班级,课程号,COUNT(成绩)AS不及格人数FROMXS_KCWHERE成绩<60GROUPBY班级,课程号24/5910/5/第三章数据库查询和试图【例3-36】对【例3-34】进行数据汇总时使用WITHROLLUP选项。SELECT班级,课程号,COUNT(成绩)AS不及格人数FROMXS_KCWHERE成绩<60GROUPBY班级,课程号WITHROLLUP【例3-37】对【例3-34】进行数据汇总时使用WITHCUBE选项。SELECT班级,课程号,COUNT(成绩)AS不及格人数FROMXS_KCWHERE成绩<60GROUPBY班级,课程号WITHCUBE25/5910/5/第三章数据库查询和试图4.使用明细汇总子句●语法格式：SELECT列名1[,…n]FROM表名1[，…n]WHERE逻辑表示式GROUPBY列名1[,…n][ORDERBY{列名1|列号1[ASC|DESC]}[,…n]]COMPUTE{聚合函数（列名1）}[，…n][BY列名1[，…n]]●注意事项：①假如使用GROUPBY子句，那么SELECT子句中指定列必须在GROUPBY子句中指定、或是和聚合函数一起使用。26/5910/5/第三章数据库查询和试图②COMPUTE子句中列必须出现在SELECT子句列表中。③使用COMPUT[BY]子句就不能同时使用SELECTINTO子句④BY选项表示按指定列进行明细汇总，使用BY选项时必须同时使用ORDERBY子句，且BY选项后列必须含有和ORDERBY后列相同次序，且不能跳过其中列。比如：假如ORDERBY子句按照以下指定列。ORDERBY列A，列B，列C则BY选项后列表只能是下面任一中形式：BY列A，列B，列CBY列A，列BBY列A

27/5910/5/第三章数据库查询和试图【例3-38】使用COMPUTE子句对不及格成绩统计进行明细汇总。SELECT学号,课程号,成绩FROMXS_KCWHERE成绩<60COMPUTECOUNT（成绩）【例3-39】使用COMPUTEBY子句对不及格成绩统计进行明细汇总。SELECT学号,课程号,成绩FROMXS_KCWHERE成绩<60ORDERBY2COMPUTECOUNT（成绩）BY课程号28/5910/5/第三章数据库查询和试图3.1.5排序●假如按一定次序显示数据统计，有两种方法：（为表中列建立簇索引或普通索引。）①当查询指定是单个列，且该列有索引时，查询结果按照该列索引定义次序显示；②不然都按照表簇索引次序显示。【例3-40】查询学生表中“姓名”单个列。SELECT姓名FROMXSQK【例3-41】查询学生表中“姓名”、“联络电话”多个列。SELECT姓名，联络电话FROMXSQK查询结果以下列图：29/5910/5/第三章数据库查询和试图30/5910/5/第三章数据库查询和试图●语法格式：SELECT列名1[,…n]FROM表名1[，…n]ORDERBY{列名1|列号1[ASC|DESC]}[，…n]其中：ASC为默认值，表示按升序排序。【例3-42】查询学生表中“姓名”，并按姓名列降序排序。SELECT姓名FROMXSQKORDERBY姓名DESC【例3-43】按出生日期列升序排列学生表。SELECT学号，姓名，出生日期FROMXSQKORDERBY331/5910/5/第三章数据库查询和试图例3-42例3-4332/5910/5/第三章数据库查询和试图【例3-44】按多个列对学生表排序。SELECT学号，课程号，成绩FROMXS_KCORDERBY2，3DESC33/5910/5/第三章数据库查询和试图3.1.6SELECT语句其它子句1.将查询结果生成表●语法格式：SELECT列名1[,…n]INTO新表名FROM表名1[，…n][WHERE逻辑表示式]【例3-45】将【例3-44】查询结果保留到新表temp_KC中。SELECT学号，课程号，成绩INTOtemp_KCFROMXS_KCORDERBY2，3DESC34/5910/5/第三章数据库查询和试图2.使用连接查询所谓连接查询就是将多个表经过某个或一些列为条件进行连接，从中查询数据。两种语法方式：ANSI连接语法形式以下：SELECT表名.列名1[，…n]FROM{表名1[连接类型]JOIN表名2ON连接条件}[，…n]WHERE查询条件SQLServer连接语法形式以下：SELECT表名.列名1[，…n]FROM表名1[，…n]WHERE{查询条件AND|OR连接条件}[，…n]35/5910/5/第三章数据库查询和试图（1）内连接[INNER]JOIN在内连接中，能够使用等号“=”作为比较运算符，此时称作等值连接；也能够使用不等比较运算符，此时为不等值连接。【例3-46】用ANSI语法表示内连接查询成绩不及格学生信息。SELECTXSQK.学号，姓名，联络电话，课程号，成绩FROMXSQKINNERJOINXS_KCONXSQK.学号=XS_KC.学号36/5910/5/第三章数据库查询和试图（2）外连接外连接包含左外连接、右外连接和全外连接。左外连接包含左表全部数据行，右外连接包含右表全部数据行，全外连接包含两个表全部数据行。

左连接：在连接两个表时，保留左表中不匹配行。

LEFT[OUTER]JOIN

右连接：在连接两个表时，保留右表中不匹配行。

RIGHT[OUTER]JOIN

全连接：在连接两个表时，保留两表中不匹配行。

FULL[OUTER]JOIN全外连接使用FULLOUTERJOIN操作符，它实际上是左外连接与右外连接结合，即操作符两边表中全部数据行均会显示在结果集中。不论哪一个表中没有对应数据行，对应列上都会返回空值。37/5910/5/第三章数据库查询和试图【例3-47】用ANSI语法表示左连接查询。SELECTXSQK.学号，姓名，联络电话，课程号，成绩FROMXSQKLEFTOUTERJOINXS_KCONXSQK.学号=XS_KC.学号【例3-48】用ANSI语法表示右连接查询。SELECTXSQK.学号，姓名，联络电话，课程号，成绩FROMXSQKRIGHTOUTERJOINXS_KCONXSQK.学号=XS_KC.学号【例3-49】用ANSI语法表示全连接查询。SELECTXSQK.学号，姓名，联络电话，课程号，成绩FROMXSQKFULLOUTERJOINXS_KCONXSQK.学号=XS_KC.学号38/5910/5/第三章数据库查询和试图（3）自连接[INNER]JOINJOIN连接操作符还能够用于单个表，此时代表一个表与其本身进行连接，表这种连接操作称为自连接。为了区分列名，必须在自连接中对表指定别名。【例3-50】用ANSI语法表示自连接查询显示每个学生两门课程成绩。SELECTa.学号，a.课程号，a.成绩，b.课程号，b.成绩FROMXS_KCaINNERJOINXS_KCONa.学号=b.学号WHEREa.课程号<b.课程号39/5910/5/第三章数据库查询和试图（4）无限制连接CROSSJOIN无限制连接返回进行连接操作两个表中全部数据行笛卡尔积，得到结果集行数是两个表行数乘积。【例3-51】SELECT*FROMXSQKCROSSJOINXS_KC40/5910/5/第三章数据库查询和试图3.使用联合查询●增加统计行数●语法形式：SELECT语句1UNION[ALL]SELECT语句2【例3-52】SELECT学号FROMXSQKUNION[ALL]SELECT成绩FROMXS_KC41/5910/5/第三章数据库查询和试图4.使用嵌套查询（子查询）将一个SELECT语句嵌套在另一个SELECT语句WHERE子句中查询形式，称为子查询，子查询中嵌套在WHERE子句里SELECT语句称为内部查询，相正确，另一个包含内部查询SELECT语句被称为外部查询。子查询要求服务器先计算内部查询并形成结果，然后外部查询依据内部查询结果，产生最终查询结果。子查询可分为4种类型：[NOT]IN子查询比较子查询[NOT]EXISTS子查询关联子查询42/5910/5/第三章数据库查询和试图（1）[NOT]IN子查询操作符IN用于WHERE子句中时，通惯用来确定某个属性列值是否包含在一个表示式或者常量集合中。一样，操作符IN也能够用于子查询，用来确定某个列值是否在内部查询结果集中。【例3-53】查询无不及格学生课程讲课教师。SELECTDISTINCT讲课教师FROMKCWHERE课程号NOTIN（SELECT课程号FROMXS_KCWHERE成绩<60）43/5910/5/第三章数据库查询和试图（2）比较子查询

比较子查询就是将某个列值与内部查询结果作运算比较，假如比较结果为真则返回该行，不然不返回。比较子查询通常要用到操作符ALL、ANY、SOME。其通用格式为：

<列值><比较运算符>[ALL|ANY|SOME]<内部查询>

ALL

：列值必须和内部查询结果集每一个值进行比较，只有每一次比较结果都为真时，比较结果才为真。

ANY、SOME

：列值和内部查询结果集值进行比较，只要有一次为真，比较结果就为真。44/5910/5/第三章数据库查询和试图【例3-54】查询平均分低于60学生学号。SELECTDISTINCT学号FROMXS_KCaWHERE60>（SELECTAVG(成绩)FROMXS_KCbWHEREb.学号=a.学号）【例3-54】（方法二）查询平均分低于60学生学号。SELECTDISTINCT学号，AVG（成绩）FROMXS_KCGROUPBY学号HAVINGAVG（成绩）<6045/5910/5/第三章数据库查询和试图【例3-55】查询有不及格学生课程号讲课教师。SELECTDISTINCT讲课教师FROMKCWHERE课程号=ANY（SELECT课程号FROMXS_KCWHERE成绩<60）【例3-55】(方法二)查询有不及格学生课程号讲课教师。SELECTDISTINCT讲课教师FROMKCINNERJOINXS_KCONXS_KC.课程号=KC.课程号WHERE成绩<60

46/5910/5/第三章数据库查询和试图（3）[NOT]EXISTS子查询EXISTS是测试子查询是否有数据行返回，假如有则返回TRUE，不然返回FALSE。NOTEXISTS则相反，当结果表为空时，才返回TRUE。【例3-56】查询是否有最少一门课程不及格学生。SELECTDISTINCT学号，姓名FROMXSQKaWHEREEXISTS（SELECT*FROMXS_KCbWHEREb.成绩<60ANDb.学号=a.学号）47/5910/5/第三章数据库查询和试图（4）简单查询

Transact-SQLSELECT语句从一个或者多个表或视图中检索统计，使用查询分析器输入一条SELECT语句，查询情况将在结果窗口中显示。能够编辑或打印这些结果，而且把它们保留在非数据库文件中。指定TOP关键字SQLServer使用了TOP关键字，用于查询时只显示前面几行数据。当SELECT语句检索出来统计非常多，且又无须显示全部统计时，可使用TOP关键字来降低查询时间，提升查询效率。

使用TOP关键字语法以下：SELECT[TOPinteger|TOPintegerPERCENT][WITHTIES]<目标列表示式>[，<目标列表示式>][1…n]FROM<表名>;48/5910/5/第三章数据库查询和试图【例3-57】查询前5行统计。SELECTTOP5姓名FROMXSQK【例3-58】查询前20%统计。SELECTTOP20PERCENT姓名FROMXSQK49/5910/5/第三章数据库查询和试图3.2视图■视图概念

视图不包含任何数据，只是定义在一个或多个基表上或其它视图上，而且提供一个访问基表数据方法。在物理磁盘上存放相关视图信息是：视图名称和视图定义。视图全部数据来自基本表。所以，当基本表数据发生改变时，对应视图中查询出数据也会随之改变。视图一经定义，就能够象基本表一样执行查询、删除等操作，也能够在视图上定义新视图，但对视图更新操作则有一定限制。■视图作用能够满足不一样用户需求

不一样用户对数据库操作有不一样需求，即使相同数据也可能有不一样操作要求。一张基本表可能有很多属性列，利用视图，用户能够把自己感兴趣属性列集中起来，放在一个视图中，今后用户能够将视图作为一张表来对待。50/5910/5/第三章数据库查询和试图能够简化数据读取

查询数据时，通常要用SELECT语句编写复杂连接、统计、函数等，以产生所需要结果。使用视图，能够隐蔽这种复杂性。能够将经惯用到复杂查询语句定义为视图，无须每次查询都写上复杂查询条件，这么就简化了用户查询操作。确保了基本表数据和应用程序逻辑独立性

当应用程序经过视图来访问数据时，视图实际上成为应用程序和基本表数据之间桥梁。假如应用程序直接调用基本表，则一旦基本表数据发生改变时，应用程序必须随之改动。而经过视图访问数据，则能够经过改变视图来适应基本表改变，使应用程序无须作改变，确保了基本表数据和应用程序逻辑独立性。能够提供安全机制

利用视图能够限制数据访问。假如某个用户需要访问表中一些列，但另一些属性列必须对该用户保密，则能够利用视图到达此目标，将视图建立在该用户需要访问那些列上。51/5910/5/第三章数据库查询和试图在代码方式下对视图操作：3.2.1视图建立3.2.2视图修改3.2.3视图删除52/5910/5/第三章数据库查询和试图3.2.1视图建立

SQL语言用CREATEVIEW语句来建立视图，其普通格式为：CREATEVIEW<视图名>[(<列名>[，<列名>]…)]AS<子查询>[WITHCHECKOPTION]；

说明：(1)选项WITHCHECKOPTION确保用户只能查询和修改他们所看到数据，强制全部在视图上使用数据修改语句满足定义视图时条件。(2)组成视图各属性列能够显式指定，也能够省略。假如省略不写，则组成视图各属性列由子查询中SELECT子句各目标列组成。

53/5910/5/第三章数据库查询和试图

以下情形建立视图时必须显式指定属性列：1)视图某列不是原属性列，而是统计函数或者表示式。2)多表连接时选出了两个或者多个同名列作为视图属性列。3)需要对视图中一些列重新命名。【例3-59】创建包含班委组员基本信息视图。CREATEVIEWv_班委组员ASSELECT学号,姓名,联络电话,职务=备