2021 Oracle 基本操作一站式学习

Scott（自带表学习适合初学者）

开启scott

解锁scott

alter user scott account unlock --解锁scott用户
alter user scott identified by tiger --解锁scott密码 tiger 可以自定义

登录scott后可以看见4个表

对表右键 =>View 选择columns可以查看列

序列的使用

创建序列

create sequence s_表名 --序列默认从1开始,依次递增,主要给主键赋值

拿取序列下一个值(新创建序列直接拿当前值会报错)

select s_test.nextval from dual

拿取序列当前值

select s_test.currval from dual

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

字符函数

select upper('java') from dual --转换大写
select lower('JAVA') from dual -- 转换小写

数值函数

select round(12.12,1) from dual --四舍五入，第二个参数是保留位数（可以不写，不保留小数点）
select round(52.12,-2) from dual --100 --5四舍五入
select round(52.12,-1) from dual --50 --2四舍五入
select trunc(52.2,-2) from dual-- 与round相似但直接截取不看后面位数
select mod(10,3) from dual --求余

日期函数

--明天此时此刻
select sysdate+1 from dual
--查询入职到至今天数
select sysdate-emp.hiredate from emp
--查询入职到至今几周
select (sysdate-emp.hiredate)/7 from emp
--查询入职距离现在几个月
select months_between(sysdate,emp.hiredate)  from emp
--查询入职距离现在几年
select months_between(sysdate,emp.hiredate)/12  from emp

转换函数

--date转字符串 加fm去除0
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss')  from dual 
-- 字符串转日期
select to_date('2021-1-16 15:32:14','fm yyyy-mm-dd hh24:mi:ss')  from dual

通用函数函数

--nvl 如果是null取第二个参数否则是他本身
select nvl(null,0) from dual

条件表达式

--通用写法 mysql 和oracle 通用
--如果是SMITH 替换成中文
select emp.ename,
       case 
         emp.ename
         when 'SMITH' then '史密斯'
           else 
             emp.ename
             end 
 from emp
 --如果是区间需要这样写 when ..then 可以无限
 select emp.sal , 
        case 
          when emp.sal>3000 then '高薪'
            else
              '低薪'
              end
 from emp
 
 
 ----oracle专用  同上替换中文名一样的效果
 
 select emp.ename ,decode(
        emp.ename,
        'SMITH','史密斯',
        emp.ename
 )
 from emp

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

通用函数

 --查询总数量
 select count(1) from emp
 --查询工资总和
 select sum(sal) from emp
 --查询最高工资
 select max(sal) from emp
 --查询最低工资
 select min(sal) from emp
 --查询工资平均数
 select avg(sal) from emp

分组函数

where 在group by 之前计算 having在group by 之后计算

--查询各部门平均工资
select deptno,avg(sal) from emp 
group by emp.deptno 
--查询平均工资高于2000的部门 先算分组后的平均工资 再筛选平均值大于2000的
select deptno,avg(sal) from emp
group by deptno 
having avg(sal)>2000
--查询员工工资大于800的各部门平均工资  先筛选工资大于200再参与分组
select deptno,avg(sal)  from emp 
where emp.sal>800
group by emp.deptno
--先查出部门员工工资大于800的再查询平均工资大于2000的部门
select deptno,avg(sal) from emp
where sal>800
group by deptno
having avg(sal)>2000

select nvl(null,0) from dual

内连接，外连接

内连接

--算是笛卡尔积内连接
select * from emp,dept
where emp.deptno = dept.deptno
--left join 
select * from dept
inner join emp on dept.deptno = emp.deptno

外连接

--left join 左边是主表  right join右边是主表
select * from dept
left join emp on emp.deptno= dept.deptno 

--oracle 专用外连接 (+)加右边是左连接   (+)加左边是又连接
select * from dept,emp
where dept.deptno = emp.deptno(+)

自连接

--自连接  查询员工姓名，部门名称，领导姓名，领导部门
select e.ename,d.dname,e2.ename,d2.dname from emp e,emp e2 ,dept d,dept d2
where e.mgr = e2.empno
and e.deptno = d.deptno
and d2.deptno = e2.deptno
order by e.ename asc

子查询

--子查询查找工资最低人的姓名，工资，部门，部门编号
select d.deptno,d.dname,e.ename,e.sal from
(select deptno,min(sal)sal from emp group by deptno) ds,emp e,dept d
where ds.sal = e.sal and ds.deptno = d. deptno

分页查询

rownum:每次查询时前面的行号，做分页查询必不可少 （根据查询条数递增从1开始）
例如：我查询前三名 只需要rownum>4

此时我们只需要再套一层 select * from 那我们又可以拿到一组顺序正常的行号

注意：rownum 不能大于一个正数 即 rownum>5 因rownum是从1开始依次递增

以上作为理解！实际rownum不是0开始
最终获取前三行数据

select rownum,e.* from 
(select * from emp order by sal desc) e
where rownum <4 

正式分页 ：查询第二页，每页三条

公式带入
例如：pageno (页数)= 1 pagesize(每页大小) 自行带入理解

(pageno-1)pagesize 开始行
pagenopagesize+1 结束行

select * from (
select rownum rn,e.* from 
(select * from emp order by sal desc) e
where rownum <7 
)where rn >3

视图

试图就是提供一个查询的窗口，所有数据来自于原表
注意：创建视图必须有dba权限
准备工作

查询语句创建表

--从 scott用户创建表
create table emp as select * from scott.emp

创建视图

create view v_emp  as select * from emp --with read only  后面加则创建只读视图

对视图进行增删改查

--查询视图
select * from v_emp
--修改视图【不推荐】
update v_emp set job ='xxx' where empno = 7369;
commit;
--和普通增删改一样其他省略

那视图有什么用呢？

1. 可以选择性的屏蔽一些字段根据查询语句来创建视图
2. 同是保证总部和分部数据统一（视图改变也会改变原表，反之也一样）

索引

可以增加查询速度，但会影响增删改
单列索引
关键字：index

创建单例索引 (单行函数和模糊搜索都会影响索引)
create index 索引名 on 表名（列名）

复合索引

创建复合索引（or不会触发索引）
create index 索引名 on 表名（列名1，列名2）

create index emp_id_name on emp(id,name)
select * from emp where id ='1' and name ='xx' --触发复合索引
select * from emp where id ='1' or name ='xx'	--不触发索引
select * from emp where id ='1' 	--触发单例索引

PL/SQL编程

定义变量

declare 
  i number(2) :=1; --定义数值变量i  := 赋值  2是长度 
  str varchar2(10) := 'zzz'; --定义字符变量
  ena emp.ename%type; --引用emp表中ename的类型 引用型变量
  emprow emp%rowtype;--记录型变量 存一行数据 类似对象
begin --类似java的左括号 {
  dbms_output.put_line(i); --输出语句
  dbms_output.put_line(str);
  select ename into ena from emp where ename = '张三'; --查询语句给ena 赋值
end; --类似java的右括号 }

if 判断

declare
  i number(2) := &i;  --&i 输入一个值
begin
  if i<18 then  --if 
    Dbms_Output.put_line('小年轻');
  elsif i<40 then  --坑点： java中else if  此处elsif  是els不是else
    dbms_output.put_line('壮士');
  else
    dbms_output.put_line('老了老了');
  end if;--结束if
end;

三种 loop 循环

while循环

declare
  i number(2) := 1;
begin
  while i < 11 loop  --while循环条件
    dbms_output.put_line(i);
    i := i+1; --相当于 i++
    end loop;
end;

exit循环

declare
  i number(2) :=1;
begin
  loop
    exit when i>10;  --满足跳出循环
    dbms_output.put_line(i);
    i := i+1; --i++
    end loop;  
end;

for循环

declare
  i number(2) :=1;
begin
  for i in 1..10 loop --条件   好难用啊
  dbms_output.put_line(i);
  end loop;
end;

游标

可以存放多个对象，多行记录
语法：cursor 游标名称 is 查询语句

declare
  cursor c1 is select * from 表名; --给游标赋值
  motyperow 表名%rowtype;--定义类型
begin
  open c1;--打开游标
  loop
    fetch c1 into motyperow;--遍历游标赋值给 into后面的属性
    exit when c1%notfound; -- 游标为空退出
    dbms_output.put_line(motyperow.seg);--也可以在此处写增删改
  end loop;
  close c1;
end;

存储过程

存储过程：把一段写好的PL/SQL放到数据库中，后续直接调用
语法：

--or replace 可以替换已经存在的 []要去掉里面表示可写可不写
--在视图(VIEW)中只能用AS不能用IS；
--在游标(CURSOR)中只能用IS不能用AS。
create [or replace] PROCEDURE 过程名 [(参数名 in/out 数据类型)] --不写默认in
  AS/is --as 或者 is
  begin
  	plsql子程序体
  end;

注意： 参数类型不能加长度！！！
创建存储过程并使用

create or replace PROCEDURE pagetest (fab xxx.plant%type) --不写默认in
  is 
  begin
    dbms_output.put_line(fab);
  end;

declare

begin
  pagetest('A栋');--使用
end;

out参数的使用

create or replace PROCEDURE  pageOutTest (str varchar2,strappend out varchar2)
is
begin
  strappend := str || '存储过程加BUFF';
end;

--使用
declare
strappend varchar2(50);
begin
  pageOutTest('test',strappend);--执行完会把out出来的值 赋值给我上面定义的strappend
  dbms_output.put_line(strappend);
end;

存储函数

语法：

create or replace function 函数名(name in type，name in type...) return 数据类型 is 结果变量 数据类型；
begin
	return （结果变量）；
end 函数名；

创建并使用

create or replace function pageFunctionTest(str varchar2) return varchar2
is strappend varchar2(50);
begin
  strappend := str || '给你加个BUFF';
  return strappend;
end;

--使用
declare
strappend varchar2(50);
begin
  strappend := pageFunctionTest('A栋');
  dbms_output.put_line(strappend);
end;

注意：返回值必须定义一个变量接收
总结：
不难看出存储过程和存储函数好像没有什么区别都可以实现上面的功能，但是存储函数是有返回值的，那么我们就可以自定义函数了，例如：nvl(param1,param2),sum(param)等。
存储过程本质是没有返回值的所以无法自定义函数

触发器

触发器：触发器就是定制一个规则，再增删改的时候，满足规则自动触发
触发器又分两种：

1. 语句级触发器： 不包含 for each row 的就是语句级触发器
2. 行级触发器 包含 for each row 的就是行级触发器
   加 for each row 是为了使用 :lod 和 :new 对象（一行记录）

通过触发器自增id

create or replace trigger insertpagezhutest
 before --after之后触发  before之前触发
 insert --插入
 on pagezhutest --表名
 for each row --行级触发器
declare
 autoid int;
begin
  select s_motype.nextval into :new.id from dual;--把序列作为主键
  select s_motype.currval into autoid from dual;
 dbms_output.put_line('新增的id是'|| autoid);

end insertpagezhutest;

完结撒花！