mysql5常用命令（一）
1、登录mysql
(1) mysql -u[用户] -p[密码] -h[登录到哪台数据库]
注意：-p后面不要加空格
[root@Oldboy ~]# mysql -uroot -p123456 -h localhost
(2) mysql [数据库名] -u[用户] -p[密码] -h[登录到哪台数据库]
[root@Oldboy ~]# mysql test -uroot -p123456 -h localhost
1.10、指定端口号和ip地址登录
mysql -u root -P 3307 -h 101.200.152.192 -p
1.1、查看数据库
mysql> show databases;
mysql> show databases like '%weddnqrtia%';
[root@Oldboy ~]# mysql -uroot -p123456 -e "show databases;"
1.2、查看当前用户
mysql> select user();
12.1、查看允许登录mysql的所有用户和主机
mysql> select user,host from mysql.user;
1.3、查看创建的数据库信息（字符集）
mysql> show create database oldboy;
2、设置mysql密码
[root@Oldboy ~]# mysqladmin -uroot password '123456'
3、修改mysql 密码
[root@Oldboy ~]# mysqladmin -uroot -p123456 password '654321'
[root@Oldboy ~]# mysql> update mysql.user set password=password('oldboy123') where user='root' and host='localhost';
4、刷新mysql命令
mysql> flush privileges;
4.1、创建一个新的用户，并且这个用户可以为别人授权
grant all privileges on . to ‘admin’@’localhost’ identified by ‘mypassword’ with grant option;
with grant option：可以为别人授权的权限
5、mysql为数据库授权并创建用户
mysql> grant all privileges on *.* to root@'172.16.1.%' identified by 'oldboy123';
mysql> grant all privileges on ttc_mager.* to clouddeep@'127.0.0.1' identified by 'rtlou@8ty90';
6、只创建用户
mysql> create user rtnqg@'172.16.1.%' identified by '123456';
7、只授权不创建用户
mysql> grant all on wenqiang.* to wenqiang@'172.16.1.%';
8、删除用户
(1) 查看数据库中所有用户
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+---------------------------------------+
| User: 'root'@'%'; |
| User: 'root'@'127.0.0.1'; | #保留
| User: 'root'@'::1'; | #保留
| User: ''@'localhost'; |
| User: 'root'@'localhost'; | #保留
| User: ''@'localhost.localdomain'; |
| User: 'root'@'localhost.localdomain'; | #保留
+---------------------------------------+
(2) 删除用户
mysql> drop user wenqiang@'172.16.1.5';
mysql> delete from mysql.user where user='wenqiang' and host='172.16.1.51'; (drop删不掉的时候用delete)
9、查看普通用户授权
(1) 先查看数据库中有哪些用户
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
(2) 再查看指定用户的授权情况
mysql> show grants for 'wenqiang'@'%';
后面的% 根据第一条查看到的内容来写具体ip地址
9.1、当root用户无法给普通用户授权的时候
(1)登录mysql
[root@localhost ~]# mysql -uroot -p -h localhost
mysql> grant all on *.* to root@'localhost' identified by '123456';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) #报错
(2)查看当前有哪些用户
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+---------------------------------------+
| query |
+---------------------------------------+
| User: 'root'@'127.0.0.1'; | #使用 mysql -uroot -p -h127.0.0.1登录mysql授权没问题
| User: 'wenqiang'@'172.16.1.%'; |
| User: 'root'@'localhost'; | #我发现从localhost登录的root用户无法给普通用户授权
| User: 'root'@'localhost.localdomain'; |
+---------------------------------------+
4 rows in set (0.00 sec)
(3)查看该root用户的Grant_priv选项是Y还是N(N表示无权给普通用户授权)
mysql> select * from mysql.user where User='root' and Host='localhost'\G;
*************************** 1. row ***************************
Host: localhost
User: root
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N #无授权权限
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.00 sec)
ERROR:
No query specified
(3)把Grant_priv选项的N改为Y就可以了
mysql> update mysql.user set Grant_priv='Y' where User='root' and Host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
(4)退出重新登录mysql，再次给普通用户授权(一定要先退出mysql)
mysql> grant all on *.* to root@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
10、撤回权限(这里只是撤回权限，用户仍然可以登录)
(1) 查看数据库中所有用户
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
(2) 查看某个用户的授权情况
mysql> show grants for 'wenqiang'@'%';
后面的% 根据第一条查看到的内容来写
(3) 撤回权限
mysql> revoke select, insert, update on oldboy.* from wenqiang@'172.16.1.%';
mysql> revoke all on test.* from oldboy@'127.0.0.1';
该用户需要重新连接mysql数据库权限才能生效
grant all的所有权限：
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER，index
11、创建数据库
格式：
create database 数据库名 default character set 字符集名 collate 字符集类型；
mysql> create database oldgirl; 默认创建拉丁字符集
mysql> create database oldboyDB default character set utf8 collate utf8_general_ci; 创建数据库指定字符集UTF8
mysql> create database oldgirlDB default character set gbk collate gbk_chinese_ci; 创建数据库指定字符集GBK
mysql> show CHARACTER SET; 查看所有字符集
mysql> show create database oldboyDB; 查看指定数据库字符集
12、查看当前慢查询的语句
mysql> show processlist; 可能是不完整的语句
mysql> show full processlist; 可能是不完整的语句
[root@Oldboy ~]# mysql -uroot -poldboy123 -e "show full processlist;"|grep -v Sleep 现场完整抓取慢查询语句
mysql -uroot -pmysql_748@wyx -e "show full processlist;"|grep -v Sleep |sort -k6rn 查看持续时间最长的SQL
13、查看慢查询是否开启以及慢查询日志路径
mysql> show variables like "%slow%";
14、试图连接mysql的次数（mysql的命中率，包括正常和失败次数）
show status like 'connections';
15、 已经建立的线程数
show status like 'threads_created';
16、查询user表中company_id=‘xxx’ 并且 is_activated=1的条件并统计数量某字段并统计
select * from company WHERE name like '%国药%';
SELECT count(*) FROM `user` WHERE company_id ='5f244f1169df00cee96b3f1d' AND is_activated =1;
17、对log字段降序排序
# 对log字段降序排序
select company_id,log,create_date from log ORDER BY create_date DESC
# 对log字段降序排序并筛选 '登录客户端成功' 的行
select company_id,log,create_date from log where log like '%登录客户端成功%' ORDER BY create_date DESC;
18、查询create_date 字段大于某一时间段的sql
# 查询大于某一时间段的
select company_id,create_date from log where create_date>'2019-07-27 19:30:00' order by create_date desc;
# 查询大于某一时间段的comany_id并去重
select DISTINCT company_id from log where create_date>='2021-10-01 19:30:00' order by create_date desc;;
# 使用where in语法
select * from company where id in(select DISTINCT company_id from log where create_date>='2021-10-01 19:30:00' order by create_date desc)