Oracle中表的创建及表中的增删改查

69 阅读 0 评论 46 点赞

我是靠谱客的博主可靠白开水，最近开发中收集的这篇文章主要介绍Oracle中表的创建及表中的增删改查，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、表的定义：

语法格式：
Create table tableName(
       colName colType[Default defaultExp constraint constraintDef]
       [,colName colType.....]
)
说明:表名长度为1到30个字符，包括字母、符号和数据；第一个字符必须是字母；
     首字母之后的字符可以是字母、数字或#，$符号及_;如果名称中含有空格，则必须使用双引号

--default 用于指定该列的默认值
--constraint 后跟对于该列的约束

约束的种类:
(1)主键约束：任何一条记录在主键列上的值都不能为空，且主键列的值必须唯一
(2)外键约束：列值要么为空，要么等于它引用的主列表的某个值。
             外键列只能引用另一个表中的具有主键约束或唯一约束的列
(3)唯一约束：列值要么为空，要么是一个唯一的值
(4)检查约束：列值要么为空，要么符合一个表达式的限制
(5)非空约束：表中任一行在该列的值都不能为空

●约束的定义
列级约束：紧跟每列的定义，列级约束是列定义的一部分
标记约束：所有列都定义完毕，后续独立定义约束的语句，表级约束可针对某一列或多列

●列级约束的完整语法：
constraint constraintName[选择一下某种约束]
    主键：primary key
    外键：references otherTableName(columnName)
    唯一：unique
    检查：check(exp)
    非空：not null
●约束名命名规范：
    pk_table_column
    fk_table_column
    uq_table_column
    ck_table_column
    nn_table_column

可以不指定约束名，那么数据库会自动生成一个约束名。
一个列可以有多个约束
--创建表(使用列级约束)

--学生表
create table stu_info(
       id int constraint pk_stu_id primary key,
       stu_name varchar2(100) constraint nn_stu_name not null, --默认为100个字节，能容纳33个汉字
       age smallint constraint ck_stu_age check(age >= 0 and age <= 150)
);

--课程表
create table cou_info(
       id int constraint pk_con_id primary key,
       --课程名不能为空，也不能重复
cou_name varchar2(200) constraint nn_cou_name not null constraint uq_cou_name unique,
       credit number(3,1) constraint ck_cou_credit check(credit >= 0)
);

--学分表
create table score_info(
       --定义外键
        stu_id int constraint fk_score_stu_id references stu_info(id) constraint nn_score_stu_id not null,
        cou_id int constraint fk_score_cou_id references cou_info(id) constraint nn_score_cou_id not null,
        score number(4,1)
);

●表级约束的语法：
      主键约束：constraint pk_tbName_colName primary key(colName)
外键约束：constraint fk_tbName_colName foreign key(colName) references tbName(colName)
      检查约束：constraint ck_tbName_colName check(exp)
      唯一约束：constraint uq_tbName_colName unique(colName)

--注意：非空约束只能定义在列级；联合主键只能使用表级约束；

--使用表级约束
--学生表
create table stu_info(
       id int,
       stu_name varchar2(100) default '无名' constraint nn_stu_name not null, --默认为100个字节，能容纳33个汉字
       age smallint default 0,
       constraint pk_stu_id primary key(id),         --需要指定对应的列为主键
       constraint ck_stu_age check(age >= 0 and age <= 150)
);

--课程表
create table cou_info(
       id int,
       --课程名不能为空，也不能重复
       cou_name varchar2(200) constraint nn_cou_name not null ,
       credit number(3,1),
       constraint pk_con_id primary key(id),    --需要指定约束对应的列
       constraint uq_cou_name unique(cou_name),
       constraint ck_cou_credit check(credit >= 0)
);

--学分表
create table score_info(
       --定义外键
        stu_id int,
        cou_id int,
        score number(4,1),
        constraint fk_score_stu_id foreign key(stu_id) references stu_info(id),
        constraint fk_score_cou_id foreign key(cou_id) references cou_info(id),
        constraint pk_score primary key(stu_id,cou_id) --联合声明为主键列，默认不能为空和重复取值
);

●列默认值：default exp
      default短语和constraint短语的前后顺序不能颠倒

二、修改表的结构
--add添加列名
alter table stu_info add dob date

--modify修改列名
alter table tableNAme modify columnName newType

--删除列
alter table tableNAme drop column columnName

三、插入、更新和删除记录
●插入记录
  格式1：insert into tableName(columnNameList)
         values(valueList)
(1)列名列表可以只列出表中的部分列；值列表的数量和数据类型必须和列名列表匹配;列名列表如果只列出表中的部分列，其余列会被设置为空值或默认值
(2)可以省略列名列表部分，此时该列表需要提供所有列的值；
(3)可以使用NULL关键字显式的提供一个空值；也可以使用default关键字显式指明该列使用的默认值
(4)字符串字面值中如果有单引号，则使用两个连续的单引号
例：
insert into stu_info(id,stu_name) values(10,'zhangsan');
select * from stu_info;

  格式2：insert into tableName(columnNameList)
         select ... from ... where ...
--注意：
  (1)插入新纪录时，应确保主键列的值唯一性，如果试图插入一个与主键列值重复的行，将会出现错误：'违反唯一约束条件'
  (2)如果当前表中有外键约束的列，那么插入数据时，所给定的列值要么为空值，要么该值必须存在于父表中。否则会出现错误：'违反完整约束条件'
insert into score_info values(1,1,96); --插入错误,违反了cou_info表(父表外键约束)中没有该值

●更新记录
   格式：update tableName set colName = value,...
         where ...
--注意:
(1)如果更新主键列的值，但该值已被指标中的某条记录引用，

那么会出现错误：'违反完整约束条件-已找到子记录'
(2)如果当前表中有外键约束的列，那么更新该列的数据时，所给定的列值要么为空，要么该值必须存在于父表中。
   否则会出现错误：'违反完整约束条件---未找到父项关键字'

insert into cou_info values(1,'高等数学',9);
insert into score_info values(1,1,96);
--子表中 score_info引用了父表 cou_info的记录，不能更新此操作
update cou_info set id = 10 where id = 1;
●删除记录
   格式：delete from tableName where ...
   作用：根据条件从表中删除记录
--注意：如果要删除的记录已被子表中某条记录引用，

那么会出现错误：'违反完整约束条件'

delete from cou_info where id = 1; --在子表score_info引用了父表(cou_info)，所以不能删除

●级联删除
    在创建外键约束列时指定 on delete cascade
    如果对外键约束的列指定 on delete set null,那么当父记录被删除时，只记录对应的列自动被置为 null
create table score_info(
       --定义外键
        stu_id int,
        cou_id int,
        score number(4,1),
        constraint fk_score_stu_id foreign key(stu_id) references stu_info(id),
constraint fk_score_cou_id foreign key(cou_id) references cou_info(id) on delete cascade,
        constraint pk_score primary key(stu_id,cou_id)

--联合声明为主键列，默认不能为空和重复取值
);
insert into score_info values(1,1,96);

delete from cou_info where id = 1;--父表的记录被子表所引用，此时由于级联删除，可以删除父表中的该条记录