posts - 78, comments - 34, trackbacks - 0, articles - 1
  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

                今日的内容比较多,有些吃不消。不多说了,赶紧复习!

 

一、Merge语句

根据条件在表中执行修改或插入数据的功能,如果插入的数据行在目的表中存在就执行UPDATE,如果是不存在则执行INSERT:

-避免了单独的修改

-增强性能和应用性

                它的功能就是将,一个表中的数据加入到另一个表中。

                格式:

MERGE INTO 目标表

USING 源表

ON(目标表.FIELD = 源表.FIELD)

WHEN MATCHED THEN

  UPDATE SET

  目标表.FIELD1 = 源表.FIELD1,

  目标表.FIELD2 = 源表.FIELD2,

  ...

WHEN NOT MATCHED THEN

  INSERT (目标表.FIELD1,目标表.FIELD2,...)

  VALUES (FV1,FV2,...)

 

                如果不需要,insert() vale()。可以不写WHEN NOT MATCHED THEN这一部分。

例,在“EMP”表中添加一个“LOC”字段,将“DEPT”表中的“LOC”字段值根据员工的部门编号拷贝到“EMP”表中的“LOC”字段。如果有部门编号不存在的员工,则什么也不做。

ALTER TABLE EMP ADD LOC VARCHAR2(20)

 

MERGE INTO EMP E

USING DEPT D

ON(E.DEPTNO = D.DEPTNO)

WHEN MATCHED THEN

  UPDATE SET

  E.LOC = D.LOC

 

                数据库事务处理,在此就不复习了。

 

二、数据库的主要对象

                1.数据库的对象:

对象名称

描述

基本的数据存储对象,以行和列的形式存在,列是字段,行是记录

数据字典

也就是系统表,存放数据库相关信息的表

约束条件

执行数据校验,保证了数据完整性的

视图

一个或者多个表数据的逻辑显示

索引

用于提高查询的性能

同义词

对象的别名

 

                2.Oracle中常用字段类型:

数据类型

说明

char(size)

定长字符,≤2000个字节

varchar2(size)

变长字符,≤4000个字节

date

日期数据,默认的格式是dd-mm-yy:如11-6-06

timestamp

日期数据,要比date数据更确切,包含时分秒。

integer

整型数据

number[(s,p)]

数字型,可存放实型和整型 ,精度(p)和范围(s)

long

可变字符,≤2GB个字符

float,real

NUMBER的子类型

blob

存放图形、声音和影像,大二进制对象,≤4GB

clob

存放大文本文件,≤4GB

 

                3.使用子查询创建表:

                以前直接使用Create Tabel tab_name…创建表,如果想创建表的同时,将另一张表中的某些数据导入到新表怎么办?此时,子查询派上了用场:

                例,创建一个“EMPNEW”表,它与“EMP”表具有相同的字段和值:

CREATE TABLE EMPNEW

AS SELECT * FROM EMP WHERE DEPTNO IS NOT NULL AND DEPTNO>0

 

                例,创建一个“EMPNEW2,包含“员工编号”和“员工姓名”两个字段它只需要“EMP”表中的“EMPNO”和“ENAME”字段和值:

CREATE TABLE EMPNEW2(

       "员工编号",

       "员工姓名"

) AS SELECT EMPNO,ENAME FROM EMP

--这两条语句实现的功能相同

CREATE TABLE EMPNEW2

AS SELECT EMPNO "员工编号",ENAME "员工姓名" FROM EMP

 

三、对表的字段和表的操作

1.在表中增加字段:

                例,在“EMP2表中添加一个EMAIL”字段:

ALTER TABLE EMPNEW2 ADD EMAIL VARCHAR2(30)

 

2.在表中修改字段:

                例,修改“EMPNEW2表中字段“EMAIL”的默认值为NULL@SQL.COM

ALTER TABLE EMPNEW2 MODIFY EMAIL VARCHAR2(30) DEFAULT 'NULL@SQL.COM'

不是任何情况都可以修改的,当字段只包含空值时,

类型、大小都可以修改,否则修改可能不能成功。

 

3.在表中删除字段:

例,删除“EMPNEW2”表中的“EMAIL”字段:

ALTER TABLE EMPNEW2 DROP COLUMN EMAIL

删除大表中的字段将需要比较长的时间。

 

4.删除表的内容:

                例,删除“EMPNEW2表中的内容:

TRUNCATE TABLE EMPNEW2

delete语句相比:

Truncate语句清空表中所有的内容,而delete可以根据where条件选择性删除数据。

TruncateDDL语句,如果在事件中使用它,它是不可以回滚的。

Truncate释放表空间,但delete不会。

 

5.删除表:

                例,删除表“EMPNEW2

DROP TABLE EMPNEW2

1、表中所有数据将被删除

2、没有完成的事务被提交

3、所有相关的索引被删除

4、这个删除操作不能回滚

 

6.修改对象名称:

                例,:将表“EMPNEW2名称修改为“EMPTWO”:

RENAME EMPNEW2 TO EMPTWO

使用RENAME语句改变对象名称,可以修改表、视图、序列或者同义词的名称,必须是对象的所有者。

 

四、Oracle数据库中的表

1.查询数据字典:

                例,查看被用户拥有有的表(我使用的是Oracle自带的SCOTT用户):

SELECT TABLE_NAME FROM USER_TABLES

 

                例,查看用户拥有的所有对象类型:

SELECT DISTINCT OBJECT_TYPE FROM USER_OBJECTS

 

                例,查看所有用户拥有的表(必须是DBA用户登录):

SELECT * FROM DBA_TABLES

 

五、约束

1.什么是约束:

约束是在表上强制执行的数据校验规则.

当表中数据有相互依赖性时,可以保护相关的数据不被删除.

Oracle 支持下面五类完整性约束:

1NOT NULL      非空

2UNIQUE Key   唯一键

3PRIMARY KEY              主键

4FOREIGN KEY               外键

5CHECK                            检察

 

2.非空约束:

约束字段值不能为空。

与其他约束相比,非空约束只能定义的字段级。

例,创建表“USERTABLE”并设置“NAME”字段非空:

--非空约束

CREATE TABLE USERTABLE(

       ID INT,

       NAME VARCHAR2(10) NOT NULL--字段级

)

--或:ALTER TABLE USERTABLE MODIFY NAME NOT NULL表外级

--查看用户的约束

SELECT * FROM USER_CONSTRAINTS

--查看表的约束

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMP'

 

3.唯一性约束:

约束字段不准出现重复的记录,但可以出现一个或多个空值。

例,创建表“USERTABLE”并设置“EMAIL”字段唯一:

--唯一性约束

CREATE TABLE USERTABLE(

      ID INT,

      NAME VARCHAR(10) NOT NULL,

      EMAIL VARCHAR(30) UNIQUE--字段级

      --或:CONSTRAINT EMAIL_UN1 UNIQUE(EMAIL)--表级

)

--或:ALTER TABLE USERTABLE ADD CONSTRAINT EMAIL_UN1 UNIQUE(EMAIL)--表外级

注意:这种可以在表外定义的约束,一般都在表外定义。这样易于调整和管理。

 

4.主键盘约束:

主键从功能上看相当于非空且唯一

一个表中只允许一个主键

主键是表中能够唯一确定一个行数据的字段

主键字段可以是单字段或者是多字段的组合

例,创建表“USERTABLE”并设置“ID”字段为主键:

--主键约束

CREATE TABLE USERTABLE(

       ID INT PRIMARY KEY,--字段级

       NAME VARCHAR2(10) NOT NULL,

       EMAIL VARCHAR(30) UNIQUE

       --或:CONSTRAINT ID_PRI PRIMARY KEY(ID)--表级

)

--或:ALTER TABLE USERTABLE ADD CONSTRAINT ID_PRI PRIMARY KEY(ID)--表外级

注意:这种可以在表外定义的约束,一般都在表外定义。这样易于调整和管理。

 

5.外键约束:

外键是构建于一个表的两个字段或者两个表的两个字段之间的关系

外键确保了相关的两个字段的关系:

子表外键列的值必须在主表参照列值的范围内,或者为空

主表主键值被子表参照时,主表记录不允许被删除

外键约束条件参照的是主表的一个或者多个字段的值,通常被外键参照的是主表的主键或者唯一键

例,创建一个“ROLETABLE”角色表,在“USERTABLE”表中添加一个外键,指向角色ID

--外键约束

CREATE TABLE ROLETABLE(

       ID INT PRIMARY KEY,

       MODIFYTAB NUMBER(4)

)

--

CREATE TABLE USERTABLE(

       ID NUMBER(4) PRIMARY KEY,

       NAME VARCHAR2(10) NOT NULL,

       EMAIL VARCHAR(30) UNIQUE,

       ROLE_ID NUMBER(4),

       CONSTRAINT FK_ROL FOREIGN KEY(ROLE_ID) REFERENCES ROLETABLE(ID)--表级

)

--或:ALTER TABLE USERTABLE ADD CONSTRAINT FK_ROL FOREIGN KEY(ROLE_ID) REFERENCES ROLETABLE(ID)--表外级

 

6.CHECK约束:

Check约束条件是一种比较特殊的约束条件,通过check定义,

强制定义在字段上的每一记录都要满足check中定义的条件。

check中定义检查的条件表达式,进入表中的数据必须符合。

check中设置的条件

条件表达式不允许使用:

1SYSDATE, USER等函数

2、参照其他记录的值

例,为 EMP”表中的“SAL”字段添加约束:

ALTER TABLE EMP ADD CONSTRAINT CK_SAL CHECK(SAL>6000)

 

7.删除约束:

删除约束条件对于表和数据不会产生影响

--删除“EMP”表中的“SAL”字段的CHECK约束

ALTER TABLE EMP DROP CONSTRAINT CK_SAL

--删除“ROLETABLE”中的“ID”字段的主键约束,同时“USERTABLE”的外键约束也被删除

ALTER TABLE ROLETABLE DROP PRIMARY KEY CASCADE

 

六、索引

1.什么时索引:

方案(schema)中的一个数据库对象

Oracle数据库中用来加速对表的查询

通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O

与表独立存放

Oracle数据库自动维护

 

2.索引的创建方法:

自动:当在表上定义一个PRIMARY KEY或者UNIQUE约束条件时,Oracle数据库自动创建一个对应的唯一索引。

手动:用户可以创建索引以加速查询。

 

3.创建索引:

例,创建一个“USERTABLE”,为“ID”字段创建索引:

--创建索引

CREATE TABLE USERTABLE(

       ID NUMBER(4),

       NAME VARCHAR2(10)

)

CREATE INDEX USERTABLE_INDEX ON USERTABLE(ID)

 

4.删除索引:

例,删除“USERTABLE”中“ID”字段的索引:

--删除索引

DROP INDEX USERTABLE_INDEX

删除索引,必须拥有索引或者拥有 DROP ANY INDEX 权限。

 

七、视图

1.什么是视图:

视图也就是虚表,实际上视图就是一个命名的查询,用于改变基表数据的显示。

可以限制对数据的访问

可以使复杂的查询变的简单

提供了数据的独立性

提供了对相同数据的不同显示

 

2.创建视图:

例,查询“EMP”表中薪金大于8000的员工。

SELECT * FROM EMP WHERE SAL>8000

如果上面的查询语句是我们经常要使用到的呢?每次都要编写这个语句吗?视图在此发挥了作用:

--创建视图

CREATE OR REPLACE VIEW SAL8000

AS SELECT * FROM EMP WHERE SAL>8000

 

3.从视图中查询数据:

--从视图中查询数据

SELECT * FROM SAL8000

就像是定义了一个宏!

如果创建视图“SELECT * FROM”中的“*”,是某几个字段。则查询视图中也仅能查询创建视图时指定的字段。

查询视图结构,在命令窗口中输入:DESC 视图名称

 

4.修改视图:

例,修改上面创建的视图“SAL8000”,为每个列指定列名:

--修改视图

CREATE OR REPLACE VIEW SAL8000

AS SELECT EMPNO 工号,ENAME 姓名 FROM EMP WHERE SAL>8000

这视图应该是可以被覆盖的,没发现这里有修改的关键字。

 

5.创建复杂视图:

例,创建一个“EMP”表与“DEPT”表能过“DEPTNO”字段关联的视图:(联表视图)

--创建复杂视图

CREATE OR REPLACE VIEW EMPANDDEPT

AS SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO

 

6.禁止视图进行DML操作:

例,修改上边的视图,使其只能查询,不能进行DML操作:

--使用WITH READ ONLY禁止视图进行DML操作

CREATE OR REPLACE VIEW EMPANDDEPT

AS SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO

WITH READ ONLY

 

7.删除视图:

例,删除上面创建的“DEMANDDEPT”视图:

--删除视图

DROP VIEW EMPANDDEPT

 

8.行内视图:

行内视图是在SQL语句中使用的一个带有别名的子查询.

在主查询FROM 子句中的子查询就是行内视图.

行内视图不是数据库的对象,所以不需要显式的创建.

 

八、TOP-N分析

1.什么是TOP-N

使用Top-N分析法可以得到某列上最什么的前n个值. 例如:

薪水最高的前5个员工?

人员最多的3个部门?

最大的几个值和最小的几个值都可以通过Top-N 分析的方法得到.

 

2.使用TOP-N

例,查询“EMP”表中工资最高的前3位员工:

--TON-N分析

SELECT EMPNO,ENAME,JOB,SAL

FROM (

       SELECT ROWNUM,EMPNO,ENAME,JOB,SAL FROM EMP ORDER BY SAL DESC

) WHERE ROWNUM < 4

1、使用了rownum这个伪列,这个伪列将会返回行号,可以作为返回记录的序列号显示。

2、在from后面使用了子查询,这是标准的行内视图的使用。

3、在主查询中通过where条件中的rownum伪列定义过滤条件,只返回最什么的前几行数据。

 

3.数据库中的记录分布技术:

正是使用TOP-N分析实现的。

例,查询工资排行的第4-7位的员工信息:

--TOP-N分页

SELECT EMPNO,ENAME,JOB,SAL

FROM(

       SELECT ROWNUM MR,EMPNO,ENAME,JOB,SAL FROM(

              SELECT ROWNUM,EMPNO,ENAME,JOB,SAL FROM EMP ORDER BY SAL DESC

       )WHERE ROWNUM < 7

)WHERE MR > 3

上面我们是取页面的中间部分,可以将7换为33换为0。这样取的就是页面首部(前三位)!

 

九、同义词

1.什么是同义词:

同义词是数据库中一个对象的别名,可以简化对对象的访问。

通过使用同义词,可以:

   1、简化了引用另一个用户对象的方法

   2、缩短了对象名称的长度

 

2.创建同义词:

例,为“EMP”表创建一个同义词“E”:

--同义词

CREATE SYNONYM E FOR EMP

 

3.使用同义词:

例,使用同义词查询“EMP”所有记录:

--使用同义词(默认为私有同义词)

SELECT * FROM E

 

4.公开与私有同义词:

--公开同义词

CREATE PUBLIC SYNONYM E FOR EMP

公开同义词任何用户都可以访问,私有同义词只有创建它的用户才可以访问。

 

5.删除同义词:

例,删除上面创建的同义词“E”:

--删除同义词

DROP SYNONYM E

 

十、序列

1.什么是序列:

是一数据库对象,利用它可生成唯一的整数。

一般使用序列自动地生成表主键值或唯一键值

不直接连接到数据库中的任何表

 

2.定义序列的语法:

CREATE SEQUENCE [user.]sequence_name
[increment by n]
[start with n]
[maxvalue n|]
[minvalue n ];
[cache]

[NOCYCLE|CYCLE]

 

INCREMENT BY 指定序列号之间的间隔,该值可为正的或负的整数,但不可为0。序列为升序。忽略该子句时,缺省值为1

START WITH:指定生成的第一个序列号。在升序时,序列可从比最小值大的值开始,缺省值为序列的最小值。对于降序,序列可由比最大值小的值开始,缺省值为序列的最大值。

MAXVALUE:指定序列可生成的最大值。

MINVALUE:指定序列的最小值

NOCYCLE:一直累加,不循环

CYCLE累加到最大值循环

CACHE:默认值是20

序列里的缓冲是大量并发提取序列值时提升性能的一种手段。

由于序列操作是原子操作,如果没有缓冲,序列的访问将只能由一个事务独占,独占期间阻塞其它事务的访问,且一定会涉及硬盘操作。这样如果有对一个序列的大量并发操作的话,系统性能将会下降。

缓冲是指事先成批的提取序列值,缓冲在内存中,供多个事务同时访问(每个事务占一个缓冲的序列值),且访问期间如果没有用尽缓冲是不用写硬盘的,可大幅度提高并发访问序列的性能。但带来的问题是序列值可能不连续(但肯定保证唯一),如并发访问序列值后有些事务回滚等操作,都会带来缓冲序列值的不连续或丢失。

 

3.创建序列:

例,创建一个“SEQ_EMP”序列:

--创建序列

CREATE SEQUENCE SEQ_EMP

INCREMENT BY 1

START WITH 1

MAXVALUE 100

MINVALUE 1

CYCLE

 

4.查看序列:

例,查看“SEQ_EMP”序列:

--查看序列

SELECT * FROM USER_SEQUENCES

--查看初始化序列,NEXTVAL将引起序列值增加“INCREMENT BY

SELECT SEQ_EMP.NEXTVAL FROM DUAL

--查看序列当前值

SELECT SEQ_EMP.CURRVAL FROM DUAL

 

5.使用序列:

例,向“EMP”表中插入新记录。 EMPNO”字段使用上面创建的“SEQ_EMP”序列的值:

--使用序列

INSERT INTO EMP (EMPNO,ENAME) VALUES (SEQ_EMP.NEXTVAL, 'XLTEST')

如果某个字段使用“SEQ_EMP”序列的值,就应该一直使用这个序列,否则可能会造成字段值重复。我理解序列就是自定义的“auto_increment”,可能还有些其他用途。

 

6.修改序列:

例,修改上面创建的“SEQ_EMP”序列:

--修改序列

ALTER SEQUENCE SEQ_EMP

INCREMENT BY 2

MAXVALUE 200

MINVALUE 2

修改序列可以:

修改未来序列值的增量。

设置或撤消最小值或最大值。

改变缓冲序列的数目。

指定序列号是否是有序。(这应该就是循环或不循环)

注意:

1.第一次NEXTVAL返回的是初始值

2.可以修改除start以外的所有sequence的参数。如果想要改变start值,必须删除序列再重新创建。

 

7.删除序列:

例,删除序列“SEQ_EMP”:

--删除序列

DROP SEQUENCE SEQ_EMP

 

十一、匿名块

1.什么是匿名块:

匿名块是只使用一次的PL/SQL程序块

匿名块没有名称,也不被存储在数据库中

PL/SQL的四个基本组成部分构成

不能被重复使用

 

2.匿名块语法:

DECLARE

        变量、常量声明;

BEGIN

        pl/sql_block;

EXCEPTIONS

        异常捕捉;

END;

 

十二、存储过程

1.什么是存储过程:

用于在数据库中完成特定的操作或者任务。其实,它就是数据库中的自定义函数!

语法:

Create PROCEDURE name

                parameter_name [IN | OUT | IN OUT] datatype

                [{:= | DEFAULT} expr]

IS

pl/sql_block;

Sql*Plus 中使用CREATE OR REPLACE子句创建存储程序单元

在头部定义所有参数

IS表示PL/SQL块的开始。

IS之后,声明本地变量

 

2.存储过程的参数模式:

IN

OUT

IN OUT

默认

值:

传递给子程序

手动指定

 

返回到调用环境

手动指定

 

传递给子程序,返回到调用环境

参数形式:

常量       

 

未初始化的变量

 

初始化的变量

实参:
可以是表达式,常量,或者是初始化的变量。

 

必须是一个变量

 

必须是一个变量

上面一年便知:

IN,就是传入参数。

OUT,就是接收返回值。

INOUT,就是传入参数后,也接收返回值。

 

3.创建存储过程:

例,创建一个根据“EMPNO”字段更新“EMP”表中“SAL”字段值,并返回“OK!”:

--创建存储过程

CREATE OR REPLACE PROCEDURE UPEMPFUN(

       ARG_EMPNO IN NUMBER,

       ARG_SAL IN NUMBER,

       ARG_RES OUT VARCHAR

)IS BEGIN 

    UPDATE EMP SET SAL = ARG_SAL WHERE EMPNO=ARG_EMPNO;

    COMMIT; 

    ARG_RES := 'OK!';

END;

 

4.调用存储过程:

例,调用上面创建的存储过程:

--调用存储过程

DECLARE

  ARG_RES VARCHAR(20);

BEGIN

  UPEMPFUN(7369,99999,ARG_RES);

  DBMS_OUTPUT.PUT_LINE(ARG_RES);

END;


只有注册用户登录后才能发表评论。


网站导航: