blogspot.com-GA4

星期一, 6月 09, 2008

主要SQL語法詳解

主要SQL語法詳解

CREATE DATABASE database_name [WITH LOG IN 「pathname」]


建立資料庫。

database_name:資料庫名稱。

「pathname」:事務處理日誌檔案。

建立一database_name.dbs目錄,存取權限由GRANT設定,無日誌檔案就不能使用

BEGIN WORK等事務語法(可用START DATABASE語法來改變)。

可選定當前資料庫的日誌檔案。

如:select dirpath form systables where tabtype = 「L」;

例:create databse customerdb with log in 「/usr/john/log/customer.log」;



DATABASE databse-name [EXCLUSIVE]

選擇資料庫。

database_name:資料庫名稱。

EXCLUSIVE:獨佔狀態。

存取當前目錄和DBPATH中指定的目錄下的資料庫,事務中處理過程中不要使用此語法。

例:dtabase customerdb;



CLOSE DATABASE


關閉當前資料庫。

database_name:資料庫名稱。


此語法之後,只有下列語法合法:

CREATE DATABASE; DATABASE; DROP DATABSE; ROLLFORWARD DATABASE;

刪除資料庫前必須使用此語法。

例:close database;


DROP DATABASE database_name


刪除指定資料庫。

database_name:資料庫名稱。

使用者是DBA或所有表的擁有者;刪除所有檔案,但不包括資料庫目錄;不允許刪除當前資料庫(須先關閉當前資料庫);事務中處理過程中不能使用此語法,通過ROLLBACK WORK 也不可將資料庫恢復。

例:drop databse customerdb;



CREATE [TEMP] TABLE table-name (column_name datatype [NOT NULL], …)

[IN 「pathname」]

建立表或臨時表。

table-name :資料表名稱稱。

column_name:字段名稱。

data-type:字段資料類型。

path-name:指定表的存放位置

TEMP用於指定建立臨時表;資料表名稱要唯一,字段要唯一;有CONNECT權限的使用者可建立臨時表;建立的表預設允許CONNECT使用者存取,但不可以ALTER。


例:create table user

( c0 serial not null, c1 char (10),

c2 char(2),

c3 smallint,

c4 decimal(6,3),

c5 date

) in 「usr/john/customer.dbs/user;



ALTER TABLE

ALTER TABLE table-name

{ADD (newcol_name newcol_type [BEFORE oldcol_name], …) | DROP (oldcol_name, …)

| MODIFY (oldcol_name newcol_type [NOT NULL], … )}, …

修改表結構。

table-name:資料表名稱稱。

newcol_name:新字段名稱

newcol_type:新字段類型

oldcol_name:老字段名稱

可以使用單個或多個ADD子句、DROP子句、MODIFY子句,但某個字句失敗,操作即中止;原字段是NULL,不允許MODIFY為NOT NULL,除非所有NULL字段中均非空,反之可以;ALTER使用者是表的擁有者或擁有DBA權限,或被授權;事務中處理過程中不要使用此語法。

例:alter table user

add ( c6 char(20) before c5);



RENAME TABLE oldname TO newname

修改資料表名稱。

oldname:原名稱。

newname:新名稱。

RENAME使用者是表的擁有者或擁有DBA權限,或被授權;事務中處理過程中不要使用此語法。

例:rename user to bbb;



DROP TABLE table-name

刪除表。

table-name:資料表名稱稱。

刪除表意味著刪除其中所有資料、各字段上的索引及對表的賦權、視圖等;使用者不能刪除任何系統目錄表;語法使用者是表擁有者或擁有DBA權限,事務中處理過程中不要使用此語法。



RENAME COLUMN table.oldcolumn, TO newcolumn

修改字段名。

table.oldcolumn:資料表名稱及原字段名稱

newcolumn:新字段名稱。

語法使用者是表的擁有者或擁有DBA權限或有ALTER權限的使用者,事務中處理過程中不要使用此語法。

例:rename column user.c6 to c7;

CREATE VIEW view-name column-list


CREATE VIEW view-name column-list AS select_statement [WITH CHECK OPTION]

建立視圖。

view-name:視圖名稱。

column-list:字段列表。

select_statement:SELECT語法。

以下語法不使用視圖:ALTER TABLE,DROP INDEX,ALTER INDEX,LOCK TABLE,CREATE INDEX, RENAME TABLE;視圖將延用基表的字段名,對表達式等虛字段和多表間字段重名必須指明標識其字段名;若對視圖中某些字段命名,則所有字段都必須命名;視圖中資料類型延用基表中的資料類型,虛字段起訣於表達式;不能使用ORDER BY和UNION子句;對視圖中所有的字段要有SELECT權限;事務中處理過程中使用此語法,即使事務回滾,視圖也將建立,不能恢復。

例:create view v_user as select * from user where c1 = 「B1」;



DROP VIEW view-name

刪除視圖。

view-name:視圖名稱。

使用者可刪除自己建立的視圖;視圖的後代視圖也被刪除;事務中處理中不要使用此語法。

例:drop view v_user;

CREATE INDEX


CREATE [UNIQUE/DISTINCT] [CLUSTER] INDEX index_name ON table_name

([column_name ASC/DESC],…)

建立索引。

index_name:索引名稱。

table_name:資料表名稱稱。

column_name:字段名稱。

UNIQUE/DISTINCT:唯一索引。

CLUSTER:使表的實際存放順序按索引排列。

ASC/DESC:升序或降序,預設升序。

語法執行時,將表的狀態置為EXCLUSIVE;復合索引最多包含8個字段,所有字段長度和不得大於120字節;事務中處理過程中使用此語法,即使事務回滾,索引將建立,不能恢復。

例:create cluster index ix_user on user(c5);



ALTER INDEX index-name TO [NOT] CLUSTER

修改索引性質。

index-name:索引名稱。

TO [NOT] CLUSTER:去掉或加上CLUSTER屬性。

語法執行時,將表的狀態置為EXCLUSIVE;事務中處理過程中使用此語法,即使事務回滾,索引性質將改變,不能恢復。

例:alter index ix_user to not cluster;

DROP INDEX index-name


刪除索引。

index-name:索引名稱。

語法使用者是索引的擁有者或擁有DBA權限,事務中處理過程中不要使用此語法,否則事務無法恢復。

例:drop index ix_user;



CREATE SYNONYM synonym FOR table-name

建立同義名。

synonym:同義名

table-name:資料表名稱稱

資料庫的建立者可以使用同義名;沒有賦予同義名權限的使用者不能使用同義名;同義名不能和資料表名稱相同;事務中處理過程中不要使用此語法。

例:create synonym user_alias for user;



DROP SYNONYM synonym

刪除同義名。

synonym:同義名

可以刪除自己建立的同義名;事務中處理過程中不要使用此語法,否則無法恢復。

例:drop synonym user_alias;



UPDATE STATISTICS [FOR TABLE table-name]

更新資料庫的統計數字。

table-name:資料表名稱稱

此語法僅作用於當前資料庫;可提高查詢效率;只有執行此語法,才改變統計資料。


例:update statistics for table user;

GRANT {DBA|RESOURCE|CONNECT} TO {PUBLIC|user-list}
授權命令。

PUBLIC|user-list:全部或指定的使用者。

三種權限居且僅居其一,事務處理過程中不要執行GRANT語法。

例:grant resource to pulbic;

GRANT tab-privilege ON table-name TO {PUBLIC|user-list} [WITH GRANT OPTION]


授表級權限。

tab-privilege:表級權限。

table-name:資料表名稱稱。

PUBLIC|user-list:全部或指定的使用者。

[WITH GRANT OPTION]:表示被授權使用者有否權限進行二次授權。

使用者可以在自己建立表達式或被[WITH GRANT OPTION]准許的表中進行賦權;限定越多的權限優先級越高。

例:grant update(c1,c6) on user to dick with grant option;



附(INFORMIX的權限)

(1) 資料庫的權限(控制對資料庫的瀏覽以及資料庫中表的建立和刪除)


DBA權限:全部權利,修改系統表,建立和刪除表與索引、增加和恢復表資料,以及授予其他使用者資料庫權限等;

RESOURCE權限:允許對資料庫表中的資料進行存取,建立永久性表以及索引。


CONNECT權限:只允許對資料庫表中的資料進行存取,建立和刪除視圖與臨時表。

(2)表級權限(對表的建立、修改、檢索和更新等權限)


ALTER:更改權限

DELETE:刪除權限

INDEX:索引權限

INSERT:插入權限

SELECT [(cols)]:指定字段或所有字段上的查詢權限,不指明字段預設為所有字段。

UPDATE [(cols)] :指定字段或所有字段上的更新權限,不指明字段預設為所有字段。

ALL [PRIVILEGES]:以上所有表級權限



REVOKE {DBA|RESOURCE|CONNECT} FROM {PUBLIC|user-list}

收權命令。

PUBLIC|user-list:全部或指定的使用者。

三種權限居且僅居其一,事務處理過程中不要執行GRANT語法。

例:revoke resource from john;

REVOKE tab-privilege ON table-name FROM {PUBLIC|user-list}


收表級權限。

tab-privilege:表級權限。

table-name:資料表名稱稱。

PUBLIC|user-list:全部或指定的使用者。


[WITH GRANT OPTION]:表示被授權使用者有否權限進行二次授權。

使用者只能取消由其本人賦予其他使用者的表級存取權限;不能取消自己的權限,對SELECT和UPDATE作取消時,將取消所有表中字段的SELECT 和UPDATE權限。

例;revoke update on user from dick;



LOCK TABLE table-name IN {SHARE|EXCLUSIVE} MODE

記錄級加鎖和表級加鎖或檔案加鎖。

table-name:資料表名稱稱。

SHARE:允許讀表中資料,但不允許作任何修改

EXCLUSIVE:禁止其他任何形式瀏覽表

每次只能對表瑣定一次;事務處理過程中,BEGIN WORK後立即執行LOCK TABLE以取代記錄級加鎖,COMMIT WORK和ROLLBACK WORK語法取消所有對表的加鎖;若沒有事務處理,鎖將保持到使用者退出或執行UNLOCK為止。

例:lock table user in exclusive mode;



UNLOCK TABLE table-name

取消記錄級加鎖和表級加鎖或檔案加鎖。

table-name:資料表名稱稱。

例:unlock user;

SET LOCK MODE TO [NOT] WAIT

改變鎖定狀態。

TO [NOT]:等待解鎖,有可能被死鎖或不等待並提示錯誤訊息,表示此記錄被鎖,預設值。

瀏覽一個EXCLUSIVE狀態下的記錄,將返回一個錯誤。



START DATABSE db_name [WITH LOG IN 「pathname」]

啟動事務處理。

「pathname」:事務處理日誌檔案。

執行該語法前,需要先關閉當前資料庫。

例;clost database;

start databse customer with log in 「/usr/john/log/customer.log」;



BEGIN WORK

開始事務。例:begin work;



COMMIT WORK

提交(正常結束)事務。例:commit work;



ROLLBACK WORK

回滾(非正常結束)事務。例:rollback work;



SELECT

SELECT select_list FROM tab_name|view_name

WHERE condition

GROUP BY column_name


HAVING condition

ORDER BY column_list

INTO TEMP table_name

查詢語法。

select_list:選擇表或*

tab_name:資料表名稱稱

view_name:視圖名稱。

condition:查詢條件,可使用BETWEEN、IN、LIKE、IS NULL、LIKE、MATCHES、NOT、

AND、OR、=、!=或<>、>、 >= 、<=、<、ALL、ANY、SOME

column_name:分群組字段名稱

condition:群聚條件

column_list:排序字段列表,預設ASC,可指定DSC;排序時,NULL值小於非零值。


table_name:臨時資料表名稱稱

例:略

附(常用函數)

(1)集合函數:

count(*)、

sum(資料項/表達式)、avg(資料項/表達式)、max(資料項/表達式)、min(資料項/表達式)

count(distinct 資料項/表達式)、sum(distinct資料項/表達式)、avg(distinct資料項/表達式)

(2)代數函數和三角函數

HEX(資料項/表達式)、ROUND(資料項/表達式)、TRUNC(資料項/表達式)、


TAN(資料項/表達式)、ABS(資料項/表達式)、MOD(被除數,除數)

(3)統計函數

標準差,stdev()、方差,variance()、範圍,rang()

(4)時間函數

DAY(日期/時間表達式):返回數字型

MONTH(日期/時間表達式):返回整數

WEEKDAY(日期/時間表達式):0


DATE(非日期表達式):返回日期型

EXTEND(日期/時間表達式,[第一個至最後一個]):返回指定的整數

MDY(月,日,年):返回日期型

CURRENT:返回日期型

(5)時間函數

ROUND(),四捨五入。如:ROUND(10.95,position)position進行四捨五入的前一位置

TRUNC(),截取。如:TRUNC(10.95,0)position截取的位置

INFORMIX臨時表在下列情況下自動取消:

A.退出資料庫瀏覽工具(如DBACCESS)

B.SQL通話結束(DISCONNECT)

C.發出取消表語法

D.退出程式時

INSERT
INSERT INTO view_name|table_name [(column_list)] VALUES (value_list)

或 INSERT INTO view_name|table_name [(column_list)] select_statement

插入資料

view_name|table_name:視圖名或資料表名稱稱

column_list:資料項列表。

value_list:值列表

select_statement:查詢語法。

例:略



DELETE FROM view_name|table_name WHERE search-conditions
刪除語法。

view_name|table_name:視圖名或資料表名稱稱

search-conditions;刪除條件

例:略


UPDATE
UPDATE view_name|table_name SET column_1 = value_1ist WHERE search_conditions

或UPDATE view_name|table_name SET column_1|* = value_1ist WHERE search_conditions

更新資料語法。

view_name|table_name:資料表名稱稱或視圖資料表名稱稱

value_1ist:字段值

search_conditions:更新資料的條件


例:略


CHECK TABLE table-name
檢查索引語法。

語法使用者是表的擁有者或擁有DBA權限;不能對systable使用此語法。

例:略


REPAIR TABLE table-name
修復索引。

語法使用者是表的擁有者或擁有DBA權限;不能對systable使用此語法。

例:略


LOAD FROM 「file-name」 INSERT INTO table_name [(column_name[,…])]
將文本資料栽入表中。

例:load form 「aa.txt」 insert into user;


UNLOAD TO 「pathname」
將表中資料卸為文本。

例:unload to 「aa.txt」 select * from user;


INFO
系統訊息查詢。

INFO TABLES:得到當前資料庫上表的名字。

INFO columns FOR table_name:指定表上的字段訊息。

INFO INDEXES FOR table_name:指定表上的索引訊息。

INFO [ACCESS|PRIVILEGES] FOR table_name:指定表上的存取權限。

INFO STATUS FOR table_name:指定表的狀態訊息。


例: info tables;

沒有留言: