Oracle SQL FAQ

　　Oracle SQL FAQ
　　
　　荣耀 2002
　　
　　整理这篇Oracle基本语法FAQ最初是为了回答一位客户朋友的提问（客观情况所限，我无法在线回答问题）。这些解答并非唯一答案，只是答案之一。更完备的答案请查阅Oracle正式文档。
　　
　　目录
　　
　　Q1.怎样创建表？
　　
　　Q2.怎样删除表？
　　
　　Q3.怎样创建视图？
　　
　　Q4.怎样删除视图？
　　
　　Q5.怎样给表添加字段？
　　
　　Q6.怎样删除表中某个字段？
　　
　　Q7.怎样给某个字段添加约束？
　　
　　Q8.怎样去除某个字段上的约束？
　　
　　Q9.怎样给表加上主键？
　　
　　Q10.怎样删除表的主键？
　　
　　Q11.怎样给表添加一个外键？
　　
　　Q12.怎样删除表的一个外键？
　　
　　Q13.怎样给字段加上CHECK？
　　
　　Q14.怎样去掉字段上的CHECK？
　　
　　Q15.怎样给字段设置默认值？
　　
　　Q16.怎样移去字段的默认值？
　　
　　Q17.怎样创建索引？
　　
　　Q18.怎样删除索引？
　　
　　Q19.怎样创建用户？
　　
　　Q20.怎样删除用户？
　　
　　Q21.怎样将对象权限（object privileges）授予用户？
　　
　　Q22.怎样从用户收回对象权限？
　　
　　Q23.怎样将角色权限（role privileges）授予用户？
　　
　　Q24.怎样从用户收回角色权限？
　　
　　Q25.怎样将系统权限（system privileges）授予用户？
　　
　　Q26.怎样从用户收回系统权限？
　　
　　Q27.怎样创建序列？
　　
　　Q28.怎样删除序列？
　　
　　Q29.怎样获取序列值？
　　
　　Q30.怎样创建角色？
　　
　　Q31.怎样删除角色？
　　
　　Q32.怎样将对象权限（object privileges）授予角色？
　　
　　Q33.怎样从角色收回对象权限？
　　
　　Q34.怎样将角色权限（role privileges）授予角色？
　　
　　Q35.怎样从角色收回角色权限？
　　
　　Q36.怎样将系统权限（system privileges）授予角色？
　　
　　Q37.怎样从角色收回系统权限？
　　
　　Q38.不等于条件有哪几种写法？（茴香豆问题:)）
　　
　　Q39.like子句的用法？
　　
　　Q40.请举出一个where子查询简单例子？
　　
　　Q41.Oracle常用字符串处理函数有哪些？
　　
　　Q42.Oracle支持哪些数字处理函数？
　　
　　Q43.怎样取得数据库服务器当前日期、时间？
　　
　　Q44.怎样将字符串转换为日期、时间格式？
　　
　　Q45.常用日期函数有哪些？
　　
　　Q46.能给出一个DECODE函数用法的例子吗？
　　
　　Q47.能给出一个group by、having和order by用法的例子吗？
　　
　　Q48.Oracle有哪些常用数据字典？
　　
　　Q49.怎样将日期、时间插入date型字段中？
　　
　　Q50.能介绍一下connect by的用法吗？
　　
　　内容
　　
　　Q1.怎样创建表？
　　
　　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.怎样创建视图？
　　
　　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.怎样删除表中某个字段？
　　
　　A. ALTER TABLE ROYAL_DTABLE DROP COLUMN RM_INT_FIELD;
　　
　　Q7.怎样给某个字段添加约束？
　　
　　A. ALTER TABLE ROYAL_MTABLE MODIFY RM_STR_FIELD NOT NULL;
　　
　　Q8.怎样去除某个字段上的约束？
　　
　　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.怎样给表添加一个外键？
　　
　　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.怎样删除表的一个外键？
　　
　　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.怎样创建索引？
　　
　　A. CREATE UNIQUE INDEX IDX_ROYAL_DTABLE ON ROYAL_DTABLE (RM_INT_FIELD);
　　
　　Q18.怎样删除索引？
　　
　　A. DROP INDEX IDX_ROYAL_DTABLE;
　　
　　Q19.怎样创建用户？
　　
　　A. CREATE USER TESTUSER IDENTIFIED EXTERNALLY DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;
　　
　　Q20.怎样删除用户？
　　
　　A. DROP USER TESTUSER CASCADE;
　　
　　Q21.怎样将对象权限（object privileges）授予用户？
　　
　　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.怎样从用户收回对象权限？
　　
　　A. REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_DTABLE FROM TESTUSER;
　　
　　Q23.怎样将角色权限（role privileges）授予用户？
　　
　　A. GRANT CONNECT TO TESTUSER WITH ADMIN OPTION;
　　
　　GRANT DBA TO TESTUSER;
　　
　　Q24.怎样从用户收回角色权限？
　　
　　A. REVOKE DBA FROM TESTUSER;
　　
　　Q25.怎样将系统权限（system privileges）授予用户？
　　
　　A. GRANT ALTER ANY TABLE TO TESTUSER WITH ADMIN OPTION;
　　
　　Q26.怎样从用户收回系统权限？
　　
　　A. REVOKE ALTER ANY TABLE FROM TESTUSER;
　　
　　Q27.怎样创建序列？
　　
　　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.怎样创建角色？
　　
　　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.不等于条件有哪几种写法？（茴香豆问题:)）
　　
　　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.请举出一个where子查询简单例子？
　　
　　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. || 连接两个字符串； LENGTH 字符串长度； TRIM/LTRIM/RTRIM 截断串左（右）指定字符串（包括空串）； LOWER/UPPER 将字符串转换为小/大写，等等。
　　
　　例如：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.怎样取得数据库服务器当前日期、时间？
　　
　　A. SELECT SYSDATE FROM DUAL;
　　
　　SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
　　
　　Q44.怎样将字符串转换为日期、时间格式？
　　
　　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.能给出一个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
　　
　　请观察如下SQL语句输出结果。
　　SELECT DECODE(RD_STR_FIELD, 'royal', 'royaltest', 'bill', 'billgates', RD_STR_FIELD) AS DC FROM ROYAL_DTABLE;
　　DC
　　---------
　　royaltest
　　billgates
　　joy
　　
　　Q47.能给出一个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.怎样将日期、时间插入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子句提供了遍历“树”的手段。
　　
　　假设有这样一个表：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'的记录开始，向上查找所有有父子关系的记录，可执行如下SQL语句：
　　
　　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
　　
　　自从 Oracle 9i 开始，就可以通过SYS_CONNECT_BY_PATH 函数实现将从父节点到当前
　　行内容以“path”或者层次元素列表的形式显示出来。
　　
　　取得一个表的所有字段名，用逗号分割。
　　
　　select max(substr(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','),2)) col from (
　　select COLUMN_NAME,column_id from user_tab_columns where table_name='&表名')
　　start with column_id=1
　　connect by column_id=rownum;