关系数据库标准语言SQL(下)课件.ppt

1、北京信息科技大学数据库应用与设计3.5 数据查询功能 SQL的核心是查询。SQL的查询命令也称作SQL SELECT命令，该命令使用非常灵活，可以完成复杂的查询。要求熟练掌握SQL SELECT命令的使用。北京信息科技大学数据库应用与设计SQL SELECT命令的语法 SELECT ALL|DISTINCTTOP expression PERCENTWITH TIES select_list INTO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_con

2、dition ORDER BY order_expression ASC|DESC COMPUTE AVG|COUNT|MAX|MIN|SUM(expression),.n BY expression ,.n SQL Server还支持查询结果的并、交、差和广义笛卡尔积运算。北京信息科技大学数据库应用与设计各种运算符和表达式关系运算符 NOT BETWEENAND match_expression NOT LIKE patternexpression IS NOT NULL test_expression NOT IN(subquery|expression ,.n)并(UNION)、交(IN

3、TERSECT)、差(EXCEPT)运算 北京信息科技大学数据库应用与设计关系运算符关系运算符包括：n等于（=）n不等于（或!=）n大于（）n大于等于（=）n不大于（!）n小于（）n小于等于（=）n不小于（!=40 学时=50 AND 学时=50 AND 学时=60 表达式：学时 NOT BETWEEN 50 AND 60 等价于：学时60 北京信息科技大学数据库应用与设计字符串匹配 match_expression NOT LIKE patternmatch_expression是要进行匹配比较的字符串表达式pattern是含有通配符的字符串“模版”北京信息科技大学数据库应用与设计字符串匹配

4、北京信息科技大学数据库应用与设计空值判断expression IS NOT NULL 空值是一个概念而不是一个具体的值，所以不能用相等或不相等进行比较。例如：责任教师 IS NULL而不能写为：责任教师=NULL 北京信息科技大学数据库应用与设计判断元素是否属于集合test_expression NOT IN(subquery|expression ,.n)用来确定给定的表达式的值是否与子查询或集合列表中的值匹配。如：职称 IN(讲师,副教授,教授)北京信息科技大学数据库应用与设计集合运算并(UNION)交(INTERSECT)差(EXCEPT)运算北京信息科技大学数据库应用与设计通过实践来学

5、习简单查询 连接查询 分组与汇总查询 嵌套查询 教师(Student)教师编号 院系 姓名 性别 职称 专业课程(Student)课程编号 课程名称 责任教师 学时 课程性质选课(Student)学号 课程编号 考试成绩学生(Student)学号 院系 姓名 性别 生源 状态院系(Student)编号 名称 负责人 办公地点图3-1 样本数据库北京信息科技大学数据库应用与设计教师(Student)教师编号 院系 姓名 性别 职称 专业课程(Student)课程编号 课程名称 责任教师 学时 课程性质选课(Student)学号 课程编号 考试成绩学生(Student)学号 院系 姓名 性别 生源

6、 状态院系(Student)编号 名称 负责人 办公地点图3-1 样本数据库简单查询例31：查询学生的全部信息。例32：查询所有课程名称和学时信息。例33：查询学时大于等于40的课程编号和课程名称。例34：查询课时在50到60（含）之间的课程信息。例35：同例4，使用BETWEEN。例36：查询具有教授和副教授职称的教师信息。例37：查询责任教师字段为空值的记录。例38：查询职称为讲师或副教授的教师信息。北京信息科技大学数据库应用与设计几个关键词ALL(默认)：查询结果中保留重复行DISTINCT：查询结果中删除重复行INTO：将查询结果存储到指定的表nSELECT INTO ORDER BY

7、：查询结果排序nORDER BY order_expression ASC|DESC TOP：只返回查询结果的“前”一组结果 nTOP(expression)PERCENT WITH TIES 北京信息科技大学数据库应用与设计教师(Student)教师编号 院系 姓名 性别 职称 专业课程(Student)课程编号 课程名称 责任教师 学时 课程性质选课(Student)学号 课程编号 考试成绩学生(Student)学号 院系 姓名 性别 生源 状态院系(Student)编号 名称 负责人 办公地点图3-1 样本数据库简单查询例39：查询或浏览课程性质和学时信息。例40：查询“专业基础”课程的

8、信息，并将结果存储到Student模式下、表名为“专业基础”。例41：按教师编号升序列出所有教师信息。例42：查询所有学生信息，先按生源升序排序、再按学号升序排序。例43：从选课表中查询考试成绩最高的3条选课信息。例44：从选课表中查询考试成绩最高的3条选课信息，如果有与第三条记录的考试成绩并列记录的也列出。例45：从选课表中查询考试成绩在前15%的选课记录信息，如果随后有成绩并列的记录也一起列出。北京信息科技大学数据库应用与设计教师(Student)教师编号 院系 姓名 性别 职称 专业课程(Student)课程编号 课程名称 责任教师 学时 课程性质选课(Student)学号 课程编号 考

9、试成绩学生(Student)学号 院系 姓名 性别 生源 状态院系(Student)编号 名称 负责人 办公地点图3-1 样本数据库集合运算 例46：查询职称为教授和副教授的教师姓名和专业（用集合并运算完成）。例47：查询具有教授职称并是课程责任教师的教师编号（用集合交运算完成）。例48：查询不是课程责任教师的教师编号（用集合差运算完成）。北京信息科技大学数据库应用与设计连接查询 SELECT FROM INNER|LEFT|RIGHT|FULL OUTER JOIN ON INNER|LEFT|RIGHT|FULL OUTER JOIN ON,nWHERE 当查询涉及多个表、特别是当查询结果

10、的数据涉及多个表时需要使用连接查询。北京信息科技大学数据库应用与设计连接查询教师(Student)教师编号 院系 姓名 性别 职称 专业课程(Student)课程编号 课程名称 责任教师 学时 课程性质选课(Student)学号 课程编号 考试成绩学生(Student)学号 院系 姓名 性别 生源 状态院系(Student)编号 名称 负责人 办公地点图3-1 样本数据库例3-49：查询教师所负责的课程信息（责任教师的姓名、职称、负责的课程名称和课程性质）。例3-50：查询教师所负责的课程信息，同时列出没有确定责任教师的课程。例3-51：查询教师所负责的课程信息，同时列出不是责任教师的教师信息

11、。例3-52：查询教师所负责的课程信息，同时列出没有确定责任教师的课程信息和不是责任教师的教师信息。例3-53：查询考试成绩在90分以上（含）的学生的学号、姓名、相应的课程名称和考试成绩。例3-54：查询考试成绩在90分以上（含）的学生的学号、姓名、相应的课程名称和考试成绩(使用CROSS JOIN连接完成查询)。北京信息科技大学数据库应用与设计分组与汇总查询 SQL SELECT查询可以直接对查询结果进行汇总计算，也可以对查询结果进行分组计算。在查询中完成计算的函数称作聚合函数，分组查询分为GROUP BY分组和COMPUTE BY分组两种。北京信息科技大学数据库应用与设计聚合函数 常用：n

12、COUNTnAVGnMINnMAXnSUM其他：nCHECKSUMnCHECKSUM_AGG nCOUNT_BIGnGROUPINGnSTDEVnSTDEVPnVARnVARPn等北京信息科技大学数据库应用与设计聚合函数与汇总查询 SELECT FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC|DESC COMPUTE BY expression ,.n 聚合函数是对初始的查询结果进行计算然后得到最

13、终的查询结果，所以聚合函数只可以用在：SELECT子句的选择列表；限定GROUP BY分组的HAVING子句；完成计算的COMPUTE子句。北京信息科技大学数据库应用与设计汇总查询例3-55：查询目前有多少责任教师。例3-56：查询数据库课程的平均成绩。教师(Student)教师编号 院系 姓名 性别 职称 专业课程(Student)课程编号 课程名称 责任教师 学时 课程性质选课(Student)学号 课程编号 考试成绩学生(Student)学号 院系 姓名 性别 生源 状态院系(Student)编号 名称 负责人 办公地点图3-1 样本数据库 对对SELECT的全体查的全体查询结果进行汇总

14、计算。询结果进行汇总计算。SELECT SELECT,n n FROM FROM WHERE WHERE search_conditionsearch_condition 北京信息科技大学数据库应用与设计GROUP分组查询与计算 SELECT子句和GROUP BY子句中的group_by_expression是相对应的，它是分组标识，通常是字段；WHERE子句中的search_condition是查询条件，它与分组无关，执行查询时是先查找满足条件的记录，然后对满足条件的记录进行分组；HAVING子句用来限定分组，所以该子句中的search_condition是分组的记录应该满足的条件，HAVI

15、NG子句必须配合GROUP BY子句使用；分组时使用聚合函数对每一组的记录进行计算，并且同时可以进行多种计算。SELECT SELECT group_by_expressiongroup_by_expression ,n n FROM FROM WHERE WHERE search_conditionsearch_condition GROUP BY GROUP BY group_by_expressiongroup_by_expression HAVING HAVING search_conditionsearch_condition 北京信息科技大学数据库应用与设计教师(Student)教

16、师编号 院系 姓名 性别 职称 专业课程(Student)课程编号 课程名称 责任教师 学时 课程性质选课(Student)学号 课程编号 考试成绩学生(Student)学号 院系 姓名 性别 生源 状态院系(Student)编号 名称 负责人 办公地点图3-1 样本数据库GROUP分组查询举例例3-57：查询各院系教师的人数（显示院系名称和教师人数）。例3-58：查询各门课程考试成绩的平均分、最高分和最低分。例3-59：查询专业基础课平均分大于等于60分的各门课程考试成绩的平均分、最高分和最低分。使用使用GROUP BY分组查询只能查询到分组分组查询只能查询到分组的结果，而不能看到组内记录的

17、明细信息。的结果，而不能看到组内记录的明细信息。北京信息科技大学数据库应用与设计COMPUTE分组查询与计算 SELECT子句指出查询明细；COMPUTE子句指定用聚合函数进行计算，如果指定BY子句则说明进行分组计算；为了保证分组计算的正确必须同时使用ORDER BY子句，这两个子句中的expression是相对应的；可以进行分组汇总和全部汇总。SELECT FROM WHERE search_condition ORDER BY expression ASC|DESC ,.n COMPUTE,n BY expression ,.n 北京信息科技大学数据库应用与设计教师(Student)教师编

18、号 院系 姓名 性别 职称 专业课程(Student)课程编号 课程名称 责任教师 学时 课程性质选课(Student)学号 课程编号 考试成绩学生(Student)学号 院系 姓名 性别 生源 状态院系(Student)编号 名称 负责人 办公地点图3-1 样本数据库COMPUTE查询举例例3-60：查询所有专业基础课的信息，并计算其总学时。例3-61：查询学生的考试成绩（要求查询学号、姓名、课程名称和考试成绩），同时计算每个学生的平均成绩、最高成绩和最低成绩。例3-62：查询学生的考试成绩（要求查询学号、姓名、课程名称和考试成绩），同时计算每个学生的平均成绩、最高成绩和最低成绩。同时计算所

19、有学生的平均成绩、最高成绩和最低成绩。北京信息科技大学数据库应用与设计嵌套查询SQL SELECT语句的查询结果是一个集合，它可以直接用于其他查询，通常把这种查询称作嵌套查询。使用IN运算的嵌套查询使用量词ANY、SOME、ALL进行比较的嵌套查询使用谓词EXISTS进行判断的嵌套查询 北京信息科技大学数据库应用与设计使用IN运算的嵌套查询 和出自同一个值域，并且通常是字段 SELECT FROM WHERE AND IN(SELECT FROM WHERE)北京信息科技大学数据库应用与设计教师(Student)教师编号 院系 姓名 性别 职称 专业课程(Student)课程编号 课程名称 责

20、任教师 学时 课程性质选课(Student)学号 课程编号 考试成绩学生(Student)学号 院系 姓名 性别 生源 状态院系(Student)编号 名称 负责人 办公地点图3-1 样本数据库嵌套查询举例例3-63：查询由教授负责的课程信息（课程编号、课程名称、学时和课程性质）。北京信息科技大学数据库应用与设计使用量词ANY、SOME、ALL进行比较的嵌套查询 和出自同一个值域，并且通常是字段；一般在查询时是一个值，而是内层查询结果的值的集合；ANY和SOME是同义词，表示和内层查询结果中的值的集合中的某个（或某些）值进行比较；而ALL表示和内层查询结果中的值的集合中的全部值进行比较。SEL

21、ECT FROM WHERE AND ANY|SOME|ALL(SELECT FROM WHERE)北京信息科技大学数据库应用与设计教师(Student)教师编号 院系 姓名 性别 职称 专业课程(Student)课程编号 课程名称 责任教师 学时 课程性质选课(Student)学号 课程编号 考试成绩学生(Student)学号 院系 姓名 性别 生源 状态院系(Student)编号 名称 负责人 办公地点图3-1 样本数据库嵌套查询举例例3-64：查询责任教师的有关信息（姓名、性别、职称、专业）。例3-65：查询有考试成绩大于数据库课程全部考试成绩的学生信息。例3-66：查询“系统分析与设计

22、”课程的责任教师的姓名、职称，以及所负责的其他课程（课程编号和课程名称）。北京信息科技大学数据库应用与设计内外层互相关嵌套查询 不仅外层查询可以利用内层查询的结果，内层查询也可以要求外层查询提供有关数据。北京信息科技大学数据库应用与设计教师(Student)教师编号 院系 姓名 性别 职称 专业课程(Student)课程编号 课程名称 责任教师 学时 课程性质选课(Student)学号 课程编号 考试成绩学生(Student)学号 院系 姓名 性别 生源 状态院系(Student)编号 名称 负责人 办公地点图3-1 样本数据库举例学生表增加一个平均分字段 ALTER TABLE studen

23、t.学生ADD 平均分INT 例3-67：根据选课表计算每个学生的平均成绩，并将计算结果写到学生表的平均分字段。北京信息科技大学数据库应用与设计使用谓词EXISTS进行判断的嵌套查询 内层的search_condition一定会引用外层提供的值。SELECT FROM WHERE AND NOT EXISTS(SELECT FROM WHERE search_condition)北京信息科技大学数据库应用与设计EXISTS查询举例教师(Student)教师编号 院系 姓名 性别 职称 专业课程(Student)课程编号 课程名称 责任教师 学时 课程性质选课(Student)学号 课程编号 考

24、试成绩学生(Student)学号 院系 姓名 性别 生源 状态院系(Student)编号 名称 负责人 办公地点图3-1 样本数据库例3-68：查询哪些学生（学号和姓名）有未完成考试的课程（考试成绩为NULL）。例3-69：查询哪些学生（学号和姓名）没有未完成考试的课程（考试成绩为NULL）。北京信息科技大学数据库应用与设计思考题 讨论SELECT短语中ALL、DISTINCT、TOP、PERCENT和WITH TIES等关键词的作用。在LIKE字符串匹配查询中，如果通配符是字符串的一部分应该如何查询？例如数据库表的字段中可能存储带有“%”的通过率或折扣率等形式的信息。（提示：可以使用ESCA

25、PE escape_ character在字符串中搜索通配符。）SQL SELECT查询可以分为哪几类？每类查询可以满足什么样的查询需求？北京信息科技大学数据库应用与设计思考题试述在SELECT语句中WHERE短语和HAVING短语各自的作用。在SELECT语句中不使用JOINON如何实现连接查询？在连接运算或查询中为什么还需要外（OUTER）连接？使用COMPUTE BY分组查询是否能够完成GROUP BY分组查询的功能？反之呢？在SELECT查询中聚合函数的作用是什么？聚合函数可以用在哪些短语中？北京信息科技大学数据库应用与设计思考题在SELECT语句中COMPUTE BY和COMPUTE

26、有什么区别？什么是嵌套查询？当外层查询中的属性要和内层查询的结果进行比较时有哪些情况需要注意？什么是内外层互相关嵌套查询？这样的嵌套查询有什么特点？使用EXISTS的嵌套查询是内外层互相关嵌套查询，否则的话会是什么结果？北京信息科技大学数据库应用与设计思考题连接查询是否可以写出等价的嵌套查询？嵌套查询是否可以写出等价的连接查询？在SQL SELECT语句中关系代数的选择、投影、连接都很容易表达出来，用SQL SELECT语句如何实现关系代数的除法运算？北京信息科技大学数据库应用与设计3.6 视图 在关系数据库中把独立存在的关系称作基本表；视图是从基本表派生出来的虚拟表，视图本身不包含数据；从用

27、户的观点视图和基本表都是关系，视图和基本表一样可以进行查询和操作；外部模式，对视图的所有操作都将映射到基本表。北京信息科技大学数据库应用与设计视图示意从基本表院系和教师派生出虚拟的教师列表物理上不存在，数据分别存储在院系和教师表中，但是用户无需知道这些北京信息科技大学数据库应用与设计视图的定义定义视图的核心是SQL SELECT语句；视图中可以重新命名列名。CREATE VIEW schema_name.view_name(column ,.n)AS select_statement北京信息科技大学数据库应用与设计举例例3-70：在student模式中定义一个教授视图，该视图中的数据源自教师表

28、中职称为教授的教师记录。如果如上的教授视图存在，则如下两条语句等价：CREATE VIEW student.教授 AS SELECT 教师编号,院系,姓名,性别,专业FROM student.教师 WHERE 职称=教授SELECT*FROM student.教授 SELECT 教师编号,院系,姓名,性别,专业FROM student.教师 WHERE 职称=教授 北京信息科技大学数据库应用与设计视图的操作对用户而言视图和基本表一样都是关系，所以视图可以和基本表一样进行操作；对视图的所有操作DBMS都要转换成对基本表的操作，所以操作能否成功取决于基本表上的完整性约束。北京信息科技大学数据库应用

29、与设计视图操作举例(参见P43教师表的定义)将刘昌勇教授的专业修改为“信息系统”：UPDATE student.教授 SET 专业=信息系统WHERE 姓名=刘昌勇将刘昌勇教授的所在的院系号修改为1：UPDATE student.教授 SET 院系=1WHERE 姓名=刘昌勇北京信息科技大学数据库应用与设计视图操作举例(参见P43教师表的定义)职称字段的值？系统功能的完善INSERT INTO student.教授(教师编号,姓名,性别,专业)VALUES(010222,刘炳辉,男,信息管理)北京信息科技大学数据库应用与设计定义视图举例例3-71：建立一个“教师列表”视图，包含院系表中的名称字

30、段（在视图中命名为院系）和教师表中的姓名、性别及职称字段。北京信息科技大学数据库应用与设计思考题 为什么说视图也是关系？视图中的数据是如何产生的？使用视图会带来哪些优点？视图中是否可以包含虚列（计算列）？对视图的插入、删除、修改操作是如何转换为对基本表的操作的？对视图的插入、删除、修改操作需要注意哪些问题？根据上一题的讨论归纳哪类视图是可以更新的、哪类视图是不可以更新的？并分别举例说明。为什么通过视图插入的记录有可能通过视图却查询不到？举一个这样的例子。北京信息科技大学数据库应用与设计本章小结 SQL的数据定义功能n表的定义和数据完整性及其定义SQL的数据操作功能n数据操作及完整性约束的作用SQL的数据查询功能 n简单查询、连接查询、汇总查询、嵌套查询视图n概念、定义和使用SQL语言是本课程的重点！是学习和掌握关系数据库的基础，希望大家能够深刻理解和熟练掌握本章的内容。北京信息科技大学数据库应用与设计本章实验实验2 建立表和定义完整性约束 实验3 数据操作和完整性约束的作用 实验4 数据查询 实验5 视图的应用 具体要求参见教材