mysql-cmd-ddl

DDL, Data Definition Language, 数据定义语言
用于对数据库中的表, 视图的操作

• Create
• Drop
• Alter

1. 查看表结构

show columns from $table_name;
desc $table_name;

show full columns from $table_name;

show create table $table_name;

2. 查看表大小

show table status like '$table_name';
show table status from vcash where name='$table_name';
select table_name, data_length, rows from information_schema.`tables` where table_name='$table_name';

3. 添加库

create database if not exists $database_name default charset utf8 collate utf8_general_ci;

4. 删除列

alter table $table_name drop $column_name;

5. 增加列

alter table $table_name add $column_name int not null comment $comment;

6. 增加列

alter table $table_name add $column_name int not null  comment $comment;

7. 修改列的类型信息

alter table $table_name change $column_name $new_column_name bigint not null comment $comment;

8. 重命名表

alter table $table_name rename $new_table_name;

9. 删除表中主键

alter table $table_name drop primary key;

10. 添加主键

alter table $table_name add constraint $pk_name primary key (resid, resfromid);

11. 添加索引

alter table $table_name add index $idx_name ($column_name);

12. 添加唯一限制条件索引

alter table $table_name add unique $uk_name($column_name);

13. 删除索引

alter table $table_name drop index $idx_name;

14. 修改 auto_increment

alter table $table_name auto_increment=1;

15. 改变字段顺序

alter table student modify id int(10) unsigned auto_increment first;
alter table student modify name varchar(10) after id;

16. 创建表

create table if not exists user (
    `id` bigint(20) auto_increment,
    `username` varchar(50) unique not null comment '姓名',
    `password` varchar(200) not null comment '密码',
    `salt` varchar(8) default null comment '加盐',
    `gender` tinyint(2) default null comment '性别 1:男 2:女',
    `birthday` date default null comment '出生日期',
    `create_time` datetime not null default current_timestamp comment '创建时间',
    `update_time` datetime default null on update current_timestamp comment '更新时间',
    `disable` tinyint(1) not null default '0' comment '记录是否失效 0:否, 1:是',
    primary key (`id`)
) engine = innodb default charset = utf8;

17. 修改字符集

show table status from @database like '@table';

alter database @database default character set utf8mb4;

alter table @table default character set utf8mb4 collate utf8mb4_general_ci;
alter table @table convert to character set utf8mb4;

18. 查看有哪些自定义的函数

select SPECIFIC_NAME from ROUTINES where ROUTINE_TYPE = 'FUNCTION' and DEFINER = 'root@%' ;