常用Mysql语句

纪录运维过程常用的sql語句,备忘 创建数据库 CREATE DATABASE test CHARACTER SET UTF8; 字符集为UTF8

0x01 用戶密码及登陆相关

1. 更改密码

1
2
update user set password=password('root') where user='root';
flush privileges;

2. 允许远程登录

1
2
grant all privileges on *.* to 'username'@'%' identified by 'password' with grant option;
flush privileges;

3. 更改Mysql监听地址

1
2
3
vim /etc/my.cnf
#在[mysqld]中添加
bind-address=127.0.0.1

4. 创建用户

1
2
3
grant all privileges on database.tables to 'username'@'127.0.0.1' identified by 'password' with grant option;
flush privileges;
# all privileges ---- 所有权限(可指定SELECT,DROP,UPDATE,INSTER....)

5. 查询所有用戶基本信息

1
SELECT user,host,password FROM mysql.user;??

初始安裝数据库使用mysqladmin设置密码后,直接使用mysql -uroot -ppasswd 登陆正常,
使用mysql -uroot -ppasswd -h127.0.0.1
报错:
Access denied for user ‘root‘@’hostname’
设置允许远程登陆,仍然报错
这时使用SELECT user,host,password FROM mysql.user;语句进行查询
+——–+—————-+——————————————-+
| root | localhost | 3E8000C2FA234A170531B9063518B3B479E7C47B |
| root | 127.0.0.1 |
2E9879C2FA764A1705567896B518B3B47324EA23 |
+——–+—————-+——————————————-+
更改為相同即可

0x02 数据增刪改查

1. 批量刪除匹配的表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
例:
+-----------------------------+
| userregin2017_03_01 |
| userregin2017_03_02 |
| userregin2017_03_03 |
| userregin2017_03_04 |
| userregin2017_03_05 |
| userregin2017_03_06 |
| userregin2017_03_07 |
| userregin2017_03_08 |
| userregin2017_03_09 |
| userregin2017_03_10 |
| userregin2017_03_11 |
| userregin2017_03_12 |
| userregin2017_03_13 |
+-----------------------------+
Select CONCAT( 'drop table ', table_name, ';' ) FROM information_schema.tables Where table_name LIKE 'userregin%';
# 导出的数据使用vim编辑Ctrl+v(选取) >> Shift+i >> 编辑 >> Esc >> 方向键下

2. 查询数据库大小

1
SELECT concat((SUM(DATA_LENGTH)+SUM(INDEX_LENGTH))/1024/1024,'MB') as data FROM information_schema.tables WHERE TABLE_SCHEMA='your_database';

3. 查询表大小

1
SELECT concat((SUM(DATA_LENGTH)+SUM(INDEX_LENGTH))/1024/1024,'MB') as data FROM information_schema.tables WHERE TABLE_SCHEMA='your_database' and TABLE_NAME='your_table';

4. 查询表结构

1
desc your_table;

5. 查询建表语句

1
show create table table_name;

6. 刪除

清空表数据:

1
2
3
delete from table
delete from table where id=1;
#带规则清除

7. 查询表中NAME字段有哪些类型

1
select NAME from t_user group by NAME;

0x03 备份还原

压缩备份:

1
mysqldump -B -uroot -proot Your_database| gzip > Your_database.sql.gz

压缩还原:

1
gunzip < Your_database.sql.gz | mysql -uroot -proot

sql文件还原:

1
2
3
mysql -uroot -proot
use Your_database;
source /root/Your_database.sql;