6Oracle二20.docx

6Oracle二20.docx

《6Oracle二20.docx》由会员分享，可在线阅读，更多相关《6Oracle二20.docx（35页珍藏版）》请在冰豆网上搜索。

6Oracle二20

1.处理数据（DML）

1.1.数据控制语言（DML）

DML（DataManipulationLanguage–数据操作语言）可以在下列条件下执行:

向表中插入数据

修改现存数据

删除现存数据

事务是由完成若干项工作的DML语句组成的

1.2.插入数据

1.2.1.INSERT语句语法

♦使用INSERT语句向表中插入数据。

♦使用这种语法一次只能向表中插入一条数据。

1.2.2.插入数据

●为每一列添加一个新值。

●按列的默认顺序列出各个列的值。

●在INSERT子句中随意列出列名和他们的值。

●字符和日期型数据应包含在单引号中。

例：

在emp表中插入一行信息

1.2.3.向表中插入空值

♦隐式方式:

在列名表中省略该列的值。

♦显式方式:

在VALUES子句中指定空值。

1.2.4.插入指定的值

●例1：

说明：

SYSDATE记录当前系统的日期和时间。

●例2：

加入新员工

1.2.5.创建脚本（使用&变量指定列值）

♦在SQL语句中使用&变量指定列值。

♦&变量放在VALUES子句中。

这里其实就是单纯的替换

注意：

这个地方添加字符串类型的时候要使用单引号

也可以提前设置好引号

如：

insertintoemp（empno,ename,sal）values（&empno,'&ename',&sal）;

否则就需要在插入ename的时候加上’’

1.2.6.从其它表中拷贝数据（InsertInto...Select...）

首先创建一个表，

使用createtableemp2asselect*fromempwhere1=2;

这句话创建了一个和emp表结构一样的表，而且是没有数据的。

再使用子查询添加进去数据

insertintoemp2select*fromemp;

这里只要插入的时候类型和参数个数一样就行了，列名不同没有关系

1.3.更新数据

1.3.1.语法

1.3.2.一般用法示例

1.3.3.在UPDATE语句中使用子查询

1.3.4.更新中的数据完整性错误

比如级联的时候一个表关联着另外的一个表的时候。

某些约束会限制的

updateempsetdeptno=50whereempno=7839;

1.4.删除数据

1.4.1.语法

这里可以看到from写不写都行

1.4.2.一般用法示例

1.4.3.在DELETE中使用子查询

●在DELETE中使用子查询，使删除基于另一个表中的数据。

1.4.4.删除中的数据完整性错误

deletefromdeptwheredeptno=30;

1.4.5.Delete和Truncate

♦都是删除表中的数据

♦Delete操作可以rollback，可以闪回

♦Delete操作可能产生碎片，并且不释放空间

♦Truncate是清空表

settimingon会显示一条语句的执行时间

setfeedbakcon设置响应，比如已选择15行。

setfeedbackoff设置之后就不会显示已选择15行。

truncate和delete只删除数据不删除表的结构

delete语句是dml,事务提交之后才生效

truncate,drop是ddl,操作立即生效,不能回滚.

速度,一般来说:

truncate>delete

1.5.数据库事务

1.5.1.事务基础

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

●一个或多个DML语句

●一个DDL（DataDefinitionLanguage–数据定义语言）语句

●一个DCL（DataControlLanguage–数据控制语言）语句

数据库事务：

●以第一个DML语句的执行作为开始

●以下面的其中之一作为结束:

•显示结束:

commit;rollback

•隐式结束（自动提交）:

DDL语言,DCL语言,exit（事务正常退出）、ctrl+C也是正常退出

•隐式回滚（系统异常终止了）:

关闭窗口，死机，掉电

使用COMMIT和ROLLBACK语句,我们可以：

●确保数据完整性。

●数据改变被提交之前预览。

●将逻辑上相关的操作分组。

1.5.2.控制事务

●回滚到保留点

♦使用SAVEPOINT语句在当前事务中创建保存点。

♦使用ROLLBACKTOSAVEPOINT语句回滚到创建的保存点。

1.5.3.事务进程

♦自动提交在以下情况中执行:

⏹DDL语句。

⏹DCL语句。

⏹不使用COMMIT或ROLLBACK语句提交或回滚，正常结束会话exit。

♦会话异常结束或系统异常会导致自动回滚。

如果我插入数据，再创建表，非正常退出，那么插入的数据也会成功，因为创建表时会进行事务的提交

●提交或回滚前的数据状态

⏹改变前的数据状态是可以恢复的

⏹执行DML操作的用户可以通过SELECT语句查询之前的修正

⏹其他用户不能看到当前用户所做的改变，直到当前用户结束事务。

⏹DML语句所涉及到的行被锁定，其他用户不能操作。

可以演示下回滚和删除数据

●提交后的数据状态

⏹数据的改变已经被保存到数据库中。

⏹改变前的数据已经丢失。

⏹所有用户可以看到结果。

⏹锁被释放，其他用户可以操作涉及到的数据。

⏹所有保存点被释放。

●提交数据示例：

●数据回滚后的状态

使用ROLLBACK语句可使数据变化失效:

●数据改变被取消。

●修改前的数据状态被恢复。

●锁被释放。

1.5.4.数据库的事务隔离级别

♦对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题:

⏹脏读:

对于两个事物T1,T2,T1读取了已经被T2更新但还没有被提交的字段.之后,若T2回滚,T1读取的内容就是临时且无效的.

⏹不可重复读:

对于两个事物T1,T2,T1读取了一个字段,然后T2更新了该字段.之后,T1再次读取同一个字段,值就不同了.

⏹幻读:

对于两个事物T1,T2,T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行.之后,如果T1读取同一个表,就会多出几行.

不可重复读的重点是修改:

幻读的重点在于新增或者删除

♦数据库事务的隔离性:

数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响,避免各种并发问题.

♦一个事务与其他事务隔离的程度称为隔离级别.数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,

♦隔离级别越高,数据一致性就越好,但并发性越弱 

√:

可能出现×:

不会出现

脏读

不可重复读

幻读

Readuncommitted

√

√

√

Readcommitted

×

√

√

Repeatableread

×

×

√

Serializable

×

×

×

●数据库提供的4种事务隔离级别:

1.脏读：

老板给你打了5000元钱，你一查询一看太好了，终于有工资了，结果老板发现该给你2000元工资，所以又把刚才给你转账的那个事务回滚了一下，修改金额后又提交事务，你就白高兴了吧。

如果是真实的数据，这样来说就有可能出现各种不安全的问题。

2.不可重复读：

你今天去请朋友吃饭，你查了下银行卡，发现有2000元，觉得没有问题。

结果这时你老婆把你卡里的钱转到另一个卡了，吃完饭付钱的时候发现钱没啦，是不是两次读到的数据不一样，这就是不可重复读。

3.幻读：

你老婆可以看到你的消费记录，这个月看到你消费是80月，很高兴。

然后想着把账单打印出来夸夸你，结果在查询和打印账单之间发生了一件事，就是你在这期间吃了顿大餐，花了1000元，现在你老婆拿到数据看到的是1080，觉得不可以思议。

●Oracle支持的2种事务隔离级别：

READCOMMITED,SERIALIZABLE.

Oracle默认的事务隔离级别为:

READCOMMITED

●Mysql支持4中事务隔离级别.Mysql默认的事务隔离级别为:

REPEATABLEREAD

2.管理表结构（DDL）

数据库模式定义语言DDL（DataDefinitionLanguage）

2.1.显示表结构

使用DESCRIBE命令，表示表结构

示例：

2.2.数据类型

数据类型

描述

VARCHAR2（size）

可变长字符数据

CHAR（size）

定长字符数据

NUMBER（p,s）

可变长数值数据

DATE

日期型数据

LONG

可变长字符数据，最大可达到2G

CLOB

字符数据，最大可达到4G

RAWandLONGRAW

原始的二进制数据

BLOB

二进制数据，最大可达到4G

BFILE

存储外部文件的二进制数据，最大可达到4G

Oracle官方文档中明确指定varchar2可以保证更好的兼容性,建议使用varchar2类型

这里注意一个中文是2个字符

2.3.新建表（CreateTable）

2.3.1.建表规则

表名和列名:

●必须以字母开头

●必须在1–30个字符之间

●必须只能包含A–Z,a–z,0–9,_,$,和#

●必须不能和用户定义的其他对象重名

●必须不能是Oracle的保留字

●Oracle默认存储是都存为大写

●数据库名只能是1~8位

2.3.2.CREATETABLE语句语法

●必须指定:

•表名

•列名,数据类型,数据类型的大小

●要求必须具备以个资源才可以建表:

•CREATETABLE权限

•存储空间

添加创建表的权限。

需要用管理员提升权限:

grantcreatetabletoscott;

2.3.3.Default值

●执行insert操作时，可以为其指定默认值

...hire_dateDATEDEFAULTSYSDATE,...

●值、表达式和SQL语句都可以作为默认值

●其他的列名或者是伪列都是非法的

●默认值的类型必须和该列的类型一致

设置了default，还可以插入null值吗？

可以但是必须人工指定放进去才是。

insertintotestvalues（1,’zs’,null）;

这句话就会给第三列数据放进去null，即使它有默认值，结果也是null.

2.3.4.建表示例

创建t_user表：

Idnumber

Namevarchar2（20）长度在2-10之间checked（）

Birthdaydatedefaultsysdate

Genderchar

（2）‘男’‘女’checked（in（））

createtablet_user（

idnumber,

namevarchar2（20）check（length（name）>2）,

genderchar

（2）check（genderin（'男','女'））,

birthdaydatedefaultsysdate

）

2.3.5.使用子查询创建表

●使用ASsubquery选项，将创建表和插入数据结合起来

●指定的列和子查询中的列要一一对应

●通过列名和默认值定义列

●使用子查询创建表举例

示例：

如果只想使用emp表的表结构而不使用具体的数据怎么办？

注意：

如果子查询中有表达式，必须指定别名

举例：

createtablettasselectdeptno,avg（sal）fromempgroupbydeptno;

createtablettasselectdeptno,avg（sal）avgsalfromempgroupbydeptno;

这样就可以了

2.4.修改表结构（AlterTable）

2.4.1.语法说明

使用ALTERTABLE语句可以:

●追加新的列

●修改现有的列

●删除一个列

●使用ALTERTABLE语句追加,修改,或删除列的语法

altertablettadd（agenumber

（2））;

altertablettmodify（agenumber

（2）check（agebetween0and99）

altertablettrenamecolumnagetosss;

altertablettdropcolumndeptno;

2.4.2.示例

2.4.2.1.追加一个新列

2.4.2.2.修改一个列

2.4.2.3.删除一个列

2.5.清空表（Truncate）

●TRUNCATETABLE语句:

•删除表中所有的数据

•释放表的存储空间

●TRUNCATE语句不能回滚

●可以使用DELETE语句删除数据

2.6.删除表（DropTable）

●数据和结构都被删除

●所有正在运行的相关事物被提交

●所有相关索引被删除

●DROPTABLE语句不能回滚，但是可以闪回

SQL>droptablett;

select*fromtab;

TNAMETABTYPECLUSTERID

-----------------------------------------------

BIN$vlUA93psTMehL2Kumxj35A==$0TABLE

BONUSTABLE

DEPTTABLE

EMPTABLE

EMP2TABLE

EMP3TABLE

SALGRADETABLE

T_TESTTABLE

BIN$vlUA93psTMehL2Kumxj35A==$0是什么？

2.7.改变对象的名称

●执行RENAME语句改变表,视图,序列,或同义词的名称

●必须是对象的拥有者

2.8.约束

2.8.1.约束说明

♦约束是表一级的限制

♦如果存在依赖关系，约束可以防止错误的删除数据

♦约束的类型：

◆NOTNULL

◆UNIQUE

◆PRIMARYKEY

◆FOREIGNKEY

◆CHECK

●约束规则

●用户可以自定义约束，也可以使用OracleServer的sys_cn格式命名约束

●约束创建的时机：

●创建表的时候，同时创建约束

●表结构创建完成后

●约束可以定义在列一级，或者是表一级

●通过数据字典查看约束

2.8.2.非空约束

2.8.3.唯一性约束

2.8.4.主键约束

主键，表示定义的该字段里不能包含重复值，而且不能为null值。

所以我们说primarykey=notnull+unique+索引。

2.8.5.外键约束

外键，对于同一个字段，可以同时定义为foreignkey约束和notnull约束。

注意：

外键的类型、尺寸一定要与主表的主键完全一致。

从表数据依赖于主表

•FOREIGNKEY:

在子表中，定义了一个表级的约束

•REFERENCES:

指定表和父表中的列

•ONDELETECASCADE:

当删除父表时，级联删除子表记录

•ONDELETESETNULL:

将子表的相关依赖记录的外键值置为null

格式：

altertable表名addconstraint约束名称约束类型（列名）references被引用的表名称（列名）

例子：

altertableempaddconstraintjfkdsjforeignkey（deptno）referencesdept（deptno）

2.8.6.check约束

♦定义每一行记录所必须满足的条件

♦下面的表达式可以使用在check约束中:

⏹引用CURRVAL,NEXTVAL,LEVEL,和ROWNUM

⏹调用SYSDATE,UID,USER,和USERENV函数

⏹另一个表的查询记录

2.8.7.一个用到以上所有约束的示例

createtabletestAll（

idnumber

constraintPK_TIDprimarykey,

namevarchar（20）

constraintNN_NAMEnotnull,

genderchar

（2）

constraintCH_GENDERcheck（genderin（'男','女'））,

birthdaydate

defaultsysdate,

cardNovarchar2（18）

constraintNN_CARDNOnotnull

constraintUNI_CARDNOunique,

deptnonumber

constraintFK_TDEPTNOREFERENCESdept（deptno）

）

查看本表中的所有的约束：

select*fromuser_constraintswheretable_name='T_USER';

2.8.8.违反约束

♦不能删除有外键约束的记录

2.9.查询其他用户的表

♦其他用户的表不属于本用户的空间

♦如果要查询其他用户下的表，要使用其他用户的用户名作为前缀。

3.其他数据库对象

3.1.视图

3.1.1.视图说明

可以把视图理解成封装起来的查询语句，查询视图就等同于执行了那个查询语句。

●视图是一种虚表.

●视图建立在已有表的基础上,视图赖以建立的这些表称为基表。

●向视图提供数据内容的语句为SELECT语句,可以将视图理解为存储起来的SELECT语句.

●视图向用户提供基表数据的另一种表现形式

●视图的优点

♦简化复杂查询

♦限制数据访问

在创建视图的时候，可以标成一个只读的视图。

可以通过视图修改表吗？

可以，增删改查都可以。

在创建视图时有个选项WITHREADONLY，如果加上这个，就表示是只读的，就不能增删改了，就只能查询。

看视图，不知道是从哪个表中来的数据，只有看创建视图的DDL才知道，这样比较安全。

在给银行做项目时，安全级别比较高，通常给我们的就是视图，不是表，只让查。

那改怎么办呢？

通过他提供的存储过程改，一般不让直接执行insert与update，这是为了安全。

♦同样的数据，可以有不同的显示方式

●注意：

不建议通过视图对表进行修改

视图不能提高性能

scott用户默认不能创建视图，需要用管理员提升权限:

grantcreateviewtoscott;

3.1.2.创建视图

●使用下面的语法格式创建视图

•FORCE：

子查询不一定存在

•NOFORCE：

子查询存在（默认）

•WITHREADONLY:

只能做查询操作

●子查询可以是复杂的SELECT语句

createviewtest

asselectempno,ename,salfromempwhereempno=7369;

这里需要注意的是：

视图依赖于基表，如果基表中的数据发生了变化，视图查到的数据也会发生变化

createviewv_emp2asselectsal,sal*12fromemp

另外，我们可以依赖视图做一些修改，比如插入数据。

insertintov_emp2（empno,ename,sal）values（2,'lisi',1111）;

我对视图做出修改，请问原表数据发生变化了吗？

SQL>select*fromemp;

我们发现lisi已经被插入到emp中

我们现在再来执行这条插入语句，看下结果如何？

insertintov_emp2values（3,'wangwu',1234,12321）;

3.1.3.创建复杂视图

复杂视图举例：

查询各个部门的最低工资，最高工资，平均工资

as紧跟的子查询可以任意复杂，不过如果生成的视图中的子查询有where条件，在插入不满足where条件的数据的时候可以插入。

如果想加以限制，就要使用上面的withcheckoption;就只能插入符合where条件的内容

SQL>createorreplaceviewv_emp2asselectempno,ename,sal,sal*12totalfromempwhereename='KING'withcheckoption;

3.1.4.查询视图

3.1.5.修改视图

●使用CREATEORREPLACEVIEW子句修改视图

●CREATEVIEW子句中各列的别名应和子查询中各列相对应

可以删除后再创建，对原数据没有任何影响，也可以使用createorreplaceview，同名视图会覆盖掉之前的

3.1.6.视图中使用DML的规定

（了解即可，视图一般只会被用来查询）

●可以在简单视图中执行DML操作

●当视图定义中包含以下元素之一时不能使用delete:

●组函数

●GROUPBY子句

●DISTINCT关键字

●ROWNUM伪列

当视图定义中包含以下元素之一时不能使用update:

●组函数

●GROUPBY子句

●DISTINCT关键字

●ROWNUM伪列

●列的定义为表达式

当视图定义中包含以下元素之一时不能使用insert:

●组函数

●GROUPBY子句

●DISTINCT关键字

●ROWNUM伪列

●列的定义为表达式

●表中非空的列在视图定义中未包括

3.1.7.屏蔽DML操作

●可以使用WITHREADONLY选项屏蔽对视图的DML操作

●任何DML操作都会返回一个Oracleserver错误

3.1.8.删除视图

删除视图只是删除视图的定义，并不会删除基表的数据

3.2.序列

3.2.1.什么是序列

序列:

可供多个用户用来产生唯一数值的数据库对象

●自动提供唯一的数值

●共享对象

●主要用于提供主键值

●将序列值装入内存可以提高访问效率

3.2.2.创建序列

●CREATESEQUENCE语句

例：

●