Oracle必备语句

Oracle_11g 常用SQL语句（高级工程师必备）

Oracle_11g 常用SQL语句（高级工程师必备）

   -- 退出 SQLPLUS
exit;
-- 修改 system(sys) 账号密码
SQLPLUS /NOLOG
CONN /AS SYSDBA
ALTER USER SYSTEM IDENTIFIED BY tarring;
-- 清除 SQLPLUS 屏幕
CLEAR SCREEN;
CL SCR;
-- 查看数据文件位置
SELECT NAME FROM v$datafile;
-- 查看控制文件位置
SELECT NAME FROM v$controlfile;
-- 查看日志文件位置
SELECT MEMBER FROM v$logfile;
-- 建立表空间
CREATE TABLESPACE ts01 
DATAFILE ‘D:\DataBase\Oracle11g\oradata\orcl\test_db01.dbf‘SIZE 100M
AUTOEXTEND ON NEXT 100M MAXSIZE 1024M
DEFAULT STORAGE(INITIAL 10m NEXT 1M)
PERMANENT
ONLINE
LOGGING;
-- 修改表空间
ALTER TABLESPACE ts01
NOLOGGING;
-- 表空间增加数据文件
ALTER TABLESPACE ts01 
ADD DATAFILE ‘D:\DataBase\Oracle11g\oradata\orcl\test_db02.dbf‘SIZE 100M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
-- 删除表空间
DROP TABLESPACE ts01;
-- 删除表空间同时删除数据文件
DROP TABLESPACE ts01 INCLUDING CONTENTS AND DATAFILES;
-- 表空间中建表
CREATE TABLE student(
student_id VARCHAR2(10),
student_name VARCHAR2(20)
)TABLESPACE ts01;
-- 查看表所属表空间
SELECT TABLE_NAME, TABLESPACE_NAME FROM tabs WHERE TABLE_NAME = ‘STUDENT‘;
-- 查看表结构
DESCRIBE student;
DESC student;
-- 增加表注释
COMMENT ON TABLE student IS ‘ 学生信息表 ‘;
-- 查看表注释
SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME = ‘STUDENT‘;
SELECT * FROM ALL_TAB_COMMENTS WHERE TABLE_NAME = ‘STUDENT‘;
-- 表字段增加注释
COMMENT ON COLUMN STUDENT.STUDENT_ID IS ‘ 学生编号 ‘;
-- 查看表字段注释
SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = ‘STUDENT‘;
SELECT * FROM ALL_COL_COMMENTS WHERE TABLE_NAME = ‘STUDENT‘;
-- 查看用户所有表
SELECT * FROM User_Tables;
-- 查看用户拥有的所有对象
SELECT * FROM User_Objects;
-- 查看用户拥有的 表 试图 序列
SELECT * FROM User_Catalog;
-- 表字段修改
ALTER TABLE student MODIFY(student_id CHAR(15));
-- 表字段增加
ALTER TABLE STUDENT ADD(AGE NUMBER(2));
-- 删除表字段
ALTER TABLE STUDENT DROP COLUMN student_name;
-- 修改表名称
RENAME STUDENT TO STU;
-- 删除表
DROP TABLE STUDENT;
CREATE TABLE student(
s_id Varchar2(10),
s_name varchar2(20),
s_age Number(3),
s_birthday DATE
)TABLESPACE ts01;
-- 增加一条记录
INSERT INTO
student (s_id, s_name, s_age, s_birthday) 
VALUES (‘S000000001‘, ‘Tarring01‘, 10, to_date(‘1982-10-06‘,‘yyyy-mm-dd‘));
INSERT INTO
student (s_id, s_name, s_age, s_birthday) 
VALUES (‘S000000002‘, ‘Tarring02‘, 10, Sysdate);
-- 使用替代变量时，输入字符串字段时一样要写上引号
INSERT INTO
student (s_id, s_name, s_age, s_birthday) 
VALUES (&s_id, &s_name, 10, Sysdate);
-- 修改记录
UPDATE student SET s_name = ‘ 陶川 ‘, s_age= 20 WHERE s_id = ‘S000000002‘;
-- 删除记录
DELETE FROM student WHERE s_id = ‘S000000002‘;
-- 截断表
TRUNCATE TABLE student;
-- 事务处理
COMMIT; -- 提交事务
INSERT INTO student (s_id, s_name) VALUES (‘S001‘, ‘tarring1‘);
ROLLBACK; -- 回滚，回滚到上一次提交过后的点
-- 带恢复点的事务
COMMIT;
INSERT INTO student (s_id, s_name) VALUES (‘S001‘, ‘tarring1‘);
SAVEPOINT firstdate;
INSERT INTO student (s_id, s_name) VALUES (‘S002‘, ‘tarring2‘);
SAVEPOINT seconddate;
DELETE FROM student;
ROLLBACK TO firstdate;
SELECT * FROM student;
--  约束条件             说明
--------------------------------------------------------------------
-- UNIQUE              指定字段的值，必须是唯一的
--  PRIMARY KEY         主键，会为指定的字段作索引，并且也是唯一的值
--  NOTNULL            不可以是空值【 ‘‘ NULL 】或 0 （零）
-- CHECK               检查，必须符合指定的条件
--  FOREIGN KEY         外键，用来创建一个参考表之间的关系
-- 建表同时建立唯一约束
CREATE TABLE student(
s_id Varchar2(10),
s_name varchar2(20),
s_age Number(3),
s_birthday DATE,
CONSTRAINT s_name_uk UNIQUE(s_name)
)TABLESPACE ts01;
-- 查看唯一约束
SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHEREtable_name = ‘STUDENT‘;
-- 作业：数据字典【分类 常用】
-- 建表同时建立主键
CREATE TABLE student(
s_id Varchar2(10),
s_name varchar2(20),
s_age Number(3),
s_birthday DATE,
CONSTRAINT s_id_pk PRIMARY KEY (s_id)
)TABLESPACE ts01;
-- 查看主键约束
SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHEREtable_name = ‘STUDENT‘;
-- 建表同时建立非空字段
CREATE TABLE student(
s_id Varchar2(10),
s_name varchar2(20) NOT NULL,
s_age Number(3),
s_birthday DATE,
CONSTRAINT s_id_pk PRIMARY KEY (s_id)
)TABLESPACE ts01;
INSERT INTO student (s_id, s_name) VALUES (‘S001‘, NULL); -- 插入一个 null
-- 查看非空约束
SELECT table_name, constraint_name, constraint_type, search_condition FROMUser_Constraints WHERE table_name = ‘STUDENT‘;
-- 建表同时建立检查
CREATE TABLE student(
s_id Varchar2(10),
s_name varchar2(20),
s_age Number(3),
s_birthday DATE,
CONSTRAINT s_age_ck CHECK (s_age BETWEEN 1 AND 100) -- 端点值可以使用
)TABLESPACE ts01;
-- 查看检查约束
SELECT table_name, constraint_name, constraint_type, search_condition FROMUser_Constraints WHERE table_name = ‘STUDENT‘;

-- 外键的使用
CREATE TABLE team(
t_id Varchar2(10),
t_name Varchar2(20),
CONSTRAINT t_id_pk PRIMARY KEY (t_id)
)TABLESPACE ts01;
CREATE TABLE student(
s_id Varchar2(10),
team_id VARCHAR2(10),
s_name varchar2(20),
CONSTRAINT s_id_pk PRIMARY KEY (s_id),
CONSTRAINT s_team_id_fk FOREIGN KEY (team_id) REFERENCES team(t_id)
)TABLESPACE ts01;
-- 查看表的外键约束
SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHEREtable_name = ‘STUDENT‘;
drop table team;  -- 被引用表是不能删除的
insert into team (t_id, t_name) values (‘t001‘, ‘lansene‘);
insert into student(s_id, s_name, team_id) values (‘s001‘,‘tarring‘, ‘t001‘);
delete from team; -- 被引用的记录是不能删除的
-- 关闭一个约束
ALTER TABLE student DISABLE CONSTRAINT s_team_id_fk;
-- 启用一个约束
ALTER TABLE student ENABLE CONSTRAINT s_team_id_fk;
-- 删除一个约束
ALTER TABLE student DROP CONSTRAINT s_team_id_fk;
-- 已创建的表增加一个约束
ALTER TABLE student ADD CONSTRAINT s_team_id_fk FOREIGN KEY (team_id)REFERENCES team(t_id);
/********************************************************************************************|
|             SQL 语句 5 大类型                    |  命令      | 说明                           |
|********************************************************************************************|
| Data Retrieval 数据检索                        | select    | 查询记录                       |
|********************************************************************************************|
| Date Manipulation Language 【 DML 】数据操纵语言 | insert    | 添加记录                       |
|                                              | update    | 修改记录                       |
|                                              | delete    | 删除记录                       |
|********************************************************************************************|
| Data Definition Language 【 DDL 】数据定义语言    | create    | 创建                           |
|                                              | alter     | 修改                           |
|                                              | drop      | 丢弃【删除】                   |
|                                              | rename    | 重命名                         |
|                                              | truncate  | 截断                           |
|********************************************************************************************|
| Transaction Control 事务控制                   | commit    | 确认命令                       |
|                                              | rollback  | 回退至前一次确认的命令或保存点 |
|                                              | savepoint | 设置保存点                     |
|********************************************************************************************|
| Data Control Language 【 DCL 】数据控制语言       |grant     | 授予权限                       |
|                                              | revoke    | 撤消权限                       |
|********************************************************************************************/

/*************************************|
|       系统权限        |     说明      |
|*************************************|
|   create session     |  连接数据库   |
|*************************************|
|   create table       |  创建表       |
|*************************************|
|   create sequence    |  创建序列     |
|*************************************|
|   create view        |  创建视图     |
|*************************************|
|   create proceduer   |  创建程序     |
|*************************************/

/*******************************************************************|
|      \  对象 |  表【 table 】 |  视图【 view 】 |  程序【 procedure 】   |
| 权限   \     |             |              |                    |
|*******************************************************************|
|    insert    |     Y       |      Y      |                    |
|*******************************************************************|
|    alter    |      Y      |              |                    |
|*******************************************************************|
|    update    |     Y       |      Y      |                    |
|*******************************************************************|
|    delete    |     Y       |      Y      |                    |
|*******************************************************************|
|    select    |     Y       |      Y      |                    |
|*******************************************************************|
|    index    |      Y      |              |                    |
|*******************************************************************|
|    execute  |             |              |         Y          |
|*******************************************************************/
-- 创建用户
CREATE USER u01 IDENTIFIED BY p01;
-- 创建用户并制定默认表空间
CREATE USER u01 IDENTIFIED BY p01 DEFAULT TABLESPACE QUOTA 2M ON ts01; --quota 表空间中可使用的配额
-- 修改用户密码
ALTER USER u01 IDENTIFIED BY p001;
-- 修改用户表空间配额
ALTER USER u01 QUOTA 20M ON ts01;
ALTER USER u01 QUOTA UNLIMITED ON ts01; -- 用户对表空间没有配额限制
-- 回收 unlimited tablespace 权限
REVOKE UNLIMITED TABLESPACE FROM ts01; 
-- 删除用户
DROP USER u01;
-- 切换连接数据库的用户
CONNECT u01/p01;
conn u01/p01;
-- 授权用户连接数据库的权限
GRANT CREATE SESSION TO u01;
-- 授权用户创建序列 (sequence) 的权限
GRANT CREATE sequence TO u01;
-- 授权用户创建表的权限
GRANT CREATE TABLE TO u01;
-- 授权用户查表的权限
grant select on ts01.user1 to wangkai ；
-- 授权用户修改表的权限
grant alter on user1 to  wangkai ；
-- 授权用户删除表的权限 ( 没有此权限 )
grant drop on user1 to  wangkai ； ( 错误 )
-- 授权用户对一个表的所有权限
grant all on user1 to wangkai; 
-- 授权所有用户对一个表的所有权限
grant all on user1 to public;
-- 收回用户创建表的权限
REVOKE CREATE TABLE FROM u01;
-- 收回用户查表的权限
REVOKE select on user1 FROM u01;
-- 收回用户修改的权限
REVOKE select on user1 FROM u01;
-- 收回用户对一个表的所有权限
revoke all on user1 from wangkai;
-- 收回所有用户对一个表的所有权限
revoke all on user1 to public; 
-- 创建角色
CREATE ROLE r01;
-- 角色授权
GRANT CREATE SESSION, CREATE TABLE TO r01;
-- 收回角色权限
REVOKE CREATE TABLE FROM r01;
-- 查看角色权限
SELECT ROLE,PRIVILEGE FROM role_sys_privs WHERE ROLE=‘R01‘
-- 角色赋给用户
GRANT r01 TO u01;
-- 查看当前用户角色
SELECT * FROM user_role_privs;
-- 删除角色
DROP ROLE r01;

原文：http://tzxvae.blog.51cto.com/8981690/1434124