Zephon Blog
0%

Oracle

发表于 更新于 分类于
本文字数： 8.6k 阅读时长 ≈ 8 分钟

Oracle

创建表

1
2
3
4
create table person(
	pid number(20),
    pname varchar2(10)
);

修改表结构

添加一列

1
alter table person add (gender number(1));

修改列类型

1
alter table person modify gender char(1); -- char类型固定长度，varchar2类型自动扩张

修改列名称

1
alter table person rename column gender to sex;

删除一列

1
alter table person drop column sex;

数据的CRUD

查询表中记录

1
select * from person;

添加表中记录

1
2
insert into person (pid,pname) values (1,'张三');
commit; -- oracle 事务默认不是自动提交的

修改表中记录

1
2
update person set pname='李四' where pid = 1;
commit;

删除表中记录

1
2
3
4
5
delete from person;-- 删除表中全部记录
drop table person; -- 删除表结构
truncate table person; -- 先删除表，再创建表，效果相当于删除表中全部记录
-- 在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高
-- 索引可以提供查询效率，但会影响增删改效率

序列

默认从1开始，以此递增，主要用来给主键赋值使用

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

dual：虚表，只是为了补全语法，没有任何意义

1
2
3
create sequence s_person; -- 创建序列
select s_pserson.nextval from dual; -- 获取下一值
select s_person.currval from  dual; -- 获取当前值，但是若不执行一次nextval则会报错

添加一条记录

1
2
insert into person (pid,pname) values(s_person.nextval,'张三');
commit;

scott用户

默认用户名：scott，密码：tiger

解锁scott用户：使用超级管理员执行：

1
2
3
4
5
-- 解锁scott用户
alter user scott account unlock;
-- 解锁scott用户的密码[此名也可用来重围密码]
alter user scott identified by tiger;
-- 切换到scott用户下

函数

单行函数

作用于一行，返回一个值

字符函数

1
2
select upper('yes') from dual; -- YES 小写变大写
select lower('YES') from dual; --yes 大写变小写

数值函数

1
2
3
select round(16.18,1) from dual; -- 四舍五入，后面的参数表示保留的位数，-1表示往前保留1位 16.2
select trunc(56.16,1) from dual; -- 直接截取，不四舍五入 56.1
select mod(10,3) from dual; --求余数

日期函数

1
2
3
4
5
6
7
8
9
10
-- 查询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 (sysdate-e.hiredate)/7 from emp e;

转换函数

1
2
3
4
-- 日期转字符串
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;
-- 字符串转日期
select to_date('2020-2-16 14:12:30','fm yyyy-mm-ddhh24:mi:ss') from dual;

通用函数

1
2
3
-- 算出emp表中所有员工的年薪
-- 奖金里面有null值，如果null值和任意数值算术运算，结果都是null
select e.sal*12+nvl(e.comm,0) from emp e;

条件表达式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 给emp表中员工起中文名
select e.ename,
	case e.ename
		when 'SMITH' then '史密斯'
			when 'ALLEN' then '张三'
				(else '无名') -- 没有else则其它为null
					end
from emp e;
--- 判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等，其余显示低收入
select e.sal,
	case
		when e.sal>3000 then '高收入'
			when e.sal>1500 then '中等收入'
				else '底收入'
					end
-- oracle 专用条件表达式
select e.ename,
	decode(e.ename,
          'SMITH','史密斯',
          'ALLEN','张三',
          '无名') 中文名 -- oracle 中除了起别名，都用单引号
from emp e;

多行函数[聚合函数]

作用于多行，返回一个值

1
2
3
4
5
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; -- 平均工资

分组查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 查询出每个部门的平均工资
-- 分组查询中出现在group by 后面的原始列，才能在select后面出现
-- 没有出现在group by 后面的列，想在select后面，必须加上聚合函数
-- 聚合函数有一个特性：可以把多行记录变成一个值
select e.deptno,avg(e.sal)
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; -- 所有条件中都不能使用别名来判断
-- 查询出每个部门工资高于800的员工的平均工资
select e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by e.deptno;
-- where 是过滤分组前的数据，having是过滤分组后的数据
-- 表现形式：where必须在goup by 之前，having在group by 之后

多表查询

笛卡尔积

1
select * from emp e,dept d;

等值连接

1
select * from emp e,dept d where e.deptno = d.deptno;-- 推荐

内连接

1
select * from emp e inner join dept d on e.deptno = d.deptno;

外连接

1
2
3
4
5
6
7
8
9
10
11
12
-- 查询出所有部门，以及部门下的员工信息(外连接)
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; -- 右连接

自连接

1
2
3
4
5
6
7
8
9
10
11
-- 查询出员工姓名，员工领导姓名（自连接）
-- 自连接：就是站在不同的角度将一张表看作多张表
select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr = e2.empno;
-- 查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
select e1.ename,d1.dname,e2.ename,d2.dname
from emp e1,emp e2,dept d1,dept d2
where e1.mgr = e2.empno
and e1.deptno=d1.deptno
and e2.deptno=d2.deptno;

子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 子查询
-- 子查询返回一个值
-- 查询出工资和SCOTT一样的员工信息
select * from emp where sal in( -- where 后不是主键，保险起见，使用in更好
select sal from emp where ename="SCOTT") ; 
-- 子查询返回一个集合
-- 查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in(
select sal from emp where deptno=10);
-- 子查询返回一张表
-- 查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
-- 先查询出每个部门低工资
	select deptno,min(sal) msal from emp group by deptno;
-- 三表联查，得到结果
select t.deptno,t.msal,e.ename,d.dname
from (select deptno,min(sal) msal 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;

分页

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- oracle中的分页
-- rownum行号，当我们做select 操作时，
-- 每查询出一行记录，就会在该行上加上一个行号，
-- 行号从1开始，依次递增，不能跳着走
-- 排序操作会影响 rownum的顺序
-- 如果涉及到排序，但还要使用rownum，就可以使用嵌套查询
select rownum,t.* from(
select e.* from emp e order by e.sal desc) t;
-- emp表工资倒序排列后，每页5条，查询第二页
-- rownum行号不能直接写上大于一个正数
select * from(
	select rownum rn,tt.* from(
		select * from emp order by sal desc
	) tt where rownum<11
) where rn>5;

视图

概念

视图就是提供一个查询的窗口，所有数据来自于源表

查询语句创建表

1
2
create table emp as select * from scott.emp;
select * from emp;

创建视图[必须有dba权限]

1
create view v_emp as select ename,job from emp;

查询视图

1
select * from v_emp;

修改视图[不推荐]

1
2
update v_emp set job='CLERK' where ename = 'ALLEN';
commit;

创建只读视图

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

视图的作用？

  1. 视图可以屏蔽掉一些敏感字段
  2. 保证总部和分部数据及时统一

索引

概念

索引就是在表的列上构建一个二叉树，达到大幅度提高查询效率的目的，但会影响增删改的效率

单列索引

创建单列索引

1
create index idx_ename on emp(ename);

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

1
select * from emp where ename='SCOTT'

单行函数、模糊查询都会影响索引的触发

复合索引

创建复合索引

1
create index idx_ename job on emp(ename,job);

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

如果要触发复合索引，必须包含有优先检索列中的原始值

1
2
3
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编程语言

概念

是对SQL语言的扩展，使SQL语言具有过程化编程的特性

比一般过程化编程语言，更加灵活高效

主要用来编写存储过程和存储函数等

变量定义与赋值

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 声明方法 
-- 赋值操作可以使用:=也可以使用into查询语句赋值
declare
	i number(2) :=10; -- 定义与赋值
	s varchar2(10) := '张三';
	ena emp.ename%type; -- 取ename的类型，引用型变量
	emprow emp%rowtype; -- 记录型变量 
begin
	dbms_output.put_line(i); -- 输出
	select ename into ena from emp where empno=7788;-- 赋值
	select * into emprow from emp where empno=7788;
	dbms_output.put_line(emprow.ename ||  '的工作为' ||emprow.job);
end;

IF判断

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 输入小于18的数字，输出未成年
-- 输入大于18小于40的数字，输出中年人
-- 输入大小40的数字，输出老年人
declare
	i number(3):=&ii;
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;

loop循环

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
--- 用三种方式输出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;

游标

可以存放多个对象，多行记录（类似集合）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 输出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.putline(emprow.ename);
		end loop;
	close c1;
end;
-- 给指定部门员工涨工资
declare
	cursor c2(eno emp.deptno%type)
    is select empno from emp where deptno=eno; -- 带参游标
    en emp.empno%type;
begin
	open c2(10);
		loop
			fetch c2 into eno;
			exit when c2%notfound;
			update emp set sal = sal+100 where empno = en;
			commit;
		end loop;
	close c2;
end;

存储过程

提前已经编译好的一段pl/sql语言，放置在数据库端可供人直接被调用，这一段pl/sql一般都是固定步骤的业务。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 给指定员工涨100
create or replace procedure p1(eno emp.empno%type) -- or replace 如果已经存在则修改
is
begin
	update emp set sal=sal+100 where empno = eno;
	commit;
end;

-- 测试
declare
begin
	p1(7788);
end;

存储函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 通过存储函数计算指定员工的年薪
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;

-- 测试
-- 存储函数在调用时，返回值需要接收
declare
	s number(10);
begin
	s := f_yaersal(7788);
	dbms_output.put_line(s);
end;

out类型参数使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 使用存储过程来算年薪
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;

-- 测试
declare
	ysearsal number(10);
begin
	p_yearsal(7788,yearsal);
	dbms_output.put_line(yearsal);
end;

in和out类型参数的区别：

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

存储函数和存储过程的区别：

语法区别：关键字不一样，存储函数比存储过程多两个return

本质区别：存储函数有返回值，而存储过程没有返回值

如果存储过程想实现有返回值的业务，就必须使用out类型的参数

即便是存储过程使用了out类型的参数，也不是真的有了返回值，而是在存储过程内部给out类型参数赋值，在执行完毕后，我们拿到输出类型参数的值。

可以使用存储函数有返回值的特性来自定义函数，而存储过程不能用来自定义函数

触发器

制定一个规则，在我们做增删改操作时，只要满足该规则，自动触发，无需调用

语句级触发器：不包含有for each row的

行级触发器：包含有for each row的

加for each row是为了使用:old或:new对象或一行记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 插入一条记录，输出一个新员工入职 语句级触发器
create or replace trigger t1
after
insert
on person
declare
begin
	dbms_output.put_line('一个新员工入职');
end;
-- 行级触发器
-- 不能给员工降薪
create or replace trigger t2
before
update
on emp
for each row
declare
begin
	if :old.sal>:new.sal then
		raise_application_error(-20001,'不能给员工降薪'); -- 抛出异常，只能在-20001～-20999之间
	end if;
end;
1
2
3
4
5
6
7
8
9
10
11
-- 使用触发器实现主键自增[行级触发器]
-- 分析：在用户插入操作前，拿到即将插入操作的数据，给该数据中的主键列赋值
create or replace trigger auid
before
insert
on person
for each row
declare
begin
	select s_person.nextval into :new.pid from dual;
end;