oracle blob 长度_Oracle学习--视图、序列、权限、事务、约束

最新推荐文章于 2021-02-03 11:16:29 发布

weixin_39617473

最新推荐文章于 2021-02-03 11:16:29 发布

阅读量176

点赞数

1.视图

视图（view）被称之为虚表，即在数据库中，属于一种不存在实体的表，也可说其给实体表加上了一层窗帘，只公开部分数据，一些较为私密的数据则不允许查看访问，这样的实体表，在视图中也称为物理表（基表）。所以视图是对基表的一种数据保护，值得注意的是，通过视图进行的增删改查，将会同样作用于基表中，因为我们作用的就是基表。

语法：

create or replace view 视图名 as
query

例子：

create or replace view v$empinfo
as
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.deptno
from emp e;

1.1使用视图

使用视图的方法与使用表时的操作一样。

-- 使用视图
select * from v$empinfo;

-- 删除视图
drop view v$empinfo;

修改视图中的数据：

-- 向视图添加数据
insert into v$empinfo(empno,ename,job,mgr,hiredate,deptno)
values(1090,'cai90','singer',7839,sysdate,30);

select * from emp e;

之前提到，通过视图去操作数据，将会对基表产生影响，所以，在进行增加数据时，未显示给视图的字段将会自动加null，如果该未显示的字段设置了无法添加null值时，那么我们将无法通过视图去增加数据，系统将会提示添加失败。

例子：

insert into v$empinfo(ename,job,mgr,hiredate,deptno)
values('cai100','singer',7839,sysdate,30);

1.1.1只读视图

例子：

create or replace view v$empinfo as
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.deptno from emp e
with read only;

insert into v$empinfo(empno,ename,job,mgr,hiredate,deptno)
values(1110,'cai100','singer',7839,sysdate,30);

只读视图，顾名思义，我们只能进行查询操作，而无法进行增删改操作，这就意味着数据将得到完美的保护，操作者将无法对其作出任何破坏性操作。

1.2视图的应用

-- 平均薪水的等级最低的部门，它的部门名称是什么
select vt3.deptno, d.dname
  from (select *
          from v$AvgSalGrade VT2
         where VT2.grade = (select min(vt1.grade) from v$AvgSalGrade VT1)) VT3
  join dept d
    on vt3.deptno = d.deptno

-- 通过视图优化
create or replace view v$AvgSalGrade as
select vt0.deptno,vt0.avgsal,sg.grade
      from (select e.deptno,avg(e.sal) "AVGSAL"
              from emp e
              group by e.deptno) VT0 join salgrade sg on vt0.avgsal between sg.losal and sg.hisal
with read only

2.权限管理

在下载Oracle时，我们都知道，Scott需要进行解锁，这就是给予了开发者使用scott账户的权限。

alter user  scott  account  unlock;

此时scott如果对数据库进行DDL操作是没有权限的。把创建视图、创建表的权限分配给soctt

在赋予权限时，我们需要一个具有更高权限的账户来进行授权，那就是sysdba。

小知识：

DML（data manipulation language）：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言；

DDL（data definition language）： DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用；

DCL（Data Control Language）：是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL；

TCL - Transaction Control Language：事务控制语言，COMMIT - 保存已完成的工作，SAVEPOINT - 在事务中设置保存点，可以回滚到此处，ROLLBACK - 回滚，SET TRANSACTION - 改变事务选项。

2.1创建用户，并为其赋予相应的权限

--创建用户
create user test01 identified by 123

-- 查看是否创建成功
select * from dba_users
where username = 'TEST01';

-- 授权登录（会话）权限
grant create session to test01;

-- 默认用户没有任何表，而且不具备操作其他表的权限。
--select * from emp;

-- 授权soctt.emp所有权限(all)给test01
grant all on scott.emp to test01;
-- 回收权限
revoke all on scott.emp from test01;

-- 分配创建表的权限
grant create table to test01;

-- 此时test01用户可以select，但不能insert数据
grant unlimited tablespace to test01;


-- 修改用户密码
alter user test01 identified by 1234;

-- 级联删除用户
drop user test01 cascade;

赋予权限的语法：

•1.GRANT 赋于权限
常用的系统角色权限集合有以下三个:
CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)
•
常用的数据对象权限有以下五个:
ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名,
DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名

GRANT CONNECT, RESOURCE TO 用户名;
GRANT SELECT ON 表名 TO 用户名;
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;

2.REVOKE 回收权限
REVOKE CONNECT, RESOURCE FROM 用户名;
REVOKE SELECT ON 表名 FROM 用户名;
REVOKE SELECT, INSERT, DELETE ON 表名 FROM 用户名1, 用户名2;

查看用户的权限：

select * from user_sys_privs;

3.表

3.1数据库的数据类型

其他数据类型：（仅供吹水使用）

CLOB：最大长度4G -->大对象很少使用:如果存在大对象，一般的解决方案存入文件地址（地址为程序所在应用服务器的相对路径）。

BLOB：存二进制文件

面对这样的大文件，我们没必要将其全部存放在数据库，如果全都存在数据库中，会让数据库有一定的负担，且并没有多大好处，所以我们一般会将关于这些大文件的地址存在数据库中，即可调用出这些大文件。

3.2创建表

语法：

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

例子：

创建一个学生表

T_STUINFO(sid,name,phone,gender,birthday,address)

create table t_stuinfo(
   sid number(4),
   name varchar2(20),
   phone char(11),
   gender number(1),
   birthday date,
   address varchar2(100)
)

亦可通过开发工具生成上述程序。

以下是开发工具生成的代码。

create table T_STUINFO
(
  sid      NUMBER(4) not null,
  name     VARCHAR2(20) not null,
  phone    CHAR(11),
  gender   NUMBER(1) default 1 not null,
  birthday DATE,
  address  VARCHAR2(100)
)

也可通过子查询来获得子表的表结构。

-- 通过其他表结构创建表
create table t_emp
as 
select * from emp;

-- 只创建表的结构(复制表结构)
create table t_emp2
as
select * from emp where 1=2;

select * from t_emp;
select * from t_emp2;

3.3表修改

-- 修改表操作
-- [1]给表添加字段
alter table t_stuinfo add grade number(2)

-- [2]删除表的字段
alter table t_stuinfo drop column grade 

-- [3] 修改表字段
alter table t_stuinfo modify(address varchar2(150))

-- [4]重命名
rename t_stuinfo to t_stuinfo2

通常我们不会使用这样的方式去修改表，因为其字段及记录将会分布在各个范围内，如果修改了一个表中的字段，将会产生大量的后续工作，所以，我们在创建了表之前，就需要明确知道使用哪些字段，才能更好地避免无谓的加班。

3.4表的增删改

3.4.1 insert （增）

语法：

INSERT INTO	table [(column [, column...])]
VALUES		(value [, value...]);

-- insert
insert into t_emp2(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(1010,'cai10','singer',7938,sysdate,1000,1,10)

insert into t_emp2(empno,job,ename,mgr,hiredate,sal,comm,deptno)
values(1010,'singer','cai10',7938,sysdate,1000,1,10)
-- insert是事务操作，需要提交事务。

insert into t_emp2
values(1020,'cai20','singer',7938,sysdate,2000,2,10)

3.4.2 update（改）

语法：

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

update t_emp2
set ename = 'cai22',sal = 2200
where empno = 1020

3.4.3 delete （删）

语法：

DELETE [FROM]	table
[WHERE	condition];

-- delete
delete from t_emp2
where empno = 1010

--删除表中的所有数据-没有事务-速度快
truncate table t_emp2;

4.序列

序列是Oracle专有的对象，只有在Oracle中可以使用序列对象，它用来产生一个自动递增的数列。

-- 创建序列
create sequence seq_empno
start with 1
increment by 1

-- 序列的使用
-- 序列中的下一个值，从定义(start with)的值开始
select seq_empno.nextval from dual;
-- 获取序列的当前值
select seq_empno.currval from dual;

-- 序列的应用
select * from t_emp2;
insert into t_emp2
values(seq_empno.nextval,'cai10','singer',7938,sysdate,1000,1,10)

在数据库开发设计表时，如果需要一个字段的值是自增的话，优先考虑序列。

5.事务

事务（Transaction）是一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位。

事务是为了保证数据库的完整性。

事务不能嵌套

有点类似于Java中的线程同步安全操作。

在oracle中，没有事务开始的语句。一个Transaction起始于一条DML(Insert、Update和Delete )语句，结束于以下的几种情况：

• 用户显式执行Commit语句提交操作或Rollback语句回退。

• 当执行DDL(Create、Alter、Drop)语句事务自动提交。

• 用户正常断开连接时，Transaction自动提交。

• 系统崩溃或断电时事务自动回退。

-- beginTransaction(insert/update/delete)
insert into t_emp2
values(6,'cai40','singer',7938,sysdate,4000,4,10);
insert into t_emp2
values(7,'cai50','singer',7938,sysdate,5000,5,10);

-- 显示的事务结束（endTransaction）
-- commit;
rollback;

-- beginTransaction(insert/update/delete)
insert into t_emp2
values(6,'cai40','singer',7938,sysdate,4000,4,10);
insert into t_emp2
values(7,'cai50','singer',7938,sysdate,5000,5,10);
--【2】隐式的事务结束
create table abc(
   sid number
)

如果将这段代码放在java中就变成了：

try

5.1保存点

-- beginTrans
insert into t_emp2 values(9,'cai40','singer',7938,sysdate,4000,4,10);
insert into t_emp2 values(10,'cai50','singer',7938,sysdate,5000,5,10);
select * from t_emp2;

savepoint sp1;

insert into t_emp2 values(11,'cai40','singer',7938,sysdate,4000,4,10);
insert into t_emp2 values(12,'cai50','singer',7938,sysdate,5000,5,10);
select * from t_emp2;

rollback to sp1;

commit;

相当于一个存档，在 savepiont sp1 之下的某行代码如果出现了错误，则可使用 rollback to来返回sp1的保存点，而不用再从头开始添加数据。

save point 保持当前数据库的状态点。以便后续通过rollback回滚到指定状态点。

在java中展现的形式如下：

try

5.2事务的特性

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

1. 原子性（Atomicity）

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

2. 一致性（Consistency）

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

3. 隔离性（Isolation）

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

4. 持久性（Durability）

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

6.约束

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

常见约束:

1. NOT NULL 非空

2. UNIQUE Key 唯一键

3. PRIMARY KEY 主键

4. FOREIGN KEY 外键

5. CHECK 自定义检查约束

6.1主键约束（primary key）

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

列级约束：

-- 创建表
-- 创建列级约束-显式指定名称，pk_sid
create table t_stuInfo(
     sid number(4) constraint pk_sid primary key,
     name varchar2(20)
)

-- 创建列级约束-没式显示指定名称，系统随机命名SYS_C..
create table t_stuInfo2(
     sid number(4) primary key,
     name varchar2(20)
)

表级约束：

-- 创建表，以表级约束
create table t_stuInfo3(
   sid number(4),
   phone char(11),
   name varchar2(20),
   constraint pk_stuinfo primary key(phone,name)
)

create table t_stuInfo4(
   sid number(4),
   phone char(11),
   name varchar2(20),
   primary key(phone,name)
)

6.2非空约束（NOT NULL）

可以确保该字段不能为null

注意：非空约束，只能在列级定义

-- 创建列级约束-显式指定名称，pk_sid
create table t_stuInfo5(
     sid number(4) primary key,
     phone char(11) constraint nn_phone not null
)

create table t_stuInfo5(
     sid number(4) primary key,
     phone char(11) not null
)

-- 添加操作
insert into t_stuinfo5(sid)
values(1000)

5.3唯一性约束（UNIQUE）

唯一性约束条件确保所在的字段或者字段组合不出现重复值

唯一性约束条件的字段允许出现空值

Oracle将为唯一性约束条件创建对应的唯一性索引

create table t_stuInfo6(
     sid number(4) primary key,
     phone char(11) constraint uq_phone unique 
)

create table t_stuInfo6(
     sid number(4) primary key,
     phone char(11) unique
)

create table t_stuInfo6(
     sid number(4) primary key,
     phone char(11) unique not null
)

drop table t_stuInfo6;
create table t_stuInfo6(
     sid number(4) primary key,
     phone char(11),
     constraint uq_phone unique(phone)
)

insert into t_stuinfo6(sid,phone)
values(1000,'18612340000')
insert into t_stuinfo6(sid,phone)
values(1001,'18612340000')

6.4自定义约束（Check）

我们可以自定义一些约束条件来对属性值加以限制

语法：

create table emp3
(	id number(4) primary key,
	age number(2)  check(age > 0 and age < 100),
	salary number(7,2),
	sex char(1),
	constraint salary_check check(salary > 0)
)

create table t_stuInfo7(
     sid number(4) primary key,
     phone char(11) unique,
     age number(3) check(age>0 and age<100)
)

insert into t_stuInfo7
values(1000,'18612341234',-10)

6.5外键约束

create table t_stuInfo8(
     sid number(4) primary key,
     phone char(11) unique,
     tid number(4),
     constraint fk_tid foreign key(tid) references t_teacher1(tid)
)

create table t_teacher1(
     tid number(4) primary key,
     name varchar2(20) not null
)

insert into t_teacher1
values(1,'alex');

insert into t_stuInfo8
values(1000,'18612341234',1)

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

1. RESTRICT方式：只有当依赖表中没有一个外键值与要删除的主表中主键值相对应时，才可执行删除操作。

2. CASCADE方式：将依赖表中所有外键值与主表中要删除的主键值相对应的记录一起删除

3. SET NULL方式：将依赖表中所有与主表中被删除的主键值相对应的外键值设为空值

FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)

[ON DELETE [CASCADE|SET NULL]] 如省略on短语，缺省为第一种处理方式。

drop table t_stuinfo8;
create table t_stuInfo8(
     sid number(4) primary key,
     phone char(11) unique,
     tid number(4),
     constraint fk_tid foreign key(tid) references t_teacher1(tid) 
)

create table t_stuInfo8(
     sid number(4) primary key,
     phone char(11) unique,
     tid number(4),
     constraint fk_tid foreign key(tid) references t_teacher1(tid) on DELETE CASCADE
)

create table t_teacher1(
     tid number(4) primary key,
     name varchar2(20) not null
)

insert into DELETE
values(1,'alex');

insert into t_stuInfo8
values(1000,'18612341234',1)

select * from t_stuInfo8
delete from t_teacher1 where tid = 1;