oracle基础

来源：互联网发布：软件程序员编写编辑：程序博客网时间：2024/05/20 02:25

引言:oracle的最基础是件很容易的事情,但性能优化和表结构设计却绝不是一件容易的事

一.Oracle中常用增删改查操作

以scott用户

SQL> --当前用户
SQL> show user

SQL> --当前用户下的表
SQL> select * from tab;

SQL> --员工表的结构
SQL> desc emp

SQL> --清屏
SQL> host cls

SQL> --查询所有的员工信息
SQL> select * from emp;

SQL> --设置行宽
SQL> show linesize
linesize 80
SQL> set linesize 120

SQL> --设置列宽
SQL> col ename for a8
SQL> col sal for 9999

SQL> --查询员工信息：员工号姓名月薪
SQL> select empno,ename,sal
2 form emp;
form emp
*
第 2 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字
SQL> --c命令 change
SQL> 2
2* form emp
SQL> c /form/from --将form替换为from
2* from emp

SQL> /*
SQL> SQL中的null
SQL> 1、包含null的表达式都为null
SQL> 2、null永远！=null
SQL> */
SQL> --nvl(a,b) nvl2 nvl(null,0) 当为null值返回0 nvl2(a,b,c) 当为a为空值返回c,否则返回b
SQL> select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)
2 from emp;

null永远！=null

SQL> --distinct 去掉重复记录
SQL> select deptno from emp;

--concat函数 dual为虚表.mysql在5.0以后也有

select 'Hello'||' World' 字符串 from dual;

SQL> --查询员工信息：***的薪水是****
SQL> select ename||'的薪水是'||sal 信息 from emp;

SQL> --null值 3、如果集合中含有null，不能使用not in；但可以使用in
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3* where deptno not in (10,20,null)

SQL> --转意字符
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3* where ename like '%\_%' escape '\' ---用于查询名称中带_ 如ename 为 name_wpx的字段

--原因：null值最大 ,涉及null值的排序

1 select *
2 from emp
3 order by comm desc
4* nulls last

--oracle分页(Pageing Query)
=

select *
from (select rownum r,e1.*
from (select * from emp order by sal) e1
where rownum <=8
)
where r >=5;

SQL> /*
SQL> SQL的类型
SQL> 1、DML（Data Manipulation Language 数据操作语言）: select insert update delete
SQL> 2、DDL(Data Definition Language 数据定义语言): create table,alter table,truncate table,drop table
SQL> create/drop view,sequnece,index,synonym(同义词)
SQL> 3、DCL(Data Control Language 数据控制语言): grant(授权) revoke(撤销权限)
SQL> */
SQL> --插入insert

oracle的插入类似于原生jdbc的PreparedStatement,或者说类似于mybatis的Statement

SQL> --PreparedStatement pst = "insert into emp(empno,ename,sal,deptno) values(?,?,?,?)";
SQL> --地址符 &
SQL> insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);
输入 empno 的值: 1002
输入 ename 的值: 'Mary'
输入 sal 的值: 2000
输入 deptno 的值: 20
原值 1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)
新值 1: insert into emp(empno,ename,sal,deptno) values(1002,'Mary',2000,20)

当然你在要查询的字段也可这么设置

SQL> select empno,ename,sal,&t
2 from emp;
输入 t 的值: job

SQL> --一次性将emp中，所有10号部门的员工插入到emp10中
SQL> insert into emp10 select * from emp where deptno=10;

SQL> /*
SQL> 海量插入数据：
SQL> 1、数据泵（PLSQL程序）
SQL> dbms_datapump(程序包)
SQL> 2、SQL*Loader
SQL> 3、外部表
SQL> */
SQL> host cls

SQL> /*
SQL> delete和truncate的区别
SQL> 1、delete逐条删除；truncate先摧毁表再重建2
SQL> 2、（*）delete是DML truncate是DDL
SQL> (可以回滚) （不可以回滚）
SQL> 3、delete不会释放空间 truncate会
SQL> 4、delete会产生碎片 truncate不会
SQL> 5、delete可以闪回(flashback) truncate不可以

/*
SQL> Oracle中的事务
SQL> 1、起始标志: 事务中的第一条DML语句
SQL> 2、结束标志：提交：显式 commit 隐式：正常退出 DDL DCL
SQL> 回滚：显式 rollback 隐式：非正常退出掉电宕机
SQL> */

SQL> savepoint a; SQL> rollback to savepoint a;

SQL> set transaction read only;

二.函数类

1.单行函数
SQL> --字符函数
SQL> select lower('Hello Wpx') 转小写,upper('Hello Wpx') 转大写,initcap('hello wpx') 首字母大写
2 from dual;

SQL> --substr(a,b) 从a中，第b位开始取

SQL> --substr(a,b,c) 从a中，第b位开始取，取c位

SQL> --length 字符数 lengthb 字节数

SQL> --instr(a,b)
SQL> --在a中，查找b

SQL> --lpad 左填充 rpad 右填充
SQL> -- abcd ---> 10位
SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;

SQL> --trim 去掉前后指定的字符
SQL> select trim('H' from 'Hello WorldH') from dual;

SQL> --replace He**o Wor*d 将字符中的l替换为*
SQL> select replace('Hello World','l','*') from dual;

SQL> --四舍五入 45.93 45.9 46 50 0
SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,round(45.926,-1) 四,round(45.926,-2) 五
2 from dual;

SQL> --截断 45.92 45.9 45 40 0

1 select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,trunc(45.926,-1) 四,trunc(45.926,-2) 五
2* from dual

日期类常用转化

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

SYSDATE2009-6-16 15:25:10　TRUNC(SYSDATE)2009-6-16　TO_CHAR(SYSDATE,'YYYYMMDD')20090616到日TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS')20090616 15:25:10到秒TO_CHAR(SYSTIMESTAMP,'YYYYMMDD HH24:MI:SS.FF3')20090616 15:25:10.848到毫秒TO_CHAR(SYSDATE,'AD')公元　TO_CHAR(SYSDATE,'AM')下午　TO_CHAR(SYSDATE,'BC')公元　TO_CHAR(SYSDATE,'CC')21　TO_CHAR(SYSDATE,'D')3老外的星期几TO_CHAR(SYSDATE,'DAY')星期二星期几TO_CHAR(SYSDATE,'DD')16　TO_CHAR(SYSDATE,'DDD')167　TO_CHAR(SYSDATE,'DL')2009年6月16日星期二　TO_CHAR(SYSDATE,'DS')2009-06-16　TO_CHAR(SYSDATE,'DY')星期二　TO_CHAR(SYSTIMESTAMP,'SS.FF3')10.848毫秒TO_CHAR(SYSDATE,'FM')　　TO_CHAR(SYSDATE,'FX')　　TO_CHAR(SYSDATE,'HH')03　TO_CHAR(SYSDATE,'HH24')15　TO_CHAR(SYSDATE,'IW')25第几周TO_CHAR(SYSDATE,'IYY')009　TO_CHAR(SYSDATE,'IY')09　TO_CHAR(SYSDATE,'J')2454999　TO_CHAR(SYSDATE,'MI')25　TO_CHAR(SYSDATE,'MM')06　TO_CHAR(SYSDATE,'MON')6月　TO_CHAR(SYSDATE,'MONTH')6月　TO_CHAR(SYSTIMESTAMP,'PM')下午　TO_CHAR(SYSDATE,'Q')2第几季度TO_CHAR(SYSDATE,'RM')VI 　TO_CHAR(SYSDATE,'RR')09　TO_CHAR(SYSDATE,'RRRR')2009　TO_CHAR(SYSDATE,'SS')10　TO_CHAR(SYSDATE,'SSSSS')55510　TO_CHAR(SYSDATE,'TS')下午 3:25:10　TO_CHAR(SYSDATE,'WW')24　TO_CHAR(SYSTIMESTAMP,'W')3　TO_CHAR(SYSDATE,'YEAR')TWO THOUSAND NINE　TO_CHAR(SYSDATE,'YYYY')2009　TO_CHAR(SYSTIMESTAMP,'YYY')009　TO_CHAR(SYSTIMESTAMP,'YY')09

SQL> --昨天今天明天
SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天
2 from dual;

SQL> --计算员工的工龄：天星期月年
SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,
2 (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年
3 from emp;

SQL> --months_between
SQL> select ename,hiredate,(sysdate-hiredate)/30 一,months_between(sysdate,hiredate) 二
2 from emp;

SQL> --查询员工的薪水：两位小数、千位符、本地货币代码
SQL> select to_char(sal,'L9,999.99') from emp;

SQL> --coalesce 从左到右找到第一个不为null的值
SQL> select comm,sal,coalesce(comm,sal) "第一个不为null的值"
2 from emp;

SQL> select ename,job,sal 涨前,
2 case job when 'PRESIDENT' then sal+1000
3 when 'MANAGER' then sal+800
4 else sal+400
5 end 涨后
6 from emp;

SQL> select ename,job,sal 涨前,
2 decode(job,'PRESIDENT',sal+1000,
3 'MANAGER',sal+800,
4 sal+400) 涨后
5 from emp;

2.2多行函数SQL> --工资总额
SQL> select sum(sal) from emp;

SQL> --人数
SQL> select count(*) from emp;

SQL> --平均工资
SQL> select sum(sal)/count(*) 一,avg(sal) 二 from emp;

SQL> --null值组函数会自动滤空； count(comm) 14
SQL> select count(*), count(nvl(comm,0)) from emp; --15

SQL> --多个列的分组
SQL> select deptno,job,sum(sal)
2 from emp
3 group by deptno,job
4 order by 1;

SQL> --多个列的分组: 先按照第一个列分组，如果相同，再第二个列分组，以此类推
SQL> --查询平均工资大于2000的部门
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 having avg(sal) > 2000;

SQL> --where和having的区别：where不能使用多行函数
SQL> --查询10号部门的平均工资
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 having deptno=10;

使用oracle的增强group by做报表

SQL> break on deptno skip 2 --美化
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

三.多表查询

常用公式:select 要查询的内容 from 要查询的位置 where 消笛卡尔积 + 查询条件

上述文字部分可由其他查询条件替代

SQL> --等值连接

SQL> select e.empno,e.ename,e.sal,d.dname
2 from emp e,dept d
3 where e.deptno=d.deptno;

SQL> --不等值连接
SQL> --查询员工信息：员工号姓名月薪工资级别

SQL> select e.empno,e.ename,e.sal,s.grade
2 from emp e,salgrade s
3 where e.sal between s.losal and s.hisal;

SQL> --外连接：
SQL> --按部门统计员工信息：部门号部门名称人数
SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
2 from emp e,dept d
3 where e.deptno=d.deptno
4 group by d.deptno,d.dname;

SQL> /*
SQL> 希望把某些不成立的记录（40号部门），任然包含在最后的结果中 ---> 外连接
SQL> 左外连接: 当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含在最后的结果中
SQL> 写法:where e.deptno=d.deptno(+)
SQL> 右外连接: 当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含在最后的结果中
SQL> 写法:where e.deptno(+)=d.deptno
SQL> */
SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
2 from emp e,dept d
3 where e.deptno(+)=d.deptno
4 group by d.deptno,d.dname;

SQL> --自连接: 通过表的别名，将同一张表视为多张表
SQL> select e.ename 员工姓名,b.ename 老板姓名
2 from emp e,emp b
3 where e.mgr=b.empno;

SQL> --自连接不适合操作大表
SQL> --层次查询
SQL> select level,empno,ename,mgr
2 from emp
3 connect by prior empno=mgr
4 start with mgr is null
5 order by 1;

子查询

SQL> --子查询所要解决的问题：不能一步求解
SQL> select *
2 from emp
3 where sal > (select sal
4 from emp
5 where ename='SCOTT');

SQL> /*
SQL> 注意的问题：1
SQL> 1、括号
SQL> 2、合理的书写风格
SQL> 3、可以在主查询的where select having from 后面使用子查询
SQL> 4、不可以在group by使用子查询
SQL> 5、强调from后面的子查询
SQL> 6、主查询和子查询可以不是同一张表；只有子查询返回的结果主查询可以使用即可
SQL> 7、一般不在子查询中排序；但在top-n分析问题中必须对子查询排序
SQL> 8、一般先执行子查询，再执行主查询；但相关子查询例外
SQL> 9、单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
SQL> 10、子查询中的null
SQL> */

SQL> --可以在主查询的where select having from 后面使用子查询
SQL> select empno,ename,sal,(select job from emp where empno=7839) 第四列
2 from emp;

SQL> --查询员工信息：员工号姓名月薪
SQL> select *
2 from (select empno,ename,sal from emp);

主查询和子查询可以不是同一张表；只有子查询返回的结果主查询可以使用即可
SQL> --查询部门名称是SALES的员工
SQL> select *
2 from emp
3 where deptno=(select deptno from dept where dname='SALES');

SQL> --SQL优化 3、尽量使用多表查询
SQL> --SQL执行计划
SQL> host cls

SQL> --in 在集合中
SQL> --查询部门名称是SALES和ACCOUNTING的员工
SQL> select *
2 from emp
3 where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');

SQL> select e.*
2 from emp e,dept d
3 where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');

SQL> --any: 和集合中的任意一个值比较
SQL> --查询工资比30号部门任意一个员工高的员工信息
SQL> select *
2 from emp
3 where sal > any (select sal from emp where deptno=30);

1 select *
2 from emp
3* where sal > (select min(sal) from emp where deptno=30)

SQL> select *
2 from emp
3 where empno not in (select mgr from emp);
未选定行

SQL> --查询是老板的员工信息
SQL> ed
已写入 file afiedt.buf

1 select *
2 from emp
3* where empno in (select mgr from emp)

SQL> select *
2 from emp
3 where empno not in (select mgr from emp where mgr is not null);

四.创建管理表

SQL> --rowid 行地址
SQL> select rowid,empno,ename,sal from emp;

SQL> create table emp20
2 as
3 select * from emp where deptno=20;

SQL> --修改表：增加新列，修改列，删除列，重命名列，重命名表

SQL> --增加新列
SQL> alter table test1 add photo blob;

SQL> --修改列
SQL> alter table test1 modify tname varchar2(40);

SQL> --删除列
SQL> alter table test1 drop column photo;

SQL> --重命名列
SQL> alter table test1 rename column tname to username;

SQL> --重命名表
SQL> rename test1 to test2;

SQL> --查看回收站
SQL> show recyclebin;

SQL> --清空回收站
SQL> purge recyclebin;

SQL> --注意：管理员没有回收站

五.简单pl

--打印Hello World
declare
--说明部分
begin
--程序
dbms_output.put_line('Hello World');
end;
实际上,一个plsql还应该有Exception部分

if 判断

-- 判断用户从键盘输入的数字--接受键盘输入--变量num：是一个地址值，在该地址上保存了输入的值accept num prompt '请输入一个数字';declare   --定义变量保存输入 的数字  pnum number := #begin  if pnum = 0 then dbms_output.put_line('您输入的是0');     elsif pnum = 1 then dbms_output.put_line('您输入的是1');     elsif pnum = 2 then dbms_output.put_line('您输入的是2');     else dbms_output.put_line('其他数字');  end if;end;

循环loop

-- 打印1~10declare   -- 定义变量  pnum number := 1;begin  loop    --退休条件    exit when pnum > 10;        --打印    dbms_output.put_line(pnum);    --加一    pnum := pnum + 1;  end loop;end;

记录性变量

--记录型变量: 查询并打印7839的姓名和薪水declare  --定义记录型变量：代表一行  emp_rec emp%rowtype;begin  select * into emp_rec from emp where empno=7839;    dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);end;

引用形变量

--引用型变量: 查询并打印7839的姓名和薪水declare  --定义变量保存姓名和薪水  --pename varchar2(20);  --psal   number;  pename emp.ename%type;  psal   emp.sal%type;begin  --得到7839的姓名和薪水  select ename,sal into pename,psal from emp where empno=7839;  --打印  dbms_output.put_line(pename||'的薪水是'||psal);end;

系统异常

-- 被0除declare   pnum number;begin  pnum := 1/0;  exception  when zero_divide then dbms_output.put_line('1:0不能做分母');                        dbms_output.put_line('2:0不能做分母');  when value_error then dbms_output.put_line('算术或者转换错误');                        when others then dbms_output.put_line('其他例外');end;

自定义异常

-- 查询50号部门的员工declare   cursor cemp  is select ename from emp where deptno=50;  pename emp.ename%type;    --自定义例外  no_emp_found exception;begin  open cemp;    --取第一条记录  fetch cemp into pename;  if cemp%notfound then    --抛出例外    raise no_emp_found;  end if;    --进程：pmon进程(proccesss monitor)  close cemp;exception  when no_emp_found then dbms_output.put_line('没有找到员工');  when others then dbms_output.put_line('其他例外');end;

关于plsql的游标类似于原生的jdbc取值

-- 查询并打印员工的姓名和薪水/*光标的属性： %isopen   %rowcount(影响的行数)             %found    %notfound*/declare    --定义光标（游标）   cursor cemp is select ename,sal from emp;   pename emp.ename%type;   psal   emp.sal%type;begin  --打开  open cemp;  loop       --取当前记录       fetch cemp into pename,psal;       --exit when 没有取到记录;       exit when cemp%notfound;              dbms_output.put_line(pename||'的薪水是'||psal);  end loop;  --关闭  close cemp;end;

例一

/*1、SQL语句select to_char(hiredate,'yyyy') from emp;---> 集合 ---> 光标 ---> 循环---> 退出: notfound2、变量：（*）初始值  （*）最终如何得到每年入职的人数count80 number := 0;count81 number := 0;count82 number := 0;count87 number := 0;*/declare    --定义光标   cursor cemp is select to_char(hiredate,'yyyy') from emp;   phiredate varchar2(4);      --每年入职的人数  count80 number := 0;  count81 number := 0;  count82 number := 0;  count87 number := 0;begin  --打开光标  open cemp;  loop    --取一个员工的入职年份    fetch cemp into phiredate;    exit when cemp%notfound;        --判断年份是哪一年    if phiredate = '1980' then count80:=count80+1;       elsif phiredate = '1981' then count81:=count81+1;       elsif phiredate = '1982' then count82:=count82+1;       else count87:=count87+1;     end if;  end loop;    --关闭光标  close cemp;    --输出  dbms_output.put_line('Total:'||(count80+count81+count82+count87));  dbms_output.put_line('1980:'|| count80);  dbms_output.put_line('1981:'|| count81);  dbms_output.put_line('1982:'|| count82);  dbms_output.put_line('1987:'|| count87);end;

例二

/*1、SQL语句selet empno,sal from emp order by sal;---> 光标  ---> 循环  ---> 退出：1. 总额>5w   2. notfound2、变量：（*）初始值  （*）最终如何得到涨工资的人数: countEmp number := 0;涨后的工资总额：salTotal number;(1)select sum(sal) into salTotal from emp;(2)涨后=涨前 + sal *0.1练习： 人数：8    总额:50205.325*/declare    cursor cemp is select empno,sal from emp order by sal;    pempno emp.empno%type;    psal   emp.sal%type;        --涨工资的人数:     countEmp number := 0;    --涨后的工资总额：    salTotal number;begin    --得到工资总额的初始值    select sum(sal) into salTotal from emp;        open cemp;    loop         -- 1. 总额 >5w         exit when salTotal > 50000;         --取一个员工         fetch cemp into pempno,psal;         --2. notfound         exit when cemp%notfound;                  --涨工资         update emp set sal=sal*1.1 where empno=pempno;         --人数+1         countEmp := countEmp +1;         --涨后=涨前 + sal *0.1         salTotal := salTotal + psal * 0.1;    end loop;    close cemp;        commit;    dbms_output.put_line('人数：'||countEmp||'    总额:'||salTotal);end;

例三

/*1、SQL语句部门：select deptno from dept; ---> 光标部门中员工的薪水: select sal from emp where deptno=?? ---> 带参数的光标2、变量：（*）初始值  （*）最终如何得到每个段的人数count1 number; count2 number; count3 number;部门的工资总额salTotal number := 0;(1)select sum(sal) into salTotal  from emp where deptno=??(2)累加*/declare  --部门  cursor cdept is select deptno from dept;  pdeptno dept.deptno%type;    --部门中员工的薪水  cursor cemp(dno number) is select sal from emp where deptno=dno;  psal emp.sal%type;    --每个段的人数  count1 number; count2 number; count3 number;  --部门的工资总额  salTotal number := 0;begin  --部门  open cdept;  loop       --取一个部门       fetch cdept into pdeptno;       exit when cdept%notfound;              --初始化       count1:=0; count2:=0; count3:=0;       --得到部门的工资总额       select sum(sal) into salTotal  from emp where deptno=pdeptno;              --取部门的中员工薪水       open cemp(pdeptno);       loop            --取一个员工的薪水            fetch cemp into psal;            exit when cemp%notfound;                        --判断            if psal < 3000 then count1:=count1+1;               elsif psal >=3000 and psal<6000 then count2:=count2+1;               else count3:=count3+1;            end if;       end loop;       close cemp;       --保存结果       insert into msg values(pdeptno,count1,count2,count3,nvl(saltotal,0));  end loop;  close cdept;    commit;  dbms_output.put_line('完成');  end;

每当成功插入新员工后，自动打印“成功插入新员工”

create trigger firsttrigger
after insert
on emp
declare
begin
dbms_output.put_line('成功插入新员工');
end;

阅读全文

0 0