DB2语句优化
来源:互联网 发布:通过销售数据进行分析 编辑:程序博客网 时间:2024/06/01 23:26
查询指定行数: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>;
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>;
阅读全文
0 0
- DB2语句优化
- DB2 SQL语句的优化
- DB2&SQL语句性能优化
- db2优化sql语句的方法
- SQL语句优化DB2应用程序性能
- db2 语句
- 探讨SQL语句编程技巧 优化DB2应用程序性能
- 数据库优化——SQL语句编写(DB2)
- DB2优化
- db2中的循环语句。
- db2 with递归语句
- db2中的循环语句
- db2中的循环语句
- DB2 INSERT 语句
- DB2分页查询语句
- DB2常用语句总结
- DB2 With递归语句
- DB2 UPDATE 语句
- 排序
- 接口测试指导之必收秘籍(内含用例及执行注意点)
- angularjs 动态控制添加 元素 (动态添加成员)
- 二级列表完美实现购物车
- there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT
- DB2语句优化
- Python小脚本——批量修改文件名与文件扩展名
- Setting property 'source' to 'org.eclipse.jst.jee.server:XX did not find a mat的解决办法
- DefaultHttpClient过时的替换
- [日推荐]『识花草』做个辛勤的小园丁
- 2017-9-17pat甲级 B
- 码云 IntelliJ IDEA 插件 Intellij-GitOSC
- dwr实现javaweb实时聊天通讯网页版常见问题及解决方案
- 流媒体相关交互协议和开源服务介绍