Oracle数据库知识要点
一、卸载安装（来自百度经验）
完全卸载：
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。1. 停止相关服务
2. 运行Universal Installer，卸载产品
3. 清理注册表
4. 重启电脑，删除目录（Oracle文件夹和app文件夹）
安装：
1. 运行setup.exe
2.取消勾选“接收安全更新”选项
3. 选择创建和配置数据库
4. 桌面类
5. 企业版
6. 使用utf-8字符集
7. 口令管理，将Scott解锁，设置密码tiger
打开sql plus用设置好的账户登录测试一下
二、配置
1. 确保服务（service和listener）开启
2. 打开Net Manager配置listener和Oracle主目录
3. 打开net configuration assistant配置监听程序和本地网络服务名配置
三、配置PLSQL
1. tools-->preferences Oracle Home:Oracle主目录
2. 查看主目录/network/admin/下是否有tnsnames.ora文件和listener.ora
问题汇总：TNS无法解析制定的连接符标识https://www.cnblogs.com/justlove/p/8252779.html
可以试着把主机名改为本计算机名
四、SQL数据库用户操作语句
新建用户：create user [username] identified by [password]
分配表空间：alter user [username] default tablespace users temporary tablespace temp profile default
新建用户同时分配表空间：create user zhangsan identified by 123456 default tablespace users temporary tablespace temp profile default
赋予权限：grant connect to zhangsan; grant resource to zhangsan; 给查询权限：grant select on emp to zhangsan;
一次赋予多种权限用逗号隔开，如： grant connect,resource to zhangsan
删除用户：drop user zhangsan
去除权限：revoke connect from zhangsan
修改用户密码：alter user zhangsan identified by newpassword
锁定用户：alter user zhangsan account lock
五、Oracle中的数据类型
number(10)表示数字类型，长度为10
number(5,2)表示总长度为5，小数位占其中的2位
char(10)字符型，最多放2000个字符
varchar()或者varchar2()可变长度字符，最多放4000个字符
date日期类型，系统默认日期格式：2-3月-2019
timestamp日期精确到毫秒
blob大数据，存储二进制数据，最大4G
六、SQL表操作语句
创建表：
create table student(
sid number(10),
sname varchar(20),
cid number(10),
......
)
修改表：
追加列：alter table student add(sex varchar(5))
修改列：alter table student modify(sname varchar(15))
删除列：alter table student drop column sex
修改列名：alter table student rename column sex to gender
修改表名：rename student to students
删除表：drop table student
约束 分为列级约束和表级约束
种类：主键primary key、唯一unique、非空not null、外键foreign key references、检查check
添加列级约束：
create table student(
sid number(10) primary key,
sname varchar(20) not null,
cid number(10) references class,
......
)
添加表级约束：
create table student(
sid number(10) ,
sname varchar(20) ,
cid number(10) ,
......
constraint student_sid_pk primary key(sid),
constraint student_cid_fk foreign key(cid) references class(cid),
......
)
或者alter table add constraint ...
七、数据处理
Crete table 表名
As
Select * from 表名
创建一个表，并将查询出来的数据插入到新的表中
去掉数据复制表结构
Create table 表名
As
Select * from 表名
Where 添加一个没有结果的条件 empno=null
八、查询语句
||连接查询结果
在sql中，所有的空值运算之后都为空
Nvl（comm,0）空值处理
Nvl处理空值 第一个参数为列，第二参数表示为如果列中的值为空，用0来代替
去掉重复 关键字：distinct
在sql语句中，字符、日期都必须用单引号引起来
数值的比较，字符串的比较，日期的比较，between…and….(在两者之间，包含两个边界),like(_,%)(模糊查询，关键字查询),in（匹配查询）
Is null表示空 is not null 表示非空
Between。。and。。格式： select * from 表明 where 列明 between XX and XX
比较符：> < >= <= !=或<> = sql中的赋值符号： := 赋值符号
逻辑运算符： and 与 or或 not非
排序关键字：order by asc 升序 为默认排序可以省略 desc 降序
九、函数
单行函数：只对一行数据影响的函数叫单行函数
单行函数：字符、数值、日期、转换、通用
字符函数：lower 将字符转换为小写；upper 将字符转换为大写
instr(列，str1) 查询字符 str1在列中的字符
--concat 连接 将两个字符连接 类似于||
select concat(ename,job) from emp
--length 返回字符串的长度
select length（ename) from emp
数值函数：
--dual 这个表没有具体的数据，只是在查询的时候，我们查询的格式为 select 列 from 表名，当不需要用到表而用到查询结构的时候用dual占位
Turnc（） 数字截断
round（）四舍五入
mod（） 求余
日期函数： sysdate 分为日期和时间
Months_between 获得两者之间的月份数
--获得1987/4/19到1981/2/20这个日期间的月份差
select months_between('19-4月-1987','19-4月-1981') from dual
add_months 向指定的日期中添加若干月份
last_day 获得本月的最后一天
round （日期，年/月/日） 日期同样有四舍五入
trunc 日期的截断
转换函数： 分为两种形式：隐式转换 显示转换
隐式转换：自动类型转换
显示转换：对数据用方法进行转换
To_char 转换为字符
To_number 转换为数字
To_date 转换为日期
To_char(date\number,’字符格式’)
将日期转换为字符串 to_char(sysdate,’yyyy-mm-dd’)；
将数字转换为字符串 to_char(sal,’00000.00或者9999.99’)；
格式中的9的位数，一定要大于等于要转换的数字的位数
如果是货币可以在前面加上$或者L（本地货币）为字符串
To_date(‘字符’,‘日期格式’)
yyyy 代表年 mm代表月 dd代表日 hh时 mi分 ss秒
--插入一条数据到数据库表emp 将日期转换为指定格式
insert into emp values(7856,'lisi','SALESMAN',7698,to_date('1982-12-12','yyyy-mm-dd'),6000,200,10)
To_number(‘字符’,’数字格式’)
--将￥1234.55转换为数字
select to_number('￥1234.55','L999999999.000') from dual
如果字符含有货币符号，那么格式中也必须有货币符号
通用函数：
Nvl（参数1，参数2）处理空值
Nvl2（参数1，参数2，参数3） 参数1列中的数据，如果不为空执行参数2，如果为空执行参数3
Case 参数 when 列值 then 返回数据
[when 列值2 then 返回数据2
。。。。。]
Else 返回数据
End [别名]
通过每一列查出的值作为参数匹配when后面的值，入果匹配上了则返回then后面的值，如果都没匹配上 则返回else中的值，整个函数只是一列
Decode（参数,列值1，返回值1，列值2，返回值2，列值3，返回值3，返回值）
分组函数：min、max、avg、sum、count
十、高级查询
分组查询：通过关键字group by分组的查询
规则：select中出现的非组函数列，在group by 中必须出现
group by 中出现的列，select中可以没有
order by 放在最后排序
having：针对已分组的数据再次过滤，可以使用组函数，而where不能！
多表查询：两张表及两张表以上的查询叫多表查询。
内连接：inner join 效率高于等值查询
外连接：outer join 分为左外连接和右外连接查询。
满连接：full outer join
子查询：查询的嵌套，用括号扣起来的查询语句。分为单行子查询和多行子查询。
in：匹配任意一个结果
any：用于否定？（有点懵逼）
all：匹配所有？（有点懵逼）
合并查询：union并集、union all不取消重复行、intersect交集、minus差集
分页查询：
select * from emp
delete from emp where empno=7999
--在Oracle查询中系统会为每张表提供一个rownum的伪列，rownum是一个永远从1到最大条数的有序排列
--rownum只能从1开始，所以如果数据不从一开始，需要先将rownum查询出来成为一个独自列
--如果分页查询的时候需要排序，一定要在查询rownum之前排序
select *
from (select e.*,rownum r from (
select * from emp order by sal
) e
where rownum<=3*5)
where r>(3-1)*5
此处查询第三页数据，五条数据为一页
十一、事务管理
隐式提交：DDL语句（操作表结构）自动保存
显式提交：commit手动提交
隐式回滚：异常关闭数据库，自动回滚
显示回滚：rollback 回滚到前一个提交点
savepoint 创建保存点
rollback to 保存点名字
十二、视图
虚表，数据来源于表，一般用于查看
create or replace view as...
十三、序列
主要用于提供主键值
create sequence 序列名
[Increment by n] --序列的步长，默认值为1
[start with n] --序列初始位置，不能小于最小值
[minvalue n] --最小值
[maxvalue n] --最大值
[cycle/nocycle] --序列是否可以循环再取
[cache n] --序列的缓存
十四、触发
create trigger 触发器名称
--序列：可以供多个用户来产生唯一数值的数据库对象，提供有规律的数值
create sequence emp2_seq
increment by 1 --设计序列的步长
minvalue 1 --最小值
maxvalue 9999999 ---最大值
start with 1 --起始值
nocycle --是否循环
cache 20 --缓冲 一般设置为20
--使用序列
--获取序列的下一个值 通过 序列名.nextval
insert into emp2 values(null,'MORRIS','CLREK',7499,'10-2月-1981',1100,50,10)
select * from emp2
--获取序列的当前值
select emp2_seq.currval from dual
--触发：在执行某一动作的时候，触发其他动作的执行
create or replace trigger emp2_trg --创建触发
before insert on emp2 --在emp2表插入之前触发
for each row --循环每一行
declare
begin
select emp2_seq.nextval into:new.empno from dual; --查询序列中的下一个值，into赋值给新的主键
end emp2_trg;
十五、索引
--创建索引
create index ename_index on emp(ename)
--删除索引
drop index ename_index
十六、PLSQL基础
赋值符号： ：=
引用类型： V_name table_name.column_name%type
声明式记录类型： Type record_name is record(
v_name type(number),
......
);
引用式记录类型：v_emp emp%rowtype;
选择结构：
if...then...
elsif...then...
else....
end if;
case结构：
case ... when...then ...
when...then...
else...
end case;
循环结构：
loop...exit when...end loop;
while... loop...end loop;
for v_i (计数器) in [reverse] 下限...上限 loop...end loop;
goto顺序结构：
通过标签跳转，一般用于结束循环
注意：标签不能紧邻end;
十七、游标
显式游标：
- 声明游标：cursor 名字（参数...） is 查询语句（where条件）；
- 打开游标：open 名字（参数...）；
- 提取游标数据：fetch 名字 into 记录类型变量/变量列表；
- 关闭游标：close 名字；
例子： declare
cursor emp_cur is select * from emp;
vemp emp%rowtype;
begin
open emp_cur;
fetch emp_cur into vemp;
while emp_cur%found loop
dbms_output.put_line(vemp.empno||" "||vemp.ename);
fetch emp_cur into vemp;
end loop;
close emp_cur;
end;
带返回值的游标：
带参带返回值：
declare
cursor emp_cur(vjob varchar2)
return emp%rowtype --设置返回值类型，用于规定select查询结果
is select * from emp
where job=vjob;
vemp emp%rowtype;
begin
open emp_cur('CLERK');
loop
fetch emp_cur into vemp;
exit when emp_cur%notfound;
if vemp.sal <1200 then
update emp set sal=sal+50 where empno = vemp.empno;
end if;
end loop;
end;
FOR循环游标：
for 接收变量 in 游标名字（参数...） loop
..........
end loop;
接收变量不需要声明！不需要打开游标！
declare
cursor emp_cur(vdname varchar2) is select * from emp where deptno =
(select deptno from dept where dname=vdname);
begin
-- open emp_cur('SALES'); 隐含打开游标啊
for vemp in emp_cur('SALES') loop
--隐含提取游标
dbms_output.put_line('员工编号为：'||vemp.empno||'，姓名为：'||vemp.ename||'，职位为：'||vemp.job||'，工资为：'||vemp.sal);
--隐含关闭游标
end loop;
end;
begin
for vemp in (select * from emp) loop
dbms_output.put_line('员工编号为：'||vemp.empno||'，姓名为：'||vemp.ename||'，职位为：'||vemp.job||'，工资为：'||vemp.sal);
end loop;
end;
隐式游标：
begin
update emp set comm=500 where empno=7499;
-- if sql%found then
--DBMS_OUTPUT.PUT_LINE('更新数据成功！');
--end if;
if sql%rowcount>0 then
DBMS_OUTPUT.PUT_LINE('更新数据'||sql%rowcount||'条成功！');
end if;
end;
游标变量：
定义：type 类型名 is ref cursor [返回类型]
声明：变量名 类型名
打开：open...
提取：fetch ...into...
关闭：close...
十八、异常处理
分为：系统异常和手动异常
手动异常：
declare
too_data_nofind exception; --声明异常 关键字exception
begin
update emp set sal=sal+1 where deptno=50;--一个没有不能找到的条件
if sql%notfound then
raise too_data_nofind;--抛出异常，关键字raise
end if;
--抛出异常后代码不会被执行
dbms_output.put_line('今天天气不错，适合游泳！');
exception
when too_data_nofind then --匹配异常
dbms_output.put_line('找不到此部门中的人！！');
end;
十九、存储过程及函数
区别就是存储函数有返回值。
create [or replace] procedure/function 名字(参数...)
[return...]
as -- 相当于declare
begin
...........
[return ...]
end 名字;
执行存储过程：exec 名字；
或者begin 名字 end;
参数有in、out、in out之分。
