Chapter5SQL语言.ppt

第3节 SQL的数据查询,第5章 关系数据库语言SQL,主要内容,简单查询 常用函数及其使用方式 SQL高级查询技术 谓词演算查询,2,数据查询,SELECT命令的格式与基本使用 SELECT ALL|DISTINCTTOP N PERCENTWITH TIES 列名AS 别名1 ，列名 AS 别名2 INTO 新表名 FROM表名1或视图名1AS 表1别名 ，表名2或视图名2AS 表2别名 WHERE检索条件 GROUP BY HAVING ORDER BY ASC|DESC,投影,选取,3,一、简单查询,1、SELECT和FROM子句 SELECT FROM WHERE 作用 SELECT：，用来列出查询结果中的属性 FROM：，用来列出需扫描的关系 WHERE ：，指定选择记录的条件,4,2、无条件查询,例3.16 查询教学管理数据库中全部学生的基本信息。 SELECT * FROM S；,5,2、无条件查询（续）,例3.17 查询教学管理数据库中全部教师的教职工编号、姓名、职称和所属教研室。 SELECT T#，TNAME，TITLEOF，TRSECTION FROM T；,6,2、无条件查询 聚合函数,聚合函数 能够根据查询结果的记录集或根据查询结果的记录集中某列值的特点返回一个汇总信息的函数。 输入值的一个集合 返回单个值,7,8,聚合函数 COUNT(*) 计算元组的个数 COUNT(列名) 计算某一列中数据的个数 COUNT DISTINCT(列名) 计算某一列中不同值的个数 SUM(列名) 计算某一数据列中值的总和 AVG(列名) 计算某一数据列中值的平均值 MIN(列名) 求（字符、日期、数值列）的最小值 MAX(列名) 求（字符、日期、数值列）的最大值,2、无条件查询 聚合函数（续）,9,例3.18 查询开课总门数 SELECT COUNT(*) FROM C； 例3.19 计算所有学生所学课程的最高分数、最低分数和平均分数。 SELECT MAX(GRADE),MIN(GRADE),AVG(GRADE) FROM SC；,2、无条件查询 聚合函数（例）,10,3、单条件查询,例3.20 查询所有学习了计算机网络课（课程号为C403001）的学生的学号和成绩 SELECT S#，GRADE FROM SC WHERE C# =C403001；,11,4、多条件查询,例3.21 查询选修了计算机网络课（课程号为C403001）或信息安全技术课（课程号为C403002）的学生的学号 SELECT S# FROM SC WHERE C# =C403001 OR C# =C403002 ；,12,5、分组查询,SELECT FROM WHERE GROUP BY 作用： 对满足WHERE条件的元组集按照指定的列的值进行分组，该属性列值相等的元组为一组,13,例,例3.23 计算各个同学的平均分数。 SELECT S#，AVG(GRADE) FROM SC GROUP BY S#；,SELECT FROM WHERE GROUP BY HAVING 作用： 指定GROUP BY子句形成的分组所应满足的条件,5、分组查询（续）,14,15,例,例3.25 查询学生总数超过300人的专业及其具体的总人数 SELECT SCODE，COUNT(*) FROM S GROUP BY SCODE HAVING COUNT(*)300；,16,6、结果的排序,ORDER BY ASC/DESC , ASC/DESC； 作用： 依据给定列的值，控制查询结果中元组的排列顺序。,17,6、结果的排序（例）,例3.27 按学号递增、课程成绩递减的显示顺序显示学生的课程成绩 SELECT S#，C#，GRADE FROM SC ORDER BY S# ASC，GRADE DESC；,18,7、字符串的匹配,下划线通配符_：代表任意一个字符 百分号通配符%：代表任意长度的字符串 例3.28 查询S中姓李的学生的学号和姓名SELECT S#，SNAME FROM S WHERE SNAME LIKE 李%；,19,简单查询语句,SELECT FROM WHERE GROUP BY HAVING ORDER BY ASC/DESC , ASC/DESC；,读取FROM子句指定的基本表或视图的数据，执行笛卡尔积操作,选取满足WHERE子句中给出的条件表达式的元组,按GROUP BY子句中指定列的值进行分组，同时提取满足HAVING子句中组条件表达式的那些组,按SELECT子句中给出的列名或列表达式求值输出,ORDER BY子句对输出的结果（目标表）进行排序，按指定列的值升序或降序排列,20,二、SQL高级查询技术,1、多表联接查询 例3.32 查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名 SELECT S.S#，SNAME FROM S，SC WHERE S.S# = SC.S# AND C# =C401001；,21,2、嵌套查询,在SQL语言中，如果在一个SELECT语句的 WHERE子句中嵌入了另一个SELECT语句，则称这种查询为嵌套查询。 WHERE子句中的SELECT语句称为子查询，外部的查询称为父查询。,22,SELECT * FROM S WHERE CLASS=( ) AND SSEX=女；,2、嵌套查询 例,示例 查询张华同学（学号为200401001）的那个班的女同学的基本信息。,SELECT CLASS FROM S WHERE S#=200401001,23,3、查询中的表更名,3、查询中的表更名 旧名 AS 新名 例3.35 检索成绩比该课程平均成绩低的学生成绩表。 SELECT S#，C#，GRADE FROM SC WHERE GRADE (SELECT AVG(GRADE) FROM SC AS X WHERE X.C#=SC.C#)；,24,三、谓词演算查询,所谓基于谓词演算的查询是指把谓词看作是特殊比较操作符、应用于WHERE子句的条件表达式中的查询。,25,格式： IN NOT IN 含义：,1、IN和NOT IN,如果集合1中的数据是集合2中的成员，那么其逻辑值为true，否则为false.,26,例3.36 查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名。 SELECT S.S#，SNAME FROM S WHERE S# IN (SELECT S# FROM SC WHERE C# =C401001)；,1、IN和NOT IN 例,27,格式： ANY SOME 含义：,2、ANY和SOME,如果比较运算符左边的数据与右边集合中至少一个 元素满足运算，那么其逻辑值为true，否则为false.,28,例3.38 查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名。 解法3 SELECT S.S#，SNAME FROM S WHERE S# =ANY (SELECT S# FROM SC WHERE C# =C401001)；,2、ANY和SOME 例,29,格式： ALL 含义：,3、ALL,如果比较运算符左边的数据与右边集合中所有元素满足运算，那么其逻辑值为true，否则为false.,30,3、ALL 例,例3.39 查询考试成绩大于计算机科学专业(专业代码S0401)所有学生课程成绩的学生的基本信息 SELECT S.* FROM S，SC WHERE S.S#=SC.S# AND GRADE ALL (SELECT GRADE FROM S，SC WHERE S.S#=SC.S# AND SCODE=S0401）；,31,格式：EXISTS（） NOT EXISTS（） 含义：,4、EXISTS和NOT EXISTS,EXISTS是存在量词，当集合中至少存在一个元素（非空）时，其逻辑值为true，否则为false。 NOT EXISTS是空集判断，当集合中不存在任何元素（为空）时，其逻辑值为true，否则为false。,32,例3.40 查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名。 SELECT S.S#，SNAME FROM S WHERE EXISTS (SELECT * FROM SC WHERE SC.S# = S.S# AND C# =C401001)；,4、EXISTS和NOT EXISTS 例,33,SELECT C#,AVG(GRADE) FROM SC,TEACH,T WHERE SC.C#=TEACH.C# AND TEACH.T#=T.T# AND TNAME Like 张% GROUP BY C# ORDER BY 2 DESC;,例：求张老师所授课程的每门课程的学生平均成绩，并按平均成绩降序输出。,第4节 SQL的视图操作,第5章 关系数据库语言SQL,主要内容,视图的概念 视图的定义 在视图上进行查询操作 用户视图对数据库系统设计和性能带来的影响,35,一、视图的概念,视图是从一个或几个基本表（或视图）通过视图定义语句所描述的映射关系导出的表。 数据库中，只有视图的定义，没有视图的数据。这些数据还在基本表中。因此，视图是一种“虚表”。,36,CG,S,SC,S256,Wang,C5,80,查询 语句,学生成绩视图CG(S,SNAME,C,GRADE),37,CREATE VIEW () AS WITH READ ONLY | WITH CHECK OPTION；,新定义的视图的各个列的名称,视图被定义成一个只读视图，不能进行插入、删除和修改操作,表示用户必须保证每当向该视图插入或更新数据时，所插入或更新的数据能从该视图查询出来。,二、视图的定义,38,例3.45 定义平均成绩视图（S#，AVG_GRADE ） CREATE VIEW A_GRADE（S#，AVG_GRADE） AS SELECT S#， AVG(GRADE) FROM SC WHERE GRADE IS NOT NULL GROUP BY S#；,查询结果中有表达式或聚合函数，必须重新命名,二、视图的定义 例,39,三、在视图上进行查询操作,数据查询 视图的查询方法和基本表一样，但有了视图后，查询语句变得非常简单。 SELECT * FROM A_GRADE；,40,四、用户视图对数据库系统设计和性能带来的影响,1、视图能够简化用户的操作 2、视图使用户能以多种角度看待同一数据 3、视图对重构数据库提供了一定程度的逻辑独立性 4、视图能够对机密数据提供安全保护,41,第5节 SQL中带有子查询的数据更新操作,第5章 关系数据库语言SQL,主要内容,带有子查询的数据插入操作 带有子查询条件的数据更新操作,43,复习 数据插入,1、数据插入 向表中插入一行（单元组）数据。 INSERT INTO （） VALUES（）；,44,一、带有子查询的数据插入操作,向表中插入一个子查询得到的数据。 INSERT INTO （） ； 子查询是一个合法的SELECT查询语句。,45,例1,设在教学管理数据库系统中，要建立某些临时表来辅助有关管理过程。若其中有一个临时表用于暂存选修了“信息安全技术”课程的学生的学号、姓名、专业名称和班级，表的格式为： S_C(S#, SNAME, SSNAME, CLASS) 则将从有关表中查询出的数据组成的记录插入该表的插入语句为：,46,例1 续,INSERT INTO S_C(S#, SNAME, SSNAME, CLASS) SELECT S.S#, SNAME, SSNAME, CLASS FROM S, SS WHERE SS.SCODE#=S.SCODE# AND S# IN (SELECT SC.S# FROM SC, C WHERE SC.C#=C.C# AND CNAME=“信息安全技术”);,47,INSERT INTO S_AVG(S#,AVG_GRAGE) SELECT S#,AVG(GRADE) FROM SC WHERE S# IN (SELECT S# FROM S WHERE SSEX =女） GROUP BY S# HAVING AVG(GRADE) = 80；,例2：设用表S_A