一、常用命令
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?
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?(強迫所有應用程序從數據庫斷開)?
21.?lock?table
lock?table?test?in?exclusive?mode?
22.?共享?
lock?table?test?in?share?mode?
23.?顯示當前用戶所有表?
list?tables?
24.?列出所有的系統表?
list?tables?for?system?
25.?顯示當前活動數據庫?
list?active?databases?
26.?查看命令選項?
list?command?options?
27.?系統數據庫目錄?
LIST?DATABASE?DIRECTORY?
28.?表空間?
list?tablespaces?
29.?表空間容器?
LIST?TABLESPACE?CONTAINERS?FOR?
Example:?LIST?TABLESPACE?CONTAINERS?FOR?1?
30.?顯示用戶數據庫的存取權限?
GET?AUTHORIZATIONS?
31.?啟動實例?
DB2START?
32.?停止實例?
db2stop?
33.?表或視圖特權?
grant?select,delete,insert,update?on?tables?to?user?
grant?all?on?tables?to?user?WITH?GRANT?OPTION?
34.?程序包特權?
GRANT?EXECUTE?
ON?PACKAGE?PACKAGE-name?
TO?PUBLIC?
35.?模式特權?
GRANT?CREATEIN?ON?SCHEMA?SCHEMA-name?TO?USER?
36.?數據庫特權?
grant?connect,createtab,dbadm?on?database?to?user?
37.?索引特權?
grant?control?on?index?index-name?to?user?
38.?信息幫助?(??XXXnnnnn?)?
例:??SQL30081?
39.?SQL?幫助(說明?SQL?語句的語法)?
help?statement?
例如，help?SELECT?
40.?SQLSTATE?幫助(說明?SQL?的狀態和類別代碼)?
??sqlstate?或???class-code?
41.?更改與"管理服務器"相關的口令?
db2admin?setid?username?password?
42.?創建?SAMPLE?數據庫?
db2sampl?
db2sampl?F:(指定安裝盤)?
43.?使用操作系統命令?
!?dir?
44.?轉換數據類型?(cast)?
SELECT?EMPNO,?CAST(RESUME?AS?VARCHAR(370))?
FROM?EMP_RESUME?
WHERE?RESUME_FORMAT?=?'ascii'?
45.?UDF
要運行?DB2?Java?存儲過程或?UDF，還需要更新服務器上的?DB2?數據庫管理程序配置，以包括在該機器上安裝?JDK?的路徑?
db2?update?dbm?cfg?using?JDK11_PATH?d:\sqllib\java\jdk?
TERMINATE?
update?dbm?cfg?using?SPM_NAME?sample?
46.?檢查?DB2?數據庫管理程序配置?
db2?get?dbm?cfg?
47.?檢索具有特權的所有授權名?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'DATABASE'?FROM?SYSCAT.DBAUTH?
UNION?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'TABLE?'?FROM?SYSCAT.TABAUTH?
UNION?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'PACKAGE?'?FROM?SYSCAT.PACKAGEAUTH?
UNION?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'INDEX?'?FROM?SYSCAT.INDEXAUTH?
UNION?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'COLUMN?'?FROM?SYSCAT.COLAUTH?
UNION?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'SCHEMA?'?FROM?SYSCAT.SCHEMAAUTH?
UNION?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'SERVER?'?FROM?SYSCAT.PASSTHRUAUTH?
ORDER?BY?GRANTEE,?GRANTEETYPE,?3?
create?table?yhdab?
(id?varchar(10),?
password?varchar(10),?
ywlx?varchar(10),?
kh?varchar(10));?
create?table?ywlbb?
(ywlbbh?varchar(8),?
ywmc?varchar(60))?
48.?修改表結構?
alter?table?yhdab?ALTER?kh?SET?DATA?TYPE?varchar(13);?
alter?table?yhdab?ALTER?ID?SET?DATA?TYPE?varchar(13);?
alter?table?lst_bsi?alter?bsi_money?set?data?type?int;?
insert?into?yhdab?values?
('20000300001','123456','user01','20000300001'),?
('20000300002','123456','user02','20000300002');?
49.?業務類型說明?
insert?into?ywlbb?values?
('user01','業務申請'),?
('user02','業務撤消'),?
('user03','費用查詢'),?
('user04','費用自繳'),?
('user05','費用預存'),?
('user06','密碼修改'),?
('user07','發票打印'),?
('gl01','改用戶基本信息'),?
('gl02','更改支付信息'),?
('gl03','日統計功能'),?
('gl04','沖帳功能'),?
('gl05','對帳功能'),?
('gl06','計費功能'),?
('gl07','綜合統計')
二.?目錄視圖說明
說明 目錄視圖?
檢查約束 SYSCAT.CHECKS?
列?? SYSCAT.COLUMNS?
檢查約束引用的列 SYSCAT.COLCHECKS??
關鍵字中使用的列?? SYSCAT.KEYCOLUSE??
數據類型?? SYSCAT.DATATYPES??
函數參數或函數結果?? SYSCAT.FUNCPARMS??
參考約束?? SYSCAT.REFERENCES??
模式? SYSCAT.SCHEMATA??
表約束?? SYSCAT.TABCONST??
表?? SYSCAT.TABLES??
觸發器?? SYSCAT.TRIGGERS??
用戶定義函數? SYSCAT.FUNCTIONS??
視圖?? SYSCAT.VIEWS??
三.?字符串類型
二進制大對象?(BLOB)?字符串。?
字符大對象?(CLOB)?字符串，它的字符序列可以是單字節字符或多字節字符，或這兩者的組合。?
雙字節字符大對象?(DBCLOB)?字符串，它的字符序列是雙字節字符。?
四.?數據庫范式
第一種規范形式：表中的每一行和每一列均有一個值，永遠不會是一組值。?
第二種規范形式：不在關鍵字中的每一列提供取決于整個關鍵字的事實。?
第三種規范形式：每個非關鍵字列提供與其他非關鍵字列無關并只取決于該關鍵字的事實。?
第四種規范形式：沒有行包含有關一個實體的兩個或更多個獨立的多值事實。?
五.?數據類型
數據類型? 類型?? 特性?? 示例或范圍?
CHAR(15)? 定長字符串 ?最大長度為?254??? 'Sunny?day?'?
VARCHAR(15)? 變長字符? 最大長度為?4000??? 'Sunny?day'?
SMALLINT?? 數字? 長度為?2?字節精度為?5?位??范圍為-32768?至?32767?
INTEGER?? 數字? 長度為?4?字節精度為?10?位??范圍為-2147483648?至?2147483647?
REAL?? 數字? 單精度浮點32?位近似值?? 范圍為-3.402E+38至-1.175E-37或?1.175E-37?至-3.402E+38或零?
DOUBLE?? 數字? 雙精度浮點64?位近似值? 范圍為-1.79769E+308?至-2.225E-307或?2.225E-307?至?1.79769E+308或零?
DECIMAL(5,2)? 數字? 精度為?5小數位為?2? 范圍為?-10**31+1?至?10**31-1?
DATE? 日期時間?? 三部分值? 1991-10-27????
TIME? 日期時間?? 三部分值? 13.30.05????
TIMESTAMP? 日期時間?? 七部分值??? 1991-10-27-13.30.05.000000?
六.?列函數?
列函數對列中的一組值進行運算以得到單個結果值。下列就是一些列函數的示例。?
AVG?
返回某一組中的值除以該組中值的個數的和?
COUNT?
返回一組行或值中行或值的個數?
MAX?
返回一組值中的最大值?
MIN?
返回一組值中的最小值?
七.?標量函數?
標量函數對值進行某個運算以返回另一個值。下列就是一些由DB2?通用數據庫提供的標量函數的示例。?
ABS?
返回數的絕對值?
HEX?
返回值的十六進制表示?
LENGTH?
返回自變量中的字節數（對于圖形字符串則返回雙字節字符數。）?
YEAR?
抽取日期時間值的年份部分?