一、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() values()。可以不写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”表中的内容:
与delete语句相比:
Truncate语句清空表中所有的内容,而delete可以根据where条件选择性删除数据。
Truncate是DDL语句,如果在事件中使用它,它是不可以回滚的。
Truncate释放表空间,但delete不会。
5.删除表:
例,删除表“EMPNEW2”:
1、表中所有数据将被删除
2、没有完成的事务被提交
3、所有相关的索引被删除
4、这个删除操作不能回滚
6.修改对象名称:
例,:将表“EMPNEW2”名称修改为“EMPTWO”:
使用RENAME语句改变对象名称,可以修改表、视图、序列或者同义词的名称,必须是对象的所有者。
四、Oracle数据库中的表
1.查询数据字典:
例,查看被用户拥有有的表(我使用的是Oracle自带的SCOTT用户):
SELECT TABLE_NAME FROM USER_TABLES
|
例,查看用户拥有的所有对象类型:
SELECT DISTINCT OBJECT_TYPE FROM USER_OBJECTS
|
例,查看所有用户拥有的表(必须是DBA用户登录):
五、约束
1.什么是约束:
约束是在表上强制执行的数据校验规则.
当表中数据有相互依赖性时,可以保护相关的数据不被删除.
Oracle 支持下面五类完整性约束:
1、NOT NULL 非空
2、UNIQUE Key 唯一键
3、PRIMARY KEY 主键
4、FOREIGN KEY 外键
5、CHECK 检察
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中设置的条件
条件表达式不允许使用:
1、SYSDATE, 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换为3,3换为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”:
十、序列
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;
|
相关推荐
Oracle学习笔记精华版是针对数据库管理系统Oracle的一份重要学习资源,涵盖了从基础概念到高级特性的全面知识。Oracle,作为全球广泛使用的大型企业级数据库系统,对于IT专业人员尤其是数据库管理员(DBA)来说,是...
### Oracle学习笔记知识点详解 #### 一、Oracle简介 Oracle是一家知名的软件公司,以其数据库管理系统闻名全球。该公司成立于1977年,总部位于美国加利福尼亚州。Oracle不仅提供数据库解决方案,还涉及中间件、...
在Oracle学习笔记中,对安装卸载和配置的详尽讲解,不仅为学习者提供了操作指导,而且还涉及到了数据库管理的一些基础知识点。这些内容对于数据库管理员和开发人员来说都是十分重要的,因为它们是操作Oracle数据库的...
Oracle学习笔记 以下是我这一周学习oracle整理的笔记,包括课堂的内容和自己看额外看的视频补充的一些内容,基本上囊括了所有oracle的基本知识。主要的形式是例子代码加代码解释加运行结果,我个人认为对于没有学习...
资源名称:Oracle学习笔记-日常应用、深入管理、性能优化内容简介:Oracle学习笔记-日常应用、深入管理、性能优化Oracle 11g是最具代表性的高端关系型数据库管理系统,它在世界各地的大型商务数据库应用系统中被广泛...
以下是对Oracle学习笔记整理的主要知识点的详细说明: 1. **数据库选择**: 在决定使用哪种数据库时,通常需要考虑项目的规模、性能需求、安全性要求以及可用资源。Oracle数据库因其稳定性、可扩展性和高性能而被...
### Oracle 学习笔记知识点概览 #### 一、Oracle 数据库系统参数查询与管理 在 Oracle 数据库的学习过程中,了解如何查看和管理数据库的系统参数是非常重要的。这些参数直接影响着数据库的性能和稳定性。 ##### ...
ORACLE学习笔记:日常应用、深入管理、性能优化.part1
Oracle数据库是世界上最流行的数据库管理系统之一,它提供了丰富的特性和功能来优化数据管理和查询性能。本文主要探讨Oracle数据库的入门基础知识,特别是与索引相关的概念。 首先,我们要理解ROWID的概念。ROWID是...
全网最全的oracle学习笔记,oracle学习笔记,oracle,### 4、oracle的七个服务 ```sql 1、Oracle ORCL VSS Writer Service Oracle卷映射拷贝写入服务,VSS(Volume Shadow Copy Service)能够让存储基础设备(比如...
根据提供的信息,我们可以总结出以下Oracle数据库学习的关键知识点: ...以上是基于提供的内容整理出的Oracle学习笔记中的关键知识点。通过理解这些基础知识,可以更好地管理和操作Oracle数据库。
### Oracle 学习笔记知识点详解 #### 一、Oracle 数据库简介 Oracle 是一款由美国甲骨文公司开发的关系型数据库管理系统。它以其强大的数据处理能力、高度的安全性及稳定性而闻名于世,在金融、电信、政府等领域...
Oracle 11g是最具代表性的高端关系型数据库管理系统,它在世界各地的大型商务数据库应用系统中被广泛应用。本书设计了大量的应用情景,介绍了数据库管理员和开发人员常用的管理、维护和优化Oracle 11g数据库的技术和...
在“MSDN Oracle学习笔记”中,我们可以期待找到关于Oracle数据库的详细讲解和实践指导。 首先,Oracle数据库的基础知识是必不可少的。这通常涵盖数据库系统的基本概念,如SQL(结构化查询语言)的使用,数据类型,...
oracle 学习笔记oracle 学习笔记oracle 学习笔记oracle 学习笔记oracle 学习笔记oracle 学习笔记oracle 学习笔记oracle 学习笔记oracle 学习笔记