MySQL常用命令总结（二）

一.表管理语句

1.查看表

查看所有表
show tables;

查看表的概要
show table status\G;

查看某张表的概要
show table status like 't1' \G;

查看某张表的结构
desc t1;

查看创建表时的sql语句
show create table t1;

2.创建表

简单的创建
create table t2(id int(5),name varchar(20));

添加主键约束 方法一
create table t3(id int(11) key,name varchar(45) not null comment 'student');

添加主键约束 方法二
create table t4(id int(11),name varchar(20),primary key(id));

联合主键
create table t5(id int(11),name varchar(20) not null,primary key(id,name));

外键
create table tt1(id int primary key,name varchar(20) not null,tid int,foreign key(tid) references t3(id));

索引
create table tt2(id int(11),name varchar(50),primary key(id),index ind_name(name));

自增，枚举
create table tt3(id int primary key auto_increment,name varchar(40) not null,age tinyint unsigned,gender enum('f','m') default 'm',index(name));

复制表数据，以tt3表为例子

复制一部分数据
create table tt4 select name,age,gender from tt3;
#注意这样表结构并不会复制

复制全部数据
create table tt5 select * from tt3;

复制表结构，tt3为例子

将tt3的表结构完全复制
create table tt6 like tt3;

设置表的存储引擎和字符集

create table ttt1(id int(4),name varchar(20) not null,index(id))engine=InnoDB auto_increment=2 default charset=utf8;

3.删除表，多少删库跑路人的辛酸泪

drop table tt3;

4.修改表

表重命名

alter table tt1 rename as ttt4;

添加字段

alter table t4 add age int;

添加字段时，同时设定约束
alter table t4 add city varchar(50) not null default 0;

指定添加字段的位置
alter table t4 add count varchar(20) after name;

删除字段，如果有数据也会删除：

alter table t4 drop city;

二.约束管理、索引管理、键管理语句

主键：primary，表上一个或者多个字段的组合，必须不同于已经存在的其他行数据，不能为空，一个表只能有一个主键。

唯一键：unique key：表上一个或者多个字段的组合，必须不同于已经存在的其他行数据，可以为空。主键是唯一键，但是唯一键不一定是主键。

外键：将一个表的值放入第二个表来表示关联，第一个表的主键是第二个表的外键。外键是为了保证数据的一致性，删除是先删除从表，才能删除主表。插入时必须是主表已存在的主键值才可以插入。

示例，以下的演示建立在表tt2上：

desc tt2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(50) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+

非空约束

添加非空约束
alter table tt2 modify name varchar(50) not null;

删除非空约束
alter table modify name varchar(50) null;

自动增长

添加自增，将id字段设为自增
alter table tt2 modify id int auto_increment;

删除自增
alter table tt2 modify id int;

主键约束

添加主键，一个表只能有一个主键
alter table tt2 add primary key(id);

删除主键。如果有自增要先删除自增，再删除主键。如果有外键需要先删除外键关系，才能删除主键。
alter table tt2 drop primary key;

唯一键约束

添加唯一键
alter table tt2 add unique key(name);

添加唯一键的时候，也可以指定唯一键的名称，uni_test为名称
 alter table tt2 add unique key uni_test(name);

删除唯一键，用关键字index
alter table tt2 drop index uni_test;

查看约束

方法一，但是不详细
desc tt2;

查看主外键对应关系
show create table tt3;

还可以使用第三方工具，例如navicat

外键约束

增加外键约束
alter table tt4 add tid int default 0 not null;
alter table tt4 add foreign key(tid) references tt2(id);

删除外键约束。必须先查询到对外键的名称，才能删除。
show create table tt4;
alter table tt4 drop foreign key tt4_ibfk_1;

索引管理
关于索引的文章可以看：
https://www.cnblogs.com/yanglei-xyz/p/mysql_index.html

增加一个单纯的索引，普通的索引
alter table tt3 add index ind_name(name);

指定索引类型，默认btree，desc表示降序，默认升序
create index ind_name on tt5(name(20) desc);

创建一个联合索引
create index ind_id_name on tt3(name(20),id);

重建索引：

repair table tt2 quick;

删除索引：

alter table tt2 drop index ind_name;

查看索引：

show index from tt3;

根据索引名称，查看tt2表中的部分索引：

show index from tt2 where key_name like 'ind%';