Oracle数据库基础知识点

Oracle数据库基础知识点

本人的随堂笔记，整理不全，如有错误请指点，谢谢啦

一. Oracle简介

Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的、适应高吞吐量的数据库方案。 --来源百度百科

---

二.数据类型

oracle的数据类型与mysql相差不大，但是需要注意以下几种常用类型
number(p,s) 一般用于数字，P为长度，最大38；S为精度，范围-87~127
varchar 可变类型，可不指定长度
varchar2 使用时必指定长度，最大4000字节
char 默认情况为1字节，最大为2000字节
long 长整型，最大长度为2GB

---

三.建表查询语句(CRUD)

1.新增

insert into 表面（字段名1，字段名2，…）values（字段值1，字段值2，…）；

2.删除

删表
drop 表名；

删字段
alter table 表名 drop 字段名；

删除一列字段
alter table 表名 drop column 列名;

删除一条数据
delete from 表名 where 列名=

3.修改

修改字段
alter table 表名 modify（修改的字段名 数据类型 是否为空 值）；

修改数据
update 表名 set 要修改的数据 where 修改的数据名

4.查询

基础语法
select 查询内容 from 表名

升序降序
升序order by 字段 asc
降序从大到小order by 字段 desc
如若取第一条数据limit 0，1

条件查询

select  查询内容 from 表名
where    or   not  between...and

分组查询group up
select后只有两种表达式：统计函数（sum等）和分组的组名
在select后的组名必须是要分组的组名
如果有where条件的情况下，必须先满足where条件

having
用法同where，可添加数学函数，如COUNT,MAX等

like模糊查询
模糊查询，其中_(下划线) 表示一个字符，"%“表示有多个字符，
若需要搜索带有”_"下划线的名字，需要在后面写上escape ‘’
实例 like ‘%_%’ escape ‘’

多表查询
内联查询
左外联查询left join
右外联查询right join
完全外连接 full join
例select * from student full join score where student.Num=score.Stu_id;
内连接inner join

select * from student inner join score where student.Num=score.Stu_id;

---

四.单行函数

concat字符串连接
concat（‘hello’,‘world’)输出结果为helloworld

substr字符串取出某一个或几个字符
substring(‘helloworld’,2,4)结果为ello
第一个值(2)表示从第二个字符开始
第二个值(4)表示输出4个

length()获取长度

instr 字符串中的某个字符首次出现的位置
instr（‘hello’,‘l’）输出结果为3

lpad右对齐
语法lpad（某个字段名(例salary)，显示位数(例 10) , 如果不足位数用或者’ '(空格)补齐
例子lpad(salary,10,)结果为******1000

rpad左对齐，与右对齐同理

trim 将一个字符从另一个字符串中移除(只能去除首位的)
例子 trim(‘h’ from ‘hehllo’) 输出结果为ehllo

replace将字符串中的某个字符取代另一个
例子 replace(‘abcd’,‘a’,‘b’)输出结果为bbcd

round保留小数
例子 round(435.45,2) 435.45
round(435.45) 435
round(435.45,-2) 400

months_between()用于判断相差几个月
列子months_between(第一个时间点，hire_date)

add_months向指定日期加上几个月
例子add_months(sysdate,2)

next_day从指定的时间点，往后找到最近的
例子next_day(sysdate,‘星期日’)

last_day 指定时间点的月份的最后一天
例子last_day(sysdate)

to_char 多用于将日期转换为varchar2,也可将数字转换
可以在格式数字前加L或者$，L表示当地的货币符号
tochar(sysdate,‘yyyy-mm-dd’)
tochar(1234567,‘999,999,999.99’) 1,234,567
tochar(123456,‘000,000,000.99’) 001,234,567

to_date多用于将varchar类型转换为date
to_date(‘1994-06-07’,‘yyyy-mm-dd’)

nvl(a,b)若A查询结果为空值，则使用B来代替，a和b的数据类型要一致
nvl2(a,b,c)若A不为空，返回B；若A为空，返回C
nullif（a,b)相等时返回null，不相等时返回a

coalesce(a,b,c....)如果第a为空，返回b;如果b为空返回c;如果c为空返回d，依次下去

---

五.视图

1.基础语法（简单视图）

仅能一个表，无法使用函数和分组，可以进行DML操作
create view view_name as 查询语句;
查询语句中允许字段别名

2.with read only 拒绝修改，仅查询 加在查询语句最后

3.复杂视图

可以多个表，能够使用函数，可以进行分组，
例

create  or replace view testview2
	as
		select d.name,avg(salary) avg_s from
		test t,dep d
		where t.depno = d.depno
		group by d.name;

3.DML操作

有group by，distinct,rownum不能进行新增修改删除

4.视图的操作

drop view 视图名;

---

六.TOP分析

TOP-N分析查询一个列最大或者最小的N个值
涉及分页查询
针对rownum只能使用<,或<=,而用=,>,>=都不能返回任何数据

---

七.PL/SQL(重点)

1.用途

提高sql功能嵌入在ORACLE中对sql进行扩展的编程语言

基本格式

基本格式
declare–声明部分(可选)
用于声明 变量 常量，游标，子程序
begin 程序主题开始 必选
合法的PL/SQL语句
excepaction —异常处理 可选
end;------------end后有分号，结束PL/SQL代码 必选
例：

declare
   msg varchar2(20);
begin
    msg:='hello world';
	dbms_output.put_line(msg);
end;

2.变量

(1)变量格式
变量名 数据类型:=值
注意变量赋值为冒号等于 :=

(2)常量格式
常量名 constant 数据类型:= 值

(3)全局变量

declare
   num1 number:=2;
   num2 number:=4;
begin
	dbms_output.put_line('全局变量:'|| num1);
	dbms_output.put_line('全局变量:'|| num2);
end;

(4)局部变量

declare
   num1 number :=2;
   num2 number:=4;
begin
	declare
   		num1 number :=8;
   		num2 number:=10;
			begin
				dbms_output.put_line('局部变量:'|| num1);
				dbms_output.put_line('局部变量:'|| num2);
			end;
		dbms_output.put_line('全局变量:'|| num1);
		dbms_output.put_line('全局变量:'|| num2);
end;

3.运算符

+   -   *   /   数学运算符
||    字符串连接
:=       赋值符号，右边赋给左边
>	< 	>=	<=	
(!=	~=   ^=  <>)这四个都是不等于
= 	==	都表示等于
like between    	is null
not     or     and   

4.流程控制

PL/SQL				JAVA
if then if			if
if  then else		if ...else
if  then elsif      if..else if
cas	then			swith
loop
for
while

例子如下

--if例子--
declare
	score number(3):=100;
begin
	if(score=100) then
		dbms_output.put_line('优');
	elsif(score=100) then
		dbms_output.put_line('优');
	elsif(score=100) then
		dbms_output.put_line('优');
	else
		dbms_output.put_line('对不起分数错误');
	end if;
end;

--case例--
declare
	score number(3):=100;
begin
	when 100 then
		dbms_output.put_line('优');
	when 90 then
		dbms_output.put_line('优');
	when 80 then
		dbms_output.put_line('优');
	end case;
end;

--decode例--
decode(
a,10,表达式,
a,20,表达式.
c,30,表达式)

--loop例--
declare
	a number(3):=100;
begin
	loop
		dbms_output.put_line(a);
		a:=a+10;
		if (a>50) then
			exit;
		end if
	end loop
end;

5.数组

数组定义
tpye 数组名称 is varray of 数组的数据类型
定义变量时————变量名 数组名称
实例

declare
  type arrName is varray(5)of varchar2(10);
  names ARRNAME;
  length_names number;
begin
  names:=arrName('我','爱');
  length_names:=names.count;
  --利用循环输出
     for i in 1 .. length_names loop
     dbms_output.put_line(names(i));
       end loop;
  end;

6.游标

为了能够对多行数据进行集中处理，对多个数据进行单独处理
显示游标（由程序员进行控制操作）
操作
1.声明游标

声明 cursor 游标名称 is select语句

2.打开游标

open 游标名称;----打开游标

3.提取数据

fetch emp_cur into emp_id,emp_name,emp_age;---利用fetch into取值

4.关闭游标

close 游标名称;---关闭游标

实例，以emp表为例

declare
	emp_id emp.id%type;-----给变量赋值定义和emp表中id字段一致的数据类型
	emp_name emp.map%type;
	emp_age emp.age%type;
cursor emp_cur is
	select id,names,age from emp where age>18;----声明游标
begin
	open emp_cur;----打开游标
		loop
			exit when not emp_cur%found;-----当游标中没有找到值就退出循环
			fetch emp_cur into emp_id,emp_name,emp_age;---利用fetch into取值
			DBMS_OUTPUT.PUT_LINE('ID'||emp_id||'name'||emp_name||'age'||emp_age);
		end loop;
	close emp_cur;---关闭游标
end;

注意：在使用游标时，事先检查游标是否打开
在使用游标时，每次都要使用not%found进行检查是否读取完毕
如果将游标中的值赋给数组的时候，确保数据类型一致
游标使用完成之后必须关闭

5游标属性

%isopen 判断游标是否打开 如果游标打开返回true
%found 检查数据行是否有效，有效则返回true
%notfound 如果没有数据可提取，则返回true
%rowcount 返回到当前为止使用fetch提取的数据

6.隐式游标（由数据库系统自动执行）

参数化游标
定义方法

declare
	e_name emp.names%type;
	e_salary emo_salary%type;
	cursor emp_cur(e_id number) is
	select names,salary from emp where id=e_id;
begin
	open emp_cur(1);
	loop
		exit when emp_cur%notfound;
		fetch emp_cur into e_name,e_salary;
		DBMS_OUTPUT.PUT_LINE(e_name||e_salary);
	end loop;
	close emp_cur;
end;

游标变量

declare
type r_emp is ref cursor
return emp%rowtype;

八.定义序列

用于指定数据库对象的唯一性，主要用于提供主键值

基本格式

create sequence EMP_SEQUENCE_ID
--每次增加的数值--
increment by n
--从哪个值开始--
start with 1
--最小值--
minvalue 1
--最大值
maxvalue 10
--是否缓存登录--
nocache | cache n
--是否需要循环--
cycle | nocycle

九.定义触发器

此触发器配合序列实现ID自增长

create or replace trigger EMP_TRIGGER_ID
before insert on emp
for each row
declare
next_id number;
begin
select EMP_SEQUENCE_ID.nextval into next_id from dual;
:new.id:=next_id;
end EMP_TRIGGER_ID;

---

十.索引

自动创建：在定义PRIMARY KEY或UNIQUE约束后系统自动在相应的列上创建唯一性索引

手动创建：用户可以再其他列上创建非唯一的索引，以加速查询

create index 索引名 on table(column)

什么时候创建索引：列中数值分布范围很广，经常在where子句或连接条件中出现，表经常被方位且数据量大，访问的数据大概占总数据的2%-4%

什么时候不要创建索引：表很小，并且表经常更新，列不经常作为连接条件或出现在where子句中；

---

十一.同义词synonym

使用同义词访问相同的对象：方便访问其他用户的对象，缩短对象名字的长度
语法 create synonym e for 表名 意思是为表取同义词e

---

十二.存储过程

1.基础定义（普通版）

create or replace procedure PRO_EMP   ----创建或替换存储过程PRO_EMP
as
	e_name emp.names%type;	-----存储过程中需要用到的变量定义在AS后面
begin
	select names into e_name from emp where id=1;
	DBMS_OUTPUT.PUT_LINE(e_name);
end pro_emp;

--调用存储过程--
excute PRP_EMP();

2. 2.0版存储过程(带参)

in 接受参数输入 只允许从外部进行参数赋值
out 允许参数输出 只允许在内部赋值
in…out 既能输入也能输出
例子

create or replace procedure PRO_EMP(e_id in number,e_name out varchar2)
as
	isCount number;
begin
	select names ,count(*) into e_name,isCount from emp where id=e_id group by names;
		if(isCount >=1) then
 			DBMS_OUTPUT.PUT_LINE(e_name);
		elsif(isCount<=0) then
			DBMS_OUTPUT.PUT_LINE('123');
	end if;
end;

--调用
declare
a emp.names%type;
begin
a:='';
PRO_EMO(1002,a);
end;