Oracle_数据处理

SQL语句类型

• DML语句：（data manipulation language）
  增、删、改、查：insert\update\delete\select
• DDL语句：（data definition language）
  truncate\create\alter\drop
• DCL语句：（data control language）
  commit\rollback：rollback必须在commit之前执行才有效。

数据处理

• 操作对象是数据库的表中的数据

insert：insert into

• 单行插入时需加关键字values，多行插入则不需要。

//若插入的一条数据涵盖了表中所有的列，则表的列名可不写：
desc emp; //查询表emp的结构
insert into emp values(1001, 'Tom', 'ENGINEER', 7839, sysdate, 5000, 200, 10);
//亦可在插入的列数据中转换格式，以及显式插入空值NULL：
insert into emp values(1002, 'Bone', 'CLARK', 7820, to_date('17-12月-82', 'DD-MON-RR'), NULL, 300, 20);
//若插入的一条数据中并没有包含表中所有列，则应显式列出所插入列的名字：
insert into emp(empno, ename, sal, deptno), values(1003, 'Jim', 6000, 20);
//没有写出的列自动填NULL（隐式插入空值）
insert into emp(empno, ename, sal) values(1003, 'Jim', null);

1. 使用&可提高插入数据的效率：

insert into emp(empno, ename, sal) values(&empno, &ename, &sal);
//执行该条语句时，命令行会阻塞等待用户逐个输入各列的值
//（&后的变量名可任意指定，但好习惯是与前面的列名相同，便于清楚赋值对象）
//可在DML的任意一种语句中使用&：
select empno, ename, sal, &t from emp;
//执行时会提示要求输入要查询的列名，输入后回车会显示这条select语句的查询结果。
select * from &t; //输入不同的表名，可查询不同的表内容。

2. 批处理：一次插入多条数据

//按照表emp的结构创建新表emp10：
create table emp10 as select * from emp where 1=2; //不会有emp表中的任何数据跟进来（因为where 1=2为假，这里where子句可随意指定，只要为假即可）
//把表emp中10号部门的员工信息插入新表emp10中（上一条语句保证了emp10和emp的结构相同）：
insert into emp10 select * from emp where deptno = 10; //该语句实际上也是一条子查询语句
//一次性把表emp中的指定列插入到新表emp10中：
insert into emp10(empno, ename, sal, comm)
select empno, ename, sal, comm
from emp
where deptno = 10; //注意：批处理时没有关键字values；列名须一一对应
//总结：子查询可出现在DML的任意一种语句中，不止select查询

update：update ... set

update emp10 set job='MANAGER', hiredate=sysdate, sal='2000' where empno=1001; //若没有where子句，则更新的是整张表的对应列数据
//NULL在update中的作用：赋值，而非判等
update emp10 set comm = null where empno = 1000; //若改为 comm is null 反而会出错
//区别于：
select * from emp where comm = null; //会出错

delete：delete from

delete from emp10 where empno = 1000; //若没有where子句，则删除的是整张表
//from关键字在Oracle中可省略，但在MySQL中不可。

• delete和truncate的区别：
  • delete逐条删除表内容，truncate先摧毁整张表再重建新表。（由于delete使用频繁，Oracle对delete优化后执行效率快于truncate）
  • delete是DML语句，truncate是DDL语句。DML语句可闪回（flashback），DDL语句不可闪回。（闪回即做错了一个操作并commit后，可撤销该操作。）
  • delete是逐条删除数据，会产生数据碎片（即被删除的那一条数据）；而truncate不会产生碎片。（处理数据碎片的开销：整理碎片、数据连续、行移动。不过Oracle对delete做了优化，让delete不会产生碎片。）
  • delete不会释放空间，truncate会释放空间。例如，一张大小为10M的表中存有100条数据，用delete删除这条表后，其中的100条数据没有了，但10M的空间依然还在；而用truncate摧毁了这张表后，数据和空间都没有了，只留下了重建的空表的表头所占的空间。
  • delete可回滚（rollback），truncate不可回滚。

事务

• 数据库事务由以下部分组成：
  一个或多个DML语句、一个DDL语句、一个DCL语句。
• 事务的特点：一个事务中的若干语句要么都成功、要么都失败。

事务的特性（ACID）：

1. 原子性（Atomicity）：事务中的全部操作不可分割，要么都完成，要么都不执行；
2. 一致性（Consistency）：几个并行执行的事务（如多个终端上）的执行结果必须与按某一串行顺序执行的结果一致，即保证数据库的并发性；
3. 隔离性（Isolation）：当多个客户端并发访问数据库时，彼此的事务执行不互相干扰，隔离它们的操作；
   了解：
   Oracle支持三种隔离级别：读已提交read commited（默认）、串行化serializable（即使查询也要等前一个事务结束）、只读read only（当前事务执行时允许其他事务查询）；
   MySQL支持四种隔离级别：读未提交read uncommited、读已提交read commited、可重复读repeatable read（默认）、串行化serializable
4. 持久性（Durability）：即使数据库遭遇外界故障（如掉电），系统须保证事务对数据库的修改不丢失。

事务的起始标志：

• 在Oracle中，事务是自动开启的，以DML语句为开启标志。
  执行一个增删改查语句，只要没有提交和回滚，操作都在一个事务中。

事务的结束标志：

• 提交、回滚都是事务的结束标志。
• 提交：

1. 显式提交：commit
2. 隐式提交：
   • 有DDL语句。如，create table除创建表之外，还会隐式提交create之前所有没有提交的DML语句。
   • 正常退出：exit、quit

• 回滚：

1. 显式回滚：rollback
2. 隐式回滚：掉电、宕机、非正常退出。

控制事务

• savepoint：
  DDL、DML、DCL语句都是对数据库事务的控制，除此之外，还引入了保存点savepoint，可更精细地划分事务。

create table testsp (tid number, tname varchar2(20)); //隐式commit此操作之前的操作
set feedback on; //打开回显（默认打开）
insert into testsp values(1, 'Tom');
insert into testsp values(2, 'Mary');
savepoint aaa; //设置一个保存点
insert into testsp values(3, 'Moke');
select * from testsp; //此时已有三条数据
rollback to savepoint aaa; //回滚到保存点aaa
select * from testsp; //此时表中仅有前两次插入的两条数据
//注意：前两次的操作仍没有提交，若操作完成应显式提交：执行commit

• savepoint用于在事务中间声明一个标记，将一个长事务分隔为多个较小部分，防止出错和丢失数据。若保存点名称重复，会删除前一个保存点。一旦commit，savepoint将失效。

创建和管理表

• 操作对象是数据库中的一张张表本身

create：create table

• 注意：创建一张新表需有：权限、存储空间。

create table test1 (tid number, tname varchar2(20), hiredate date default sysdate);
//列名后跟列数据类型（小括号中数字表示该数据类型长度）
//default的作用：当向表中添加的数据中没有指定时间时使用默认值sysdate
insert into test1(tid, tname) values(11, 'zhangsan'); //hiredate列默认取当前时间（前提是前面指定了列名，这相当于没指定的列采用隐式插入空值）
//区别于：
insert into test1 values(11, 'zhangsan'); //会出错，应加上hiredate列数据

• 创建表时，列使用的数据类型：

数据类型	描述
VARCHAR2(size)	可变长字符数据
CHAR(size)	定长字符数据
NUMBER(p, s)	可变长数值数据
DATE	日期型数据
LONG	可变长字符数据，最大达2G
CLOB	字符数据，最大达4G
RAW and LONG RAW	原始二进制数据
BLOB	二进制数据，最大达4G
BFILE	存储外部文件的二进制数据，最大达4G
ROWID	行地址

//区别：
create table emp10 as select * from emp where 1 = 2; //拷贝表结构但不拷贝表数据
create table emp10 as select * from emp where deptno = 20; //where子句后是一个有效条件，就会拷贝表结构，同时把查询到的数据拷贝到新表emp10
//该语句用于表数据的导入很方便

//根据两张表的数据创建一张新表：
//创建一张新表，要求包含：员工号、姓名、月薪、年薪、年收入、部门名称
create table test2 as 
select e.empno, e.ename, e.sal, e.sal*12 年薪, e.sal*12+nvl(comm, 0) 年收入, d.dname
from emp e, dept d
where e.deptno = d.deptno;
//注意：创建某一列需用到表达式时，必须给表达式取个别名作为新表的列名

alter：alter table

//向表中插入一列：
alter table test1 add job varchar2(20); //插入了新列job，数据类型是最长20个字符
//修改一列的属性：
alter table test1 modify job varchar2(40);
//删除一列：
alter table test1 drop column job;
//重命名一列：
alter table test1 rename column tid to tnumber;

drop：drop table

• 删除表：

drop table test1;
select * from test1; //不可再访问表test1
select * from tab; //可发现，被删除的表test1现被一个名称复杂的表替代，这是它回收站中的名字
//因为drop操作会把表test1放到回收站中
purge recyclebin; //清空回收站
select * from tab; //那个复杂名字的表没有了
//也可彻底删除一张表，不让它经过回收站：
drop table test1 purge;

• 注意：并不是所有用户都有回收站，管理员用户就没有，它的删除操作不可逆。
• 当表被删除：
  1）数据和表结构都被删除；
  2）所有正在运行的相关事务被提交；（因drop语句也是一个DDL语句）
  3）所有相关索引被删除；
  4）DROP TABLE语句不能回滚，但可闪回。

rename：rename ... to

rename test1 to test2;

约束

约束的种类

1. Not Null：非空约束
   表示此列数据不能为空
2. Unique：唯一性约束
   不可与已有数据重复。如，电子邮件地址。
3. Primary Key：主键约束
   通过这列的值可唯一确认一行记录，隐含Not Null + Unique。
4. Foreign Key：外键约束
   制约表与表之间的关系。如，对于部门表dept和员工表emp，不应该在表emp中存在不属于表dept中的任一部门的员工，这里表dept是表emp的外键。也把外键约束称为父子表或主从表，外键表是父表或主表。
   问题是，如果要删除父表中的某一列数据，而这列数据又在子表中被引用了，如何处理？
   方法一：先将子表的对应内容删除，再删除父表：
   定义外键时，通过references指定参数：ON DELETE CASCADE（当删除父表时，如发现父表内容被子表引用，级联删除子表引用记录）
   方法二：将子表外键一列置为NULL值，断开引用关系，再删除父表：
   定义外键时，通过references指定参数：ON DELETE SET NULL（先把子表中对应外键置空，再删除父表）
5. Check：检查性约束
   如，密码设置要求不少于6位；某列数据采用下拉列表多选一。

• constraint：给约束起别名（好处是当用户违反某一约束时，提醒信息能做到见名知意）

create table student
( sid number constraint student_PK primary key, //给sid列（数据类型为number）设置主键约束（别名为student_PK）
  sname varchar2(20) constraint student_name_notnull not null, //sname：not null约束
  email varchar2(20) constraint student_email_unique unique
                     constraint student_email_notnull not null, //email: unique和not null两个约束
  age number constraint student_age_min check(age > 10), //age：check约束
  gender varchar2(6) constraint gender_female_or_male check(gender in ('男', '女')), //gender: check约束
  deptno number constraint student_FK references dept(deptno) ON DELETE SET NULL //deptno: foreign key约束
)

//测试用例：
insert into student values(1, 'Tom', 'tom@126.com', 20, '男', 10); //正确插入数据
insert into student values(2, 'Tom', 'tom@126.com', 15, '男', 10); //违反student_email_unique约束
insert into student values(3, 'Tom2', 'tom2@126.com', 14, '男', 100); //违反student_FK约束：未找到父项关键字（表dept的deptno列中没有100）

• 注意：并不是父表中的所有列都可以设置为子表的外键，而必须是父表中的主键。
• 查看当前表的约束：

//查看表student的约束：
select constraint_name, constraint_Type, search_condition
from user_constraints where table_name='STUDENT'; //表名需大写

其他数据库对象：视图、序列、索引、同义词

视图

• 视图基于表，是一个逻辑概念，它本身没有数据。
• 创建视图的语法与创建表一样，除了将关键字table换为view。
  开启创建视图的权限：

sqlplus / as sysdba;
grant create view to xdb; //给用户xdb开启权限

• 视图的优点：

1. 简化复杂查询：原来的分组、多表、子查询等可以用一条select * from xxxview代替。视图可看作是表的复杂的SQL的一种封装。

create view empincomeview
as
select e.empno, e.ename, e.sal, e.sal*12 annualsal, e.sal*12+nvl(comm, 0) income, d.dname
from emp e, dept d
where e.deptno = d.deptno;

2. 限制数据访问，隐藏源数据：只看视图的结构和数据是无法知道该视图是怎么来的。

• 注意：视图不能提高性能；不建议（但不是做不了）通过视图修改表数据（不通过视图做：insert\update\delete操作），因为视图提供的目的就是为了简化查询。
• 几点细节：

1. 视图只能创建、删除、替换。（不能修改）

//视图不存在则创建，存在则替换
create or replace view empincomeview 
as 
select ... from ... where ...
with read only; //将视图修改为只读视图（只能对它使用select语句）

2. 别名：可写在select子查询各个列的后面，也可写在视图名后
3. with check option：

create view testview
as
select * from emp where deptno = 10
with check option;
//再向视图testview中插入deptno不为10的数据会出错

4. 删除视图：

drop view testview; //不加关键字purge

序列（了解）

• 可理解为数组：[1, 2, … , 20]。初始时，游标指向1前的位置，向后移取出第一个值。每取一次游标后移一次。
• 序列可提高效率（因序列存于内存中）。常用于指定表中的主键。
• 创建并使用序列：

create sequence myseq;
create table tableA (tid number, tname varchar2(20)); //tid列作为主键，准备使用序列来向表中插入值
insert into tableA values(myseq.nextval, 'Tim'); //只有nextval取完会向后移动，currval不会移动
insert into tableA values(myseq.nextval, 'Jack'); //继续使用nextval向表中添加主键tid

//对于新创建的序列：
select myseq.currval from dual; //出错：初始状态下currval指向1前面的值
select myseq.nextval from dual; //1
select myseq.currval from dual; //1：此时currval的值也为1

• 只有将来的序列值会被改变。改变序列的初始值只能通过删除序列之后重建序列实现。
• 删除序列：

drop sequence myseq;

• 注意问题：
  序列是公有对象，多张表同时使用序列会造成主键不连续；回滚也可能造成主键不连续，因为nextval不会随着rollback操作回退；掉电也可能造成不连续，这是因为序列是存在于内存中的，掉电后内存内容会丢失，且恢复供电后序列直接从21开始。

索引

• 用户需要做的仅有创建和删除索引：

create index myindex on emp(deptno); //创建索引：索引是基于表中的列存在的
drop index myindex; //删除索引
//在多列上建立索引：
create index myindex on emp(deptno, job);

• 索引相当于目录，提高数据检索速度。
• 索引的基本介绍：

1. 独立于表，可存储在与表不同的磁盘或表空间中：它与表相关，但独立存储。它实际上是在逻辑上与表产生联系。
2. 索引被破坏或删除时，不会对表产生影响，只会影响查询速度。
3. 索引一旦建立，用户不需维护，系统为自动维护，而且由系统决定何时使用索引，用户不用在查询语句中指定使用哪个索引。
4. 删除表时，所有基于该表的索引会自动被删除。
5. 通过指针加速Oracle服务器的查询速度。（Oracle实现索引的机制）
6. 通过快速定位数据减少磁盘I\O。

• 以下情况可考虑创建索引：

1. 列中数据值分布范围很广；
2. 列经常在where子句或连接条件中出现；
3. 表经常被访问而且数据量很大（访问的数据量大概占数据总量的2%~4%）。
   与上述情况相反的条件下不适合创建索引。另外，若表经常更新，也不适合创建索引，因为维护索引的成本高。

同义词（synonym）

• 即表的别名：隐藏原表，且简化执行。

//用户scott访问用户hr下的表employees：（默认无权限，需hr给scott授权）
sqlplus hr/11 //或：conn hr/11（已登录界面，切换登录）
grant select on employees to scott; //hr给scott开放表employees的查询权限
select count(*) from hr.employees; //现在scott可查询该表了
//scott为hr.employees取别名：
create synonym hremp for hr.employees;
//若有权限限制，则切换至管理员登录，给scott添加同义词设置权限：
conn / as sysdba
grant create synonym to scott;
select count(*) from hremp; //现在scott可使用同义词查询了

• 同义词、视图等用法在数据保密要求高的机构使用广泛，既不影响对数据的操作，又能保证数据的安全。