Oracle之个人总结（20080218）

Oracle个人总结

1 SQL脚本类
1.1 基本SQL语句
1.1.1 select, from, where, group, order
1.2 常用函数
1.2.1 时间函数
1.2.2 字符串处理
1.2.3 数学函数

1.3 高级SQL语句
1.3.1 over, keep用法
1.3.2 层级显示: connect by, prior, level
1.4 SQL优化

2 pl/sql编程
2.1 函数(function)
2.2 存储过程(procedure)
2.3 任务(jobs)
2.4 包与包体(package/package bodies)
2.5 触发器(triggers)
2.6 序列(sequenece)
2.7 对象类型(object types)
2.8 游标的使用

3 系统管理
3.1 创建数据库实例
3.2 创建/管理表空间及常用参数含义
3.3 创建/管理用户及分配权限
3.4 数据文件管理
3.5 配置文件(pfile/spfile)
3.6 系统性能管理
3.7 其他管理
3.7.1 大表分区
3.7.2 如何创建数据库联接
3.7.3 Oracle锁
3.7.4 Oracle对象创建参数

4 缩略语词典

 

1 SQL脚本类
1.1 基本SQL语句
    一、SQL语句的基本框架：select [A] from [B] where [C] group by [D] order by [E]
 其中[A]代表的是所要选择的字段内容，有以下几种常用形式：
 1、from后面跟着的表的字段的内容，如T表有名为a的字段，则可以如下SQL实现：select t.a from T t;
 2、常量。通常是数字（1,2,3），字符串等内容（'AbcD','hello world!'），如：select 1,'hello world!' from dual;
 3、函数返回值。这个通常是对字段或常量处理后的数据组成的，或是系统函数（sysdate,level等），如：select sysdate, substr('abcdef',2,3) from dual;
 4、子查询。即在select语句中嵌套select语句，但是子查询的返回结果必须是唯一的，且只能为一个字段(ora-00913)，如果同时返回多个则会抱错(ora-01427);
 5、表达式。如case when then end语句。功能于decode类似，但更灵活;
 6、聚合函数。如count,sum等，此类函数与前面提到的函数的不同之处在于聚合函数需要以group by来分组（前提是有分组的字段）;
 7、OLAP函数。OLAP函数有关键字over，组成：function_name over(partition clause)。
    如：select e.*, count(*) over(partition by e.department_id, e.salary) from emp e;
    这里的函数可以是sum/rank/dense_rank等等组成。

 [B]子句有以下几种形式：
 1、系统表或视图。系统表如：dual,user_*,dba_*,v$*, v_$等表或视图。
 2、用户自定义表。如：select t.a from T t;此处T即为一个表;
 3、视图。视图的形式跟表相同。视图的优点在于视图的基表数据变化了则视图查询结果也就变化了，缺点是视图的速度在基表数据量比较大的时候会比较慢。
 4、子查询。即将子查询视为一个表来处理。原理上类似于视图;
 5、组合查询。即组合各个形式的查询。

 [C]子句有以下几种形式：
 1、简单条件关联。即Ta.a = Tb.a;
 2、常量条件。   即Ta.a = 30;
 3、函数条件。   即function(Ta.a) = Tc.f。

 [D]按照指定的字段分组，可以为字段，函数等。
 group by 语句除了基本的用法之外，还支持rollup或cube语句。下面介绍一个这两个函数的用法：
 rollup(a,b,c)：首先会对(a,b,c)进行group by; 然后对(a,b)进行group by; 然后是对(a)进行group by; 最后对全表进行group by。
 cube(a,b,c)：则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。
 这里不对详细功能进行展开，参考文档：http://xsb.itpub.net/post/419/29159 对此功能点作了详细的分析。
 其他还有关键字grouping()指明返回的当前列是否是一个合计列，如果是则返回1，否则返回0。
 grouping_id()也可以起到美化效果的作用。

 [E]对结果集排序，需要根据结果集中的字段来排序。也可以指定结果集中字段的顺序号来指定，如：order by 1，表示对结果集中的第一个字段排序。

1.2 常用函数及用法
1.2.1 时间函数
    一、设置当前session的时间显示格式：alter session set nls_date_format = 'yyyy-mm-dd';退出当前sesion重新登录后，该时间格式实效。需要重新设定。
 E:/01.公共区域>sqlplus lyon/passwd@oracle_192.168.11.211
 SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 2月 16 11:18:45 2008
 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 连接到:
 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
 With the Partitioning, OLAP and Data Mining options

 SQL> select sysdate from dual;

 SYSDATE
 ----------
 16-2月 -08

 SQL> alter session set nls_date_format='yyyymmdd';

 会话已更改。

 SQL> select sysdate from dual;

 SYSDATE
 --------
 20080216

 SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

 会话已更改。

 SQL> select sysdate from dual;

 SYSDATE
 -------------------
 2008/02/16 11:18:48

 

    二、时间函数主要有以下几种：sysdate, last_day...。下面介绍一下这些函数的含义及用法。
 1.sysdate：显示数据库系统的当前时间。使用：select sysdate from dual;

 2.add_months：在当前日期的基础上加/减月份，返回加/减后的日期。如：
     SQL> select add_months(sysdate, -3) three_months_ago from dual;

     THREE_MONTHS_AGO
     -------------------
     2007/11/16 11:23:16

 3.last_day：返回包含了日期参数的月份的最后一天的日期。如：
     SQL> select last_day(sysdate) from dual;

     LAST_DAY(SYSDATE)
     -------------------
     2008/02/29 11:24:04
     SQL> select last_day(date'1892-02-15') from dual;

     LAST_DAY(DATE'1892-
     -------------------
     1892/02/29 00:00:00

 4.months_between：判断两个日期之间的月份数量。如：
     SQL> select months_between(sysdate, date'2008-05-01') month_between from dual;

     MONTH_BETWEEN
     -------------
        -2.5007557

 5.trunc：返回由fmt指定的单位的日期d。如：
     select trunc(sysdate, 'dd') from dual;将如sysdate是2008-02-16 09:28:00，则显示的是2008-02-16。
     SQL> select trunc(sysdate,'dd') from dual;

     TRUNC(SYSDATE,'DD')
     -------------------
     2008/02/16 00:00:00

     SQL> select trunc(sysdate,'mm') from dual;

     TRUNC(SYSDATE,'MM')
     -------------------
     2008/02/01 00:00:00

 6.to_date：日期转换函数，将字符串类型转换为日期类型。如：select to_date('2008-02-16','yyyy-mm-dd') from dual;

 7.to_char：将输入的内容转换为字符串类型。对于日期类型的数据，如：select to_char(sysdate, 'yyyy-mm-dd') from dual;

 
 8.由于以上的时间函数对于日期的加减都是基于天或月的。没有基于小时、分钟的，所以又有了下面这个用法：
 显示当前时间，及距当前日期7天的时间：
     SQL> select sysdate, sysdate - interval '7' day from dual;

     SYSDATE             SYSDATE-INTERVAL'7'
     ------------------- -------------------
     2008/02/16 11:29:14 2008/02/09 11:29:14

 显示当前时间，及在当前时间以后12小时的时间：
     SQL> select sysdate, sysdate + interval '12' hour from dual;

     SYSDATE             SYSDATE+INTERVAL'12
     ------------------- -------------------
     2008/02/16 11:30:38 2008/02/16 23:30:38

 时间间隔乘以一个数字。显示8*3=24分钟以前的时间：
     SQL> select sysdate, sysdate - 8*interval '3' minute from dual;

     SYSDATE             SYSDATE-8*INTERVAL'
     ------------------- -------------------
     2008/02/16 11:31:32 2008/02/16 11:07:32

 同样的操作还有分钟（minute）、年（year）、秒（second）。

 其他还有一些不常用的函数，如：
 current_date()：返回当前会放时区中的当前日期;
 current_timestamp()：以timestamp with time zone数据类型返回当前会放时区中的当前日期;
 dbtimezone()：返回时区;
 extract()：找出日期或间隔值的字段值;  --select extract(month from sysdate) "This Month" from dual;
 localtimestamp()：返回会话中的日期和时间;

 --返回当前时间 年月日小时分秒毫秒
    　　select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;
    　　--返回当前 时间的秒毫秒，可以指定秒后面的精度(最大=9)
    　　select to_char(current_timestamp(9),'MI:SSxFF') from dual;

1.2.2 字符串处理函数
    一、字符串处理函数有以下几种：substr,instr,length...。介绍如下：
 1.substr(str,pos,len)：在字符串str中从pos位置开始截取len长度的字符串。如：
     SQL> select substr('hello world!',7,3) from dual;

     SUB
     ---
     wor

 2.instr(str, findstr[, pos][, nth])：在字符串str中查找findstr的字符串，
 可选参数：pos表示从str字符串中的第pos个字符开始查找。nth表示返回第nth个符合findstr查询条件的位置。
 如，例子中返回第二个字符为o的位置，在本例中即为8。
     SQL> select instr('hello world!','o',1,2) from dual;

     INSTR('HELLOWORLD!','O',1,2)
     ----------------------------
           8

 3.replace(str, src, dst)：将字符串str中的src字符串替换为dst字符串。返回替换后的字符串。
 如：
     SQL> select replace('hello world!', 'llo','amy') from dual;

     REPLACE('HEL
     ------------
     heamy world!

 4.translate(str1, src, dst)：也是替换字符串。但是与replace不同的是，translate是根据src中的字符找到在dst中的字符，
 然后按照指定的对应字符替换str1中所有src中的字符。
 如：
     SQL> select translate('hello world!', 'llo','amy') from dual;

     TRANSLATE('H
     ------------
     heaay wyrad!

 5.length(str)：返回字符串str的长度（即字符个数）。
     SQL> select length('hello world!') from dual;

     LENGTH('HELLOWORLD!')
     ---------------------
          12
 
 6.upper(str)和lower(str)：转化大小写。如：
     SQL> select upper('Hello World!'), lower('Hello WORLD!') from dual;

     UPPER('HELLO LOWER('HELLO
     ------------ ------------
     HELLO WORLD! hello world!

 7.nvl(x,value)和nvl2(x,value1,value2)：对空值得判断处理。
 nvl(x,value)：如果x为空，则返回value。不为空，则返回x。
 nvl2(x,value1,value2)：如果x为空，则返回value1。不为空，则返回value2。如：
     SQL> select nvl(null, 3), nvl2(null, 2, 4) from dual;

     NVL(NULL,3) NVL2(NULL,2,4)
     ----------- --------------
        3              4

 8.ltrim(x[,trim_str])/rtrim(x[,trim_str])/trim(x[,trim_str])：将x的左/右/左右去掉一些字符。默认取空格。可指定trim_str来指定要过滤得字符。
 如：
     SQL> select ltrim('  Hello') || rtrim(' world --','--') || trim('  wang  ') from dual;

     LTRIM('HELLO')||
     ----------------
     Hello world wang

 9.lpad(x, width[,pad_str])/rpad(x, width[,pad_str])：在字符串x的左/右添加字符pad_str，使x的长度达到width。如：
     SQL> select lpad('Hello world!',length('Hello world!')+3,'+') col_lpad,
       2  rpad('Hello world!',length('Hello world!')+4,'$') col_rpad from dual;

     COL_LPAD        COL_RPAD
     --------------- ----------------
     +++Hello world! Hello world!$$$$

 10.initcap(x)：用于将字符串x中的每个单词的首字符大写。如：
     SQL> select initcap('hello world!') from dual;

     INITCAP('HEL
     ------------
     Hello World!
 11.ascii(x)和chr(x)：将字符x转换为ASCII码，及ASCII码转换为对应字符。如：
     SQL> select ascii('a') col_ascii, chr(70) col_chr from dual;

      COL_ASCII C
     ---------- -
      97 F
1.2.3 数学函数
    数学函数这里不作详细介绍，简单罗列如下：
    1.abs(x)取绝对值函数;
    2.ceil(x)获得>=x的最小整数;
    3.power(x,y)加权函数;
    4.log(x)对数函数;
    5.mod(x,y)对x除以y所得的余数;
    6.round(x[,y])对x取整。可选参数y表示对第几位小数进行取整;
    7.sign(x)：如果x>0，返回1；小于0，则返回0;
    8.sqrt(x)：计算x的平方根。

   
1.3 高级SQL语句
1.3.1 Oracle开窗函数(over子句)
    Oracle开窗函数

1.3.2 层级显示: connect by, prior, level

1.4 SQL优化

2 pl/sql编程
2.1 函数(function)
2.2 存储过程(procedure)
2.3 任务(jobs)
2.4 包与包体(package/package bodies)
2.5 触发器(triggers)
2.6 序列(sequenece)
2.7 对象类型(object types)

3 系统管理
3.1 创建数据库实例
3.2 创建/管理表空间及常用参数含义
3.3 创建/管理用户及分配权限
3.4 数据文件管理
3.5 配置文件(pfile/spfile)
3.6 系统性能管理
3.7 其他管理
3.7.1 大表分区
3.7.2 如何创建数据库联接
3.7.3 Oracle锁
3.7.4 Oracle对象创建参数

4 缩略语词典