Mysql常用语句

•  创建数据库

CREATE DATABASE IF NOT EXISTS $database DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE DATABASE $database DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

• 创建用户并且添加权限

# 只是创建用户
CREATE USER phpergao@'localhost' IDENTIFIED BY 'yourpasswd';

# 赋予权限
GRANT select,update on phpergao.* to phpergao@'localhost';

GRANT index ON phpergao.* TO phpergao@'192.168.0.%';

# 创建用户并赋予权限
GRANT ALL PRIVILEGES ON phpergao.* TO 'phpergao'@'localhost' IDENTIFIED BY 'yourpasswd';

# 相反的revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：
REVOKE ALL PRIVILEGES ON phpergao.* FROM 'phpergao'@'localhost';

# ALL PRIVILEGES 可以换为select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。

# 删除用户
DELETE FROM user WHERE User='phpergao' and Host='localhost';

# 修改用户密码
UPDATE USER SET PASSWORD = PASSWORD ('newpasswd') WHERE USER = 'phpergao' AND HOST = 'localhost';

　　

• 刷新权限

FLUSH PRIVILEGES;

• 查看自己的权限

# 查看自己的权限
SHOW GRANTS;
# 查看其他人的权限
SHOW GRANTS FOR 'phpergao'@'%';

• 建表

DROP TABLE IF EXISTS `table`;  
CREATE TABLE `table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

• 修改密码

#修改配置
[mysqld] 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock 
# ADD
skip-name-resolve 
skip-grant-tables

#重启免登陆
service mysqld restart
mysql

#修改root密码：
UPDATE mysql.user SET Password=password('123456') WHERE User='root';

#修改用户登录host
UPDATE mysql.user SET Host='&' WHERE User='root';