MYSQL学习笔记五（表操作）

一、创建表（CREATE TABLE语句）

1.1 创建表语法

使用 CREATE TABLE 语句的语法格式为：

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];
其中，[表定义选项]的格式为：  <列名1> <类型1> [,…] <列名n> <类型n>

CREATE TABLE 语句的主要语法及使用说明如下：
CREATE TABLE：用于创建给定名称的表，必须拥有表CREATE的权限。
<表名>：指定要创建表的名称，在 CREATE TABLE 之后给出，必须符合标识符命名规则。
<表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition）以及可能的空值说明、完整性约束或表索引组成。

1.2 创建表Demo

这里创建一个表名为testTable的数据库表，该表包含4个字段（id，NAME,deptId，salary），其数据类型分别是：INT(11)，VARCHAR(5),INT（11），FLOAT。其详细命令如下：

CREATE TABLE IF NOT EXISTS testTable
(
  id INT(11),
  NAME VARCHAR(25),
  deptId INT(11),
  salary FLOAT
);

在表上鼠标点击右键，弹出菜单中选择改变表，就可以查看表的详细的结构信息。

二、查看表定义

查看表的定义有四种方法，具体如下：

方法一

show create table table_name;    ##可以用这个看表的代码。

方法二

show full columns from table_name;  ##展示指定表的所有列

方法三

show columns from table_name;  ##展示指定表的所有列

方法四

describe table_name;   ##查看表定义 

三、表结构的更新

3.1 基本语法

在MySQL中使用 ALTER TABLE 语句修改表。其具体语法结构如下：

ALTER TABLE <表名> [修改选项]
{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名> }

3.2 添加字段

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

eg；

ALTER TABLE testtable      ##testtable为表明
ADD COLUMN col1 INT FIRST     ##   col1 为列名      FIRST插入列的顺序

注意：在上述案例中，是将新添加的列添加到第一列，采用的是First。如果需要新添加一列，列名为test，并将test列添加到name列之后，脚本语句的写法如下：

ALTER TABLE tb_emp1
ADD COLUMN test INT AFTER name;

3.3 修改字段类型

修改字段时，一般指的是修改表的字段的数据类型。其语法规则如下：

ALTER TABLE <表名> MODIFY <字段名> <数据类型>     
##表名指的是修改数据类型的字段所在表的名称，字段名指需要修改的字段，数据类型指修改后字段的新数据类型。

eg：
修改表 testtable的结构，将 name 字段的数据类型由 VARCHAR(25) 修改成 VARCHAR(30)，其语句如下：

ALTER TABLE testtable
MODIFY name VARCHAR(30);

命令执行完成之后，可以查看表定义情况（如下图），可以看到数据类型已经修改成功：

3.4 删除字段

删除字段就是指将数据表中冗余的字段从表中移除，语法格式如下：

ALTER TABLE <表名> DROP <字段名>；
##字段名指需要从表中删除的字段的名称。

eg：
修改表 testtable的结构，删除 col1 字段，其详细的语法如下：

ALTER TABLE tb_emp1
DROP col1;

以上命令运行完成之后，直接可以看看表定义（如下图），可以看到col1的列已经不存在了。

3.5 修改字段名称

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

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；
##旧字段名指修改前的字段名；
##新字段名指修改后的字段名；
##新数据类型指修改后的数据类型，
##如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。

eg：
修改表 testtable的结构，将 name 字段名称改为 fullname，同时将数据类型变为 CHAR(30)。详细语句如下：

ALTER TABLE testtable
CHANGE name  fullname CHAR(30);

以上命令运行完成之后，直接可以看看表定义（如下图），可以看到name的列已经变成fullname了。

注意：由于不同类型的数据在机器中的存储方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录，因此，当数据表中已经有数据时，不要轻易修改数据类型。

3.6 修改表名

MySQL 实现表名的修改，语法规则如下：

ALTER TABLE <旧表名> RENAME [TO] <新表名>；
TO 为可选参数，使用与否均不影响结果。

eg：
将数据表 testtable改名为 testtable2，其sql语句如下：

ALTER TABLE testtable
RENAME TO testtable2;

执行完上述命令之后，可以使用F5的方法刷新左侧数据库，其结果如下图。

四、表删除

4.1 表删除语法

在MYSQL中删除表需要使用 DROP TABLE 语句来完成，语法格式如下：

DROP TABLE [IF EXISTS] <表名> [ , <表名1> , <表名2>] …

<表名>：被删除的表名。
注意事项：
DROP TABLE 语句可以同时删除多个表，用户必须拥有该命令的权限。
表被删除时，所有的表数据和表定义会被取消，所以使用本语句要小心。
表被删除时，用户在该表上的权限并不会自动被删除。
参数IF EXISTS用于在删除前判断删除的表是否存在，加上该参数后，在删除表的时候，如果表不存在，SQL 语句可以顺利执行，但会发出警告（warning）。

4.2 表删除Demo

删除数据表 testtable2，详细语句如下：

DROP TABLE testtable2;

执行结果可以看到，firstdb数据库的testtable2表已经不存在删除操作成功。