Oracle的DML、DDL、DCL详解数据库

DML 全称Data Manipulation Language，即数据操作语言：insert update delete select

DDL 全称Data Definition Language，即数据定义语言：create alter drop runcate

DCL 全称Data Control Language，即数据控制语言：commit rollback

表名和列名命名规则：
必须以字母开头
必须在 1–30 个字符之间
必须只能包含 A–Z, a–z, 0–9, _, $, 和 #
必须不能和用户定义的其他对象重名
必须不能是Oracle 的保留字
Oracle默认存储是都存为大写
数据库名只能是1~8位，datalink可以是128位，和其他一些特殊字符

DML 介绍

插入数据 insert
为每一列添加一个新值。
按列的默认顺序列出各个列的值。
在 INSERT 子句中随意列出列名和他们的值。
字符和日期型数据应包含在单引号中

INSERT INTO table [(column [, column...])] 
VALUES      (value [, value...]); 
 
使用这种语法一次只能向表中插入一条数据 
 
insert into emp(empno,ename,sal,deptno) values(1001,'Tom',3000,10);

在SQL 语句中使用 & 变量指定列值。
& 变量放在VALUES子句中

insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno); 
输入 empno 的值:  1002 
输入 ename 的值:  'Mary' 
输入 sal 的值:  3000 
输入 deptno 的值:  20 
原值    1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno) 
新值    1: insert into emp(empno,ename,sal,deptno) values(1002,'Mary',3000,20) 
 
已创建 1 行。 

从其它表中拷贝数据
在 INSERT 语句中加入子查询。 
不必书写 VALUES 子句。
子查询中的值列表应与 INSERT 子句中的列名对应

insert into emp10 
select * from emp where deptno=10;

更新数据 update
可以一次更新多条数据。

UPDATE      table 
SET     column = value [, column = value, ...] 
[WHERE      condition]; 

使用 WHERE 子句指定需要更新的数据。
如果省略WHERE子句，则表中的所有数据都将被更新

update emp set sal=20000 where sal<3000;

删除数据 delete

DELETE [FROM]     table 
[WHERE    condition]; 

使用WHERE 子句指定删除的记录。
如果省略WHERE子句，则表中的全部数据将被删除。

delete from emp where sal = 5000;

DDL 介绍

创建表 CREATE TABLE

CREATE TABLE [schema.]table 
        (column datatype [DEFAULT expr][, ...]); 

必须具备: CREATE TABLE权限 和 存储空间
必须指定:表名、列名、 数据类型、 数据类型的大小

数据类型

create table test1 
(tid number, 
tname varchar2(20), 
hiredate date default sysdate);

使用子查询创建表
指定的列和子查询中的列要一一对应
通过列名和默认值定义列

create table empinfo 
as 
select e.empno,e.ename,e.sal,e.sal*12 annlsal,d.dname 
from emp e,dept d 
where e.deptno=d.deptno;

修改表结构语句 ALTER TABLE
使用 ALTER TABLE 语句可以:
追加新的列
修改现有的列
删除一个列

使用 ALTER TABLE 语句追加, 修改, 或删除列的语法

追加 
ALTER TABLE table 
ADD        (column datatype [DEFAULT expr] 
           [, column datatype]...); 
 
修改 
ALTER TABLE table 
MODIFY     (column datatype [DEFAULT expr] 
           [, column datatype]...); 
删除 
ALTER TABLE table 
DROP     column     (column); 
 
重命名 
ALTER TABLE table_name rename column old_column_name  
to new_column_name 
 

alter table test1 add photo blob; 
 
alter table test1 modify tname varchar2(40); 
 
alter table test1 drop column photo; 
 
alter table test1 rename column tname to username;

删除表 DROP TABLE
数据和结构都被删除
所有正在运行的相关事物被提交
所有相关索引被删除
DROP TABLE 语句不能回滚，但是可以闪回

drop table emp20; 
/* 
执行上调语句后，oracle会把表放入oracle回收站recyclebin， 
可以通过show recyclebin进行查看。然后利用RECYCLEBIN NAME还可以对表进行操作。如：select * from "BIN$dM0SdftKTjKHEAQrtudcZg==$0"; 
回收站中的表可以通过闪回进行恢复（后面会对闪回进行介绍） 
可以通过purge recyclebin;进行清空回收站。 
*/

清空表 TRUNCATE TABLE
删除表中所有的数据
释放表的存储空间
TRUNCATE语句不能回滚

truncate table emp20;

delete 和truncate区别
根本区别: delete是DML(可以回滚)，truncate是DDL(不可以回滚)
delete是逐条删除；truncate先摧毁表 再重建
delete不会释放空间 truncate会
delete会产生碎片 truncate不会
delete可以闪回 truncate不可以

DCL介绍

数据库事务
数据库事务由以下的部分组成:
一个或多个DML 语句
一个 DDL(Data Definition Language – 数据定义语言) 语句
一个 DCL(Data Control Language – 数据控制语言) 语句

以第一个 DML 语句的执行作为开始
以下面的其中之一作为结束:
显示结束: commit rollback
隐式结束(自动提交): DDL语言,DCL语言, exit(事务正常退出)
隐式回滚(系统异常终了): 关闭窗口，死机，掉电

COMMIT和ROLLBACK语句的优点：
确保数据完整性。
数据改变被提交之前预览。
将逻辑上相关的操作分组。

使用 SAVEPOINT 语句在当前事务中创建保存点。
使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。

UPDATE... 
SAVEPOINT update_done; 
Savepoint created. 
INSERT... 
ROLLBACK TO update_done; 
Rollback complete. 

事务进程
自动提交在以下情况中执行:
DDL 语句。
DCL 语句。
不使用 COMMIT 或 ROLLBACK 语句提交或回滚，正常结束会话exit。
会话异常结束或系统异常会导致自动回滚

提交或回滚前的数据状态:
改变前的数据状态是可以恢复的
执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正
其他用户不能看到当前用户所做的改变，直到当前用户结束事务。
DML语句所涉及到的行被锁定， 其他用户不能操作。

提交后的数据状态:
数据的改变已经被保存到数据库中。
改变前的数据已经丢失。
所有用户可以看到结果。
锁被释放， 其他用户可以操作涉及到的数据。
所有保存点被释放。

提交数据 COMMIT;
使用 ROLLBACK 语句可使数据变化失效:
数据改变被取消。
修改前的数据状态被恢复。
锁被释放。

数据库的隔离级别

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
脏读: 对于两个事物 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
不可重复读: 对于两个事物 T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
幻读: 对于两个事物 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.
数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.
一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱

数据库提供的 4 种事务隔离级别:

Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE. Oracle 默认的事务隔离级别为: READ COMMITED
Mysql 支持 4 中事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ