表设计
前提: 设计表首先应该按需遵循三范式
1.确定表名
2.确定字段名 类型 +约束(主键 外键 非空 默 检查认 唯一)
主键: 唯一标识一条记录(唯一并且非空)
唯一: 唯一
非空:不能为空
默认: 当没给值时使用给定一个默认值
外键:参考其他表(自己)的某个(某些)字段
检查:自定义的规则
创建表(不加约束)
表名 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;
创建表(同时创建约束+默认名称)
这种在创建表的同时创建约束并使用默认约束名称的方
式,后期不方便排错,所以不推荐使用。其主要的优点
是简单
--删除 (先删除从表 再删除主表 ;同时删除约束)
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列';
创建表(同时创建约束+指定名称)
创建表的同时创建约束并指定约束的名称,后期方便排错,推荐使用
--删除 (先删除从表 再删除主表 ;同时删除约束)
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列';
创建表(追加创建约束+指定名称)
推荐, 便于后期排错
--删除 (先删除从表 再删除主表 ;同时删除约束)
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) ,
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
主键 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)
);
--追加约束
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列';
约束
在 oracle中所有的一切都是对象, 约束也是一个个的对
象,除了能创建约束我们还能对约束进行一些其他的操
作
查看某个用户的约束
select constraint_name, constraint_type
from user_constraints
where owner = upper('SCOTT');
查看表的约束
select constraint_name, constraint_type
from user_constraints
where table_name = upper('emp');
查看 字段名+约束
select constraint_name, column_name
from user_cons_columns
where table_name = upper('emp');
约束的禁用与启用
ALTER TABLE tb_user disable constraint
nn_user_name;
ALTER TABLE tb_user enable constraint
nn_user_name;
删除约束
alter table tb_user drop constraint
uq_user_email cascade;
修改约束
--非空
alter table tb_user modify (username
varchar2(20));
--默认
alter table tb_user modify (age default null);
DDL
SQL语言结构:
DDL(Data Definition Language 数据定义语言)用于操
作对象和对象的属性,这种对象包括数据库本身,以及
数据库对象,像:表、视图等等,DDL 对这些对象和属
性的管理和定义具体表现在 create、drop 和 alter 上。特
别注意:DDL 操作的“对象”的概念,”对象“包括对象及
对象的属性,而且对象最小也比记录大个层次。以表举
例:create 创建数据表,alter 可以更改该表的字段,
drop 可以删除这个表,从这里我们可以看到,DDL 所站
的高度,他不会对具体的数据进行操作。
DDL 的主要语句(操作)
语句 作用
create 可以创建数据库和数据库的一些对象
drop 可以删除数据表、索引、条件约束等
alter 修改数据表定义及属性
删除表
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;
修改表结构
- 修改表名 :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;
DML
DML(Data Manipulation Language 数据操控语言)用于
操作数据库对象中包含的数据,也就是说操作的单位是
记录。
DML 的主要语句(操作
语句 作用
Insert 向数据表张插入一条记录
Delete
删除数据表中的一条或多条记录,也可以删
除数据表中的所有记录,但是,它的操作对象仍是记录
Update 用于修改已存在表中的记录的内容
使用场景:
insert 注册
update 修改密码
delete 退出、删除、剔除会员
e tb_txt_new add col varchar2(30);
–删除列
alter table tb_txt_new drop column col;
select * from tb_txt_new;
## DML
**DML(Data Manipulation Language 数据操控语言)用于**
**操作数据库对象中包含的数据,也就是说操作的单位是**
**记录。**
### DML 的主要语句(操作
**语句 作用**
**Insert 向数据表张插入一条记录**
**Delete**
**删除数据表中的一条或多条记录,也可以删**
**除数据表中的所有记录,但是,它的操作对象仍是记录**
**Update 用于修改已存在表中的记录的内容**
### 使用场景:
**insert 注册**
**update 修改密码**
**delete 退出、删除、剔除会员**
**select 登录|查看会员**