SQL华水复试总结
概念整理
描述数据库对嵌入式SQL的处理过程
答:
DBMS的预处理程序对源程序进行扫描,识别出嵌入式SQL语句,把他们转换成主语言调用语句,使得主语言编译程序可以识别它们,然后将它们编译成目标代码。
*什么是锁?有哪两种锁?
答:
锁是最常用的并发控制机构,是防止其他事务访问指定资源,实现并发控制的一种手段。
排他锁(X写锁):当数据被加上写锁,其他事务不能对该数据进行读和写;
共享锁(S读锁):当数据被加上读锁,允许其他事务对该数据进行读,不允许写。
*简要概括一、二、三级封锁协议
答:
一级封锁协议:事务在修改数据之前加写锁,直到事务结束才释放。该协议可以防止丢失修改;
二级封锁协议:在一级封锁协议的基础上,加上了事务在读取数据之前对其加读锁,读完后即可释放读锁。该协议避免了读脏数据;
三级封锁协议:在一级封锁协议的基础上,加上了事务在读取数据之前必须加上读锁,直到事务结束才释放。该协议解决了不可重复读问题。
数据库保护(访问)的内容有哪些?
答:
利用权限机制;利用完整性约束防止法数据进入数据库;提供故障恢复能力,提供并发访问控制。
存取控制是指什么?主要包括哪两部分?有哪两类方法?
答:
存取控制是指确保只授权给有资格的用户访问数据库的权限,且令所有未被授权的人员无法接近数据。
两个部分:定义用户权限和合法权限检查;
两种方法:
自主存取控制(DAC):用户对不同的数据库对象有不同的存取权限,不同的用户对同一对象也有不同的权限
强制存取控制(MAC):每一个数据库对象被标以一定的密级,每一个用户也被授予一定级别的许可证,只有具有合法许可证的用户才可以进行存取。
数据库中常用的索引有哪些?
答:
B+树索引:B+树是对B树优化处理了的多路搜索树,与B树的区别在于,B树的每个结点都可以存储数据,而B+树只有叶子结点可以存储数据,且每个叶子结点都用链表链接。
在数据库的查询中一般都是批量查询数据,采用B+树一次查询多条时,确定首尾位置,便可以方便的确定多条数据位置,提高查询效率。
Hash索引:通过一定的算法计算出哈希值,然后映射出对应的数据存储位置。检索速度快,但是哈希值可能存在碰撞。
*等值连接和自然连接的区别是什么?
答:
自然连接是等值连接的一种特殊情况;
等值连接要求连接的是值相等的分量,两个关系中可以没有相同的属性;进行自然连接的两个关系中必须有相同的属性。
等值连接不要求去掉重复属性列;自然连接时需要除掉重复的属性列。
事物是什么?ACID特性包括?
答:
事物是数据库进行操作的一个基本单位。
ACID特性包括:
隔离性:一个事务的执行不能被其他事务所干扰;
原子性:事务是一个不可分割的单位,要么全做,要么全不做;
一致性:事务执行的结果必须使数据库从一个一致性状态变到另一个一致性状态;
永久性:一旦事务被提交,它对数据库的改变就是永久的。
为什么事务非正常结束时会影响数据库数据的正确性?
答:
事物具有一致性,事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。如果数据库系统运行中发生故障,有些事务尚未完成就被迫中断,这些未完成事务对数据库所做的修改有一部分已写入物理数据库,这时数据库就处于一种不正确的状态,或者说是不一致的状态。
数据库恢复的关键是什么?
答:
关键是建立冗余数据;
数据库恢复的基本技术有哪些?
答:
数据转储和登录日志文件是数据库恢复的基本技术。
数据转储是指DBA定期将数据库复制到其他介质上进行保存,这些备份的数据叫做后备副本;
登记日志文件,日志文件是用来记录事物对数据库进行更新操作的文件。
当系统运行过程中发生故障,利用转储的数据库后备副本和日志文件就可以将数据库恢复到故障前的某个一致性状态。
登记日志文件时为什么必须先写日志文件,后写数据库?
答:
把对数据的修改写到数据库中和把表示这个修改的日志记录写到日志文件中是两个不同的操作。有可能在这两个操作之间发生故障,即这两个写操作只完成了一个。
如果先写了数据库修改,而在运行记录中没有登记这个修改,则以后就无法恢复这个修改了。如果先写日志,但没有修改数据库,在恢复时并不会影响数据库的正确性。所以一定要先写日志文件,即首先把日志记录写到日志文件中,然后写数据库的修改。
用于数据库恢复的文件是什么?
答:
日志文件、后备副本。
*数据库故障的种类有哪几种?相应的恢复策略是什么?
答:
三种:事物故障、系统故障、介质故障;
事物故障是指事物在运行至终点前被中止;
事物故障恢复是由系统自动完成,利用日志文件撤销此事务已对数据库进行的修改;
系统故障是指造成系统停止运转的任何事件,使得系统要重新启动;
系统故障的恢复是在重新启动后系统自动完成,为了防止系统故障造成的数据不一致性,必须撤销故障时未完成的事物,重做已完成的事物;
介质故障是指磁盘上的物理数据和日志文件被破坏;
介质故障的恢复方法就是重装数据库,重做已经完成的事物。
什么是存储过程?用什么来调用?
答:
存储过程是个预编译的SQL语句集合,优点是可以建立非常复杂的查询,只需创建一次,在程序中即可多次调用,且比执行单纯的SQL语句要快。可以创建一个命令对象进行调用。
候选码,主码,区别?
若关系中的某一属性组的值能够唯一标识一元组,其子集不能,则称该属性组为候选码。
若一个关系有多个候选码,选定其中一个为主码,主码是任意一个候选码。
候选码的诸属性成为主属性,不包含候选码的属性成为非主属性。
大数据?
一般意义上,大数据是指无法在可容忍的时间内用现有的IT技术和软硬件工具对其进行感知、获取、管理、处理和服务的数据集合。
大数据通常被认为是PB或EB或者更高级的数据,包括结构化的,半结构化的和非结构化的数据。其规模或复杂程度超出了传统数据库和软件技术所能管理和处理的数据集范围。
大数据特征:巨量、多样、快变、价值。
- 传统数据诠释宏观、整体的教育状况,用于影响教育政策决策;大数据可以分析微观、个体的学生与课堂状况,用于调整教育行为与实现个性化教育。
- 传统数据挖掘方式,采集方法,内容分类,采信标准等都已存在既有规则,方法论完整;大数据挖掘为新鲜事物,还没有形成清晰的方法、路径、以及评判标准。
- 传统数据来源于阶段性的,针对性的评估,其采样过程可能有系统误差;大数据来源于过程性的,即时性的行为与现象记录,第三方、技术型的观察采样的方式误差较小。
- 传统数据分析所需要的人才、专业技能以及设施设备都较为普通,易获得;大数据挖掘需要的人才,专业技能以及设施设备要求较高,并且从业者需要有创新意识与挖掘数据的灵感而不是按部就班者,这样的人才十分稀缺。
大数据与传统数据最本质的区别体现在采集来源以及应用方向上。
大数据是互联网的海量数据挖掘。
数据库的安全性是指什么?有哪些安全性技术?
答:
数据库的安全性是指保护数据库以恶意破坏和非法存取。
安全性技术:用户标识和鉴别、多层存取控制、审计、视图、数据加密。
什么是断言?
答:
断言是指更具有一般性的约束,断言创建后,任何涉及到断言中的关系的操作都会引发数据库对断言的检查,任何使断言为假的操作都会被拒绝执行。
什么是触发器?触发器的作用?
答:
触发器是用户定义在关系表上的一类由数据驱动的一类由事物驱动的特殊过程,类似于约束,但是比约束更灵活,是保证数据库完整性的一种方法。
任何用户对表进行增删改操作都会有数据库服务器自动激活相应的触发器,对数据库进行相应的检查和操作。
审计功能是如何对数据库实现安全性控制的?
答:
审计功能是把用户对数据库的一系列操作自动记录到审计日志中,审计员可以利用审计日志监控数据库中的各种行为,找出导致数据库发生异常的事件。
SQL的特点?
答:
综合统一。集DCL,DML,DDL功能于一体;
高度非过程化。使用SQL进行数据操作时,是需要提出“做什么”,而不需要指明怎么做;
面向集合的操作方式。操作的对象、查找结果都可以是元组的集合;
同一种语法结构提供多种使用方式。既可以作为独立的语言进行交互,又可以作为嵌入式语言嵌入到更高级的语言程序中进行操作;
语言简洁,易学易用。
数据、数据库、数据库管理系统、数据库系统?
数据:数据是数据库中存储的基本对象。描述数据的符号记录称为数据。
数据库:严格地讲,数据库是长期储存在计算机内、有组织的、可共享的大量数据的集合。数据库中的数据按一定的数据模型组织、描述和储存,具有较小的冗余度(redundancy)、 较高的数据独立性(data independency)和易打展性(scalability), 并可为各种用户共享。数据库数据有永久存储、有组织、可共享三个基本特点。
数据库管理系统:数据库管理系统是位于用户与操作系统之间的一层数据管理软件。 数据库管理系统和操作系统一样是计算机的基础软件,也是一个大型复杂的软件系统。
数据定义、数据操纵、数据库的事务管理和运行管理、数据库建立和维护
数据库系统:数据库系统是由数据库、数据库管理系统( 及其应用开发工具)、应用程序和数据库管理员(DataBase Administrator, DBA)组成的存储、管理、处理和维护数据的系统。
SQL语句整理
模式
模式的定义
模式的定义与删除
1.定义模式
在SQL中,模式定义语句如下:
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;
如果没有指定<模式名>,那么<模式名>隐含为<用户名>。
要创建模式,调用该命令的用户必须拥有数据库管理员权限,或者获得了数据库管理员授予的CREATE SCHEMA的权限。
例:为用户 WANG定义-一个学生-课程模式S-T
CREATE SCHEMA"S-T" AUTHORIZATION WANG;
例:CREATE SCHEMA AUTHORIZATION WANG;
该语句没有指定<模式名>,所以<模式名>隐含为用户名WANG.
定义模式实际上定义了一个命名空间,在这个空间中可以进一步定 义该模式包含的数据库对象,例如基本表、视图、索引等。
这些数据库对象可以用表3.3中相应的CREATE语句来定义。
目前,在CREATE SCHEMA
中可以接受CREATE TABLE
, CREATE VIEW
和GRANT
子句。也就是说用户可以在创建模式的同时在这个模式定义中进-步创建基本表、 视图,
定义授权。即
CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];
例:为用户 ZHANG创建-一个模式TEST,并且在其中定义一个表TAB1
CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TABI(COL1 SMALLINT,
COL2 INT,
COL3 CHAR(20),
COL4 NUMERIC(10,3),
COLS DECIMAL(5,2)
);
模式的删除
2.删除模式
在SQL中,删除模式语句如下:
DROP SCHEMA <模式名><CASCADE I RESTRICT>;
其中CASCADE
和RESTRICT
两者必选其一。选择了CASCADE
(级联),表示在删除模式的同时把该模式中所有的数据库对象全部删除;选择了RESTRICT
(限制),表示如果该模式中已经定义了下属的数据库对象(如表、视图等),则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行DROP SCHEMA
语句。
基本表
定义基本表
定义基本表
创建了一个模式就建立了一个数据库的命名空间,一个框架。在这个空间中首先要定义的是该模式包含的数据库基本表。
SQL语言使用CREATE TABLE
语句定义基本表,其基本格式如下:
CREATE TABLE<表名> (<列名>< 数据类型> [列级完整性约束条件]
[,<列名><数据类型> [列级完整性约束条件]]
...
[,<表级完整性约束条件>]) ;
建表的同时通常还可以定义与该表有关的完整性约束条件,这些完整性约束条件被存入系统的数据字典中,当用户操作表中数据时由关系数据库管理系统自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及该表的多个属性列,则必须定义在表级上,否则既可以定义在列级也可以定义在表级。
[例3.5]建立一个 “学生”表Student。
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY, /*列级完整性约束条件,Sno 是主码*/
Sname CHAR(20) UNIQUE, /*Sname取唯一值*/
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
系统执行该CREATE TABLE
语句后,就在数据库中建立一个新的空“学生”表Student
,并将有关“学生”表的定义及有关约束条件存放在数据字典中。
[例3.7]建立学生选课表 SC.
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4).
Grade SMALLINT,
PRIMARY KEY (Sno,Cno), /* 主码由两个属性构成,必须作为表级完整性进行定义*/
FOREIGN KEY (Sno) REFERENCES Student(Sno), /*表级完整性约束条件,Sno 是外码,被参照表是Student中*/
FOREIGN KEY (Cno) REFERENCES Course(Cno) /*表级完整性约束条件,Cno是外码,被参照表是Course*/
);
模式与表
模式与表
每一个基本表都属于某一一个模式,一个模式包含多个基本表。当定义基本表时一般可以有三种方法定义它所属的模式。例如在例3.1中定义了一个学生课程模式S-T.现在要
在S-T中定义Student、Cource、 SC等基本表。
方法一,在表名中明显地给出模式名。
CREATE TABLE "S-T".Student(...); /* Student所属的模式是S-T*/
CREATE TABLE "S-T".Course(...); /* Course所属的模式是S-T*/
CREATE TABLE "S-T".SC(...); /* Sc所属的模式是S-T */
方法二,在创建模式语句中同时创建表,如例3.3所示。
方法三,设置所属的模式,这样在创建表时表名中不必给出模式名。
当用户创建基本表(其他数据库对象也样)时若没有指定模式,系统根据搜索路径(search path)来确定该对象所属的模式。
搜索路径包含一组模式列表, 关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名。若搜索路径中的模式名都不存在,系统将给出错误。
使用下面的语句可以显示当前的搜索路径:
SHOW search_path;
搜索路径的当前默认值是$user
, PUBLIC
.其含义是首先搜索与用户名相同的模式名,如果该模式名不存在,则使用PUBLIC
模式。
数据库管理员也可以设置搜索路径,例如:
SET search_path TO "S-T", PUBLIC;
然后,定义基本表:
CREATE TABLE Student (...);
实际结果是建立了S-T.Student
基本表。因为关系数据库管理系统发现搜索路径中第一个模式名S-T
存在,就把该模式作为基本表Student
所属的模式。
修改基本表
修改基本表
随着应用环境和应用需求的变化,有时需要修改已建立好的基本表。SQL语言用ALTER TABLE
语句修改基本表,其一般格式为
ALTER TABLE<表名>
[ADD [COLUMN] <新列名><数据类型> [完整性约束]]
[ADD <表级完整性约束> ]
[DROP [COLUMN] <列名> [CASCADE RESTRICT]]
[DROP CONSTRAINT<完整性约束名> [RESTRICT |CASCADE ]]
[ALTER COLUMN <列名><数据类型>] ;
其中<表名>是要修改的基本表,ADD
子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件。DROP COLUMN
子句用于删除表中的列,如果指定了CASCADE
短语,则自动删除引用了该列的其他对象,比如视图:如果指定了RESTRICT
短语,则如果该列被其他对象引用,RDBMS将拒绝删除该列。DROP CONSTRAINT
子句用于删除指定的完整性约束条件。ALTER COLUMN
子句用于修改原有的列定义,包括修改列名和数据类型。
[例3.8] 向Student表增加“入学时间”列,其数据类型为日期型。
ALTER TABLE Student ADD s _entrance DATE;
不论基本表中原来是否已有数据,新增加的列一律为空值。
[例3.9] 将年龄的数据类型由字符型 (假设原来的数据类型是字符型)改为整数。
ALTER TABLE Student ALTER COLUMN Sage INT;
[例3.10] 增加课程名称必须取唯t值的约束条件。
ALTER TABLE Course ADD UNIQUE(Cname);
删除基本表
删除基本表
当某个基本表不再需要时,可以使用DROP TABLE语句删除它。其一般格式为:
DROP TABLE <表名> [RESTRICT|CASCADE] ;
若选择RESTRICT
,则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用(如CHECK
, FOREIGN KEY
等约束),不能有视图,不能有触发器(trigger
),不能有存储过程或函数等。如果存在这些依赖该表的对象,则此表不能被删除。若选择CASCADE,则该表的删除没有限制条件。在删除基本表的同时,相关的依赖
对象,例如视图,都将被-起删除。默认情况是RESTRICT
.
索引
建立索引
建立索引
在SQL语言中,建立索引使用CREATE INDEX语句,其一-般格式为
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON<表名>(<列名> [<次序>][, <列名> [<次序>]]...);
其中,<表名>
是要建索引的基本表的名字。索引可以建立在该表的一列或多列上,各列名之间用逗号分隔。每个<列名>
后面还可以用<次序>
指定索引值的排列次序,可选ASC
(升序)或DESC
(降序),默认值为ASC
.
UNIQUE
表明此索引的每一个索 引值只对应唯一的数据记录。
CLUSTER
表示要建立的索引是聚簇索引。有关聚簇索引的概念在第7章7.5.2小节关系模式存取方法选择中介绍。
[例3.13]为学生-课程 数据库中的Student
、 Course
和SC
三个表建立索引。其中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);
修改索引
修改索引
对于已经建立的索引,如果需要对其重新命名,可以使用ALTER INDEX
语句。其一般格式为
ALTER INDEX <旧索引名> RENAME TO <新索引名>;
[例3.14]将sc
表的SCno
索引名改为SCSno
.
ALTER INDEX SCno RENAME TO SCSno;
删除索引
删除索引
索引一经建立就由系统使用和维护,不需用户干预。建立索引是为了减少查询操作的时间,但如果数据增、刑、改频繁,系统会花费许多时间来维护索引,从而降低了查询效率。这时可以删除一些不必要的索引。
在SQL中,删除索引使用DROP INDEX
语句,其一一般格式为
DROP INDEX <索引名>;
[例3.15]删除Student
表的Stusname
索引。
DROP INDEX Stusname;
删除索引时,系统会同时从数据字典中删去有关该索引的描述。
数据字典
数据字典
数据字典是关系数据库管理系统内部的一组系统表,它记录了数据库中所有的定义信息,包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。关系数据库管理系统在执行SQL的数据定义语句时,实际上就是在更新数据字典表中的相应信息。在进行查询优化和查询处理时,数据字典中的信息是其重要依据。
数据查询
数据查询是数据库的核心操作。SQL提供了SELECT语句进行数据查询,该语句具有灵活的使用方式和丰富的功能。其-般格式为
SELECT [ALLIDISTINCT] <目标列表达式> [,<目标列表达式>]...
FROM<表名或视图名> [,<表名或视图>...](<SELECT语句>)[AS] <别名>
[WHERE <条件表达式> ]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]] ;
ORDER BY子句
ORDER BY子句
用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列,默认值为升序。
[例3.39]查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列。
SELECT Sno,Grade
FROM SC
WHERE Cno '3'
ORDER BY Grade DESC;
对于空值,排序时显示的次序由具体系统实现来决定。例如按升序排,含空值的元组最后显示:按降序排,空值的元组则最先显示。各个系统的实现可以不同,只要保持一致就行。
[例3.40]查 询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;
聚集函数
聚集函数
为了进一步方便用户,增强检索功能,SQL 提供了许多聚集函数,主要有:
COUNT(*) //统计元组个数
COUNT( [DISTINCT|ALL] <列名>) //统计一列中值的个数
SUM( [DISTINCT|ALL] <列名>) //计算一列值的总和(此列必须是数值型)
AVG( [DISTINCT|ALL] <列名>) //计算一列值的平均值(此列必须是数值型)
MAX( [DISTINCT|ALL] <列名>) //求一列值中的最大值
MIN( [DISTINCT|ALL] <列名>) //求一列值中的最小值
如果指定DISTINCT
短语,则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT
短语或指定ALL
短语(ALL
为默认值),则表示不取消重复值。
[例3.41]查询学生总人数。
SELECT COUNT(*)
FROM Student;
[例3.42]查询选修 了课程的学生人数。
SELECT COUNT(DISTINCT Sno)
FROM SC;
学生每选修一门课,在Sc中都有一条相应的记录。一个学生要选修多门课程,为避免重复计算学生人数,必须在COUNT
函数中用DISTINCT
短语。
[例3.43]计算选修 1号课程的学生平均成绩。
SELECT AVG(Grade)
FROM SC
WHERE Cno='1';
自身连接
自身连接
连接操作不仅可以在两个表之间进行,也可以是一个表与其自己进行连接,称为表的自身连接。
[例3.52]查 询每一门课的间接先修课(即先修课的先修课)。
在Course
表中只有每门课的直接先修课信息,而没有先修课的先修课。要得到这个信息,必须先对一门课找到其先修课,再按此先修课的课程号查找它的先修课程。这就要将Course
表与其自身连接。
为此,要为Course
表取两个别名,一个是FIRST
,另一个是SECOND
.
FIRST表(Course 表)
Cno | Cname | Cpno | Ccredit |
---|---|---|---|
1 | 数据库 | 5 | 4 |
2 | 数学 | 2 | |
3 | 信息系统 | 1 | 4 |
4 | 操作系统 | 6 | 3 |
5 | 数据结构 | 7 | 4 |
6 | 数据处理 | 5 | |
7 | PASCAL语言 | 6 | 4 |
SECOND表(Course 表)
Cno | Cname | Cpno | Ccredit |
---|---|---|---|
1 | 数据库 | 5 | 4 |
2 | 数学 | 2 | |
3 | 信息系统 | 1 | 4 |
4 | 操作系统 | 6 | 3 |
5 | 数据结构 | 7 | 4 |
6 | 数据处理 | 5 | |
7 | PASCAL语言 | 6 | 4 |
完成该查询的SQL语句为
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno-SECOND.Cno;
结果为
Cno | Cpno |
---|---|
1 | 7 |
3 | 5 |
5 | 6 |
EXISTS
EXISTS
[例3.60]查询所有选修了 1号课程的学生姓名。
本查询涉及Student
和SC
表。可以在Student
中依次取每个元组的Sno
值,用此值去检查SC
表。若SC
中存在这样的元组,其Sno
值等于此Student.Sno
值,并且其Cno='1'
,则取此Student.Sname
送入结果表。将此想法写成
SQL语句是
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1');
使用存在量词EXISTS
后,若内层查询结果非空,则外层的WHERE
子句返回真值,否则返回假值。
由EXISTS
引出的子查询,其目标列表达式通常都用*
,因为带EXISTS
的子查询只返回真值或假值,给出列名无实际意义。
本例中子查询的查询条件依赖于外层父查询的某个属性值(Student
的Sno
值),因此也是相关子查询。这个相关子查询的处理过程是:首先取外层查询中Student
表的第一个
元组,根据它与内层查询相关的属性值(Sno
值)处理内层查询,若WHERE
子句返回值为真,则取外层查询中该元组的Sname
放入结果表:然再取Student
表的下一个元组:
重复这一过程,直至外层Student
表全部检查完为止。
本例中的查询也可以用连接运算来实现,读者可以参照有关的例子自己给出相应的SQL语句。
与EXISTS
谓词相对应的是NOT EXISTS
谓词。使用存在量词NOT EXISTS
后,若内层查询结果为空,则外层的WHERE子句返回真值,否则返回假值。
集合查询
集合查询
SELECT
语句的查询结果是元组的集合,所以多个SELECT
语句的结果可进行集合操作。集合操作主要包括并操作UNION
、交操作INTERSECT
和差操作EXCEPT
.
注意,参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同。
例:计算机系男生的信息
Select *
From S
Where Ssex_='男'
Intersect
Select *
From S
Where Sdept ='计算机'
基于派生表的查询
基于派生表的查询
子查询不仅可以出现在WHERE子句中,还可以出现在FROM子句中,这时子查询生成的临时派生表(derived table)成为主查询的查询对象。
例如,例3.57找出每个学生超过他自已选修课程平均成绩的课程号,也可以用如下的查询完成:
SELECT Sno, Cno
FROM SC ,(SELECT Sno, Avg(Grade) FROM SC GROUP BY Sno)
AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno and SC.Grade >= Avg_sc.avg_grade
这里FROM
子句中的子查询将生成一个派生表Avg_sc
. 该表由avg_sno
和avg_grade
两个属性组成,记录了每个学生的学号及平均成绩。主查询将SC表与Avg sc按学号相等进行连接,选出修课成绩大于其平均成绩的课程号。
如果子查询中没有聚集函数,派生表可以不指定属性列,子查询SELECT
子句后面的列名为其默认属性。
例如例3.60查询所有选修了1号课程的学生姓名,可以用如下查询完成:
SELECT Sname
FROM Student, (SELECT Sno FROM SC WHERE Cno='1' )AS SC1
WHERE Student.Sno =SC1 .Sno;
需要说明的是,通过FROM子句生成派生表时,AS 关键字可以省略,但必须为派生关系指定一个别名。而对于基本表,别名是可选择项。
视图
建立视图
建立视图
SQL语言用CREATE VIEW
命令建立视图,其一般格式为
CREATE VIEW <视图名> [(<列名> [,<列名>] ...)]
AS<子查询>
[WITH CHECK OPTION] ;
其中,子查询可以是任意的SELECT
语句,是否可以含有ORDER BY
子句和DISTINCT
短语,则取决于具体系统的实现。
WITH CHECK OPTION
表示对视图进行UPDATE
、INSERT
和DELETE
操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。
组成视图的属性列名或者全部省略或者全部指定,没有第三种选择。如果省略了视图的各个属性列名,则隐含该视图由子查询中SELECT
子句目标列中的诸字段组成。但在下列三种情况下必须明确指定组成视图的所有列名:
- (1)某个目标列不是单纯的属性名,而是聚集函数或列表达式;
- (2)多表连接时选出了几个同名列作为视图的字段;
- (3)需要在视图中为某个列启用新的更合适的名字。
[例3.85]建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;
由于在定义IS_Student
视图时加上了WITH CHECK OPTION
子句,以后对该视图进行插入、修改和删除操作时,关系数据库管理系统会自动加上Sdept='IS'
的条件。
若一个视图是从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,但保留了主码,则称这类视图为行列子集视图。IS_Student
视图就是一个行列子集视图。
视图不仅可以建立在单个基本表上,也可以建立在多个基本表上。
删除视图
删除视图
该语句的格式为
DROP VIEW <视图名> [CASCADE] ;
视图删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图,则使用CASCADE
级联删除语句把该视图和由它导出的所有视图一起删除。
基本表删除后,由该基本表导出的所有视图均无法使用了,但是视图的定义没有从
字典中清除。删除这些视图定义需要显式地使用DROP VIEW
语句。
[例3.91]删除视图BT_S和视图IS_S1
DROP VIEW BT_S; /*成功执行*/
DROP VIEW IS_S1; /*拒绝执行*/
执行此语句时由于IS_S1
视图上还导出了IS_S2
视图,所以该语句被拒绝执行。如果确定要删除,则使用级联删除语句:
DROP VIEW IS SI CASCADE; /*删除了视图IS SI和由它导出的所有视图*/
修改表,表上的视图也会随之改变。修改视图则不会影响到表。
授权
授予(GRANT)
SQL中使用GRANT
和REVOKE
语句向用户授子或收回对数据的操作权限。GRANT
语句向用户授子权限,REVOKE
语句收回已经授予用户的权限。
GRANT
GRANT语句的一般格式为
GRANT <权限> [,<权限>]...
ON <对象类型><对象名> [,<对象类型> <对象名>]...
TO <用户> [,<用户)]...
[WITH GRANT OPTION];
其语义为:将对指定操作对象的指定操作权限授予指定的用户。发出该GRANT
语句的可以是数据库管理员,也可以是该数据库对象创建者(即属主owner),还可以是已经拥有该权限的用户。接受权限的用户可以是一个或多个具体用户,也可以是PUBLIC
,即全体用户。
如果指定了WITH GRANT OPTION
子句,则获得某种权限的用户还可以把这种权限再授予其他的用户。如果没有指定WITH GRANT OPTION
子句,则获得某种权限的用户只能使用该权限,不能传播该权限。
收回(REVOKE)
REVOKE
授予用户的权限可以由数据库管理员或其他授权者用REVOKE
语句收回,REVOKE
语句的一般格式为
REVOKE <权限>,<权限>...
ON <对象类型><对象名>[,<对象类型><对象名>]...
FROM <用户> [,<用户>]...[CASCADE|RESTRICT];
创建数据库模式的权限
创建数据库模式的权限
GRANT
和REVOKE
语句向用户授予或收回对数据的操作权限。对创建数据库模式一类的数据库对象的授权则由数据库管理员在创建用户时实现。
CREATE USER
语句一般格式如 下:
CREATE USER <username> [WITH] [DBA| RESOURCE |CONNECT];
对CREATE USER
语句说明如下:
- 只有系统的超级用户才有权创建一个新的数据库用户。
- 新创建的数据库用户有三种权限: CONNECT、RESOURCE和DBA.
-CREATEUSER命令中如果没有指定创建的新用户的权限,默认该用户拥有CONNECT权限。拥有CONNECT权限的用户不能创建新用户,不能创建模式,也不能创建基本表,只能登录数据库。由数据库管理员或其他用户授予他应有的权限,根据获得的授权情况他可以对数据库对象进行权限范围内的操作。 - 拥有RESOURCE权限的用户能创建基本表和视图,成为所创建对象的属主,但不能创建模式,不能创建新的用户。数据库对象的属主可以使用GRANT语句把该对象上的存取权限授予其他用户。
- 拥有DBA权限的用户是系统中的超级用户,可以创建新的用户、创建模式、创建基本表和视图等; DBA拥有对所有数据库对象的存取权限,还可以把这些权限授予一-般用户。
以上说明可以用表4.6来总结。
表4.6权限与可执行的操作对照表
注意: CREATE USER
语句不是SQL标准,因此不同的关系数据库管理系统的语法和内容相差甚远。这里介绍该语句的目的是说明对于数据库模式这一类数据对象也
有安全控制的需要,也是要授权的。
角色
角色的创建
1.角色的创建
创建角色的SQL语句格式是
CREATE ROLE <角色名>
刚刚创建的角色是空的,没有任何内容。可以用GRANT为角色授权。
角色授权
2.给角色授权
GRANT <权限> [,<权限1]...
ON<对象类型>对象名
TO <角色> [,<角色>]...
数据库管理员和用户可以利用GRANT
语句将权限授予某一个或几个角色。
将一个角色授子其他的角色或用户
3.将一个角色授子其他的角色或用户
GRANT<角色1> [,<角色2>]...
TO<角色3> [,<用户1>]...
[WITH ADMIN OPTION]
该语句把角色授予某用户,或授予另一个角色。这样,一个角色(例如角色3)所拥有的权限就是授予它的全部角色(例如角色1和角色2)所包含的权限的总和。
授予者或者是角色的创建者,或者拥有在这个角色上的ADMIN OPTION
。
如果指定了WITH ADMIN OPTION
子句,则获得某种权限的角色或用户还可以把这种权限再授予其他的角色。
一个 角色包含的权限包括直接授予这个角色的全部权限加上其他角色授予这个角色的全部权限。
4.角色权限的收回
4.角色权限的收回
REVOKE <权限> [,<权限>]...
ON<对象类型> <对象名>
FROM <角色> [,<角色>]...
用户可以收回角色的权限,从而修改角色拥有的权限。
REVOKE
动作的执行者或者是角色的创建者,或者拥有在这个(些)角色上的ADMIN OPTION
。
完整性约束
完整性约束命名子句
1.完整性约束命名子句
CONSTRAINT <完整性约束条件名> <完整性约束条件>
<完整性约束条件>包括NOT NULL
、UNIQUE
、PRIMARY KEY
、FOREIGN KEY
、CHECK
短语等。
[例5.10]建立学生登记表Student,要求学号在90000 99999之间,姓名不能取空值,年龄小于30,性别只能是“男”或“女”。
CREATE TABLE Student
(Sno NUMERIC(6)
CONSTRAINT CI CHECK (Sno BETWEEN 90000 AND 9999),
Sname CHAR(20)
CONSTRAINT C2 NOT NULL,
Sage NUMERIC(3)
CONSTRAINT C3 CHECK (Sage < 30),
Ssex CHAR(2)
CONSTRAINT C4 CHECK (Ssex IN('男','女')),
CONSTRAINT StudentKey PRIMARY KEY(Sno)
);
在Student表上建立了5个约束条件,包括主码约束( 命名为StudentKey)以及C1、C2、C3、C4这4个列级约束。
修改表中的完整性限制
2.修改表中的完整性限制
可以使用ALTER TABLE语句修改表中的完整性限制。
[例5.12]去掉例 5.10 Student表中对性别的限制。
ALTER TABLE Student
DROP CONSTRAINT C4;
[例5.13]修改表 Student 中的约束条件,要求学号改为在900 000~-999 999之间,年龄由小于30改为小于40。
可以先删除原来的约束条件,再增加新的约束条件。
ALTER TABLE Student
DROP CONSTRAINT CI;
ALTER TABLE Student
ADD CONSTRAINT CI CHECK (Sno BETWEEN 900000 AND 99999);
ALTER TABLE Student
DROP CONSTRAINT C3;
ALTER TABLE Student
ADD CONSTRAINT C3 CHECK (Sage < 40);
断言
创建断言的语句格式
1.创建断言的语句格式
CREATE ASSERTION <断言名> <CHECK子旬>
每个断言都被赋予一个名字,<CHECK 子句>
中的约束条件与WHERE子句的条件表达式类似。
[例5.18]限制数据库课程最多 60名学生选修。
CREATE ASSERTION ASSE_SC_DB_NUM
CHECK(60> =(SELECT count(*) /*此断 言的谓词涉及聚集操作count的SQL语旬*/
FROM Course,SC
WHERE SC.CNO=COURSE.CNO AND COURSE.CNAME = '数据库'
);
每当学生选修课程时,将在SC表中插入一条元组(Sno, Cno, NULL), ASSE_SC_DB_ NUM
断言被触发检查。如果选修数据库课程的人数已经超过60人,CHECK子句
返回值为“假”,对SC表的插入操作被拒绝。
[例5.19]限制每一 一门课程最多60名学生选修。
CREATE ASSERTION ASSE_SC_CNUMI
CHECK( 60>=ALL( SELECT count(*) /*此断言的谓词, 涉及聚集操作count */
FROM SC /*和分组函数group by的SQL语句*/
GROUP by cno )
);
触发器
创建触发器
SQL使用CREATE TRIGGER命令建立触发器,其一般格式为
CREATE TRIGGER <触发器名> /*每当触发事件发生时, 该触发器被激活*/
{BEFORE|AFTER} <触发事件> ON<表名> /*指明触发器激活的时间是在执行触发事件前或后*/
REFERENCING NEW|OLD ROW AS<变量> /*REFERENCING 指出引用的变量*/
FOR EACH{ROW | STATEMENT} /*定义触发器的类型,指明动作体执行的频率*/
[WHEN <触发条件>] <触发动作体> /*仅当触发条件为真时才执行触发动作体*/
语法说明
下面对定义触发器的各部分语法进行详细说明。
- (1)只有表的拥有者,即创建表的用户才可以在表上创建触发器,并且一个表上只能创建一定数量的触发器。触发器的具体数量由具体的关系数据库管理系统在设计时确定。
- (2)触发器名
触发器名可以包含模式名,也可以不包含模式名。同一模式下,触发器名必须是唯一的,并且触发器名和表名必须在同一模式下。 - (3)表名
触发器只能定义在基本表上,不能定义在视图上。当基本表的数据发生变化时,将激活定义在该表上相应触发事件的触发器,因此该表也称为触发器的目标表。 - (4)触发事件
触发事件可以是INSERT
、DELETE
或UPDATE
,也可以是这几个事件的组合,如INSERT OR DELETE
等,还可以是UPDATE OF <触发列,…>, 即进一步 指明修改哪些列时激活触发器。AFTER/BEFORE
是触发的时机。AFTER 表示在触发事件的操作执行之后激活触发器; BEFORE 表示在触发事件的操作执行之前激活触发器。 - (5)触发器类型
触发器按照所触发动作的间隔尺寸可以分为行级触发器(FOR EACH ROW
)和语句级触发器(FOR EACH STATEMENT
) - (6)触发条件
触发器被激活时,只有当触发条件为真时触发动作体才执行,否则触发动作体不执行。如果省略WHEN触发条件,则触发动作体在触发器激活后立即执行。 - (7)触发动作体
触发动作体既可以是一一个匿名PL/SQL过程块,也可以是对已创建存储过程的调用。如果是行级触发器,用户可以在过程体中使用NEW
和OLD
引UPDATE/INSERT
事件之后的新值和UPDATE/DELETE
事件之前的旧值:如果是语句级触发器,则不能在触发动作体中使用NEW
或OLD
进行引用。
如果触发动作体执行失败,激活触发器的事件( 即对数据库的增、删、改操作)就会终止执行,触发器的目标表或触发器可能影响的其他对象不发生任何变化。
案例1
[例5.21] 当对表SC
的Grade
属性进行修改时,若分数增加了10%, 则将此次操作记录到另一个表SC_U
(Sno、 Cno、Oldgrade、 Newgrade)
中,其中Oldgrade
是修改前的分数,Newgrade
是修改后的分数。
CREATE TRIGGER SC_T /*SC_T是触发器的名字*/
AFTER UPDATE OF Grade ON SC /*UPDATE OF Grade ONSC是触发事件,*/
/* AFTER是触发的时机,表示当对SC的Grade属性修改完后再触发下面的规则*/
REFERENCING
OLDROW AS OldTuple,
NEWROW AS NewTuple
FOR EACH ROW /*行级触发器,即每执行一次Grade的更新,下面的规则就执行一次*/
WHEN (NewTuple.Grade>= 1.1 * OldTuple.Grade) /*触发条件, 只有该条件为真时才执行*/
INSERT INTO SC_U (Sno,Cno,OldGrade,NewGrade) /* 下面的insert操作*/
VALUES(OIdTuple.Sno,OldTuple.Cno,OldTuple .Grade,NewTuple.Grade)
在本例中REFERENCING
指出引用的变量,如果触发事件是UPDATE
操作并且有FOR EACH ROW
子句,则可以引用的变量有OLDROW
和NEWROW
,分别表示修改之前的元组和修改之后的元组。若没有FOR EACH ROW
子句,则可以引用的变量有OLDTABLE
和NEWTABLE
, OLDTABLE
表示表中原来的内容,NEWTABLE
表示表中变化后的部分。
案例2
[例5.22] 将每次对表Student 的插入操作所增加的学生个数记录到表Student-InsertLog
中。
CREATE TRIGGER Student Count
AFTER INSERT ON Student /*指明触发器激活的时间是在执行INSERT后*/
REFERENCING
NEW TABLE AS DELTA
FOR EACH STATEMENT /*语句级触发器,即执行完INSERT语句后下面的触发动作体才执行一次*/
INSERT INTO StudentInsertLog (Numbers)
SELECT COUNT(*) FROM DELTA
在本例中出现的FOR EACH STATEMENT
,表示触发事件INSERT
语句执行完成后才执行一次触发器中的动作,这种触发器叫做语句级触发器。而例5.21中的触发器是行级触发器。默认的触发器是语句级触发器。DELTA
是一个关系名,其模式与Student
相同,包含的元组是INSERT
语句增加的元组。
案例3
[例5.23] 定义一个BEFORE行级触发器,为教师表Teacher 定义完整性规则“教授的工资不得低于4000元,如果低于4000元,自动改为4000元”。
CREATE TRIGGER Insert Or_Update_Sal /*对教师表插入或更新时激活触发器*/
BEFORE INSERT OR UPDATE ON Teacher /*BEFORE触发事件*/
REFERENCING NEW row AS newTuple
FOR EACH ROW /*这是行级触发器*/
BEGIN /*定义触发动作体,这是一个PLSQL过程块*/
IF (newtuple.Job='教授') AND (newtuple.Sal < 4000) /*因为是行级触发器,可在过程体中*/
THEN newtuple.Sal :=4000; /*使用插入或更新操作后的新值*/
END IF;
END; /*触发动作体结束*/
因为定义的是BEFORE
触发器,在插入和更新教师记录前就可以按照触发器的规则调整教授的工资,不必等插入后再检查再调整。
删除触发器
删除触发器
删除触发器的SQL语法如下:
DROP TRIGGER <触发器名> ON<表名>;
触发器必须是一个已经创建的触发器,并且只能由具有相应权限的用户删除。
触发器是一种功能强大的工具,但在使用时要慎重,因为在每次访问一个表时都可能触发一个触发器,这样会影响系统的性能。
存储过程的用户接口
用户通过下面的SQL语句创建、执行、修改和删除存储过程。
创建存储过程
(1)创建存储过程
CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2...])) /*存储过程 首部*/
AS<过程化SQL块>;/* 存储过程体,描述该存储过程的操作*/
存储过程包括过程首部和过程体。在过程首部,“过程名”是数据库服务器合法的对象标识:参数列表[参数1,参数2, …]用名字来标识调用时给出的参数值,必须指定值的数据类型。可以定义输入参数、输出参数或输入/输出参数,默认为输入参数,也可以无参数。
过程体是一个<过程化SQL块>,包括声明部分和可执行语句部分。<过程化SQL块>的基本结构已经在8.2节中讲解了。
执行存储过程
(2)执行存储过程
CALL/PERFORM PROCEDURE过程名([参数1,参数2.....]);
使用CALL
或者PERFORM
等方式激活存储过程的执行。在过程化SQL中,数据库服务器支持在过程体中调用其他存储过程。
[例8.9]从账户 01003815868转1000元到01003813828账户中。
CALL PROCEDURE TRANSFER(01003815868, 01003813828, 10000);
修改存储过程
(3)修改存储过程
可以使用ALTER PROCEDURE
重命名一个存储过程:
ALTER PROCEDURE 过程名1 RENAME TO 过程名2;
可以使用ALTER PROCEDURE
重新编译一个存储过程:
ALTER PROCEDURE 过程名 COMPILE;
删除存储过程
(4)删除存储过程
DROP PROCEDURE 过程名();
函数
本章讲解的函数也称为自定义函数,因为是用户自己使用过程化SQL设计定义的。函数和存储过程类似,都是持久性存储模块。函数的定义和存储过程也类似,不同之处是函数必须指定返回的类型。
函数的定义语句格式
1.函数的定义语句格式
CREATE OR REPLACE FUNCTION 函数名([参数1,参数2...]) RETURNS <类型>
AS <过程化SQL块>;
函数的执行语句格式
2.函数的执行语句格式
CALL/SELECT 函数名([参数1,参数2...]);
修改函数
3.修改函数
可以使用ALTER FUNCTION
重命名一个自定义函数:
ALTER FUNCTION过程名1 RENAME TO 过程名2;
可以使用ALTER FUNCTION重新编译-个函数:
ALTER FUNCTION 函数名 COMPILE;
由于函数的概念与存储过程类似,这里就不再赘述了。有关的示例可参考本章参考文
献[4]。
游标
不用游标的 SQL语句
有的嵌入式SQL语句不需要使用游标,它们是说明性语句、数据定义语句、数据控制语句、查询结果为单记录的SELECT
语句、非CURRENT
形式的增删改语旬。
查询结果为单记录的SELECT语句
1.查询结果为单记录的SELECT语句
这类语句因为查询结果只有一个,只需用INTO
子句指定存放查询结果的主变量,不需要使用游标。
[例8.2]根据学生 号码查询学生信息。
EXEC SQL SELECT Sno,Sname,Ssex,Sage,Sdept
INTO :Hsno,:Hname,:Hsex,:Hage,:Hdept
FROM Student
WHERE Sno=:givensno; /*把要 查询的学生的学号赋给了主变量givensno */
使用查询结果为单记录的SELECT
语句需要注意以下几点:
- (1)
INTO
子句、WHERE
子句和HAVING
短语的条件表达式中均可以使用主变量。 - (2)查询结果为空值的处理。查询返回的记录中可能某些列为空值
NULL
.为了表示空值,在INTO
子句的主变量后面跟有指示变量,当查询得出的某个数据项为空值时,系统会自动将相应主变量后面的指示变量置为负值,而不再向该主变量赋值。所以当指示变量值为负值时,不管主变量为何值,均认为主变量值为NULL
. - (3)如果查询结果实际上并不是单条记录,而是多条记录,则程序出错,关系数据库管理系统会在SQL通信区中返回错误信息。
[例8.3] 查询某个学生选修某门课程的成绩。假设已经把将要查询的学生的学号赋给了主变量givensno
,将课程号赋给了主变量givencno
.
EXEC SQL SELECT Sno,Cno,Grade
INTO :Hsno, :Hcno, :Hgrade:Gradeid /*指示变量Gradeid*/
FROM SC
WHERE Sno=:givensno AND Cno=:givencno;
如果Gradeid
<0,则不论Hgrade
为何值均认为该学生成绩为空值。
非CURRENT形式的增删改语句
2.非CURRENT形式的增删改语句
有些非CURRENT
形式的增删改语句不需要使用游标。在UPDATE
的SET
子句和WHERE
子句中可以使用主变量,SET
子句还可以使用指示变量。
[例8.4]修改某个学生选修 1号课程的成绩。
EXEC SQL UPDATE SC
SET Grade=:cnewgrade /*修改的成绩已赋给主变量: newgrade */
WHERE Sno=:givensno and Cno=l; /*学号已赋给主变量: givensno*/
[例8.5]某个学生新选修了某门课程,将有关记录插入sc
表中。假设插入的学号已赋给主变量stdno
,课程号已赋给主变量couno
,由于该学生刚选修课程,成绩应为空,所以要把指示变量赋为负值。
gradeid=-1; /*gradeid 为指示变量,赋为负值*/
EXEC SQL INSERT
INTO SC(Sno,Cno,Grade)
VALUES(:stdno,:couno,:gr:gradeid);/*:stdno. couno, :gr 为主变量*/
使用游标的SQL语句
必须使用游标的SQL语句有查询结果为多条记录的SELECT
语句、CURRENT
形式的UPDATE
和DELETE
语句。
查询结果为多条记录的SELECT语句
- 查询结果为多条记录的
SELECT
语句般情况下,SELECT
语句查询结果是 多条记录, 因此需要用游标机制将 多条记录次一条地送主程序处理,从而把对集合的操作转换为对单个记录的处理。使用游标的步骤为:
说明游标
(1)说明游标
用DECLARE
语句为一条SELECT
语句定义游标:
EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT 语句>;
定义游标仅仅是一条说明性语句,这时关系数据库管理系统并不执行SELECT语句。
打开游标
(2)打开游标
用OPEN
语句将定义的游标打开。
EXEC SQL OPEN <游标名>;
打开游标实际上是执行相应的SELECT
语句,把查询结果取到缓冲区中。这时游标处于活动状态,指针指向查询结果集中的第一条记录。
推进游标指针并取当前记录
(3)推进游标指针并取当前记录
EXEC SQL FETCH <游标名>
INTO<主变量>[<指示变量>][<主变量>[<指示变量>]]...;
其中主变量必须与SELECT
语句中的目标列表达式具有一一对应关系。
用FETCH
语句把游标指针向前推进一条记录,同时将缓冲区中的当前记录取出来送至主变量供主语言进一步处理。通过循环执行FETCH
语句逐条取出结果集中的行进
行处理。
关闭游标
(4)关闭游标
用CLOSE
语句关闭游标,释放结果集占用的缓冲区及其他资源。
EXEC SQL CLOSE <游标名>;
游标被关闭后就不再和原来的查询结果集相联系。但被关闭的游标可以再次被打开,与新的查询结果相联系。
CURRENT形式的UPDATE和DELETE语句
- CURRENT形式的UPDATE和DELETE语句
UPDATE
语句和DELETE
语句都是集合操作,如果只想修改或删除其中某个记录,则需要用带游标的SELECT
语句查出所有满足条件的记录,从中进一步找出要修改或删除的记录,然后用CURRENT
形式的UPDATE
和DELETE
语句修改或删除之。即UPDATE
语句和DELETE
语句中要用子句:
WHERE CURRENTOF <游标名>
来表示修改或删除的是最近一次取出的记录, 即游标指针指向的记录。
[例8.1]中的UPDATE就是用CURRENT形式的。
注意:当游标定义中的SELECT
语句带有UNION
或ORDER BY
子句,或者该SELECT
语句相当于定义了一个不可更新的视图时,不能使用CURRENT
形式的UPDATE
语句和DELETE
语句。
动态SQL
前面所讲的嵌入式SQL语句中使用的主变量,查询目标列、条件等都是固定的,属于静态SQL语句。静态嵌入式SQL语句能够满足一般要求,但某些应用可能要到执行时才能够确定要提交的SQL语句、查询的条件,此时就要使用动态SQL语句来解决这类问题。动态SQL方法允许在程序运行过程中临时“组装”SQL语句。动态SQL支持动态组装SQL语句和动态参数两种形式,给开发者提供设计任意SQL语句的能力。
使用SQL语句主变量
1.使用SQL语句主变量
程序主变量包含的内容是SQL语句的内容,而不是原来保存数据的输入或输出变量,这样的变量称为SQL语句主变量。SQL语句主变量在程序执行期问可以设定不同的SQL语句,然后立即执行。
[例8.6]创建基本表 TEST.
EXEC SQL BEGIN DECLARE SECTION:
const char *stmt="CREATE TABLE test(a int);";
/*SQL语句主变量,内容是创建表的SQL语句*/
EXEC SQL END DECLARE SECTION;
...
EXEC SQL EXECUTE IMMEDIATE :stmt;/*执行动态 SQL语句*/
动态参数
2.动态参数
动态参数是SQL语句中的可变元素,使用参数符号(?)表示该位置的数据在运行时设定。和前面使用的主变量不同,动态参数的输入不是编译时完成绑定,而是通过PREPARE
语句准备主变量和执行语句EXECUTE
绑定数据或主变量来完成。使用动态参数的步骤如下:
(1)声明SQL语句主变量
SQL语句主变量的值包含动态参数(?)。
(2)准备SQL语句(PREPARE)
PREPARE
将分析含主变量的SQL语句内容,建立语句中包含的动态参数的内部描述符,并用<语句名>标识它们的整体。
EXEC SQL PREPARE <语句名> FROM <SQL语句主变量>;
执行准备好的语句(EXECUTE)
3.执行准备好的语句(EXECUTE)
EXECUTE
将SQL语句中分析出的动态参数和主变量或数据常量绑定,作为语句的输入或输出变量。
EXEC SQL EXECUTE <语旬名> (INTO<主变量表>][USING<主变量或常量>];
[例8.7]向TEST中插入元组。
EXEC SQL BEGIN DECLARE SECTION;
const char *stmt="INSERT INTO TEST VALUES(?);";
/*声明SQL主变量内容是INSERT语句*/
EXEC SQL END DECLARE SECTION;
...
EXEC SQL PREPARE mystmt FROM :stmt;/*准备语句*/
EXEC SQL EXECUTE mystmt USING 100; /*执行语句,设定INSERT语句插入值100*/
EXEC SQL EXECUTE mystmt USING 200;/*执行语句,设定INSERT语句拯入值200*/
总结
本人调剂复试收集的资料,现将整理为电子档,复试笔试取得不错成绩,希望帮助更多人。