DB常用SQL写法.docVIP

DB2常用SQL的写法 DB2常用SQL的写法(未整理) UPDATE FROM update a set aa =(select bb from b where a.cc = b.cc) where exists (select b.cc from a.cc =b.cc) 1. 建立数据库DB2_GCB CREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCB USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM DFT_EXTENT_SZ 32 2. 连接数据库 connect to sample1 user db2admin using 8301206 3. 建立别名 create alias db2admin.tables for sysstat.tables; CREATE ALIAS DB2ADMIN.VIEWS FOR SYSCAT.VIEWS create alias db2admin.columns for syscat.columns; create alias guest.columns for syscat.columns; 4. 建立表 create table zjt_tables as (select * from tables) definition only; create table zjt_views as (select * from views) definition only; 5. 插入记录 insert into zjt_tables select * from tables; insert into zjt_views select * from views; 6. 建立视图 create view V_zjt_tables as select tabschema,tabname from zjt_tables; 7. 建立触发器 CREATE TRIGGER zjt_tables_del AFTER DELETE ON zjt_tables REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL Insert into zjt_tables1 values(substr(o.tabschema,1,8),substr(o.tabname,1,10)) 8. 建立唯一性索引 CREATE UNIQUE INDEX I_ztables_tabname [size=3]ON zjt_tables(tabname); 9. 查看表 select tabname from tables where tabname=ZJT_TABLES; 10. 查看列 select SUBSTR(COLNAME,1,20) as 列名,TYPENAME as 类型,LENGTH as 长度 from columns where tabname=ZJT_TABLES; 11. 查看表结构 db2 describe table user1.department db2 describe select * from user.tables 12. 查看表的索引 db2 describe indexes for table user1.department 13. 查看视图 select viewname from views where viewname=V_ZJT_TABLES; 14. 查看索引 select indname from indexes where indname=I_ZTABLES_TABNAME; 15. 查看存贮过程 SELECT SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15) FROM SYSCAT.PROCEDURES; 16. 类型转换(cast) ip datatype:varchar select cast(ip as integer)+50 from log_comm_failed 17. 重新连接 connect reset 18. 中断数据库连接 disconnect db2_gcb 19. view application LIST APPLICATION; 20. kill application FORCE APPLICATION(0); db2 force applications all (强