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';