ORACLE的表维护

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;