zhu的Oracle数据库笔记

吐血总结了一堆的东西，奋斗了2天，写到后面有点不耐烦了，但还是含泪且默默的写完了

今天勇士又哗啦啦的赢的，在水花兄弟溅不起水花的时候，KD开启了单打模式，拿了31分13个板，勇士3--爵士0，爵士到了生死关头，敬请期待下一次比赛。

数据库概要

什么是数据库？

数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。

数据库的种类有哪些？

根据存储模型划分，数据库类型主要可分为:

--网状数据库(Network Database)、

--关系数据库(Relational Database)、

--树状数据库(Hierarchical Database)、

--面向对象数据库(Object-oriented Database)、

--等

常用数据库有哪些？

商业应用中主要是关系数据库，比如：

--Oracle、

--DB2、

--MS SQL Server、

--MySQL

--等，

为什么要用数据库，有哪些优点？

--1、实现数据共享

数据共享包含所有用户可同时存取数据库中的数据，也包括用户可以用各种方式通过接口使用数据库，并提供数据共享。

--2、减少数据的冗余度

同文件系统相比，由于数据库实现了数据共享，从而避免了用户各自建立应用文件。减少了大量重复数据，减少了数据冗余，维护了数据的一致性。

--3、数据的独立性

数据的独立性包括逻辑独立性（数据库中数据库的逻辑结构和应用程序相互独立）和物理独立性（数据物理结构的变化不影响数据的逻辑结构）。

--4、数据实现集中控制

文件管理方式中，数据处于一种分散的状态，不同的用户或同一用户在不同处理中其文件之间毫无关系。利用数据库可对数据进行集中控制和管理，并通过数据模型表示各种数据的组织以及数据间的联系。

--5、数据一致性和可维护性，以确保数据的安全性和可靠性

主要包括：

①安全性控制：以防止数据丢失、错误更新和越权使用；

②完整性控制：保证数据的正确性、有效性和相容性；

③并发控制：使在同一时间周期内，允许对数据实现多路存取，又能防止用户之间的不正常交互作用。

--6、 故障恢复

由数据库管理系统提供一套方法，可及时发现故障和修复故障，从而防止数据被破坏。数据库系统能尽快恢复数据库系统运行时出现的故障，可能是物理上或是逻辑上的错误。比如对系统的误操作造成的数据错误等

数据库的使用（oracle数据库 ）

一、sql语句分类：

--DML（data manipulation language）：

命令包括SELECT、UPDATE、INSERT、DELETE，是用来对数据库里的数据进行操作的

--DDL（data definition language）：

命令包括CREATE、ALTER、DROP等，主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用

--DCL（Data Control Language）：

是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

二、sql语句组成（DML语句，查询）

（数据库工具里面可以创建数据库，数据库里面可以创建数据库表，表里面可以增删改数据）

例子：select *from emp e where e.deptno= 30;

例子： select e.enamefrom emp e where e.comm> e.sal;

语句分解：

--1、

select 表明你是查询，插入数据是insert into，删除是delete

--2、

*或者e.ename代表你要展示的是什么数据

*是所有数据都展示

e.ename是e这张表的某一列，列名叫ename，所以是展示这一列给用户看

--3、

from emp e的from代表你要去操作的是哪张表，emp是表的名字，e呢，可有可无，是emp这张表的临时名字，也叫别名，打个比方，如果表的名字叫亚洲舞王霹雳舞之魂暧昧一百分之尼古拉斯赵四，例子：

select*from 亚洲舞王霹雳舞之魂暧昧一百分之尼古拉斯赵四where 亚洲舞王霹雳舞之魂暧昧一百分之尼古拉斯赵四 .deptno =30;

看上面语句，是不是长到没朋友，所以需要一个别名来简化操作，例子：

select*from 亚洲舞王霹雳舞之魂暧昧一百分之尼古拉斯赵四 e where e .deptno=30;

一个e就代替了，当然你也可以取其他名字，result也可以，abc也可以，随你

--4、

where e.comm > e.sal或者where e.deptno = 30，代表判断条件，

where是关键字，先where后条件

你要展示的内容，如果需要过滤一些不相干的信息，就需要用到判断条件来过滤

where e.deptno = 30表示只有e表中的deptno部门编号等于30的才展示，也就是说只展示部门30的信息

where e.comm > e.sal表示e表中的comm奖金大于sal工资的员工的信息才展示

--5、

例子： select e.enamefrom emp e where e.comm> e.sal;

获取emp表中的数据，将表中的comm奖金大于sal工资的员工的信息都返回给select，然后select表明了只展示ename员工姓名

所以用户看到的数据是奖金大于工资的所有员工的姓名

--6、

函数分类：

1)单行函数

2)组函数

通俗简单的理解：

单行函数就是不分组的函数

组函数就是分组的函数

组函数如下：

AVG    返回平均值

COUNT    返回记录的数量

MAX    返回最大值

MIN    返回最小值

SUM    返回总值

STDDEV    返回标准差

VARIANCE    返回统计方差

通过GROUP BY子句将表中的记录划分成若干个小组

  -GROUP BY子句中必须包含指定的列

  -GROUP BY子句中不能使用列的别名

  -当使用GROUP BY子句时Orale服务器会自动对结果集合默认按GROUP BY子句所指定的列升序排列

  -在SELECT列表中除了组函数外，所有列都必须包含在GROUP BY子句中

  例：

     SELECT   deptno,AVG(sal)

     FROM     emp

     GROUP BY deptno;

  结果：

        DEPTNO     AVG(SAL)  

          10       2916.66667  

          20       2175  

          30       1566.66667

三、oracle关键字与函数

--1、

关键字distinct表示查询结果，去重复，再展示

select distinct deptno from emp

--2、

any表示多个条件的集合，满足any中的其中一个条件即可

select * from emp e where e.sal > any(1000,2000,3000)

--3、

some，跟any一样

select * from emp e where e.sal > some(1000,2000,3000)

--4、

all表示多个条件的集合，满足all中的所有条件才可

select * from emp e where e.sal > all(1000,2000,3000)

--5、

如果要展示或者不展示null值，可以这样写（comm是表中的某一列 ）

select * from emp e where comm is null，展示comm中null值的数据

select * from emp e where comm is not null，展示comm中不是null值的数据

--6、

between表示一个区间，1500与2000之间，包含1500与2000

select * from emp e where e.sal between 1500 and 2000;--在区间范围的

select e.ename,e.salfrom emp e where e.salnot between1500 and 2850;--不在区间范围的

--7、

in 代表是800或者1600，不是区间

select * from emp e where e.sal in (800,1600)在范围内

等价于select * from emp e where e.sal=800 or e.sal=1600

select * from emp e where e.sal not in (800,1600)不在范围内

select *from emp e1 where e1.salin(select e2.salfrom emp e2);

注意1：

in内的条件不支持超过1000条，如果要超过，要找办法解决

1)把in在条件拆分成不超过1000的多个in，然后用union all合并结果集

2)使用exists代替

注意2：

null值问题，比如not in（参数1，参数2，......，null），返回null

参考：

http://www.linuxidc.com/Linux/2012-07/66212.htm

--8、

执行exists括号里面的语句，如果语句执行有数据可以查到，则返回true或者false

select * from emp e where exists(select * from emp where sal>6000)

专业的说法：

将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行则可作为外查询的结果行，否则不能作为结果。

注意：如果要使用exists所查到的内容，必须将外面的表与exists的结果关联起来

select*

 fromemp e

 whereexists(select*

         from(selecte2.ename getnamefromemp e2 wheree2.deptno=20)

        wheree.ename=getname)

getname是别名

--9、

like

--10、

通配符，%，表示匹配任意次

where e.emane like 'S%'

--11、

通配符，下划线，表示只匹配一次

where e.emane like '_S%'

--12、

order by表示排序，它的语句放在所有语句的末尾

order by后面跟你要排序的列，列后面跟你排序的方式，是升序还是降序

select * from emp e order by sal asc升序

select * from emp e order by sal desc降序

select * from emp e order by sal asc，empno desc多列的排序

select * from emp e order by sal asc nulls first 遇到null值的排序，null值排最前面

select * from emp e order by sal asc nulls last遇到null值的排序，null值排最后面

注意：

要理解多列的排序，order by sal asc，empno desc

先按sal排序

如果sal排序完之后有相同的内容

相同内容的那几行按empno再进行排序

--13、

union，对两个集合进行并集操作，不包括重复行，同时进行默认规则的排序；

union all，对两个集合进行并集操作，包括重复行，不进行排序；

select * from emp e union select * from dept d；交集

select * from emp e union all select * from dept d；并集

--14、

intersect，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

select * from emp e intersect select * from dept d；

--15、

minus，取两个集合的差集，就是取两个集合的不同部分，不包括重复行，同时进行默认规则的排序

前面集合内容多于后面集合，会把前面集合多出来的东西返回

前面集合内容少于后面集合，后面集合多出来的东西不会返回

select * from emp e minus select * from dept d；

--16、

trim只去除两边空格，不去除中间空格

select trim('   a  a    ')from dual

--17、

字符串拼接使用双杠或者concat()

select e.ename || '的月薪是' || e.salfrom emp e;

select concat(concat(e.ename,'的月薪是'),e.sal)from emp e;

concat一次只能连接两个字符串，所以要多所以几次才连接成一句完整的话

--18、

nvl函数，为空值赋值

如果参数1是空，就返回参数2

select nvl(e.comm,0)from emp e；

nvl2函数，为空值赋值

如果参数1是空，就返回参数2，

参数2也为空，就返回参数3

select nvl2(e.comm,e.comm,9)from emp e；

--19、

to_char（参数1，参数2），将参数1（date或者number类型 ）按照参数2的格式转化为char数据类型

select to_char(1234,'9,9,9,9')from dual；

注意1：

两个参数的小数点前面的数字个数最好匹配，比如1210.73匹配 '9999.9'

注意2：

to_char返回的是char数据类型的，按java的理解，就是输入数字返回字符串

数值：

to_char(1210.73, '9999.9') 返回 '1210.7'
to_char(1210.73, '9,999.99') 返回 '1,210.73'
to_char(1210.73, '$9,999.00') 返回 '$1,210.73'
to_char(21, '000099') 返回 '000021'

to_char(852,'xxxx') 返回' 354'

日期：

to_char(sysdate,'d') 每周第几天
to_char(sysdate,'dd') 每月第几天
to_char(sysdate,'ddd') 每年第几天
to_char(sysdate,'ww') 每年第几周
to_char(sysdate,'mm') 每年第几月
to_char(sysdate,'q') 每年第几季

to_char(sysdate,'yyyy') 年

--20、

to_number，将char数据类型转换为数值并返回

select to_number('19902219')from dual

--21、

to_date，输入格式1、字符串、格式2，返回日期（格式1可以省略）

如果x格式为日期型(date)格式时，date '2008-12-31'

如果x格式为日期时间型(timestamp)格式时，timestamp '2008-12-31 12:31:30'

1)

select to_date('199912','yyyymm')from dual;

2)

select to_date((date'2008-12-31'))from dual;

3)

select to_date('199912','yyyymm'),
to_date('2000.05.20','yyyy.mm.dd'),
(date '2008-12-31') XXdate, 
to_date('2008-12-31 12:31:30','yyyy-mm-dd hh24:mi:ss'),
(timestamp '2008-12-31 12:31:30') XXtimestamp
from dual

--22、

extract(类型 from 日期)，从日期中获取信息，比如几号，几月等

1)

select 
extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小时,
extract(minute from timestamp '2001-2-16 2:38:40 ' ) 分钟,
extract(second from timestamp '2001-2-16 2:38:40 ' ) 秒,
extract(DAY from timestamp '2001-2-16 2:38:40 ' ) 日,
extract(MONTH from timestamp '2001-2-16 2:38:40 ' ) 月,
extract(YEAR from timestamp '2001-2-16 2:38:40 ' ) 年
 from dual;
2)
select extract (YEAR from date '2001-2-16' ) from dual; 

3)

select sysdate 当前日期,
extract(hour from timestamp timestamp sysdate) 小时,
extract(DAY from sysdate ) 日,
extract(MONTH from sysdate ) 月,
extract(YEAR from sysdate ) 年
 from dual;

--23、

decode，按条件取值

select t4.name "名字",

       decode(t4.subject,'语文', 1, '数学', t4.score,'英语', t4.score)

  from test4 t4

第一个参数，t4.subject是要判断的值，返回语文、数学、英语

第二个参数，表示目标值，比如语文

第三个参数，当要判断的值与目标匹配，返回第三个参数

第四个参数，表示目标值，比如数学

第五个参数，当要判断的值与目标匹配，返回第三个参数

后面的参数以此类推

注意1：

这个就跟java里面的switch类似，不断的case匹配

注意2：

参数必须类型相同或null

--24、

casewith，按条件取值 ，java里面的switch类似，不断的case匹配

写法1：

SELECT xqn, 
       CASE
          WHEN xqn = 1  THEN '星期一'
          WHEN xqn = 2  THEN '星期二'
          WHEN xqn = 3  THEN '星期三'
          else '星期三以后'
       END 星期
FROM xqb

写法2：

SELECT xqn, 
       CASE xqn
          WHEN 1  THEN '星期一'
          WHEN 2  THEN '星期二'
          WHEN 3  THEN '星期三'
          else '星期三以后'
       END 星期
FROM xqb

--25、

avg()，求平均数，计算分组之后的某一列值的平均

select avg(e.sal)from emp e groupby e.deptno

emp表按deptno部门编号分类，并求出每个部门的的平均工资

--26、

sum，求和

select sum (e.sal)from emp e groupby e.deptno

--27、

max，取最大值

select max (e.sal)from emp e groupby e.deptno

--28、

min，取最小值

select min (e.sal)from emp e groupby e.deptno

--29、

count（参数1，参数2），一列数据的统计，即这一列有多少条数据，包括null值

selectcount(e.deptno)from emp e;

selectcount(all e.deptno)from emp e;

select count(distinct e.deptno)from emp e;

参数1是all/distinct，all表示可重复，distinct 表示去重复，若加了参数1，返回的结果不统计null值

参数2是要统计的值

--30、

group by，分组语句，后面跟分组条件

select e.deptno from emp e group by e.deptno

select avg(e.sal)from emp e groupby e.deptno

group by是分组的关键字，deptno是分组条件

emp表的内容按deptno部门编号分组，并展示deptno部门编号

注意1、

使用了group by，select后面跟的条件会被限制，要么是组函数，要么是条件

比如上面只能是select e.deptno，因为分组条件是groupby e.deptno

或者是组函数比如select  avg()

注意2、

group by 可以过滤重复的内容

--31、

having，写在group by语句之后，是分组之后获取的结果集的过滤条件

select e.deptno from emp e group by e.deptno having e.deptno = 20;

emp表的内容按deptno部门编号分组，并展示deptno部门编号等于20的数据

注意1、

having和where作用是一样的，只是服务的对象不同，having是服务于group by分组，where服务于普通场景

--32、

sysdate，表示当前日期

select sysdate  hz from dual;

--33、

下一周星期几，next_day(日期,'星期几

多少月个后，add_months(日期,多少个月)

select e.ename,e.hiredate,next_day(add_months(e.hiredate,6),'星期一')from emp e

员工雇佣期 6 个月后下一个星期一的日期

--等等

四、oracle注意点

--1、

单引号里面内容表示一个值，双引号里面内容表示别名，不要搞混

--2、

两个横杠是注释，如--，或者/**/也是注释

--3、

英文逗号分隔两个内容，比如条件select e.ename,e.sal,e.comm

--4、

%是通配符，那如果我要查询带百分号的字符要怎么做？例如‘周%池’

用escape关键字，指定某个字符为转义功能

例子：

like '%\%%'  escape ‘\’，这个时候\就有转义功能，转义后的%就是只一个字符，不是通配符

like '%a%%'  escape ‘a’， 你也可以换为‘a’ ，

like '%f%%'  escape ‘f’， 换为‘f’ ，随你

--5、

任何含有null的表达式的结果都是null，需要使用nvl转义

select nvl(e.comm,0)from emp e；

--6、

dual是官方提供的一张虚拟表，里面只有一条记录

参考：

http://www.cnblogs.com/qiangqiang/archive/2010/10/15/1852229.html

--7、

null与空字符串的处理

1)

oracle中null与0，空字符串，空格，包括null与null也是不等的。

2)

oracle中对null做算术运算结果仍然为null。

3)

处理方法有nvl函数，比较方法是is null或者is not null。

4)

null不能被索引，比如select count（null） from dual的查询结果是0。

5)

null排序比其他所有类型的大，一个可以为空的字段倒排序，前面的都是null数据。

参考：

http://blog.itpub.net/24870090/viewspace-1057853/

http://blog.csdn.net/eleven204/article/details/6613925

--等等、

五、效率与区别

--1、exitst与in的区别与效率

select* from emp e1where e1.sal in(select e2.salfrom emp e2);

select *from emp e1 whereexists(select e2.salfrom emp e2 where e1.sal= e2.sal);

相同：都可以作为多个条件的集合，多数场景都可以互替

区别：（通俗地讲）

in是先查子表再到主表，exists是先查主表再查子表，所以这里涉及到表大小的问题

如果主表小子表大，那么肯定用exists先做主查询，然后在一个一个的去对比子表的数据，对比成功即返回

如果主表大子表小，那么肯定用in先执行子查询，然后在一个一个的去对比主表的数据，对比成功即返回

参考：

http://www.cnblogs.com/yf520gn/archive/2009/01/12/1374359.html

--2、any和some

共同：any表示多个条件的集合，满足any中的其中一个条件即可，some也是一样，使用上是没有区别的

区别：any一般用在非“=”的比较关系中，some用在“=”的比较关系中，一个否定一个肯定

--3、百分号与下划线的区别

共同：百分号和下划线都是表示通配符

区别：百分号表示匹配1个或者多个内容，下划线表示只匹配一个内容

--4、casewith与decode

共同：casewith与decode都可以用作按不同的条件返回不同的结果

区别：decode的条件只可以是一个确定的值，casewith可以是一个区间

--5、having和where

共同：都是用作条件过滤的关键字

区别：where是跟在from的后面添加条件判断，having是跟在group by后面添加条件判断

六、92语法与99语法

简介：

两个语法都是服务于多表连接的，两个语法在结构上有所不同，达到的效果却是一样的。

那么效果一样为什么还有搞两种写法呢？

这是因为92语法的过滤条件和表连接条件都放到了where子句中 。当条件过多时，联结条件多， 过滤条件多时，就容易造成混淆，所以在99语法里面，连接条件与过滤条件分开了。 

--92语法

1、等值连接

作用：展示多个表中相关的数据

理解：要多个表连接时，取多个表中相同的内容作为连接条件

举例：select *from emp e,dept dwhere e.deptno = d.deptno

2、非等值连接

作用：展示多个表中不相关的数据

理解：要多个表连接时，使用!= > < >=  <= between and 这些关键字作为连接条件

举例：select *from emp e,dept dwhere e.deptno != d.deptno

3、外连接

作用：跟等值连接一样展示多个表中相关的数据，并且把主表中不相关的数据也展示

理解：在等值基础上，确保 一张表(主表)的记录都存在，从表满足则匹配，不满足补充null

写法：关键字为(+) ，没有加号的那一边为主表，有加号的是从表，从表又叫左外连接或右外连接

举例：select *from emp e,dept dwhere d.deptno = e.deptno(+)

d.deptno = e.deptno(+)表示将主表d的deptno与从表e的deptno做关联，并且主表d中没有被关联上的内容也会展示

4、自连接 

作用：展示同表中相关的数据

理解：等值连接的异类，等值连接是不同表之间的连接，自连接是同一张表做连接

举例：select *from emp e1,emp e2where e1.mgr = e2.empno

5、笛卡尔积

作用：两张表的所有内容都连接一遍，也叫表*表

理解：简单粗暴的把两张表的所有内容都连接一遍

举例：select emp.empno,dept.locfrom emp,dept

--99语法

1、交叉连接，crossjoin

例子：select emp.empno,dept.locfrom emp crossjoin dept

对比：select emp.empno,dept.locfrom emp,dept（参考笛卡儿积）

理解：参考笛卡儿积

2、自然连接 ，naturaljoin

举例：select *from emp e naturaljoin dept d

对比：select *from emp e,dept dwhere e.deptno = d.deptno（参考等值连接）

理解：自然连接是基于两个表中同名列来进行连接

区别：自然连接的结果集不包含重复的数据

3、左连接，leftouter join

例子：select e.ename,d.deptno,d.dnamefrom emp e leftouter join dept don e.deptno=d.deptno

对比：selecte.ename,d.deptno,d.dnamefromemp e,dept d whered.deptno(+)=e.deptno;（参考外连接）

理解：emp left dept，emp是主表，即返回所有左边表中的行，即使在右边的表中没有可对应的列值

4、右连接，right outerjoin

例子：select e.ename,d.deptno,d.dnamefrom emp e right outerjoin dept d on e.deptno=d.deptno

对比：selecte.ename,d.deptno,d.dnamefromemp e,dept d whered.deptno=e.deptno(+);（参考外连接）

理解：emp right dept，dept是主表，即返回所有右边表中的行，即使在左边的表中没有可对应的列值

5、内连接，inner join，默认是内连接

例子：select* from emp einner join dept don e.deptno = d.deptno;

对比：select* from emp ejoin dept d on e.deptno= d.deptno;（默认是内连接）

对比：select* from emp e,dept dwhere e.deptno = d.deptno;（参考等值连接）

理解：就是普通的两个表的连接查询

6、全连接，fulljoin

例子：select*fromemp efulljoindept done.deptno=d.deptno;

理解：按条件把两张表连接起来，并把不连接的内容也展示，也可以说是左连接与右连接的并集