- 一、准备工作
- 二、常用表结构操作语句
- 1.在数据库表中新增 字段、约束
- 1.1 新增字段
- 1.2 新增约束
- 2.在数据库表中修改 表、字段
- 2.1 修改表的名字
- 2.2 修改字段名、数据类型、默认值、注释、自增属性
- 3.在数据库表中删除表、字段
- 3.1 删除表
- 3.2删除字段、字段约束
- 3.在数据库表中查看表结构
- 3.1 查看数据库
- 3.2 查看表结构
- 3.3 查看表SQL语句
- 3.2 判断字段是否为自增
数据库：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:下架';
执行结果：
-- 添加主键 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;
执行结果：
-- 修改表的名字 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;
执行结果：
-- 删除表 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';
执行结果：
-- 查看表SQL语句 show create table 表名;
示例：查看表SQL语句
-- 查看表SQL语句 show create table t_product;
执行结果：
-- 判断字段是否为自增 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 为空
