关系数据库标准语言SQL（二）

声明：最近在准备考试，故整理数据库原理笔记。

视图

视图的创建和删除

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;