细说MySQL表操作

要想在某一个数据库里创建表，首先要使用（进入）这个数据库,使用命令 use 数据名

如果是通过Windows控制台创建表，则一定注意，将控制台的编码设置为gbk(set names gbk),因为控制台就只有一种编码，那就是gbk。如果不设置会出错。虽然这里设置的编码是gbk,这里设置的是控制台的编码，不是数据库的编码也不是表的编码。数据库管理系统会自动将表的格式转换为创建表时设置的格式。

如果是通过MacOS的终端创建表，则需要设置编码的编码和数据库的字符编码保持一致。

语法

CREATE TABLE 表名 (
字段名1  数据类型  字段选项 ,
字段名2  数据类型  字段选项 ,
字段名3  数据类型   字段选项,
字段名4  数据类型   字段选项,
字段名n  数据类型 (这里千万不能再有逗号，否则会报错)
)CHARACTER SET 字符集 COLLATE 校对规则 ENGINE 存储引擎方式;

（字段选项、字符集、校对规则、存储引擎等都可以不写）

字段名: 自定义的名称，来告诉表数据表头是什么

字段类型: mysql数据类型分三大类，数值型，字符串型，日期时间型

字段选项: 每个字段有不同的功能，可以添加不同选项来特殊化字段

主键: primary key，作用：加上主键后数据不能重复，查询时主要用这个字段

ENGINE: 数据库表的存储引擎(PHP程序员需要掌握MyISAM、InnoDB、Memory),目前我们使用最多的是MyISMAM和InnoDB,默认是InnoDB.该值在my.ini中设置

说明:

(1) 表也可以指定字符集

(2) 如果表指定了字符集和校对规则，则以表指定的为准，如果表没有指定，则以表所在的数据库的字符集和校对规则。

(3) engin 就是存储引擎，比如常用的(MyISAM、InnoDB, Memory), 如果创建表的时指定了这个存储引擎，则以这个准，如果没有指定以默认的. 在my.ini中设置的有

(4) AUTO_INCREMENT,加上这个数据就会自动增长

示例:

CREATE TABLE `user`(    `id` int unsigned AUTO_INCREMENT PRIMARY KEY COMMENT "主键ID",    `name` varchar(64) NOT NULL COMMENT "姓名",    `nickname` varchar(64) NOT NULL DEFAULT "" COMMENT "昵称",    `password` char(32) NOT NULL COMMENT "密码",    `birthday` date COMMENT "出生日期")CHARSET=UTF8 COLLATE=utf8_general_ci ENGINE=InnoDB;

查看表结构

desc 表名;describe 表名;

查看所有的表

show tables;

删除表

drop table 表名;

该命令一旦执行，那么这个表连同表中的数据都会被删掉，虽然有恢复数据的方法，但是很困难，除非你想删库跑路，否则，在遇到在删除数据或者数据库的时候一定要谨慎操作，除非真的有必要。

查看创建表的语句

show create table 表名;

修改表的字符集和校验规则

ALTER  TABLE  表名 CONVERT TO CHARACTER SET 字符集 COLLATE 校对规则;

修改表名

rename table 旧表名 to 新表名;

在表中添加新字段

alter table 表名 add  新字段名 字段类型 [字段属性列表];

原本abc表中，只有两个字段，新增一个hobby字段

在指定位置后面添加新的字段

alter  table  表名  add  新字段  字段类型[字段属性列表]   after  指定位置的字段名;

在name后面添加一个age字段

在表的最前面添加新的字段

alter  table  表名  add  新字段  字段类型[字段属性列表]  first;

注意:使用first只能是在表的最前面添加字段，first后面不要再接字段名；

修改表的字段名

alter table 表名 change  旧字段名 新字段名 新字段类型 [新字段属性列表]；

注意: 不能修改主键的名字, 修改字段名的时候一定要给新字段名添加属性设置，并且设置的字段类型的存储范围要大于等于存储的数据，否则保护机制会提醒，更改后原有的数据无法存下，比如原有的字段名类型为varchar(4),存储了一个数据为'1234'，如果改为char(2),就无法存下'1234'了，这样是不允许的。

而且修改字段名的时候就必须声明字段类型，即使是字段类型和原来的字段类型一样也要重新声明。

下图中把hobby字段改成了love

修改表中字段的属性

alter table 表名 modify  字段名 新字段类型 [新字段属性列表]；

这个属性是指字段的数据类型int ,decimal等

删除表中的某个字段

alter table 表名 drop 字段名;

设置某个字段为主键

alter table 表名 add primary key(要设置为主键的字段名);

把某个字段的主键属性删掉

alter table 表名 drop primary key;

注意:如果把某个字段设置为了主键，并且该字段是auto_increment，那么我们将无法把他的主键属性删除掉，如果我们想删除这个带auto_increment的属性的主键属性，那么就必须直接把这个字段删除掉（用alter table 表名 drop 字段名；来删除），如果我们还需要这个字段，只是不需要它成为主键了，我们可以再通过增加字段的方法再添上该字段，由于一个表中只能有一个主键(这个主键也可能是由多个字段组合而成的主键)，所以当一个表中已经存在一个主键时，我们不能再同时添加另一个主键，要想在添加其它主键就必须删除原有的主键。

只有主键和unique才能使用auto_increment，如果一个字段不是主键或unique，那么他也不能使用auto_increment。

移动字段

如果把某个字段移动到另一个字段的后面(比如把B字段移动到A字段后面）

alter table 表名  modify 要被移动的字段名(B) 要被移动的字段的定义(和原来的定义一样)  after A字段名（也就是所说的另一个字段A）;

示例：将name字段移到nickname字段后面

注意，要被移动的字段一定要带上它本身的属性，不然会报错。比如如果使用这样的语句就会报错。

错误用法:alter table user modify name after nickname;

清空表数据

# 这个只是清空了表的数据,表和表结构都还在,等同于一张从没插入过数据的新表,如果插入数据主键ID重新开始计数truncate  tableName;

从图中数据的主键ID就可以看到，通过truncate 表名清空的数据表就相当于一张新表，好像以前就没插入过数据一样。

删除表数据

# 这条命令删除整个表的数据，表结构还在，插入数据时，主键ID会按照原来以后的主键ID基数基础上继续计数delete from 表名;

从图中可以看出，delete 命令只是删除数据，被删除的数据占用的空间依然存在，所以插入的数据时候主键ID会在原来主键ID基数的基础上开始增加。

复制一张表

如果我们需要创建的表和原来的表的结构类似，我们就可以依据原来的表复制一张新表，只是复制了结构，并不复制数据，然后可以通过命令把不需要的字段删除掉。这样我们就可以快速创建一张类似的数据表。

create table 新表名 like  被复制的表名；

原文出处：https://www.cnblogs.com/itbsl/p/9963584.html