第十三章创建与维护表

数据定义语言 

• 数据定义语言： (Data Definition Language, DDL) 是SQL语言集中负责数据结构定义与数据库 对象定义的语言，由CREATE、ALTER与DROP三个语法所组成。 

• 数据库对象，是数据库的组成部分，有表、约束 、索引、视图、序列、同义词、触发器、存储过程、函数等。 

    表  表是用来存放用户数据的对象，由行和列组成，列就是字段，行就是表中的记录 

    约束     保证数据完整性的规则，设置在单个字段或者多个字段组合上，写入这些字段的数据必须符合约束的限制 

    视图       虚表，是一个命名的查询，用于改变基表数据的显示，简化查询。访问方式与表相同，同样可使用查询语句 

     索引       构建于表的单字段或者字段组合上，用于加速对表中数据的查询 

     序列       产生顺序的不重复数字串，被作为主键约束值的参照 

 同义词     数据库对象的别名 

• Oracle中数据库对象命名原则 

     –必须由字母开始，长度在 1–30个字符之间。 

     –名字中只能包含 A –Z, a –z, 0 –9, _ (下划线)，$ 和 #。 

     –同一个Oracle服务器用户所拥有的对象名字不能重复。 

     –名字不能为Oracle的保留字。 

     –名字是大小写不敏感 

–您必须具备 : 

         •CREATE TABLE的权限 

         •一定的存贮空间(ALTER USER 用户名 QUOTA 尺寸 ON 表空间名字) 

     –您需要指定: 

          •表名 

          •列名、列的类型及列的宽度 

DEFAULT 选项：

指定在插入或更新数据时，列的默认值，数据类型必须与列的类型匹配

数据类型：                                   

CHAR(size)：  固定长度字符型数据，长度的大小以字节为单位，默认和最小长度为1；最大长度为2000

VARCHAR2 (size)  可变长度字符数据，最小长度是1，最大长度是40                                

 CLOB     可变长度字符数据，最大可存储 4G  数据 

–例： 

     –char(10)：固定占用10个字节的空间，如 ‘沈阳’，声明为char(10)的话，前4个字节放'沈阳'，后面补充6个空值 。 

     –Varchar2(10):最大只能保存10个字节，如‘沈阳’，声明为Varchar2(10) 的话， 则该数据实际长度为4。 

  NUMBER 数值型，可以表示整数，也可以表示小数，表数范围-10的124次方到10的126次方

NUMBER（n）整型

Number（p，s）数值型， 总长度为p，小数位最大为s位，整数位最大为p-s位，p的范围从1-38，s的范围从-84-127 

–例： 

     –number(5,2)：表示一个小数，表数范围为 -999.99到999.99 

     –number(5) ：表示一个整数，范围-99999到99999

• NUMBER(p,s)：数值型：总长度为p，小数位最大为s位 ，整数位最大为p-s位，p的范围从1到38，s的范围从-84到127。

–S>0：只能表示小数，精确到小数点右边s位，并四舍五入，然后检验有效数位是否<=p 

–S<0：只能表示整数，并且精确到小数点左边s位，进行四舍五入，然后检验有效数位是否<=p+|s| 

 –S=0：只能表示整数

 –p<s：只能表示数字是绝对值小于1的数字，且从小数点右边开始的前s-p位必须是0，保留s位小数。 

日期型：

  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 

1.学校想做一个选课系统，其中涉及到课程表， 学生表，请分别创建这两个表，自己思考表中应有的列及数据类型。  

create table student (                 --学生表 

                    xh char(4),--学号 

                    xm varchar2(10),--姓名 

                    sex char(2),--性别 

                    birthday date,--出生日期 

                    sal number(7,2), --奖学金 

                   studentcid number(2) --学生班级号 

CREATE TABLE course(

       courseno NUMBER(4),

       coursename CHAR(30),

       teacher CHAR(10),

       TYPE CHAR(6),

       grade NUMBER(1),

       begindate DATE,

       RESUME VARCHAR2(500))

使用子查询创建表 ：

 –新表的字段列表必须与子查询中的字段列表匹配 

 –字段列表可以省略  

–Select列表中的表达式列需要给定别名，如果没有别名会产生错误 

练习2 

• 1.通过子查询的方式创建一个表dept10,该表保存10号部门的员工数据。 

   CREATE  TABLE dept10 AS

SELECT * 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]...); 

• 修改已存在的列 

     –列的修改可以修改列名，数据类型，长度，及默认值。 

     –修改数据类型：已有的行数据必须为空。 

     –修改长度原则：

       数值型修改长度：当长度向小改时，已有行的数该列必须为空；当长度向大改                                             时，可以随意修改。 

       字符型修改长度：当长度向小改时，只要修改后的值能容纳下当前已有数据的最大值即可，当长度向大改时，可以随意修改。 

     –修改列的默认值： 

     –默认值的修改不会影响已经存在的行，只影响新增加的行。 

• 8i版本之后，可以修改列名字 

     –ALTER TABLE 表名 RENAME COLUMN 原有列名 TO 新列

修改已存在的列 

      –把dossier表性别(sex) 列，修改为长度为2 

  ALTER TABLE dossier MODIFY  (sex CHAR(2)); 

–添加默认值 

  ALTER TABLE dossier 

  MODIFY  (sex  DEFAULT ‘男’); 

–删除列语法： 

   ALTER TABLE table 

   DROP (columnname [,columnname]); 

 –可以用DROP子句从表中删除列，包括列的定义和数据。 

     –删除列原则： 

          •列可以有也可以没有数据。 

          •表中至少保留一列。 

          •列被删除后，不能再恢复。 

          •被外键引用的列，不能被删除。 

–删除列语法一 

  ALTER TABLE emp DROP COLUMN sex; 

     –删除列语法二 

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

     –删除dept10表的两个字段 “last_name”和 “newsalary”。 

  ALTER TABLE dept10 DROP (last_name,newsalary); （这个如果出现错误看看是否列没有用括号括起来）

 练习3 

• 1.在员工表中添加一个性别列，列名为gender，类型为char(2)，默认值为“男” 

ALTER TABLE emp ADD gender CHAR(2) DEFAULT '男'

• 2.修改员工表中性别列的数据类型为char(4)

ALTER TABLE emp MODIFY gender CHAR(4) 

• 3.修改员工表中性别列的默认值为“女”

ALTER TABLE emp MODIFY gender CHAR(4) DEFAULT'女' 

• 4.删除员工表中的性别列

ALTER TABLE emp DROP (gender)        

删除表 

      –删除表语法： 

   DROP TABLE table； 

           •只有表的创建者 

           •或具有DROP ANY TABLE权限的用户才能删除表 

      –删除表原则： 

           •表中所有的数据和结构都被删除。 

           •任何视图和同义词被保留但无效。 

           •所有与其相关的约束和索引被删除。 

           •任何未完成的事务被提交。DROP TABLE emp; 

   • 重命名表 

          –重命名语句语法： 

     RENAME old_name TO new_name;

        •必须是对象的所有者 

        –把emp表重新命名为empl 

    RENAME emp TO empl; 

截断表 

• TRUNCATE和DELETE区别 

     –TRUNCATE是DDL，只能删除表中所有记录，释放存储空间， 使用ROLLBACK不可以回滚。 

     –DELETE是DML，可以删除指定记录，不释放存储空间，使用ROLLBACK可以回滚。 

数据字典： 系统自动创建的表，由oracle自己管理，自己创建的叫做用户表

SELECT * FROM User_Tables

SELECT * FROM User_Col_Comments WHERE table_name='emp'（查看某表有多少列）

• 相关数据字典 

–查询数据字典 

        SELECT table_name 

        FROM  user_tables; 

–查看数据字典结构 

      DESC user_tables