创建和维护表

一、数据定义语言

数据定义语言（DDL）是SQL语言集中负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER和DROP三个语法组成。

二、数据库对象

• 数据库对象，是数据库的组成部分，有表、约束、索引、视图、序列、同义词、存储过程、函数等。
  1.表：表是用来存放用户数据的对象，由行和列组成，列就是字段，行就是表中的记录。
  2.约束：保证数据完整性的约束，设置在单个字段或者多个最短的组合上，写入这些字段的数据必须符合约束的限制。
  3.视图：是一个命名的查询，用于改变基表的数据显示，通过视图能够简化查询，访问方式与表相同。
  4.索引：构建于表的单字段或者字段组合上，用于加速对表中的数据进行查询。
  5.序列：产生顺序的不重复数字串，被作为主键约束值的参照。
  6.同义词：数据库对象的别名。
• Oracle数据库对象的命名原则
  1.必须由字母开始，长度在1-30个字符之间。
  2.名字中只能包含A-Z，a-z，0-9，_（下划线），$和#。
  3.同一个Oracle服务器用户所拥有的对象名字不能重复。
  4.名字不能用Oracle的保留字。
  5.名字是大小写不敏感。

三、创建表

• 建表语句语法

1. 建表语法
   CREATE TABLE [schema.]table
   (column datatype [DEFAULT expr][, ...]);
2. 你必须具备
   1.CREATE TABLE的权限
   2.一定的存储空间（ALTER USER 用户名 QUOTA 尺寸 ON 表空间名字）
3. 你需要指定
   1.表名
   列名、列的类型及列的宽度

• DEFAULT选项

1. 指定插入或更新数据时，列的默认值
2. 合法的值可以是字面值、表达式或SQL函数
3. 非法的值是另一个列的名称或虚拟列
4. 默认值的数据类型必须和列的类型匹配

四、数据类型

• 数据类型（字符型）

CHAR（size）: 固定长度字符型数据，长度的大小以字节为单位，默认和最小长度为1；最大长度为2000
VARCHAR2（size）: 可变长度字符数据，最小长度为1；最大长度为4000
CLOB 可变长度字符数据，最大可存储4G数据

• 数据类型（数值型）

NUMBER 数值型：可以表示整数，也可以表示小数，表数范围-10的125次方到10的126次方
NUMBER（n） 整型:
NUMBER（p,s） 数值型：总长度为p，小数位最大为s位，整数位最大为p-s位，p的范围从1到38，s的范围从-84到127

• 数据类型（日期型）

DATE 包括年月日时分秒
TIMESTAMP 9i后新增的，精度比DATE更高一些，可以精确到毫秒

• 数据类型（图片类型）

BLOB 最大可以存储4G二进制的数据，可以存放图片，声音，文件

五、创建表的几种方式

• 建表语法创建

CREATE TABLE DOSSIER ( 
   ID  NUMBER(4),  
   CNAME  VARCHAR2(20 ),  
   BIRTHDAY  DATE,  
   STATURE    NUMBER(3),  
   WEIGHT  NUMBER(5, 2),  
   COUNTRY_CODE  CHAR(2 )  DEFAULT ‘01’);

• 子查询语法创建表

1. 使用子查询创建表的语法

CREATE  TABLE  table[(column, column...)] 
AS  subquery;

2. 新表的字段列表必须与子查询中的字段列表匹配
3. 字段列表可以省略
4. Select列表中的表达式列需要给定别名，如果没有别名会产生错误

CREATE  TABLE  dept10  
AS  SELECT empno, ename, sal＋1000 newSalary  
FROM emp   
WHERE deptno = 10;

• 引用另一个用户的表

如果一个表不属于当前用户，如果引用它，必须把方案名放在前面。例如:

scott.emp
SELECT  * 
FROM  scott.emp

六、修改表

• 修改表的定义

• 添加列语法

ALTER TABLE table   
ADD (columnname datatype[DEFAULT expr]  
          [, columnname datatype]...);

• 修改列语法

ALTER TABLE table  
MODIFY(columnname datatype[DEFAULT expr]  
             [, columnname datatype]...);

1. 把dossier表性别（sex）列，修改为长度为2

ALTER  TABLE  dossier  MODIFY  ( sex  CHAR(2) )

2. 添加默认值

ALTER  TABLE  dossier  MODIFY  ( sex  DEFAULT  ‘ 男 ’ )

• 删除列语法

ALTER  TABLE  table 
DROP (columnname [,columnname]);

• 添加新列

• 添加列原则
  a)可以添加或修改列
  b)不能指定新添加列的位置，新列会成为最后一列
• 如在dossier表上增加性别字段

ALTER  TABLE  dossier  ADD  (  sex  CHAR(1)  )

• 修改已经存在的列

• 列的修改可以修改列名，数据类型，长度，及默认值。
• 修改数据类型：已有的行数据必须为空。
• 修改长度原则：
  a)字符型修改长度：当长度向小改时，只要是修改后的值能够容纳下当前已有数据的最大值即可，当长度向大改时，可以随意改变。
  b)数值型修改长度：当长度向小改时，已有行的数该列必须为空，当长度向大改时，可以随意改。
• 修改列的默认值：默认值的修改不会影响已经存在的行，只会影响新增加的行。
• 修改列名：ALTER TABLE表名RENAMR COLUMN 原有列名 TO 新列名

• 删除列

• 可以用DROP子句从表中删除列，包括列的定义和数据。
• 删除列的原则：
  a)列可以有也可以没有数据
  b)表中至少要保留一列
  c)列被删除后，不能再恢复
  d)被外键引用的列，不能被删除
• 删除列的用法

删除列的语法一：

ALTER  TABLE  emp  DROP  COLUMN  sex

删除列的语法二：

ALTER  TABLE  emp  DROP  ( sex )

删除表中的两个字段：

ALTER  TABLE  emp  DROP  ( sex , ename )

七、删除表

• 语法：DROP TABLE table

a)只有表的创建者
b)或具有DROP ANY TABLE权限的用户才能删除表

• 删除表的原则

a)表中的所有数据和表结构都被删除
b)任何视图和同义词被保留但是无效
c)所有与其相关的约束和索引都被删除
d)任何未完成的事务被提交

八、重命名表

• 语法：RENAME old_name TO new_name

a)必须是对象的所有者
-例子: 将emp 表重新命名为emp1

RENAME  emp  TO  emp1

九、截断表

• 语法：TRUNCATE TABLE table

a)执行TRUNCATE语句的前提，必须是表的所有者
b)或者有DELETE ANY TABLE系统权限来截断表

TRUNCATE和DELETE的区别
1)TRUNCATE是DDL，只能删除表中的所有记录，释放存储空间，使用ROLLBACK不可以回滚。
2)DELETE是DML，可以删除指定记录，不释放存储空间，使用ROLLBACLK可以回滚。

十、数据字典

• 数据字典表：有oracle服务器创建和维护的表，通过数据字典，可以很容易了解当前用户的对象的信息。比如user_tables这个数据字典表，里面保存当前用户所有的表对象。
• 数据字典表的数据是由oracle自动维护的。
• 相关数据字典

a)查询数据字典

SELECT  table_name
FROM  user_tables

b)查看数据字典结构

DESC  user_tables