Oracle
什么是oracle?
Oracle公司，以数据库起家的，收购了sun【java mysql数据库】
Oracle数据库是其中的产品之一。也是第一个提出sql命令的数据库。
Oracle提供了数据的解决方案。 解决十万百万级数据
而Windows 系统无法处理庞大的数据在企业中 一般把oracle服务器装在linux,unix;
【装window上 一辆跑车开在田野上】
如何安装
1. 登录网站下载
http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html
2. 找到需要下载的文件
win32_11gR2_database_1of2.zip (1,625,721,289bytes)
win32_11gR2_database_2of2.zip (631,934,821 bytes)
3. 将两个压缩文件同时解压到以文件夹【文件所在的位置不要出现空格/中文/特殊符号】
4. 在文件夹中找到 setup.exe 双击安装下一步
5. 最好不要安装在系统盘
如何卸载/重建库
安装的文件目录 database configuration assistant【安装/卸载 数据库】
Oracle11g 简介
9i internet互联网的崛起
10g 11g g代表grid【网格】 代表底层使用网格计算
12c c代表cloud 云
Oracle是企业级数据的解决方案 一般都是要钱的
oracle提供了学习版本
Oracle有自己的一套证书
1. OCA 专员 基本的sql,特性，安全性
2. OCP 专家 设计，优化，部署，集群
3. OCM 大师 oracle的设计和开发
如何使用？
开启服务
计算机—管理—服务和应用—服务
先开启oracleOrabd11g_home1TNSListener 【oracle监听服务】
在开启 oracleServiceORCL
【ORCL 全局数据区的唯一标识（区分不同的数据库的）】
【ORCL 全局数据库额实例应用
Oracle的组成；
Oracle数据库；一系列的物理文件
Oracle实例：后台的进程
通常情况下一个实例对应一个数据库】
服务器/数据库打开后怎么连接的
本地查看配置信息
客户端配置信息：D:\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
服务器端配置信息：D:\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
如何配置服务名
Net Manager—本地—服务名称—点击+--自己取一个网络服务名---TCP协议---
主机名【localhost/ip地址】---服务器名【全局数据库名】---测试成功了【保存网络配置】
登录方式DOS界面
登陆
Sqlplus
用户名
密码
Sqlplus 用户名/密码
Sqlplus 用户名/密码@orlc
修改密码
用户名 sqlplus/as sysdba
密码 回车
alter user sys identified by 新密码
alter user system identified by 新密码
alter user scott identified by 新密码
解锁【安装数据库后忘记给测试用户解锁了 只能在sys/system账户下操作】
Alter user scott【需要解锁的用户】 account unlock;
切换用户
Conn 用户名/密码
Conn 用户名/密码@全局数据库名
查询当前登录用户的全局数据库名【sys/system 用户下才有权限访问】
Select name from v$database;
查询当前登录用户的全局数据实例名字【sys/system 用户下才有权限访问】
Select instance_name from v$instance
Sys/system用户【scott测试账户】
Sys system
地位 oracle的一个超级用户 oracle默认的系统管理员
作用 维护系统信息和管理实例 管理oracle数据库用户/权限/存储
登录身份 只能以sysdba/sysoper角色登录 可以以Normal【普通】方式登录
表空间
表空间的概念
最大的逻辑单位【不是真正物理存在的数据，就像我们的文件夹】
最小的逻辑单位就是表
一个表空间只能属于一个数据库，所有的数据对象都可以存放在表空间中，主要存放表..
表空间的作用
方便存储管理
提高i/o性能
备份和还原
表空间在oracle的分类/系统自带的表空间
永久性【需要永久保存的数据】
System:存放系统级的数据
Sysaux:存放统计数据
Users:用户默认的表空间【以后的开发中 不指定表空间，所建的表都存放在users表空间】
临时性【不需要长久保存的数据 比如说根据条件查询的数据放在临时表空间， 用完删掉】
Temp
撤销表空间【需要用到事务的数据存放的空间 比如转账】
undo
查看当前数据库下的表空间有哪些【sys/system权限才能查看】
Select * from v$tablespace;
查看当前数据库下的表空间对应的路径【sys/system权限才能查看】
Select file_name,tablespace_name fromdba_data_files;
创建表空间并设置其自增长 【edit以记事本的形式返回所写的命令 修改完保存 输入/执行命令】
create tablespace XXX
datafile ‘d:\XXX01.DBF’
size 100M
autoextend on next 10M maxsize unlimited
查看表空间是否为自增长
Select tablespace_name,autoextensible from dba_data_files;
修改表空间的自增长
Alter database datafile ‘d:\XXX01.dbf’ autoextend on next 20M maxsize unlimited;
删除表空间
drop tablespace XXX including contents anddatafiles;
创建用户
create user XXXXXX ---创建用户
identified by XXXX ---设置密码
default tablespace XXX ---指定默认的表空间
查看该数据库下的所有用户
Select username from dba_users;
权限
Oracle权限指特定类型的sql,命令，访问对象的权利；
Oracle分为2中权限
系统权限
用户执行数据库的操作如创建表。视图。。。。。
对象权限
操作数据库对象，操作表数据…..
为了简化权限管理引入了角色概念
常用的系统角色
connect:临时用户【登录数据库】
resource:正式用户【创建表空间，表，对表数据的操作】
dba：数据库管理员，拥有最高的权限
授权 grant connect,resource to 用户名;
撤权 revoke connect,resource from 用户名;
给用户指定/更换表空间
Alter user 用户名 default tablespace 表空间
删除用户
drop user 用户名 cascade
用户/表/表空间
用户=商家
表=商品
表空间=仓库
1. 一个商家可以有多个商品，一个商品只能属于一个商家【system/sys除外】
2. 一个商品可以放到仓库A，也可以放到仓库B，但是不能同时存在A和B
3. 仓库不属于任何商家，但是商家可以使用
4. 商家都有个默认仓库如果不指定具体的仓库，商品放置在默认的仓库【users】
表
常用的oracle数据类型
字符类型 char 固定长度的字符串 如：char(1) 【数据库字符集存储】
varchar2 可变长度的字符串 如：varchar2(10)
nchar 固定长度的字符串如：nchar(1) 【Unicode字符集类型】
nvarchar2 可变长度的字符串 如：nvarchar2(10)
long 可变长度的字符串 最大长度2GB
数值类型 number 可以描述整数/浮点数
如number 无限大的数值
number(p,s) p有效数字 s精确到小数点几位
number(5,3) 如：99.999
number(3,-2) 900
日期类型 date 日期和数间数据 如 2017/10/9 15/25/52
timestamp 秒值精确到小数点后6位
建表
create table teacher(
tno number(4),
tname varchar2(8),
tid char(18),
gender char(2),
birthdate date
);建表顺便添加约束
create table teacher(
tno number(4) primary key,
tname varchar2(8) unique,
tid char(18),
gender char(2) check(gender in(‘’男,’女’)),
birthdate date not null
);查看表结构
desc tracher
添加列alter table teacher add(sal number(3),age number(2));
修改列alter table teacher modify(sal number(4));
删除列alter table teacher drop column sal;添加约束
Oracle 把约束分为4中【域完整性 实体完整性 引用完整性 自定义完整性】
非空约束
alter table teacher add constraint CK_NULL ckeck(birthdate is not null);
主键约束【唯一且不能为空】
alter table teacher add constraint PK_TNOprimary key (tno);
唯一约束【唯一可以为空只能有一个空】
alter table teacher add constraint UK_TNAMEunique(tname);
比较约束
alter table teacher add constraint CK_GENDERcheck (gender in(‘男’,’女’));
alter table teacher add constraint CK_AGEcheck(age between 0 and 99);
外键约束
alter table teacher add constraint FK_deptno foreign key (deptno) references dept(deptno);
注意点：1.主表的外键只能引用从表的的主键；
2.在有约束的情况下，必须先删除主表的引用数据再删除从表；
3.主外建数据格式必须一致；
删除约束
alter table teacher drop constraint CK_NULL;
删除表【都不介意使用】
drop table teacher 删除表【毛都不剩】
truncate table teacher 格式化表【保留表结构/约束/触发器/索引】
备份
把数据复制到储存设备的过程
恢复
把数据由存在故障的状态转换为无故障的状态
为什么要备份
1. 故障 2.迁移 3.误操作 4.数据库的更新/程序的更新
导入导出数据库对象的四种模式
完全数据库 用户 表 表空间
【在dos下导入导出
Oracle11g创建一个表 未对这个表做任何的数据操作 就 无法导出
】
1. 将数据库ORCL完全导出，用户名system 密码xxxx 导出到 d:\xxxx.dmp
exp system/aaa@qq.com file=d:\xxxx.dmpfull=y 【full=y 全库导出】
2. 将数据库中的xxxxxx用户下的表导出【owner=(xxxxxx,scott)可导多用户】
exp system/aaa@qq.com file=d:\xxxx.dmp owner=(xxxxxx)
3. 将数据库中的student表导出 【tables=(student,teacher) 可导多表】
exp 用户名/密码@orcl file=d:\xxxx.dmp tables=(student)
4. 将数据库中的student表中的stu_name 以‘马’开头的数据导出
exp 用户名/密码@orcl file=d:\xxxx:dmptables=(student) query=\”where stu_name like ‘马%’\”
where stu_age=18
导入
1. 将d:\niit中的数据导入到orcl数据库中
imp system/密码@orcl file=d:\xxxx.dmp 【如果表已存在会报错】
在后面加上ignore=y 【碰到相同结构的表不会创建，会导入数据进去】
一般情况下在导入之前 会将相同的表删除
2．将d:\xxxx.dmp 中的表student导入到用户下
imp 用户名/密码@orclfile=d:\xxxx.dmp tables=(student)
Sql语句
分为4大类
数据定义语言（DDL）
Create
Alter
Drop
Truncate
数据操作语言（DML）
select 查询
insert 增加
update 修改
delete 删除
事物控制语言（TCL）
commit 提交
savepoint
rollback 回滚
数据对象控制语言(DCL)
grant
revoke
【所有的DML语言都要遵循事务处理】
查询学生表的所有数据
Select * from student
增加
insert into
student(stu_id,stu_name,stu_age,stu_sex,stu_classname)values(1,'a',18,'男','xxx')
insertinto student(stu_id,stu_name,stu_age,stu_sex,stu_classname)
select 3,'b',18,'男','xxx' from dual union
select 4,'c',18,'男','xxx' from dual union
select 5,'d',18,'男','xxx' from dual序列
创建序列create sequence sq_stuid start with 6 increment by 1
**序列select sq_stuid.nextval from dual
查看序列select sq_stuid.currval from dual
使用序列
insert into student(stu_id,stu_name,stu_age,stu_sex,stu_classname) values(sq_stuid.nextval,'a',18,'男','xxx')修改
update student set stu_name='老大' wherestu_id=1
update student set stu_name='小学生' wherestu_age<13
update student set stu_name='大学生' wherestu_age between 17 and 20
update student set stu_classname='好学校' where stu_sex='女' andstu_name='大学生'
update student set stu_name='女大学生',stu_age=stu_age+1where stu_classname='好学校'
update student set stu_age=stu_age+1 wherestu_sex='男' or stu_sex='女'
update student set stu_classname='尊老爱幼学校' wherestu_age in(3,89) 删除
delete from student where stu_sex='男' orstu_sex='女'
查询【占DML的90%】
--查询全部
select* from student--查询单个或多个
select stu_name from student
select tu_name,stu_classname from student--as取别名
select stu_name as 姓名,stu_classnameas 班级 from student
select stu_name 姓名,stu_classname班级 from student
select student.stu_name 姓名,student.stu_classname班级 from student
select stu.stu_name 姓名,stu.stu_classname班级 from student stu--distinct
select distinct stu_classname as 不同的班级 from student--where
selectstu_name from student where stu_id=1
selectstu_id,stu_name from student where stu_sex='男' and stu_classname='xxx'
select* from student where stu_sex='男' orstu_sex='女'--is null /is not null
select stu_name from student where stu_classname is null
select stu_name from student where stu_age is not null--between/ not between
select* from student where stu_age between 12 and 20
select* from student where stu_age not between 12 and 20--in/ ont in
select* from student where stu_age in(16,18)
select* from student where stu_age not in(16,18)--like模糊匹配 【%匹配0个或多个字符】【_匹配一个字符】
select* from student where stu_name like '刘%'
select* from student where stu_name like '刘_'
select* from student where stu_name like '%天%'
select* from student where stu_name like '%天'--order by 排序[asc升序默认的 desc降序]
select* from student order by stu_id asc
select* from student order by stu_id desc
select* from student order by stu_age desc,stu_id desc--给男生的学号做降序
select* from student where stu_sex='男' order bystu_id desc--聚合函数
--计算总数
select count(stu_id) as 总人数 from student
select count(stu_classname) as 总人数 from student--计算总和
select sum(stu_age) as 年龄总和 from student--计算最大值
select max(stu_age) as 年龄最大的 from student--计算最小值
select min(stu_age) as 年龄最小的 from student--计算平均值
select avg(stu_age) as 平均年龄 from student--分组 group by
select count(stu_sex) as 总数,stu_sex as性别 from student group by stu_sex
select count(stu_classname) as 班级人数,stu_classnameas 班级 from student group by stu_classname--分组之后的再一次筛选 having
select count(stu_classname) as 班级人数,stu_classname as 班级 from student group by stu_classname
having count(stu_classname)>2
select count(stu_sex) as 总数,stu_sex as 性别 from student group by stu_sex
having count(stu_sex)>2select 6将最终的数据显示
from 1拿到所有的表数据
where 2删选条件
group by 3分组
having 4对分组数据的筛选
order by 5对分组数据的筛选后的数据进行排序
子查询
Teacher表 tid tname tage tsex tsal tcomm tdeptname以一个查询的结果作为另一种查询的条件
--查询工资最高的那个老师的姓名
select tname from teacher wheretsal=(select max(tsal) from teacher)
--查询年龄小于平均年龄的老师列表
select * from teacher where tage<(selectavg(tage) from teacher)
--给工资低于平均工资的老师奖金加1块
update teacher set tcomm=tcomm+1 wheretsal<(select avg(tsal) from teacher)函数
函数带有一个或多个参数 并返回一个值
Sql函数的分类：单行函数，聚合函数，分析函数
单行函数对于表中查询的每一行只返回一个值
可以出现在select中/where中
单行函数的大致划分为
数字函数
--round 四舍五入
select tsal 原工资,round(tsal) 四舍五入的工资 from teacher 四舍五入的整数
select tsal 原工资,round(tsal,1) 四舍五入的工资 from teacher 四舍五入精确到小数点1位
select tsal 原工资,round(tsal,-1) 四舍五入的工资 from teacher 四舍五入精确到十位
--length
select tname 姓名,length(tname) 长度 from teacher
select tid 编号,length(tid) 长度 from teacher字符函数
--截取 substr （下标1开始）
select tname 姓名,substr(tname,2) 截取的姓名 from teacher 从第二位完后截取所有
select tname 姓名,substr(tname,2,2) 截取的姓名 from teacher 截取从二位开始的后两位
select tname 姓名,substr(tname,-3) 截取的姓名 from teacher 截取倒数第三位后的所有
select tname 姓名,substr(tname,-3,2)截取的姓名 from teacher 截取倒数第三位后的两位日期函数
--日期函数
--获取当前系统时间 sysdate
select sysdate from dual--获取明天的当前时间
select sysdate+1 from dual--获取当前时间4个小时后的时间
select sysdate+1/6 from dual转换函数
--将字符串转换为日期格式 to_date
select to_date('19921112','yyyy/mm/dd')from dual--将数值类型转换成字符串 to_char
select to_char(123456,'999,999') from dual----- ||字符串的拼接
select '$'||to_char(123456,'999,999') from dual--将字符串类型转换成数值 to_number
select to_number('123456') from dual--将当前时间转成字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual聚合函数
基于一注数据来返回的结果/返回一个值
聚合函数 count sum avg max min
面试题【count(*) count(主键) count(1)/count(0) 统计条数哪个执行效率快】
group by
将信息划分为更小的组
每组返回针对改组的单个结果
having
用于对group by 字句的检索条件
分析函数
根据一组进行计算的聚合值
用于计算排名等
分析函数为每组返回多个行
Row_number 顺序排序 1 2 3 4 5
Rank 跳跃排序 1 2 2 4 5
Dense_rank 连续排序 1 2 2 3 4
--查看每位老师的工资排名（从大到小）
select tname 姓名,tsal 工资,
row_number() over(order by tsal desc) as row的排名,
rank() over(order by tsal desc) as rank的排名,
dense_rank() over(order by tsal desc) as dense的排名
from teacher联合查询
--联合查询
--union 并集 并集去掉重复重复部分
select * from teacher where tsal>600 union select* from teacher where tsal>5000--union all 并集 并集不会去掉重复部分
select * from teacher where tsal>600 union all select * from teacher wheretsal>5000--intersect 交际
select * from teacher where tsal>=666.66 intersect select * from teacher where tsal<=666.66--minus 补集
select * from teacher where tsal>600 minus select* from teacher where tsal>5000分页
什么是oracle的伪列
伪列就像表里面的普通列，但是并没有真正的存储在表中
伪列只能查看不能增删改
常用的伪列有哪些？【select rowid,rownum,teacher.*from teacher】
rowid
rowid是表中行的存储地址，该地址 指向当前行的数据。可以快速的查询数据
rownum
rownum是查询返回结果集中的序号【从1始的】，可以使用他来限制返回的行数
--获取教师表中前3名老师
select * from teacher where rownum<=3--获取教师表中3-6名的老师 [不行的]
select * from teacher where rownum>=3and rownum<=6--rownum并不是真正存在的列
--rownum是由返回结果集来产生的 没有结果就没有rownum
--rownum是从1开始的
--改造
select * from
(select rownum rn,teacher.* from teacherwhere rownum<=6) t
where t.rn>=3------------------------------
--分页的思路
--第一层 加限制条件
--第二层 给结果集过滤最大的范围
--第三层 在最大氛围的基础上 过滤出最小范围
--获取教师表中工资最高的前2人
select * from teacher where rownum<3order by tsal desc [错]
select * from
(select teacher.* from teacher order bytsal desc)
where rownum<3--获取教师表中工资排名第3的教师信息
select * from (select * from teacher orderby tsal desc) where rownum=3[错]
select * from
(select rownum rn,t.* from
(select rownum,teacher.* from teacher orderby tsal desc) t) th
where th.rn=3;--获取工资从高到低的 第2-4条记录
select * from teacher where rownum >=2and rownum<=4 order by tsal desc[错]
select * from
(select rownum rn,s.* from
(select * from teacher order by tsal desc)s
where rownum<=4) t
where t.rn>=2为什么使用多表
更好的维护，和扩展
主表
Teacher表 tid tname tage tsex tsal tcomm tld deptno
Dept表 deptno deptname
外键约束
注意点：1主表的外键只能引用从表的主键
alter table TEACHER
add constraint FK_deptno foreignkey (DEPTNO)
references dept (DEPTNO);2再有约束的情况下删除必须先删主表引用的数据再删除从表
3.主外键的数据类型必须一致
多表查询
多表的连接
内连接
等值连接
Where
select t.tname,d.deptname from teacher t,deptd where t.deptno=d.deptnojoin
select t.tname,d.deptname from teacher tinner join dept d on t.deptno=d.deptno
--查询保洁部门的老师
selectt.tname,d.deptname from teacher t inner join dept d on t.deptno=d.deptno whered.deptname='保洁'自然连接【两个表的连接值得列必须相同名】
select * fromteacher natural join dept
外连接
左外连接【以左边的那张表为主表拿右边的表填充】
查询每个老师所属的部门
select t.tname,d.deptname from teacher t left join dept d on t.deptno=d.deptno右外连接【以右边的那张表为主表拿左边的表填充】
查询每个部门下所有的老师
selectt.tname,d.deptname from teacher t right join dept d on t.deptno=d.deptno自连接【一定要区分表名】
查询每个老师对应的领导
select t1.tname,t2.tname 领导 fromteacher t1 left join teacher t2 on t1.tld=t2.tid交叉连接【是所有连接的基础】
select t.*,d.* from teacher t cross joindept d
【面试 房屋 街道 区县 如何设计表】
多表子查询
--查询教学部的老师姓名
select tname from teacher where deptno=
(select deptno from dept where deptname='教学')--查询所有老师对应的部门
select t.tname,
(select deptname from dept d whered.deptno=t.deptno)
from teacher t数据库设计
空间【在有效的空间发挥最大的作用】
完整性【数据完整性】
程序开发【好的数据库设计可以减少业务代码】
E-R模型
见图！！
数据库设计的三大范式【针对数据库设计而言的 针对你的业务可以适当地调整】
第一范式：确保列的原子性【每列的数据不可分割】
第二范式：满足第一范式的基础上，每个表只描述一件事情
【除了主键意外的其他列 都依赖于主键则满足第二范式】
如 学号 姓名 课程 成绩 /成绩依赖于课程的
第三范式：满足第二范式的情况下，并且其他列的传递依赖性
如 学号 姓名 出生年份年龄 改成 学号 姓名 出生年份
数据库的设计步骤
1. 收集信息【需求的分析，哪些信息需要通过数据来描述】
2. 标识对象【分析这些数据，组成对应的对象【表】】
3. 标识对象的属性【标的列去描述数据】
4. 标识对象之前的关系【表与表的关系 主外键【开发中不使用外键】】
Sql优化与体系结构
一般的优化【在最短的时间 达到最好的效果】
建议不使用* 代替所有的列名【oracle会一个一个的去匹配 一般查询不会查询全部的】
删除所有的数据用drop/truncate /delete 【truncate 代替delete 有利有弊】
用not exists 代替not in【11g往后已经优化好了】
用 exists 代替 in
用exists 代替 distinct
举例
--查询所有 有部门的老师
--内连接查询
select t.tname,d.deptname from teacher tjoin dept d on (t.deptno=d.deptno)
--in
select t.tname from teacher t wheret.deptno in (select deptno from dept)
--使用exists优化
select t.tname from teacher t where exists
(select 'a' from dept d whered.deptno=t.deptno)
--distinct 去重复
--查询出现在老师表里的不同的部门编号
--distinct
select distinct deptno from teacher wheredeptno is not null
--exists 优化
select d.deptno from dept d where exists
(select 'a' from teacher t where t.deptno=d.deptno)
表连接的优化技巧
驱动表
Form后面靠右的表就是驱动表（两张表都没有索引）
有索引的情况下，没有索引的这张表是驱动表
如：A(10) B(10W)(有索引的)
【说白了驱动表就是 驱动自己去跟别人连接 一般让数据量少的表作为驱动表】
Where 字句的连接顺序
【oracle采用自下而上的顺序解析where字句 where条件是从后往前执行的】
注意：表之间的连接必须写在其他where条件之前
过滤记录越多的条件字句应该放在后面
索引
为什么使用索引
【oracle的存储方式是按行来存储的比如rowid 比如where name=?这是按照列查询的】
逻辑分类 物理分类
单列/或组合索引 分区/非分区索引
唯一/不唯一索引 B树索引
函数索引 正常/反向键索引
位图索引
B树索引（普通索引）
比如：100条记录分成1-50 50-100
唯一索引【唯一键索引】
在你创建唯一约束的时候自动更具你的唯一键创建索引 索引对应你的唯一值
这也就是唯一约束为什么数据不能相同的原因之二【值相同索引根本不知道对应那一条数据】
位图索引
对大量数据中利用的比较少的数据建立的索引
组合索引
多个列组成的索引
函数索引
针对使用函数的列的索引
使用plsql创建索引
编辑表 索引【普通/唯一/位图/反向键】
通过查询语句的解释计划查看【F5】是否使用索引
indexes文件夹查看索引
创建索引的原则
频繁搜索的列
经常用来排序/分组的列
经常连接的列
不适合创建索引的原则
数据量小
仅包含几个不同值得列【大部分数据都是相同的】
数据表操作特别平凡的表，业务特别平凡的【增删改】
【开发中表中导入数据后创建索引否则每次表的插入数据都要更新索引】
【开发中某列的数据低于数据类型总和的15% 建议添加索引】
【并不是索引越多查询越快 索引越多 修改表时 修改的所有也就越多】
表分区
把表中的数据分成若*分，并存在不同的位置。
优点
改善查询的性能
表更容管理
便于备份/恢复
提高了安全性
表分区的分类
范围分区
列值得范围作为划分条件
散列分区
列的hash值自动分配
列表分区
列值明确的指定 如：year=2017
复合分区
多种分区的组合
其他分区
外键分区
使用plsql创建表分区
创建表-----表分区---选类型【range范围 hash散列 list列表】----选择分区的列
给分区取名字----分区以什么值划分【值是汉字加‘’】
按表分区查看数据 select * from 表名 partition(分区名)；
数据库对象
Oracle数据库对象也称之为 模型对象
数据库对象是逻辑机构的集合 最基本的数据库对象是表
【序列 用户 索引 视图 数据库连接 同义词…..】
视图
【一些sql语句查询的结果】
创建视图
create view v_usespage as
select us.* from
(select rownum rn,u.* from
(select * from users order by us_id) u
) us授权某个有用户具有建立视图的权限
grant create any view to laowang
使用视图
select * from v_usespage where rn>=3 and rn<=5视图并不是真正存在的【无法对视图里的数据进行修改】
用途
限制对表中某一些数据的列的访问
隐藏数据的复杂性，简化了程序员的sql，从一个角度提供数据
DML限制
只能对基表操作，【视图无法增删改】
注意权限问题
数据字典
静态的数据字典
dba_*** 所有的用户表表空间的位置 dba_data_file
all_*** 当前用户的所有表
user_*** 当前用户下的所有表 user_tables
当前用户下的表对应的列 user_tab_cols
当前用户下的视图 user_views
动态的数据字典
v$** v$sqlarea 查看执行的性能
v$database 查看当前用户的全局数据库名
v$session 查看当前数据库连接的用户
DBlink
用途
访问远程的数据库
案例
【两个数据库ORCL ORCL2】
【在ORCL库里访问ORCL2数据库下xx1用户下的teacher表】
创建的步骤：
1. 为远程的数据库建立网路服务名
2. 在database links 创建库与库的连接
当前数据库下访问别人的数据库 select * from 表名@随便取
同一数据库 不同用户之间的访问【当前用户xx1访问xx2的teacher表】
select * from xx2.teacher
PL/SQL
什么是pl/sql【它是一门编程语言】
是过程语言（pl）与结构化查询语言（sql）的结合
【与Java逻辑类似】
变量和类型
控制语句
子程序和函数
对象类型/方法
Pl/sql的组成
Pl/sql块是构成plsql程序的基本单元
Pl/sql分成了三个部分 【声明/执行/异常/结束】
语法
declare
声明
begin
执行
exception
异常
end;声明
声明变量和常量
declare
变量名 数据类型【类型的大小】 【:=值】
v_name varchar2(20) :=’jeck’;
v_name varchar2(20) default ‘ jeck’;
c_day constant number(1) :=7; 【常量c开头 常量名后跟constant关键字】
【类型可以直接使用列的数据类型 属性类型赋值（%type %rowtype）】
v_name teacher.tname%type
例：创建两个变量分别存储1号老师的姓名和工资
declare
v_name teacher.tname%type;
v_sal teacher.sal%type;
begin
select t.tname,t.sal intov_name,v_sal from teacher t wheret.tid=1;
dbms_output.put_line(v_name||'-----'||v_sal);
end;控制结构
条件控制 if case
循环控制 loop while for
顺序控制 null goto
条件控制
例题：获取1号老师工资和对应的级别
【级别 4000-8000蓝领 8000-15000白领 15000以上金领 否则就是咸鱼】
declare
--声明变量
v_name teacher.tname%type;
v_sal teacher.sal%type;
v_lv varchar2(10);
begin
--获取第一位老师的工资/姓名
select t.tname,t.sal into v_name,v_sal from teacher t where t.tid=5;
if v_sal>=4000 and v_sal<=8000 then
v_lv:='蓝领';
elsif v_sal>8000 and v_sal<=15000 then
v_lv:='白领';
elsif v_sal>15000 then
v_lv:='金领';
else
v_lv:='咸鱼';
end if;
dbms_output.put_line(v_name||'工资为：'||v_sal||'属于：'||v_lv);
--根据不同的级别输出工资范围
case v_lv
when '蓝领' then
dbms_output.put_line(v_name||'工资范围：4000-8000');
when '白领' then
dbms_output.put_line(v_name||'工资范围：8000-15000');
when '金领' then
dbms_output.put_line(v_name||'工资范围：15000以上');
else
dbms_output.put_line(v_name||'工资范围：4000以下');
end case;
end;循环控制
--插入10条部门信息
declare
--声明变量
v_start dept.deptno%type:=1;
v_end number:=0;
begin
/*
--loop
loop
insert into dept values(sq_dept.nextval,'部门'||v_start);
v_start:=v_start+1;
exit when v_start>10;
end loop;
*/
/*
--while
while v_start<=10 loop
insert into dept values(sq_dept.nextval,'部门'||sq_dept.currval);
v_start:=v_start+1;
end loop;
*/
--for
for v_start in v_end+1..v_end+10 loop
insert into dept values(sq_dept.nextval,'部门'||sq_dept.currval);
dbms_output.put_line(v_start);
end loop;
commit;
end;顺序控制
goto 跳转到标记的位置，继续往下执行
null 本身无任何意义，将执行传递下去
--循环1-10 输出小于5的数
declare
--声明变量
begin
for i in 1..10 loop
if i<5 then
dbms_output.put_line(i||'小于');
goto end_loop; --跳转
end if;
dbms_output.put_line(i);
<<end_loop>> --标记
null;
end loop;
end; 游标
游标是系统为用户开设的一个数据缓冲区，存在sql语句的执行结果，
每个游标都有名字，用户可以通过游标逐一获取里面的值
游标的分类
静态游标：在编译时知道其select语句的游标
动态游标：运行时动态的执行某种查询
使用游标
1. 声明游标：cursor cursor_name is query_sql;
【cursor_name 自定义的游标名 query_sql执行sql语言的结果集】
2. 打开游标：open cursor_name;
3. 提取游标：fetch cursor_name into varible_list;
【varible_list必须与从游标提取结果集的类型相同】
4. 关闭游标：close cursor_name
【关闭游标后 所有的资源释放】
例： 计算各个老师的个人所得税和这个学校老师的所得税之和
【5000-10005% 10000-40000 10% 40000 30%】
--例题： 计算各个老师的个人所得税和这个学校老师的所得税之和
--【5000-1000 5% 10000-4000010% 40000 30%】
declare
cursor cursor_teacher is select tname,sal from teacher; --创建游标
v_teacher_record cursor_teacher%rowtype;--存游标里每行的数据
v_tname teacher.tname%type;--每个老师的姓名
v_sal teacher.sal%type;--每个老师的工资
v_tax number:=0;--每个老师的所得税
v_tax_sum number:=0;--所有老师的所得税之和
begin
for v_teacher_record in cursor_teacher loop --迭代游标里面的数据
v_tax:=0;--清空上面一个人的所得税
v_sal:=v_teacher_record.sal;--获取每一行数据的工资列
v_tname:=v_teacher_record.tname;--获取每一行数据的名字列
if v_sal>=5000 and v_sal<10000 then--判断去区间
v_tax:=(v_sal-5000)*0.05;
elsif v_sal>=10000 and v_sal<40000 then--判断去区间
v_tax:=(v_sal-10000)*0.1+5000*0.05;
elsif v_sal>=40000 then--判断去区间
v_tax:=(v_sal-40000)*0.3+30000*0.1+5000*0.05;
end if;
v_tax_sum:=v_tax_sum+v_tax;--累加个税
dbms_output.put_line(v_tname||'-----'||v_tax); --输出每个老师对应的个税
end loop;
dbms_output.put_line('公司的所有所得税之和为：'||v_tax_sum);--个税之和
end;游标的属性
%found：用于检验游标是否成功，通常在fetch语句前使用。当游标查询出记录时 返回true；
%isopen：判断游标是否处于打开状态。
%notfound：当游标查询记录时，没有记录 返回true；
%rowcount：循环执行游标读取数据时，返回检索出的记录数据行数。
动态sql
什么是静态sql：在编写程序时确定的sql
什么是动态sql：在程序执行时生成的sql
实现动态sql的两种方法
1. 本地动态sql【execute immediate 语句执行】【通过游标】
2. DBMS——sql包【9i】
executeimmediate
语法：execute immediate sql_Str into_list using_list
Sql_str：动态sql的字符串
【 Into_list: 用于接收select 语句选择的记录值】
【Using_list: 用于绑定输入参数的变量/具体的值】
例题：给老师涨100工资。能够更具姓名/性别/部门进行加薪
--例题：给老师涨100工资。能够更具姓名/性别/部门进行加薪
declare
v_sql varchar2(100):='update teacher set sal=sal+1 where 1=1 '; --存sql语句
v_name teacher.tname%type;--姓名
v_sex teacher.sex%type:='男';--性别
v_deptno teacher.deptno%type:=1;--部门编号
begin
if v_deptno is not null then
v_sql:=v_sql||'and deptno='||v_deptno;
end if;
if v_name is not null then
v_sql:=v_sql||' and tname='''||v_name||'''';
end if;
if v_sex is not null then
v_sql:=v_sql||' and sex='''||v_sex||'''';
end if;
execute immediate v_sql;
commit;
end;异常处理
在运行程序是出现的错误
发生异常后，语句停止执行，控制权转移到pl/sql块的异常处理部分
异常分类：1预定义异常 2 用户定义异常
举例：除数为0的异常
declare
begin
update teacher set sal=100/10;
exception
when zero_divide then --【zero_divide 内部定义好的异常类型】
dbms_output.put_line('算数异常 除数不能为0');
when others then --【others 捕获全部异常】
dbms_output.put_line('其他异常');
end;声明异常：
declare
exception_nameexception;
抛出异常：
begin
raise exception_name;
例如：查询某位老师的工资 如果小于0 抛出异常/捕获
declare
v_sal teacher.sal%type;
ex_tracher_sql exception;
begin
select sal into v_sal from teacher where tid=1;
if v_sal<0 then
raise ex_tracher_sql;
end if;
exception
when ex_tracher_sql then
dbms_output.put_line('工资不给 黑心老板');
when others then
dbms_output.put_line('其他异常！');
end;子程序
什么是子程序？
命名的pl/sql块，编译并存储在数据库中的。
子程序的分类
存储过程 --存储执行某些操作的过程
函数【系统函数/自定义函数】 --执行操作并返回结果
创建存储过程的语法
create or replace procedure
pro_name(参数列表) 【pro_name自定义的过程名，参数一般以p_开头】
is
变量的声明
begin
执行部分
exception 【可选】
异常处理部分
end pro_name;例题：创建一个过程，增加老师的信息【身份证号必须为18位 否则抛出异常】
create or replace procedure --创建存储过程
pro_add_teacher(p_name varchar2,p_id char,p_gender char,p_birthdate date,p_sal number)给存储过定义名字设置参数列表
is
ex_id_vaildate exception; --定义异常类型
begin
if length(p_id)!=18 then --判断id 抛出异常
raise ex_id_vaildate;
end if;
insert into teacher(tno,tname,tid,gender,birthdate,sal) --增加操作
values
(sq_teacher.nextval,p_name,p_id,p_gender,p_birthdate,p_sal);
commit; --提交事务
exception --异常处理
when ex_id_vaildate then
dbms_output.put_line('请使用正确的身份证号');
rollback; --回滚
when others then
dbms_output.put_line('其他异常');
rollback;
end pro_add_teacher; --结束存储过程如何返回存储过程的结果
in 接收存储过程的参数 【默认的】
out 用于调用存储过程的返回值
in out用于结果和调用存储过程的返回值
例题：创建一个过程删除老师的信息 参数为编号 返回删除的影响行数
create or replace procedure --创建存储过程
pro_delete_teacher(p_tno in number,r_rwos out number)--给存储过定义名字 设置参数列表
is
begin
delete from teacher t where t.tno=p_tno;--删除操作
r_rows:=sql%rowcount;
commit; --提交事务
end pro_delete_teacher; --结束存储过程动态sql和存储过程的使用：
例题：创建分页查询老师的过程，参数为性别，起始位置，结束为止
返回查询的总行数 返回结果集
create or replace procedure --创建存储过程
pro_qry_teacher(p_gender char,p_startrow number,p_endrow number,r_total out number,r_result out sys_refcursor)
--给存储过定义名字 设置参数列表存储过程除了执行DML还能执行DDL操作
例题：创建一个过程参数为表明（要创建的表）已有的表名（创建好了的里面有数据）
功能：把以有的表结构包括数据复制给宁外一张表
create or replace procedure --创建存储过程
pro_copy_teacher(oldtablename varchar2,newtavlename varchar2)--给存储过定义名字 设置参数列表
is
v_sql varchar2(300); --存放复制表的语句
begin
v_sql:='create table '||newtavlename||' as select * from'||oldtablename;
execute immediate v_sql;
end pro_copy_teacher; --结束存储过程授权复制表 grant create any table to 用户名;
grant execute on pro_name to username 把存储过程给username使用
drop procedure pro_name 删除存储过程
函数
创建函数语法【函数必须有返回值】
create or replace function
fn_name(参数列表) 【fn_name函数名】
return 类型 【类型要兼容函数执行结果的值】
is
声明变量
begin
执行部分
exception
异常
end fn_name;grant execute on fn_name to username 把函数给username使用
drop function fn_name 删除函数
例题：创建函数，参数为身份证，返回值为处理过的身份证号：123456789123456789
1234567891****6789
create or replace function --创建函数
fn_tran_id(p_id char) --自定义函数名 定义参数列表
return char --设置返回类型 【返回类型必须兼容返回值】
is
ex_id_validate exception --定义异常类型
v_result teacher.tid%type; --定义返回值
begin
iflength(p_id)!=18 then
raise ex_id_validate;
endif;
v_result:=substr(p_id,1,10)||'****'||substr(p_id,15,4); --加密
return v_result; --返回值
exception
when ex_id_validate then
dbms_output.put_line('身份证号异常');
when others then
dbms_output.put_line('其他异常');
end fn_tran_id;触发器
如何跟踪并记录所有对老师表数据改变的操作
也就是说：
只要你一改变记录将你的操作记录到日志表中
不需要显示调用的，而是改变的那一瞬间启动的。
触发器的特性
不需要显示的调用，它是由一个事件启动的。
事件包括哪些？
Insert update delete 对表
执行DDL操作
数据库的启动/关闭
创建触发器的语法：
create or replace trigger 创建触发器
tr_name 自定义触发器名
after 指定在什么位置触发【after before instead of】
insert or update or delete 指定触发的事件
on table/view 指定触发的表/视图
for each row 指定触发器的类型 【行级触发】
declare begin plsql块
end tr_name;跟踪并记录 所有对老师表数据改变的操作
create or replace trigger --创建触发器
tr_teacher --自定义触发器名
after --触发器位置
insert or update or delete --触发的事件
on teacher --监控的表
for each row --触发器的类型【DML的行级】
declare
v_opt_type teacher_log.opt_type%type; --日志列的操作类型
v_old_value teacher_log.old_date%type; --改变之前的值
v_tno teacher_log.tno%type; --改变列的主键
begin
if inserting then --判断是增加操作
v_opt_type:=1;
v_tno:=:new.tno; --:new 获取当前增加的这个对象
v_old_value:=',,,,';
els if deleting then --判断是删除操作
v_opt_type:=2;
v_tno:=:old.tno; --:old 获取被执行之后的对象
v_old_value:=:old.tname||','||:old.sal||','||:old.tid||','||:old.gender||','||:old.birthdate;
else --判断是修改操作
v_opt_type:=3;
v_tno:=:old.tno;
v_old_value:=:old.tname||','||:old.sal||','||:old.tid||','||:old.gender||','||:old.birthdate;
end if;
insert into teacher_log(log_id,opt_time,opt_type,old_date,tno) --添加日志
values
(sq_log.nextval,sysdate,v_opt_type,v_old_value,v_tno);
end tr_teacher; --结束上一篇： Oracle数据迁移存储过程
下一篇： Oracle拆列自定义分组查询