Oracle小结一

一、Oracle概述
    1.基本概念
        （1） 一个Oracle服务器是由一个Oracle实例和一个Oracle数据库组成.
        （2） Oracle数据库：硬盘上实际存放数据文件的地方.
        （3） Oracle实例：位于物理内存里的数据结构，它由一个共享的内存池和多个后台进程所组成，共享的内存池可以被所有进程访问，
                    用户如果要存取数据库里的数据，必须通过实例才能实现，而不能直接读取硬盘上的文件.
        （4） 在Oracle看来，"数据库"是指硬盘上文件的逻辑集合，必须要与内存里实例合作，才能对外提供数据管理服务.
        （5） 表空间（users）和数据文件
                * 表空间由多个数据文件组成.
                * 数据文件只能属于一个表空间.
                * 表空间为逻辑概念，数据文件为物理概念.
        （6）段、区、块
                * 段存在于表空间中.
                * 段是区的集合.
                * 区是数据块的集合.
                * 数据块会被映射到磁盘块.
    2.常用操作
            set linesize 120:设置行宽120字符
            set pagesize 30:设置每页显示多少行
            col ename for a8:设置ename这个字段宽为8个字符
            col sal for 9999:设置sal这个字段为4位数
            host cls ： 清屏
            c命令，change.例如： c /fomr/from
            ed : 打开编辑器
            
        * select * from tab; 查看当前用户下的表
        * desc emp; 查看表结构
        * select * from emp; 查询所有员工信息
        * select empno,ename,sal,sal*12 年薪,comm 奖金,sal*12+nvl(comm,0) 年收入 from emp;
                nvl(comm,0) 的意思是，如果comm为null，则用0代替.
        
        * select distinct deptno,job from emp; 查询员工的部门号和职位，去掉重复的信息.distinct作用于后面所有列
        * select concat('Hello','World') from dual; concat为连接字符串，dual为伪表.
        * select 'Hello'||'World' from dual; ||为连接符
        * save c:\a.sql   :  保存上条语句
        * @c:\a.sql       :  执行保存的语句.
    3.过滤
        * 使用where子句，将不满足条件的过滤掉
        * 比较运算
                := 赋值   
                = 等于（这里不是==）
                >,>=,<,<= 这些不多说了
                <> 不等于（也可以是!=）
                between..and..  : 在两个值之间（包含边界）
                in(set)  :  等于值列表中的一个
                like  ： 模糊查询   其中，% 代表零个或多个字符（即任意个字符）；_  下划线代表一个字符.
                is null ： 空值
                escape ： 回避特殊符号，使用转移符，可以用escape定义转移符
                        例如：查找名字中含有_的信息可以这样，select * from emp where ename like '%\_%' escape '\';
        * 逻辑运算：and,or,not        
        * 排序： order by子句，默认为升序，后边加 desc（descend） 可转为降序    
                 示例： select * from emp order by sal; 升序
                       select * from emp order by sal desc;降序
             order by后边跟多列，先按第一列排序，第一列相同的按第二列排序.
    4.函数
        函数有单行函数和多行函数
        （1）单行函数
            单行函数:字符函数，数值函数，日期函数，转换函数，通用函数.
            * 字符函数：
                大小写控制函数:lower(转小写 )，upper(大写),initcap(每个单词首字母大写)
                字符控制函数：concat('Hello','World')---HelloWorld
                         substr('HelloWorld',1,5)---Hello:从第一位取5个字符，数据库中都从1开始.
                         length('HelloWorld')---10    
                         instr('HelloWorld','w')---6 :返回后边字符在前边字符串中的位置，如果没有则返回0
                         lpad(salary,10,'*') ---*****24000 : 左填充
                         rpad(salary,10,'*') ---24000***** : 右填充
                         trim('H' from 'HelloWorldH')---elloWorld : 去掉前后指定字符.    
                         replace('abcd','b','m')---amcd : 替换字符.
            * 数值函数：
                round(45.926,2) -- 45.93 ：四舍五入
                    rount(45.926,-1) -- 50
                    rount(45.926,-2) -- 0
                trunc(45.926,2) -- 45.92:截断
                    trunc(45.926,-1) -- 40
                    trunc(45.926,-2) -- 0
                mod(1600,300) -- 100 : 取余
            * 日期函数：
                sysdate：系统时间
                months_between : 两个日期相差的月数
                add_months : 向指定日期中加上若干月数
                next_day : 指定日期的下一个日期
                last_day : 本月的最后一天
                round : 日期四舍五入
                trunc : 日期截断
            * 转换函数：
                隐式数据类型转换：(转换的前提，被转换的对象是可以转换的)
                    varchar2 or char --> number
                    varchar2 or char --> date
                    number --> varchar2
                    date --> varchar2
                显式数据类型转换：
                    to_char:
                        * 对日期的转换
                            例如to_char(date,'format_model')
                                格式：必须包含在单引号中，且大小写敏感
                                    可以包含任意的有效的日期格式
                            格式元素：YYYY : 2011
                                   YEAR : twenty eleven    
                                   MM ：04
                                   MONTH : 4月
                                   DY ： 星期一
                                   DAY ： 星期一
                                   DD ： 02     月份中的第几天
                            时间格式：HH24:MI:SS AM    
                        * 对数字的转换
                            to_char(number,'format_model')                            
                            格式：
                                9 ： 数字
                                0 ：零
                                $ ：美元符
                                L ： 本地货币符号
                                . ： 小数点
                                , ： 千位符
                    to_number:可以将字符转换成数字
                    to_date : 将字符转换成日期，字符必须符合一定格式.
            * 通用函数：这些函数适用于任何数据类型，同时也适用于空值
                nvl (expr1,expr2) ： 如果expr1为空，则将空值转换成expr2
                nvl2 (expr1,expr2,expr3)： expr1部位null，返回expr2;为null，返回expr3
                nullif (expr1,expr2) : 相等返回null，不等返回expr1
                coalesce (expr1,expr2,...,exprn):从左至右找到第一个不为null的值.
            * 条件表达式：
                case表达式：SQL99的语法.
                decode函数：Oracle自己的语法.
                示例：select ename,job,sal,case job when 'PRESIDENT' then sal+1000
                                                  when 'MANAGER' then sal+800
                                                  else sal+400
                                                  end 涨后薪水
                                        from emp;
                    select ename,job,sal,decode(job,'PRESIDENT',sal+1000,
                                                    'MANAGER',sal+800,        
                                                    sal+400) 涨后薪水
                                        from emp;
        （2）分组
            分组函数作用于一组数据，并对一组数据返回一个值.    
            组函数会自动虑空，如果不想它忽略空值，可以使用nvl函数.                                    
            * avg
                avg(comm) :奖金总额除以有奖金的人数. 把没有奖金的（即空）忽略掉
            * count
                    count(distinct expr) : 返回expr非空且不重复的记录总数.
            * max
            * min
            * sum        
                     分组数据：group by子句
                      注意：在select列表中所有未包含在组函数中的列都应该,必须包含在group by 子句中.
                          包含在group by 子句中的列不必包含在select列表中.
                          不能在where子句中使用组函数.
                          可以在having子句中使用组函数.
    5.多表查询
        笛卡尔集：如果在两个或两个以上的表中获取数据就有可能产生笛卡尔集.
        为了避免笛卡尔集，可以在where中加入有效的连接条件.
        在实际运行环境下，应该避免使用全笛卡尔集.
        （1）连接的类型
            * Oracle连接：                    
                Equijoin:等值连接
                Non-equijoin:不等只连接
                Outer join:外连接
                Selfjoin：自连接
            * SQL：1999
                Cross joins
                Natural joins
                Using clause
                Full or two sided outer joins
            等值连接：
                查询员工信息，有员工表，部门表，部门名称只存在部门表中.    
                select empno,ename,sal,dname from emp,dept where emp.deptno=dept.deptno;
            不等值连接：
                查询员工信息: 姓名 月薪  工资级别
                select ename,sal,grade from emp e,salgrade s
                where e.sal between s.losal and s.hisal;
            外连接：
                使用外连接可以查询不满足连接条件的数据.
                即，当条件不成立时，仍然希望在结果中包含不成立的记录.
                外连接的符号是(+)
                左外连接：where d.deptno=e.deptno 当不成立时，等号左边代表的表的信息仍然被包含
                    写法：where d.deptno=e.deptno(+)
                右外连接：where d.deptno=e.deptno 当不成立时，等号右边代表的表的信息任然被包含                
                    写法：where d.deptno(+)=e.deptno
            自连接：利用表的别名，将同一张表视为多张表（不适合操作大表）
                示例：查询员工信息：***的老板是***
                    select e.ename||'的老板是'||b.ename
                    from emp e, emp b
                    where e.mgr=b.empno;
            层次查询：对同一张表的前后两次操作（想象成一颗树），进行连接.
                示例：select level,empno,ename,mgr from emp
                    connect by prior empno=mgr
                    start with mgr is null
                    order by 1;(按值为高低向下排)
    6.子查询
        查询薪水大于scott的员工的信息
        select * from emp where sal > (select sal from emp where ename='scott');
        子查询需注意的问题：
        * 可以在主查询的where，select，from，having后面，放置子查询.
        * 不可以在group by 后面放置子查询.
        * 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用即可.
        * 一般先执行子查询，再执行主查询，但相关子查询除外.
        * 单行子查询只能使用单行操作符，多行子查询只能使用多行操作符.
        * 注意子查询中的null.
        （1）单行子查询
            *只返回一行.
            *使用单行比较操作符.
        （2）多行子查询
            *返回多行.
            *使用多行比较操作符.
                in : 等于列表中的任何一个.
                any : 和子查询返回的任意一个值比较.
                all : 和子查询返回的所有值比较.
    7.集合运算
        （1）集合运算符
            *union（去重复元素）/union all（包括重复元素） ： 并集
            *intersect : 交集
            *minus（返回属于第一集合，不属于第二集合的记录） ： 差集
            注意：参与运算的各个集合必须列数相同，且类型一致.
                采用第一个集合的表头作为最后的表头.
                如果排序，必须在每个集合后使用相同的order by.
                可以使用括号.
            示例：利用集合运算实现group by的增强
            select deptno,job,sum(sal) from emp group by deptno,job
            union
            select deptno,to_char(null),sum(sal) from emp group by deptno
            union
            select to_number(null),to_char(null),sum(sal) from emp;
    8.练习
        （1）找到emp表中工资最高的前三名，如下格式：
                ROWNUM      EMPNO ENAME        SAL                                                                                  
            ---------- ---------- ---------- -----                                                                                  
                     1       7839 KING        5000                                                                                  
                     2       7788 SCOTT       3000                                                                                  
                     3       7902 FORD        3000         
            答案：select rownum,empno,ename,sal
                from (select * from emp order by sal desc)
                where rownum <= 3;
        （2）找到员工表中薪水大于本部门平均薪水的员工，如下格式：
                 EMPNO ENAME        SAL     AVGSAL                                                                                  
            ---------- ---------- ----- ----------                                                                                  
                  7499 ALLEN       1600 1566.66667                                                                                  
                  7566 JONES       2975       2175                                                                                  
                  7698 BLAKE       2850 1566.66667                                                                                  
                  7788 SCOTT       3000       2175                                                                                  
                  7839 KING        5000 2916.66667                                                                                  
                  7902 FORD        3000       2175
            答案一（多表查询）：
                select empno,ename,sal,avgsal
                from emp e, (select deptno,avg(sal) avgsal from emp group by deptno)
                where e.deptno=d.deptno and e.sal > d.avgsal;
            答案二（相关子查询）：
                select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
                from emp e
                where sal > (select avg(sal) from emp where deptno=e.deptno);
        （3）统计每年入职的员工个数，格式如下：
                 TOTAL       1980       1981       1982       1987                                                                                                
            ---------- ---------- ---------- ---------- ----------                                                                                                
                    14          1         10          1          2
            答案：select count(*) Total,sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
                          sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
                          sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
                          sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
                from emp;
    9.处理数据
        （1）delete 和 truncate table 的区别
            * delete是DML，truncate是DDL.（DML可以rollback，DDL不可以）
            * delete逐条删除；truncate先摧毁，再重建.
            * delete会产生碎片；truncate不会.
            * delete不会释放空间；truncate会.
        另外：show recyclebin :查看回收站
            purge recyclebin : 清空回收站
            如果在删除一个表或者数据时，后面加purge，则不走回收站，直接删除.
            例如： drop table test purge; 则彻底删除.
    10.数据库事务
        （1）数据库事物的组成：
            * 一个或多个DML语句.
            * 一个DDL语句.
            * 一个DCL语句.
        （2）数据库事务的开始与结束
            * 起始标志： DML语句
            * 结束标志：提交：显式提交 commit
                          隐式提交 DDL 语句，正常退出 exit
                     回滚：显式  rollback
                         隐式  非正常退出，掉电，宕机
        （3）控制事务
            * 使用savepoint语句在当前事务中创建保存点.
            * 使用rollback to savepoint 语句回滚到创建的保存点.
    11.数据库的隔离级别
        （1）read uncommitted:读未提交数据
            允许事物读取未被其他事物提交的变更，脏读，不可重复读和幻读的问题都会出现.
        （2）read commited：读已提交数据
            只允许事物读取已经被其它事务提交的变更，可以避免脏读，但不可重复读和幻读问题仍然可能出现.
        （3）repeatable read ： 可重复读
            确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新，可以避免脏读和不可重复读，但幻读问题仍然存在.
        （4）serializable :　串行化
            确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作，所有并发问题都可避免，但性能十分低下．
        （5）并发运行多个事务，且这些事务访问数据库中相同的数据时，如果没有必要的隔离机制，会导致各种并发问题：
            * 脏读 ：一个事务t1读到另一个事务t2还没有提交的数据，如果t2回滚，t1读的数据是无效的.
            * 不可重复读：一个事务t1读取一个字段，然后t2更新了该字段，之后t1再读同一字段，值就不同了.
            * 幻读： t1读取一个字段，然后t2在该表中插入一些新的行，之后，t1再读同一表，就会多出几行.
        （6）Oracle支持两种事务隔离级别：read commited,serializable.
            且默认隔离级别为：read commited.
            Mysql支持以上四种隔离级别.默认隔离级别为：repeatable read.
二、常见的数据库对象
    1.表 ：基本的数据存储集合，由行和列组成.
        （1）创建表
            * create table test (id number,name varchar2(20),hidate date default sysdate);
            * 使用子查询创建表
                create table test as select * from emp; //即拷贝emp.
        （2）修改表
            * 追加：alter table test1 add photo blob;
            * 修改：alter table test1 modify name varchar(20);
            * 删除：alter table test1 drop column photo;
            * 重命名：alter table test1 rename column name to username;
        （3）约束
            约束类型：    *not null
                    *unique
                    *primary key
                    *foreign key
                    *check
            示例：create table person(
                id varchar2(18) constraint person_PK primary key,
                name varchar2(4) constraint person_Name not null,
                gender varchar2(2) constraint person_Gender check (gender in ('男','女')),
                email varchar2(30) constraint person_Email unique,
                deptno number constraint person_FK references dept(deptno） on delete cascade //级联删除子表.
                );
            其中：on delete cascade : 当删除附表时，级联删除子表记录.
                on delete set null: 将子表的相关依赖记录的外键值置为null.        
    2.视图（view）：从表抽出的逻辑上相关的数据集合.
        （1）视图概述：
            * 视图不能提高性能.
            * 不建议通过视图对表进行修改.
            * 视图是一种虚表.
            * 视图建立在已有表的基础上，视图赖以建立的这些表称为基表.
            * 向视图提供数据内容的语句为select语句，可以将视图理解为存储起来的select语句.
        （2）视图的操作
            * 创建视图：员工号，姓名，月薪，年薪
                    create view view1
                    as
                    select empno,ename,sal,sal*12 annlsal from emp;
            * 查询视图：select * from view1
            * 修改视图：（视图没有alter，这里用create or replace view 子句修改视图）
                    create or replace view view1
                    as
                    select empno,ename,sal,sal*12 annlsal,dname
                    from emp e,dept d
                    where e.deptno=d.deptno
                    with read only;
                由于非常不建议在视图中使用DML操作，因此一般使用with read only来屏蔽对视图的DML操作.
            * 删除视图：（只是删除视图的定义，并不会删除基表中的数据）
                    drop view view1;
    3.序列（sequence）：提供有规律的数值.
        （1）序列概述
            *序列：可共多个用户用来产生唯一数值的数据库对象.
            *可以自动提供唯一的数值.
            *共享对象.
            *主要用于提供主键值.
            *将序列值装入内存可以提高访问效率.
        （2）序列操作
            * 创建序列：create sequence myseq; //还有许多参数可以设置，具体查文档，一般采用默认即可.
                可设置的参数：    increment by n
                            start with n
                            maxvalue n | nomaxvalue
                            minvalue n | nomixvalue
                            cycle | nocycle
                            cache n | nocache
                可以在数据字典视图user_sequences获取序列定义的一些信息:
                select sequence_name,min_value,max_value,increment by,last_number
                from user_sequences;
            * nextval 和 currval 伪列
                nextval 返回序列中下一个有效的值，任何用户都可以引用.
                currval 中存放序列的当前值.
                nextval应在currval之前指定，二者应同时有效.
                示例：根据上边定义的myseq来演示：
                    select myseq.nextval from dual;//第一次查询会返回1，再次查会返回2
                    select myseq.currval from dual;//返回当前的值，
            * 应用序列
                create table testseq (id number,name varchar2(20));
                insert into testseq values(myseq.nextval,'aaa');//应用序列
            * 使用序列中的问题
                序列在下列情况下出现裂缝：
                    回滚.
                    多个表同时使用同一个序列.
                    系统异常.
            * 修改序列
                修改序列的增量，最大值，最小值，循环选项，或知否装入内存等
                alter sequence myseq
                                increment by 20
                                maxvalue 9999
                                nocache
                                nocycle;
                注意事项：    必须是序列的拥有者或对序列有alter权限
                        只有将来的序列值会被改变.
                        改变序列的初始值只能通过删除序列之后重建序列的方法实现.
            * 删除序列
                drop sequence myseq;
                删除之后，序列不能再次被引用.
    4.索引：提高查询的效率.
        （1）索引概述
            * 一种独立于表的模式对象，可以存储与表不同的磁盘或表空间中.
            * 索引被删除或损坏，不会对表产生影响，其影响的只是查询的速度.
            * 索引一旦建立，Oracle管理系统会对其进行自动维护，而且有Oracle管理系统决定何时使用索引，
                用户不用在查询语句中指定使用哪个索引.
            * 在删除一个表时，所有基于该表的索引会自动被删除.
            * 通过指针加速oracle 服务器的查询速度.
            * 通过快速定位数据的方法，减少磁盘I/O.
        （2）创建索引
            * 自动创建：在定义primary key 或 unique 约束后系统自动在响应的列上创建唯一性索引.
            * 手动创建：用户可以在其他列上创建非唯一的索引，以加速查询.
                在一个或多个列上创建索引：
                create index index on table (column[,column]...);
                在表emp的列last_name上创建索引
                create index emp_last_name_idx
                on             emp(last_name);
            什么时候创建索引：
                列中数据值分布范围很广的时候.
                列经常在where子句或连接条件中出现.
                列经常被访问而且数据量很大，访问的数据大概站数据总量的2%到4%
            什么时候不要创建索引：
                    表很小.
                    表经常更新.
                    列不经常作为连接条件或出现在WHERE子句中.
                    查询的数据大于2%到4%.
        （3）查询索引
            可以使用数据字典视图user_indexes 和 user_ind_columns 查看索引的信息.
            select  ic.index_name,ic.column_name,
                    ic.column_position col_pos,ix.uniqueness
            from    user_indexes ix, user_ind_columns ic
            where    ic.index_name = ix.index_name
            and        ic.table_name = 'emp';
        （4）删除索引
            删除索引dept_index:
                drop index dept_index;    
            只有索引的拥有者或拥有 drop any index权限的用户才可以删除索引.
    5.同义词：给对象起别名.
        （1）好处
            * 方便访问其它用户的对象.
            * 缩短对象名字的长度.    
        （2）创建和删除同义词
            * 为视图dept_sum_view1创建同义词
                create synonyn dsum
                for dept_sum_view1;
            * 删除同义词
                drop synonyn dsum;