常用SQL語(yǔ)句詞典

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

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

【示例】

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

如果你創(chuàng)建一個(gè)新用戶(hù)使用PASSWORD EXPIRE項(xiàng)，在嘗試登陸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的使用權(quán)。
臨時(shí)表空間： temp_ts
在系統(tǒng)表空間SYSTEM有5M的使用權(quán)。
使用數(shù)據(jù)庫(kù)資源定義在配置文件engineer中。
一個(gè)過(guò)期密碼，在登陸ORACLE之前該用戶(hù)的密碼必須改變。

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

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

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

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

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

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

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

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

PASSWORD EXPIRE
指定用戶(hù)密碼的過(guò)期。這個(gè)設(shè)置迫使用戶(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.怎樣給字段設(shè)置默認(rèn)值？

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

Q16.怎樣移去字段的默認(rèn)值？

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.怎樣將對(duì)象權(quán)限（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ù)收回對(duì)象權(quán)限？

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

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

A. GRANT CONNECT TO TESTUSER WITH ADMIN OPTION;

GRANT DBA TO TESTUSER;

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

A. REVOKE DBA FROM TESTUSER;

Q25.怎樣將系統(tǒng)權(quán)限（system privileges）授予用戶(hù)？

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

Q26.怎樣從用戶(hù)收回系統(tǒng)權(quán)限？

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.怎樣將對(duì)象權(quán)限（object privileges）授予角色？

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

Q33.怎樣從角色收回對(duì)象權(quán)限？

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

Q34.怎樣將角色權(quán)限（role privileges）授予角色？

A. GRANT DBA TO TESTROLE;

Q35.怎樣從角色收回角色權(quán)限？

A. REVOKE DBA FROM TESTROLE;

Q36.怎樣將系統(tǒng)權(quán)限（system privileges）授予角色？

A. GRANT CREATE TABLE TO TESTROLE;

Q37.怎樣從角色收回系統(tǒng)權(quán)限？

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.請(qǐng)舉出一個(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常用字符串處理函數(shù)有哪些？

A. || 連接兩個(gè)字符串； LENGTH 字符串長(zhǎng)度； TRIM/LTRIM/RTRIM 截?cái)啻螅ㄓ遥┲付ㄗ址ò沾?LOWER/UPPER 將字符串轉(zhuǎn)換為小/大寫(xiě)，等等。

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

Q42.Oracle支持哪些數(shù)字處理函數(shù)？

A. Oracle支持所有常用數(shù)字函數(shù)，包括（但不限于）+、-、*、/、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.怎樣取得數(shù)據(jù)庫(kù)服務(wù)器當(dāng)前日期、時(shí)間？

A. SELECT SYSDATE FROM DUAL;

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

Q44.怎樣將字符串轉(zhuǎn)換為日期、時(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.常用日期函數(shù)有哪些？

A. NEXT_DAY LAST_DAY ADD_MONTHS MONTHS_BETWEEN等等。

例如：SELECT LAST_DAY(SYSDATE) FROM DUAL;

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

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

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

請(qǐng)觀察如下SQL語(yǔ)句輸出結(jié)果。
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有哪些常用數(shù)據(jù)字典？

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ù)”的手段。

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

表中有如下數(shù)據(jù)：

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

假如我們現(xiàn)在需要從NAME = ‘EEE‘的記錄開(kāi)始，向上查找所有有父子關(guān)系的記錄，可執(zhí)行如下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[從此長(zhǎng)大]