Oracle必备语句

qqwe

　　Oracle_11g常用SQL语句（高级工程师必备）
　　Oracle_11g常用SQL语句（高级工程师必备）
　　-- 退出 SQLPLUS
　　exit;
　　-- 修改 system(sys) 账号密码
　　SQLPLUS /NOLOG
　　CONN /AS SYSDBA

　　ALTER USER SYSTEM>　　-- 清除 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    | 创建                          |

　　|                                              |>　　|                                              | 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      |                    |
　　|*******************************************************************|

　　|   >　　|*******************************************************************|
　　|    update    |     Y       |      Y      |                    |
　　|*******************************************************************|
　　|    delete    |     Y       |      Y      |                    |
　　|*******************************************************************|
　　|    select    |     Y       |      Y      |                    |
　　|*******************************************************************|
　　|    index    |      Y      |              |                    |
　　|*******************************************************************|
　　|    execute  |             |              |         Y          |
　　|*******************************************************************/
　　-- 创建用户

　　CREATE USER u01>　　-- 创建用户并制定默认表空间

　　CREATE USER u01>　　-- 修改用户密码

　　ALTER USER u01>　　-- 修改用户表空间配额
　　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>　　--授权用户删除表的权限(没有此权限)
　　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;