Skip to content

Latest commit

 

History

History
428 lines (290 loc) · 12.1 KB

3表.md

File metadata and controls

428 lines (290 loc) · 12.1 KB

表

实验介绍

实验内容

本节实验主要讲解了 Oracle 的表操作，如何实现对表进行创建、重重命名、复制、删除和修改等。

实验知识点

  • 常用数据类型
  • 表的创建
  • 表的重命名
  • 截断表
  • 表的复制
  • 表的删除
  • 修改表的结构

常用数据类型

以下是介绍一些我们最常用的数据类型：

  • 字符串：使用 VARCHAR2 描述（其他数据库用 VARCHAR）。例如姓名，地址等。
  • 数字：使用 NUMBER 描述。如果要描述小数则使用 NUMBER(p,s) ，其中 s 是小数位（scale），p-s 是整数位，例如 number(4,2) 代表存储小数位数为 2，总长度最多为 4 的浮点数。也可以用 INT 描述整数，FLOAT 描述小数。
  • 日期：使用 DATE 描述。Oracle 里的 DATE 包含有日期时间。其他数据库一般 DATE 只是日期，DATETIME 才表示日期时间。
  • 大文本数据：使用 GLOB 描述。最多可以保存 4G 的文字信息。

想了解更多数据类型可参考Oracle SQL 数据类型

创建表

普通创建

语法：

CREATE TABLE 表名称(
	列名称		类型		[DEFAULT 默认值]
	列名称		类型		[DEFAULT 默认值]
	...
)

例如：

注：前面的 SQL> 不用输入，只是为了说明是在 SQL 命令行输入内容。

SQL> CREATE TABLE student(
  id		NUMBER,
  name		VARCHAR2(20),
  age		number(3),
  birthday	DATE	DEFAULT SYSDATE,
  note		CLOB
);

查看是否已经创建好 student 表：

SQL> select * from tab where tname='STUDENT';

查看 student 表结构：

SQL> desc student;

我们可以向其中插入数据：

SQL> INSERT INTO student(id,name,age,birthday,note) VALUES (1,'syl',19,TO_DATE('1999-01-01','yyyy-mm-dd'),'note test');
SQL> INSERT INTO student(id,name,age,birthday,note) VALUES (2,'lou',21,TO_DATE('1997-01-01','yyyy-mm-dd'),'note test');

查看我们插入的数据：

SQL> select * from student;

通过查询结果创建

例如我们查询出 student 表中 name 字段值为 syl 的记录，通过查询结果创建一个 名叫 syl_stu 的表。

SQL> create table syl_stu as select * from student where name='syl';
SQL> select * from syl_stu;

表的重命名

SQL> RENAME student TO stu;

我们使用数据字典查询一个用户全部的数据表，可以发现表已经被更名：

SQL> select * from user_tables;

列出数据库表对象的全部信息内容，例如名称，存储情况等。

数据字典：记录所有对象的信息。

  • 用户级别：user_*，指一个用户可以使用的数据字典。
  • 管理员级别：dba_*，指由数据库管理员使用的数据字典。
  • 全部级别：all_* ，指不管是用户还是管理员都可以使用。

修改表结构

首先我们使用 desc 来查看表的结构。

SQL> desc stu;
名称       空值 类型           
-------- -- ------------ 
ID          NUMBER       
NAME        VARCHAR2(20) 
AGE         NUMBER(3)    
BIRTHDAY    DATE         
NOTE        CLOB

接下来我们来修改表的结构。

增加列 （ADD）

例一：为 stu 表增加一列 address ，设默认值为 none。

SQL> alter table stu add(address varchar2(50) default 'none');

然后查看表中数据会发现多了 address 列，并且值为 none：

SQL> select * from stu;

例二：为 stu 表增加一列 email ，不设默认值。

SQL> alter table stu add(email varchar2(50));

再次查看表，会发现多了 email 列，并且为 null。

SQL> select * from stu;

修改表中的数据列 （MODIFY）

例一：修改 stu 表的 name 列的类型为 varchar2(30) 。

SQL> alter table stu modify (name varchar2(30));

例二：修改 stu 表的 email 列的默认值为 no email 。

SQL> alter table stu modify (email default 'no email');

注意：虽然这里我们设置了默认值，但是以前插入的数据的 email 列的值仍然是 null，只有我们新插入的数据的 email 列的值才默认为 no email ，如下所示：

SQL> select * from stu;
SQL> INSERT INTO stu(id,name,age,birthday,note) VALUES (3,'plus',19,TO_DATE('1999-01-01','yyyy-mm-dd'),'note test');
SQL> select * from stu;

重命名列（RENAME COLUMN）

例：将 address 列的名称改为 saddress 。

SQL> alter table stu rename column address to saddress;

重命名列还有一个作用就是在要删除一个列的时候，用来确认是否有用户或应用程序正在使用此列，如果有的话，就会报错。

删除列 （DROP）

例：将 saddress 列删除。

SQL> alter table stu drop (saddress);

或者使用
SQL> alter table stu drop column saddress;

在删除一列的时候如果数据太多，通常需要一定时间，如果想快速使用户或应用程序不能访问某些列的时候，可以使用 unused 标记列。例如下面我们标记 email 列：

SQL> alter table stu set unused (email);

标记过后使用 desc stu 查看会发现没有显示 email 列，但是它并没有真正被删除，执行下面的语句可以删除所有被 unused 标记的列。

SQL> alter table stu drop unused columns;

复制表结构

复制表结构类似于用查询创建表，只是修改了 where 条件。例如，我们要复制 stu 的表结构。

SQL> create table stu_cp as select * from stu where 1=2;

该语句只是复制了结构，并没有复制其数据：

SQL> desc stu_cp;
SQL> select * from stu_cp;

添加约束

有时候我们存储的数据需要满足某些条件，例如：假设我们定义一个学生表，表中每个学生的学号和身份证号码是不可以重复的，学生的学号不能为空等等。这就是约束，数据库上为了保证数据表中的数据完整性而增加了约束。

约束一共有六种：数据类型，非空约束，唯一约束，主键约束，检查约束，外键约束。

注意：约束太多，会导致更新速度缓慢，有些验证操作应交给应用程序去做。

非空约束 NOT NULL

非空约束（NK）指表中的某一个字段的内容不允许为空。使用 not null 声明。

例：创建一个名叫 tech 的表，表中有 id 和 name 两个字段。设置 name 字段不为空。

SQL> create table tech(id number,name varchar2(20) not null);

下面我们就来插入一条没有 name 的数据。

SQL> insert into tech(id) values (2);

你会发现报错了 ORA-01400: 无法将 NULL 插入 ("SYSTEM"."TECH"."NAME") 。

唯一约束 UNIQUE

唯一约束（UK）指表中某一字段内容不允许重复。使用 unique 。

例：为表 tech 增加一列 email ，不允许重复。

SQL> alter table tech add(email varchar2(30) unique);

下面来插入两条 email 重复的数据：

SQL> insert into tech(id,name,email) values (2,'syl2','[email protected]');
SQL> insert into tech(id,name,email) values (3,'syl3','[email protected]');

报错 SQL 错误: ORA-00001: 违反唯一约束条件 (SYSTEM.SYS_C007385) 。这个 SYS_c007385 实际上就是约束的对象信息（约束也是数据库对象），可以从数据字典中查到。

注意：这个对象信息可能跟你的报错不一样，实际查询时，要改为你自己实际看到的对象信息才可以查询到。

-- 格式化
SQL> col owner for a10;
SQL> col constraint_name for a20;
SQL> col table_name for a15;
SQL> col column_name for a20;
-- 查询
SQL> select owner,constraint_name,table_name,column_name from user_cons_columns where constraint_name='SYS_C007385';

输出结果如下：

OWNER      CONSTRAINT_NAME      TABLE_NAME      COLUMN_NAME
---------- -------------------- --------------- --------------------
SYSTEM     SYS_C007385          TECH            EMAIL

可以看到这个约束是在 TECH 表的 EMAIL 列。

这样去数据字典中查还是比较麻烦，我们可以使用下面的方式创建唯一约束，这样能够直接根据报错知道是哪一个字段违反了约束条件。例如我们增加 cid 字段，添加唯一约束。

SQL> alter table tech add(cid number,constraint uk_cid unique(cid));

注意：设置的约束名称（这里是 uk_cid ）不可以重复。

向里面插入数据：

SQL> insert into tech(id,name,email,cid) values (3,'syl3','[email protected]',1);
SQL> insert into tech(id,name,email,cid) values (4,'syl4','[email protected]',1);

报错 SQL 错误: ORA-00001: 违反唯一约束条件 (SYSTEM.UK_CID) 。

主键约束 PRIMARY

主键约束（PK）= 非空约束 + 唯一约束 。主键约束在每一个数据表中只有一个，但是一个主键约束可以由数据表中多个列组成。一般我们只把一个字段定义成主键。

例：为表 tech 添加主键约束

SQL> alter table tech add constraints pk_id primary key(id);

检查约束 CHECK

检查约束（CK）限定数据的正确性。

例一：设定性别只能为 man 或者 female 。

SQL> alter table tech add (sex varchar2(10),constraints chk_sex check(sex='man' or sex='female'));

例二：设定年龄在 5 到 200 岁。

SQL> alter table tech add (age number(3),constraints chk_age check(age between 5 and 200));

或者
SQL> alter table tech add (age number(3),constraints chk_age check(age>=5 and age<=200));

外键约束 FOREIGN

比如我们现在有两张表，实验楼的课程表和课程类别表。一个课程是属于一个类别的。我们在课程表中增加了一个字段来描述课程的类别。这个字段的值来自于课程类别表的类别 id 。为了不允许插入类别表中类别 id 以外的值，我们就定义一个外键约束。

-- 类别表
SQL> create table syl_category(
  cid number(5) primary key,
  name varchar2(30)
);
-- 课程表
SQL> create table syl_course(
  id number(10) primary key,
  name varchar2(30),
  cid number(5),
  constraint fk_cate foreign key(cid) references syl_category(cid) on delete cascade
);

上面的 syl_category 表就叫父表 ，syl_course 表就叫子表 。

on delete cascade 表示当 syl_category 表中的 cid 被删除时，对应的 syl_cource 表中的 cid 也会被删除。

向其中插入数据：

SQL> insert into syl_category(cid,name) values (1,'cate1');
SQL> insert into syl_course(id,name,cid) values (1,'course1',1);
SQL> insert into syl_course(id,name,cid) values (2,'course1',2);

会发现第三句报错 SQL 错误: ORA-02291: 违反完整约束条件 (SYSTEM.FK_CATE) - 未找到父项关键字

删除约束

语法：

SQL> alter table <table_name> drop constraint <constraint_name>;

例如我们删除表 tech 的 sex 字段的检查约束 chk_sex 。

SQL> alter table tech drop constraint chk_sex;

删除表内容

首先我们来进行普通的删除操作。

SQL> commit; 
SQL> delete from stu;

查看表内容会发现已经被清空。但是在我们执行 rollback 回滚操作过后会发现数据又被恢复：

SQL> rollback;
SQL> select * from stu;

rollback 会恢复到上一次 commit 的数据。

上面的删除操作并没有立即将表所占的资源（约束，索引等）立即释放掉，如果想要完全删除，就需要使用 truncate ，也叫做截断表的操作。例如：

SQL> truncate table stu;

这个时候再执行回滚操作，会发现数据无法恢复：

SQL> rollback;
SQL> select * from stu;

表的删除

例如我们要删除 syl_stu 这张表，可以使用如下命令：

SQL> drop table syl_stu;

注意：如果删除的表是含有约束的父表的话，将会报错。此时需要使用 drop table <table_name> cascade constraints 删除。

总结

此处输入图片的描述