DB2语句优化

查询指定行数:select ... where ...FETCH FIRST 10 ROWS ONLY;
1.INSERT 语句
1)INSERT INTO USER (NAME,BIRTHDAY) VALUES('张三','2000-1-1'),('李四','2000-1-1'),
                                         ('王五','2000-1-1');
2)INSERT INTO USER (NAME,BIRTHDAY) SELECT <COLUMN1>,<COLUMN2> FROM <TABLE_NAME> WHERE ...
2.UPDATE 语句
ROW_NUMBER() OVER()函数:ROW_NUMBER
UPDATE(
SELECT TT.*, ROW_NUMBER() OVER() AS RN FROM TRANSACTION AS TT WHERE CUSTOMERID=... )
      SET SEQ=RN
----
UPDATE USER SET BIRTHDAY=
( CASE NAME WHEN '张三' THEN '1949-10-1'
            WHEN '李四' THEN '1997-7-1'
            ELSE BIRTHDAY
            END )
where NAME in ('张三','李四');
--防止关联不到的被更新为null
UPDATE a SET a.a1=(select b1 from b where b.b2=a.a2)where exists(select 1 from b where b.b2=a.a2);
--批量更新:
UPDATE TBL_ABOUT_RECIPIENT SET (STAFF_ID,STAFF_NAME) = ('', '');
3.删除数据(删除大量数据)
1)DELETE FROM( SELECT * FROM <TABLE_NAME> WHERE <CONDITION> );
2)删除所有表数据：先DROP TABLE，然后CREATE TABLE 的方式
3)提升性能语句:表的操作将不会记录日志，适合临时表
ALTER TABLE <TABLE_NAME> ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE
相关子查询
1.查询：
SELECT * FROM USER WHERE (BIRTHDAY,DEGREE) = ('1980-8-8','硕士');
SELECT * FROM USER WHERE (BIRTHDAY,DEGREE) IN(---此处是子查询);
跟新：
UPDATE USER SET (BIRTHDAY,DEGREE)=
( --此处是相关子查询 ) WHERE <CONDITION>;
2.GROUPING SETS:
GROUP BY GROUPING SETS (A,B,C) 等价与
     GROUP BY A
      UNION ALL
     GROUP BY B
      UNION ALL
     GROUP BY C
GROUP BY GROUPING SETS ((A,B,C)) 等价与 GROUP BY A,B,C    
3.UNION, INTERSECT, EXCEPT
UNION用来求两个集合的并集，INTERSECT用来求两个集合的交集，EXCEPT用来求在第一个集合中存在，
而在第二个集合中不存在的记录。每个关键字后面都可以接ALL(UNION ALL, INTERSECT ALL, EXCEPT ALL),
如果不接ALL，操作集合将会去掉重复值。
4.在操作数据的同时查看操作前或操作后的值
1)更新数据的同时查看一下哪些值被更新了，更新前或更新后的值是多少
SELECT * FROM FINAL TABLE
( UPDATE USER SET SALARY=SALARY*(1+0.2) WHERE SALARY<=2000 )
WHERE NAME LIKE '李%' FETCH FIRST 10 ROWS ONLY;
--更新后姓李的人取10条查看一下
2)查看更新前的值
SELECT * FROM OLD TABLE
( UPDATE USER SET SALARY=SALARY*(1+0.2) WHERE SALARY<=2000 )
WHERE NAME LIKE '李%' FETCH FIRST 10 ROWS ONLY;
3)看一下更新前的值和更新后的值 INCLUDE
SELECT * FROM FINAL TABLE
( UPDATE USER INCLUDE (OLD_SALARY FLOAT) SET SALARY=SALARY*(1+0.2),OLD_SALARY=SALARY
 WHERE SALARY<=2000 )
--把上面的UPDATE语句换成INSERT和DELETE语句同样适用。
5.尽量避免在SQL语句的WHERE子句中使用函数
如时间的对比用范围 代替 等于某个时间
SELECT * FROM USER WHERE REGISTERDATE>=to_date('2009-9-24 00:00:00.0','yyyy-mm-dd hh24:mi:ss')
AND REGISTERDATE<to_date('2009-9-25 00:00:00.0','yyyy-mm-dd hh24:mi:ss');
6.尽量避免在SQL语句中使用LIKE
如范围扫描 代替 like
开头是2102
SELECT * FROM USER WHERE MYNUMBER>='210200000000000000' AND MYNUMBER<'210300000000000000';
7.指定隔离级别
可重复读(RR)
读稳定性(RS)
游标稳定性(CS)---默认的隔离级别
未落实的读(UR)
SELECT * FROM <TABLE-NAME> WITH UR;
隔离级别越低，并发性越好，但是导致的并发性问题也越多
8.CAST 表达式
CAST(NAME AS CHAR(4) )AS NAME2---将VARCHAR 转换为CHAR
CAST(SALARY AS INTEGER) AS SALARY2, ---将DOUBLE 转换为INTEGER
CAST(NULL AS INTEGER) AS TEST_NULL---将NULL 转换为INTEGER
ORDER BY CAST(ID AS INTEGER); ---将VARCHAR 转换为INTEGER
9.WITH语句是用查询(也就是select 语句)来定义临时集合
WITH TEST(NAME_TEST, BDAY_TEST) AS
( VALUES ('张三','1997-7-1'),('李四','1949-10-1') )
SELECT NAME_TEST FROM TEST WHERE BDAY_TEST='1949-10-1'
--如何用WITH 语句做递归查询
WITH TEMP(PARENTID,ID,NAME) AS
( SELECT PARENTID,ID,NAME FROM BBS WHERE NAME='DB2'---语句1
UNION ALL---语句2
SELECT B.PARENTID,B.ID,B.NAME FROM BBS AS B, TEMP AS T WHERE B.PARENTID=T.ID---语句3
)
SELECT NAME FROM TEMP;---语句4
10.分页查询
SELECT * FROM
( SELECT B.*, ROWNUMBER() OVER() AS RN FROM
  ( SELECT * FROM <TABLE_NAME> ) AS B
    )AS A WHERE A.RN BETWEEN <START_NUMBER> AND <END_NUMBER>;