* 建库、建表、添加数据
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';
