SQL语言与编程二

5.使用[NOT] NULL关键字进行查询
使用NULL和NOT NULL关键字用于查询某一字段值为空或不空的记录.
例2-42 假设在"教工登记表"中插入一条记录:
JCB001 汪洋 男 27 1 NULL 500 基础部
再查询"教工登记表"中职称列不为空的记录.
SELECT *
FROM 教工登记表
WHERE 职称 IS NOT NULL;
则查询结果为:
教师编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JCB002 张扬 女 29 0 讲师 900 基础部
JGX001 王冠 男 33 1 讲师 900 经管系
JGX002 刘柳 女 39 1 副教授 1000 经管系
JGX003 王芝环 女 25 0 助教 500 经管系
JSJ001 江河 男 31 1 讲师 980 计算机系
JSJ002 张大伟 男 25 0 助教 660 计算机系
例2-43查询"教工登记表"中职称列为空的记录.
SELECT *
FROM 教工登记表
WHERE 职称 IS NULL
则查询结果为:
教师编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JCB001 汪洋 男 27 1 NULL 500 基础部
2.4.4 分组查询
使用GROUP BY子句,可将查询结果按GROUP BY子句中的分组,在这些列上,值相同的记录分为一组,然后分别计算库函数的值.
语法格式:
SELECT [ALL|DISTINCT]
FROM
[WHERE ]
GROUP BY [HAVING ]
说明:
(1) 一般当中有库函数时,才使用GROUP BY子句.
(2) 当使用了GROUP BY子句时,SELECT子句的中就只能出现库函数和GROUP BY子句中中的分组字段.
(3) 当使用HAVING 子句时,将对GROUP BY子句分组查询的结果进行进一步的筛选.
例2-44 分别查询"教工登记表"中各种职称的基本工资总和.
SELECT 职称,SUM(基本工资) AS 基本工资总和
FROM 教工登记表
WHERE 职称 IS NOT NULL
GROUP BY 职称;
则查询结果为:
职称 基本工资总和
副教授 1000.00
讲师 2780.00
助教 1160.00
例2-45 查询"教工登记表"中各种职称的总人数.
SELECT 职称,COUNT(职称) AS 总人数
FROM 教工登记表
WHERE 职称 IS NOT NULL
GROUP BY 职称;
则查询结果为:
职称 总人数
副教授 1
讲师 3
助教 2
例2-46 查询"教工登记表"中各种职称的平均工资大于800的记录.
SELECT 职称,AVG(基本工资) AS 平均工资
FROM 教工登记表
WHERE 职称 IS NOT NULL
GROUP BY 职称
HAVING AVG(基本工资)>800;
则查询结果为:
职称 平均工资
副教授 1000.000000
讲师 926.666666
该查询中,由于助工的平均工资不大于800,被HAVING子句筛去.
注意:WHERE子句和HAVING子句都是用于筛选记录,但用法不同,WHERE子句用于在GROUP BY子句使用之前筛选记录,而HAVING子句用于在GROUP BY子句使用之后筛选记录.
2.4.5 查询结果排序
使用ORDER BY子句,可将查询结果按指定的列进行排序.
语法格式:
SELECT [ALL|DISTINCT]
FROM
[WHERE ]
[GROUP BY ][HAVING ]
ORDER BY
说明:
(1) 使用ASC关键字表示升序排序,使用DESC关键字表示降序排序,默认为升序排序.
(2) ORDER BY子句后有多个列名时,各列名用逗号隔开,先依据第一个列名排序,在此列上值相同,再按第二个列名排序,依此类推.
(3) ORDER BY子句必须是SELECT语句中的最后一个子句.
例2-47 查询"教工登记表"中各记录,并将查询结果按职称排序.
SELECT *
FROM 教工登记表
ORDER BY 职称;
则查询结果为:
教工编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JCB001 汪洋 男 27 1 NULL 500.00 基础部
JGX002 刘柳 女 39 1 副教授 1000.00 经管系
JGX001 王冠 男 33 1 讲师 900.00 经管系
JCB002 张扬 女 29 0 讲师 900.00 基础部
JSJ001 江河 男 31 1 讲师 980.00 计算机系
JGX003 王芝环 女 25 0 助教 500.00 经管系
JSJ002 张大伟 男 25 0 助教 660.00 计算机系

例2-48 查询"教工登记表"中各记录,并将查询结果按职称排序,职称相同的记录按基本工资降序排序.
SELECT *
FROM 教工登记表
ORDER BY 职称,基本工资 DESC;
则查询结果为:
教工编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JCB001 汪洋 男 27 1 NULL 500.00 基础部
JGX002 刘柳 女 39 1 副教授 1000.00 经管系
JSJ001 江河 男 31 1 讲师 980.00 计算机系
JGX001 王冠 男 33 1 讲师 900.00 经管系
JCB002 张扬 女 29 0 讲师 900.00 基础部
JSJ002 张大伟 男 25 0 助教 660.00 计算机系
JGX003 王芝环 女 25 0 助教 500.00 经管系
注意:ORDER BY子句的作用,只是将查询结果排序,基本表并没有按此要求排序.
2.4.6 连接查询
在数据库的实际应用中,往往需要查询许多数据,有可能这些数据出现在两个或两个以上的表中,而我们希望这些数据出现在一个结果集中,这就要用到连接查询.
连接查询包括以下几种类型:
1.等值连接与非等值连接
是最常用的连接查询方法.等值连接与非等值连接是通过两个表(关系)中具有共同性质的列(属性)的比较,将两个表(关系)中满足比较条件的记录组合起来作为查询结果.
语法格式:
SELECT
FROM 表1,表2
WHERE 表1.列1 表2.列2
其中比较运算符可以是:=,>,=,<=,等.
说明:
(1)连接的列(属性)名可不相同,但数据类型必须兼容.
(2)当是"="时,称等值连接,否则为非等值连接.
例2-49 查询每个部门教工的实发工资的信息.
SELECT 教工登记表.姓名,部门,实发工资
FROM 教工登记表,教工工资表
WHERE 教工登记表.姓名=教工工资表.姓名;
则查询结果为:
姓名 部门 实发工资
张扬 基础部 1320.00
王冠 经管系 1300.00
刘柳 经管系 1740.00
王芝环 经管系 800.00
江河 计算机系 1430.00
张大伟 计算机系 1090.00
该例中,"姓名"列同时出现在两个表中,应具体指定选择哪个表的"姓名"列,在等值连接中,去掉目标列的重复属性,即为自然连接.
2.自身连接
即在同一个表中进行连接.自身连接可以看作一张表的两个副本之间进行的连接.在自身连接中,必须为表指定两个别名,使之在逻辑上成为两张表.
例2-50 在教工登记表中增加一列"负责人",按自连接查询全体教工的负责人姓名及负责人的编号信息.
SELECT A.姓名, B.负责人,B.教师编号 AS 负责人编号
FROM 教工登记表 A,教工登记表 B
WHERE B.姓名=A.负责人
则查询结果为:
姓名 负责人 负责人编号
汪洋 张扬 JCB002
张扬 张扬 JCB002
王冠 刘柳 JGX002
刘柳 刘柳 JGX002
王芝环 江河 JSJ001
江河 江河 JSJ001
张大伟 江河 JSJ001
2.4.7 嵌套查询
指在一个外层查询中包含另一个内层查询,即在一个SELECT语句中的WHERE子句中,包含有另一个SELECT语句,外层的查询称主查询,WHERE子句中包含的SELECT语句被称为子查询.一般将子查询的查询结果作为主查询的查询条件.使用嵌套查询,可完成复杂的查询操作.
1.使用IN关键字
语法格式:WHERE 表达式 [NOT] IN(子查询)
说明: IN表示属于,即若表达式的值属于子查询返回的结果集中的值,则满足查询条件.而NOT IN则表示不属于.
例2-51 查询教工登记表中实发工资大于800的教工的记录.
SELECT *
FROM 教工登记表
WHERE 姓名 IN(SELECT 姓名 FROM 教工工资表 WHERE 实发工资>800);
则查询结果为:
教师编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JCB002 张扬 女 29 0 讲师 900.00 基础部
JGX001 王冠 男 33 1 讲师 900.00 经管系
JGX002 刘柳 女 39 1 副教授 1000.00 经管系
JSJ001 江河 男 31 1 讲师 980.00 计算机系
JSJ002 张大伟 男 25 0 助教 660.00 计算机系
例2-52 查询教工登记表中实发工资不大于800的教工的记录.
SELECT *
FROM 教工登记表
WHERE 姓名 NOT IN(SELECT 姓名 FROM 教工工资表 WHERE 实发工资>800);
则查询结果为:
教师编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JCB001 汪洋 男 27 1 NULL 500.00 基础部
JGX003 王芝环 女 25 0 助教 500.00 经管系
2.使用比较运算符
语法格式:WHERE表达式 比较运算符 [ANY|ALL](子查询)
说明:
(1)比较运算符包括:>(大于),=(大于等于),<=(小于等于),=(等于),(不等于)
(2)ANY关键字表示任何一个(其中之一),只要与子查询中一个值符相合即可;ALL关键字表示所有(全部),要求与子查询中的所有值相符合.
例2-53 查询岗位补贴在400至800之间的教工的信息.
SELECT *
FROM 教工登记表
WHERE 姓名=ANY(SELECT 姓名 FROM 教工工资表 WHERE 岗位补贴>=400 AND 岗位补贴=400 AND 岗位补贴<=800);
执行后则无查询结果显示,因为子查询结果有多个值,而外部查询中的一个姓名值不可能对应于子查询的多个姓名值,因而无查询结果.
3.使用BETWEEN关键字
语法格式:
WHERE 表达式1 [NOT] BETWEEN(子查询)AND 表达式2
或:
WHERE 表达式1 [NOT] BETWEEN 表达式2 AND(子查询)
说明:使用BETWEEN关键字,则查询条件是表达式1的值必须介于子查询结果值与表达式2值之间.而使用NOT BETWEEN关键字则正好相反.
例2-54 查询年龄介于教工"汪洋"的年龄和30岁之间的教工的记录.
SELECT *
FROM 教工登记表
WHERE 年龄 BETWEEN
(SELECT 年龄 FROM 教工登记表 WHERE 姓名='汪洋') AND 30;
则查询结果为:
教师编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JCB001 汪洋 男 27 1 NULL 500.00 基础部
JCB002 张扬 女 29 0 讲师 900.00 基础部
4.使用EXISTS关键字
语法格式:WHERE [NOT] EXISTS(子查询)
说明:EXISTS关键字表示存在量词,带有EXISTS关键字的子查询不返回任何数据,只返回逻辑真值和逻辑假值,当子查询的结果不为空集时,返回逻辑真值,否则返回逻辑假值.NOT EXISTS则与EXISTS查询结果相反.
例2-55 查询"学生干部登记表"(见表2-1)中各班主任的编号,姓名,部门信息.
SELECT 教师编号,姓名,部门
FROM 教工登记表 A
WHERE EXISTS(SELECT * FROM 学生干部登记表B WHERE A.教师编号=B.教师编号);
则查询结果为:
教师编号 姓名 部门
JGX001 王冠 经管系
JGX002 刘柳 经管系
JSJ001 江河 计算机系
JSJ002 张大伟 计算机系
例2-56 查询不在"学生干部登记表"(见表2-1)中出现的教师的编号,姓名,部门信息.
SELECT 教师编号,姓名,部门
FROM 教工登记表 A
WHERE NOT EXISTS(SELECT * FROM 学生干部登记表 B WHERE A.教师编号=B.教师编号);
则查询结果为:
教师编号 姓名 部门
JCB001 汪洋 基础部
JCB002 张扬 基础部
JGX003 王芝环 经管系
2.4.8 使用聚集函数查询
常用的聚集函数包括:SUM,AVG,MAX,MIN,COUNT和COUNT(*).其作用是在查询结果集中生成汇总值.聚集函数常与GROUP BY子句配合使用,进行分组查询.
1.SUM函数
用于计算一列或多列的算术表达式的和.
语法格式:
SUM([ALL|DISTINCT] 表达式)
说明:使用DISTINCT关键字表示不计重复值.默认为ALL,计算全部值.
例2-57 查询所有教工的基本工资总和.
SELECT SUM(基本工资) 基本工资总和
FROM 教工登记表;
则查询结果为:
基本工资总和
5440.00
例2-58 查询各部门教工的基本工资总和.
SELECT 部门,SUM(基本工资) 基本工资总和
FROM 教工登记表
GROUP BY 部门;
则查询结果为:
部门 基本工资总和
基础部 1400.00
计算机系 1640.00
经管系 2400.00
2.AVG函数
用于计算一列或多列的算术表达式的平均值.
语法格式:
AVG([ALL|DISTINCT] 表达式)
例2-59 查询所有教工的基本工资平均值.
SELECT AVG(基本工资) 平均工资
FROM 教工登记表;
则查询结果为:
平均工资
777.142857
例2-60 查询各部门教工的平均工资值.
SELECT 部门,AVG(基本工资) 平均工资
FROM 教工登记表
GROUP BY 部门;
则查询结果为:
部门 平均工资
基础部 700.000000
计算机系 820.000000
经管系 800.000000
例2-61 查询各种职称的教工的平均年龄.
SELECT 职称,AVG(年龄) 平均年龄
FROM 教工登记表
WHERE 职称 IS NOT NULL
GROUP BY 职称;
则查询结果为:
职称 平均年龄
副教授 39
讲师 31
助教 25
3.MAX函数
用于计算一列或多列的表达式的最大值.
语法格式:
MAX(表达式)
例2-62 查询全体教工中的基本工资最高值.
SELECT MAX(基本工资) 最高工资
FROM 教工登记表;
则查询结果为:
最高工资
1000.00
例2-63 查询各部门教工的基本工资最高值.
SELECT 部门,MAX(基本工资) 最高工资
FROM 教工登记表
GROUP BY 部门;
则查询结果为:
部门 最高工资
基础部 900.00
计算机系 980.00
经管系 1000.00
4.MIN函数
用于计算一列或多列的表达式的最小值.
语法格式:
MIN(表达式)
例2-64 查询全体教工中的基本工资最低值.
SELECT MIN(基本工资) 最低工资
FROM 教工登记表;
则查询结果为:
最低工资
500.00
例2-65 查询各部门教工的基本工资最低值.
SELECT 部门,MIN(基本工资) 最低工资
FROM 教工登记表
GROUP BY 部门;
则查询结果为:
部门 基本工资
基础部 500.00
计算机系 660.00
经管系 500.00
5.COUNT和COUNT(*)函数
用于计算查询到的结果的数目.
语法格式:
COUNT([ALL|DISTINCT] 表达式);
或:
COUNT(*);
说明:COUNT(表达式)不计算空值行,COUNT(*)计算所有行(包括空值行).
例2-66 查询职称为"讲师"的教工的人数.
SELECT COUNT(职称) 讲师人数
FROM 教工登记表
WHERE 职称='讲师';
则查询结果为:
讲师人数
3
例2-67 查询各种职称的教工的人数.
SELECT 职称,COUNT(职称) 人数
FROM 教工登记表
GROUP BY 职称;
则查询结果为:
职称 人数
NULL 0
副教授 1
讲师 3
助教 2
"教工登记表"中,职称为空的记录本有一条,但COUNT(表达式)格式不计算空值行,所以查询结果显示职称为NULL的人数为0.
若将代码改为:
SELECT 职称,COUNT(*) 人数
FROM 教工登记表
GROUP BY 职称;
则查询结果为:
职称 人数
NULL 1
副教授 1
讲师 3
助教 2
因为COUNT(*)格式计算所有行,包括空值行,所以查询结果显示职称为NULL的人数为1.
例2-68 查询男性教工的人数.
SELECT COUNT(*) 男职工人数
FROM 教工登记表
WHERE 性别='男';
则查询结果为:
男职工人数
4
2.4.9 子查询与数据更新
上一节中介绍了数据更新的三种语句(INSERT,UPDATE,DELETE),实际上这三种语句还能与子查询结合,实现更加灵活的数据更新操作.
1.子查询与INSERT语句
子查询与INSERT语句相结合,可以完成一批数据的插入.
语法格式:
INSERT [INTO] []
例2-69 先创建一个计算机系教工登记表"计算机系教工表",然后将"教工登记表"中计算机系教工的数据插入到该表中.
创建表:
CREATE TABLE 计算机系教工表
(编号 CHAR(6) NOT NULL,
姓名 CHAR(8) NOT NULL,
性别 CHAR(2) NOT NULL,
年龄 SMALLINT,
婚否 BIT,
职称 CHAR(6),
基本工资 DECIMAL(7,2),
部门 CHAR(10));
插入数据:
INSERT 计算机系教工表
SELECT *
FROM 教工登记表
WHERE 部门='计算机系';
此时,计算机系教工表中有如下记录:
教师编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JSJ001 江河 男 31 1 讲师 980 计算机系
JSJ002 张大伟 男 25 0 助教 660 计算机系
例2-70 创建一个"职称查询表",包括"姓名,性别,职称"列,然后将"教工登记表"中的数据插入到该表中.
CREATE TABLE 职称查询表
( 姓名 CHAR(8) NOT NULL,
性别 CHAR(2) NOT NULL,
职称 CHAR(6),
);
插入数据:
INSERT 职称查询表
SELECT 姓名,性别,职称
FROM 教工登记表;
执行后,"职称查询表"中有如下记录:
姓名 性别 职称
汪洋 男 NULL
张扬 女 讲师
王冠 男 讲师
刘柳 女 副教授
王芝环 女 助教
江河 男 讲师
张大伟 男 助教
以上两例都使用子查询,在指定的表中有选择的插入了一批记录.也可完整地插入一个表的数据.
2.子查询与UPDATE语句
子查询与UPDATE语句结合,一般是嵌在WHERE子句中,查询结果作为修改数据的条件依据之一,可以修改一批数据.
语法格式:
UPDATE
SET =[,=][,…N]
WHERE
例2-71 给计算机系的教工,每人增加100元奖金.
UPDATE 教工工资表
SET 奖金=奖金+100
WHERE 姓名=ANY(SELECT 姓名 FROM 教工登记表 WHERE 部门='计算机系');
执行结果:在"教工工资表"中计算机系的教工"江河"和"张大伟"的奖金分别由400和250,增加到500和350.
3.子查询与DELETE语句
子查询与DELETE语句结合,一般也是嵌在WHERE子句中,查询结果作为删除数据的条件依据之一,可以删除一批数据.
语法格式:
DELETE FROM
WHERE
例2-72 在"职称查询表"中,删除非计算机系教师的记录.
DELETE FROM 职称查询表
WHERE 姓名=ANY(SELECT 姓名 FROM 教工登记表 WHERE 部门'计算机系');
执行后,"职称查询表"中有如下记录:
姓名 性别 职称
江河 男 讲师
张大伟 男 助教
非计算机系的5条记录被删除.
2.4.10 集合运算
SQL中的集合运算实际上是对两个SELECT语句的查询结果进行的运算,主要包括:
UNION:并
INTERSECT:交
EXCEPT:差
例2-73 在"教工登记表"中,查询职称为"讲师"及"讲师"以上,年龄小于27岁的教工记录的并集.
SELECT *
FROM 教工登记表
WHERE 职称 IN('讲师','副教授','教授')
UNION
SELECT *
FROM 教工登记表
WHERE 年龄<27;
则查询结果为:
教师编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JCB002 张扬 女 29 0 讲师 900.00 基础部
JGX001 王冠 男 33 1 讲师 900.00 经管系
JGX002 刘柳 女 39 1 副教授 1000.00 经管系
JGX003 王芝环 女 25 0 助教 500.00 经管系
JSJ001 江河 男 31 1 讲师 980.00 计算机系
JSJ002 张大伟 男 25 0 助教 660.00 计算机系
例2-74 在"教工登记表"中,查询职称为"讲师"以上与年龄小于30岁的教工记录的交集.
SELECT *
FROM 教工登记表
WHERE 职称 IN('讲师','副教授','教授')
INTERSECT
SELECT *
FROM 教工登记表
WHERE 年龄<30
相当于:
SELECT *
FROM 教工登记表
WHERE 职称 IN('讲师','副教授','教授') AND 年龄<30
则查询结果为:
教师编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JCB002 张扬 女 29 0 讲师 900.00 基础部

例2-75 在"教工登记表"中,查询职称为"讲师"以上与年龄小于30岁的教工记录的差集.
SELECT *
FROM 教工登记表
WHERE 职称 IN('讲师','副教授','教授')
EXCEPT
SELECT *
FROM 教工登记表
WHERE 年龄=30;
则查询结果为:
教师编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JGX001 王冠 男 33 1 讲师 900.00 经管系
JGX002 刘柳 女 39 1 副教授 1000.00 经管系
JSJ001 江河 男 31 1 讲师 980.00 计算机系
2.5 视图
2.5.1 视图的作用
视图实际上是从一个或多个基本表或已有视图中派生出来的虚拟表,也是一个关系,每个视图都有命名的字段和记录(列和行).但在数据库中只存在视图的定义,并不存在实际数据,实际数据都存放在基本表中,视图是一个虚表,但可通过操作视图而达到操作基本表数据的目的,操作方法与操作基本表相类似.
视图的优点:
简化用户操作
多角度的看待同一数据
提高数据的安全性
2.5.2 视图的定义
语法格式:
CREATE VIEW []
AS
[WITH CHECK OPTION]
说明:
(1) 选项WITH CHECK OPTION将在对视图进行INSERT,UPDATE和DELETE操作时,检查是否符合定义视图时SELECT语句中的.
(2) SELECT语句即前面介绍的查询语句.
例2-76 利用"教工登记表"创建一个视图"中高级职称名册".
CREATE VIEW 中高级职称名册
AS SELECT *
FROM 教工登记表
WHERE 职称 IN ('讲师','教授','副教授')
WITH CHECK OPTION
例2-77 利用"教工登记表"创建一个视图"经管系教工名册".
CREATE VIEW 经管系教工名册
AS SELECT *
FROM 教工登记表
WHERE 部门='经管系'
WITH CHECK OPTION
以后通过以上两视图插入记录只能分别插入职称为所列出的职称的记录或部门为"经管系"的记录,无法插入别的记录.
2.5.3 视图的删除
删除视图即删除视图的定义.即将指定的视图从数据字典中删除.
语法格式:
DROP VIEW ;
例2-78 删除视图"经管系教工名册".
DROP VIEW 经管系教工名册;
删除视图后,若有从该视图中导出的其他视图,则其他视图的定义仍保留在数据字典中,但已失效.
2.5.4 使用视图操作表数据
1.查询数据
视图也可像基本表一样通过SELECT查询数据,由于视图是一个虚表,其中是不存放数据的,所以查询视图的数据,实际上是查询基本表中的数据,查询时,首先从数据字典中取出指定视图的定义,然后检查数据源表是否存在,若不存在则无法执行,否则将SELECT语句指定的查询与视图的定义相结合,到基本表中查询数据,然后将结果显示出来.
例2-79 检索"中高级职称名册".
SELECT *
FROM 中高级职称名册;
则查询结果为:
教师编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JCB002 张扬 女 29 0 讲师 900.00 基础部
JGX001 王冠 男 33 1 讲师 900.00 经管系
JGX002 刘柳 女 39 1 副教授 1000.00 经管系
JSJ001 江河 男 31 1 讲师 980.00 计算机系
例2-80 检索"中高级职称名册"中,职称是"讲师",且性别为"女"的记录.
SELECT *
FROM 中高级职称名册
WHERE 职称='讲师' AND 性别='女';
则查询结果为:
教师编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JCB002 张扬 女 29 0 讲师 900.00 基础部

2. 插入数据
可使用INSERT语句向视图中添加数据,由于视图是一个虚表,不存放数据,所以对视图插入数据,实际上是对基本表插入数据.
例2-81 向"中高级职称名册"插入一条数据为:编号(JGX01),姓名(姜环红),性别(女),年龄(23),婚否(0),职称(助教),部门(经管系)的记录.
INSERT 中高级职称名册
VALUES ('JGX01','姜环红','女',23,0,'助教',400,'经管系');
执行后发现无法插入,因为该记录职称为"助教",不满足定义该视图时指定的条件,而定义视图时有又指定了"WITH CHECK OPTION"选项.
若执行下列操作:
INSERT 中高级职称名册
VALUES ('JGX02','王杨','女',35,0,'副教授',900,'经管系')
则插入成功,可通过打开视图或查询视图看到此记录.此时打开基本表"教工登记表"或对此表进行查询,也可发现该记录出现在表中,可见对视图的插入操作,即对基本表的插入操作.
3. 修改数据
可使用UPDATE语句通过视图对基本表的数据进行修改.同样修改后的数据如果不满足定义该视图时指定的条件,而定义视图时有又指定了"WITH CHECK OPTION"选项,则系统也会拒绝执行.
例2-82 将上例中插入在"中高级职称名册"中的一条记录的职称改为"高工".
UPDATE 中高级职称名册
SET 职称='高工'
WHERE 姓名='王杨'
运行时数据并没有得到修改,原因是,在定义视图"中高级职称名册"时,"高工"并不在职称列表之中,系统拒绝执行修改.
若执行下列操作,将职称改为"教授",
UPDATE 中高级职称名册
SET 职称='教授'
WHERE 姓名='王杨'
则修改成功.实际上是基本表中的数据得到了修改.
4. 删除数据
使用DELETE语句删除视图中的数据,也就是删除基本表中的数据.
例2-83 将"中高级职称名册"中"王杨"的记录删除.
DELETE 中高级职称名册
WHERE 姓名='王杨'
运行后查询"中高级职称名册"和"教工登记表",该记录已不存在.
2.6 SQL的数据完整性约束
数据完整性约束指的是保证数据库中的数据始终是正确的,一致的.在SQL中,提供了许多保障数据正确,完整的机制,如:事务处理可以保证数据库中的数据的一致性;主键(PRIMARY KEY)约束,唯一性(UNIQUE)约束可实现实体完整性约束,外键(FOREIGN KEY)约束可实现参照完整性约束,检查(CHECK)约束可实现用户自定义完整性约束.
2.6.1 事务(Transaction)
事务是RDBMS提供的一种特殊手段,事务可确保数据能够正确的被修改,避免因某些原因造成数据只修改一部分而致使数据不一致的现象.
1.基本概念
所谓事务,实际上就是对于一个不可分割的操作序列,控制它要么全部执行,要么都不执行.
例如:某人去银行转帐,准备将10000元人民币从活期存折转入定期存折,10000元人民币从活期存折提取之后,在将10000元人民币存入定期存折时发生了故障,后面的业务没有完成,这时,从活期存折提款的业务也应取消,否则用户的活期帐户钱少了,定期帐户钱并没有增加,用户肯定不答应.转帐中提取和存入是一个连续的操作序列,必须保证该操作序列完成之后,数据库中的数据是一致的.
2.事务的特性
事务具有如下特性:
原子性(Atomicity)
即要求事务中的所有操作都作为数据库中的一个基本的工作单元,这个工作单元中的所有操作,要么全部被执行,要么一个都不执行,即只要其中有一个语句操作失败,则这个工作单元的所有语句将全部拒绝执行.回到这个工作单元执行前的状态.
一致性(Consistency)
即要求无论事务完成或失败,都应保持数据库中的数据的一致性,当事务执行结果从一种状态变为另一种状态时,在状态的始终,数据库中的数据必须保持一致.事务的原子性是事务一致性的重要保证.
独立性(Isolation)
即要求多个事务并发(同时)执行时,事务之间彼此不会发生干扰,一个事务所做的操作是独立于其它事务的.事务的独立性由并发控制来保证.
持久性(Durability)
即要求一个事务一旦成功完成执行,则它对数据库中数据的修改就应永久的在系统中保存下来,即使系统出现故障也不至于对它产生影响.
事务的四个特性一般统称为ACID特性,即取每个特性的英文的第一个字母表示.
3.事务控制语句
SQL语言对事务的控制是通过几个事务控制语句来实现的.主要有以下三种控制语句:
(1)BEGIN TRANSACTION
用于标识一个用户定义的事务的开始.
(2)COMMIT
用于提交一个用户定义的事务.保证本次事务对数据的修改已经成功的写入数据库中,并被永久的保存下来.在COMMIT语句执行之前,事务对数据的修改都是暂时的.
(3)ROLLBACK
在事务执行的过程中,若发生故障,无法将事务顺利完成,则使用该语句回滚事务,即将事务的执行撤消,回到事务的开始处.
例2-84 给教工"刘柳"增加工资100元.
BEGIN TRANSACTION
update 教工登记表
set 基本工资=基本工资+100
WHERE 姓名='刘柳'
update 教工工资表
set 基本工资=基本工资+100
WHERE 姓名='刘柳'
COMMIT
因为教工的"基本工资"同时出现在"教工登记表"和"教工工资表"中,所以"刘柳"的工资必须在两个表中同时修改,以确保数据的一致性.把这两个修改操作放在一个事务中,即可使得两个表要么都修改成功,要么一个都不修改.
例2-85 在"中高级职称名册"中修改一条记录,并插入一条记录.
BEGIN TRANSACTION
UPDATE 中高级职称名册
SET 职称='副教授'
WHERE 姓名='王冠'
INSERT 中高级职称名册
VALUES('SYS010','高山','男',40,1,'教授','计算机系')
SELECT *
FROM 中高级职称名册
COMMIT
运行后发现,插入操作不成功,原因是插入的记录少了一项"基本工资"值;修改操作也不成功,"王冠"那条记录也没得到修改.修改代码为如下:
BEGIN TRANSACTION
UPDATE 中高级职称名册
SET 职称='副教授'
WHERE 姓名='王冠'
INSERT 中高级职称名册
VALUES('SYS010','高山','男',40,1,'教授',1200,'计算机系')
SELECT *
FROM 中高级职称名册
COMMIT
运行结果为:
教师编号 姓名 性别 年龄 婚否 职称 基本工资 部门
JCB002 张扬 女 29 0 讲师 900.00 基础部
JGX001 王冠 男 33 1 副教授 900.00 经管系
JGX002 刘柳 女 39 1 副教授 1100.00 经管系
JSJ001 江河 男 31 1 讲师 980.00 计算机系
SYS010 高山 男 40 1 教授 1200.00 计算机系
插入和修改操作同时成功完成.
2.6.2 完整性约束
完整性约束主要包括:实体完整性约束,参照完整性约束和用户自定义完整性约束.约束用来强制实现数据库中数据的完整性,正确性.
在SQL中,一般用以下形式来完成完整性约束:
主键完整性约束(PRIMARY KEY)
外键完整性约束(FOREIGN KEY)
键值唯一完整性约束(UNIQUE)
检查完整性约束(CHECK)
非空值完整性约束(NOT NULL)
1.主键完整性约束(PRIMARY KEY)
主键是一个表中能够唯一标识每一行的列或列的组合,SQL中是使用主键来实现表的实体完整性.
主键约束的特征:
主键列不允许输入重复值,若主键列由多个列组合而成,则某一列上的数据可以重复,但列的组合值不能重复.
一个表中只能有一个主键约束,主键约束列不允许取空值(NULL).
主键约束可在创建表时定义,也可在已有表中添加.
定义主键的子句格式:
[CONSTRAINT 约束名]
PRIMARY KEY [()]
说明:
[CONSTRAINT 约束名]:指定建立的主键约束的约束名,可选,若不选该项,则由系统自动取一默认约束名.
例2-86 创建"学生干部登记表".并将"学号"列设置为主键列.
CREATE TABLE 学生干部登记表
(学号 CHAR(8) PRIMARY KEY, /*列级主键约束*/
姓名 CHAR(8),
性别 CHAR(2),
年龄 SMALLINT,
班级 CHAR(12),
任职 CHAR(10),
教师编号 CHAR(6))
也可这样定义:
CREATE TABLE 学生干部登记表
(学号 CHAR(8),
姓名 CHAR(8),
性别 CHAR(2),
年龄 SMALLINT,
班级 CHAR(12),
任职 CHAR(10),
教师编号 CHAR(6),
PRIMARY KEY(学号)) /*表级主键约束*/
"学生干部登记表"建立后,在学号列上就不能有重复值和空值.
2.外键完整性约束(FOREIGN KEY)
外键完整性约束,是用于限制两个表之间数据的完整性,在SQL中是使用外键来体现表的参照完整性.
定义外键的子句格式:
[CONSTRAINT 约束名]
[FOREIGN KEY (列名)]
REFERENCES (父表的列名)
[ON DELETE {CASCADE|NO ACTION}]
[ON UPDATE {CASCADE|NO ACTION}]
说明:
(1)CONSTRAINT 约束名:指定建立的外键约束的约束名,可选,若不选该项,则由系统自动取一默认约束名.
(2)FOREIGN KEY (列名):此项可选,若不选该项,则需直接在要建立外键的列名后跟"REFERENCES (父表的列名)"项.
(3)父表名:即建立外键要参照的表的表名.
(4)父表的列名:即建立外键要引用的父表中的列的列名.
(5)ON DELETE {CASCADE|NO ACTION}:如果指定CASEDE,则在从父表中删除被引用的记录时,也将从引用表(子表)中删除引用记录;如果指定NO ACTION,则在删除父表中被引用的记录时,将返回一个错误消息并拒绝删除操作.默认值为NO ACTION.
(6)ON UPDATE {CASCADE|NO ACTION}:如果指定CASEDE,则在父表中更新被引用的记录时,也将在引用表(子表)中更新引用记录;如果指定NO ACTION,则在更新父表中被引用的记录时,将返回一个错误消息并拒绝更新操作.默认值为NO ACTION.
例2-87 将上例中的"学生干部登记表"中的"教师编号"列设置为相对于"教师登记表"的外键.
CREATE TABLE 学生干部登记表
(学号 CHAR(8) PRIMARY KEY,
姓名 CHAR(8),
性别 CHAR(2),
年龄 SMALLINT,
班级 CHAR(12),
任职 CHAR(10),
教师编号 CHAR(6) REFERENCES 教工登记表(教师编号));
也可这样定义:
CREATE TABLE 学生干部登记表
(学号 CHAR(8) PRIMARY KEY,
姓名 CHAR(8),
性别 CHAR(2),
年龄 SMALLINT,
班级 CHAR(12),
任职 CHAR(10),
教师编号 CHAR(6),
FOREIGN KEY(教师编号)
REFERENCES 教工登记表(教师编号));
向"学生干部登记表"中插入如下记录:
学号 姓名 性别 年龄 班级 任职 教师编号
J2004001 李宏伟 男 19 04计算机1班 班长 JSJ001
J2003005 张华东 男 20 03电商1班 班长 JSJ002
G2003102 江蔚然 女 19 03国贸2班 学习委员 JGX001
G2003209 刘芳红 女 20 03经管1班 副班长 JGX005
执行后发现最后一条记录无法插入,因为教师编号"JGX005"在父表(被引用的表,这里是"教工登记表")中不存在,违反参照完整性约束,更新操作被拒绝.将该记录的"教师编号"改为"JGX003",则插入成功.
SQL中提供了三种方法来保证参照完整性的实施:
(1)限制方法(RESTRICT)
即任何违反参照完整性的更新都将被拒绝.如:上例中在子表中插入的记录时,父表中"教师编号"列中无"JGX002"值,所以无法插入.若在子表中修改"教师编号"的值,而修改后的值非空且在父表中不存在,也将无法更改.如将上例中子表中的"教师编号"值"JSJ001"更改为"JSJ007",系统将拒绝修改.若在上例中删除父表中的一条记录,而该记录的"教师编号"值仍出现在子表的"教师编号"列中,此记录也无法删除.比如在"教工登记表"中删除"教师编号"值为"JSJ001"的记录,系统将拒绝删除.除非先将子表"学生干部登记表"中"教师编号"值为"JSJ001"的记录先删除,才能将父表中相对应的记录删除.
(2)级联方法(CASCADE)
限制方法对于经常要对父表的主键值进行删除,更改操作不大方便,即当对父表的主键值进行删除,或更改操作时,都必须先将子表中的相应记录先删除,不能使得子表的数据随父表的数据而改变.
级联方法就是指当对父表的主键值进行删除和修改时,子表中的相应的外键值也将随之删除或修改.以便保证参照完整性.
例2-88同上例,只是在创建外键约束时增加选项"ON DELETE CASCADE"和"ON UPDATE CASCADE".
CREATE TABLE 学生干部登记表
(学号 CHAR(8) PRIMARY KEY,
姓名 CHAR(8),
性别 CHAR(2),
年龄 SMALLINT,
班级 CHAR(12),
任职 CHAR(10),
教师编号 CHAR(6)REFERENCES 教工登记表(教师编号)
ON DELETE CASCADE
ON UPDATE CASCADE);
执行后,子表中输入如下记录:
J2004001 李宏伟 男 19 04计算机1班 班长 JSJ001
J2003005 张华东 男 20 03电商1班 班长 JSJ002
G2003102 江蔚然 女 19 03国贸2班 学习委员 JGX001
G2003209 刘芳红 女 20 03经管1班 副班长 JGX003
然后将父表中"教师编号"值 "JSJ001"修改为"JSJ007",修改成功,查看子表,子表中对应的外键值也修改为"JSJ007".再将父表中"教师编号"值为"JGX003"的记录删除,查看子表,子表中外键"教师编号"值为"JGX003"的记录也随之被删除.
(3)置空方法(SET NULL)
置空方法也是针对父表的删除或修改操作的,当删除或修改父表中的某一主键值时,与其对应的子表中的外键值置空.
3.键值唯一完整性约束(UNIQUE)
键值唯一完整性约束是用于限制非主键的其他指定列上的数据的唯一性.
定义唯一性约束的子句格式:
[CONSTRAINT 约束名]
UNIQUE [(字段名表)]
键值唯一性约束与主键约束的异同点:
相同点:
列值不能重复,都能保证表中记录的唯一性.
都可以被外键约束所引用.
不同点:
一个表中只能定义一个主键约束,但可以定义多个唯一性约束.
定义了主键约束的列上不能取空值,定义了唯一性约束的列上可以取空值.
例2-89在"学生干部登记表"上"姓名"列上建立一个唯一性约束.
CREATE TABLE 学生干部登记表
(学号 CHAR(8) PRIMARY KEY,
姓名 CHAR(8) UNIQUE, /*列级唯一性约束*/
性别 CHAR(2),
年龄 SMALLINT,
班级 CHAR(12),
任职 CHAR(10),
教师编号 CHAR(6));
也可这样定义:
CREATE TABLE 学生干部登记表
(学号 CHAR(8) PRIMARY KEY,
姓名 CHAR(8),
性别 CHAR(2),
年龄 SMALLINT,
班级 CHAR(12),
任职 CHAR(10),
教师编号 CHAR(6),
UNIQUE(姓名)); /*表级唯一性约束*/
执行后,若在姓名列输入了重复的数据,则系统拒绝接受,可在姓名列输入一个空值NULL(即任何值都不输入),但超过一个空值,则认为是重复数据,系统仍然拒绝接受.
4.检查完整性约束(CHECK)
检查完整性约束可以实现用户自定义完整性约束.检查约束主要用于限制列上可以接受的数据值.一个列上可以使用多个检查约束.
定义检查约束的子句格式:
[CONSTRAINT 约束名]
CHECK(逻辑表达式)
说明:这里"逻辑表达式"指的是用于约束列值的逻辑表达式.
例2-90在"教工工资表"上"基本工资"列上建立一个检查约束,限制基本工资值在500~1200范围.
CREATE TABLE 教工工资表
(工资编号 INT IDENTITY ,
姓名 CHAR(8) NOT NULL,
性别 CHAR(2) ,
职称 CHAR(6),
基本工资 DECIMAL(7,2)
CHECK(基本工资>=500 AND 基本工资=500 AND 基本工资=20 AND 年龄=20 AND 年龄<=60);
2.7 触发器
触发器(Trigger)是一种可以实现程序式完整性约束的机制,可以用来对表实施复杂的完整性约束,当对触发器所保护的数据进行增,删,改操作时,系统会自动激发触发操作,以防止对数据进行不正确的修改,从而实现数据的完整性约束.触发器基于一个表创建,但可针对多个表进行操作.
2.7.1 触发器的作用
触发器一般有以下几种用途:
对数据库中相关的表进行级联修改
撤消或回滚违反引用完整性的操作,防止非法修改数据.
完成比检查约束更为复杂的约束操作
比较表修改前后数据之间的差别,并根据这些差别来进行相应的操作
对一个表上的不同操作(INSERT,UPDATE或者DELETE)可采取不同的触发器,对一个表上的相同操作也可调用不同的触发器进行不同的操作.
2.7.2 触发器的组成
1.触发器的组成
每一个触发器一般都包括三个组成部分:
触发器名
触发器的触发事件
触发器执行的操作
触发器名即所创建的触发器的命名,触发器的触发事件是指对表进行的插入(INSERT),修改(UPDATE),删除(DELETE)操作;触发器执行的操作是一个存储过程或一个批处理,也即一个SQL的语句序列.
2.触发器动作时间:
由BEFORE和AFTER关键字定义,使用BEFORE则表示触发动作在触发事件之前出现,使用AFTER则表示触发动作在触发事件之后.
2.7.3 触发器的操作
1.创建触发器
语法格式:
CREATE TRIGGER
{BEFORE|AFTER}
ON
其中:指INSERT,UPDATE,DELETE操作;指具体要执行的触发操作,由一组SQL语句构成.
例2-94 在"教工登记表"上创建一触发器.
CREATE TRIGGER CFQ1 ON 教工登记表
AFTER INSERT
AS
SELECT '请核对修改后的记录:'
SELECT * FROM 教工登记表
2.触发触发器
即针对触发器,执行相应的触发事件(INSERT,UPDATE,DELETE).
例2-95 在"教工登记表"上插入一条记录,触发触发器cfq1.
INSERT 教工登记表
VALUES('JSJ006','李立','男',30,1,'讲师',700,'计算机系','江河');
执行结果:
请核对修改后的记录:
教师编号 姓名 性别 年龄 婚否 职称 基本工资 部门 负责人
JCB001 汪洋 男 27 1 NULL 500.00 基础部 张扬
JCB002 张扬 女 29 0 讲师 900.00 基础部 张扬
JGX001 王冠 男 33 1 副教授 900.00 经管系 刘柳
JGX002 刘柳 女 39 1 副教授 1100.00 经管系 刘柳
JGX003 王芝环 女 25 0 助教 500.00 经管系 江河
JSJ001 江河 男 31 1 讲师 980.00 计算机系 江河
JSJ002 张大伟 男 25 0 助教 660.00 计算机系 江河
JSJ006 李立 男 30 1 讲师 700.00 计算机系 江河
3.删除触发器
触发器不再需要时可将其删除.
语法格式:
DROP TRIGGER ;
例2-96 删除触发器cfq1.
DROP TRIGGER cfq1
早在标准SQL之前,许多RDBMS就已经支持触发器,因此它们的定义与标准SQL有着不同,且相互之间也有着不同,应注意区别.
2.8 存储过程
2.8.1 存储过程的基本概念
存储过程是RDBMS中的由一组SQL语句组成的程序,存储过程被编译好后保存在数据库中,可以被反复调用,运行效率高.目前大部分的RDBMS都提供了存储过程.以下以T-SQL存储过程为例.
2.8.2存储过程的定义
语法格式:
CREATE PROCEDURE
[]
AS
;
其中:用于指定默认参数,输入参数或输出参数.
例2-97创建一个存储过程,使职工通过输入姓名可查询本人的工资情况.
CREATE PROCEDURE 查询工资
@NAME VARCHAR(8)=NULL
AS
IF @NAME IS NULL
SELECT '请输入姓名后再查询!'
ELSE
SELECT 姓名,基本工资
FROM 教工登记表
WHERE 姓名=@NAME;
2.8.3存储过程的执行
存储过程一经建立就可反复调用执行.
例2-98执行上例中创建的存储过程"查询工资".
不带参调用:
输入:查询工资
则输出:请输入姓名后再查询!
带参调用:
输入:查询工资 '刘柳'
则输出:
姓名 基本工资
刘柳 1100.00
如果在定义时,输入参数给定了默认值(上例中的@NAME VARCHAR(8)=NULL),则在调用时可不给出确定的参数值,否则一定要给出确定值.
2.8.4存储过程的删除
不再需要的存储过程可将其删除.
语法格式:
DROP PROCEDURE
例2-99删除上例中创建的存储过程"查询工资".
DROP PROCEDURE 查询工资;
2.9 嵌入式SQL语言
2.9.1嵌入式SQL语言的基本概念
SQL语言有两种形式,一种是自主式SQL,即SQL作为独立的数据语言,以交互方式使用;一种是嵌入式SQL(Embedded SQL),即SQL嵌入到其他高级语言中,在其他高级语言中使用.被嵌入的高级语言(如:C/C++,Basic,Java等)称为宿主语言(或主语言).
2.9.2嵌入式SQL语言需解决的问题
将SQL嵌入到高级语言中使用,一方面可以使SQL借助高级语言来实现本身难以实现的复杂操作问题(如递归),另一方面也可使高级语言克服对数据库操作的不足,获得更强的数据库的操作能力.但是如果要使SQL语言在高级语言中得到正确无误的运用,必须首先要考虑解决以下几个问题:
(1)首先应考虑在宿主语言中如何区分SQL语句和高级语言的语句,宿主语言的预编译器无法识别和接受SQL语句,必须要有能区分宿主语言语句和SQL语句的标识.
(2)数据库的工作单元与宿主语言程序工作单元如何进行信息传递.
(3)一般一个SQL语句一次能完成对一批记录的处理,而宿主语言一次只能对一个记录进行处理,这两种处理方式不同,如何协调.
2.9.3 嵌入式SQL语言的语法格式
嵌入式SQL的语法结构与交互式SQL的语法结构基本保持相同,一般只是在嵌入式SQL中加入一些前缀和结束标志.对于不同的宿主语言,嵌入SQL时,格式上可能略有不同.
以下都以C语言为例,说明嵌入式SQL的一般使用方法.
在C语言中嵌入的SQL语句以EXEC SQL开始,以分号";"结束:
EXEC SQL ;
说明:
(1) EXEC SQL大小写都可.
(2) EXEC SQL与分号之间只能是SQL语句,不能包含有任何宿主语言的语句.
(3) 当嵌入式SQL语句中包含的字符串在一行写不下时,可用反斜杠(\)作为续行标志,将一个字符串分多行写.
(4) 嵌入式SQL语句按照功能的不同,可分为可执行语句和说明语句.而可执行语句又可分为数据定义语句,数据操纵语句和数据控制语句.
2.9.4嵌入式SQL与宿主语言之间的信息传递
嵌入式SQL与宿主语言之间的信息传递,即SQL与高级语言之间的数据交流,包括SQL向宿主语言传递SQL语句的执行信息,以及宿主语言向SQL提供参数.前者主要通过SQL通信区来实现,后者主要通过宿主语言的主变量来实现.
1.主变量(Host Variable)
主变量即宿主变量,是在宿主语言中定义的变量,而在嵌入式SQL语言中可以引用的变量,主要用于嵌入式SQL与宿主语言之间的数据交流.
主变量在使用前一般应预先加以定义,定义格式:
EXEC SQL BEGIN DECLARE SECTION;
…… /*主变量的定义语句*/
EXEC SQL END DECLARE SECTION;
例2- 100 定义若干主变量.
EXEC SQL BEGIN DECLARE SECTION;
int num;
char name[8];
char sex;
int age;
EXEC SQL END DECLARE SECTION;
说明:
(1)主变量的定义格式符合宿主语言的格式要求,且变量所取的数据类型应是宿主语言和SQL都能处理的数据类型,如整型,字符型等.
(2)在嵌入式SQL语句中引用主变量时,变量前应加上冒号":",以示对数据库对象名(如表名,列名等)的区别.而在宿主语言中引用主变量时,不必加冒号.
主变量不能直接接受空值(NULL),但主变量可附带一个指示变量(Indicator Variable)用以描述它所指的主变量是否为NULL.指示变量一般为短整型,若指示变量的值为0,则表示主变量的值不为NULL,若指示变量的值为-1,则表示主变量的值为NULL.指示变量一般跟在主变量之后,用冒号隔开.
例2- 101 指示变量的使用.
EXEC SQL SELECT TDepartment INTO:Dept:dp
FROM TEACHER
WHERE TName=:name:na;
其中,dp和na分别是主变量Dept和name的指示变量.该例是从TEACHER表中根据给定的教师姓名(name),查询该教师所在部门(Tdepartment),如果na的值为0,而dp的值不为0,则说明指定的教师部门为NULL;如果na的值不为0,则说明查询姓名为NULL的教师所在的部门,一般这种查询没有意义;如果na和dp的值都为0,则说明查询到了指定姓名的教师所在的部门.
负责对SQL操作输入参数值的主变量为输入主变量,负责接受SQL操作的返回值的主变量为输出主变量,如果返回值为NULL,将不置入主变量,因为宿主语言一般不能处理空值.
2.SQL通信区(SQLCA)
SQL通信区(SQL Communication Area)简称SQLCA,是宿主语言中的一个全局变量,用于应用程序与数据库间的通信,主要是实时反映SQL语句的执行状态信息,如数据库连接,执行结果,错误信息等.
SQLCA已经由系统说明,无须再由用户说明,只需在嵌入的可执行SQL语句前加INCLUDE语句就能使用.
语法格式:EXEC SQL INCLUDE SQLCA;
SQLCA有一个成员是SQLCODE,取整型值,用于SQL向应用程序报告SQL语句的执行情况.每执行一条SQL语句,都有一个SQLCODE代码值与其对应,应用程序根据测得的SQLCODE代码值,来判定SQL语句的执行情况,然后决定执行相应的操作.
一般约定:
SQLCODE=0,表示语句执行无异常情况,执行成功.
SQLCODE=1,表示SQL语句已经执行,但执行的过程中发生了异常情况.
SQLCODE<0,表示SQL语句执行失败,具体的负值表示错误的类别.如出错的原因可能是系统,应用程序或是其他情况.
SQLCODE=100,表示语句已经执行,但无记录可取.
不同的应用程序,SQLCODE的代码值可能会略有不同.
2.9.5游标(Cursor)
前面提到一个SQL语句一次能完成对一批记录的处理,而宿主语言一次只能对一个记录进行处理,这两种处理方式不同,如何协调.实际上,SQL语言与宿主语言的不同数据处理方式可以通过游标来协调.
游标是系统为用户在内存中开辟的一个数据缓冲区,用于存放SQL语句的查询结果,每个游标都有一个名字,通过宿主语言的循环使SQL逐一从游标中读取记录,赋给主变量,然后由宿主语言作进一步的处理.
游标的操作一般分为如下几个步骤:
1.定义游标(DECLARE CURSOR)
游标必须先定义.
语法格式:EXEC SQL DECLARE 〈游标名〉 CURSOR FOR ;
游标定义后,并不马上执行定义中的SELECT语句,需在打开后才执行.
2.打开游标(OPEN CURSOR)
游标定义后,在使用之前一定要先打开.
语法格式:EXEC SQL OPEN〈游标名〉;
游表打开后,将执行游标定义中的SELECT语句,将执行结果存入游标缓冲区,游标指针指向第一条记录.
3.推进游标(FETCH CURSOR)
要对游标缓冲区的记录逐一进行处理,需移动游标指针,依次取出缓冲区中的记录.
语法格式:EXEC SQL FETCH〈游标名〉INTO 〈主变量名表〉;
主变量名表中的主变量要与SELECT语句查询结果中的每一个属性相对应,多个主变量间用逗号分隔,主变量必须加冒号以示区别.
FETCH语句每执行一次,只能取得一个元组,要想得到多个元组,必须在宿主程序中使用循环.
4.关闭游标(CLOSE CURSOR)
游标使用完后,应关闭游标.
语法格式:EXEC SQL CLOSE〈游标名〉;
关闭游标后,若还要使用,仍可用OPEN语句打开.
例2-102 查询各种职称的教师的名单.
EXEC SQL BEGIN DECLARE SECTION;
char xm[8];
char zc[6];
EXEC SQL END DECLARE SECTION;
printf("Enter 职称:");
scanf("%s",zc);
EXEC SQL DECLARE zc_cur CURSOR FOR
SELECT Tname,Ttitle
FROM TEACHER
WHERE Ttitle=:zc;
EXEC SQL OPEN zc_cur
while(1)
{
EXEC SQL FETCH zc_cur INTO :xm,:zc;
if(sqlca.sqlcode0)
break;
……
}
EXEC SQL CLOSE zc_cur;
……
2.10小结
本章介绍的是标准SQL语言,SQL语言是关系数据库的标准语言,功能全面强大.
SQL标准文本:SQL-86,SQL-89,SQL-92,SQL-99
SQL语言特点:简单易学,非过程化,面向集合,多种使用,综合功能.
SQL语言分类:数据定义语言,数据操纵语言,数据控制语言,事务处理语言.
数据定义:模式定义,基本表定义,视图定义,索引定义.
数据操纵:数据更新,数据查询.
数据更新:插入,修改,删除基本表数据.
数据查询:SELECT语句可灵活方便地完成各种简单或复杂的查询.
视图:一个或多个表中导出的虚表,简化操作,提高安全性.
SQL的数据完整性约束:主键约束,外键约束,键值唯一性约束,检查完整性约束,非空值完整性约束,事务等.
SQL用户
内模式
模式
外模式
图2-1 SQL支持的数据库模式
视图3
视图2
存储文件1
存储文件2
存储文件3
基本表1
基本表2
基本表3
视图1
SQL用户
SQL用户