oracle实战学习记录

最新推荐文章于 2023-11-30 19:22:23 发布

迷失的小鹿

最新推荐文章于 2023-11-30 19:22:23 发布

阅读量1k

点赞数

文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/Java_Fly1/article/details/121914109

版权

oracle实战学习记录

序列
修改表数据行(update)
删除数据行(delete)
截断表
drop,delete,truncate的区别
分页查询
- oracle的隐藏列
分页查询的原理
内连接
自然连接
外连接
- 左外连接
- 右外连接
- 全外连接
exists运算符
聚集函数
数据库完整性(约束)
- 实体完整性（Entity Integrity）
- 域完整性(Domain Integrity)
- 参照完整性(Referential Integrity)
- 添加约束
- 删除约束
- 查询约束
- 启用约束
- 禁用约束
级联
表设计
- 范式
- 表关系设计
PLSQL
- PL/SQL基本格式
- PL/SQL有效字符
- 变/常量声明
- 输出
- 接受用户的输入赋值&变量名称
- into 赋值
- 流程控制
- - if ... then ...
  - case when ... when
- for 循环
- - loop循环(自动退出)
  - loop循环(手动退出)
- while 循环
异常处理
- 所有异常
- 预定义异常
- 非预定义异常
存储过程
- 创建存储过程
- 形参声明
- 实参声明
- 存储过程的调用
- 存储过程示例
游标（Cursor)
- 隐式Cursor
- 动态 Cursor (Ref Cursor)
视图
同义词

序列

序列就是有顺序的数列，如123456

# 创建序列
# 创建序列，最小值是1，最大值是99999999999 ，增量为1，开始于1
create sequence 序列名称 [MINVALUE 1 MAXVALUE 99999999999 increment by 1 start with 1]

序列调用nextval将触发增量条件。序列只能向前走，不能向后退！
使用序列：

select 序列名称.nextval from dual;# 查询序列的下一个值
select 序列名称.currval from dual;#查询序列的当前值（保存在会话中）

序列可以实现表中主键的自增长

修改表数据行(update)

update 表名 set 字段1=字段1的值,字段2=字段2的值... where 字段1=字段1的值
[and/or] 字段2=字段2的值

删除数据行(delete)

基本格式

delete from 表名 where 字段1=字段1的值[and/or]字段2=字段2的值
#例如:
delete from t_student where id = 1
# 删除id为1的这个学生的信息

截断表

# 基本格式
truncate table 表名;

截断表指的是将表恢复到初始新建状态，从表中删除所有的行和重置表的存储区域。

drop,delete,truncate的区别

1.delete和truncate都是只删除表的数据，而不是删除表的结构(定义),drop删除数据和定义。
2.delete语句是dml,事务提交之后才生效。truncate和drop是ddl,操作立即生效不需要提交事务，不能回滚。
3. truncate 将表重置回最开始。而delete只删除数据，不释放存储空间，等待写入新的数据来覆盖旧的数据
4. 速度一般来说:drop>truncate>delete
5. truncate只能删除全部的数据，而delete可以通过where条件来筛选要删除的数据

分页查询

oracle的隐藏列

每个表必有以下两个列
1.rowid，物理存在，必是唯一
2. rownum 逻辑存在，

select t.*,t.rowid from t;#查询rowid

select t.*,rownum from t;# 查询rownum

# 循环插入数据
begin 
for i in 1.. 100
loop 
insert into goods (goods_id,gooods_name)
values (seq_goods.nextval,'Pingg')
end loop;
end;

分页查询的原理

select t2.* from (select t1.*,rownum rn from 
(select * from t order by id desc)
t1)t2
where t2.rn between 1 and 10

开始行号的公式：1+(当前页号-1)每页记录数
结束行号的公式：每页页号每页记录数

内连接

又叫等值连接（只有满足连接条件，值相等的时候采取出相关数据）

select * from a [inner] join b on a.pk = b.pk;

# 内连接标准写法，只能用on来过滤条件
select * from t inner join b on a.pk = b.pk;

自然连接

自然连接是内连接当中极其特殊的一种，他会自动关联两张表当中字段名称和字段类型一模一样的值，也就是说两张表当中字段名称和字段类型一模一样的情况下，它会自动加上on a.fk =b.pk;
自动连接会去除重复的字段

select * from t natural join b;

外连接

左外连接

左外连接简称就是左连接
以左边的表为主表，右边的表为从表，查询的结果以左边的表为主，

# 左连接
select * from t_student s left join t_teacher t on s.tid = t.id;

等于以下sql语句

# 左外连接
select * from t_student s left outer join t_teacher t on s.tid = t.id;

右外连接

右外连接简称就是右连接
以右边的表为主表，左边的表为从表，查询的结果以右边的表为主，

# 右连接
select * from t_student s right join t_teacher t on s.tid = t.id;

等于以下sql语句

select * from t_student s right outer join t_teacher t on s.tid = t.id

全外连接

左连接和右连接返回结果的并集

select * from t_student s left join t_teacher t on s.tid = t.id
union 
select * from t_student s right join t_teacher t on s.tid = t.id;

等于如下sql

select * from t_student s full join t_teacher t on s.tid = t.id;

标准写法

select * from t_student s full outer join t_teacher t on s.tid = t.id;

exists运算符

select 1 from dual where exists(
select 1 from dual where 1>any(0,2,3)
);

聚集函数

聚集函数(也称分组函数、聚合函数。聚合函数可以对行集进行操作，并且为每组给出一个结果)
select 组函数 (表达式) /表达式 from 子句 group by 表达式(可省略，当求最大，最小，总和)

select avg(age) from t_student;

非group by 中出现的表达式如果要出现在select当中，必须使用组函数
聚集函数可以使用任何有效的表达式
NULL值在聚集函数中被忽略
可以在聚集函数中使用distinct关键字，排除重复值
筛选条件中如果使用了组函数，应该使用having筛选

数据库完整性(约束)

数据完整性(Data Integrity)是指数据的精确性(Accuracy)和可靠性(Reliablity),它
是防止数据库中存在不符合语义规定的数据和因错误信息的输入造成无效操作信息而提出的。

实体完整性（Entity Integrity）

实体完整性哟球每一个表中的主键字段都不能为空（非空）或者重复的值(唯一)

唯一约束（unique)
主键约束（primary key)
隐含非空约束和唯一约束

域完整性(Domain Integrity)

域完整性指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等。

限制数据类型(检查约束check)

参照完整性(Referential Integrity)

外键约束（foreign key)
完整语句：

constraint 约束名称 foreign key(列名) references 引用表名 (引用列名)

建表语句：

create table 表名(列 类型 references 引用表名(引用主键) 注意，没有
constraint 约束名称 foreign key ,也就是说：不支持对约束命名，也不用写出
 foreign key

当更新、删除、插入一个表中的数据时，通过参照引用相互关联的另一个表中的
数据，来检查对表的数据操作是否正确
引用完整性要求关系中国不允许引用不存在的实体
引用完整性与实体完整性是关系模型必须满足的完整性约束条件

添加约束

1.先建表，后创建约束
alter table 表名 add constraint 约束名称约束类型(列名)
2.建表时直接加约束(不适用外键约束，外键有它自己的格式)

check (列名 in (值1，值2…))
check (列名 >=0 and 列名<=100)
check (name like ‘M%’)
默认值 default 没有约束名称，也不能用constraint 关键字
非空约束 (not null)没有约束名称，也不能用 constraint 关键字

create table 表名 (列名 类型 [constraint 约束名称]约束类型)
create table 表名(列名 类型 ,constraint  约束名称 约束类型(列名))
create table 表名(列名 类型 约束类型 -- 这种格式仅适用于非空和默认值，非空和默认值可以同时存在，例如:sex number(1) not null default 1)

唯一约束

# 第一种方式
create table test (id number(5) constraint id_un unique);
# 第二种方式
create table test(id number(5), constraint id_un unique(id));
# 第三种方式
create table test(id number(5) unique);# 系统自动生成约束名称
# 第四种方式
alter table test add constraint test_uni unique(id);

主键约束

在表的id列上添加主键约束

# 第一种
alter table student add constraint stu_pk primary key(id);
# 第二种
create student(id number(4) constraint su_pk primary key,
name varchar2(10)
);

检查约束 check

# 检查表中sex列的值是否是(0和1）集合内
alter student add constraint student_sex_check check (sex in(0,1));
# 检查表中年龄列是否在18-30之间
alter student add constraint student_age_check 
check(age>=18 and age<=30);

默认约束和非空约束

默认约束 default
非空约束 not null

# 年龄列添加默认值
alter table t_student 
modify age number(2) default 18 not null;

外键约束

# 第一种方式
# alter 表名 add constraint  约束名称 foreign key(引用的主键) references 
#表名(主键)
alter t_student add constraint student_teacher_fk foreign key(tea_id)
references t_teacher(tea_id);

删除约束

alter table 表名 drop constraint 约束名称

查询约束

select table_name,constraint_nam,constraint_type,
status from user_constraints;

查询约束作用在哪些列上

select * from user_cons_columns;

启用约束

alter table 表名 modify constraint 约束名称 enable
alter table 表名 modify  列名 not null
alter table 表名 modify 列名 default 值

禁用约束

alter table 表名 modify constraint 约束名称 disable
alter table 表名 modify  列名  null
alter table 表名 modify 列名 default null

级联

参考文档
Oracle在外键的删除上有NO ACTION(类似RESTRICT)、CASCADE和SET NULL三种行为。
NO ACTION指当删除主表中被引用列的数据时，如果子表的引用列中包含该值，则禁止该操作执行。
SET NULL指当删除主表中被引用列的数据时，将子表中相应引用列的值设置为NULL值。SET NULL有个前提就是外键引用列必须可以设置为NULL。

级联删除
在创建外键约束时可以添加 on delete cascade 选项，那么当主表的数据被删除时，子表对应的行同样也自动被删除。
级联更新

Oracle本身并不支持外键的级联更新，不过可以按照如下方法达到级联更新的效果。首先要先了解Oracle延迟约束和非延迟约束。非延迟约束就是在修改记录的时候会立刻进行约束条件的查看，是否因为违反了某些约束条件而不能执行修改。延迟约束不会在刚进行修改的时候进行约束查看，只有提交的时候才会检查。Oracle的级联更新就是使用这个特性来实现的。 Oracle的外键默认是非延迟约束，修改学生的外键为延迟约束。

--删除学生表(TB_STUDENT)上的已有外键 
 ALTER TABLE TB_STUDENT DROP CONSTRAINT FK_TB_STUDENT_CLASS_ID;
  3 --添加延迟约束外键 
   ALTER TABLE TB_STUDENT ADD CONSTRAINT FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES TB_CLASS (ID) ON DELETE CASCADE DEFERRABLE;

设置触发器，当班级表(TB_CLASS)的主键改变了，就更新学生表(TB_STUDENT)的外键(CLASS_ID)。

CREATE OR REPLACE TRIGGER TGR_TB_CLASS_UPDATE
 AFTER UPDATE OF ID ON TB_CLASS FOR EACH ROW BEGIN  
  IF :OLD.ID<>:NEW.ID 
  THEN    
   UPDATE TB_STUDENT SET CLASS_ID=:NEW.ID WHERE CLASS_ID=:OLD.ID;   END IF; 
   END;

表设计

范式

第一范式
对于表中的每一行，必须且仅仅有唯一的行值。在一行中的每一列仅有唯一的值
并且具有原子性

有主键（必须且仅仅有唯一的行值)
字段不可以在拆分(仅有唯一的值并且具有原子性)
比如：联系方式：24040486@qq.com.185029999 不符合第一方式。因为联系方式Email和电话。并且具有两个值

第二范式：
非主键列是主键的子集，非主键列活动必须完全依赖两个主键。

表不可以再拆分(表的原子性)
非主键列是主键的子集不相关的信息不要放到一张表，确保表中的每列都和主键相关。
第三范式
非主键列互不依赖

表关系设计

一对多多的一方引用一的一方
一对一两表共用主键
多对多通过第三张表实现，建立两个一对多的关系

1.第一种方式，通过第三张表关联两张多对多的表中的主键字段，通过第三张中表的字段的id主键查询多对多表数据

create table course(id number(4) not null,
teacher_id number(4) not null,
student_id number(4) not null);

2.第二种方式：多对多表关系的两张表在第三张表中建立联合主键

create table course(id number(4) not null,
teacher_id number(4) not null,
student_id number(4) not null);

PLSQL

PL/SQL(Procedural Language /Structured Query Language)过程语言/结构化查询语言

PL/SQL基本格式

PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成；
DECLARE （声明）
声明一些变量、常量、用户定义的数据类型及游标

name varchar(30);-- 声明时不设置值
name varchar(30):='jack';--声明时带有默认值
name person.name%type ;--直接引用一个表的数据类型

BEGIN(主体)
主程序体，在这里可以加入各种合法语句。
EXCEPTION(异常处理)
异常处理程序，当程序中出现错误时执行这一部分。
END(结束)

PL/SQL有效字符

大小写的英文字母
0~9的阿拉伯数字
下划线
操作符，包括+、-、*、/、<、>、！、=、@、%等
最大长度为30个字符，不区分大小写，但建议适当使用大小写，增加程序的可读性

变/常量声明

在这里插入图片描述

输出

set serveroutput on – SQL*Plus需要，PL/SQL Developer 不需要

在这里插入图片描述

接受用户的输入赋值&变量名称

declare
begin
	dbms_output.putline('你输入的值是:'|| '&word');
end;

into 赋值

用into关键字可以将查询结果的值，赋给变量：
在这里插入图片描述

流程控制

if … then …

if … then …
elsif … then…
else …
end if;
示例

declare 
	score number :=&score;--从控制台输入一个分数
	if score >=90 then 
		dbms_output.put_line('学生的考试成绩为优秀');
	elsif score >=80 then 
			dbms_output.put_line('学生的考试成绩为良好');
	elsif score >=60 then 
			dbms_output.put_line('学生的考试成绩为及格');		
	else 
		dbms_output.put_line('学生的考试成绩为不及格');				
	end if;		
begin

case when … when

case var
when … then
when … then
else
end(类似于三元表达式，只能赋值，不能有动作)
示例：sql语句当中

select 
case sex 
	when 0 then '男'
	when 1 then '女'
	else '未知;
	end
	from student where id = 1;

PL/SQL当中
在这里插入图片描述

for 循环

for 变量 in 起始值 … 结束值
loop
end loop
在这里插入图片描述

loop循环(自动退出)

loop
exit when …
end loop;
在这里插入图片描述

loop循环(手动退出)

在这里插入图片描述

while 循环

while …
loo …
end loop
在这里插入图片描述

异常处理

所有异常

ACCESS_INFO_NULL 在未初始化对象时出现
CASE_NOT_FOUNF 在case语句中的选项与用户输入的数据不匹配时出现
COLLECTION_IS_NULL 在给尚未初始化的表或数组赋值时出现
CURSOR_ALREADY_OPEN 用户试图重新打开已经打开的游标时出现。在重现打开游标前必须先将其关闭
INVALID_CURSOR 在执行非法游标运算（如fetch一个尚未打开的游标)时出现
LOGIN_DENIED 输入的用户名或密码无效时出现
SOTRAGE_ERROR 在内存损坏或PL/SQL耗尽内存时出现
DUP_VAL_ON_INDEX 用户试图将重复的(duplicate)值存储在使用唯一索引的数据库列中时出现(insert into student (id) values (1) 1为已存在的id)
INVALID_NUMBER 将字符串转换为数字时出现(select id into i from student where name = 3
NO_DATA_FOUND
在表中不存在请求的行时出现(select id into i from student where id = 1）
TOO_MANY_ROWS
在执行select into 语句返回多行时出现(select id into i from student)
VALUE_ERROR
变量中的列值超出变量的大小或类型(i number(1); i:=‘a’😉
ZERO_DIVIDE
以零做除数时出现(i number;i:=1/0)

declare 
	insert into t_student (id) values (2);
	exception
	when DUP_VAL_ON_INDEX then
	dbms_output.put_line('id已存在无法插入');
	when others then 
	dbms_output.put_line('出现了未知的错误');
end;

预定义异常

DECLARE
BEGIN
…
EXCEPTION
WHEN 异常名称 THEN
— 异常处理语句
— 异常可以使用SQLCODE输出错误编码，以及使用SQLERRM 来输出错误信息：
dbms_output.put_line(‘值类错误’|| SQLCODE|| SQLERRM);
WHEN 异常名称 THEN

非预定义异常

Oracle允许自定义的错误代码的范围为-20000 — -20999

# 示例1：
DECLARE 
BEGIN
	RAISE_APPLICATION_ERROR(-20001,'I am sorry to seee you ');
EXCEPTION
	WHEN OTHERS THEN
	dbms_output.put_line(SQLCODE||'    '|| SQLERRM);
END;

存储过程

存储过程，存储在数据库中供用户程序调用的程序叫存储过程
(数据库中的程序)

创建存储过程

用create procedure 命令建立存储过程
语法：

create [or replace ] procedure 过程名 [参数名称1 in /out 参数类型1,参数名称2 in/out 参数类型2...)] as 
PLSQL 子程序体(begin .... end)

形参声明

基本格式：参数名称 in/out 参数类型

int 类型为输入类型的参数，out类型为输出类型的参数
过程没有返回值，in 类型的参数，只可以接收值，不能再给in类型的参数设置新的值
.利用out参数在过程中实现返回多个值
声明接收参数的只声明类型，不声明大小

实参声明

实参声明跟在as和begin之间即可，不需要declare 关键字

存储过程的调用

3.1 exec 过程名称 [(参数1，参数2…)];PLSQL不支持这种
3.2 begin过程名称[(参数1，参数2…)]end;

存储过程示例

在这里插入图片描述
创建存储过程
批量向学生表写入100条数据

执行存储过程

begin
student_batch_insert
end;

创建存储过程
在这里插入图片描述
调用存储过程

游标（Cursor)

游标时SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用
就是用于临时存储从数据库中提取的数据块。
游标的三种类型：隐式Cursor,显示Cursor,Ref Cursor(动态Cursor).

隐式Cursor

隐式Cursor是系统自动打开和关闭Cursor。
可以通过隐式Cursor的属性来了解DML操作的状态和结果(行数和是否成功),从而达到流程的控制。
隐式和显式Cursor都具有的属性：
SQL%ROWCOUNT整型，代表DML语句成功执行的数据行数
SQL%FOUND 布尔型，值为true代表操作成功
SQL%NOTFOUND 布尔型，与SQL%FOUND相反
SQL%ISOPEN 布尔型，DML执行过程中为真，结束后为假
示例：
在这里插入图片描述

动态 Cursor (Ref Cursor)

运行时才产生的游标，是动态游标
Ref cursor的使用步骤：
Type自定义游标类型 is ref cursor;-- 自定义了一个游标类型，并声明它是一个动态游标

游标定义自定义游标类型; – 声明一个游标，它是自定义类型
Open 游标名称 for sql 语句;
Fetch 游标名称 into 行对象;
Close 游标名称
示例：

在这里插入代码片

视图

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加SQL函数、WHERE以及JOIN语句，我们也可以提交数据，就像这些来自于某个单一的表
注释：数据库的设计和结构不会受到视图中的函数、where或join语句的影响。
创建格式
create view 视图名 as

create view v_student as 
select 
* from t_student

同义词

在这里插入图片描述
为表student创建同义词 s

create synonym s for student;

迷失的小鹿

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle实战学习记录

oracle实战学习记录序列修改表数据行(update)删除数据行(delete)截断表drop,delete,truncate的区别分页查询oracle的隐藏列分页查询的原理内连接自然连接外连接左外连接右外连接全外连接exists运算符聚集函数数据库完整性(约束)实体完整性（Entity Integrity）域完整性(Domain Integrity)参照完整性(Referential Integrity)添加约束唯一约束主键约束检查约束 check默认约束和非空约束外键约束删除约束查询约束启用约束禁用约
复制链接

扫一扫