Oracle数据库入门(使用参考)
零、简介
Oracle Database,又名Oracle RDBMS,或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统,系统可移植性好、使用方便、功能强,适用于各类大、中、小微机环境。它是一种高效率的、可靠性好的、适应高吞吐量的数据库方案。
ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完整的数据管理功能;作为一个关系数据库,它是一个完备关系的产品;作为分布式数据库它实现了分布式处理功能。但它的所有知识,只要在一种机型上学习了ORACLE知识,便能在各种类型的机器上使用它。
一、与mysql对比
内容挺多的,请参考这篇融合怪文章 https://blog.csdn/qq_44769485/article/details/114460712
一、Oracle基础和表的CURD
1.表空间以及用户的创建-用户的授权
-- 创建表空间
create tablespace oracletest -- 创建表空间
datafile 'E:\oracle\tablespace\oracletest.dbf' -- 把表空间放在那,这里要指定路径名以及文件名,必须以.dbf结尾
size 100m -- 设置初始容量
autoextend on -- 设置自动扩容
next 10m; -- 每次扩容10兆
-- 删除表空间
drop tablespace oracletest;
-- 创建用户
create user oracletest -- 创建用户
identified by oracletest -- 设置密码
default tablespace oracletest; -- 设置用户出生位置,出生在那个表空间
-- 给用户授权
-- oracle数据库中常用角色
connect -- 连接角色 ,基本角色
resource -- 开发者角色
dba -- 超级管理员角色
--给oracletest用户授予dba角色
grant dba to oracletest;
--切换到oracletest用户
2.数据类型与表的创建
-- 创建一个person表
-- 数据类型
-- Varchar,varchar2 表示一个字符串,可自动缩容,但不能自动扩容
-- char 不可变字符类型
-- NUMBER NUMBER(n)表示一个整数,长度是n 例: number(2) 这就是0到99
-- NUMBER(m,n)表示一个小数,总长度是m,小数是n,整数是m-n
-- DATE 表示日期类型
-- CLOB 大对象,表示大文本数据类型,可存4G
-- BLOB 大对象,表示二进制数据,可存4G
create table person(
pid number(20),
pname varchar2(10)
);
3.修改表结构
-- 修改表结构
-- 添加一列
alter table person add (sex number(1),money number(4));
-- 修改列类型 modify(修改)
alter table person modify sex char(1);
-- 修改列名称 column(列)
alter table person rename column sex to gander;
-- 删除一列
alter table person drop column money;
4.数据的增删改
-- 数据的增删改
-- 数据的查询
select * from person;
-- 添加一条记录
insert into person(pid,pname,sex) values(1,'小明','男');
commit;
-- 修改一条数据
update person set pname='小马' where pid=1;
commit;
-- 删除记录
-- 删除表中全部记录
delete from person;
-- 删除表结构
drop table person;
-- 先删除表,再创建表.效果等同于删除表中全部记录 truncate(截断=删除)
-- 在数据量大的情况下,尤其在表中带有索引的情况下,该操作效率高
-- 索引可以提供查询效率,但是会影响增删改的效率
truncate table person;
5.序列的使用
-- 序列的使用
-- 序列:默认从1开始,一次递增,主要用来给主键赋值使用
-- 序列不真的属于任何一张表,但是可以逻辑和表做绑定
-- dual:虚表,只为了补全语法,没有任何意义
create sequence s_person;
select s_person.nextval from dual;
-- 序列的使用:添加一条记录(设置主键增长)
insert into person values(s_person.nextval,'小王','男');
commit;
select * from person;
6.scott用户解锁
-- scott用户,密码默认是tiger
-- 解锁scott用户
alter user scott account unlock;
-- 解锁scott的密码[此句也可以用来重置密码]
alter user scott identified by tiger;
-- 切换到scott用户
select * from emp;
7.单行函数
-- 单行函数:作用于一行,返回一个值
-- 字符函数
select upper('yes') from dual; --小写变大写
select lower('YES') from dual; --大写变小写
-- 数值函数
select round(26.14,1) from dual;-- 四舍五入,后面参数表示保留的位数,-1表示往前保留一位(试试就知道了)
select trunc(26.14,1) from dual;-- 直接截取,保留小数点后一位,再后面的直接截取掉
select mod(10,3) from dual;-- 取余
-- 日期函数
select sysdate-e.hiredate from emp e;-- 查询出emp表中所有员工入职距离现在几天
select sysdate+1 from dual;--获取明天这个时候的时间
select months_between(sysdate,e.hiredate) from emp e;-- 查询出emp表中所有员工入职距离现在几月
select months_between(sysdate,e.hiredate)/12 from emp e;-- 查询出emp表中所有员工入职距离现在几年
select round((sysdate-e.hiredate)/7) from emp e;-- 查询出emp表中所有员工入职距离现在几周
-- 转换函数
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;--当前日期转换成字符串,fm表示不带0开头,24表示用24小时计时法
select to_date('2020-8-12 11:45:32','fm yyyy-mm-dd hh24:mi:ss') from dual;-- 字符串转日期
-- 通用函数
-- null值和任意数字运算结果都为null
-- nvl(em,0) 如果值为null,则换成0
select e.sal+nvl(em,0) from emp e;-- 算出emp表中所有员工的年薪+奖金
8.条件表达式
-- 条件表达式
--下面两个是mysql和oracle都统用的条件表达式
-- 给emp表中员工起中文名称
select e.ename, -- 等值判断用这种写法
case e.ename
when 'SMITH' then '曹贼'
when 'ALLEN' then '大儿子'
when 'WARD' then '诸葛小儿'
else '无名'
end
from emp e;
--判断emp表中员工工资,如果高于3000显示高收入,如果高于1500低于3000显示中收入,低于1500显示低收入,其余都是低收入
select e.sal,-- 范围判断用这种写法
case -- 这就类似一个switsh
when e.sal>300 then '高收入' -- 第一个条件不满足才会到下一个条件
when e.sal>1500 then '中等收入'
else '低收入'
end
from emp e;
-- Oracle中除了起别名,都用单引号
-- Oracle专用条件表达式
select e.ename,
decode(e.ename,
'SMITH' , '曹贼',
'ALLEN' , '大儿子',
'WARD', '诸葛小儿',
'无名') "中文名" --这个别名可以双引号或者直接写中文名
from emp e;
9.多行函数
-- 多行函数【聚合函数】:作用于多行,返回一个值
select count(1) from emp;--查询总数量 这里的1等于emp表的主键,推荐写1
select sum(sal) from emp;--求和 求所有员工工资综合
select max(sal) from emp;--求最大 求工资最大的值
select min(sal) from emp;--求最小 求工资最小的值
select avg(sal) from emp;--求平均 求工资平均值
10.分组查询
-- 分组查询 【重要】
--分组查询中,出现再group by后面的原始列,才能出现在select后面
--没有出现再group by后面的列,想在select后面,必须加上聚合函数
--聚合函数有一个特性,可以把多行记录编程一个值
--所有条件都不能使用别名来判断
--where执行先于select
-- 查询出每个部门的平均工资
select e.deptno,avg(e.sal) --e.ename
from emp e
group by e.deptno;--分组依据
--查询出平均工资高于2000的部门信息
select e.deptno,avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal)>2000;--判断工资大于2000
--查询出每个部门工资高于800的员工的平均工资
select e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by e.deptno;
--【重要】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;
11.多表查询的一些概念
-- 多表查询中的一些概念
--笛卡尔积
--两张表的数据量进行相乘
select *
from emp,dept;
--等值连接
select *
from emp e,dept d
where e.deptno=d.deptno;
--内链接(这是sql最开始的等值连接写法,推荐使用等值连接)
select *
from emp e inner join dept d
on e.deptno=d.deptno;
--查询中所有部门,以及部门下的员工信息,【外连接】
select * -- 右外查询 right
from emp e right join dept d
on e.deptno=d.deptno;
--查询所有员工信息,以及员工所属部门
select * -- 左外查询 left
from emp e left join dept d
on e.deptno=d.deptno;
--oracle中专用的外连接
select * from emp e,dept d where e.deptno(+)=d.deptno; --(+)放在那边,他的对面的全部信息,和(+)边的符合条件的数据
12.子链接概念和练习
--查询出员工姓名,员工领导姓名
select e1.ename,e2.ename -- 自链接:自联结其实就是站在不同的角色把一张表看成多张表
from emp e1,emp e2
where e1.mgr=e2.empno;
--查询出员工部门名称和员工领导名称和部门名称
select e1.ename,e2.ename,d.dname
from emp e1,emp e2,dept d
where e1.mgr=e2.empno
and e2.deptno=d.deptno
;
-- 查询出每个员工编号,姓名,部门名称,工资等级和他的上级领导的姓名,工资等级
select e1.empno,e1.ename,d.dname,
case
when e1.sal>3000 then '高收入'
when e1.sal>1500 then '中收入'
when e1.sal<1500 then '低收入'
else '低收入'
end ,d.dname,case
when e2.sal>3000 then '高收入'
when e2.sal>1500 then '中收入'
when e2.sal<1500 then '低收入'
else '低收入'
end
from emp e1,emp e2,dept d
where e1.mgr=e2.empno and e1.deptno=d.deptno ;
13.分页查询
-- 分页查询
--rownum行号:当我们做select操作得时候
--每查询一行记录,就会在该行上加一个行号
--行号从1开始,一次递增,不能跳着走 【重要】
--排序操作会影响rownum得顺序
--如果涉及到排序,但是还要使用rownum得话,我们可以再次嵌套查询
--倒序查询并加上第二次查询得行号
select rownum,t.* from (select * from emp e order by e.sal desc) t; -- order by 排序操作,默认正序
--emp表工资倒叙排列后,每页五条记录,查询第二页
--rownum这个不能被别的表.(点)这个是个独立的东西,不算在表里 【重要】
select *
from(
select rownum r,t.*
from (
select *
from emp
order by sal desc
) t
where rownum<11
)
where r>5;
二、Oracle加强部分
1.视图
--视图
--视图的概念:视图就是提供一个查询的窗口,所有数据来自于原表
--查询语句创建表
create table emp as select * from scott.emp;
select * from emp;
--创建视图【必须有dba权限】
create view v_emp as select ename,job from emp;
--查询视图
select * from v_emp;
--修改视图【不推荐】
update v_emp set job='CLERK' where ename='ALLEN';
commit;
--创建只读视图
create view v_emp1 as select * from emp with read only;
--视图的作用?
--第一:视图可以屏蔽一些敏感字段
--第二:保证总部和分部数据及时统一
2.索引
-- 索引
--索引的概念:索引就是再表的列上构建一个二叉树
--达到大幅度提高查询效率的目的,但是索引会影响增删改的效率
--单列索引
--创建单列索引
create index idx_ename on emp(ename);-- 创建一个索引 idx_ename 作用在 emp表的 ename属性上
--单列索引触发规则,条件必须是索引列中的原始值
--单行函数,模糊查询,都会影响索引的触发
select * from emp where ename='SCOTT';--触发单行索引
--复合索引
--创建复合索引
create index idx_enamejob on emp(ename,job);--ename为优先索引列
--复合索引中第一列为有先索引列
--如果要触发复合索引,必须包含有优先检索列中的原始值
select * from emp where ename='SCOTT' and job='xx';--触发复合索引
select * from emp where ename='SCOTT' or job='xx';--不触发索引
select * from emp where ename='SCOTT';--如果存在单列索引和多列索引,那么这种情况就触发单列索引
3.PLSQL语言定义变量
--pl/sql编程语言
--pl/sql编程语言是对sql语言的扩展,使得sql语言具有过程化编程的特性
--pl/sql编程语言比一般的过程化编程语言,更加灵活高效
--pl/sql编程语言主要用来编写储存过程和储存函数等
--声明方法
--赋值操作可以使用:=也可以使用into查询语句赋值
declare
i number(2):=10;
s varchar2(10):='小明';
ena emp.ename%type;--引用类型变量
emprow emp%rowtype;--记录型变量
begin
dbms_output.put_line(i);
dbms_output.put_line(s);
select ename into ena from emp where empno = 7788;-- 查询emp表中 empno为7788的用户的名字,将名字字段的属性和值都赋值给 ena
dbms_output.put_line(ena);
select * into emprow from emp where empno = 7788;-- 查询emp表中 empno为7788的用户的信息列,将这一行信息封装给emprow,现在emprow相当于一行数据
dbms_output.put_line(emprow.ename || '的工作为' || emprow.job);
end;
4.PLSQL中的if判断
--pl/sql中的if判断
--输入大于18的数字,判断为未成年
--输入大于18小于40,判断为中年人
--输入大于50 ,判断为老年人
declare
i number(2):=&年龄;
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;
5.PLSQL中的循环
--pl/sql中的loop循环
--用三种方式输出1到10十个数字
--while循环
declare
i number(2):=1;
begin
while i<11 loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
--exit循环(退出循环)【一般这个用的比较多】
declare
i number(2) :=1;
begin
loop
exit when i>10;--注意这个when(什么时候)
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
--for循环
declare
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
6.PLSQL中的游标
--游标:可以存放多个对象,多行记录 (类似于java里面的集合)
--输出emp表中所有员工的姓名
declare
cursor c1 is select * from emp;--将整个emp表装进c1游标中
emprow emp%rowtype;--创建一个记录型变量,变量类型为emp中的一行数据
begin
open c1;--开启游标
loop
fetch c1 into emprow;--个人感觉像一个foreach,只不过是放判断前面 fetch(取)
exit when c1%notfound;--当c1查不到东西的时候退出查询
dbms_output.put_line(emprow.ename);--这个记录型变量一定要点一个属性,他不能当作一行数据来查
end loop;
close c1;--关闭游标
end;
--给指定的部门员工涨工资 (我感觉不用写的这麽麻烦,只用一个where判断不就完了)
select * from emp where deptno=10;
declare
cursor c2(eno emp.deptno%type) is select empno from emp where deptno=eno;--创建一个有参数的游标,将游标赋值where条件为deptno=传入的指定参数
en emp.empno%type;--用于接受员工号码
begin
open c2(10);--开启游标并传参,这个因为是含参游标,所以必须传参数
loop
fetch c2 into en;--将c2的数据foreach到en中(每个循环都类似一次重新赋值)
exit when c2%notfound;--退出条件为c2找不到东西为止
update emp set sal=sal+100 where empno=en;--将部门id等于c2的参数的员工的工资加100
commit;--提交事务
end loop;
close c2;--关闭就不必传参了
end;
7.储存过程
--存储过程:只是类似于java的方法,这里叫过程
--存储过程:春促过程就是提前已经编译高的一段pl/sql语言。防止再数据库端
----可以直接被调用,这一段pl/sql一般都是固定步骤的业务
--给指定员工工资涨100块钱
--创建 可修改 方法 方法名 (参数名 in/out 参数类型)
create or replace procedure p1(eno emp.empno%type) -- or replace 这个加上之后可以修改错误(就是允许替换方法内容)
is
begin
update emp set sal=sal+100 where empno=eno; --方法调用后会执行这里面的方法
commit;
end;
select * from emp where empno=7788;
declare
begin
p1(7788);--方法的调用
end;
8.储存函数
--存储函数
--通过储存函数实现计算指定员工的年薪
--储存过程和储存函数的参数都不能带长度
--储存函数和返回值类型不能带长度
--创建 可修改的 存储函数 函数名 (参数列表 默认in) 返回值类型
create or replace function f_yearssal(eno emp.empno%type)return number--创建存储函数
is
s number(10);--定义一个接收返回值的对象
begin
select sal*12+nvl(comm,0)into s from emp where empno=eno;-- 计算指定员工的年薪 nvl(comm,0)奖金 如果为null就用0代替,将查询的值存入s对象中
return s; //返回年薪
end;
declare
s number(10);--接收对象
begin
s := f_yearssal(7788);--执行方法,存储函数的返回值必须被接收
dbms_output.put_line(s);--打印返回值
end;
9.out类型参数如何使用
--out类型参数如何使用
-- in和out类型参数的区别是什么?
-- 凡是涉及到into查询语句赋值或者:=赋值操作的参数,都必须使用out来修饰
--使用储存过程来计算年薪
create or replace procedure p_yearsal(eno emp.empno%type,yearsal out number)--out修饰的参数在调用过程时不用传参,只需要设置一个对象取接收即可
is
s emp.sal%type;--接收年工资
c empm%type;--接收奖金
begin
select sal*12,nvl(comm,0) into s,c from emp where empno=eno;--查询年工资赋值给s 查询奖金并对null做化0处理然后赋值给c
yearsal:=s+c;--对out修饰的参数进行传值(类似于返回值)
end;
declare
yearsal number(10);--接收out修饰对象传来的值,这个参数名不必须要和过程默认参数名一样
begin
p_yearsal(7788,yearsal);--调用过程
dbms_output.put_line(yearsal);--打印接收的out参数
end;
10.储存过程和储存函数的区别
--储存过程和储存函数的区别
--语法区别:关键字不一样
-----------存储函数比存储过程多了两个return
--本质区别:存储函数有返回值,而存储过程没有返回值
-----------如果存储过程想实现有返回值的业务,我们就必须使用out类型的参数
-----------即使是存储过程使用了out类型的参数,其本质也不是有了返回值
-----------而是在存储过程内部给out类型参数赋值,在执行完毕之后,我们直接拿到输出类型参数的值
--我们可以使用存储函数有返回值的特性,来自定义函数
--而存储过程不能用来自定义函数
--案例需求:查询出员工姓名,员工所在部门名称
--案例准备工作:把scott用户下的dept表复制到当前用户下
create table dept as select * from scott.dept;
--使用传统方式来实现案例需求
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno;
--使用存储函数来实现提供一个部门编号,输出一个部门名称
create or replace function fdna(dno dept.deptno%type) return dept.dname%type
is
dna dept.dname%type;
begin
select d.dname
into dna
from dept d
where d.deptno=dno;
return dna;
end;
--使用fdna存储函数来实现案例需求:查询出员工姓名,员工所在部门名称
select e.ename,fdna(e.deptno)
from emp e;
11.触发器
--触发器:就是制定一个规则,在我们做增删改操作的时候
---只要满足该规则,自动触发,无需调用
---语句级触发器:不包含有for each row的触发器
---行级触发器:包含有for each row的就是行级触发器
-------------------加for each row是为了使用:old或者:new对象或者一行记录
--语句级触发器
---插入一条数据,输出一个新员工入职
create or replace trigger t1 --创建语句级触发器
after--在语句执行之后执行
insert--在insert语句执行这个触发器
on person--触发器作用于person表
declare
begin
dbms_output.put_line('有一位新员工加入');--触发器触发打印这段文字
end;
select * from person;
insert into person values(1,'小红','女');--插入操作
commit;
--行级触发器
--不能给员工降薪
create or replace trigger t2 -- 创建行级触发器
before--在语句执行之后执行
update--触发器作用在更新语句
on emp--触发器作用于emp表
for each row--行级触发器需要加这段,方便使用:old和:new (修改之前的旧对象,和修改之后的新对象)
declare
begin
if :old.sal>:new.sal then--判断如果工资降了触发下列异常
raise_application_error(-20001,'不能给员工降薪');-- raise_application_error(-20001~20999之间,'错误提示信息');
end if;
end;
update emp set sal=sal-1 where emp.empno=7788;--更新操作
commit;
select * from emp;
12.触发器实现主键的自增
--触发器实现主键自增。【行级触发器】
--分析:在用户做插入操作的之前,拿到即将插入的数据
-----给该数据中的主键列复制
create or replace trigger auid--创建行级触发器
before --在语句执行之后执行
insert --作用在插入语句
on person --作用在person表
for each row --添加这一段
declare
begin
select s_person.nextval into :new.pid from dual; --语句执行之前执行传序列下一个的值,然后付给修改后的对象的pid
end;
select s_person.nextval from dual;
select * from person;
insert into person values(1,'小兰','男');--这里无论pid传什么都对自增长没有影响
commit;
之前学的时候的一些笔记,欢迎参考
更多推荐
Oracle数据库入门(使用参考)
发布评论