oracle常用sql语句以及案例,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;