Oracle学习笔记

Oracle学习笔记

Oracle与SqlServer区别

对操作系统的支持：Oracle支持Windows、Linux、Mac。

数据库架构：Oracle一个实例只能管理一个数据库

数据库安全性：Oracle是ISO安全认证数据库。

内存分配：Oracle是由INIT.ORA来分配内存

版本历史

1979年Oracle 2g：整合比较完整的SQL实现

1983年Oracle 3g：使用C语言重新编写

1984年Oracle 4g：产品稳定性升级

1985年Oracle 5g：首批可以运行在C/S模式下的数据库产品

1988年Oracle 6g：添加行级锁、联机热备份等功能

1992年Oracle 7g：添加分布式事务处理能力，对数据库管理功能增强，提高安全性

1997年Oracle 8g：支持面向对象的开发及新的多媒体应用

1998年Oracle 8i：添加为Internet设计的特征，提供全方位的Java支持

2001年Oracle 9i：添加Real Application Cluster（RAC)。集群功能

2003年Oracle 10g：应用服务器版本，添加网格计算的功能

2007年Oracle 11g：实现了信息声明周期管理、全新的Data Guard可用性最大化，数据压缩技术提升

2013年Oracle 12c：引入的多租用户环境（Multitenant Environment）中，允许一个数据库容器（CDB）承载多个可插拔数据库（PDB）

Oracle 11g新特性

数据库管理

1. 数据库重放：可以以二进制文件格式捕获SQL级别一下的所有活动，然后再不同数据库重放
2. SQL计划管理：让系统自动控制SQL执行计划的稳定性，防止由于执行计划发生变化而导致的性能下降
3. 自动存储管理：用于降低共享池使用的可变的区大小，以及实例能够读取磁盘组的特定磁盘
4. 自动健康检查：Oracle对数据库进行健康检查
5. 企业管理器功能增强：增加LOGMINER接口，主要用作日志的查询。
6. 自动诊断知识库：检测到重要错误时，会自动创建一个事件，并捕捉到这一事件相关的信息
7. 闪回事务：即使是已经提交的事务，也可以回退
8. 自动内存优化：所有内存可以通过设定一个参数来实现全表自动优化。

SQL部分

1. 触发器：引入一个复合触发器。在一个触发器中使用4部分内容：申明部分、before过程部分、after each row过程部分、after过程部分
2. 对象依赖性改进：如果函数或试图依赖于某个表，一旦发生结构变化，无论是否涉及都会使函数或试图变为invalid
3. 调用某个函数时，可以通过=>来为特定的函数参数指定数据

三级模式和二级映像

三级模式

1. 概念级：由数据库设计者综合所有用户的数据，按照统一的观点构造的全局逻辑结构，对数据库中全部数据和逻辑结构和特征的总体描述。由数据库管理系统提供的数据模式描述语言（Data Description Language, DDL）来描述。主要包含：DROP、CREATE、ALTER、GRANT、REVOKE、TRUNCATE
2. 用户级：某个或某几个用户所看到的数据库的数据视图。用户可以通过外模式描述语言来描述，也可以用数据操纵语言（Data Manipulation Language，DML）对数据进行操作。主要包含：Create、Read、Update、Delete
3. 物理级：全体数据的内部表示或底层描述，数据库最低一级，描述数据在存储介质上存储方式的物理结构

二级映像

三级模式是对数据的3个抽象级别。为了能够在内部实现3个抽象层次的联系和转换，DBMS提供两层映像

1. 外模式映像：具有较高的逻辑独立性
2. 内模式映像：具有较高的物理独立性

约束

Oracle中使用的约束有：

1. 主键约束（Primary Key）：没有重复值且不为空
2. 外键约束（Foreign Key）：保证数据完整性
3. 唯一约束（unique）：不能重复
4. 检查约束（check）：取值范围
5. 非空约束（not null）：不能为空

数据库三大范式

关系型数据库的设计标准就是数据库的三大范式

1. 第一范式：数据库中的字段都是单一属性，不可再分。 第二范式：不存在非关键字对任一候选关键字段部分函数依赖。组合主键AB不能与其他字段存在组合重复。例如：客户编号、产品名称、产品类型。如果客户编号、产品名称组合主键，那么产品名称与产品类型存在一定关系。不符合第二范式要求。会出现以下4种问题
2. 1. 数据冗余
   2. 更新异常
   3. 插入异常
   4. 删除异常
3. 第三范式：不存在非关键字段对任意候选关键字段的传递函数依赖。指如果A决定B，B决定C，则C传递函数依赖与A。例如：员工编号、部门编号、部门电话。因为部门编号决定了部门电话，故此传递依赖

SQL种类

数据定义语言（DDL）：定义数据库中数据要如何存储。包含对象创建、修改、删除。涉及：数据库、数据表、视图、索引

数据操纵语言（DML）：对数据表进行操作的。主要包含对数据进行：增加、删除、修改的操作。

数据查询语言（DQL）：对数据进行查询

数据控制语言（DCL）：权限设置和操作。设置不同用户不同权限

常用数据类型

Oracle提供数据类型有23种，分为字符型、数字型、日期类型、其他数据类型

字符型：varchar2-变长、char-定长、nchar、nvarchar、long。带n是Unicode字符、不带是Ascii码

数字型：number-小数、float-整数。

日期：date-日期、timestamp-时间。

其他：blob-存储二进制数据、clob-存储字符串数据、bfile-非结构化的二进制数据

Create

Create TABLE table_name{

column_name datatype[null | notnull],

...

[constraint]

}

语法说明

table_name：数据表名称

column_name：列名称

datatype：数据类型

null ：允许为空

constraint：设置约束

Alter

ALTER TABLE table_name ADD column_name|MODIFY column_name|DROP COLUMN column_name;

语法说明

ADD：添加

MODIFY：修改

DROP COLUMN：删除

Drop

DROP TABLE table_name;

添加约束

ALTER TABLE table_name

ADD CONSTRAINTS constraint_name PRIMARY KEY(column_name);

移除约束

ALTER TABLE table_name

DROP CONSTRAINTS constraint_name;

外键约束

CONSTRAINT constraint_name FOREGIN KEY(column_name)

REFERENCE table_name(column_name) ON DELETE CASCADE;

CHECK约束

CONSTRAINT constraint_name CHECK(condition)

condition是检查约束条件：age>=18 and age <= 30

UNIQUE约束

CONSTRAINT constraint_name UNIQUE(condition)

NOT NULL约束

ALTER TABLE table_name MODIFY column NOT NULL;

TRUNCATE

表中所有记录清除，比delete速度块

TRUNCATE TABLE table_name;

MERGE

同步数据信息，包含增加、修改操作

MERGE INTO table_name1 USING table_name2

ON(condition)

WHEN MATCHED THEN merge_update_clause

WHEN NOT MATCHED THEN merge_insert_clause;

语法说明

table_name1：要修改或添加的表

table_name2：参照表

condition：链接条件

merge_update_clause：更新SQL

merge_insert_clause：添加SQL

SELECT

SELECT [DISTINCT|ALL]

select_list FROM table_list

[where_clause]

[group_by_clause][HAVING condition]

[order_by_clause]

排序语法

Order by需要放置在select语句的最后面。

NULLS FIRST|NULLS LAST：对空字段的处理方式

子查询返回多行

如果子查询返回的值为多行值，需要用到IN关键字。也可以使用量化比较关键字SOME、ANY、ALL，配合< >使用

ANY：表示满足子查询结果的任何一个：SELECT * FROM TABLE WHERE A <ANY(...) AND

SOME：Any大多数用于非等于，SOME只用于等于：SELECT * FROM TABLE WHERE A <ANY(...) AND

ALL：满足子查询结果的所有结果：SELECT * FROM TABLE WHERE A <ALL(...) AND

连接查询

连接分为内连接、外连接、全连接、自连接

内连接：也称为简单连接，会把两个或多个表进行连接，只能查询出匹配的记录，不匹配的记录无法查询。

1. 等值连接：使用等号连接两个条件列表。SELECT * FROM A,B WHERE a.id = b.id 或者 INNER JOIN、可以简写成JOIN
2. 不等值连接：使用其他符号连接两个条件列表。

自连接：把自身表的一个引用作为另一个表来处理，获取一些特殊数据

外连接：分为左连接 LEFT JOIN、右连接 RIGHT JOIN、

全连接：FULL JOIN

内置函数

ABS(n)：绝对值，可以隐式转换

MOD(n2,n1)：返回n2除以n1的余数

SIGN(n)：返回参数n的符号。正数返回1，0返回0，负数返回-1

COS(n)：余弦、ACOS(n)：反余弦、COSH(n)：双曲余弦、SIN(n)：正弦

SINH 双曲正弦、ASIN 反正弦、TAN 正切、TANH 双曲正切、ATAN 反正切

CEIL：返回结果是大于等于输入参数的最小整数

FLOOR：返回结果是大于或等于参数的最大整数

SQRT：平方根

POWER：次幂结果

LOG：对数

LN：自然对数

ROUND：四舍五入

TRUNC：把数值根据integer的值进行截取

CHR：给定的Ascii码转字符

ASCII：返回参数首字母的ASCII码

LENGTH：得到指定字符串长度

SUBSTR：截取

CONCAT：连接

INSTR：搜索存在的位置

UPPER：转换大写

LOWER：转换小写

INITCAP：首字母大写

NLS_INITCAP：将指定参数的第一个字母转换大写。

NLS_LOWER：参数转换小写

NLSSORT：指定方式对char进行排序

REPLACE：替换函数。SELECT REPLACE('THIS IS A TEST','TEST','test') FROM DUAL;

RPAD：右侧填充

LPAD：左侧填充

TRIM：首位删除字符

RTRIM：右侧删除

LTRMI：左侧删除

NLS_CHARSET_ID：字符串名称对应ID

NLS_CHARSET_NAME：字符串ID对应名称

SYSDATE：获得当前日期

SYSTIMESTAMP：返回系统时间

DBTIMEZONE：数据库时区

ADD_MONTH：添加指定月份

SESSIONTIMEZONE：返回当前会话时区

LAST_DAY：返回该月份最后一天

NEXT_DAY：向后一周的char的对应日期

EXTRACT：提取指定日期

MONTHS_BETWEEN：两个日期之间月份

NEW_TIME：时区转换

BIN_TO_NUM：二进制转换十进制

CAST：类型转换

CHARTOROWID：字符串转换ROWID

ROWIDTOCHAR：ROWID转换字符串

TO_CHAR：数值转换字符型

TO_DATE：字符转日期

TO_NUMBER：字符串转数字

TO_SINGLE_BYTE：全角转半角

COALESCE：返回第一个不为null的表达式

LNNVL：得到要求条件之外的数据

NVL：替换NULL值

NVL2：同NVL类似，不同的是当export1为NULL时，返回expre3的值，不为NULL则返回expr2

AVG：平均值

USER：返回当前登录名

USERENV：返回当前会话信息。SELECT USERENV('ISDBA') FROM DUAL;

DECODE：该函数执行过程中，当expre符合条件search时返回result的值，过程可以重复多个

SELECT NAME,TYPE,DECODE(SIGN(TYPE-100),1,'充足',-1,'不足',0,'不足') FROM TABLE

PL/SQL

 

 

 

#

转载于:https://www.cnblogs.com/chenxygx/p/11225855.html