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