mysql 常用命令

database

查看数据库 
show databases;

使用数据库 
use test;

删除数据库  
drop database test;

创建数据库
create database  [if not exists] test; 

查看当前使用的是哪个数据库
select database();

查看数据库创建命令
show create database test;

table

基本表操作

查看数据库下所有的表 
show tables;

查看表详情
desc test;

创建表 
create table if not exists test (id int not null primary key auto_increment,name varchar(50)  default '' );

0填充
zerofill: 当插入的数值比定义的属性长度小的时候，会在数值前面进行补值操作。

create table table_name (
id bigint(20) NULL,
order_no int(6) unsigned zerofill NULL
);

插入表
insert into test(name) values("test");

查询
select * from test where id =1;

更新
update test set name="wang" where id = 1;

删除 
delete from test where id = 1;

清空
truncate table test;

查看表详情
desc test;

表删除
drop table test;

查询

去重查询
select distinct name from test;

字段操作

新增字段
alter table test add age int(11) not null default 0  comment "age";

新增两个字段
alter table test add age2 int(11),add age3 int(11);

删除字段多个
alter table test drop column age2,drop column age3;

修改字段
alter table test modify age int(8) not null default 0 comment "nianning";

索引操作

ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）

索引新增
//普通索引
alter table test add index idx_age (age);
//唯一索引
alter table test add unique uni_age1(age1);
//主键索引
alter table test add primary key (id,name) ;


删除索引
drop index index_name on table_name ;

alter table test drop index index_name ;

alter table test drop primary key ;

组合索引
alter table  test ADD INDEX idx_name_age_age1 (name(16),age,age1); 

查看索引
show index from tb_user;

调优

explain  sql
desc sql 

用的比较少的

查看进程
show processlist;

杀掉进程
kill id

查看版本
select version();

查看自增的步长
select @@auto_increment_increment
 
字段加 1 后查询返回
select id,`id`+1 as id1  from test;

主从

查看master的状态 Position不应该为0

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 1168
     Binlog_Do_DB: mydb
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

show processlist; state状态应该为Has sent all binlog to slave; waiting for binlog to be updated

mysql> show processlist;
+----+-----------------+------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User            | Host             | db   | Command     | Time | State                                                         | Info             |
+----+-----------------+------------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  5 | mydb_slave_user | 172.22.0.4:46984 | NULL | Binlog Dump | 1236 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  6 | mydb_slave_user | 172.22.0.3:36338 | NULL | Binlog Dump | 1234 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  8 | root            | 172.22.0.1:59784 | mydb | Sleep       |  211 |                                                               | NULL             |
|  9 | root            | 172.22.0.1:59796 | mydb | Sleep       |  216 |                                                               | NULL             |
| 10 | root            | 172.22.0.1:59808 | NULL | Query       |    0 | starting                                                      | show processlist |
+----+-----------------+------------------+------+-------------+------+---------------------------------------------------------------+------------------+
5 rows in set (0.00 sec)

2. 查看slave状态

   Slave_IO_Running 与 Slave_SQL_Running 状态都要为Yes

   mysql> show slave status\G
   *************************** 1. row ***************************
                  Slave_IO_State: Waiting for master to send event
                     Master_Host: 172.22.0.2
                     Master_User: mydb_slave_user
                     Master_Port: 3306
                   Connect_Retry: 60
                 Master_Log_File: mysql-bin.000003
             Read_Master_Log_Pos: 1168
                  Relay_Log_File: mysql-relay-bin.000002
                   Relay_Log_Pos: 888
           Relay_Master_Log_File: mysql-bin.000003
                Slave_IO_Running: Yes
               Slave_SQL_Running: Yes
   

   show processlist; 应该有两行state值为：
   　　Has read all relay log; waiting for the slave I/O thread to update it
   　　Waiting for master to send event

   mysql> show processlist;
   +----+-------------+------------------+------+---------+------+--------------------------------------------------------+------------------+
   | Id | User        | Host             | db   | Command | Time | State                                                  | Info             |
   +----+-------------+------------------+------+---------+------+--------------------------------------------------------+------------------+
   |  4 | system user |                  | NULL | Connect | 1692 | Waiting for master to send event                       | NULL             |
   |  5 | system user |                  | NULL | Connect |  667 | Slave has read all relay log; waiting for more updates | NULL             |
   |  8 | root        | 172.22.0.1:63012 | mydb | Sleep   |  663 |                                                        | NULL             |
   |  9 | root        | 172.22.0.1:63024 | mydb | Sleep   |  663 |                                                        | NULL             |
   | 10 | root        | 172.22.0.1:63036 | NULL | Query   |    0 | starting                                               | show processlist |
   +----+-------------+------------------+------+---------+------+--------------------------------------------------------+------------------+
   5 rows in set (0.00 sec)
   

3. 说明：

   Slave_IO_Running：连接到主库，并读取主库的日志到本地，生成本地日志文件

   Slave_SQL_Running:读取本地日志文件，并执行日志里的SQL命令。