数据库系统原理与技术第4章.ppt

数据库原理与技术（第二版）,程学先 程传慧 曾 玲 陈永辉 郑秋华 杨晓艳,第四章 关系数据库实现及使用,关 系 代 数,SQL 语 言,本 章 要 点,退 出,第四章 关系数据库实现及应用,4.1 关系代数4.2 关系演算4.3 SQL语言概貌4.4 SQL数据定义功能4.5 SQL数据查询语句4.6 SQL查询语句的加强4.7 视图4.8 SQL数据更新语句 4.9 SQL数据控制功能 4.10 嵌入式SQL4.11 查询优化,关系代数是一种抽象的查询语言，是关系数据操纵语言的一种传统表达方式，它是用对关系的运算来表达查询。按照表达查询的方式可分为两大类。第一类是用对关系的运算来表达查询的方式，称为关系代数。第二类是用谓词来表达查询要求的方式称为关系演算。,关 系 代 数,一、传统的集合运算 包括四种运算：并（）、交（）、差（）、广义笛卡尔积（）。1并（Union）设关系R和关系S具有相同的目n，且相应的属性取自同一个域。则关系R和关系S的并记为RS，其结果仍为n目关系，由属于R或属于S的元组组成。,关 系 代 数,如R和S的元组分别用两个圆表示,则RS的集合如图4.1所示虚影部分元组。,关 系 代 数,R S,关 系 代 数,关 系 代 数,2、交（Intersection）设关系R和关系S具有相同的目n，且相应的属性取自同一个域。关系R和关系S的交记为RS，结果仍为n 目关系，由既属于R又属于S的元组组成。如R和S的元组分别用两个圆表示，则RS集合运算结果可用图4.2示意，则两圆相交部分元组表示R与S的交。,关 系 代 数,关 系 代 数,图4.2 集合RS,关 系 代 数,关 系 代 数,3、差(Difference)设关系R和关系S具有相同的目n，且相应的属性取自同一个域。定义关系R和关系S的差记为R-S，其结果仍为n目关系，由属于R而不属于S的元组组成。,关 系 代 数,关 系 代 数,如R和S的元组分别用两个圆表示，则 R-S的集合如图4.3所示。比较图4.1.2和图4.1.3，显然R(RS)(R-S)或R-SR-(RS)。,图4.3 集合R-S,关 系 代 数,4笛卡尔积（Extended Cartesian Product）两个分别为n，m的关系R和S的广义笛卡尔积RS是一个（nm）元组的集合。元组的前n个分量是R的一个元组，后m个分量是S的一个元组.若R有K1个元组，S有K2个元组，则RS有K1K2个元组。记为RS,关 系 代 数,二、专门的关系运算 包括四种运算即选择（）、投影（）、连接（）和除法（），是关系数据库数据维护、查询、统计等操作的基础。1选择（Selection）设有关系R，在关系R中求取满足给定条件F的元组组成新的关系的运算称为选择。记作:F（R）,关 系 代 数,在F（R）中，f 是一个条件表达式，其值为“真”或“假”。F（R）是从关系R中选取使条件表达式 F 为真的元组。这是以行为处理单位进行的运算。条件表达式 F 是由常量、变量及算术比较符，和逻辑运算符（，）等构成。,关 系 代 数,关 系 代 数,关系代数式为：单价5(营业库)，结果如下表所示：,2投影（Projection）设有关系R(A,B,C,D)，在关系R中求指定的若干个属性列组成新的关系的运算称作投影，记作A（R）。其中A为欲选取的属性的列表。可以用：列名1，列名2形式表示，也可以用1，2，形式表示。这是以列作为处理单位进行的运算，示意图如图4.4所示的阴影部分，aA,cA,dA。,关 系 代 数,关 系 代 数,关系R(a,c,d)在A上的投影，即A(R)，A包括：a,c,d等域。,关 系 代 数,3，4（营业库）,结果：,注意：在投影后如出现重复元组，应只保留一个,关 系 代 数,【例4.7】求“营业库”中所有单价大于5元的商品品名和单价，求关系运算式。关系运算式：品名，单价（单价5(营业库)表4.9 从表4.6再作投影,关 系 代 数,3连接（Join）从两个分别为n,m元的关系R和S的广义笛卡尔积中选取满足给定条件F的元组组成新的关系称为R和S的连接，记作 R S（F=AB）。其中A和B分别为R和S上度数相等且可比的属性列，是算术比较符(，)。,AB,例如：关系R和S如表4.10,4.11所式，求R S，连接结果如图所示。,A=C,关 系 代 数,4除（Division）给定关系R（x,y）与S（y）其中x，y为属性集（也可为单属性），R中的y 和S中的y可以有不同的属性名，但必须出自相同的域集。在求解RS时，对R按x的值分组，然后检查每一组，如某一组中的y包含S中全部的y，则取该组中的x的值作为商关系中的一个元组，否则不取。如此求出RS的商。,关 系 代 数,关 系 代 数,第四章 关系数据库实现及应用,4.1 关系代数4.2 关系演算4.3 SQL语言概貌4.4 SQL数据定义功能4.5 SQL数据查询语句4.6 SQL查询语句的加强4.7 视图4.8 SQL数据更新语句 4.9 SQL数据控制功能 4.10 嵌入式SQL4.11 查询优化,关系演算以数理逻辑中的谓词演算为基础。谓词（Predicate）指明一个条件，通过对它的求解可得出下列之一的值：“真”、“假”、“未知”。常见的谓词例如：比较谓词：，；包含谓词：IN；存在谓词：EXISTS,关 系 演 算,一、元组关系演算 用表达式t|Q(t)来表示，其中t为元组变量，Q(t)是由关系名、元组变量、常量及运算符组成的公式。t|Q(t)表示使Q(t)为“真”的元组的集合。,关 系 演 算,关系代数的运算均可用关系演算表达式来表示（反之亦然）表示如下：1）并：RSt|R(t)S(t)2）交：RSt|R(t)S(t)3）差：RSt|R(t)S(t)4）投影：i1,i2,ik(R)=tk|(u)(R(u)t1=ui1tk=uik),关 系 演 算,5）选择：其中F是由F用ti代替运算对象i 得到的等价公式。6）连接=t(n+m)|(u(n)(v(m)(R(u)s(v)t1=u1t2=u2tn=untn+1=v1tn+m=vm),关 系 演 算,关 系 演 算,【例4.10】根据表4.2营业库显示所有品名及其单价。C0l=t(2)|(u)(营业库(u)t1=u品名t2=u单价,二、域关系演算 用表达式x1,x2,xk|(x1,x2,xk)来表示，其中x1,x2,xk是域变量，是由关系、域变量、常量及运算符组成的式子。x1,x2,xk|(x1,x2,xk)表示所有使(x1,x2,xk)为“真”的那些x1,x2,xk组成的元组的集合。每一个关系代数表达式有一个等价的域演算表达式，反之亦然。,关 系 演 算,关 系 演 算,【例4.11】如果有关系：学生（学号，姓名，年龄，性别），求用域关系演算式表示年龄大于20的学生的学号，姓名，年龄。解：|性别（学生年龄20）,第四章 关系数据库实现及应用,4.1 关系代数4.2 关系演算4.3 SQL语言概貌4.4 SQL数据定义功能4.5 SQL数据查询语句4.6 SQL查询语句的加强4.7 视图4.8 SQL数据更新语句 4.9 SQL数据控制功能 4.10 嵌入式SQL4.11 查询优化,SQL（Structured Query Language）:指结构化查询语言，但成为关系数据库标准语言之后它已成为一种综合的、通用的、功能极强的关系数据库语言。,SQL 语 言 概 貌,SQL 语 言 概 貌,（1）一体化的特点。（2）语言简洁，易学易用。（3）高度非过程化。（4）极强适应性。（5）SQL语言支持关系数据库三级模式的结构。,第四章 关系数据库实现及应用,4.1 关系代数4.2 关系演算4.3 SQL语言概貌4.4 SQL数据定义功能4.5 SQL数据查询语句4.6 SQL查询语句的加强4.7 视图4.8 SQL数据更新语句 4.9 SQL数据控制功能 4.10 嵌入式SQL4.11 查询优化,一、基本表的定义和修改 SQL数据定义功能包括:定义基本表定义视图定义索引,SQL数据定义功能,1、定义基本表的语句：CREATE TABLE 表名（列名1 类型）NOT NULL,列名2 类型NOT NULL）其他参数“其他参数”指与物理存储有关的参数，随具体系统不同而不同；“”表示可选项；“|”表示多选一项，对于其两边内容，用户只能且必需选一项。,SQL数据定义功能,SQL数据定义功能,一般的SQL支持的数据类型有：INTEGER 全字长（31bits精度）的二进制整数。SMALLINT 半字长（精度为15bits）的二进制整数。DECIMAL(p,q)压缩十进制数，共p位，小数点后有q位（15Pq0，q0时可省略）。FLOAT 双字长的浮点数。CHAR(n)长度为n的定长字符串。VARCHAR(n)变长字符串，最大长度为n。,SQL数据定义功能,SQL支持空值（NULL）的概念.空值是不知道的或不确定的值，除了候选关键字外，任何列可以有空值。如不允许空值，则应指定该列NOT NULL,2、修改基本表定义语句 ALTER TABLE 表名 ADD 列名 类型例如 ALTER TABLE 学生 ADD 班级 CHAR(4)在修改基本表定义的列的语句中不允许NOT NULL。ALTER TABLE 表名 RENAME COLUMN 原列名 TO 新列名用于更改某列名。,SQL数据定义功能,ALTER TABLE 表名ALTER COLUMN 列名 类型(宽度),小数位修改字段类型、字段或数值型字段宽度和数值型字段小数点后位数。ALTER TABLE 表名 DROP COLUMN 列名从表中删去一列。,SQL数据定义功能,3、删除基本表的语句 DROP TABLE 表 例如：DROP TABLE 学生 将把一个基本表的定义连同其中记录、索引及它导出的所有视图全部删除或不可引用。,SQL数据定义功能,二、索引的建立和删除1、索引的建立 CAEATE UNIQUE INDEX 索引名 ON 基本表名（列名次序,列名次序）其他参数 索引可以建立在一列或几列上，其中次序可取：ASC（升序）或DESC（降序），缺省值为升序。UNIQUE表示每一个索引值只对应唯一的数据记录。用中括号表示是可选项。例如 CREATE INDEX 学生索引 ON 学生（班级 DESC，学生号）。,SQL数据定义功能,2.删除索引的语句DROP INDEX 索引名例如 DROP INDEX 学生索引,SQL数据定义功能,第四章 关系数据库实现及应用,4.1 关系代数4.2 关系演算4.3 SQL语言概貌4.4 SQL数据定义功能4.5 SQL数据查询语句4.6 SQL查询语句的加强4.7 视图4.8 SQL数据更新语句 4.9 SQL数据控制功能 4.10 嵌入式SQL4.11 查询优化,一、语句格式SQL语言数据库查询语句一般格式是：SELECT*|FROM 基本表|视图WHERE 条件表达式1 GROUP BY 列名1 HAVLNG 条件表达式2ORDER BY 列名2 ASC/DESC注意：这是一条SQL语句,SQL数据查询语句,SQL数据查询语句,“目标列”的格式可以是以下格式：1）列名1，列名2其中列名1，列名2为FROM子句中所指基本表或视图中的列名；如果FROM子句中指定多个表，且列名有相同的时，则列名应写为“表名.列名”的形式。,SQL数据查询语句,2）上述每一项可以是表达式，“目标列”允许使用SQL提供的集合函数形成表达式。常用的集合函数如下：COUNT(*)计算记录条数COUNT(列名)计算一列值的个数SUM(列名)计算某一列值的总和，该列必须为数值类型,AVG(列名)计算某一列值的平均值，该列必须为数值类型MAX(列名)计算某一列值的最大值，该列必须为数值类型MIN(列名)计算某一列值的最小值，该列必须为数值类型如无HAVING 子句，上述函数完成对全表统计，否则作分组统计。,SQL数据查询语句,SQL数据查询语句,3）DISTINCT 列名1,列名2表示在最终结果表中，属性相同的元组只保留一条。在书写时，允许使用通配符“”、“？”。“”表示任意一字符串。“？”表示任意一个字符。“基本表或视图”格式如下所示：表名1（或视图名1）别名1,表名2(或视图名2)别名2,SQL数据查询语句,如果由列名构成，将实现关系投影运算。如果FROM中指定的是一个表，WHERE的描述将实现选择运算。如果FROM中指定的是多个表，WHERE的描述中将包括连接运算。,SQL数据查询语句,二、对单一表查询语句示例下列示例中使用的基本表的结构假设为：学生（学生号，姓名，性别，出生年份，班级，寝室号）课程（课程号，课程名，先行课程名）成绩（学生号，课程号，分数）其中出生年份和分数为整型，其他为字符类型,SQL数据查询语句,例4.12 求所有学生表数据。SELECT FROM 学生例4.13 求所有学生姓名的列表。SELECT 姓名 FROM 学生例4.14 如当前系统设定的年份为YEAR1求显示所有学生姓名、年龄。SELECT 姓名，YEAR1-出生年份 FROM学生,SQL数据查询语句,例4.15 求显示学生人数和学生平均年龄。SELECT COUNT(),AVG(YEAR1出生年份)FROM 学生例4.16 求所有学生班级的列表。SELECT DISTINCT 班级 FROM 学生因为每个学生班级均有许多学生，为在列表中不出现重复班级名，故使用了“DISTINCT”。,SQL数据查询语句,例4.17 显示一个学生表,要求先按班级，在班级相同时按学生号排序。SELECT FROM 学生 ORDER BY 班级，学生号例4.18 求全体男生信息。SELECT FROM 学生 WHERE 性别男例4.19 求1980年以前出生的女生姓名、性别、出生年份。SELECT 姓名，性别，出生年份 FROM 学生 WHERE 性别女 AND 出生年份1980,SQL数据查询语句,例4.20 假设没有安排寝室学生的寝室号为空值，求没有安排寝室学生姓名。SELECT 姓名 FROM 学生 WHERE 寝室号 IS NULL 涉及空值的谓词的一般形式是：列名 IS NOT NULL。在条件表达式中还经常使用谓词IN、BETWEEN、LIKE。,SQL数据查询语句,例4.21 求200101班和200103班的学生表。实现语句1：SELECT FROM 学生 WHERE 班级 IN(200101,200103)实现语句2：SELECT FROM 学生 WHERE 班级=200101 OR 班级=200103谓词IN实际是一系列逻辑关系词“OR”的缩写。也可用谓词NOT IN表示不在某集合之中。,SQL数据查询语句,例4.22 求出生年份在1982至1985年间的学生姓名和出生年份。SELECT 姓名,出生年份 FROM 学生 WHERE 出生年份 BETWEEN 1982 AND 1985,SQL数据查询语句,例4.23 找出所有2001级学生。SELECT FROM 学生 WHERE 班级 LIKE 2001%使用谓词LIKE时，列名必须为各种字符串类型。可采用一些通配字符：（下横线）表示任意一单个字符。（百分号）表示任意长度字符串。,SQL数据查询语句,例4.23 求选修课程超过了三门的学生号SELECT 学生号 FROM 成绩 GROUP BY 学生号 HAVING COUNT()3以上查询实现了关系运算中投影与选择两种运算。,SQL数据查询语句,三、对两个以上表的连接查询例4.25 求所有学生信息，包括他所学的课程的课程号和成绩，并按课程号和成绩排名。SELECT 学生.,成绩.FROM 学生,成绩WHERE 学生.学生号=成绩.学生号 ORDER BY 课程号,分数 DESC,SQL数据查询语句,例4.26 求分数为优良（80分数及以上）的所有成绩组成的表，要求显示内容包括姓名、课程名和分数。SELECT 姓名,课程名,分数 FROM 学生,成绩,课程 WHERE 学生.学号成绩.学号 AND 成绩.课程号课程.课程号 AND 分数=80,SQL数据查询语句_自连接,例4.27 求每门课程的先行课的先行课。SELECT 表1.课程名，表2.先行课程名 FROM 课程 表1，课程 表2 WHERE 表1.课程号表2.课程号,别名,例4.28 求既选修了课程号为C1，又选修了课程号为C2这两门课的学生的学生号。SELECT 表1.学生号 FROM 成绩 表1，成绩 表 2 WHERE表1.学生号表2.学生号 AND 表1.课号C1 AND 表2.课号C2,SQL数据查询语句_自连接,SQL数据查询语句_嵌套查询,嵌套查询亦称为子查询，它是指一个SELECTFROMWHERE 查询块可以嵌入到另一个查询块之中的查询。例4.29 求选修了课程号为C1的学生姓名。SELECT 姓名 FROM 学生 WHERE 学号 IN(SELECT 学号 FROM 成绩 WHERE 课程号C1),SQL数据查询语句,例4.30 求选修了课程名为C语言的学生姓名。SELECT 姓名 FROM 学生 WHERE 学号 IN(SELECT 学号 FROM 成绩 WHERE 课程号 IN(SELECT 课程号 FROM 课程 WHERE 课程名C 语言),SQL数据查询语句,例4.31 由表4.3所示关系，求表成绩1和成绩2的两个关系的交。SELECT FROM 成绩1 WHERE 学生号 IN(SELECT 学生号 FROM 成绩2 WHERE 成绩1.课程号=成绩2.课程号 AND 成绩1.分数=成绩2.分数),SQL数据查询语句,例4.32 由表4.3所示关系，求成绩1成绩2 两个关系的差。SELECT FROM 成绩1 WHERE 学生号 NOT IN(SELECT 学生号 FROM 成绩2 WHERE 成绩1.课程号=成绩2.课程号 AND 成绩1.分数=成绩2.分数）,SQL数据查询语句,两个SELECT-FROM-WHERE查询块如果目标列相同，可利用谓词UNION构成一个查询，实现关系并运算，其格式如下：SELECT 目标列1 FROM 表1 WHERE 条件表达式1 UNION SELECT 目标列2 FROM 表2 WHERE 条件表达式2,SQL数据查询语句,目标列1与目标列2名字不要求一样，但列数对应列类型和宽度必须一样，结果中的列名按第一个查询块中列名查询时将去掉重复元组。例4.33 求营业库1和营业库2两个关系的并。SELECT FROM 营业库1 UNION SELECT FROM 营业库2,SQL数据查询语句_关系除法,一般用于实现关系除法，需利用存在量词EXISTS和NOT EXISTS。这类查询称为相关子查询（Correlated Subquery）。相关子查询中查询条件依赖于外层查询中的某个值，因此不能只处理一次，而要内外交错反复求值。,应用EXISTS查询语句定义见下例。例4.35 求选修了课程号为C1的学生姓名SELECT 姓名 FROM 学生 WHERE EXISTS(SELECT FROM 成绩 WHERE 成绩.学号=学生.学号 AND 课程号=C1),SQL数据查询语句_关系除法,SQL数据查询语句_关系除法,在SQL中没有全称量词（For all），但是带全称量词的谓词都可转换成等价的带存在量词的谓词：例4.36 求选修了全部课程的学生姓名。SELECT 姓名FROM 学生 WHERE NOT EX1STS(SELECT FROM 课程 WHERE NOT EX1STS(SELECT FROM 成绩 WHERE 成绩.学号=学生.学号 AND 成绩.课程号=课程.课程号),SQL数据查询语句_关系除法,更一般的关系除法问题如下例。例4.37 求至少选修了学生号为S1的学生所选修的全部课程的学生的学生号。本问题含义是对成绩表按学生号分组，如每组课程号包含了学生号为S1，所选修的全部课程的课程号则取该组学生号列入结果表。,SQL数据查询语句,SQL没有蕴函(Implication)逻辑运算。但是蕴函逻辑运算可作如下变换，其意义是说如p包含在q中，则一定不存在：“包含在p内的元素”却“不在q之中”的情况。上例也就可如下表述：求这样一些学生学号，不存在这样的情况，即S1选修了的课程CY，他却没有选修。,SQL数据查询语句,如果用p表示谓词“学生s1选修课程cy”，用q表示谓词“学生Sx选修课程Cy”，则上述查询可表示为：SELECT DISTINCT 学生号 FROM 成绩 表1WHERE NOT EXISTS(SELECT*FROM 成绩 表2 WHERE表2.学生号S1 AND NOT EXISTS(SELECT*FROM 成绩 表3 WHERE 表3.学生号表1.学生号 AND 表3.课程号表2.课程号),许多数据库产品对SQL查询语句进一步扩展。以下结合SQL Server摘要介绍：1输出列更名运算SELECT DISTINCT AS,ASFROM“AS新列名”指定查询结果中列的重新定义的标题。,SQL查询语句的加强,2输出定向有许多应用要求根据查询语句产生一个新表并将查询结果送到新表中等，其方法是在语句中增加子句：INTO,SQL查询语句的加强,3外部连接实现外连接的方法是在FROM子句中增加有关连接的语句成分，格式为：OUTER JOIN 根据连接类别不同具体分为：,SQL查询语句的加强,左外连接，格式为：LEFT OUTER JOIN，意义为可供选作输出内容的记录除了满足连接条件的记录之外，还要加上那些左表（表名1）中不满足连接条件的记录。右外连接，格式为：RIGHT OUTER JOIN，意义为可供选作输出内容的记录除了满足连接条件的记录之外，还要加上那些右表（表名2）中不满足连接条件的记录。全外连接，格式为：OUTER OUTER JOIN，意义为可供选作输出内容的记录除了满足连接条件的记录之外，还要同时加上那些左表（表名1）中不满足连接条件的记录以及右表（表名2）中不满足连接条件的记录。,SQL查询语句的加强,例4.38 求根据表4.2生成一个新表：统计表，包括所有单价大于5元的记录，并要求增加二列：金额、说明，其中金额数量单价，说明要求10个字符宽，以空格填充。SELECT,数量单价 AS 金额,“”AS 说明 FROM 营业库 WHERE 单价5 INTO 统计表,SQL查询语句的加强,例4.39 求生成按课程成绩的分析统计表，包括每门课平均分、最高分、最低分，存放在表TMP中。SELECT 课程号,AVG(分数)AS 平均分,MAX(分数)AS 最高分,MIN(分数)AS最低分 FROM 成绩 GROUP BY 课程号 INTO TMP,SQL查询语句的加强,例4.40 对于表4.2营业库，求生成统计表，要求按商品代码分组，显示每笔记录，对每组记录显示之后求该组“数量”数据之和，并在其中品名栏内标注“小计”字样。,SQL查询语句的加强,例4.41 求生成按课程成绩的分析表，包括所有学生姓名，班级，课程号，分数。要求没有成绩的学生情况也能显示。SELECT 学生.姓名,学生.班级,成绩.课程号,成绩.分数 FROM 学生 LEFT OUTER JOIN 成绩,第四章 关系数据库实现及应用,4.1 关系代数4.2 关系演算4.3 SQL语言概貌4.4 SQL数据定义功能4.5 SQL数据查询语句4.6 SQL查询语句的加强4.7 视图4.8 SQL数据更新语句 4.9 SQL数据控制功能 4.10 嵌入式SQL4.11 查询优化,视图和表不同之处在于，它并没有真正地存储数据，它所存取的数据必须依附于所关联的数据表，它是一种虚的映射关系。SQL建立视图的语句格式：CREATE VIEW视图名(字段名,字段名 AS 子查询 WITH CHECK OPTION视图所用字段名可以与基本表中字段名不一致。,视 图,当字段名不一致或子查询中目标列列名是函数、一般表达式或子查询中目标列有相同列名时，在视图定义中必须指出视图的各个字段名，也可以不列出，默认与子查询结果相同。,视 图,在子查询中一般不能包括DISTINCT,INTO,ORDER等，不能涉及临时表。WITH CHECK OPTION选项表示在通过视图对基本表进行插入和更新操作时必须满足子查询中WHERE语句中规定的条件。,视 图,视 图,例4.42 将学生表、成绩表、课程表连接后取部分字段建立视图。CREATE VIEW 学生成绩 AS SELECT 姓名,课名,分数 FROM 学生,成绩,课程 WHERE 学生.学生号=成绩.学生号 AND 成绩.课程号=课程.课程号,视 图,例4.43 例4.39我们可改为视图方式。CREATE VIEW 成绩统计(课程号,平均分,最高分,最低分)AS SELECT 课程号,AVG(分数),MAX(分数),MIN(分数)FROM 成绩 GROUP BY 课程号再查看成绩统计表。使用如下语句：SELECT*FROM 成绩统计,删除视图语句格式为：DROP VIEW 视图名 一个基本表的删除，由它导出的视图将自动删除。当一个视图被删去后，由它导出的其他视图也将自动删除或变为不可引用。视图不仅可用于查询，还可借助视图实现对基本表的插入、修改和删除操作。,视 图,在关系优化过程中，我们将一个实际数据改为多个表存储，使用户面对的是复杂的数据结构，而视图可不改变数据存储结构同时又使用户面对的是简单的数据结构，视图将其中连接操作对用户隐藏起来，就使用户对数据使用大大简化，更容易操作。,视 图,在一些数据库系统中，利用视图可建立两个不同数据库系统的联系和通信。例如VFP中，可十分容易地建立ORACLE、ACCESS、SQL SERVER等系统中的表的视图，称之为“远程视图”。VFP的程序可对这些视图如同基本表一样作查询、录入、修改、删除等操作，并借之实现对相关数据库系统中表的操作，使得程序设计大大简化。,视 图,视 图,视图的作用：（1）视图对于数据库的重构提供了一定程度的逻辑独立性（2）简化用户观点，隐藏了表之间的连接（3）方便用户，使用户从不同角度看待数据（4）提供数据安全功能（5）在一些数据库系统中，利用视图可建立两个不同数据库系统的联系和通信,第四章 关系数据库实现及应用,4.1 关系代数4.2 关系演算4.3 SQL语言概貌4.4 SQL数据定义功能4.5 SQL数据查询语句4.6 SQL查询语句的加强4.7 视图4.8 SQL数据更新语句 4.9 SQL数据控制功能 4.10 嵌入式SQL4.11 查询优化,一、修改(UPDATE)语句格式：UPDATE 表名 SET字段=表达式,字段=表达式WHERE条件表达式例4.44 将营业库中所有单价高于5元的商品单价减少10%。UPDATE 营业库 SET 单价=单价0.9 WHERE 单价5,SQL数据更新语句,SQL数据更新语句,二、删除（DELETE）语句格式：DELETE FROM 表名 WHERE 条件表达式 例4.45 清空成绩表。DELETE FROM 成绩例4.46 清空89级学生成绩记录。DELETE FROM成绩 WHERE 学生号 IN(SELECT 学生号 FROM学生 WHERE 班级 LIKE“1989%”),SQL数据更新语句,三、插入(INSERT)语句格式1：INSERT INTO 表名(字段名,字段名)VALUES(表达式,表达式)格式2：INSERT INTO 表名(字段名,字段名)子查询,SQL数据更新语句,例4.47 录入一个学生记录到学生表中，假设已将其学号、姓名、性别、出生年份和班级数据分别赋值给变量xh1、xm1、xbl、csnf1、bj1。INSERT INTO 学生(学号，姓名，性别，出生年份，班级)VALUES(xh1,xm1,xbl,csnf1,bj1),SQL数据更新语句,例4.48 欲由表4.1的营业库1和营业库2生成表4.2的营业库表且增加金额一栏，可先基于营业库1生成一个新表，在新表中增加“金额”字段，再将营业库2的数据添加到生成的新表中。用如下两条语句完成。SELECT，数量单价 AS 金额 FROM 营业库1 INTO营业库 INSERT INTO 营业库 SELECT，数量单价 FROM 营业库2,SQL数据更新语句,四、视图的更新 若一个视图是从单个基本表导出，且只是去掉了基本表的某些行和某些列（不包括候选关键字），我们称这类视图为行列子集视图。例如：建立女学生视图S_V1。CREATE VIEW S_V1ASSELECT 学生号，性别FROM 学生WHERE 性别=女,SQL数据更新语句,对于行列子集视图允许通过它更新基本表。对视图的更新，实质是通过视图实现对基本表的更新。但如果视图中某些字段对应的是对基本表按某种表达式运算的结果，或者视图是基于多表建立的，将不能通过这样的视图实现对基本表的更新。,SQL数据更新语句,例如 删除女学生视图s_v1中学号为200101的学生。DELETEFROM s_v1WHERE 学号=200101；转换为对基本表的更新：DELETEFROM 学生WHERE 学生号=200101 AND性别=女；,SQL数据更新语句,视图更新的限制：目前各DBMS一般都只允许对行列子集视图进行更新，并有以下限制：（1）若视图是由两个以上基本表导出的，则此视图不允许更新。（2）若视图的字段来自表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。（3）若视图的字段来自集函数，则此视图不允许更新。（4）若视图定义中含有GROUP BY子句，则此视图不允许更新。（5）若视图定义中含有DISTINCT任选项，则此视图不允许更新。（6）若视图定义中有嵌套查询，并且内层查询的FROM子句中的表是相同的，则此视图不允许更新。（7）一个不允许更新的视图所导出的视图不允许更新。,第四章 关系数据库实现及应用,4.1 关系代数4.2 关系演算4.3 SQL语言概貌4.4 SQL数据定义功能4.5 SQL数据查询语句4.6 SQL查询语句的加强4.7 视图4.8 SQL数据更新语句 4.9 SQL数据控制功能 4.10 嵌入式SQL4.11 查询优化,SQL数据控制功能,SQL具有授权语句，实现对数据库使用的控制。其语句格式为：GRANT 权力,权力ON 对象 类型 对象名 TO 用户,用户WITH GRANT OPTION IDENTIFIED BY 口令,SQL数据控制功能,其中，所授的权力可分为两类：1进入数据库系统权力2使用他人数据的权力。,1进入数据库系统权力，建立自己的数据权力。这些权力由系统管理员授予。包括：CONNECT 连接到数据库的权力 CREATETAB 建立数据库的权力或在数据库中建立表的权力。DBA，系统管理员的权力。,SQL数据控制功能,例4.49 授予用户USER1进入数据库系统权力。GRANT CONNECT TO USER1例4.50 将数据库学生系统中建立表的权力授予用户USER1。GRANT CREATETAB ON DATABASE 学生系统 TO USER1,SQL数据控制功能,2使用他人数据的权力。一般由数据表的拥有者授予，这些权力包括：SELECT 查询权限 INSERT 插入新记录权限 DELETE 删除记录权限 UPDATE(属性名,属性名)对有关列修改权限 ALTER 修改表结构权限 INDEX 建立索引权限 ALL 以上所有权限,SQL数据控制功能,SQL数据控制功能,例4.51 设成绩表由USER1所建，则USER1可发出命令将对成绩表查询、修改的权力授予USER2。GRANT SELECT,UPDATE ON TABLE 成绩 TO USER2授权者可以撤销授权，其语句为：REVOKE 权力,权力ON 对象类型 对象名 FROM 用户,用户例4.52 将用户USER2修改学生号的权力收回。REVOKE UPDATE(学生号)ON TABLE 学生 FROM USER2,这类权力授予时，用户名可用“public”表示向所有用户授权选项。选项“WITH GRANT OPTION”表示得到授权的用户可将同样权力再转授他人。,SQL数据控制功能,第四章 关系数据库实现及应用,4.1 关系代数4.2 关系演算4.3 SQL语言概貌4.4 SQL数据定义功能4.5 SQL数据查询语句4.6 SQL查询语句的加强4.7 视图4.8 SQL数据更新语句 4.9 SQL数据控制功能 4.10 嵌入式SQL4.11 查询优化,SQL语句嵌入的语言称为宿主语言。例如JAVA、C宿主语言中使用的SQL结构称为嵌入式SQL。嵌入C的查询语句格式为：EXEC SQL SELECT 目标列 INTO：变量 FROM 基本表 或视图 WHERE 条件表达式 其中主变量为宿主语言程序的变量，前面加“：”作为标识，以和SQL变量相区分。,嵌入式 SQL,嵌入式 SQL,一般宿主语言执行一次只处理一个元组，而在实际问题中，查询结果是多个元组，则要使用游标。例如下面一段程序：EXEC SQL DECLARE C CURSOR FOR SELECT 姓名,性别,出生年份 FROM 学生；EXEC SQL OPEN C；LOOPEXEC SQL FETCH C INTO:xm1,:xb1,:csnf1；PRINT xm1,xb1,csnf1；GOTO LOOP,第四章 关系数据库实现及应用,4.1 关系代数4.2 关系演算4.3 SQL语言概貌4.4 SQL数据定义功能4.5 SQL数据查询语句4.6 SQL查询语句的加强4.7 视图4.8 SQL数据更新语句 4.9 SQL数据控制功能 4.10 嵌入式SQL4.11 查询优化,对于查询过程可以用语法树表示。以参与运算的关系作为叶，向上列出每步运算表达式，例第一方案可用图4.5表示，第二方案用图4.6表示。,查 询 优 化,查 询 优 化,图4.5 查询优化方法1,图4.6 查询优化方法2,查询优化关系变换时常用到一些变换规则：此处F1、F2是选择条件，将两个条件合并后，一次对关系的扫描就可完成两个条件的选择操作。查询涉及对同一关系选择和投影两步操作时，如果先选择的中间结果大大小于先投影的中间结果，宜先作选择再作投影。,查 询 优 化,式中B1,B2Bm是F中的属性。在前面例中，虽然最终结果只需姓名、分数两列数据，但为了学生表，成绩表连接的需要，在首先求学生投影时取姓名、学生号两个字段。虽然多做了一步操作，但总查询时间是减少的。如果F中属性全包含在A1,A2,An之中，则可简单交换：,查 询 优 化,4.关于F(E1E2)如果F中涉及属性全为E1中的属性，则F(E1E2)F(E1)E2 如果F=F1 F2，且F1中属性全属于E1，F2中属性全属于E2，则F(E1E2)F1(E1)F2(E2)如果F=F1 F2，F1只涉及E1中属性，F2涉及E1，E2两者的属性,则F(E1E2)F2(F1(E1)E2),查 询 优 化,5.如A1,A2,An是E1的属性，B1,B2Bm是E2的属性，则投影与笛卡尔积交换，即A1,A2,An，B1,B2Bm(E1E2)=A1,A2,An(E1)B1,B2Bm（E2）6.选择与并的交换：7.选择与差的交换：8.投影与并的交换：,查 询 优 化,