sql学习第一篇：表的创建，修改，删除

1.通过 SQL 语句管理数据表

1.1 创建数据表     SQL 语句 CREATE TABLE 用于创建数据表，其基本语法如下：

　　CREATE TABLE 表名 (  字段名 1 字段类型，  字段名 2 字段类型，  字段名 3 字段类型，  ………………  约束定义 1,  约束定义 2,  ……………… )

    这里的 CREATE TABLE 语句告诉数据库系统我们要创建一张数据表，CREATE TABLE 语句后紧跟着表名，这个表名不能与数据库中已有的表名重复。    

　　括号中是一条或者多条表定义，表定义包括字段定义和约束定义两种，一张表中至少要 有一个字段定义，而约束定义则是可选的。约束定义包括主键定义、外键定义以及唯一约束 定义等。下面用例子来演示这个语句的使用。     下面的 SQL 语句创建了一个用于保存人员信息的数据表： CREATE TABLE T_Person (     FName VARCHAR(20),     FAge INT )    

　　注意：上边的 SQL 在 MYSQL、MSSQLServer 以及 DB2 下可以正常运行，不过由于各 个主流数据库系统中数据类型的差异，所以在其他数据库中可能需要改写。    

　　下面是此 SQL 在 Oracle 下的写法： CREATE TABLE T_Person (     FName VARCHAR2(20),     FAge NUMBER (10) )    

　　可以看到这里将人员数据表的名称为 T_Person，并且拥有两个字段，一个字段为记录 姓名的字段 FName，另一个为记录年龄的 FAge。姓名为长度不确定的字符串类型，因此这 里我们使用最大长度为 20 的可变长度字符串 VS Studio 的插件开发来定义 FName 字段；年 龄为整数，所以使用 INT 来定义 FAge 字段。需要用逗号来分隔开每一个字段的定义，我们 这里将每个字段都在单独一行中定义，这并不是强制要求的，我们可以将所有字段定义在一 行中，如下： CREATE TABLE T_Person(FName VARCHAR(20),FAge INT);    

　　这样的 SQL 语句也是合法的，不过当字段数量比较多的时候这样写就会显得过于杂乱， 因此推荐使用每行一个字段定义的方式，这样容易阅读，而且出现错误的时候也容易调试， 因为很多数据库系统都是根据行号来提示错误信息的。    

　　字段定义只能限制一个字段中所能填充的数据类型，对于“字段值必须唯一、录入的年 龄必须介于 18 到 26 岁之间、姓名不能为空”这样的需求则无法满足，这也就是约束定义的 工作。

1.2 定义非空约束    

　　我们在注册一些网站的会员的时候都需要填写一些表格，这些表格中有一些属于必填内 容，如果不填写的话会无法完成注册。同样我们在设计数据表的时候也希望某些字段为必填 值，比如学生信息表中的学号、姓名、年龄字段是必填的，而个人爱好、家庭电话号码等字 段则选填，所以我们如下设计建表 SQL：

　　MYSQL、MSSQLServer、DB2：     CREATE TABLE T_Student (FNumber VARCHAR(20) NOT NULL ,FName VARCHAR(20) NOT NULL ,FAge INT NOT NULL ,FFavorite VARCHAR(20),FPhoneNumber VARCHAR(20))

　　Oracle：     CREATE  TABLE  T_Student  (FNumber  VARCHAR2(20)  NOT  NULL  ,FName     VARCHAR2(20)  NOT  NULL  ,FAge  NUMBER  (10)  NOT  NULL  ,FFavorite     VARCHAR2(20),FPhoneNumber VARCHAR2(20))    

　　可以看到，与普通字段定义不同的地方是，非空字段的定义在类型定义后增加了“NOT NULL”，其他定义方式与普通字段相同。

1.3 定义默认值    

　　我们在定义字段的时候为字段设置一个默认值，当向表中插入数据的时候如果没有为这 个字段赋值则这个字段的值会取值为这个默认值。比如我们希望设置教师信息表中的是否班 主任字段 FISMaster 的默认值为“NO”，那么只要如下设计建表 SQL：

　　MYSQL、MSSQLServer、DB2：     CREATE TABLE T_Teacher (FNumber VARCHAR(20),FName VARCHAR(20),FAge     INT,FISMaster VARCHAR(5) DEFAULT 'NO')

　　Oracle：     CREATE TABLE T_Teacher (FNumber VARCHAR2(20),FName VARCHAR2(20),FAge     NUMBER (10),FISMaster VARCHAR2(5) DEFAULT 'NO')    

　　可以看到，与普通字段定义不同的地方是，非空字段的定义在类型定义后增加了 “DEFAULT 默认值表达式”，其他定义方式与普通字段相同。

1.4 定义主键。    

　　通过主键能够唯一定位一条数据记录，而且在进行外键关联的时候也需要被关联的数据 表具有主键，所以为数据表定义主键是非常好的习惯。在 CREATE TABLE 中定义主键是通 过 PRIMARY KEY 关键字来进行的，定义的位置是在所有字段定义之后。比如我们为公交 车建立一张数据表，这张表中有公交车编号 FNumber、驾驶员姓名 FDriverName、投入使用 年数 FUsedYears 等字段，其中公交车编号 FNumber 字段要定义为主键，那么只要如下设计 建表 SQL：

　　MYSQL,MSSQLServer:     CREATE TABLE T_Bus (FNumber VARCHAR(20),FDriverName VARCHAR(20),     FUsedYears INT,PRIMARY KEY (FNumber))

　　Oracle:     CREATE TABLE T_Bus (FNumber VARCHAR2(20),FDriverName VARCHAR2(20),     FUsedYears NUMBER (10),PRIMARY KEY (FNumber))

　　DB2:     CREATE  TABLE  T_Bus  (FNumber  VARCHAR(20)  NOT  NULL,FDriverName     VARCHAR(20),     FUsedYears INT,PRIMARY KEY (FNumber))    

　　可以看到，主键定义是在所有字段后的“约束定义段”中定义的，格式为 PRIMARY KEY (主键字段名)，在有的数据库系统中主键字段名两侧的括号是可以省略的，也就是可以写成 PRIMARY KEY FNumber，不过为了能够更好的跨数据库，建议不要采用这种不通用的写法。 需要注意的是，在上边列出的 DB2 数据库的 CREATE TABLE 语句中，我们为 FNumber 字段设置了非空约束。因为在 DB2 中，主键字段必须被添加非空约束，否则会报出类似 “ "FNUMBER" 不能是一列主键或唯一键，因为它可包含空值。 ”的错误。     　　　　     有的时候数据表中是不存在一个唯一的主键的，比如某个行业协会需要创建一个保存个 人会员信息的表，表中记录了所属公司名称FCompanyName、公司内部工号FInternalNumber、 姓名 FName 等，由于存在同名的情况，所以不能够使用姓名做为主键，同样由于各个公司 之间的内部工号也有可能重复，所以也不能使用公司内部工号做为主键。不过如果确定了公 司名称，那么公司内部工号也就唯一了，也就是说通过公司名称 FCompanyName 和公司内 部工号 FInternalNumber 两个字段一起就可以唯一确定一个个人会员了，我们可以让 FCompanyName、FInternalNumber 两个字段联合起来做为主键，这样的主键被称为联合主键 （或者称为复合主键）。可以有两个甚至多个字段来做为联合主键，这就可以解决一张表中 没有唯一主键字段的问题了。定义联合主键的方式和唯一主键类似，只要在 PRIMARY KEY 后的括号中列出做为联合主键的各个字段就可以了。上面的例子的建表 SQL 如下：

　　MYSQL,MSSQLServer,DB2:     CREATE TABLE T_PersonalMember (FCompanyName VARCHAR(20),     FInternalNumber VARCHAR(20),FName VARCHAR(20),     PRIMARY KEY (FCompanyName,FInternalNumber))

　　Oracle:     CREATE TABLE T_PersonalMember (FCompanyName VARCHAR2(20),     FInternalNumber VARCHAR2(20),FName VARCHAR2(20),     PRIMARY KEY (FCompanyName,FInternalNumber))

　　DB2:     CREATE TABLE T_PersonalMember (FCompanyName VARCHAR(20) NOT NULL,     FInternalNumber VARCHAR(20) NOT NULL,FName VARCHAR(20),     PRIMARY KEY (FCompanyName,FInternalNumber))    

　　同样需要注意的是，在 DB2 中组成联合主键的每一个字段也都必须被添加非空约束。    

　　采用联合主键可以解决表中没有唯一主键字段的问题，不过联合主键有如下的缺点：    效率低。在进行数据的添加、删除、查找以及更新的时候数据库系统必须处理两个字 段，这样大大降低了数据处理的速度。    使得数据库结构设计变得糟糕。组成联合主键的字段通常都是有业务含义的字段，这 与“使用逻辑主键而不是业务主键”的最佳实践相冲突，容易造成系统开发以及维护 上的麻烦。   

　　使得创建指向此表的外键关联关系变得非常麻烦甚至无法创建指向此表的外键关联 关系。    加大开发难度。很多开发工具以及框架只对单主键有良好的支持，对于联合主键经常 需要进行非常复杂的特殊处理。   考虑到这些缺点，我们应该只在兼容遗留系统等特殊场合才使用联合主键，而在其他场 合则应该使用唯一主键。

1.5 定义外键    

　　外键是非常重要的概念，也是体现关系数据库中“关系”二字的体现，通过使用外键， 我们才能把互相独立的表关联起来，从而表达丰富的业务语义。    

　　外键是定义在源表中的，定义位置同样为所有字段定义的后面，使用 FOREIGN KEY 关键字来定义外键字段，并且使用 REFERENCES 关键字来定义目标表名以及目标表中被关 联的字段，格式为：     FOREIGN KEY 外键字段名称 REFERENCES 目标表名(被关联的字段名称)    

　　比如我们创建一张部门信息表，表中记录了部门主键 FId、部门名称 FName、部门级别 FLevel 等字段，建表 SQL 如下：

　　MYSQL,MSSQLServer:     CREATE TABLE T_Department (FId VARCHAR(20),FName VARCHAR(20),     FLevel INT,PRIMARY KEY (FId))

　　Oracle:     CREATE TABLE T_Department (FId VARCHAR2(20),FName VARCHAR2(20),     FLevel NUMBER (10) ,PRIMARY KEY (FId))

　　DB2:     CREATE TABLE T_Department (FId VARCHAR(20) NOT NULL,FName VARCHAR(20), FLevel INT,PRIMARY KEY (FId))    

　　接着创建员工信息表，表中记录工号、姓名以及所属部门等信息，为了能够建立同部门 信息表之间的关联关系，我们在员工信息表中保存部门信息表中的主键，保存这个主键的字 段就被称为员工信息表中指向部门信息表的外键。建表 SQL 如下： MYSQL,MSSQLServer,DB2:     CREATE TABLE T_Employee (FNumber VARCHAR(20),FName VARCHAR(20),     FDepartmentId VARCHAR(20),     FOREIGN KEY (FDepartmentId) REFERENCES T_Department(FId)) Oracle:     CREATE TABLE T_Employee (FNumber VARCHAR2(20),FName VARCHAR2(20),     FDepartmentId VARCHAR2(20),     FOREIGN KEY (FDepartmentId) REFERENCES T_Department(FId))

1.6修改已有数据表    

　　通过 CREATE TABLE 语句创建的数据表的结构并不是永远不变的，很多因素决定我们 需要对数据表的结构进行修改，比如我们需要在 T_Person 表中记录一个人的个人爱好信息， 那么就需要在 T_Person 中增加一个记录个人爱好的字段，再如我们不再需要记录一个人的 年龄，那么我们就可以将 FAge 字段删除。这些操作都可以使用 ALTER TABLE 语句来完成。 ANSI-SQL 中为 ALTER TABLE 语句规定了两种修改方式：添加字段和删除字段，有的数据 库系统中还提供了修改表名、修改字段类型、修改字段名称的语法。     　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　     首先来看添加字段的语法： ALTER TABLE 待修改的表名 ADD 字段名 字段类型    

　　在语句中需要指定要修改的表的表名、要增加的字段名以及字段的数据类型，其使用方 式和创建表的非常类似。下面是为 T_Person 表增加个人爱好字段的 SQL 语句： ALTER TABLE T_PERSON ADD FFavorite VARCHAR(20)    

　　注意：上边的 SQL 在 MYSQL、MSSQLServer 以及 DB2 下可以正常运行，不过由于各 个主流数据库系统中数据类型的差异，所以在其他数据库中可能需要改写。    

　　下面是此 SQL 在 Oracle 下的写法： ALTER TABLE T_PERSON ADD FFavorite VARCHAR2(20)    

　　接下来看删除字段的语法： ALTER TABLE 待修改的表名 DROP 待删除的字段名    

　　在语句中需要指定要修改的表的表名以及要删除字段的名称。下面是删除 T_Person 表 中年龄字段的 SQL 语句：     ALTER TABLET_Person DROP FAge    

　　注意：DB2 中不能删除字段，所以这个 SQL 语句在 DB2 中是无法正确执行的。

1.7 删除数据表    

　　当一个数据表不再有用的时候我们就可以将其删除，使用 DROP TABLE 语句就可以完 成这个功能，DROP TABLE 语句的语法如下： DROP TABLE 要删除的表名    

　　可以看到 DROP TABLE 语句语法非常简单，只要指定要删除的表名就可以了。执行下 面的 SQL 就可以将 T_Person 表删除了： DROP TABLE T_Person    

　　需要注意的是，如果在表之间创建了外键关联关系，那么在删除被引用数据表的时候会 删除失败，因为这样会导致关联关系被破坏，所以必须首先删除引用表，然后才能删除被引 用表。比如 A 表创建了指向 B 表的外键关联关系，那么必须首先删除 A 表后才能删除 B 表。

1.8 受限操作的变通解决方案    

　　 各个数据库系统中提供的修改表结构的方法是不同的，有的提供了修改表名、修改字段 类型、修改字段名称等操作的 SQL 语句，而有的则没有提供这些功能，甚至有的数据库系 统连删除字段的功能都不支持。但是这些操作有的时候又是必要的，那么有没有变通的手段 来实现这些功能呢？答案是有！    

　　 在 DB2 中如果要在表 T 中删除一个字段 F1，那么可以首先创建一个表 T1，这个表 T1 的结构和表 T 结构一致，唯一区别就是缺少字段 F1；接着将表 T 中的数据导出到 T1 中， 然后将表 T 删除；最后将表 T1 重命名为 T 就可以了。这样就可以达到修改表名的效果了。    

　　在不支持修改字段名称操作的数据库系统上同样可以采用类似策略来解决。比如我们要 将表 T 的 F1 字段重命名为 F2，那么首先在表 T 上创建新字段 F2，类型和 F1 一致，然后将 F1 的数据复制到 F2 上，最后将字段 F1 删除就可以了。这样就可以达到修改字段名称的效 果了。