Oracle学习札记(四)DCL和Transaction
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>;