第8章 数据库的高级操作

1、让IT教学更简单，让IT学习更有效让IT教学更简单，让IT学习更有效第八章 数据库的高级操作数据的备份用户管理数据的还原权限管理让IT教学更简单，让IT学习更有效 学习目标掌握数据的备份与还原13了解mysql.user表2熟悉数据库的用户管理和权限管理掌握了解熟悉让IT教学更简单，让IT学习更有效 目录数据备份与还原8.18.1用户管理8.28.2权限管理8.38.3点击查看本小节知识架构点击查看本小节知识架构点击查看本小节知识架构让IT教学更简单，让IT学习更有效8.1 数据备份与还原返回目录 知识架构8.1.28.1.1数据的备份数据的还原让IT教学更简单，让IT学习更有效8.2 用户管

2、理返回目录8.2.38.2.4删除普通用户修改用户密码 知识架构8.2.28.2.1user表创建普通用户让IT教学更简单，让IT学习更有效8.3 权限管理返回目录8.3.38.3.4查看权限回收权限 知识架构8.3.28.3.1MySQL的权限授予权限让IT教学更简单，让IT学习更有效8.1 数据备份与还原 8.1.1 数据的备份数据的备份 MySQL提供了一个mysqldump命令，它可以实现数据的备份。 mysqldump命令可以备份单个数据库、多个数据库和所有数据库，具体如下： 1、备份单个数据库、备份单个数据库 mysqldump命令备份数据库的语法格式如下：mysqldump -u

3、username -ppassword dbname tbname1 tbname2filename.sql上述语法格式中，-u后面的参数username表示用户名，-p后面的参数password表示登录密码，dbname表示需要备份的数据库名称，tbname表示数据库中表名，可以指定一个或多个表，多个表名之间用空格分隔，如果不指定则备份整个数据库，filename.sql表示备份文件的名称，文件名前可以加上绝对路径。让IT教学更简单，让IT学习更有效8.1 数据备份与还原 8.1.1 数据的备份数据的备份 需要注意的是，在使用mysqldump命令备份数据库时，直接在Dos命令行窗口执行该命

4、令即可，不需要登录到MySQL数据库。 接下来通过具体的案例来演示如何使用mysqldump命令。 在演示之前创建一个名称为chapter08的数据库，并在数据库中创建表student，插入相应数据，SQL语句如下：让IT教学更简单，让IT学习更有效8.1 数据备份与还原 8.1.1 数据的备份数据的备份 使用SELECT语句查询表student，查询结果如下： 从上述查询结果可以看出，数据添加成功了。 让IT教学更简单，让IT学习更有效8.1 数据备份与还原 8.1.1 数据的备份数据的备份 【例8-1】首先在C盘创建一个名为backup的文件夹用于存放备份好的文件，然后重新开启一个DOS命

5、令行窗口（不用登录到MySQL数据库），使用mysqldump命令备份chapter08数据库，mysqldump语句如下： 上述语句执行成功后，会在backup文件夹中生成一个名为chapter08_20140305.sql的备份文件。mysqldump -uroot -pitcast chapter08C:/backup/chapter08_20140305.sql让IT教学更简单，让IT学习更有效8.1 数据备份与还原 8.1.1 数据的备份数据的备份 使用记事本打开该备份文件，可以看到如下所示的内容：让IT教学更简单，让IT学习更有效8.1 数据备份与还原 8.1.1 数据的备份数据的

6、备份 2、备份多个数据库、备份多个数据库 mysqldump命令不仅可以备份一个数据，还同时可以备份多个数据库，其语法格式如下： 上述语法格式中，“-database”参数后面至少应指定一个数据库名称，如果有多个数据库，则名称之间用空格隔开。mysqldump uusername ppassword -database dbname1 dbname2 dbname3filename.sql让IT教学更简单，让IT学习更有效8.1 数据备份与还原 8.1.1 数据的备份数据的备份 3、备份所有数据库、备份所有数据库 使用mysqldump命令备份所有数据库时，只需在该命令后使用“-all-dat

7、abases”参数即可，其语法格式如下： 需要注意的是，如果使用“-all-databases”参数备份了所有的数据库，那么在还原数据库时，不需要创建数据库并指定要操作的数据库，因为，对应的备份文件中包含了CREATE DATABASE语句和USE语句。mysqldump uusername ppassword -all-databasesfilename.sql让IT教学更简单，让IT学习更有效8.1 数据备份与还原 8.1.2 数据的还原数据的还原 mysql命令还原数据的语法格式如下： 上述语法格式中，username表示登录的用户名，password表示用户的密码，dbname表示要还

8、原的数据库名称，如果使用mysqldump命令备份的filename.sql文件中包含创建数据库的语句，则不需要指定数据库。mysql uusername ppassword dbname filename.sql让IT教学更简单，让IT学习更有效8.1 数据备份与还原 8.1.2 数据的还原数据的还原 我们知道数据库中的库是不能被还原的，因此在还原数据之前必须先创建数据库。接下来通过一个案例来学习数据的还原，具体操作步骤如下： （1） 删除数据库删除数据库 为了演示数据的还原，首先需要使用DROP语句将数据库chapter08删除，具体语句如下：DROP DATABASE chapter08

9、;让IT教学更简单，让IT学习更有效8.1 数据备份与还原 8.1.2 数据的还原数据的还原 上述语句执行成功后，可以使用SHOW DATABASES语句查询数据库，查询结果如下： 从查询结果可以看出，数据库chapter08被成功删除了。让IT教学更简单，让IT学习更有效8.1 数据备份与还原 8.1.2 数据的还原数据的还原 （2） 创建数据库 由于库是不能被还原的，因此先要创建一个数据库chapter08，具体语句如下： 上述语句执行成功后，接下来就可以还原数据库中的数据。CREATE DATABASE chapter08;让IT教学更简单，让IT学习更有效8.1 数据备份与还原 8.1

10、.2 数据的还原数据的还原 （3） 还原数据 使用mysql语句还原C:/backup目录下的chapter08_20140305.sql文件，具体语句如下： 上述语句执行成功后，数据库中的数据就会被还原。mysql -uroot -pitcast chapter08 目录下进行修改。让IT教学更简单，让IT学习更有效8.2 用户管理 8.2.4修改用户密码修改用户密码 在命令行窗口中，使用mysqladmin命令，将root用户的密码修改为mypwd1，SQL语句如下： 上述语句执行成功后，会提示输入密码，具体如下： 需要注意的是，上面提示输入密码，是指root用户的旧密码，密码输入正确后，

11、该语句执行完毕，root用户的密码被修改，下次登录时使用新的密码即可。 可以在命令行窗口中进行验证，使用新密码成功登录了MySQL数据库，说明密码修改成功。mysqladmin -u root -p password mypwd1让IT教学更简单，让IT学习更有效8.2 用户管理 8.2.4修改用户密码修改用户密码 一、修改一、修改root用户的密码用户的密码 2、使用、使用UPDATE语句修改语句修改root用户密码用户密码 由于所有的用户信息都存放在mysql.user表中，因此，只要root用户登录到MySQL服务器，使用UPDATE语句就可以直接修改自己的密码。 UPDATE语句修改密

12、码的语法格式如下：UPDATE mysql.user set Password=PASSWORD(new_password)WHERE User=username and Host=hostname;让IT教学更简单，让IT学习更有效8.2 用户管理 8.2.4修改用户密码修改用户密码 root用户登录到MySQL服务器，通过UPDATE语句将root用户的密码修改为mypwd2，UPDATE语句如下： 上述语句执行成功后，还需使用FLUSH PRIVILEGES重新加载权限表，然后就可以使用新密码登录MySQL数据库。 可以在命令行窗口中验证用户密码是否修改成功，使用新密码成功登录了MySQ

13、L数据库，说明修改成功。UPDATE mysql.user SET Password=PASSWORD(mypwd2) WHERE User=root and Host=localhost;让IT教学更简单，让IT学习更有效8.2 用户管理 8.2.4修改用户密码修改用户密码 二、二、root用户修改普通用户的密码用户修改普通用户的密码 1、使用、使用GRANT语句修改普通用户密码语句修改普通用户密码 GRANT语句的作用比较多，不仅可以创建用户为用户授权，还可以修改用户的密码，通常情况下，为了不影响当前账户的权限，可以使用GRANT USAFE语句修改指定账户的密码。 GRANT语句修改密码

14、的语法格式：GRANT USAFE ON *.* TO usernamelocalhost IDENTIFIED BY PASSWORDnew_password;让IT教学更简单，让IT学习更有效8.2 用户管理 8.2.4修改用户密码修改用户密码 二、二、root用户修改普通用户的密码用户修改普通用户的密码 2、使用、使用UPDATE语句修改普通用户的密码语句修改普通用户的密码 root用户的具有操作数据库的所有权限，因此，它不仅可以使用UPDATE语句修改自己的密码，还可以使用UPDATE语句修改普通用户的密码。 UPDATE语句修改普通用户的密码，语法格式具体如下： 需要注意的是，使用上

15、述语句修改完普通用户的密码后，还需要使用FLUSH PRIVILEGES语句重新加载权限表。UPDATE mysql.user set Password=PASSWORD(new_password)WHERE User=username and Host=hostname;让IT教学更简单，让IT学习更有效8.2 用户管理 8.2.4修改用户密码修改用户密码 二、二、root用户修改普通用户的密码用户修改普通用户的密码 3、使用、使用SET语句修改普通用户的密码语句修改普通用户的密码 前面讲过使用SET不仅可以修改root用户密码，而且还可以修改普通用户密码，在修改普通用户密码时，还需要增加一

16、个FOR子句，指定要修改哪个用户即可。 SET语句修改密码的语法格式如下：SET PASSWORD FORusernamehostname=PASSWORD(new_password);让IT教学更简单，让IT学习更有效8.2 用户管理 8.2.4修改用户密码修改用户密码 三、普通用户修改密码三、普通用户修改密码 普通用户也可以修改自己的密码，这样普通用户就不需要每次修改密码时都通知管理员，普通用户登录到MySQL服务器后，可以通过SET语句来设置自己的密码。 SET语句的基本格式如下： SET语句修改普通用户密码时，和修改root用户是一样的，都需要使用PASSWORD()函数进行加密。SE

17、T PASSWORD=PASSWORD(new_password);让IT教学更简单，让IT学习更有效8.2 用户管理 多学一招：如何解决多学一招：如何解决root用户密码丢失用户密码丢失 停止MySQL服务 使用-skip-grant-tables启动MySQL服务 MySQL服务器中有一个skip-grant-tables选项可以停止MySQL的权限判断，任何用户都可以访问数据库，并且通过该选项也可以启动MySQL服务，在运行对话框中执行如下命令： 登录MySQL服务器 重新开启一个运行对话框，在运行对话框中登录MySQL服务器，具体命令如下：让IT教学更简单，让IT学习更有效8.2 用户

18、管理 多学一招：如何解决多学一招：如何解决root用户密码丢失用户密码丢失 使用UPDATE语句设置root用户密码 加载权限表 MySQL密码设置完成后，还需重新加载权限表，让设置的密码生效，具体语句如下：让IT教学更简单，让IT学习更有效8.3 权限管理 MySQL中的权限信息被存储在mysql数据库的user、db、host、tables_priv、column_priv和procs_priv表中，当MySQL启动时会自动加载这些权限信息，并将这些权限信息读取到内存中。接下来通过表8-2列举一下MySQL的相关权限以及在user表中对应的列和权限范围。 8.3.1MySQL的权限的权限让

19、IT教学更简单，让IT学习更有效8.3 权限管理 表8-2对MySQL的权限以及权限的范围进行了介绍，对于初学者来说可能无法理解，接下来针对表中部分权限进行分析，具体如下： CREATE和DROP权限，可以创建数据库、表、索引，或者删除已有的数据库、表、索引。 INSERT、DELETE、UPDATE 、SELECT权限，可以对数据库中的表进行增删改查操作。 INDEX权限，可以创建或删除索引，适用于所有的表。 ALTER权限，可以用于修改表的结构或重命名表。 GRANT权限，允许为其它用户授权，可用于数据库和表。 FILE权限，被赋予该权限的用户能读写MySQL服务器上的任何文件。 8.3.

20、1MySQL的权限的权限让IT教学更简单，让IT学习更有效8.3 权限管理 8.3.2授予权限授予权限 数据库中的用户拥有不同的权限，合理的授权可以保证数据库的安全。在MySQL中提供了一个GRANT语句，该语句可以为用户授权。 上述语法格式中，privileges表示权限类型，columns参数表示权限作用于某一列，该参数可以省略不写，此时权限作用于整个表，username表示用户名，hostname表示主机名，IDENTIFIED BY参数为用户设置密码，PASSWORD参数为关键字，password为用户的新密码。WITH关键字后面可以带有多个参数with_option。with_opt

21、ion有五个取值，具体如下：lGRANT OPTION：将自己的权限授予其他用户。lMAX_QUERIES_PER_HOUR count：设置每小时最多可以执行多少次（count）查询。lMAX_UPDATES_PER_HOUR count：设置每小时最多可以执行多少次更新。lMAX_CONNECTIONS_PER_HOUR count：设置每小时最大的连接数量。lMAX_USER_CONNECTIONS：设置每个用户最多可以同时建立连接的数量。让IT教学更简单，让IT学习更有效8.3 权限管理 8.3.2授予权限授予权限 使用GRANT语句创建一个新的用户，用户名为user4、密码为123，

22、user4用户对所有数据库有INSERT、SELECT权限，并使用WITH GRANT OPTION子句，GRANT语句如下： 上述语句执行成功后，可以使用SELECT语句来查询user表中的用户权限，查询结果如下：从上述结果可以看出，User的值为user4，Insert_priv、Select_priv、Grant_priv的值都为Y，因此可以说明用户user4对所有数据库具有增加、查询、以及对其他用户赋予相应权限的功能。让IT教学更简单，让IT学习更有效8.3 权限管理 8.3.3查看权限查看权限 为了方便查询用户的权限信息，MySQL还提供了一个SHOW GRANTS语句。SHOW GRANTS的语法格式如下： 从上述语法格式可以看出，SHOW GRANTS语法格式比较简单，只需要指定查询的用户名和主机名即可。SHOW GRANTS FOR usernamehostname;让IT教学更简单，让IT学习更有效8.3 权限管理 8.3.3查看权查看权限限 使用SHOW GRANTS语句查询root用户的权限 root用户拥有所有权限，并且可以为其他用户赋予权限。让IT教学更简单，让IT学习更有效8.3 权限管理 8.3.3查看权查看