03-MySQL应用管理及进阶实战操作

4.修改密码方式

1)方式1

mysqladmin -u root -p'123456' password'123456'

2)方式2

update user set password = password('123456') where user='root' and host = '%'
flush privileges;

3)方式3:当前用户

mysql> set password=password('123456')

5.找回丢失的mysql密码

1)先停止服务

2)使用--skip-grant-tables启动mysql，忽略授权登录验证

mysqld_safe --skip-grant-tables --user=mysql & mysql -u root -p

9.显示连接删除数据库

1) show databases;

2) show databases like ‘%oldboy%’;

3) select database() 查看当前连接数据库

4) use database

5) drop database test 删除数据库

7) select version(); 查看当前版本

8) select user(); 查看当前用户

9) select new(); 查看当前时间

10)show tables; 查看当前数据库表

11)show tables from test 查看指定数据库中的表

12)show tables in test

13)drop user '用户'@‘主机域’ 删除系统多余账号

drop user 'user1'@'localhost';
flush privileges;

14)drop user 无法删除时

delete from test.user where user='root' and host='user1';
flush privileges;

10.创建mysql用户及授权的多种方式

1)创建用户 identified : 密码

create user 'user002'@'localhost' identified by 'SN'

2) 授权用户:

grant all privileges on db1.* to 'user002'@'localhost' identified by 'SN'

grant	all privileges	on db1.*	to 'user002'@'localhost'	identified by 'SN'
授权	操作权限	库.表	用户和主机	用户密码

3)select host,user from mysql.user; 查看用户

4)show grants  for 'user002'@'localhost';  查看用户权限

11.本机及远程主机授权

1)授权局域网内主机远程连接数据库

mysql>grant all on *.* to 'user1'@'10.0.0.%' identified by '123456';
mysql>flush privileges;

12.MySql权限我表

1) select , insert, update, delete, create, drop, references, index, alter,lock tables, execute, create view, show view, create routine,alter routine,event,  triggei

2) desc user\G 竖着显示内容

3)select * from mysql.user\G; 显示用户权限

13.