声明:最近在准备考试,故整理数据库原理笔记。
视图
视图的创建和删除
CREATE VIEW <视图名 > [ <列名 > , ……, <列名 >)] AS <查询表达式 >
[WITH CHECK OPTION]
视图名 对定义的视图命名
列名 为 查询表达式 结果的诸列命名
查询表达式 通常是一个 SELECT 查询
WITH CHECK OPTION 表示该视图是可更新的,并且对视图进行更新时要满足 <查询表达式> 的查询条件
行列子集视图
CREATE
VIEW SE_Students
AS
SELECT Sno, Sname, Sex, Birthday, Dno
FROM Students
WHERE Speciality = ‘软件工程'
WITH CHECK OPTION;
基于多个表的视图
CREATE VIEW Student_Grades (Sno , Sname , Cname),
AS SELECT S .Sno , Sname , Cname , Grade
FROM Students S , SC, Courses C
WHERE S .Sno = SC.Sno AND C .Cno = SC.Cno ;
基于视图的视图
CREATE VIEW CS_Student_Grades (Sno, Sname, Cname, Grade)
AS SELECT S.Sno, Sname, Cname, Grade
FROM Students S, Student_Grades SG
WHERE S.Sno = SG.Sno AND Speciality = ‘计算机科学与技术’;
基于聚集函数的视图
CREATE VIEW Student_Avg_Grades (Sno, Sname, Avg_Grade)
AS SELECT S.Sno, Sname, AVG (Grade)
FROM Students S, SC
WHERE S.Sno=SC.Sno
GROUP BY S.Sno, Sname;
视图删除
DROP VIEW Student_Grades或
DROP VIEW Student_Grades RESTRICT
不能删除视图Student_Grades,因为视图CS_Student_Grades的定义依 赖于它
DROP VIEW Student_Grades CASCADE
将删除视图Student_Grades,并且级联地删除视图CS_Student_Grades
基于视图的查询
查询软件工程专业的男生
SELECT *
FROM SE_Students
WHERE Sex= ‘男’;
等价于
SELECT *
FROM (SELECT Sno, Sname, Sex, Birthday, Dno
FROM Students
WHERE Speciality = ‘软件工程’)
AS SE_Students (Sno, Sname, Sex, Birthday, Dno)
WHERE Sex= ‘男’;
基于视图的更新
向软件工程专业学生的视图SE_Students中插入一个新的记录,学号为201805109,姓名为吴畅,出生年月1999-05-04,女性,所在院系EI
INSERT INTO SE_Students (Sno, Sname, Birthday, Sex, Dno)
VALUES (‘201805109’, ‘吴畅’, 1999-05-04, ‘女’, ‘EI’);
等价于
INSERT INTO Students (Sno, Sname, Birthday, Sex, Dno, Speciality)
VALUES (‘201805109’, ‘吴畅’, 1999-05-04, ‘女’, ‘EI’, ‘软件工程’);
删除软件工程专业学号为201805201的学生
DELETE FROM SE_Students
WHERE Sno=‘201805201’;
等价于:
DELETE FROM Students
WHERE Sno=‘201805201’ AND Speciality = ‘软件工程’;
不可更新视图
视图S_G为不可更新视图
CREATE VIEW S_G (Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
视图的作用
使用视图可以 使一些查询表 达更加简洁
视图提供了一 定程度的逻辑 独立性
视图可以起到 安全保护作用
视图使得用户 能够以不同角 度看待相同的 数据
嵌入式SQL
为什么使用嵌入式SQL
SQL是一种非过程语言,用SQL语言表达查询比用通用的程序设计语言编码简单得多
我们在开发数据库应用系统时,很多时候需要使用通用程序设计语言访问数据库:
一些非数据库操作,如打印报表、将查询结果送到图形用户界面中,都不能用SQL语句实现
SQL能够表达常见的查询,但是不能表达所有查询
SQL语句的识别与处理
当主语言源程序中嵌入SQL语句时,这种源程序已经不是纯的主语言源程序,通常的主语言编译系统不能处理这种源程序
解决这一问题的方法有两种:
(1) 扩充主语言编译系统,使之能处理SQL语句
(2) 预处理:在编译前先扫描源程序,将SQL语句翻译成目标代码
通常,商品化DBMS采用预处理方法,预处理程序由DBMS开发商提供
例如,微软的SQL Server 2000 提供的预处理程序nsqlprep.exe
为了能够区分源程序中的SQL语句和主语言语句,SQL规定:
所有嵌入式SQL语句都必须加前缀EXEC SQL
SQL语句的结束标志则因主语言的不同而异
当主语言是C语言时,嵌入式SQL语句的一般形式为:
EXEC SQL <SQL语句>;
与主语言通信
SQLCODE
每个SQL语句执行执行之后需要反馈一些状态信息,系统将这些状态信息存入SQLCODE中 主语言语句可以访问SQLCODE,根据结果采取相应的动作
SQLCODE是一个整型变量
如果SQL语句成功执行,则SQLCODE=0
如果执行结果无数据则SQLCODE=100
其他情况为异常,SQLCODE取负值,其具体值依赖于实现
主语言
SQL语句与主语言语句交换信息的另一种途径是使用主语言变量
使用如下形式说明的主语言变量在主语言语句和SQL语句都能使用:
EXEC SQL BEGIN DECLARE SECTION;
主语言变量说明;
EXEC SQL END DECLARE SECTION;
SQL语句与主语言语句可以通过主语言变量交换信息
SQL规定:SQL语句中出现的主语言变量之前必须加冒号(:)
指示变量
SQL支持空值(NULL),但是主语言并无对应概念
为了解决这一问题,嵌入式SQL引进了指示变量(indicator variable)
指示变量是主语言的整型变量
每个可能被SQL语句赋予空值的主变量都可以后随一个指示变量,用来指示对应的主变量是否为空值
在SQL语句执行结束时,如果指示变量的值小于0,则其对应的主变量并未被赋值(可以视为空值)
游标
为什么需要游标
主语言是面向记录的,而SQL是面向集合
存在矛盾:一个SQL语句得到的结果可能是多个记录,而主语言没有办法一次处理多个记录
解决该问题的方法是使用游标
游标其实就是一个数据缓冲区,暂时存放SQL语句的执行结果
使用游标需要预先说明游标,在使用前打开游标,通过专门的SQL语句逐一提取记录,并在使用完之后关闭游标
连接数据库
访问数据库前必须先建立数据库连接。SQL提供了建立和关闭数据库连接语句
建立数据库连接的语句形式为:EXEC SQL CONNECT TO <SQL服务器>[AS <连接名>] [USER <用户名>];
建立到当前服务器的默认连接:EXEC SQL CONNECT TO DEFAULT;
关闭数据库连接的语句形式为:EXEC SQL DISCONNECTION <连接名>;
不使用游标的SQL语句
查询结果为单个记录的SELECT语句
使用带INTO子句的SELECT语句将查询结果存放到主变量中
EXEC SQL SELECT <选择序列>
INTO <选择目标序列>
<其他子句>
<选择目标序列>和<选择序列>包含相同个数的元素
<选择目标序列>中的每个元素形如:
:<主变量> [:<指示变量>]
SQL执行有两种情况
查询不成功:SQLCODE 0, 除了I/O错误之外,有两种情况导致查询不成功
查询结果实际上并不是单条记录,而是多条记录
满足查询条件的元组不存在
查询成功:SQLCODE = 0
EXEC SQL SELECT Grade
INTO :Hgrade :igrade
FROM SC
WHERE Sno = :Hsno AND Cno = :Hcno;
非交互式更新
某学生退学,需要删除他/她在Students中的登记和他/她的所有选课记录。假设该学生的学号已经赋予主变量Hsno,以下两个语句可
以完成删除工作:
EXEC SQL DELET FROM SC
WHERE Sno=:Hsno;
EXEC SQL DELET FROM Students
WHERE Sno=:Hsno;
假设信工院全体学生的专业改为软件工程,软件工程已经赋予主变量Hspeciality。使用下面的程序可以修改学生的专业:
EXEC SQL UPDATE Students
SET Speciality=:Hspeciality
WHERE Dno=‘IE’;
使用游标的SQL语句
查询结果为多个元组的SELECT语句
当查询结果为多个记录需要提交主语言程序处理时,可以查询语句说明一个只读游标
打开游标就导致查询语句的执行
主语言程序可以使用FETCH语句推进游标指针,取出每个查询结果进行处理
最后,关闭游标
查询某个院全体学生的信息(学号、姓名、性别和年龄),要查询的院系号由用户在程序运行过程中指定, 放在主变量Hdno中 对应的主变量定义如下:
EXEC SQL BEGIN DECLARE SECTION;
char Hsno[9];
char Hsname[8];
char Hsex[2];
int Hage;
char Hdno[10];
EXEC SQL END DECLARE SECTION;
实现该查询的程序段:
EXEC SQL DECLARE YX CURSOR FOR
SELECT Sno, Sname, Ssex, Sage
FROM Students
WHERE Dno=:Hdno;
EXEC SQL OPEN YX
Hdno=’IE’;
WHILE(1) /* 用循环结构逐条处理结果集中的记录 */
{
EXEC SQL FETCH YX
INTO : HSno, : HSname, : HSsex, : HSage;
if (sqlca.sqlcode <> 0) break;
…… /* 由主语言语句进行进一步处理 */
}
EXEC SQL CLOSE YX;
Current形式的更新语句
带CURRENT形式的UPDATE语句格式如下:
UPDATE T
SET A1 = e1, …, Ak = ek
WHERE CURRENT OF <游标名>
其中T是基本表,游标定义在基本表T上,并且是可更新的
A1, …, Ak是T的属性,而e1, …, ek是表达式
从Students和SC中删除某学生的记录的程序段如下:
char YN; // 变量YN不在SQL语句中使用
EXEC SQL BEGIN DECLARE SECTION;
char Givenname[8];
char Hsno[9];
char Hsname[8];
char Hsex[2];
char Henrollyear[4];
char Hspeciality[20];
char Hdno[4];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE Student_Cursor CURSOR FOR
SELECT Sno, Sname, Sex, Enrollyear, Speciality, Dno
FROM Students
WHER Sname=:Givenname
FOR UPDATE;
Givenname= ‘李明’;
EXEC SQL OPEN Student_Cursor;
EXEC SQL FETCH Student_Cursor
INTO :Hsno, :Hsname, :Hsex, :Henrollyear, Hspeciality, :Hdno;
while (SQLCODE= 0) {
printf (‘%s %s %s %s %s %s \n’, Hsno, Hsname, Hsex, Henrollyear, Hspeciality, Hdno);/* 是否删除 */
printf("delete?(Y—delete,N—not delete): ");
scanf("%c\n",&YN);
if (YN=='y' || YN=='Y') {/* 删除SC元组,不用游标 */
EXEC SQL DELETE FROM SC
WHERE Sno=:Hsno;/* 删除Students元组,使用游标 */
EXEC SQL DELETE FROM Students
WHERE CURRENT OF Student_Cursor;/* 完成,退出 */
break;
}
/* 取出下一位学生的信息 */
EXEC SQL FETCH Student_Cursor
INTO :Hsno, :Hsname, :Hsex, :Henrollyear, Hspeciality, :Hdno;
}
/* 关闭游标 */
EXEC SQL CLOSE Student_ Cursor;