长文警告，对照目录使用更佳，本文包含了sql语句最基本的一些用法以及其示例，推荐初学者收藏起来当手册使用
本文是在我刚开始接触sql时整理的，难免会有一些错误与瑕疵，如有问题，欢迎各位在评论区指正
SQL概述
SQL(Structured Query Language):结构化查询语言，是关系数据库的标准语言
SQL是一个通用的、功能极强的关系数据库语言
SQL的特点
- 综合统一
- 高度非过程化
- 面向集合的操作方式
- 一种语法结构多种使用方式
- 语言简洁，易学易用
综合统一
- 集数据定义语言(DDL)，数据操纵语言(DML)，数据控制语言(DCL)功能于一体。
- 可以独立完成数据库生命周期中的全部活动：
- 定义和修改、删除关系模式，定义和删除视图，插入数据，建立数据库;
- 对数据库中的数据进行查询和更新;
- 数据库重构和维护
- 数据库安全性、完整性控制，以及事务控制
- 嵌入式SQL和动态SQL定义
- 用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据库的运行。
- 数据操作符统一
高度非过程化
- 非关系数据模型的数据操纵语言“面向过程”必须指定存取路径。
- SQL只要提出“做什么”，无须了解存取路径。
- 存取路径的选择以及SQL的操作过程由系统自动完成。
面向集合的操作方式
- 非关系数据模型采用面向记录的操作方式，操作对象是一条记录。
- SQL采用集合操作方式
- 操作对象、查找结果可以是元组的集合。
- 一次插入、删除、更新操作的对象可以是元组的集合。
一种语法结构多种使用方式
-
SQL是独立的语言,
能够独立地用于联机交互的使用方式
-
SQL又是嵌入式语言
SQL能够嵌入到高级语言(例如C，C++，Java)程序中，供程序员设计程序时使用
语言简洁，易学易用
SQL功能极强，核心功能只用了9个动词
SQL的基本概念
SQL支持关系数据库三级模式结构
基本表
- 本身独立存在的表
- SQL中一个关系就对应一个基本表
- 一个(或多个)基本表对应一个存储文件
- 一个表可以带若干索引
存储文件
- 逻辑结构组成了关系数据库的内模式。
- 物理结构对用户是隐蔽的。
视图
- 从一个或几个表导出的“虚表”
- 数据库中只存放视图的定义而不存放视图对应的数据
- 用户可以在视图上再定义视图
数据定义
SQL的数据定义功能
- 模式的定义与删除
- 表的定义与删除
- 视图的定义与删除
- 索引的定义与删除
模式的定义与删除
- 现代关系数据库管理系统提供了一个层次化的数据库对象命名机制
- 一个关系数据库管理系统的实例(Instance)中可以建立多个数据库
- 一个数据库中可以建立多个模式
- 一个模式下通常包括多个表、视图和索引等数据库对象
定义模式
定义模式实际上定义了一个命名空间。
在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。
在CREATE SCHEMA中可以接受CREATE TABLE，CREATE VIEW和GRANT子句。
定义语句
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>]
为用户WANG定义一个学生-课程模式S-T:
CREATE SCHEMA “S-T” AUTHORIZATION WANG;
为用户ZHANG创建了一个模式TEST，并且在其中定义一个表TAB1
CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1 ( COL1 SMALLINT,COL2 INT,COL3 CHAR(20),COL4 NUMERIC(10,3),COL5 DECIMAL(5,2));
删除模式
删除语句
DROP SCHEMA <模式名> <CASCADE|RESTRICT>
- CASCADE(级联)
- 删除模式的同时把该模式中所有的数据库对象全部删除
- RESTRICT(限制)
- 如果该模式中定义了下属的数据库对象(如表、视图等)，则拒绝该删除语句的执行
- 仅当该模式中没有任何下属的对象时才能执行。
删除模式ZHANG,同时该模式中定义的表TAB1也被删除
DROP SCHEMA ZHANG CASCADE;
基本表的定义,删除与修改
定义基本表
CREATE TABLE <表名>
(<列名> <数据类型>[ <列级完整性约束条件> ]
[,<列名> <数据类型>[ <列级完整性约束条件>] ]
…
[,<表级完整性约束条件> ] );
- <表名>：所要定义的基本表的名字
- <列名>：组成该表的各个属性(列)
- <列级完整性约束条件>：涉及相应属性列的完整性约束条件
- <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件
如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。
建立“学生”表Student。学号是主码，姓名取值唯一。
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,/*列级完整性约束条件,Sno是主码*/
Sname CHAR(20) UNIQUE,/*Sname取唯一值*/
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
建立'课程'表Course
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),/*先修课*/
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)/*Cpno是外码被参照表是Course被参照列是Cno*/
);
建立选课表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*/
);
数据类型
- SQL中域的概念用数据类型来实现
- 定义表的属性时需要指明其数据类型及长度
- 选用哪种数据类型
- 取值范围
- 要做哪些运算
模式与表
-
每一个基本表都属于某一个模式
-
一个模式包含多个基本表
-
定义基本表所属模式
-
方法一：在表名中明显地给出模式名
Create table"S-T".Student(......); /*模式名为 S-T*/ Create table "S-T".Cource(......); Create table "S-T".SC(......); -
方法二:在创建模式语句中同时创建表
-
方法三：设置所属的模式
-
-
创建基本表(其他数据库对象也一样)时，若没有指定模式，系统根据搜索路径来确定该对象所属的模式
-
关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名
修改基本表
ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;
- <表名>是要修改的基本表
- ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件
- DROP COLUMN子句用于删除表中的列
- 如果指定了CASCADE短语，则自动删除引用了该列的其他对象
- 如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列
- DROP CONSTRAINT子句用于删除指定的完整性约束条件
- ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型
向Student表增加“入学时间”列，其数据类型为日期型
ALTER TABLE Student ADD S_entrance DATE;
/*不管基本表中原来是否已有数据，新增加的列一律为空值*/
将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数
ALTER TABLE Student ALTER COLUMN Sage INT;
增加课程名称必须取唯一值的约束条件
ALTER TABLE Course ADD UNIQUE(Cname);
删除基本表
DROP TABLE <表名>［RESTRICT| CASCADE］;
- RESTRICT：删除表是有限制的。
- 欲删除的基本表不能被其他表的约束所引用
- 如果存在依赖该表的对象，则此表不能被删除
- CASCADE：删除该表没有限制。
- 在删除基本表的同时，相关的依赖对象一起删除
删除Student表
DROP TABLE Student CASCADE
/*基本表定义被删除，数据被删除,表上建立的索引、视图、触发器等一般也将被删除*/
若表上建有视图，选择RESTRICT时表不能删除;选择CASCADE时可以删除表，视图也自动删除。
例如:先在表上新建一个视图,然后删除
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';
使用RESTRICT删除会报错:
DROP TABLE Student RESTRICT;
--ERROR: cannot drop table Student because other objects depend on it
使用CASCADE时可以删除
DROP TABLE Student CASCADE;
--NOTICE: drop cascades to view IS_Student
索引的建立与删除
建立索引的目的:加快查询速度
建立索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);
- <表名>：要建索引的基本表的名字
- 索引：可以建立在该表的一列或多列上，各列名之间用逗号分隔
- <次序>：指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC
- UNIQUE：此索引的每一个索引值只对应唯一的数据记录
- CLUSTER：表示要建立的索引是聚簇索引
为学生-课程数据库中的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 <旧索引名> RENAME TO <新索引名>
将SC表的SCno索引名改为SCSno
ALTER INDEX SCno RENAME TO SCSno;
删除索引
DROP INDEX <索引名>/*删除索引时，系统会从数据字典中删去有关该索引的描述*/
删除Student表的Stusname索引
DROP INDEX Stusname;
数据字典
数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有定义信息：
- 关系模式定义
- 视图定义
- 索引定义
- 完整性约束定义
- 各类用户对数据库的操作权限
- 统计信息等
关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的相应信息。
数据查询
SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)
[AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
- SELECT子句：指定要显示的属性列
- FROM子句：指定查询对象(基本表或视图)
- WHERE子句：指定查询条件
- GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
- HAVING短语：只有满足指定条件的组才予以输出
- ORDER BY子句：对查询结果表按指定列值的升序或降序排序
单表查询
选择表中的若干列
查询全体学生的学号与姓名。
SELECT Sno,Sname
FROM Student;
查询全体学生的姓名、学号、所在系。
SELECT Sname,Sno,Sdept
FROM Student;
/*查询全部列, 将<目标列表达式>指定为 '*' */
查询全体学生的详细记录
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
或
SELECT *
FROM Student;
/*查询经过计算的值*/
查全体学生的姓名及其出生年份
SELECT Sname,2014-Sage
FROM Student;
查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名
SELECT Sname,'Year of Birth: ',2014-Sage,LOWER(Sdept)
FROM Student;
/*别名*/
SELECT Sname NAME,'Year of Birth:' BIRTH,2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT,执行结果3
FROM Student;
选择表中的若干元组
消除重复的行,DISTINCTT关键字
查询选修了课程的学生学号
SELECT Sno FROM SC;
等价于：
SELECT ALL Sno FROM SC;
指定DISTINCT关键字,去掉表中重复的行
SELECT DISTINCT Sno
FROM SC;
查询满足条件的元组
比较大小
/*=, >, <, >=, <=, !=, <>, !>, !<*/
查询软件学院全体学生的名单。
SELECT Sname
FROM Student
WHERE Sdept=‘SE’;
查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage < 20;
查询考试成绩有不及格的学生的学号。
SELECT DISTINCT Sn
FROM SC
WHERE Grade<60;
确定范围
/*BETWEEN … AND …,NOT BETWEEN … AND …*/
查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
确定集合
/*IN <值表>, NOT IN <值表>*/
查询软件学院(SE)、数学系(MA)和信息系(IS)学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('SE','MA','IS' );
查询既不是软件学院、数学系，也不是信息系的学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept NOT IN ('IS','MA’,'SE' );
字符匹配
/*[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’],<匹配串>可以是一个完整的字符串，也可以含有通配符%和 _
% (百分号) 代表任意长度(长度可以为0)的字符串
_ (下横线) 代表任意单个字符。*/
查询学号为201215121的学生的详细情况。
SELECT *
FROM Student
WHERE Sno LIKE '201215121';
查询所有姓刘学生的姓名、学号和性别。
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE '刘%';
查询姓"欧阳"且全名为三个汉字的学生的姓名。
SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳__';
查询名字中第2个字为"阳"字的学生的姓名和学号。
SELECT Sname，Sno
FROM Student
WHERE Sname LIKE '__阳%';
查询所有不姓刘的学生姓名、学号和性别。
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname NOT LIKE '刘%';
/*换码字符*/
查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。
SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i_ _' ESCAPE '\ ' ;
/*ESCAPE '＼' 表示“ ＼” 为换码字符*/
涉及空值的查询
/* IS NULL 或 IS NOT NULL, “IS” 不能用 “=” 代替*/
某些学生选修课程后没有参加考试，所以有选课记录，但没 有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
SELECT Sno，Cno
FROM SC
WHERE Grade IS NULL
查所有有成绩的学生学号和课程号
SELECT Sno，Cno
FROM SC
WHERE Grade IS NOT NULL;
多重条件查询
逻辑运算符:AND和OR来连接多个查询条件
- AND的优先级高于OR
- 可以用括号改变优先级
查询计算机系年龄在20岁以下的学生姓名。
SELECT Sname
FROM Student
WHERE Sdept= 'SE' AND Sage<20;
ORDER BY语句
ORDER BY子句:
-
可以按一个或多个属性列排序
-
升序：ASC;降序：DESC;缺省值为升序
查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列
SELECT Sno, Grade
FROM SC
WHERE Cno= ' 3 '
ORDER BY Grade DESC;
查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
SELECT *
FROM Student
ORDER BY Sdept, Sage DESC;
聚集函数
- 统计元组个数: COUNT(*)
- 统计一列中值的个数: COUNT([DISTINCT|ALL] <列名>)
- 计算一列值的总和(此列必须为数值型): SUM([DISTINCT|ALL] <列名>)
- 计算一列值的平均值(此列必须为数值型): AVG([DISTINCT|ALL] <列名>)
- 求一列中的最大值和最小值: MAX([DISTINCT|ALL] <列名>), MIN([DISTINCT|ALL] <列名>)
查询学生总人数
SELECT COUNT(*)
FROM Student;
查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno)
FROM SC;
计算1号课程的学生平均成绩
SELECT AVG(Grade)
FROM SC
WHERE Cno= ' 1 ';
查询学生201215012选修课程的总学分数。
SELECT SUM(Ccredit)
FROM SC,Course C
WHERE Sno='201215012' AND SC.Cno=C.Cno;
GROUP BY子句
细化聚集函数的作用对象
- 如果未对查询结果分组，聚集函数将作用于整个查询结果
- 对查询结果分组后，聚集函数将分别作用于每个组
- 按指定的一列或多列值分组，值相等的为一组
求各个课程号及相应的选课人数。
SELECT Cno，COUNT(Sno)
FROM SC
GROUP BY Cno;
查询选修了3门以上课程的学生学号。
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) >3;
/*WHERE子句中是不能用聚集函数作为条件表达式*/
查询平均成绩大于等于90分的学生学号和平均成绩
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
下面这种查询语句是不对的,因为WHERE子句中是不能用聚集函数作为条件表达式:
SELECT Sno, AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno;
HAVING短语与WHERE子句的区别
- 作用对象不同
- WHERE子句作用于基表或视图，从中选择满足条件的元组
- HAVING短语作用于组，从中选择满足条件的组
连接查询
连接查询：同时涉及两个以上的表的查询
连接条件或连接谓词：用来连接两个表的条件
/*一般格式*/
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND[<表名2>.]<列名3>
等值与非等值连接查询
/*连接运算符为=*/
查询每个学生及其选修课程的情况
SELECT Student.*, SC.*
FROM Student S, SC
WHERE S.Sno = SC.Sno;
连接操作的执行过程
- 嵌套循环法(NESTED-LOOP)
- 排序合并法(SORT-MERGE)
- 索引连接(INDEX-JOIN)
查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno AND
SC.Cno=' 2 ' AND SC.Grade>90;
执行过程:
1. 先从SC中挑选出Cno='2'并且Grade>90的元组形成一个中间关系
2. 再和Student中满足连接条件的元组进行连接得到最终的结果关系
自身连接
自身连接: 一个表与其自己进行连接,
需要给表起别名以示区别
由于所有属性名都是同名属性，因此必须使用别名前缀
/*自链接*/
查询每一门课的间接先修课(即先修课的先修课)
SELECT C1.Cno, C2.Cpno
FROM Course C1, Course C2
WHERE C1.Cpno = C2.Cno;
外连接
查询每个学生及其选修课程的情况
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno);
多表连接
多表连接：两个以上的表进行连接
查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno, Sname, Cname, Grade
FROM Student S, SC, Course C /*多表连接*/
WHERE S.Sno = SC.Sno AND SC.Cno = C.Cno;
嵌套查询
概述
- 一个SELECT-FROM-WHERE语句称为一个查询块
- 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
- 上层的查询块称为外层查询或父查询
- 下层查询块称为内层查询或子查询
- 子查询中不可以使用ORDER BY
SELECT Sname /*外层查询/父查询*/
FROM Student
WHERE Sno IN
( SELECT Sno /*内层查询/子查询*/
FROM SC
WHERE Cno= ' 2 ');
不相关查询
子查询的查询条件不依赖于父查询
- 由里向外 逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
相关子查询
相关子查询：子查询的查询条件依赖于父查询
- 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表。
- 然后再取外层表的下一个元组。
- 重复这一过程，直至外层表全部检查完为止。
带有IN谓词的子查询
查询与“刘晨”在同一个系学习的学生
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname= ' 刘晨 ');
查询选修了课程名为“信息系统”的学生学号和姓名
SELECT Sno,Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno IN
(SELECT Cno
FROM Course
WHERE Cname= '信息系统'
)
);
用连接查询实现
SELECT Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno AND
SC.Cno = Course.Cno AND
Course.Cname='信息系统';
带有比较运算符的子查询
当能确切知道内层查询返回单值时，可用比较运算符(>，<，=，>=，<=，!=或< >)
查询与“刘晨”在同一个系学习的学生
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept =
(SELECT Sdept
FROM Student
WHERE Sname= '刘晨');
找出每个学生超过他选修课程平均成绩的课程号
SELECT Sno, Cno
FROM SC x
WHERE Grade >=
(SELECT AVG(Grade)
FROM SC y
WHERE y.sno=x.SNO)
带有ANY(SOME)或ALL谓词的子查询
使用ANY或ALL谓词时必须同时使用比较运算,语义为：
>ANY 大于子查询结果中的某个值
ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
= ANY 大于等于子查询结果中的某个值
= ALL 大于等于子查询结果中的所有值
<= ANY 小于等于子查询结果中的某个值
<= ALL 小于等于子查询结果中的所有值
= ANY 等于子查询结果中的某个值
=ALL 等于子查询结果中的所有值(通常没有实际意义)
!=(或<>)ANY 不等于子查询结果中的某个值
!=(或<>)ALL 不等于子查询结果中的任何一个值
查询非软件学院中比软件学院任意一个学生年龄小的学生姓名和年龄
/*比任一个小,相当于比最大的小*/
SELECT Sname,Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
FROM Student
WHERE Sdept= ' SE ')
AND Sdept <> 'SE ' ; /*父查询块中的条件 */
用聚集函数实现本例
SELECT Sname,Sage
FROM Student
WHERE Sage <
(SELECT MAX(Sage)
FROM Student
WHERE Sdept= 'SE ')
AND Sdept <> ' SE ';
查询非软件学院中比软件学院所有学生年龄都小的学生姓名及年龄。
/*比所有小,相当于比最小的小*/
方法一：用ALL谓词
SELECT Sname,Sage
FROM Student
WHERE Sage < ALL
(SELECT Sage
FROM Student
WHERE Sdept= ' SE ')
AND Sdept <> ' SE ';
方法二：用聚集函数
SELECT Sname,Sage
FROM Student
WHERE Sage <
(SELECT MIN(Sage)
FROM Student
WHERE Sdept= ' SE ')
AND Sdept <>' SE ';
带有EXISTS谓词的子查询
EXISTS谓词
- 存在量词
- 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
- 若内层查询结果非空，则外层的WHERE子句返回真值
- 若内层查询结果为空，则外层的WHERE子句返回假值
- 由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。
NOT EXISTS谓词
- 若内层查询结果非空，则外层的WHERE子句返回假值
- 若内层查询结果为空，则外层的WHERE子句返回真值
查询所有选修了1号课程的学生姓名
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno= ' 1 ');
查询没有选修1号课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno='1');
用EXIST表示FORALL
( ∀ x ) P ≡ ┐ ( ∃ x ( ┐ P ) ) (\forall x)P\equiv \urcorner(\exist x(\urcorner P)) (∀x)P≡┐(∃x(┐P))
查询与“刘晨”在同一个系学习的学生
SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
(SELECT *
FROM Student S2
WHERE S2.Sdept = S1.Sdept AND
S2.Sname = '刘晨');
/*不太会......*/
查询选修了全部课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno= Student.Sno
AND Cno= Course.Cno
)
);
集合查询
集合操作的种类
- 并操作UNION
- 交操作INTERSECT
- 差操作EXCEPT
参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同
查询软件学院的学生及年龄不大于19岁的学生
SELECT *
FROM Student
WHERE Sdept= 'SE'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
/*
UNION：将多个查询结果合并起来时，系统自动去掉重复元组
UNION ALL：将多个查询结果合并起来时，保留重复元组
*/
查询选修了课程1或者选修了课程2的学生
SELECT Sno
FROM SC
WHERE Cno=' 1 '
UNION
SELECT Sno
FROM SC
WHERE Cno= ' 2 ';
查询软件学院的学生与年龄不大于19岁的学生的交集
方法一:集合查询
SELECT *
FROM Student
WHERE Sdept='SE'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19
方法二:使用and连接词
SELECT *
FROM Student
WHERE Sdept= 'SE' AND Sage<=19;
查询既选修了课程1又选修了课程2的学生
方法一:集合查询
SELECT Sno
FROM SC
WHERE Cno=' 1 '
INTERSECT
SELECT Sno
FROM SC
WHERE Cno='2 ';
方法而:and连接词加in
SELECT Sno
FROM SC
WHERE Cno=' 1 ' AND Sno IN
(SELECT Sno
FROM SC
WHERE Cno=' 2 ');
基于派生表的查询
子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的**临时派生表(**Derived Table)成为主查询的查询对象
找出每个学生超过他自己选修课程平均成绩的课程号
SELECT Sno, Cno
FROM SC, (SELECTSno, 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;
/*如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属性*/
查询所有选修了1号课程的学生姓名
SELECT Sname
FROM Student,(SELECT Sno FROM SC WHERE Cno=' 1 ') AS SC1
WHERE Student.Sno=SC1.Sno;
数据查询总结
SELECT语句的一般格式
SELECT [ALL|DISTINCT]
<目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
FROM <表名或视图名> [别名]
[ ,<表名或视图名> [别名]] …
|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];
目标列表达式的可选格式
-
- <表名>.*
- COUNT([DISTINCT|ALL]* )
- [<表名>.]<属性列名表达式>[,<表名>.]<属性列名表达式>]…
其中<属性列名表达式>可以是由属性列、作用于属性列的聚集函数和常量的任意算术运算(+，-，*，/)组成的运算公式
聚集函数的一般格式
WHERE子句的条件表达式的可选格式
数据更新
插入数据
插入元组
语句格式:
INSERT
INTO <表名> [(<属性列1>[,<属性列2 >…)]
VALUES (<常量1> [,<常量2>]… );
INTO子句
- 指定要插入数据的表名及属性列
- 属性列的顺序可与表定义中的顺序不一致
- 没有指定属性列：表示要插入的是一条完整的元组，且 属性列属性与表定义中的顺序一致
- 指定部分属性列：插入的元组在其余属性列上取空值
VALUES子句
- 提供的值必须与INTO子句匹配
- 值的个数
- 值的类型
将一个新学生元组(学号：201215128;姓名：陈冬;性别：男;所在系：IS;年龄：18岁)插入到Student表中
NSERT
INTO Student (Sno,Sname,Ssex,Sdept,Sage)
VALUES ('201215128','陈冬','男','IS',18);
插入一条选课记录( '200215128','1 ')
INSERT
INTO SC(Sno,Cno)
VALUES ('201215128 ',' 1 ');
关系数据库管理系统将在新插入记录的Grade列上自动地赋空值。
或者：
INSERT
INTO SC
VALUES (' 201215128 ',' 1 ',NULL);
插入子查结果
语句格式
INSERT
INTO <表名> [(<属性列1> [,<属性列2>… )]
子查询;
SELECT子句目标列必须与INTO子句匹配
1. 值的个数
2. 值的类型
对每一个系，求学生的平均年龄，并把结果存入数据库
第一步：建表
CREATE TABLE Dept_age
( Sdept CHAR(15)
Avg_age SMALLINT);
第二步：插入数据
INSERT
INTO Dept_age(Sdept,Avg_age)
SELECT Sdept，AVG(Sage)
FROM Student
GROUP BY Sdept;
- 关系数据库管理系统在执行插入语句时会检查所 插元组是否破坏表上已定义的完整性规则
- 实体完整性
- 参照完整性
- 用户定义的完整性
- NOT NULL约束
- UNIQUE约束
- 值域约束
修改数据
语句格式
UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件>];
功能
- 修改指定表中满足WHERE子句条件的元组
- SET子句给出<表达式>的值用于取代相应的属性列
- 如果省略WHERE子句，表示要修改表中的所有元组
三种修改方式:
- 修改某一个元组的值
- 修改多个元组的值
- 带子查询的修改语句
修改某一个元组的值
将学生201215121的年龄改为22岁
UPDATE Student
SET Sage=22
WHERE Sno=' 201215121 ';
修改多个元组的值
将所有学生的年龄增加1岁
UPDATE Student
SET Sage= Sage+1;
带子查询的修改语句
将计算机科学系全体学生的成绩置零。
UPDATE SC
SET Grade=0
WHERE Sno IN
(SELETE Sno
FROM Student
WHERE Sdept= 'CS' );
- 关系数据库管理系统在执行修改语句时会检查修 改操作是否破坏表上已定义的完整性规则
- 实体完整性
- 主码不允许修改
- 用户定义的完整性
- NOT NULL约束
- UNIQUE约束
- 值域约束
删除数据
语句格式
DELETE
FROM <表名>
[WHERE <条件>];
功能
- 删除指定表中满足WHERE子句条件的元组
WHERE子句
- 指定要删除的元组
- 缺省表示要删除表中的全部元组，表的定义仍在字 典中
三种删除方式
- 删除某一个元组的值
- 删除多个元组的值
- 带子查询的删除语句
删除某一个元组的值
删除学号为201215128的学生记录。
DELETE
FROM Student
WHERE Sno= 201215128 ';
删除多个元组的值
删除所有的学生选课记录。
DELETE
FROM SC;
带子查询的删除语句
删除计算机科学系所有学生的选课记录。
DELETE
FROM SC
WHERE Sno IN
(SELETE Sno
FROM Student
WHERE Sdept= 'CS') ;
视图
视图的特点
- 是从一个或几个基本表(或视图)导出的“虚表”。
- 只存放视图的定义，不存放视图对应的数据。
- 基表中的数据发生变化，从视图中查询出的数据也随之改变。
建立视图
语句格式
CREATE VIEW
<视图名> [(<列名> [,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION];
WITH CHECK OPTION
- 对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)
子查询可以是任意的SELECT语句，是否可以含有ORDER BY子句和DISTINCT短语，则决定具体系统的实现。
组成视图的属性列名：全部省略或全部指定
- 全部省略:
- 由子查询中SELECT目标列中的诸字段组成
- 明确指定视图的所有列名:
- 某个目标列是聚集函数或列表达式
- 多表连接时选出了几个同名列作为视图的字段
- 需要在视图中为某个列启用新的更合适的名字
关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。
在对视图查询时，按视图的定义从基本表中将数据查出。
建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS';
WITH CHECK OPTION;
/*定义IS_Student 视图时加上了WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept='IS'的条件*/
基于多个基表的视图
建立信息系选修了1号课程的学生的视图(包括
学号、姓名、成绩)。
CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept= 'IS' AND
Student.Sno=SC.Sno AND SC.Cno= '1';
基于视图的视图
建立信息系选修了1号课程且成绩在90分以上的 学生的视图。
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;
带表达式的视图
定义一个反映学生出生年份的视图。
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2014-Sage
FROM Student;
分组视图
将学生的学号及平均成绩定义为一个视图
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
删除视图
语句的格式：
DROP VIEW <视图名>[CASCADE];
- 该语句从数据字典中删除指定的视图定义
- 如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除
- 删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除
删除视图BT_S和IS_S1
DROP VIEW BT_S; /*成功执行*/
DROP VIEW IS_S1; /*拒绝执行*/
要删除IS_S1，需使用级联删除：
DROP VIEW IS_S1 CASCADE;
查询视图
用户角度：查询视图与查询基本表相同
关系数据库管理系统实现视图查询的方法:
视图消解法:
- 进行有效性检查
- 转换成等价的对基本表的查询
- 执行修正后的查询
在信息系学生的视图中找出年龄小于20岁的学生
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;
视图消解转换后的查询语句为：
SELECT Sno,Sage
FROM Student
WHERE Sdept= 'IS' AND Sage<20;
更新视图
/*修改*/
将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。
UPDATE IS_Student
SET Sname= '刘辰'
WHERE Sno= ' 201215122 ';
转换后的语句 ：
UPDATE Student
SET Sname= '刘辰'
WHERE Sno= ' 201215122 ' AND Sdept= 'IS';
/*插入*/
向信息系学生视图IS_S中插入一个新的学生记录， 其中学号为”201215129”，姓名为”赵新”，年龄为20岁
INSERT
INTO IS_Student
VALUES(‘201215129’,’赵新’,20);
转换为对基本表的更新：
INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES('200215129 ','赵新',20,'IS' );
/*删除*/
删除信息系学生视图IS_Student中学号为”201215129”的记录
DELETE
FROM IS_Student
WHERE Sno= ' 201215129 ';
转换为对基本表的更新：
DELETE
FROM Student
WHERE Sno= ' 201215129 ' AND Sdept= 'IS';
更新视图的限制
- 若视图是由两个以上基本表导出的，则此视图不允许更新。
- 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。
- 若视图的字段来自聚集函数，则此视图不允许更新。
- 若视图定义中含有GROUP BY子句，则此视图不允许更新。
- 若视图定义中含有DISTINCT短语，则此视图不允许更新。
- 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。
嵌入式SQL
SQL语言提供了两种不同的使用方式:
- 交互式(isql)
- 嵌入式(esql)
嵌入式SQL的处理过程
主语言
嵌入式SQL是将SQL语句嵌入程序设计语言中，被嵌入的程序设计语言，如C、C++、Java，称为宿主语言，简称主语言。
处理过程
预编译方法
嵌入式SQL的处理过程
为了区分SQL语句与主语言语句，所有SQL语句必须加前缀EXEC SQL，
主语言为C语言时，语句格式:
EXEC SQL <SQL语句>;
嵌入式SQL语句与主语言之间的通信
将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句:
-
SQL语句
- 描述性的面向集合的语句
- 负责操纵数据库
-
高级语言语句
- 过程性的面向记录的语句
- 负责控制逻辑流程
数据库工作单元与源程序工作单元之间的通信:
- 向主语言传递SQL语句的执行状态信息，使主语言能够据此控制程序流程，主要用SQL通信区实现
- 主语言向SQL语句提供参数，主要用主变量实现
- 将SQL语句查询数据库的结果交主语言处理，主要用主变量和游标实现
SQL通信区
定义
SQLCA( SQL Communication Area): SQLCA是一个数据结构
用途
SQL语句执行后，系统反馈给应用程序信息
- 描述系统当前工作状态
- 描述运行环境
这些信息将送到SQL通信区中
应用程序从SQL通信区中取出这些状态信息，据此决定接下来执行的语句
使用方法
- 定义SQLCA
- 用EXEC SQL INCLUDE SQLCA定义
- 使用SQLCA
- SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE
- 如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示出错
- 应用程序每执行完一条SQL 语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理
主变量
定义
嵌入式SQL语句中可以使用主语言的程序变量来输入
在SQL语句中使用的主语言程序变量简称为主变量
类型
- 输入主变量
- 由应用程序对其赋值，SQL语句引用
- 输出主变量
- 由SQL语句对其赋值或设置状态信息，返回给应用程序
- 指示变量
- 是一个整型变量，用来“指示”所指主变量的值或条件
- 一个主变量可以附带一个指示变量（Indicator Variable）
- 指示变量的用途
- 指示输入主变量是否为空值
- 检测输出变量是否为空值，值是否被截断
使用方法
说明主变量和指示变量
BEGIN DECLARE SECTION
...
.../*说明主变量和指示变量*/
END DECLARE SECTION
- 使用主变量
- 说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现
- 为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（:）作为标志
- 使用指示变量
- 指示变量前也必须加冒号标志
- 必须紧跟在所指主变量之后
游标
定义
-
游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果
-
每个游标都有一个名字
-
用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理
用途
- SQL语言与主语言具有不同数据处理方式
- SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录
- 主语言是面向记录的，一组主变量一次只能存放一条记录
- 仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求
- 嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式
建立和关闭数据库连接
建立数据库连接
EXEC SQL CONNECT TO target[AS connection-name][USER user-name];
-
target是要连接的数据库服务器
- 常见的服务器标识串，如@:
- 包含服务器标识的SQL串常量
- DEFAULT
-
connect-name是可选的连接名，连接名必须是一个有效的标识符
-
在整个程序内只有一个连接时可以不指定连接名。
-
程序运行过程中可以修改当前连接。
EXEC SQL SET CONNECTION connection-name|DEFAULT;
关闭数据库连接
EXEC SQL DISCONNECT [connection];
程序实例
依次检查某个系的学生记录，交互式更新某些学生年龄
EXEC SQL BEGIN DECLARE SECTION; /*主变量说明开始*/
char Deptname[20];
char Hsno[9];
char Hsname[20];
char Hssex[2];
int HSage;
int NEWAGE;
EXEC SQL END DECLARE SECTION; /*主变量说明结束*/
long SQLCODE;
EXEC SQL INCLUDE SQLCA; /*定义SQL通信区*/
int main(void) /*C语言主程序开始*/
{
int count = 0;
char yn; /*变量yn代表yes或no*/
printf("Please choose the department name(CS/MA/IS): ");
scanf("%s",deptname); /*为主变量deptname赋值*/
EXEC SQL CONNECT TO TEST@localhost:54321 USER "SYSTEM"/"MANAGER"; /*连接数据库TEST*/
EXEC SQL DECLARE SX CURSOR FOR /*定义游标SX*/
SELECT Sno,Sname,Ssex,Sage /*SX对应的语句*/
FROM Student
EXEC SQL OPEN SX; /*打开游标SX，指向查询结果的第一行*/
for ( ; ; ) /*用循环结构逐条处理结果集中的记录*/
{
EXEC SQL FETCH SX INTO :HSno,:Hsname,:HSsex,:HSage; /*推进游标，将当前数据放入主变量*/
if (SQLCA.SQLCODE!= 0) /*SQLCODE != 0，表示操作不成功*/
break; /*利用SQLCA中的状态信息决定何时退出循环*/
if(count++ == 0) /*如果是第一行的话，先打出行头*/
printf("\n%-10s %-20s %-10s %-10s\n","Sno“,"Sname“,"Ssex", "Sage");
printf("%-10s %-20s %-10s %-10d\n“,HSno,Hsname,Hssex,HSage); /*打印查询结果*/
printf(“UPDATE AGE(y/n)?”); /*询问用户是否要更新该学生的年龄*/
do{scanf("%c",&yn);}
while(yn != 'N' && yn != 'n' && yn != 'Y' && yn != 'y');
if (yn == 'y' || yn == 'Y') /*如果选择更新操作*/
{
printf("INPUT NEW AGE:");
scanf("%d",&NEWAGE); /*用户输入新年龄到主变量中*/
EXEC SQL UPDATE Student /*嵌入式SQL更新语句*/
SET Sage = :NEWAGE
WHERE CURRENT OF SX;
} /*对当前游标指向的学生年龄进行更新*/
}
EXEC SQL CLOSE SX; /*关闭游标SX，不再和查询结果对应*/
EXEC SQL COMMIT WORK; /*提交更新*/
EXEC SQL DISCONNECT TEST; /*断开数据库连接*/
}
不用游标的SQL语句
不用游标的SQL语句的种类
- 说明性语句
- 数据定义语句
- 数据控制语句
- 查询结果为单记录的SELECT语句
- 非CURRENT形式的增删改语句
查询结果为单记录的SELECT语句
这类语句不需要使用游标，只需用INTO子句指定存放查询结果的主变量
根据学生号码查询学生信息
EXEC SQL SELECT Sno,Sname,Ssex,Sage,Sdept
INTO:Hsno,:Hname,:Hsex,:Hage,:Hdept
FROM Student
WHERE Sno=:givensno;/*把要查询的学生的学号赋给为了主变量givensno*/
- INTO子句、WHERE子句和HAVING短语的条件表达式中均可以使用主变量
- 查询返回的记录中，可能某些列为空值NULL
- 如果查询结果实际上并不是单条记录，而是多条记录，则程序出错，关系数据库管理系统会在SQLCA中返回错误信息
查询某个学生选修某门课程的成绩。假设已经把将要查询的学生的学号赋给了主变量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形式的增删改语句
在UPDATE的SET子句和WHERE子句中可以使用主变量，SET子句还可以使用指示变量
修改某个学生选修1号课程的成绩
EXEC SQL UPDATE SC
SET Grade=:newgrade /*修改的成绩已赋给主变量：newgrade*/
WHERE Sno=:givensno; /*学号赋给主变量：givensno*/
某个学生新选修了某门课程，将有关记录插入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语句
- CURRENT形式的DELETE语句
查询结果为多条记录的SELECT语句
使用游标的步骤
- 说明游标
- 打开游标
- 推进游标指针并取当前记录
- 关闭游标
说明游标
使用DECLARE语句
EXEC SQL DECLARE <游标名> CURSOR
FOR <SELECT语句>;
功能
是一条说明性语句，这时关系数据库管理系统并不执行SELECT语句
打开游标
使用OPEN语句
语句格式
EXEC SQL OPEN <游标名>;
功能
-
打开游标实际上是执行相应的SELECT语句，把查询结果取到缓冲区中
-
这时游标处于活动状态，指针指向查询结果集中的第一条记录
推进游标指针并取当前记录
使用FETCH语句
语句格式
EXEC SQL FETCH <游标名>
INTO <主变量>[<指示变量>][,<主变量>[<指示变量>]]...;
功能
指定方向推动游标指针，同时将缓冲区中的当前记录取出来送至主变量供主语言进一步处理
关闭游标
使用CLOSE语句
语句格式
EXEC SQL CLOSE <游标名>
-
功能
- 关闭游标，释放结果集占用的缓冲区及其他资源
-
说明
- 游标被关闭后，就不再和原来的查询结果集相联系
- 被关闭的游标可以再次被打开，与新的查询结果相联系
CURRENT形式的UPDATE和DELETE语句
如果只想修改或删除其中某个记录
-
用带游标的SELECT语句查出所有满足条件的记录
-
从中进一步找出要修改或删除的记录
-
用CURRENT形式的UPDATE语句和DELETE语句修改或删除之
-
UPDATE语句和DELETE语句中要用子句WHERE CURRENT OF <游标名> /*表示修改或删除的是最近一次取出的记录，即游标指针指向的记录*/
不能使用CURRENT形式的UPDATE语句和DELETE语句
- 当游标定义中的SELECT语句带有UNION或ORDERBY子句
- 该SELECT语句相当于定义了一个不可更新的视图。
动态SQL
静态SQL与动态SQL的比较
- 静态嵌入式SQL
- 静态嵌入式SQL语句能够满足一般要求
- 无法满足要到执行时才能够确定要提交的SQL语句、查询的条件
- 动态嵌入式SQL
- 允许在程序运行过程中临时“组装”SQL语句
- 支持动态组装SQL语句和动态参数两种形式
动态SQL使用过程:
- 使用SQL语句主变量
- 动态参数
- 执行准备好的语句（EXECUTE）
使用SQL语句主变量
程序主变量包含的内容是SQL语句的内容，而不是原来保存数据的输入或输出变量
SQL语句主变量在程序执行期间可以设定不同的SQL语句，然后立即执行
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语句*/
动态参数
-
动态参数
- SQL语句中的可变元素
- SQL语句中的可变元素
-
和主变量的区别
- 动态参数的输入不是编译时完成绑定
- 而是通过 PREPARE语句准备主变量和执行语句EXECUTE绑定数据或主变量来完成
-
使用动态参数的步骤
-
声明SQL语句主变量
-
准备SQL语句（PREPARE）
EXEC SQL PREPARE <语句名> FROM <SQL语句主变量>;
-
执行准备好的语句
语句格式
EXEC SQL EXECUTE <语句名>
[INTO <主变量表>]
[USING <主变量或常量>];
向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 */
2922
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
