MySQL数据表的基本操作

一、数据表的创建

        MySQL数据表的创建语法：

CREATE TABLE table_name (

        column1 datatype [optional_parameters],

        column2 datatype [optional_parameters],

        ……

        columnN datatype [optional_parameters],

);

        其中，datatype是数据类型。可选的参数可以为以下类型：

• NULL/NOT NULL:指定列是否为空。
• DEFAULT:指定列的默认值。
• AUTO_INCREMENT:指定列为自增长列。
• PRIMARY KEY：指定表为主键，一个表只能有一个主键。
• UNIQUE:指定列不能有重复值。
• FOREING KEY:指定列为外键。

注意：使用 CREATE TABLE 创建表时，必须指定以下信息：

• 要创建的表的名称不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。
• 数据表中每个列（字段）的名称和数据类型，如果创建多个列，要用逗号隔开。

二、数据表的修改

        MySQL数据表的修改语法如下：

ALTER TABLE <表名> [修改选项]

# 修改选项可以为

• ADD COLUMN <列名> <类型>
• CHANGE COLUMN <旧列名> <新列名> <新列类型>
• ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
• MODIFY COLUMN <列名> <类型>
• DROP COLUMN <列名>
• RENAME TO <新表名>
• CHARACTER SET <字符集名>
• COLLATE <校对规则名> 

        具体如下：

#修改表名(TO 为可选参数，使用与否均不影响结果)
 ALTER TABLE old_table_name RENAME [TO] new_table_name；

#修改表字符集( 将数据表 students 的字符集修改为 gb2312，校对规则修改为 gb2312_chinese_ci)
ALTER TABLE students CHARACTER SET gb2312  DEFAULT COLLATE gb2312_chinese_ci;

ADD COLUMN new_column_name datatype [optional_parameters],
MODIFY COLUMN column_name datatype [optional_parameters],
CHANGE COLUMN old_column_name new_column_name datatype [optional_parameters],
DROP COLUMN column_name;

三、数据表的修改/删除

修改字段名称

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

        新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。

        CHANGE 也可以只修改数据类型，实现和 MODIFY 同样的效果，方法是将 SQL 语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。

修改字段数据类型

ALTER TABLE <表名> MODIFY <字段名> <数据类型>；

删除字段

ALTER TABLE <表名> DROP <字段名>；

四、删除数据表

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]；

• DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。
• IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（warning）。

两点注意：

• 用户必须拥有执行 DROP TABLE 命令的权限，否则数据表不会被删除。
• 表被删除时，用户在该表上的权限不会自动删除。

五、数据表添加字段

        MySQL 允许在开头、中间和结尾处添加字段。

在末尾添加字段

ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];

        这种语法格式默认在表的最后位置（最后一列的后面）添加新字段。

在开头添加字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;

在中间添加字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;

六、外键的创建和主表的删除

        数据表之间经常存在外键关联的情况，这时如果直接删除父表，会破坏数据表的完整性，也会删除失败。删除父表有以下两种方法：

• 先删除与它关联的子表，再删除父表；但是这样会同时删除两个表中的数据。
• 将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。

例子：已创建表 xinxi_1，以该表为父表，关联xinix_2。

CREATE TABLE xinxi_2
        (id INT(11) PRIMARY KEY,
        name VARCHAR(25),
        deptId INT(11),
        CONSTRAINT xinxi_1_2 FOREIGN KEY (deptId) REFERENCES xinxi_1);

当主表在存在外键约束时，不能被直接删除。下面解除子表 xinxi_2 的外键约束，

ALTER TABLE xinxi_2 DROP FOREIGN KEY xinxi_1_2;

解除关联关系后，可以使用 DROP TABLE 语句直接删除父表 xinxi_2，SQL 语句如下：

DROP TABLE xinxi_2;

七、查看表结构

        在 MYSQL 中，可以使用 DESCRIBE 和 SHOW CREATE TABLE 命令来查看数据表的结构。

DESCRIBE <表名>; 或简写成：DESC <表名>;

SHOW CREATE TABLE <表名>;

在 SHOW CREATE TABLE 语句的结尾处（分号前面）添加\g或者\G参数可以改变展示形式。