Oracle11g--常用SQL命令（实用版）

客官°小女子只卖身不卖艺 2021-08-13 19:31 393阅读 0赞

*  **退出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 (&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;

<table> 
 <thead> 
  <tr> 
   <th align="center">约束条件</th> 
   <th align="center">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">UNIQUE</td> 
   <td align="center">指定字段的值，必须是唯一的</td> 
  </tr> 
  <tr> 
   <td align="center">PRIMARY KEY</td> 
   <td align="center">主键，会为指定的字段作索引，并且也是唯一的值</td> 
  </tr> 
  <tr> 
   <td align="center">NOT NULL</td> 
   <td align="center">不可以是空值【 NULL 】或 0 （零）</td> 
  </tr> 
  <tr> 
   <td align="center">CHECK</td> 
   <td align="center">检查，必须符合指定的条件</td> 
  </tr> 
  <tr> 
   <td align="center">FOREIGN KEY</td> 
   <td align="center">外键，用来创建一个参考表之间的关系</td> 
  </tr> 
 </tbody> 
</table>

*  **建表同时建立唯一约束**  
    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 WHERE table\_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 WHERE table\_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;
 *  **查看非空约束**  
    SELECT table\_name, constraint\_name, constraint\_type, search\_condition FROM User\_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 FROM User\_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 WHERE table\_name = ‘STUDENT’;
 *  **被引用表是不能删除的**  
    drop table team;
 *  **被引用的记录是不能删除的**  
    nsert 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);

<table> 
 <thead> 
  <tr> 
   <th align="center">系统权限</th> 
   <th align="center">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">create session</td> 
   <td align="center">连接数据库</td> 
  </tr> 
  <tr> 
   <td align="center">create table</td> 
   <td align="center">创建表</td> 
  </tr> 
  <tr> 
   <td align="center">create sequence</td> 
   <td align="center">创建序列</td> 
  </tr> 
  <tr> 
   <td align="center">create view</td> 
   <td align="center">创建视图</td> 
  </tr> 
  <tr> 
   <td align="center">create proceduer</td> 
   <td align="center">创建程序</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th align="center">权限\对象</th> 
   <th align="center">表【table】</th> 
   <th align="center">视图【view】</th> 
   <th align="center">程序【procedure】</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">insert</td> 
   <td align="center">Y</td> 
   <td align="center">Y</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">alter</td> 
   <td align="center">Y</td> 
   <td align="center"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">update</td> 
   <td align="center">Y</td> 
   <td align="center">Y</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">delete</td> 
   <td align="center">Y</td> 
   <td align="center">Y</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">select</td> 
   <td align="center">Y</td> 
   <td align="center">Y</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">index</td> 
   <td align="center">Y</td> 
   <td align="center"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">execute</td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center">Y</td> 
  </tr> 
 </tbody> 
</table>

*  **创建用户**  
    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;