数据库原理与应用锦州师范高等专科学校计算机系课件

数据库原理与应用锦州师范高等专科数据库原理与应用锦州师范高等专科学校计算机系学校计算机系11、不为五斗米折腰。12、芳菊开林耀，青松冠岩列。怀此贞秀姿，卓为霜下杰。13、归去来兮，田蜀将芜胡不归。14、酒能祛百虑，菊为制颓龄。15、春蚕收长丝，秋熟靡王税。第三章第三章 数据库的查询和试图数据库的查询和试图【例【例3-33-3】查询】查询XS_KCXS_KC表，并增加了一个计算列，使其为成绩列加表，并增加了一个计算列，使其为成绩列加5 5。SELECT SELECT 学号学号,课程号课程号,成绩成绩,成绩成绩+5+5 FROM XS_KC FROM XS_KC4.4.改变查询结果中的列名改变查询结果中的列名 第一种方法：第一种方法：“=”“=”法。法。【例【例3-43-4】为【例】为【例3-33-3】中的计算列指定了一个列名】中的计算列指定了一个列名“调整后的调整后的成绩成绩”。SELECT SELECT 学号学号,课程号课程号,成绩成绩,调整后的成绩调整后的成绩=成绩成绩+5+5 FROM XS_KC FROM XS_KC第二种方法：第二种方法：“AS”“AS”法。法。【例【例3-53-5】为【例】为【例3-43-4】中的成绩列指定了一个新列名】中的成绩列指定了一个新列名“原成绩原成绩”。6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图 SELECT 学号学号,课程号课程号,成绩成绩 AS 原成绩原成绩,调整后的成绩调整后的成绩=成绩成绩+5 FROM XS_KC5.增加说明列增加说明列目的：增加查询结果的可读性。目的：增加查询结果的可读性。【例【例3-63-6】在查询结果中备注列前增加了一个说明列。】在查询结果中备注列前增加了一个说明列。SELECT SELECT 学号学号,姓名姓名,职务是职务是,备注备注 FROM XSQK FROM XSQK【例【例3-73-7】说明文字串中有单引号的情况。】说明文字串中有单引号的情况。SELECT SELECT 学号学号,姓名姓名,其其职务职务是是,备注备注 FROM XSQK FROM XSQK6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图6.使用系统函数使用系统函数包括：聚合函数、数学函数、字符串函数、日期和时间函数以包括：聚合函数、数学函数、字符串函数、日期和时间函数以及其他一些系统函数。及其他一些系统函数。AVG函数：返回指定列的算术平均值。函数：返回指定列的算术平均值。【例【例3-83-8】计算】计算XS_KCXS_KC表中成绩列的算术平均值，并为列指定列名表中成绩列的算术平均值，并为列指定列名为为“平均值平均值”SELECT AVG(SELECT AVG(成绩成绩)AS)AS 平均平均 FROM XS_KC FROM XS_KC COUNT COUNT函数：返回指定列的数据记录行数，不包含全部为函数：返回指定列的数据记录行数，不包含全部为NULLNULL值的记录行。值的记录行。【例【例3-93-9】选择计算】选择计算XSQKXSQK表中学生记录的行数。表中学生记录的行数。SELECT COUNT(SELECT COUNT(学号学号)FROM XSQK FROM XSQK6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图【例【例3-103-10】计算】计算XS_KCXS_KC表中成绩不及格的课程记录的总数，并指表中成绩不及格的课程记录的总数，并指定列名。定列名。SELECT COUNT(SELECT COUNT(学号学号)AS)AS 不及格课程总数不及格课程总数 FROM XS_KC FROM XS_KC WHERE 成绩成绩60DISTINCT：指定返回的是不同记录行数。：指定返回的是不同记录行数。【例【例3-113-11】计算】计算XS_KCXS_KC表中不及格学生记录的总数。表中不及格学生记录的总数。SELECT COUNT(SELECT COUNT(DISTINCTDISTINCT 学号学号)AS)AS 不及格课程总数不及格课程总数 FROM XS_KC FROM XS_KC WHERE 成绩成绩60 MAX MAX函数：返回指定列的最大值。函数：返回指定列的最大值。【例【例3-123-12】计算】计算XS_KCXS_KC表中成绩列的最大值。表中成绩列的最大值。6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图 SELECT MAX(成绩成绩)AS 最高分最高分 FROM XS_KC MIN函数：函数：返回指定列的最小值。返回指定列的最小值。【例【例3-133-13】计算】计算XS_KCXS_KC表中成绩列的最小值。表中成绩列的最小值。SELECT MIX(成绩成绩)AS 最低分最低分 FROM XS_KC SUM SUM函数：返回指定列的总和。函数：返回指定列的总和。【例【例3-143-14】用于计算】用于计算XS_KCXS_KC表中成绩列的总和值，并指定列名。表中成绩列的总和值，并指定列名。SELECT SUM(成绩成绩)AS 总分总分 FROM XS_KC6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图3.1.2 选择行选择行语法形式：语法形式：SELECT 列名列名1，n FROM 表名表名1，n WHERE 查询条件查询条件1.使用比较运算符使用比较运算符【例【例3-153-15】查询】查询XS_KCXS_KC表成绩不及格的学生记录。表成绩不及格的学生记录。SELECT SELECT 学号学号,课程号课程号,成绩成绩 FROM XS_KC FROM XS_KC WHERE WHERE 成绩成绩60大于大于=大于等于大于等于=小于等于小于等于,!=不等于不等于!不大于不大于!不小于不小于表表3.1 比较运算符比较运算符6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图【例【例3-163-16】查询】查询XS_KCXS_KC表中学号小于表中学号小于“020201”“020201”的学生记录。的学生记录。SELECT SELECT 学号学号,课程号课程号,成绩成绩 FROM XS_KC FROM XS_KC WHERE WHERE 成绩成绩020201=80 AND 成绩成绩=60【例【例3-25】查询】查询XS_KC表中不满足表中不满足“101”号课程的成绩介于号课程的成绩介于6070的记录。的记录。SELECT 学号学号,课程号课程号,成绩成绩 FROM XS_KC WHERE 课程号课程号=101 AND NOT（成绩（成绩=60 AND 成绩成绩=70）6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图4.使用查询范围使用查询范围除了比较运算符，还有另一种指定列值范围的方法。除了比较运算符，还有另一种指定列值范围的方法。语法格式为：语法格式为：SELECT 列名列名1，n FROM 表名表名1，n WHERE 列名列名 BETWEEN 取值范围下界取值范围下界 AND 取值取值范围上界范围上界 将【例将【例3-23】改成如下形式，查询结果不变。】改成如下形式，查询结果不变。SELECT 学号学号,课程号课程号,成绩成绩 FROM XS_KC WHERE 课程号课程号=101 AND 成绩成绩 BETWEEN 80 AND 896/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图5.使用查询集合（关键字使用查询集合（关键字IN）语法格式：语法格式：SELECT 列名列名1，n FROM 表名表名1,n WHERE 列名列名 IN（列值（列值1，n）【例【例3-26】查询】查询XS_KC表中课程号表中课程号“101”、“103”或或“107”的学的学生成绩记录。生成绩记录。SELECT 学号学号,课程号课程号,成绩成绩 FROM XS_KC WHERE 课程号课程号 IN（101,103,107）6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图【例【例3-27】把【例】把【例3-26】改成用】改成用OR运算符连接形式，查询结果不变运算符连接形式，查询结果不变。SELECT 学号学号,课程号课程号,成绩成绩 FROM XS_KC WHERE 课程号课程号=101 OR 课程号课程号=103 OR 课程课程号号=107关键字关键字IN常常使用于常常使用于SELCET语句的嵌套形式中。语句的嵌套形式中。【例【例3-28】使用】使用IN的嵌套查询实现至少有一门课程不及格的学生名单的嵌套查询实现至少有一门课程不及格的学生名单。SELECT 学号学号,姓名姓名,联系电话联系电话 FROM XSQK WHERE 学号学号 IN （SELECT DISTINCT 学号学号 FROM XS_KC WHERE 成绩成绩70 AND AVG(成绩成绩)806/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图【例【例3-34】统计】统计1班各课程不及格的人数。班各课程不及格的人数。SELECT 班级班级,课程号课程号,COUNT(成绩成绩)AS 不及格人数不及格人数 FROM XS_KC WHERE 成绩成绩60 GROUP BY 班级班级,课程号课程号 HAVING 班级班级=13.使用使用WITH ROLLUP和和WITH CUBE选项选项使用使用WITH ROLLUP和和WITH CUBE选项可以对汇总结果再汇总，生成超级选项可以对汇总结果再汇总，生成超级组。组。【例【例3-35】数据汇总时不使用】数据汇总时不使用WITH ROLLUP和和WITH CUBE选项。选项。SELECT 班级班级,课程号课程号,COUNT(成绩成绩)AS 不及格人数不及格人数 FROM XS_KC WHERE 成绩成绩60 GROUP BY 班级班级,课程号课程号6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图【例【例3-36】对【例】对【例3-34】进行数据汇总时使用】进行数据汇总时使用WITH ROLLUP选选项。项。SELECT 班级班级,课程号课程号,COUNT(成绩成绩)AS 不及格人数不及格人数 FROM XS_KC WHERE 成绩成绩60 GROUP BY 班级班级,课程号课程号 WITH ROLLUP【例【例3-37】对【例】对【例3-34】进行数据汇总时使用】进行数据汇总时使用WITH CUBE选项。选项。SELECT 班级班级,课程号课程号,COUNT(成绩成绩)AS 不及格人数不及格人数 FROM XS_KC WHERE 成绩成绩60 GROUP BY 班级班级,课程号课程号 WITH CUBE6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图4.使用明细汇总子句使用明细汇总子句 语法格式：语法格式：SELECT 列名列名1,nFROM 表名表名1，nWHERE 逻辑表达式逻辑表达式GROUP BY 列名列名1,nORDER BY列名列名1|列号列号1ASC|DESC,nCOMPUTE聚合函数（列名聚合函数（列名1），nBY 列名列名1，n注意事项：注意事项：如果使用如果使用GROUP BY子句，那么子句，那么SELECT子句中指定的列必子句中指定的列必须在须在GROUP BY子句中指定、或是和聚合函数一起使用子句中指定、或是和聚合函数一起使用。6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图 COMPUTE子句中的列必须出现在子句中的列必须出现在SELECT子句的列表中。子句的列表中。使用使用COMPUT BY子句就不能同时使用子句就不能同时使用SELECT INTO子句子句 BY选项表示按指定的列进行明细汇总，使用选项表示按指定的列进行明细汇总，使用BY选项时必须选项时必须同时使用同时使用ORDER BY 子句，且子句，且BY选项后的列必须具有和选项后的列必须具有和ORDER BY后的列相同的顺序，且不能跳过其中的列。后的列相同的顺序，且不能跳过其中的列。例如：如果例如：如果ORDER BY子句按照如下指定列。子句按照如下指定列。ORDER BY 列列A，列，列B，列，列C 则则BY选项后的列表只能是下面任一中形式：选项后的列表只能是下面任一中形式：BY 列列A，列，列B，列，列C BY 列列A，列，列B BY 列列A 6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图【例【例3-38】使用】使用COMPUTE子句对不及格的成绩记录进行明细汇子句对不及格的成绩记录进行明细汇总。总。SELECT 学号学号,课程号课程号,成绩成绩 FROM XS_KC WHERE 成绩成绩60 COMPUTE COUNT（成绩）（成绩）【例【例3-39】使用】使用COMPUTE BY子句对不及格的成绩记录进行明细汇总。子句对不及格的成绩记录进行明细汇总。SELECT 学号学号,课程号课程号,成绩成绩 FROM XS_KC WHERE 成绩成绩60 ORDER BY 2 COMPUTE COUNT（成绩）（成绩）BY 课程号课程号6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图3.1.5 排序排序如果按一定顺序显示数据记录，有两种方法：如果按一定顺序显示数据记录，有两种方法：（为表中的列建（为表中的列建立簇索引或普通索引。）立簇索引或普通索引。）当查询指定的是单个列，且该列有索引时，查询结果按照当查询指定的是单个列，且该列有索引时，查询结果按照该列的索引定义的顺序显示；该列的索引定义的顺序显示；否则都按照表的簇索引顺序显示。否则都按照表的簇索引顺序显示。【例【例3-40】查询学生表中的】查询学生表中的“姓名姓名”单个列。单个列。SELECT 姓名姓名 FROM XSQK【例【例3-41】查询学生表中的】查询学生表中的“姓名姓名”、“联系电话联系电话”多个列。多个列。SELECT 姓名，联系电话姓名，联系电话 FROM XSQK查询结果如下图：查询结果如下图：6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图语法格式：语法格式：SELECT 列名列名1,nFROM 表名表名1，nORDER BY 列名列名1|列号列号1ASC|DESC，n其中：其中：ASC为默认值，表示按升序排序。为默认值，表示按升序排序。【例【例3-42】查询学生表中的】查询学生表中的“姓名姓名”，并按姓名列的降序排序。，并按姓名列的降序排序。SELECT 姓名姓名 FROM XSQK ORDER BY 姓名姓名 DESC【例【例3-43】按出生日期列的升序排列学生表。】按出生日期列的升序排列学生表。SELECT 学号，姓名，出生日期学号，姓名，出生日期 FROM XSQK ORDER BY 36/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图例例3-42例例3-436/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图【例【例3-44】按多个列对学生表排序。】按多个列对学生表排序。SELECT 学号，课程号，成绩学号，课程号，成绩 FROM XS_KC ORDER BY 2，3 DESC6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图3.1.6 SELECT语句的其他子句语句的其他子句1.将查询结果生成表将查询结果生成表 语法格式：语法格式：SELECT 列名列名1,nINTO 新表名新表名FROM 表名表名1，nWHERE 逻辑表达式逻辑表达式【例【例3-45】将【例】将【例3-44】的查询结果保存到新表】的查询结果保存到新表temp_KC中。中。SELECT 学号，课程号，成绩学号，课程号，成绩 INTO temp_KC FROM XS_KC ORDER BY 2，3 DESC6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图2.使用连接查询使用连接查询 所谓连接查询就是将多个表通过某个或某些列为条件进所谓连接查询就是将多个表通过某个或某些列为条件进行连接，从中查询数据。行连接，从中查询数据。两种语法方式：两种语法方式：ANSI连接语法形式如下：连接语法形式如下：SELECT 表名表名.列名列名1，n FROM表名表名1连接类型连接类型 JOIN 表名表名2 ON 连接条件连接条件，n WHERE 查询条件查询条件SQL Server连接语法形式如下：连接语法形式如下：SELECT 表名表名.列名列名1，n FROM 表名表名1，n WHERE 查询条件查询条件 AND|OR 连接条件连接条件，n6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图（1）内连接）内连接 INNER JOININNER JOIN 在内连接中，可以使用等号在内连接中，可以使用等号“=”“=”作为比较运算符，此时作为比较运算符，此时称作等值连接；也可以使用不等比较运算符，此时为不等值连称作等值连接；也可以使用不等比较运算符，此时为不等值连接。接。【例【例3-46】用】用ANSI语法表示的内连接查询成绩不及格的学生信语法表示的内连接查询成绩不及格的学生信息。息。SELECT XSQK.学号，姓名，联系电话，课程号，成绩学号，姓名，联系电话，课程号，成绩 FROM XSQK INNER JOIN XS_KC ON XSQK.学号学号=XS_KC.学号学号6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图（2）外连接）外连接 外外连连接接包包括括左左外外连连接接、右右外外连连接接和和全全外外连连接接。左左外外连连接接包包含含左左表表的的所所有有数数据据行行，右右外外连连接接包包含含右右表表的的所所有有数数据据行行，全全外外连接包含两个表的所有数据行。连接包含两个表的所有数据行。左连接：左连接：在连接两个表时，保留左表中的不匹配行。在连接两个表时，保留左表中的不匹配行。LEFT OUTER JOIN LEFT OUTER JOIN 右连接：右连接：在连接两个表时，保留右表中的不匹配行。在连接两个表时，保留右表中的不匹配行。RIGHT OUTER JOINRIGHT OUTER JOIN 全连接：全连接：在连接两个表时，保留两表中的不匹配行。在连接两个表时，保留两表中的不匹配行。FULL OUTER JOINFULL OUTER JOIN 全全外外连连接接使使用用FULL FULL OUTER OUTER JOINJOIN操操作作符符，它它实实际际上上是是左左外外连连接接与与右右外外连连接接的的结结合合，即即操操作作符符两两边边的的表表中中所所有有数数据据行行均均会会显显示示在在结结果果集集中中。不不论论哪哪一一个个表表中中没没有有对对应应的的数数据据行行，相相应应列列上都会返回空值。上都会返回空值。6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图【例【例3-47】用】用ANSI语法表示的左连接查询。语法表示的左连接查询。SELECT XSQK.学号，姓名，联系电话，课程号，成绩学号，姓名，联系电话，课程号，成绩 FROM XSQK LEFT OUTER JOIN XS_KC ON XSQK.学学号号=XS_KC.学号学号【例【例3-48】用】用ANSI语法表示的右连接查询。语法表示的右连接查询。SELECT XSQK.学号，姓名，联系电话，课程号，成绩学号，姓名，联系电话，课程号，成绩 FROM XSQK RIGHT OUTER JOIN XS_KC ON XSQK.学号学号=XS_KC.学号学号【例【例3-49】用】用ANSI语法表示的全连接查询。语法表示的全连接查询。SELECT XSQK.学号，姓名，联系电话，课程号，成绩学号，姓名，联系电话，课程号，成绩 FROM XSQK FULL OUTER JOIN XS_KC ON XSQK.学学号号=XS_KC.学号学号6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图（3）自连接）自连接 INNER JOININNER JOIN JOIN JOIN连接操作符还可以用于单个表，此时代表一个表与其连接操作符还可以用于单个表，此时代表一个表与其自身进行连接，表的这种连接操作称为自连接。为了区别列名，自身进行连接，表的这种连接操作称为自连接。为了区别列名，必须在自连接中对表指定别名。必须在自连接中对表指定别名。【例【例3-50】用】用ANSI语法表示的自连接查询显示每个学生的两门语法表示的自连接查询显示每个学生的两门课程成绩。课程成绩。SELECT a.学号，学号，a.课程号，课程号，a.成绩，成绩，b.课程号，课程号，b.成绩成绩 FROM XS_KC a INNER JOIN XS_KC ON a.学号学号=b.学学号号 WHERE a.课程号课程号b.课程号课程号6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图（4）无限制连接）无限制连接 CROSS JOINCROSS JOIN 无无限限制制连连接接返返回回进进行行连连接接操操作作的的两两个个表表中中所所有有数数据据行行的的笛笛卡尔积，得到的结果集的行数是两个表的行数的乘积。卡尔积，得到的结果集的行数是两个表的行数的乘积。【例【例3-51】SELECT *FROM XSQK CROSS JOIN XS_KC 6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图3.使用联合查询使用联合查询增加记录的行数增加记录的行数语法形式：语法形式：SELECT 语句语句1 UNION ALL SELECT 语句语句2【例【例3-52】SELECT 学号学号 FROM XSQK UNION ALL SELECT 成绩成绩 FROM XS_KC6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图4.使用嵌套查询（子查询）使用嵌套查询（子查询）将将一一个个SELECTSELECT语语句句嵌嵌套套在在另另一一个个SELECTSELECT语语句句的的WHEREWHERE子子句句中中的的查查询询形形式式，称称为为子子查查询询，子子查查询询中中嵌嵌套套在在WHEREWHERE子子句句里里的的SELECTSELECT语语句句称称为为内内部部查查询询，相相对对的的，另另一一个个包包含含内内部部查查询询的的SELECTSELECT语语句句被被称称为为外外部部查查询询。子子查查询询要要求求服服务务器器先先计计算算内内部部查查询询并并形形成成结结果果，然然后后外外部部查查询询根根据据内内部部查查询询的的结结果果，产产生生最最终终查询结果。查询结果。子查询可分为子查询可分为4 4种类型：种类型：NOTINNOTIN子查询子查询比较子查询比较子查询NOTEXISTSNOTEXISTS子查询子查询关联子查询关联子查询6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图（1）NOTINNOTIN子查询子查询 操操作作符符ININ用用于于WHEREWHERE子子句句中中时时，通通常常用用来来确确定定某某个个属属性性列列的的值值是是否否包包含含在在一一个个表表达达式式或或者者常常量量的的集集合合中中。同同样样，操操作作符符ININ也也可可以以用用于于子子查查询询，用用来来确确定定某某个个列列值值是是否否在在内内部部查查询询的的结结果集中。果集中。【例【例3-53】查询无不及格学生的课程的授课教师。】查询无不及格学生的课程的授课教师。SELECT DISTINCT 授课教师授课教师 FROM KC WHERE 课程号课程号 NOT IN （SELECT 课课程程号号 FROM XS_KC WHERE 成成绩绩60）6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图（2）比较子查询 比比较较子子查查询询就就是是将将某某个个列列值值与与内内部部查查询询的的结结果果作作运运算算比比较较，如如果果比比较较结结果果为为真真则则返返回回该该行行，否否则则不不返返回回。比比较较子子查查询询通通常常要用到操作符要用到操作符ALLALL、ANYANY、SOMESOME。其通用格式为：。其通用格式为：ALL|ANY|SOME ALL|ANY|SOME ALLALL ：列列值值必必须须和和内内部部查查询询结结果果集集的的每每一一个个值值进进行行比比较较，只只有每一次的比较结果都为真时，比较结果才为真。有每一次的比较结果都为真时，比较结果才为真。ANYANY、SOMESOME ：列列值值和和内内部部查查询询结结果果集集的的值值进进行行比比较较，只只要要有有一次为真，比较结果就为真。一次为真，比较结果就为真。6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图【例【例3-54】查询平均分低于】查询平均分低于60的学生学号。的学生学号。SELECT DISTINCT 学号学号 FROM XS_KC a WHERE 60 （SELECT AVG(成成绩绩)FROM XS_KC b WHERE b.学号学号=a.学号）学号）【例【例3-54】（方法二）查询平均分低于】（方法二）查询平均分低于60的学生学号。的学生学号。SELECT DISTINCT 学号，学号，AVG（成绩）（成绩）FROM XS_KC GROUP BY 学号学号 HAVING AVG（成绩）（成绩）606/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图【例【例3-55】查询有不及格学生的课程号的授课教师。】查询有不及格学生的课程号的授课教师。SELECT DISTINCT 授课教师授课教师 FROM KC WHERE 课程号课程号=ANY （SELECT 课课程程号号 FROM XS_KC WHERE 成成绩绩60）【例【例3-55】(方法二方法二)查询有不及格学生的课程号的授课教师。查询有不及格学生的课程号的授课教师。SELECT DISTINCT 授课教师授课教师 FROM KC INNER JOIN XS_KC ON XS_KC.课程课程号号=KC.课程号课程号 WHERE 成绩成绩60 6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图（3）NOTEXISTS NOTEXISTS 子查询子查询 EXISTSEXISTS是是测测试试子子查查询询是是否否有有数数据据行行返返回回，如如果果有有则则返返回回TRUETRUE，否否则则返返回回FALSEFALSE。NOT NOT EXISTS EXISTS 则则相相反反，当当结结果果表表为为空空时时，才返回才返回TRUETRUE。【例【例3-56】查询是否有至少一门课程不及格的学生。】查询是否有至少一门课程不及格的学生。SELECT DISTINCT 学号，姓名学号，姓名 FROM XSQK a WHERE EXISTS （SELECT*FROM XS_KC b WHERE b.成成绩绩60 AND b.学号学号=a.学号）学号）6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图（4）简单查询简单查询 Transact-SQLTransact-SQL的的SELECTSELECT语语句句从从一一个个或或者者多多个个表表或或视视图图中中检检索索记记录录，使使用用查查询询分分析析器器输输入入一一条条SELECTSELECT语语句句，查查询询情情况况将将在在结结果果窗窗口口中中显显示示。可可以以编辑或打印这些结果，并且把它们保存在非数据库文件中。编辑或打印这些结果，并且把它们保存在非数据库文件中。指定指定TOPTOP关键字关键字 SQL SQL Server Server 2000 2000 使使用用了了TOP TOP 关关键键字字，用用于于查查询询时时只只显显示示前前面面几几行行数数据据。当当SELECTSELECT语语句句检检索索出出来来的的记记录录非非常常多多，且且又又无无须须显显示示全全部部记记录录时时，可可使使用用TOPTOP关关键键字字来来减减少少查查询询时时间间，提提高高查询效率。查询效率。使用使用TOPTOP关键字的语法如下：关键字的语法如下：SELECT SELECT TOP TOP integer integer|TOP TOP integer integer PERCENTWITH PERCENTWITH TIESTIES ，1n1n FROM FROM ;6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图【例【例3-57】查询前】查询前5行记录。行记录。SELECT TOP 5 姓名姓名 FROM XSQK【例【例3-58】查询前】查询前20%记录。记录。SELECT TOP 20 PERCENT 姓名姓名 FROM XSQK6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图3.2 视图视图 视图的概念视图的概念 视视图图不不包包含含任任何何数数据据，只只是是定定义义在在一一个个或或多多个个基基表表上上或或其其他他视视图图上上，并并且且提提供供一一种种访访问问基基表表数数据据的的方方法法。在在物物理理磁磁盘盘上上存存储储的的有有关关视视图图的的信信息息是是：视视图图的的名名称称和和视视图图的的定定义义。视视图图的的所所有有数数据据来来自自基基本本表表。因因此此，当当基基本本表表的的数数据据发发生生变变化化时时，对对应视图中的查询出的数据也会随之变化。应视图中的查询出的数据也会随之变化。视视图图一一经经定定义义，就就可可以以象象基基本本表表一一样样执执行行查查询询、删删除除等等操操作作，也也可可以以在在视视图图上上定定义义新新的的视视图图，但但对对视视图图的的更更新新操操作作则则有有一定限制。一定限制。视图的作用视图的作用可以满足不同用户的需求可以满足不同用户的需求 不不同同的的用用户户对对数数据据库库操操作作有有不不同同的的需需求求，即即使使相相同同的的数数据据也也可可能能有有不不同同的的操操作作要要求求。一一张张基基本本表表可可能能有有很很多多属属性性列列，利利用用视视图图，用用户户可可以以把把自自己己感感兴兴趣趣的的属属性性列列集集中中起起来来，放放在在一一个个视图中，此后用户可以将视图作为一张表来对待。视图中，此后用户可以将视图作为一张表来对待。6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图可以简化数据读取可以简化数据读取 查查询询数数据据时时，通通常常要要用用SELECTSELECT语语句句编编写写复复杂杂的的连连接接、统统计计、函函数数等等，以以产产生生所所需需要要的的结结果果。使使用用视视图图，可可以以隐隐蔽蔽这这种种复复杂杂性性。可可以以将将经经常常用用到到的的复复杂杂查查询询的的语语句句定定义义为为视视图图，不不必必每每次次查询都写上复杂查询条件，这样就简化了用户的查询操作。查询都写上复杂查询条件，这样就简化了用户的查询操作。保证了基本表数据和应用程序的逻辑独立性保证了基本表数据和应用程序的逻辑独立性 当当应应用用程程序序通通过过视视图图来来访访问问数数据据时时，视视图图实实际际上上成成为为应应用用程程序序和和基基本本表表数数据据之之间间的的桥桥梁梁。如如果果应应用用程程序序直直接接调调用用基基本本表表，则则一一旦旦基基本本表表的的数数据据发发生生变变化化时时，应应用用程程序序必必须须随随之之改改动动。而而通通过过视视图图访访问问数数据据，则则可可以以通通过过改改变变视视图图来来适适应应基基本本表表的的变变化化，使使应应用用程程序序不不必必作作改改变变，保保证证了了基基本本表表数数据据和和应应用用程程序序的的逻逻辑辑独立性。独立性。可以提供安全机制可以提供安全机制 利用视图可以限制数据访问。如果某个用户需要访问表中利用视图可以限制数据访问。如果某个用户需要访问表中的某些列，但另一些属性列必须对该用户保密，则可以利用视的某些列，但另一些属性列必须对该用户保密，则可以利用视图达到此目的，将视图建立在该用户需要访问的那些列上。图达到此目的，将视图建立在该用户需要访问的那些列上。6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图在代码方式下对视图的操作：在代码方式下对视图的操作：3.2.1 3.2.1 视图的建立视图的建立3.2.2 3.2.2 视图的修改视图的修改3.2.3 3.2.3 视图的删除视图的删除6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图3.2.1 3.2.1 视图的建立视图的建立 SQLSQL语言用语言用CREATE VIEWCREATE VIEW语句来建立视图，其一般格式为：语句来建立视图，其一般格式为：CREATE VIEW CREATE VIEW (，)AS AS WITH CHECK OPTION WITH CHECK OPTION；说明：说明：(1)(1)选选项项WITH WITH CHECK CHECK OPTIONOPTION确确保保用用户户只只能能查查询询和和修修改改他他们们所所看看到到的的数数据据，强强制制所所有有在在视视图图上上使使用用的的数数据据修修改改语语句句满满足足定定义义视视图时的条件。图时的条件。(2)(2)组组成成视视图图的的各各属属性性列列可可以以显显式式指指定定，也也可可以以省省略略。如如果果省省略略不不写写，则则组组成成视视图图的的各各属属性性列列由由子子查查询询中中SELECTSELECT子子句句的的各各目目标列组成。标列组成。6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图 下列情形建立视图时必须显式指定属性列：下列情形建立视图时必须显式指定属性列：1)1)视图的某列不是原属性列，而是统计函数或者表达式。视图的某列不是原属性列，而是统计函数或者表达式。2)2)多表连接时选出了两个或者多个同名列作为视图的属性列。多表连接时选出了两个或者多个同名列作为视图的属性列。3)3)需要对视图中的某些列重新命名。需要对视图中的某些列重新命名。【例【例3-593-59】创建包含班委成员基本信息的视图。】创建包含班委成员基本信息的视图。CREATE VIEW v_ CREATE VIEW v_班委成员班委成员 AS SELECT AS SELECT 学号学号,姓名姓名,联系电话联系电话,职务职务=备注备注 FROM xsqk FROM xsqk WHERE WHERE 备注备注 LIKE%LIKE%其中可用：其中可用：CREATE VIEW v_ CREATE VIEW v_班委成员班委成员(a,b,c,d)(a,b,c,d)；或；或 =；或或 AS AS 更改列名（即别名）更改列名（即别名）注意：先显示视图名后的别名。注意：先显示视图名后的别名。6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图【例【例3-603-60】创建按课程分组汇总的视图】创建按课程分组汇总的视图 CREATE VIEW v_ CREATE VIEW v_课程分组汇总课程分组汇总 AS SELECT AS SELECT 课程号课程号,AVG(,AVG(成绩成绩)AS)AS 平均分平均分 FROM xs_kc FROM xs_kc GROUP BY GROUP BY 课程号课程号;注意：注意：GROUP BY GROUP BY 的使用方法。的使用方法。（selectselect与与group by group by 中的列名应一致，或使用聚合函数）中的列名应一致，或使用聚合函数）【例例3-613-61】使使用用ANSIANSI语语法法形形式式创创建建所所有有不不及及格格课课程程的的学学生生信信息记录息记录6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图3.2.2 3.2.2 视图的修改视图的修改SQLSQL语言用语言用ALTER VIEWALTER VIEW语句来修改视图，其一般格式为：语句来修改视图，其一般格式为：ALTER VIEW ALTER VIEW WITH WITH ENCRYPTION ENCRYPTION|SCHEMABINDING SCHEMABINDING|VIEW_METADATA VIEW_METADATA AS AS WITH CHECK OPTION WITH CHECK OPTION；说明：说明：(1)(1)修改视图不会改变其原有的权限。修改视图不会改变其原有的权限。(2)(2)如如果果创创建建视视图图时时使使用用了了WITH WITH ENCRYPTIONENCRYPTION 或或 WITH WITH CHECK CHECK OPTIONOPTION选选项项，那那么么在在ALTER ALTER VIEWVIEW 命命令令中中也也使使用用它它们们，否否则则它它们不起作用。们不起作用。6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图【例例3-623-62】修修改改【例例3-593-59】中中班班委委成成员员基基本本信信息息的的视视图图。（新新增专业名列）增专业名列）ALTER VIEW v_ ALTER VIEW v_班委成员班委成员 AS SELECT AS SELECT 学号学号,姓名姓名,专业名，联系电话专业名，联系电话,职务职务=备注备注 FROM xsqk FROM xsqk WHERE WHERE 备注备注 LIKE%LIKE%注意：注意：ALTER ALTER 与与 CREATE CREATE 的区别。的区别。6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图 当当创创建建视视图图时时使使用用了了“SELECT SELECT*”子子句句，那那么么当当基基表表变变化化时时，视视图图不不变变。【例【例3-633-63】步骤一：使用】步骤一：使用“SELECT*”“SELECT*”子句创建基本信息视图。子句创建基本信息视图。CREATE VIEW v_xsqk CREATE VIEW v_xsqk AS AS SELECT*SELECT*FROM xsqk FROM xsqk 步骤二：使用步骤二：使用“ALTER TABLE”“ALTER TABLE”修改表信息修改表信息(增加列增加列)。ALTER TABLE xsqk ALTER TABLE xsqk ADD ADD 籍贯籍贯 char(40)char(40)步骤三：比较基表步骤三：比较基表“xsqk”“xsqk”和视图和视图“v_xsqk”“v_xsqk”的变化。的变化。方法：方法：窗口界面下：窗口界面下：代码方式下：代码方式下：SELECT*SELECT*SELECT*SELECT*FROM sxqk FROM v_xsqk FROM sxqk FROM v_xsqk6/25/2024第三章第三章 数据库的查询和试图数据库的查询和试图3.2.3 3.2.3 视图的删除视图的删除SQLSQL语言用语言用DROP VIEWDROP VIEW语句来删除视图，其一般格式为：语句来删除视图，其一般格式为：DROP VIEW DROP VIEW 视图名视图名1,n1,n 【例【例3-643-64】一次删除两个视图】一次删除两个视图 DROP VIEW v_a,v_b DROP VIEW v_a,v_b 【例【例3-653-65】视图绑定到架构的应用】视图绑定到架构的应用 方法：方法：界面方式绑定到架构：视图界面方式绑定到架构：视图aaaaaa，视图，视图bbb(bbb(基于视图基于视图aaa)aaa)代码方式绑定到架构：代码方式绑定到架构：create view aaa create view bbb create view aaa create view bbb with schemabinding with schemabindingwith schemabinding with schemabindingAs asAs asselect select 学号学号,姓名姓名,总学分总学分 select select 学号学号,姓名姓名,总学分总学分from dbo.xsqk from dbo.aaafrom dbo.xsqk from dbo.aaa删除视图删除视图aaaaaa看效果看效果6/25/2024 31、只有永远躺在泥坑里的人，才不会再掉进坑里。黑格尔32、希望的灯一旦熄灭，生活刹那间变成了一片黑暗。普列姆昌德33、希望是人生的乳母。科策布34、形成天才的决定因素应该是勤奋。郭沫若35、学到很多东西的诀窍，就是一下子不要学很多。洛克