Oracle应用开发实战
1. oracle介绍
ORACLE数据库系统是美国ORACLE公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只要在一种机型上学习了ORACLE知识，便能在各种类型的机器上使用它。
2. oracle10g安装
参考：下的安装说明书。
1. 解压oracle数据库安装包，如果是win7或者win8系统右键点击setup.exe选择兼容性，以xp方式，并且以管理员方式运行，以及其他所有用户都按着此规则如图
2. 并双击解压目录下的setup.exe，出现安装界面，如下：
3. 输入口令和确认口令，如：password，点击下一步，出现如下进度条，
注：此口令即是管理员密码。
4. 检查先决条件，选中红框所示的选择框，如下图：
5. 点击“下一步”，出现“概要”界面，点击“安装”。
6. 出现安装进度条，等待安装完成，如下图：
7. 安装完成后，自动运行配置向导，如下图，等待其完成：
8. 完成后，出现“口令管理”界面，点击“口令管理”，如下图：
9. 将SCOTT和HR用户的沟去掉（解锁这两个账户），如下图所示，点击“确定”：
10. 回到“口令管理”界面，点击“确定”，如下图：
11. 安装结束，点击“退出”。
1. 登录
运行cmd进入命令行
sqlplus 用户名/密码@ip地址:1521/orcl [as sysdba]
如果是超级管理员需要在用户名/密码后面加上 as sysdba,是以系统管理员的身份来登录的，如图。
如果是普通用户不需要as sysdba
2. 查看当前连接数据库的用户
使用show user查看当前的用户
3. 用户的切换
在登录的状态下输入：conn 用户名/密码 [as sysdba]
如图：
Ø 切换为超级管理员
Ø 切换为普通的用户
4. 查看用户下的表
为了防止命令行中数据展示表格错乱的情况可以设计行宽和列宽
set linesize 300; 每行展示300个字符
Col 列名 for a[数字]，某一列占几个字符
在用户登录的情况下输入：select * from tab;
查看表中的数据
Col ename for a8
Col mgr for 9999
Col sal for 9999
Select * from emp;
如果使用dba用户登录查询该表无法查看到，因为sys用户下没有这个emp表
可以使用select * from [用户名].[表名] 来查看某用户下的表的数据
5. 查看表的结构
Desc 表名
Number(4) 最大长度为4为的数值类型
Varchar2(10) 最大长度为10的字符串，varchar2用于存储可变长度的字符串，.varchar2把所有字符都占两字节处理(一般情况下)，varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节，VARCHAR2把空串等同于null处理，而varchar仍按照空串处理；建议在oracle中使用varchar2
Number(7,2) 数值类型整数位占5位，小数位占2位，一共7位。
Date 时间类型
3. PLsql Developer安装
傻瓜式安装即可，不建议汉化，最后会提示输入序列号，打开plsql-sn.txt复制序列号，输入即可。重要提示：不要把plsql developer安装在有括号的目录下，否则会出现连不上数据库的情况，安装目录也不要出现中文。
Plsql developer是最好用也是公司里面大量使用的数据库图形化工具，功能十分强大，在开发人员中广受欢迎，基本上是使用oracle数据库的必备神器。Oracle支持命令行方式操作，在实际的开发中很少使用命令行，绝大多数使用图形化的工具Plsql developer
4. Oracle体系结构
Ø 数据库：
Oracle数据库是数据的物理存储。这就包括（数据文件ORA或者DBF、控制文件、联机日志、参数文件）。其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。
Ø 实例：
一个Oracle实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有n个实例。
Ø 用户：
Ø 表空间：
表空间是Oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为system表空间)。
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。
Ø 数据文件（dbf、ora）:
数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。
注： 表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。
由于oracle的数据库不是普通的概念，oracle是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！
5. 创建表空间
表空间？ ORACLE数据库的逻辑单元。 数据库---表空间 一个表空间可以与多个数据文件（物理结构）关联
一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表。
| create tablespace itcast001 datafile 'c:\itcast001.dbf' size 100m autoextend on next 10m |
itcast 为表空间名称
datafile 指定表空间对应的数据文件
size 后定义的是表空间的初始大小
autoextend on 自动增长 ，当表空间存储都占满时，自动增长
next 后指定的是一次自动增长的大小。
6. 用户
1创建用户
| create user itcastuser identified by itcast default tablespace itcast001 |
identified by 后边是用户的密码
default tablespace 后边是表空间名称
oracle数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。
2用户赋权限
Oracle中已存在三个重要的角色：connect角色，resource角色，dba角色。
CONNECT角色： --是授予最终用户的典型权利，最基本的
ALTER SESSION --修改会话
CREATE CLUSTER --建立聚簇
CREATE DATABASE LINK --建立数据库链接
CREATE SEQUENCE --建立序列
CREATE SESSION --建立会话
CREATE SYNONYM --建立同义词
CREATE VIEW --建立视图
RESOURCE角色： --是授予开发人员的
CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立过程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立触发器
CREATE TYPE --建立类型
DBA角色：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构，并且系统权限也需要DBA授出，且DBA用户可以操作全体用户的任意基表，包括删除
| grant dba to itcastuser |
进入system用户下给用户赋予dba权限，否则无法正常登陆
3中文乱码问题解决
1.查看服务器端编码
select userenv('language') from dual;
我实际查到的结果为:AMERICAN_AMERICA.ZHS16GBK
2.执行语句 select * from V$NLS_PARAMETERS
查看第一行中PARAMETER项中为NLS_LANGUAGE 对应的VALUE项中是否和第一步得到的值一样。
如果不是，需要设置环境变量.
否则PLsql客户端使用的编码和服务器端编码不一致,插入中文时就会出现乱码.
3.设置环境变量
计算机->属性->高级系统设置->环境变量->新建
设置变量名:NLS_LANG,变量值:第1步查到的值， 我的是
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
4.重新启动PLsql,插入数据正常
7.Oracle数据类型
1.oracle 数据类型
2.常用的数据类型
| No |
数据类型 |
描述 |
| 1 |
Varchar， varchar2 |
表示一个字符串 |
| 2 |
NUMBER |
NUMBER(n)表示一个整数，长度是n |
| NUMBER(m,n):表示一个小数，总长度是m，小数是n，整数是m-n Number(10,2):整数部分占 8 位，小数部分占2份 |
||
| 3 |
DATE |
表示日期类型 |
| 4 |
CLOB |
大对象，表示大文本数据类型，可存4G |
| 5 |
BLOB |
大对象，表示二进制数据，可存4G |
8.表的管理
1建表
语法：
Create table 表名（
字段1 数据类型 [default 默认值],
字段2 数据类型 [default 默认值],
...
字段n 数据类型 [default 默认值]
）;
范例：创建person表
create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
birthday date
);
insert into person(pid,name,gender,birthday)
values(1,'张三',1,to_date('1999-12-22','yyyy-MM-dd'));
2.表删除
语法：DROP TABLE 表名
3.表的修改
Ø 添加语法：ALTER TABLE 表名称 ADD(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]...)
Ø 修改语法：ALTER TABLE 表名称 MODIFY(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]...)
Ø 修改列名: ALTER TABLE 表名称 RENAME COLUMN 列名1 TO 列名2
范例：在person表中增加列address
alter table person add(address varchar2(10));
范例：把person表的address列的长度修改成20长度
alter table person modify(address varchar2(20));
4.截断表
在person表中使用delete语句删除数据，则可以使用rollback来回滚，如果想要清空一张表的数据，同时不想回滚可以立即释放资源需要使用截断表的语法
语法：TRUNCATE TABLE 表名
范例：截断person表
truncate table person;
5.数据库表数据的更新
1. INSERT（增加）
语法：
INSERT INTO表名[(列名1，列名2，...)]VALUES(值1，值2，...)
标准写法
简单写法（不建议）
INSERT INTO 表名VALUES(值1，值2，...)
insert into person
values(1,'9-5月-1981','北京北七家');
注意：使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用null
insert into person
values(2,'李四',null,'北京育新');
2.事务
这是因为oracle的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据库。如果事务提交后则不可以再回滚。
提交：commit
回滚：rollback
1.事务保存点与回滚：
语法：
savepoint 保存点名字
示例：
savepoint a1; --设置一个保存点 a1;
update person set name='张三' where pid='101' --修改一条数据
savepoint a2; --设置一个保存点 a1;
update tmp set name='李四' where id='102 --再修改一条数据
rollback to a2; --回滚到a2 保存点。 此时在 a2 保存点之后的所有数据修改视为无效。
rollback to a1; --这里可以再从a2保存点再回滚到a1 保存点。 此时在 a1 保存点之后的所有数据修改视为无效。
rollback; --回滚全部。即撤消至上一次提交事务后的所有数据修改。
commit; --提交事务 将回滚后的事务提交，并会删除所有保存点。
注意：我们可以从a2向前再回滚到a1 ，但无法从a1回滚到a2。也就是只能向前回滚，不能从前面的点再向后回滚！
2．事务的隔离级别：
事务的隔离性是什么？
事务隔离级别有哪些？
Oracle支持的隔离级别是什么？
3.UPDATE（修改）
全部修改：UPDATE 表名 SET 列名1=值1，列名2=值2，....
局部修改：UPDATE 表名 SET 列名1=值1，列名2=值2，....WHERE 修改条件；
全部更新
局部更新
4. DELETE（删除）
语法 : DELETE FROM 表名 WHERE 删除条件;
Truncate table实现数据删除
比较truncat与delete实现数据删除？
1. delete删除的数据可以rollback,也可以闪回
2. delete删除可能产生碎片，并且不释放空间
3. truncate是先摧毁表结构，再重构表结构
4.通过闪回实现数据恢复
1.闪回的原理
2.闪回的实现方式
3.闪回的注意事项
5. 碎片问题的思考
1.什么是碎片
2.碎片的产生
3.碎片问题的避免与解决
9.序列
在很多数据库中都存在一个自动增长的列,如果现在要想在oracle 中完成自动增长的功能,则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。
语法：CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];
范例:创建一个seqpersonid的序列,验证自动增长的操作
CREATE SEQUENCE seqpersonid;
序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：
nextval :取得序列的下一个内容
currval :取得序列的当前内容
select seqpersonid.nextval from dual;
select seqpersonid.currval from dual;
在插入数据时需要自增的主键中可以这样使用
在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪一张表使用都可以，但是我们一般都是一张表用一个序列。
序列的管理一般使用工具来管理。
10.约束
在数据库开发中，约束是必不可少，使用约束可以更好的保证数据的完整性。
1.主键约束
主键约束都是在id上使用，而且本身已经默认了内容不能为空，可以在建表的时候指定。
创建一张表，把pid作为主键
create table person(
pid number(10) primary key,
birthday date
);
主键不可重复， SCOTT.SYS_C0017981是系统自动分配的约束的名字
主键不可为空
我们可以自己来指定主键约束的名字
create table person(
pid number(10),
birthday date,
constraint person_pk_pid primary key(pid)
);
2.非空约束
使用非空约束，可以使指定的字段不可以为空。
范例：建立一张pid和name不可以为空的表
create table person(
pid number(10) not null,
name varchar2(10) not null,
gender number(1),
);
3.唯一约束（unique）
表中的一个字段的内容是唯一的
范例：建表一个name是唯一的表
create table person(
pid number(10),
name varchar2(10) unique,
birthday date
);
唯一约束的名字也可以自定义
create table person(
pid number(10),
constraint person_name_uk unique(name)
);
4.检查约束
使用检查约束可以来约束字段值的合法范围。
范例：创建一张表性别只能是1或2
create table person(
pid number(10),
gender number(1) check(gender in (1,2)),
birthday date
);
检查约束也可以自定义
create table person(
pid number(10),
constraint person_gender_ck check(gender in (1,2))
);
5.外键约束
之前所讲的都是单表的约束，外键是两张表的约束，可以保证关联数据的完整性。
范例：创建两张表，一张订单表，一张是订单明细表，订单和明细是一对多的关系
create table orders(
order_id number(10),
total_price number(10,2),
order_time date,
constraint orders_order_id_pk primary key(order_id)
);
create table order_detail(
detail_id number(10),
order_id number(10),
item_name varchar2(10),
quantity number(10),
constraint order_detail_detail_id_pk primary key(detail_id)
);
insert into orders values(1,200,to_date('2015-12-12','yyyy-MM-dd'));
insert into order_detail values(1,2,'java',1);
我们在两张表中插入如上两条数据，我们发现在order_detail表中插入的order_id在order表中并不存在，这样在数据库中就产生了脏数据。此时需要外键来约束它。
我们再次建表
create table orders(
order_id number(10),
constraint order_detail_detail_id_pk primary key(detail_id),
constraint order_detail_order_id_fk foreign key(order_id) references orders(order_id)
);
外键关联一定注意：
外键一定是主表的主键
删表时一定先删子表再删主表，如果直接删主表会出现由于约束存在无法删除的问题
但是可以强制删除drop table orders cascade constraint;(不建议)
删除主表的数据可以先删除子表的关联数据，再删主表，也可以使用级联删除。
级联删除在外键约束上要加上on delete cascade 如
constraint order_detail_order_id_fk foreign key(order_id)
references orders(order_id) on delete cascade
11.Scott用户下的表结构
12. sql
1.sql简介
结构化查询语言(Structured Query Language)简称sql(发音：/ˈɛs kjuː ˈɛl/ "S-Q-L")，结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统,可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。
Ø DML(数据库操作语言): 其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。
Ø DDL(数据库定义语言): 其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
Ø DCL(数据库控制语言):它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户 组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
2. 简单查询
1.查询语法
Select * |列名 from 表名
2.别名用法
在查询的结果列中可以使用别名
Select 列名 别名，列名别名，... from emp;
3. 消除重复的数据
Select distinct *|列名,... from emp;
使用distinct可以消除重复的行，如果查询多列的必须保证多列都重复才能去掉重复
4. 字符串连接查询
查询雇员编号，姓名，工作
编号是：7369的雇员， 姓名是：smith，工作是：clerk
字符串的连接使用‘||’
5. 查询中四则运算
查询每个雇员的年薪
select ename,sal*12 from emp;
select ename,sal*12 income from emp;
3. 条件查询
在查询绝大多数都会有条件的限制
语法：select *|列名 from 表名 where 条件
例如：查询工资大于1500的所有雇员
1. 非空和空的限制
Ø 示例：查询每月能得到奖金的雇员
分析：只要字段中存在内容表示不为空，如果不存在内容就是null，
语法：列名 IS NOT NULL
为空 列名 IS NULL
Ø 范例：查询工资大于1500并且有奖金领取的雇员
分析：多个查询条件同时满足之间使用‘AND’
Ø 范例:查询工资大于1500或者有奖金的雇员
分析：多个查询条件或满足，条件之间使用“OR”
Ø 范例：查询工资不大于1500和没有奖金的人
语法：NOT(查询条件)
2.范围限制
范例：基本工资大于1500但是小于3000的全部雇员
分析：sal>1500,sal<3000
Between and等于 sal > =1500 and sal <= 3000
范例：查询1981-1-1到1981-12-31号入职的雇员
分析：between and 不仅可以使用在数值之间，也可以用在日期的区间
范例：查询雇员名字叫smith的雇员
范例:查询雇员编号是7369,7499,7521的雇员编号的具体信息
如果使用之前的做法可以使用OR关键字
语法: 列名 IN (值1，值2，....)
列名 NOT IN (值1， 值2，...)
其中的值不仅可以是数值类型也可以是字符串
范例:查询雇员姓名是’SMITH’,’ALLEN’,’WARD’的雇员具体信息
3．模糊查询
在常用的站点中经常会有模糊查询，即：输入一个关键字，把符合的内容全部的查询出来，在sql中使用LIKE语句完成。
在LIKE中主要使用以下两种通配符
“%”：可以匹配任意长度的内容
“_”:可以匹配一个长度的内容
范例：查询出所有雇员姓名中第二个字符包含“M”的雇员
在LIKE中如果没有关键字表示查询全部
查询名字中带有“M”的雇员
在oracle中不等号的用法可以有两种形式“<>”和“!=”
范例：查询雇员编号不是7369的雇员信息
6. 对结果集排序
1.排序的语法
语法：SELECT * |列名 FROM 表名 {WEHRE 查询条件} ORDER BY 列名1 ASC|DESC，列名2...ASC|DESC
范例：查询雇员的工资从低到高
分析：ORDER BY 列名 默认的排序规则是升序排列，可以不指定ASC，如果按着降序排列必须指定DESC
如果存在多个排序字段可以用逗号分隔
2.排序中的空值问题
排序 order by 经验：当排序时存在null时就会产生问题 nulls first,nulls last
--查询雇员的工资从低到高
select * from emp order by sal nulls first;
select * from emp order by sal desc nulls last ;
13.函数
1.字符函数
接收字符输入返回字符或者数值，dual是伪表
1. 把小写的字符转换成大小的字符
upper('smith')
2. 把大写字符变成小写字符
lower('SMITH')
3. 把首字符大写
initcap('smith')
4. 字符串的连接可以使用concat可以使用“||”建议使用“||”
concat('hello','world')
5. 字符串的截取，使用substr，第一个参数是源字符串，第二个参数是开始索引，第三个参数长度，开始的索引使用1和0效果相同
substr('hello',3)
6. 获取字符串的长度
length('hello')
7. 字符串替换，第一个参数是源字符串，第二个参数被替换的字符串，第三个是替换字符串
replace('hello','l','x')
2. 数值函数
1. 四舍五入函数：ROUND()
默认情况下ROUND四舍五入取整，可以自己指定保留的位数。
2. 取整：TRUNC()，默认全部去掉小数，也可以指定保留的位数
3. 取余数MOD()
3.日期函数
Oracle中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律
日期 – 数字 = 日期
日期 + 数字 = 日期
日期 – 日期 = 数字
1. 范例：查询雇员的进入公司的周数。
分析：查询雇员进入公司的天数(sysdate – 入职日期)/7就是周数
2. 获得两个时间段中的月数：MONTHS_BETWEEN()
范例：查询所有雇员进入公司的月数
3. 获得几个月后的日期：ADD_MONTHS()
范例：求出三个月后的日期
4.转换函数
1. TO_CHAR:字符串转换函数
范例：查询所有的雇员将将年月日分开，此时可以使用TO_CHAR函数来拆分
拆分时需要使用通配符
年：y,年是四位使用yyyy
月：m,月是两位使用mm
日：d,日是两位使用dd
在结果中10以下的月前面被被补了前导零，可以使用fm去掉前导零
TO_CHAR还可以给数字做格式化
范例：把雇员的工资按三位用“,”分隔，在oracle中“9”代表一位数字
如果在钱的前面加上国家的符号可以使用“$”代表是美元，如果要使用本地的钱的单位使用“L”
2. TO_NUMBER:数值转换函数
TO_NUMBER可以把字符串转换成数值
3. TO_DATE:日期转换函数
TO_DATE可以把字符串的数据转换成日期类型
5.通用函数
1．空值处理nvl
范例：查询所有的雇员的年薪
我们发现很多员工的年薪是空的，原因是很多员工的奖金是null，null和任何数值计算都是null，这时我们可以使用nvl来处理。
2.Decode函数
该函数类似if....else if...esle
语法：DECODE(col/expression,[search1,result1],[search2,result2]....[default])
Col/expression:列名或表达式
Search1，search2...:用于比较的条件
Result1,result2...:返回值
如果col/expression和Searchi匹配就返回resulti,否则返回default的默认值
3.case when
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
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 emp t
6.聚合函数
1.统计记录数count()
范例：查询出所有员工的记录数
不建议使用count(*)，可以使用一个具体的列以免影响性能。
2.最小值查询min()
范例：查询出来员工最低工资
3.最大值查询max()
范例：查询出员工的最高工资
4.查询平均值avg()
范例：查询出员工的平均工资
5.求和函数sum()
范例：查询出20号部门的员工的工资总和
14.集合操作
1.并集
UNION
UNION ALL
2.交集
INTERSECT
3.差集
MINUS
