MySQL语句-表操作汇总

表操作

• 查看创建表语法

  help create table;
  

• 显示库中所有的表
  格式：

   SHOW [FULL] TABLES [{FROM | IN} 表名]  [LIKE 'pattern' | WHERE expr]
  

  示例:

  显示所有有表：
    show tables;
  显示表名包含user名称所有表：
    show tables like '%user%';
  

• 查看建表语句
  格式：

  SHOW CREATE TABLE 表名
  

  示例：

  SHOW CREATE TABLE t_user; 
  

  

• 查看表结构
  格式：

  DESC 表名 等同于：SHOW COLUMNS FROM 表名
  

  示例:

  desc t_user;
  

  

• 创建表基本格式
  格式：

  CREATE TABLE [IF NOT EXISTS] <表名>(
    <列名> <数据类型> [约束条件]  [COMMENT字段的注释],
    ...
    <列名> <数据类型> [约束条件]  [COMMENT字段的注释]
  )[COMMENT=表的注释];
  

  示例：

  create table t_user(
    id int,
    user_name varchar(32) 
  );  
  
  

• 创建和存在的表一样结果的表
  格式:

  CREATE TABLE [IF NOT EXISTS]  表名  { LIKE old_tbl_name | (LIKE old_tbl_name) }
  

  示例:

  create table t_user_temp like t_user;
  

  

• 修改表注解

  ALTER TABLE 表名 COMMENT'表注释';
  

• 修改表名
  格式：

  ALTER TABLE 旧表名 RENAME 新表名;
  

  实例:

  alter table t_user rename t_user_temp;
  

• 删除表
  格式：

  DROP  TABLE [IF EXISTS] 表名;
  

  示例:

  drop table t_user_temp;
  

• 更改表存储引擎
  格式：

  ALTER TABLE 表名 ENGINE = 存储引擎名;
  

  查看数据库支持的存储引擎:

  SHOW ENGINES;
  

列操作

• 新增列
  格式：

  ALTER TABLE 表名 ADD 列名 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];
  

  示例：

  alter table t_user add password varchar(32);
  

• 修改列
  格式：

  ALTER TABLE 表名 MODIFY 列名 数据类型 [COMMENT '修改的字段注释'];
  

  示例：

   alter table t_user modify password varchar(16);   
  

• 修改列名
  格式：

  ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型;
  

  示例：

  alter table t_user change password pass_word varchar(32);
  

• 删除列
  格式：

  ALTER TABLE 表名 DROP 列名;
  

字段约束条件

• 所有约束

  约束条件	说明
  PRIMARY KEY	主键约束
  FOREIGN KEY	外键约束
  NOT NULL	约束不能为空
  UNIQUE	约束值唯一
  AUTO_INCREMENT	标识该属性的值是自动增加
  DEFAULT	为该属性设置默认值

• PRIMARY KEY
  建表格式:

  单字段主键格式：列名 数据类型 PRIMARY KEY
  多字段主键格式：PRIMARY KEY(列名1,列2...)
  

  SQL格式：

  ALTER TABLE 表名 ADD PRIMARY KEY(列名1,列名2...);
  

  删除格式：

   ALTER TABLE 表名 DROP PRIMARY KEY;
  

• FOREIGN KEY
  建表格式:

  CONSTRAINT 外键名 FOREIGN KEY(外键列名1,外键列名2...) REFERENCES 外键表名(列名1,列名2...)
  

  SQL格式：

   ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键列名1,外键列名2...) REFERENCES 外键表名(列名1,列名2...);
  

  删除格式：

  ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;
  

• NOT NULL
  建表格式:

  列名 数据类型 NOT NULL
  

  SQL格式：

  ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;
  

  删除格式：

  ALTER TABLE 表名 MODIFY 列名 数据类型 NULL;
  

• UNIQUE
  建表格式:

  列名 数据类型 UNIQUE
  

  SQL格式：

  ALTER TABLE 表名 ADD UNIQUE INDEX 名称(列名1,列名2...);
  

  删除格式：

  DROP INDEX 名称 ON 表名;
  ALTER TABLE 表名 DROP INDEX 名称;
  

• AUTO_INCREMENT
  建表格式:

  列名 数据类型 AUTO_INCREMENT
  

  SQL格式：

  ALTER TABLE 表名 MODIFY 列名 数据类型  AUTO_INCREMENT PRIMARY KEY;
  

  删除格式：

  ALTER TABLE 表名 MODIFY 列名 数据类型  PRIMARY KEY;
  

• DEFAULT

  列名 数据类型 DEFAULT 默认值
  

索引

• 创建主键索引(只能一个)
  见上-PRIMARY KEY

• 创建普通索引
  方法1：建表时，增加建立索引语句

   create table t_user(
    id int(16) not null AUTO_INCREMENT,  
     user_name varchar(32) not null,
    primary key(id)，
    KEY index_name(user_name)    <====user_name普通索引,索引名称：index_name
  );
  

  方法2：建表后,alter命令增加普通索引
  alter table 表名 add index 索引名(列);

  alter table t_user add index index_name(user_name);
  

• 对字段的前n个字符创建普通索引
  create index 索引名称 on 表名(列(n));

  create index index_name on t_user(user_name(8));
  

• 多个字段创建联合索引
  create index 索引名称 on 表名(列1,列2…)

  create index index_name on t_user(user_name,password)
  

• 多个字段前n个字符创建联合索引
  create index 索引名称 on 表名(列1(n),列2(n)…)

• 删除普通索引
  alter table 表名 drop index 索引名;
  drop index 索引名称 on 表名;

• 联合索引生效特性
  索引:index(列1,列2,列3)
  查询条件:列1、列1列2、列1列2列3 三个条件都会走索引

• 查看索引
  SHOW INDEX FROM 表名

  show index from t_user\G
  

• 创建唯一索引(非主键)
  减少-UNIQUE