好好学MySQL：MySQL常用命令

1. 前言

mysql是郝同学使用最多的数据库，但是每次要手敲命令，还是得谷歌百度。好记性不如烂笔头，本文对常用mysql命令进行一些整理，方便再次拾起。

2. mysql中SQL语句的大小写规则

mysql中，数据库名、表名、列名、索引名、SQL关键字、函数名都不区分大小写
详情参考MySQL中SQL语句的大小写规则

3. 权限相关

参考《MySQL重置密码》。

4. 数据库相关

4.1. 创建/删除数据库

-- 创建数据库
create database vkdb default character set utf8 collate utf8_general_ci;

-- 删除数据库
drop database vkdb;

4.2. 创建/删除用户

-- 创建用户
create user 'vkuser'@'%' identified by 'vkpassword';
-- or
insert into mysql.user(Host,User,Password) values('%','vkuser','vkpassword');

-- 删除用户
drop user 'vkuser';
drop user 'vkuser'@'192.168.56.101';

4.3. 授权数据库给用户

-- 授权数据库给用户
grant all privileges on vkdb.* to 'vkuser'@'%' with grant option;
-- or
insert into mysql.user(HOST,Db,USER,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
    VALUES('%','vkdb','vkuser','Y','Y','Y','Y','Y','Y');

-- 授权数据库给用户，同时指定密码
-- 危险！这条命令会同时修改vkuser的密码，不区分数据库！
grant all privileges on vkdb.* to 'vkuser'@'%' identified by 'vkpassword2' with grant option; 

-- 授权数据库给用户，同时指定主机和密码
-- 只有通过192.168.56.101访问数据库时，使用密码vkpassword2，其他主机访问数据库时还是使用vkpassword
grant all privileges on vkdb.* to 'vkuser'@'192.168.56.101' identified by 'vkpassword2' with grant option;

-- 取消授权
revoke all on vkdb.* from 'vkuser'@'%';

-- 授权用户只允许登录权限
grant usage on *.* to 'vkuser'@'%';

-- 查看用户和授权
show grants for 'vkuser'@'%';
select User,Host from mysql.user;

5. 数据表相关

5.1. 创建/删除数据表

-- 创建数据表
create table if not exists `user` (
  `id` int(8) not null auto_increment,
  `name` varchar(32) not null,
  `password` varchar(32) not null default '',
  primary key (id)
) engine=innodb default charset=utf8;

-- 查看数据表
describe table user;

-- 删除数据表
drop table `user`;

-- 删除所有数据表
select concat('drop table ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='vkdb';

5.2. 添加/修改/删除column

-- 添加column
alter table `user` add column `deleted1` bool not null default false;

-- 修改column
alter table `user` change `deleted1` `deleted` bool not null default false;
alter table `user` modify `deleted` int(1) not null default 0;
alter table `user` modify column `deleted` int(1) not null default 0;

-- 删除column
alter table `user` drop `deleted`;
alter table `user` drop column `deleted`;

5.3. 设置unique

-- 设置unique
alter table `user` modify `name` varchar(32) not null unique;
alter table `user` modify column `name` varchar(32) not null unique;

5.4. 插入/修改/删除数据

-- 插入数据
insert into `user` (`name`,`password`) values('haojin','voidking');

-- 修改数据
update `user` set password='haojin' where name='haojin';

-- 删除数据
delete from `user` where name='haojin';

5.5. 查询数据

-- 查询数据
select * from `user` where name='haojin' and password='voidking';
select * from `user` where name='haojin' or password='haojin';
select * from `user` where name<>'haojin0';
select * from `user` where name like '%ao%';
select * from `user` order by id asc;
select * from `user` order by id desc;
select * from `user` order by id desc limit 2;
select * from `user` where unix_timestamp(update_time) < unix_timestamp('2020-03-01 10:14:51');

5.6. 查看表是否被锁

-- 查看表是否被锁
SHOW OPEN TABLES;
SHOW OPEN TABLES WHERE In_use > 0;
SHOW PROCESSLIST;
SHOW TABLE STATUS LIKE 'your_table_name';

6. 导入导出数据

6.1. 导出数据库数据

mysqldump -uroot -ppassword vkdb > /root/vkdb.sql

6.2. 导出数据库所有表结构

mysqldump -uroot -ppassword -d vkdb > /root/vkdb_structure.sql

6.3. 导出数据库user表数据

mysqldump -uroot -ppassword vkdb user > /root/vkdb_user.sql

6.4. 导出数据库user表结构

mysqldump -uroot -ppassword -d vkdb user > /root/user_structure.sql

CREATE DATABASE 语句之前加一个 DROP DATABASE

mysqldump -uroot -ppassword -d --add-drop-table vkdb user > /root/user_structure.sql 

6.5. 导入数据库数据

通过mysql命令导入

mysql -uroot -ppassword vkdb < /root/vkdb.sql
mysql -uroot -ppassword -f vkdb < /root/vkdb.sql

或者登录到mysql后执行导入

source /root/vkdb.sql;

6.6. csv数据导入

使用dbeaver导入csv数据，可以自动创建表结构。

7. 从binlog导出sql语句

首先确保mysql server开启了binlog

show variables like '%log_bin%';

本地导出：

mysqlbinlog --base64-output=decode-rows -v --result-file=output.sql binlog.000001

binlog文件可能比较多，选择最近修改的那个导出。

远程导出：

mysqlbinlog --read-from-remote-server --host=host_name --user=user_name --password=password --base64-output=decode-rows -v --result-file=output.sql -v binlog.000001

8. shell中执行sql

# shell中直接执行sql
mysql -h 192.168.56.100 -uroot -pvoidking -D vkdb -e "select * from user;"

需求：mysql数据库，获取数据库vk中的app表中所有记录的name，写入到 name.txt 文件。

脚本：

#!/bin/bash

get_all_name="select name from app where app.deleted=0;"

all_name=$(mysql -h192.168.56.100 -uroot -pmypassword -s -e "use vk;${get_all_name}")
echo "${all_name}" > name.txt

9. 高级命令

9.1. 联合查询

left join（左联接）：返回左表中的所有记录以及和右表中的联接字段相等的记录。
right join（右联接）：返回右表中的所有记录以及和左表中的联接字段相等的记录。
inner join（等值联接）：只返回两个表中联接字段相等的记录。

例子：查询用户表（user）中的用户名（name）和用户详细表（user_detail）中的用户手机号码（telephone）。

select user.name, user_detail.telephone
from user inner join user_detail
on user.id=user_detail.user_id
where user.id=1;
-- or
select U1.name, U2.telephone
from user as U1 inner join user_detail as U2
on U1.id=U2.user_id
where U1.id=1;

9.2. 联合查询3张表

例子：查询用户表（user）中的用户名（name）、用户角色表（role）中的角色名（name）、用户详细表（user_detail）中的用户手机号码（telephone）。

select user.name, user_detail.telephone, role.name
from user 
left join role
on user.role_id=role.id
left join user_detail
on user.id=user_detail.user_id
where user.id=1;

9.3. 查询重复记录

需求：查询user表中的重名记录

select * from user 
where name in 
(select name from user group by name having count(name) > 1);

9.4. 替换字符串

需求：用户表（user）的个性签名字段（info）是字符串，字符串中都包含“my name is haojin”，现在需要把“my name is haojin”都替换成“my name is voidking”。

update user set info=replace(info,"my name is haojin","my name is voidking") where info like "%my name is haojin%";

9.5. 拼接命令

例子：根据手机号17625160000查询用户，给该用户添加另外一个手机号17625160001，手机号用逗号分隔。

select name,mobiles from user where mobiles like "%17625160000%";
-- 查询出name="haojin",mobiles="17625160000"
update user set mobiles="17625160000,17625160001" where name="haojin";

更简单的方法是使用concat拼接：

select CONCAT('update user set mobiles="',mobiles,',17625161201" where name="',name,'";') from user where mobiles like "%17625160000%";

假设有很多用户需要另外添加手机号，使用拼接的方式也很麻烦，此时需要借助shell脚本。
已有文本mobiles.txt，内容为：

17625160000 17625160001
17625160002 17625160003

编写shell脚本为：

#!/bin/bash
 
grep -v "^$" mobiles.txt | while read line
do
  old=`echo $line | awk '{print $1}'`
  new=`echo $line | awk '{print $2}'`
  echo "select CONCAT('update user set mobiles=\"',mobiles,',${new}\" where name=\"',name,'\";') from user where mobiles like \"%${old}%\";"
done

9.6. 定时备份

需求：每天自动备份数据库。
1、测试命令

/usr/bin/mysqldump -h 127.0.0.1 -uroot -pvoidking vkdb > /root/vkdb-`date +%Y%m%d-%H%M%S`.sql

2、测试定时任务

crontab -e

写入每分钟执行一次的定时任务：

# backup vkdbdb
* * * * * /usr/bin/mysqldump -h 127.0.0.1 -uroot -pvoidking vkdb > /root/vkdb-`date +\%Y\%m\%d-\%H\%M\%S`.sql

查看执行结果是否正常，如果不正常，通过crontab日志排查：

tail -f /var/log/cron

3、确认定时任务

# backup vkdbdb
0 0 * * * /usr/bin/mysqldump -h 127.0.0.1 -uroot -pvoidking vkdb > /root/vkdb-`date +\%Y\%m\%d-\%H\%M\%S`.sql