最全oracle语法整理

                                                                                            ----------------------------------------
                                                                                                 || ORACLE最全语法整理 ||
                                                                                             ----------------------------------------
【子查询】
单行子查询：（内部查询的结果作为外部查询的条件。）
    SELECT ENAME,JOB,SAL FROM EMP
    WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES')
any子查询：（<any:比子查询结果中任意的值都小，也就是说，比子查询结果中最大值还小，那么同理>any 表示比子查询结果中最小的还大。）
    SELECT ENAME,JOB,SAL FROM EMP
    WHERE SAL<ANY (SELECT SAL FROM EMP WHERE JOB='SALESMAN')
all子查询：（>ALL：比子查询结果中所有值还要大，也就是说，比子查询结果中最大值还要大。<ALL 表示比最小值还要小。）
    SELECT ENAME,JOB,SAL FROM EMP
    WHERE SAL>ALL (SELECT SAL FROM EMP WHERE JOB='SALESMAN')

【伪列】
ROWID 和 ROWNUM
ROWID 值:可以唯一的标识表中的一行。由于 ROWID 返回的是该行的物理地址，因此使用 ROWID 可以显示行是如何存储的。
ROWNUM:通过 ROWNUM 伪列可以限制查询结果集中返回的行数。
区别：
ROWNUM 与 ROWID 不同， ROWID 是插入记录时生成， ROWNUM 是查询数据时生成。
ROWID 标识的是行的物理地址。 ROWNUM 标识的是查询结果中的行的次序。

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

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

【函数】

单行函数：字符函数、数字函数、转换函数、日期函数
1、字符函数
ASCII(x)                        --返回字符 x 的 ASCII 码。
CONCAT(x,y)               --连接字符串 x 和 y。
INSTR(x, str [,start] [,n)    --在 x 中查找 str，可以指定从 start 开始，也可以指定从第 n 次开始。
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 的字串，从 staart 处开始，截取 length 个字符，缺省 length，默认到结尾。
2、数字函数
ABS(x)                    --x 绝对值 ABS(-3)=3
ACOS(x)                --x 的反余弦 ACOS(1)=0
COS(x)                  --余弦 COS(1)=1.57079633
CEIL(x)                  --大于或等于 x 的最小值 CEIL(5.4)=6
FLOOR(x)              --小于或等于 x 的最大值 FLOOR(5.8)=5
LOG(x,y)                 --x 为底 y 的对数 LOG(2,4)=2
MOD(x,y)                --x 除以 y 的余数 MOD(8,3)=2
POWER(x,y)            --x 的 y 次幂 POWER(2,3)=8
ROUND(x[,y])            --x 在第 y 位四舍五入 ROUND(3.456,2)=3.46
SQRT(x)                  --x 的平方根 SQRT(4)=2
TRUNC(x[,y])            --x 在第 y 位截断 TRUNC(3.456,2)=3.45
3、日期函数
ADD_MONTHS(d,n)，在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。d 表示日期， n 表示要加的月数。
LAST_DAY(d)，返回指定日期当月的最后一天。
ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式模型。默认 fmt 为 DDD，即月中的某一天。
     如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。
     如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。
     默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
     如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。
EXTRACT(fmt FROM d)，提取日期中的特定部分。
    fmt 为： YEAR、 MONTH、 DAY、 HOUR、 MINUTE、 SECOND。其中 YEAR、 MONTH、 DAY
    可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、 MINUTE、 SECOND 必
    须与 TIMESTAMP 类型匹配。
4、转换函数
TO_CHAR(d|n[,fmt])：
[对日期处理]SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS') "date" FROM DUAL; 结果：2009年08月11日 12:06:00
[对数字处理]SELECT TO_CHAR(-123123.45,'L9.9EEEEPR') "date" FROM DUAL；  结果：<￥1.2E+05>
TO_DATE(x [,fmt])：把一个字符串以 fmt 格式转换为一个日期类型，前面已经学习过。
TO_NUMBER(x[,fmt])：把一个字符串以 fmt 格式转换为一个数字。 fmt 格式字符参考表 3。
其他单行函数：
NVL(x,value)：如果 x 为空，返回 value，否则返回 x。
NVL2(x,value1,value2)：如果 x 非空，返回 value1，否则返回 value2。
聚合函数：AVG,SUM,MIN/MAX,COUNT

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

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

【表空间，数据库对象】
#同义词：就是给数据库对象一个别名。
序列： Oracle 中实现增长的对象。
视图：预定义的查询，作为表一样的查询使用，是一张虚拟表。
索引：对数据库表中的某些列进行排序，便于提高查询效率。
-------------------------------------------------------------------------------
创建同义词的语法是：
CREATE [OR REPLACE] [PUBLIC] SYSNONYM [schema.]synonym_name
FOR [schema.]object_name
语法解析：
① CREATE [OR REPLACE： ]表示在创建同义词时，如果该同义词已经存在，那么就用新
创建的同义词代替旧同义词。
② PULBIC：创建公有同义词时使用的关键字，一般情况下不需要创建公有同义词。
③ Oracle 中一个用户可以创建表、视图等多种数据库对象，一个用户和该用户下的所
有数据库对象的集合称为 Schema（中文称为模式或者方案），用户名就是 Schema
名。一个数据库对象的全称是：用户名.对象名，即 schema.object_name。
删除同义词语法：
DROP [PUBLIC] SYNONYM [schema.]sysnonym_name
-------------------------------------------------------------------------------
#序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列，
序列中的可以升序生成，也可以降序生成。
创建序列的语法是：
CREATE SEQUENCE sequence_name
[START WITH num]
[INCREMENT BY increment]
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE num|NOCACHE]
语法解析：
① START WITH：从某一个整数开始，升序默认值是 1，降序默认值是-1。
② INCREMENT BY：增长数。如果是正数则升序生成，如果是负数则降序生成。升序默
认值是 1，降序默认值是-1。
③ MAXVALUE：指最大值。
④ NOMAXVALUE：这是最大值的默认选项，升序的最大值是： 1027，降序默认值是-1。
⑤ MINVALUE：指最小值。⑥ NOMINVALUE：这是默认值选项，升序默认值是 1，降序默认值是-1026。
⑦ CYCLE：表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最
小值后，从最大值重新开始。
⑧ NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默
认 NOCYCLE。
⑨ CACHE：使用 CACHE 选项时，该序列会根据序列规则预生成一组序列号。保留在内
存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统
再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。 Oracle
默认会生产 20 个序列号。
⑩ NOCACHE：不预先在内存中生成序列号。

序列创建之后，可以通过序列对象的 CURRVAL 和 NEXTVAL 两个“伪列”分别访问该序
列的当前值和下一个值。
----------------------------------------------------------------------------------------
#视图（View）实际上是一张或者多张表上的预定义查询，这些表称为基表。从视图中
查询信息与从表中查询信息的方法完全相同。 只需要简单的 SELECT…FROM 即可
创建视图的语法：
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
AS
SELECT查询
[WITH READ ONLY CONSTRAINT]
语法解析：
1. OR REPLACE：如果视图已经存在，则替换旧视图。
2. FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表
创建成功后，视图才能正常使用。
3. NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。
4. WITH READ ONLY：默认可以通过视图对基表执行增删改操作，但是有很多在基表上
的限制（比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视
图执行 insert 操作）， WITH READ ONLY 说明视图是只读视图，不能通过该视图进行
增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。
-----------------------------------------------------------------------------------------------
#索引：
1、如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该列创建索引。
2、当从很多行的表中查询少数行时，也要考虑创建索引。有一条基本的准则是：当任何单个查询要检索的行少于或者
等于整个表行数的 10%时，索引就非常有用。
创建索引语法：
CREATE [UNIQUE] INDEX index_name ON
table_name(column_name[,column_name…])
语法解析：
1. UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。
2. index_name：指定索引名。
3. tabl_name：指定要为哪个表创建索引。
4. column_name：指定要对哪个列创建索引。我们也可以对多列创建索引；这种索引
称为组合索引。
------------------------------------------------------------------------------------------------
#表空间
系统表空间、TMEP 表空间、用户表自定义空间
语法结构：创建表空间
CREATE TABLESPACE 空间名称
DATAFILE '文件名1' SIZE 数字M
[,'文件名2' SIZE 数字….]
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 数字M
语法解析：
1. 文件名包括完整路径和文件名，每个数据文件定义了文件的初始大小，初始大小一
般以“M”为单位。一个表空间中可以有多个数据文件。
2. EXTENT MANAGEMENT LOCAL 指明表空间类型是：本地管理表空间。本地管理表空
间要求 Oracle 中的数据分区（Extent）大小统一。
3. UNIFORM SIZE：指定每个分区的统一大小。
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
【常规语法】
1、SELECT *|列名|表达式 FROM 表名 WHERE 条件 ORDER BY 列名        --普通查询
2、CREATE TABLE 表名 AS SELECT 语句                    --根据结果集创建表
3、INSERT INTO 表名(列名 1，列名 2……) VALUES (值 1，值 2……)        --数据插入
--------------------------------------------------------------------------------------------------
#常见的格式化字符如下：
--------------------------------------------------------------------------------------------------
    1. yyyy 表示四位年份
    2. mm 表示两位月份，比如 3 月表示为 03
    3. dd 表示两位日期
    4. hh24 表示小时从 0-23， hh12 也表示小时从 0-11。
    5. mi 表示分钟
    6. ss 表示秒
--------------------------------------------------------------------------------------------------
4、INSERT INTO 表 SELECT 子句                        --向表中插入一个结果集
5、Oracle 的简单查询和 SQL Server 一样都可以在查询列中使用常量
6、UPDATE 表名 SET 列名 1=值，列名 2=值…… WHERE 条件            --更新数据
7、DELETE FROM 表名 WHERE 条件                        --删除数据
8、TRUNCATE TABLE 表名                            --删除数据
--------------------------------------------------------------------------------------------------
#TRUNCATE 和 DELETE 都能把表中的数据全部删除，他们的区别是：
--------------------------------------------------------------------------------------------------
    1. TRUNCATE 是 DDL 命令，删除的数据不能恢复； DELETE 命令是 DML 命令，删除后
       的数据可以通过日志文件恢复。
    2. 如果一个表中数据记录很多， TRUNCATE 相对 DELETE 速度快。
       由于 TRUNCATE 命令比较危险，因此在实际开发中， TRUNCATE 命令慎用。
--------------------------------------------------------------------------------------------------
9、Oracle 中的算术运算符，没有 C#中的算术运算符丰富，只有+、 -、 *、 /四个，其中除号
(/)的结果是浮点数。求余运算只能借助函数： MOD(x,y)：返回 x 除以 y 的余数。
10、
#关系运算符：
---------------------------------------
运算符     说明    运算符 说明
---------------------------------------
 =         等于    >      大于
 <>或者!=  不等于  <=     小于或者等于
 <         小于    >=     大于或者等于
---------------------------------------
#逻辑运算符有三个： AND、 OR、 NOT
#字符串连接操作符：||
10、#distinct：                                --消除重复行
11、#NULL 值：如果某条记录中有缺少的数据值，就是空值（NULL 值）。空值不等于 0 或者空格，空值
是指未赋值、未知或不可用的值。任何数据类型的列都可以包括 NULL 值，除非该列被定义
为非空或者主键。
【在查询条件中 NULL 值用 IS NULL 作条件，非 NULL 值用 NOT IS NULL 做条件。】
12、in操作：在 Where 子句中可以使用 IN 操作符来查询其列值在指定的列表中的行。--in操作
13、在 WHERE 子句中，可以使用 BETWEEN 操作符来查询列值包含在指定区间内的行。 --between...and...
14、like['s%'、'_s%'、'%30\%%']                             --模糊查询
15、集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：
 INTERSECT(交集)，返回两个查询共有的记录。
 UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
 UNION(并集)，返回各个查询的所有记录，不包括重复记录。
 MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩
   余的记录。
16、在 SQL Server 中已经学习过内联接(inner join)、外联接(outer join)，外联接又分为左外联
    接(left outer join)和右外联接(right outer join)。 Oracle 中对两个表或者若干表之间的外联接用
   （+）表示。
【(+)： Oracle 专用的联接符，在条件中出现在左边指右外联接，出现在右边指左外联接。】

总结：
 #Oracle SQL 语句中有数据操纵语言（DML）、数据定义语言（DDL）、数据控制语言 （DCL）、事务控制语言（TCL）等等。
 #DML 语句包括增删改查语句， DDL 语句包括数据库对象创建、修改和删除语句，数据控制命令包括 GRANT、 REVOKE 等，事务控制命令有 COMMIT、 ROLLBACK 等。
 #数据库中建表常用的类型有：数字类型 number （p，s），可变字符串 varchar2（length），日期 date。
 #Oracle 中 default 是一个值，在 Oracle 中不存在 default 约束。
 #Oracle 的增删改语句与 SQL Server 基本一致，都是使用 INSERT、 UPDATE、 DELETE完成。
 #Oracle 高级查询中要注意： DISTINCT、 NULL、 IN、 BETWEEN…AND…。
 #集合操作有： UNION、 UNION ALL、 INTESECT、 MINUS。
 #联接查询有内联接和外联接。