MySQL資料庫---資料庫管理

-- 創建用戶，指定明文密碼
create user 'rose'@'localhost' identified by 'rosepwd';

-- 查看用戶是否創建成功
select user,host from mysql.user;

-- 創建用戶，不設置密碼
create user 'rose01'@'localhost';

select user,host from mysql.user;

-- 查看密碼的哈希值
select password('rosepwd');

-- 創建用戶，使用哈希值的密碼
create user 'rose02'@'localhost' identified by password '*15151B36B8E49FD6A6222C4AF15758661CFCE654';

-- 創建用戶，並授予用戶查詢、更新資料庫所有表的許可權
grant select,update on *.* to 'testuser'@'localhost' identified by 'testpwd';

select user,host from mysql.user;

-- 刪除用戶
drop user 'testuser'@'localhost';

select user,host from mysql.user;

-- 刪除用戶
delete from mysql.user where user = 'rose02'and host='localhost';

select user,host from mysql.user;

-- 授予rose用戶插入、查詢book庫所有表的許可權
grant insert,select on book.* to 'rose'@'localhost';

-- 刷新系統許可權表
flush privileges;

-- 授予rose01用戶作用於所有庫的所有表的所有許可權
grant all privileges on *.* to 'rose01'@'localhost';

-- 查看用戶的許可權信息
show grants for 'rose'@'localhost';

show grants for 'rose01'@'localhost';

-- 回收rose用戶的作用於book庫所有表的插入的許可權
revoke insert on book.* from 'rose'@'localhost';

flush privileges;

show grants for 'rose'@'localhost';

-- 查看日誌文件的路徑
show variables like 'log_error';

-- 創建新的日誌信息表
flush logs;

-- 創建新的日誌信息表
mysqladmin -uroot -p flush-logs

-- 備份book庫
mysqldump -uroot -p book >C:\mysqlbackup\book_20180120.sql

-- 備份book庫中的readerinfo表
mysqldump -uroot -p book readerinfo>C:\mysqlbackup\book_readerinfo_20180120.sql

-- 備份book和mytest庫
mysqldump -uroot -p --databases book mytest>C:\mysqlbackup\book_mytest_20180120.sql

-- 備份所有的資料庫
mysqldump -uroot -p --all-databases>C:\mysqlbackup\dball_20180120.sql

-- 恢復book庫下的readerinfo表
mysql -uroot -p book<C:\mysqlbackup\book_readerinfo_20180120.sql

-- 恢復book庫下的readerinfo表
use book;
source C:\mysqlbackup\book_readerinfo_20180120.sql

-- 使用select...into outfile導出readerinfo表的數據到readerinfo.txt這個文件
select * from book.readerinfo into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/readerinfo.txt';

-- 使用select...into outfile導出readerinfo表的數據到readerinfo.txt這個文件，並設置導出文件的格式
select * from book.readerinfo into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/readerinfo1.txt'
fields terminated by ','
lines terminated by '\r\n';

-- 使用mysqldump命令導出book庫下的bookcategory表
mysqldump -T "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads" -uroot -p book bookcategory

-- 使用mysql命令導出book庫下的readerinfo表到readerinfo2.txt
mysql -uroot -p --execute="select * from readerinfo;" book > "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/readerinfo2.txt"

-- 使用load data infile命令導入readerinfo表中的數據
load data infile "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/readerinfo.txt" into table book.readerinfo;

-- 使用mysqlimport命令導入readerinfo表中的數據
mysqlimport -uroot -p book "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/readerinfo.txt"