当前位置： 首页 > news >正文

ORACLE的表维护

news 2025/8/1 15:09:37

1 数据类型

1.1 常用类型

  • 字符型：char、varchar2
  • 数字类型：number
  • 日期类型：date
  • char

固定长度字符串，最大长度2000bytes

  • varchar2

可变长度的字符串，最大长度4000bytes，可做索引的最大长度749

  • nchar

根据字符集而定的固定长度字符串，最大长度2000bytes

  • nvarchar2

根据字符集而定的可变长度字符串，最大长度4000bytes

  • long

超长字符串——已过时，最大长度2G（231-1），足够存储大部份著作

  • date

日期（日-月-年），格式DD-MM-YY（HH-MI-SS），经过严格测试无千年虫问题

  • raw

固定长度的二进制数据——已过时，最大长度2000bytes ，可存放多媒体图象声音等

  • long raw

可变长度的二进制数据，最大长度2G

  • blob

二进制数据 ，最大长度4G

  • clob

字符数据 ，最大长度4G

  • nclob

根据字符集而定的字符数据 ，最大长度4G

  • bfile

存放在数据库外的二进制数据，最大长度4G

  • rowid

数据表中记录的唯一行号，长度10 bytes

  • nrowid

二进制数据表中记录的唯一行号，最大长度4000bytes

  • number(p,s)

数字类型，P为整数位，S为小数位

  • decimal(p,s)

数字类型，P为整数位，S为小数位

  • integer

整数类型，小的整数

  • float

浮点数类型，NUMBER(38)，双精度

  • real

实数类型，NUMBER(63)，精度更高

2 创建表结构

  • 表结构

表名
字段(列名) 单元格类型(长度) 约束 注释

2.1 创建表

  • 语法：

create table 表名(
字段1 类型(长度) [约束],
字段1 类型(长度) [约束],
…
字段n 类型(长度) [约束]
);

  • 示例：
--学生表：学号 姓名 班级 专业
create table student(sno            number(6),sname          varchar(20),cid        number(10),major          varchar(40)
);

教材表：书号 书名 作者 价格
create table book(bno varchar(20),bname varchar(40),author varchar(20),money  number(7,2)
);

2.2 查看表结构

  • 语法：desc 表;
  • 示例：
--查看表的结构
desc student;
desc book;

--查看表的内容
select * from student;
select * from book;

2.3 删除表

  • 语法：deop table 表名;
  • 示例：
drop table book;

2.4 注释

2.4.1 添加表注释

  • 语法：comment on table 表 is ‘注释’;
  • 示例：
comment on table student is '学生表';
comment on table book is '书表';

2.4.2 查看表注释

  • 语法：

select table_name, comments
from user_tab_comments
where table_name = ‘表名大写’;

  • 示例：
SELECT table_name, comments
FROM user_tab_comments
WHERE table_name = 'STUDENT';

SELECT table_name, comments
FROM user_tab_comments
WHERE table_name = 'BOOK';

2.4.3 添加列注释

  • 语法：comment on column 表.列 is ‘注释’;
  • 示例：
comment on column student.sno is '学号';
comment on column student.sname is '学生姓名';
comment on column student.classid is '班级';
comment on column student.major is '专业';

comment on column book.bno is '书号';
comment on column book.bname is '书名';
comment on column book.author is '作者';
comment on column book.money is '价格';

2.4.4 查看列注释

  • 语法1：desc 表;
  • 示例：
desc student;
desc book;
  • 语法2：

SELECT table_name, column_name, comments
FROM user_col_comments
WHERE table_name = ‘表名大写’;

  • 示例：
SELECT table_name, column_name, comments
FROM user_col_comments
WHERE table_name = 'STUDENT';

SELECT table_name, column_name, comments
FROM user_col_comments
WHERE table_name = 'BOOK';

2.4.5 修改注释

重写添加注释，会对原有注释进行覆盖

  • 示例：
--修改表注释
comment on table book is '教材表';
SELECT table_name, comments
FROM user_tab_comments
WHERE table_name = 'BOOK';

--修改列注释
comment on column book.money is '价钱';
desc book;

3 添加和修改约束

3.1 什么是约束

向创建好的表中填充值的时候必须满足的此表定义好的条件。

如定义了学生表，规定学号不能不写，学号不能重复，那么这就是一个约束。

3.2 oracle的5种约束

主键约束：PRIMARY KEY
外键约束：FOREIGN KEY
非空约束： NOT NULL
检查约束： CHECK
唯一约束： UNIQUE

3.3 主键约束

ORACLE会自动为具有PRIMARY KEY约束的字段创建一个唯一索引和一个NOT NULL约束。

主键约束的字段在本表中必须唯一且不能为null值。

主键在创建时可以给约束起名字，约束名不能重复；也可以不起名字，由系统命名。

3.3.1 主键的分类

  • 自然主键：某些字段天生带有唯一、非空的特性，如学号、工号

  • 非自然主键：定义一个与本记录中其它字段毫无关联的字段作为主键，如

    • 1.自然序列：oracle中通过sequeue实现，mysql中通过主键自增实现

    • 2.uuid

      UUID，全称为UniversallyUniqueIdentifier，是一种用于软件构建的标准标识符。它基于特定的算法生成，确保在全球范围内具有唯一性。
      UUID通常由32个16进制数字组成，并按照8-4-4-4-12的分组格式显示。

    • 3.guid

      GUID，全称为GloballyUniqueIdentifier，是微软公司对UUID标准的一种实现。与UUID类似，GUID也是一种全局唯一的标识符，用于在分布式系统中标识对象。

3.3.2 建立主键约束

  • 方法一：在建表时在需要约束的字段后加 primary key，只适用于单主键
create table bkeep1(empno number(5)primary key);create table bkeep2(empno number(5),ename varchar(10)primary key
);
desc bkeep1;
desc bkeep2;
  • 方法二：建表时，在所有字段定义完毕后创建，可以是单主键也可以是多（联合）主键
create table bkeep3(empno number(5),ename varchar(10),constraint bkeep2 primary key(empno,ename)
);
desc bkeep3;
  • 方法三：表创建完毕，通过修改表的结构，添加或删除主键
create table bkeep4(empno number(5),ename varchar(10)
);
alter table bkeep4 add primary key(empno);
desc bkeep4;

3.4 外键约束

外键：外部主键

A表外键字段的值参考B表的主键（或唯一）的值，只有在B表中出现的值才能作为A表外键字段的值。A表被称为B表的从表，B表则为A表的主表。

A表的外键字段必须和B表的参考字段类型相同，值范围大于等于主表。

外键值可以为空。

可以定义多个外键。

  • 示例：
--建表
create table people(ppno  number(10) primary key,pname varchar2(20),sex   varchar2(10),idcard number(20)
);
create table idcards(ino  number(10) primary key,idcard number(20),ppno number(10),picture varchar2(10),effective date
);
--定义外键
insert into people values(1,'zs','男'，101);
insert into people values(2,'ls','男'，101);
--alter table people add foreign key(idcard) references idcards(ino);
alter table idcards add foreign key(ppno) references people(ppno);
insert into idcards values(1,1,1,111,'23-1月-2029');
insert into idcards values(2,2,2,222,'23-1月-2029');
--违反约束规则
insert into idcards values(3,3,3,333,'23-1月-2029');

3.4.1 在建表时创建外键

create table a(ano  number(10) primary key,aname varchar2(20)
);

create table b(bno  number(10) primary key,bname varchar2(20),ano number(10) references a(ano)
);

3.4.2 在建表语句的最后创建外键

create table a(ano  number(10) primary key,aname varchar2(20)
);

create table b(bno  number(10) primary key,bname varchar2(20),ano number(10),foreign key(ano) references a(ano)
);

3.4.3 建表后通过修改表创建外键

create table a(ano  number(10) primary key,aname varchar2(20)
);

create table b(bno  number(10) primary key,bname varchar2(20),ano number(10)
);

alter table b add foreign key(ano) references a(ano);

3.4.4 实例：1 vs 1

1 vs 1形式本质上是1 vs N的特殊情况，很少会用到

  • 建表
create table people(ppno  number(10) primary key,pname varchar2(20),sex   varchar2(10),idcard number(20)
);
create table idcards(ino  number(10) primary key,idcard number(20),ppno number(10),picture varchar2(10),effective date
);
  • 定义外键
alter table people add foreign key(idcard) references idcards(ino);
alter table idcards add foreign key(ppno) references people(ppno);
  • 插入数据
--两个表护卫外键无法插入任何数据
insert into people values(1,'zs','男'，101);
insert into idcards values(1,1,1,111,'23-1月-2029');

3.4.5 实例：1 vs N

这种形式遇到的较多

  • 建表
create table classroom(cid   number(10)    primary key,cname varchar2(20),address   varchar2(20)
);
create table student(sno   number(6)     primary key,sname varchar2(20),major varchar2(40),cid   number(10) references classroom(cid)
);
  • 插入数据
insert into classroom values(1,'计算机1班','301');
insert into student values(1,'a1','computer',1);
insert into student values(2,'b2','computer',1);

--班级3不存在，插入失败
insert into student values(3,'c3','computer',2);

--外键值可以为空，当外键存在后，更新它的值
insert into student values(3,'c3','computer',null);
insert into classroom values(2,'计算机2班','302');
update student set cid=2 where sno=3;

3.4.6 实例：N vs N

多对多的关系通常会通过第3方表（关系表）进行维护

  • 建表
create table student(sno   number(6)     primary key,sname varchar2(20)
);
create table course(courseid number(6)     primary key,cname varchar2(20)
);
create table relation(relationid number(5) primary key,studentid number(6) references student(sno),courseid number(6) references course(courseid)
);
  • 插入数据
insert into student values(1,'zs');
insert into student values(2,'ls');
insert into student values(3,'ww');
insert into course values(1,'美术');
insert into course values(2,'音乐');
insert into course values(3,'舞蹈');
insert into relation values(1,1,2);
insert into relation values(2,1,3);
insert into relation values(3,2,2);

3.4.4 主表和从表

主表对从表通常有5种控制权限

  • CASCADE(级联删除)

主表做了更改，从表外键跟着更改（ORACLE只支持级联删除）

创建外键时使用[on delete cascade]

create table a(aid number(5) primary key,aname number(10)
);
create table b(bid number(5) primary key,bname number(10),aid number(5),foreign key(aid) references a(aid) on delete cascade
);

--删除主表中的某行数据时，从表中对应的行数据也会被删除
insert into a values(1,101);
insert into a values(2,102);
insert into b values(1,201,1);
insert into b values(2,202,2);
delete from a where aid=1;
  • SET NULL(级联置空)

主表对应记录被删除，从表对应字段的值会被设置为null值。

创建外键时使用[on delete SET NULL]

create table c(cid number(5) primary key,cname number(10)
);
create table d(did number(5) primary key,dname number(10),cid number(5),foreign key (cid) references c(cid) on delete set null
);

--删除主表中的某行数据，从表中对应的数据的值会变为空
insert into c values(1,101);
insert into c values(2,102);
insert into d values(1,201,1);
insert into d values(2,202,2);
delete from c where cid=1;
  • NO ACTION

如有子记录，不允许删除主表的对应记录（ORACLE默认级别）

  • RESTRICT

拒绝对父表的删除或更新操作（ORACLE不支持）

  • SET DEFAULT

设置为初始值（ORACLE不支持）

3.5 非空约束

此约束定义的字段值不能为空

  • 建表时创建
create table a(aid number(3),aname varchar2(5) not null
);
  • 建完表后，修改表结构——之前添加的值不能有null
create table b(bid number(3),bname varchar2(5)
);
alter table b modify(bname number(5) not null);

3.6 唯一约束

此约束定义的字段值只能出现一次

  • 建表时创建
create table a(aid number(3) unique,aname varchar2(5)
);
  • 修改表结构
create table b(bid number(3),bname varchar2(5)
);
alter table b modify(bid number(5) unique);

3.7 检查约束

此约束定义的字段值不能超过约束定义的范围

  • 在建表时添加
create table a(age number(3) check ((age>=0) and (age<=150));
);
  • 修改表结构添加
create table b(sex varchar2(2)
);
alter table b add check ((sex='男') or (sex='女'));

3.8 默认约束

在ORACLE中，默认约束（Default Constraint）允许在建表时为列指定一个默认值。如果插入新行时没有为该列提供值，ORACLE将自动使用默认值。

  • 修改表结构添加
create table a(ano number(5) primary key,aname varchar2(5),deptno number(5)
);

insert into a values(1,'a1',6000);
insert into a(ano,aname) values(2,'a2');
alter table a modify deptno default 5000;
--只对修改后插入的数据有效
insert into a(ano,aname) values(3,'a3');
--插入值为空值时，默认约束不生效
insert into a values(3,'a3',null);
  • 建表时添加
create table a(ano number(5) primary key,aname varchar2(5),deptno number(5) default 5000
);

4 修改表结构

ORACLE允许在表创建后修改表结构，尽量少的执行修改表结构操作。

create table a(ano  number(5) primary key,aname varchar2(20)
);

create table b(bno  number(5) primary key,bname varchar2(5),ano number(5)
);

alter table b add foreign key(ano) references a(ano);

4.1 删除约束

  • 删除表约束
alter table b drop primary key;
  • 根据约束名删除约束（外键约束使用约束名删除）
alter table b drop constraints SYS_C0011147;
  • 如主键已被从表引用 ，cascade表示删除外键关联
alter table a drop primary key cascade;

4.2 添加新字段

alter table a add(sex varchar2(2));

4.3 修改字段长度

alter table a modify(ano number(10));

4.4 修改字段类型

alter table a modify(sex char(3));

4.5 修改字段名字

alter table a rename column ano to aid;

4.6 删除字段

alter table a drop column sex;

4.7 修改表名

rename a to c;
http://www.lryc.cn/news/604894.html

相关文章：

  • RHEL 9.5 离线安装 Ansible 完整教程
  • 力扣热题100-------74.搜索二维矩阵
  • ES 文件浏览器：多功能文件管理与传输利器
  • 深度学习中的注意力机制：原理、应用与未来展望
  • 1+1＞2！特征融合如何让目标检测更懂 “场景”？
  • SD-WAN助力船舶制造业数字化转型：打造智能化网络支撑体系
  • gtest框架的安装与使用
  • C#程序员计算器
  • 单片机学习笔记.AD/DA（略含有SPI，用的是普中开发板上的XPT2046芯片）
  • Rust × Elasticsearch官方 `elasticsearch` crate 上手指南
  • 《安富莱嵌入式周报》第356期：H7-TOOL的250M示波器模组批量生产中，自主开发QDD执行器，开源14bit任意波形发生器（2025-07-28）
  • ConcurrentHashMapRedis实现二级缓存
  • (LeetCode 面试经典 150 题) 141. 环形链表（快慢指针）
  • 如何将JPG、PNG、GIF图像转换成PDF、SVG、EPS矢量图像
  • 简单线性回归模型原理推导(最小二乘法)和案例解析
  • react+ant design怎么样式穿透-tooltip怎么去掉箭头
  • 工作笔记-----存储器类型相关知识
  • Solon v3.4.2（Java 应用开发生态基座）
  • Java 控制台用户登录系统（支持角色权限与自定义异常处理）
  • python之asyncio协程和异步编程
  • 【MySQL学习|黑马笔记|Day3】多表查询（多表关系、内连接、外连接、自连接、联合查询、子查询），事务（简介、操作、四大体系、并发事务问题、事务隔离级别）
  • 自动化与配置管理工具 ——Ansible
  • 创建型设计模式-Builder
  • Newman+Jenkins实施接口自动化测试
  • 浏览器pdf、image显示
  • 防火墙与入侵检测
  • Linux下C/C++服务端崩溃排查手册
  • VMware16安装Ubuntu-22.04.X版本（并使用桥接模式实现局域网下使用ssh远程操作Ubuntu系统）
  • Linux 进程管理与计划任务设置
  • 16-C语言：第17天笔记