【IBM DB2 学习笔记一】
【彭建军】
注意：在 IBM DB2 中，与 MS SQL Server 2000 中相同的语法或者概念，这里就不一一列出了。
一、【DB2 SQL 概述】
1、 【模式】
1.1、模式是已命名的对象（如表和视图）的集合。模式提供了数据库中对象的逻辑分类。
1.2、当在数据库中创建对象的时候，系统就隐性的创建了模式。当然，也可以使用 CREATE SCHEMA 显式的创建模式。
1.3、当命名对象的时候，需要注意对象的名称有两个部分，即，模式.对象名称,形如：pjj.TempTable1。如果不显示指定模式，则系统使用默认模式（默认用户的ID）。
2、 【数据类型】
定长字符串 CHAR(x) x值域（1～254） 一个字节序列
变长字符串 VARCHAR(X)
LONG VARCHAR(X)
LOB(大对象)
定长图形字符串 GRAPHCI(X) x值域（1～127） 两个字节序列
变长图形字符串 VARGRAPHCI(X)
LONG GRAPHCI(X)
DBCLOB(大对象)
二进制字符串 暂缺 一个字节序列
BLOB(大对象)
数字 (所有数字都有精度，精度是指除符号位以外的位数或者数字数)
SMALLINT 精度为5 2字节整数
INTEGER 精度为10 4字节整数
BIGINT 精度为19 8字节整数
REAL 实数的32位近似值
DOUBLE 实数的64位近似值
DECIMAL(P,S) P,精度，S,小数位数，十进制数，P必须<=32，S必须<=P，缺省:P=5,S=0
日期时间型 14位字符串，即非数字类型也非字符串类型
日期 DATE 年月日
时间 TIME 24小时制，分为 小时分钟秒
时间戳记 TIMESTAMP 1日期和时间的值，分为 年月日小时分钟秒微秒
空值 不同于任何非空值
3、 其他
DB2不区分大小写（单引号或者双引号内的内容除外）
二、【创建表和视图】
1、【创建表】
CREATE TABLE PERS
(
ID SMALLINT NOT NULL,
NAME VARCHAR(9),
DEPT SMALLINT WITH DEFAULT 10,
JOB CHAR(5),
YEARS SMALLINT,
SALARY DECIMAL(7,2),
COMM DECIMAL(7,2),
BIRTH_DATE DATE
)
2、【在表中插入值】(三种方式)
INSERT INTO PERS
VALUES (12,'Harris',20,'Sales',5,18000,1000,'1950-1-1')
INSERT INTO PERS (NAME,JOB,ID)
VALUES ('Swagerman','Pramr',500),
('Limoges','Prgmr',510),
('Li','Prgmr',520)
INSERT INTO PERS (ID,NAME,DEPT,JOB,YEARS,SALARY,COMM,BIRTH_DATE)
SELECT ID,NAME,DEPT,JOB,YEARS,SALARY,COMM,BIRTH_DATE
FROM STAFF
WHERE ID = 58
3、【更新数据】
UPDATE PERS
SET JOB = 'Prgmr',SALARY = SALARY + 300
WHERE ID = 410
UPDATE PERS
SET SALARY = SALARY * 1.15
WHERE JOB = 'Sales'
4、【删除数据】
DELETE FROM PERS
WHERE ID = 120
5、【删除表】
DROP TABLE PERS
6、【创建视图】
(可以选用 WITH CHECK OPTION 选项，该选项针对 WHERE 的条件进行限定)
CREATE VIEW STAFF_ONLY
AS SELECT ID,NAME,DEPT,JOB,YEARS
FROM STAFF
WHERE JOB <> 'Mgr' AND DEPT = 20 WITH CHECK OPTION
三、【使用 SQL 语句存取数据】
(? CREATE 显示一般帮助提示信息)
1、【连接数据库】
CONNECT TO MYDB2 USER USERID USING PASSWORD
2、【谓词】
x=y x<>y x<y x>y x>=y x<=y
IS NULL
IS NOT NULL
3、【其他的一些去SQL SERVER 类似的东西就省略】
4、【运算次序】
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
5、【函数】
5.1、【列函数】
(列函数对列中的一组值进行运算以得到单一的结果值！)
AVG
COUNT
MAX
MIN
5.2、【标量函数】
(标量函数对一个单一值进行运算以返回另一个单一的结果值！)
ABS 绝对值
HEX 十六进制
LENGTH 返回字节数（对于图形字符串则返回双字节字符串）
YEAR
5.3、【表函数】
(表函数仅可用于 FROM 子句，返回表的列！)
四、【表达式和子查询】
1、【标量全查询】
(返回一行，该行只包括一个值，用于从数据库中检索值)
SELECT LASTNAME,FIRSTNAME
FROM EMPLOYEE
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE)
SELECT AVG(SALARY) AS "Average_Employee",
(SELECT AVG(SALARY) AS "Average_Staff" FROM STAFF)
FROM EMPLOYEE
注意:
SQL SERVER 中,字符串用单引号,而DB2 中则使用双引号!
SELECT XX AS "OO" 也可以写为 SELECT XX "XX"
2、【转换数据类型】
（使用 CAST,CAST的另外一个用法是截断数据）
SELECT CAST(NAME AS VARCHAR(2)) AS "NAME" FROM EMPLOYEE
3、【条件表达式】
(CASE 注意，单引号和双引号)
SELECT DEPTNAME
CASE DEPTNUMB
WHEN 10 THEN 'Market'
WHEN 20 THEN 'Sales'
WHEN 30 THEN 'Development'
ELSE 'NULL'
END AS FUNCTION
FROM ORG
--避免产生被0除
SELECT NAME,WORKDEPT
FROM EMPLOYEE
WHERE (CASE WHEN BONUS = 0 THEN NULL ELSE SALARY/BONUS END) > 10
--替代简单的函数功能
CASE WHEN X<0 THEN -1 WHEN X=0 THEN 0 ELSE 1 END
4、【表表达式】（临时的）
4.1、【嵌套表表达式】
（嵌套于FROM字句中）
SELECT EDLEVEL, HIREYEAR, DECIMAL(AVG(TOTAL_PAY),7,2)
FROM (SELECT EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,
SALARY+BONUS+COMM AS TOTAL_PAY
FROM EMPLOYEE
WHERE EDLEVEL > 16) AS PAY_LEVEL
GROUP BY EDLEVEL, HIREYEAR
ORDER BY EDLEVEL, HIREYEAR
4.2【公共表达式】
（以WITH开头，对公共表达式的重复引用使用同一结果，而使用嵌套表达式则可能会出现不同的结果。）
WITH
PAYLEVEL AS
(SELECT EMPNO, EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,
SALARY+BONUS+COMM AS TOTAL_PAY
FROM EMPLOYEE
WHERE EDLEVEL > 16),
PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS
(SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)
FROM PAYLEVEL
GROUP BY EDLEVEL, HIREYEAR)
SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, DECIMAL(AVG_TOTAL_PAY,7,2)
FROM PAYLEVEL, PAYBYED
WHERE EDLEVEL = EDUC_LEVEL
AND HIREYEAR = YEAR_OF_HIRE
AND TOTAL_PAY < AVG_TOTAL_PAY
4.3 【相关名】
（注意，一旦使用了相关名，则不能再在上下文中使用原名，否则出错！）
SELECT NAME, DEPTNAME
FROM STAFF S, ORG O
WHERE O.MANAGER = S.ID
--另外，相关名还可以用来复制对象，例如说自身
SELECT E2.FIRSTNME, E2.LASTNAME, E2.JOB, E1.FIRSTNME AS MGR_FIRSTNAME,
E1.LASTNAME AS MGR_LASTNAME, E1.WORKDEPT
FROM EMPLOYEE E1, EMPLOYEE E2
WHERE E1.WORKDEPT = E2.WORKDEPT
AND E1.JOB = 'MANAGER'
AND E2.JOB <> 'MANAGER'
AND E2.JOB <> 'DESIGNER'
4.4【不/相关子查询】
4.4.1【不相关子查询】
SELECT EMPNO, LASTNAME
FROM EMPLOYEE
WHERE WORKDEPT = 'A00'
AND SALARY > (SELECT AVG(SALARY)
FROM EMPLOYEE
WHERE WORKDEPT = 'A00')
4.4.2【相关子查询】
SELECT E1.EMPNO, E1.LASTNAME, E1.WORKDEPT
FROM EMPLOYEE E1
WHERE SALARY > (SELECT AVG(SALARY)
FROM EMPLOYEE E2
WHERE E2.WORKDEPT = E1.WORKDEPT)
ORDER BY E1.WORKDEPT
五、【在查询使用运算符与谓词】
1、【集合运算符】
1.1【UNION】：
组合两个表，并消除重复行；如果 UNION ALL 则不消除重复行
SELECT ID, NAME FROM STAFF WHERE SALARY > 21000
UNION
SELECT ID, NAME FROM STAFF WHERE JOB='Mgr' AND YEARS < 8
ORDER BY ID
1.2【EXCEPT】:
包括所有在表1中而不在表2中的行并消除所有重复行；如果 EXCEPT ALL 则不消除重复行
SELECT ID, NAME FROM STAFF WHERE SALARY > 21000
EXCEPT
SELECT ID, NAME FROM STAFF WHERE JOB='Mgr' AND YEARS < 8
--也就是说，凡是满足 EXCEPT 后面语句的数据都不在选择的范围内
1.3【INTERSECT】：
只包括表1和表2都有的行并消除所有重复行；如果 INTERSECT ALL 则不消除重复行
SELECT ID, NAME FROM STAFF WHERE SALARY > 21000
INTERSECT
SELECT ID, NAME FROM STAFF WHERE JOB='Mgr' AND YEARS < 8
2、【谓词】
2.1【IN】【NOT IN】
SELECT NAME
FROM STAFF
WHERE DEPT IN (20, 15)
SELECT LASTNAME
FROM EMPLOYEE
WHERE EMPNO IN
(SELECT RESPEMP
FROM PROJECT
WHERE PROJNO = 'MA2100'
OR PROJNO = 'OP2012')
2.2【BETWEEN】【NOT BETWEEN】
SELECT LASTNAME
FROM EMPLOYEE
WHERE SALARY BETWEEN 10000 AND 20000
2.3【LIKE】【NOT LIKE】
注意 _ 表示任何单个字符，% 表示任何零个或者多个的字符
SELECT NAME
FROM STAFF
WHERE NAME NOT LIKE 'S%'
2.4【EXISTS】【NOT EXISTS】
(检查存在性)
SELECT DEPTNO, DEPTNAME
FROM DEPARTMENT X
WHERE NOT EXISTS
(SELECT *
FROM PROJECT
WHERE DEPTNO = X.DEPTNO)
ORDER BY DEPTNO
2.5【定量谓词】
(将一个值和一个值的集合进行比较)
2.5.1【> ALL】
(必须全部符合，谓词结果才为真！)
查询所有收入超过所有经理收入的雇员的姓名和职位（其实后面的子查询中返回值有多个，所以用了ALL）
SELECT LASTNAME, JOB
FROM EMPLOYEE
WHERE SALARY > ALL
(SELECT SALARY
FROM EMPLOYEE
WHERE JOB='MANAGER')
2.5.2【> ANY】【> SOME】
(两个谓词同意，只要有一个结果符合，谓词结果即为真)
查询所有收入超过任何一个经理收入的雇员的姓名和职位（其实后面的子查询中返回值有多个，所以用了ALL）
SELECT LASTNAME, JOB
FROM EMPLOYEE
WHERE SALARY > ANY
(SELECT SALARY
FROM EMPLOYEE
WHERE JOB='MANAGER')
六、【定制和增强数据操作】
1、【用户定义类型】UDT
1、创建
CREATE DISTINCT TYPE PAY AS DECIMAL WITH COMPARISONS
2、使用
SELECT * FROM EMPLOYEE WHERE DECIMAL(SALARY)=5120
3、注意：
UDT认为是于任何其他类型不同的类型，例如上例中的 PAY 和 DECIMAL 类型是不同的，但是可以相互转换！
如 PAY(SALARY) 或者 DECIMAL (SALARY)
2、【用户自定义函数】UDF
1、创建
CREATE FUNCTION MAX(PAY) RETURNS PAY
SOURCE MAX(DECIMAL)
2、使用
SELECT column1,FunctionName(XXX) FROM TableXXX
3、注意：
UDF可以调用其他已有库函数和其他UDF。
3、【大对象】LOB
1、简介
BLOB,CLOB,DCLOB分别代表二进制数大对象、字符大对象（多用于字符串）、双字节字符大对象（多用于图形）
2、操作（略）
4、专用寄存器
1、定义:
由DBMS为连接定义的存储区，用于SQL引用的信息。
2、存储的常用数据：
CURRENT DATE、USER、CURRENT TIMESTAMP、CURRENT TIME、CURRENT TIMEZONE(指定与世界时间的差别)、CURRENT SERVER
3、调用
VALUES(CURRENT DATE) 或者 SELECT CURRENT TIME FROM TABLENAME
5、常用系统视图
SYSCAT.CHECKS
SYSCAT.COLUMNS
SYSCAT.COLCHECKS
SYSCAT.KEYCOLUSE
SYSCAT.DATATYPES
SYSCAT.FUNCPARMS
SYSCAT.REFERENCES
SYSCAT.SCHEMATA
SYSCAT.TABCONST
SYSCAT.TABLES--模式
SYSCAT.TRIGGERS
SYSCAT.FUNCTIONS
SYSCAT.VIEWS
