Oracle常用SQL语句

Oracle使用过程中经常会用到的SQL语句。

表
制约
视图
顺序
索引
用户

--------------------------------------------------------------------------------

表
创建表。

CREATE TABLE [schema.]t_employees (
employee_id NUMBER(2), -- 长度=2的整数
hire_date DATE DEFAULT SYSDATE, -- default的例子
...
) AS 子查询; -- 利用子查询建立表确认表。

desc t_employees; -- 确认表结构
SELECT table_name FROM user_tables; -- 确认用户拥有的表
SELECT DISTINCT object_type FROM user_objects; -- 确认用户拥有的对象的种类
SELECT * FROM user_catalog; -- 确认用户拥有的对象修改表

ALTER TABLE t_employees ADD (colname ..., colname...,); -- 增加列
ALTER TABLE t_employees MODIFY (colname DATATYPE [DEFAULT ...], colname ...); -- 修改列定义
ALTER TABLE DROP { COLUMN 列名 | (列名[,列名...])}; -- 删除列，列名用括号或者COLUMN关键字其他

DROP TABLE dept; -- 删除表
RENAME dept TO detail_dept; -- 修改名称。
TRUNCATE TABLE detail_dept; -- 清空表。
COMMENT ON TABLE employee IS 'Employee Information'; -- 添加注释。注释确认可以通过 ALL_COL_COMMENTS、USER_COL_COMMENTS、ALL_TAB_COMMENTS、USER_TAB_COMMENTS 视图的 COMMENTS 列来进行。

制约
定义制约

CREATE TABLE employee (
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(20) CONSTRAINT last_name_nn NOT NULL,
email VARCHAR2(25),
dept_id NUMBER(6),
salary NUMBER(8,2),
...
job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id),
CONSTRAINT emp_email_uk UNIQUE(email),
CONSTRAINT emp_deptid_fk FOREIGN KEY (department_id) REFERENCES departments(department_id),
CONSTRAINT emp_salary_min CHECK (salary>0));确认制约。

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name='EMPLOYEES';

SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name='EMPLOYEES';视图
选出前n条记录。

SELECT * FROM ( select ....) WHERE rownum<=n;顺序
创建顺序。

CREATE SEQUENCE dept_deptdi_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;确认顺序。

SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences; -- last_number为上次取得的顺序值使用方法

INSERT INTO departments (department_id, department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);变更顺序。

ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;删除顺序。

DROP SEQUENCE dept_deptid_seq;索引
创建索引。

CREATE INDEX emp_last_name_idx ON employees(last_name);确认索引内容。

SELECT ic.index_name, ic.column_name, ic.column_position col_pos, ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name=ix.index_name AND ic.table_name='YOUR_TABLE_NAME'用户
创建用户。

CREATE USER scott IDENTIFIED BY tiger;赋予权限。

GRANT create session, create table, create sequence, create view TO scott; -- 系统权限
GRANT select ON employees TO sue, rich WITH GRANT OPTION;
-- 对象权限, with grant option表明该用户可以将被赋予的权限再赋予别人
GRANT update (department_name, location_id) ON departments TO PUBLIC;取消权限。

REVOKE select, insert ON departments FROM scott;角色。

CREATE ROLE manager;
GRANT create table, create view TO manager;
GRANT manager TO dehaan, kochhar;修改密码。

ALTER USER scott IDENTIFIED BY lion;