oracle学习（5）

?数据处理

SQL语言的类型：

1. 数据库中，称呼增删改查，为DML语句。(Data Manipulation Language 数据操纵语言)，就是指代：

insert、update、delete、select这四个操作。

2. DDL语句。(Data Definition Language 数据定义语言)。 如：truncate table(截断/清空 一张表)

create table(表)、create view(视图)、create index(索引)、create sequence(序列)、

create synonym(同义词)、alter?table、drop table。

3. DCL语句。DCL（Data Control Language数据控制语言）如：

commit(提交)、rollback(回滚)?

插入数据insert：

使用 INSERT 语句向表中插入数据。其语法为：

INSERT INTO table [(column [, column...])]

VALUES (value [, value...]);

如果：values后面的值，涵盖了表中的所有列，那么table的列名可以省略不写。

SQL> ?desc emp; ? 查看员工表的结构，得到所有的列名。

SQL> ?insert into?emp values?(1001, 'Tom', 'Engineer', 7839, sysdate, 5000, 200, 10 ) ???

SQL> ?insert into emp values (1005, 'Bone', 'Raphealy', 7829, to_date('17-12月-82', 'DD-MON-RR'), NULL, 300, 20); ?

如果：插入的时候没有插入所有的列，就必须显式的写出这些列的名字。

SQL> ?insert into emp(empno, ename, sal, deptno) values(1002, 'Marry', 6000, 20); ?

注意：字符串和日期都应该使用 ' ' 号引用起来。

没有写出的列自动填NULL， 这种方式称之为“隐式插入空值”。

?“显示插入空值”： SQL> ?insert into emp(empno, ename, sal) values(1003, 'Jim', null); ??

?“&” 地址符：

SQL> ?insert into emp(empno, ename, sal, deptno) values(&empno, &ename, &sal, &deptno); ??

理论上“&”后面的变量名任意，习惯上一般与前面的列名相同，赋值的时候清楚在给谁赋值。

当再次需要插入新员工的时候直接输入“/”就可以继续输入新员工的值。注意输入的正确性。

可以在DML的任意一个语句中输入“&”，如：select

SQL> ?select empno, ename, sal, &t ?from emp ;

执行时，会提示你输入要查询的列名。当输入不同的列名时，显示不同的执行结果。

也可以 SQL> ?select * from &t; ??修改t的值，可以查看不同表。

批处理：

一次插入多条数据。

SQL> ?create table emp10 as select * from emp where 1=2; 创建一张表，用于测试。

SQL> ?select * from tab; ? 可以查看多了一张新表emp10，但select * from emp10?结果为空

SQL> ?desc emp10; ?? 发现该表的结构和emp表的结构完全相同。

一次性将emp表中所有10号部门的员工，放到新表emp10中来。

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

一次性将 emp表中的指定列插入到表emp10中。

注意：insert的列名，要和select的列名一致

SQL> ?insert into emp10(empno, ename, sal, deptno)

???select empno, ename, sal, deptno ?from emp

???where deptno=10; ? 注意没有values关键字了。且列名必须一一对应

总结： 子查询可以出现在DML的任何语句中，不只是查询套查询。

更新数据update

对于更新操作来说，一般会有一个“where”条件，如果没有这限制条件，更新的就是整张表。

SQL> ?update?emp10 set sal=4000, comm=300 where?ename = 'CLARK'; ??（ 或 where empno=7782;?）

注意：若没有where限定，那么会将所有的员工的sal都设置成4000，comm设置成300；

SQL> ?update emp10 set comm = null where empno=1000; ?? 这个操作是否能成功呢？

SQL> ?select *?from emp where comm = null 这个查询可以成功吗？

主语句、子语句操作的可以不是同一张表。

涉及问题: 数据完整性问题——约束。 (插入、更新、删除都可能造成表数据的变化)

约束：主键约束、外键约束、唯一约束、非空约束。 (简单了解。后面约束章节讲解)

删除数据delete

SQL> ?delete from?emp10 where empno=7782;（或ename= 'KING'） 如不加“where”会将整张表删除掉。

“from”关键字在Oracle中可以省略不写，但MySQL中不可以。

delete 和 truncate的区别：

1. delete 逐条删除表“内容”，truncate 先摧毁表再重建。

(由于delete使用频繁，Oracle对delete优化后delete快于truncate)

2. delete 是DML语句，truncate 是DDL语句。

DML语句可以闪回(flashback)，DDL语句不可以闪回。

???（闪回： 做错了一个操作并且commit了，对应的撤销行为。了解）

3. 由于delete是逐条操作数据，所以delete会产生碎片，truncate不会产生碎片。

（同样是由于Oracle对delete进行了优化，让delete不产生碎片）。

两个数据之间的数据被删除，删除的数据——碎片，整理碎片，数据连续，行移动 【图示】

4. delete不会释放空间，truncate 会释放空间

用delete删除一张10M的表，空间不会释放。而truncate会。所以当确定表不再使用，应truncate

5. delete可以回滚rollback， truncate不可以回滚rollback。

delete和truncate的时效性

【做实验sql.sql】：验证delete和truncate的时效性。 终端里@c:\sql.sql 可以执行脚本sql.sql???

语句执行时间记录开关：set timing on/off

回显开关：set feedback on/off

【测试步骤】： 1. 关闭开关： SQL> set timing off; SQL> set feedback off; ?

2. 使用脚本创建表： SQL> @c:\sql.sql??

3. 打开时间开关： SQL> set timing on; ?

4. 使用delete删除表内容： SQL> delete from testdelete; ?

5. 删除表： SQL> drop table testdelete purge; ?

6. 关闭时间开关： SQL> set timing off; ?

7. 使用脚本创建表： SQL> @c:\sql.sql??

8. 打开时间开关： SQL> set timing on; ?

9. 使用truncate删除表内容： SQL> truncate table testdelete; ?

???????事务

数据库事务，是由有限的数据库操作序列组成的逻辑执行单元，这一系列操作要么全部执行，要么全部放弃执行。

数据库事务由以下的部分组成:

一个或多个DML 语句

1. 一个 DDL(Data Definition Language – 数据定义语言) 语句
2. 一个 DCL(Data Control Language – 数据控制语言) 语句

?事务的特点：要么都成功，要么都失败。

事务的特性

事务4大特性(ACID)?：原子性、一致性、隔离性、持久性。

原子性?(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。

一致性?(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。

隔离性 (Isolation)：事务的执行不受其他事务的干扰，当数据库被多个客户端并发访问时，隔离它们的操 作，防止出现：脏读、幻读、不可重复读。

持久性?(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障。?

事务的起始标志：Oracle中自动开启事务，以DML语句为开启标志。

执行一个增删改查语句，只要没有提交commit和回滚rollback，操作都在一个事务中。

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

提交： 显示提交：commit?

隐式提交：1. 有DDL语句，如：create table除了创建表之外还会隐式提交Create之前所有没有提交的DML语句。 2. 正常退出(exit / quit)?

回滚： 显示回滚：rollback?

隐式回滚：掉电、宕机、非正常退出。

???????控制事务

savepoint ???

保存点（savepoint）可以防止错误操作影响整个事务，方便进行事务控制。

【示例】：1. SQL> create table testsp ( tid number, tname varchar2(20)); ?DDL语句会隐式commit之前操作

?2. set feedback on ????? 打开回显

?3. insert into testsp values(1, 'Tom') ?????

?4. insert into testsp values(2, 'Mary') ?????

?5. savepoint aaa????

?6. insert into testsp values(3, 'Moke')? 故意将“Mike”错写成“Moke”。

?7. select * from testsp? 三条数据都显示出来。

?8. rollback to savepoint aaa? 回滚到保存点aaa

?9. select * from testsp? 发现表中的数据保存到第二条操作结束的位置

需要注意，前两次的操作仍然没有提交。如操作完成应该显示的执行 commit 提交。

savepoint主要用于在事务上下文中声明一个中间标记，将一个长事务分隔为多个较小的部分，和我们编写文档时，习惯性保存一下一样，都是为了防止出错和丢失。如果保存点设置名称重复，则会删除之前的那个保存点。一旦commit之后，savepoint将失效。

隔离级别

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

SQL99定义4中隔离级别： 1. Read Uncommitted 读未提交数据。

2. Read Commited 读已提交数据。 （Oracle默认）

3. Repeatable Read 可重复读。 （MySQL默认）

这4种MySQL都支持 4. Serializable 序列化、串行化。 （查询也要等前一个事务结束）

Oracle支持的隔离级别： Read Commited（默认）和 Serializable，以及Oracle自定义的Read Only三种。

Read Only：由于大多数情况下，在事务操作的过程中，不希望别人也来操作，但是如果将别人的隔离级别设置为Serializable（串行），但是单线程会导致数据库的性能太差。是应该允许别人来进行read操作的。

创建和管理表

常见的数据库对象

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

：存储过程、存储函数、触发器、包、包体、数据库链路(datalink)、快照。(12个)

表 基本的数据存储集合，由行和列组成。

视图 从表中抽出的逻辑上相关的数据集合。

序列 提供有规律的数值。

索引 提高查询的效率

同义词 给对象起别名

???????表的基本操作

基本的数据存储集合，由行和列组成。表名和列名遵循如下命名规则：

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

创建一张表必须具备：1. Create Table的权限 2. 存储空间。我们使用的scott/hr用户都具备这两点。

SQL> ?create table?test1 (tid number, tname varchar2(20), hiredate date default sysdate) ?

default的作用是，当向表中插入数据的时候，没有指定时间的时候，使用默认值sysdate。

SQL> ?insert into test1(tid, tname) values(11, 'wangwu') ??插入时没有指定Hiredate列，取当前时间。

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

rowid：行地址 ——伪列

SQL> ?select rowid, empno, deptno from emp?? 看到该列存储的是一系列的地址(指针)，创建索引用。

分析，之前我们使用过的创建表的语句：

SQL> ?create table?emp10 ?as??select * from emp where 1=2???

??在这条语句中，“where 1=2”一定为假。所以是不能select到结果的，但是将这条子查询放到Create语句中，可以完成拷贝表结构的效果。最终emp10和emp有相同的结构。

如果，“where”给定的是一个有效的条件，就会在创建表的同时拷贝数据。如：

SQL> ?create table?emp20 ?as??select * from emp where deptno=20???

???这样emp20在创建之初就有5条数据。

创建一张表，要求包含：员工号 姓名 月薪 年薪 年收入 部门名称。

分析：根据要求，涉及emp和dept两张表(至少有一个where条件)，并且要使用表达式来计算年收入和年薪。

1. 先写出select语句： SQL> ?from emp e, dept d

???where e.deptno=d.deptno

↓

SQL> 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. 在查询语句之前加上： SQL> ?create table empincome ?as??

由于此时的“where”条件是有效的条件，就会在创建表的同时拷贝数据。

创建“视图”的语法与上边表的语法、顺序几乎完全一样，只是将“table”→“view”即可。

修改表

ALTER TABLE ??

追加一列：

SQL> ?alter?table?test1 add?image blob?? ?向test1表中加入新列 image 类型是blob

SQL> ?desc test1 ??

修改一列：

SQL> ?alter?table?test1 modify?tname varchar2(40)???将tname列的大小有20→40。

删除一列：

SQL> ?alter table?test1 drop?column?image?? ?将刚加入的新列image删除。

重命名一列：

SQL> ?alter?table?test1 rename?column?tname to?username? 将列tname重命名为username。

删除表

当表被删除：

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

SQL> ?select * from tab?? 查看当前用户下有哪些表。 拷贝保存表名。

SQL> ?drop table testsp?? 将测试保存点的表删除。

SQL> ?select * from tab?? 再次查询跟刚刚保存的表名比对，少了testsp，但多了另外一张命名复杂的表。

Oracle的回收站：

1. 查看回收站：show recyclebin??(sqlplus 命令) 那个复杂的命名即是testsp在回收站中的名字。

SQL> select * from testsp?? 这样是不能访问的。

SQL> select * from "BIN$+vu2thd8TiaX5pA3GKHsng==$0" ? 要使用“回收站中的名字”

2. 清空回收站：purge recyclebin?

SQL> drop table test1 purge? 表示直接删除表，不经过回收站。

将表从回收站里恢复，涉及“闪回”的知识，作为了解性知识点。

注意：并不是所有的用户都有“回收站”，对于没有回收站的用户（管理员）来说，删除操作是不可逆的。

重命名表

SQL> ?rename?test1?to?test8 ???? ????

Truncate Table：DDL语句 ——注意：不能回滚(rollback)

1. 1. 约束：
      1. 约束的种类

1. Not Null 非空约束

例如：人的名字，不允许为空。

2. Unique 唯一性约束

例如：电子邮件地址，不可以重复。

3. Primary Key 主键约束

通过这个列的值可以唯一的确认一行记录，主键约束隐含Not null + Unique

4. Foreign Key 外键约束

例如：部门表dept和员工表emp，不应该存在不属于任何一个部门的员工。用来约束两张表的关系。

注意：如果父表的记录被子表引用的话，父表的记录默认不能删除。解决方法：

1） 先将子表的内容删除，然后在删除父表。

2） 将子表外键一列设置为NULL值，断开引用关系，然后删除父表。

无论哪种方法，都要在两个表进行操作。所以定义外键时，可以通过references指定如下参数：

——ON DELETE CASCADE：当删除父表时，如发现父表内容被子表引用，级联删除子表引用记录。

——ON DELETE SET NULL：当发现上述情况，先把子表中对应外键值置空，再删除父表。

多数情况下，使用SET NULL方法，防止子表列被删除，数据出错。

5. Check 检查性约束

如：教室中所有人的性别；工作后薪水满足的条件。

SQL> ?create table test7

???(tid number, ?

tname varchar2(20),??

gender varchar(6) check (gender in ('男', '女')),

sal number check (sal > 0)

???) ???

check (gender in ('男', '女'))?检查插入的性别是不是‘男’或‘女’（单引号）。

check (sal > 0) 检查薪水必须是一个正数。

如果我们这样插入数据： SQL> ?insert into test7 values(1, 'Tom', '男', 1000); 正确。

但是，如果这样插入： SQL> ?insert into test7 values(2, 'Mary', '啊', 2000); ?会报错：

ORA-02290：违反检查约束条件 （SCOTT.SYS_C005523）

其中的“SYS_C005523”是约束的名字，由于在定义约束时没有显式指定，系统默认给起了这样一个名称。所以我们建议，创建约束的时候，自定义一个见名知意的约束名。

constraint：使用该关键字，来给约束起别名。

???????约束举例

【约束举例】：

SQL> ? create table student

( sid number constraint student_PK primary key, ? 学生Id主键约束

sname varchar2(20) constraint student_name_notnull not null, 学生姓名非空约束

email varchar2(20) constraint student_email_unique unique 学生邮件唯一约束

?????constraint student_email_notnull not null, 同时邮件可再设非空，没有“，”

age number constraint student_age_min check(age > 10), 学生年龄设置check约束

gender varchar2(6) constraint gender_female_or_male check(gender in ('男', '女')),

deptno number constraint student_FK references?dept (deptno)?ON DELETE SET NULL

) ??

在定义学生deptno列的时候，引用部门表的部门号一列作为外键，同时使用references设置级联操作

——当删除dept表的deptno的时候，将student表的deptno置空。

SQL> ?desc student 查看student表各列的约束。

测试用例：

SQL> ?insert into student values(1, 'Tom', 'tom@126.com', 20, '男', 10)?? 正确插入表数据。

SQL> ?insert into student values(2, 'Tom', 'tom@126.com', 15, '男', 10)???

违反student_email_unique约束。

SQL> ?insert into student values(3, 'Tom3', 'tom3@126.com', 14, '男',100 )??? 违反完整约束条件 (SCOTT.STUDENT_FK) - 未找到父项关键字

……

问题：是不是父表的所有列，都可以设置为子表的外键？作外键有要求吗？

外键：必须是父表的主键。

SQL> select constraint_name, constraint_Type, search_condition ?

??from user_constraints where table_name='STUDENT' ??

可以查看指定表（如student）的约束，注意表名必须大写。