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