Oracle笔记（期末复习）

创建表空间、创建用户、用户授权

--创建表空间create tablespace wxy datafile \'E:\\oracle\\wxy.dbf\'size 100m autoextend on next 10m;--删除表空间drop tablespace wxy;--创建用户create user wxyidentified by 123456default tablespace wxy;--给用户授权--oracle数据库中常用角色connect--连接角色，基本角色resource--开发者角色dba--超级管理员角色--给wxy用户授予dba角色grant dba to wxy;--切换到wxy用户下

数据类型介绍和表的创建

创建表

--创建一个表personcreate table person(pid number(20),pname varchar2(10));

修改表结构

--修改表结构--添加一列alter table person add gender number(1);--修改列类型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,\'小明\');commit;--修改一条记录update person set pname = \'小马\' where pid = 1;commit;--三个删除--删除表中全部记录delete from person;--删除表结构drop table person;--先删除表，再次创建表。效果等同于删除表中全部记录。--在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高。--索引可以提高查询效率，但是会影响增删改效率。truncate table person;

序列的使用

CREATE SEQUENCE 序列名[INCREMENT BY n][START WITH n][{MAXVALUE/MINVALUE n|NOMAXVALUE}][{CYCLE|NOCYCLE}][{CACHE n|NOCACHE}]

scott用户

--scott用户，密码tiger。--解锁scott用户alter user scott account unlock;--解锁scott用户的密码【此句也可以用来重置密码】alter user scott identified by tiger;--切换scott用户

单行函数

--单行函数：作用与一行，返回一个值。--多行函数：作用与多行，返回一个值。----字符函数select upper(\'yes\') from dual;select lower(\'YES\') from dual;----数值函数select round(26.18,1) from dual; --四舍五入，后面参数表示保留的位数select trunc(26.16,-1) from dual;--直接截取，不再看后面位数字是否大于5select mod(10,3) from dual;--求余----日期函数----查询出emp表中所有员工入职距离现在几天select sysdate-e.hiredate from emp e;----算出明天此刻select sysdate+1 from dual;----查询出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;----字符串转日期select to_date(\'2020-6-20 10:29:33\',\'fm yyyy-mm-dd hh24:mi:ss\') from dual;----通用函数----算出emp表中所有员工的年薪-----奖金里面有null值，如果null值和任意数字做算术运算，结果都是nullselect e.sal*12+e.comm from emp e;--修改格式位：select e.sal*12+nvl(e.comm,0) from emp e;

条件表达式

--条件表达式--条件表达式的通用写法，mysql和oracle通用----给emp表中员工起中文名字select e.name from emp e;select e.ename,case e.enamewhen \'SMITH\' then \'曹贼\'when \'ALLEN\' then \'大耳贼\'when \'WARD\' then \'诸葛小儿\'--else \'无名\'endfrom emp e;----判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入-----其余显示低收入select e.sal,casewhen e.sal>3000 then \'高收入\'when e.sal>1500 then \'中等收入\'else \'低收入\'endfrom emp e;--oracle专用条件表达式--oracle除了起别名，都用单引号select e.ename,decode(e.ename,\'SMITH\' , \'曹贼\',\'ALLEN\' , \'大耳贼\',\'WARD\' , \'诸葛小儿\',\'无名\')\"中文名\"from emp e;

多行函数

--多行函数【聚合函数】：作用与多行，返回一个值。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;--平均工资

分组查询

--分组查询----查询出每个部门的平均工资----分组查询中，出现在group by 后面的原始列，才能出现在select后面----没有出现在group by 后面的列，现在select后面出现，必须加上聚合函数----聚合函数有一个特性，可以把多行记录变成一个值。select e.deptno ,avg(e.sal)--, e.enamefrom emp egroup by e.deptno;----查询出平均工资高于2000的部门信息select e.deptno ,avg(e.sal) asalfrom emp egroup by e.deptnohaving avg(e.sal)>2000;--having asal>2000;这样是错误的。----所有条件都不能使用别名来判断----比如下面的条件语句也不能使用别名当条件，因为where语句是优先于select 语句的。--select * from emp where sal>1500; √--select ename , sal s from emp where sal>1500;√--select ename , sal s from emp where s>1500; ×--查询出每个部门工资高于800的员工的平均工资select e.deptno ,avg(e.sal) asalfrom emp ewhere e.sal>800group by e.deptno;----where是过滤分组前的数据，having是过滤分组后的数据。----表现形式，where必须在group by 之前，having实在group by之后。----查询出每个部门工资高于800的员工的平均工资然后再查询出平均工资高于2000的部门select e.deptno ,avg(e.sal) asalfrom emp ewhere sal>800group by e.deptnohaving avg(e.sal)>2000;

多表查询中的一些概念

----多表查询的一些概念----笛卡尔积select *from emp e,dept d;----等值连接select *from emp e,dept dwhere e.deptno = d.deptno;----内连接select *from emp e inner join dept don e.deptno = d.deptno;---查询出所有部门，以及部门下的员工信息。【外连接】select *from emp e right join dept don e.deptno = d.deptno;----查询所有员工信息，以及员工所属部门select *from emp e left join dept don e.deptno=d.deptno;----oracle中专用外连接-- 上段 right join 外连接select *from emp e,dept dwhere e.deptno(+) = d.deptno;----上段 left join 外连接select *from emp e,dept dwhere e.deptno = d.deptno(+);

自连接概念和练习

select * from emp;--查询出员工姓名，员工领导姓名--自连接：自连接其实就是站在不同的角度把一张表看成多场表select e1.ename ,e2.enamefrom emp e1 , emp e2where e1.mgr = e2.empno;----查询员工姓名，员工部门名称，员工领导姓名,员工领导部门名称select e1.ename , d1.dname , e2.ename , d2.dnamefrom emp e1, emp e2, dept d1, dept d2where e1.mgr = e2.empnoand e1.deptno = d1.deptnoand e2.deptno = d2.deptno;

子查询

--子查询--子查询返回一个值----查询出工资和scott一样的员工信息select * from emp where sal in(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) msalfrom empgroup by deptno;--2.三表联查，得到最终结果select t.deptno , t.msal , e.ename , d.dnamefrom (select deptno ,min(sal) msalfrom empgroup by deptno) t , emp e , dept dwhere t.deptno = e.deptnoand t.msal = e.saland e.deptno = d.deptno;

分页查询

--oracle中的分页----rownum行号：当我们做select操作的时候，----每查询出一行记录，就会在该行上加上一个行号，----行号从1开始，依次递增，不能跳着走。--排序操作会影响rownum的顺序select rownum ,e.* from emp e  order by e.sal desc;----如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询select * from (select rownum ,e.* from emp e  order by e.sal desc) t;--emp 表工资倒叙排列后，每页5条记录，查询第二页--rownum行号不能写上大于一个整数select * from (select rownum rn, e.* from (select * from emp e order by e.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 ename , job from emp;----查询视图select * from v_emp;----修改视图update v_emp set job = \'CLERK\' where ename = \'ALLEN\';commit;----创建只读视图create view v_emp1 as select ename , job from emp with read only;----视图的作用？----第一：视图可以屏蔽掉一些敏感字段。----第二：保证总部和分部数据及时统一。

索引

--索引----索引的概念：索引就是在表的列上，创建一个二叉树----达到大幅度提高查询效率的目的，但是索引会影响增删改的效率。--单列索引----创建单列索引create index idx_enamen on emp(ename);----单列索引触发规则，条件必须是索引列中的原始值。----单行函数，模糊查询，都会影响索引的触发。select * from emp where ename = \'SCOTT\';--复合索引----创建复合索引create inde idx_enamejob on emp(ename,job);----符复合索引中第一列为优先检索列----如果要触发复合索引，必须包含有优先检索列中的原始值。select * from emp where ename = \'SCOTT\' and job = \'xx\';--触发复合索引select * from emp where ename = \'SCOTT\' or job = \'xx\';--不触发索引select * from emp where ename = \'SCOTT\'--触发单列索引

pl/sql编程语言

pl/sql怎么定义变量

--pl/sql编程语言----pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性。----pl/sql编程语言比一般的过程化语言，更加灵活高效。----pl/sql编程语言主要用来编写存储过程和存储函数等。--声明方法，类似main方法----赋值操作可以使用:=也可以使用into查询语句赋值declarei number(2) := 10;s varchar2(10) := \'小明\';ena emp.ename%type;----引用型变量emprow emp%rowtype;----记录型变量begindbms_output.put_line(i);dbms_output.put_line(s);select ename into ena from emp where empno = 7788;dbms_output.put_line(ena);select * into emprow from emp where empno = 7788;dbms_output.put_line(emprow.ename || \'的工作为：\' ||emprow.job);end;

pl/sql中的if判断

--pl/sql中的if判断----输入小于18的数字，输出未成年----输入大于18小于40的数字，输出成年人----输入大于40的数字，输出老年人declarei number(3) := &ii;beginif i<18 thendbms_output.put_line(\'未成年\');elsif i<40 thendbms_output.put_line(\'中年人\');elsedbms_output.put_line(\'老年人\');end if;end;

pl/sql中的循环

--pl/sql中的loop循环---用三种方式输出1到10十个数字---while循环declarei number(2) := 1;beginwhile i<11 loopdbms_output.put_line(i);i := i + 1;end loop;end;---exit循环declarei number(2) := 1;beginloopexit when i>10;dbms_output.put_line(i);i := i + 1;end loop;end;---for循环declarebeginfor i in 1..10 loopdbms_output.put_line(i);end loop;end;

pl/sql中的游标

--游标：可以存放多个对象，多行记录。----输出emp表中所有员工的姓名declarecursor c1 is select * from emp;emprow emp%rowtype;beginopen c1;loopfetch c1 into emprow;exit when c1%notfound;dbms_output.put_line(emprow.ename);end loop;close c1;end;----给指定部门员工涨工资declarecursor c2(eno emp.deptno%type)is select empno from emp where deptno = eno;en emp.empno%type;beginopen c2(10);loopfetch c2 into en;exit when c2%notfound;update emp set sal = sal + 100 where empno = en;commit;end loop;close c2;end;----查询10号部门员工信息select * from emp where deptno = 10;

存储过程使用

--存储过程----存储过程：存储过程就是提前编译好的一段pl/sql语言，放置在数据库端----可以直接被调用。这一段pl/sql一般都是固定步骤的业务。--创建存储过程语法：----create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]----AS/is----begin--------pl/sql 子程序体;----end;--给指定员工涨100块工资create or replace procedure p1(eno emp.empno%type)isbeginupdate emp set sal = sal + 100 where empno = eno;commit;end;select * from emp where empno = 7788;----测试p1declarebeginp1(7788);end;

存储函数

--存储函数----create or replace function 函数名(Name in type , Name in type ,····) return 数据类型----is 结果变量 数据类型;----begin--------return(结果变量);----end 函数名;--通过存储函数实现计算指定员工的年薪----创建存储过程和存储函数的参数都不能带长度----存储函数的返回值类型不能带长度create or replace function f_yearsal(eno emp.empno%type) return numberiss number(10);beginselect sal * 12 +nvl(comm,0) into s from emp where empno = eno;return s;end;----测试f_yearsal----存储函数在调用的时候，返回值需要接收。declares number(10);begins := f_yearsal(7788);dbms_output.put_line(s);end;

out类型参数如何使用

--out类型参数如何使用----使用存储过程来算年薪create or replace procedure p_yearsal(eno emp.empno%type,yearsal out number)iss number(10);c emp.comm%type;beginselect sal*12,nvl(comm,0) into s , c from emp where empno = eno;yearsal := s+c;end;--测试p_yearsaldeclareyearsal number(10);beginp_yearsal(7788,yearsal);dbms_output.put_line(yearsal);end;----in 和 out 类型参数的区别是什么？----凡是涉及到into查询语句赋值或者:= 赋值操作的参数，都必须使用out来修饰。

触发器的概念和分类

--触发器，就是指定一个规则，在我们做增删改操作时，--只要满足规则，自动触发，无需调用。----语句级触发器：不包含for each row 的就是行级触发器----行级触发器：包含有 for each row 的就是行级触发器--加for each row 是为了使用：:old 或者 :new对象。--语句级触发器：---插入一条记录，输出一个新员工入职create or replace trigger t1afterinserton persondeclarebegindbms_output.put_line(\'一个新员工入职\');end;----触发t1insert into person values(1,\'小红\');commit;--查看表select * from person;--行级触发器：----不能给员工降薪create or replace trigger t2beforeupdateon empfor each rowdeclarebeginif :old.sal > :new.sal thenraise_application_error(-20001,\'不能给员工降薪\');end if;end;----触发t2select * from emp where empno = 7788;--查看是否修改了数据。update emp set sal = sal - 1 where empno = 7788;commit;

触发器实现主键自增

---触发器实现主键自增。【行级触发器】----分析：在用户做插入操作之前，拿到即将插入的数据，----给该数据中的主键列赋值。create or replace trigger auidbeforeinserton personfor each rowdeclarebeginselect s_person.nextval into :new.pid from dual;end;--使用auid实现主键自增insert into person (pname) values(\'a\');commit;