SQL 语言基础

SQL概述与数据库定义

语句格式：CREATE TABLE <表名> (<列名><数据类型>[列级完整性约束条件]
[，<列名><数据类型>[列级完整性约束条件]]…
[，<表级完整性约束条件>])；
注：[ ]表示可选，< >表示必填。

如：CHECK (Sex='男' OR Sex='女')，CHECK (余额>=0)，CHECK (年龄>=18 AND 年龄<=60)

例：入职日期小于等于离职日期，可以用 CHECK (入职日期<=离职日期）
CREATE TABLE E
(Eno CHAR(8) PRIMARY KEY,
入职日期 DATE,
离职日期 DATE,
CHECK (入职日期<=离职日期） );

CREATE ASSERTION <断言名> CHECK (<条件>)
例：教学数据库的模式Students、SC、C中创建一个约束ASSE_SC1：不允许男同学选修“张勇”老师的课。
CREATE ASSERTION ASSE_SC1 CHECK
(NOT EXISTS
(SELECT * FROM SC WHERE Cno IN
(SELECT Cno FROM C WHERE TEACHER='张勇'）
AND Sno IN
(SELECT Sno FROM Students WHERE SEX='M')));

语句格式：ALTER TABLE <表名> [ADD <新列名><数据类型>[列级完整性约束条件]]
[DROP <完整性约束名>]
[Modify <列名><数据类型>])；
如：
ALTER TABLE S ADD Zap CHAR(6);
//在表S中新增一列ZAP，该列的数据为空
ALTER TABLE S MODIFY Status INT;
//将表S的Status属性的数据类型更改为INT
ALTER TABLE S ADD Constraint C_cno CHECK(......) //在表S中新增CHECK约束，取名为C_cno

语句格式：DROP TABLE <表名>
如：
DROP TABLE S；
//表删除后，不再是数据库模式的一部分

语句格式：CREATE [UNIQUE][CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>]]…);
• 次序：ASC(升序)或DESC（降序)，默认为升序。
• UNIQUE：表明此索引的每一个索引值只对应唯一的数据记录。
• CLUSTER：表明要建立的索引是聚簇索引，意为索引项的顺序是与表中记录的物理顺序一致的索引组织
如：
CREATE UNIQUE INDEX S_Sno on S(Sno);
//在表S的Sno列上建立索引S_Sno，默认为升序
CREATE UNIQUE INDEX P_Pno on P(Pno);
//在表P的Pno列上建立索引P_Pno，默认为升序
CREATE UNIQUE INDEX J_Jno on J(Jno);
//在表J的Jno列上建立索引J_Jno，默认为升序
CREATE UNIQUE INDEX SPJ_NO on SPJ(Sno ASC,Pno DESC,Jno ASC);
//在表SPJ上建立索引SPJ_NO，属性Sno按升序，Pno按降序，Jno按升序

语句格式：DROP INDEX <索引名>
例：DROP INDEX StudentIndex；
//删除索引StudentIndex

例：学生关系模式S(Sno,Sname,Sage,Sex,SD,Email,Tel),建立计算机系（CS表示计算机系）学生的
视图，并要求进行修改、插入操作时保证该视图只有计算机系的学生。
CREATE VIEW CS_STUDENT
//创建视图CS_STUDENT
AS SELECT Sno,Sname,Sage,Sex
//选择学号、姓名、年龄、性别列
FROM Student
//从学生表中查询
Where SD='CS'
//选择系名等于“CS”的行
WITH CHECK OPTION;
//以后对该视图进行修改、插入操作时DBMS
会自动加上SD='CS'的条件，保证视图中只有计算机系的学生

语句格式：DROP VIEW 视图名
如：DROP VIEW CS_STUDENT //删除视图CS_STUDENT

数据操作

授权与触发器

语句格式： GRANT 权限 ON TABLE/DATABASE 表名/数据库名 TO 用户1,用户2… /PUBLIC
[WITH GRANT OPTION];
PUBLIC：表示将权限授予所有人
WITH GRANT OPTION：表示获得了这个权限的用户还可以将权限赋给其他用户。

（1）将对供应商S、零件P、项目J的所有操作权限赋给用户User1及User2。
GRANT ALL PRIVILEGES
ON TABLE S,P,J
TO USER1,USER2;

GRANT INSERT
ON TABLE S
TO USER1 WITH GRANT OPTION;

GRANT CREATETAB
ON DATABASE SPJ
TO User1;

语句格式： REVOKE 权限 ON TABLE/DATABASE 表名/数据库名
FROM 用户1,用户2… /PUBLIC
[RESTRICT | CASCADE];
RESTRICT：表示只收回语句中指定的用户的权限
CASCADE：表示除了收回指定用户的权限外，还收回该用户赋予的其他用户的权限。
例：将用户User1及User2对供应商S、零件P、项目J的所有操作权限收回：
REVOKE ALL PRIVILEGES ON TABLE S,P,J FROM User1,User2;
将所有用户对供应商S的所有查询权限收回：
REVOKE SELECT ON TABLE S FROM PUBLIC;
将User1用户对供应商S的供应商编号Sno的修改权限收回。
REVOKE UPDATE(Sno) ON TABLE S FROM User1;

CREATE TRIGGER 触发器名称 [BEFORE | AFTER]
[DELETE | INSERT | UPDATE OF 列名]
//触发事件
ON 表名
[REFERENCING <临时视图名>]
[FOR EACH ROW | FOR EACH STATEMENT]
[WHEN <触发条件>]
//WHEN后面跟触发条件，指明当什么条件满足时，执行下面的触发动作
BEGIN
<触发动作>
//BEGIN...END 中定义触发动作，即当触发条件满足时，需要数据库做什么
END [触发器名称]
BEFORE/AFTER：指明是在执行触发语句之前激发触发器还是执行触发语句之后激发触发器。
DELETE：当一个DELETE语句从表中删除行时激发触发器。
INSERT：当一个INSERT语句向表中插入行时激发触发器。
UPDATE/UPDATE OF(列名)：当UPDATE修改表中的值时，激发触发器，也可加（OF 列名）指定是某一列的值被修改时激发触发器。
REFERENCING：触发器运行过程中，系统会生成两个临时视图，分别存放更新前和更新后的值，对于行级触发器，为OLD ROW
和NEW ROW，对于语句级触发器，为OLD TABLE和NEW TABLE。
REFERENCING new row AS nrow / REFERENCING old row AS orow 。
FOR EACH ROW：表示为行级触发器，对每一个被影响的元组（即每一行）执行一次触发过程。
FOR EACH STATEMENT：表示为语句级触发器，对整个事件只执行一次触发过程，为默认方式。

ALTER TRIGGER <触发器名> [BEFORE|AFTER]
DELETE|INSERT|UPDATE OF [列名]
ON 表名|视图名
AS
BEGIN
要执行的SQL语句
END

语句格式：
DROP TRIGGER <触发器名>

嵌入式SQL与存储过程

EXEC SQL <SQL语句>

CREATE PROCEDURE 存储过程名(IN|OUT|IN OUT 参数1 数据类型，IN|OUT|IN OUT 参数2 数据类型…)
[AS]
//参数的数据类型只需要指明类型名即可，不需要指定宽度。具体宽度由外部调用者决定
BEGIN
<SQL语句>
END
IN：为默认值，表示该参数为输入型参数，在过程体中值一般不变。
OUT：表示该参数为输出参数，可以作为存储过程的输出结果，供外部调用者使用。
IN OUT： 既可作为输入参数，也可作为输出参数。