MySQL常用命令

前言

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

权限相关

参考《MySQL重置密码》

数据库相关

1、常用命令

1
2
3
4
5
6
7
8
9
10
11
12
# 创建数据库
create database vkphp default character set utf8 collate utf8_general_ci;

# 删除数据库
drop database vkphp;

# 导出数据库数据
mysqldump -uroot -ppassword vkphp > /root/vkphp.sql

# 导入数据库数据
mysql -uroot -ppassword vkphp < /root/vkphp.sql
source /root/vkphp.sql;

数据表相关

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
# 创建数据表
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;

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

# 删除所有数据表
SELECT CONCAT('drop table ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='vkphp';

# 添加column
alter table `user` add `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;

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

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

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

# 查询数据
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;

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

高级命令

联合查询

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

例子:查询用户表(user)中的用户名(name)和用户详细表(user_detail)中的用户手机号码(telephone)。

1
2
3
4
5
6
7
8
9
select user.name, user_detail.telephone
from user inner join user_detail
on user.id=user_detail.user_id
where user.id=1;
# 或者
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;

联合查询3张表

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

1
2
3
4
5
6
7
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;

拼接命令

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

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

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

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

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

1
2
17625160000 17625160001
17625160002 17625160003

编写shell脚本为:

1
2
3
4
5
6
7
8
#!/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

定时备份

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

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

2、测试定时任务
crontab -e,写入每分钟执行一次的定时任务:

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

查看执行结果是否正常,如果不正常,通过crontab日志排查:
tail -f /var/log/cron

3、确认定时任务

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

0%