Oracle笔记之表设计

一、设计表

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

复习一下3NF：

• 1NF–>列不可再分最小原子 （避免重复);
• 2NF–>主键依赖(确定唯一)；
• 3NF–>消除传递依赖(建立主外键关联 拆分表);

1.1. 确定表名

表名 tb_user
表名 tb_txt

1.2. 确定字段名 类型 +约束(主键 外键 非空 默 检查认 唯

一)
主键： 唯一标识一条记录（唯一并且非空）
唯一： 唯一
非空：不能为空
默认： 当没给值时使用给定一个默认值
外键：参考其他表（自己）的某个（某些）字段
检查：自定义的规则

实例：

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	-

2. 文章表

表名 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 表名(
字段名 类型(长度) 约束,
...其他字段....
..约束........
);

2.1. 创建表（不加约束）

tb_user表：

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表：

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 列';

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

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

--删除 （先删除从表 再删除主表 ；同时删除约束）
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;

tb_user表：

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表：

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列';

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

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

--删除 （先删除从表 再删除主表 ；同时删除约束）
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;

tb_user表：

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表：

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) ondelete 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列';

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

推荐， 便于后期排错

--删除 （先删除从表 再删除主表 ；同时删除约束）
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;

tb_user表：

create table tb_user(
userid number(5),
username varchar2(30) ,
userpwd varchar2(20) ,
age number(3) ,
gender char(2) ,
email varchar2(30),
regtime date default(sysdate)
);
--追加约束
alter table tb_user add constraint pk_user_id primary key (userid);
alter table tb_user add constraint ck_user_name check(length(username)between 4 and 20) ;
alter table tb_user add constraint ck_user_pwd check(length(userpwd) between 4 and 18);
alter table tb_user add constraint ck_user_age check(age>=18);
alter table tb_user add constraint ck_user_gender check(gender in('男','女'));
alter table tb_user add constraint uq_user_email unique(email);
--非空与默认
alter table tb_user modify (username constraint nn_user_name not null);
alter table tb_user modify (userpwd constraint nn_user_pwd not null);
alter table tb_user modify (age default(18));
alter table tb_user modify (gender default('男'));
--加入注释
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表：

create table tb_txt(
txtid number(10),
title varchar2(32),
txt varchar2(1024),
pubtime date,
userid number(5)
);
--追加约束
alter table tb_txt add constraint pk_txt_id primary key(txtid);
alter table tb_txt add constraint ck_txt_id
check(length(title)>=4 and length(title)<=30);
--三种级联删除规则
alter table tb_txt 
add constraint fk_txt_ref_user_id foreign key(userid)
 references tb_user(userid);

alter table tb_txt add constraint fk_txt_ref_user_id
 foreign key(userid) references tb_user(userid) on delete cascade ;

alter table tb_txt 
add constraint fk_txt_ref_user_id 
foreign key(userid) 
references tb_user(userid) on delete set null;
--注意非空 默认
alter table tb_txt modify (title constraint
nn_txt_title not null) ;
alter table tb_txt modify (pubtime default(sysdate));
--注释
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列';

2.5. 已有表中拷贝结构

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;

三、约束（了解）

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

3.1. 查看某个用户的约束

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

3.2. 查看表的约束

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

3.3. 查看 字段名+约束

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

3.4. 约束的禁用与启用

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

3.5. 删除约束

alter table tb_user drop constraint uq_user_email cascade;

3.6. 修改约束

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