MySQL操作表

一. 创建表

1. 创建表的语法格式

语法：

create table table_name (
	field1 datatype,
    field2 datatype,
    field3 databyte,
) character set 字符集 collate 校验规则 engine 存储引擎;

说明：

• field 表示列名
• datatype 表示列的类型
• character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准
• collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准

其中，datatype所代表的各种类型不是这一节中所侧重的。在实际的创建表中，可以不指定校验规则、字符集以及存储引擎，数据库会有默认的字符集和校验规则。

2.创建表的案例

案例一：

create  table users (
     id int,
     name varchar(20) comment '用户名',
     password char(32) comment '用户的密码',
     birthday date comment '用户的生日'
) character set utf8 collate utf8_general_ci engine MyISAM;

说明：

1. comment是变量的含义，可写可不写。
2. set collate engine选项可写可不写。
3. 不同的存储引擎，创建表的文件不一样。users 表存储引擎是 MyISAM ，在数据目中有三个不同的文件，分别是：
   • users.frm：表结构
   • users.MYD：表数据
   • users.MYI：表索引

案例二：

create  table user2 (
     id int,
     name varchar(20) comment '用户名',
     password char(32) comment '用户的密码',
     birthday date comment '用户的生日'
) charset=utf8 collate=utf8_general_ci engine=InnoDB;

通过右侧的现象发现，案例一和案例二的表结构不同，users是三种表结构，user2则是两种表结构，这是由于二者用的是不同的存储引擎。这与后续的索引是有关系的。但由此可见，不同的存储引擎，对存储文件的个数和要求是有区别的。

后面会详细的介绍：MyIsam的MYD、MYI是将数据和索引分开存的；InnoDB的ibd是将数据和索引放在一起存的。此外，可以发现，创建表和创建数据库是类似的，同样可以加上if not exists。

二.查看表结构

首先确定是在哪个数据库中：

• 查看有哪些表：

show tables;

• 查看表的详细信息：

desc 表名

• 查看创建表时的详细信息：

show create table 表名;

用 \G替换;可以格式化把不需要的格式全部去掉：

这个结果和我们自己创建表时的结果多少有些不同，比如最后的字符集、校验规则、存储引擎的顺序以及大小写。在sql服务器内部，会对我们下达的sql指令进行词法语法分析，纠正我们的指令编码，使其标准化。

三.修改表结构

在项目实际开发中，经常修改某个表的结构，比如字段名字，字段大小，字段类型，表的字符集类型，表的存储引擎等等。我们还有需求，添加字段，删除字段等等。这时我们就需要修改表。

修改表名：将user1表名改为user

alter table user1 rename to user;

在演示更改内部属性之前，需要先插入一定的数据：

1. 添加字段

alter table user add image_path varchar(128) comment '这个是用户的头像路径' after birthday;

插入新字段后，对原来表中的数据没有影响：

分析此语句的组成：

• alter table 表名：表示修改的表。
• add ：表示修改的方式为添加字段，后面为对应的字段名image_path 以及字段类型varchar(128)
• comment： 表示字段名的含义，也就是相当于注释。
• after：after后的birthday表示新增的字段在birthday字段后。

由于有两条数据，因此在新增字段时，结果会显示：2 row in set。

2. 修改字段

比如想将name对应的类型属性的长度由20改成60：

alter table user modify name varchar(60);

但需要注意的是，我们之前的name有对应的解释，即comment后的文字叙述，即用户的名字，但此时修改之后，就不再显示：

这说明是把新的字段信息全部覆盖之前的字段信息，而不是单一的修改。

3. 删除字段

先看看之前的表结构：

如果password字段不想要了，那么通过如下语句就可将其删掉，即删除password列：

alter table user drop password;

4. 表的重命名

要把user改名为User，执行如下语句:

alter table user rename to User;

注：to可以省略。那么通过省略to的形式再改一次表名：

5. 表中列（字段）的重命名

要想把表中的name字段重命名为xingming，执行如下语句：

alter table users change name xingming varchar(60) DEFAULT NULL;

其中，属性不能省略。

四.删除表

想要删除表users，执行如下语句：

drop table users;

五.总结操作表

我们这一节所讲的是操作表的结构，而不是操作标表的内容。像插入数据insert，以及查看数据select，这些都是对表的内容所进行的操作。我们所学习SQL语言的种类有DDL，DCL，DML，像我们目前所学的数据库的操作及其表的操作都属于DDL，因为这些操作都属于定义表的结构。

那么对于三.修改表结构 实际上的添加、修改、删除、重命名都有对应的动作标志，即对应的词控制，除此之外的语法结构基本相同。

1. 添加字段：add
2. 修改字段：modify
3. 删除字段：drop
4. 表的重命名：rename
5. 表中列的的重命名：change

最后，需要注意的是，在以后的开发工作中，最好不要进行表的结构的修改、重命名以及删除，否则语言层面的操作会非常繁琐，所以一定要事先确定好表的结构及相应的字段命名。