MySQL 用戶管理、授權、修改密碼與常用操作速查表

1. 新建用戶

mysql -u root -p
INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject)
VALUES("localhost","pppadmin",PASSWORD("passwd"),'','','');
FLUSH PRIVILEGES;

2. 用戶授權

CREATE DATABASE phplampDB;
GRANT ALL PRIVILEGES ON phplampDB.* TO phplamp@localhost IDENTIFIED BY '1234';
FLUSH PRIVILEGES;

-- 僅指定部分權限
GRANT SELECT,UPDATE ON phplampDB.* TO phplamp@localhost IDENTIFIED BY '1234';
FLUSH PRIVILEGES;

3. 刪除用戶

DELETE FROM user WHERE User="phplamp" AND Host="localhost";
FLUSH PRIVILEGES;
DROP DATABASE phplampDB;

4. 修改密碼

UPDATE mysql.user SET password=PASSWORD('新密碼') WHERE User="phplamp" AND Host="localhost";
FLUSH PRIVILEGES;

5-9. 常用資料庫/表操作

SHOW DATABASES;
USE '資料庫名';
SHOW TABLES;
DESCRIBE 表名;
DROP DATABASE 資料庫名;
DROP TABLE 資料表名;