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 FORk 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;