--创建数据库

CREATE DATABASE example
      AUTOMATIC STORAGE YES  --自动存储
      ON 'D:\' DBPATH ON'D:\' --指定数据库控制文件的存储路径，和数据库表数据的存储路径
      ALIAS example  --数据库别名
      USING CODESET UTF-8  TERRITORY CN --指定编码集，和地区
      COLLATE USING SYSTEM --指定数据库处理字符串的排序顺序
      PAGESIZE 4096 --默认表空间的大小，单位为字节
      WITH '样本数据库' --注释

--编目数据库

CATALOG DATABASE example
      AS example --数据库别名
      ON D --指定被编目的数据库所在路径
      AUTHENTICATION SERVER --身份认证方式
      WITH '注释'

--除去数据库

UNCATALOG DATABASE example

--删除数据库

DROP DATABASE example

--创建SMS(System Managed Space)表空间

CREATE TABLESPACE tablespace_name
      MANAGED BY SYSTEM
      USING path

--创建DMS(Database Managed Space)表空间

CREATE TABLESPACE tablespace_name
      MANAGED BY DATABASE
      USING FILE path size

--创建自动存储表空间

CREATE TABLESPACE tablespace_name
      MANAGED BY AUTOMATIC STORAGE--可省略，默认值

--创建一个常规表空间

CREATE TABLESPACE  tablespace_name
      PAGESIZE 4K
      MANAGED BY AUTOMATIC STORAGE

--加注释

COMMENT ONTABLESPACE tablespace_name IS '注释'

--删除表空间

DROP TABLESPACE tablespace_name

--数据类型

      SMALLINT                     --2字节  --小整数
      INT                          --4字节 --整数
      DEC(总位数，小数点位数)      --十进制
      REAL/FLOAT                   --4字节 --单精度浮点型
      DOUBLE                       --8字节 --双精度浮点型
      CHAR（字符数）               --固定长度字符型，最长不超过254个字符
      VARCHAR(最大字符数)          --可变长度字符型
      LONG VARCHAR(最大字符数)     --可变长度长字符型
      CLOB                         --字符大对象，最大2G字节
      BLOB                         --二进制大对象，常用于存储文档、图片、视音频等非传统数据
      GRAPHIC(字节长度)            --定长图形字符串，最大长度127
      VARGRAPHIC(字节长度)         --变长图形字符串，最大长度16336字节
      LONG VARGRAPHIC(字节长度)    --变长图形长字符串，最大长度16336字节
      DATE                         --如：2013-11-29
      TIME                         --如：15:40:46
      TIMESTAMP                    --如：yyyy-mm-dd-hh.ss.nnnnnn
      DECFLOAT                     --v9.5以后引入的新类型，货币数据
      XML                          --xml文档

--创建表

      CREATE TABLE schema.table_name
      (
             first_column  CHAR(4) NOT FULL, --非空
             second_column SMALLINT WITH DEFAULT0, --默认值为0
             third_column  BLOB(1M) LOGGED NOTCOMPACT, --记录，不压缩
             forth_column  VARCHAR(120) NOT FULLUNIQUE,--非空、唯一约束
             CONSTRAINT primary_key PRIMARYKEY(first_column), --定义主键
             CONSTRAINT check_nameCHECK(second_column='0' OR second_column='1' ) --定义检查约束
                    ENFORCED --强制此约束
                    ENABLE QUERY OPTIMIZATION --查询优化期间考虑此约束
             )IN tablespace;

--加注释

       COMMENT ON TABLE schema.table_nameIS '注释'
             COMMENT ON CONSTRAINT schema.table_name.constraint_name IS '注释'

--修改表

ALTERschema.table_name
      ADD COLUMN column_name CHAR(6); --增加一列
      DROP COLUMN column_name --删除列    

--删除表

DROPschema.table_name

--插入记录

INSERT INTO schema.table_name(column1,column2,column3)
      VALUES(value,value,value)

--创建一个与已有表相同的表

CREATE TABLE tableA LIKE tableB

--批量插入数据

INSERT INTO student
      SELECT * FROM 学生表
             WHERE 总学分>50 --将“学生表”中总学分大于50的所有记录都插入到“student”表中

--修改记录

UPDATE 学生表
      SET 总学分=52
             WHERE 姓名='name'

--删除记录

DELETE FROM 学生表
      WHERE 学号=‘1001’

--查询所有列

SELECT *
      FROM 学生表

--查询指定列

SELECT 学号,姓名
      FROM 学生表

--使用case语句替换查询结果中的数据

SELECT 学号，姓名，CASE
      WHEN 学分 IS NULL THEN '尚未获得学分''
      WHEN 学分<53 THEN '未修够学分'
      ELSE '修够学分'
      END
      AS '是否修够学分' --列名
             FROM 学生表
                    WHERE 专业='计算机'

--使用算数运算符计算列值

SELECT 学号，成绩*2 AS 两倍成绩
      FROM 学生表

--聚集函数

除count函数外，均忽略null值

     SUM--求总和
     AVG--求平均值
     MAX--求最大值
     MIN--求最小值
     COUNT--求记录总数

SELECT COUNT(DISTINCT 学号) --求选修了课程的总人数，DISTINCT去重复记录
      FROM 成绩表

--指定表的快捷名称

SELECT x.学号,k.课程号,c.成绩
      FROM 学生表 x,课程表 k,成绩表 c
             WHERE x.学号=k.学号 AND k.课程号=c.课程号 AND x.学号=c.学号

--内连接(JOIN/INNER JOIN)

SELECT x.学号，x.姓名，c.课程名，c.成绩       --查询选修了“计算机基础”且成绩在80以上的学生
      FROM 学生表 x JOIN 成绩表 ON x.学号=c.学号   JOIN 课程表 c ON k.课程号=c.课程号
             WHERE 课程名="计算机基础"AND 成绩>=80

--自连接

SELECT a.学号,a.课程号,b.课程号,a.成绩     --查询不同课程成绩相同的学生的学号、课程号、成绩</span>

      FROM 成绩表 a JOIN 成绩表 b
             ON a.成绩=b.成绩 AND a.学号=b.学号 AND a.课程号!=b.课程号

--外连接

--内连接不会保留两表中不匹配的行为（即数据列），而外连接会保留

--全外连接（ALL OUTER JOIN）两表的不匹配列都保留

--左外连接（LEFT OUTER JOIN）只保留左侧表的不匹配列

--右外连接（RIGHT OUTER JOIN）只保留右侧表的不匹配列

SLECT x.*，课程号   --查询所有学生及他们选修的课程号，学生未选课也要包含在内,其字段为null
      FROM 学生表 x LEFT OUTER JOIN成绩表 c
             ON x.学号=c.学号

--限制结果集返回行数

SELECT  *
      FROM 学生表
             FETCH FIRST 6 ROW ONLY

--WHERE子句

DB2支持6种WHERE子句谓语：比较型谓语（>、<、>=、<=、<>、=、NOT）、BETWEEN、 LIKE、 IN 、EXISTS 、IS NULL/ISNOT NULL

只可以和谓词使用的比较操作符：

ALL 只有当子查询返回的所有行的指定关系为真时，这个谓语才为真

SOME/ANY 只有当子查询返回的结果中至少有一行指定关系为真时，谓语的结果才为真

EXCEPT 产生一个结果表，该表是有第一条SELECT语句产生的唯一的数据行组成，SELECT语句不是有第二条SELECT语句产生的

INTERSEC     产生一个结果表，该表由在两条SELECT语句产生的结果中的共同的唯一的数据行组成

--查询所有出生时间不在1990年的学生

SELECT *
      FROM 学生表
             WHERE 出生时间 NOTBETWEEN '1990-01-01' AND '1990-12-31'

--LIKE谓语的模式匹配

_ 代表一个任意单个字符的通配符

%    代表任意多个字符的通配符

--查询所有姓王的学生

SELECT *
      FROM 学生表
             WHERE 姓名 LIKE '王%

--IN谓词的使用

SELECT * FROM 学生表
      WHERE 姓名 IN('王林','程明'.'王燕')

SELECT * FROM 成绩表
      WHERE 姓名 IN
             SELECT 学号 FROM 学生表 WHERE 专业='计算机'

--EXISTS谓语的使用

EXISTS用来确定某行数据是否存在于表中（IN适用于外表大而内表小的查询，EXISTS适用于外表小而内表大的查询）

SELECT 姓名 FROM 学生表 c          --查询成绩表中成绩大于80的所有学生姓名
      WHERE EXISTS
             SELECT * FROM 成绩表 c  WHERE >80 AND x.学号=c.学号

--GROUP BY子句

其列名必须是select子句未使用聚集函数处理的所有列的子集

SELECT 专业，COUNT(*)学生数  --查询各专业的学生人数
      FROM 学生表
             GROUP BY 专业

SELECT 课程号，AVG（成绩），COUNT（学号） 选修人数   --查询被选修的各门课程的平均成绩和选修该课程的人数
      FROM 成绩表
             GROUP BY 课程号 ASC/DESC
                    WITH ROOLUP --除各数据行以外，还包括汇总行（WITH CUBE  --包含各列所有可能组合的汇总行）

--GROUPING聚集函数

产生一个附加列，该列为NULL时，其值为1；反之，则为0

SELECT 专业，性别
      GROUPING（专业） zy,GROUPING(性别) xb
             FROM 学生表

--HAVING子句
SELECT 学号，AVG(成绩) 平均成绩     --查询平均成绩在85分以上的学生学号和姓名
      FROM   成绩表  --筛选由FROM指定的数据对象
             GROUP BY 学号 --对WHERE的筛选结果进行分组
                    HAVING AVG(成绩)>=85   --对GROUP BY的结果进行过滤

SELECT 学号     --查询选修课程超过两门且成绩都在80分以上的学生学号
      FROM 成绩表
             WHERE 成绩>=80
                    GROUP BY 学号
                           HAVING COUNT(*)>2

--ORDER BY子句

SELECT 姓名，学号
      FROM 学生表
             WHERE 姓名 LIKE '王%'
                    ORDER BY 姓名 ASC，学号 DESC

--UNION子句

用来把两个单独的结果集合并成一个结果集，默认情况下删除重复行，UNION ALL则不删除重复行

SELECT       学号，姓名，出生时间
      FROM 学生表
             WHERE 出生时间 BETWEEN'1990-01-01' AND '1990-12-31'
UNION
SELECT       学号，姓名，出生时间
      FROM 学生表
             WHERE 姓名 LIKE '王%'

--创建视图

不能使用ORDER BY语句，若视图的关联基本表增加了新的字段，必须重建视图

CREATE VIEW schema.view_name
      AS
      SELECT k.学号 k.课程号 k.成绩
             FROM 成绩表 k
             WHERE k.课程号='101'
                    WITH CHECK OPTION(确保始终检查此条件，即始终确保课程号为101，这将限制课程列的输入，如果在INSERT语句中使用此视图，课程列不是101时将被拒绝)

视图的增删修改与基本表的操作基本一致

--约束

包括非空约束、检车约束、主键约束、唯一约束、外键约束，可以使用ALTER TABLE语句修改表中的约束

ALTER TABLE 学生表
      DROP PRIMARY KEY  --删除主键约束
      ADD CONSTRAINT constraint_name UNIQUE  --添加唯一约束

ALTER TABLE 学生表
      ADD CONSTRAINT constraint_name CHECK(开课学期>0 AND 开课学期<8)  --添加检查约束，限制开课学期为0-8
      ENFORCED
      ENABLE QUERY OPTIMIZATION

ALTER TABLE 成绩表
      ADD CONSTRAINT constraint_name FOREIN KEY(学号) REFERENCES schema.学生表（学号）
             ON DELETE CASCADE/NOACTION/RESTRICT/SET FULL  --NOACTION/RESTRICT，删除发生错误时，不会删除任何行
             ON UPDATE NO ACTION/RESTRICT
             ENFORCED
             ENABLE QUERY OPTIMIZATION

--删除约束

ALTER TABLE 学生表
      DROP UNIQUE constraint_name

--别名是引用表、昵称或视图的间接方法，在检查约束的检查条件中不能使用别名，别名不能引用已声明的临时表，别名不能与现有的表、视图重名
CREATE ALIAS alias_name
      FOR table_name

--数据定义语言（DDL）

CREATE、ALTER、DROP

--数据操纵语言（DML）

SELECT、INSERT、UPDATE、DELETE、MERGE

--数据控制语言（DCL）

GRANT、REVOKE、DENY

--MERGE语句

MERGE  INTO table1  t1  USING
(SELECT  * FROM  table2  WHERE ... ) t2
ON  t1.id = t2.id
WHEN  MATCHED THEN  UPDATE  SET
(
      t1.id,
      t1.name,
      t1.age
) = (
      t2.id,
      t2.name,
      t2.age
)
WHEN  NOT MATCHED  TEHN  INSERT
(
      t1.id,
      t1.name,
      t1.age
) values (
      t2.id,
      t2.name,
      t2.age
)

--用户自定义数据类型

CREATE DISTINCTTYPE schema.student_id_type --创建单值数据类型
      AS CHAR(6)
      WITH COMPARISONS  --指定要创建系统生成的比较运算符，且该运算符用于比较一个单值类型的两个实例，LOB或VARCHAR不能用此关键字
      COMMENT ON DISTINCT TYPE schema.student_id_type  IS '学号类型'

CREATE TYPE schema.学生结构类型 (UNDER supertype_name) AS  --创建结构数据类型
(
      学号 CHAR(6)
      姓名 CHAR(16)
      性别 CHAR(4)
)MODE DB2SQL

CREATE TYPE type_name --创建数组型数据类型
      AS INTEGER ARRAY[100]
CREATE TYPE type_name
      AS DECIMAL(12，2) ARRAY[]

--专用寄存器

CURRENT DATE
CURRENT TIME
CURRENT TIMESTAMP
CURRENT USER
CURRENT PATH
CURRENT SCHEMA

--声明游标

DECLARE cursor_name FOR
      SELECT 学号 FROM 学生表
             WITH HOLD/WITHOUT HOLD(默认)--使用WITH HOLD时，COMMIT操作之后，游标保持OPEN；反之，则关闭。ROLLBACK操作后，所有游标关闭
             WITHOUT RETURN/WITH RETURN/WITHRETURN TO CALLER/WITH RETURN TO CLIENT
             --WITHOUT RETURN/WITH RETURN --指明游标是否携带结果集
             --WITH RETURN TO CALLER --指定将来自游标的结果集返回给调用者（默认项）
             --WITH RETURN TO CLIENT --指定将来自游标的结果集返回给客户及程序，绕过任何中间的嵌套过程

--IF...ELSE...条件语句

CREATE PROCEDURE schema.pro_name (OUT text CHAR(40))
BEGIN
      IF((SELECT AVG(成绩) FROM 成绩表 WHERE 课程号='1001')<75)
      THEN SET text='平均成绩低于75分';
      ELSE SET text='平均成绩大于等于75分';
      END IF;
END#

--CASE语句
SELECT 学号，
      (CASE
             WHEN YEAR(CURRENT DATE)-YEAR(出生时间)<18
                    THEN '年龄偏小'
             WHEN YEAR(CURRENT DATE)-YEAR(出生时间) BETWEEN18 AND 22
                    THEN '年龄适中'
             ELSE '年龄偏大'
      END) 年龄评价
      FROM 学生表

--LOOP循环

CREATE PROCEDURE pro_name (OUT out_counter INT)  --从学生表中获取学号、姓名、年龄插入新表STU_INFO中

L1:BEGIN ATOMIC
      DECLARE var_at_end,var_counter INTEGER DEFAULT 0;  --var_at_end用于判断游标是否在结果集的末尾，var_counter用于记录行数
      DECLARE var_sid CHAR(6);  --学号
      DECLARE var_sname CHAR(16);  --姓名
      DECLARE var_year_old INT;  --年龄

      DECLARE c1 CURSOR FOR
             SELECT 学号,姓名,(YEAR(CURRENTDATE)-YEAR(出生时间)) 年龄
                    FROM 学生表；
             DECLARE CONTINUE HANDLER FOR NOTFOUND SET var_at_end=1;--如果发生找不到的异常则将var_at_end变成1
             OPEN c1;

      FETCH_LOOP:LOOP
             FETCH c1 INTOvar_sid,var_sname,var_year_old;

             IF var_at_end<>0
                    THEN LEAVE FETCH_LOOP;  --跳出循环
             END IF;

             SET var_counter=var_counter+1;
             INSERT INTO stu_info
                    VALUES(var_sid,var_sname,var_year_old);
      END LOOP FETCH_LOOP;

      SET out_counter=var_counter;

END L1#

--WHILE循环

CREATE PROCEDURE pro_name (OUT out_counter INT)  --从学生表中获取学号、姓名、年龄插入新表STU_INFO中

P1:BEGIN ATOMIC
      DECLARE var_at_end,var_counter INTEGER DEFAULT 0;  --var_at_end用于判断游标是否在结果集的末尾，var_counter用于记录行数
      DECLARE var_sid CHAR(6);  --学号
      DECLARE var_sname CHAR(16);  --姓名
      DECLARE var_year_old INT;  --年龄

      DECLARE c1 CURSOR FOR
             SELECT 学号,姓名,(YEAR(CURRENTDATE)-YEAR(出生时间)) 年龄
                    FROM 学生表；
             DECLARE CONTINUE HANDLER FOR NOTFOUND SET var_at_end=1;--如果发生找不到的异常则将var_at_end变成1
             OPEN c1;

             WHILE(var_at_end=0)
             DO
                    INSERT INTO stu_info
                    VALUES(var_sid,var_sname,var_year_old);
                    SETvar_counter=var_counter+1;
                    FETCH c1 INTOvar_sid,var_sname,var_year_old;
             END WHILE;

      SET out_counter=var_counter;

END P1#

--FOR循环

CREATE PROCEDURE pro_name (OUT out_counter INT)  --从学生表中获取学号、姓名、年龄插入新表STU_INFO中

P1:BEGIN ATOMIC
      DECLARE var_counter INTEGER DEFAULT 0;

      FOR for_loop AS
             SELECT  学号,姓名,(YEAR(CURRENTDATE)-YEAR(出生时间)) 年龄
                    FROM 学生表
      DO
             INSERT INTO stu_info
                    VALUES(for_loop.学号,for_loop.姓名,for_loop.年龄);
             SET var_counter=var_counter+1;
      END FOR;

      SET out_counter=var_counter;

END P1#

--常用的系统内置函数

COALESCE函数

语法：COALESCE(ARG1,ARG2...)

COALESCE返回参数集中第一个非null参数。用法类似于VALUE函数。

NULLIF ( expression-1, expression-2 )

如果第一个表达式的值与第二个表达式的值相等，NULLIF 返回 NULL。

如果第一个表达式的值不等于第二个表达式的值，或者第二个表达式为 NULL，则 NULLIF 返回第一个表达式。

ABS()  --取绝对值

RAND() --返回一个0-1间的随机数，类型为real

ASCII(string)  --返回字符串最左端字符的ASCII码，非纯数字的字符串需用单引号括起来

CHAR(integer) --将ASCII码转换为字符，integer介于0-225之间

UPPER()/LOWER()  --大小写转换

LTRIM()  --去掉前导空格

RTRIM()  --去掉尾部空格

LEFT(character_expresion,integer_expresion)   --返回character_expresion左边integer_expresion个字符    RIGHT()函数类似

CHARINDEX(substring,expresion)  --返回字符串中指定的字串的开始位置；若没有，则返回0

REVERSE()  --颠倒字符串序列

REPLACE(string,string1,string2)  --替换指定字符

SUBSTR(string_expresion,stat_index,end_index)  --按照指定的其实下标截取字符串

CAST(expresionAS data_type)

SELECT 学号 CAST(成绩 ASDEC(5,2)

FROM 成绩表

DAY(date_expresion)  --返回日期值

MONTH(date_expresion)  --返回月份值

YEAR(date_expresion)  --返回年份值

DATEADD(yy/mm/dd,number,date_expresion)  --返回date_expresion加上number之后的新日期

DATEDIFF(yy/mm/dd,number,date_expresion)  --返回date_expresion在yy/mm/dd方面的不同之处，结果为带正负号的整数值

GETDATE()  --以DATETIME的默认格式返回系统当前的日期和时间

--创建有源函数

CREATE FUNCTION schema.AVG(schema.year_old_type)
      RETURNS schema.year_old_type  --自定义单值类型
      SOURCE SYSIBM.AVG(INTEGER)  --创建基于内置函数SYSIBM.AVG(INTEGER)的有源函数

--创建一个SQL标量函数

CREATE FUNCTION schema.this_month()
      RETURN VARCHAR(12)
             SPECIFIC schema.this_month  --指定分配给这个函数的特定名称，这个名称可以用来引用或删除函数，但是不能用来调用函数
             LANGUAGE SQL
             NOT DETERMINISTIC --表示在相同的参数值调用函数时，函数是否总返回相同的结果
             NO EXTERNAL ACTION --表示函数执行的操作是否会改变不有DB2管理的对象的状态，外部操作包括发送电子邮件或在外部文件中写记录等
             CONTAIN SQL/READS SQL DATA/MODIFIESSQL DATA --可执行的sql语句不读也不修改数据/可执行的sql语句读数据而不修改数据/可执行的sql语句既可读数据，也可修改数据
             RETURN MONTHBANE(CURRENT_DATE)  --返回当前月份

--创建一个SQL表函数

CREATE FUNCTION schema.funName(in_sid VARCHAR(6))
      RETURN TABLE
      (
             学号 CHAR(6),
             姓名 CHAR(8),
             课程名 CHAR(40),
             成绩 SMALLINT,
             学分 SMALLINT,
      )
      SPECIFIC schema.funName
      LANGUAGE SQL
      NOT DETERMINISTIC
      NO EXTERNAL ACTION
      READS SQL DATA

      BEGIN ATOMIC  --如果发生任何未处理的复合语句中错误条件，执行到这个点的所有执行语句全部回滚
             RETURN
                    SELECT x.学号  x.姓名 k.课程名 c.成绩 k.学分
                           FROM 学生表 x
                                  INNER JOIN 成绩表 c ON (x.学号=c.学号 AND x.学号=in_sid)
                                  INNER JOIN 课程表 k ON (c.课程号 AND k.课程号)
                           ORDER BY x.学号,k.课程号
      END

--删除函数

DROP FUNCTION function_name

--查看已存在的存储过程

SELECT * FROM SYSCAT.PROCEDURES  --SYSCAT.PROCEDURES所有存储过程的系统目录视图
      WHERE PROCNAME=pro_name

--创建存储过程

CREATE PROCEDURE schema.pro_name
             (IN/OUT parameter_name data_type)
             SPECIFIC specific_name
             DYNAMIC RESULT SETS number  --指定存储过程返回结果集的最大数量
             CONTAIN SQL/READS SQL DATA/MODIFIESSQL DATA
             NOT DETERMINISTIC/DETERMINISTIC
             CALLED ON NULL INPUT  --表示可以调用存储过程，不管输入参数是否为空
             LANGUAGE SQL
             NO EXTERNAL ACTION/EXTERNAL ACTION

             P1:BEGIN ATOMIC  --存储过程主体开始的标识符BEGIN，P1为行标
                    DECLARE parameter data_typeDEFAULT value;
                    DECLARE c2 CURSOR WITHRETURN FOR
                           SELECT *
                                  FROM 学生表;
                    OPEN c2;
             END

--调用存储过程

CALL pro_name(data_value,data_value,?)  --问号代表OUT参数的占位符

--创建全局变量,在存储过程外面声明

CREATE VARIABLE var_name DATA_TYPE DEFAULT value

--诊断语句

用于获取刚刚被执行的存储过程的信息

CALL pro_name();
GET DIAGNOSTICS var_name=DB2_RERURN_STATUS

--外部存储过程使用内部存储过程的结果集数据

DECLARE rs_locator_var RESULT_SET_LOCATOR VARYING;--声明一个结果集定位器
ASSOCIATE RESULTSET LOCATOR (rs_locator_var) WITH PROCEDURE pro_called  --为内部被调用的存储过程分配一个结果集定位器
ALLOCATE cursor1 CURSOR FOR RESULT SET rs_locator_var  --分配一个游标，指向调用存储过程的结果集
                                    --此后，cursor1就携带了被调用存储过程的结果集，使用方法与自己声明的游标类似

--临时表的使用

DECALRE GLOBLALTEMPORARY TABLE SESSION.temporary_table_name --临时表的模式必须为SESSION，其使用方法和普通表类似
      (
      学号 CHAR(4),
      姓名 CHAR(16),
      ...
      )WITH REPLACE --      如果存在临时表，则替换
      NOT LOGGED; --不在日志里记录临时表

--存储过程的重载

--DB2允许同名单参数数目不同的存储过程，但不允许同名且参数数目相同的存储过程，即使数据类型不同也不行

--删除存储过程

DROP PROCEDURE pro_name  --如果存在同名的存储过程，必须使用DROP SPECIFIC PROCEDURE才能删除

--存储过程的异常处理

SQL PL允许为给定的SQLSTATE声明用。户命名的条件，以用于之后的错误处理。条件名称在整个复合语句中必须是唯一的，并且只能在声明它的复合语句中引用它

DECALARE condition_name CONDITION FOR
      SQLSTATE [VALUE] string_constant

DECLARE foreign_key_violation CONDITION FOR SQLSTATE ‘23503’;

如果发生一个错误，存储过程的行为是根据条件处理程序来决定的，可以为

一个普通的或有名称的条件和特定的SQLSTATE声明一个或多个条件处理程序，当一个SQL语句产生一个SQLEXCEPTION或者SQLWARNING（SQLCODE<>0）时，控制被转移到一个声明的处理程序中，以获取普通的异常或特定的SQLSTATE

DECLARE<CONDITION | EXIT |UNDO> HANDLER FOR
<specific_condition_value| general_condition_value> procedure_statement
WHERE specific_condition_value
[SQLSTATE[VALUE] string_constant | condition_name]

--强制发出异常 SIGNAL SQLSTATE

SIGNAL [SQLSTATE[VALUE] <sqlstate_string_constant | variable_name |condition_name>]
[SETMESSAGE_TEXT = diagnostic_expression]

--创建触发器

--前触发器，在更新、插入或删除操作前执行

--后触发器，在更新、插入或删除操作后执行

--INSTEAD OF触发器，描述如何对视图进行插入、更新和删除操作

CREATE TRIGGER schema_name.trigger_name [NO CASCADE]
      [BEFORE | AFTER | INSTEAD OF]
      [INSERT | DELETE | UPDATE [OF column_name]]
      ON <table_name | view_name>
REFERENCING

指定转换变量的相关名和转换表的表名。触发器执行前后数据库的状态不同，有两类方法可以标识数据库的不同状态。

第一类是把即将（或已经）受触发器影响的行集合中的一行定义成“转换变量”，并且给转换变量定义一个相关名。

OLD AS correlation_name：当前触发器执行前，受影响的行集合中的一行的相关名为correlation_name，每当引用当前触发器执行前的某一行的某一列的值时，就在列名前加上“correlation_name”

NEW AS correlation_name：当前触发器或者BEFORE触发器中的SET语句执行后，受影响的行集合中的一行的相关名为correlation_name，每当引用当前触发器或者BEFORE触发器执行后的某一行的某一列的值时，就在列名前加上“correlation_name”

第二类方法是把触发器执行前即将影响（或触发器执行后已经受影响）的行集合看成一个“转换表”，并且给转换表定义一个表名

OLD TABLE AS identifier：将前触发器执行前即将受影响的行的集合组成名为identifier的转换表

NEW TABLE AS identifier：当前触发器或者BEFORE触发器中的SET语句执行后，受影响的行的集合组成转换表，该表命名为identifier

[FOR EACH<ROW | STATEMENT>]

被触发的SQL语句的动作应用到受影响的表或视图的每一行

被触发的SQL语句集合中的每一个SQL语句的动作只执行一次

注意：

OLD TABLE 和NEW TABLE在每个触发器中只能指定一次，而且只能指定给AFTER或者INSTEAD OF触发器

DELETE触发器中不允许NEW相关名和NEW_TABLE名

INSERT触发器中不允许OLD相关名和OLD_TABLE名

BEFORE触发器中不允许TABLE名和NEW_TABLE名

FOR EACHSTATEMENT不可以指定给BEFORE和INSTEAD OF 触发器

WHEN条件语句不得用在INSTEADOF触发器

只有后触发器和INSTEAD OF 触发器可以包含下列一个或多个SQL语句：UPDATE、DELETE、INSERT、MERGE

CREATE TRIGGER schema_name.trigger_name
      AFTER INSERT ON table_name  –为表创建插入操作的后触发器
      REFERENCING NEW AS N –将触发器执行后受影响的行定义为N
      FOR EACH ROW
WHEN(60<=(SELECT 成绩 FROM 成绩表 WHERE 学号 = N.学号 AND 课程号 = N.课程号 ))
BEGIN ATOMIC
      DECLARE credit SMALLINT DEFAULT 0;
IF(0<(SELECTc.学分 FROM 课程表 c  WHERE c.课程号 = N.课程号))
THEN UPDATE 学生表 s SET 总学分 = 总学分 + creditWHERE  s.学号 = N.学号
END IF;
END#
--每次向成绩表增加一条记录且该记录的成绩大于60分时，就将学生表中相应学生的总学分更新

CREATE TRIGGER schema_name.trigger_name
      BEFORE INSERT ON 成绩表  --前触发器
      REFERENCING NEW AS N
      FOR EACH ROW
BEGIN ATOMIC
IF(N.学号 NOT IN(SELECT s.学号 FROM 学生表 s))
      THEN SIGNALSQLSTATE ‘70001’(‘错误：该记录的学号在学生表中不存在！’);
END IF;
IF(N.课程号 NOTIN(SELECT c.课程号 FROM 课程表 c))
      THEN SIGNAL SQLSTATE ‘70002’(‘错误：该记录的课程号在课程表中不存在！’);
END IF;
END# 

--INSTEAD OF 触发器实例

CREATE TRIGGER schema_name.trigger_name
      INSTEAD OF INSERT ON view_name
      REFERENCING NEW AS N
      FOR EACH ROW
INSERT INTO table_name(column1,column2)
      VALUES(value1,value2)

--查询触发器

SELECT * FROM syscat.triggers

--删除触发器

DROP TRIGGER schema_name.trigger_name

--身份验证

SERVER        在服务器上进行验证

SERVER_ENCRYPT            在服务器上验证，密码在客户机上加密，然后再发送到服务器

CLIENT         在客户机上进行验证

KERBEROS          有KERBEROS安全软件进行身份验证

DATA_ENCRYPT         服务器接受加密的用户ID和密码，并对数据进行加密

--管理权限

实例权限级别：SYSADM（最高权限）、SYSCTRL、SYSMAINT、SYSMON

数据库权限级别：DBAMD、SECADM、LOAD、CONNECT、BINDADD、CREATETAB、CREATE_NOT_FENCED、IMPLICITSCHEMA、QUIESCE_CONNECT、CREATE_EXTERNAL_ROUTINE

--权限授予

GRANT database_authorities_type ON DATABASE TO [USER | GROUP]

--权限撤销

REVOKE database_authorities_type ON DATABASE FROM [USER | GROUP]