DML、DDL、DCL

D是Data，L是language；中间字母分别是，M：manipulation 操纵，操作，D：definition 定义，C：control 控制

在执行commit、DDL、DCL、exit、conn语句之后会进行自动提交事务；rollback、quit取消事务，savepoint s1，指定保存点s1，rollback to s1可以回到保存点s1。进程异常终止执行的是rollback。

一、事务

包含一条或多条SQL语句的逻辑句子，以提交事务开始，以提交事务结束，每个事务的开始和结束都有一个SCN来标识，SCN(system change number)是单调递增的，read only模式SCN号不会修改；

如①commit；②insert...,update....,delete.....；③commit，在①至③中都是属于一个事务；通过select xid,status from v$transaction可以查看事务的id号，在事务提交后查询不到xid，之后执行select语句也没有重新开启一个事务，要执行DML才会重新查出xid。

有Atomicity(原子性，提交100条sql不会出现前50条提交后50条没有提交的情况，都是一起提交)、Consistency(一致性，转账时a账户装给b账户100元，a减100，b一定加100)、Isolation(隔离性，a和b是隔离的,a未提交的数据b读不到)、Durability(持久性，插入数据提交之后就保存到数据库了)。

1、事务的开始(显示开始set transaction name 'TRX_INSERT')

2、事务的结束：commit、rollback(而不是rollback to)、DDL、用户正常退出(隐式执行commit)、进程异常终止(隐式执行rollback)

3、保存点：存在事务中，事物结束就释放了。设置保存点后可以回滚到保存点，如果保存点在已结束的事务中，就不可以回滚了。

4、幻读：查询第一次有5条数据，再次查询后有6条数据，数据多了(其他会话插入并提交了)；模糊读：第一次查询id=5的salary是5000，第二次查询是5500,数据修改了(其他会话修改并提交了)。脏都：还没提交的数据被读到了，oracle没有脏读。

5、锁种类：

有自动锁和手动锁(ORACLE自动管理)等，自动锁有DML锁、DDL锁、系统锁。

(1)、DML锁有TM表锁和TX行锁(某个会话a对某行进行操作，其他会话在a还未释放那一行之前不可以操作)，是执行UPDATE、INSERT、DELETE操作后产生的锁。

a、TM表锁

Row Share(RS)：行共享锁是表锁中限制最少的模式，在线创建索引时获得行共享锁，不影响DML操作

Row Exclusive Table Lock(RX)也称为subexclusive table lock(SX)：允许事务同时修改同一张表，但是不允许同时修改同一行数据

Share table lock(S)：允许并行查询，但是不允许并行执行DML操作

Exclusive table lock(X)：限制级别最高的锁，当执行DDL操作时，往往需要获取数据。

update hr.departments set department_name='IT ...' where department_id=210;
select * from v$mystat where rownum = 1;  //是345
select sid,type,lmode from v$lock where sid = 345;

 

二、DML：数据操作语言，可以rollback

主要是对数据库数据的操作语言，主要有select、insert、delete、update、merge，主要改变数据。DML提交的条件有：COMMIT、ROLLBACK、DDL、DCL、exit、系统崩溃。

1、数据查询

select * from jobs;  //查询数据

2、数据插入，在插入数据之前看表的结构哪些数据不能为空、数据的类型和约束条件，一次只能插入一条数据(Mysql可以一次插入多条数据)。插入数据可以是子查询，但要与字段一一对应。

job_id和job_title不能为空，min_salary和max_salary的数据类型是数值，不能插入字符。

insert into jobs(job_id,job_title,min_salary,max_salary) values('WK_T','Work Test',1000,5000);

未执行commit之后rollback回滚可以取消插入该条数据，因为没有对该条数据进行存盘，该条数据被锁住了，其他用户看不到该条数据，只有本身可以看到，commit对数据进行存盘之后，其他用户可以看到，在commit之后rollback，插入的该条数据还是存在盘中。

rollback

insert into jobs(job_id,job_title,min_salary,max_salary) values('WK_T','Work Test',1000,5000);
commit;
rollback;

3、update更新数据，有where条件可以对一条或几条数据进行更改，如果没有where就对表所有数据进行更改，update更新是先查询where后面的数据，再更新。set value=null是允许的（没有约束的情况下），即判断字段是否为空用col is null，但是更新空值可以用col=null，update tablenamea set (cola,colb) =(select vala,valb from tablenameb) where condition;

update jobs set job_title='Work Test Category A' where job_id='WK_T';
select * from jobs;

4、delete删除数据，有where条件可以删除一条或几条数据，如果没有where就删除整个表的数据，oracle里删除时delete from后面的from可以去掉，但是最好加上；delete删除表数据不释放表空间，所以如果要释放表空间之后要使用truncate，但truncate是整个表数据都删除。delete后可以数据回撤，truncate之后数据不可以回撤，delete删除数据是将数据保存到undo tablespace，truncate删除数据不会将数据保存到undo tablespace。

delete from jobs where job_id='WK_T';
select * from jobs;

delete删除整个表的数据速度比truncate慢，因为delete删除会被重做日志文件记录，即记录redo和undo，object_id是元数据唯一表示；data_object_id是数据段唯一标识，truncate删除会重新分配数据段唯一标识，从75392变到87907。

select * from tabb;  //要确保表里有数据
select object_name,object_id,data_object_id from user_objects;

truncate table tabb;
select object_name,object_id,data_object_id from user_objects;

 

三、DDL：数据定义语言。

主要该表的结构，有create、alter、rename、truncate、drop、comment；执行DDL时先①commit；再②执行DDL，③最后commit。DDL日志记录数据库执行的ddl(没有DML)，要设置enable_ddl_logging参数值为true，默认是false，不建议打开，否则会占用很大的空间，$ORACLE_BASE/diag/rdbms/<db_name>/<sid>/log/ddl保存的是XML类型文件，$ORACLE_BASE/diag/rdbms/<db_name>/<sid>/log保存的是TXT类型文件。

select name,value,isses_modifiable from v$parameter where lower(name)='enable_ddl_logging';  //默认是false，session级别可修改
alter session set enable_ddl_logging=true;
select name,value,isses_modifiable from v$parameter where lower(name)='enable_ddl_logging';
create table atest(tid number(6),tname varchar2(10),salary number(6,2)); 
insert into atest values(1,'chenmu',5000);
commit;
select * from atest;

cd /u01/app/oracle/diag/rdbms/chenmu/chenmu/log;  //生成一个ddl_chenmu.log文件,只记录DDL语句
more ddl_chenmu.log;

dbms_metadata.get_ddl()函数导出建对象的元数据，例如表空间、用户、表等，查看desc DBMS_METADATA，可以这个包里的函数

desc DBMS_METADATA;  //查看这个包里面的函数get_ddl

object_type和name都没有默认值，所以需要指定，这个函数范围CLOB值，需要将其转换成字符类型。例如创建表空间的DDL语句

select dbms_lob.substr(dbms_metadata.get_ddl('TABLESPACE','USERS')) from dual;

1、create建表

//建表模板
create table [schema.]tablename(
id number constraint tab_id_pk primary key,
tname varchar2(10) not null,
salary number(6,2)
)
//在外面添加主键约束，alter table tablename add constraint tab_id_pk primary key(id);
//在外面添加外键约束，alter table tablename add constraint tab_id_fk foreign key(fid) references tablename(id);
//在外面添加check约束，alter table tablename add constraint tab_salary_check check(salary>0)

（1）schema：模式，是该用户下所有对象集合，一般与用户同名；user是人，schema是容器。

（2）tablename的命名规则：开头为字母；不超过30个字符；由A-Z、a-z、0-9组成；同一用户下表必须不同名；不用oracle保留字。

（3）字段类型：

①varchar2，variable char，char类型的第二个版本，可变字符，插入数据后数据长度不一定和建表时候相等 。

②char，定长字符串，插入数据后数据长度和建表时候相同。 

③number(p,s)数字类型，p是精度，s是小数点右边的数字个数。 

④date日期类型，保留日期和时间。

⑤timestamp日期类型，保存日期和时间，还能保存小数秒。

⑥long存储可变长字符串，最大长度限制是2GB，一个表中只能包含一个Long类型的列，不能索引或加约束，不能在where、group by、order by、distinct子句中等，有很多限制，不推荐使用。 

⑦clob字符型大型对象，lob是large object大型对象，blob是二进制型大型对象。

⑧rowid伪列，是oracle数据库的表中每一行数据的唯一标识，使用rowid查询是最快的，删除两条同样的数据也用到rowid。

⑨interval year to month 

⑩interval day to second

（4）约束条件(另外约束整理在https://www.cnblogs.com/muhai/p/15595592.html)

not null、unique、primary key、foreign key、check，如果由系统命名，前缀是SYS_；当要删除主键时该主键值是另一个表的外键值，不能删除，除非使用delete ... cascade。unique约束和逐渐约束的区别是unique可以为空。not null、foreign key、check在数据库尽量不用。

外键约束创建：alter table test2 add constraint test_id_fk foreign key(fid) references test(tid).

create table test(tid number(6) constraint test_id_pk primary key,tname varchar2(10) not null,salary number(6,2)); 
desc test;
alter table test add constraint test_salary_check check(salary>0)

2、alter添加字段或禁用约束

alter table test add (loc varchar2(50));
desc test;

select constraint_name,table_name from user_constraints;
col table_name for a30;
col constraint_name for a30;
/;
alter table DEPARTMENTS disable constraint DEPT_MGR_FK;  //禁用约束

除了上面的还有以下功能

alter table test read only|write;  //设置表为只读，可以通过将其转为视图(视图就是一个查询语句的别名，无存储结构，复杂查询语句简单化)
alter table test drop column loc;  //删除一列
alter table test drop (loc,salary);  //删除多列
alter table test set unused column salary;  //隐藏salary列，隐藏之后不可恢复，只能查看和删除
alter table test drop unused column;  //删除隐藏的列 

3、rename修改字段名

alter table test rename column loc to location;
desc test;

4、truncate删除整张表的数据，rollback之后也不能恢复数据。

insert into test(tid,tname) values(1,'chen');
insert into test(tid,tname) values(2,'yang');
select * from test;
truncate table test where tid=1;  //错误
truncate table test;
select * from test;

5、drop将整个表删除，包括表的结构，可以从回收站恢复表，但是purge删除之后就不可以从回收站恢复了。delete和truncate是删除data，drop是删除metadata和data。

insert into test(tid,tname) values(1,'chen');
insert into test(tid,tname) values(2,'yang');
drop table test;
select * from test;  //出现表或视图不存在

desc recyclebin;
select OBJECT_NAME,ORIGINAL_NAME from recyclebin;
flashback table "BIN$5kAmuOkkfJvgU4lkGawVqg==$0" to before drop; //这里BIN..是OBJECT_NAME
select * from test;

drop table test purge;
select OBJECT_NAME,ORIGINAL_NAME from recyclebin;  //没有信息显示

 

三、DCL：数据控制语言

主要是权限授予与收回、事务提交，grant、revoke。Transaction control有commit、rollback、savepoint。

1、权限授予

dba：拥有全部特权，是系统最高权限，只有dba可以创建数据库结构

resource：拥有该权限的用户可以创建实体，不可以创建数据库结构

connect：拥有该权限的用户可以登录Oracle，不可以创建实体，不可以创建数据库结构

select *  from role_sys_privs;  //查看当前用户所具有的的角色
conn / as sysdba;
grant resource,connect to hr;
conn hr/hr123;  //到hr用户查看用户具有的角色
select *  from role_sys_privs;

2、角色撤销

conn / as sysdba;
revoke resource,connect from hr;
select *  from role_sys_privs;

 

3、事务提交

未commit之前，执行DML之后可以使用rollback撤销数据(rollback to savepoint s)；另一用户select不到当前用户操作的数据；操作的该条数据是被锁的。

执行commit之后，不可以使用rollback撤销数据；另一个用户select到当前用户操作的数据；操作的该条数据被释放了。