oracle学习笔记

一、oracle的体系结构

数据库（database）：oracle数据库是数据的物理存储。（相当于一大块存所有oracle数据的硬盘区域？）包括（数据文件ORA或DBF、控制文件、联机日志、参数文件）。它和其它数据库概念不一样，一整个操作系统只有这一个库（就是说实际上oracle就只有一个大数据库）。

实例（oracle instance）：database可以创建n个oracle实例。（就好比一个地球能有n个国家，分了一块区域给你用啦）。实例中有一系列后台进程和内存结构。但实际上通常oracle数据库只会用一个数据库实例orcl。

表空间：是一个逻辑单位，一个实例可以创建n个表空间（就好比一个国家有n个省份）。它只是个逻辑单位，但可以映射磁盘上的物理存储单位数据文件（dbf），一个表空间可以映射多个dbf，但一个dbf只能属于一个表空间。

数据文件（dbf）：是一个小的物理存储单位。数据库当中的数据实际就是存储于数据文件当中，然后数据文件是对应属于表空间的（表空间好比省份，是行政区域单位，数据文件类似山川河流，为物理地区单位，数据好比人，人实际是居住在山川河流之上的）。

用户：是在数据库实例下创建的（也是由已经登录的用户创建的，该用户一定要有创建用户的权限）。创建了用户之后，用户登录后就能创建表（当然是放我们的数据字段啦），表是会放于某一个表空间的，实际则是放于该表空间所对应的一个或多个数据文件啦。表实际上是隶属于用户的，用户对表进行增删改查。

oracle里新建一个项目（一个java项目所有的数据）：

创建表空间（对应好磁盘上的数据文件），创建用户，用户创建表存于该表空间。

mysql创建项目：

创建数据库，创建表

二、基本查询

用sql（结构化查询语言），但oracle有基于sql的方言。

DML（数据库操作语言）：insert，update，delete这些。分别用于表中行的增删改查。

DDL（数据库定义语言）：create（创建表），drop（删除表）；还可为表加入索引。

DCL（数据库控制语言）：grant,revoke确定单个用户和用户组对数据库对象的访问。（是用来给用户更改权限的嘛？）

（1）基本查询：select好像不属于DML语言（以上四种都属于sql语言咯）

select * from 表名；

select distinct *|列名 from 表名；（除去相同数据，只查找不同数据）

select ename，sal*12 from emp；（sql支持‘+，-，*，/’四则运算）

select ‘名字是’|| ename || ‘工资是’|| sal from emp；（‘||’为oracle中连接字符串的方法，当然也可以用concat（‘名字是’，ename，‘工资是’，sal）函数来对字符串进行拼接）

（2）条件查询和排序

条件查询会用到以下

比较运算符1：=，>,>=,<,<=,<>(!=) (oracle中赋值使用 ：= 符号)

比较运算符2：between...and....(即在哪两个数值之间，包含边界)；in（set）（在这个set里面的数值）；like（模糊查询）（%...%）(_...%) _代表一个字符，%代表很多字符或这没有字符均可；is null（是空值，空值和任何值运算结果都是null）

逻辑运算符：and ；or； not （交集，并集和补集。应该较好理解吧）

举例子哦：

select * from emp where comm is not null;

select * from emp where comm is not null and sal>1500;

select * from emp where comm is not null and not（sal>1500）;

select * from emp where sal>=1500 and sal<=3000;

select * from emp where sal between 1500 and 3000;(和上述效果相同哦)

select * from emp where hiredate between '1-1月-1981' and '31-12月-1981';(这玩意还能像这样比较Date类型的数据哦)

select * from emp where ename = 'SMITH';(oracle中查询条件的值是区分大小写的)

select * from emp where empno in（7369,7499,7521）；

select * from emp where empno not in（7369,7499,7521）；

select * from emp where ename like ‘_M%’;(这个是模糊查询，即查询了第二个字母是M的员工)

select * from emp where ename like ‘%%’;（这样其实等同于查询全部）

讲完了条件查询再来讲排序哦

select * from emp where deptno=1 order by sal;(默认是升序asc ,降序需要在后面加上desc)

select * from emp order by sal asc,hiredate desc;(首先一定要满足工资是上涨的顺序,如果遇到工资相同时,才去满足雇佣日期下降的规则)

select * from emp order by sal nulls first;(因为sal可能为null,这样可以把空值都显示在前面)

划重点就是 !!! order by在oracle中一定是放在select语句后的.

（3）左外连接、右外连接和内连接

select * from emp inner join dept on emp.dnum =dept.id;（内连接，只查询左右连接共同满足的数据）

select * from emp left outer join dept on emp.dnum =dept.id;(左外连接，左边全部数据都要查出来，右边没有的就补null)

select * from emp right outer join dept on emp.dnum =dept.id;（和左外连接相反，右边的数据全部要查出来，左边不能对应就补null）

三、sql中的函数

（1）单行函数

字符、数值、日期、转换、通用

字符函数：包括大小写控制函数（lower，upper，initcap）和字符控制函数（concat，substr，instr，length，trim，replace）（用来对字符串进行操作的函数）

concat（）：连接字符串

substr（‘zhang’，1,3）：截取字符串，结果为‘zha’，第二个参数1和0都可以，都表示个字符的索引

例子：dual为伪表，这玩意是oracle给我们用来验证函数的

select concat('hello','world') from dual；

select substr（‘hello’，1,3） from dual；

select length（‘hello’） from dual；

select replace（‘hello’，‘l’,'x'） from dual；(相当于把l都替换成x了)

数值函数：

round（45.926,2）（四舍五入到小数点后第二位，45.93）

trunc（45.926,2）（截断到小数点后第二位，45.92）

mod（1600,300）（求余数，100）

日期函数：

oracle中的日期函数实际上包含两个值，一个是日期，一个是时间。默认格式是DD-MON-RR（说实话不知道是啥意思）

两个日期之间可以相减返回的相差的天数。

select round（（sysdate-hiredate）/7） from emp;(相当于把雇佣的周数四舍五入出来了)

months_between()函数

select round(months_between(sysdate,hiredate)) from emp;(雇佣月数)

add_months() 函数

select add_months(sysdate,3) from dual;(求系统时间后3个月的时间)

转换函数：

select to_char(hiredate,'yyyy-mm-dd') from emp;

select to_char(hiredate,'fmyyyy-mm-dd') from emp;(将日期转换为字符串，但是删除所有的前导0)

select to_number(‘10’)+to_number(‘10’) from dual;

select to_date（‘1994-04-03’，‘yyyy-mm-dd’） from dual;

通用函数：nvl（comm，0），nullif（），nvl2（），coalesce（）

select nvl(comm,0) from emp;(相当于将comm字段里的所有null变成了0，这很有必要，因为comm字段要拿来计算，而null和任何计算都为null)

——————————————————————————————

四、rownum、rowid、条件表达式、select中的行转列和列转行、创建索引对象、索引的分类、创建序列对象、触发器

rownum(用作分页查询)：和mysql的分页查询limit不一样，oracle的分页查询需要用到rownum也就是oracle给我们提供的伪列，这个rownum是每查询到一行数据才增加1的，初始值为1。

所以像select rownum,ename from emp where rownum>6;这种句子是错误的,因为你要查行号大于6的可是你条的行号必须是1,所以你一条数据也查不出来。（貌似好像不是这个步骤，是先查询到一个结果集再加上行号的一列，可是条数据必须为1，发现需要的数据不是1，就查下一条，还是给它行号标为1，还是不行，所以后一条数据都查不出来）会直接报错。而要做到分页查询必须要用到rownum且是子查询，是因为oracle没有索引吗？

select * from (select rownum hanghao,ename from emp) tt where tt.hanghao between 6 and 10;

__________________________________________________

rowid：也是个伪列，代表的是每行记录所存放的真实物理地址（16进制数）

华为笔试题有个就是删除除小rowid外的相同名字数据，要用两张表关联来做。因为rowid物理位置相同所以很方便。

delete from p p1 where rowid>(select min(rowid) from p p2 where p1.name = p2.name);

__________________________________________________

条件表达式：

1）sql通用的:相当于可以把行内的数据取别名，还可以动态的取别名比如取该行数据中别的列的数据，可用于行转列啊

select case grade

when 1 then '一级'

when 2 then '二级'

when 3 then '三级'

when 4 then '四级'

else '五级'

end "等级" from salgrade;

2）oracle自己的：decode（）函数

select decode(grade,1,'一级',2,'二级',3,'三级',4,'四级','五级') 等级 from salgrade;

__________________________________________________

select中的行转列和列转行：

下面这个相当于是一个小的行转列，动态的取出了每一行yy字段中的cc值，并将原本的yy名字变成了列名，cc变成了该列对应的值，成功完成了行转列。当然也可以用decode函数做哦。

select

sum(cc) 总员工数,

sum(case yy when '1980' then cc end) "1980",

sum(case yy when '1981' then cc end) "1981",

sum(case yy when '1982' then cc end) "1982",

sum(case yy when '1987' then cc end) "1987"

from

(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;

行转列：（用pivot函数），但貌似只有oracle11才可以。

select * from (select account,type,month from a) pivot(sum(account) for type in (select distinct type from a));

前面括号里为转换之前的表，sum（account）为对应的需要列于转换列的值，in后面写的是需要转换成列的行的不重复集合，也可以一个个列出来并换成自己想要的别名。

列转行：（用unpivot函数）

select nation,name,title from

temp

unpivot

(name for title in (,第二,第三,第四))t；

name和title均为自定义的列名，分别对应原列下的数值和原列名。（好复杂。。。可能不太准确）

————————————————————————————————

创建序列对象 sequence（oracle中要进行数据的自增必须要用到序列对象）

create sequence lwx;

次取序列对象必须先用nextval，再用currval才能取到当前值

————————————————————————————————

创建索引对象

索引是用于加速数据存储的数据对象，合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。创建索引后，查询数据时用的条件是该索引对应的列的话速度会提高很多倍哦。

索引分为单列索引和复合索引。

create index id1 on emp(ename); 单列索引

create index id2 on emp(ename，deptno); 双列索引

create index id3 on emp(deptno，ename); 这样掉个顺序的话其实也可以

——————————————————————————————————