一、用戶
1、創(chuàng)建用戶

create user zhang3 identified by '123123';
表示創(chuàng)建名稱為zhang3的用戶，密碼設(shè)為123123；

2、user表

查看用戶
select host,user,password,select_priv,insert_priv,drop_priv from mysql.user;

select * from user\G;
將 user 中的數(shù)據(jù)以行的形式顯示出來(lái)(針對(duì)列很長(zhǎng)的表可以采用這個(gè)方法 )

image.png

host ：   表示連接類型
        % 表示所有遠(yuǎn)程通過(guò) TCP方式的連接
        IP 地址 如 (192.168.1.2,127.0.0.1) 通過(guò)制定ip地址進(jìn)行的TCP方式的連接
       機(jī)器名   通過(guò)制定i網(wǎng)絡(luò)中的機(jī)器名進(jìn)行的TCP方式的連接
        ::1   IPv6的本地ip地址  等同于IPv4的 127.0.0.1
        localhost 本地方式通過(guò)命令行方式的連接 ，比如mysql -u xxx -p 123xxx 方式的連接。
User:表示用戶名
       同一用戶通過(guò)不同方式鏈接的權(quán)限是不一樣的。
  
password ： 密碼
       所有密碼串通過(guò) password(明文字符串) 生成的密文字符串。加密算法為MYSQLSHA1 ，不可逆 。
        mysql 5.7 的密碼保存到 authentication_string 字段中不再使用password 字段。
 
  select_priv , insert_priv等 
        為該用戶所擁有的權(quán)限。

3、設(shè)置密碼

修改當(dāng)前用戶的密碼:
set password =password('123456')
 
修改某個(gè)用戶的密碼:
update mysql.user set password=password('123456') where user='li4';
flush privileges;   #所有通過(guò)user表的修改，必須用該命令才能生效。

4、修改用戶

image.png

update mysql.user set user='li4' where user='wang5';
flush privileges;   #所有通過(guò)user表的修改，必須用該命令才能生效。

5、刪除用戶

image.png

drop user li4 ;

不要通過(guò)delete from  user u where user='li4' 進(jìn)行刪除，系統(tǒng)會(huì)有殘留信息保留。 

二、權(quán)限
1、授予權(quán)限

授權(quán)命令： 
grant 權(quán)限1,權(quán)限2,…權(quán)限n on 數(shù)據(jù)庫(kù)名稱.表名稱 to 用戶名@用戶地址 identified by ‘連接口令’;
該權(quán)限如果發(fā)現(xiàn)沒(méi)有該用戶，則會(huì)直接新建一個(gè)用戶。
比如  
grant select,insert,delete,drop on atguigudb.* to li4@localhost  ;
 #給li4用戶用本地命令行方式下，授予atguigudb這個(gè)庫(kù)下的所有表的插刪改查的權(quán)限。
 
grant all privileges on *.* to joe@'%'  identified by '123'; 
#授予通過(guò)網(wǎng)絡(luò)方式登錄的的joe用戶 ，對(duì)所有庫(kù)所有表的全部權(quán)限，密碼設(shè)為123.
就算 all privileges 了所有權(quán)限，grant_priv 權(quán)限也只有 root 才能擁有。
 
給 root 賦連接口令 grant all privileges on *.* to root@'%'  ;后新建的連接沒(méi)有密碼，需要設(shè)置密碼才能遠(yuǎn)程連接。
update user set password=password('root') where user='root' and host='%';

2、收回權(quán)限

授權(quán)命令： 
revoke  權(quán)限1,權(quán)限2,…權(quán)限n on 數(shù)據(jù)庫(kù)名稱.表名稱  from  用戶名@用戶地址 ;
 
REVOKE ALL PRIVILEGES ON mysql.* FROM joe@localhost;
#若賦的全庫(kù)的表就 收回全庫(kù)全表的所有權(quán)限
 
REVOKE select,insert,update,delete ON mysql.* FROM joe@localhost;
#收回mysql庫(kù)下的所有表的插刪改查權(quán)限
 對(duì)比賦予權(quán)限的方法。
 必須用戶重新登錄后才能生效

3、查看權(quán)限

查看當(dāng)前用戶權(quán)限
show grants;
 
查看某用戶的全局權(quán)限
select  * from user ;
 
查看某用戶的某庫(kù)的權(quán)限
select * from  db;
 
查看某用戶的某個(gè)表的權(quán)限
select * from tables_priv;

三、雜項(xiàng)
1、大小寫問(wèn)題

 SHOW VARIABLES LIKE '%lower_case_table_names%' 

image.png

windows系統(tǒng)默認(rèn)大小寫不敏感，但是linux系統(tǒng)是大小寫敏感的
默認(rèn)為0，大小寫敏感。
設(shè)置1，大小寫不敏感。創(chuàng)建的表，數(shù)據(jù)庫(kù)都是以小寫形式存放在磁盤上，對(duì)于sql語(yǔ)句都是轉(zhuǎn)換為小寫對(duì)表和DB進(jìn)行查找。
設(shè)置2，創(chuàng)建的表和DB依據(jù)語(yǔ)句上格式存放，凡是查找都是轉(zhuǎn)換為小寫進(jìn)行。
設(shè)置變量常采用 set lower_case_table_names = 1； 的方式，但此變量是只讀權(quán)限，所以需要在配置文件中改。
當(dāng)想設(shè)置為大小寫不敏感時(shí)，要在my.cnf這個(gè)配置文件 [mysqld] 中加入 lower_case_table_names = 1 ，然后重啟服務(wù)器。
但是要在重啟數(shù)據(jù)庫(kù)實(shí)例之前就需要將原來(lái)的數(shù)據(jù)庫(kù)和表轉(zhuǎn)換為小寫，否則更改后將找不到數(shù)據(jù)庫(kù)名。
在進(jìn)行數(shù)據(jù)庫(kù)參數(shù)設(shè)置之前，需要掌握這個(gè)參數(shù)帶來(lái)的影響，切不可盲目設(shè)置。

2、sql_mode

MySQL的sql_mode合理設(shè)置
sql_mode是個(gè)很容易被忽視的變量，默認(rèn)值是空值，在這種設(shè)置下是可以允許一些非法操作的，比如允許一些非法數(shù)據(jù)的插入。在生產(chǎn)環(huán)境必須將這個(gè)值設(shè)置為嚴(yán)格模式，所以開發(fā)、測(cè)試環(huán)境的數(shù)據(jù)庫(kù)也必須要設(shè)置，這樣在開發(fā)測(cè)試階段就可以發(fā)現(xiàn)問(wèn)題。

image.png

使用 set sql_mode=ONLY_FULL_GROUP_BY; 的方式設(shè)置會(huì)將之前的設(shè)置覆蓋掉
同時(shí)設(shè)置多個(gè)限制：set sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO';
 
sql_mode常用值如下： 
ONLY_FULL_GROUP_BY：
對(duì)于GROUP BY聚合操作，如果在SELECT中的列，沒(méi)有在GROUP BY中出現(xiàn)，那么這個(gè)SQL是不合法的，因?yàn)榱胁辉贕ROUP BY從句中
 
NO_AUTO_VALUE_ON_ZERO：
該值影響自增長(zhǎng)列的插入。默認(rèn)設(shè)置下，插入0或NULL代表生成下一個(gè)自增長(zhǎng)值。如果用戶 希望插入的值為0，而該列又是自增長(zhǎng)的，那么這個(gè)選項(xiàng)就有用了。
 
STRICT_TRANS_TABLES：
在該模式下，如果一個(gè)值不能插入到一個(gè)事務(wù)表中，則中斷當(dāng)前的操作，對(duì)非事務(wù)表不做限制
NO_ZERO_IN_DATE：
在嚴(yán)格模式下，不允許日期和月份為零
 
NO_ZERO_DATE：
設(shè)置該值，mysql數(shù)據(jù)庫(kù)不允許插入零日期，插入零日期會(huì)拋出錯(cuò)誤而不是警告。
 
ERROR_FOR_DIVISION_BY_ZERO：
在INSERT或UPDATE過(guò)程中，如果數(shù)據(jù)被零除，則產(chǎn)生錯(cuò)誤而非警告。如 果未給出該模式，那么數(shù)據(jù)被零除時(shí)MySQL返回NULL
 
NO_AUTO_CREATE_USER：
禁止GRANT創(chuàng)建密碼為空的用戶
 
NO_ENGINE_SUBSTITUTION：
如果需要的存儲(chǔ)引擎被禁用或未編譯，那么拋出錯(cuò)誤。不設(shè)置此值時(shí)，用默認(rèn)的存儲(chǔ)引擎替代，并拋出一個(gè)異常
 
PIPES_AS_CONCAT：
將"||"視為字符串的連接操作符而非或運(yùn)算符，這和Oracle數(shù)據(jù)庫(kù)是一樣的，也和字符串的拼接函數(shù)Concat相類似
 
ANSI_QUOTES：
啟用ANSI_QUOTES后，不能用雙引號(hào)來(lái)引用字符串，因?yàn)樗唤忉尀樽R(shí)別符
 
ORACLE：
  設(shè)置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.