Oracle 一

 一、Oracle Database 基本概念

        1、Oracle 服务器是一个数据管理系统（RDBMS），它提供开放的、全面的近乎完美的信息管理。Oracle 是由一个 Oracle 实例和一个 Oracle 数据库组成。

             Oracle 服务器由两大部分组成，Oracle 数据库和 Oracle 实例。

            （1）Oracle 数据库是位于硬盘上实际存放数据的文件，这些文件组织在一起成为一个逻辑整体，即为 Oracle 数据库。因此在 Oracle 看来，数据库是指硬盘上文件的逻辑集合，必须要在内存里的实例合作才能对外提供数据管理服务。

            （2）Oracle 实例是位于物理内存类的数据结构，它有一个共享的内存池和多个后台进程所组成，共享的内存池可以被所有进程访问，用户如果要存取数据库里的数据（也就是硬盘上的文件）就必须通过实例才能实现，不能直接读取硬盘上的文件文件。实例可以操作数据库，在任何时刻一个实例只能与一个数据库关联，但是一个数据库有可能被多个实例访问。

              一个数据库文件读到内存中，就是一个实例 Instance，一个 Instance 会把所占用的内存分为多个块，我们所操作的数据库就是操作内存中的 Instance ，不能直接操作数据库。当我们通过 JDBC 获得 Connection 连接到这个 Instance 时,Instance 会为这个 Connection 开辟一个内存（PGA），有一个连接进来就会有一个 PGA。一个Instance 中可以有多个 PGA，但是只有一个全局 SGA 区段。Instance 和数据库可以是多对一的关系。也就是一个数据库可以对应多个 Instance ，这就是集群（RAC），如果一台机器崩溃，也就是一个 Instance 瘫痪，我们可以用另外一个主机中的 Instance 继续提供数据库连接。 

              我们操作数据库只是在 PGA 中进行增删改查，当我们将操作 commit 时，是将操作提交给了 SGA 中，SGA 还是在内存中，至于 SGA 中的数据什么时候提交到数据库是由服务器决定。这种方式叫两阶段提交。只要数据还在 SGA 中，我们就还有机会修改数据，这就是闪回的原理。

         2、表空间，表空间为逻辑概念，数据文件为物理概念。表空间由多个数据文件组成，数据文件只能属于一个表空间。表空间对应着实例中的 PGA。我们常用的表空间为 users 表空间。

 

          3、Oracle 相关服务：OracleDBConsoleorcl： 管理 Oracle 的 Web 界面服务器。OracleJobSchedulerORCL：Oracle 的定时服务。OracleOraDb10g_home1liSQL*Plus：是一个界面画操作的 Web 服务器。OracleOraDb10g_home1TNSListener：Oracle 客户机监听服务。客户机要是连接 Oracle 就必须启动该服务。比如通过 JDBC 连接 Oracle（jdbc:oralce:this:@localhost:1521:orcl）性能比较低，通过 Oracle 客户机连接 Oracle（jdbc:oracle:oci:@loaclhost:1521:orcl）性能要高很多。

 

二、基本查询

       1、技巧：

                  （1）spool d:\1.txt （记录命令）

                           spool off

                           如果想在文件中追加文本，则可以使用 append 命令：

                           spool d:\1.txt append

                  （2）host cls（windows清屏命令）host clear（linux 清屏命令）

                  （3）col 列名 for 9999 （调整列宽为4位数字，仅限数字列）

                  （4）col 列名 for a8（调整列宽为8位字符）

                  （5）set linesize 120 （调整行宽）

                  （6）set pagesize 30（调整页面大小，记录数）

                  （7）如果在输入sql命令出错时，可以有两种方式修改：c命令：首先输出要修改sql的行号，然后在键入 c /要修改的字符/修改后的字符。比如要修改 select * ffom emp; 1 回车 c /ffom/from 回车。另一种方式：ed命令，打开默认编辑器

                  （8）如果想在sql语句中追加语句，则可以使用 a 命令：

                          select * from emp order by sal;

                          a  desc 注意要 a 和 desc 之间要两个以上空格

                   （9）显示当先用户：show user;

                           以管理员登录：sqlplus / as sysdba;

                           解锁用户：alter user scott account unlock;

                           更改用户密码：alter user scott identified by tiger;

                           快速切换：conn scott/tiger;

       2、select * from emp; 查询所有信息（在查询所有记录时，使用指定列名要比 * 号性能好，因为 * 要解析出代表的哪一个列。但是这种性能差别仅限于 Oracle 9i 之前的版本，在 Oracle 9i 之后做了优化，可以认为性能是一样的。）

       3、查询中加入算术表达式：

            select empno,ename,sal,sal*12 from emp

            select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0) from emp（在 Oracle 中关于 null，如果一个表达式中含有空值，则整个表达式为空，并且在 Oracle 中 null 不等于 null。对于第一种情况要使用虑空函数 nvl。对于第二种情况要使用 is null 或 is not null 来进行处理）

       4、在查询中使用别名：

             select empno as "员工编号",ename "姓名",sal 月薪 from emp;

             总共有三种方式可以实现别名，其中 as 可以省略，"" 也可以省略，但是加了 "" 和不加 "" 的区别就是加了 "" 的别名中可以有空格和特殊字符，没有加 "" 号不能有特殊字符和空格。

       5、distinct 去重：如果后面跟了多列，则作用域是后面的所有列

       6、连接符：select 'Hello' || ' Wolrd' from dual; 等同于

                          select concat('Hello',' World') from dual;

                          select ename||'的薪水是'||sal from emp;

 

三、过滤和排序

       1、select * from emp where deptno=10;

            select * from emp where ename='KING'; 对于字符串大小写是敏感的 KING 与 King 是不同的。 

            对于日期是格式敏感的：查询当前系统日期格式

            select * from v$nls_parameters;

            修改日期格式：

            alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

            将字符串转为日期：

            select * from emp where hiredate=to_date('1981-11-17','yyyy-mm-dd');

       2、between...and（含边界）、in(set)、like、is null。

            like 模糊查询要注意转义问题，like '%\_%' escape '\' ，也可以自定义转义符：like '%a_%' escape 'a'

       3、order by后可以跟列名、别名、表达式、序号。

            order by sal desc 如果是根据多列排序，只有一个 desc 则规则是先按第一列降序，如果第一列相同，则按第二列降序，如果不相同则还是升序排列，如果想都降序，则要在每一列后面加 desc

            order by sal desc 如果有空值的话，空值会在前面，这样不符合习惯，如果想让空值排在最后，只需在最后加 nulls last

            select * from emp order by comm desc nulls last;

 

四、单行函数

       函数分为单行函数和多行函数，单行函数是指一个输入产生一个输出，多行函数是指多个输入产生一个输出。

       单行函数：操作数据对象，接收参数返回一个结果，只对一行进行转换，每行返回一个结果，可以转换数据类型，可以嵌套，参数可以是一列或一个值。

         字符函数：操作对象为字符串（大小写转换，字符串长度等）

         数值函数：对数值进行操作（绝对值、四舍五入、求余等）

         日期函数：操作对象为日期（对日期进行运算，日期间的差等）

         转换函数：类型间的转换（字符串与日期间的转换等）

         通用函数：可以接受参数类型是通用的（比如虑空函数等）

 

        （1）字符函数

                大小写控制函数：lower、upper、initcap

                    select lower('Hello World'),upper('Hello World') from dual;

                    select initcap('hello world') from dual;（单词首字母大写）

                字符控制函数：concat、substr、length/lengthb、instr、lpad/rpad、trim、replace

                    select concat('Hello',' World') from dual;（连接字符串）

                    select substr('hello world',3) from dual;（从第三位开始截取）

                    select substr('hello world',3,2) from dual;（从第三位开始截取两位）

                    select length('China'),lengthb('China'),length('中国'),lengthb('中国') from dual;（length为字符个数，lengthb为字节数）

                    select instr('China','z') from dual;（查找子串在母串中的位置，没有则返回0）

                    select lpad('dk',5,'*') ,rpad('dk',5,'*') from dual;（左填充与右填充，指定填充到第几位，以什么填充）

                    select trim('h' from 'hhelo worldh') from dual;（去掉前后的 h 字符，注意结果是 elo world，前面两个 h 都没有了）

                    select replace('hello world','l','*') from dual;（替换函数，替换所有匹配字符）

          （2）数值函数

                  select round(45.926,2) 四舍五入,trunc(45.926,2) 截断,mod(10,3) 求余 from dual;

                 （四舍五入是取小数后指定位数进行四舍五入，如果第二个参数是0，则是取整数部分，然后按小数第一位四舍五入，如果第二个参数是-1，则代表取整数从右往左取第二位，保留十位。截断是直接阶段多少位）

           （3）日期函数

                    months_between、add_months、next_day、last_day、round、trunc

                    查询当前时间格式：select sysdate from dual;

                   将时间转为指定字符串：select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

                   日期运算：select sysdate-1 昨天,sysdate 今天,sysdate+1 明天 from dual;

                   select sysdate-to_date('1988-02-16', 'yyyy-mm-dd') from dual;（两个日期相减为相差天数，日期间不能相加）

                   select months_between(sysdate,to_date('1988-02-16','yyyy-mm-dd')) from dual;（两个日期之间的月差）

                    select add_months(sysdate,123) "123个月后" from dual;（增加月后的日期）

                    select last_day(sysdate) from dual;（某月的最后一天）

                    select next_day(sysdate,'星期日') from dual;（从某个时间算起，下次出现星期几是多少号）

                    select round(sysdate,'month') from dual;（按月四舍五入）

                    select round(sysdate,'year') from dual;（按年四舍五入）

                    select trunc(sysdate,'month') from dual;（按月截断，返回当月第一天）

                    select trunc(sysdate,'year') from dual;（按年截断，返回当年1月1号）

            （4）转换函数

                    转换函数分为隐式转换和显示转换，隐式转换条件为：被转换对象是可以被转换的，隐式转换效率较低。

                   

 

* 两位的年还可以用“RR” 表示，但是容易有百年虫问题，但是 Oracle 采取了一定的措施，保留这种形式是为了兼容以前的数据库

                       select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;（to_char函数内可以进行字符串的拼接，必须用双引号引起来）

                       select to_char(sal,'L9,999.99') from emp;（结果为：￥1,600.00 其中9表示数字，0表示0，$表示美元符，L表示本地货币符，.表示小数点,，号表示千位符）

           （5）通用函数

                   通用函数之所以叫通用函数是因为它可以接收任何数据类型，同时也适用于空值。

                   nvl(expr1,expr2) 虑空函数，当第一个表达式为空时返回第二个表达式

                   nvl2(expr1,expr2,expr3) 虑空函数，当第一个表达式为空时返回第二三个表达式，不为空时返回第二个表达式的值

                   nullif(expr1,expr2) 如果两个表达式相等则返回空，否则返回第一个表达式的值

                   coalesce(expr1,expr2,.....exprn) 寻找这些表达式中第一个不为空的值，如果都为空，则返回空

             （6）条件表达式

                     有两种方式：case 表达式，是sql99语法，类似于 basic 比较繁琐

                                        decode 函数，Oracle 自己的语法，类似 Java，比较简介

                    select sal 涨前工资, case job
                                                          when 'president' then sal+1000
                                                          when 'manager' then sal+600
                                                          else sal+400
                                                   end 涨后工资
                     from emp;

                     select sal 涨前工资,decode               

                                                 (job,'president',sal+1000,'manager',sal+600,sal+400) from emp;

 

五、多行函数（分组函数）

       以多行为单位的操作，例如（最大值，最小值，求和，求平均数）

      （1）avg、count、max、min、sum（组函数会自动虑空）

      （2）分组数据 group by，可以用 group by 子句将表中的数据分成若干个组，在 select 列表中所有未包含在聚合函数中的列都应该包含在 group by 子句中。

               当 group by 作用于多列时，先按第一列分组，如果相同再按第二列分组，要对分组后的结果再过滤，只能用 having 关键字

       （3）group by 语句的增强

              当我们要想获取这样的结果，就必须对多个列进行多个分组，这样的结果是多个集合的和：

                          select deptno,job,sum(sal) from emp group by deptno,job + selcet deptno,job,sum(sal) from emp group by deptno + selcet deptno,job,sum(sal) from emp

                          因为首先我们要对工种进行分组，算出每个工种的总工资，然后再对部门进行分组，算出这个部门的总工资，最后还要算出公司的总工资。

              Oracle 提供了 rollup 函数对 group by 进行了增强，方便我们管理多个分组

                          select deptno,jpb,sum(sal) from emp group by rollup(deptno,job)

                          得到的结果为：

                           然后要对数据进行美化，比如部门相同的部门号只显示一次，并且部门与部门之间隔两行：

                           break on deptno skip 2; （取消：break on null）

 

六、多表查询

     （1）笛卡尔积：在涉及到多表查询时，如果没有限制连接条件，那么我们查出来的数据就是一个笛卡尔全集，就是每张表的行数的乘积

                               为了避免查出笛卡尔全集我们就必须增加连接条件：dept.deptno=emp.deptno

       （2）多表连接的分类：

               等值连接（两个表之间的关系是=号连接）

               不等值连接（两个表之间的关系是用>、<、<>等不等关系连接）

               外连接（左外连，右外连）当条件不成立时，某些记录仍然希望显示

               自连接（自己连接自己）

       （3）等值连接：（连接条件的个数至少为 n-1个，n为表的个数）

               select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno

       （4）不等值连接：

               select e.ename,e.sal,s.grade from emp e,salgrade s where s.sal between s.losal and s.hisal;

        （5）外连接：（条件不成立时，仍希望显示，比如要查处部门号、部门名、部门人数，那么如果部门人数为0时，我们还希望显示部门号和部门名称，所以我们应该以部门表为参照进行连接，如果部门表在左边，应该是左外连节，反之右外连接）

                 select d.deptno,d.dname,count(e.empno) from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname

        （6）自连接：（通过表的别名，将同一张表视为多张表。自连接通常只适合操作小表，因为如果这一张表含有n个记录，那么在查询中会产生 n*n 个记录，笛卡尔积是相当大的，很消耗资源，严重影响性能）

                select e.ename||'的老板是'||b.ename from emp e,emp b where e.mgr=b.empno

                Oracle 提供了层次查询来解决自连接问题。

                 select level empno,ename,mgr

                           from emp

                           connect by prior empno=mgr

                           start with empno=7839       

                  查询结果：

                      原理分析：层次查询不是把一个表当做多个表，而是同一个表的前后两次查询做连接，也就是前一次操作的员工号码，等于本一次操作的老板号码（connect by prior empno=mgr），但是要指定一个起始条件 start with empno=7839 ，并且提供了一个 level 伪列。