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)
-
查看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 eventmysql> 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)
-
说明:
Slave_IO_Running:连接到主库,并读取主库的日志到本地,生成本地日志文件
Slave_SQL_Running:读取本地日志文件,并执行日志里的SQL命令。