mysql 常用命令

零 修订记录

序号	修订内容	修订时间
1	新增	2022/5/6

一 摘要

本文主要记录常用mysql 命令

二 环境信息

三 记录

（一）基础工具

3.1.1 查看某张表索引

3.1.1.1 mysql 版本

mysql 5.7

3.1.1.2 执行命令

show index from 表名;

show index from tdp_material;

示例

mysql> show index from tdp_material;
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tdp_material |          0 | PRIMARY        |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| tdp_material |          0 | index_material |            1 | group_id    | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3.1.2 新建用户和数据库以及授权

以下命令在mysql 5.7 上执行

create database piwigo_db  character set utf8 collate utf8_bin;
create user piwigo_db_user identified by '123456';
grant all privileges on piwigo_db.* to  piwigo_db_user@'%' identified by '123456' with grant option;
grant all privileges on piwigo_db.* to piwigo_db_user@localhost  identified by '123456' with grant option;
flush privileges;

3.1.2 修改表字段属性

alter table 表名 modify column 字段名 数据类型 comment ‘注释’;

alter table dev_project_workload modify column man_month  VARCHAR(20)    COMMENT '人月' ;

3.1.3 新增字段

在某个字段A后面新增一个字段

alter table 表名 add column 新增字段名 数据类别 DEFAULT NULL COMMENT ‘注释’ AFTER 字段A;

alter table dev_project_hr_detail add column demand_reason TEXT DEFAULT NULL COMMENT '需求理由' AFTER demand_date;