實(shí)訓(xùn)案例名稱：用戶管理一、任務(wù)介紹MySQL數(shù)據(jù)庫(kù)的用戶管理包括創(chuàng)建用戶、修改用戶、刪除用戶等操作。下面創(chuàng)建一個(gè)用戶名為xiaogang的用戶，密碼設(shè)置為123456，然后查詢數(shù)據(jù)庫(kù)里有哪些用戶。二、實(shí)現(xiàn)步驟1.創(chuàng)建用戶#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#創(chuàng)建用戶xiaogang，任何IP地址的客戶端都可以訪問(wèn)mysql>CREATEuser'xiaogang'@'%'identifiedby'123456';QueryOK,0rowsaffected(0.22sec)#創(chuàng)建用戶xiaoying，只有本地的客戶端才可以訪問(wèn)mysql>CREATEuser'xiaoying'@'localhost'identifiedby'123456';QueryOK,0rowsaffected(0.00sec)#創(chuàng)建用戶xiaoming，只有指定IP地址0的客戶端才可以訪問(wèn)mysql>CREATEuser'xiaoming'@'0'identifiedby'123456';QueryOK,0rowsaffected(0.00sec)#查詢數(shù)據(jù)庫(kù)有哪些用戶mysql>SELECTuser,hostFROMmysql.user;+------------------+--------------+|user

|host

|+------------------+--------------+|shopdb

|%

||xiaogang

|%

||xiaoming

|0

||schema|localhost

||mysql.session

|localhost

||mysql.sys

|localhost

||root

|localhost

||xiaoying

|localhost

|+------------------+--------------+8rowsinset(0.00sec)使用新創(chuàng)建的用戶xiaogang連接數(shù)據(jù)庫(kù)，語(yǔ)句所示。#使用用戶xiaogang連接數(shù)據(jù)庫(kù)C:\Users\Administrator>mysql-uxiaogang-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.mysql>2.修改用戶下面將用戶為xiaogang的密碼設(shè)置為123456789，并將xiaoying的客戶端ip限制改為00，如下所示。#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#修改用戶xiaogang的密碼為123456789mysql>ALTERUSER'xiaogang'@'%'IDENTIFIEDBY'123456789';QueryOK,1rowaffected,1warning(0.00sec)Rowsmatched:1Changed:1Warnings:1#退出連接mysql>exitBye#使用用戶xiaogang和修改后的密碼進(jìn)行連接，連接失敗C:\Users\Administrator>mysql-uxiaogang-p123456789mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.ERROR1045(28000):Accessdeniedforuser'xiaogang'@'localhost'(usingpassword:YES)#使用root用戶連接C:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#已經(jīng)擁有reload權(quán)限，如果沒(méi)有，則需要授權(quán)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)#退出連接mysql>exitBye#使用用戶xiaogang和修改后的密碼進(jìn)行連接，連接成功C:\Users\Administrator>mysql-uxiaogang-p123456789mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#退出連接mysql>exitBye#使用root用戶連接C:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#修改用戶xiaoying可以訪問(wèn)數(shù)據(jù)庫(kù)的客戶端IP地址mysql>updatemysql.usersethost='00'whereuser='xiaoying';QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0#查看用戶mysql>SELECTuser,hostFROMmysql.user;+-------------------------------+-----------------------------+|user

|host

|+-------------------------------+-----------------------------+|shopdb

|%

||xiaogang

|%

||xiaoying

|00

||xiaoming

|0

||mysql.session

|localhost

||mysql.sys

|localhost

||root

|localhost

|+-------------------------------+-----------------------------+7rowsinset(0.00sec)mysql>3.刪除用戶下面將xiaoying用戶刪除，執(zhí)行刪除命令后，需要執(zhí)行flushprivileges;命令或者重啟一下MySQL服務(wù)，才能生效。#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#查看所有用戶mysql>SELECTuser,hostFROMmysql.user;+-------------------------------+-----------------------------+|user

|host |+-------------------------------+-----------------------------+|shopdb

|% ||xiaogang

|% ||xiaoying

|00 ||xiaoming

|0 ||mysql.session

|localhost ||mysql.sys

|localhost ||root

|localhost |+-------------------------------+-----------------------------+7rowsinset(0.00sec)#刪除用戶xiaoying，host為localhostmysql>DELETEFROMmysql.userWHEREuser='xiaoying'andhost='localhost';QueryOK,0rowsaffected(0.00sec)#查看用戶，用戶xiaoying對(duì)應(yīng)的host是00，并沒(méi)有刪除成功mysql>SELECTuser,hostFROMmysql.user;+-------------------------------+-----------------------------+|user

|host |+-------------------------------+-----------------------------+|shopdb

|% ||xiaogang

|% ||xiaoying

|00 ||xiaoming

|0 ||mysql.session

|localhost ||mysql.sys

|localhost ||root

|localhost |+-------------------------------+-----------------------------+7rowsinset(0.00sec)#刪除用戶xiaoying，host為“00”，有一行數(shù)據(jù)受到影響，說(shuō)明刪除有效mysql>DELETEFROMmysql.userWHEREuser='xiaoying'andhost='00';QueryOK,1rowaffected(0.00sec)#查看所有用戶，發(fā)現(xiàn)用戶xiaoying依然存在mysql>SELECTuser,hostFROMmysql.user;+-------------------------------+-----------------------------+|user

|host |+-------------------------------+-----------------------------+|shopdb

|% ||xiaogang

|% ||xiaoying

|00 ||xiaoming

|0 ||mysql.session

|localhost ||mysql.sys

|localhost ||root

|localhost |+-------------------------------+-----------------------------+7rowsinset(0.00sec)#執(zhí)行刷新操作mysql>flushprivileges;QueryO