关系数据库标准语言.ppt

关系数据库标准语言,SQL结构化查询语言1986年被批准为关系数据库语言的美国标准，87年国际标准化组织ISO也通过了这一标准。经过ANSI的不断修改和完善，89年公布了SQL-89标准，92年公布了SQL-92标准。现在最新发布的是SQL-99标准，亦称SQL3，其中增加了一些面向对象的特征。SQL作为标准的关系数据库语言，具有十分重大的意义，倍受用户及计算机工业界欢迎，被众多的计算机公司和 软件公司所采用，对数据库以外的领域也产生了很大影响。 SQL是一个综合的、通用的、功能极强而又简捷易学的语言。它集数据定义、数据操纵和数据控制功能于一体，充分体现了关系数据库语言的特点和优点。,SQL语言的特点：,1）综合统一 SQL集DDL、DML、DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动。 关系模式中，实体和实体间的联系均用关系来表示，数据结构的单一性带来了数据操作符的统一性，使得查找、插入、删除、修改等每种操作都只需一种操作符。 2）高度非过程化 只需指明“做什么”，而不必指明“怎么做”，无需了解存取路径及操作过程。 3）面向集合的操作方式 采用集合操作方式，不仅查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。 4）以同一种语法结构提供两种使用方式 自含式（交互式）和嵌入式（程序式）两种使用方式，语法结构基本是一致的。 5）语言简洁，易学易用 语言功能极强但十分简洁，只用了9个动词；而且语法简单，接近口语，易学易用。,SQL语言功能和动词,数据查询： 检索SELECT 数据定义： 创建CREATE，删除DROP， 修改ALTER 数据操纵： 插入INSERT，删除DELETE， 更改UPDATE 数据控制：授权GRANT，回收REVOKE,SQL语言的基本概念,SQL语言支持关系数据库三级模式结构：,SQL,视图1,视图2,基本表2,基本表1,存储文件1,存储文件2,基本表3,基本表4,外模式,模式,内模式,样本数据库表结构,学生表：Student(Sno, Sname, Ssex, Sage, Sdept, Sclass); 其中Sno为主码。 课程表：Course(Cno, Cname, Cpno, Ccredit); 其中Cno为主码。 选课表：SC(Sno, Cno, Grade); 其中（Sno, Cno）为主码。,SELECT（投影）,投影查询: SELECT ALL|DISTINCT FROM 1）当有多列时，用“，”分隔。从多表取时表名之间也用“，”分隔。 SELECT sname, ssex, sclass FROM student ； 2）如果要去掉重复行，加上“DISTINCT”来指明。 SELECT DISTINCT sclass FROM student ； 3）可以通过指定别名来改变查询结果的列标题 SELECT sno 学号, sname 姓名, ssex 性别, sclass 班号 FROM student ； 结果： 学号 姓名 性别 班号 1001 张三 男 1 1002 李四 女 2,SELECT（选择）,选择查询 SELECT FROM WHERE WHERE子句的条件中除了一般的关系运算符，另增谓词算符，主要有： BETWEENAND ： 字段值在介定的下限和上限之间。 IN： 字段值在某个集合之中。 LIKE： 字段值与一个字符图式匹配 IS NULL： 字段值为空值 1）输出学生选修课号为C601或C602的学生成绩。 SELECT sno, cno, grade FROM SC WHERE cno in (C601,C602) ; 2）显示成绩表中成绩为80到90的记录： SELECT * FROM SC WHERE grade BETWEEN 80 AND 90 ; 2)显示姓“王”的学生名单： SELECT * FROM student WHERE sname LIKE 王% ; 通配符：% 可以与0个或多个任意字符匹配； _ 可以与一个未知字符匹配。,SELECT（排序）,排序查询 SELECT FROM WHERE ORDER BY 1)以class 降序在屏幕上显示STUDENT表的所有记录。 SELECT * FROM student ORDER BY sclass DESC ; 2)以cno为升序 ,sno为降序在屏幕上显示SCORE表的所有记录。 SELECT * FROM SC ORDER BY cno, sno DESC ; 3)按班级顺序列出学生姓名、性别、班级，同一班级按学生姓名排序： SELECT sclass,sname,sno FROM student ORDER BY sclass,sname ; 缺省为升序排列。,SELECT（分组聚合）,统计查询（聚合函数、分组及筛选子句） SELECT FROM WHERE GROUP BY HAVING 该方式下，用SELECT语句实现了数据统计的功能。 聚合函数（集函数）： AVG（），COUNT（），MIN（），MAX（），SUM（） 分组子句： GROUP BY子句将查询结果表按某一列或多列值分组，值相等的为一组。目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数作用于整个查询结果（整个查询结果只有一个函数值）；而一旦分组，集函数将作用于每一个组，即每一个组都有一个函数值（先分组后统计）。 例：求各个课程号及响应的选课人数： SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno; 应注意Select子句中各项的一致性，当出现GROUP BY子句时，必须全部为分组属性。 SELECT empno,sal FROM emp group by empno; （错） SELECT empno,sum(sal) FROM emp GROUP BY empno;（对）,SELECT-(分组筛选),Where与having 区别 类似处：都是筛选数据。 不同之处： 1）使用的前提不同 where条件可以随意使用 having条件只能在分组group by 子句中出现。 2）作用的对象不同： where 条件作用于整个表，从中选择满足条件的元组；而having只作用于每个组，从中选择出满足条件的组。 3）Having条件必须是描述分组性质的属性。 例如：列出选修了3门以上课程的学生学号： SELECT Sno FROM SC GROUP BY Sno HAVING count(*)3; 1：用GROUP BY子句按sno进行分组； 2：然后用集函数COUNT对每一组记数； 3：最后用HAVING 筛选出满足条件的组输出。,SELECT-（连接查询）,在数据查询中，经常涉及提取两个或多个表的操作，（如关系操作中的连接操作），成为连接查询。 连接查询包括： 等值连接查询 非等值连接查询 自然连接查询 自身连接查询 外连接查询 复合条件连接查询 ,普通连接查询,例子1： 查询每个学生及其选修课程的情况 SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno; 通过Sno将两个表联系起来，即将Student与SC中同一学生的元组连接起来。 Student.Sno=SC.Sno成为连接条件或连接谓词。连接谓词中的列名称为连接字段。,例子2（多表连接查询） 求王小艳同学所学课程的成绩。结果要求：学生姓名、课程名称和成绩。 用SQL语言描述求解： SELECT a.sname, ame, c.grade ; FROM Student a, Course b, SC c ; WHERE a.sname=王小艳 AND a.sno=c.sno AND o=o 总之：涉及多个表的字段时，在WHERE条件中将所有连接条件表述清楚，再进行选择和投影等操作。,自身连接查询,连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，成为表的自身连接。 例：查询每一门课程的间接先修课（即先修课的先修 课）。 为了完成该查询，需要将Course表与其自身连接。我 们可以为Course表取两个别名：FIRST, SECOND. SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno = SECOND.Cno;,SELECT-（嵌套查询）,子查询： 当一个查询的结果是另一个查询的条件时，即筛选条件依赖于另外一个查询的结果，这种查询称作子查询，又叫做嵌套查询。相应内层查询称为子查询，外层称主查询或父查询。 子查询返回值只一个： 查询与成功在同一个班的学生名单： 分两步：首先找到成功同学所在的班级，然后去筛选这个班的学生。 select * from student where sclass= (select sclass from student where sname =“成 功”); 子查询返回值是一组值： 查询选修2号课程的学生姓名： select sname from Student where Sno IN (select Sno from SC where Cno=2);,嵌套查询的求解方法是由里到外处理。它使得可以用简单查询构成复杂的查询，明显增强了SQL的查询能力。层层嵌套的方式构造程序正是SQL中的“结构化”的含义所在。 上面的嵌套查询用连接查询如何表示？,SELECT-（相关子查询）,在前面的嵌套查询中，子查询仅仅执行一次，返回的值被主查询的WHERE子句所使用；但是在有的查询中，子查询需要根据主查询进行计算，然后再将其查询结果影响主查询。子查询不只执行一次，我们称之为相关子查询，也叫主子查询的同步操作。 例如：查询成绩比该课程平均成绩高的学生选课成绩： 分析：主查询： select * from SC where grade待选同学所修课程的平均分 子查询：select avg(grade) from SC where cno=主查询待选的课程号 主查询在判断每个待选行时，必须“唤醒”子查询，告诉它该同学选修的课程号，并由子查询计算课程的平均成绩，然后将该同学的grade与平均 成绩进行比较，找出相应的符合条件的行。 实现：select * from SC a where grade (select avg(b.grade) from SC b where o = o) 关键 ：理解相关子查询的关键是别名。它出现在主查询（from SC a）和子 查询 (from SC b)中。这样同一个表相当于在内存中有两个表，当在 子查询中使用o时，它访问待选行的cno，这时是一个常量， 从而 能在b别名表中找出该常量课程的平均分。 不足：由于这个过程很费时，不要大量使用相关子查询。,SELECT-(带EXISTS谓词的子查询),当子查询的返回值是一个逻辑真|假值时（不返回任何数据），即提供给父查询一个二值判断结果时，可以使用存在量词EXISTS来实现。 例如： 。查询选修了课程的学生记录： Select * from Student where exists (select * from SC where Student.Sno=Sno) 当子查询找到了满足条件的记录时，父查询的WHERE的条件为真，未找到时为假。 。查询未选修课程的学生记录： Select * from Student a where not exists (select * from SC where a.Sno=Sno) 当子查询找到了满足条件的记录时，父查询的WHERE的条件为假，未找到时为真。,INSERT,1。在一个表中添加一条新记录并给字段赋值。 INSERT INTO , VALUES(,) 。列出部分字段名，如向student 表中插入一个学生记录： Insert into student(sno,sname,sold,ssex,sclass) values(10000101,田七,20,女,微机100001) 。 所有列都赋值时不需要列出列名，如向成绩表中插入一条记录： insert into score values(10000101,C601,100) 2。利用子查询，一次插入多条记录。/标准SQL可以，VFP不可以。 INSERT INTO SELECT FROM creeate table degree (sno c(8),cno c(4),degree n(6,2) insert into degree(sno,cno,degree) select sno,cno,score from score？,UPDATE,用新的值来更新表中的一条或多条记录。 UPDATAE 数据库名！ SET 字段名1=表达式 1，字段名2=表达式 WHERE 条件 。将学号为10000101的学生年龄改为25。 update tms!student set sold=22 where sno=10000101 或 update student set sold=22 where sno=10000101 。将所有学生的年龄加1 update tms!student set sold=sold+1,DELETE,删除命令用于给一条或多条记录加删除标记，而不是真正地删除（VFP特定）。标准SQL中是真删除。 DELETE FROM 数据库名！WHERE 条件 。删除student 表学号为10000101的学生记录： delete from student where sno= 10000101 。删除学号为10000101的成绩记录： delete from score where sno=10000101 若要真正删除（物理删除），需要用pack命令 delete pack,数据定义（SQL-DDL）,包括创建各种数据库对象：如表、视图、索引以及存储 过程等。 相关的命令动词包括： CREATE ALTER DROP （1）建立表： CREATE TABLE ( 列级完整性约束条件 , ) ； 例子：CREATE TABLE sample(no char(4) NOT NULL UNIQUE, name char(8), birth date) （2）修改表：ALTER TABLE ADD 完整性 约束 DROP MODIFY ； （3）删除表：DROP TABLE ,建立与删除索引,建立索引是加快查询速度的有效手段。用户可以根据应用环境的需要，在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。系统在存取数据时会自动选择合适的索引作为存取路径。 建立索引 CREATE UNIQUECLUSTER INDEX ON (,); 删除索引 DROP INDEX ;,视图,视图：从一个或几个基本表（或视图）导出的表，它与基本表不同 是一个虚表。 数据库中只存放视图的定义，而不存放对应的数据，这些数据仍存在原来的基本表中。 视图就象一个窗口，透过它可以看到用户感兴趣的数据及其变化，故视图是关系数据库系统提供给用户的以多种角度观察数据库中数据的重要机制。 在数据操作上，视图可以和基本表一样被查询，但对视图数据的更新（增、删、改）操作则有一定的限制。 可以定义和删除视图，也可以在一个视图之上再定义新的视图。,视图的创建,建立视图：CREATE VIEW (, ) AS WITH CHECK OPTION； 说明： 1、 WITH CHECK OPTION表示对试图进行UPDATE，INSERT和DELETE操作要保证操作的行满足视图定义中的谓词条件（即子查询中的条件表达式）。子查询中不允许含ORDER BY 子句和DISTINCT短语。 2、执行时，DBMS执行CREATE VIEW语句的结果知识把视图的定义存入数据字典，并不执行其中的SELECT语句，只是在对视图查询时，才按视图的定义从基本表中将数据查出。 3、视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或建立在基本表和视图上。 4、行列子集视图：从单个基本表导出，只是去掉了某些行和列，但保留了码。 5、带表达式的试图：为由基本数据经过各种计算派生出的数据设置了派生属性列。 6、若基本表的结构发生改变，则基本表和视图的映象关系被破坏，视图不能正常工作，需要在修改基本表后删除由该基本表导出的视图，然后重建（同名）视图。,查询视图,视图消解（VIEW RESOLUTION） 对视图的查询，首先进行有效性检查，检查查询的表、视图等是否存在，若存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。该转换过程称视图消解。 一般情况下，视图查询的转换是直截了当的 ，但有些情况下，这种转换不能直接进行时，查询不能进行，应直接操作基本表。目前，多数关系数据库系统对行列子集视图的查询均能进行正确转换。但对非行列子集视图的查询不一定能做转换了。,更新视图,通过视图对数据进行增删改操作时，DBMS回检查视图定义中的 条件，若条件不满足，则拒绝执行该操作。 更新操作一般是直接转换为对基本表的更新操作。但是并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新则不可执行。 一般，行列子集视图是可更新的。而其它的不能保证。各种具体的DBMS对视图的更新还有更进一步的规定，而且规定也不尽相同。 不可更新的视图与不允许更新的视图是两个不同的概念。前者指理论上已证明是不可更新的视图，后者指实际系统中不支持其更新，但它本身有可能是可更新的视图。,视图的作用,视图使用有许多的不便，为什么还要定义视图？ 1、视图能简化用户的操作。 使用户可以将注意力集中在所关心的数据上。使数据结构看起来简单、清晰并可简化查询操作。 2、视图使用户能以多种角度看待同一数据。 当许多不同种类的用户共享同一数据库时，视图为其带来灵活性。如物资处采购人员和仓库管理人员从不同角度看待和共享物资数据。 3、视图对重构数据库提供了一定程度的逻辑独立性。 如当基本表增加新的字段时，数据的外模式（即视图）不会更改，从而用户程序不会受影响。当然，当基本表结构的更改改变了视图与其的映象时，视图仍需要更新。故是一定程度上的。 4、视图能够对机密数据提供安全保护。 对不同权限的拥护定义不同的视图，使机密数据不出现在不应看到这些数据的用户视图上，这样视图机制就提供了对机密数据的安全保护功能。 如不同的系只能看到本系学生的数据。,数据控制（SQL_DCL）,SQL中的数据控制功能包括事务管理功能和数据保护功能，即： 事务管理和数据库恢复 数据库并发控制 数据完整性控制 数据安全性保护 这里主要讨论安全性控制功能： 某个用户对某类数据具有何种操作权利是政策问题而非技术问题。DBMS的功能是保证则这些决定的执行，为此应具有以下功能： 把授权的决定告知系统，由GRANT和REVOKE来完成。 把授权的结果存入数据字典。 当用户提出操作请求时，根据字典进行查询，以决定是否执行操作请求。,授权和回收权限,GRANT：将对指定操作对象的指定权限授予指定的用户。 1、对不同类型的操作对象（属性列、视图、基本表或数据库）有不同的操作权限。参见表3.4（p130）。 2、接受权限的用户可以是一个或多个具体用户，也可以是PUBLIC，即全体用户。 3、如果指定了WITH GRANT OPTION子句，则获得该权限的用户还可以把这种权限再授予其他的用户。否则不能传播该权限。 REVOKE：授予用户的权限可以由DBA或其他授权者用该语句收回。 1、回收权限的操作回级联下去。 2、所有授予出去的权利在必要时都可以用REVOKE 语句收回。 总之： DBA拥有对数据库中所有对象的所有权限，并可授权和回收。,嵌入式SQL,嵌入式SQL： SQL是非过程语言，大多数语句都是独立执行，与上下文无关的。但是许多事务处理应用都是过程性的，需要根据不同的条件来执行不同的任务，所以SQL语言提供了另一种使用方式即将其嵌入到某种高级语言中（过程语言）中使用，这种方式下使用的SQL语言称为嵌入式SQL，而嵌入SQL的高级语言称主语言或宿主语言。 一般形式： 在嵌入式SQL中，所有SQL语句都必须加前缀EXEC SQL。而SQL语句结束标志则随主语言的不同而不同。,嵌入式SQL与主语言之间的通信,数据库工作单元与源程序单元之间的通信包括： 1、向主语言传递SQL语句的执行状态信息，主要用SQL通信区SQLCA来实现。常用的 判断SQLCA.SQLCODE=SUCCESS，得知SQL语句是否执行成功，并获取错误原因等。 2、主语言向SQL语句提供参数，主要用主变量实现。 主变量：嵌入式SQL中使用的主语言程序变量的简称。根据用途的不同分输入主变量和输出主变量。主变量可以附带一个任选的指示变量（它是一个整型变量），用来指示所指主变量的值或条件。主变量名前要加冒号（：）作为标志，以同数据库对象名相区别。 3、将SQL语句查询数据库的结果交主语言进一步处理，主要用主变量和游标实现。 游标：SQL是面向集合的，而主语言是面向记录的，一组主变量依次只能存放一条记录。所以用游标来协调这两种不同的处理方式。它是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果（记录集合），用户可通过游标逐一获取记录，并赋给主变量做进一步处理。,不用游标的SQL语句,所有的说明性语句及数据定义与控制语句都不需要游标,因它们不需要返回结果数据,也不需要使用主变量。只需加上前缀EXEC SQL和语句结束符即可。 说明性语句（用来说明主变量而设置的语句，相当于一个括号。 EXEC SQL BEGIN DECLARE SECTION； EXEC SQL END DECLARE SECTION；） 数据定义语句（CREATE，ALTER，DROP） 数据控制语句（GRANT，REVOKE） 查询结果为单记录的SELECT语句 非CURRENT(当前记录)形式的UPDATE语句 非CURRENT形式的DELETE语句 INSERT语句 （以上语句可以使用主变量）,使用游标的SQL语句,一般情况下，SELECT 语句查询结果都是多条记录，而高级语言一次只能处理一条记录，因此需要用游标机制，将多条记录一次一条地送至宿主程序处理，从而把对集合的操作转换为对单个记录的处理。 必须使用游标的SQL语句： 查询结果为多条记录的SELECT语句。 CURRENT形式的UPDATE语句 CURRENT形式的DELETE语句 使用游标的步骤： 1、说明游标。 EXEC SQL DECLARE CURSOR FOR ； 2、打开游标。 EXEC SQL OPEN ； 3、推进游标指针并取当前记录。 EXEC SQL FETCH INTO ； 4、关闭游标。 EXEC SQL CLOSE；,使用游标的例子,参见书，或给出例子。（略） 注意： 当游标定义中的SELECT语句带有UNION或ORDER BY 子句时，或者该SELECT 语句相当于定义了一个不可更新的视图时，不能使用CURRENT形式的UPDATE语句和DELETE 语句。,动态SQL简介,静态SQL语句： 语句中主变量的个数和数据类型在预编译时都是确定的，只有主变量的值是程序运行过程中动态输入的。 动态SQL技术： 动态SQL方法允许在程序 运行过程中临时“组装”SQL语句。 当SQL语句正文、主变量个数或数据类、SQL语句中引用的数据库对象等不可确定时，就必须使用动态SQL技术。 动态形式几乎可覆盖所有的可变要求，SQL提供了相应的语句，如EXECUTE IMMEDIATE，PREPARE 和EXECUTE， PREPARE 和FETCH，DESCRIBE等。使用它们更多的是涉及程序设计方面的知识，而不是SQL语言本身。,使用EXECUTE IMMEDIATE 的例子,这种方法是先将一个SQL命令存放在宿主变量中，EXECUTE IMMEDIATE 语句执行时即将这个命令送到SQL Server去执行。 例子： EXEC SQL BEGIN DECLARE SECTION; DBCHAR sqlstring200; EXEC SQL END DECLARE SECTION; char cond100; EXEC SQL WHENEVER SQLERROR PERFORM err_p();,strcpy(sqlstring, “ update titles set price=price*1.10 where”); printf(“Enter search condition:”); scanf(“%s”,cond); strcat(sqlstring,cond); EXEC SQL EXECUTE IMMEDIATE :sqlstring; EXEC SQL COMMIT Transaction Release;,本章小结,本章系统而详尽地讲解了SQL语言，SQL是关系数据库语言的工业标准。标准SQL在具体的DBMS系统上运行，一般要稍作修改后才能运行。 SQL的功能分DDL、DML、DCL三部分，也可将数据查询从DML中分出来，列为四部分。 本章重点：SQL查询语句的学习和使用。,上机练习,上机完成习题中的SQL命令查询。 练习用查询设计器来完成书上的例子。,4个样本表,Student.dbf学生信息表：,teacher.dbf教师信息表：,course.dbf 课程表：,score.dbf成绩表：,练习题,1、显示教师所在的单位，即不重复的depart列。 2、显示SCORE表中成绩在60到80之间的所有记录。 3、以CNO升序、DEGREE降序显示SCORE表的所有记录。 4、显示3-105号课程的平均分。 5、显示SCORE表中至少有5名学生选修的并以3开头的课程号的平均分数。 6、从SCORE表中，找出最低分大于70，最高分小于90的NO列。 7、列出“95033”班所选课程的平均分（多表连接） 8、显示“张旭”教师任课的学生成绩。（子查询） 9、显示选修某课程的同学人数多于5人的教师姓名。 10、列出“计算机系”教师所教课程的成绩表。 11、列出所有教师和同学的name,sex和birthday.(用UNION) 12、列出成绩比该课程平均成绩低的同学的成绩表。（相关子查询） 13、列出