oracle-常用语句

1、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;删除表内数据保存表delete test_tb_grade where 1=1Q3.怎样创立视图？A. CREATE OR REPLACE VIEW ROYAL_MDVIEW ASSELECT T1.RM_STR_F

2、IELD AS F1, T2.RD_STR_FIELD AS F2 FROM ROYAL_MTABLE T1, ROYAL_DTABLE T2WHERE T1.RM_INT_FIELD = T2.RM_INT_FIELDQ4.怎样删除视图？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. ALTE

3、R 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;Q1

4、1.怎样给表添加一个外键？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_S

5、TR_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

6、;Q17.怎样创立索引？A. CREATE UNIQUE INDEX IDX_ROYAL_DTABLE ON ROYAL_DTABLE (RM_INT_FIELD);Q18.怎样删除索引？A. DROP INDEX IDX_ROYAL_DTABLE;Q19.怎样创立用户？USERSA. CREATE USER TESTUSER IDENTIFIED EXTERNALLY DEFAULT TABLESPACETEMPORARY TABLESPACE TEMP PROFILE DEFAULT;Q20.怎样删除用户？A. DROP USER TESTUSER CASCADE;21.怎样将对象权限ob

7、ject 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_DTAB

8、LE 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 T

9、ESTUSER;Q27.怎样创立序列？A. CREATE SEQUENCE RM_INT_FIELD_SEQMINvalue 1MAXvalue 999999999999999999999999999START WITH 1INCREMENT BY 1CACHE 10ORDER;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

10、 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 DB

11、A 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 A= 'Y'SELECT * FROM ROY

12、AL_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 FROMROYAL_M

13、TABLE 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支持所有常用数字函数，包括但不限于+、-、

14、*、/、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.怎样将字符串转换为

15、日期、时间格式？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. DEC

16、ODE 函数的格式为 DECODE(value, ifl, thenl, if2, then2,else)。假设表 ROYAL_DTABLE 中有如下数据：RD_INT_FIELD RD_STR_FIELD RM_INT_FIELD1 royal 12 bill 23 joy 1请观察如下SQL语句输出结果。SELECT DECODE(RD_STR_FIELD, 'royal', 'royaltest', 'bill', 'billgates', RD_STR_FIELD) AS DC FROMROYAL_DTABLE;DC ro

17、yaltestbillgatesjoyQ47.能给出一个group by、having和order by 用法的例子吗?A. SQL> SELECT * FROM ROYAL_MTABLE;RM_INT_FIELD RM_STR_FIELD1 Y2 N3 YSQL> SELECT * FROM ROYAL_DTABLE;RD_INT_FIELD RD_STR_FIELD RM_INT_FIELD1 royal 12 bill 23 joy 1SQL> SELECT RM_INT_FIELD, SUM(RD_INT_FIELD) FROM ROYAL_DTABLE GROUP

18、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 21 4Q48.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&