• 1. 第3章 关系数据库标准语言SQL3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL(不讲)
  • 2. 第3章 关系数据库标准语言SQL3.1 SQL概述1. SQL的特点2. SQL的核心功能3. SQL的基本概念
  • 3. SQL1. 名称由来:Structured Query Language2. 特点:1. 混合型:关系代数、关系演算2. 综合统一:含DDL、DML、DCL3.非过程化:只需提出“做什么”, 数据独立性高4.集合操作:对象和结果都是元组集合5.多种使用方式:独立使用: 自含式 嵌入高级语言:嵌入式3. 版本:74年发明,86年 ANSI公布SQL-86,ISO通过, ANSI: SQL-89、SQL-92、SQL3。6.语言简洁:接近英语口语,易学易用。1. SQL的特点
  • 4. 第3章 关系数据库标准语言SQL3.1 SQL概述1. SQL的特点2. SQL的核心功能3. SQL的基本概念
  • 5. 2. SQL的核心功能SQL功能 命 令 动 词数据查询 SELECT数据定义CREATE,DROP,ALTER数据操纵INSERT,UPDATE,DELETE数据控制GRANT,REVOKE
  • 6. 第3章 关系数据库标准语言SQL1. SQL的特点2. SQL的核心功能3. SQL的基本概念3.1 SQL概述
  • 7. 3. SQL的基本概念基本表:对应于模式。特点: 一个基本关系一个基本表; 多个基本表一个存储文件; 一个基本表可带多个索引,放在存储文件中。 存储文件:逻辑结构内模式,物理结构任意。 视图(view):对应于外模式。 是从基本表或其他视图中导出的虚表,不存储。 基本表和视图都是关系,都用SQL语言进行操作。SQL对数据库三级模式结构的支持:视图1视图2基本表2存储文件1SQL模式内模式基本表1基本表3存储文件2外模式
  • 8. 第3章 关系数据库标准语言SQL3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL
  • 9. 第3章 关系数据库标准语言SQL3.2 数据定义1. 数据定义概述 2. 定义、删除与修改基本表3. 建立与删除索引
  • 10. 1. 数据定义概述 创建和删除: 基本表、视图、索引。 修改基本表。无修改索引、视图的操作: 删除重建。操作对象 操作方式创建删除修改表CREATE TABLEDROP TABLEALTER TABLE视图CREATE VIEWDROP VIEW索引CREATE INDEXDROP INDEX本节只介绍基本表和索引,视图在3.5节介绍。
  • 11. 第3章 关系数据库标准语言SQL3.2 数据定义1. 数据定义概述 2. 定义、删除与修改基本表3. 建立与删除索引
  • 12. 1. 定义基本表 一般格式: CREATE TABLE <表名> ( <列名> <数据类型> [列级完整性约束] [,<列名> <数据类型> [列级完整性约束]...] [,<表级完整性约束条件>] ) [;] <表名>:基本表的名字, <列名>:基本表所含的属性(列)的名字,一个或多个。 列级完整性约束:规定列要满足的一些约束条件(仅涉及一个属性,如:NOT NULL、UNIQUE、单属性主码、外码等) 表级完整性约束条件:规定表要满足的一些约束条件(涉及一个或多个属性,如:单属性或组合的主码、外码等) <数据类型> :指明属性的数据类型及长度。不同DBMS支持的数据类型不完全相同(在实验课中介绍)。 ;为语句结束标志,在SQL Server中可省略。  2. 定义、删除与修改基本表
  • 13. CREATE TABLE Student ( Sno CHAR(5) CONSTRAINT PK_Stu PRIMARY KEY, Sname CHAR(20) CONSTRAINT UQ1 UNIQUE, Ssex CHAR(2), Sage INT NOT NULL CONSTRAINT UQ2 UNIQUE CONSTRAINT CK1 CHECK(Sage>=15 AND Sage<=35), Sdept CHAR(15), CHECK(Ssex IN ('男','女') AND Sno LIKE '95___ ') ) 说明: 1) CONSTRAINT用来为约束取名字,如果不取名则不用 2) 一列可以含多个约束,其间用空格分隔 3) 在SQL Server中, [NOT] NULL不是对象,可取名但无效。 4) 列级CHECK约束中的<逻辑表达式>只能访问本列 5) 表级CHECK约束中的<逻辑表达式>可访问本表的所有列例1 建立: Student(Sno, Sname, Ssex, Sage, Sdept) Course(Cno, Cname, Cpno,Ccredit) SC(Sno, Cno, Grade) 例:创建基本表
  • 14. CREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY, Cname CHAR(20), Cpno CHAR(4), Ccredit INT ) CREATE TABLE SC ( Sno CHAR(5) FOREIGN KEY REFERENCES Student(Sno), Cno CHAR(4) FOREIGN KEY REFERENCES Course(Cno), Grade INT, PRIMARY KEY (Sno, Cno) ) 要先建Student和Course, 后建SC,为什么??例1 建立: Student(Sno, Sname, Ssex, Sage, Sdept) Course(Cno, Cname, Cpno,Ccredit) SC(Sno, Cno, Grade) 例:创建基本表
  • 15. ALTER TABLE <表名> [ ADD <新列名> <数据类型> [完整性约束] ] [ DROP COLUMN <列名> ] [ ALTER COLUMN <列名> <数据类型> [ [NOT] NULL] ] [ DROP [CONSTRAINT] <完整性约束名> ] <表名>: 指定要修改的基本表; ADD子句: 用于增加新列和新的完整性约束; DROP COLUMN子句: 用于删除指定列 ALTER COLUMN 子句:用于修改原有的列定义 DROP [CONSTRAINT] 子句:用于删除完整性约束;2. 修改基本表 一般格式为(与书上格式有区别):2. 定义、删除与修改基本表
  • 16. 例:修改基本表例2 向Student表增加Scity列。 ALTER TABLE Student ADD Scity CHAR(20) CONSTRAINT CK3 CHECK( Scity IN ('杭州', '宁波') ) 如果表中已有数据,则新增加的列一律为空值。 例3 将年龄的数据类型改为SMALLINT并允许取空值 。 ALTER TABLE Student ALTER COLUMN Sage SMALLINT NULL 1)若有约束对象访问该列,则不允许修改,除非删除这些对象 2)修改原有的列定义有可能会破坏已有数据。 例4 删除Scity列。 ALTER TABLE Student DROP COLUMN Scity 若有约束对象访问该列,则不允许删除。 例5 先删除约束对象CK1、CK3、UQ2, 然后执行例3和例4。 ALTER TABLE Student DROP CK1, CK3, UQ2
  • 17. DROP TABLE <表名>[, <表名> ]… 例5 删除SC表,Couser表和Student表 DROP TABLE SC , Course, Student 1) SC一定要写在Course和Student前面,为什么?? 2) 基本表定义一旦删除,表中的数据、索引都将自动被删除掉。执行删除操作一定要格外小心。3. 删除基本表 一般格式为:2. 定义、删除与修改基本表
  • 18. 第3章 关系数据库标准语言SQL3.2 数据定义1. 数据定义概述 2. 定义、删除与修改基本表3. 建立与删除索引
  • 19. 3. 建立与删除索引 索引是加快查询速度的有效手段: 无索引:在表R中查找A属性值为ai的元组,顺序查找。比较次数为 |R|/2有索引:在索引顺序表中二分查找ai,…A…………………ai………………t索引项记录位置……aiLoc……若|R|=232=4294967296, 无索引要比较2147483647次 有索引只需比较32次。根据记录位置找到所需记录。比较次数为log2(|R|)。按索引项的值升序或降序
  • 20. 可以在一个基本表上建立一个或多个索引。 1.建立索引 一般格式为: CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名>[<次序>] [,<列名>[<次序>] ]... ) <表名>:指定要建索引的基本表。 <列名>:指定要建索引的列名。索引可以建在一列或多列上,各列名之间用逗号分隔。 <次序>:每个<列名>后可指定索引值的排列次序,包括ASC(升序)和DESC(降序)两种,缺省值为ASC。 UNIQUE:表明每一个索引值只对应唯一的数据记录。 CLUSTER:建立聚簇索引:表中的记录按索引项的顺序存放。一个基本表最多只能建立一个聚簇索引。 聚簇索引列数据的更新会导致表中记录的物理移动,代价较大,因此经常更新的列不宜建聚簇索引。 3. 建立与删除索引
  • 21. 例:建立索引 例6 为Student、Course、SC建立索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno) /*默认升序*/ CREATE UNIQUE INDEX Coucno ON Course(Cno) /*默认升序*/ CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC)
  • 22. DROP INDEX <表名>.<索引名>[, <表名>.<索引名>]… 例7 删除索引Stusno、 Coucno、 SCno DROP INDEX Student.Stusno,Course.Coucno,SC.SCno 1)索引一经建立,就由DBMS使用和维护它,不需用户干预; 2)如果数据增删改频繁,系统会花费许多时间来维护索引, 这时,可以删除一些不必要的索引。2. 删除索引 一般格式为:3. 建立与删除索引
  • 23. 第3章 关系数据库标准语言SQL3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL
  • 24. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询
  • 25. Π ( )1. 查询概述 SQL用SELECT语句进行查询,其一般格式为(含若干子句): SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>]...  FROM <表名或视图名> [,<表名或视图名>] ...  [WHERE <条件表达式>] [GROUP BY <列名列表1> [HAVING <条件表达式>] ] [ORDER BY <列名列表2>  [ASC|DESC]] 含义:σ ( )根据WHERE子句的条件表达式,从FROM子句的基本表或视图的笛卡儿积中,找出满足条件的元组,按SELECT子句的目标列表达式,进行投影,形成结果表。Fr×s×…X1, X2,…X1X2…………
  • 26. 1. 查询概述如果有GROUP BY子句,则: 最后将这条“记录”投影到目标列,产生结果表的一条记录。 演示 然后将(聚)集函数作用到组,得到一条“记录”,演示 先将选择结果σF(r× s×…),按<列名列表1>的值分组(值相等的元组分为一组),演示如果有ORDER BY,则结果表还要按<列名列表2>的值升序或降序排列。如果GROUP BY带HAVING,则只输出满足HAVING条件的组SQL用SELECT语句进行查询,其一般格式为(含若干子句): SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>]...  FROM <表名或视图名> [,<表名或视图名>] ...  [WHERE <条件表达式>] [GROUP BY <列名列表1> [HAVING <条件表达式>] ] [ORDER BY <列名列表2>  [ASC|DESC]] 含义:
  • 27. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值判断,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组指仅涉及一个表的查询,包括:选择列、选择行。是最简单的查询。
  • 28. 2. 单表查询 选择表中的部分或全部列,是投影。 1) 查询指定列 例1 查询全体学生的学号与姓名。 SELECT Sno, Sname FROM Student 结果表中列的顺序可以和原表不同: 例2 查询全体学生的姓名、学号、所在系。 SELECT Sname, Sno, Sdept FROM Student(1)选择列:
  • 29. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值判断,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 30. a) 在SELECT子句中列出所有属性; b) 若结果表列顺序与原表相同,可将 <目标列表达式>指定为* 例3 查询全体学生的详细记录 SELECT * FROM Student 无条件地查询表的全部信息:全表查询,最简单。 2) 查询所有列。两种方法:2. 单表查询
  • 31. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值判断,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 32. <目标列表达式>可以是算术表达式:  例4 查全体学生的姓名及其出生年份 SELECT Sname, YEAR( GETDATE( ) ) - Sage FROM Student 用当前年份减去学生的年龄,得到学生的出生年份。输出的结果为: Sname (无列名) 李勇 1987 刘晨 1988 王名 1989 张立 1988 3) 查询计算列:表中不存在,是计算而得的列<目标列表达式>也可以是字符串常量、函数等2. 单表查询
  • 33. SELECT Sname, 'Birth In', 2007-Sage, LOWER(Sdept) FROM Student 结果为: Sname (无列名) (无列名) (无列名) 李勇 Birth In 1987 cs 刘晨 Birth In 1988 is …………………..……………………………………… 可以为结果表的列指定别名,例如: SELECT Sname AS NAME, 'Birth In' BIRTH, 2007-Sage AS 'Year of Birth', LOWER(Sdept) DEPARTMENT FROM Student 结果为: NAME BIRTH Year of Birth DEPARTMENT 李勇 Birth In 1987 cs …………………..……………………………………… 思考:要将2007-Sage 列的别名改为Birth's Year, 应如何写?例5 查全体学生的姓名、出生年份和所在系,要求用小写字母表示所在系名。
  • 34. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值判断,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 35. 1) 消除重复行: 例6 查选修了课程的学生的学号。 SELECT Sno FROM SC 假设SC表中有下列数据: Sno Cno Grade   95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80 选择部分元组的部分列或全部列。 需指定DISTINCT短语、WHERE子句。2. 单表查询用DISTINCT短语可消除重复: SELECT DISTINCT Sno FROM SC执行结果为: Sno 95001 95001 95001 95002 95002结果为: Sno 95001 95002(2)选择行:
  • 36. 1) 消除重复行: 例6 查选修了课程的学生的学号。 SELECT DISTINCT Sno FROM SC 选择部分元组的部分列或全部列。 需指定DISTINCT短语、WHERE子句。2. 单表查询注意:DISTINCT属于整个SELECT子句, 必须紧跟SELECT 例如以下SELECT语句是错误的: SELECT Ssex, DISTINCT Sname FROM Student 正确的语句为: SELECT DISTINCT Ssex, Sname FROM Student(2)选择行:
  • 37. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值判断,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 38. 2)查询满足条件的行:通过WHERE子句实现。查询条件运算比较大小=, >, <, >=, <=, !=, <>, !>, !<确定范围BETWEEN AND, NOT BETWEEN AND确定集合IN, NOT IN字符匹配LIKE, NOT LIKE空值判断IS NULL, IS NOT NULL组合条件NOT, AND, OR下面逐一举例说明。 WHERE子句中常用的查询条件为:
  • 39. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值判断,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 40. 例7 查CS系全体学生的名单。 SELECT Sname FROM Student WHERE Sdept= 'CS' 例8 查年龄在20岁以下的学生姓名及其年龄。 SELECT Sname, Sage FROM Student WHERE Sage <20①比较大小: =, >, <, >=, <=, !=, <>, !>, !<或: SELECT Sname, Sage FROM Student WHERE NOT Sage>= 20例9 查至少有一门课不及格的学生学号。 SELECT DISTINCT Sno FROM SC WHERE Grade <60 这里使用了DISTINCT短语,当一个学生有多门课程不及格,他的学号也只列一次。
  • 41. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值判断,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 42. 例10 查询年龄在20至23岁之间的学生的姓名、系别和年龄 SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23 与BETWEEN...AND相对的运算是NOT BETWEEN...AND 例11 查询年龄不在20至23岁之间的学生姓名、系别和年龄 SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23 显然: X BETWEEN L AND H 等价于: X>=L AND X<=H X NOT BETWEEN L AND H 等价于: XH②确定范围: <表达式> BETWEEN <下限> AND <上限>
  • 43. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值判断,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 44. 例12 查IS系、 MA系或CS系的学生姓名和性别。 SELECT Sname, Ssex FROM Student   WHERE Sdept IN ('IS', 'MA', 'CS') /*用(…)表示集合*/ <表达式> NOT IN <集合> 查找属性值不属于指定集合的元组 例13 查既不是IS系和MA系,也不是CS系的学生姓名和性别 SELECT Sname, Ssex FROM Student   WHERE Sdept NOT IN ('IS', 'MA', 'CS') 显然: X IN (e1, …, ek) 等价于: X=e1 OR…OR X=ek X NOT IN (e1, …, ek)等价于: X<>e1 AND…AND X<> ek③确定集合: <表达式> IN <集合>查找属性值属于指定集合的元组
  • 45. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值判断,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 46. <主串> [NOT] LIKE '<匹配串>' [ESCAPE '<换码符>'] 含义:若<主串>与<匹配串>匹配则为真,否则为假。 <匹配串>:由普通字符串和通配符(%和_)构成: %:代表任意字符串。如a%b:a开头b结尾,长度>=2 _:代表任意一个字符。如a_b:a开头b结尾, 长度为3 例14 查所有姓刘的学生姓名、学号和性别。 SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE '刘%' 例15 查姓“欧阳”且全名为3个汉字的学生姓名。 SELECT Sname FROM Student WHERE Sname LIKE '欧阳_' 注意:在支持中文的DBMS中一个汉字为一个字符。 例16 查名字中第2个字为“阳”的学生的姓名和学号。 SELECT Sname, Sno FROM Student WHERE Sname LIKE '_阳%'④字符串匹配 :用LIKE运算符实现,格式为:
  • 47. SELECT Sname , Sno, Ssex FROM Student WHERE Sname NOT LIKE '刘%' 若需要匹配%或_本身,就要用ESCAPE对通配符进行转义, 即将匹配串中的%或_转义为普通字符。 例18 查DB_ Design课程的课程号和学分。 SELECT Cno, Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\' ESCAPE ‘\’ 表示 \ 为换码字符,这样紧跟在 \ 后面的字符“_”不再是通配符,被转义为普通的“_”字符。 例19 查以“DB_”开头,倒数第三个字符为i的课程信息。 SELECT * FROM Course WHERE Cname LIKE 'DB\_%i _ _' ESCAPE '\' 注意:若匹配串中不含%或_, 则: X LIKE Pat 等价于: X = Pat X NOT LIKE Pat 等价于: X <> Pat例17 查所有不姓刘的学生姓名。
  • 48. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值判断,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 49. 例20 查成绩为空的选课记录中的学号和课程号。 SELECT Sno, Cno FROM SC WHERE Grade IS NULL 注意:IS不能用等号代替。 例21 查有成绩的选课记录中的学号和课程号。 SELECT Sno, Cno   FROM SC WHERE Grade IS NOT NULL 注意: X IS NOT NULL等价于: NOT X IS NULL⑤空值判断 :IS NULL、IS NOT NULL
  • 50. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 51. 例22 查询CS系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept='CS' AND Sage<20⑥组合条件查询:用NOT、AND、OR和括号连接多个简单查询条件,形成一个复杂查询条件。例12 查IS系、 MA系或CS系的学生姓名和性别。 SELECT Sname, Ssex FROM Student   WHERE Sdept IN ('IS', 'MA', 'CS')IN运算实际上是多个OR运算的缩写,因此例12中的查询 也可以用OR运算写成: SELECT Sname, Ssex   FROM Student   WHERE Sdept='IS' OR Sdept='MA' OR Sdept='CS'
  • 52. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 53. 例23 查询选修了3号课程的学号及成绩,按成绩降序排列。 SELECT Sno, Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC 例24 查询全体学生情况,按所在系升序排列,对同一系的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept, Sage DESC(3)对查询结果排序:用ORDER BY指定按某些列升序(ASC)或降序(DESC) ,其中ASC为缺省值。
  • 54. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 55. COUNT([DISTINCT] *) 统计元组个数 COUNT([DISTINCT] <列名>) 统计列值的个数 SUM([DISTINCT] <列名>) 计算数值型列的总和 AVG([DISTINCT] <列名>) 计算数值型列的平均值 MAX([DISTINCT] <列名>) 求列的最大值 MIN([DISTINCT] <列名>) 求列的最小值 如果指定DISTINCT,则在计算时要取消重复值。 例25 查询学生总人数。 SELECT COUNT(*) FROM Student 例26 查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC 例27 计算1号课程的平均成绩。 SELECT AVG(Grade) FROM SC WHERE Cno='1' 例28 查询1号课程的最高分数。 SELECT MAX(Grade) FROM SC WHERE Cno='1'(4)使用集函数:SQL用集函数进行统计查询,包括:
  • 56. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 57. 查询结果为: Cno  (无列名) 1 1 2 2 3 2 思考:下列语句正确吗?为什么? SELECT Cno, Sno FROM SC GROUP BY Cno(5)对查询结果分组: 用GROUP BY子句按列值相等的原则对查询结果进行分组。例29 查询各门课程的课程号及相应的选课人数。SELECT Cno, Sno FROM SCGROUP BY Cno中间结果Cno相同的元组分为一组COUNT( )对每一组作用集函数COUNT,求得选课人数。CnoSno1 2 2 3 395001 95001 95002 95001 95002
  • 58. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询选择列:指定列,所有列,计算列 选择行 消除重复行 查询满足条件的行。查询条件: 比较大小,确定范围,确定集合 字符匹配,空值,组合条件 对查询结果排序 使用集函数 对查询结果分组 使用HAVING筛选组
  • 59. 例30 查询前4位为“9500”且选修了1门以上课程的学号。 (6)使用HAVING筛选组:只输出满足HAVING条件的组。只有满足HAVING条件的组才会被选出来。 WHERE与HAVING的区别:作用对象不同。 WHERE作用于表,选择元组,不能直接包含集函数。 HAVING作用于组,选择组,常包含集函数。 SELECT Sno FROM SC WHERE Sno LIKE '9500%'GROUP BY SnoHAVING COUNT(*)>1中间结果学号相同的元组分为一组挑选组Sno95001 95001 95001 95002 95002 95003
  • 60. 单表查询单表查询小结选择列(投影):指定列,所有列( * ),计算列 选择行(选择): 消除重复行:DISTINCT 查询满足条件的行。查询条件: 比较大小:=、>、< 等比较运算符 确定范围:[NOT] BETWEEN … AND… 确定集合:[NOT] IN 字符串匹配:[NOT] LIKE 空值判断: IS [NOT] NULL 组合条件:用AND、OR组合多个条件 对查询结果排序:ORDER BY 子句 使用集函数:SUM、COUNT、AVG、MAX、MIN 对查询结果分组:GROUP BY子句 使用HAVING筛选组:HAVING子句 对多表查询仍适用。 当不同表的列名相同时,在列名前加表名前缀: SC.Sno=Student.Sno; GROUP BY SC.Cno
  • 61. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询连接条件的一般格式:连接字段:连接条件中的列名。 连接字段必须是可比的。 种类:等值、非等值、自身、外、复合含义:涉及多个表的查询。 FROM后面涉及多个表 WHERE 条件: 连接条件 and 普通条件[<表1>.]<列1> <比较运算符1> [<表2>.]<列2> and/or [<表3>.]<列3> <比较运算符2> [<表4>.]<列4> and/or …………………………………………………………… [<表2n-1>.]<列2n-1 > <比较运算符n> [<表2n>.]<列2n>
  • 62. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询等值连接 非等值连接 自身连接 外连接 复合条件连接广义笛卡儿积 自然连接特例
  • 63. 3. 连接查询 等值连接:<比较运算符>为“=”的连接。 非等值连接: <比较运算符>不是“=”的连接。 连接过程:设表1与表2连接 for (表1中的每个元组t ){ } for (表2中的每个元组s ){ } if ( t和s使连接条件成立 )(1) 等值连接与非等值连接表1…………表2…………结果表……………………t.<列1> <比较运算符> s.<列2> 成立?ts拼接拼接t和s, 形成结果表的一个元组;
  • 64. 涉及Student与SC两个表。将这两个表中学号相同的元组连接起来,是等值连接: SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.SnoSCSnoCnoGrade95001 95001 95001 95002 950021 2 3 2 392 85 88 90 80StudentSnoSnameSsexSageSdept95001 95002 95003 95004李勇 刘晨 王名 张立男 女 女 男20 19 18 19CS IS MA IS查询结果Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001 95001 95001 95002 95002李勇 李勇 李勇 刘晨 刘晨男 男 男 女 女20 20 20 19 19CS CS CS IS IS95001 95001 95001 95002 950021 2 3 2 392 85 88 90 80例31 查询学生及其选课情况。
  • 65. 广义笛卡儿积:不带连接条件的连接。表1的每个元组都要与表2的每个元组拼接,结果表往往很大。无实际意义。 自然连接:按两表的相同属性进行等值连接,去掉重复列。 例32 自然连接Student和SC表Student.SnoSnameSsexSageSdeptCnoGrade95001 95001 95001 95002 95002李勇 李勇 李勇 刘晨 刘晨男 男 男 女 女20 20 20 19 19CS CS CS IS IS1 2 3 2 392 85 88 90 80有两种特殊的连接:广义笛卡儿积、自然连接。 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student, SC WHERE Student.Sno=SC.Sno
  • 66. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询等值连接 非等值连接 自身连接 外连接 复合条件连接广义笛卡儿积 自然连接特例
  • 67. 例33 查询每一门课的先行课的先行课。 思路: (1) 准备两张Course表: C1、C2C1CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2)自身连接:一个表与自身进行的连接。C2CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2) FOR (C1表的每门课t){ }得到t的先行课号t.Cpnot到C2表中查询并输出课程t.Cpno的先行课号Cno Cppno
  • 68. 到C2表中查询并输出课程t.Cpno的先行课号Cno Cppno t1 7例33 查询每一门课的先行课的先行课。 思路: (1) 准备两张Course表: C1、C2C1CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2)自身连接:一个表与自身进行的连接。C2CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2) FOR (C1表的每门课t){ }得到t的先行课号t.Cpno
  • 69. t例33 查询每一门课的先行课的先行课。 思路: (1) 准备两张Course表: C1、C2C1CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2)自身连接:一个表与自身进行的连接。C2CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2) FOR (C1表的每门课t){ }得到t的先行课号t.Cpno到C2表中查询并输出课程t.Cpno的先行课号Cno Cppno 1 7
  • 70. 例33 查询每一门课的先行课的先行课。 思路: (1) 准备两张Course表: C1、C2C1CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2)自身连接:一个表与自身进行的连接。C2CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2) FOR (C1表的每门课t){ }得到t的先行课号t.Cpno到C2表中查询并输出课程t.Cpno的先行课号Cno Cppno 1 73 5t
  • 71. 例33 查询每一门课的先行课的先行课。 思路: (1) 准备两张Course表: C1、C2C1CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2)自身连接:一个表与自身进行的连接。C2CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2) FOR (C1表的每门课t){ }得到t的先行课号t.Cpno到C2表中查询并输出课程t.Cpno的先行课号Cno Cppno 1 73 54 NULLt
  • 72. 例33 查询每一门课的先行课的先行课。 思路: (1) 准备两张Course表: C1、C2C1CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2)自身连接:一个表与自身进行的连接。C2CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2) FOR (C1表的每门课t){ }得到t的先行课号t.Cpno到C2表中查询并输出课程t.Cpno的先行课号Cno Cppno 1 73 54 NULL5 6t
  • 73. 例33 查询每一门课的先行课的先行课。 思路: (1) 准备两张Course表: C1、C2C1CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2)自身连接:一个表与自身进行的连接。C2CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2) FOR (C1表的每门课t){ }得到t的先行课号t.Cpno到C2表中查询并输出课程t.Cpno的先行课号Cno Cppno 1 73 54 NULL5 6t
  • 74. 例33 查询每一门课的先行课的先行课。 思路: (1) 准备两张Course表: C1、C2C1CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2)自身连接:一个表与自身进行的连接。C2CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4(2) FOR (C1表的每门课t){ }得到t的先行课号t.Cpno到C2表中查询并输出课程t.Cpno的先行课号Cno Cppno 1 73 54 NULL5 67 NULL
  • 75. (2)自身连接:一个表与自身进行的连接。Cno Cppno1 73 54 NULL5 67 NULL例33 查询每一门课的先行课的先行课。 思路简述:SQL命令: SELECT C1.Cno, C2.Cpno AS Cppno FROM Course AS C1, Course AS C2 WHERE C1.Cpno=C2.Cno两张Course之间的连接C1CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4C2CnoCnameCpnoCcredit1 2 3 4 5 6 7数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL5 1 6 7 64 2 4 3 4 2 4表别名的作用: 1)若FROM后有相同的表, 则要取不同的别名,否则无法表 达目标列和连接条件 2)表名较长时为了缩短目标列和连接条件, 可取较短的别名 3)AS可以省略(与目标列别名相同)
  • 76. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询等值连接 非等值连接 自身连接 外连接 复合条件连接广义笛卡儿积 自然连接特例
  • 77. (3)外连接:普通连接(内连接)只输出满足条件的元组。SCSnoCnoGrade95001 95001 95001 95002 950021 2 3 2 392 85 88 90 80StudentSnoSnameSsexSageSdept95001 95002 95003 95004李勇 刘晨 王名 张立男 女 女 男20 19 18 19CS IS MA IS查询结果Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001 95001 95001 95002 95002李勇 李勇 李勇 刘晨 刘晨男 男 男 女 女20 20 20 19 19CS CS CS IS IS95001 95001 95001 95002 950021 2 3 2 392 85 88 90 80无95003和95004的信息,因他们暂时没选课。不合理!例如:例31 查询学生及其选课情况:
  • 78. (3)外连接:普通连接(内连接)只输出满足条件的元组。查询结果Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001 95001 95001 95002 95002 95003 95004李勇 李勇 李勇 刘晨 刘晨 王名 张立男 男 男 女 女 女 男20 20 20 19 19 18 19CS CS CS IS IS MA IS95001 95001 95001 95002 95002 NULL NULL1 2 3 2 3 NULL NULL92 85 88 90 80 NULL NULL例如:例31 查询学生及其选课情况:有时想以Student表为主,列出学生的基本情况和选课情况,没选课的学生只输出基本情况,其选课信息为空外连接。
  • 79. (3)外连接:普通连接(内连接)只输出满足条件的元组。例如:例31 查询学生及其选课情况:左外连接:将左边关系的未用元组配上空值加到结果集中。 右外连接:将右边关系的未用元组配上空值加到结果集中。 全外连接:将两边关系的未用元组配上空值加到结果集中。 改写例31: SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno *= SC.Sno 连接运算也可写为: SELECT Student.*, SC.* FROM Student [INNER] JOIN SC ON Student.Sno=SC.Sno LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN外连接运算符:*=(左外连接)、 =* (右外连接) 。有时想以Student表为主,列出学生的基本情况和选课情况,没选课的学生只输出基本情况,其选课信息为空外连接。
  • 80. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询等值连接 非等值连接 自身连接 外连接 复合条件连接广义笛卡儿积 自然连接特例
  • 81. 例34 查询选修2号课且成绩在90分以上的学生学号和姓名 (4)复合条件连接:SCStudentSELECT Student.Sno, Sname FROM Student, SCSELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course WHEREWHEREStudent.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90WHERE子句中有多个条件的连接。还可以进行多表连接:Course例35 查询学生的学号、姓名、选修的课程名及成绩。StudentSCAND SC.Cno=Course.CnoStudent.Sno=SC.Sno含n张表的连接查询至少应有n-1个连接条件,否则在某些位置将蜕化为笛卡尔积。
  • 82. 格式1: SELECT … FROM Student S, SC, Course C WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno /*连接条件*/ AND <普通查询条件> 格式2: SELECT … FROM Student S JOIN SC ON S.Sno=SC.Sno JOIN Course C ON SC.Cno=C.Cno WHERE <普通查询条件> 内连接时两种格式等价; 外连接时:连接查询格式的进一步说明1)格式1不能用于全外连接,格式2可以2)对含普通查询条件的外连接,格式1和格式2含义不同:格式1: SELECT … FROM Student S, SC, Course C WHERE S.Sno*=SC.Sno AND SC.Cno*=C.Cno /*错误*/ 格式2: SELECT … ROM Student S LEFT OUTER JOIN SC ON S.Sno=SC.Sno LEFT OUTER JOIN Course C ON SC.Cno=C.Cno --格式1 (查询IS系的学生、选修1号课的学生或未选课的学生): SELECT S.*, SC.* FROM Student S, SC WHERE S.Sno*=SC.Sno AND ( Sdept= 'IS' OR Cno= '1' ) --格式2 (查询IS系的学生或选修1号课程的学生): SELECT S.*, SC.* FROM Student S LEFT OUTER JOIN SC ON S.Sno=SC.Sno WHERE Sdept= 'IS' OR Cno='1' 格式2是从外连接结果中挑选满足普通查询条件的元组;格式1不对未用元组检查普通查询条件,直接将它们配上空值加到结果集中;
  • 83. T1×… ×Tn含义:涉及多个表的查询。 FROM后面涉及多个表 WHERE 条件连接条件 and 普通查询条件 种类:等值与非等值~: <列1> <比较运算符> <列2> 自身~: FROM后面的表要取别名 外~:左~:*=、右~:=* 、全~ 复合条件~:连接条件 and 普通查询条件 连接条件总数: n张表, n-1个条件 注意:连接查询和单表查询无本质区别。 只要将多个表看成一张大表(多个表的笛卡儿积),SELECT X1, …, Xn FROM T1, …, Tn WHER FΠX1, …, Xn (σF ( T1×… ×Tn ) )连接查询小结连接查询连接查询就是在这张大表上的单表查询。
  • 84. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询概念:结果表查询块: 一条SELECT语句嵌套查询(子查询): 将一个查询块嵌入另一个的WHERE或HAVING中。 例如:SELECT Sname FROM Student WHERE 外层查询 (父~、主~)内层查询 (子查询) Sno IN ( SELECT Sno FROM SC WHERE Cno='2' )
  • 85. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询概念:将子查询嵌入查询条件规定:允许多层嵌套; 子查询不含ORDER BY子句, 即只对最终结果排序。 处理过程: 由内向外: 先求子~,再求父~ 好处:用简单查询构造复杂查询 种类:4类带IN运算的子查询 带比较运算的子查询 带ANY或ALL的子查询 带EXISTS的子查询
  • 86. SdeptIS MASnoSnameSdept95002 95004 95020 95022刘晨 张立 刘晨 张三IS IS MA MA(1)带有IN运算的子查询:用自身连接实现: SELECT S2.Sno, S2.Sname, S2.Sdept FROM Student S1, Student S2 WHERE S1.Sname='刘晨' AND S2.Sdept = S1.Sdept 父查询与子查询用IN连接, 判断父查询的属性值是否包含在子查询的结果表中。 例36 查询与“刘晨” 同系的学生。 分步实现:①确定“刘晨”所在系 SELECT Sdept FROM Student WHERE Sname='刘晨' 用嵌套查询实现: SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname=‘刘晨’ )  ②查找所有IS系或MA系的学生。 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN ('IS', 'MA' )
  • 87. 例37 查询选修了‘信息系统’的学生学号和姓名。1) 在Course中找‘信息系统’ 的课程号集合Cnos思路:CourseCnoCnameCpnoCcredit… 3 … 10 …… 信息系统 … 信息系统 … … 1 … 1 …… 4 … 3 …SELECT Cno FROM Course WHERE Cname='信息系统' CnosCno ( /* Cnos*/ )3 10
  • 88. ( /* Cnos*/ ) ( /* Snos */ )例37 查询选修了‘信息系统’的学生学号和姓名。1) 在Course中找‘信息系统’ 的课程号集合Cnos思路:SELECT Cno FROM Course WHERE Cname='信息系统' CnosCno3 102) 在SC中找选修了Cnos中课程的学号集合SnosSCSnoCnoGrade… 95001 95002 95020 …… 3 3 10 …… … … … …SnosSno95001 9500295020 SELECT Sno FROM SC WHERE Cno IN
  • 89. 例37 查询选修了‘信息系统’的学生学号和姓名。1) 在Course中找‘信息系统’ 的课程号集合Cnos思路:SELECT Cno FROM Course WHERE Cname='信息系统' CnosCno3 10 ( /* Cnos*/ )2) 在SC中找选修了Cnos中课程的学号集合SnosSnosSno95001 9500295020 SELECT Sno FROM SC WHERE Cno IN ( /* Snos */ )3) 在Student中找与Snos中每个学号对应的姓名StudentSnoSnameSsexSageSdept95001 95002 95003 95004 95020李勇 刘晨 王名 张立 张三男 女 女 男 男20 19 18 19 20CS IS MA IS CS查询结果SnoSname95001 李勇95002 刘晨95020 张三 SELECT Sno, Sname FROM Student WHERE Sno IN
  • 90. 例37 查询选修了‘信息系统’的学生学号和姓名。1) 在Course中找‘信息系统’ 的课程号集合Cnos思路:SELECT Cno FROM Course WHERE Cname='信息系统' ( /* Cnos*/ )2) 在SC中找选修了Cnos中课程的学号集合Snos SELECT Sno FROM SC WHERE Cno IN ( /* Snos */ )3) 在Student中找与Snos中每个学号对应的姓名 SELECT Sno, Sname FROM Student WHERE Sno IN也可以用连接查询实现: SELECT S.Sno, Sname FROM Student AS S, SC, Course AS C WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno AND C.Cname='信息系统' 以上两例是不相关子查询: 子查询的条件不依赖于父查询
  • 91. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询概念:将子查询嵌入查询条件规定:允许多层嵌套; 子查询不含ORDER BY子句, 即只对最终结果排序。 处理过程: 由内向外: 先求子~,再求父~ 好处:用简单查询构造复杂查询 种类:4类带IN运算的子查询 带比较运算的子查询 带ANY或ALL的子查询 带EXISTS的子查询
  • 92. 父查询与子查询之间用比较运算符连接。要求内层查询结果只含一个元组,且只有一列。 例36(查询与“刘晨” 同系的学生) 若学校只有一个“刘晨”,则可用=来实现: SELECT Sno, Sname, Sdept FROM Student WHERE Sdept = ( SELECT Sdept FROM Student WHERE Sname= '刘晨' ) 注意:书上讲:子查询一定要跟在比较运算符之后, SQL Server 无此限制, 返回单值的子查询甚至可参加表达式运算,例如: 查询“刘晨”所在系的每个学生比“刘晨”大多少岁。 SELECT Sname, Sage-( SELECT Sage FROM Student WHERE Sname = '刘晨' ) FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= '刘晨' ) = Sdept(2)带有比较运算的子查询:
  • 93. 父查询与子查询之间用比较运算符连接。要求内层查询结果只含一个元组,且只有一列。 例36(查询与“刘晨” 同系的学生) 若学校只有一个“刘晨”,则可用=来实现: SELECT Sno, Sname, Sdept FROM Student WHERE Sdept = ( SELECT Sdept FROM Student WHERE Sname= '刘晨' )(2)带有比较运算的子查询:例37(查询选修了‘信息系统’的学生学号和姓名) 设只有一门‘信息系统’课,则可用=和IN共同实现: SELECT Sno, Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno = ( SELECT Cno FROM Course WHERE Cname='信息系统' ) )
  • 94. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询概念:将子查询嵌入查询条件规定:允许多层嵌套; 子查询不含ORDER BY子句, 即只对最终结果排序。 处理过程: 由内向外: 先求子~,再求父~ 好处:用简单查询构造复杂查询 种类:4类带IN运算的子查询 带比较运算的子查询 带ANY或ALL的子查询 带EXISTS的子查询
  • 95. 子查询结果包含多个单列元组时使用比较运算符+ANY或ALL: >ANY 大于子查询结果中的某个值 =ANY 大于等于子查询结果中的某个值 <=ANY 小于等于子查询结果中的某个值 =ANY 等于子查询结果中的某个值 !=ANY或<>ANY 不等于子查询结果中的某个值(无意义) >ALL 大于子查询结果中的所有值 =ALL 大于等于子查询结果中的所有值 <=ALL 小于等于子查询结果中的所有值 =ALL 等于子查询结果中的所有值(通常无意义) !=ALL或<>ALL 不等于子查询结果中的任何一个值 (3)带ANY或ALL的子查询:
  • 96. SELECT * FROM Student WHERE Sdept <> 'IS' AND Sage 'IS' AND Sage < ( SELECT MAX(Sage) FROM Student WHERE Sdept='IS' ) ORDER BY Sage DESC例38 查询其他系中比IS系某个学生年龄小的学生名单
  • 97. SELECT * FROM Student WHERE Sdept <> 'IS' AND Sage 'IS' AND Sage < ( SELECT MIN(Sage) FROM Student WHERE Sdept='IS' ) ORDER BY Sage DESC ANY、ALL与集函数和IN的对应关系:=<>或!=<<=> >=ANYIN--MIN>=MINALL--NOT INMAX>=MAX例39 查询其他系中比IS系所有学生年龄小的学生名单。
  • 98. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询概念:将子查询嵌入查询条件规定:允许多层嵌套; 子查询不含ORDER BY子句, 即只对最终结果排序。 处理过程: 由内向外: 先求子~,再求父~ 好处:用简单查询构造复杂查询 种类:4类带IN运算的子查询 带比较运算的子查询 带ANY或ALL的子查询 带EXISTS的子查询
  • 99. EXISTS:存在量词,用来对子查询进行检查,若结果非空则返回 true,否则返回 false 例40 查询选修了1号课程的学生姓名。 分析:1) 涉及Student和SC;(4)带EXISTS的子查询:StudentSnoSnameSsexSageSdept95001 95002 95003 95004 95020李勇 刘晨 王名 张立 张三男 女 女 男 男20 19 18 19 20CS IS MA IS CSSCSnoCnoGrade95001 95001 95002 950201 2 3 192 85 80 50st结果Sname2) 对每一个学生s; 3) 若SC中存在满足下列条件的元组t:At.Sno=s.Sno就将s.Sname送入结果关系AND t.Cno='1',李勇
  • 100. EXISTS:存在量词,用来对子查询进行检查,若结果非空则返回 true,否则返回 false 例40 查询选修了1号课程的学生姓名。 分析:1) 涉及Student和SC;(4)带EXISTS的子查询:StudentSnoSnameSsexSageSdept95001 95002 95003 95004 95020李勇 刘晨 王名 张立 张三男 女 女 男 男20 19 18 19 20CS IS MA IS CSSCSnoCnoGrade95001 95001 95002 950201 2 3 192 85 80 50st结果Sname2) 对每一个学生s; 3) 若SC中存在满足下列条件的元组t:At.Sno=s.Sno就将s.Sname送入结果关系AND t.Cno='1',李勇
  • 101. EXISTS:存在量词,用来对子查询进行检查,若结果非空则返回 true,否则返回 false 例40 查询选修了1号课程的学生姓名。 分析:1) 涉及Student和SC;(4)带EXISTS的子查询:StudentSnoSnameSsexSageSdept95001 95002 95003 95004 95020李勇 刘晨 王名 张立 张三男 女 女 男 男20 19 18 19 20CS IS MA IS CSSCSnoCnoGrade95001 95001 95002 950201 2 3 192 85 80 50st结果Sname2) 对每一个学生s; 3) 若SC中存在满足下列条件的元组t:At.Sno=s.Sno就将s.Sname送入结果关系AND t.Cno='1',李勇
  • 102. EXISTS:存在量词,用来对子查询进行检查,若结果非空则返回 true,否则返回 false 例40 查询选修了1号课程的学生姓名。 分析:1) 涉及Student和SC;(4)带EXISTS的子查询:StudentSnoSnameSsexSageSdept95001 95002 95003 95004 95020李勇 刘晨 王名 张立 张三男 女 女 男 男20 19 18 19 20CS IS MA IS CSSCSnoCnoGrade95001 95001 95002 950201 2 3 192 85 80 50st结果Sname2) 对每一个学生s; 3) 若SC中存在满足下列条件的元组t:At.Sno=s.Sno就将s.Sname送入结果关系AND t.Cno='1',李勇张三
  • 103. SELECT Sname FROM Student AS s WHERE EXISTS ( SELECT * FROM SC AS t WHERE t.Sno=s.Sno AND Cno='1' ) 说明:a) 子查询的目标列用*,给出列名无意义。 b) 子查询依赖于父查询的元组相关子查询。 c) 相关子查询的处理过程: for (父查询表的每个元组s) { 在s已知的条件下执行子查询; if (父查询的WHERE条件为真) 用s构造父查询结果表中的一个元组; }需多次执行子查询 EXISTS:存在量词,用来对子查询进行检查,若结果非空则返回 true,否则返回 false 例40 查询选修了1号课程的学生姓名。 分析:1) 涉及Student和SC;(4)带EXISTS的子查询:2) 对每一个学生s; 3) 若SC中存在满足下列条件的元组t:At.Sno=s.Sno就将s.Sname送入结果关系。SQL语句为:AND t.Cno='1',
  • 104. SELECT Sname FROM Student AS s WHERE EXISTS ( SELECT * FROM SC AS t WHERE t.Sno=s.Sno AND Cno='1' ) EXISTS:存在量词,用来对子查询进行检查,若结果非空则返回 true,否则返回 false 例40 查询选修了1号课程的学生姓名。 分析:1) 涉及Student和SC;(4)带EXISTS的子查询:2) 对每一个学生s; 3) 若SC中存在满足下列条件的元组t:At.Sno=s.Sno就将s.Sname送入结果关系。SQL语句为:AND t.Cno='1',也可以用连接运算实现:SELECT Sname FROM Student AS s, SC AS t WHERE s.Sno=t.Sno AND t.Cno='1'
  • 105. 例41 查询未选修 1 号课程的学生姓名。 SELECT Sname FROM Student AS s WHERE NOT EXISTS ( SELECT * FROM SC AS t WHERE t.Sno=s.Sno AND Cno='1' ) 所有带IN、比较运算符、ANY和ALL的子查询都能用带EXISTS的子查询等价替换,反之不然。 例如带IN的例36(查询与“刘晨” 同系的学生): SELECT Sno, Sname, Sdept FROM Student AS s WHERE EXISTS ( SELECT * FROM Student AS t WHERE t.Sname='刘晨' AND t.Sdept=s.Sdept)NOT EXISTS:若子查询为空,则返回真,否则返回假。
  • 106. ( ) ( )SELECT * FROM Course WHERE Cno NOT IN解法1:如果学生s没有选的课不存在,则s选了全部课程! 1)给定学生s, SELECT Cno FROM SC WHERE Sno=s.Sno2)找出学生s没有选的课如果一门课的课程号不在s选修的课程号列表中, 则s没有选修这门课程(用NOT IN)。例42 查询选修了全部课程的学生姓名(难题)。WHERE NOT EXISTS /*s没有选的课不存在s选了全部课程*/3)用WHERE条件表达“s没有选的课不存在”4)完成SELECT语句SELECT Sname关键:找出学生s没有选的课FROM Student AS s查询学生s选修的课程号列表
  • 107. SELECT * FROM SC WHERE Sno=s.Sno AND Cno=c.Cno( ) ( )SELECT * WHERE NOT EXISTS解法2:如果学生s没有选的课不存在,则s选了全部课程! 1)给定学生s和课程c,2)找出学生s没有选的课如果不存在s选修课程c的信息,则s没有选修课程c(用NOT EXISTS)。WHERE NOT EXISTS /*s没有选的课不存在s选了全部课程*/3)用WHERE条件表达“s没有选的课不存在”4)完成SELECT语句SELECT Sname关键:找出学生s没有选的课例42 查询选修了全部课程的学生姓名(难题)。查询学生s选修课程c的信息FROM Student AS sFROM Course AS c
  • 108. 例43 查询选修了95002选修的全部课程的学生学号(难题) 。解法1:如果: 不存在这样的课程,95002选了,学生s没选, 则s选修了95002选修的全部课程! 1) 给定学生s,SELECT SnoWHERE NOT EXISTSSELECT Cno FROM SC WHERE Sno='95002' AND Cno NOT IN SELECT Cno FROM SC WHERE Sno=s.Sno2) 找出95002选了,学生s没选的课程号列表对95002选的一门课c,若c的课程号不在s选修的课程号列表中,则95002选了c而学生s没选c ( )3)用WHERE条件表达“95002选了,学生s没选的课程号不存在”4)完成SELECT语句关键找出学生s选修的课程号列表FROM Student AS s ( )
  • 109. ( )解法1:如果: 不存在这样的课程,95002选了,学生s没选, 则s选修了95002选修的全部课程! 说明:1) 由于用的是NOT EXISTS,可将Cno改为*SELECT Sno FROM Student AS sWHERE NOT EXISTSSELECT Cno FROM SC WHERE Sno='95002' AND Cno NOT IN SELECT Cno FROM SC WHERE Sno=s.Sno由于不在SC中出现的学号不用检查, 所以可将Student换成SC, 但由于学号相同的选修记录可以有多条, 为避免重复,要加DISTINCT。 ( )*SELECT DISTINCT Sno FROM SC AS s二者语义稍有差别:如果95002没有选课,则前者列出Student表中所有学生的学号,而后者只列出SC表中出现的所有学号(排除了没有选课的学生)例43 查询选修了95002选修的全部课程的学生学号(难题) 。
  • 110. ( ) SELECT * FROM SC WHERE Sno=s.Sno AND Cno=c.Cno ( )解法2:如果: 不存在这样的课程,95002选了,学生s没选, 则s选修了95002选修的全部课程! 1) 给定学生s和课程c,SELECT SnoWHERE NOT EXISTSSELECT Cno WHERE Sno='95002' AND NOT EXISTS2) 找出95002选了,学生s没选的课程号列表对95002选的一门课c,若不存在s选修c的信息,则95002选了c而学生s没选c3)用WHERE条件表达“95002选了,学生s没选的课程号不存在”4)完成SELECT语句关键例43 查询选修了95002选修的全部课程的学生学号(难题) 。找出学生s选修课程c的信息FROM Student AS sFROM SC AS c
  • 111. ( ) SELECT * FROM SC WHERE Sno=s.Sno AND Cno=c.Cno ( )解法2:如果: 不存在这样的课程,95002选了,学生s没选, 则s选修了95002选修的全部课程! 说明:同解法1 SELECT Sno FROM Student AS sWHERE NOT EXISTSSELECT Cno FROM SC AS c WHERE Sno='95002' AND NOT EXISTS*SELECT DISTINCT Sno FROM SC AS s例43 查询选修了95002选修的全部课程的学生学号(难题) 。
  • 112. 第3章 关系数据库标准语言SQL1. 查询概述2. 单表查询3. 连接查询4. 嵌套查询5. 集合查询3.3 查询
  • 113. 例45 查询选修了课程1或者课程2的学生学号。 SELECT Sno FROM SC WHERE Cno='1' UNION SELECT Sno FROM SC WHERE Cno='2'5. 集合查询集合操作:用来将多个结果集合并为一个。包括: (1) UNION:要求各结果表的属性集相同; 系统自动去掉重复元组。 例44 查询CS系的学生或者年龄不大于19岁的学生。 SELECT * FROM Student WHERE Sdept='CS' UNION SELECT * FROM Student WHERE Sage<=19SELECT DISTINCT * FROM Student WHERE Sdept='CS' OR Sage<=19SELECT DISTINCT Sno FROM SC WHERE Cno='1' OR Cno='2'如果结果集来自不同的关系,就不简单地等价于OR
  • 114. 例45* 查询CS系的学生学号或选修了课程1的学生学号。 SELECT Sno FROM Student WHERE Sdept='CS' UNION SELECT Sno FROM SC WHERE Cno='1'5. 集合查询集合操作:用来将多个结果集合并为一个。包括: (1) UNION:要求各结果表的属性集相同; 系统自动去掉重复元组。 例44 查询CS系的学生或者年龄不大于19岁的学生。 SELECT * FROM Student WHERE Sdept='CS' UNION SELECT * FROM Student WHERE Sage<=19SELECT DISTINCT * FROM Student WHERE Sdept='CS' OR Sage<=19SELECT DISTINCT S.Sno FROM Student S LEFT OUTER JOIN SC ON S.Sno = SC.Sno WHERE Sdept= 'CS' OR Cno='1'
  • 115. 例46 查询CS系的学生与年龄不大于19岁的学生的交集。 即:查询CS系中年龄不大于19岁的学生。 SELECT * FROM Student WHERE Sdept='CS' AND Sage<=19 例47 查询CS系的学生与年龄不大于19岁的学生的差集。 即:查询CS系中年龄大于19岁的学生。 SELECT * FROM Student WHERE Sdept='CS' AND Sage>19 例48 查选修课程1的学号集与选修课程2的学号集的交集。 即:查询选修了课程1又选修课程2的学生学号。 SELECT Sno FROM SC WHERE Cno='1' AND Sno IN ( SELECT Sno FROM SC WHERE Cno='2')(2)INTERSECT和MINUS:标准SQL没有交和差操作,但可用其他方法实现。
  • 116. 例46 查询CS系的学生与年龄不大于19岁的学生的交集。 即:查询CS系中年龄不大于19岁的学生。 SELECT * FROM Student WHERE Sdept='CS' AND Sage<=19 例47 查询CS系的学生与年龄不大于19岁的学生的差集。 即:查询CS系中年龄大于19岁的学生。 SELECT * FROM Student WHERE Sdept='CS' AND Sage>19(2)INTERSECT和MINUS:标准SQL没有交和差操作,但可用其他方法实现。 即:查询选修了课程1但没有选修课程2的学生学号。 SELECT Sno FROM SC WHERE Cno='1' AND Sno NOT IN ( SELECT Sno FROM SC WHERE Cno='2')例49 查选修课程1的学号集与选修课程2的学号集的差集。
  • 117. 查询小结 SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>]...  FROM <表名或视图名> [,<表名或视图名>] ...  [WHERE <条件表达式>] [GROUP BY <列名列表1> [HAVING <条件表达式>] ] [ORDER BY <列名列表2>  [ASC|DESC]] (1)目标列表达式的格式: 1) [<表名>.]* 2) <表达式> <表达式> 由列、(集)函数、常量、局部变量、单列单值SELECT 语句和运算符组成。 集函数的一般格式: COUNT SUM AVG ([ALL|DISTINCT] <表达式>) MAX MINCOUNT([ALL|DISTINCT]*)
  • 118. <表达式> 1) <表达式> θ {ANY|ALL} ( 返回单列多值的SELECT语句 )(2)WHERE条件表达式的格式: 2) <表达式> [NOT] BETWEEN <表达式> AND <表达式> ( <值1>[, <值2>]… ) 3) <表达式> [NOT] IN ( SELECT语句 )4) <表达式> [NOT] LIKE <匹配串> 5) <表达式> IS [NOT] NULL 6) [NOT] EXISTS ( SELECT语句 )简单条件表达式7) [NOT] <条件表达式> {AND|OR} [NOT] <条件表达式> [{AND|OR} [NOT] <条件表达式> ]… 复合条件表达式
  • 119. 第3章 关系数据库标准语言SQL3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL
  • 120. 第3章 关系数据库标准语言SQL1. 插入数据2. 修改数据3. 删除数据3.4 数据更新
  • 121. 1. 插入数据INSERT:插入单个元组、插入子查询结果。 (1)插入单个元组: INSERT [INTO] <表名> [(<列1>[,<列2> ]...)] VALUES (<常量1> [,<常量2>]...) 说明: 1) 功能:将新元组插入指定表中,新元组列1的值为常量1,列2的值为常量2,…。两表要等长。 2) 没有在INTO子句中出现的列取空值(定义为NOT NULL的列不能为空,必须在INTO子句中出现)。 3) 如果INTO子句不含列名,则VALUES子句必须指定所有列的值。
  • 122. INSERT INTO Student VALUES ('95020', '陈冬', '男', 'IS', 18) 例2 插入一条选课记录('95020','1') 。 INSERT INTO SC(Sno, Cno) VALUES ('95020', '1' ) 新插入的记录在Grade列上取空值。例1 将新生记录: (学号:95020; 姓名:陈冬; 性别:男; 所在系:IS; 年龄:18) 插入Student表中。
  • 123. INSERT INTO <表名> [(<属性列1>[,<属性列2> ]...)] 子查询 功能:将子查询的结果全部插入指定表中。 例3 求每个系学生的平均年龄,并把结果存入数据库。分两步: a) 建立一个新表,用来存放系名、平均年龄: CREATE TABLE Deptage (Sdept CHAR(15), Avgage SMALLINT) b) 对Student表按系分组求平均年龄,把结果存入新表中。 INSERT INTO Deptage(Sdept, Avgage) SELECT Sdept, AVG(Sage) FROM Student GROUP BY Sdept(2)插入子查询结果:
  • 124. SELECT <目标列>[,…n] INTO <新表名> [SELECT语句的其他子句] 说明:1)将SELECT语句的结果全部插入新表中。 2)功能同INSERT… SELECT, 系统自动建立新表 3)当目标列是计算列时要取别名 例3* 求每个系学生的平均年龄,把结果存入数据库。 SELECT Sdept, AVG(Sage) FROM Student GROUP BY Sdept(3)用SELECT … INTO语句进行数据插入INTO DeptageAS Avgage
  • 125. 第3章 关系数据库标准语言SQL1. 插入数据2. 修改数据3. 删除数据3.4 数据更新
  • 126. 2. 修改数据修改又称为更新,格式为: UPDATE <表名>   SET <列名>=<表达式>[,<列名>=<表达式>]... [ FROM <表名>[, …n] ] [ WHERE <条件>] 功能:1) 修改指定表中满足WHERE条件的元组 2) 用<表达式>的值取代相应的列值 3) 如果省略WHERE子句,则修改表的所有元组 4) WHERE条件可以访问的列名:(1)修改某一个元组的值 例4 将学生95001的年龄改为22岁,姓名改为‘王宏’。 UPDATE Student SET Sage=22, Sname= '王宏' WHERE Sno='95001'
  • 127. 例5 将所有学生的年龄增加1岁。 UPDATE Student SET Sage=Sage+1 (3) 带子查询的修改语句: 用子查询构造修改条件。 例6 将CS系全体学生的所有成绩置零。 UPDATE SC SET Grade=0 WHERE (SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno ) = 'CS' 或: UPDATE SC SET Grade=0 FROM Student AS S WHERE SC.Sno=S.Sno AND Sdept= 'CS' (相当于连接查询)(2) 修改多个元组的值
  • 128. UPDATE一次只能修改一个表,这会带来问题。例如: 学生95001休学一年,复学后要将学号改为96089: 需要修改Student和SC表,要用两条UPDATE语句进行: 1) UPDATE Student SET Sno='96089' WHERE Sno='95001' 2) UPDATE SC SET Sno='96089' WHERE Sno='95001' 执行1)后,已没有学生95001了,但SC表中仍记录着他的选课信息,即参照完整性受到破坏。 只有执行了2)之后,数据库才重新处于一致状态。 但如果执行完1)后,机器突然出现故障,无法再继续执行2),则数据库中的数据将永远处于不一致状态。 必须保证这两条语句要么都做,要么都不做事务(第5章) 如果建立了SC和Student间的参照关系,则1)和2)都不允许执行,除非将关系设置为“级联更新相关的字段”(演示)(4) 修改操作与数据库的一致性
  • 129. 第3章 关系数据库标准语言SQL1. 插入数据2. 修改数据3. 删除数据3.4 数据更新
  • 130. 3. 删除数据删除语句的一般格式为: DELETE [FROM] <表名> [ FROM <表名>[, …n] ] [WHERE <条件>] 功能: a) 从指定表中删除满足WHERE条件的元组。 b) WHERE条件可以访问的列名: c) 若省略WHERE,则删除表中全部元组,留下空表。 d) 一次只能操作一个表,存在潜在的数据不一致问题,除非将关系设置为“级联删除相关的记录” (1)删除某一个元组 例7 删除学号为95001的学生记录。 DELETE FROM Student WHERE Sno='95001' (演示)
  • 131. 例8 删除所有的学生选课记录。 DELETE FROM SC 这条DELETE语句删除SC的所有元组。 (3) 带子查询的删除语句: 用子查询构造删除条件 例9 删除CS系所有学生的选课记录。 DELETE FROM SC WHERE ( SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno) = 'CS' 或: DELETE FROM SC FROM Student AS S WHERE SC.Sno=S.Sno AND Sdept= 'CS' (相当于连接查询)(2) 删除多个元组的值
  • 132. 第3章 关系数据库标准语言SQL3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL
  • 133. 第3章 关系数据库标准语言SQL1. 视图概述2. 定义视图3. 查询视图3.5 视图4. 更新视图5. 视图的用途
  • 134. 视图虚表,从基本表或视图导出操作查询( SELECT )删除(DROP VIEW)定义(CREATE VIEW)更新(增、删、改):同基本表有一定限制1. 视图概述
  • 135. 第3章 关系数据库标准语言SQL1. 视图概述2. 定义视图3. 查询视图3.5 视图4. 更新视图5. 视图的用途
  • 136. (1)创建视图 : CREATE VIEW <视图名>[(<列名>[,<列名>]...)] AS <子查询> [WITH CHECK OPTION] 说明:将一个子查询的结果集定义成一张虚表(视图) a) 子查询中不能含ORDER BY。 b) WITH CHECK OPTION表示对视图进行增、删、改 时要满足子查询中的条件表达式。 c) 若省略列名,则列名由子查询决定,但在下列情况下必须明确指定所有列名: ·子查询的目标列含无别名的计算列  ·子查询的目标列含同名列 d) 视图的列名或者全部省略,或者全部指定。 2. 定义视图
  • 137. CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept='IS' DBMS执行CREATE VIEW语句时只把视图定义存入数据字典,并不执行其中的SELECT语句。只有对视图进行查询时,才按视图的定义从基本表中将数据查出。 例2 建立IS系学生的视图,并要求进行修改和插入操作时仍须保证该视图只有IS系的学生。 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage, Sdept FROM Student WHERE Sdept='IS' WITH CHECK OPTION 以后用命令对视图进行插入、修改和删除操作时,DBMS会自动检查Sdept=‘IS’这个条件。 (演示)例1 建立IS系学生的视图。
  • 138. 视图也可以定义在多个基本表上: 例3 建立IS系选修了1号课程的学生视图 CREATE VIEW IS_S1(Sno, Sname, Grade) AS SELECT S.Sno, Sname, Grade FROM Student S, SC WHERE Sdept='IS' AND S.Sno=SC.Sno AND SC.Cno='1' 视图可以定义在视图上,或定义在基本表和视图上: 例4 建立IS系选修了1号课程且成绩在90分以上的学生视图 CREATE VIEW IS_S2 AS SELECT Sno, Sname, Grade FROM IS_S1 WHERE Grade>=90 1)从单个基本表导出 2)每个属性列都对应于基本表的一个列 3)保留了基本表的码 IS_Student就是行列子集视图。行列子集视图:是满足下列条件的视图
  • 139. 例5 定义一个反映学生出生年份的视图。 CREATE VIEW BT_S(Sno, Sname, Sbirth) AS SELECT Sno, Sname, 2005-Sage FROM Student 分组视图:子查询中含集函数和GROUP BY子句。 例6 将学生的学号及他的平均成绩定义为一个视图。 CREAT VIEW S_G(Sno, Gavg) AS SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno 带表达式的视图:视图的列可以是不存在的虚拟列。
  • 140. 例7 将Student表中所有女生记录定义为一个视图。 CREATE VIEW F_Student(stdnum,name,sex,age,dept) AS SELECT * FROM Student WHERE Ssex='女' 问题:视图建立后,视图的列名和Student表的列名就构成了一一对应关系,如果以后修改了Student的结构,这个对应关系就不成立了,视图也就不能工作了。 办法: a) 建立视图时明确指明属性列名,而不用SELECT * CREATE VIEW F_Student(stdnum,name,sex,age,dept) AS SELECT Sno, Sname, Ssex, Sage, Sdept FROM Student WHERE Ssex='女' Student表增加列后视图仍能工作,但删除列仍不行。 b) 修改基本表后删除原视图后重建。最保险的方法。由“SELECT *”建立的视图:
  • 141. DROP VIEW <视图名> 视图删除后,由它导出的视图也将失效,应将他们删除。 例8 删除视图IS_S1。 DROP VIEW IS_S1 执行此语句后,IS_S1视图的定义将从数据字典中删除。 由IS_S1视图导出的IS_S2视图的定义仍在数据字典中,但已无法使用了,应同时删除。(2) 删除视图: 当视图失效时要删除它:
  • 142. 第3章 关系数据库标准语言SQL1. 视图概述2. 定义视图3. 查询视图3.5 视图4. 更新视图5. 视图的用途
  • 143. 3. 查询视图可以象基本表一样查询视图。视图查询的执行步骤: 1) 有效性检查:检查查询涉及的表和视图是否存在 2) 视图消解:把视图定义中的子查询和视图查询结合起来,转换成对基本表的查询 3) 查询执行:执行经过修正的查询 例9 在IS系学生视图中找出年龄小于20岁的学生。 SELECT Sno, Sage FROM IS_Student WHERE Sage<20 执行时,将此查询与IS_Student视图定义中的子查询: SELECT Sno, Sname, Sage FROM Student WHERE Sdept='IS' 结合起来,转换成对基本表Student的查询: SELECT Sno, Sage FROM Student WHERE Sdept='IS' AND Sage<20
  • 144. 例10 查询IS系选修了1号课程的学生。 SELECT S.Sno, Sname FROM IS_Student S, SC WHERE S.Sno=SC.Sno AND SC.Cno='1' 有时不能将视图查询转换为基本表查询, 例如,在S_G视图中查询平均分>=90的学号和成绩: SELECT * FROM S_G WHERE Gavg>=90 S_G的定义为: CREAT VIEW S_G(Sno, Gavg) AS SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno 结合后得: SELECT Sno, AVG(Grade) FROM SC WHERE AVG(Grade)>=90 GROUP BY Sno WHERE中不能直接用集函数!正确的查询是: 视图可以和其他基本表一起使用,实现连接或嵌套查询: SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>=90有些DBMS不能执行上述查询(SQL Server 可以)。 应尽量避免视图中的特殊属性出现在查询条件中。
  • 145. 第3章 关系数据库标准语言SQL1. 视图概述2. 定义视图3. 查询视图3.5 视图4. 更新视图5. 视图的用途
  • 146. 4. 更新视图 更新视图: 插入、删除、修改。 对视图的更新最终要转换为对基本表的更新。 为防止用户通过视图操作不属于视图的基本表数据,可在定义视图时加上WITH CHECK OPTION,视图更新时DBMS会检查视图定义中的条件,不满足条件则拒绝执行。 例11 将视图IS_Student中学生95002的姓名改为“刘辰”。 UPDATE IS_Student SET Sname='刘辰' WHERE Sno='95002' 执行时转换成对基本表的更新: UPDATE Student SET Sname='刘辰' WHERE Sno='95002' AND Sdept='IS'
  • 147. INSERT INTO IS_Student VALUES('95029', '赵新', 20) 执行时转换为对基本表的更新: INSERT INTO Student(Sno,Sname,Sage,Sdept) VALUES('95029', '赵新', 20, 'IS') 系统自动将系名‘IS’放入VALUES子句中。 (SQL Server不行:必须把视图定义条件涉及的列作为视图的列,否则无法对视图进行插入!) 例13 删除视图IS_Student中学号为95029的记录。 DELETE FROM IS_Student WHERE Sno='95029' 执行时转换为对基本表的更新: DELETE FROM Student WHERE Sno='95029' AND Sdept='IS'例12 向视图IS_Student中插入一个新的学生记录,其中学号为95029,姓名为赵新,年龄为20岁。
  • 148. 若不能唯一且有意义地转换成对基本表的更新,则视图不允许更新。 具体规定为: (1) 由多个基本表导出的视图不允许更新。 (2) 含表达式或常数字段的视图不允许执行INSERT和UPDATE,但允许执行DELETE。 (3) 含集函数字段的视图不允许更新。 (4) 定义中含GROUP BY的视图不允许更新。 (5) 定义中含有DISTINCT的视图不允许更新。 (6) 若视图定义中有嵌套查询,且内层查询FROM子句中的表也是导出视图的基本表,则此视图不允许更新。 例如: 将成绩>平均成绩的元组定义成视图GOOD_SC: CREATE VIEW GOOD_SC AS SELECT Sno, Cno, Grade FROM SC WHERE Grade > (SELECT AVG(Grade) FROM SC)   SQLServer无此限制。 (7) 在不允许更新的视图上定义的视图也不允许更新。 视图更新的限制:
  • 149. 第3章 关系数据库标准语言SQL1. 视图概述2. 定义视图3. 查询视图3.5 视图4. 更新视图5. 视图的用途
  • 150. 合理使用视图有下列好处: (1) 能够简化用户的操作 隐藏复杂的多表查询过程,通过视图使用所关心的数据,不必了解视图数据来源的具体细节。 (2) 能以不同方式看待同一数据(定义不同的视图) 非常实用,因为同一个数据库往往有多种不同的用户。 (3)对重构数据库提供了一定程度的逻辑独立性 数据库重构:关系分解、增加关系、增加字段等。 逻辑数据独立性:重构时只需修改视图定义,不必修改程序(例)。 视图只能在一定程度上提供逻辑数据独立性:由于视图更新是有条件的,因此重构时数据更新语句可能仍要修改。 (4)视图能够对机密数据提供安全保护 对不同的用户定义不同的视图,使机密数据不出现在不应看到这些数据的用户视图上,达到保护机密数据的目的5. 视图的用途
  • 151. 第3章 关系数据库标准语言SQL3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL 3.8 小结
  • 152. 第3章 关系数据库标准语言SQL1. 数据控制概述2. 授权3. 回收权限3.6 数据控制
  • 153. 1. 数据控制概述数据 控制 (数据 保护)安全性并发控制恢复完整性控制指数据的正确性和一致性SQL:定义主码(实体~)、外码(参照~)、Unique列、Check约束等(用户定义的~)。SQL:通过定义事务和封锁,第5章控制多个用户对数据库的并发操作,保证操作的正确执行和数据库的一致性。SQL:第5章当发生故障时,将数据库恢复到一致状态的功能。SQL:授予和回收权限,本节~防止数据泄露和破坏,通过权限~
  • 154. 第3章 关系数据库标准语言SQL1. 数据控制概述2. 授权3. 回收权限3.6 数据控制
  • 155. 2. 授权SQL用GRANT语句向用户授予操作权限,格式为: GRANT <权限>[,<权限>]... [ON <对象名>] TO <用户>[,<用户>]... [WITH GRANT OPTION] 含义:将对象的操作权限授予用户。 说明:1)不同对象有不同的操作权限:对象对象类型操作权限列TABLESELECT, INSERT, UPDATE, DELETE, ALL PRIVIEGES视图TABLE同上基本表TABLESELECT, INSERT, UPDATE, ALTER, INDEX, DELETE, ALL PRIVIEGES数据库DATABASECREATE TABLE (属DBO, 可授予普通用户,拥有后可建表)对象的创建者(属主)拥有对象的所有权限
  • 156. 3)WITH GRANT OPTION:获得某种权限的用户可以把权限再授予别的用户。 例1 把Student表的SELECT权限授给用户U1。 GRANT SELECT ON Student TO U1 例2 把Student表的全部权限授予用户U2和U3。 GRANT ALL PRIVILEGES ON Student TO U2, U3 例3 把表SC的查询权限授予所有用户。 GRANT SELECT ON SC TO PUBLIC 例4 把查询Student表和修改学生学号的权限授给用户U4。 要授予表Student的SELECT权限和列Sno的UPDATE权限。 GRANT UPDATE(Sno), SELECT ON Student TO U42)接受权限的用户:具体用户、PUBLIC(全体用户)。
  • 157. GRANT INSERT ON SC TO U5 WITH GRANT OPTION U5可以将此权限授予U6: GRANT INSERT ON SC TO U6 WITH GRANT OPTION U6还可以将此权限授予U7: GRANT INSERT ON SC TO U7 U7不能再传播此权限。 例6 DBA把在数据库S_C中建立表的权限授予用户U8。 USE S_C GRANT CREATE TABLE TO U8例5 把表SC的INSERT权限授予U5用户,并允许传播。
  • 158. 第3章 关系数据库标准语言SQL1. 数据控制概述2. 授权3. 回收权限3.6 数据控制
  • 159. 可以用REVOKE收回授权,格式为: REVOKE <权限>[,<权限>]... [ON <对象名>] FROM <用户>[,<用户>] ... 例7 把用户U4修改学生学号的权限收回。 REVOKE UPDATE(Sno) ON Student FROM U4 例8 收回所有用户对表SC的查询权限。 REVOKE SELECT ON SC FROM PUBLIC 例9 把用户U5对SC表的INSERT权限收回。 REVOKE INSERT ON SC FROM U5 在例5中,U5U6U7,此REVOKE语句同时收回U5、U6、U7对SC表的INSERT权限。 对象的创建者拥有对象的所有权限,可授予、回收。3. 回收权限
  • 160. 第3章 关系数据库标准语言SQL3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL (不讲)
  • 161. 第3章 关系数据库标准语言SQL1. 嵌入式SQL概述2. SQL语句与主语言之间的通信3. 嵌入式SQL编程举例3.7 嵌入式SQL
  • 162. 嵌入式 SQLSQL存在的问题没有流程控制结构(if、while等),不能直接用来编程序。解决办法1. 扩充SQL: 如T-SQL2. 将SQL嵌入高级语言(如C) : 称嵌入式SQL, 称(宿)主语言形式:EXEC SQL ;嵌入式 SQL 语句说明性语句:如变量定义可执行语句数据定义数据控制数据操纵1. 嵌入式SQL概述分类
  • 163. 第3章 关系数据库标准语言SQL1. 嵌入式SQL概述2. SQL语句与主语言之间的通信3. 嵌入式SQL编程举例3.7 嵌入式SQL
  • 164. 2. SQL语句与主语言之间的通信嵌入式SQL语句应用程序主语言语句负责操纵数据库负责程序流程控制通信用SQL通信区实现2. 主语言SQL语句:参数1. SQL语句主语言:执行状态用主变量实现3. SQL语句主语言:查询结果用主变量和游标实现
  • 165. SQLCA内含许多变量,其中一个为: SQLCODE:返回SQL语句的执行状态。 SQLCODE=0:表示执行成功。 一条SQL语句执行后,可立即测试SQLCODE: … EXEC SQL ; if (SQLCODE = = 0) printf(“成功”); else printf(“失败”); …(1) SQL通信区(SQLCA)
  • 166. 主变量:SQL语句中使用的主语言变量。 输入主变量:由主语言赋值,由SQL引用。用来实现主语言向SQL传递参数。 输出主变量:由SQL赋值,由主语言引用。用来返回SQL语句的执行状态和结果。 主变量定义格式: EXEC SQL BEGIN DECLARE SECTION; <主变量定义区> EXEC SQL END DECLARE SECTION; 在SQL语句中,使用主变量时要加冒号(:)作为前缀, 在主语言中,使用主变量时不加冒号。(2) 主变量
  • 167. (3) 游标 用途:用来逐个访问元组集合的每个元组,解决SQL只能整个处理元组集合的问题。 定义游标:在实验课中已讲过 有关游标的WHERE条件: WHERE CURRENT OF <游标名>
  • 168. 第3章 关系数据库标准语言SQL1. 嵌入式SQL概述2. SQL语句与主语言之间的通信3. 嵌入式SQL编程举例3.7 嵌入式SQL
  • 169. 3. 嵌入式SQL编程举例
  • 170. 将选择结果σF(r× s×…),按<列名列表1>的值分组(值相等的元组分为一组)SELECT [ALL|DISTINCT] <目标列> [,<目标列>]...  FROM <表名或视图名> [,<表名或视图名>] ...  [WHERE <条件表达式>] [GROUP BY <列名列表1> [HAVING <条件表达式>] ] [ORDER BY <列名列表2>  [ASC|DESC]] …r.Ai…s.Aj…………………ai…bj…………………ai…bj………………σF(r× s×…)返回是r.U∪s.U∪…的子集一个组
  • 171. SELECT [ALL|DISTINCT] <目标列> [,<目标列>]...  FROM <表名或视图名> [,<表名或视图名>] ...  [WHERE <条件表达式>] [GROUP BY <列名列表1> [HAVING <条件表达式>] ] [ORDER BY <列名列表2>  [ASC|DESC]] …r.Ai…s.Aj…………………ai…bj…………………ai…bj………………σF(r× s×…)返回将(聚)集函数作用到每个组的某些列,得到一条“记录”作用集函数(Count、Sum、Avg等)…r.Ai…s.Aj………………… ai… bj……………………未作用集函数的列Sum集函数中的列Group By中的列
  • 172. SELECT [ALL|DISTINCT] <目标列> [,<目标列>]...  FROM <表名或视图名> [,<表名或视图名>] ...  [WHERE <条件表达式>] [GROUP BY <列名列表1> [HAVING <条件表达式>] ] [ORDER BY <列名列表2>  [ASC|DESC]] 返回将这条“记录”投影到目标列,产生结果表的一条记录…r.Ai…s.Aj………………… ai… bj……………………Sum未作用集函数的列Group By中的列X1X2……………ai…………结果表要求目标列(即X1,X2,…)要么在Group By中,要么在集函数中,否则结果表将违反1NF。集函数中的列
  • 173. 重构时将Student(Sno, Sname, Ssex, Sage, Sdept)分解为: SX(Sno, Sname, Sage) 和 SY(Sno, Ssex, Sdept) 这时原表Student是SX和SY的自然连接。 通过建立视图Student, 可以保证应用程序不必修改: CREATE VIEW Student(Sno, Sname, Ssex, Sage, Sdept) AS SELECT SX.Sno, Sname, Ssex, Sage, Sdept FROM SX, SY WHERE SX.Sno=SY.Sno返回