Oracle学习笔记(四)DCL和Transaction

1.delete和truncate的区别:

 

DELETE语句和TRUNCATE TABLE语句都可以删除表中的所有数据，但前者删除表的所有数据时，不会释放所占用的空间，并且操作可以撤销（ROLLBACK)；后者删除表的所有数据时，执行速度更快，而且还会释放表，段所占用的空间，并且操作不能撤销（ROLLBACK）。

 2.设置保存点并且回滚。

 

SQL> savepoint sp1;

保存点已创建。

SQL> update stu_grade set score=300 where cid=10201;

已更新 1 行。

SQL> select * from stu_grade;

       SID        CID      SCORE
---------- ---------- ----------
     10101      10101         87
     10101      10201        300
     10101      10301         79
SQL> rollback to  sp1;

回退已完成。

SQL> select * from stu_grade;

       SID        CID      SCORE
---------- ---------- ----------
     10101      10101         87
     10101      10201        100
     10101      10301         79

 3.数据库的权限:

  CREATE SESSION：连接到数据库

  CREATE SEQUENCE:创建序列。序列是一些列数字用来自动填充主键列

  CREATE SYNONYM:创建同名对象，同名对象可以用于引用其他模式中的表。

  CREATE TABLE:创建表。

  CREATE ANY TABLE:在任何模式中创建表。

  DROP TABLE:删除表。

  DROP ANY TABLE:删除任何模式中的表。

  CREATE PROCEDURE:创建存储过程。

  EXECUTE ANY PROCEDURE:执行任何存储过程。

  CREATE USER:创建用户。

  CREATE VIEW:创建视图。

  DROP USER:删除用户。

 4.实例

 

1.建立用户,默认没有登录的权限，必须赋予create session的权限。
 SQL> create user test identified by test;

用户已创建。

SQL> conn test/test;
ERROR:
ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied
警告: 您不再连接到 ORACLE。
-----------------------------------------------------
赋权限之后:
SQL> grant create session to test;

授权成功。

SQL> conn test/test;
已连接。

2.修改用户密码
 SQL> alter user test identified by mytest;
用户已更改。
SQL> conn test/mytest;
已连接。

3.删除用户
 SQL> drop user test;
 用户已删除。

4.查看授予用户scott的系统权限
  SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SCOTT                          UNLIMITED TABLESPACE                     NO

5.收回scott的用户权限
  SQL>revoke user_sys_privs from scott;

6.授予用户scott在表teachers上wage、bonus的update权限
  SQL>grant update  (wage,bonus) on teachers to scott with grant option;
  SQL> select * from user_tab_privs_recd;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
GRANTOR                        PRIVILEGE                                GRA HIE
------------------------------ ---------------------------------------- --- ---
SYSTEM                         MVIEW_WORKLOAD
SYSTEM                         SELECT                                   YES NO

SYSTEM                         MVIEW_FILTER
SYSTEM                         SELECT                                   YES NO

SYSTEM                         MVIEW_LOG
SYSTEM                         SELECT                                   YES NO

SYSTEM                         MVIEW_FILTERINSTANCE
SYSTEM                         SELECT                                   YES NO

SYSTEM                         MVIEW_RECOMMENDATIONS
SYSTEM                         SELECT                                   YES NO

SYSTEM                         MVIEW_EVALUATIONS
SYSTEM                         SELECT                                   YES NO

SYSTEM                         MVIEW_EXCEPTIONS
SYSTEM                         SELECT                                   YES NO

SYSTEM                         DEF$_AQCALL
SYSTEM                         SELECT                                   YES NO

SYSTEM                         DEF$_ERROR

7.创建表
 SQL> create table teacher as select * from teachers;

表已创建。

SQL> select * from teacher;

       TID NAME     TITLE  HIREDATE            BONUS       WAGE        DID
---------- -------- ------ -------------- ---------- ---------- ----------
     10101 王彤     教授   01-9月 -90           1000       3000        101
     10104 孔世杰   副教授 06-7月 -94            800       2700        101
     10103 邹人文   讲师   21-1月 -96            600       2400        101
     10106 韩冬梅   助教   01-8月 -02            500       1800        101
     10210 杨文化   教授   03-10月-89           1000       3100        102
     10206 崔天     助教   05-9月 -00            500       1900        102
     10209 孙晴碧   讲师   11-5月 -98            600       2500        102
     10207 张珂     讲师   16-8月 -97            700       2700        102
     10308 齐沈阳   高工   03-10月-89           1000       3100        103
     10306 车东日   助教   05-9月 -01            500       1900        103
     10309 臧海涛   工程师 29-6月 -99            600       2400        103
     10307 赵昆     讲师   18-2月 -96            800       2700        103
     10128 王晓            05-9月 -07                      1000        101
     10328 张笑            29-9月 -07                      1000        103
     10228 赵天宇          18-9月 -07                      1000        102
     11111 林飞            11-10月-07                      1000

已选择16行。 

 5.修改表定义

 

1.添加列
 
SQL> create table test(
  2   tid int primary key,
  3   tname varchar(23)
  4  );

 SQL> alter table test add  tage int;
表已更改。
SQL> alter table test add tsex varchar(2) check (tsex in('男','女'));

表已更改。

SQL> desc test;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ------------------
 TID                                       NOT NULL NUMBER(38)
 TNAME                                              VARCHAR2(23)
 TAGE                                               NUMBER(38)
 TSEX                                               VARCHAR2(2)

2.修改列
 
SQL> alter table test modify tsex varchar(2) default '男' check (tsex in('男','女'));
表已更改。
SQL> insert into test(tid) values(23423);

已创建 1 行。

SQL> select * from test;

       TID TNAME                         TAGE TS
---------- ----------------------- ---------- --
     23423                                    男

3.删除列
SQL> alter table test drop column tsex;

表已更改。

SQL> desc test;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------
 TID                                       NOT NULL NUMBER(38)
 TNAME                                              VARCHAR2(23)
 TAGE                                               NUMBER(38)

4.添加NOT NULL约束，使用alter。。。。modified，不能使用alter....add constraint。
  alter table student name not null;

5.添加/删除约束
 alter table student add/drop constraint student_pk primary key (sid);

6.禁止/允许约束的使用
 alter table student disable/enable  constraint student_pk;

7.修改表名
 rename <table_old_name> to <table_new_name>;
  
8.修改索引名称
 alter index <index_old_name> rename to <index_new_name>;