105---oracle的 函数，转换函数， 分析函数， 表空间，系统用户， 权限和角色， 序列， 同义词， 索引，表分区，，，

一、     函数

1)       单行函数

•       每一行只返回一个值

•       可以出现在 SELECT 子句中和 WHERE 子句中

2)       分组函数

•       根据分组的情况，返回每组里的一个结果值

•       可以在使用ORDER BY 和 HAVING子句中使用

3)       分析函数

•       分析函数根据一组行来计算聚合值

•       分析函数为每组记录返回多个行

•       日期函数

•       数字函数

•       字符函数

•       转换函数

•       其他函数

 

 

二、     转换函数

4)       常用的转换函数

     TO_CHAR

     TO_DATE

     TO_NUMBER

     SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS')

         FROM dual;

      SELECT TO_CHAR(1210.7, '$9,999.00') FROM dual;

     SELECTTO_DATE('2005-12-06','yyyy-mm-dd') FROM dual;

      SELECT TO_NUMBER('100') FROM dual;

 

 

5)       转换空值的函数

a)        NVL

b)       NVL2

c)        DECODE

 

SELECTename,

                sal+NVL(comm,0) sal1,

                NVL2(comm,sal+comm,sal) sal2,

                DECODE(to_char(hiredate,‘MM’),‘

                                  01’,‘一月’, ‘02’,‘二月’,

                                  ‘03’,‘三月‘,'04','四月',

                                  ‘05','五月','06','六月',

                                  '下半年') mon

  FROM employee;

 

三、     分析函数

 

6)       函数名([ 参数]) OVER( [ 分区子句 ] [ 排序子句])

        函数名：分析函数名字。

         参数：函数需要传入的参数。

         分区子句（PARTITION BY）：将查询结果分为不同的组，功能类似于GROUP BY语句

            排子句（ORDER BY）：将每个分区进行排序。

        例：

          RANK( ) OVER (PARTITION BY deptnoORDER BY sal

 

四、     表空间分类

 

一般不需要建临时和撤销表空间，除非把它们转移其他磁盘中以提高性能。

 

类别	举例	说   明
永久性表空间	SYSTEM，USERS	一般保存表、视图、过程和索引等的数据
临时性表空间	TEMP	只用于保存系统中短期活动的数据
撤销表空间	UNDO	用来帮助回退未提交的事务数据

 

7)       基于应用性能和管理方面的考虑，最好为不同的子系统创建独立的表空间

通过CREATE TABLESPACE命令创建表空间

CREATETABLESPACE tp_hr

   DATAFILE

   'd:\data\tp_hr01.dbf' SIZE 60M;

 

8)       --方法一：更改数据文件的大小

ALTERDATABASE DATAFILE

   'D:\DATA\tp_hr01.dbf'

   RESIZE 80M;

      

9)       --方法二：向表空间内添加数据文件

ALTERTABLESPACE tp_hr

   ADD DATAFILE

   'E:\DATA\tp_hr02.DBF' SIZE 20M

   AUTOEXTEND ON;

 

更改表空间状态只读

ALTER TABLESPACE tp_hr READ ONLY;

 

删除表空间前先备份再删除:通过DROP TABLESPACE命令创建表空间

DROPTABLESPACE tp_hr INCLUDING CONTENTS;

 

 

五、     登录管理后台

 

     SYS和SYSTEM用户都是Oracle 的系统用户，它们都使用SYSTEM表空间，SYS拥有更大的权限

SYS用户	SYSTEM用户
地位	Oracle的一个超级用户	Oracle默认的系统管理员，拥有DBA权限
作用	主要用来维护系统信息和管理实例	通常用来管理Oracle数据库的用户、权限和存储等
登录身份	只能以SYSDBA或SYSOPER角色登录	只能以Normal方式登录

 

10)  创建用户：建议为用户指定默认表空间或临时表空间

CREATEUSER user

       IDENTIFIED BY password

       [DEFAULT TABLESPACE tablespace]

       [TEMPORARY  TABLESPACE tablespace]

       [QUOTA {integer [K|M] | UNLIMITED}ONtablespace

       [QUOTA {integer [K|M] | UNLIMITED}ONtablespace ] ...]

       [PASSWORD EXPIRE ]

 

 

六、     权限和角色

 

权限指执行特定类型SQL 命令或访问其他对象的权利

11)  系统权限和对象权限

系统权限允许用户执行某些数据库操作

对象权限允许用户对某一特定对象执行特定的操作

角色是具有名称的一组权限的组合

常用系统预定义角色

CONNECT：临时用户

RESOURCE：更为可靠和正式的用户

DBA：数据库管理员角色，拥有管理数据库的最高权限

 

12)  --分配权限或角色

GRANT  privileges or role TO user;

13)  --撤销权限或角色

REVOKE  privileges or role FROM user;

 

 

七、     序列

 

序列是用于生成唯一、连续序号的对象

序列可以是升序的，也可以是降序的

14)  使用CREATE SEQUENCE语句创建序列

CREATESEQUENCE toys_seq

        START WITH 10

        INCREMENT BY 10

        MAXVALUE 2000

        MINVALUE 10

        NOCYCLE

        CACHE 10;

 

 

15)  访问序列:通过序列的伪列来访问序列的值

NEXTVAL返回序列的下一个值

CURRVAL返回序列的当前值

SYS_GUID函数

生成32位的唯一编码作为主键

与SYS_GUID函数区别

在不需要并行的环境中使用序列作为主键

在并行的环境里或者希望避免使用序列的况下使用函数

SQL>INSERT INTO toys (toyid, toyname, toyprice)

     VALUES ( toys_seq.NEXTVAL, ‘TWENTY’, 25);

SQL>INSERT INTO toys (toyid, toyname, toyprice)

     VALUES ( toys_seq.NEXTVAL, ’MAGIC PENCIL’,75);

SQL>SELECT toys_seq.CURRVAL FROM dual;

 

 

16)  更改和删除序列:

使用ALTER SEQUENCE语句修改序列，,不能更改序列的START WITH参数

SQL>ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE;

SQL>DROP SEQUENCE toys_seq;

 

 

 

八、     同义词

 

同义词是现有对象的一个别名

简化SQL语句

隐藏对象的名称和所有者

提供对对象的公共访问

17)  同义词共有两种类型：

       私有同义词只能在其模式内访问，且不能与当前模式的对象同名

     公有同义词可被所有的数据库用户访问

18)  同义词

私有同义词 ：CREATE OR REPLACE SYNONYM emp FOR SCOTT.emp;

公有同义词：CREATE PUBLIC SYNONYMpublic_sy_dept FOR SCOTT.dept;

删除同义词：SQL> DROP SYNONYM emp;

SQL> DROP PUBLIC SYNONYMemp_syn;

 

 

 

 

 

 

 

九、     索引

 

物理分类	逻辑分类
分区或非分区索引	单列或组合索引
B树索引	唯一或非唯一索引
正常或反向键索引	基于函数索引
位图索引

 

19)  B树索引结构: CREATE INDEX index_id ON t（id）;

 

20)  反向键索引：CREATE INDEXindex_reverse_empno ON emp(empno) REVERSE;

反转索引列键值的每个字节

CREATE INDEX index_reverse_empno ONemp(empno) REVERSE;

位图索引：CREATE BITMAP INDEX index_bit_job ON emp(job);

位图索引不直接存储ROWID，而是存储字节位到ROWID的映射

21)  索引使用原则：

表中导入数据后再创建索引。否则每次表中插入数据时都必须更新索引

在适当的表和字段上创建索引。如果经常检索的数据少于表中的15%则需要创建索引

限制表中索引的数目。索引越多，在修改表时对索引做出修改的工作量越大

 

 

 

 

 

 

十、     表分区

 

22)  允许用户将一个表分成多个分区

用户可以执行查询，只访问表中的特定分区

将不同的分区存储在不同的磁盘，提高访问性能和安全性

可以独立地备份和恢复每个分区

 

23)  范围分区

以表中的一个列或一组列的值的范围分区

PARTITIONBY RANGE (column_name)

(

  PARTITION part1 VALUES LESS THAN (range1),

  PARTITION part2 VALUES LESS THAN (range2),

  ...

  [PARTITION partN VALUES LESS THAN (MAXVALUE)]

);

 

 

间隔分区

实现范围分区的自动化

其中INTERVAL代表“间隔”，按照后面括号中的定义间隔添加分区；

NUMTOYMINTERVAL(n,'interval_unit') 函数

                 将n转换成interval_unit所指定的值,      

                 interval_unit可以为: YEAR, MONTH

PARTITIONBY RANGE(column_name)

                 INTERVAL(NUMTOYMINTERVAL(n,’interval_unit'))

                 (PARTITION P1 VALUES LESS THAN(range1));

 

24)  需求说明

根据订单表orders创建间隔分区表intervalOrders

按订单日期以年为间隔单位进行间隔分区

查询指定分区内容

删除分区内容并查看

查询每一个分区数据：

请先查找一共有几个分区，每个分区的分区名称是什么。

                 SELECTtable_name,partition_name

                    FROM user_tab_partitions

                 WHERE table_name=UPPER('intervalOrders');

 

表空间分为永久性表空间、临时性表空间、撤销表空间

Oracle中的 SYSTEM 用户和 SYS 用户具有管理权限，而 SCOTT 用户只有基本的权限

序列用于生成唯一、连续的序号

同义词是现有数据库对象的别名

索引是与表相关的一个可选结构，用于提高SQL 语句执行的性能

索引分为B树索引、唯一索引、反向键索引、位图索引等

表分区允许将一个表划分成几部分，以改善大型应用系统的性能