oracle知识大全。复习oracle知识点01.。。。。基本语句。。。。。存储函数

oracle

修改列的类型

alter table person modify gender char(1)；

修改列的名称

alter table person rename column gender to sex;

删除列

alter table person drop column sex;

查询表中记录

select * from person；

插入数据

insert into person (pid,pname) values (1,‘xiaoming’)；

commit提交事务

删除表中的全部的记录

delete from persom;

删除表结构

drop table person;

删除表，后去创建表，。等同删除表结构。如果数据量大，特别是有索引，效率高。

truncate talbe person;

序列不属于任何一张表，但是可以逻辑和表做绑定。

默认从1开始，依次递增，主要用来给主键赋值使用。

dual;虚表，为了补全语法，没有任何意义。

create sequence s_person

select s_person.currval from dual.

scott 用户，密码tiger。

初学使用，解锁用户

解锁用户

alter user scott account unlock;

解锁密码，也可以改密码

alter user scott identified by tiger;

-查询：

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

–字符函数

select upper(“yes”) from dual;–YES,小写变大写

select lower(“YES”) from dual;–yes大写变小写

–数值函数

–四舍五入，后面的参数表示保留的位数，

select round (26.16,1) from dual;–26.2

select round (26.14,1 ) from dual:–26.1

select round (26.14,-1 ) from dual:30

select round (26.14,-2 ) from dual:0

select round (56.14,1-2) from dual:100

–直接截取，不在看后面的位数是否大于5

select trunc（56.16，-1）from dual；56.1

–求余数

select mod（10,3）from daul；1

–日期函数

–算出明天此刻

select sysdate+1 from dual;

–查询出emp表中所有员工入职距离现在几天。

select sysdate-e.hiredate from emp e;

–查询出emp表中所有员工入职距离现在几月。

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;

–转换函数

–日期转字符串

select to_char(sysdate，‘fm yyyy-mm-dd hh24-mi-ss’) from dual;fm把02改为2，24表示24小时。mi区别mm

–字符串转日期

select to-date(‘2019-6-7 16:39:52’,‘fm yyyy-mm-dd hh24:mi:ss’) from dual;

–通用函数

–算出emp表中所有员工的年薪：工作+年薪

–奖金里面有null，如果null值和任意数字做算术运算，结果都是null,如果nvl把null改为0

select e.sal*12+nvl(e.comm ,0)from emp e;

–条件表达式

–用通用的表达式，oracle和mysql。

–给emp表中员工起中文名，等值判断不用e.ename=‘guojun’

–把要的值取名字。else去掉，其他值不要

select e.ename ,

​ case e.ename

​ when ‘SMiTH’ then ‘小米’

​ when ’ guojun’ then ‘小姐姐’

​ else ‘无名’

​ end

from emp e;

–判断工资等级

select e.sal,

​ case e.sal

​ when e.sal>8000 then ‘高收入’

​ when e.sal>3000 then ‘中收入’

​ else ‘低收入’

​ end

from emp e；

–oracle中除了起别名，都用单引号

–oracle专用条件表达式

select e.ename,

​ decode(e.ename,

​ ‘SMITH’,’ 小米’,

​ ‘ALLEN’, ‘大耳机’,

​ ‘WARN‘, ’诸葛西欧阿尔‘,

​ ’无名‘

​ )“中文名”

from emp e;

​

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

–查询总数量

select count (1) from emp:1第1列

–工资总和

select sum(sal) from emp;

–最大工资

select max(sal) from emp;

–最低工资

select min(sal) from emp;

–平均工资

select avg(sal) from emp;

–分组查询

—查询出每一个部门的平均工资

–分组查询中，出现在group by后面的原始列，才能出现在select后面

–没有出现在group by后面的列，想在select后面。必须加上聚合函数

–所有的条件都不能使用别名来判断：select ename ,sal s from emp where s>1500(错误不能s>1500.)

select e.deptno,avg(e.sal),e.ename

from emp e

group by e.deptno;

—查询平均工资高于2000的部门信息

select e.deptno, avg(e.sal) asal

from emp e

group by e.deptno

having asal>2000;

–查询出每一个部门高于888的员工的平均工资

–where是过滤分组前的数据，having是过滤分组后的数据。

–表现形式：where必须在group by之前，having 是在group by之后。

–然后在查询出平均工资高>2000的部门

select e.deptno ,avg(e.sal) asal

from emp e

where e.sal>888

group by e.deptno;

–多表查询中的一些概念

–笛卡尔积（没有值多，一般不常用）

select *

from emp e,dept d;

–等值连接（ 隐性连接 ）

select *

from emp e,dept d

where e.deptno=d.deptno;

–内连接(等效于等值连接，后面出现等值连接。而常用等值连接)连接中的条件是找连接对象。where条件是找查询后的符合条件的数据。

select *

from emp inner join dept d

on e.deptnoe=d.deptnoe;

–查询出所有的部门，以及部门下的员工信息。【外连接】

–右连接（右边数据保存）

select *

from emp e right join dept d

on e.deptno =d.deptno;

–左连接（左边数据保存）

select *

from emp e left join dept d

on e.deptno=d.deptno;

–oracle中的专用外连接(和上面的外连接一样，一般选通用的)

select *

from emp e,dept d

where e.deptno(+)=d.deptno;

–查询出员工姓名，员工领导姓名

–自连接。

select e1.ename.e2,ename

from emp el,emp e2

where e1.mgr=em.empno;

–查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称

select e1.ename,d.dname,e2.ename,d.dname

from emp e1,emp e2 ,dept d1,dept d2

where e1,mgr=e2.empno

and el.deptno=d1.deptno

and e2.deptno=d2.deptno;

–子查询

–子查询返回一个值

–查询出工资和SCOTT一个的员工的

用=有隐患，如果ename不唯一，则返回是一个集合，就有问题。用in保险。

select * from emp where sal=

(select sal from emp where ename=‘SCOTT’)

–子查询返回一个集合

–查询出工资和10号部门任意一个一样的员工信息

select * from emp where sal in

(select sal from emp where deptno=10;

–子查询返回一张表

–查询出每一个部门最低工资，和最低工资一个工资，和该员工所在部门名称

–1，先查询出每一个部门最低工资

select deptno,min(sal)

from emp

group by deptno;

–2,三表联查，得到最终结果

select t.deptno,t.msal.e.ename,d.dname

from （select deptno,min(sal)

from emp

group by deptno）t,emp e,dept d

where t,deptno=e,deptno

and t.msal=e.sal

and e.deptno=d.deptno;

–oracle中的分页

–rownum行号，当我们做select操作的时候，每查询出一行记录，就会在该行上加上一个行号，

–行号从1开始，依次递增，不能跳着走。

–emp表工资倒叙排序后。每页五条记录，查询第二页。rownum>5这个条件不可以

–排序操作会影响rownum的顺序,因为先查询后排序，rownum乱了。

–如果涉及到排序，但是还要增加rownum的话，我们可以再次嵌套查询。

select rownum，t.* from(

select rownum,e.* from emp e by e.sal desc) t;

–emp表工资倒叙排序后，每页五条记录，查询第二页。

–rownum行号不能写上大于一个正数（0可以，没有意义)

select * from(

select rownum,e.* from(

​ select * from emp order by sal desc

​ )e where rownum<11

)where rn>5

–视图

–视图的概念：视图就是提供一个查询的窗口，所有数据来自于原表。

–查询语句创建表

create table emp as select * from scott.emp;

select * from emp;

–创建视图【必须有dba权限】

create view v_emp as select enmae,job from emp;

–查询视图

select * from v_emp;

–修改视图[不推荐]

update v_emp set job=‘CLERK’ where ename=‘ALLEN’;

commit;

–创建之读视图

create view v_emp1 as select ,job from emp with read only;

–视图的作用？

–第一：视图可以屏蔽掉一些敏感字段。

–第二：保证总部和分部数据及时统一。

–索引

–索引的概念：索引就是在表的列上构建一个二叉树

–达到大幅度提高查询效率的目的，但是索引会影响增删改的效率。

–单列索引

–创建单列索引

create index idx_ename on emp(ename);

–单列索引触发规则，条件必须是索引列中的原始值。

–单列索引，模糊查询，都会影响索引的触发。

select * from emp where ename=‘scott’

–复合索引

–创建复合索引

create index idx_enamejob on emp(ename,job);

–复合索引中第一列为优先检索列

–通过要触发复合索引，必须包含有优先检索列中的原始值。

select * from emp where ename=‘scott’ and job=‘xx’;触发复合索引

select * from emp where ename=‘scott’ or job=’xx‘；不触发索引，因为or实际上是两个查询语句，一个是单列索引触发，一个没有索引。结果就没有索引。

select * from emp where ename=‘soctt’ ;触发单列索引

–pl/sql编程语言

–pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性。

–pl/sql编程语言比一般的过程化编程语言，更加有灵活高效。

–pl/sql编程语言主要用来编写存储过程和存储函数等。

–声明方法

定义变量

赋值操作可以使用：=也可以使用into查询语句赋值

一般情况用number，int不可以表示小数

declare

​ i number(2) ：=10；

​ s varchar2(10):=‘xiaoming’;

​ 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 empo=7788;

​ dbms_output.put_line(ena);

​ select * into emprow from emp where empo=7788;

​ dbms_output.put_line(emprow.ename || ‘的工作为：’||empro.job)

end;

控制台打印：

19

xiaoming

scott

scott的工作为：sht

–pl/sql中的if判断

–输入小于18的数字，输出未成年

–输入大于18小于40的数字，输出中年人

–输入大于40的数字，输出老年人

declare

​ i number(3):=&iu;用户输入一个值

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;

–pl/sql中的循环

—用三中方式输出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;

​ 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;

–游标

可以存放多个对象，多行记录。

—输出emp表中使所有员工的姓名

declare

​ cursor c1 is select * from emp;表中值给游标

​ emprow emp%rowtype;

begin

​ open c1;

​ loop

​ fetch c1 into emprow;

​ exit when c1%notfound;

​ dbms_output.put_line(emprow.ename);

​ end loop;

end;

—给指定部门员工涨工资

declare

​ cursor c2(eno emp.depton%type)

​ is select empno from where deptno=eno;带参数游标，给值

​ en emp.empno%type;

begin

​ open c2(10);

​ loop

​ fetch c2 into en;

​ exit when c2%notfound;

​ update emp set sal=sal+100 where empno=en;

​ commit;

​ end loop;

​ close c2;

end;

—查询10号部门员工信息

select * form emp where deptno=10；

–存储过程

存储过程：存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端

可以直接被调用。这一段pl/sql一般都是固定步骤的业务。

or replace:不能保证一次写对，一般要写。

—给指定员工涨100元

create or replace procedure p1(eno in emp.empno%type)

is

​ begin

​ update emp set sal=sal+100 where empno=eno;

​ commit;

​ end;

—测试p1

declare

begin

​ p1(7788);

end;

–存储函数

存储过程和存储函数的参数都不能带长度

存储函数的返回类型不能带长度

区别：函数有返回值。过程没有返回值。

in和out：凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰

相同点：都通过out指定一个或多个输出参数

—通过存储函数实现计算指定员工的年薪

create or replace function f_yearsal(eno emp.empno%type) return number

is

​ s number(10);

begin

​ select sal*12+nvl(comm,0) into s from emp where empno=eno;

​ return s;

end;

—测试f_yearsal

测试存储函数在调用的时候，返回值需要接收

declare

​ s number(10);

begin

​ s:=f_yearsal(7788);

​ dbms_output.put_line(s);

end;

–out类型参数如何使用

—使用存储过程来算年薪

create or replace procedure p_yearsal(eno emp.empno%type ,yearsal out number())

is

​ s number(10);

​ c emp.comm%type

begin

​ select sal*12 ,nvl(comm,0) into s,c from emp where empno=eno;

​ yearsal:=s+c;

end;

—测试p_yearsal

declare

​ yearsal number(10);

begin

​ p_yearsal(7788,yearsal);

​ dbms_putout.put_line(yearsal);

end;