注册
登录
| 关键词： tables select tabname 数据库 create zjt 特权 list user grant |
1. 建立数据库DB2_GCBCREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCBUSING CODESET GBK TERRITORY CN COLLATE USING SYSTEM DFT_EXTENT_SZ 322. 连接数据库connect to sample1 user db2admin using 83012063. 建立别名create alias db2admin.tables for sysstat.tables;CREATE ALIAS DB2ADMIN.VIEWS FOR SYSCAT.VIEWScreate 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_delAFTER DELETE ON zjt_tablesREFERENCING OLD AS OFOR EACH ROW MODE DB2SQLInsert 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 tableswhere tabname='ZJT_TABLES';10. 查看列select SUBSTR(COLNAME,1,20) as 列名,TYPENAME as 类型,LENGTH as 长度from columnswhere tabname='ZJT_TABLES';11. 查看表结构db2 describe table user1.departmentdb2 describe select * from user.tables12. 查看表的索引db2 describe indexes for table user1.department13. 查看视图 select viewname from viewswhere viewname='V_ZJT_TABLES';14. 查看索引select indname from indexeswhere indname='I_ZTABLES_TABNAME';15. 查看存贮过程SELECT SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15)FROM SYSCAT.PROCEDURES;16. 类型转换(cast)ip datatype:varcharselect cast(ip as integer)+50 from log_comm_failed17. 重新连接connect reset18. 中断数据库连接disconnect db2_gcb19. view applicationLIST APPLICATION;20. kill applicationFORCE APPLICATION(0);db2 force applications all (强迫所有应用程序从数据库断开)21. lock tablelock table test in exclusive mode22. 共享lock table test in share mode23. 显示当前用户所有表list tables24. 列出所有的系统表list tables for system25. 显示当前活动数据库list active databases26. 查看命令选项list command options27. 系统数据库目录LIST DATABASE DIRECTORY28. 表空间list tablespaces29. 表空间容器LIST TABLESPACE CONTAINERS FORExample: LIST TABLESPACE CONTAINERS FOR 130. 显示用户数据库的存取权限GET AUTHORIZATIONS31. 启动实例DB2START32. 停止实例db2stop33. 表或视图特权grant select,delete,insert,update on tables to usergrant all on tables to user WITH GRANT OPTION34. 程序包特权GRANT EXECUTEON PACKAGE PACKAGE-nameTO PUBLIC35. 模式特权GRANT CREATEIN ON SCHEMA SCHEMA-name TO USER36. 数据库特权grant connect,createtab,dbadm on database to user37. 索引特权grant control on index index-name to user38. 信息帮助 (? XXXnnnnn )例:? SQL3008139. SQL 帮助(说明 SQL 语句的语法) |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
