Oracle数据库入门(使用参考)

admin管理员组

文章数量:1794759

Oracle数据库入门(使用参考)

Oracle数据库入门(使用参考) 零、简介

Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小微机环境。它是一种高效率的、可靠性好的、适应高吞吐量的数据库方案。

ORACLE数据库系统是美国ORACLE公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只要在一种机型上学习了ORACLE知识，便能在各种类型的机器上使用它。

一、与mysql对比

内容挺多的，请参考这篇融合怪文章 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