mysql 的一些常用命令

 允许所有主机远程访问
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' IDENTIFIED BY 'dbapassword' WITH GRANT OPTION;
指定主机可远程
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'192.168.0.100' IDENTIFIED BY 'dbapassword' WITH GRANT OPTION;
指定权限开远程
grant select,insert,update,delete on test.* to dba@"192.168.0.100" Identified by "dbapassword";
查看用户信息
select user,host,password from mysql.user;
设置和修改root密码
set password for root@localhost=password('在这里填入root密码');
修改密码
use mysql
update user set password=password("新密码") where user="root";
删除匿名用户
delete from mysql.user where user='';
删除名为test的空数据库
drop database test;
建立库
create database test;
在数据库中建立表
create table test(num int, name varchar(50));
插入一个值到表中
insert into test values(1,'Hello World!');　
查表信息
select * from test;
更新表的信息，赋予新的值
update test set name='Hello Everyone!';
删除表
drop table test;
删除库
drop database test;　
取消dba对ccvic的权限
revoke all privileges on ccvic.* from dba@localhost;
删除dba用户
delete from mysql.user where user='dab' and host='localhost';　
查询dba用户信息
select user from mysql.user where user='dba';
刷新，使操作生效
FLUSH   PRIVILEGES;
mysql密码忘记，找回办法：
如果 MySQL 正在运行，首先杀之： killall -TERM mysqld
启动 MySQL ：bin/safe_mysqld --skip-grant-tables &
就可以不需要密码就进入 MySQL 了
然后就是
>use mysql
>update user set password=password("new_pass") where user="root";
>flush privileges;
重新杀 MySQL ，用正常方法启动 MySQL