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;