oracle-单表构建脚本_oracle 单个表生成脚本-优快云博客

本文链接：https://blog.youkuaiyun.com/huangdezxy/article/details/37495571

本文介绍在Oracle Database 10g中进行表的操作，包括表的创建、删除、数据插入及约束验证等。通过具体示例展示了如何定义表结构、设置不同类型的约束条件，并演示了在插入数据时触发这些约束的效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 
Connected as test1


-- 删除表格
SQL> drop table tbl_student;

Table dropped

--创建表格
create table tbl_student(
   stu_no     char(3)      primary key,
   stu_name   varchar2(30) not null,
   stu_age    int          not null check(stu_age>12),
   stu_height number(5,2)
);

Table created


-- 查看表结构信息
SQL> desc tbl_student;
Name       Type         Nullable Default Comments 
---------- ------------ -------- ------- -------- 
STU_NO     CHAR(3)                                
STU_NAME   VARCHAR2(30)                           
STU_AGE    INTEGER                                
STU_HEIGHT NUMBER(5,2)  Y                         


-- 插入数据 (全字段，天然顺序插入）
SQL> insert into tbl_student values('001','mary',13,130.50);

1 row inserted

-- 年龄范围检查约束被启动
SQL> insert into tbl_student values('002','david',11,131.50);

insert into tbl_student values('002','david',11,131.50)

ORA-02290: check constraint (TEST1.SYS_C005268) violated


-- 主键约束被启动
SQL> insert into tbl_student values('001','david',14,131.50);

insert into tbl_student values('001','david',14,131.50)

ORA-00001: unique constraint (TEST1.SYS_C005269) violated

SQL> insert into tbl_student values('002','david',14,131.50);

1 row inserted


-- 非null约束被启动
SQL> insert into tbl_student values('003',null,13,133.50);

insert into tbl_student values('003',null,13,133.50)

ORA-01400: cannot insert NULL into ("TEST1"."TBL_STUDENT"."STU_NAME")


-- 可null字段可以不填写
SQL> insert into tbl_student values('003','tom',13,null);

1 row inserted

-- 字段类型也对输入数据的情况进行检测
SQL> insert into tbl_student values('004','jenny',13,'abcd');

insert into tbl_student values('004','jenny',13,'abcd')

ORA-01722: invalid number

-- 全字段非天然顺序录入
SQL> insert into tbl_student(stu_age,stu_height,stu_no,stu_name) values(14,135.5,'006','kent');

1 row inserted

-- 查看该表所有的行和列 (所有的字段和记录)
SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT
------ ------------------------------ --------------------------------------- ----------
001    mary                                                                13     130.50
002    david                                                               14     131.50
003    tom                                                                 13 
006    kent                                                                14     135.50


-- 部分字段非天然顺序录入(必须显式申明字段名）
SQL> insert into tbl_student(stu_age,stu_name,stu_no) values(15,'jenny','009');

1 row inserted

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT
------ ------------------------------ --------------------------------------- ----------
001    mary                                                                13     130.50
002    david                                                               14     131.50
003    tom                                                                 13 
006    kent                                                                14     135.50
009    jenny                                                               15