Oracle SQL FAQ 荣耀 2002 整理这篇Oracle基本语法FAQ最初是为了回答一位客户朋友的提问(客观情况所限,我无法在线回答问题)。这些解答并非唯一答案,只是答案之一。更完备的答案请查阅Oracle正式文档。 目录 Q21.怎样将对象权限(object privileges)授予用户? Q23.怎样将角色权限(role privileges)授予用户? Q25.怎样将系统权限(system privileges)授予用户? Q32.怎样将对象权限(object privileges)授予角色? Q34.怎样将角色权限(role privileges)授予角色? Q36.怎样将系统权限(system privileges)授予角色? Q38.不等于条件有哪几种写法?(茴香豆问题:)) Q47.能给出一个group by、having和order by用法的例子吗? 内容 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) ) A. DROP TABLE ROYAL_DTABLE; 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 A. DROP VIEW ROYAL_MDVIEW; A. ALTER TABLE ROYAL_DTABLE ADD RM_INT_FIELD INTEGER; A. ALTER TABLE ROYAL_DTABLE DROP COLUMN RM_INT_FIELD; A. ALTER TABLE ROYAL_MTABLE MODIFY RM_STR_FIELD NOT NULL; A. ALTER TABLE ROYAL_MTABLE MODIFY RM_STR_FIELD NULL; A. ALTER TABLE ROYAL_MTABLE ADD CONSTRAINT PK_ROYAL_MTABLE PRIMARY KEY (RM_INT_FIELD); A. ALTER TABLE ROYAL_MTABLE DROP CONSTRAINT PK_ROYAL_MTABLE CASCADE; A. ALTER TABLE ROYAL_DTABLE ADD CONSTRAINT FK_ROYAL_DTABLE FOREIGN KEY (RM_INT_FIELD) REFERENCES ROYAL_MTABLE (RM_INT_FIELD) ON DELETE CASCADE; A. ALTER TABLE ROYAL_DTABLE DROP CONSTRAINT FK_ROYAL_DTABLE; A. ALTER TABLE ROYAL_MTABLE ADD CONSTRAINT CHK_RM_STR_FIELD CHECK (RM_STR_FIELD IN ('Y','N')); A. ALTER TABLE ROYAL_MTABLE DROP CONSTRAINT CHK_RM_STR_FIELD; A. ALTER TABLE ROYAL_DTABLE MODIFY RD_STR_FIELD DEFAULT 'ROYAL'; A. ALTER TABLE ROYAL_DTABLE MODIFY RD_STR_FIELD DEFAULT NULL; A. CREATE UNIQUE INDEX IDX_ROYAL_DTABLE ON ROYAL_DTABLE (RM_INT_FIELD); A. DROP INDEX IDX_ROYAL_DTABLE; A. CREATE USER TESTUSER IDENTIFIED EXTERNALLY DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT; 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; 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; A. REVOKE DBA FROM TESTUSER; Q25.怎样将系统权限(system privileges)授予用户? A. GRANT ALTER ANY TABLE TO TESTUSER WITH ADMIN OPTION; A. REVOKE ALTER ANY TABLE FROM TESTUSER; A. CREATE SEQUENCE RM_INT_FIELD_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 10 ORDER; A. DROP SEQUENCE RM_INT_FIELD_SEQ; A. SELECT RM_INT_FIELD_SEQ.NEXTVAL FROM DUAL; A. CREATE ROLE TESTROLE; A. DROP ROLE TESTROLE; Q32.怎样将对象权限(object privileges)授予角色? A. GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_MTABLE TO TESTROLE; A. REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_MTABLE FROM TESTROLE; Q34.怎样将角色权限(role privileges)授予角色? A. GRANT DBA TO TESTROLE; A. REVOKE DBA FROM TESTROLE; Q36.怎样将系统权限(system privileges)授予角色? A. GRANT CREATE TABLE TO TESTROLE; 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'; A. SELECT * FROM ROYAL_DTABLE WHERE RD_STR_FIELD LIKE '%Y%'; SELECT * FROM ROYAL_DTABLE WHERE RD_STR_FIELD LIKE '_Y%'; 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')); A. || 连接两个字符串; LENGTH 字符串长度; TRIM/LTRIM/RTRIM 截断串左(右)指定字符串(包括空串); LOWER/UPPER 将字符串转换为小/大写,等等。 例如:SELECT RM_INT_FIELD||'--'||RM_STR_FIELD||'YES' FROM ROYAL_MTABLE; 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; A. SELECT SYSDATE FROM DUAL; SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL; 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; A. NEXT_DAY LAST_DAY ADD_MONTHS MONTHS_BETWEEN等等。 例如:SELECT LAST_DAY(SYSDATE) FROM DUAL; A. DECODE函数的格式为DECODE(value, if1, then1, if2, then2...,else)。假设表ROYAL_DTABLE中有如下数据: RD_INT_FIELD RD_STR_FIELD RM_INT_FIELD 请观察如下SQL语句输出结果。 Q47.能给出一个group by、having和order by用法的例子吗? A. SQL> SELECT * FROM ROYAL_MTABLE; 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'; 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')); A. connect by子句提供了遍历“树”的手段。 假设有这样一个表:CREATE TABLE ROYAL_TREETABLE (ID INTEGER, PARENT_ID INTEGER, NAME VARCHAR2(32)); 表中有如下数据: 假如我们现在需要从NAME = 'EEE'的记录开始,向上查找所有有父子关系的记录,可执行如下SQL语句: SELECT * FROM ROYAL_TREETABLE START WITH NAME = 'EEE' CONNECT BY ID = PRIOR PARENT_ID; -完- |
|
![]() |
![](https://img2024.cnblogs.com/blog/35695/202407/35695-20240713070336838-1837943664.jpg)