中级——008 SQL语言

数据库语言概述

SQL语言是集数据定义和数据操纵为一体的典型数据库语言。

SQL的基本组成

1、DDL（数据定义语言）：数据库模式定义、数据库存储结构和存取方法定义，数据库模式的修改和删除功能，CREATE、DROP、ALTER
2、DML（数据操纵语言）：查询数据、插入数据、从数据库删除信息、修改数据库信息，INSERT、UPDATE、DELETE
3、（数据查询）：SELECT
4、(数据控制)：GRANT、REVOKE
5、嵌入式SQL和动态SQL
6、完整性，DDL命令必须满足完整性约束条件
7、权限管理，DDL中说明对关系和视图的访问权限

SQL的特点

综合统一、高度非过程化、面向集合的操作方式、两种使用方式、语言简洁，易学易用

SQL的支持的三级模式结构

视图的作用

1、可以集中数据、简化和定制不同用户对数据库的不同数据要求。
2、可以屏蔽数据的复杂性，用户不必了解数据库的结构，就可以方便地使用和管理数据，简化数据权限管理和重新组织数据以便输出到其他应用程序中。
3、帮助用户限定感兴趣的某些特定数据和所负责的特定任务，不需要关注其他不需要的数据。
4、简化用户的操作。
5、可以让用户以不同方式看到不同或相同的数据集。
6、在某些情况下，由于表中数据量太大，因此在表的设计时场将表进行水平或垂直分割，但表的结构的变化对应用程序产生不良的影响。
7、视图提供了一个简单而有效的安全机制。

视图创建的注意事项

1、子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语
2、WITH CHECK OPTION表示对UPDATE\INSERT\DELETE操作时保证更新、插入或删除的行满足视图定义的谓词条件（即子查询中的条件表达式）。
3、组成视图的属性列名或者全部省略或者全部指定。如果省略属性列名，则隐含该视图由SELECT子查询目标列的主属性组成。

视图更新的注意事项

1、从多个基本表通过连结操作导出的视图不允许更新。
2、对使用了分组、集函数操作的视图不允许进行更新操作。
3、如果视图是从单个基本表通过投影、选取操作导出的则运行进行更新操作，且语法同基本表。

HAVING子句的注意事项

假如元组在分组前按照某种方式加上限制，使得不需要的分组为空，可以在GROUP BY子句后面加一个HAVING子句。
注意：当元组含有空值时，应该记住以下两点
	1、空值在任何聚集操作中被忽视。它对求和、求平均值和计数都没有影响。它也不能是某列的最大值或最小值。例如，COUNT（*）是某个关系中所有元组数目之和，但COUNT(A)却是A属性非空的元组个数之和。
	2、NULL值又可以在分组属性中看作是应该一般的值。例如，SELECT A,AVG(B) FROM R 中，当A的属性值为空时，就会统计A=NULL的所有元组中B的均值。

字符串操作

通配符：%任意字符串，_任意一个字符
谓词LIKE： [NOT] LIKE '<匹配字符串>' [ESCAPE'<换码字符>']

索引的作用

1、通过创建唯一索引，可以保证数据记录的唯一性。
2、可以大大加快数据检索速度。
3、可以加速表与表之间的连接，这一点在实现数据的参照完整性上面有意义。
4、在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。
5、使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。

完整性约束

数据库的完整性是指数据库正确性何相容性，是防止合法用户使用数据库时向数据库加入不符合语义的数据。保证数据库中数据是正确的，避免非法的更新。

完整性约束条件的分类

完整性约束条件作用的对象有关系、元组、列三种，共分为六类。

完整性控制应具备的功能

定义功能、检测功能、处理功能（一旦发现违背了完整性约束条件，采取相关的动作来保证数据的完整性）
检查是否违背完整性约束的时机有两种：若在一条语句执行完后立即检查称为立即执行约束；若检查需要延迟到整个事物执行完后再执行称为延迟执行约束。

实体完整性（使用“PARMARY KEY"子句）

在关系中只能有一个主键。声明主键有2种方法
	1、将PRIMARY KEY保留字加在属性类型之后。
	2、在属性列表中引入一个新元素，该元素包含保留字PRIMARY KEY和用圆括号括起来的形成该键的属性或属性组列表。

外键约束（Foreign Key）

FOREIGN KEY定义哪些列为外码；REFERENCES指明外码对应于哪个表的主码；ON DELETE CASCADE 指明删除被参照关系的元组时，同时删除参照关系中的元组；

属性值上的约束

1、NOT NULL：不允许取空值
2、UNIQUE：唯一标识数据库表中的每条记录
3、CHECK：CHECK子句可以保证属性值满足指定的条件。如设置check(assets>=0)

全局约束

全局约束是指一些比较复杂的完整性约束，这些约束涉及多个属性间的联系或多个不同关系间的联系。有两种：基于元组的检查子句和断言。
基于元组的检查子句
	这种约束是对单个关系的元组值加以约束。方法是在关系定义中的任何地方加上关键字CHECK和约束条件。
	CHECK(SAGE>=16 AND SAGE<=20)
基于断言的语法格式
	CREATE ASSERTION <断言名> CHECK(<条件>)
	CREATE ASSERTION ASSE_SCI 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')));

授权（GRANT）与销权（REVOKE）

数据控制是控制用户对数据的存储权力，是由DBA来决定的。但是，某个用户对某类数据具有何种权力，是个政策问题而不是技术问题。DBMS的功能就是保证这些决定的执行。因此，DBMS数据控制应具有如下功能：
	1、通过GRANT和REVOKE将授权通知给系统，并存入数据字典。
	2、当用户提出请求时，根据授权情况检查是否执行操作请求。
SQL标准包括delete、insert、select和update权限。select权限对应于read权限，SQL还包括了references权限，用来限制用户在创建关系时定义外码的能力。
授权语句如下：GRANT <权限> [ON <对象类型><对象名> TO <用户>][WITH GRANT OPTION]；
销权语句如下：REVOKE <权限> [ON <对象类型><对象名> FROM <用户>；
注意：若指定了WITH GRANT OPTION子句，那么获得了权限的用户还可以将权限赋予给其他用户；
      接受权限的用户可以是单个或多个具体的用户，PUBLIC参数可将权限赋给全体用户。

数据库定义

创建表
	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)),
	               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)),
	                Status CHAR(8),
	                QTY NUMERIC(9),
	                PRIMARY KEY(Sno,Pno),
	                FOREIGN KEY(Sno) REFERENCES S(Sno),
	                FOREIGN KEY(Pno) REFERENCES P(Pno));
修改表
	ALTER TABLE S ADD Zap CHAR(6);
	ALTER TABLE S MODIFY Status INT;
删除表
	DROP TABLE Student;
建立索引
	CREATE UNIQUE INDEX S-SNO ON S(Sno);
	CREATE UNIQUE INDEX P-PNO ON P(Pno);
	CREATE UNIQUE INDEX J-JNO ON J(Jno);
	CREATE UNIQUE INDEX SPJ-NO ON SPJ(Sno ASC,Pno DESC,JNO ASC)
删除索引
	DROP INDEX StudentIndex;
创建视图
	CREATE VIEW CS-STUDENT
	      AS SELECT Sno，Sname，Sage,Sex
             FROM Student
             WHERE SD='CS'
             WITH CHECK OPTION;
视图查询
	SELECT Sno,Sage FROM CS-Student WHERE SD='CS' AND Sage<20;
删除视图
	DROP VIEW CS-STUDENT;
表查询
	SELECT Sno,Sname FROM S WHERE SD='CS';
连接查询
	SELECT Sno,Sname FROM S，SC WHERE S.Sno =SC.Cno AND SC.Cno='C1';
子查询
	SELECT Sno,Sname 
	  FROM S 
	  WHERE Sno IN (SELECT Sno 
	                FROM SC WHERE Cno IN (SELECT Cno 
	                                     FROM C
	                                     WHERE Cname='MES'));
聚集函数
	SELECT MAX(Grade)，MIN（Grade），MAX(Grade)-MIN(Grade)
	  FROM SC
	  WHERE Cno='C1';
分组查询
	SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno;
HAVING子句
	SELECT Sno,AVG(QTY) 
	  FROM SPJ 
	  GROUP BY Sno
	  HAVING COUNT(DISTINCT(Sno))>2
	  ORDER BY Jno DESC;
字符串操作
	SELECT Sname FROM S WHERE Addr like '%科技路%'; --包含科技路
	SELECT Sname FROM S WHERE Addr like '_科技路'; --等于_科技路
	SELECT Sname FROM S WHERE Addr like 'ab\%cd%' escape '\'; --匹配所有以ab%cd开头的字符串
	SELECT Sname FROM S WHERE Addr like 'ab\\cd%' escape '\'; --匹配所有以ab\cd开头的字符串
UINON & UNION ALL操作
	(SELECT Customer-no FROM depositor)
	   UNION       --会去重
	(SELECT Customer-no FROM borrower)
	(SELECT Customer-no FROM depositor)
	   UNION ALL   --不去重
	(SELECT Customer-no FROM borrower)
INSERSECT操作  
	(SELECT Name,Address 
	    FROM Students
	    WHERE SEX='女' AND Type='研究生')
	INTERSECT --取交集，查询既是女研究生又是教师且工资大于等于2600元的名字何地址
	(SELECT Name,Address 
	    FROM Teachers 
	    WHERE Salary >=2600)
EXCEPT操作
	(SELECT Name,Address FROM Students)
	EXCEPT
	(SELECT Name,Address FROM Teachers)
WITH子句查询
	WITH MAX_SALARY(VALUE) AS 
	    (SELECT MAX(SALARY) A
	     FROM TEACHERS)
	 SELECT A FROM MAX_SALARY;
GRANT授权
	GRANT ALL PRIVILEGES ON TABLE S,P,J TO USER1,USER2;--将所有操作权限赋给用户USER1及USER2
	GRANT INSERT ON TABLE S TO USER1 WITH GRANT OPTION;--将S的插入权限赋给USER1，并运行将此权限赋给其他用户
	GRANR CREATETAB ON DATABASE SPJ TO USER1;--将数据库SPJ中建表的权限赋给USER1
REVOKE销权
REVOKE ALL PRIVILEGES ON TABLE S,P,J FROM USER1,USER2;--取消所有操作权限赋给用户USER1及USER2
REVOKE SELECT ON TABLE S FROM PUBLIC;--取消所有用户对S的查询权限
REVOKE UPDATE(SNO) ON TABLE S FROM USER1;--将user1对s表的sno的修改权限收回

触发器

触发器使每个站点在有数据修改时自动强制执行其业务规则。
特点
	1、当数据库程序员声明的事件发生时，触发器被激活。事件可以是对某个特定关系的插入insert、删除delete或更新update
	2、当触发器被事件激活时，不是立即执行，而是首先由触发器测试触发条件，若条件不成立，响应该事件的触发器什么事情也不做。
	3、如果触发器声明的条件满足，则与该触发器相连的动作由DBMS执行。动作可以阻止事件发生，可以撤销事件。
注意：触发器为数据库对象，当创建一个触发器时，必须指定
	1、名称
	2、在其上定义触发器的表
	3、触发器何时被激发
	4、指明触发器执行时应做的动作
触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。
尽管不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。
行级触发器
	对被事件影响的每一行（FOR EACH ROW）每一元组执行触发过程，称为行级触发器。
语句级触发器
	对整个事件只执行一次的触发过程（FOR EACH STATEMENT）,称为语句级触发器。该方式是触发器的默认方式。

创建触发器

CREATE TRIGGER <触发器>[{BEFORE|AFTER}]
{[DELETE|INSERT|UPDATEOF[列名清单]]}
ON 表名
[REFERENCING <临时视图名>]
[WHEN<触发条件>]
BEGIN
<触发动作>
END[触发器名]
	BEFORE:指DBMS在执行触发语句之前激发触发器。
	AFTER：指DBMS在执行触发语句之后激发触发器。
	DELETE：每当一个DELETE语句从表中删除一行时激发触发器。
	INSERT：每当一个INSERT语句向表中插入一行时激发触发器
	UPDATE：每当UPDATE语句修改，由OF子句指定的列值时，激发触发器。如果忽略OF子句，每当UPDATE修改表的任何列值，DBMS都将激发触发器。
	REFERNECING<临时视图名>：指定临时视图的别名。在触发器运行过程中，系统会生成两个临时视图，分别存放被更新值（旧值）和更新后的值（新值）。对于行级触发器，默认临时视图名分别是OLD和NEW；对于语句级触发器，默认临时视图分别是OLD-TABLE和NEW-TABLE。一旦触发器运行结束，临时视图就不在。

更改触发器

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

删除触发器

DROP TRIGGER<触发器>[,....n]--表示可以指定多个触发器的占位符。

嵌入式SQL

SQL提供了将SQL语句嵌入某种高级语言中的使用方式，但是如何识别嵌入在高级语言中的SQL语句，通常采用预编译的方法。
该方法的关键问题是必须区分主语言中嵌入的SQK语句，以及主语言和SQL间的通信问题。
采用的方法是由DBMS的预处理程序对源程序进行扫描，识别出SQL语句，把他们转换为主语言调用语句，以使主语言编译程序能识别它，最后由主语言的编译程序将整个源程序编译成目标码。

区分主语言与SQL语句

主语言语句需要在所有的SQL语句前加前缀EXEC SQL，而SQL的结束标志随主语言的不同而不同。

主语言工作单元与数据库工作单元通信

SQL通信区
	SQL通信区向主语言传递SQL语句执行的状态信息，使主语言能够根据此信息控制程序流程。
主变量
	主变量也叫共享变量。主语言向SQL语句提供参数主要通过主变量，主变量由主语言的程序定义，并用SQL的DECLARE语句说明。

动态SQL

SQL的动态SQL组件允许程序在运行时构造、提交SQL查询。
与此相反，嵌入式SQL语句必须在编译时完全确定，由预处理程序编译和宿主语言编译程序编译。
也就是说，在实际使用时，源程序往往不能包括用户的所有操作，用户对数据库的操作有时往往在实际运行时才提出请求
为此需要采用SQL的动态技术。
动态SQL预备语句格式
	EXEC SQL PREPARE <动态SQL语句名> FROM <共享变量或字符串>;
动态SQL执行语句格式
	EXEC SQL EXECUTE <动态SQL语句名>;
使用动态SQL语句时，还可以改进技术：当预备语句组合而成的SQL语句只需要执行一次，那么预备语句可以在程序运行时由用户输入才组合起来，但并不执行。