初识oracle

        linux 下连接oracle
            实例：
            [root@VM_0_9_centos ~]# su - oracle
            Last login: Wed Dec 12 15:30:37 CST 2018
            [oracle@VM_0_9_centos ~]$  sqlplus /nolog

            SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 12 15:33:18 2018

            Copyright (c) 1982, 2009, Oracle.  All rights reserved.

            SQL>  connect /as sysdba
            Connected.
            SQL> select * from dual;

            D
            -
            X
        window下连接Oracle 
            实例：    
                C:\Users\23228>sqlplus
                SQL*Plus: Release 11.2.0.1.0 Production on 星期三 12月 12 15:55:48 2018
                Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                请输入用户名:  system
                输入口令:
                连接到:
                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing options
                SQL> select * from dual;
                D
                -
                X
        

        windows下清屏host cls
        linux下清屏 host clear
        
        dual 表 伪表
        RowID  RowNum Level 伪列
        
        oracle中字符串的含义
            字符串可以是表中的一个字符，数字，日期
            单引号表示字符串
            双引号表示别名
            
        spool '路径'    开始录制
        spool off         结束录制
        
        查看表结构
            desc 表名;
        查看用户下的所有表
            select * from tab;
        oracle中的null值，永远！=null
            需要用到oracle函数中的nvl(字段名，0);
        
        关于oracle的取别名的区别
             select empno as "员工号",ename "姓名",sal 月薪,sal*12,comm,sal*12+nvl(comm,0) from emp;
             第一个和第二个没区别，第二个和第三个有区别，区别：没有引号可能会报错
        
        编辑上一条的sql语句，使用ed回车在文本中编辑
        
        去重
            distinct 
        
        连接符 ||
            concat()
        
        默认的时间格式
            DD-MON-RR
            
        修改系统的字典
            alter session|system set NLS_DATE_FORMAT='yyyy-mm-dd'
                session:修改当前会话
                system：系统管理人员修改系统全局
        查看系统的字典
            select * from v$nls_parameters;
            
        常用的select语句
            select * from emp where com between 100 and 200;
            select * from scott.emp where deptno in(10,20);
            select * from scott.emp where deptno is null;
            select * from scott.emmp where ename like '%j%'; (_表示一个占位符，%表示任意占位符)
            select * from scott.emmp where ename like '%\_%' escape  '\'; (定义转义字符让_具有其本身的含义)
        
        执行sql的时候使用where语句是从右边往左判断
        
            order by后面 + 列、表达式、别名、序号 DESC|ASC nulls last(如果有空值则放到最后)
            在oracle中null值最大
            
        修改页面大小
            set pagesize 20
            
        函数
            单行函数：1.字符函数：a.大小写控制函数，b.字符控制函数
                      2.数字函数：a.四舍五入：round() ,b.截断：trunc(),c.求余：mod()
                      3.日期：a.格式化:to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),    b.日期只有减
                                c.months_between()两个日期相差的月数,                d.add_months()在某个日期上加上月数
                                e.next_day()指定日期的下一个日期,                    f.last_day()本月的最后一天
                      4.转化：a.隐式，b.显示：to_char(date,'format_model')
                                              to_number()
                      5.通用函数：适用于任何数据类型，同时适用空值
                                a.nvl(extr1,extr2)                                                    b.nvl2(a,b,c) 当a=null的时候，返回c,否则返回b
                                c.nullif(a,b)判断是否相等,a==b返回空，否则返回a                        d.coalesce(a,b,c....n) 从左往右找到第一个不为空的值
                      6.条件表达式：a.case            b.decode
                                例如：    
                                        case 需要判断的值 when 判断条件 then 条件成立执行的结果    
                                                          when 判断条件 then 条件成立执行的结果    
                                                          when 判断条件 then 条件成立执行的结果    
                                                          ...
                                                          else 其他结果
                                        end
                                        
                                        case when 判断的值<100 then 条件成立执行的结果
                                             when 判断的值>100 then 条件成立执行的结果
                                             else 其他结果
                                        end
                                        （sql语句通用）
                                        
                                例如：decode(需要判断的值，判断条件1，条件成立执行的结果1，判断条件2，条件成立执行的结果2，其他结果    )
                                
            多行函数：    自动过滤空值，在oracle中，多行函数是不会统计null值的。
                    1.count()
                        范例1： 
                        统计emp表中员工总人数 
                        select count(*) from emp; 
                        *号适用于表字段较少的情况下，如果字段较多，扫描多间多，效率低，
                        项目中提倡使用某一个非null唯一的字段，通常是主键
                        
                        范例2： 
                        统计公司有多少个不重复的部门 
                        select count(distinct deptno) from emp;
                    2.max和min 
                    3.sum和avg
                        范例1： 
                        按部门求出该部门平均工资，且平均工资取整数，采用截断 
                        select deptno “部门编号”,trunc(avg(sal),0) “部门平均工资” 
                        from emp 
                        group by deptno;

                        (继续)查询部门平均工资大于2000元的部门 
                        select deptno “部门编号”,trunc(avg(sal),0) “部门平均工资” 
                        from emp 
                        group by deptno 
                        having trunc(avg(sal),0) > 2000;
                    4.单引号和双引号出现的地方
                        单引号出现的地方如下： 
                        1）字符串，例如：’hello’ 
                        2）日期型，例如：’17-12月-80’ 
                        3）to_char/to_date(日期,’YYYY-MM-DD HH24:MI:SS’)

                        双引号出现的地方如下： 
                        1）列别名，例如：select ename “姓 名” from emp 
                        2）to_char/to_date(日期,’YYYY”年”MM”月”DD”日” HH24:MI:SS’)‘’号中的英文字符大小写不敏感
            
            分组数据：在select语句中所有未包含在组函数中的列都必须包含在group by子句中
                        where和having的区别：where后面不能使用多行函数
                        SQL优化 原则：3. 尽量使用where
                    
                      group by 的增强
                        group by rollup(a,b) ：这条语句相当于分组三次
                                                第一次，a，b两个约束条件，即a相同，b也相同的分为同一组
                                                第二次，只是用a一个条件进行分组，把上一次形成的结果在进行分组，把a相同的分为一组
                                                第三次，没有约束条件，将整个表数据分为一组
                            例如：
                            SQL> select deptno,job,sum(sal) from emp group by deptno,job order by deptno;
                            单独使用分组查询
                             
                            DEPTNO JOB         SUM(SAL)
                            ------ --------- ----------
                                10 CLERK           1300
                                10 MANAGER         2450
                                10 PRESIDENT       5000
                                20 ANALYST         6000
                                20 CLERK           1900
                                20 MANAGER         2975
                                30 CLERK            950
                                30 MANAGER         2850
                                30 SALESMAN        5600
                             
                            已选择9行。
                             
                            SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job) order by deptno;
                             
                            DEPTNO JOB         SUM(SAL)
                            ------ --------- ----------
                                10 CLERK           1300 第一次分组的sum总和计算
                                10 MANAGER         2450
                                10 PRESIDENT       5000
                                10                 8750 第二次分组添加的计算
                                20 ANALYST         6000
                                20 CLERK           1900
                                20 MANAGER         2975
                                20                10875
                                30 CLERK            950
                                30 MANAGER         2850
                                30 SALESMAN        5600
                                30                 9400
                                                  29025 第三次分组添加的计算
                            所以rollup函数进行多次分组计算
                             
                            已选择13行。
                            
                            
                        break on 列名 skip 行数        ：将该列下相同的只显示一次并不同的相隔行数  
                        break on null                 ：撤销
    表连接：1.等值连接，不等值连接，外连接，自连接
        外连接：
            (+):代表连接方向
            主表的数据可以完全查出来
            左连接：左边的表是主表，右连接：右边的表是主表
            如果(+)在右边，是左连接（左边的表是主表）；select  e.ename,e.deptno,d.dname from emp e ,dept d where e.deptno(+)=d.deptno
            如果(+)在左边，是右连接（右边的表是主表）例如：select  e.ename,e.deptno,d.dname from emp e ,dept d where e.deptno=d.deptno(+)
        自连接：
            通过表的别名，将同一张表视为多张表，不适合操作大表。
        层次查询：
            单表大数据量的时候使用这样的方法
            select [level], column, expr... from table
        　　[where condition]
        　　start with condition
        　　connect by [prior column1= column2 |
        　　column1 = prior column2];
            
            例如：
            select level,empno,ename,mgr
            from emp
            connect by prior empno=mgr
            start with mgr is null
            order by 1;
            ：level 伪列
        
    子查询
        1.可以在主查询的where select having from后面使用子查询
        2.不可以在group by后面使用子查询
        3.主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用即可
        4.一般不在子查询排序；但top-n分析问题中，必须对子查询排序
        5. 一般先执行子查询，再执行主查询；但相关子查询例外
        6. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
        7.子查询中的null
        
        单行子查询，查询结果返回单条记录 
                例如  select empno,ename,(select job from emp where empno =7839) from emp
                而不能 select empno,ename,(select job from emp) from emp
                单行子查询只能使用单行操作符，例如>,<,>=,<=,<>,=
            查询结果返回多行记录(having与where的区别在于having能接收多行函数而where不行)
                例如：select deptno,min(sal) from emp
                group by deptno having min(sal)>(select distinct min(sal) from emp where deptno=10)
                而不能：select deptno,min(sal) from emp
                        group by deptno where min(sal)>(select distinct min(sal) from emp where deptno=10)
        
        多行子查询，多行子查询的操作符：in,any,all
                如果使用not in 则在子查询语句中不能有null
                any：大于任意值相当于大于最小值，小于任意值相当于小于最大值
                all：大于任意值相当于大于最大值,小于任意值相当于小于最小值
                select  * from emp where sal >  (select min(sal) from emp where deptno=30)
                ==select  * from emp where sal > any (select sal from emp where deptno=30)
        
                关于子查询结果中存在空值，则不能使用not in ，否则会导致整个查询结果为空值
                如果想要使用not in，首先确保子查询的结果没有空值，即在子查询中添加where xx is  not null
                例如  查询不是老板的员工信息:
                            select * from emp where empno not in(select mgr from emp where mgr is not null)
        
        相关子查询，相关子查询：将主查询中的值 作为参数传递给子查询
                 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);
                
                sql优化：多表查询优于子查询
                
    集合查询：
            并集：union/union all    相等的存在一次/相等的存在两次
            交集：intersect    同属于A与B
            差集：minus 只属于A不属于B
            
            sql优化：尽量使用 union all
            union =union all + distinct    
        
            参与运算的各个集合必须列数相同 且类型一致
            采用第一个集合作为最后的表头
            order 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;
            相当于：select deptno,job,sum(sal) from emp group by rollup(deptno,job) order by deptno;
    
            sql优化：尽量不要使用集合运算
            
    sql执行时间的开关：set timing on 和set timing off
    
    
    sql的类型：
                1. DML（data manipulation language 数据操作语言）:insert update delete select
                2. DDL(data definition language 数据定义语言): create table,alter table,drop table,truncate table, create/drop view,sequence,index,synonym(同义词)
                3. DCL(data control language 数据控制语言）：grant(授权) revoke（撤销权限）
    
    地址符：& 使用地址符可用来二次输入
                例如：insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);
            
            创建相同数据结构的表：create table emp10 as select * from emp where 1=2;
            一次性将查询的结果插入：insert into emp10 select * from emp where deptno=10;
            如果需要插入大量的数据则使用1. 数据泵（PLSQL程序：dbms_datapump） 2. SQL*Loader工具3. 外部表
            
            
            delete和truncate的区别：
            1. delete逐条删除；truncate先摧毁表 再重建
            2. (*)delete是DML（可以回滚） truncate是DDL（不可以回滚）
            3. delete不会释放空间 truncate会
            4. delete可以闪回（flashback）  truncate不可以(一个事物提交了之后，可以被撤销)
            5. delete会产生碎片 truncate不会

            碎片：数据库插入是从后往上插入，在第一个位置上是高水位线，这个位置专门用来插入输入的
                而删除数据的时候，某个数据删除，数据插入不会填充插入到删除数据的位置上，将还是在
                高水位线上插入，所以产生了碎片，碎片影响数据查询的速度。
                去掉碎片：1.alter table 表名 move;
                          2.数据的重新导出和导入;导出：exp,导入 imp ，增强导出 expdp 增强导入：impdp
            
            取消回显：set feedback off
            导入数据： @d:\temp\testdelete.sql
            
    事务：1. 起始标志:事务中第一条DML语句
          2. 结束标志：提交 显式 commit，            隐式 正常退出exit，DDL，DCL
                       回滚 显式 rollback，            隐式 非正常退出，掉电，宕机
                       
            控制事务：使用保存点：savepoint a 开始一个事务
                                 rollback to savepoint a 回滚
            
            事务的隔离级别：oracle 支持三种：READ COMMITED(读已提交数据),SERIALIZABLE(串行化)，默认为：READ COMMITED
                            mysql 支持四种：默认：REPEATABLE READ（可重复读）
                            
                            oracle 还支持 readonly(只读的隔离级别) ，所以是三种
                                例如：SET TRANSACTION read only;设置只读 
    
    额外知识：关于rownum:1. rownum永远按照默认的顺序生成
                         2. rownum只能使用< <=;不能使用> >=
            
              临时表：  手动生成： create global temporary table *****
                        自动：排序
                        特点：当事务或者会话结束的时候，表中自动删除
                        
                        使用rownum可以实现分页，把rownum当作基表的列，作为子查询的表，然后可以排序
                        
                        创建一个基于事务的临时表
                        create global temporary table temptest1
                        (tid number,tname varchar2(20))
                        on commit delete  rows;
                        
                        在oracle中事务提交之后，表中的数据不一定会保存下来，原因是基于事务的临时表
                        
                        创建一个基于会话的临时表
                        create global temporary table temptest1
                        (tid number,tname varchar2(20))
                        on commit preserve rows;
                        
              行转列：wm_concat(varchar2) 组函数
                        例如：select deptno,wm_concat(ename) nameslist from emp group by deptno;
        
    Oracle的回收站    
                查看回收站：show recyclebin;
                清空回收站：purge recyclebin;
                查看回收站的表内容：
                例如：select * from "BIN$4tn/JLnaTb+dmYhRTzDM0w==$0";
                
        注意：管理员没有回收站
                
    闪回删除： oracle 10g:6种不同闪回  oracle11g：7种
            flashback table 表名 to before drop;
            flashback table "回收站表名" to before drop;(有双引号)
            flashback table 表名 to before drop rename to TESTSAVEPOINT_OLD;
            
            增加列
            alter table test1 add photo blob;
            修改列
            alter table test1 modify tname varchar2(40);
            删除列
            alter table test1 drop column photo;
            重命名列
            alter table test1 rename column tname to username;
            重命名表
            rename test1 to test2;
            删除表
            drop table test2;
            
    约束：约束是表一级的限制
          如果存在依赖关系，约束可以防止错误的删除数据
          约束的类型：
                not null    非空
                unique        唯一约束
                primary key  主键
                foreign key  外键
                check        检查
                
            例如：gender varchar2(2) check (gender in ('男','女'))
        外键约束：
                foreign key ：在字表中，定义了一个标记的约束
                references:指定表和父表的列
                on delete cascade:当删除父表时，级联删除字表记录
                on delete set null:将子表的相关依赖记录的外键位置设置为null
        关键字：constraint 给约束起别名 
        
        例如：
         create table student
        (
           sid number constraint student_pk primary key,
           sname varchar2(20) constraint student_name_notnull not null,
           gender varchar2(2) constraint student_gender check (gender in ('男','女')),
           email varchar2(40) constraint student_email_unique unique
                              constraint student_email_notnull not null,
           deptno number constraint student_fk references dept(deptno) on delete set null        -- 设置外键
        );
        
    其他数据库对象
        1.表
        2.视图
            视图就是封装了一条复杂查询的语句，视图是一种虚表，视图建立在已有表的基础上，
            视图赖以建立这些表成为基表。
            可以通过修改视图来改变基表的数据。但是不建议。
            
            
            语法1：create view 视图名称 as 子查询
            语法2：create or replace view  as 子查询 视图约束
            
        视图约束：with check option; --通过视图只能操作看得到的数据
                  with read only;    --只读，可以屏蔽对视图的修改操作。
                  
         删除视图 drop view 视图名
             
        3.序列 sequence
            相当于mysql的自增，一个数组
            语法：create sequence 序列名
            
            查看当前序列：        序列名.currval         伪列
            查看下一个序列值：    序列名.nextval        伪列
            
            使用序列可能会有裂缝
                回滚，系统异常，多表同时使用一个序列
            将序列值装入内存可提高访问效率
            
            修改序列 alter sequence 
        
        4.索引  index
            增加查询的效率以及速度
            查看执行计划 ：explain plan for 子查询
            
            创建索引: create index 索引名 on 表名(字段);
            
        5.同义词 synonym 
            给一个用户下的表创建一个表的别名，让其他用户可以操作
            首先查看当前用户：show user
            创建同义词：create [public] synonym 别名 for  对象;
            删除同义词：drop synonym 别名;