Oracle数据库所有知识点集合

------------------------------ORACLE LEARN----------------------------------


1.与ORACLE相关的数据库

MySql 小型数据库 (免费,开源)
Sqlserver 中大型数据库
Oracle 大型数据库
Db2(IBM公司)


----------------------------------------------------------------------------
2.开启Oracle服务

net start OracleServiceOrcl(OracleServiceOrcl不区分大小写)

然后输入SqlPlus进入Oracle的CMD界面


------------------------------------------------------------------------------
3.Oracle默认账户

Sys/123456 最高管理员 董事长
System/123456 总经理
Scott/tiger (Oracle第一个员工、需要解锁)


----------------------------------------------------------------------------
4.Oracle三个认证(Oracle证书)

Oca 初级
Ocp 专业级
Ocm master 大师级


-----------------------------------------------------------------------------
5.Oracle常用命令

查询员工表
select * from scott.emp;(注意分号)


查询名字为SCOTT的员工信息
select * from scott.emp where ename = 'SCOTT';


查询员工scott创建的表
select * from scott.dept;


在员工表插入一个员工数据
insert into scott.emp(empno,ename) values(800,'OCEANMIX');


删除某个员工信息
delete from scott.emp where ename = 'OCEANMIX';


创建一个表(Oracle中没有自增列)
create table UserInfo(
id int primary key,
ename nvarchar2(64)
);


//向表中添加数据
insert into userinfo values(1,'oceanmix');


//删除表
drop table userinfo;


//修改员工某项数据
update scott.emp set sal ='5000' where ename = 'OCEANMIX';


//输入ed;创建表(decimal(3,2)表示总共3位,小数占据2位)
create table emp
(
id int primary key,
ename nvarchar2(64) default 'test',
money decimal(3,2)
)


查看当前登陆的用户 show user
清除屏幕命令 clear
登陆命令 conn 用户名/密码
解锁命令 alter user scott account unlock;
修改别人密码命令 alter user scott identified by 123456;
修改自己密码命令 password(最后还需输入commit;确认提交、适用于CMD窗口中)
创建用户命令 create user cz identified by 123456;
删除用户命令 drop user 用户名(注意:自己不能删除自己,如果删除当前连接用户需要在菜单Session处的Log off注销掉正在连接的用户)
进入编辑窗口 ed(ok后输入/执行编辑窗口内的命令)
显示错误 show error;


--------------------------------------------------------------------------------------------------------------------------------------------------------------------
6.Oracle权限


注意:一个新用户是不具有任何权限的

权限授予命令:
授予登陆权限:grant create session to cz;
授予查询权限:grant select on scott.emp to cz;
授予删除权限:grant delete on scott.emp to cz;
一次性权限授予:grant insert,delete,update,select on scott.emp to cz;
建表权限:grant create table to zz;
对表空间进行操作的权限:grant unlimited tablespace to zz;

权限回收命令:
回收cz用户对于scott.emp表的查询权限:revoke select on scott.emp from cz;
回收cz用户对于scott.emp表的增删改查四项权限:revoke insert,delete,update,select on scott.emp from cz;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
7.权限的传递

权限传递方式:
1.with admin option权限不能被级联取消
2.with grant option权限会被级联取消


注意:默认情况下权限是不能进行传递的


-----------------------------------------------------------------------------------------------------------------------------------------
权限传递命令:
system将授予他人登录权限授予cz:grant create session to cz with admin option;(with admin option权限不能被级联取消)
cz授予zz登录权限:grant create session to zz;
system回收cz的登录权限:revoke create session from cz;
system将授予他人查询scott.emp表权限授予cz:grant select on scott.emp to cz with grant option;(with grant option权限会被级联取消)
system回收cz的授予他人查询scott.emp表权限:revoke select on scott.emp from cz;

-----------------------------------------------------------------------------------------------------------------------------------------
实际例子:


1.system创建了两个用户,一个cz,一个zz,system授予cz登录权限和授予他人登录权限,cz再授予zz登录权限,
如果system收回了cz的登录和授予登录权限,那么zz的权限会消失吗?(zz不会失去登录的权限)


2.system将“授予他人查询scott.emp表权限”授予cz,cz将“查询scott.emp表权限”授予zz,system回收了cz的授予他人查询scott.emp表权限,
zz也失去了查询scott.emp表的权限(注意:授予他人查询scott.emp表权限包含查询scott.emp表权限)


---------------------------------------------------------------------------------------------------------------------------------------------------------------------
8. 权限的分类


1.系统权限(登录、创建表、创建表空间、创建视图)-->代表着大的范围
2.对象权限(针对某一个特定对象下面的细分权限)-->代表着小的范围

实际例子:
不同的用户查询相同的表看到的东西是不一样的(如使用不同的用户查询select * from user_tables;)


---------------------------------------------------------------------------------------------------------------------------------------------------------------------
9.角色授权(角色:一组权限)

1.系统的三个重要角色:(Connection、Resource、dba),其中dba的权限等于system

2.相关查询:(SQL Window处查询、F8查询、注意:在SQLWindow中查询时,需要将查询条件更改为英文大写)

--查询所有的角色
select * from dba_roles;


--查询指定角色拥有的系统权限
select * from dba_sys_privs where grantee= '角色名';


--查询指定角色是否存在于角色表
select * from dba_roles where role ='角色名'

--查询指定角色的对象权限
select * from dba_tab_privs where grantee= '角色名';


--查询隶属指定角色的角色
select * from role_role_privs where role='角色名';


3.实际例子:
system将dba角色授予cz,cz又将dba角色授予zz,如果使用zz删除用户cz能够成功吗?(drop user cz)
能成功!虽然dba这个角色是cz授予zz的,但是丝毫不影响zz使用dba权限来删除cz,请注意:授予dba角色时,同样授予了授予他人dba角色权限


4.角色相关命令

//创建一个角色(自定义角色)
create role myrole;


//把权限给角色
grant create session to myrole;
grant select,insert,update,delete on scott.emp to myrole;


//把角色给角色
grant RESOURCE to myrole;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
10.Oracle中Char、Varchar2、Nvarchar2的问题

1.所谓的Varchar2和Nvarchar2是一种数据库的规范,如同Varchar和Nvarchar

2.Char(10)-->长度固定,如果你存入oceanmix这个字符只有8个字节,也会被算作10个字节,不存在的字节会以空的形式补全


3.Varchar2和Nvarchar2-->都是可变长度的字符串,存入oceanmix都只会占用8个字节,不会多占。
如果使用Varchar2存储汉字会被占用2个字节,使用Nvarchar2存储汉字、英语字母、特殊符号都占用相同的字节(2个字节)


4.从空间上来说:Varchar2和Nvarchar2要比Char占用的空间少,但是从以空间换效率来说:Char对比Varchar2和Nvarchar2是更有优势的
Char只需要补空,填满固定容量,而Varchar2和Nvarchar2需要计算实际上使用到的容量,即用户这个字段的数据占用了多少字节


--------------------------------------------------------------------------------------------------------------------------------------------------------------------
11.同义词


1.注意事项:

1.同义词是为了解决表名过长的问题


2.根据同义词来操作一张表的增删改查是可行的


2.命令:


--查询数据库所有的同义词
select * from dba_synonyms;


--查询指定的同义词
select * from dba_synonyms where synonym_name='同义词名';


--给指定表创建私有同义词(仅限创建用户可见,其他用户不可见)
create synonym ep for scott.emp;


--给指定表创建公有同义词(所有用户都可见)
create public synonym ep for scott.emp;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
12.视图


1.注意事项:

1.根据视图来操作一张表的增删改查是可行的(但前提是必须拥有操作该表的权限,如果不是自己创建的表,需要授权)


2.视图可以存放多张表的查询结果,这是同义词不具备的


3.视图可以是只读的(不允许增删改)


----------------------------------------------------------------------------------------------
2.命令:


--查询所有的视图
select * from dba_views;


--查询指定的视图
select * from dba_views where view_name='视图名';


--查询指定用户创建的视图
select * from dba_views where owner ='用户名';


--创建一个视图
create view view_emp
as
select * from scott.emp


--创建只读视图
create view view_emp
as
select * from scott.emp
with read only


--如果创建的视图已经存在(即视图名重复,使用如下代码解决)-->有创建视图的作用
create or replace view view_emp
as
select * from scott.emp
with read only

--创建存放多张表查询结果的只读视图
create view view_emp
as
select e.ename,d.dname from scott.emp e join scott.dept d on e.deptno = d.deptno
with read only


------------------------------------------------------------------------------------------------------
3.实际例子-->如何将视图授予指定用户查看

system创建了一个存放2张表查询结果的只读视图view_emp(创建2张表的用户是scott),如何让cz能够查看?

步骤:
system:
1.grant select on view_emp to cz; 失败,因为不存在 'SCOTT.DEPT' 的授权选项(由于组成视图的表是scott创建的,所以需要scoot授权给system)


scott:
2.grant select on scott.dept to system with grant option; 成功授予system授予他人查看scott.dept表权限
3.grant select on scott.emp to system with grant option; 成功授予system授予他人查看scott.emp表权限


system:
4.grant select on view_emp to cz; 成功授予cz查看system.view_emp视图权限


cz:
5.select * from system.view_emp; 成功!可以查看到system.view_emp视图

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
13.Oracle序列


1.开始:


1.Oracle没有自带的自增ID,所以需要借助序列和触发器来实现自增ID


---------------------------------------------------------------------------------------
2.命令:

--查询Oracle的默认序列
select * from dba_sequences;


--查询Oracle的默认序列前5条数据
select * from dba_sequences where rownum<=5;


--查询自定义序列
select mysequ.nextval from dual;


--创建序列
create sequence mysequ
minvalue 1 --最小值
increment by 1 --每次增长数
nomaxvalue --没有最大值
nocache
nocycle


--修改序列
alter sequence mysequ
minvalue 1 --最小值
increment by 1 --每次增长数
maxvalue 100 --有最大值
nocache
cycle --超出最大值后从1重新开始


--删除序列
drop sequence mysequ;


--序列在表中的使用
insert into scott.emp(EMPNO,ENAME) values(mysequ.nextval,'OCEANMIX');


---------------------------------------------------------------------------------------------------------------------------------------------------------------------
14.表空间


1.开始:


1.Oracle数据表存储在表空间内、新创建的用户是没有权限操作表空间的


2.临时表空间主要是做排序、索引之类的操作,避免操作表空间(表空间数据量大,操作时间较长),临时表空间在使用完后立即释放


3.执行删除表空间命令并没有彻底删除表空间文件(即DBF文件、可能是为了防止误删除和方便恢复数据)


---------------------------------------------------------------------------------------------
2.命令:

--查询默认的表空间
select * from dba_tablespaces


--查询指定表空间下面的表
select * from dba_tables where tablespace_name='SYSTEM';


--查询数据库中所有用户的默认表空间、临时表空间
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users


--查询数据库中指定用户的默认表空间、临时表空间
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SCOTT'


--修改指定用户的默认表空间为users
alter user cz default tablespace users;


--授予指定用户操作表空间的权限
grant unlimited tablespace to cz;


--查询默认表空间存储位置
select * from dba_data_files;


--创建表空间
create tablespace OWSLA --表空间名字
datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\OWSLA.DBF' --表空间文件路径
size 20m --表空间大小


--SQLServer中将Test表的数据全部复制到Test2(表数据转移)
select * into Test2 from Test


--删除非空表空间(即该表空间下面还存在表、删除意味着将删除表的数据)
drop tablespace OWSLA INCLUDING CONTENTS;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
15.Oracle系统函数


1.数值函数

--求绝对值
select abs(123) from dual


--进一法(123.1得出124)
select ceil(123.1) from dual


--去尾法(-123.9得出-124、123.9得出123)
select floor(-123.9) from dual


--四舍五入
select round(123.46) from dual

--四舍五入保留2..n位小数
select round(123.44,2) from dual


--数字截取·默认截取整数
select trunc(123.45) from dual


--数字截取·截取小数后几位
select trunc(123.4567,1) from dual


--数字截取·截取小数前几位(得出100)
select trunc(123.4567,-2) from dual


--------------------------------------------------------------------------
2.字符函数


--转换成大写
select upper('abc') from dual


--转换成小写
select lower('ABC') from dual


--去掉字符中所有空格
select trim(' AbC ') from dual


--去掉左边空格
select ltrim(' AbC ') from dual


--去掉左边指定的字符
select ltrim('abcd','dca') from dual


--去掉右边指定的字符
select rtrim('abcd','dca') from dual


--替换函数
select replace('this is a boy','boy','girl') from dual
select replace('this is a boy',' ','') from dual --去掉字符中的空格


--字符串相加(下面两个例子都得出aabb)
select 'aa'||'bb' from dual
select concat('aa','bb') from dual


--截取字符串的函数(注意:Oracle中0和1都是从第一个字符开始的、后面的数字是截取的位数)
select substr('abcdef',1,3) from dual
select substr('abcdef',2,3) from dual


--求长度的函数
select length('a') from dual


-------------------------------------------------------------------------------
3.转换函数


--转化成数字
select to_number('10') from dual


--转化成字符串
select to_char(sysdate) from dual


--转化成字符串,格式化时间
select to_char(sysdate,'yyyy-mm-dd') from dual


--完整的年月日时分秒的时间
select to_char(sysdate,'yyyy-mm-dd hh:Mi:ss') from dual


--时间24小时制
select to_char(sysdate,'yyyy-mm-dd hh24:Mi:ss') from dual


-------------------------------------------------------------------------------
4.处理Null的函数


--为空返回指定的代替,不为空返回自己
select nvl('','b') from dual


--不为空返回参数2为空返回参数3
select nvl2('','b','c') from dual


-------------------------------------------------------------------------------
5.自定义函数(函数必须要有一个返回值)


--创建自定义函数(无参数有返回值)
create or replace function func_GetDate
return varchar2
as
begin
return to_char(sysdate,'yyyy-mm-dd');
end;


--创建函数(有参数有返回值、第一参数为空,输出第三参数,不为空,输出第二参数)
create or replace function func_ResultValue(p1 nvarchar2,p2 nvarchar2,p3 nvarchar2)
return nvarchar2
is --is和as都可以使用
begin
if (p1 is null or p1='') then
return p3;
else
return p2;
end if;
end;


--查询指定员工的工资函数(有参有反)
create or replace function func_test(pname nvarchar2)
return nvarchar2
is
r_sal int;
begin
select sal into r_sal from scott.emp where ename=pname;
return rsal;
end;


--无论如何都返回固定的值0.1(因为用于判断的参数始终默认不变)
create or replace function myround(p1 decimal,p int default 0)
return decimal
as
begin
if p=0 then
return 0.1;
end if;
end;


--Oracle四舍五入函数
create or replace function myround(p1 number,p int default 0)
return number
is
needn int;
tempp number;
begin
needn:=substr(p1,length(trunc(p1))+2+p,1);
--应该进1
if needn >=5 then
begin
--在指定位置构建一个1(扩大1)
tempp:=p1+1/power(10,p);
return trunc(tempp,p);
--return tempp;
end;
else --应该舍去(舍去很简单直接截取)
return trunc(p1,p);
end if;
end;


--Oracle递归函数实现
create or replace function func_num(pnum int)
return int
is
begin
if(pnum=1 or pnum=2) then
return 1;
else
--调用自身
return func_num(pnum-1)+func_num(pnum-2);
end if;
end;


--对员工表工资进行等级分类
select (
case sal 
when 5000 then '三万元'
when 800 then '五千元'
else '五千元到三万元之间'
end
),ename,sal from scott.emp


--Oracle中if的初步使用(赋值也可以在声明变量时赋值:declare p int:=1;)注意:记得开启set serveroutput on;
declare p int;
begin
p:=1;
if p=1 then
dbms_output.put_line('oceanmix');
end if;
end;


--Oracle中if...elsif...else...end if的使用
declare p int:=2;
begin
if p=1 then
dbms_output.put_line('oceanmix1');
elsif p=2 then
dbms_output.put_line('oceanmix2');
else
dbms_output.put_line('oceanmix3');
end if;
end;


--Oracle中case...when...else...end case的使用
declare p int:=2;
begin
case p
when 1 then
dbms_output.put_line('oceanmix1');
when 2 then
dbms_output.put_line('oceanmix2');
else
dbms_output.put_line('oceanmix3');
end;


--Oracle中loop循环使用(此循环输出1-10)
declare p int:=1;
begin
loop
dbms_output.put_line(p);
p:=p+1;
exit when p=11; --退出循环的条件,当p=11的时候
end loop;
end;


--调用函数
select func_GetDate from dual;


--删除函数
drop function 函数名

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
16.Oracle存储过程


1.优点:


1.存储过程会预编译sql语句,编译一次后执行不再需要编译,执行效率快一点
2.可以在存储过程进行sql编码,可以减少服务器压力


2.缺点:代码量多,很难维护


3.命令


--Oracle存储过程输出控制
set serveroutput on;
set serveroutput off;


--1.创建一个输出系统时间的存储过程
create procedure proc_sel
as
begin
dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd'));
end;


--2.定义存储过程求2个数字之和
create procedure proc_sum(p int,p2 int)
as
begin
dbms_output.put_line(p+p2);
end; 


--3.定义存储过程求2个数字之和,并提供返回参数
create or replace procedure proc_sum(p int,p2 int,pout out int)
as
begin
pout := p+p2;
end;


--4.创建根据ename查询并返回Job的存储过程
create or replace procedure proc_sel(pename nvarchar2,pout out nvarchar2)
as
begin
select job into pout from scott.emp where ename = pename;
end;


--调用Oracle存储过程
begin
proc_sel;
end;


--调用Oracle存储过程
begin
proc_sum(6,6);
end;


--调用有返回参数的存储过程
declare pr int;
begin
proc_sum(3,6,pr);
dbms_output.put_line(pr);
end;


--调用有返回参数的存储过程
declare pr nvarchar2(64);
begin
proc_sel('SCOTT',pr);
dbms_output.put_line(pr);
end;


--修改存储过程
create or replace procedure proc_sel
as
begin
dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd'));
end;

4.创建自定义存储过程

--向SCOTT.EMP表插入数据的存储过程
create or replace procedure proc_insert_scott_emp(pno int,pename nvarchar2)
as
begin
insert into scott.emp(empno,ename) values(pno,pename);
commit;
end;


--如何调用别人所创建的存储过程,需要原来创建存储过程的用户执行下列语句,将执行存储过程的权限授予需要授予的用户
GRANT EXECUTE ON proc_insert_scott_emp TO SCOTT;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
17.Oracle游标-->cursor


1.作用:游标可以装一个数据集


2.使用游标的步骤:

1.定义一个游标


2.给游标关联一个数据源


3.解析游标(循环去读取游标)


3.命令:


--定义一个游标(数据源主要是表中字段)
declare cursor cur_sel
--给游标关联数据源
is select ename,sal,job from scott.emp;
v_ename nvarchar2(64);
v_sal int;
v_job nvarchar2(64);
begin
--打开游标(真正去执行关联的sql语句)
open cur_sel;
loop
--读取一行游标,往下读取一行
fetch cur_sel into v_ename,v_sal,v_job;
dbms_output.put_line(v_ename||':'||v_sal||':'||v_job);
exit when cur_sel%notfound;
end loop;
--关闭游标
close cur_sel;
end;


--定义一个游标(数据源主要是一个表)
declare cursor cur_sel
--给游标关联数据源
is select * from scott.emp;
--emp表一行的数据类型
emp_row scott.emp%rowtype;
begin
--打开游标(真正去执行关联的sql语句)
open cur_sel;
loop
--读取一行游标,往下读取一行
fetch cur_sel into emp_row;
dbms_output.put_line(emp_row.empno||':'||emp_row.ename||':'||emp_row.job||':'||emp_row.sal);
exit when cur_sel%notfound;
end loop;
--关闭游标
close cur_sel;
end;


--1.先定义一个用来绑定数据源和打开游标的存储过程
create or replace procedure proc_sel(rcursor out sys_refcursor)
as
begin
open rcursor for select * from scott.emp;
end;


--2.存储过程直接查询一个表的所有数据(借助游标、游标事先存储了一个表的所有数据,所以可以直接输出)
declare rcursor sys_refcursor;
emp_row scott.emp%rowtype;
begin
proc_sel(rcursor);
loop
fetch rcursor into emp_row;
dbms_output.put_line(emp_row.empno||':'||emp_row.ename||':'||emp_row.job||':'||emp_row.sal);
exit when rcursor%notfound;
end loop;
--关闭游标
close rcursor;
end;

--------------------- 

转自:https://blog.csdn.net/weixin_39550799/article/details/78820225

 

 

©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值