oracle创建视图以及如何创建表

Question

如题所述

Answer 1

视图
视图称为虚表，在数据库中不存在实体。

试图本质上对物理表的一种数据保护，让开发者或者用户只能看到局部数据。



创建视图 形式：
create or replace view as

selecr e.empno,e.ename,e.job,e.mgr,e.hiredate,e.deptno



fromemp e;

使用视图也可以进行一些数据的增 删 改 查 ，但通过视图添加的数据，数据最终添加到物理表中，因为视图是虚表。

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

添：形式：insert into view ( 字段名1.。) values (对应的类型)。

删： 形式： delete from view ,加条件 where : empno = 10;

跟新（改）： 形式： update view set comm =20(一定是视图中存在的字段)，where empno =10;

特殊情况：试图也有只读，后缀： with read only,不能修改数据。

权限管理
第一个使用scott账户是需要解锁

alter user scott account unlock;

首先得登录sysdba才能操作，

创建用户

createusertest01 identifiedby123



-- 查看是否创建成功

select* fromdba_users

whereusername = ‘TEST01‘;



-- 授权登录（会话）权限

grantcreatesessiontotest01;



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

--select * from emp;



-- 授权soctt.emp所有权限(all)给test01

grantallonscott.emp totest01;

- 回收权限

revokeallonscott.emp fromtest01;



-- 分配创建表的权限

grantcreatetabletotest01;



-- 此时test01用户可以select，但不能insert数据

grantunlimitedtablespacetotest01;





-- 修改用户密码

alterusertest01 identifiedby1234;



-- 级联删除用户

dropusertest01 cascade;

1.GRANT 赋于权限

常用的系统角色权限集合有以下三个:CONNECT(基本的连接),RESOURCE(程序开发),DBA(数据库管理)
常用的数据对象权限有以下五个:ALLON数据对象名,SELECTON数据对象名,UPDATEON数据对象名,DELETEON数据对象名,INSERTON数据对象名,ALTERON数据对象名GRANTCONNECT,RESOURCETO用户名;GRANTSELECTON表名TO用户名;GRANTSELECT,INSERT,DELETEON表名TO用户名1,用户名2;2.REVOKE回收权限REVOKECONNECT,RESOURCEFROM用户名;REVOKESELECTON表名FROM用户名;REVOKESELECT,INSERT,DELETEON 表名FROM用户名1,用户名2;
select * from user_sys_privs;

表
数据库数据类型

number(x,y)

数值型

最长是x位，y位小数

varchar2(maxlength）

变长字符串

maxlength这个参数的上限是32767字节

char(max_length)

定长字符串

最大2000字节

Date

日期时间

只能精确到秒。

timestamp

时间戳

精确到微秒

long

长字符串

最大支持2GB



其他类型:

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

BLOB：存二进制文件



注意:

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

表的创建
创建表的语法：

CREATE TABLE [schema.]table(

column datatype [DEFAULT expr] , …

);

例如：

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

createtablet_stuinfo(

sidnumber(4),

namevarchar2(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)

)



通过子查询结果创建表



- 通过其他表结构创建表

createtablet_emp

as

select* fromemp;



-- 只创建表的结构(复制表结构)

createtablet_emp2

as

select* fromemp where1=2;



select* fromt_emp;

select* fromt_emp2;



表的修改


- 修改表操作

-- [1]给表添加字段

altertablet_stuinfo addgrade number(2)



-- [2]删除表的字段

altertablet_stuinfo dropcolumngrade



-- [3] 修改表字段

altertablet_stuinfo modify(address varchar2(150))



-- [4]重命名

renamet_stuinfo tot_stuinfo2



insert
语法

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

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



insert

insertintot_emp2(empno,ename,job,mgr,hiredate,sal,comm,deptno)

values(1010,‘cai10‘,‘singer‘,7938,sysdate,1000,1,10)



- insert是事务操作，需要提交事务。



update
update语法



UPDATE table

SET column= value[, column = value] …

[WHERE condition];



updatet_emp2

setename = ‘cai22‘,sal = 2200

whereempno = 1020



1.1.1delete
delete 语法

DELETE [FROM] table

[WHERE condition];



-- delete

deletefromt_emp2

whereempno = 1010



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

truncatetablet_emp2;



序列
序列是oracle专有的对象，它用来产生一个自动递增的数列。

创建序列

createsequenceseq_empno

startwith1

incrementby1

序列的使用

-- 序列中的下一个值，从定义(start with)的值开始

selectseq_empno.nextval fromdual;

-- 获取序列的当前值

selectseq_empno.currval fromdual;



-- 序列的应用

select* fromt_emp2;

insertintot_emp2

values(seq_empno.nextval,‘cai10‘,‘singer‘,7938,sysdate,1000,1,10)

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



事务(A)
事务概念：
事务（transaction）是一个操作序列，这些操作要么不做，要么都做，是一个不可分割的工作单位，是数据库环境的逻辑工作单位。

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

事务不能嵌套。



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



用户显式执行Commit语句提交操作或Rollback语句回退。
当执行DDL(Create、Alter、Drop)语句事务自动提交。
用户正常断开连接时，Transaction自动提交。
系统崩溃或断电时事务自动回退。




beginTransaction(insert/update/delete)



insertintot_emp2



values(6,‘cai40‘,‘singer‘,7938,sysdate,4000,4,10);



insertintot_emp2



values(7,‘cai50‘,‘singer‘,7938,sysdate,5000,5,10);





显示的事务结束（endTransaction）

-- commit;

rollback;



- beginTransaction(insert/update/delete)

insertintot_emp2

values(6,‘cai40‘,‘singer‘,7938,sysdate,4000,4,10);

insertintot_emp2

values(7,‘cai50‘,‘singer‘,7938,sysdate,5000,5,10);

--【2】隐式的事务结束



事务结合java代码的格式



try{

insert …

…

insert …

commit

}catch(Exception e){

rollback

}finlly{

关闭数据库

}

保存点(save point)



beginTrans

insertintot_emp2 values(9,‘cai40‘,‘singer‘,7938,sysdate,4000,4,10);

insertintot_emp2 values(10,‘cai50‘,‘singer‘,7938,sysdate,5000,5,10);

select* fromt_emp2;



savepointsp1;



insertintot_emp2 values(11,‘cai40‘,‘singer‘,7938,sysdate,4000,4,10);

insertintot_emp2 values(12,‘cai50‘,‘singer‘,7938,sysdate,5000,5,10);

select* fromt_emp2;



rollbacktosp1;



commit;



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

try{

insert …

insert …

save point sp1



insert …

insert …

save point sp2



commit

}catch(AException e){

rollback

}catch(BException e){

rollback to sp1

}

finlly{

关闭数据库

}

事务的特性
第一特性,原子性：

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

第二特性，一致性：



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

第三特性， 隔离性：



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



第四特性，持久性：

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



约束


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



常见约束:

NOT NULL 非空
UNIQUE Key 唯一键
PRIMARY KEY 主键
FOREIGN KEY 外键
CHECK 自定义检查约束
主键约束(primary key)
主键用于唯一标识一条记录。主键值不可为空，也不允许出现重复。（非空且唯一）

- 创建表

-- 创建列级约束-显式指定名称，pk_sid

createtablet_stuInfo(

sidnumber(4) constraintpk_sid primarykey,

namevarchar2(20)

)



-- 创建列级约束-没式显示指定名称，系统随机命名SYS_C..

createtablet_stuInfo2(

sidnumber(4) primarykey,

namevarchar2(20)

)

表级约束:当多个列(字段)参与约束，可以用表级约束。

createtablet_stuInfo3(

sidnumber(4),

phone char(11),

namevarchar2(20),

constraintpk_stuinfo primarykey(phone,name)

)



createtablet_stuInfo4(

sidnumber(4),

phone char(11),

namevarchar2(20),

primarykey(phone,name)

)

非空约束(not null)
确保字段值不允许为空

只能在列级定义

建列级约束-显式指定名称，pk_sid

createtablet_stuInfo5(

sidnumber(4) primarykey,

phone char(11) constraintnn_phone notnull

)



createtablet_stuInfo5(

sidnumber(4) primarykey,

phone char(11) notnull

)



-- 添加操作

insertintot_stuinfo5(sid)

values(1000)

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

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



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

自定义约束
Check约束用于对一个属性的值加以限制

createtablet_stuInfo7(

sidnumber(4) primarykey,

phone char(11) unique,

age number(3) check(age>0andage<100)

)



insertintot_stuInfo7

values(1000,‘18612341234‘,-10)

外键约束
createtablet_stuInfo8(

sidnumber(4) primarykey,

phone char(11) unique,

tid number(4),

constraintfk_tid foreignkey(tid) referencest_teacher1(tid)

)



createtablet_teacher1(

tid number(4) primarykey,

namevarchar2(20) notnull

)



insertintot_teacher1

values(1,‘alex‘);



insertintot_stuInfo8

values(1000,‘18612341234‘,1)

于主表的删除和修改主键值的操作，会对依赖关系产生影响，以