Oracle数据库-表

表

一、设计表

• 满足三范式
• 确定表名
• 确定字段
• 添加约束

二、创建表并添加约束

1、创建不带约束的表

语法：

create table 表名([字段1名称 字段类型,字段2名称 字段类型....])

create table sstudent(  -- 表名为sstudent
       sid number(5),    -- 字段名是sid,类型为number，长度为5字节
       sname varchar2(15),-- 字段名是sname,类型为varchar2，长度为15字节
       sage number(3),  -- 字段名是sage,类型为number，长度为3字节
       sgender number(1), -- 字段名是sgender,类型为number，长度为1字节
       hirdate date       -- 字段名是hirdate,类型为date
)

2、给表添加注释

comment on table sstudent is '学生表';  -- 表添加注释
comment on column sstudent.sid is '学生ID'; -- 字段添加注释
comment on column sstudent.sname is '姓名'; -- 字段添加注释
comment on column sstudent.sage is '年龄'; -- 字段添加注释
comment on column sstudent.sgender is '性别'; -- 字段添加注释
comment on column sstudent.sdate is '学生入学日期'; -- 字段添加注释
comment on column sstudent.cid is '班级'

3、创建表时添加约束

create table sstudent(
       sid number(5) primary key, -- 创建表时添加主键约束
       sname varchar2(15) not null unique,        -- 创建表时添加非空约束和唯一约束
       sage number(3) check(sage between 0 and 150),     -- 创建表时添加检查约束
       sgender number(1) check(sgender in (1,0)),    -- 创建表时添加检查约束 性别 1男,0女
       sdate date default(sysdate),    -- 创建表时添加默认值 默认给当前时间
       cid number(3)                   -- 没有约束
)

4、创建表时添加约束并指定约束名

create table sstudent(
       sid number(5) constraints pk_sstudent_sid primary key, --指定约束名为pk_sstudent_sid
       sname varchar2(15) constraints sstudent_sname not null constraints sstudent_sname_unique unique,        -- 指定约束名为sstudent_sname
       sage number(3) constraints sstudent_sage check(sage between 0 and 150),     -- 指定约束名为sstudent_sage
       sgender number(1) constraints sstudent_sgender check(sgender in (1,0)),    -- 指定约束名为sstudent_sgender
       sdate date default(sysdate),
       cid number(3)
)

5、创建表结束时添加约束

create table sstudent(
       sid number(5),
       sname varchar2(15),
       sage number(3),
       sgender number(1),
       sdate date,
       cid number(3),
       primary key (sid), -- 表创建结束时添加主键约束
       unique(sname), -- 表创建结束时添加唯一约束
       check(sage between 0 and 150), -- 表创建结束时添加检查约束
       check(sgender in (1,0)), -- 表创建结束时添加检查约束
       check(sname is not null), -- 创建表结束时添加检查约束
)

6、创建表结束时添加约束并指定约束名

create table sstudent(
       sid number(5),
       sname varchar2(15) not null,
       sage number(3),
       sgender number(1),
       sdate date default(sysdate),
       cid number(3),
       constraints pk_sstudent_sid primary key (sid), -- 表创建结束时添加主键约束
       constraints sstudent_sname_unique  unique(sname), -- 表创建结束时添加唯一约束
       constraints sstudent_sage check(sage between 0 and 150), -- 表创建结束时添加检查约束
       constraints sstudent_sgender check(sgender in (1,0)), -- 表创建结束时添加检查约束   
       constraints sstudent_sname_notnull check(sgender in (1,0)) -- 表创建结束时添加检查约束
)

7、创建表结束后追加约束

create table sstudent(
       sid number(5),
       sname varchar2(15) not null,
       sage number(3),
       sgender number(1),
       sdate date,
       cid number(3)
)
-- 追加约束
alter table sstudent add primary key (sid);
alter table sstudent add unique(sname);
alter table sstudent add check(sage between 0 and 150);
alter table sstudent add check(sgender in (1,0));
alter table sstudent add check(sgender in (1,0));

8、创建表结束后追加约束并指定约束名

create table sstudent(
       sid number(5),
       sname varchar2(15) not null,
       sage number(3),
       sgender number(1),
       sdate date,
       cid number(3)
)
-- 追加约束，指定约束名
alter table sstudent add constraints pk_sstudent_sid primary key (sid);
alter table sstudent add constraints sstudent_sname_unique unique(sname);
alter table sstudent add constraints sstudent_sage check(sage between 0 and 150);
alter table sstudent add constraints sstudent_sgender check(sgender in (1,0));
alter table sstudent add constraints sstudent_sname_notnull check(sgender in (1,0));

9、删除约束

alter table sstudent drop constraints sstudent_sname_notnull

三、创建表并添加外键约束

1、创建表时添加外键约束

create table sstudent(
       sid number(5) primary key, -- 创建表时添加主键约束
       sname varchar2(15) not null unique,        -- 创建表时添加非空约束和唯一约束
       sage number(3) check(sage between 0 and 150),     -- 创建表时添加检查约束
       sgender number(1) check(sgender in (1,0)),    -- 创建表时添加检查约束 性别 1男,0女
       sdate date default(sysdate),    -- 创建表时添加默认值 默认给当前时间
       cid number(3) references cclass(cid) --创建表时添加外加约束
)

2、创建表结束时添加外键约束

create table sstudent(
       sid number(5) primary key, -- 创建表时添加主键约束
       sname varchar2(15) not null unique,        -- 创建表时添加非空约束和唯一约束
       sage number(3) check(sage between 0 and 150),     -- 创建表时添加检查约束
       sgender number(1) check(sgender in (1,0)),    -- 创建表时添加检查约束 性别 1男,0女
       sdate date default(sysdate),    -- 创建表时添加默认值 默认给当前时间
       cid number(3),
       foreign key(cid) references cclass(cid) -- 创建表结束时添加外键约束     
)

3、创建表结束后追加外键约束

create table sstudent(
       sid number(5),
       sname varchar2(15) not null,
       sage number(3),
       sgender number(1),
       sdate date,
       cid number(3)
)
alter table sstudent add foreign key(cid) references cclass(cid);

三、表的其他操作

1、删除表

两个表如果出现了主外键关系，那么主表在删除表时就不能能在以普通方式进行删除。

-- 常规删除
drop table cclass
-- 级联删除
drop table cclass cascade constraints -- 会一并删除从表里添加的外键约束

使用级联删除，会一并删除从表的外键约束

2、删除数据

delete from sstudent

如果两张表存在主外关系，主表里的记录不能随意删除。从表可以随意删除。

如果从主表删除数据，没有引用则可以直接删除，入股被引用有三种处理方式。

• 先删除从表里引用了主表数据的记录，然后删除主表里的数据
• 添加外键约束，on delete set null,删除主表的数据时，从表引用的字段设置为null
• 添加外键约束，on delete cascade,删除住表的数据时，级联删除从表引用主表数据的记录

-- 一、先删除从表里的记录，然后删除主表里的记录
delete from sstudent where cid = 1	
delete from cclass where cid = 1
-- 二、on delete set null
create table sstudent(
       sid number(5) primary key, -- 创建表时添加主键约束
       sname varchar2(15) not null unique,        -- 创建表时添加非空约束和唯一约束
       sage number(3) check(sage between 0 and 150),     -- 创建表时添加检查约束
       sgender number(1) check(sgender in (1,0)),    -- 创建表时添加检查约束 性别 1男,0女
       sdate date default(sysdate),    -- 创建表时添加默认值 默认给当前时间
       cid number(3) references cclass(cid) on delete set null --创建表时添加外加约束
)
delete from cclass where cid = 1
-- 三、on delete cascade
create table sstudent(
       sid number(5) primary key, -- 创建表时添加主键约束
       sname varchar2(15) not null unique,        -- 创建表时添加非空约束和唯一约束
       sage number(3) check(sage between 0 and 150),     -- 创建表时添加检查约束
       sgender number(1) check(sgender in (1,0)),    -- 创建表时添加检查约束 性别 1男,0女
       sdate date default(sysdate),    -- 创建表时添加默认值 默认给当前时间
       cid number(3) references cclass(cid) on delete cascade --创建表时添加外加约束
)
delete from cclass where cid = 1

3、truncate

truncate也算是一种删除表的数据，不过和delete有点不同

• delete
  • 可以删除全部或部分数据
  • 自动开启实务
  • 检查主表是否有被从表引用的数据
• truncate
  • 删除全部数据
  • 不会开启事物
  • 直接检查表结构上是否有被关联

truncate table sstudent

4、序列

在MySQL中可以设置自增，但是Oracle不可以，但是可以通过序列生成一个数字，来达到自增的效果。

序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。

1、创建

语法：

create sequence 序列名字 start with 起始值 increment by 步进值

create sequence sq_ahh_a start with 1 increment by 1;

2、使用

创建序列后第一次使用需要调用nextval获取下一个值。

序列名.naxtval获取下一个值，第一次使用需要先执行

select sq_ahh_a.nextval from dual

序列名.currval获取当前值

select sq_ahh_a.currval from dual

可以在增加数据时用序列，来达到自增的效果

insert into sstudent values(sq_ahh_a.nextval,'abcd',20,1,sysdate,01)