oracle数据库

最新推荐文章于 2025-04-30 14:41:06 发布

So Young007

最新推荐文章于 2025-04-30 14:41:06 发布

阅读量202

点赞数

文章标签：数据库 sql mysql

本文链接：https://blog.youkuaiyun.com/qiaoge66/article/details/115054862

版权

这篇博客详细介绍了Oracle数据库的表设计，包括约束的创建、查看、禁用与启用、删除等操作，以及DDL语言结构，如创建、修改和删除表。此外，还探讨了事务的特性、隔离级别和操作，以及DML中的插入、更新和删除数据。最后，讲解了序列的使用，并提供了操作实例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.表设计

前提: 设计表首先应该按需遵循三范式

确定表名
确定字段名类型 +约束(主键外键非空默检查认唯一)
主键：唯一标识一条记录（唯一并且非空）

唯一：唯一

非空：不能为空

默认：当没给值时使用给定一个默认值

外键：参考其他表（自己）的某个（某些）字段

检查：自定义的规则

用户表
表名 tb_user
主键 userid
字段名 中文 类型 为空 默认值 其他说明
userid 流水号 number(5) 否 主键
username 用户名 varchar2(30) 否 长度在4-20
userpwd 密码 varchar2(20) 否 长度在4-18
age 年龄 number(3) 18 大于>=18
gender 性别 char(2) 男 男or 女
email 邮箱 varchar2(30) 唯一
regtime 注册日期 date sysdate
备注
文章表
表名 tb_txt
主键 txtid
字段名 中文 类型 为空 默认值 其他说明
txtid 流水号 number(10) 否 主键
title 标题 varchar2(32) 否 长度在4-30
txt 正文 varchar2(1024)
pubtime 发布时间 date sysdate
userid 发布人 number(5) 外键，参考tb_user的
userid列
备注

2.创建表

表名必须唯一，如果存在，必须删除

create table 表名(
字段名 类型(长度) 约束,
...其他字段....
..约束........
);

3.创建表（不加约束）

表名 tb_user
主键 userid
字段名 中文 类型 为空 默认值 其他说明
userid 流水号 number(5) 否 主键
username 用户名 varchar2(30) 否 长度在4-20
userpwd 密码 varchar2(20) 否 长度在4-18
age 年龄 number(3) 18 大于>=18
gender 性别 char(2) 男 男or 女
email 邮箱 varchar2(30) 唯一
regtime 注册日期 date sysdate
create table tb_user(
userid number(5),
username varchar2(30),
userpwd varchar2(20),
age number(3) ,
gender char(2) ,
email varchar2(30),
regtime date
);
--加入注释
comment on table tb_user is '用户表';
comment on column tb_user.userid is '流水号,主
键';
comment on column tb_user.username is '用户名';
comment on column tb_user.userpwd is '密码';
comment on column tb_user.age is '年龄';
comment on column tb_user.gender is '性别';
comment on column tb_user.email is '邮箱';
comment on column tb_user.regtime is '注册日
期';
表名 tb_txt
主键 txtid
字段名 中文 类型 为空 默认值 其他说明
txtid 流水号 number(10) 否 主键
title 标题 varchar2(32) 否 长度在4-30
txt 正文 varchar2(1024)
pubtime 发布时间 date sysdate
userid 发布人 number(5) 外键，参考tb_user的
userid列
create table tb_txt(
txtid number(10),
title varchar2(32),
txt varchar2(1024),
pubtime date,
userid number(5)
);
--注释
comment on table tb_txt is '文章表';
comment on column tb_txt.txtid is '流水号,主键';
comment on column tb_txt.title is '标题';
comment on column tb_txt.txt is '正文';
comment on column tb_txt.pubtime is '发布时间';
comment on column tb_txt.userid is '发布人,外
键，参考 tb_user 的 userid 列';
--删除 （先删除从表 再删除主表 ；同时删除约束）
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;

4.创建表（同时创建约束+默认名称

这种在创建表的同时创建约束并使用默认约束名称的方式，后期不方便排错，所以不推荐使用。其主要的优点是简单。

--删除 （先删除从表 再删除主表 ；同时删除约束）
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;
表名 tb_user
主键 userid
字段名 中文 类型 为空 默认值 其他说明
userid 流水号 number(5) 否 主键
username 用户名 varchar2(30) 否 长度在4-20
userpwd 密码 varchar2(20) 否 长度在4-18
age 年龄 number(3) 18 大于>=18
gender 性别 char(2) 男 男or 女
email 邮箱 varchar2(30) 唯一
regtime 注册日期 date sysdate
create table tb_user(
userid number(5) primary key,
username varchar2(30) check(length(username)
between 4 and 20) not null ,
userpwd varchar2(20) not null
check(length(userpwd) between 4 and 18),
age number(3) default(18) check(age>=18),
gender char(2) default('男') check(gender
in('男','女')),
email varchar2(30) unique,
regtime date default(sysdate)
);
--加入注释
comment on table tb_user is '用户表';
comment on column tb_user.userid is '流水号,主
键';
comment on column tb_user.username is '用户名';
comment on column tb_user.userpwd is '密码';
comment on column tb_user.age is '年龄';
comment on column tb_user.gender is '性别';
comment on column tb_user.email is '邮箱';
comment on column tb_user.regtime is '注册日
期';
表名 tb_txt
主键 txtid
字段名 中文 类型 为空 默认值 其他说明
txtid 流水号 number(10) 否 主键
title 标题 varchar2(32) 否 长度在4-30
txt 正文 varchar2(1024)
pubtime 发布时间 date sysdate
userid 发布人 number(5) 外键，参考tb_user的
userid列
create table tb_txt(
txtid number(10) primary key,
title varchar2(32) not null
check(length(title)>=4 and length(title)<=30),
txt varchar2(1024),
pubtime date default(sysdate),
userid number(5) references tb_user(userid)
on delete set null
);
--注释
comment on table tb_txt is '文章表';
comment on column tb_txt.txtid is '流水号,主键';
comment on column tb_txt.title is '标题';
comment on column tb_txt.txt is '正文';
comment on column tb_txt.pubtime is '发布时间';
comment on column tb_txt.userid is '发布人,外
键，参考tb_user的userid列';

5.创建表（同时创建约束+指定名称）

创建表的同时创建约束并指定约束的名称，后期方便排错，推荐使用

--删除 （先删除从表 再删除主表 ；同时删除约束）
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;
表名 tb_user
主键 userid
字段名 中文 类型 为空 默认值 其他说明
userid 流水号 number(5) 否 主键
username 用户名 varchar2(30) 否 长度在4-20
userpwd 密码 varchar2(20) 否 长度在4-18
age 年龄 number(3) 18 大于>=18
gender 性别 char(2) 男 男or 女
email 邮箱 varchar2(30) 唯一
regtime 注册日期 date sysdate
create table tb_user(
userid number(5),
username varchar2(30) constraint
nn_user_name not null ,
userpwd varchar2(20) constraint nn_user_pwd
not null ,
age number(3) default(18) ,
gender char(2) default('男'),
email varchar2(30),
regtime date default(sysdate),
constraint pk_user_id primary key (userid),
constraint ck_user_name
check(length(username)between 4 and 20) ,
constraint ck_user_pwd check(length(userpwd)
between 4 and 18),
constraint ck_user_age check(age>=18),
constraint ck_user_gender check(gender
in('男','女')),
constraint uq_user_email unique(email)
);
--加入注释
comment on table tb_user is '用户表';
comment on column tb_user.userid is '流水号,主
键';
comment on column tb_user.username is '用户名';
comment on column tb_user.userpwd is '密码';
comment on column tb_user.age is '年龄';
comment on column tb_user.gender is '性别';
comment on column tb_user.email is '邮箱';
comment on column tb_user.regtime is '注册日
期';
表名 tb_txt
主键 txtid
字段名 中文 类型 为空 默认值 其他说明
txtid 流水号 number(10) 否 主键
title 标题 varchar2(32) 否 长度在4-30
txt 正文 varchar2(1024)
pubtime 发布时间 date sysdate
userid 发布人 number(5) 外键，参考tb_user的
userid列
create table tb_txt(
txtid number(10),
title varchar2(32) constraint nn_txt_title
not null,
txt varchar2(1024),
pubtime date default(sysdate),
userid number(5) ,
constraint pk_txt_id primary key(txtid),
constraint ck_txt_id check(length(title)>=4
and length(title)<=30),
constraint fk_txt_ref_user_id foreign
key(userid) references tb_user(userid) on
delete cascade
);
--注释
comment on table tb_txt is '文章表';
comment on column tb_txt.txtid is '流水号,主键';
comment on column tb_txt.title is '标题';
comment on column tb_txt.txt is '正文';
comment on column tb_txt.pubtime is '发布时间';
comment on column tb_txt.userid is '发布人,外
键，参考tb_user的userid列';

6.创建表（追加创建约束+指定名称）

7.已有表中拷贝结构

create table 表名 as select 字段列表 from 已有表
where 1!=1;
--拷贝结构 emp
create table emp_his as select ename,sal from
emp where 1!=1;
--拷贝结构 emp +数据
create table emp_his2 as select ename,sal from
emp where sal>2000;

8.约束（了解）

在 oracle中所有的一切都是对象，约束也是一个个的对象，除了能创建约束我们还能对约束进行一些其他的操作

9.查看某个用户的约束

select constraint_name, constraint_type
from user_constraints
where owner = upper('SCOTT');

10.查看表的约束

select constraint_name, constraint_type
from user_constraints
where table_name = upper('emp');

11.查看字段名+约束

select constraint_name, column_name
from user_cons_columns
where table_name = upper('emp');

12.约束的禁用与启用

ALTER TABLE tb_user disable constraint
nn_user_name;
ALTER TABLE tb_user enable constraint
nn_user_name;

13.删除约束

alter table tb_user drop constraint
uq_user_email cascade;

14.修改约束

--非空
alter table tb_user modify (username
varchar2(20));
--默认
alter table tb_user modify (age default null);

15.DDL

SQL语言结构:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lDUt6Vb5-1616328479679)(C:\Users\李银桥\AppData\Roaming\Typora\typora-user-images\image-20210320210718634.png)]

DDL（Data Definition Language 数据定义语言）用于操作对象和对象的属性，这种对象包括数据库本身，以及数据库对象，像：表、视图等等，DDL 对这些对象和属性的管理和定义具体表现在 create、drop 和 alter 上。特别注意：DDL 操作的“对象”的概念，”对象“包括对象及对象的属性，而且对象最小也比记录大个层次。以表举例：create 创建数据表，alter 可以更改该表的字段， drop 可以删除这个表，从这里我们可以看到，DDL 所站的高度，他不会对具体的数据进行操作。

DDL 的主要语句（操作）

语句	作用
create	可以创建数据库和数据库的一些对象
drop	可以删除数据表、索引、条件约束等
alter	修改数据表定义及属性

16.删除表

drop table 表名 (cascade constraints)
--删除表
drop table emp_his;
--主从表关系下删除表
--先删除从表 再删除主表 ；同时删除约束
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;
--删除主表的同时级联删除约束
drop table emp_his cascade constraints;

17.修改表结构

1.修改表名 :rename to

修改列名: alter table 表名 rename column to
修改类型: alter table 表名 modify(字段类型)
修改约束: 先删除后添加
添加列: alter table 表名 add 字段类型
删除列:alter table 表名 drop column 字段

--修改表名
rename tb_txt to tb_txt_new;
--修改列名
alter table tb_txt_new rename column txtid
to tid;
--修改类型
alter table tb_txt_new modify(tid
varchar2(20));
--添加列
alter table tb_txt_new add col varchar2(30);
--删除列
alter table tb_txt_new drop column col;
select * from tb_txt_new;

18.事务

事务是指作为单个逻辑工作单元执行的一组相关操作。这些操作要求全部完成或者全部不完成。使用事务是为了保证数据的安全有效。

19.事务的特点

事务有一下四个特点：（ACID）

原子性（Atomic）：事务中所有数据的修改，要么全部执行，要么全部不执行。
一致性（Consistence）：事务完成时，要使所有所有的数据都保持一致的状态，换言之：通过事务进行的所有数据修改，必须在所有相关的表中得到反映。
1. 隔离性（Isolation）：事务应该在另一个事务对数据的修改前或者修改后进行访问。
  1. 持久性（Durability）：保证事务对数据库的修改是持久有效的，即使发生系统故障，也不应该丢失。

20.事务的隔离级别

当事务之间发生并发时有几个隔离级别:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AX4DE1ge-1616328479681)(C:\Users\李银桥\AppData\Roaming\Typora\typora-user-images\image-20210320211613073.png)]

Oracle 默认的隔离级别是 read committed。 Oracle 支持上述四种隔离级别中的两种:read committed 和 serializable。除此之外， Oralce 中还定义 Read only 和 Read write 隔离级别。

Read only：事务中不能有任何修改数据库中数据的操作语句，是 Serializable 的一个子集。 Read write：它是默认设置，该选项表示在事务中可以有访问语句、修改语句，但不经常使用。

丢失更新：两个事务同时存储，一个存储 100 ，一个存储 200，最终可能至存储了 200 或者 100，那另一个的更新就没成功，即结果不为预想的 300 脏读：事务 T1 更新了一行数据，还没有提交所做的修改，T2 读取更新后的数据，T1回滚，T2 读取的数据无效，这种数据称为脏读数据。不可重复读：事务 T1 读取一行数据，T2 修改了 T1 刚刚读取的记录，T1 再次查询，发现与第一次读取的记录不相同，称为不可重复读。幻读：事务 T1 读取一条带 WHERE 条件的语句，返回结果集，T2 插入一条新纪录，恰好也是 T1 的 WHERE 条件，T1 再次查询，结果集中又看到 T2 的记录，新纪录就叫做幻读。

21.事务的开启

自动开启于 DML 之 insert delete update

22.事务的结束

1.成功

正常执行完成的 DDL 语句：create、alter、drop
正常执行完 DCL 语句 GRANT、REVOKE
正常退出的 SQLPlus 或者 SQL Developer 等客户端
如果人工要使用隐式事务，SET AUTOCOMMIT ON (只针对一个连接)
手动提交：使用 commit

2.失败

rollback ，手动回滚
非法退出意外的断电

rollback 只能对未提交的数据撤销，已经 Commit 的数据是无法撤销的，因为 commit 之后已经持久化到数据库中。

23.DML

DML（Data Manipulation Language 数据操控语言）用于操作数据库对象中包含的数据，也就是说操作的单位是记录。

DML 的主要语句（操作）

语句	作用
Insert	向数据表张插入一条记录
Delete	删除数据表中的一条或多条记录，也可以删除数据表中的所有记录，但是，它的操作对象仍是记录
Update	用于修改已存在表中的记录的内容

使用场景：

insert	注册
update	修改密码
delete	退出、删除、剔除会员
select	登录\|查看会员

24.序列

使用工具|程序管理流水号，序列在创建时没有与表关联 ,在操作数据时与表关联

25.创建

create sequence 序列名 start with 起始值
increment by 步进;
create sequence seq_tb_user start with 2
increment by 2;

26.使用

在操作数据 添加 更新 -->主键
1)、currval :当前值
2)、nextval:下个值
select seq_tb_user.nextval from dual;
select seq_tb_user.currval from dual

27.删除

drop sequence 序列名;
drop sequence seq_tb_user;

28.insert

insert into 表名 [(字段列表)] values(值列表);
添加记录

添加记录时需要满足一下条件
类型长度兼容: 字段兼容值
值满足约束 :主键 (唯一+非空) 非空(必填) 唯一(不重复 ) 默认(没有填写使用默认值) 检查(满足条件) 外键(参考主表主键列的值) 个数必须相同: 指定列，
个数顺序与列相同；没有指定，个数与表结构的列个数和顺序相同 (null也得占位，没有默认值)

29.添加数据

insert into 表名 values(和表结构顺序和个数和类型一致的数据可以手写也可以从别的表中获取);
insert into 表名(指定列名) values(和指定的列个数、
顺序、类型一致的列数据)

30.eg;

insert into 表(指定列) select 查询列 from 源表
where 过滤数据; insert into 表(指定列) values(值
列表);
insert into 表名 select 查询列 from 源表 where 过
滤数据; insert into 表名 values(值列表 );

31.操作实例

创建序列： 一般为一张表准备一个序列
create sequence seq_user_id increment by 1
start with 1 ; create sequence seq_txt_id
increment by 1 start with 1 ;
准备表：

eg: 添加所有列

--没有列|所有列 所有字段,值个数必须为7个，没有默认值，
非空可以使用null占位
insert into tb_user
values
(seq_user_id.nextval, 'test', 'test123',
null, '女', null, sysdate);

eg: 添加指定列(推荐: 方便阅读操作、更改顺序、非空列和默认值列可以选填)

--指定所有列
insert into tb_user
(username, userid, userpwd, gender, age,
email, regtime)
values
('shsxt',
seq_user_id.nextval,
'verygood',
'男',
27,
'bjsxt@qq.com.cn',
sysdate);
--指定部分列(非空列和默认值列 可以选填, 必填项|主键列
必须指定 ,没有指定列 如果存在默认值，使用默认值填充，否
则null填充)
insert into tb_user
(username, userid, userpwd)
values
('shsh', seq_user_id.nextval, 'shanghai');
commit;
insert into tb_user
(username, userid, userpwd, age)
values
('穷屌丝男', seq_user_id.nextval, '极品女士',
20);
commit;

eg: 添加外键

--添加同时查询 :使用一条sql 查询外键 ,直接添加 (少
用，外键只有一个值，多个值运行错误)一条sql搞定
insert into tb_txt
(txtid, title, userid)
values
(seq_txt_id.nextval,
'iphone6来了',
(select userid from tb_user where username
= '穷屌丝男'));
--先查询后添加 :
--查询值
select userid from tb_user where username='穷屌
丝男';
--添加
insert into tb_txt(txtid,title,userid)
values(seq_txt_id.nextval,'您的肾值钱吗?',10);
commit;

32.eg: 添加时间

--添加时间
insert into tb_user
(username, userid, userpwd, age, regtime)
values
('java程序猿',
seq_user_id.nextval,
'bjsxt',
20,
to_date('2014-10-31', 'yyyy-mm-dd'));
commit;

33.查看数据

select * from tb_user;
select * from tb_txt;

34.eg: 从已有表中添加数据

select * from emp_his;
insert into emp_his
(empno, ename, job)
select empno, ename, job from emp where sal
> 2000;
rollback;
select * from emp_his;
insert into emp_his
select empno, ename, job, mgr, hiredate,
sal, comm, deptno
from emp
where deptno = 20;

35.update

update 表名 set 字段=值 [,....] where 过滤行记
录;

要求：

记录存在
类型长度兼容: 字段兼容值
个数相同

36.更改数据

从已有表中查询数据更改字段值：

update 表名
set (字段列表) =
(select 字段列表 from 源表 where 过滤源表
记录)
where 更新记录的条件手动更改字段值：

37.操作实例

eg:手动更改字段值

select * from tb_user;
--重置所有人员的密码 8888
update tb_user set userpwd=8888 where 1=1;
--修改 shsxt 的密码 和年龄
update tb_user set userpwd='good',age=29 where
username='shsxt' and userpwd='verygood';
commit;

38.eg:从表中获取

--了解 将用户名与密码对换
update tb_user
set (username, userpwd) =
(select userpwd, username from tb_user
where userid = 6)
where userid = 6;
--select 只能返回一行数据:
update tb_user
set (username, userpwd) =
(select userpwd, username from tb_user)
where userid = 6;

39.delete

delete [from] 表名 where 过滤行记录说明：

delete 可以删除指定部分记录，删除全部记录
记录上存在主外键关联时，删除存在关联的主表的

记录时,注意参考外键约束，约束强制不让删除先删除从表再删除主表

--删除全部数据
delete from tb_user where 1=1;
--删除指定数据
delete from tb_user where userid<10;
--主外键关联时，注意 参考约束， 约束强制不让删除
--默认先删除从表 再删除主表
delete from tb_txt where 1=1;
delete from tb_user where 1=1;
commit;
--删除主表数据,并设置从表数据为null
--删除主表数据,级联删除从表数据

40.截断数据

truncate: 截断所有的数据 ,如果截断的是主表，结构不能存在外键关联，截断数据同时从结构上检查

create table emp_his as select * from emp
where 1=1;
select * from emp_his;
--截断所有的数据
truncate table emp_his;
--不能截断: truncate table dept;

41.截断数据与删除数据区别 truncate 与delete 区别

1、truncate -->ddl ，不涉及事务，就不能回滚 delete -->dml ,涉及事务，可以回滚

2、truncate 截断所有的数据 delete 可以删除全部或者部分记录

3、truncate从结构上检查是否存在主外键，如果存在，不让删除

delete 从记录上检查是否存在主外键，如果存在，按参考外键约束进行删除。

42.测试：

truncate table tb_user;
delete from tb_user;