3.1、创建数据表

创建数据表指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性(实体完整性、引用完整性、域完整性)约束的过程。

创建表的语法格式

数据表属于数据库，在创建数据表之前，应该先创建数据库，指定在哪个数据库中进行。

mysql>USEtest;Databasechangedmysql>CREATETABLEtb_name(字段名1,数据类型[列级别约束条件][默认值],字段名2,数据类型[列级别约束条件][默认值],...[表级别约束条件]);

例如创建员工表tb_emp1，表结构为：

mysql>USEtest;Databasechangedmysql>CREATETABLEtb_emp1->(->idINT(11),->nameVARCHAR(25),->depIdINT(11),->salaryFLOAT->);QueryOK,0rowsaffected(0.08sec)mysql>SHOWTABLES;+----------------+|Tables_in_test|+----------------+|tb_emp1|+----------------+1rowinset(0.00sec)

使用主键约束

主键，又称主码，是表中一列或多列的组合。主键约束要求主键列的数据唯一，并且不允许为空。主键能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据查询的速度。主键分为单字段主键和多字段联合主键。

单字段主键由一个字段组成，其语法格式有两种：

字段名数据类型PRIMARYKEY[默认值]mysql>CREATETABLEtb_emp2->(->idINT(11)PRIMARYKEY,->nameVARCHAR(25),->depIdINT(11),->salaryFLOAT->);

[CONSTRAINT<约束名>]PRIMARYKEY[字段名]mysql>CREATETABLEtb_emp3->(->idINT(11),->nameVARCHAR(25),->depIdINT(11),->salaryFLOAT,->PRIMARYKEY(id)->);

多字段联合主键，即主键由多个字段组合，其语法格式为：

PRIMARYKEY[字段1,字段2,字段3,...]mysql>CREATETABLEtb_emp4->(->nameVARCHAR(25),->depIdINT(11),->salaryFLOAT->PRIMARYKEY(name,depId)->);

使用外键约束

外键用来在两个表的数据之间建立链接，它可以是一列或多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空，当不为空时，则每一个外键值必须等于另一个表中主键的某个值。

外键：它是表中的一个字段，可以不是本表的主键，但对应另一个表的主键。外键主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。例如：部门表tb_dept的主键是id，在员工表tb_emp5中有一个键depId与这个id关联。

主表：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。

从表：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

创建外键的语法规则：

[CONSTRAINT<外键名>]FOREIGNKEY字段名1[,字段名2,...]REFERENCES<主表名>主键列1[,主键列2,...]

'外键名'为定义的外键约束的名称，一个表中不能有相同的外键；'字段名'表示字表需要添加外键约束的字段列；'主表名'即被子表外键所依赖的表的名称；'主键列'表示主表中定义的主键列。

例如：定义数据表tb_emp5，并在tb_emp5上创建外键约束。

创建一个部门表：

mysql>CREATETABLEtb_dept1(->idINT(11)PRIMARYKEY,->nameVARCHAR(22)NOTNULL,->locationVARCHAR(50)->);QueryOK,0rowsaffected(0.02sec)

定义数据表tb_emp5，让它的键depId作为外键关联到tb_dept1的主键id：

mysql>CREATETABLEtb_emp5(->idINT(11)PRIMARYKEY,->nameVARCHAR(25),->deptIdINT(11),->salaryFLOAT,->CONSTRAINTfk_emp_dept1FOREIGNKEY(deptId)REFERENCEStb_dept1(id)->);QueryOK,0rowsaffected(0.05sec)

使用非空约束

非空约束指字段的值不能为空。对于使用非空约束的字段，如果在添加数据时没有指定值，数据库会报错。

非空约束的语法规则：

字段名数据类型NOTNULLLmysql>CREATETABLEtb_emp6(->idINT(11)PRIMARYKEY,->nameVARCHAR(25)NOTNULL,->depIdINT(11),->salaryFLOAT->);QueryOK,0rowsaffected(0.03sec)

使用唯一性约束

唯一性约束要求该列唯一，允许为空，但只能出现一个空值。唯一性约束可以确保一列或几列不出现重复值。

唯一性约束的语法规则有：

1、定义完列之直接指定唯一约束

字段名数据类型UNIQUEmysql>CREATETABLEtb_dept2(->idINT(11)PRIMARYKEY,->nameVARCHAR(22)UNIQUE,->locationVARCHAR(50)->);QueryOK,0rowsaffected(0.20sec)

2、定义完所有列之后指定唯一约束

[CONSTRAINT<约束名>]UNIQUE(<字段名>)mysql>CREATETABLEtb_dept3(->idINT(11)PRIMARYKEY,->nameVARCHAR(22),->locationVARCHAR(50),->CONSTRAINTSTHUNIQUE(name)->);QueryOK,0rowsaffected(0.03sec)

UNIQUE和PRIMARY KEY的区别：一个表可以有多个字段声明为UNIQUE，但是只能有一个PRIMARY KEY声明，声明为PRIMARY KEY的列不允许有空值，但是声明为UNIQUE的字段允许空值(NULL)的存在。

使用默认约束

默认约束指某列的默认值。其语法规则为：

字段名数据类型DEFAULT默认值mysql>CREATETABLEtb_emp7(->idINT(11)PRIMARYKEY,->nameVARCHAR(25)NOTNULL,->depIdINT(11)DEFAULT1111,->salaryFLOAT->);QueryOK,0rowsaffected(0.03sec)

设置表的属性值自动增加

在数据库应用中，经常希望在每次插入新纪录时，系统自动生成字段的主键值。可以通过为表主键添加AUTO_INCREMENT 关键字来实现。其语法格式为：

字段名数据类型AUTO_INCREMENTmysql>CREATETABLEtb_emp8(->idINT(11)PRIMARYKEYAUTO_INCREMENT,->nameVARCHAR(25)NOTNULL,->depIdINT(11),->salaryFLOAT->);QueryOK,0rowsaffected(0.03sec)mysql>INSERTINTOtb_emp8(name,salary)->VALUES('Lucy',1000),('Lura',2000),('Kevin',3000);QueryOK,3rowsaffected(0.02sec)Records:3Duplicates:0Warnings:0mysql>SELECT*FROMtb_emp8;+----+-------+--------+--------+|id|name|deptId|salary|+----+-------+--------+--------+|1|Lucy|NULL|1000||2|Lura|NULL|2000||3|Kevin|NULL|3000|+----+-------+--------+--------+3rowsinset(0.00sec)

查看数据表结构

使用SQL语句创建好数据表之后，可以查看表结构的定义，以确定表的定义是否正确。在MySQL中，查看表结构可以使用DESCRIBE和SHOW CREATE TABLE语句。

mysql>DESCRIBEtb_dept1;+----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(22)|NO||NULL|||location|varchar(50)|YES||NULL||+----------+-------------+------+-----+---------+-------+3rowsinset(0.00sec)mysql>SHOWCREATETABLEtb_emp1\G***************************1.row***************************Table:tb_emp1CreateTable:CREATETABLE`tb_emp1`(`id`int(11)DEFAULTNULL,`name`varchar(25)DEFAULTNULL,`depId`int(11)DEFAULTNULL,`salary`floatDEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf81rowinset(0.00sec)

3.2、修改数据表

修改数据表指的是修改数据库中已经存在的数据表的结构。MySQL使用ALTER TABLE 语句修改表。常用的修改表的操作有：修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列顺序、更改表的存储引擎、删除表的外键约束等。

修改表名

修改表名的语法规则为：

ALTERTABLE<旧表名>RENAME[TO]<新表名>mysql>SHOWTABLES;+----------------+|Tables_in_test|+----------------+|tb_dept1||tb_dept2||tb_dept3||tb_emp1||tb_emp2||tb_emp5||tb_emp6||tb_emp8|+----------------+8rowsinset(0.00sec)mysql>ALTERTABLEtb_dept3RENAMETOtb_deptment3;QueryOK,0rowsaffected(0.04sec)mysql>SHOWTABLES;+----------------+|Tables_in_test|+----------------+|tb_dept1||tb_dept2||tb_deptment3||tb_emp1||tb_emp2||tb_emp5||tb_emp6||tb_emp8|+----------------+8rowsinset(0.00sec)

修改字段的数据类型

修改字段的数据类型，就是把字段的数据类型转换成另一种数据类型，其语法规则为：

ALTERTABLE<表名>MODIFY<字段名><数据类型>mysql>DESCtb_dept1;+----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(22)|NO||NULL|||location|varchar(50)|YES||NULL||+----------+-------------+------+-----+---------+-------+3rowsinset(0.00sec)mysql>ALTERTABLEtb_dept1MODIFYnameVARCHAR(33);QueryOK,0rowsaffected(0.04sec)Records:0Duplicates:0Warnings:0mysql>DESCtb_dept1;+----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(33)|YES||NULL|||location|varchar(50)|YES||NULL||+----------+-------------+------+-----+---------+-------+3rowsinset(0.00sec)

修改字段名

MySQL中修改表字段名的语法规则为：

ALTERTABLE<表名>CHANGE<旧字段名><新字段名><新数据类型>mysql>DESCtb_dept1;+----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(33)|YES||NULL|||location|varchar(50)|YES||NULL||+----------+-------------+------+-----+---------+-------+3rowsinset(0.00sec)mysql>ALTERTABLEtb_dept1CHANGElocationlocVARCHAR(50);QueryOK,0rowsaffected(0.13sec)Records:0Duplicates:0Warnings:0mysql>DESCtb_dept1;+-------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(33)|YES||NULL|||loc|varchar(50)|YES||NULL||+-------+-------------+------+-----+---------+-------+3rowsinset(0.00sec)

添加字段

添加字段的语法格式为：

ALTERTABLE<表名>ADD<新字段><数据类型>[约束条件][FIRST|AFTER已存在字段名]

添加无完整约束条件的字段

mysql>ALTERTABLEtb_dept1ADDmanagerIdINT(10);QueryOK,0rowsaffected(0.05sec)Records:0Duplicates:0Warnings:0mysql>DESCtb_dept1;+-----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(33)|YES||NULL|||loc|varchar(50)|YES||NULL|||managerId|int(10)|YES||NULL||+-----------+-------------+------+-----+---------+-------+4rowsinset(0.00sec)

添加有完整约束条件的字段

mysql>ALTERTABLEtb_dept1ADDcolumn1VARCHAR(12)NOTNULL;QueryOK,0rowsaffected(0.04sec)Records:0Duplicates:0Warnings:0mysql>DESCtb_dept1;+-----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(33)|YES||NULL|||loc|varchar(50)|YES||NULL|||managerId|int(10)|YES||NULL|||column1|varchar(12)|NO||NULL||+-----------+-------------+------+-----+---------+-------+5rowsinset(0.00sec)

在表的第一列添加字段

mysql>ALTERTABLEtb_dept1ADDcolumn2INT(11)FIRST;QueryOK,0rowsaffected(0.04sec)Records:0Duplicates:0Warnings:0mysql>DESCtb_dept1;+-----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+-------------+------+-----+---------+-------+|column2|int(11)|YES||NULL|||id|int(11)|NO|PRI|NULL|||name|varchar(33)|YES||NULL|||loc|varchar(50)|YES||NULL|||managerId|int(10)|YES||NULL|||column1|varchar(12)|NO||NULL||+-----------+-------------+------+-----+---------+-------+6rowsinset(0.00sec)

在表的指定列之后添加字段

mysql>ALTERTABLEtb_dept1ADDcolumn3INT(11)AFTERname;QueryOK,0rowsaffected(0.05sec)Records:0Duplicates:0Warnings:0mysql>DESCtb_dept1;+-----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+-------------+------+-----+---------+-------+|column2|int(11)|YES||NULL|||id|int(11)|NO|PRI|NULL|||name|varchar(33)|YES||NULL|||column3|int(11)|YES||NULL|||loc|varchar(50)|YES||NULL|||managerId|int(10)|YES||NULL|||column1|varchar(12)|NO||NULL||+-----------+-------------+------+-----+---------+-------+7rowsinset(0.00sec)

删除字段

删除字段是将数据表中的某个字段从表中移除，其语法格式为：

ALTERTABLE<表名>DROP<字段名>mysql>DESCtb_dept1;+-----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+-------------+------+-----+---------+-------+|column2|int(11)|YES||NULL|||id|int(11)|NO|PRI|NULL|||name|varchar(33)|YES||NULL|||column3|int(11)|YES||NULL|||loc|varchar(50)|YES||NULL|||managerId|int(10)|YES||NULL|||column1|varchar(12)|NO||NULL||+-----------+-------------+------+-----+---------+-------+7rowsinset(0.00sec)mysql>ALTERTABLEtb_dept1DROPcolumn2;QueryOK,0rowsaffected(0.04sec)Records:0Duplicates:0Warnings:0mysql>DESCtb_dept1;+-----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(33)|YES||NULL|||column3|int(11)|YES||NULL|||loc|varchar(50)|YES||NULL|||managerId|int(10)|YES||NULL|||column1|varchar(12)|NO||NULL||+-----------+-------------+------+-----+---------+-------+6rowsinset(0.00sec)

修改字段的排列位置

对一个数据表来说，在创建的时候，字段在表中的排列顺序已经确定。但是表的结构并不是完全不能改变，可以通过ALTER TABLE来改变表中字段的相对位置，语法格式为：

ALTERTABLE<表名>MODIFY<字段1><数据类型>FIRST|AFTER<字段2>

修改字段为表的第一个字段

mysql>ALTERTABLEtb_dept1MODIFYcolumn1VARCHAR(12)FIRST;QueryOK,0rowsaffected(0.04sec)Records:0Duplicates:0Warnings:0mysql>DESCtb_dept1;+-----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+-------------+------+-----+---------+-------+|column1|varchar(12)|YES||NULL|||id|int(11)|NO|PRI|NULL|||name|varchar(33)|YES||NULL|||column3|int(11)|YES||NULL|||loc|varchar(50)|YES||NULL|||managerId|int(10)|YES||NULL||+-----------+-------------+------+-----+---------+-------+6rowsinset(0.00sec)

修改字段到表的指定列之后

mysql>ALTERTABLEtb_dept1MODIFYcolumn1VARCHAR(12)AFTERloc;QueryOK,0rowsaffected(0.04sec)Records:0Duplicates:0Warnings:0mysql>DESCtb_dept1;+-----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(33)|YES||NULL|||column3|int(11)|YES||NULL|||loc|varchar(50)|YES||NULL|||column1|varchar(12)|YES||NULL|||managerId|int(10)|YES||NULL||+-----------+-------------+------+-----+---------+-------+6rowsinset(0.00sec)

更改表的存储引擎

更改表的数据引擎语法格式为：

ALTERTABLE<表名>ENGINE=<更改后的存储引擎>mysql>SHOWCREATETABLEtb_deptment3\G***************************1.row***************************Table:tb_deptment3CreateTable:CREATETABLE`tb_deptment3`(`id`int(11)NOTNULL,`name`varchar(22)DEFAULTNULL,`location`varchar(50)DEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`STH`(`name`))ENGINE=InnoDBDEFAULTCHARSET=utf81rowinset(0.00sec)mysql>ALTERTABLEtb_deptment3ENGINE=MyISAM;QueryOK,0rowsaffected(0.06sec)Records:0Duplicates:0Warnings:0mysql>SHOWCREATETABLEtb_deptment3\G***************************1.row***************************Table:tb_deptment3CreateTable:CREATETABLE`tb_deptment3`(`id`int(11)NOTNULL,`name`varchar(22)DEFAULTNULL,`location`varchar(50)DEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`STH`(`name`))ENGINE=MyISAMDEFAULTCHARSET=utf81rowinset(0.00sec)mysql>

删除表的外键约束

对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表之间的关联关系，MySQL中删除外键的语法格式为：

ALTERTABLE<表名>FROPFOREIGNKEY<外键约束名>

mysql>CREATETABLEtb_emp9#创建带有外键约束的表->(->idINT(11)PRIMARYKEY,->nameVARCHAR(25),->deptIdINT(11),->salaryFLOAT,->CONSTRAINTfk_emp_deptFOREIGNKEY(deptId)REFERENCEStb_dept1(id)->);QueryOK,0rowsaffected(0.03sec)mysql>SHOWCREATETABLEtb_emp9\G***************************1.row***************************Table:tb_emp9CreateTable:CREATETABLE`tb_emp9`(`id`int(11)NOTNULL,`name`varchar(25)DEFAULTNULL,`deptId`int(11)DEFAULTNULL,`salary`floatDEFAULTNULL,PRIMARYKEY(`id`),KEY`fk_emp_dept`(`deptId`),CONSTRAINT`fk_emp_dept`FOREIGNKEY(`deptId`)REFERENCES`tb_dept1`(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf81rowinset(0.00sec)mysql>ALTERTABLEtb_emp9DROPFOREIGNKEYfk_emp_dept;QueryOK,0rowsaffected(0.04sec)Records:0Duplicates:0Warnings:0mysql>SHOWCREATETABLEtb_emp9\G***************************1.row***************************Table:tb_emp9CreateTable:CREATETABLE`tb_emp9`(`id`int(11)NOTNULL,`name`varchar(25)DEFAULTNULL,`deptId`int(11)DEFAULTNULL,`salary`floatDEFAULTNULL,PRIMARYKEY(`id`),KEY`fk_emp_dept`(`deptId`))ENGINE=InnoDBDEFAULTCHARSET=utf81rowinset(0.00sec)

3.3、删除数据表

删除没有被关联的表

在MySQL中，使用DROP TABLE 可以一次删除一个或多个没有被其他表关联的数据表，语法格式为：

DROPTABLE[IFEXISTS]表1，表2，...表nmysql>DROPTABLEIFEXISTStb_dept2;QueryOK,0rowsaffected(0.04sec)mysql>SHOWTABLES;+----------------+|Tables_in_test|+----------------+|tb_dept1||tb_deptment3||tb_emp1||tb_emp2||tb_emp5||tb_emp6||tb_emp8||tb_emp9|+----------------+8rowsinset(0.00sec)

删除被其他表关联的主表

数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败。原因是直接删除，将破坏表的参照完整性。如果必须删除，可以先删除外键约束，再删除父表。

mysql>CREATETABLEtb_dept2(idINT(11)PRIMARYKEY,nameVARCHAR(25),locationVARCHAR(50));#创建父表QueryOK,0rowsaffected(0.03sec)mysql>CREATETABLEtb_emp(idINT(11)PRIMARYKEY,nameVARCHAR(25),deptIdINT(11),salaryFLOAT,CONSTRAINTfk_emp_deptFOREIGNKEY(deptId)REFERENCEStb_dept2(id));#创建子表，外键约束QueryOK,0rowsaffected(0.03sec)mysql>DROPTABLEtb_dept2;#无法父表删除ERROR1217(23000):Cannotdeleteorupdateaparentrow:aforeignkeyconstraintfailsmysql>ALTERTABLEtb_empDROPFOREIGNKEYfk_emp_dept;#删除子表外键QueryOK,0rowsaffected(0.10sec)Records:0Duplicates:0Warnings:0mysql>DROPTABLEtb_dept2;#成功删除父表QueryOK,0rowsaffected(0.02sec)