Oracle表结构&数据类型&约束
1、Oracle 表结构
1.1、创建表名的规范
- 推荐以"t_xxx"
- 不能含有"+- "等非法字符，eg：
sql create table "t-1" (id int); - 不能以"_"开头，eg：
sql create table "_t" (id int); - 表名如果不用""，那么全部大写，如果""，严格区分大小写，T_USER和t_user是两个不同的表对象
1.2、查看当前用户所拥有的表
当前用户*
select * from user_tables;
DBA角色
select * from dba_users;
这种以dba_xxx或者user_xxx的数据对象，我们一般称作数据字典！
1.3重命名表
alter table t_user rename to t_student;
rename t_student to t_user;
1.4、重命名列
注意：user是关键字，不要作为表名、列名，如果一定要这么定义，使用""
alter table t_user rename column name to "user";
1.5、添加列
alter table t_user add gender char(1);
alter table t_user add (mail varchar(50), addr varchar(50));
1.6、修改列类型
alter table t_user modify "user" varchar(5);
alter table t_user modify ("user" varchar(15), mail varchar(60));
1.7、删除列
alter table t_user drop column addr;
alter table t_user drop (id, mail);
1.8、表名注释
comment on table t_user is 'user info';
select * from user_tab_comments;
1.9、列名注释
comment on column t_user."user" is 'user real name';
select * from user_col_comments where table_name = 'emp';
1.10、授予其他用户可以修改我的表的表结构权限
grant alter on t_user to pp0419;
2、Oracle 数据类型
2.1、字符
char(2000)
2000个字节（2000个英文，666个汉字——utf8，1000个汉字——gbk）
nchar(1000)
1000个字符（1000个英文，1000个汉字——utf8，1000个汉字——gbk）
varchar2(4000)
4000个字节 （4000个英文，1333个汉字——utf8，2000个汉字——gbk）
nvarchar2(2000)
2000个字符（2000个英文，2000个汉字——utf8，2000个汉字——gbk）
char和nchar是固定长度存储（不足会自动补齐你指定的长度）
varchar和nvarchar是可变长度存储（按实际字符串长度存储）
n表示按字符存储（不加n按字节存储）
drop table t_user;
-- create table t_user (name char(10)); --
-- create table t_user (name nchar(10)); --
-- create table t_user (name varchar2(10)); --
create table t_user (name nvarchar2(10)); --
insert into t_user values ('Tom');
insert into t_user values ('我们');
select * from t_user;
select length(name) from t_user; -- 字符长度
2.2、数值
int/long 存整数
number/float 存小数
number(m, n) 存指定精度的小数
number(5, 2) [-999.99, 999.99]
insert into t_user values (-999.99);
insert into t_user values (999.99);
insert into t_user values (-999.945); -- -999.95
insert into t_user values (-999.995); -- 超出精度
number(5, -2)
create table t_user (salary number(5, -2));
insert into t_user values (12345.99); -- 12300
insert into t_user values (12355.99); -- 12400
注意：没有double！
2.3、时间日期
date 年月日时分秒
timestamp 年月日时分秒毫秒
create table t_user (birth date);
insert into t_user values (sysdate);
create table t_user (birth timestamp);
insert into t_user values (systimestamp);
2.4、二进制
blob binary large object
可以含有图片、影像、文本数据
clob character large object
只能含有文本数据
没有长度限制
3、Oracle 约束
3.1、主键 primary key
唯一约束 + 非空约束
一个表上只能有一个主键
drop table t_user;
create table t_user (
-- id int primary key,
id int constraint pk_id primary key,
name varchar(20)
);
create table t_user (
id int,
name varchar(20),
-- primary key(id)
constraint pk_id primary key(id)
);
alter table t_user add primary key (id);
alter table t_user drop primary key;
alter table t_user drop constraint pk_id;
3.2外键 foreign key
只能引用别人表的主键列或者是有唯一约束的列
drop table t_user cascade constraints; -- 删除主表的同时删除引用本表主键的外键约束（但是外键数据还在）
drop table t_contact;
-- 用户
create table t_user (
id int primary key,
name varchar(20)
);
-- 监护人
create table t_contact (
id int primary key,
name varchar(20),
-- user_id int references t_user(id)
user_id constraint fk_user_id references t_user(id)
);
create table t_contact (
id int primary key,
name varchar(20),
user_id int,
constraint fk_user_id foreign key(user_id) references t_user(id)
);
alter table t_contacts add constraint fk_user_id foreign key (userid) references t_user (id);
alter table t_contact drop constraint fk_user_id;
技巧：删除主键时级联删除外键数据或修改外键值为null
在MySQL也有类似的级联删除外键的操作！！！
drop table t_user cascade constraints; -- 删除主表的同时删除引用本主键的外键约束（但是外键数据还在）
drop table t_contact;
-- 用户
create table t_user (
id int primary key,
name varchar(20)
);
-- 监护人
create table t_contact (
id int primary key,
name varchar(20),
user_id int references t_user(id)
-- on delete cascade -- 级联删除外键数据行
on delete set null -- 外键数据行保留，把外键值改为null
);
insert into t_user values (111, 'tom');
insert into t_user values (112, 'sam');
insert into t_user values (113, 'john');
insert into t_contact values (1, 'ben', 111);
insert into t_contact values (2, 'mike', 111);
insert into t_contact values (3, 'sophia', 112);
delete from t_user where id = 111;
select * from t_contact;
3.3、组合主键/组合外键（了解）
create table t_user (
id int,
name varchar2(10),
gender char(1),
primary key(id, name) -- id和name值的组合不能重复，且两个列都不能为null
);
create table t_contact (
name varchar2(10),
cid int,
cname varchar2(10),
constraint cfk_cid_cname foreign key (cid, cname) references t_user(id, name)
);
3.4、唯一 unique
一个表中可以有多个列定义唯一约束
唯一约束的值可以为null
唯一可以被外键引用（因为有时主键已被其他列定义）
create table t_user (
id int primary key,
-- name varchar2(10) unique
/*
name varchar2(10),
unique(name)
*/
-- constraint uq_name unique(name)
);
alter table t_user add constraint uq_name unique(name);
3.5、组合唯一
create table t_user (
id int,
name varchar2(10),
gender char(1),
unique(id, name) -- id和name值的组合不能重复，但是某个列可以为null
);
3.6、非空 not null
不需要使用constraint关键字去创建（直接通过表结构就可以定义了）
-- 直接通过表结构定义或修改
create table t_user (
id int primary key,
name varchar(20) not null
);
alter table t_user modify name varchar(10) null;
-- 通过constraint约束形式
create table t_user (
id int primary key,
name varchar(20) constraint not_null_name not null
);
alter table t_user drop constraint not_null_name;
desc t_user;
3.7、检查 check
check(SQL表达式)
可以对值的大小、范围、引用都可以进行限定
create table t_user (
id int,
name varchar(10),
mobile varchar(11),
-- check(length(mobile) = 11)
constraint ck_mobile check(length(mobile) = 11)
);
create table t_user (
id int,
name varchar(10),
-- gender char(1) check(gender in ('M', 'F'))
-- age int check(age between 18 and 30)
-- age int check(age >= 18 and age <= 30)
-- mobile char(11) check(mobile like '1%') -- 注意定长不适用于length检查
mobile varchar(11) check(length(mobile) = 11)
);
检查引用关系的写法
create table t_user (
id int,
name varchar(10),
hiredate date, -- 入职时间
firedate date, -- 离职时间
-- firedate date check(firedate >= hiredate) -- ERROR
constraint ck_firedate check(firedate >= hiredate) -- 雇员的离职时间(不能定义为行内约束写法，必须新行定义)
);
注意：针对于一个列可以同时存在多个检查约束(注意约束之间可能有冲突)
create table t_user (
age int check (age between 18 and 40)
);
alter table t_user add constraint ck_age check (age between 16 and 30);
select * from user_constraints where table_name = 'T_USER';
insert into t_user values (31); -- ERROR
默认 default
create table t_user (
id int,
name varchar(10) default 'unknown'
);
insert into t_user values (1, 'tom');
insert into t_user values (2, '');
insert into t_user values (3, null);
insert into t_user (id) values (4);
insert into t_user values (5, default);
select * from t_user;
alter table t_user modify name varchar(10) default null; -- 取消默认约束
insert into t_user (id) values (6);
3.8、数据字典（约束相关）
select * from user_constraints;
CONSTRAINT_TYPE 列存储的就是约束类型:
C - 检查
P - 主键
R - 外键
U - 唯一
CONSTRAINT_NAME 列存储的就是约束名!
select * from user_constraints where table_name = 'T_USER';
alter table t_user drop constraint SYS_C00111;
采用约束实现三大关联关系
1. 1:1
-- 身份证和护照（有身份证不一定有护照，但是有护照一定有身份证）
create table t_idcard (
id char(18) primary key,
name varchar(20) not null,
gender char(1) not null
);
create table t_passport (
id char(15) primary key,
name varchar(50) not null,
idcard char(18),
constraint fk_idcard foreign key (idcard) references t_idcard(id), -- 外键定义在护照这张表
constraint uq_idcard unique(idcard) -- 唯一关系实现1对1
);
2. 1:M
只要有外键就是多的引用关系
3. M:N
-- 学生选课
create table t_student (
sid int primary key,
sname varchar2(20) not null
);
create table t_course (
cid int primary key,
cname varchar2(10) not null
);
create table r_student_course (
rsid int,
rcid int,
constraint fk_rsid foreign key (rsid) references t_student(sid),
constraint fk_rcid foreign key (rcid) references t_course(cid),
unique(rsid, rcid) -- 不能重复选课
);
posted on 2023-08-08 17:20 JavaCoderPan 阅读(64) 评论(0) 编辑 收藏 举报
