Oracle常用知识点汇总

数据类型

• NUMBER类型
     NUMBER(P,S)是最常见的数字类型，P为有效位数，Precision的取值范围为[1~38]，S为精度，Scale的取值范围为[-84~127]，整数部分的长度为P-S，不区分S的正负。若P小于S，则存储的是没有整数的小数。

实际值	数据类型	存储值
1234.56	NUMBER	1234.56
1234.56	NUMBER(6)	1234
1234.56	NUMBER(6,1)	1234.6
1234.56	NUMBER(6,-2)	1200
0.056	NUMBER(2,3)	0.056
0.0567	NUMBER(2,3)	0.057

• INTEGER类型
    INTEGER是NUMBER的子类型，它等同于NUMBER(38,0)，用来存储整数。若插入、更新的数值有小数，则会被四舍五入。
• FLOAT类型
    FLOAT是NUMBER的子类型，等同于，最大存储38位小数精度，在不确定小数精度的时候用这个，其他情况最好用NUMBER。
• CHAR
    CHAR(size [BYTE | CHAR])是固定长度的字符串类型，默认字节存储，可指定字符，不指定长度时默认长度为1，指定长度后会用空格填充来达到其最大长度，最多2000字节。
• NCHAR
    固定长度的Unicode字符串类型。
• VARCHAR2
    可变长度字符串类型，不会使用空格填充至最大长度，最多可以存储4,000字节的信息。
• NVARCHAR2
    可变长度的Unicode字符串类型。
• DATE
    日期和时间数据类型。

-- 获取系统date
select sysdate from dual;

• TIMESTAMP
    日期和时间数据类型，精确到小数秒。

-- 获取系统timesstamp
select systimestamp from dual;

• TIMESTAMP WITH TIME ZONE
    日期和时间数据类型，TIMESTAMP变种，含时区偏移量。
• INTERVAL
    时间段数据类型。

select sysdate - interval '10' day as "10天前",
       sysdate - interval '10' hour as "10小时前",
       sysdate - interval '10' minute as "10分钟前",
       sysdate - interval '10' second as "10秒钟前",
       sysdate - 10 as "10天前",
       sysdate - 10 / 24 as "10小时前",
       sysdate - 10 / (24 * 60) as "10分钟前",
       sysdate - 10 / (24 * 3600) as "10秒钟前"
  from dual;

数据排序

对结果集按照升序或者降序排序，默认升序，空值排在前面。

SELECT
    column_1
    ,column_2
    ,column_3
FROM
    table_name
ORDER BY
    column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST],
    column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST]

数据过滤及查询

Oracle中常用的过滤和查询关键词有DISTINCT、WHERE、AND、OR、BETWEEN、IN、LIKE、FETCH、EXISTS、NOT EXISTS、ANY，SOME、ALL等

• FETCH 限制查询返回的行数，类似mysql的limit
  [ OFFSET offset ROWS] FETCH NEXT [ row_count | percent PERCENT ] ROWS [ ONLY | WITH TIES ]
  或
  [ OFFSET offset ROWS] FETCH FIRST [ row_count | percent PERCENT ] ROW [ ONLY | WITH TIES ]

SELECT product_name,quantity FROM products ORDER BY quantity DESC FETCH NEXT 5 ROWS ONLY

• SOME 满足其中某个条件

-- 等于条件中的任何一个值
SELECT * FROM COMSUMER WHERE age = SOME (SELECT age FROM COMSUMER WHERE ...)

• ANY 不满足条件，大于最小值，小于最大值

-- 小于条件中的任何一个值，即小于最大值的所有数据均返回
SELECT * FROM COMSUMER WHERE age < ANY (SELECT age FROM COMSUMER WHERE ...)
-- 大于条件中的任何一个值，即大于最小值的所有数据均返回
SELECT * FROM COMSUMER WHERE age > ANY (SELECT age FROM COMSUMER WHERE ...)

• ALL 满足所有条件，大于最大值，小于最小值

-- 小于条件中所有值，即小于最小值的数据均返回
SELECT * FROM COMSUMER WHERE age < ALL (SELECT age FROM COMSUMER WHERE ...)
-- 大于条件中所有值，即大于最大值的数据均返回
SELECT * FROM COMSUMER WHERE age > ALL (SELECT age FROM COMSUMER WHERE ...)

表连接

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• CROSS JOIN
• SELF JOIN

数据分组

• GROUP BY
• HAVING

集合运算

• UNION - 演示如何将两个查询的结果合并为一个结果。
• INTERSECT - 演示如何实现两个独立查询的结果的交集。
• MINUS - 学习如何从一个结果集中减去另一个结果(也就是求差集)。

数据操纵语言(DML，Data Manipulation Language)

• SELECT
• INSERT 在表中插入一行。
• INSERT INTO SELECT - 从查询结果中将数据插入到表中。
• INSERT ALL - 讨论多重插入语句，将多行插入到一个或多个表中。
• UPDATE - 演示如何更新表的存在的数据值。
• DELETE - 演示如何从表中删除一行或多行。
• MERGE - 使用单个语句逐步完成插入，更新和删除操作。

数据定义语言(DDL，Data Definition Language)

• CREATE TABLE - 演示如何在数据库中创建新表。
• IDENTITY列 - 了解如何使用IDENTITY子句来定义表的标识列。
• ALTER TABLE - 演示如何改变表的结构。
• ALTER TABLE ADD列 - 显示如何将一个或多个列添加到现有表
• ALTER TABLE MODIFY列 - 演示如何更改表中现有列的定义。
• DROP COLUMN - 了解如何使用各种语句从表中删除一列或多列。
• DROP TABLE - 演示如何从数据库中删除表。
• TRUNCATE TABLE - 更快，更有效地删除表中的所有数据。
• RENAME TABLE - 学习如何重命名表和处理其依赖对象的过程。

约束

• 主键 - 解释主键概念，并演示如何使用主键约束来管理表的主键。
• 外键 - 解释介绍外键概念，并演示如何使用外键约束来强制表之间的关系。
• NOT NULL约束 - 演示如何确保列不接受NULL值。
• UNIQUE约束 - 讨论如何确保存储在一列或一组列中的数据在整个表内的行之间是唯一的。
• CHECK约束 - 在将数据存储到表中之前添加用于检查数据的逻辑的过程。

字符串函数

• Ascii()函数 Ascii()函数可以返回代表指定字符的数字值代码
• Asciistr()函数 可以使用数据库字符集将任何字符集中的字符串转换为ASCII字符串
• Chr()函数 可以根据数字代码返回字符，其功能和ASCII函数相反。
• Compose()函数可以返回一个Unicode字符串
• CONCAT()函数在Oracle中可以用于将两个字符串连接在一起
• ||运算符可以将两个或两个以上的字符串连接在一起
• Convert()函数可以将字符串从一个字符集转换为另一个字符集。
• Dump()函数可以返回一个varchar2值，这个值包含了数据类型代码、字节长度和表达式的内部表示形式。
• Initcap()函数可以将每个单词中的第一个字符设置为大写，其余的设置为小写。

字符函数
ASCII(X)，返回字符X的ASCII码
CONCAT(X,Y)，拼接字符串X和Y
instr(x,str[,start][,n])，从字符串x中查找str,可以指定开始位置及长度
length(x)，返回字符串x的长度
lower(x)，将字符串x转换成小写
upper(x)，将字符串x转换成大写
ltrim(x[,trim_str])，把字符串x的左边截取trim_str字符串，缺省截取空格
rtrim(x[,trim_str])，把字符串x的右边截去trim_str字符串，缺省截去空格
trim([trim_str from ]x)，截去字符串x两边的trim_str，缺省截去空格
replace(x,old,new)，在字符串x中查找old，并用new替换
substr(x,start[,length])，返回字符串x的子串，截取长度为length，缺省length时默认到结尾
lpad(x,n[,y])，将字符串x左边以y填充到长度n
rpad(x,n[,y])，将字符串x右边以y填充到长度n

数字函数
abs(x)，x的绝对值
cos(x)，x的余弦
acos(x)，x的反余弦
ceil(x)，大于等于
floor(x)，小于等于
log(x,y)，x为底y的对数
mod(x,y)，x除以y的余数
power(x,y)，x的y次幂
round(x[,y])，x在y位四舍五入
sqrt(x)，x的平方根
trunc(x[,y])，x在第y位直接截断trunc(3.456,2)=3.45
sign(x)，类似bool型，x为0则为0，大于0则为1，小于0则为-1

转换函数
to_char(d|n[,fmt])，把日期或者数字转换为指定格式的字符串，如to_chart(sysdate,‘YYYYMMDD HH24:MI:SS’)，
to_date(x[,fmt])，把字符串x转换为fmt格式的日期
to_number(x[,fmt])，把字符串x转换为fmt格式的数字，如to_number(‘333.9998899’,‘99.99’)则为333.99

日期函数
sysdate 返回当前日期
add_month(d,n)，在日期d上加上指定的月数n，返回计算后的新日期
last_day(d)，返回指定日期当月的最后一天
round(d[,fmt])，日期的四舍五入，fmt默认为DDD，即舍入结果为月中的一天，当为YEAR时舍入到某年的1月1日，前半年舍去，后半年到下一年，fmt为MONTH则舍入到某月的1日，fmt为DDD舍入到最靠近的某一天，fmt为DAY则舍入到最近的周的周日
extract(fmt from d)，提取日期d中的特定部分，fmt可为YEAR/MONTH/DAY/HOUR/MINUTE/SECOND

填充类函数
nvl(x,value)，如果x为空则返回value，否则返回x
nvl2(x,value1,value2)，如果x非空则返回value1，否则返回value2

聚合函数
avg(表达式)，对列x求平均值
sum(表达式)，求和
min(表达式)，最小值
max(表达式)，最大值
count(表达式)，数据统计

ORACLE事务
事务在数据库中是工作的逻辑单元，单个事务是由一个或多个完成一组的相关行为的SQL语句组成，通过事务机制，可以确保这一组SQL语句所作的操作要么都成功执行，完成整个工作单元操作，要么一个也不执行。

• 原子性(Atomicity)：一个事务里面所有包含的SQL语句都是一个整体，是不可分割的，要么不做，要么都做。
• 一致性(Consistency)：事务开始时，数据库中的数据是一致的，事务结束时，数据库的数据也应该是一致的。
• 隔离性(Isolation)：数据库允许多个并发事务同时对其中的数据进行读写和修改的能力，隔离性可以防止事务在并发执行时，由于他们的操作命令交叉执行而导致的数据不一致状态。
• 持久性 (Durability) : 当事务结束后，它对数据库中的影响是永久的，即便系统遇到故障的情况下，数据也不会丢失。
  一组SQL语句操作要成为事务，数据库管理系统必须保证这组操作的原子性（Atomicity）、一致性（consistency）、隔离性（Isolation）和持久性（Durability），这就是ACID特性。
  COMMIT语句（提交事务）、ROLLBACK语句（回滚事务）、SET TRANSACTION语句（设置事务）、LOCK TABLE语句（锁表）