Oracle总结（一）

1、安装：将数据库安在虚拟机上，在本机上访问，以模拟远程访问服务器。

  从客户端远程连接虚拟器上的数据库： sqlplus scott/admin@192.168.56.101:1521/orcl

  其中192.168.56.101是虚拟机的默认地址，1521是Oracle的端口号，orcl是Oracle自带的数据库。

  如果scott账户锁定，则需要的操作：首先进入用管理员权限登录，解锁scott账户，设置scott密码。

<span style="font-family:Courier New;font-size:14px;">   sqlplus / as sysdba 
   alter user scott account unclock;
   alter user scott identified by tiger;
</span>

如果在cmd界面上输入sqlplus scott/admin无效，那么需要安装instantclient（客户机），里面有sqlplus执行命令。

2、JDBC连接oracle（两种方式）：

    **提供驱动，URL为jdbc:oracle:thin:@localhost:1521:oracle    图形化工具SQL Developer

    **jdbc:oracle:oci:@localhost:1521:oracle  PL/SQL Developer就是用的这种连接方式（必须提供客户机）

3、RDBMS：关系型数据库管理系统，就是采用了关系模型来组织数据的数据库，何为关系模型，其实指的就是二维表格模型，而一

  个RDBMS就是由二维表及其之间的联系组织成的一个数据组织。

  对数据库和实例的理解：

  数据库可以理解为物理概念，指的是硬盘上的文件，如orcl数据库我们可以在硬盘中找到这个文件夹，而操作这个文件夹下的   文件需要将这些文件读入到内存，把内存中的镜像就称之为实例（逻辑概念），其实实例就是数据库在内存中的镜像。

  数据库集群的特点：

     ** Load Balance  负载均衡

     ** Fail Over     失败迁移
       RAC：Real Application Cluster（RAC特指Oracle的集群）

4、Oracle体系结构图：

    

5、Oracle常用命令

   清屏：host cls； 当前用户：show user;  录屏：spool d:/a.txt 显示表结构：desc 表名;

   显示行宽：show linesize; 设置行宽：set linesize 150; 

   设置列宽：col ename for a8(这一列是字符串格式的，ename列名，for即format，a代表字符串，8代表8个字符)

           col sal for 9999(这一列是数字)

           c(change)命令：SQL>  c /form/from将form改成from;  ed(edit)命令：作用同c命令

           a(depend)命令，在语句后追加内容

6、其他基本操作：

   **连接符|| ，作用同concat(str1,str2)函数，连接两个字符串

   **like 模糊查询  select *  from emp where ename like '%\_%' escape '\' 

                                      （escape关键字的作用，将特殊字符转义为本来的字符意义）

   **order by 排序  默认升序，降序desc  

     order by 后可以 + 列名、表达式、别名、序号

<span style="font-family:Courier New;font-size:14px;">   select empno,ename,sal,sal*12 from emp order by sal*12 desc;
   select empno,ename,sal,sal*12 年薪 from emp order by 年薪 desc;
   select empno,ename,sal,sal*12 from emp order by 4 desc;
</span>

   order by 作用于后面所有的列，desc只作用于离它最近的列。

<span style="font-family:Courier New;font-size:14px;">   select * from emp order by deptno ，sal desc---deptno升序，只有sal降序</span>

7、单行函数

  select lower('Hello World') 转小写,upper('Hello World') 转大写, initcap('hello world') 首字母   大写from dual;

  select substr('Hello World',3) 子串 from dual;  --- substr(a,b) 

  从a中，第b位开始取  注意：索引从1开始算

  select length('Hello World') 字符,lengthb('Hello World') 字节 from dual;

  select instr('Hello World','ll') 位置 from dual;---instr(a,b) 在a中，查找b

  select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;---lpad 左填充 rpad右填充

  select trim('H' from 'Hello WorldH') from dual;---trim 去掉前后指定的字符（这一点同java不同）

  select replace('Hello World','l','*') from dual;---replace 替换

  select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,round(45.926,-1)       四,round(45.926,-2) 五 fromdual; ---round 四舍五入  结果分别为：45.93、45.9、46、50、0

  select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,trunc(45.926,-1)     

  四,trunc(45.926,-2) 五 from dual;---trunc 舍弃 结果分别为：45.92、45.9、45、40、0     

  select sysdate from dual;---系统时间  显示格式为：11-11月-14

  select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--改变格式 2014-11-1114:27:05 

  可以+N或-N或者—日期,但是日期+日期是错误的。

  select ename,hiredate,(sysdate-hiredate)/30  一, months_between(sysdate,hiredate) 二 from     emp;--查询员工入职的月数，months_between函数比前者更准确。

  select add_months(sysdate,78) "78个月后" from dual; --N个月之后

  select last_day(sysdate) from dual; --当前月的最后一天

  select next_day(sysdate,'星期一') from dual; --下一个日期  

  应用：如每个星期一备份数据：方式有分布式数据库、快照

  select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是" day') from dual;

  select to_char(sal,'L9,999.99') 薪水 from emp; --查询员工薪水： 两位小数 千位符 本地货币代码

  关于转换：把character转成number--to_number；把character转成date--to_date；把date或者number转成   character--to_char

8、通用函数

  nvl(a,b) 当a=null时候，返回b

  nvl2(a,b,c) 当a=null时候，返回c；否则返回b   select sal*12+nvl2(comm,comm,0) 年收入

  nullif(a,b)  当a=b时候，返回null；否则返回a

  coalesce 从左到右找到第一个不为null的值 

  select comm,sal,coalesce(comm,sal) "第一个不为null的值" from emp;

9、条件表达式：（重要）--Oracle中的DECODE函数

  使用两种方式：

  ** CASE表达式：SQL99的语法，较繁琐。 ** DECODE函数：Oracle自带的语法。 

  以涨工资为例，总裁涨1000 经理涨800 其他涨400

  1、CASE表达式

<span style="font-family:Courier New;font-size:14px;">   select ename,job,sal 涨前,
          case job when 'PRESIDENT' then sal+1000
                   when 'MANAGER' then sal+800
                   else sal+400
          end 涨后
   from emp;</span>

  2、DECODE函数

<span style="font-family:Courier New;font-size:14px;">  select ename,job,sal 涨前,
         decode(job, 'PRESIDENT',sal+1000,
                      'MANAGER',sal+800,
                                sal+400) 涨后
  from emp;</span>

10、多行函数（又称为分组函数，组函数）---定义：作用于一组数据，并对一组数据返回一个值。

  sum()、count()、avg()、max()、min()  

  分组数据：group by

  △注意：select a,b,c,sum(x) from table group by a,b,c;  所有包含于select列表中，而未包含于组函数  中的列必须包含于group by的子句中。但可以这样写select a,b,c,sum(x) from table group by a,b,c,d,e; 

  使用having过滤分组。（使用了组函数用having才有意义）

  【where和having的区别：where后面不能使用组函数】

  select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;(√)

  select deptno,avg(sal) from emp where avg(sal)>2000;(√) group by deptno;(×)--where后使用了组   函数

  group by的增强

按照部门统计各部门不同工种的工资情况，要求按如下格式输出：

从左图中可以看出，数据是以下三条语句的组合。 select deptno,job,sum(sal) from emp group by deptno,job;  + select deptno,sum(sal) from emp group by deptno;  + select sum(sal) from emp; 以上三句用group by增强来表示  = select deptno,job,sum(sal) from emp group by rollup(deptno,job); 抽象： group by rollup(a,b)  = group by a,b  + group by a  + group by null break on deptno skip 2；不同部门之间隔两行

11、多表查询

  等值连接：select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno;

  不等值连接:select e.empno,e.empme,e.sal,s.grade from emp e,salgrade s where e.sal between     s.losal and s.hisal;
  外连接：有主从之分，其中主表所有记录都会显示，无论是否满足关联关系，从表只显示满足条件的记录，主表不满足条件的，   以null补位。

  Oracle中的左外连接和右外连接跟MySQL稍有不同，以 where e.deptno=d.deptno 为例。

  写法： 左外：where e.deptno=d.deptno(+);右外：where e.deptno(+)=d.deptno;

select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from emp e,dept d  where e.deptno(+)=d.deptno group by d.deptno,d.dname;

部门号      部门名称       人数              ---------- -------------- ----------                 10          ACCOUNTING     3                               40          OPERATIONS     0                       20          RESEARCH       5                  30          SALES          6

 自连接：通过表的别名，把同一张表视为多张表。

 自连接不适合操作数据量过大的表。于是有了层次查询

查询员工信息：员工姓名 老板姓名 select e.ename 员工姓名，m.ename 老板姓名 where e.mgr = m.empno;

 层次查询： 

<span style="font-family:Courier New;font-size:14px;">  select level,empno,ename,mgr
  from emp
  connect by prior empno=mgr//上一层的员工是下一层的老板
  start with mgr is null  //从mgr为null的开始
  order by 1;</span>

 
 层次结构图：