关系数据库标准语言SQL(4).ppt

3.1 SQL的特征,3.2 SQL的数据定义,3.3 SQL的数据查询,3.4 SQL的数据更新,第三章 关系数据库标准语言SQL,本章小结,第三章 关系数据库标准语言SQL,3.6 嵌入式 SQL （自学）,3.7 动态 SQL （自学）,本章小结,3.8 ODBC与JDBC （自学）,3.5 SQL的数据控制（自学）,第三章 关系数据库语言SQL,3.1 SQL的特征 1. SQL的主要标准 SQL-86 SQL-89 SQL-92(SQL2) SQL-99(SQL3),SQL-86。SQL的第一个标准是1986年10月由美国国家标准化组织(ANSI)公布的。 SQL-89。ANSI以后通过对SQL-86的不断修改和完善，于1989年第二次公布了SQL标准，即SQL-89，该标准增强了完整性的语言特征。 SQL-92(SQL2)。1992年又公布了SQL-92标准，该标准增加了支持对远程数据库的访问，扩充了数据类型、操作类型、动态SQL等许多新的特征。 SQL-99(SQL3)。完成于1999年的SQL-99修订本具有更高级的特征。引入了支持对象-关系DBMS模型的SQL，扩展了对象、递归、触发等许多新的特征，支持用户自定义函数、自定义数据类型。,2. SQL的功能特点 功能： 数据定义 数据查询 数据操纵 数据控制,第三章 关系数据库语言SQL,特点： 综合统一 非关系模型的数据语言不同模式有不同的数据定义语言及数据操纵语言。SQL语言则集数据定义、操纵和控制功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，这就为数据库应用系统开发提供了良好的环境，例如用户在数据库投入运行后，还可根据需要随时地逐步地修改模式，并不影响数据库的运行，从而使系统具有良好的可扩充性。,第三章 关系数据库语言SQL,高度非过程化 非关系数据模型的数据操纵语言是面向过程的语言，用其完成某项请求，必须指定存取路径。而用SQL语言进行数据操作，用户只需提出“做什么”，而不必指明“怎么做”，因此用户无需了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。这不但大大减轻了用户负担，而且有利于提高数据独立性。,第三章 关系数据库语言SQL,灵活的使用方式 SQL语言既是自含式语言，又是嵌入式语言。 作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作。作为嵌入式语言，SQL语句能够嵌入到高级语言（例如C、COBOL、FORTRAN、PL/1）程序中，供程序员设计程序时使用。而在两种不同的使用方式下，SQL语言的语法结构基本上是一致的。这种以统一的语法结构提供两种不同的使用方式的作法，为用户提供了极大的灵活性与方便性。,第三章 关系数据库语言SQL,简洁、通用、功能强 SQL语言功能极强，设计巧妙，语言简洁，完成数据定义、数据操纵、数据控制的核心功能只用了9个动词，如表3-1所示。而且SQL语言语法简单，接近英语口语，因此容易学习，容易使用。,3.1 SQL的特征,3. SQL的基本组成 （1）SQL数据库层次结构,术语对照： 一般关系模型 SQL 外模式-视图（VIEW) 模式-基本表(TABLE) 内模式-存储文件（索引） 元组-行(ROW) 属性-列(COLUMN),3.1 SQL的特征,基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。 存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 用户可以用SQL语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。,3.2 SQL的数据定义,1. SQL模式的定义与撤销 （1）SQL模式的定义 CREATE SCHEMA 模式名 AUTHRIZATION 用户名 CREATE DOMAIN子句|CREATE TABLE子句|CREATE VIEW | 例；CREATE SCHEMA Teaching_db AUTHRIZATION Hang； （2）数据库模式的删除 DROP SCHEMA 模式名 CASCADE | RESTRICT CASCADE(级联式) ： RESTRICT（约束式)：,3.2 SQL的数据定义,2. 表的建立和删除 （1）表的建立 命令格式： CREATE TABLE ( 列级完整性约束条件 ， 列级完整性约束条件.) ,; 例：CREATE TABLE Student ( sno CHAR(5) NOT NULL UNIQUE， sname CHAR(20) NOT NULL ， sex CHAR(1)， age INT ， dept CHAR(15) )；,主码,3.2 SQL的数据定义,完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可定义在列级，也可以定义在表级。,注意：,例：定义三个表结构，并指定相应的数据完整性约束条件。,分析,外码：sno,cno,主码：sno,姓名：非空,性别：男、女两值,Student表：,Course表：,主码：cno,课程名：非空,外码：pcno,SC表：,主码：(sno,cno),成绩：0100,3.2 SQL的数据定义,CREATE TABLE Student ( sno CHAR (5), sname CHAR (8) NOT NULL, sex CHAR (2), age SMALLINT, dept CHAR (20), PRIMARY KEY(sno), CHECK sex IN (男,女) );,列级完整性约束条件,实体完整性约束条件,用户自定义完整性约束条件,3.2 SQL的数据定义,CREATE TABLE Course ( cno CHAR (4), cname CHAR (10) NOT NULL, pcno CHAR (4), credit SMALLINT, PRIMARY KEY (cno), FOREIGN KEY (pcno) REFERENCES Course(cno) );,参照完整性约束条件,3.2 SQL的数据定义,CREATE TABLE SC ( sno CHAR (5), cno CHAR (4) , grade SMALLINT, PRIMARY KEY (sno,cno), FOREIGN KEY (sno)REFERENCES Student(sno), FOREIGN KEY (cno)REFERENCES Course(cno), CHECK (grade IS NULL) OR (grade BETWEEN 0 AND 100) );,3.2 SQL的数据定义,（2）表的删除 格式：DROP TABLE CASCADE | RESTRICT 基本表定义一旦删除，表中的数据、在此表上建立的视图、索引、触发器、断言都将自动被删除掉。RESTRICT确保只有不具有相关对象的表才能被撤销。 例：DROP TABLE Student CASCADE；,3.2 SQL的数据定义,3.基本表的扩充和修改 一般格式为： ALTER TABLE ADD 完整性约束DROP MODIFY ; 其中指定需要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义。 （1）在现存表中增加新列 格式：ALTER TABLE ADD ( ， ),3.2 SQL的数据定义,（2）删除已存在的某个列 格式：ALTER TABLE DROP CASCADE | RESTRICT 例：ALTER TABLE Student DROP addr； （3）修改原有列的类型 格式：ALTER TABLE MODIFY ； 例：ALTER TABLE Student MODIFY place CHAR(8)；,3.2 SQL的数据定义,（4）补充定义主码 格式：ALTER TABLE ADD PRIMARY KEY ( ),（5）删除主码 格式：ALTER TABLE DROP PRIMARY KEY,3.2 SQL的数据定义,修改基本表实例 例 向Student表增加“入学时间”列，其数据类型为日期型 ALTER TABLE Student ADD Scome DATE; 例 将年龄的数据类型改为半字长整数 ALTER TABLE Student MODIFY Sage SMALLINT; 例 删除关于学号必须取唯一值的约束 ALTER TABLE Student DROP UNIQUE(Sno);,3.2 SQL的数据定义,4.索引的建立与删除 建立索引的目的：基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。 命令格式： CREATE UNIQUE CLUSTER INDEX ON ( ，)； 次序： 升序（ASC，缺省） 降序 （DESC） UNIQUE: 每一个索引值只对应惟一的数据记录。 CLUSTER: 建立聚簇索引，即索引项的顺序与表中记录的物理顺序一致。,3.2 SQL的数据定义,注意：在一个基本表上最多只能建立一个聚簇索引。 经常更新的列不宜建立聚簇索引。 所建索引放何处？ 例: 为Student表按学号升序建惟一聚簇索引。 为SC表按学号升序和课程号降序建惟一索引。 CREATE UNIQUE CLUSTER INDEX Stno ON Student(Sno)； CREATE UNIQUE INDEX Scno ON SC(Sno ，Cno DESC)； 删除索引一般格式为： DROP INDEX； 例： DROP INDEX Stno ； 删除索引时，系统会同时从数据字典中 删去有关该索引的描述。,3.3 SQL的数据查询,一般形式： SELECT FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC;,基本语句含义: 根据WHERE中的F，从基表或视图R中找出满足条件的元组，再从中选出目标属性值形成结果表。,查询目标,数据来源,选择元组的条件,将结果按的值进行分组,满足条件的组才予输出,按排序查询结果,3.3 SQL的数据查询,如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常在成组后再使用集函数。 如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。 如果有ORDER子句，则结果表还要按的值的升序或降序排序。,3.3 SQL的数据查询,其中：表示其中的成分为任选项。 ：表示其中的成分由用户具体给定。 |： 表示其中并列的成分只能择一。 查询目标： ALL：表示保留满足条件的所有元组（缺省）。 DISTINCT：表示去掉重复元组。 目标列：可以为属性名、表达式、通配符*（表示所有属性列）。,3.3 SQL的数据查询单表查询,学生-课程数据库: Student(sno，sname，sex，age，dept) Course(cno，cname，pcno，credit) SC(sno，cno，grade) 3.3.1 单表查询 一、选择表中的列 例 ：查询所有学生的姓名、学号、所在系。,SELECT sname，sno，dept FROM Student （次序不同 ）;,目标A 来源R 条件F,sname sno dept 王萧虎 200101 信息院 ,3.3 SQL的数据查询单表查询,查询经过计算的值 SELECT子句的不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。 例4 查全体学生的姓名及其出生年份 SELECT Sname, 2006-Sage FROM Student; 例5 查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名 SELECT Sname, Year of Birth:, 1996-Sage, ISLOWER(Sdept) FROM Student;,3.3 SQL的数据查询单表查询,例：查全体学生的姓名及其出生年份。 SELECT sname，2007-age AS FROM Student ；,sname 2005-age 王萧虎 1987 ,Birthday,别名,Birthday,二、选择表中的行 1 消除取值重复的行 例：查考试成绩不及格的学号。,SELECT sno FROM SC WHERE grade60 ；,DISTINCT,3.3 SQL的数据查询单表查询,2. 查询满足条件的元组 查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如下所示。 常用的查询条件： 查询条件 谓 词 比较 比较运算符 确定范围 BETWEEN AND, NOT BETWEEN AND 确定集合 IN, NOT IN 字符匹配 LIKE, NOT LIKE 空值 IS NULL, IS NOT NULL 多重条件 AND, OR,3.3 SQL的数据查询单表查询,(1) 比较（=, ） 例 查计算机系全体学生的姓名 SELECT Sname FROM Student WHERE Sdept = CS; 例 查所有年龄在20岁以下的学生姓名及其年龄 SELECT Sname, Sage FROM Student WHERE Sage = 20;,3.3 SQL的数据查询单表查询,(2)确定范围 (BETWEEN AND ) 例: 查询选002号课程且成绩在80-90的学生号。 SELECT sno FROM SC WHERE sno=002 AND grade BETWEEN 80 AND 90；,其他表示？,关系运算？,(3)确定集合(IN) 例：查询信息院、数学系和计算机学院学生的姓名和性别。 SELECT sname，sex FROM Student WHERE dept IN (信息院，数学系，计算机学院) ? 查询不是这三个系的学生的姓名和性别。,NOT,3.3 SQL的数据查询单表查询,(4)字符匹配-近似查询，模糊查询 格式：NOT LIKE 含义：是查找指定的属性列值与相匹配的元组。 其中匹配串可含： ：代表任意长度(可为0)的字符串。 _：代表任意单个字符。 例： 查所有姓刘或姓王的学生姓名、学号和性别。 SELECT sname，sno，sex FROM Student WHERE sname LIKE 刘 OR sname LIKE 王 ； ? 查询所有非姓刘或非姓王的学生姓名、学号和性别。,NOT,NOT,3.3 SQL的数据查询单表查询,例 查姓“欧阳”且全名为三个汉字的学生的姓名 SELECT Sname FROM Student WHERE Sname LIKE 欧阳_; 注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟个_。 例 查名字中第二字为“阳”字的学生的姓名和学号 SELECT Sname, Sno FROM Student WHERE Sname LIKE _阳%;,注意：一个汉字要占两个字符的位置,3.3 SQL的数据查询单表查询,格式2： LIKE ESCAPE 若要查的串本身就含有或_ ，则用ESCAPE 对通配符进行转义。ESCAPE 短语表示为换码字符，这样匹配串中紧跟在后面的字符”_”不再具有通配符的含义，而是取其本身含义，被转义为普通的”_”字符。 例：查“DB_”开头且倒数第2个字符为 i 的课程情况。 SELECT * FROM Course WHERE cname LIKE DB _ i_ ESCAPE ；,3.3 SQL的数据查询单表查询,查以”DB_”开头，且倒数第三个字符为i的课程的详细情况 SELECT * FROM Course WHERE Cname LIKE DB_%i_ ESCAPE 注意这里的匹配字符串DB_%i_。第一个_前面有换码字符，所以它被转义为普通的_字符。而%、第二个_和第三个_前面均没有换码字符，所以它们仍作为通配符。 执行结果为： Cno Cname Ccredit 8 DB_Design 4 10 DB_Programming 2 13 DB_DBMS Design 4,&,3.3 SQL的数据查询单表查询,(5) 涉及空值的查询 例 某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NULL; 注意这里的IS不能用等号（=） 代替。 例 查所有有成绩的记录的学生学号和课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL;,3.3 SQL的数据查询单表查询,三、对查询结果排序 例: 查询全体男学生的学号、系、年龄 结果按所在的系升序排列， 同一系中的学生按年龄降序排列。,Student表,排序二,排序一,SELECT sno, dept, age FROM Student WHERE sex=男 ORDER BY dept，age DESC；,Orderby的排序键应该是 查询清单中的列名,3.3 SQL的数据查询单表查询,四、使用集函数 COUNT(DISTINCT | ALL * ) 统计元组个数 COUNT(DISTINCT | ALL) 统计一列中值的个数 SUM(DISTINCT | ALL ) 计算一数值型列值的总和 AVG(DISTINCT | ALL ) 计算一数值型列值的平均值 MAX(DISTINCT | ALL ) 求一列值的最大值 MIN(DISTINCT | ALL ) 求一列值的最小值,缺省值,无重复值,3.3 SQL的数据查询单表查询,例: 查询女学生的总人数和平均年龄。 SELECT COUNT(sno),AVG(age) FROM Student WHERE sex=女 例: 查询选修001号课程并及格的学生的最高分数、最低分及总分。 SELECT MAX(grade),MIN(grade),SUM(grade) FROM SC WHERE cno=001 and grade=60,3.3 SQL的数据查询单表查询,五、对查询结果分组： GROUP BY 子句 将查询结果表按某一(多)列值分组，值相等的为一组。 目的：细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。 例 查询各个课程号与相应的选课人数 SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno;,3.3 SQL的数据查询单表查询,该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元组为一组，然后对每一组作用集函数COUNT以求得该组的学生人数。 查询结果为： Cno COUNT(Sno) - - 1 22 2 34 3 44 4 33 5 48,3.3 SQL的数据查询单表查询,如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。 例：求每个学生（号）的平均成绩，并将其超过88分的输出。 SELECT sno，AVG( grade) FROM SC GROUP BY sno；,HAVING短语：在各组中选择满足条件的小组。 WHERE子句 ：在表中选择满足条件的元组。,WHERE ?,HAVING AVG( grade) 88；,3.3 SQL的数据查询单表查询,集函数在成组之前不计算，因此 不能用于WHERE子句, 一般将简单条件写入WHERE。,HAVING子句的条件运算数之一 是一个集函数。,若HAVING子句无前导GROUPBY， 选择清单中不能含有非集函数列。,3.3 SQL的数据查询单表查询,练习：查询至少选修4门课程的学生学号。 例：找出选课学生超过30人的课程的平均成绩及选课人数。 SELECT cno,AVG(grade),COUNT(*) AS st_number FROM SC GROUP BY sno HAVING st_number=30,3.3 SQL的数据查询单表查询,例：求学生关系中女生的每一年龄组（超过20人）有多少，要求查询结果按人数升序排列，人数相同时按年龄降序排列。 SELECT age,COUNT(sno) AS number FROM Student WHERE sex=女 GROUP BY age HAVING number20 ORDER BY number,age DESC,3.3 SQL的数据查询,3.3.3 嵌套查询 1. 子查询（嵌套查询） 查询块： SELECT FROM WHERE 子查询（嵌套查询）： 一个查询块嵌套在另一查询块的条件之中。 上层的查询块又称为外层查询或父查询或主查询，下层查询块又称为内层查询或子查询。,3.3 SQL的数据查询,子查询一般跟在IN、SOME（ANY）、ALL和EXIST等谓词后面。 SQL语言允许多层嵌套查询。 嵌套查询的求解方法是由里向外处理。即每个子查询在其上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。 嵌套查询使得可以用一系列简单查询构成复杂的查询，从而明显地增强了SQL的查询能力。以层层嵌套的方式来构造程序正是 SQL(Structurred Query Language)中“结构化”的含义所在。,3.3 SQL的数据查询,(1) 带有IN谓词的子查询 带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。 例：查询与“刘晨”在同一个系学习的学生的学号、姓名、系 确定“刘晨”所在系名 SELECT Sdept FROM Student WHERE Sname=刘晨; 查找所有在IS系学习的学生。 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept=IS;,子查询实现：将第一步查询嵌入到第二步查询中，用以构造第二步查询的条件。 WHERE Sdept IN (SELECT),3.3 SQL的数据查询,例:查询选修了数据库课程的学生号、成绩。 SELECT sno， grade FROM SC WHERE cno IN ( SELECT cno FROM Course WHERE cname=数据库)；,（2）带有比较运算符的子查询 例:找出年龄超过平均年龄的学生姓名。 SELECT sname FROM Student WHERE age (SELECT AVG(age) FROM Student)；,3.3 SQL的数据查询,（3）带SOME ( ANY)、ALL谓词的子查询 SOME 大于子查询结果中的某个值 ALL 小于子查询结果中的所有值 注意：SOME、ALL必须与关系比较符同时使用。,例：查询有一门课程成绩在90分以上的学生的姓名。 SELECT sname FROM Student WHERE sno = SOME ( SELECT DISTINCT sno FROM SC WHERE grade 90);,3.3 SQL的数据查询,例：找出(平均)成绩最高的学生号。 SELECT sno FROM SC GROUP BY sno HAVING AVG(grade) = ALL (SELECT AVG(grade) FROM SC GROUP BY sno)；,SELECT sno FROM SC WHERE AVG(grade) =ALL ( SELECT AVG(grade) FROM SC GROUP BY sno) ?,3.3 SQL的数据查询,例：查询其他系中比IS系任一学生年龄大的学生名单 SELECT Sname, Sage FROM Student WHERE Sage (SELECT MAX(Sage) FROM Student WHERE Sdept=IS) AND Sdept IS,3.3 SQL的数据查询,SELECT Student. sno，sname，sex，age， dept，cno，grade FROM Student，SC WHERE Student. sno=SC. sno ；,2. 条件连接查询 连接条件的一般格式为： . . 当连接运算符为=时，称为等值连接。 例: 查询每个学生的情况及其选课成绩。,3.3 SQL的数据查询,例：找出系为信息系，选课成绩为90分以上的学生的姓名、课号和成绩。 SELECT sname，cno，grade FROM Student，SC WHERE Student.sno = SC.sno AND dept =信息系 AND grade90；,3.3 SQL的数据查询,例:查询选修了数据库的学生号、成绩 SELECT sno， grade FROM SC WHERE cno IN ( SELECT cno FROM Course WHERE cname=数据库)；,学分 ？,SELECT sno， grade FROM SC，Course WHERE SC. cno = Course . cno AND cname=数据库 ；,， Ccredit,=,子查询,连接 查询,3.3 SQL的数据查询,例：按平均成绩的降序给出所有课程都及格的学生（号、名）及其平均成绩，其中成绩统计时不包括008号考查课。 SELECT Student.sno，sname，AVG(grade) AS avg_g FROM Student，SC WHERE Student.sno=SC.sno AND cno008 GROUP BY sno HAVING MIN(grade)=60 ORDER BY avg_g DESC ；,3.3 SQL的数据查询,3.自身连接查询 例：找出年龄比“王迎”同学大的同学的姓名及年龄。 SELECT s1.sname，s1.age FROM S AS s1，S AS s2 WHERE s1.age s2.age AND s2.sname =王迎；,其他方法？,练习：查询每一门课的间接先修课（即先修课的先修课）。 SELECT FIRST.cno,SECOND.pcno FROM Course FIRST,Course SECOND WHERE FIRST.pcno=SECOND.cno;,3.3 SQL的数据查询,4. FROM子句中的子查询 在FROM子句中可以使用子查询，其查询的结果表称为导出关系（Derived Relation）。 例：查出选课成绩在80分以上的女学生的姓名、课程名和成绩。 SELECT sname，cname， grade FROM (SELECT sname， cname， grade FROM Student，SC，Course WHERE Student.sno=SC.sno AND SC.cno=Co AND sex女) AS Temp (sname， cname，grade) WHERE grade = 80 ；,导出关系,3.3 SQL的数据查询,5. 相关子查询 当一个子查询的判断条件涉及到一个来自外部查询的列时，称为相关子查询。 带存在谓词的子查询：只产生逻辑值 存在谓词EXISTS作用: 若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。 求解相关子查询不能象求解不相关子查询那样，一次将子查询求解出来，然后求解父查询。相关子查询的内层查询由于与外层查询有关，因此必须反复求值。,3.3 SQL的数据查询,例：查询所有选修了1号课程的学生姓名。 SELECT sname FROM Student WHERE EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND cno=1)；,分析： 查询所有选修了1号课程的学生姓名涉及Student关系和SC关系，在Student关系中依次取每个元组的Sno值，用此Student.Sno值去检查SC关系，若SC中存在这样的元组：其SC.Sno值等于用来检查的Student.Sno值，并且其SC.Cno=1，则取此Student.Sname送入结果关系。,3.3 SQL的数据查询,相关子查询的一般处理过程是： 首先取外层查询中Student表的第一个元组，根据它与内层查询相关的属性值（即Sno值）处理内层查询，若WHERE子句返回值为真（即内层查询结果非空），则取此元组放入结果表；然后再检查Student表的下一个元组；重复这一过程，直至Student表全部检查完毕为止。 其它方法？优点？,3.3 SQL的数据查询,SQL语言中没有全称量词（For all）。因此对于求所有的操作，必须利用谓词演算将一个带有全称量词的谓词转换为等价的带有存在量词的谓词。 SQL语言中也没有蕴涵（Implication）逻辑运算。因此也必须利用谓词演算将一个逻辑蕴函的谓词转换为等价的带有存在量词的谓词。,3.3 SQL的数据查询,例: 查询选修了全部课程的学生姓名。 转换成：查询这样的学生，没有一门课程是他不选修的。 (x)P (x(P),SELECT sname FROM Student WHERE NOT EXISTS ( SELECT * FROM Course WHERE NOT EXISTS ( SELECT * FROM SC WHERE sno=Student. sno AND cno=Course. cno)；,每门课都没选,某学生选某课,3.3 SQL的数据查询,练习：查询至少选修了学号为200101的学生选修的全部课程的学生学号。 分析：查询这样的学生，凡是200101选修的课，他都选修了。换句话说，若有一个学号为x的学生，对所有的课程y，只要学号为200101的学生选修了课程y，则x也选修了y；那么就将他的学号选出来。它所表达的语义为：不存在这样的课程y，学生200101选修了y，而学生x没有选。,3.3 SQL的数据查询,SELECT sno FROM Student X WHERE NOT EXISTS (SELECT * FROM SC Y WHERE Y.sno=200101 AND NOT EXISTS (SELECT * FROM SC Z WHERE X.sno=Z.sno AND Y.cno=Z.cno),3.3 SQL的数据查询,6.集合运算 （1）UNION （并） （2）INTERSECT （交） （3）EXCEPT （差） 例：求选修了001或002号而没有选003号课程的学生号。 (SELECT sno FROM SC WHERE cno =001 OR cno =002) EXCEPT (SELECT sno FROM SC WHERE cno =003)；,3.3 SQL的数据查询,3.3.4 自然连接与外连接 1.自然连接(NATURAL JOIN) 例：查系别为信息、课程成绩在90分以上的学生档案及其成绩情况。 SELECT * FROM Student NATURAL JOIN SC WHERE dept=信息 AND grade =90； 2. 外连接 左外连接。LEFT OUTER JOIN，保留左关系的所有元组。 右外连接。RIGHT OUTER JOIN，保留右关系的所有元组。 全外连接。FULL OUTER JOIN，保留左右两关系的所有元组。,FROM SC, Student?,自然连接的定义？,3.3 SQL的数据查询,教师表：Teacher(教师号，姓名，所属大学，职称) 任职表：Post(编号，姓名，职务) 例：SELECT * FROM Teacher FULL OUTER JOIN Post； 例：SELECT * FROM Techer LEFT OUTER JOIN Post； 例：SELECT * FROM Techer RIGHT OUTER JOIN Post；,3.3 SQL的数据查询,3.3.5 SQL中的空值处理 1.空值的含义 值暂未知。 值不适用。 值需隐瞒。 2.空值的若干规则 （1）空值与数值型数据进行算术运算，结果为空值。 （2）空值和任何值(包括空值)进行比较运算，结果为UNKNOWN。,3.3 SQL的数据查询,（3）空值的布尔运算 U AND T = U ； U AND F =F ； U AND U = U ； U OR T = T ； U OR F =U ； U OR U = U ； NOT U = U 。 空值的测试：IS NULL和IS NOT NULL。 例：查询缺少选课成绩的学生号和相应的课程号。 SELECT Sno，Cno FROM SC WHERE grade IS NULL；,3.3 SQL的数据查询,视图的概念及作用 是从一个或几个基本表(或视图)导出的表，是虚表。 与表一样可被查询。 对视图的更新操作有一定的限制。 对视图的一切操作最终将转换为对基本表的操作。 视图的作用： （1）简化结构及复杂操作。 （2）多角度地、更灵活地共享。 （3）提高逻辑独立性。 （4）提供安全保护。,3.4 SQL的数据更新,一. 插入数据 1. 插入单个元组 格式：INSERT INTO （ ，) VALUES (,); 说明：（1）若插入全部属性，则属性列可省略。 （2）表定义中说明为NOT NULL的列不能取空值。 （3）属性值与相对应的属性列的数据类型要匹配。 （4）向参照表中插入元组，关系系统自动支持： 实体完整性 参照完整性,3.4 SQL的数据更新,2. 插入子查询结果 格式：INSERT INTO (,) 子查询； 二. 修改数据 格式：UPDATE SET =,= WHERE ; 三. 删除数据 格式：DELETE FROM WHERE ;,3.5 SQL的视图,创建与使用视图 1. 创建视图 一般格式： CREATE VIEW (,) AS WITH CHECK OPTION； 其中：子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语。 WITH CHECK OPTION表示对视图进行UPDATE、 INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。 作用： 命名一个视图，AS子句定义每次查看视图时将看到的数据，在任何时刻，视图的数据由对其查询定义求值的结果行构成。,3.5 SQL的视图,例1：建立信息院学生的视图， CREATE VIEW D_ Student AS SELECT sno，sname，age FROM Student WHERE dept=信息院；,例2：建立信息院学生的视图， 要求进行更新时仍保证该视图只有信息院的学生。 CREATE VIEW D_ Student AS SELECT sno，sname，age FROM Student WHERE dept=信息院； WITH CHECK OPTION,3.5 SQL的视图,视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或同时建立在基本表与视图上。 例3 建立信息系选修了1号课程的学生的视图 CREATE VIEW IS_S1(Sno, Sname, Grade) AS SELECT Student.Sno, Sname, Grade FROM Student, SC WHERE Sdept=IS AND Student.Sno=SC.Sno AND SC.Cno=1;,3.5 SQL的视图,例4 建立信息系选修了1号课程且成绩在90分以上的学生的视图 CREATE VIEW IS_S2 AS SELECT Sno, Sname, Grade FROM IS_S1 WHERE Grade=90; 这里的视图IS_S2就是建立在视图IS_S1之上的。,3.5 SQL的视图,带虚拟列的视图：是指设置了一些基本表中并不存在的派生列(虚拟列)的视图。 定义基本表时，为了减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。但由于视图中的数据并不实际存储，所以定义视图时可以根据应用的需要，设置一些派生属性列。这些派生属性由于在基本表中并不实际存在，所以有时也称他们为虚拟列，带虚拟列的视图也称为带表达式的视图。 例5 定义一个反映学生出生年份的视图 CREATE VIEW BT_S(Sno, Sname, Sbirth) AS SELECT Sno, Sname, 1996-Sage FROM Student;,3.5 SQL的视图,分组视图：带有集函数和GROUP BY子句查询所定义的视图。 例6 将学生的学号及他的平均成绩定义为一个视图 假设SC表中“成绩”列Grade为数字型，否则无法求平均值。 CREAT VIEW S_G(Sno, Gavg) AS SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno; 例7 将Student表中所有女生记录定义为一个视图 CREATE VIEW F_Student(stdnum,name,sex,age,dept) AS SELECT * FROM Student WHERE Ssex=女;,3.5 SQL的视图,删除视图 DROP VIEW CASCADE | RESTRICT 注意： 视图删除后，视图的定义将从数据字典中自动删除。但要删除该视图导出的其他视图应用CASCADE 。,作用： 撤销一个视图，但不消除数据，在基础表中的数据仍然保留。 RESTRICT 确保只有不具有相关对象的视图（不涉及任何的约束、断言、触发器）才能被撤消。,3.5 SQL的视图,查询视图 视图定义后，用户就可以象对基本表进行查询一样对视图进行查询了。 DBMS执行对视图的查询时，首先进行有效性检查，检查查询涉及的表、视图等是否在数据库中存在，如果存在，则从数据字典中取出查询涉及的视图的定义，把定义中的子查询和用户对视图的查询结合起来，转换成对基本表的查询，然后再执行这个经过修正的查询。将对视图的查询转换为对基本表的查询的过程称为视图的消解（View Resolution）。,3.5 SQL的视图,例1 在信息系学生的视图中找出年龄小于20岁的学生 SELECT Sno, Sage FROM IS_Student WHERE Sage20; 视图是定义在基本上的虚表，它可以和其他基本表一起使用，实现连接查询或嵌套查询。 例2 查询信息系选修了1号课程的学生 SELECT Sno, Sname FROM IS_Student, SC WHERE IS_Student.Sno=SC.Sno AND SC.Cno=1; 本查询涉及虚表IS_Student和基本表SC，通过这两个表的连接来完成用户请求。,3.5 SQL的视图,更新视图 最终要转换为对基本表的更新。 注意：并不是所有的视图都可以更新的。,3.5 SQL的数据控制（自学）,由DBMS提供统一的数据控制功能是数据库系统的特点之一。数据控制亦称为数据保护，包括数据的安全性控制(连接第九章 数据库安全性)、完整性控制(连接第十章 数据库完整性)、并发控制和恢复(连接第八章 并发控制)。这里主要介绍SQL的数据控制功能。 SQL语言提供了数据控制功能，能够在一定程度上保证数据库中数据的安全性、完整性，并提供了一定的并发控制及恢复能力。,3.5 SQL的数据控制,数据库的完整性是指数据库中数据的正确性与相容性。SQL语言定义完整性约束条件的功能主要体现在CREATE TABLE语句中，可以在该语句中定义码、取值唯一的列、参照完整性及其他一些约束条件。 并发控制指的是当多个用户并发地对数据库进行操作时，对他们加以控制、协调，以保证并发操作正确执行，并保持数据库的一致性。恢复指的是当发生各种类型的故障，使数据库处于不一致状态时，将数据库恢复到一致状态的功能。SQL语言也提供了并发控制及恢复的功能，支持事务、提交、回滚等概念。 数据库的安全性是指保护数据库，防止不合法的使用所造成的数据泄露和破坏。数据库系统中保证数据安全性的主要措施是进行存取控制，即规定不同用户对于不同数据对象所允许执行的操作，并控制各用户只能存取他有权存取的数据。不同的用户对不同的数据应具有何种操作权力，是由DBA和表的建立者（即表的属主）根据具体情况决定的，SQL语言则为DBA和表的属主定义与回收这种权力提供了手段。,3.5 SQL的数据控制,1. 授权语句 SQL语言用GRANT语句向用户授予操作权限，GRANT语句的一般格式为： GRANT ,. ON TO ,. WITH GRANT OPTION; 其语义为：将对指定操作对象的指定操作权限授予指定的用户。 接受权限的用户可以是一个或多个具体用户，也可以是PUBLIC即全体用户。 如果指定了WITH GRANT OPTIO