Oracle

最新推荐文章于 2022-10-11 13:45:11 发布

那时年少岁月静好

最新推荐文章于 2022-10-11 13:45:11 发布

阅读量376

点赞数

本文链接：https://blog.csdn.net/weixin_44891982/article/details/102614859

版权

Oracle数据库

本地环境连接Oracle数据库

	通过PLSql连接（需要整合instantclient工具使用）

整合步骤：

PLSql中点击Tools，设置Oracle Home和OCI Library
E:\develop\instantclient_12_1(instantclient的路径)
E:\develop\instantclient_12_1\oci.dll(instantclient的路径\oci.dll)

登陆：

Username：system(该用户名为Oracle自带用户)  
Password：安装时设置的  
DataBase：服务器的Ip地址:端口号（Oracle默认端口为1521）/数据库名（默认为orcl） 
Connect as：以什么身份连接数据库 
将Oracle中的tnsnames.ora文件复制到本地，新建环境变量（变量名：TNS_ADMIN。值为tnsnames文件所在的路径）

Oracle数据库的体系结构

数据库：侧重硬件，Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是 Oracle 就只有一个大数据库
实例：侧重进程，一个数据库可以有N个实例   
用户：管理表的基本单位，Mysql管理表的基本单位是数据库
	Mysql可以说当前数据库下有几张表
	Oracle是用户下有几张表
表空间：逻辑单位，当实例比较大时，会把实例分成一些细小的块，块就是表空间，表空间里的内容就是数据文件（dbf，ora）
数据文件：一堆数据文件合到一起就是表空间

创建表空间
要创建表空间，必须使用有超级管理员权限的用户

写sql语句：PLSQL中File下new，Test Window和SQL Window都可以，一般用SQL Window

注释：--

创建表空间

create tablespace 表空间名  
datafile 'c:\itheima.dbf'（指定表空间位置，后缀一定要用dbf，因为是数据文件）  
size 100m（设置表空间初始大小为100m）
autoextend on(表空间大小不够用的时候自动扩展)  
next 10m；（每次扩展10m），点击小齿轮执行

删除表空间

drop tablespace 表空间名;（该操作只是解除占用，还需要手动删除）

创建用户
创建用户后不能立即登陆，因为每个用户显示的表不一样，需要给用户授权

create user 用户名  
identified by 密码（设置密码）  
default tablespace 表空间名;（指定用户出生的表空间）

Oracle数据库中常用角色

connect--连接角色，基本角色  
resource--开发者角色  
dba--超级管理员角色

给用户授权

grant 角色 to 用户;

切换用户

点击session，log off，all退出所有用户，再点击session，log on，随便点击一个用户登陆即可

Oracl中的数据类型

字符类型

varchar：不常用
varchar2：常用，可变长度（只能缩小，不能增加）
char（）：定长，不可变

数值类型

NUMBER：
	NUMBER（n）：表示整数，长度是n
		NUMBER（2）：表示最大是两位数，能存99以内的数字
	NUMBER（m，n）：表示一个小数，总长度是m，小数是n，整数是m-n
		NYUMBER（4，2）表示长度是4，最大存99.99
int：
	只能存整数，不能存小数，所以一般都会用NUMBER

日期类型

DATA：
	相当于Mysql中的datatime类型，精确到时分秒

大对象

CLOB：存文本，可存4G
BLOB：存二进制，可存4G

创建表，先写名称，再写类型

create table person(
   pid number(20),
   pname varchar2(10)
);

修改表结构（就是修改表的操作）

添加一列

alter table person add gender number(1);

添加多列

alter table person add （
	gender number(1)，
	age number（1）
）；

修改一列类型

alter table person modify gender varchar2(2);

修改多列类型

alter table person modify（
	gender varchar2(2);
	age char（2）
）；

修改列名称

alter table person rename column gender to sex;

删除一列

alter table person drop column sex;

数据的增删改

添加一条记录

java中的Connection对象事务是自动提交的，使用Spring框架时，事务时Spring管理的，也就是事务会从dao挪到业务层。直接使用PLSQL工具时，没有第三方来管理事务，所以需要commit

insert into person (pid,pname) values (1,'小明');
commit;

修改一条记录

update person set pname = 'xiaoma' where pid = 1;
commit;

删除

删除表中全部记录

delete from person;

删除表结构

drop table person；

先删除表，再创建表，效果等同于删除表中全部记录，在数据量大的情况下，尤其表中带有索引，该操作效率高
索引可以提高查询效率，但是会影响增删改效率

truncate table person；

序列

默认从1开始，依次递增，主要用来给主键赋值使用

语法：[]里卖内容表示可写可不写  
	CREATE SEQUENCE 序列名  
	[INCREMENT BY n] n表示每次增加几，默认增加1    
	[START WITH n]   从几开始，默认从1开始  
	[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]   
	[{CYCLE|NOCYCLE}]   
	[{CACHE n|NOCACHE}];
	缓存，如果序列用到8了，将n设置为2，会自动缓存9，10适当提高插入效率，但是效果不明显。
    序列不真的属于任何一张表，但是可以用逻辑和表做绑定  
dual:
	虚表，只是为了补全语法，没有任何实际意义

创建序列

create sequence s\_person;

查询下一个序列

select s_person.nextval from dual;

查询当前序列

selecr s_person.currval from dual;

绑定序列和数据的关系

insert into person (pid,pname) values (s_person.nextval,'xiaoming');  
commit；

scott用户，该用户的默认密码为tiger
刚刚装完Oracle数据库，scott用户是被锁定的，需要超级管理员权限解锁

解锁scott用户

alter user scott account unlock;（lock为锁定用户）

解锁scott用户的密码，也可以重置密码

alter user scott identified by tiger;

函数

单行函数
作用于一行，返回一个值

字符函数

select upper('yse') from dual;--大写  
select lower('YES') from dual;--小写

数值函数

select round(26.18，1) from dual;--四舍五入，后面的参数表示保留的位数，正数往后保留，负数往前保留  
select trunc(50.26,1) from dual;--直接截取，不再看后面的数字是否大于5 
select mod(10,3) from dual;--求余

日期函数

--查询emp表中，所有员工入职距离现在几天  
select sysdate - e.hiredate from emp e;  
sysdate:系统时间  
日期可以相加减，单位是天  
--算出明天此刻  
select sysdate + 1 from dual;  
--查询emp表中，所有员工入职距离现在几个月  
select months_between(sysdate,e.hiredate) from emp e;

转换函数，主要还是对日期进行转换

--将日期转换成字符串  
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;  
格式中不区分大小写，所以分钟处需要写mi，结果为2019-10-07 02:07:51  
select to_char(sysdate,'fm yyyy-mm-dd hh:mi:ss') from dual;--结果为 2019-10-7 2:9:18，格式前加fm，去掉结果中占位的0  
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;--结果显示为24小时计数法  
--字符串转日期  
select to_date(' 2019-10-7 14:14:4','fm yyyy-mm-dd hh24:mi:ss') from dual;

通用函数
–算出emp表中所有员工的年薪

select e.sal*12+e.comm from emp e;--结果有null，因为奖金里有null值，任何数和null计算，结果都为null  
select e.sal*12+nvl(e.comm,0) from emp e;  
nvl（列名，替换的值），如果该列有null，则会自动替换

条件表达式
条件表达式的通用写法，MySql和Oracle都可以这么写

--给emp表中员工起中文名
select e.ename,  
   case e.ename  
     when 'SMITH' then '小明'  
     when 'ALLEN' then '小华'  
     when 'WARD' then '郭德纲'  
     else '无名'  
     end  
from emp e;  
关键字when不限数量，可以写多个    
else可以不写，不写的时候结果为null  
end必须写  
--判断emp表中员工工资，如果高于3000显示高收入，高于1500低于3000显示中等，其余显示低收入  
select e.sal,  
   case   
     when e.sal>3000 then '高收入'  
     when e.sal>1500 then '中等收入'  
     else '低收入'  
     end  
from emp e;

Oracle专用条件表达式

select e.ename,  
    decode( e.ename,  
      'SMITH' , '小明',  
      'ALLEN',  '小华',  
      'WARD' , '郭德纲',  
      '无名') 中文名   
from emp e;  
Oracle中，除了起别名，其他都用单引号''，别名可以用双引号，也可以什么都不用

多行函数（聚合函数）
聚合函数的特性：作用于多行，返回一个值

select count(1) from emp;--查询总数量  
	--count(*)底层也是count(1)，相当于count（主键）  
select sum(sal) from emp;--工资总和    
select max(sal) from emp;--最大工资  
select min(sal) from emp;--最小工资  
select avg(sal) from emp;--平均工资

分组查询
查询出每个部门的平均工资

select e.deptno,avg(sal) 
from emp e 
group by e.deptno 
--分组查询中，出现在group by 后面的原始列，才能出现在select的后面。
--没有出现在group by 后面的列，想要加到select的后面，必须加上聚合函数

查询出平均工资高于2000的部门信息

select e.deptno, avg(e.sal)  
from emp e  
group by e.deptno  
having avg(e.sal) > 2000;  
--所有条件，都不能使用别名来判断，因为判断优先于查询

查询出每个部门工资高于800的员工的平均工资

select avg(e.sal)  
from emp e  
where e.sal>800  
group by e.deptno

where和having的区别

where是过滤分组前的数据，having是过滤分组后的数据。  
表现形式：where必须在group by之前，having是在group by 之后

查询出每个部门工资高于800的员工的平均工资,在查询平均工资高于2000的部门

select e.deptno,avg(e.sal)  
from emp e  
where e.sal > 800  
group by e.deptno  
having avg(e.sal) > 2000;

多表查询的一些概念
笛卡尔积

select *
from emp e , dept d

等值连接

select *  
from emp e , dept d  
where e.deptno = d.deptno

内连接

select *  
from emp e  
inner join dept d  
on e.deptno = d.deptno;

外连接

查询出所有部门，以及部门下的员工信息

select *   
from dept d  
left join emp e   
on d.deptno = e.deptno;

Oracle中专用外连接

该方式是以（+）对面的表为主表

select *  
from emp e , dept d  
where e.deptno(+) = d.deptno

自连接
查询员工姓名和员工领导姓名

select e1.ename,e2.ename  
from emp e1,emp e2  
where e1.empno = e2.mgr  
--e1的员工=e2的领导，因此，e1是领导表，e2是员工表。

查询员工名称部门名称，员工领导名称，部门名称

select e1.ename,e2.ename,d1.dname,d2.dname  
from emp e1,emp e2,dept d1,dept d2  
where e1.empno = e2.mgr   
and e1.deptno = d1.deptno  
and e2.deptno=d2.deptno

子查询

子查询返回一个值
查询出工资和scott一样的员工信息

select *
from emp where sal in
(select sal from emp e where e.ename='SCOTT')  
--此处不能用=号，因为不能保证子查询的结果只为1，或者非空，除非子查询的条件是主键

子查询返回一个集合
查询工资和10号部门任意员工一样的员工信息

select * from emp where sal in   
(select sal from emp where deptno = 10);

子查询返回一张表
查询每个部门的最低工资和最低工资员工姓名，和该员工所在部门名称

select e.ename,dm.ms,d.dname  
from emp e , dept d , (select deptno,min(sal) ms from emp e group by deptno) dm  
where dm.deptno = e.deptno   
and dm.ms = e.sal   
and e.deptno = d.deptno  
--1.先查询出每个部门的最低工资。
--2.三表联查（需要每个表都关联）

Oracle中的分页查询
rownum：行号
当我们做select操作的时候，每查询一行，就会在该行上加上一个行号，行号从1开始，依次递增，不能跳着走。排序操作会影响rownum的顺序
rownum不属于任何表，因此前面不能加别名，直接查询就可以
rownum不能写大于一个正数，因为从1开始，1小于任何一个正整数。
降序排列工资，查询最高的三个

select rownum,e.* from (select * from emp e order by sal desc) e where rownum < 4;  
--先执行查询，后排序，但是查询就会有行号，排序后将行号打乱了，因此需要在排序后重新加行号，使用嵌套查询，再取出前三个数据**

emp工资表倒叙排列后，每页五条记录，查询第二页

select * from(  
select rownum rn ,e.*   
from (select * from emp  order by sal desc) e where rownum < 11) where rn> 5 ;

由于rownum不能写大于正数，因此将第二层查询中的rownum当作一个列加上别名后作为最外层的条件使用

查询语句创建表
查询语句可以跨用户查询

create table emp as select * from scott.emp;

视图
视图的概念

视图就是提供一个查询的窗口，所有数据来自于原表

创建视图

--要创建视图，必须有dba权限，关键字view，后面加视图名
create view v_emp as select ename,job from emp;

查询视图，和查询表一样

select * from v_emp;

修改视图

--由于视图的数据来源于原表，所以修改视图的数据就是修改原表的数据，因此不推荐修改视图数据
update v_emp set job = 'CLERK' where ename = 'ALLEN';  
commit;

创建只读视图

--关键字 whih read only
create view v_emp1 as select * from emp with read only;

视图的作用

1.视图可以屏蔽一些敏感字段  
2.保证总部和分部数据及时统一

索引
索引的概念：

就是再表的列上，构建一个二叉树，达到大幅度提高查询的效率的目的，但是索引会影响增删改的效率。  
--索引分为单列索引和复合索引，单列索引就是作用在一个列上，复合索引就是作用在多个列上

创建一个单列索引
创建索引 idx_ename 在emp表的ename列上

create index idx_ename on emp(ename);
--关键字index后面加索引的名字  
select * from emp where ename = 'SCOTT'; 
--单行索引的触发规则：条件必须是索引列中的原始值  
--单行函数，模糊查询，都会影响索引的触发

复合索引
创建复合索引

create index idx_enamejob on emp(ename,job);  
--复合索引中第一列为优先检索列，如果要触发复合索引，where条件中必须包含有优先列的原始值，如果条件只有一个，并且该条件既是单列索引，又是复合索引，则触发单列索引
select * from emp where ename = 'SOCTT' or job = 'CLERK';
--上面的查询不会触发索引，因为or等于是两个查询语句 一个where ename = 'SOCTT'，一个where job = 'CLERK'

pl/sql编程语言

pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性，它是面向过程的编程语言.
pl/sql编程语言比一般的过程化编程语言，更加灵活高效.
pl/sql编程语言主要用来编写存储过程和存储函数等.

声明方法
类似java中的main方法

declare  
	声明变量的地方  
begin  
	处理业务逻辑的地方  
end;

定义一个数字变量i，长度为2（2位数），并输出

declare  
  i number(2) := 10;  
begin  
  dbms\_output.put\_line(i);  
end;

定义ena变量为emp表中ename的类型，使用查询赋值，将empno为7788的ename赋值给ena

declare  
  ena emp.ename%type;--引用型变量
begin  
  select ename into ena from emp where empno = 7788;  
  dbms_output.put_line(ena);  
end;  
--总结：赋值操作可以使用:=,也可以使用查询语句into来赋值

java中有对象，在pl/sql中，一行的记录就是对象
定义对象emprow

declare  
  emprow emp%rowtype;--记录型变量  
begin  
  select * into emprow from emp where empno = 7788;  
 dbms_output.put_line(emprow.ename || '的工作为' || emprow.job);
 --这里没有get方法，只能用对象.来获取,连接符号为||，而不是+    
end;

pl/sql编程语言中的if判断
输入小于18的数字，输出未成年，输入大于18小于40的数字，输出中年人，输入大于40的数字，输出老年人

declare  
  i number(3) := &i;--输入：&后面接变量，相当于Java中Scanner,中间不能有空格，变量名随便写    
begin  
  if i<18 then   
    dbms_output.put_line('未成年');  
  elsif i<40 then  
    dbms_output.put_line('中年人');  
  else   
    dbms_output.put_line('老年人');   
  end if;--该语言中没有括号的概念，需要以end作为结束  
end;  
--pl/sql编程语言中的if与java中的类似，也可以只写if，或if...else

#pl/sql编程语言中的loop循环
用三种循环，输出1-10十个数字

--while循环  
declare  
  i number(2) := 0;  
begin  
  while i<11 loop  
    dbms\_output.put\_line(i);  
    i := i+1;--该语法中没有i++  
  end loop;  
end;

--exit循环,退出循环
declare  
  i number(2) := 0;  
begin  
  loop  
    exit when i > 10;--当i>10的时候结束循环  
    dbms\_output.put\_line(i);  
    i := i+1;  
  end loop;  
end;

--for循环 
declare  
  
begin  
  for i in 1..10 loop--定义变量i，从1开始循环，到10结束  
    dbms\_output.put\_line(i);  
  end loop;  
end;

PLSQL中的游标
游标：相当于java中的集合，可以存放多个对象，也就是多行记录

输出emp表中的所有员工姓名

declare  
  cursor c1 is select * from emp;--创建游标，取名c1  
  emprow emp%rowtype; --定义引用型变量接收从游标中获取的对象  
begin  
  open c1;--要想使用游标，需要先打开  
    loop  
      fetch c1 into emprow;--从c1游标中拿对象，存入到变量emprow中  
      exit when c1%notfound;--当拿不到对象的时候自动退出  
      dbms\_output.put\_line(emprow.ename);  
    end loop;  
  close c1;--关闭游标  
end;

给指定部门员工涨工资

declare  
 cursor c1(eno emp.deptno%type) is select empno from emp where deptno = eno; --定义带参数的游标，并赋值  
 en emp.empno%type;  
begin  
  open c1(10);--只在打开的时候给参数赋值，结束时不要带参数  
     loop  
       fetch c1 into en;  
       exit when c1%notfound;  
       update emp set sal = sal + 100 where empno = en;  
       commit;  
    end loop;  
  close c1;  
end;

存储过程

--存储过程就是提前已经编译好的一段PL/SQL语言，放置在数据库端，可以直接被调用。这一段PL/SQL一般都是固定步骤的业务

给指定员工涨100工资

create or replace procedure p1(eno emp.empno%type)
--p1为过程名，相当于java中的方法名.  
--存储过程中的参数(eno in/out emp.empno%type),分为in/out类型，不写默认为in类型,参数不能具有长度.
  as  
begin  
  update emp set sal = sal+100 where emp.empno = eno;  
  commit;  
end;  
--执行存储过程时，出现错误不会报错，需要从Procedures文件夹中查看存储过程名中是否有红色的X号

--测试p1  
declare  
  
begin  
  p1(7788);  
end;

存储函数
通过存储函数，计算指定员工的年薪

create or replace function f_yearsal(eno emp.empno%type) return number   
is   
--f_yearsal为变量名，参数也有in/out，该参数不能带有长度，return后跟数据类型,该数据类型不能具有长度  
  s number(10);--定义变量s，长度为10的数字型  
begin  
  select sal*12+nvl(emp.comm,0) into s from emp where emp.empno = eno;  
  --into s:表示将查询结果存入变量s中  
  return s;--这个return后跟变量名  
end;

调用存储函数,存储函数在调用的时候，返回值需要接收

declare   
  s number(10);  
begin  
  s := f\_yearsal(7369);   
  dbm\s_output.put\_line(s);  
end;

out类型参数讲解
使用存储过程来计算年薪

create or replace procedure p_yearsal(eno emp.empno%type,yearsal out number)--yearsal为输出类型参数  
as  
  s emp.sal%type;  
  c emp.comm%type;  
begin  
  select sal*12 , nvl(emp.comm,0) into s,c from emp where emp.empno = eno;  
  yearsal := s+c;  
end;

调用存储过程计算年薪

declare  
 yearsal emp.comm%type;--先定义yearsal参数  
begin  
  p_yearsal(7788,yearsal);  
  dbms_output.put_line(yearsal);  
end;  
--凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰

存储过程和存储函数的区别

--关键字不同，procedure过程，function函数.
--存储过程比存储函数多了两个return.
--本质区别:存储函数有返回值，存储过程没有返回值，如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数，即便是存储过程使用了out类型的参数，其本质也不是真的有了返回值，而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值.

我们可以使用存储函数有返回值的特性来自定义函数。而存储过程不能用来自定义函数
案例需求:查询员工姓名和部门名称
先把scott用户下的dept表复制到当前用户下

create table dept as select * from scott.dept;

定义函数，提供部门编号，查询部门名称

create or replace function fun(dno dept.deptno%type) return dept.dname%type  
is  
name dept.dname%type;  
begin  
  select dept.dname into name from dept where dept.deptno = dno;  
  return name;  
end;

使用函数完成需求

select emp.ename,fun(emp.deptno)
from emp;

触发器

触发器就是指定一个规则，在我们做增删改的时候，只要满足该规则，自动触发，无需调用。查询时无法触发触发器
触发器分为两类:语句级触发器(不包含有for each row的就是语句级触发器).
			 行级触发器(包含有for each row的就是行级触发器).
加 for each row是为了使用:old，:new 对象或一行记录。

触发语句	:old	:new
Insert	所有字段都是空（null）	将要插入的数据
Update	更新以前该行的值	更新后的值
Delete	删除以前改行的值	所有字段都是空（null）