Oracle数据库管理、应用与开发(十二)

十八、创建与管理表

18.1 数据库中的对象

表：基本的数据存储的集合,由行与列组成

视图：从表中抽出的逻辑上相关的数据集合。

序列: 提供有规律的数值

索引：提高查询效率

同义词：给对象起的别名。

18.2 Oracle数据库中的表

18.2.1 用户自定义表

18.2.1.1 表名和列名的命名规范:

必须以字母开头

必须在 1–30 个字符之间

必须只能包含 A–Z, a–z, 0–9, _, $, 和 #

必须不能和用户定义的其他对象重名

必须不能是Oracle 的保留字

18.2.1.2 创建表必须具备的条件

CREATE TABLE权限

存储空间

必须指定表名、列名、数据类型、尺寸

18.2.1.3 创建表的语法

              CREATE  TABLE  table_name

                  (column datatype [DEFAULT expr][, ...]);

           18.2.1.4 数据类型

数据类型            描述

VARCHAR2(size)    可变长字符数据

CHAR(size)        定长字符数据

NUMBER(p,s)      可变长数值数据

DATE              日期型数据

LONG              可变长字符数据，最大可达到2G

CLOB              字符数据，最大可达到4G

RAW (LONG RAW)    原始的二进制数据

BLOB              二进制数据，最大可达到4G

BFILE             存储外部文件的二进制数据，最大可达到4G

ROWID             行地址

           18.2.1.5创建表的分析

    在数据库中必须遵循一定的规范，这些规范称为范式。目前关系数据库有6种方式，数据库只需要满足3NF即可，需要使用范式对数据库中表进行处理。

              下面我以学生信息为例分析

   学生信息表包含的字段：学号、学生姓名、出生日期、学生性别、所属班级、所属院系、系主任姓名、课程编号、成绩

              学号：sid  varchar2

              学生姓名：sname

              出生日期：sbirth  date

              学生性别：ssex      

              所属班级：sclass

              所属院系：sdep

              系主任姓名：dname

              课程编号：cid

              成绩：grade  number(5,2)

根据如上信息可以创建存储学生信息表，包含以上的全部信息，但非规范。

18.2.1.6分析设计过程

                  必须满足3NF，但前提必须满足1NF和2NF。

属性不可再分(1NF)，一对多关系

学生信息表：sid、sname、sbirth、ssex、sclass、sdep、dname

成绩表：sid 、cid 、grade

(前提必须满足1NF )表中的所有非主属性都完全依赖于任一候选键。

sid → sname

sid → sbirth

sid → ssex

sid → sclass

sdep → dname

其他普通属性不应当存在部分依赖于主要属性，此普通属性应当被分离到新的表中去

Student(sid,sname,sbirth,ssex,sclass,sdep)

Dept(sdep,dname)

Grade(sid,cid,grade)

（前提满足2NF）表中的每一个非主属性都不传递依赖于任何候选键。

s(sid,sname,sbirth,ssex,sclass,sdep)

d(sdep,dname)

g(sid,cid,grade)

在满足2NF后分析

Sid → sclass

Sclass → dep

Sid → sdep

Student(sid,sname,sbirth,ssex,sclass)

Class_dept(sclass,sdep)

Ddpt(sdep,dname)

Grade(sid,cid,grade)

18.2.2 Oracle数据字典

           18.2.2.1查询用户自定义表

//查询用户定义的表的名称
SQL> select table_name from user_tables;
//查询用户自定表的详细特征
SQL> select * from user_tables
//等效于
SQL> select * from tabs;
18.2.2.2 查询用户定义的各种数据库对象
SQL> select distinct object_type from user_objects; 
OBJECT_TYPE
-------------------
SEQUENCE
TABLE
INDEX
18.2.2.3 查看用户定义的表、视图、序列和同义词
SQL> select * from user_catalog; 
TABLE_NAME              TABLE_TYPE
------------------------------ -----------
USESS                       TABLE
ABC                          TABLE
DEPT                        TABLE
EMP                         TABLE
SALGRADE                 TABLE
PRODUCT_SEQ             SEQUENCE
ORDERS_SEQ               SEQUENCE
ORDERITEM_SEQ          SEQUENCE
USERS                       TABLE
PRODUCT                   TABLE
ORDERS                     TABLE
ORDERITEM                TABLE
TMP                         TABLE
PAGES                       TABLE
STUDENT                   TABLE
COURSE                     TABLE
SC                            TABLE
TEST                         TABLE
 
18 rows selected

 

18.3 修改表

       18.3.1 追加、修改和删除字段

使用 ALTER TABLE 语句追加、修改、或删除列的语法形式：

18.3.1.1追加

ALTER TABLE table_name

ADD (column datatype [DEFAULT expr]

              [, column datatype]...);

18.3.1.2 修改

ALTER  TABLE  table_name

MODIFY (column datatype [DEFAULT expr]

              [, column datatype]...);

18.3.1.3删除

ALTER TABLE table

DROP  column  (column_name);

18.3.2 修改列的名称

ALTER TABLE table_name rename column old_column_name

to new_column_name

18.3.3 案例

//采用子查询创建customer表，表的结构与users表完全一致
SQL> create table customer as select * from users; 
Table created 
SQL> commit; //事务提交
Commit complete
//为customer表添加新的列email ，类型varchar2(20).
SQL> alter table customer add email varchar2(20);
Table altered
SQL> commit;
SQL> desc customer;
Name Type       Nullable Default Comments 
-------- ------------ -------- ------- -------- 
USERNAME VARCHAR2(20) Y                        
PASSWORD VARCHAR2(20)                           
NAME VARCHAR2(20)                           
ADDRESS VARCHAR2(20) Y                        
ZIP     NUMBER(6) Y                        
EMAIL VARCHAR2(20) Y    
//修改某个字段的类型
SQL> alter table customer modify email number(4);
Table altered
SQL> commit;
Commit complete
SQL> desc customer;
Name Type       Nullable Default Comments 
-------- ------------ -------- ------- -------- 
USERNAME VARCHAR2(20) Y                        
PASSWORD VARCHAR2(20)                           
NAME VARCHAR2(20)                           
ADDRESS VARCHAR2(20) Y                        
ZIP     NUMBER(6) Y                        
EMAIL NUMBER(4) Y      
//删除某个字段
SQL> alter table customer drop column email;
Table altered
SQL> commit;
Commit complete
SQL> select * from customer;
//修改表的某个字段的名称
SQL> alter table customer rename column address to addr;
Table altered
SQL> commit;

 

18.4 删除表

数据和结构都被删除

所有正在运行的相关事物被提交

所有相关索引被删除

DROP TABLE 语句不能回滚

18.5 修改对象的名称

执行RENAME语句改变表, 视图, 序列, 或同义词的名称

必须是对象的拥有者

//修改对象的名称操作

SQL> rename customer to customers;

18.6 清空表

TRUNCATE TABLE 语句:

删除表中所有的数据

释放表的存储空间

TRUNCATE语句不能回滚

可以使用 DELETE 语句删除数据

//清空表的操作

SQL> truncate table customers;

18.7 总结

您已经学会如何使用DDL语句创建、修改、删除、和重命名表。

语句               描述

CREATE TABLE        创建表

ALTER TABLE          修改表结构

DROP TABLE           删除表

RENAME                重命名表

TRUNCATE              删除表中的所有数据，并释放存储空间