SQL语句二维表管理

Oracle的二维表管理

Oracle数据库表的字段类型说明

Oracle常用的数据类型主要有

数据类型 参数 描述
char(n) n=1 to 2000字节 定长字符串，n字节长，如果不指定长度，缺省的话为1个字节长(一个汉字是连个字节)
varchar2(n) n=1 to 4000字节 可变长的字符串，具体定义时指明最大长度n
number(m,n) m=1 to 38 n=-84 to 127 可变长的数值列，允许0、正值及负值，m是所有有效数字的位数，n是小数点之后的位数，如果实际位数超出了位数限制就会被截取多余的位数
date 无 从公元前4712年1月1日到公元4712年12月31日的所有合法日期
long 无 可变长字符列，最大长度限制是2GB，用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。
raw(n) n=1 to 2000 可变长二进制数据，在具体定义字段的时候必须指明最大长度n，Oracle 8i用这种格式来保存较小的图形文件或带格式的文本文件，如Miceosoft Word文档。
blob clob nclob 无 三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件，最大长度是4GB。LOB有几种类型，取决于你使用的字节的类型，Oracle 8i实实在在地将这些数据存储在数据库内部保存。可以执行读取、存储、写入等特殊操作。
bfile 无 在数据库外部保存的大型二进制对象文件，最大长度是4GB。这种外部的LOB类型，通过数据库记录变化情况，但是数据的具体保存是在数据库外部进行的。Oracle 8i可以读取、查询BFILE，但是不能写入。大小由操作系统决定
char和varchar2的比较
varchar2类型(底层需要做一次判断)：varchar2(ln) ln表示字符最大长度实际存储内存长度是根据字符大小来分配，但是最大不能超过ln
char类型(底层不需要判断)：char(ln) 不管字符数据大小是多大，直接开辟ln大小的空间存储数据
总结：varchar2的特点是：动态分配内存空间，节省空间，char的特点是：存储效率高于 varchar2，所以小型数据可以选择 char，比较大型的数据建议使用 varchar2

Oracle二维表的创建

创建二维表，下面给了一个最简单的无任何约束的创建数据库表的示例

create table student(
sno number(10,2), -- sno 数值类型只有 number 最多10位 小数占2位
sname varchar2(100),
sage number(3),
ssex char(4), -- char varchar2 都代表是string的类型
sfav varchar2(500),
sbirth date
)
1
2
3
4
5
6
7
8
在了解了简单数据库表的创建之后，接下来学习数据库表的多种约束，在下面列出来的种种约束的创建都是可以通过SQL以及PL/SQL两种方式来完成的，首先介绍SQL命令方式，之后再补充简单的PL/SQL的操作

主键约束

主键：在数据库表中唯一非空的字段便可以定义为主键，在一张表中可以定义为主键的字段是唯一的，接下来介绍三种添加主键以及一种删除主键的方式

直接在创建表的时候添加主键约束，具体如下

create table student(
sno number(10,2) primary key, -- 注意是添加在逗号前面
sname varchar2(100),
sage number(3),
ssex char(4),
sfav varchar2(500),
sqq varchar2(30) -- 最后一行没有逗号
)
1
2
3
4
5
6
7
8
在声明数据库表格结构的最后一行声明主键约束，具体如下

语法： constraints 约束名称 primary key(主键名称)
注意：在这里的约束名称当执行SQL有问题时会被提示出来，一般约束名称的命名规则是 约束类型的简写(pk,ck,un)_数据表名_字段名
create table student(
sno number(10,2) ,
sname varchar2(100),
sage number(3),
ssex char(4),
sfav varchar2(500),
sqq varchar2(30), --注意此时应该加上逗号
constraints pk_student_sno primary key(sno)
)
1
2
3
4
5
6
7
8
9
10
11
在创建表之后使用 alter table语句给对应表设置约束，具体如下

alter table student add constraints pk_student_sno primary key(sno);
1
删除主键约束，这里同样使用 具体如下

alter table student drop constraints pk_student_sno;
1
非空约束

非空约束是指对于数据库表中的特定字段，应该不能输入空值，添加非空约束同样也是可以三种方式以及一种方式的删除约束

直接在创建表的时候添加非空约束

sname varchar2(100) not null, ----添加非空约束
1
在声明数据库表格结构的最后一行声明非空约束，具体如下

constraints ck_student_sname check(sname is not null)
1
在创建表之后使用 alter table语句给对应表设置约束，具体如下

alter table student add constraints ck_student_cname check(sname is not null)
1
删除主键约束，这里同样使用 具体如下，具体如下

alter table student drop constraints ck_student_cname;
1
检查约束

检查约束只要是指给特定字段添加限制，比如年龄必须是在特定范围内，性别只能是男女等

直接在创建表的时候添加非空约束，具体如下

sage number(3) check(sage>0 and sage<150),
ssex char(4) check(ssex='男' or ssex='女'),
1
2
在声明数据库表格结构的最后一行声明主键约束，具体如下

constraints ck_student_sage check(sage>0 and sage <200),
constraints ck_student_ssex check(ssex='男' or ssex='女'),
1
2
在创建表之后使用 alter table语句给对应表设置约束，具体如下

alter table student add constraints ck_student_sage checkcheck(sage>0 and sage<150);
alter table student add constraints ck_student_sex check(ssex='男' or ssex='女')
1
2
删除主键约束，这里同样使用 具体如下，具体如下

alter table student drop constraints ck_student_sage
alter table student drop constraints ck_student_sex;
1
2
唯一约束

唯一约束是指在这个字段中，每个值都应该是互不相同的

直接在创建表的时候添加非空约束，具体如下

sqq varchar2(30) unique,
1
在声明数据库表格结构的最后一行声明主键约束，具体如下

constraints un_student_qq unqiue(qq)
1
在创建表之后使用 alter table语句给对应表设置约束，具体如下

alter table student add constraints un_student_qq unique(qq)
1
删除主键约束，这里同样使用 具体如下，具体如下

alter table student drop constraints un_student_qq
1
外键约束

外键的概念：当一张表的某个字段的值需要依赖另外一张某个表的某个字段的值，则使用外键约束，其中主动依赖的表称为子表，被依赖的表称为父表，外键加在子表中。例如如下两张表格，学生表中需要课程表的课程号，且不能添加不存在的课程号信息到学生表中，这种情况便可以设置外键约束。

--创建学生表（子表）
create table student(
sno number(10) primary key,
sname varchar2(100) not null,
sage number(3) check(sage>0 and sage<150),
ssex char(4) check(ssex='男' or ssex='女'),
sfav varchar(500),
sqq varchar2(30) unique,
cno number(10)
)

--创建班级表格(父表)
create table class(
cno number(10) primary key,
cname varchar2(100) not null,
cdesc varchar2(300)
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
其中一般选取父表的主键作为子表的外键，且外键与主键名称不必相同

直接在创建表的时候添加非空约束，具体如下

cno number(10) references class(cno),
1
在声明数据库表格结构的最后一行声明主键约束，具体如下

constraints fk_student_cid foreign key(cid) references class(cno)
1
在创建表之后使用 alter table语句给对应表设置约束，具体如下

alter table student add constraints fk_student_cid foreign key(cno) references class(cno)
1
删除主键约束，这里同样使用 具体如下，具体如下

alter table student drop constraints fk_student_cid
1
外键约束优化

有外键约束的数据表在删除时会因为存在外键约束导致数据删除失败，这时需要优化数据表的外键设置

使用 on delete cascade关键字 级联删除，缺点是级联删除会将有关联的数据一起删除，比如完成如下设置以后，假如删除 class表中的1班级，student表中的班级号为1的也将全部删除

alter table student add constraints fk_student_cid foreign key(cno) references class(cno) on delete cascade;
1
使用 on delete set null关键字,删除父表数据时 将子表中的依赖字段的值设置为null,因此子表依赖的字段不能设置非空约束

alter table student add constraints fk_student_cid foreign key(cno) references class(cno) on delete set null;
1
总结

这一篇博客的主要内容是了解Oracle主要数据类型的含义以及添加约束的三种方法，重点了解约束类型：主键约束，非空约束，检查约束，唯一约束以及数据表中中外键的设置。
---------------------