[疯狂Java]SQL:DDL语句(定义、修改表结构)

时间:2022-11-29 14:36:46

1. DDL的概念:

    1) 即Data Definition Language,即数据定义语言,操作的对象是数据库对象,主要用来定义数据对象;

    2) 数据对象:共8种,分为4大类

         i. 存储的数据:table表、数据字典(系统表);

         ii. 查询结果(临时数据):view视图(并不存储数据,只是查询结果的临时显示);

         iii. 关系:constraint约束(数据的校验规则,保证数据的完整性)、index索引(相当于目录,提高查询效率);

         vi. 代码:function函数(一次小规模的特定计算,可返回结果)、procedure存储过程(一次大规模的完整的业务处理,无返回值,但返回的东西可以通过输出参数调用给环境)、trigger触发器(事件监听/响应器);

!!因此可以看到DDL的作用是定义数据结构等抽象模型,而不是具体的数据(即数据记录的插入、修改、删除等不在DDL的范围内,那是DML的事情);

    3) DDL语句主要包括create、drop、alter、truncate,这些语句都可以操作以上8种数据对象;

    4) 之后都以表的DDL为例来讲解;


2. 创建表:

    1) 创建表只是定义表的结构,即标有多少列、每列数据类型、约束等等,并不插入实际的数据;

    2) 格式:

create table [模式名.]表名
(
列名1 类型 [其它属性和约束],
列名2 类型 [其它属性和约束],
...
);
!列定义之间用逗号,隔开,最后一条不用逗号;

    3) 如果想定义该列的默认值直接用约束:default XXX,比如:name varchar(255) default 'Peter'的列定义;

    4) MySQL支持的数据类型可以查看教程:点击打开链接

    5) 子查询建表:create table [模式名.]表名 as 子查询;

         i. 例如:create table copy_student as select * from student;

         ii. 这样就会利用查询结果来建表,列(属性)等完全跟查询结果的一样;


3. 所有在数据库中建的表都可以在元数据表information_schema的TABLES表中记录,这是系统数据库中的系统表;


4. 修改表的结构:

    1) 使用alter关键字,其格式为:alter table 表名 选项;

    2) 具体怎么修改表结构关键就在这个选项上,注意:这里能修改的仅仅是表的结构,即属性(列)的数据类型、默认值、约束等等,但是不能改变表中的数据;

!!因此alter table的作用对象是表的结构而不是表中的数据;

    3) 增加字段(属性/列):使用add选项,增加大于1列用括号括起来,只增加一列则不用,格式如下

add
(
增加列1 类型 [其它属性和约束] [first|after 列名(插入到哪列之后)],
增加列2 类型 [其它属性和约束] [first|after <span style="font-family: Arial, Helvetica, sans-serif;">列名(插入到哪列之后)],</span>
...
);
!只增加一列可以不用括号;

!!如果是first表示插入到第一列,“after 列名”表示插入到“列名“所在的列之后,什么都不写表示简单地追加到最后一列;

    4) 如果数据表里已经有数据了,则加完这一列之后该列的值必定为空,因此此时决不能将这列指定为非空(NOT NULL),因为两者逻辑冲突,会直接报错,除非该表还没有任何数据,或者为改列定义默认值;

    5) 修改已有的列(字段/属性):add是无中生有,而modify则用来修改已有的列(数据类型、约束等,所有都能修改,就是列名不能修改),并且一次只能修改一列,不能同时修改多列(标准SQL规定的,但是有些数据库实现允许同时修改多列,比如Oracle,但是MySQL还是执行了SQL的标准),格式如下

<pre name="code" class="sql">modify 要修改的列的列名 新的数据类型 [新的属性以及约束] [first|after 要插在之后的列名];

!!可以看到修改的不仅仅可以是数据类型、属性约束等,也开改变改列的位置哟!! 

    6) 虽然标准SQL不支持修改列名,但是通常修改列名是广泛需求的,因此MySQL提供了change选项,该选项作用和modify完全一样,只不过多了一个修改列名的功能,格式如下:

modify 要修改的列的列名 新的数据类型 [新的属性以及约束] [first|after 要插在之后的列名];
!就多了一个参数而已,即第三个参数是新的列名;

!change选项不是标准SQL语法,而是MySQL自己提供的;

    7) 删除列:drop 列名;

!!只支持一次删一列,不能一次删多列;

    8) 修改表名:标准SQL不支持修改表名,但MySQL还是提供了这个功能,选项格式是:rename to 新表名;

!!可以看到,标准SQL并不提倡修改列名和表名,希望创建时就确定而不要后期再改动,原因很简单,因为一旦名称发生改变,那么所有依赖数据库的应用程序可能也需要修改,这就加大了维护的难度

    9) 修改表结构并非总能成功,因为修改必须符合原有的规则和约束,否则系统会因数据不完整而拒绝修改,从而报错!

!!因此尽量在设计表的时候就确定表的结构,后期再改变表的结构将会非常被动(产生大量连带效应);


5. 删除表:

    i. 使用drop关键字,格式:drop table 表1[, 表2, ...];

    ii. 可以看到支持一次删除多个表;

    iii. 删表总是可以成功的,删除后表结构、表中的所有数据、所有的索引和约束也统统删除;

6. 删除表中的数据但不删除表结构——truncate:

    i. 格式:truncate 表名;

    ii. 该SQL语句仅仅删除了表中的全部数据,但是仍保一张空的该表,同时保留表的结构;

    iii. 在没有truncate语句之前是使用delete来逐条删除表中的数据达到该目的,因此为了方便达到该目的(减少编程工作量)就提供了truncate语句;

    iv. 在MySQL5.0.3之前,truncate底层只是简单调用delete,因此和delete效率一样,但是在这之后大大优化了truncate,因此现在truncate总比delete要高效;