二、Oracle(2)

作者: 木鱼_cc | 来源:发表于2018-08-09 00:29 被阅读0次

8.数据处理

8.1SQL语言的类型

数据库中，称呼增删改查，为DML语句（Data Manipulation Language 数据操纵语言）,就是指代insert、update、delete、select这四个操作
DDL语言。（Data Definition Language数据定义语言）。如truncate table(阶段/清空一张表)、create table(表)、create view(视图)、create index(索引)、create sequence(序列)、create synonym(同义词)、alter table、drop table
DCL语句。DCL(Data Control Language数据控制语言)如：commit（提交）、rollback(回滚)

8.2插入数据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);

8.2.1“&”地址符

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的值，可以查看不同表

8.2.2批处理

一次插入多条数据

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的任何语句中，不只是查询套查询

8.3更新数据update

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

SQL>update emp10 set sal=4000,comm=300 where ename='CLARK';
注意：若没有where限定，那么会将所有员工的sal都设置成4000,comm设置成300
SQL>update emp set comm=null where empno=1000;#这个操作是否能成功？
SQL>select * from emp where comm=null;#这个查询能成功吗？

主语句、子语句操作的可以不是同一张表
涉及问题：数据完整性问题——约束（插入、更新、删除都可能造成数据的变化）

约束：主键约束、外键约束、唯一约束、非空约束

8.4删除数据delete

SQL>delete from emp10 where empno=7782;如不加“where”会将整张表删掉
"from"关键字Oracle中可以忽略不写，但MySQL中不可以

8.4.1delete和truncate的区别

delete逐条删除表“内容”，truncate先摧毁表再重建
(由于delete使用频繁，Oracle对delete优化后delete快于truncate)
delete是DML语句，truncate是DDL语句
DML语句可以闪回(flashback),DLL语句不可以闪回
(闪回：做错一个操作并且commit了，对应的撤销行为）
由于delete是逐条操作数据，所以delete会产生碎片，truncate不会产生碎片。
（同样是由于Oracle对delete进行了优化，让delete不产生碎片）
两个数据之间的数据被删除，删除的数据——碎片，整理碎片，数据连续，行移动
delete不会释放空间，truncate会释放空间
用delte删除一张10M的表，空间不会释放，而truncate会。所以当确定表不再使用，应truncate
delete可以回滚rollback,truncate不可以回滚rollback

8.5事务

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

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

一个或多个DML语句
一个DDL(Data Definition Language-数据定义语言)语句
一个DCL(Data Control Language-数据控制语言)语句

事务的特点L要么都成功，要么都失败

8.5.1事务的特性

事务4大特性（ACID）：原子性、一致性、隔离性、持久性（定义自己查书，偶尔考点）

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

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

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

提交：

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

回滚：

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

8.5.2控制事务

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

savepoint

SQL>create table testsp(tid number,tname varchar2(20));#DDL语句胡隐式commit之前的操作
set feedback on   #打开回显
insert into testsp values(1,'Tom');
insert into testsp values(2,'Mary');
savepoint aaa;
insert into testsp values(3,'Moke');#故意将'Mike'错写成'Moke'
select * from testsp; #三条数据都显示出来
rollback to savepoint aaa; #回滚到保存点aaa
select * from testsp;#发现表中的数据保存到第二条操作结束的位置

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

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

9.创建和管理表

9.1常见的数据库对象

数据库对象：表、试图、索引、序列、同义词（存储过程、存储函数、触发器、包、包体、数据库链路(datalink)、快照）

表     基本的数据存储集合，由行和列组成
视图   从表中抽出的逻辑上相关的数据集
序列   提供有规律的数值
索引   提高查询的效率
同义词  给对象起别名

9.2表的基本操作

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

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

9.2.1创建表

创建一张表必须具备

Create Table的权限
存储空间。我们使用的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”即可

9.2.2修改表

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

9.2.3删除表

当表被删除时

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

SQL>select * from tab  #查看当前用户下有哪些表  拷贝保存表名
SQL>drop table testsp  #将测试保存点的表删除
SQL>select * from tab  #再次查询更刚刚保存的表明比对，少了testsp,但是多了另外一张命名复杂的表

少了testsp,但是多了另外一张命名复杂的表

Oracle回收站

1.查看回收站：show recyclebin(sqplus命令) 哪个复杂的命名即使testsp在回收站中的名字
SQL>select * from testsp   这样是不能访问的
SQL>select * from "BIN$+vu2thd8TiaX5pA3GKHsng==$0"  
要使用“回收站中的名字”

2.清空回收站:purge recyclebin
SQL>drop table test1 purge  表示直接删除表，不经过回收站

将表从回收站里恢复，设计“闪回”的只是，作为了解性知识点

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

9.2.4重命名表

SQL>rename test1 to test8
Truncate Table:DDL语句 ——注意：不能回滚（rollback）

9.3约束

9.3.1约束的种类

Not Null 非空约束
例如：人的名字，不允许为空
Unique 唯一性约束
例如：电子邮件地址，不允许重复
Primary Key 主键约束
通过这个列的值可以唯一的确认一行记录，主键约束隐含Not Null + Unique
Foreign Key 外键约束
例如：部门表dept和员工表emp,不应该存在不属于任何一个部门的员工。用来约束两张表的关系
注意：如果父亲的记录被子表引用的话，父表的记录默认不能删除。解决方法
1）先将子表的内容删除，然后再删除父表
2）将子表外键一列设置为NULL值，断开引用关系，然后删除父表

无论哪种方法，都要在两个表进行操作。所以定义外键时，可以通过references制定如下记录
——ON DELTE 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) 会报错

9.3.2

【约束举例】

SQL>create table student
    ( sid number constraint student_PK primary key,  #学生id主键约束，约束名student_PK
      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);#唯一性约束报错 
SQL>insert into student values(3,'Tom3','tom3@126.com',14,'男',100);#完整性约束错误，没有100部门

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

SQL>select constraint_name,constraint_Type,search_condition 
    from user_constraints where table_name='STUDENT';
可以查看指定表（如student）的约束，注意表明必须大写

10其他数据库对象

10.1视图：

原理：《数据库系统概论》P119
Oracle中创建视图和创建表的操作几乎一样

10.2序列

可以理解成数组：默认，从[1]开始，长度[20] [1,2,3,4,5,....,20] 在内存中

由于序列式被保存在内存中，访问内存的速率要高于访问硬盘的速率。所以序列可以提高效率

10.2.1序列的使用

初始状态下：指针*指向1前面的位置。欲取出第一个值，应该将*向后移动。每取出一个值指针都向后移
常常用序列来指定表中的主键
创建序列:create sequence myseq来创建一个序列

创建序列

CREATE SEQUENCE sequence
       [INCREMENT BY n]
       [START WITH n]
       [{MAXVALUE n|NOMAXVALUE}]
       [{MINVALUE n|NOMINVALUE}]
       [{CYCLE | NOCYCLE}]
       [{CACHE n|NOCHACHE}]

NOCACHE表示没有缓存，一次不产生20个，而只产生一个

10.2.2创建序列

创建序列、表，以备后续测试使用

SQL>create sequence myseq  按默认属性创建一个序列
SQL>create table tableA
   (tid number,
    tname varchar2(20))
tid作为主键，准备使用序列来向表中插入值

10.2.3序列的属性

每个序列都有两个属性

NextVal必须在CurrVal之前被指定。因为初始状态下，CurrVal指向1前面的位置，无值

对于新创建的序列使用
SQL>select myseq.currval from dual  #得到出错
但
SQL>select myseq.nextval from dual #可以得到序列的第一个值1
此时再执行
SQL>select myseq.currval from dual #currval的值也得到1

使用序列给tableA表创建主键tid
SQL>insert into tableA values(myseq.nextval,'aaa')
只有nextval取完会向后移动，使用currval不会移动

SQL>insert into tableA values(myseq.nextval,'bbb')
继续使用nextval向表中添加主键tid

10.3索引

索引，相当于书的目录，提高数据检索速度。提高效率（视图不可以提高效率）

一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中
索引被删除或损坏，不会对表产生影响，其影响的只是查询的速度
索引一旦建立，Oracle管理系统会对其进行自动维护，而且由Oracle管理系统决定何时使用索引，用户不用再查询语句中指定使用哪个索引
在删除一个表时，所有基于该表的索引会自动被删除
通过指针加速Oracle服务器的查询速度
通过快速定位数据的方法，减少磁盘I/O

索引表

上图中：

emp表中保存数据，其中包含部门号列。有10号部门，有20部门员工
当select * from emp where deptno=10的时候。由于10号部门员工不连续，没规律。为了提高访问速度，可以在数据库中，依照rowid给deptno列建立索引
SQL>create index myindex on emp(deptno)
这样就建立了“索引表”可以通过rowid保存的行地址快速的找到表中数据。即使表中数据不连续
建立了索引以后，如果再执行select语句的时候，会先检查表上是否有索引表。如果有，可以通过有规律的rowid找到不联系的数据
Oracle的数据库中，索引有B树索引（默认）和位图索引两种
使用create index 索引表名 on 表明(列名1，列名2...);来创建索引表。由数据库自动进行维护

使用主键查询数据最快捷，因为主键本身就是“索引”，所以检索比较快

索引使用的场景

以下情况可以创建索引

列中数据值分布范围很广
列经常在where子句或链接条件中出现
表经常被访问而且数据量大，访问的数据大概数据总量的2%到4%

下列情况不要创建索引

表很小
列不经常作为链接条件或出现在where子句中
查询的数据大于2%到4%
表经常更新

删除索引:SQL>drop index myindex;

10.4synonym同义词

就是指表的别名

如：scott用户想访问hr用户下的表employees。默认是不能访问的。需要hr用户为scott用户授权

SQL>sqplus hr/hr
SQL>grant select on employees to scott #hr用户为scott用户开放了employees表的查询权限
SQL>select count(*) from hr.employees   (若用户名叫zhangsanfeng则zhangsanfeng.employees)

hr.employees名字过长，为了方便操作，scott用户为它重设别名
SQL>create synonym hremp for hr.employees; #为hr.employees创建同义词

如有权限限制，那么切换管理员登陆，给scott用户添加设置同义词权限
SQL>conn /as sysdba
SQL>grant create sysnonym to scott
SQL>select count(*) from hremp #使用同义词进行表查询操作

——同义词、视图等用法在数据保密要求比较高的机构使用广泛，如银行机构。好处是既不影响对数据的操作，同时又保证数据的安全