常用SQL語(yǔ)句詞典

作者：作者不詳 來(lái)源：來(lái)源網(wǎng)絡(luò ) 發(fā)布日期：2005-11-1 15:00:24 編輯：阿志

Q0.創(chuàng )建用戶(hù)
【前提】
你必須有CREATE USER系統權限。當你使用CREATE USER語(yǔ)句創(chuàng )建一個(gè)新用戶(hù)后，該用戶(hù)的權
限是空的，要登錄到ORACLE該用戶(hù)必須有CREATE SESSION 系統權限。因此在創(chuàng )建用戶(hù)之后
，你至少應該授予用戶(hù)CREATE SESSION 權限。

【示例】

創(chuàng )建一個(gè)用戶(hù)的例子

如果你創(chuàng )建一個(gè)新用戶(hù)使用PASSWORD EXPIRE項，在嘗試登陸ORACLE之前該用戶(hù)的密碼必須
改變。你可以創(chuàng )建該用戶(hù)使用以下語(yǔ)句：

CREATE USER sidney
IDENTIFIED BY welcome
DEFAULT TABLESPACE cases_ts
QUOTA 10M ON cases_ts
TEMPORARY TABLESPACE temp_ts
QUOTA 5M ON system
PROFILE engineer
PASSWORD EXPIRE;

該用戶(hù)有以下特征：
密碼：welcome
缺省表空間： cases_ts,有10M的使用權。
臨時(shí)表空間： temp_ts
在系統表空間SYSTEM有5M的使用權。
使用數據庫資源定義在配置文件engineer中。
一個(gè)過(guò)期密碼，在登陸ORACLE之前該用戶(hù)的密碼必須改變。

【關(guān)鍵字和參數】
user
標識要被創(chuàng )建的用戶(hù)名稱(chēng)。

IDENTIFIED
IDENTIFIED 項讓你指出ORACLE如何鑒別該用戶(hù)。

BY password
BY password 項讓你創(chuàng )建一個(gè)本地用戶(hù)并指出該用戶(hù)必須使用指定密碼登錄ORACLE。
密碼只包含單字節字符。

DEFAULT TABLESPACE
指出所創(chuàng )用戶(hù)的缺省表空間。如果你忽略該項，缺省值是SYSTEM表空間。

TEMPORARY TABLESPACE
指出所創(chuàng )用戶(hù)的臨時(shí)表空間。如果你忽略該項，缺省值是SYSTEM表空間。

QUOTA
使用QUOTA項指定允許用戶(hù)使用的表空間大小，以K或M為單位的整數值。
一個(gè)用戶(hù)可以擁有多個(gè)表空間的多個(gè)QUOTA值。

UNLIMITED
允許用戶(hù)無(wú)限制的使用表空間。

PROFILE
指你想給用戶(hù)分配哪個(gè)配置文件。配置文件限制用戶(hù)使用數據庫的資源量。如果忽略該
項，ORACLE為用戶(hù)指定缺省配置文件。

PASSWORD EXPIRE
指定用戶(hù)密碼的過(guò)期。這個(gè)設置迫使用戶(hù)（或DBA）在登錄到ORACLE之前必須修改密碼
。

Q1.怎樣創(chuàng )建表？

A. CREATE TABLE ROYAL_MTABLE

(

RM_INT_FIELD INTEGER,

RM_STR_FIELD VARCHAR2(64)

)

CREATE TABLE ROYAL_DTABLE

(

RD_INT_FIELD INTEGER,

RD_STR_FIELD VARCHAR2(32)

)

Q2.怎樣刪除表？

A. DROP TABLE ROYAL_DTABLE;

Q3.怎樣創(chuàng )建視圖？

A. CREATE OR REPLACE VIEW ROYAL_MDVIEW AS

SELECT T1.RM_STR_FIELD AS F1, T2.RD_STR_FIELD AS F2 FROM ROYAL_MTABLE T1, ROYAL_DTABLE T2

WHERE T1.RM_INT_FIELD = T2.RM_INT_FIELD

Q4.怎樣刪除視圖？

A. DROP VIEW ROYAL_MDVIEW;

Q5.怎樣給表添加字段？

A. ALTER TABLE ROYAL_DTABLE ADD RM_INT_FIELD INTEGER;

Q6.怎樣刪除表中某個(gè)字段？

A. ALTER TABLE ROYAL_DTABLE DROP COLUMN RM_INT_FIELD;

Q7.怎樣給某個(gè)字段添加約束？

A. ALTER TABLE ROYAL_MTABLE MODIFY RM_STR_FIELD NOT NULL;

Q8.怎樣去除某個(gè)字段上的約束？

A. ALTER TABLE ROYAL_MTABLE MODIFY RM_STR_FIELD NULL;

Q9.怎樣給表加上主鍵？

A. ALTER TABLE ROYAL_MTABLE ADD CONSTRAINT PK_ROYAL_MTABLE PRIMARY KEY (RM_INT_FIELD);

Q10.怎樣刪除表的主鍵？

A. ALTER TABLE ROYAL_MTABLE DROP CONSTRAINT PK_ROYAL_MTABLE CASCADE;

Q11.怎樣給表添加一個(gè)外鍵？

A. ALTER TABLE ROYAL_DTABLE ADD CONSTRAINT FK_ROYAL_DTABLE FOREIGN KEY (RM_INT_FIELD) REFERENCES ROYAL_MTABLE (RM_INT_FIELD) ON DELETE CASCADE;

Q12.怎樣刪除表的一個(gè)外鍵？

A. ALTER TABLE ROYAL_DTABLE DROP CONSTRAINT FK_ROYAL_DTABLE;

Q13.怎樣給字段加上CHECK？

A. ALTER TABLE ROYAL_MTABLE ADD CONSTRAINT CHK_RM_STR_FIELD CHECK (RM_STR_FIELD IN (‘Y‘,‘N‘));

Q14.怎樣去掉字段上的CHECK？

A. ALTER TABLE ROYAL_MTABLE DROP CONSTRAINT CHK_RM_STR_FIELD;

Q15.怎樣給字段設置默認值？

A. ALTER TABLE ROYAL_DTABLE MODIFY RD_STR_FIELD DEFAULT ‘ROYAL‘;

Q16.怎樣移去字段的默認值？

A. ALTER TABLE ROYAL_DTABLE MODIFY RD_STR_FIELD DEFAULT NULL;

Q17.怎樣創(chuàng )建索引？

A. CREATE UNIQUE INDEX IDX_ROYAL_DTABLE ON ROYAL_DTABLE (RM_INT_FIELD);

Q18.怎樣刪除索引？

A. DROP INDEX IDX_ROYAL_DTABLE;

Q19.怎樣創(chuàng )建用戶(hù)？

A. CREATE USER TESTUSER IDENTIFIED EXTERNALLY DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;

Q20.怎樣刪除用戶(hù)？

A. DROP USER TESTUSER CASCADE;

Q21.怎樣將對象權限（object privileges）授予用戶(hù)？

A. GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_MTABLE TO TESTUSER;

GRANT INSERT, UPDATE, DELETE ON ROYAL_DTABLE TO TESTUSER;

GRANT SELECT, ALTER ON ROYAL_DTABLE TO TESTUSER WITH GRANT OPTION;

Q22.怎樣從用戶(hù)收回對象權限？

A. REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_DTABLE FROM TESTUSER;

Q23.怎樣將角色權限（role privileges）授予用戶(hù)？

A. GRANT CONNECT TO TESTUSER WITH ADMIN OPTION;

GRANT DBA TO TESTUSER;

Q24.怎樣從用戶(hù)收回角色權限？

A. REVOKE DBA FROM TESTUSER;

Q25.怎樣將系統權限（system privileges）授予用戶(hù)？

A. GRANT ALTER ANY TABLE TO TESTUSER WITH ADMIN OPTION;

Q26.怎樣從用戶(hù)收回系統權限？

A. REVOKE ALTER ANY TABLE FROM TESTUSER;

Q27.怎樣創(chuàng )建序列？

A. CREATE SEQUENCE RM_INT_FIELD_SEQ

MINVALUE 1

MAXVALUE 999999999999999999999999999

START WITH 1

INCREMENT BY 1

CACHE 10

ORDER;

Q28.怎樣刪除序列？

A. DROP SEQUENCE RM_INT_FIELD_SEQ;

Q29.怎樣獲取序列值？

A. SELECT RM_INT_FIELD_SEQ.NEXTVAL FROM DUAL;

Q30.怎樣創(chuàng )建角色？

A. CREATE ROLE TESTROLE;

Q31.怎樣刪除角色？

A. DROP ROLE TESTROLE;

Q32.怎樣將對象權限（object privileges）授予角色？

A. GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_MTABLE TO TESTROLE;

Q33.怎樣從角色收回對象權限？

A. REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_MTABLE FROM TESTROLE;

Q34.怎樣將角色權限（role privileges）授予角色？

A. GRANT DBA TO TESTROLE;

Q35.怎樣從角色收回角色權限？

A. REVOKE DBA FROM TESTROLE;

Q36.怎樣將系統權限（system privileges）授予角色？

A. GRANT CREATE TABLE TO TESTROLE;

Q37.怎樣從角色收回系統權限？

A. REVOKE CREATE TABLE FROM TESTROLE;

Q38.不等于條件有哪幾種寫(xiě)法？（茴香豆問(wèn)題:)）

A. SELECT * FROM ROYAL_MTABLE WHERE RM_STR_FIELD != ‘Y‘;

SELECT * FROM ROYAL_MTABLE WHERE RM_STR_FIELD ^= ‘Y‘;

SELECT * FROM ROYAL_MTABLE WHERE RM_STR_FIELD <> ‘Y‘;

Q39.like子句的用法？

A. SELECT * FROM ROYAL_DTABLE WHERE RD_STR_FIELD LIKE ‘%Y%‘;

SELECT * FROM ROYAL_DTABLE WHERE RD_STR_FIELD LIKE ‘_Y%‘;

Q40.請舉出一個(gè)where子查詢(xún)簡(jiǎn)單例子？

A. SELECT * FROM ROYAL_DTABLE WHERE RM_INT_FIELD IN (SELECT RM_INT_FIELD FROM ROYAL_MTABLE WHERE RM_STR_FIELD NOT IN (‘Y‘,‘B‘));

Q41.Oracle常用字符串處理函數有哪些？

A. || 連接兩個(gè)字符串； LENGTH 字符串長(cháng)度； TRIM/LTRIM/RTRIM 截斷串左（右）指定字符串（包括空串）； LOWER/UPPER 將字符串轉換為小/大寫(xiě)，等等。

例如：SELECT RM_INT_FIELD||‘--‘||RM_STR_FIELD||‘YES‘ FROM ROYAL_MTABLE;

Q42.Oracle支持哪些數字處理函數？

A. Oracle支持所有常用數字函數，包括（但不限于）+、-、*、/、ABS、COS、EXP、LN、LOG、MOD、POWER、ROUND、SIN、 SINH、SQRT、TAN、TRUNC、AVG、COUNT、MAX、MIN、SUM、GREATEST、LEAST等等。

例如：

SELECT GREATEST(3, 4, 5)*4 FROM DUAL;

SELECT POWER(2,3) FROM DUAL;

Q43.怎樣取得數據庫服務(wù)器當前日期、時(shí)間？

A. SELECT SYSDATE FROM DUAL;

SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH:MI:SS‘) FROM DUAL;

Q44.怎樣將字符串轉換為日期、時(shí)間格式？

A. SELECT TO_DATE(‘2002-11-27‘, ‘YYYY-MM-DD‘) FROM DUAL;

SELECT TO_DATE(‘2002-11-27 09:28:55‘, ‘YYYY-MM-DD HH:MI:SS‘) FROM DUAL;

Q45.常用日期函數有哪些？

A. NEXT_DAY LAST_DAY ADD_MONTHS MONTHS_BETWEEN等等。

例如：SELECT LAST_DAY(SYSDATE) FROM DUAL;

Q46.能給出一個(gè)DECODE函數用法的例子嗎？

A. DECODE函數的格式為DECODE(value, if1, then1, if2, then2...,else)。假設表ROYAL_DTABLE中有如下數據：

RD_INT_FIELD RD_STR_FIELD RM_INT_FIELD
--------------------------------------
1 royal 1
2 bill 2
3 joy 1

請觀(guān)察如下SQL語(yǔ)句輸出結果。
SELECT DECODE(RD_STR_FIELD, ‘royal‘, ‘royaltest‘, ‘bill‘, ‘billgates‘, RD_STR_FIELD) AS DC FROM ROYAL_DTABLE;
DC
---------
royaltest
billgates
joy

Q47.能給出一個(gè)group by、having和order by用法的例子嗎？

A. SQL> SELECT * FROM ROYAL_MTABLE;

RM_INT_FIELD RM_STR_FIELD
-------------------------
1 Y
2 N
3 Y

SQL> SELECT * FROM ROYAL_DTABLE;

RD_INT_FIELD RD_STR_FIELD RM_INT_FIELD
--------------------------------------
1 royal 1
2 bill 2
3 joy 1

SQL> SELECT RM_INT_FIELD, SUM(RD_INT_FIELD) FROM ROYAL_DTABLE GROUP BY RM_INT_FIELD HAVING SUM(RD_INT_FIELD) >= 2 ORDER BY SUM(RD_INT_FIELD) ASC;

RM_INT_FIELD SUM(RD_INT_FIELD)
------------------------------
2 2
1 4

Q48.Oracle有哪些常用數據字典？

A. USER_TABLES(TABS)、USER_TAB_COLUMNS(COLS)、USER_VIEWS、USER_SEQUENCES(SEQ)、 USER_CONSTRAINTS、USER_CONS_COLUMNS、USER_TAB_COMMENTS、USER_COL_COMMENTS、 USER_INDEXES(IND)、USER_IND_COLUMNS、USER_USERS、DBA_USERS、ALL_USERS、 USER_TABLESPACES等等。

例如：SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME = ‘FK_ROYAL_DTABLE‘;

Q49.怎樣將日期、時(shí)間插入date型字段中？

A. INSERT INTO ROYAL_MTABLE (RM_INT_FIELD, RM_STR_FIELD, RM_DATE_FIELD) VALUES (9, ‘Y‘, TO_DATE(‘2002-05-23‘, ‘YYYY-MM-DD‘));

INSERT INTO ROYAL_MTABLE (RM_INT_FIELD, RM_STR_FIELD, RM_DATE_FIELD) VALUES (10, ‘Y‘, TO_DATE(‘2002-10-10 8:23:33‘, ‘YYYY-MM-DD HH:MI:SS‘));

Q50.能介紹一下connect by的用法嗎？

A. connect by子句提供了遍歷“樹(shù)”的手段。

假設有這樣一個(gè)表：CREATE TABLE ROYAL_TREETABLE (ID INTEGER, PARENT_ID INTEGER, NAME VARCHAR2(32));

表中有如下數據：

ID PARENT_ID NAME
-----------------
2 1 AAA
3 1 BBB
4 2 CCC
5 2 DDD
6 4 EEE
8 7 GGG

假如我們現在需要從NAME = ‘EEE‘的記錄開(kāi)始，向上查找所有有父子關(guān)系的記錄，可執行如下SQL語(yǔ)句：

SELECT * FROM ROYAL_TREETABLE START WITH NAME = ‘EEE‘ CONNECT BY ID = PRIOR PARENT_ID;

ID PARENT_ID NAME
-----------------
6 4 EEE
4 2 CCC
2 1 AAA[從此長(cháng)大]