Mysql数据库常用表结构操作语句

一、准备工作

数据库：test
数据库表：t_user、t_product
t_user:

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  `age` int(10) DEFAULT NULL COMMENT '年龄',
  `remarks` varchar(255) DEFAULT NULL COMMENT '备注',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_user` varchar(255) DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `update_user` varchar(255) DEFAULT NULL COMMENT '更新人',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';

t_product:

CREATE TABLE `t_product` (
  `product_id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL COMMENT '标题',
  `sub_title` varchar(255) DEFAULT NULL COMMENT '副标题',
  `sale_price` decimal(10,2) DEFAULT NULL COMMENT '商品售价',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_by` int(11) DEFAULT NULL COMMENT '创建者',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `update_by` int(11) DEFAULT NULL COMMENT '修改者',
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品表';

二、常用表结构操作语句

1.在数据库表中新增 字段、约束

1.1 新增字段

# 在表末尾增加列
alter table 表名 add 字段名 数据类型 default 默认值 comment '注释';
# 在表开头添加列
alter table 表名 add 字段名 数据类型 default 默认值 comment '注释' first;
# 在某字段之后添加列
alter table 表名 add 字段名 数据类型 default 默认值 comment '注释' after 字段;

示例：新增字段status

-- 新增字段status
alter table t_product add status int(1) default 0 comment '状态 0:上架 1:下架';

执行结果：

1.2 新增约束

-- 添加主键
alter table 表名 add primary key(字段名);
-- 添加外键
alter table 表名 constraint 约束名 foreign key(字段名) references 主表名(字段名);
-- 添加唯一约束
alter table 表名 constraint 约束名 unique(字段名);
-- 添加检查约束
alter table 表名 constraint 约束名 check(字段);
-- 添加默认值
alter table 表名 change column 字段名  字段名 数据类型 default 默认值;
-- 添加非空约束
alter table 表名 change column 字段名  字段名 数据类型 not null;

示例：为字段title添加非空约束

-- 为字段title添加非空约束
alter table t_product change column title title varchar(255) not null;

执行结果：

2.在数据库表中修改 表、字段

2.1 修改表的名字

-- 修改表的名字
alter table 旧表名 rename 新表名;

2.2 修改字段名、数据类型、默认值、注释、自增属性

-- 修改字段名、数据类型、默认值、注释
alter table 表名 change 旧字段名 新字段名 数据类型 default 默认值 commit '注释';
-- 修改数据类型
alter table 表名 modify 字段名 数据类型;
-- 修改现有主键字段自增属性(确保该字段是主键，若不是先设置主键)
alter table 表名 change 字段名 字段名 数据类型 not null auto_increment;

示例1：修改字段sub_title的数据类型

-- 修改字段sub_title的数据类型
alter table t_product modify sub_title varchar(200);

执行结果：

示例2：修改现有主键字段自增属性

-- 修改现有主键字段自增属性(确保该字段是主键，若不是先设置主键)
alter table t_product change product_id product_id int(11) not null auto_increment;

执行结果：

3.在数据库表中删除表、字段

3.1 删除表

-- 删除表
drop table 表名;

3.2删除字段、字段约束

-- 删除字段
alter table 表名 drop 字段名;
-- 删除主键
alter table 表名 drop primary key;
-- 删除外键
alter table 表名 drop foreign key 约束名;
-- 删除唯一约束
alter table 表名 drop index 约束名;
-- 删除检查约束
alter table 表名 drop constraint 约束名;
-- 删除默认值
alter table 表名 change 字段名 字段名 数据类型 default null;
-- 删除非空约束
alter table 表名 change 字段名 字段名 数据类型 null;

3.在数据库表中查看表结构

3.1 查看数据库

-- 查看数据库
show databases;

3.2 查看表结构

-- 查看表结构方法1：
desc 表名;
-- 查看表结构方法2：
describe 表名;
-- 查看表结构方法3：
show columns from 表名;

示例：

-- 查看 表t_product的结构
desc t_product;

以上三种方法的执行效果相同：

查看表结构方法4：借用MySQL自身的information_schema数据库

-- 查看表结构方法4：
SELECT * 
FROM information_schema.COLUMNS 
WHERE table_schema = '数据库' AND table_name = '表名';

-- 查看表结构方法4的简化版（需要处于information_schema数据库内 ）：
SELECT * FROM COLUMNS 
WHERE table_name = '表名';

示例：查看表结构方法4

SELECT * 
FROM information_schema.COLUMNS 
WHERE table_schema = 'test' AND table_name = 't_product';

执行结果：

3.3 查看表SQL语句

-- 查看表SQL语句
show create table 表名;

示例：查看表SQL语句

-- 查看表SQL语句
show create table t_product;

执行结果：

3.2 判断字段是否为自增

-- 判断字段是否为自增
SELECT extra FROM information_schema.columns 
WHERE TABLE_SCHEMA='数据库名' 
AND table_name = '表名' 
AND column_name='字段名'

示例：判断t_product表的product_id字段是否自增

select extra 
from information_schema.columns 
where table_schema='test' 
and table_name = 't_product' 
and column_name='product_id'

是自增如下结果，否则EXTRA 为空