数据库系统原理与设计.ppt

第三章 关系数据库语言 SQL “数据库系统原理及应用 课程 第三章 关系数据库语言 SQL 主 讲 人：李俊山 教授 联系方式： 743419 办公地点：训练楼 516房间 第三章 关系数据库语言 SQL SQL语句的分类： 数据定义语句： 用于定义数据库的逻辑结构 ， 包括 定义基本表 、 定义视图和定义索引 。 数据查询语句： 用于按不同查询条件实现对数据库 中数据的检索查询 。 数据操纵语句： 用于更改和操作表中的数据 ， 包括 数据插入 、 数据修改 、 数据删除及数据查询 。 数据控制语句： 用于实现用户授权、基本表和视图 授权、事务控制、完整性和安全性控制等。 第三章 关系数据库语言 SQL 3.1.1 表的定义 、 修改与撤消 1. 表的定义 表的定义语句格式为： CREATE TABLE （ ， ， ， ， ） ； 第三章 关系数据库语言 SQL 其中： 典型的 （ a） CHAR(m)：长度为 m的字符 (串 )型数据 ， 长度 不够时用空白字符补充 ， 不超过 240。 （ b） VARCHAR(m)：长度小于等于 m的字符 (串 )型 数据 ， 长度不够时不补充其它字符 。 （ c） INT / INTEGER：长整型数据 。 （ d） DATE：日期型数据 ， 形式为 YYYY-MM-DD， 分别表示年 、 月 、 日 。 第三章 关系数据库语言 SQL 其中： 典型的 （ a） NULL：指出该列可以为空值 。 （ b） NOT NULL：指出该列不能为空值 。 每一个 表中至少应有一个列的可选项为 NOT NULL。 （ c） PRIMARY KEY：指出该列名为表的主键 。 （ d） DEFAULT：给所在的列设置一个缺省值 。 （ e） CHECK：指出该列的值只能取 CHECK约束 条件范围的值 。 第三章 关系数据库语言 SQL 例 3.2 创建图 1.30所示的教学管理数据库系统中的学生 关系表 S， 可用如下的表定义语句定义： CREATE TABLE S （ S# CHAR(9) PRIMARY KEY， SNAME CHAR(10) NOT NULL， SSEX CHAR(2) CHECK(SSEX IN (男 ， 女 )， SBIRTHIN DATE NOT NULL， PLACEOFB CHAR(16)， SCODE CHAR(5) NOT NULL， CLASS CHAR(5) NOT NULL） ； 第三章 关系数据库语言 SQL 其中： (a) 表的主键约束 格式为： PRIMARY KEY() 当该表的主键由 2个或 2个以上属性组成时 ， 表的主 键必须由表的完整性约束给出 。 第三章 关系数据库语言 SQL 例 3.3 创建教学管理数据库系统中的学习关系 SC， 可 用如下的表定义语句定义： CREATE TABLE SC （ S# CHAR(9), C# CHAR(7)， GRADE INT DEFAULT(0), PRIMARY KEY(S#， C#)） ； 第三章 关系数据库语言 SQL (b) 表的外键约束 格式为： FOREIGN KEY () REFERENCE （ ） 本子句定义了一个列名为 ” 的外键 ， 它与 表 ” 中的 ” 相对应 ， 且 ” 在表 ” 中是主键 。 第三章 关系数据库语言 SQL 例 3.4 教学管理数据库系统中的学习关系 SC， 可重新 用如下的表定义语句定义如下： CREATE TABLE SC （ S# CHAR(9), C# CHAR(7)， GRADE INT DEFAULT(0), PRIMARY KEY(S#， C#)， FOREIGN KEY (C#) REFERENCES C (C#)； 第三章 关系数据库语言 SQL (c) 表检验 CHECK约束 格式为： CHECK () 表检验约束 CHECK子句的含义和格式与列检验约束相 同 ， 所不同的是 ， 表检验约束 CHECK子句是一个独立的 子句而不是子句中的一部分 。 表检验约束 CHECK子句中 的 不仅可以是一个条件表达式 ， 而且 还可以是一个包含 SELECT语句的 SQL语句 。 第三章 关系数据库语言 SQL 例 3.5 教学管理数据库系统中的学习关系 SC， 还可用 如下的表定义语句定义如下： CREATE TABLE SC （ S# CHAR(9), C# CHAR(7)， GRADE INT DEFAULT(0), PRIMARY KEY(S#， C#)， FOREIGN KEY (C#) REFERENCES C（ C#） , CHECK(GRADE BETWEEN 0 AND 100)； 第三章 关系数据库语言 SQL 2. 表的修改 (1) 改变表名 修改表名的语句格式为： RENAME TO ； 第三章 关系数据库语言 SQL 2. 表的修改 (2) 增加列 在表的最后一列后面增加新的一列 ， 但不允许将一 个列插入到原表的中间 。 增加列语句的格式为： ALTER TABLE ADD ； 第三章 关系数据库语言 SQL 例 3.7 给专业表 SS增加一个新属性 NOUSE_COLUMN， 设 其数据类型为 NUMERIC(8， 1)。 语句应为： ALTER TABLE SS ADD NOUSE_COLUMN NUMERIC(8， 1)； 第三章 关系数据库语言 SQL 2. 表的修改 (3) 删除 列 删除表中不再需要的列 ， 语句格式为： ALTER TABLE DROP CASCADE | RESTRICT； 其中 ， 可选项 CASCADE | RESTRICT” 是删除方式 。 当 选择 CASCADE时 ， 表示在删除名为 ” 的表中的列 ” 时 ， 所有引用到该列的视图或有关约束 也一起被删除；当选择 RESTRICT时 ， 表示当没有视图或有 关约束引用列 ” 时 ， 该列才能被删除 ， 否 则拒绝该删除操作 。 第三章 关系数据库语言 SQL 例 3.8 删除专业表 SS中增加的属性 NOUSE_COLUMN的两 种删除 语句分别为： ALTER TABLE SS DROP NOUSE_COLUMN CASCADE； ALTER TABLE SS DROP NOUSE_COLUMN RESTRICT； 第三章 关系数据库语言 SQL 2. 表的修改 (4) 修改 列的定义 修改属性列的定义语句只用于修改列的类型和长度 ， 列 的名称不能改变 。 当表中已有数据时 ， 不能缩短列的长度 ， 但可以增加列的长度 。 修改列定义语句格式为： ALTER TABLE MODIFY ； 第三章 关系数据库语言 SQL 例 3.8 将专业表 SS中的专业名称 SSNAME(30)修改为 SSNAME(40)， 即长度增加 10。 ALTER TABLE SS MODIFY SSNAME VARCHAR(40)； 第三章 关系数据库语言 SQL 3. 表的撤销 表的撤消就是将不再需要的表或定义有错误的表删除掉 。 当一个表被撤消时 ， 该表中的数据也一同被撤消 （ 删除 ） 。 撤消表的语句格式为： DROP TABLE CASCADE | RESTRICT； 其中 , CASCADE表示在撤消表 ” 时 ， 所有引用这 个表的视图或有关约束也一起被撤消； RESTRICT表示在没 有视图或有关约束引用该表的属性列时 ， 表 ” 才 能被撤消 ， 否则拒绝该撤消操作 。 第三章 关系数据库语言 SQL 3.1.2 数据的插入 、 修改 、 删除 、 提交与撤消 1. 数据的插入 向表中插入一行数据的单元组值插入方式的 INSERT语 句格式为： INSERT INTO （ ） VALUES（ ） ； 第三章 关系数据库语言 SQL 其中： (1)如果选择可选项 （ ） ” ， 表示在插入 一个新元组时 ， 只向由 指出的列中插入数据 ， 其 他没有列出的列不插入数据 (为空值 )， 且 ” 中 必须至少包括表中那些列约束为 NOT NULL” 的列和主键 列 。 如果不选择该可选项 ， 则默认表中所有的列都要插入 数据 。 (2) 指出要插入列的具体值 。 如果选择了可选项 ， 则 中的属性列值必须与 中的 属性列名一一对应；如果没有选择可选项 ， 则 中的属性列值必须与 中的属性列名一一对 应 。 第三章 关系数据库语言 SQL 例 3.10 给学习关系 SC中插入王丽丽同学 （ 学号为 200401003） 学习计算机网络课 （ 课程号为 C403001） 的成绩 （ 89分 ） 。 INSERT INTO SC （ S#， C#， GRADE） VALUES（ 200401003 ， C403001 ， 89） ； 或： INSERT INTO SC VALUES（ 200401003 ， C403001 ， 89） ； 第三章 关系数据库语言 SQL 3.1.2 数据的插入 、 修改 、 删除 、 提交与撤消 2. 数据的修改 语句格式为： UPDATE SET ， , , WHERE 其中 ， ” 指出将列 ” 的 值修改成 。 可选项 WHERE ” 中的 指定修改有关列的数据时所应满足的条件 。 当不选 择该选项时 ， 表示修改表中全部元组中相应列的数据 。 第三章 关系数据库语言 SQL 例 3.13 将学生关系 S中的学生名字 “ 王丽丽 ” 改为 “ 王黎丽 ” 。 UPDATE S SET SNAME 王黎丽 WHERE S# 200401003 ； 第三章 关系数据库语言 SQL 例 3.14 将所有女同学的专业改为 S0404。 UPDATE S SET SCODE S0404 WHERE SSEX 女 ； 第三章 关系数据库语言 SQL 3.1.2 数据的插入 、 修改 、 删除 、 提交与撤消 3. 数据的删除 语句格式为： DELETE FROM WHERE 其中 ， 可选项 WHERE ” 中的 指定所删元 组应满足的条件 。 当不选该可选项时 ， 表示删除表中全部 数据 。 第三章 关系数据库语言 SQL 例 3.15 在学生关系 S中删除学号为 200403001的学生信 息 。 DELETE FROM S WHERE S# = 200403001 ； 例 3.16 删除专业关系中的全部信息 。 DELETE FROM SS； 第三章 关系数据库语言 SQL 3.1.2 数据的插入 、 修改 、 删除 、 提交与撤消 4. 数据的提交 工作区概念： 数据提交的概念： 就是把用户对数据库中数据的更新结 果永久地保存到数据库中 。 显式提交 : 隐式提交： 第三章 关系数据库语言 SQL 3.1.2 数据的插入 、 修改 、 删除 、 提交与撤消 5. 数据的撤销 命令格式 : 第三章 关系数据库语言 SQL 3.2.1 简单查询 1. SELECT查询语句 SQL查询语句的基本格式为： SELECT FROM WHERE 第三章 关系数据库语言 SQL 3.2.1 简单查询 2. 无条件查询 例 3.17 查询教学管理数据库中全部学生的基本信息 。 SELECT * FROM S； 例 3.19 查询课程关系 C中的记录数 ， 也即开课的总门数 。 SELECT COUNT(*) FROM C； 第三章 关系数据库语言 SQL SQL语言中常用的聚合函数主要有 ： （ 1） COUNT(*) 计算元组的个数； （ 2） COUNT(列名 ) 计算某一列中数据的个数； （ 3） COUNT DISTINCT(列名 ) 计算某一列中不同值的个数； （ 4） SUM(列名 ) 计算某一数据列中值的总和； （ 5） AVG(列名 ) 计算某一数据列中值的平均值； （ 6） MIN(列名 ) 求 (字符、日期、属性列 )的最小值； （ 7） MAX(列名 ) 求 (字符、日期、属性列 )的最大值； 第三章 关系数据库语言 SQL 例 3.20 计算所有学生所学课程的最高分数 、 最低分 数和平均分数 。 SELECT MAX(GRADE)， MIN(GRADE)， AVG(GRADE) FROM SC； 第三章 关系数据库语言 SQL 3.2.1 简单查询 3. 单条件查询 例 3.21 查询所有学习计算机网络课 (课程号为 C403001) 的学生的学号和成绩 。 SELECT S#， GRADE FROM SC WHERE C# = C403001 ； 第三章 关系数据库语言 SQL 表 3.1 条件表达式中的关系比较符 运算符 含义 = ! = 或 = = 18 AND AGE = 25； 第三章 关系数据库语言 SQL 上例中： 这种把某数值型列的值限定在某个数值区 间的比较查询条件 （ 、 、 =） 可以用比较运算 符 BETWEEN AND” 来表示 ， 格式为： BETWEEN AND 例 3.23的查询可以改写为： SELECT * FROM S1 WHERE AGE BETWEEN 18 AND 25； 第三章 关系数据库语言 SQL 5. 分组查询 在 SQL语言中 ， 把元组按某个或某些列上相同的值分组 ， 然后再对各组进行相应操作的查询方式称为分组查询 。 分组查询的语句格式为： SELECT FROM WHERE GROUP BY HAVING ； 第三章 关系数据库语言 SQL （ 1） GROUP BY子句 GROUP BY子句用来将列的值分成若干组 ， 从而控制查询 的结果排序 。 例 3.24 计算各个同学的平均分数 。 SELECT #S， AVG(GRADE) FROM SC GROUP BY #S； 第三章 关系数据库语言 SQL 例 3.25 计算每个专业的男 、 女生分别有多少人数 。 SELECT SCODE， SSEX， COUNT(*) FROM S GROUP BY SCODE， SSEX； 第三章 关系数据库语言 SQL （ 2） HAVING子句 在数据查询中 ， 有时只希望选择满足一定条件的分组 。 一般是利用 GROUP BY子句进行分组 ， 利用 HAVING子句判断 分组应满足的条件 。 例 3.26 查询学生总数超过 300人的专业及其具体的总人 数 。 SELECT SCODE， COUNT(*) FROM S GROUP BY SCODE HAVING COUNT(*)300； 第三章 关系数据库语言 SQL 6. 结果排序 通常 ， SELECT的查询结果是按元组在数据库中的存储顺 序给出的 。 但有时用户希望按照某种约定的顺序给出查询 结果 ， ORDER BY子句可以实现查询结果的排序显示功能 ， 其语句格式为： SELECT FROM WHERE ORDER BY ASC/DESC , ， ASC/DESC； 第三章 关系数据库语言 SQL 例 3.27 按学号递增的顺序显示学生的基本信息 。 SELECT * FROM S ORDER BY S# ASC； 例 3.28 按学号递增 、 课程成绩递减的顺序显示学生的课 程成绩 。 SELECT S#， C#， GRADE FROM SC ORDER BY S# ASC， GRADE DESC； 第三章 关系数据库语言 SQL 7. 字符串的匹配 在 WHERE子句的条件表达式中 ， 实现两个字符串的部分 字符的相等比较 。 比较运算符的格式为： LIKE 字符串 1通配符 字符串 2 其中： （ 1） 下划线 _：在字符串比较时 ， 如果有一个字符可以 任意 ， 则在该字符位置上用下划线表示 。 （ 2） 百分号 %：在字符串比较时 ， 如果有一个长度大于 等于 0子字符串可以任意 ， 则在该子字符串位置上用百分 号表示 。 第三章 关系数据库语言 SQL 例 3.29 查询学生关系 S中姓李的学生的学号和姓名 。 SELECT S#， SNAME FROM S WHERE SNAME LIKE 李 % ； 第三章 关系数据库语言 SQL 8. 比较完整的 SELECT查询语句 SELECT FROM WHERE GROUP BY HAVING ORDER BY ASC/DESC , ， ASC/DESC； 第三章 关系数据库语言 SQL 3.2.2 SQL语言中的常用函数及其使用方式 1. 日期型函数 日期是 SQL语言中的标准数据类型 （ DATE） 。 函 数 功 能 sysdate last_day next_day(d， w_day) add_months(d， n) months_between(d1,d2) to_char(d， new_d) 取当前日期和时间 取本月最后一天 计算日期 d之后第一周指定星期几 (由 w_day指 定 )的日期 取在日期 d的基础上后推 n个月的日期 计算日期 d1和 d2之间相差的月数 将日期 d的缺省格式 DD-MON-YYYY”表示成指 定的新格式 new_d 表 3.3 常用的日期函数 第三章 关系数据库语言 SQL 例 3.30 查询出生日期在 1982年 1月 1日到 1982年 12月 31日 之间的所有学生的学号 、 姓名和出生日期 ， 并按日期的递 增顺序排列 。 SELECT S#， SNAME， SBIRTHIN FROM S WHERE SBIRTHIN BETWEEN 1-JUN-1982 AND 31-DEC-1982 ORDER BY SBIRTHIN； 第三章 关系数据库语言 SQL 表 3.4 常用的日期显示格式 日期显示格式 （ 模式 ） 示例 缺省格式 MM/DD/YY YYYY/MM/DD DY DD MON YY Day Mon DD 23-FEB-99 02/23/99 1999/02/23 WED 12 JAN 88 Wednesday Jan 12 第三章 关系数据库语言 SQL 例 3.31 在上例中 ， 出生日期的缺省显示方式不符合我国 的日期表示习惯 ， 可用日期转换函数函数 to_char将其转 换成 年 /月 /日 的表示形式 。 SELECT S#， SNAME， to_char(SBIRTHIN， YYYY” /” MM” /” DD ) FROM S WHERE SBIRTHIN BETWEEN 1-JUN-1982 AND 31-DEC-1982 ORDER BY SBIRTHIN； 第三章 关系数据库语言 SQL 2. 字符串函数 表 3.5 常用的字符串函数 函 数 功 能 length(str) initcap(str) lower(str) upper(str) replace(str， str1， str2) substr(str， m， n) itrim(str) ascii(str) 求字符串的长度 将字符串 str中的第一个字符转换成大写字符 将字符串 str中的大写字母转换成小写字母 将字符串 str中的小写字母转换成大写字符 将字符串 str中的所有 str1换成 str2 从字符串 str的第 m个字符开始取出 n个字符 截掉字符串 str尾部的空格 取字符串 str的 ASCII码值 第三章 关系数据库语言 SQL 3.2.2 SQL语言中的常用函数及其使用方式 3. 算术函数 表 3.6 常用的算术函数 函数 功能 abs(n) ceil(n) floor(n) mod(m,n) power(m,n) round(n,m) sign(n) sqrt(n) 求 n的绝对值 求大于等于数值 n的最小整数 求小于等于数值 n的最大整数 取余数 。 若 n=0， 则取 m 求 m的 n次方 四舍五入 ， 保留 m位 n=0， 返回 0； nn， 返回 1； n0， 返回 -1 求 n的平方 第三章 关系数据库语言 SQL 4. 空值显示与空值函数 例 3.33 查询学习关系 SC中分数 GRADE为空值的课程号 。 SELECT C# FROM SC WHERE GRADE IS NULL； 第三章 关系数据库语言 SQL 3.2.3 SQL高级查询技术 1. 多表联接查询 （ 二元查询 /多元查询 ） SELECT S.S#， SNAME FROM S， SC WHERE S.S# = SC.S# AND C# =C401001； 例 3.37 查询所有学习了数据结构课 （ 课程号为 C401001） 的学生的学号和姓名 。 第三章 关系数据库语言 SQL 例 3.38 查询选修了 信息安全技术 课程的学生的学号 与姓名 。 SELECT S.S#， SNAME FROM S， SC， C WHERE S.S# = SC.S# AND SC.C# = C.C# AND CNAME = 信息安全技术 ； 第三章 关系数据库语言 SQL 3.2.3 SQL高级查询技术 2. 外联接查询 学 号 姓名 性别 出生年月 籍贯 专业代码 班级 200401001 张华 男 14-dec-82 北京 S0401 200401 200401002 李建平 男 20-aug-82 上海 S0401 200401 200401003 王丽丽 女 02-feb-83 上海 S0401 200401 200402001 杨秋红 女 09-may-83 西安 S0402 200402 200402002 吴志伟 男 30-jun-82 南京 S0402 200402 200402003 李涛 男 25-jun-83 西安 S0402 200402 200403001 赵晓艳 女 11-mar-82 长沙 S0403 200403 专业代码 专业名称 S0401 计算机科学 S0402 指挥自动化 S0403 网络工程 S0404 信息研究 学生关系 S： 专 业 关 系 SS 注意： 虽然存在信息研究专业， 但学生关系的当前值中没 有信息研究专业的学生 。 第三章 关系数据库语言 SQL 对于查询： SELECT SS.SCODE#， SS.SSNAME， S#， SNAME FROM SS， S WHERE SS.SCODE# = S.SCODE ORDER BY SCODE#； 有查询结果： SCODE SSNAME S# SNAME S0401 计算机科学 200401001 张华 S0401 计算机科学 200401002 李建平 S0401 计算机科学 200401003 王丽丽 S0402 指挥自动化 200402001 杨秋红 S0402 指挥自动化 200402002 吴志伟 S0402 指挥自动化 200402003 李涛 S0403 网络工程 200403001 赵晓艳 由于信息研究 专业没有学生， 所以查询结果 丢失了信息研 究的专业信息， 为此， SQL引 入了外联接查 询。 第三章 关系数据库语言 SQL 外联接查询包括：左外联接和右外联接 。 (1)左外联接 (Left-outer Join) R S = (R S)(R1 (? ?) 其中： R1=R-R（ R S） (2)右外联接 (Right-outer Join) R S = (R S)(? ?) S1) 其中： S1=S-S （ R S） 第三章 关系数据库语言 SQL 外联接查询包括：左外联接和右外联接 。 设 R.A = S.B” 是 WHERE子句中的等值条件表达式 ， 则： （ 1） 左外联接的表示形式为 R.A(+) = S.B， 其含义是 假设在 等值条件右边的 关系 S中有一个 全空 的元组 ， 且该元组可与 等值条件左边的 关系 R中任何不满足等值联 接条件的元组匹配构成一个新的元组； （ 2） 右外联接的表示形式为 R.A = S.B(+)， 其含义是 假设在 等值条件左边的 关系 R中有一个 全空 的元组 ， 且该元组可与 等值条件右边的 关系 S中任何不满足等值联 接条件的元组匹配构成一个新的元组 。 第三章 关系数据库语言 SQL 这时 ， 上述的 查询语句： SELECT SS.SCODE#， SS.SSNAME， S#， SNAME FROM SS， S WHERE SS.SCODE# = S.SCODE ORDER BY SCODE#； 就可以改写成： SELECT SS.SCODE#， SS.SSNAME， S#， SNAME FROM SS， S WHERE SS.SCODE#(+)= S.SCODE# ORDER BY SCODE#； 查询出的结果就包含了： S0404 信息研究 null null 尽管信息研究专 业没有学生，但 专业仍然存在！ 第三章 关系数据库语言 SQL 3. 嵌套查询 即：在 SQL语言中 ， 如果在一个 SELECT语句的 WHERE 子句中嵌入了另一个 SELECT语句 ， 则称为嵌套查询 。 WHERE子句中的 SELECT语句称为子查询 。 第三章 关系数据库语言 SQL 例 3.39 查询张华同学 （ 学号为 200401001） 的那个班的女 同学的基本信息 。 SELECT * FROM S WHERE CLASS=(SELECT CLASS FROM S WHERE S# = 200401001 ) AND SSEX = 女 ； 第三章 关系数据库语言 SQL 4. 谓词演算查询 表 3.7 常用的谓词操作符 操作符 说 明 between A and B not between A and B like in not in any some all exists not exists 某列的数值区间是 A， B 某列的数值区间在 A， B外 两个字符串的部分字符相等 ， 其余可以任意 某列的某个值属于集合成员中的一个成员 某列的值不属于集合成员中的任何一个成员 某列的值满足一个条件即可 满足集合中的某些值 某列的值满足子查询中所有值的记录 总存在一个值满足条件 不存在任何值满足条件 第三章 关系数据库语言 SQL 4. 谓词演算查询 （ 1） IN 和 NOT IN谓词 条件表达式格式为： IN NOT IN 前者的含义： 如果集合 1中的数据是集合 2中的成 员 ， 那么逻辑值为 true， 否则为 false。 后者的含义： 如果集合 1中的数据不是集合 2中的 成员 ， 那么逻辑值为 true， 否则为 false。 第三章 关系数据库语言 SQL 例 3.40 查询所有 学习了数据结构课 （ 课程号为 C401001） 的学生 的学号和姓名 。 SELECT S.S#， SNAME FROM S WHERE S# IN (SELECT S# FROM SC WHERE C# = C401001 )； 第三章 关系数据库语言 SQL 例 3.41 利用嵌套查询实现例 3.38， 也即查询选修了 信 息安全技术 课程的学生的学号与姓名 。 SELECT S.S#， SNAME FROM S WHERE S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE CNAME= 信息安全技术 ); 思路：选修了 信息安全技术 课的课程号；选修了该课程 （ 号 ） 的学生的学 号 第三章 关系数据库语言 SQL 4. 谓词演算查询 （ 2） ANY和 SOME谓词 条件表达式格式为： ANY SOME 其中 ， 是算术比较运算符 、 、 =、 =、 !=。 其含义 是：比较运算符 左边的数据与右边集合中的某个或某些元 素 是否 满足 运算 ， 满足则为真 ， 不满足则为假 。 在 SQL语言中 ， ANY和 SOME具有相同的含义 ， 早期的版本 用的是 ANY， 新的版本都改为 SOME， 有些商用数据库版本 的 SQL语言中 ， 同时保存了 ANY和 SOME两个谓词 。 第三章 关系数据库语言 SQL 例 3.42 查询所有学习了数据结构课 （ 课程号为 C401001） 的学生的学号和姓名 。 SELECT S.S#， SNAME FROM S WHERE S# = ANY (SELECT S# FROM SC WHERE C# = C401001 )； 其中 ， = ANY” 的作用相当于 IN。 第三章 关系数据库语言 SQL 4. 谓词演算查询 （ 3） ALL谓词 条件表达式格式为： ALL 含义是：比较运算符 左边的数据与右边集合中的 所有 元素是否满足 运算 ， 满足其逻辑值为 true， 不满足其逻 辑值为 false。 。 第三章 关系数据库语言 SQL 例 3.43 查询考试成绩大于 网络工程专业 （ 专业代码为 S0403） 所有学生的课程成绩 的学生的基本信息 。 SELECT S.S#， SNAME， SSEX， SBIRTHIN， PLACEOFB， SCODE， CLASS FROM S， SC WHERE S.S# = SC.S# AND GRADE ALL (SELECT GRADE FROM S， SC WHERE S.S# = SC.S# AND SCODE = S0403 ； 第三章 关系数据库语言 SQL 4. 谓词演算查询 （ 4） EXISTS和 NOT EXISTS谓词 条件表达式格式为： EXISTS（ ） NOT EXISTS（ ） 前者的含义是： 当集合中至少存在一个元素 （ 非空 ） 时 ， 其逻辑值为 true， 否则为 false； 后者的含义是： 当集合中不存在任何元素 （ 为空 ） 时 ， 其逻辑值为 true， 否则为 false。 通常用于测试子查询是否有返回结果 。 第三章 关系数据库语言 SQL 例 3.44 查询所有学习了数据结构课 （ 课程号为 C401001） 的学生的学号和姓名 。 SELECT S.S#， SNAME FROM S WHERE EXISTS (SELECT * FROM SC WHERE SC.S# = S.S# AND C# = C401001 )； 直观的意义为：查询的是那些 ， 在学习关系中存在所学 课程为数据结构的学生的学号和姓名 。 第三章 关系数据库语言 SQL 5.查询结果的并 、 交 、 差操作 （ 1） 并操作 即 ， 指将两个或多个 SELECT语句的查询结果组合在一起 作为总的查询结果输出 。 语句格式为： SELECT FROM WHERE UNION ALL SELECT 语句 ； 其中 ， 如果不选择可选项 ALL， 则在输出总查询结果时 重复的行会自动被取掉 。 如果选择可选项 ALL， 则表示将 全部行合并输出 ， 也即不取掉重复行 。 第三章 关系数据库语言 SQL 例 3.46 合并学生关系和专业关系中的专业代码 。 SELECT SCODE# FROM S UNION SELECT SCODE# FROM SS； 第三章 关系数据库语言 SQL 5.查询结果的并 、 交 、 差操作 （ 2） 交操作 查询结果的交操作是指将同时属于两个或多个 SELECT语 句的查询结果作为总的查询结果输出 。 查询结果交操作的 基本数据单位是行 。 其语句格式为： SELECT FROM WHERE INTERSECT SELECT 语句 ； 第三章 关系数据库语言 SQL 例 3.47 查询有成绩的学生的学号 。 SELECT S# FROM S INTERSECT SELECT S# FROM SC WHERE GRADE IS NOT NULL; 第三章 关系数据库语言 SQL 5.查询结果的并 、 交 、 差操作 （ 3） 差操作 是指从第一个 SELECT语句的查询结果中去掉属于第二个 SELECT语句查询结果的行作为总的查询结果输出 。 其语句 格式为： SELECT FROM WHERE MINUS SELECT 语句 ； 第三章 关系数据库语言 SQL 例 3.48 查询没有成绩的学生的学号 。 SELECT S# FROM S MINUS SELECT S# FROM SC WHERE GRADE IS NOT NULL; 第三章 关系数据库语言 SQL 课堂综合练习： 1、 查询全部学生的学号 、 姓名 、 所学课程号和成绩 。 2、 查询没有选修任何课程的学生的学号和姓名 。 3、 找出选修了全部课程的学生的学号和姓名 。 第三章 关系数据库语言 SQL 堂综合练习答案： 1、 查询全部学生的学号 、 姓名 、 所学课程号和成 绩 。 SELECT S.S#， SNAME， C#， GRADE FROM S， SC WHERE C# IN (SELECT C# FROM SC WHERE S.S# = SC.S#)； 第三章 关系数据库语言 SQL 堂综合练习答案： 2、 查询没有选修任何课程的学生的学号和姓名 。 SELECT S#， SNAME FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.S# = S.S#)； 第三章 关系数据库语言 SQL 堂综合练习答案： 3、 找出选修了全部课程的学生的学号和姓名 （ 没有一 们可他不选 ） 。 SELECT S#， SNAME FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.S# = S.S# AND SC.C# = C.C#)； 第三章 关系数据库语言 SQL 第三章第三次课 第三章 关系数据库语言 SQL 3.3.1 视图的概念 视图是由数据库中满足一定条件约束的数据组成的 虚表 ， 它可以由某个表中满足一定条件的行组成 ， 也可以由某个 （ 或某些 ） 表的某些列组成 ， 还可以由 若干个表经过一定的运算而形成 。 第三章 关系数据库语言 SQL 图 3.1 视图与基本表的映射关系 视图 3 视图 4 视图 1 视图 2 表 1 表 2 表 3 表 4 第三章 关系数据库语言 SQL 3.3.2 视图的定义 视图的定义语句格式为： CREATE VIEW () AS WITH READ ONLY | WITH CHECK OPTION； 其中 ， 当选择 WITH READ ONLY可选项时 ， 表示该视图被 定义成一个只读 ， 不能进行插入 、 删除和修改操作 。 当选 择 WITH CHECK OPTION可选项时 ， 用户必须保证每当向 该视图中插入或更新数据时 ， 所插入或更新的数据能够从 该视图查询出来 。 第三章 关系数据库语言 SQL 例 3.49 教学安排视图： TA(C#， CNAME， CLASSH， TNAME， TRSECTION） 的创建语句为： CREATE VIEW TA AS SELECT C#， CNAME， CLASSH， TNAME， TRSECTION FROM C， TEACH， T WHERE C.C#=TEACH.C# AND TEACH.T#=T.T#； 其中 ， 只有视图名 TA， 没有 选项说明视图 TA 的列名表与 SELECT语句中的列名表相同 。 第三章 关系数据库语言 SQL 例 3.49（ 续 ） 平均成绩视图 A_GRADE(S#， SNAME， AVG_GRADE) 的创建语句为： CREATE VIEW A_GRADE（ S#， SNAME， AVG_GRADE） AS SELECT S.S#， SNAME， AVG(GRADE) FROM S， SC WHERE S.S# = SC.S# AND GRADE IS NOT NULL； GROUP BY S.S# ORDER BY S.S#； 第三章 关系数据库语言 SQL 3.3.3 在视图上进行查询操作 例 3.50 利用视图 A_GRADE查询学生所学课程的平均成绩 。 SELECT * FROM A_GRADE； 如果用查询语句实现查询学生所学课程的平均成绩 ， 其 查询语句应为： SELECT S.S#， SNAME， AVG(GRADE) FROM S， SC WHERE S.S# = SC.S# AND GRADE IS NOT NULL； GROUP BY S.S# ORDER BY S.S#； 第三章 关系数据库语言 SQL 3.3.4 在视图上进行数据更新 在视图上进行数据更新操作 (INSERT、 DELETE、 UPDATE) 时应注意以下几点： （ 1） 如果某视图是建立在多个表的基础上 ， 那么不允 许对该视图进行更新操作 。 （ 2） 如果在某视图的定义中使用了 GROUP BY子句和聚 合函数 ， 那么不允许对该视图进行更新操作 。 （ 3） 如果视图是建立在单个表的基础上 ， 且视图的属 性列个数与表的属性列个数相同 ， 或少于表的属性列个数 时 ， 且所少的列是表中可为空值 (NULL)的列 ， 则可在该视 图上进行更新操作 。 第三章 关系数据库语言 SQL 3.3.6 用户视图对数据库设计和系统性能带来的影响 （ 1） 用户视图给数据库应用系统的设计提供了极大 的优越性 用户视图的建立 ， 会使在应用程序中用到并反复出现的 含有复杂关系联接和投影的查询语句被简单的视图查询语 句所代替 。 这样不仅简化了用户接口 ， 使应用程序中的 SQL语句变得简单明了 ， 清晰可读；而且可以使应用程序 员把编写应用程序的主要精力集中在对数据的处理 、 分析 和用户界面的实现上 ， 方便应用程序的设计 。 第三章 关系数据库语言 SQL 3.3.6 用户视图对数据库设计和系统性能带来的影响 （ 2） 实现了数据库的逻辑数据独立性 由于不同的用户具有不同用户视图 ， 这样就使一个用户视 图的变化不会影响另一些用户视图 。 当数据库的逻辑结构 （ 概 念模型 ） 或存储结构 （ 存储模型 ） 发生变化时 ， 并且这些变化 与某一或某些用户的视图无关时 ， 就不必改变该用户的应用程 序；当这些变化与某一或某些用户的视图有关时 ， 可通过改变 基本表到用户视图之间的映射关系 ， 也即通过重新定义用户视 图而使用户视图保持不变或稍有变化 ， 从而不必修改应用程序 或少量修改应用程序 。 这样就实现了数据库的逻辑数据独立性 。 第三章 关系数据库语言 SQL 3.3.6 用户视图对数据库设计和系统性能带来的影响 （ 3） 可起到了对数据库中数据进行安全保护的作用 由于不同用户具有不同的用户视图 ， 这样就使得在各个 用户视图中只出现他自己关心的那部分数据 ， 其它数据对 他来说是不能使用的 。 而且系统给用户使用的是视图而不 是在数据库中存储有数据的表 。 这样就起到了对数据库中 数据进行安全保护的作用 。 第三章 关系数据库语言 SQL 3.4.1 数据插入操作 （ 数据的导入 ） 把从某个或某些表中查询出的数据插入到另一个表中去 。 语句格式为： INSERT INTO （ ） ） ； 其中 ， 是一个合法的 SELECT查询语句 。 第三章 关系数据库语言 SQL 例 3.51 设在教学管理数据库系统中 ， 要建立某些临时 表来辅助有关管理过程 。 若其中有一个临时表用于暂 存选修了 信息安全技术 课程的学生的学号 、 姓名 、 专业名称和班级 ， 表的其格式为： S_C（ S#， SNAME， SSNAME， CLASS） 第三章 关系数据库语言 SQL 例 3.51（ 续 ） 则将从有关表中查询出的有关数据组成的记 录插入该表的插入语句为： 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 S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE CNAME = 信息安全技术 )； 第三章 关系数据库语言 SQL 例 3.52 设在教学管理数据库系统中 ， 有一个临时表用于 暂存平均成绩大于等于 80分的女同学的学号和平均成绩 。 临时表的格式为： S_AVG(S#,AVG_GRADE) 则将从有关表中查询出的有关数据组成的记录插入该表的 插入语句为： 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； 第三章 关系数据库语言 SQL 3.4.2 数据更新操作 用子查询表示条件的数据更新操作 。 语句格式为： UPDATE SET ， , , WHERE ； 其中 ， WHERE条件中包含有 SELECT查询子句 。 第三章 关系数据库语言 SQL 例 3.53 为了加强对高水平尖子人才的重点培养 ， 学校拟 将计算机应用技术专业 (专业代码为： S0401)中 ， 各门课 程均在 85分以上的学生单独编为 200400班 ， 所以需要修改 学生基本情况数据库中的 班级 属性 。 其实现语句为： UPDATE S SET CLASS = 200400 WHERE SCODE = S0401 AND S# IN (SELECT S# FROM SC WHERE GRADE 85)； 第三章 关系数据库语言 SQL 例 3.54 由于试题难度原因 ， 需要将 计算机网络 课 的成绩提高 5%。 其实现语句为： UPDATE SC SET GRADE = GRADE * 1.05 WHERE C# IN (SELECT C# FROM C WHERE CNAME = 计算机网络 ） ； 第三章 关系数据库语言 SQL 3.5.1 嵌入式 SQL的概念 交互式 SQL ： 是非过程语言 ， 大多数语句的执行都是独 立的 ， 与上下文无关的；无法满足绝大多数应用所需的过 程性要求 。 嵌入式 SQL： 引入过程性结构和把 SQL语言嵌入到 C、 PASCAL等高级语言的机制 。 宿主语言： 在 SQL嵌入形式中用到高级语言 。 应用程序： 含有嵌入 SQL语句的高级语言应用程序称为 宿主应用程序 ， 简称为宿主程序或应用程序 。 第三章 关系数据库语言 SQL 3.5.2 嵌入式 SQL遇到的问题 （ 1） 当 SQL语言语句嵌入到 C语言后 ， 宿主程序中就 包含了不属于高级语言的一部分成份 。 换句话说 ， 宿 主程序不再是普通的高级语言源程序了 ， 显然也不能 用通常的编译 、 连接方式对其进行处理 。 如何实现宿 主程序的编译是要解决的问题之一 。 第三章 关系数据库语言 SQL 3.5.2 嵌入式 SQL遇到的问题 （ 2） SQL语言是非过程性语言 ， 大多数 SQL语句的执 行是独立的 ， 与上下文无关的 。 而 C语言是过程性语言 ， 程序的执行机制是顺序 、 分支和循环 ， 当前语句的执 行与前面语句的执行结果有关 ， 各语句的执行是上下 文有关的 。 那么 ， 当把 SQL语句嵌入到 C语言后 ， 如何 来确定 SQL语句的执行顺序是要解决的问题之一 。 第三章 关系数据库语言 SQL 3.5.2 嵌入式 SQL遇到的问题 （ 3） SQL语言语句中的属性名是表示关系的属性及 其当前值的集合型 参变量 。 而 C语言语句中的变量 既有单值变量 ， 又有数组变量 。 如何建立两种语言变 量之间联系是要解决的问题之一 。 （ 4） SQL语句是面向集合的 ， 一次查询的结果往往 有许多行 。 而 C语言的数据处理方式原则上是一次处理 一个记录的顺序方式 。 如何使它们在对数据的保持和 处理上相一致是要解决的问题之一 。 第三章 关系数据库语言 SQL 3.5.3 嵌入式 SQL的实现 两种实现途径： （ 1） 建立全新的编译程序 ， 使之可直接编译 SQL语句； （ 2） 采用预处理方式 ， 把宿主程序中的 SQL语句转换成 可为高级语言调用的函数形式： 后一种途径的实现需要： 提供预编译器； 提供实现所有 SQL语句的函数库 。 第三章 关系数据库语言 SQL 3.5.3 嵌入式 SQL的实现 （ 续 ） 基于预处理方式的数据库应用程序从编写到执行的过程 为： （ 1） 编写包含有嵌入式 SQL语句的宿主语言应用程序； （ 2） 利用预编译器对应用程序进行预编译 ， 产生含有 高级语言函数调用的 普通 高级语言源程序； （ 3） 利用高级语言编译器编译由 (2)生成的高级语言源 程序 ， 生成相应的目标文件； （ 4） 对生成的目标文件和所需的函数库进行连接 ， 生 成可执行文件； （ 5） 运行可执行文件形式的应用程序 。 第三章 关系数据库语言 SQL 3.5.4 SQL语句的嵌入形式 第三章 关系数据库语言 SQL 3.5.5 宿主程序的组成与结构 预编译单元： 在嵌入式 SQL中 ， 将宿主应用程序中的 一个源程序文件称为一个预编译单元 。 宿主程序的组成： 具有与 C语言一样的组成结构 ， 一 个宿主应用程序可以分成若干个源程序文件 ， 每个源程 序文件可以是一个独立的预编译单元 ， 可以将这些经过 独立预编译的程序再分别独立编译 ， 并生成各自的目标 文件 ， 最后将这些目标文件连接成一个完整的执行文件 。 第三章 关系数据库语言 SQL 3.5.5 宿主程序的组成与结构 预编译单元的组成结构： （ 1） 说明部分 （ DECLARE部分 ） （ 2） 通信区定义部分 （ 3） 系统连接部分 （ 4） 程序体 程序首部 程序体 第三章 关系数据库语言 SQL 1. 说明部分 （ DECLARE部分 ） （ 1） 主语言变量 、 宿主变量 、 宿主变量的使用规则： 主语言变量： 宿主变量： 在 SQL语句中使用的主语言的变量称为宿主变量 。 第三章 关系数据库语言 SQL 例 3.55 根据由宿主变量 S1给出的学生学号 ， 查询 、 学生的姓名 、 性别和出生年月 。 EXEC SQL SELECT SNAME， SSEX， SBIRTHIN FROM S INTO :SNAME1， :SSEX1， :SBIRTH WHERE S# = :S1； 由本例说明： 宿主变量是主语言和 SQL语句都可以 对其赋值和引用其值的变量。 第三章 关系数据库语言 SQL 宿主变量的使用规则： 宿主变量必须在 DECLARE部分定义 。 在 SQL语句中使用宿主变量时 ， 必须在仅接宿主 变量的前面写一个冒号 ： ；在纯 C语言语句中使用 宿主变量时 ， 与非宿主变量一样不加任何标记 。 宿主变量不能是 SQL语言中的保留字 。 一个宿主变量在一条 SQL语句中只能使用一次 。 宿主变量可以带指示变量 。 第三章 关系数据库语言 SQL 1. 说明部分 （ DECLARE部分 ） （ 续 ） （ 2） 指示变量 是一个用来指示所指宿主变量的值或条件的整型变 量 。 宿主变量利用指示变量赋空值或检测是否是空值 。 第三章 关系数据库语言 SQL 指示变量的使用规则为： 指示变量必须在 DECLARE部分定义 。 在 SQL语句中使用指示变量时 ， 必须在仅接指示 变量的前面写一个冒号 ： ；在纯 C语言语句 （ 也即 ， 主语言 ） 中使用指示变量时 ， 不用加任何标记 。 指示变量不能是 SQL语言中的保留字 。 指示变量必须定义成 2字节的整型变量 ， 如 short， int类型 。 在 SQL语句中 ， 指示变量必须紧跟在所指宿主变 量之后 ， 中间不能有逗号或空格 。 第三章 关系数据库语言 SQL 例 3.56 宿主变量带有指示变量的例