第九章 SQL 语言
1、SQL是集数据定义和数据操纵为一体的数据库语言。
数据定义子语言DDL，用来定义数据库模式。DDL包括数据库模式定义，数据库存储结构和存取方法定义，以及数据库模式的修改删除功能。
数据定义子语言的处理程序也分为了数据库模式定义处理程序，和数据库存储结构和存取方法处理程序。前者接收用DDL表示的数据模式定义，翻译成内部表示形式，存储到数据字典中；后者接收数据库存储结构和存取方法定义，在存储设备上创建相关的数据库文件，建立物理数据库。
数据操纵子语言，通常有这样几种操作：查询、插入、删除、修改。后三种应该可以都纳入更新的范畴。
嵌入式SQL，宿主语言
2、SQL是一种通用的、功能强大的关系数据库语言，它的主要功能包括数据查询、数据操纵、数据定义和数据控制。
SQL的特点有：
综合统一
高度非过程化
面向集合的操作方式
两种使用方式， 一是在终端上键入SQL命令直接操作数据库，另一种是将SQL嵌入到高级语言中去。
简洁、易用，完成核心功能只用了9个动词，包括了4类：数据查询（SELECT）、数据定义（CREATE、DROP、ALTER）、数据操纵（INSERT、UPDATE、DELETE）、数据控制（GRANT、REVOKE）。
SQL支持关系数据库的三级模式结构，其中视图对应外模式，基本表对应模式，存储文件对应内模式。
SQL的基本组成：DDL、DML、事务控制、嵌入式SQL和动态SQL、完整性、权限管理。
3、数据库定义
（一）创建表： CREATE TABLE ＜表名＞（＜列名＞＜数据类型＞[列级完整性约束]
[，＜列名＞＜数据类型＞[列级完整性约束]]...
[，＜表级完整性约束条件＞]）；
其中列级完整性约束条件有：NULL和UNIQUE。
例9.1 建立一个供应商和零件数据库。其中供应商表S（Sno,Sname,Status,City）的属性分别表示供应商代码、姓名、状态、所在城市。“零件”表P（Pno,Pname,Color,Weight,City）的属性分别表示零件号、零件名、颜色、重量及产地。其中数据库要满足这样的要求：
1）供应商代码不能为空，且值是惟一的，供应商名也是惟一的；
2）零件号不能为空，且值是惟一的。零件名不能为空；
3）一个供应商可以供应多个零件，而一个零件可以由多个供应商供应。
解：供应商与零件之间需要建立一个关系模式，二者之间是一个多对多的关系，新生成的关系模式的码应该是供应商的码和零件表的码，以及二者联系的属性构成。如SP（Sno,Pno,Qty） Qty表示数量。
CREATE TABLE S(Sno CHAR(5) NOT NULL UNIQUE,
Sname CHAR(30) UNIQUE,
Status CHAR(8),
City CHAR(20)
PRIMARY KEY (Sno));
CREATE TABLE P(Pno CHAR(6) NOT NULL UNIQUE,
Pname CHAR(30) NOT NULL,
Color CHAR(8),
Weight NUMERIC(6,2),
City CHAR(20)
PRIMARY KEY(Pno));
CREATE TABLE SP(Sno CHAR(5),
Pno CHAR(6),
Qty NUMERIC(9)
PRIMARY KEY(Sno,Pno),
FOREIGN KEY(Sno) REFERENCES S(Sno),
FOREIGN KEY(Pno) REFERENCES P(Pno));
（二）修改表和删除表
ALTER TABLE ＜表名＞[ADD＜新列名＞＜数据类型＞[完整型约束条件]]
[DROP ＜完整性约束名＞]
[MODIFY ＜列名＞＜数据类型＞]
DROP TABLE ＜表名＞
（三）定义和删除索引
数据库中的索引就是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的指针清单。作用如下：
通过创建惟一的索引，可以保证数据记录的惟一性；
加快数据检索速度；
加速表与表之间的连接，由其在实现数据的参照完整性方面有特别意义；
在使用ORDER BY ，GROUP BY语句时可以明显地减少计算时间；
使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。
分为聚集索引和非聚集索引
聚集索引，对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，叶子节点中存储的是实际数据；
非聚集索引，具有完全独立于数据行的结构，不必对物理数据页中的数据按列排序，叶子节点存储的是组成非聚集索引的关键字和行定位器。
建立索引：CREATE [UNIQUE][CLUSTER] INDEX ＜索引名＞
ON ＜表名＞（＜列名＞[＜次序＞][，＜列名＞[＜次序＞]]...）；
其中次序可选ASC，DSC，默认为ASC
UNIQUE表明此索引的每一个索引值只对应惟一的数据记录。
CLUSTER表示要建立的索引是聚簇索引，即索引项的顺序是与表中记录的物理顺序一致的索引组织。
几个例子： CREATE UNIQUE INDEX S-SNO ON S(Sno);
CREATE UNIQUE INDEX P-PNO ON P(PNO);
CREATE UNIQUE INDEX SPJ-NO ON SPJ(SNO ASC,PNO DESC, JNO ASC)。
删除索引： DROP INDEX ＜索引名＞
（四）定义、删除、更新视图
视图的创建：CREATE VIEW 视图名 （列表名）
AS SELECT 查询子句
[WITH CHECK OPTION]；
其中查询子句可以是任意复杂的select语句，但一般不能出现order by , distinct。
WITH CHECK OPTION，表示在对视图进行更新、插入或删除操作时，要保证满足子查询中的条件表达式。
例如 CREATE VIEW CS－STUDENT
AS SELECT SNO，SNAME，SAGE，SEX
FROM STUDENDS
WHERE SD＝‘CS’
WITH CHECK OPTION；
其中使用了with check option，所以在对视图插删操作时，要保证SD＝‘CS’的条件成立。
视图的删除：DROP VIEW 视图名
4、数据操作，SELECT，INSERT，DELETE，UPDATE
（一）SELECT基本结构
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>]...
FROM <表名或视图名>[,<表名或视图名>]
[WHERE <条件表达式>]
[GROUP BY <列名 1> [HAVING <条件表达式>]]
[ORDER BY <列名 2> [ASC|DESC]...]
其中，子句顺序是：SELECT、FROM、WHERE、GROUP BY、HAVING和ORDER BY。HAVING只能和GROUP BY搭配使用。
SELECT对应关系代数运算中的投影运算；FROM对应笛卡尔积；WHERE对应选择。
SELECT查询中没有全程量词，也没有逻辑蕴涵，但可以通过谓词转换来实现。？
（二）简单查询
（三）连接查询
检索至少选修了课程号为C1和C3的学生号：SELECT Sno FROM SC SCX, SC SCY WHERE SCX.Sno = SCY.Sno AND SCX.Cno = 'C1' AND SCY.Cno = 'C3'；
（四）子查询与聚集函数， 子查询也叫嵌套查询。
例：检索选修课程名为MS的学生号和学生姓名。
SELECT Sno, Sname
FROM Students
WHERE Sno IN
(SELECT Sno FROM SC
WHERE Cno IN
(SELECT Cno FROM C WHERE Cname = 'MS'));
聚集函数：AVG, MIN, MAX, SUN, COUNT
使用谓词ANY和ALL必须同时使用比较运算符，其含义与等价的转换关系如下：
＞ANY －－－ ＞MIN
＞ALL －－－ ＞MAX
＜ANY －－－ ＜MAX
＝ANY －－－ IN
＜＞ALL－－－ NOT IN
几个例子：查询其他系比计算机系CS所有学生年龄都要小的学生姓名及年龄。
SELECT Sname, Sage
FROM Students
WHERE Sage < ALL
(SELECT Sage
FROM Students
WHERE SD = 'CS')
AND SD<>'CS';
用＜MIN代替上面的＜ALL：
SELECT Sname, Sage
FROM Students
WHERE Sage <
(SELECT MIN(Sage)
FROM Students
WHERE SD = 'CS')
AND SD<>'CS';
（五）分组查询
GROUP BY 子句
HAVING 子句，如果在元组被分组之前需要按某种方式加以限制，使不需要的分组为空，可以在GROUP BY子句后面加一个HAVING子句。
注意：空值在任何聚集操作中都会被忽视，COUNT（＊）是计算某个关系中所有元组数目之种，但COUNT（A）是计算A属性中非空的元组个数之和。
例：针对供应商数据库中的S、P、J。SPJ关系，查询哪一个工程至少用了3家供应商（包含3家）供应的零件的平均数量，并按工程号降序排列。
SELECT JNO，AVG（QTY）
FROM SPJ
GROUP BY JNO
HAVING COUNT（DISTINCT（SNO））＞2
ORDER BY JNO DESC；
（六）别名运算
（七）字符串操作， 使用操作符LIKE的模式匹配。%匹配任意字符串，_ 可以匹配任意一个字符。
在Like中可以使用转义字符，将特殊字符当作普通字符处理，如反斜杠“\"。
（八）集合操作， 保留字UNION，INTERSECT，EXCEPT分别对应并、交、差。保留字用于两个查询时，其两侧应用括号括起来。
例：学生和教师的关系模式如下，查询既是女研究生又是教师且工资大于1500元的名字和地址。
(SELECT Name, Address
FROM Students
WHERE SEX='女' AND Type='研究生')
INTERSECT
(SELECT Name, Address
FROM Teachers
WHERE Salary >=1500)
查询不是教师的学生：(SELECT Name, Address FROM Students)
EXCEPT
(SELECT Name, Address FROM Teachers)
（九）视图的查询和删除
视图的查询：当查询视图表时，通常先将其转换成等价的对基本表的查询，然后执行查询语句。即系统先从数据字典中取出该视图的定义，然后与视图中的查询语句结合起来，形成一个修正的查询语句。
视图更新要遵守的规则：
从多个基本通过连接操作导出的视图不允许更新；
对使用了分组、集函数操作的视图不允许更新；
若视图是从单个基本表通过投影、选取操作导出的，则允许进行更新操作。
WITH子句，将一个复杂的查询分解成一小视图？？
（十）插入、删除和修改语句
插入： INSERT INTO 表名（字段名[，字段名]...)
VALUES(常量[，常量]...);
删除： DELETE FROM 表名
WHERE 条件表达式；
修改： UPDATE 表名
SET 列名＝值表达式
[WHERE条件表达式]
5、SQL中的授权
数据库中的完整性是指数据库的正确性和相容性。
（一）主键约束 PRIMARY KEY
完整性约束条件：完整性约束条件作用的对象有关系、元组、列3种，每种又分为静态、动态两类。
完整性控制：有3方面的功能，定义功能、检测功能、处理功能。这样来保证实现对数据的完整性控制。检查是否违背完整性约束的时机 有两个：立即执行约束和延迟执行约束。前者在一条语句执行完后立即检查，后者在整个事务执行完成后进行。
实体完整性（PRIMARY KEY子句），关系中只能有一个主键，声明主键的方法有两个，就是primary key放的位置不同。
如， CREATE TABLE Students
(Sno CHAR(8),
Sname CHAR(10),
Sex CHAR(1),
Sdept CHAR(20),
Sage NUMBER(3),
PRIMARY KEY(Sno));
或 CREATE TABLE Students
(Sno CHAR(8) PRIMARY KEY,
Sname CHAR(10),
Sex CHAR(1),
Sdept CHAR(20),
Sage NUMBER(3));
（二）外键约束 FOREIGN KEY（参照完整性）
格式： FOREIGN KEY（属性名）REFERENCES 表名（属性名）
[ON DELETE[ CASCADE|SET NULL]]
ON DELETE CASCADE指明删除参照关系的元组时，同时删除参照关系中的元组。
（三）属性值上的约束 NULL和CHECK
如果要求某属性为空，在定义时在数据类型的后面加上NOT NULL。
如， CREATE TABLE Students
(Sno CHAR(8),
Sname CHAR(10) NOT NULL,
Sex CHAR(1),
Sdept CHAR(20),
Sage NUMBER(3),
PRIMARY KEY(Sno));
在Students表中，要求男生的年龄在15－25之间，女生的年龄在15－24之间。
如， CREATE TABLE Students
(Sno CHAR(8),
Sname CHAR(10) NOT NULL,
Sex CHAR(1),
Sdept CHAR(20),
Sage NUMBER(3),
PRIMARY KEY(Sno))
CHECK(Sage >=15 AND ((SEX='M' AND Sage<=25) OR
(SEX='F' AND Sage<24)));
（四）全局约束 CREATE ASSERTIONS
全局约束是指一些较复杂的完整性约束，会涉及到多个属性间的联系或多个关系间的联系。分为两种：基于元组的检查子句和断言。
1）使用CHECK子句对单个关系的元组值加以约束，可以在关系的定义中的任何地方加上CHECK及约束条件；
2）断言： CREATE ASSERTION ＜断言名＞ CHECK（＜条件＞）
例如，在教学数据库模式Students,SC,C中加一个约束，不允许男同学选修“张勇”教师的课。
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')));
又如，在Students,SC,C中有一个约束，每门课最多允许50名男同学选修。
CREATE ASSERTION ASSE－SC2 CHECK
（50＞＝ALL（SELECT COUNT（SC.Sno)
FROM Students,SC
WHERE Students.Sno=SC.Sno AND SEX='M'
GROUP BY Cno));
（五）授权与销权，DBMS数据控制应具有这样的功能，通过GRANT和REVOKE将授权通知系统并存入数据字典；当用户提出请求时，检查其授权情况。
授权语句格式：
GRANT ＜权限＞[，＜权限＞]...
[ON＜对象类型＞＜对象名＞]
TO ＜用户＞[，＜用户＞]...
[WITH GRANT OPTION];
PUBLIC与WITH GRANT OPTION：PUBLIC参数可以将权限授给所有用户；后者使获得授权的用户还可以将此权限授给其它用户。
例如，将对供应商S、零件P及项目J的所有操作权限授给用户User1及User2。
GRANT ALL PRIVILEGES ON TABLE S, P, J TO User1,User2;
将S的插入权限授组User1，并允许将此权限授给其他用户。
GRANT INSERT ON TABLE S TO User1 WITH GRANT OPTION;
DBA把数据库SPJ中建立表的权限授给用户User1。
GRANT CREATETAB ON DATABASE SPJ TO User1;
收回授权语句格式：
REVOKE <权限>[,<权限>]...
[ON <对象类型><对象名>]
FROM <用户>[,<用户>]...;
例如， REVOKE ALL PRIVILEGES ON TABLE S , P, J FROM User1,User2;
REVOKE INSERT ON TABLE S FROM User1 WITH GRANT OPTION;
REVOKE SELECT ON TABLES S FROM PUBLIC;
REVOKE UPDATE(Sno) ON TABLE S FROM User1; －－将权限的控制定位在某一个属性上
6、触发器，触发器是一种特殊类型的存储过程，它是通过事件触发而执行的。主要特点是，当被声明的事件发生时触发器被激活；触发器激活后不会立即执行，而是先测试触发条件；如果触发条件满足，则由DBMs执行与该触发器相连的动作。
创建触发器，不同数据库使用的触发器语法不同。
例：假定银行数据库关系模式为： Account(Account-no, branch-name,balance)
Loan(Loan-no, branch-name, amount)
depositor(customer-name, Account-no)
假定银行在处理透支时，不是将账户余额设成负值，而是将账户余额设置为零，并且建立一笔贷款，其金额为透支金额。这笔贷款的贷款号应该等该透支帐户的账户号。采用SQL－99标准创建触发器如下：
CREATE TRIGGER overdraft_trigger after update on Account
Refferencing new row as nrow
For each row
When nrow.balance<0
Begain atomic
Insert into borrower
(SELECT customer-name, Account-no
FROM depositor
Where nrow.account-no=depositor.account-no);
Insert into values
(nrow.account-no, nrow.branch-name, nrow.balance);
update account set balance=0
Where account.account-no=nrow.account-no
End
When nrow.balance<0是触发条件；
Begin atomic ... End子句用来将多行SQL语句集成为一个复合语句。其中前两条Insert into语句表示在borrower和loan关系中建立新的贷款业务，update语句用来将账户余额清零。
Referencing old row as 子句建立一个变量，用来存储已经被更新或删除的行的旧值。Referencing new row as 可以被update和Insert语句使用，可以存放经过更新的新值。
Referencing old table as 或Referencing new table as 子句可以用来指向临时表，使之容纳所有被影响的行。临时表不能使用before触发器，但可以用after触发器。
触发器在事件之前被激发，可以避免非法更新。
例9.45：仓库管理数据库中有如下关系，
inventory(item, level)，表示仓库中某种商品的现有量。
minlevel(item, level)，表示仓库中存有某种商品的量小量。
reorder(item, amount)，表示某种商品小于最小量时要订购的数量。
orders(item, amount)，表示定购某种商品的量。
CREATE TRIGGER reorder_trigger after update of amount on inventory --我怀疑amount应该是level
Referencing old row as orow, new row as nrow
For each row
When nrow.level <= (SELECT level
FROM minlevel
Where minlevel.item = orow.item)
And orow.level > (SELECT level
FROM minlevel
Where minlevel.item = orow.item)
Begin
Insert into orders
(SELECT item, amount
FROM reorder
Where reorder.item = orow.item)
End
删除触发器： DROP TRIGGER {trigger}[,...,n]