Oracle - 数据库对象

1 权限管理

权限性操作都要以 sysdba 什么来操作。

如何创建一个用户并授予一定权限？

 1 --创建用户
 2 create user test01 identified by 123
 3 
 4 -- 查看是否创建成功
 5 select * from dba_users
 6 where username = 'test01';
 7 
 8 -- 授权登录（会话）权限
 9 grant create session to test01;
10 
11 -- 默认用户没有任何表，而且不具备操作其他表的权限。
12 --select * from emp;
13 
14 -- 授权 soctt.emp 所有权限(all)给 test01，在此之后 test01 可以对 scott 下的 emp 表操作
15 grant all on scott.emp to test01;
16 -- 回收权限
17 revoke all on scott.emp from test01;
18 
19 -- 分配创建表的权限
20 grant create table to test01;
21 
22 -- 此时 test01 用户可以建表，可以 select，但不能 insert 数据，需要给予表空间
23 grant unlimited tablespace to test01;
24 
25 -- 修改用户密码
26 alter user test01 identified by 1234;
27 
28 -- 级联删除用户
29 drop user test01 cascade;
30 
31 -- 查看用户权限
32 select * from user_sys_privs;

2 视图

视图(view)，称为虚表，在数据库中不存在实体。视图本质上是对物理表(基表)的一种数据保护，让开发者或者用户只能看到基表中的部分数据。

2.1 创建视图

1 create or replace view v$empinfo as
2 select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.deptno
3 from emp e;
4 
5 --还可以创建只读视图
6 create or replace view v$empinfo as
7 select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.deptno from emp e
8 with read only;

2.2 使用视图

在使用视图时，可以把视图当作一个表来使用。

1 -- 使用视图
2 select * from v$empinfo;
3 
4 -- 删除视图
5 drop view v$empinfo;
6 
7 -- 向视图添加数据
8 insert into v$empinfo(empno,ename,job,mgr,hiredate,deptno)
9 values(1090,'cai90','singer',7839,sysdate,30);

通过视图添加数据，数据最终添加到基本中，因为视图是虚表。视图一般只是基表的部分数据，通过视图向基表添加数据时，基本的数据只能添加一部分，此时基表会对未提供的字段置null。如果基表对未提供的字段要求不能为null，此次添加会失败。

1 -- 删除数据
2 delete from v$empinfo where empno = 1090
3 
4 -- 更新数据
5 update v$empinfo set comm = 20
6 where empno = 1080

3 表

数据库数据类型

• number(x,y) - 数值型，最长是x位，y位小数
• varchar2(maxlength) - 变长字符串，maxlength这个参数的上限是32767字节
• char(max_length) - 定长字符串，最大2000字节
• date - 日期时间，只能精确到秒。
• timestamp - 时间戳，精确到微秒
• long - 长字符串，最大支持2gb

其他类型:

• clob - 大文件，最大长度4g
• blob - 存二进制文件

在数据库设计时,如果要存大文件(视频，音频等)，一定不要用blob/clob,通用的解决方案都是文件的地址。

3.1 表的创建

创建表的语法

1 create table [schema.]table(
2 column datatype [default expr] , …
3 );

例如直接创建一个学生表

1 create table t_stuinfo(
2    sid number(4),
3    name varchar2(20),
4    phone char(11),
5    gender number(1),
6    birthday date,
7    address varchar2(100)
8 );

通过子查询结果创建表

1 -- 通过其他表结构创建表
2 create table t_emp
3 as 
4 select * from emp;
5 
6 -- 只创建表的结构(复制表结构)
7 create table t_emp2
8 as
9 select * from emp where 1=2;

3.2 表的修改

对表的属性修改

 1 -- [1]给表添加字段
 2 alter table t_stuinfo add grade number(2)
 3 
 4 -- [2]删除表的字段
 5 alter table t_stuinfo drop column grade 
 6 
 7 -- [3] 修改表字段属性
 8 alter table t_stuinfo modify(address varchar2(150))
 9 
10 -- [4]重命名
11 rename t_stuinfo to t_stuinfo2

对表的内容修改

• insert - 添加行

1 insert into t_emp2(empno,ename,job,mgr,hiredate,sal,comm,deptno)
2 values(1010,'cai10','singer',7938,sysdate,1000,1,10);
3 -- insert是事务操作，需要提交事务。
4 
5 -- 按顺序添加可忽略字段名
6 insert into t_emp2
7 values(1020,'cai20','singer',7938,sysdate,2000,2,10);

• update - 修改表数据

1 update t_emp2
2 set ename = 'cai22',sal = 2200
3 where empno = 1020;

• delete - 删除行

1 delete from t_emp2
2 where empno = 1010;
3 
4 --删除表中的所有数据-没有事务-速度快
5 truncate table t_emp2;

4 序列

序列是oracle专有的对象，它用来产生一个自动递增的数列。在数据库开发设计表时，如果需要一个字段的值是自增的话，优先考虑序列。

1 -- 创建序列
2 create sequence seq_empno
3 start with 1       --初始值
4 increment by 1;    --增量
5 
6 -- 序列中的下一个值，从定义(start with)的值开始，调用了 nextval 之后， currval 会变成 nextval 的值
7 select seq_empno.nextval from dual;
8 -- 获取序列的当前值
9 select seq_empno.currval from dual;

5 事务

5.1 概念

事务（transaction）是一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位。事务是为了保证数据库的完整性。

在 oracle 中，没有事务开始的语句。一个 transaction 起始于一条 dml (insert、update和delete )语句，结束于以下的几种情况：

1. 用户显式执行 commit 语句提交操作或 rollback 语句回退。
2. 当执行 ddl(create、alter、drop) 语句事务自动提交。
3. 用户正常断开连接时，transaction 自动提交。
4. 系统崩溃或断电时事务自动回退。

5.2 保存点（save point）

可以在事务中添加保存点，再通过 rollback sp 的方式回滚到保存点的位置再提交事务，保留有效的工作进度。

 1 -- begintrans
 2 insert into t_emp2 values(9,'cai40','singer',7938,sysdate,4000,4,10);
 3 insert into t_emp2 values(10,'cai50','singer',7938,sysdate,5000,5,10);
 4 select * from t_emp2;
 5 
 6 savepoint sp1;
 7 
 8 insert into t_emp2 values(11,'cai40','singer',7938,sysdate,4000,4,10);
 9 insert into t_emp2 values(12,'cai50','singer',7938,sysdate,5000,5,10);
10 select * from t_emp2;
11 
12 rollback to sp1;
13 
14 commit;

5.3 事务的特性

事务四大特征：原子性，一致性，隔离性和持久性。

• 原子性（atomicity）

一个原子事务要么完整执行，要么干脆不执行。这意味着，工作单元中的每项任务都必须正确执行。如果有任一任务执行失败，则整个工作单元或事务就会被终止。即此前对数据所作的任何修改都将被撤销。如果所有任务都被成功执行，事务就会被提交，即对数据所作的修改将会是永久性的。

• 一致性（consistency）

一致性代表了底层数据存储的完整性。它必须由事务系统和应用开发人员共同来保证。事务系统通过保证事务的原子性，隔离性和持久性来满足这一要求; 应用开发人员则需要保证数据库有适当的约束(主键，引用完整性等)，并且工作单元中所实现的业务逻辑不会导致数据的不一致(即，数据预期所表达的现实业务情况不相一致)。例如，在一次转账过程中，从某一账户中扣除的金额必须与另一账户中存入的金额相等。支付宝账号100 你读到余额要取，有人向你转100 但是事物没提交（这时候你读到的余额应该是100，而不是200） 这种就是一致性

• 隔离性（isolation）

隔离性意味着事务必须在不干扰其他进程或事务的前提下独立执行。换言之，在事务或工作单元执行完毕之前，其所访问的数据不能受系统其他部分的影响。

• 持久性（durability）

持久性表示在某个事务的执行过程中，对数据所作的所有改动都必须在事务成功结束前保存至某种物理存储设备。这样可以保证，所作的修改在任何系统瘫痪时不至于丢失。

6 约束

当我们创建表的时候，同时可以指定所插入数据的一些规则，比如说某个字段不能为空值，某个字段的值（比如年龄）不能小于零等等，这些规则称为约束。约束是在表上强制执行的数据校验规则。

常见约束:

1. not null 非空
2. unique key 唯一键
3. primary key 主键
4. foreign key 外键
5. check 自定义检查约束

6.1 主键约束

主键用于唯一标识一条记录。主键值不可为空，也不允许出现重复。

 1 -- 创建列级约束-显式指定名称，pk_sid
 2 create table t_stuinfo(
 3      sid number(4) [constraint pk_sid] primary key,    --约束规则名可以省略
 4      name varchar2(20)
 5 );
 6 
 7 
 8 -- 当对多个字段进行约束的时候（除了非空约束，其它约束都可以采用这种方式，也只有这种方式可以选择两个主键）
 9 create table t_stuinfo3(
10    sid number(4),
11    phone char(11),
12    name varchar2(20),
13    [constraint pk_stuinfo] primary key(phone,name)
14 );

6.2 非空约束

确保字段值不允许为空，只能在列级定义。

1 create table t_stuinfo5(
2      sid number(4) primary key,
3      phone char(11) [constraint nn_phone] not null
4 );

6.3 唯一性约束

唯一性约束条件确保所在的字段或者字段组合不出现重复值，唯一性约束条件的字段允许出现空值，但只能出现一个。oracle将为唯一性约束条件创建对应的唯一性索引。

1 create table t_stuinfo6(
2      sid number(4) primary key,
3      phone char(11) [constraint uq_phone] unique 
4 );

6.4 自定义约束

check约束用于对一个属性的值通过特定条件加以限制。

 1 -- 不显示设定约束名
 2 create table t_stuinfo7(
 3      sid number(4) primary key,
 4      phone char(11) unique,
 5      age number(3) check(age>0 and age<100)
 6 );
 7 
 8 -- 显示设定约束
 9 create table emp3(
10      id number(4) primary key,
11     age number(2)  check(age > 0 and age < 100),
12     salary number(7,2),
13     sex char(1),
14     constraint salary_check check(salary > 0)
15 );

6.5 外键约束

当表中的某个字段和另外一个表的主键字段相互关联时，可以设定外键约束。从键受主键的约束，只能设为主键中拥有的值。

1 create table t_stuinfo8(
2      sid number(4) primary key,
3      phone char(11) unique,
4      tid number(4),
5      constraint fk_tid foreign key(tid) references t_teacher1(tid)
6 );

对于主表的删除和修改主键值的操作，会对依赖关系产生影响，以删除为例：当要删除主表的某个记录，即删除一个主键值，那么对依赖的影响可采取下列3种做法：

1. restrict方式：只有当依赖表中没有一个外键值与要删除的主表中主键值相对应时，才可执行删除操作。
2. cascade方式：将依赖表中所有外键值与主表中要删除的主键值相对应的记录一起删除
3. set null方式：将依赖表中所有与主表中被删除的主键值相对应的外键值设为空值

可以在建表时对外键约束的删除规则进行设定，若不加以设定，默认采用第一种方式。

1 --foreign key (deptno) references dept(deptno) [on delete [cascade|set null]]
2 -- cascade 的例子
3 create table t_stuinfo8(
4      sid number(4) primary key,
5      phone char(11) unique,
6      tid number(4),
7      constraint fk_tid foreign key(tid) references t_teacher1(tid) on delete cascade
8 );