* 建庫、建表、添加數據
CREATE DATABASE chapter08;
USE chapter08;
CREATE TABLE student(
id int primary key auto_increment,
name varchar(20),
age int
);
INSERT INTO student(name,age) VALUES ('Tom',20);
INSERT INTO student(name,age) VALUES ('Jack',16);
INSERT INTO student(name,age) VALUES ('Lucy',18);
* 查詢結果
SELECT * FROM student;
#### 【例8-1】
mysqldump -uroot -pitcast chapter08>C:/backup/chapter08_20140305.sql
-----------------------------------------------------------------------------------------
##### 8.1.2 數據的還原
（1） 刪除數據庫
DROP DATABASE chapter08;
（2） 創建數據庫
CREATE DATABASE chapter08;
（3） 還原數據
mysql -uroot -pitcast chapter08 <C:/backup/chapter08_20140305.sql
-----------------------------------------------------------------------------------------
##### 8.2.2 創建普通用戶
* GRANT創建用戶
GRANT SELECT ON chapter08.student TO 'user1'@'localhost' IDENTIFIED BY '123';
* 查看用戶
SELECT host,user,password FROM user;
* CREATE USER語句創建用戶
CREATE USER 'user2'@'localhost' IDENTIFIED BY '123';
* 查看用戶
SELECT host,user,password FROM user;
* INSERT語句創建用戶
INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject)
VALUES('localhost','user3',PASSWORD('123'),'','','');
* 查看用戶
SELECT host,user,password FROM user;
-----------------------------------------------------------------------------------------
##### 8.2.3 刪除普通用戶
* 使用DROP USER語句刪除用戶
DROP USER 'user1'@'localhost';
* DELETE語句刪除用戶
DELETE FROM mysql.user WHERE Host='localhost' AND User='user2';
-----------------------------------------------------------------------------------------
##### 8.2.4 修改用戶密碼
* 使用mysqladmin命令修改root用戶密碼
mysqladmin -u root -p password mypwd1
* 使用UPDATE語句修改root用戶密碼
UPDATE mysql.user SET Password=PASSWORD('mypwd2') WHERE User='root' and Host='localhost';
* 使用SET語句修改root用戶的密碼
SET PASSWORD=password('mypwd3');
* 使用GRANT語句修改普通用戶密碼
GRANT USAFE ON *.* TO 'username'@'localhost' IDENTIFIED BY [PASSWORD]'new_password';
* 使用UPDATE語句修改普通用戶的密碼
UPDATE mysql.user set Password=PASSWORD('new_password')
WHERE User='username' and Host='hostname';
* 使用SET語句修改普通用戶的密碼
SET PASSWORD FOR'username'@'hostname'=PASSWORD('new_password');
* 普通用戶修改密碼
SET PASSWORD=PASSWORD('new_password');
-----------------------------------------------------------------------------------------
##### 8.3.2 授予權限
GRANT INSERT,SELECT ON *.* TO 'user4'@'localhost' IDENTIFIED BY '123'
WITH GRANT OPTION;
* 查看
use mysql;
SELECT Host,User,Password,Insert_priv,Select_priv,Grant_priv FROM mysql.user
WHERE user='user4'\G
* 使用SHOW GRANTS語句查詢root用戶的權限
SHOW GRANTS FOR 'root'@'localhost';
* 使用REVOKE語句收回user4用戶的INSERT權限
REVOKE INSERT ON *.* FROM 'user4'@'localhost';
