Oracle
Oracle
Oracle数据库简介
Oracle数据库系统是美国Oracle公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只要在一种机型上学习了ORACLE知识，便能在各种类型的机器上使用它。
Oracle体系结构
数据库：Oracle数据库是数据的物理存储。这就包括（数据文件ORA或者DBF、控制文件、联机日志、参数文件）。其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。
实例：1个Oracle实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有n个实例。
schema（模式，创建用户时分配和用户名称一样的一个模式）：一般而言，一个用户就对应一个schema，该用户的schema名等于用户名，并作为该用户缺省schema，用户是不能创建schema的，schema在创建用户的时候创建，并可以指定用户的各种表空间（这点与PostgreSQL是不同，PostgreSQL是可以创建schema并指派给某个用户）。当前连接到数据库上的用户创建的所有数据库对象默认都属于这个schema（即在不指明schema的情况下），比如若用户scott连接到数据库，然后create table test(id int not null)创建表，那么这个表被创建在了scott这个schema中；但若这样create kanon.table test(id int notnull)的话，这个表被创建在了kanon这个schema中，当然前提是权限允许。
用户：用户是在实例下建立的，不同实例可以建相同名字的用户。
表空间（给表分配空间）：表空间是Oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间（称之为system表空间)。每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件（datafile）。一个数据文件只能属于一个表空间。一个表空间就是一片磁盘区域，他又一个或者多个磁盘文件组成，一个表空间可以容纳许多表、索引或者簇等。每个表空间又一个预制的打一磁盘区域称为初始区间（initialextent）用完这个区间后在用下一个，直到用完表空间，这时候需要对表空间进行扩展，增加数据文件或者扩大已经存在的数据文件。
数据文件（dbf、ora）：数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。注：表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。由于oracle的数据库不是普通的概念，oracle是有用户和表空间对数据进行管理和存放的。但是表不是由表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表，这里区分就是用户了。
Oracle数据类型
Oracle创建表空间与用户
注意：先创建一个表空间或者使用已经存在的空间，然后创建一个用户，创建用户的同时会生成一个schema，创建用户之后需要给用户分配权限才能正常使用。
-- 创建表空间
create tablespace itcast
datafile 'c:\itcast.dbf'
size 100m
autoextend on
next 10m
itcast：为表空间名称
datafile：指定表空间对应的数据文件
size：定义的是表空间的初始大小
autoextend on：自动增长，当表空间存储都占满时，自动增长
next：后指定的是一次自动增长的大小
-- 创建用户
create user itcastuser
identified by itcast
default tablespace itcast
identified by：用户的密码
default tablespace：表空间名称
oracle数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。
-- 用户授权
Oracle中存在三个重要的角色：connect角色，resource角色，dba角色。
1）CONNECT角色：授予最终用户的典型权利，最基本的
ALTER SESSION --修改会话
CREATE CLUSTER --建立聚簇
CREATE DATABASE LINK --建立数据库链接
CREATE SEQUENCE --建立序列
CREATE SESSION --建立会话
CREATE SYNONYM --建立同义词
CREATE VIEW --建立视图
2）RESOURCE角色：授予开发人员的
CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立过程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立触发器
CREATE TYPE --建立类型
3）DBA角色：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构，并且系统权限也需要DBA授出，且DBA用户可以操作全体用户的任意基表，包括删除。
-- 授权语句，进入system用户下给用户赋予dba权限，否则无法正常登陆
-- 注意：在安装oracle时需要记住给默认角色分配的密码，否则找回比较麻烦
grant dba to itcastuser
Oracle表管理语句
注意：其实数据库操作语句大部分一致，只是每个数据库的关键字或数据类型不太一样。因为Oracle的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据库。如果事务提交后则不可以再回滚。
-- 创建表
create table sys_dept (
dept_id number(20) not null,
parent_id number(20) default 0,
ancestors varchar2(50) default '',
dept_name varchar2(30) default '',
order_num number(4) default 0,
leader varchar2(20) default null,
phone varchar2(11) default null,
email varchar2(50) default null,
status char(1) default '0',
del_flag char(1) default '0',
create_by varchar2(64) default '',
create_time date,
update_by varchar2(64) default '',
update_time date
);
-- 修改表
范例：在person表中增加列address
alter table person add(address varchar2(10));
范例：把person表的address列的长度修改成20长度
alter table person modify(address varchar2(20));
-- 设置主键
alter table sys_dept add constraint pk_sys_dept primary key (dept_id);
-- 设置注释
comment on table sys_dept is '部门信息表';
comment on column sys_dept.dept_id is '部门主键';
-- 删除表
DROP TABLE 表名
-- 序列，在很多数据库中都存在一个自动增长的列，如果现在要想在Oracle中完成自动增长的功能，则只能依靠序列完成，所有的自动增长操作，需要用户手工完成处理。
-- 创建序列
CREATE SEQUENCE seqpersonid;
-- 序列创建完成之后，所有的自动增长应该由用户自己处理，所以在序列中提供了以下的两种操作：
nextval:取得序列的下一个内容
currval:取得序列的当前内容
select seqpersonid.nextval from dual;
select seqpersonid.currval from dual;
-- 在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪一张表使用都可以，但是我们一般都是一张表用一个序列。序列的管理一般使用工具来管理。
-- 在插入数据时需要自增的主键中可以这样使用
insert into person values(seqpersonid.nextval,"name");
Oracle常用查询语句
-- 把小写的字符转换成大小的字符
select upper('smith') from person;
-- 把大写字符变成小写字符
select lower('SMITH') from person;
-- 四舍五入函数：ROUND()，默认情况下ROUND四舍五入取整，可以自己指定保留的位数。
select ROUND(12.5678,2) from person;
-- 查询雇员的进入公司的周数
select ROUND((sysdate-入职时间)/7) from person;
-- 获得两个时间段中的月数：MONTHS_BETWEEN()
select MONTHS_BETWEEN(sysdate,时间字段) from person;
-- TO_CHAR:字符串转换函数
select TO_CHAR(时间字段,'yyyy') 年, TO_CHAR(时间字段,'mm') 月, TO_CHAR(时间字段,'dd') 日 from person;
-- 加fm去掉前面的0，即2023-05-31变为2023-5-31
select TO_CHAR(时间字段,'fmyyyy-mm-dd') 时间 from person;
-- TO_DATE可以把字符串的数据转换成日期类型
select TO_DATE('2023-05-31','yyyy-mm-dd') 时间 from person;
-- 如果为空则返回0
select nvl(字段名称,0) from person;
-- Decode函数，该函数类似 if....else if...esle
语法：DECODE(col/expression, [search1,result1],[search2, result2]....[default])
Col/expression：列名或表达式
Search1，search2...：用于比较的条件
Result1, result2...：返回值
-- 如果年龄为18则年轻，如果为30则老了，如果都不是，则默认输出你几岁
select decode(age,18,'年轻',30,'老了','你几岁?') from person;
-- case when语句
select t.empno,t.ename,
case when t.job = 'CLERK' then '业务员'
when t.job = 'MANAGER' then '经理'
when t.job = 'ANALYST' then '分析员'
when t.job = 'PRESIDENT' then '总裁'
when t.job = 'SALESMAN' then '销售'
else '无业'
end from person t
-- 统计记录数count()
-- 最小值查询min()
-- 最大值查询max()
-- 查询平均值avg()
-- 求和函数sum()
-- 分组查询：SELECT * |列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列
名 1 ASC|DESC，列名 2...ASC|DESC
-- 分页查询
-- 方式一
select * from (select rownum no,a.* from emp a) where no between (pageNo-1)*pageSize+1 and pageNo*pageSize;
-- 其中pageNo代表当前页，pageSize代表每页显示的数据条数，比如当前是第1页，每页显示5条，套用上述sql语句公式后如下所示：
select * from (select rownum no,a.* from emp a) where no between 1 and 5;
-- rownum会给每一条数据加上一个编号，如果需要排序之后在进行分页，可以直接在()中的sql语句里面进行排序，例如我们取出按照薪水sal排序之后的前5条数据的sql语句如下：
select * from (select rownum no,a.* from emp a order by a.sal) where no between 1 and 5;
-- 方式二
select * from emp where rownum between (pageNo-1)*pageSize+1 and pageNo*pageSize;
-- 其中rownum是表中的虚拟编号从1开始，pageNo代表当前页，pageSize代表每页显示的数据条数，比如当前是第1页，每页显示5条，套用上述sql语句公式后如下所示：
select * from emp where rownum between 1 and 5;
-- 如果需要排序之后在进行分页，可以把排序完成之后的表放在from后面，之后在进行分组，例如我们取出按照薪水sal排序之后的前5条数据的sql语句如下：
select * from (select * from emp order by sal) where rownum between 1 and 5;
-- 方式三
select * from emp where rownum < pageNo*pageSize+1 minus select * from emp where rownum < (pageNo-1)*pageSize+1;
-- 其中rownum是表中的虚拟编号从1开始，pageNo代表当前页，pageSize代表每页显示的数据条数，使用minus可以获得“第一条sql语句获得的结果”减去“minus前后两条sql语句结果的交集”，比如当前是第1页，每页显示5条，套用上述sql语句公式后如下所示：
select * from emp where rownum < 6 minus select * from emp where rownum < 1;
-- 如果需要排序之后在进行分页，可以把排序完成之后的表放在from后面，之后在进行分组，例如我们取出按照薪水sal排序之后的前5条数据的sql语句如下：
select * from (select * from emp order by sal) where rownum < 6 minus select * from (select * from emp order by sal) where rownum < 1;
-- 12c新版本分页语法，从第10条开始（第一条为0），取100条
select * from person offset 10 rows fetch next 100 rows only;
Oracle视图、索引、存储过程、触发器等
视图管理
-- 创建视图
create view empvd20 as select * from emp t where t.deptno = 20;
-- 覆盖替换视图
create or replace view empvd20 as select * from emp t where t.deptno = 20;
-- 设置视图只读
create or replace view empvd20 as select * from emp t where t.deptno = 20 with read only;
索引管理
-- 创建单列索引
create index pname_index on person(name);
-- 创建复合索引
create index pname_gender_index on person(name, gender);
存储过程管理
-- 创建存储过程语法：
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
AS
begin
PLSQL子程序体;
End;
-- 或者
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
is
begin
PLSQL子程序体;
End 过程名;
-- 范例：创建一个输出helloword的存储过程
create or replace procedure helloworld is
begin
dbms_output.put_line('helloworld');
end helloworld;
-- 调用存储过程，在plsql中调用存储过程
begin
-- Call the procedure
helloworld;
end;
-- 范例2：给指定的员工涨100工资，并打印出涨前和涨后的工资。分析：我们需要使用带有参数的存储过程
create or replace procedure addSal1(eno in number) is
pemp myemp%rowtype;
begin
select * into pemp from myemp where empno = eno;
update myemp set sal = sal + 100 where empno = eno;
dbms_output.put_line('涨工资前' || pemp.sal || '涨工资后' ||
(pemp.sal + 100));
end addSal1;
-- 调用
begin
-- Call the procedure
addsal1(eno => 7902);
commit;
end;
触发器管理
-- 触发器语法
CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(条件) ] ]
begin
PLSQL块
End 触发器名
-- 范例：插入员工后打印一句话“一个新员工插入成功”
create or replace trigger testTrigger
after insert on person
declare
-- local variables here
begin
dbms_output.put_line('一个员工被插入');
end testTrigger;
