DB2 SQL自学笔记

db2与其他数据相比，不同的地方如下所示：

数据类型来说： Decimal:一个固定位数的压缩的十进制数字。 10**31+1 to 10**31-1

               Real：一个数字的32位bite近似值。

 

时间与日期：

日期的格式：

 

 

 

Format Name	Abbreviation	Date Format	Example
International Standards Organization	ISO	yyyy-mm-dd	1991-10-27
IBM USA standard	USA	mm/dd/yyyy	10/27/1991
IBM European standard	EUR	dd.mm.yyyy	27.10.1991
Japanese Industrial Standard Christian Era	JIS	yyyy-mm-dd	1991-10-27

时间的格式：

 

Format Name	Abbreviation	Date Format	Example
International Standards Organization	ISO	hh.mm.ss	13.30.05
IBM USA standard	USA	hh:mm AM or PM	1:30 PM
IBM European standard	EUR	hh.mm.ss	13.30.05
Japanese Industrial Standard Christian Era	JIS	hh:mm:ss	13:30:05

Timestamp的格式如下所示：

yyyy-mm-dd hh:mm:ss

 

其他的数据类型：

blob：二进制字符串

xml：xml文件

还有图形字符串类型，自行补充。

 

DDL的区别

DECLARE：除了创建在过程中使用的临时表外，与CREATE类似。唯一可被声明的对象是表，并且必须放入用户的临时表空间当中。可以定义程序中的变量和游标。 

 创建和删除数据库的语句：

create database database-name

drop database database-name

创建新表的语句

  create tabe table-name (col1 data-type not null,col2 ... ... not null,coln data-type not null)

根据已有的表创建新表：

  create table new-table-name like old-table-name

  create table new-table-name as select col1,col2,... ... ，coln from old-table-name definition only  

 创建索引和视图

  create [unique] index index-name on table-name (col1, ... ...)

  drop index index-name

  索引是不可以修改的，要想修改必须删除后新建

  create view view-name as select statement

  drop view view-name

视图唯一能修改的是引用列的数据类型，视图的基表删除后，视图无效。

 

索引的规划

l 要避免某些排序，只要有可能，就通过使用 CREATE UNIQUE INDEX 语句定义主键和唯一键。

l 要改善数据检索，将 INCLUDE 列添加至唯一索引。合适的列为：

n 被频繁访问，因此可从纯索引访问中受益的列。

n 不需要用来限制索引扫描的范围的列

n 不影响索引键的排序或唯一性的列。

l 对含有较多数据页的表的频繁查询，数据页数记录在 SYSCAT.TABLES 目录视图的 NPAGES 列中。您应该：

n 根据连接表时要使用的任何一列来创建索引。

n 根据将用于定期搜索特定值的任何列来创建索引。

l 要有效地搜索，决定对键使用升序还是降序，这取决于将最常使用的顺序。尽管当在 CREATE INDEX 语句中指定了 ALLOW REVERSE SCANS 参数时可以按逆向方向搜索值，但是，执行按指定索引顺序的扫描比执行逆向扫描稍微更快一些。

要保存索引维护成本和空间：

l 避免创建的关系索引是这些列上其他索引键的部分键。例如，如果列 a 、 b 和 c 上有索引，则列 a 和 b 上的第二个索引一般用处不大。

l 不要在所有列上任意创建关系索引。不必要的索引不仅使用空间，而且导致大量准备时间。当使用具有动态编程连接枚举的优化级别时，这对于复杂的查询特别重要。

l 使用下列一般规则来确定将为表定义的关系索引的典型数目。此数目根据数据库的主要使用来确定：

对于联机事务处理（OLTP）环境，创建一个或两个索引

对于只读查询环境，可以创建 5 个以上索引

对于混合查询和 OLTP 环境，可以创建 2 到 5 个索引。

l 要提高涉及到 IMMEDIATE 和 INCREMENTAL MQT 的 DELETE 和 UPDATE 操作的性能，对 MQT 的隐含唯一键创建唯一关系索引，该隐含唯一键是 MQT 定义的 GROUP BY 子句中的列。

l 对于快速排序操作，在频繁用于排序关系数据的列上创建关系索引。

l 要提高多列索引的连接性能，如果第一个键列有多项选择，则选用最常用“ =” （等值连接）谓词指定的那一列，或使用具有最高级数的那些列。

l 要帮助新插入的行根据索引进行集群并避免页分割，定义一个集群索引。集群索引应显著减少重组表的需要。

l 当定义表时使用 PCTFREE 关键字来指定页上应该留下多少可用空间，才能允许将插入行适当地放在页上。也可以指定 LOAD 命令的 pagefreespace MODIFIED BY 子句。

l 要启用联机索引整理碎片，创建关系索引时使用 MINPCTUSED 选项。 MINPCTUSED 指定索引叶子页中最小使用空间量的阈值并启用联机索引整理碎片。如果这些删除实际上从索引页除去键，则这可以在键删除期间以性能损失为代价而减少重组的需要。

 

join使用的注意

l 表越小，越有可能被选为外表。这有助于减少必须再次访问内表的次数。

l 如果选择谓词可以应用到某个表，则该表更适合于被选作外表，因为在访问内表时只会用那些符合这些谓词（应用于该外表的）的行。

l 如果可能对其中某个表做索引查找，则该表很适合于作为内表。如果一个表没有索引，则最好不要将其作为内表，因为每扫描外表中的一行，就要扫描一遍整个内表。

l 在连接操作中，重复元素最少的表倾向于被选作外表。

 

select优化策略：

ü 仅指定需要的列。尽管避免 SELECT * ，因为会导致不必要的处理和返回不需要的列。

ü 通过谓词限定结果集

ü 当需要的行数大大小于可能返回的总行数时，指定 OPTIMIZE FOR 子句。此子句影响访问方案的选择以及在通信缓冲区中分块的行数。

ü 当要检索的行数很小时，仅指定 OPTIMIZE FOR k ROWS 子句。不需要 FETCH FIRST n ROWS ONLY 子句。但是，如果 n 值很大，并且想快速获取前 k 行而对后续 k 行进行可能的延迟，则同时指定两个子句。通信缓冲区的大小基于 n 和 k 中的较小者。

下列示例显示两个子句： SELECT EMPNAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC FETCH FIRST 100 ROWS ONLY OPTIMIZE FOR 20 ROWS

ü 合理使用 FOR READ ONLY, FOR UPDATE

ü 尽可能避免数字数据类型转换。当比较值时，使用有相同数据类型的项可能更有效。如果需要转换，则可能由于精度受限制使结果不准确，并且由于运行时转换而使性能降低。如果可能的话，使用下列数据类型：

            对于较短的列，尽量使用字符而不是可变字符

           尽量使用整数，而不是浮点数或小数

           尽量使用日期时间，而不是字符

           尽量使用数字，而不是字符

ü 要减小排序操作发生的可能性，省略诸如 DISTINCT 或 ORDER BY 的子句或操作（如果这种操作不是必需的话）。

ü 要检查一个表中是否存在某些行，选择某一单行。打开游标并访存一行，或执行单行（ SELECT INTO ）选择。记住，如果发现多行，则要检查是否有 SQLCODE -811 错误。

除非您知道表很小，否则，不要使用下列语句来检查非零值： SELECT COUNT(*) FROM TABLENAME 对于大表，对所有行计数会影响性能。

ü 如果更新活动较少且表较大，则在频繁用作谓词的列上定义索引。

ü 如果同一列出现在多个谓词子句中，则考虑使用 IN 列表。对于配合主变量使用的大型 IN 列表，循环主变量的子集可能会提高性能。

 

优化多表关联的select

n 在连接谓词中的列上定义索引，可以更有效地处理连接。索引也会改善 UPDATE 和 DELETE 语句的性能，这些语句包含访问几个表的 SELECT 语句。

n 如果可能的话，避免将表达式或 OR 子句与连接谓词一起使用，因为数据库管理器不能使用某些连接技术。因此，可能未选择最有效的连接方法。

n 在一个分区数据库环境中，如果可能的话，确保在连接列上对已连接的两个表进行了分区。

 

优化Insert的方法

1. 减少索引的个数 ( 或者考虑批量 INSERT 后重建索引 )

2. 约束会影响 INSERT 性能

3. 触发器会影响 INSERT 性能

4. IDENTITY 字段和 SEQUENCE 会影响 INSERT 性能

5. MQT 会影响 INSERT 性能

6. DPF 环境下，考虑 Buffer Insert

7. 将 LOB 和 LONG 改为 VARCHAR

8. INSERT 语句中查询子句的执行效率

优化update的考虑

− 1 、建立合适的索引。

− 2 、经常 update 的列顺序。

− 3 、可能的话，使用 merge 代替 update 与 insert 。

 

 

DCL数据库控制语言

grant:赋予权限

revoke:撤销用户权限

commit:提交事务,可以使数据库的修改永久化.

rollback:回滚事务,消除上一个提交的commit修改操作,使数据库恢复到上一个commit执行之后的状态.

 

事务的管理

commit事务要考虑很多的情况,下面是一些建议:

 为了减少锁等待时间，应用的逻辑设计应遵循以下规则：

A：估计可能的提交最长时间的间隔，如果超过阀值（通常为5秒），就要使用以下方法来缩短提交间隔

§ 尽量在逻辑工作单元的后面更新数据。

§ 尽早关闭光标。

§ 只要有可能，尽量提早提交修改。 

§ 当发现错误时，应当尽早回滚。

 B：按一定的逻辑顺序访问数据，以防止死锁。当不同的应用访问相同的数据时，他们应该遵循同一顺序来访问数据。

 

SQL高级语法

union运算符通过组合其他两个结果表派生出一个没有任何重复行的结果表。当all与union连用的时候，表示不去掉两表中的重复行。（union all）

except运算符通过包含所有在表table1但不在表table2中的去掉重复行的结果表。当all与except连用时（except all），不消除重复行。

intersect运算符通过既包含在表table1中，又包含在table2中的消除重复行的结果表。当它与all连用时（intersect all），不消除重复行。

复合外连接查询的结果集是从左到右的新顺序连接结果集。

grouping sets:用来在一个SQL中形成多级分组。例如：

    select company_id,node_id,count(customer_id) from customer group by grouping sets(company_id,node_id);

rollup:可以在单个数据库操作中形成多个分组。例如：

    select company_id,node_id,count(customer_id) from customer group by rollup(company_id,node_id);

cube:生成分组表中分组的所有组合。例如：

    select company_id,node_id,count(customer_id) from customer group by cube(company_id,node_id);

over：移动函数可以帮助实现移动的数据分析。例如：

    select date,avg(qty) over (order by date rows between 1 preceding and 1 following) as values from sale

merge:可以将一个表中的数据合并到另一个表中，在合并的同时可以根据条件进行插入，删除，更新等操作。例如：  

MERGE INTO table_name alias1

USING (table|view|sub_query) alias2

ON (join condition)

WHEN MATCHED THEN

     UPDATE table_name SET col1 = col_val1,col2 = col2_val

WHEN NOT MATCHED THEN

     INSERT (column_list) VALUES (column_values)

 

关键字、参数

into子句 

在into子句中指定所要修改或者插入数据的目标表

using子句 

在using子句中指定用来修改或者插入的数据源。数据源可以是表、视图或者一个子查询语句。

on子句 

在on子句中指定执行插入或者修改的满足条件。

when matched | not matched 

用该子句通知 数据库 如何对满足或不满足条件的结果做出相应的操作。可以使用以下的两类子句。
 

merge _update子句 

merge _update子句执行对目标表中的字段值修改。当在符合on子句条件的情况下执行。如果修改子句执行，则目标表上的修改触发器将被触发。
 

限制：当修改一个视图时，不能指定一个default值

merge _insert 子句 

merge _insert子句执行当不符合on子句条件时，往目标表中插入数据。如果插入子句执行，则目标表上插入触发器将被触发。

限制：当修改一个视图时，不能指定一个default值

 

示例：

MERGE INTO BONUSES D 

USING (SELECT EMPLOYEE_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 80) S 

ON (D.EMPLOYEE_ID = S.EMPLOYEE_ID) 

WHEN MATCHED THEN UPDATE SET D.BONUS = D.BONUS + S.SALARY*.01 

WHEN NOT MATCHED THEN INSERT (D.EMPLOYEE_ID, D.BONUS)  VALUES (S.EMPLOYEE_ID, S.SALARY*0.01);

 

MERGE INTO XCMDTRANSFERSTATE AS T  

USING TABLE (VALUES(?,?,?,?,?,?))  T1(DATASNO,EXTERIORSYSTEM,STATUS,DATA_TIME,MARKFORDELETE,DATATRANSFERNO)  

ON (T.DATATRANSFERNO = T1.DATATRANSFERNO)  

WHEN MATCHED THEN  update set T.DATASNO = T1.DATASNO,T.EXTERIORSYSTEM = T1.EXTERIORSYSTEM,T.STATUS = T1.STATUS,T.LASTUP_TIME = T1.DATA_TIME,T.MARKFORDELETE = T1.MARKFORDELETE  

WHEN NOT MATCHED THEN    INSERT (DATASNO,EXTERIORSYSTEM,STATUS,CREATE_TIME,MARKFORDELETE,DATATRANSFERNO) VALUES (T1.DATASNO,T1.EXTERIORSYSTEM,T1.STATUS,T1.DATA_TIME,T1.MARKFORDELETE,T1.DATATRANSFERNO

 

MERGE INTO EMPLOYE AS EM  

USING MANAGER AS MA  

ON EM.EMPLOYEID=MA.MANAGERID  

WHEN MATCHED AND EM.SALARY<MA.SALARY THEN UPDATE SET EM.SALARY=MA.SALARY  

WHEN MATCHED AND EM.SALARY>MA.SALARY THEN SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'EM.SALARY>MA.SALARY'

WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY)  

ELSE IGNORE;