常用SQL語句，看這篇就夠了

一、摘要

本文主要以 Mysql 數(shù)據(jù)庫為基礎，對常用 SQL 語句進行一次深度總結，由于篇幅較長，難免會有些遺漏的地方，歡迎網(wǎng)友批評指出！

具體內容主要有以下幾個部分：

• 庫操作
• 表操作
• 數(shù)據(jù)操作
• 運算符
• 視圖
• 函數(shù)
• 存儲過程
• 觸發(fā)器
• 序列
• 用戶權限

二、庫操作

2.1、新增庫

創(chuàng)建數(shù)據(jù)庫比較簡單，在創(chuàng)建的時候直接指定字符集、排序規(guī)則即可！

CREATE?DATABASE?IF?NOT?EXISTS?`庫名`?default?charset?utf8mb4?COLLATE?utf8mb4_unicode_ci;

例子：

CREATE?DATABASE?IF?NOT?EXISTS?test_db?default?charset?utf8mb4?COLLATE?utf8mb4_unicode_ci;

2.2、修改庫名

數(shù)據(jù)庫修改庫名的有三種方法，如果是MyISAM存儲引擎，那么可以直接去數(shù)據(jù)庫目錄mv就可以了，如果是Innodb完全不行，會提示相關表不存在。

方法一

RENAME?database?olddbname?TO?newdbname

這個語法在 mysql-5.1.7 中被添加進來，到了mysql-5.1.23又去掉了，官方不推薦，會有丟失數(shù)據(jù)的危險！

方法二

思路是先創(chuàng)建一個新庫，之后將舊庫的數(shù)據(jù)導入到新庫，即可完成修改庫名！

• 1、創(chuàng)建需要改成新名的數(shù)據(jù)庫。
• 2、mysqldum 導出要改名的數(shù)據(jù)庫
• 3、刪除原來的舊庫（確定是否真的需要）

當然這種方法雖然安全，但是如果數(shù)據(jù)量大，會比較耗時，同時還需要考慮到磁盤空間等硬件成本。

例子：

#?將db1庫備份到db1.sql文件
mysqldump?-u?root?-p?db1?>?/usr/db1.sql;

#?導入備份文件到新庫db2
mysql?-u?root?-p?db2?
#?刪除舊庫（如果真的需要）
DROP?DATABASE?db1;

方法三

直接跑一個 shell 腳本！

#!/bin/bash
#?假設將db1數(shù)據(jù)庫名改為db2
#?MyISAM直接更改數(shù)據(jù)庫目錄下的文件即可

mysql?-uroot?-p123456?-e?'create?database?if?not?exists?db2'
list_table=$(mysql?-uroot?-p123456?-Nse?"select?table_name?from?information_schema.TABLES?where?TABLE_SCHEMA='db1'")

for?table?in?$list_table
do
????mysql?-uroot?-p123456?-e?"rename?table?db1.$table?to?db2.$table"
done

其中p123456，p是password的簡稱，123456表示數(shù)據(jù)庫密碼值！

2.3、刪除庫名

刪除庫，比較簡單，直接刪除即可！

DROP?DATABASE?db1;

2.4、使用庫

USE?db2;

三、表操作

3.1、創(chuàng)建表

CREATE?TABLE?ts_user?(
??id?bigint(20)?unsigned?NOT?NULL?COMMENT?'編碼',
??name?varchar(100)?COLLATE?utf8mb4_unicode_ci?NOT?NULL?DEFAULT?''?COMMENT?'用戶姓名',
??mobile?varchar(11)?COLLATE?utf8mb4_unicode_ci?DEFAULT?''?COMMENT?'手機號',
??create_userid?varchar(32)?COLLATE?utf8mb4_unicode_ci?NOT?NULL?DEFAULT?''?COMMENT?'創(chuàng)建人',
??create_time?datetime?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?COMMENT?'創(chuàng)建時間',
??update_userid?varchar(32)?COLLATE?utf8mb4_unicode_ci?NOT?NULL?DEFAULT?''?COMMENT?'更新人',
??update_time?datetime?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'更新時間',
??PRIMARY?KEY?(id),
??KEY?idx_create_time?(create_time)?USING?BTREE
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?COLLATE=utf8mb4_unicode_ci?COMMENT='用戶表';

3.2、修改表名

ALTER??TABLE?ts_user?RENAME?TO?ts_new_user;

3.3、刪除表

DROP?TABLE?ts_new_user;

3.4、字段操作

3.4.1、查詢表字段

show?full?columns?from?ts_user;

3.4.2、新增字段

ALTER?TABLE?ts_user?add?column?gender?tinyint(4)?NOT?NULL?DEFAULT?'1'?COMMENT?'性別，1，男；2，女'?AFTER?mobile;

3.4.3、修改字段

ALTER?TABLE?ts_user?modify?column?mobile?varchar(30)?NOT?NULL?DEFAULT?''?COMMENT?'用戶手機號';

3.4.4、刪除字段

ALTER?TABLE?ts_user?drop?column?gender;

3.5、索引操作

3.5.1、查詢表索引

?SHOW?INDEXES?FROM?ts_user;

3.5.2、新增普通索引

alter?table?ts_user?add?index?idx_id?(id);

3.5.3、新增唯一索引

alter?table?ts_user?add?unique?idx_id?(id);

3.5.4、新增主鍵索引

alter?table?ts_user?add?primary?key?idx_id?(id)?;

3.5.5、新增多列索引

alter?table?ts_user?add?index?idx_id_name?(id,name)?;

3.5.6、新增全文索引

alter?table?ts_user?add?fulltext?idx_id?(id)?;

3.5.7、刪除索引

#?刪除普通索引
alter?table?ts_user?drop?index?idx_id;

#?刪除主鍵索引
alter?table?ts_user?drop?primary?key;

四、數(shù)據(jù)操作

4.1、查詢操作

4.1.1、單表查詢

select?*?from?ts_user;

或者

select?id,?name?from?ts_user;

4.1.2、關鍵字查詢

• and 查詢

select?id,?name?from?ts_user?where?name?=?'張三'

• or 查詢

select?id,?name?from?ts_user?where?name?=?'張三'?or?name?=?'李四'

• in 查詢（參數(shù)個數(shù)不能超過1000）

select?id,?name?from?ts_user?where?name?in?('張三',?'李四')

• like 模糊查詢（%屬于通配符）

select?id,?name?from?ts_user?where?name?like?'張%'

• 非空查詢

select?id,?name?from?ts_user?where?name?is?not?null

• 區(qū)間字段查詢

select?id,?name,?age?from?ts_user?where??age?>=?18?and?age?<=?30

select?id,?name,?age?from?ts_user?where?age?between?18?and?30

• 多條件判斷

select?
name,
(
case
when?scope?>=?90?then??'優(yōu)'
when?80?<=?scope?then??'良'
when?80?>?scope?>=?70??then??'中'
else?'差'
end
)?as?judge
from?ts_user

4.1.3、連表查詢

• 左連接查詢

select?tu.id,?tu.name,tr.role_name
from?ts_user?tu
left?join?ts_role?tr?on?tu.id?=?tr.user_id

• 右連接查詢

select?tu.id,?tu.name,tr.role_name
from?ts_user?tu
right?join?ts_role?tr?on?tu.id?=?tr.user_id

• 內連接查詢

select?tu.id,?tu.name,tr.role_name
from?ts_user?tu
inner?join?ts_role?tr?on?tu.id?=?tr.user_id

• 滿連接查詢

select?tu.id,?tu.name,tr.role_name
from?ts_user?tu
full?join?ts_role?tr?on?tu.id?=?tr.user_id

4.1.4、分組查詢

• 統(tǒng)計學生總數(shù)

select?count(id)?from?ts_user

• 查詢學生最大的年紀

select?max(age)?from?ts_user

• 查詢學生最大的年紀

select?min(age)?from?ts_user

• 查詢各個學生各項成績的總和

select?id,?sum(score)?from?ts_user?group?by?id

• 查詢各個學生各項成績的平均分

select?id,?avg(score)?from?ts_user?group?by?id

• 查詢各個學生各項成績的平均分大于100的學生信息

select?id,?avg(score)?from?ts_user?group?by?id?having?avg(score)??>?100

4.2、插入操作

4.2.1、單列插入

INSERT?INTO?ts_user(id,?name)?VALUES?('1',?'張三');

4.2.2、多列插入

INSERT?INTO?ts_user(id,?name)
VALUES
('1',?'張三'),
('2',?'李四'),
('3',?'王五');

4.3、修改操作

update?ts_user?set?name?=?'李四1',?age?=?'18'?where?id?=?'1'

4.4、 刪除操作

#?刪除表全部內容
delete?from?ts_user

#?根據(jù)判斷條件進行刪除
delete?from?ts_user?where?id?=?'1'

五、運算符

MySQL 主要有以下幾種運算符：

• 算術運算符
• 比較運算符
• 邏輯運算符
• 位運算符

5.1、算術運算符

說明：在除法運算和模運算中，如果除數(shù)為0，將是非法除數(shù)，返回結果為NULL。

5.2、比較運算符

SELECT 語句中的條件語句經常要使用比較運算符。通過這些比較運算符，可以判斷表中的哪些記錄是符合條件的。比較結果為真，則返回 1，為假則返回 0，比較結果不確定則返回 NULL。

說明：mysql中，IN 語句中參數(shù)個數(shù)是不限制的。不過對整段 sql 語句的長度有了限制，最大不超過 4M！

5.3、邏輯運算符

邏輯運算符用來判斷表達式的真假。如果表達式是真，結果返回 1。如果表達式是假，結果返回 0。

5.4、位運算符

位運算符是在二進制數(shù)上進行計算的運算符。位運算會先將操作數(shù)變成二進制數(shù)，進行位運算。然后再將計算結果從二進制數(shù)變回十進制數(shù)。

5.5、運算符優(yōu)先級

說明：在無法確定優(yōu)先級的情況下，可以使用圓括號()來改變優(yōu)先級，并且這樣會使計算過程更加清晰。

六、視圖

視圖（view）是一種虛擬存在的表，是一個邏輯表，本身并不包含數(shù)據(jù)。作為一個select語句保存在數(shù)據(jù)字典中的。

6.1、創(chuàng)建視圖

CREATE?[OR?REPLACE]?[ALGORITHM?=?{UNDEFINED?|?MERGE?|?TEMPTABLE}]
????VIEW?view_name?[(column_list)]
????AS?select_statement
???[WITH?[CASCADED?|?LOCAL]?CHECK?OPTION]

參數(shù)說明：

• OR REPLACE：表示替換已有視圖
• ALGORITHM：表示視圖選擇算法，默認算法是UNDEFINED(未定義的)：MySQL自動選擇要使用的算法 ；merge合并；temptable臨時表
• select_statement：表示select語句
• [WITH [CASCADED | LOCAL] CHECK OPTION]：表示視圖在更新時保證在視圖的權限范圍之內
• cascade：是默認值，表示更新視圖的時候，要滿足視圖和表的相關條件
• local：表示更新視圖的時候，要滿足該視圖定義的一個條件即可

基本格式：

create?view?<視圖名稱>[(column_list)]
???????as?select語句
???????with?check?option;

創(chuàng)建視圖示例：

create?view?v_user(用戶名,年齡)
as
select?user_name,age?from?t_user
with?check?option;

6.2、查看視圖

• 使用show create view語句查看視圖信息

show?create?view?v_user;

• 視圖一旦創(chuàng)建完畢，就可以像一個普通表那樣使用，視圖主要用來查詢

select?*?from?v_user;

6.3、刪除視圖

刪除視圖是指刪除數(shù)據(jù)庫中已存在的視圖，刪除視圖時，只能刪除視圖的定義，不會刪除數(shù)據(jù)，也就是說不動基表：

DROP?VIEW?[IF?EXISTS]???
view_name?[,?view_name]?...

刪除示例：

drop?view?IF?EXISTS?v_user;

七、函數(shù)

7.1、常用函數(shù)列表

7.2、自定義函數(shù)語法介紹

7.2.1、創(chuàng)建函數(shù)

CREATE?FUNCTION?fn_name(func_parameter[,...])
RETURNS?type
[characteristic...]
routine_body

參數(shù)說明：

• fn_name：自定義函數(shù)名稱
• func_parameter: ?param_name type
• type: 任何mysql支持的類型
• characteristic: LANGUAGE SQL
• routine_body: 函數(shù)體

7.2.2、編輯函數(shù)

ALTER?FUNCTION?fn_name?[characteristic...]

參數(shù)說明：

• fn_name：自定義函數(shù)名稱
• func_parameter: ?param_name type
• characteristic: LANGUAGE SQL

7.2.3、刪除函數(shù)

DROP?FUNCTION??[IF?EXISTS]??fn_name;

參數(shù)說明：

• fn_name：自定義函數(shù)名稱
• func_parameter: ?param_name type

7.2.4、查看函數(shù)語法

SHOW?FUNCTION?STATUS?[LIKE?'pattern']

參數(shù)說明：

• pattern：函數(shù)名稱

示例：

SHOW?FUNCTION?STATUS?LIKE?'user_function';

7.2.5、查看函數(shù)的定義語法

SHOW?CREATE?FUNCTION?fn_name;

參數(shù)說明：

• fn_name：自定義函數(shù)名稱

7.3、實例操作介紹

7.3.1、創(chuàng)建一個表

CREATE?TABLE?`t_user`?(
??`user_id`?int(10)?NOT?NULL?AUTO_INCREMENT?COMMENT?'用戶id,作為主鍵',
??`user_name`?varchar(5)?DEFAULT?NULL?COMMENT?'用戶名',
??`age`?int(3)?DEFAULT?NULL?COMMENT?'年齡',
??PRIMARY?KEY?(`user_id`)
)?ENGINE=InnoDB?AUTO_INCREMENT=1?DEFAULT?CHARSET=utf8;

7.3.2、插入數(shù)據(jù)

INSERT?INTO?t_user?(user_name,?age)
VALUES('張三',24),('李四',25),('王五',26),('趙六',27);

7.3.3、創(chuàng)建函數(shù)

--?創(chuàng)建一個函數(shù)
DELIMITER?$$

--?開始創(chuàng)建函數(shù)
CREATE?FUNCTION?user_function(v_id?INT)
RETURNS?VARCHAR(50)
READS?SQL?DATA
DETERMINISTIC
BEGIN
??--?定義變量
??DECLARE?userName?VARCHAR(50);
??--?給定義的變量賦值
??SELECT?user_name?INTO?userName?FROM?t_user
??WHERE?user_id?=?v_id;
??--?返回函數(shù)處理結果
??RETURN?userName;
END;

--?函數(shù)創(chuàng)建定界符
DELIMITER?;

7.3.4、調用函數(shù)

//查詢用戶ID為1的信息
SELECT?user_function(1);

7.3.5、刪除函數(shù)

DROP?FUNCTION??IF?EXISTS??user_function;

八、存儲過程

8.1、創(chuàng)建語法

CREATE?PROCEDURE?存儲過程名([[IN?|OUT?|INOUT?]?參數(shù)名?數(shù)據(jù)類形...])

過程與創(chuàng)建函數(shù)類似，其中的聲明語句結束符，可以自定義:

DELIMITER?$$
或
DELIMITER?//

參數(shù)說明：

• IN 輸入?yún)?shù)：表示該參數(shù)的值必須在調用存儲過程時指定，在存儲過程中修改該參數(shù)的值不能被返回，為默認值
• OUT 輸出參數(shù)：該值可在存儲過程內部被改變，并可返回
• INOUT 輸入輸出參數(shù)：調用時指定，并且可被改變和返回

創(chuàng)建一個查詢用戶信息的存儲過程示例：

DELIMITER?$$
CREATE?PROCEDURE?user_procedure(IN?v_id?int,OUT?userName?varchar(255))??
????BEGIN
????SELECT?user_name?as?userName?FROM?t_user?WHERE?user_id?=?v_id;
????END?$$?
DELIMITER?;

8.2、存儲過程調用

--?@out為輸出參數(shù)
CALL?user_procedure(1,?@out);

輸出結果：

張三

8.3、存儲過程刪除

DROP?PROCEDURE?[IF?EXISTS]??proc_name;

刪除示例：

DROP?PROCEDURE?IF?EXISTS??user_procedure;

8.4、存儲過程和函數(shù)的區(qū)別

• 函數(shù)只能通過return語句返回單個值或者表對象。而存儲過程不允許執(zhí)行return，但是通過out參數(shù)返回多個值。
• 函數(shù)是可以嵌入在sql中使用的,可以在select中調用，而存儲過程不行。
• 函數(shù)限制比較多，比如不能用臨時表，只能用表變量，還有一些函數(shù)都不可用等等，而存儲過程的限制相對就比較少
• 一般來說，存儲過程實現(xiàn)的功能要復雜一點，而函數(shù)的實現(xiàn)的功能針對性比較強。
• 當存儲過程和函數(shù)被執(zhí)行的時候，SQL Manager會到procedure cache中去取相應的查詢語句，如果在procedure cache里沒有相應的查詢語句，SQL Manager就會對存儲過程和函數(shù)進行編譯。

九、觸發(fā)器

觸發(fā)器是與表有關的數(shù)據(jù)庫對象，在滿足定義條件時觸發(fā)，并執(zhí)行觸發(fā)器中定義的語句集合。

9.1、創(chuàng)建觸發(fā)器

定義語法

CREATE
????[DEFINER?=?{?user?|?CURRENT_USER?}]
TRIGGER?trigger_name
trigger_time?trigger_event
ON?tbl_name?FOR?EACH?ROW
??[trigger_order]
trigger_body
trigger_time:?{?BEFORE?|?AFTER?}
trigger_event:?{?INSERT?|?UPDATE?|?DELETE?}
trigger_order:?{?FOLLOWS?|?PRECEDES?}?other_trigger_name

參數(shù)說明：

• FOR EACH ROW：表示任何一條記錄上的操作滿足觸發(fā)事件都會觸發(fā)該觸發(fā)器，也就是說觸發(fā)器的觸發(fā)頻率是針對每一行數(shù)據(jù)觸發(fā)一次。

• trigger_time：BEFORE和AFTER參數(shù)指定了觸發(fā)執(zhí)行的時間，在事件之前或是之后。

• tigger_event詳解：

• INSERT 型觸發(fā)器：插入某一行時激活觸發(fā)器，可能通過INSERT、LOAD DATA、REPLACE 語句觸發(fā)(LOAD DAT語句用于將一個文件裝入到一個數(shù)據(jù)表中，相當與一系列的INSERT操作)；

• UPDATE型觸發(fā)器：更改某一行時激活觸發(fā)器，可能通過UPDATE語句觸發(fā)；

• DELETE型觸發(fā)器：刪除某一行時激活觸發(fā)器，可能通過DELETE、REPLACE語句觸發(fā)。

• trigger_order：是MySQL5.7之后的一個功能，用于定義多個觸發(fā)器，使用follows(尾隨)或precedes(在…之先)來選擇觸發(fā)器執(zhí)行的先后順序。

示例，創(chuàng)建了一個名為trig1的觸發(fā)器，一旦在t_user表中有插入動作，就會自動往t_time表里插入當前時間。

CREATE?TRIGGER?trig1?AFTER?INSERT
ON?t_user?FOR?EACH?ROW
INSERT?INTO?t_time?VALUES(NOW());

創(chuàng)建有多個執(zhí)行語句的觸發(fā)器語法

CREATE?TRIGGER?觸發(fā)器名?BEFORE|AFTER?觸發(fā)事件
ON?表名?FOR?EACH?ROW
BEGIN
????????執(zhí)行語句列表
END;

示例如下：

DELIMITER?//
CREATE?TRIGGER?trig2?AFTER?INSERT
ON?t_user?FOR?EACH?ROW
BEGIN
INSERT?INTO?t_time?VALUES(NOW());
INSERT?INTO?t_time?VALUES(NOW());
END//
DELIMITER?;

一旦插入成功，就會執(zhí)行BEGIN ...END語句！

9.2、查詢觸發(fā)器

• 查詢所有觸發(fā)器

SHOW?TRIGGERS;

• 查詢指定的觸發(fā)器

select?*?from?information_schema.triggers?where?trigger_name='trig1';

所有觸發(fā)器信息都存儲在information_schema數(shù)據(jù)庫下的triggers表中，可以使用SELECT語句查詢，如果觸發(fā)器信息過多，最好通過TRIGGER_NAME字段指定查詢。

9.3、刪除觸發(fā)器

DROP?TRIGGER?[IF?EXISTS]?[schema_name.]trigger_name

示例如下：

DROP?TRIGGER?IF?EXISTS?trig1

刪除觸發(fā)器之后最好使用上面的方法查看一遍。

9.4、總結

觸發(fā)器盡量少的使用，因為不管如何，它還是很消耗資源，如果使用的話要謹慎的使用，確定它是非常高效的：觸發(fā)器是針對每一行的；對增刪改非常頻繁的表上切記不要使用觸發(fā)器，因為它會非常消耗資源。

10、序列

在 MySQL 中，可以有如下幾種途徑實現(xiàn)唯一值：

• 自增序列
• 程序自定義
• UUID() 函數(shù)
• UUID_SHORT() 函數(shù)

10.1、自增序列

在mysql中，一般我們可以給某個主鍵字段設置為自增模式，例如：

#創(chuàng)建一個表test_db，字段內容為id,name
create?table?test_db(id?int,name?char(10));

#?設置id主鍵
alter?table?test_db?add?primary?key(id);

#?將id主鍵設置為自增長模式
alter?table?test_db?modify?id?int?auto_increment;

這種模式，在單庫單表的時候，沒啥問題，但是如果要對test_db表進行分庫分表，這個時候問題就來了，如果水平分庫，這個時候向test_db_1、test_db_2中插入數(shù)據(jù)，就會出現(xiàn)相同的ID！

10.2、程序自定義

當然，為了避免出現(xiàn)這種情況，有的大神就自己單獨創(chuàng)建了一張自增序列表，單獨維護，這樣就不會出現(xiàn)在分表的時候出現(xiàn)相同的ID！

實現(xiàn)過程也很簡單！

• 1、創(chuàng)建一個序列表

CREATE?TABLE?`sequence`?(
??`name`?varchar(50)?COLLATE?utf8_bin?NOT?NULL?COMMENT?'序列的名字',
??`current_value`?int(11)?NOT?NULL?COMMENT?'序列的當前值',
??`increment`?int(11)?NOT?NULL?DEFAULT?'1'?COMMENT?'序列的自增值',
??PRIMARY?KEY?(`name`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8?COLLATE=utf8_bin;

• 2、創(chuàng)建–取當前值的函數(shù)

BEGIN?
?????DECLARE?value?INTEGER;?
?????SET?value?=?0;?
?????SELECT?current_value?INTO?value?
??????????FROM?sequence?
??????????WHERE?name?=?seq_name;?
?????RETURN?value;?
END

• 3、創(chuàng)建–取下一個值的函數(shù)

DROP?FUNCTION?IF?EXISTS?nextval;?
DELIMITER?$?
CREATE?FUNCTION?nextval?(seq_name?VARCHAR(50))?
?????RETURNS?INTEGER?
?????LANGUAGE?SQL?
?????DETERMINISTIC?
?????CONTAINS?SQL?
?????SQL?SECURITY?DEFINER?
?????COMMENT?''?
BEGIN?
?????UPDATE?sequence?
??????????SET?current_value?=?current_value?+?increment?
??????????WHERE?name?=?seq_name;?
?????RETURN?currval(seq_name);?
END?
$?
DELIMITER?;

• 4、創(chuàng)建–更新當前值的函數(shù)

DROP?FUNCTION?IF?EXISTS?setval;?
DELIMITER?$?
CREATE?FUNCTION?setval?(seq_name?VARCHAR(50),?value?INTEGER)?
?????RETURNS?INTEGER?
?????LANGUAGE?SQL?
?????DETERMINISTIC?
?????CONTAINS?SQL?
?????SQL?SECURITY?DEFINER?
?????COMMENT?''?
BEGIN?
?????UPDATE?sequence?
??????????SET?current_value?=?value?
??????????WHERE?name?=?seq_name;?
?????RETURN?currval(seq_name);?
END?
$?
DELIMITER?;

• 最后，直接通過函數(shù)調用，測試如下

#?添加一個sequence名稱和初始值，以及自增幅度
INSERT?INTO?sequence?VALUES?('testSeq',?0,?1);

#設置指定sequence的初始值
SELECT?SETVAL('testSeq',?10);

#查詢指定sequence的當前值
SELECT?CURRVAL('testSeq');

#查詢指定sequence的下一個值
SELECT?NEXTVAL('testSeq');

這方案，某種情況下解決了分表的問題，但是如果分庫還是會出現(xiàn)相同的ID！

10.3、UUID() 函數(shù)

UUID 基于 16 進制，由 32 位小寫的 16 進制數(shù)字組成，如下：

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

比如d0c754a8-178e-11eb-ae3d-2a7bea22ed3d就是一個典型的 UUID。

在 MySQL 的UUID()函數(shù)中，前三組數(shù)字從時間戳中生成，第四組數(shù)字暫時保持時間戳的唯一性，第五組數(shù)字是一個IEEE 802節(jié)點標點值，保證空間唯一。

使用 UUID() 函數(shù)，可以生成時間、空間上都獨一無二的值。據(jù)說只要是使用了 UUID，都不可能看到兩個重復的 UUID 值。當然，這個只是在理論情況下。

使用方法也很簡單，在sql可以直接當成函數(shù)調用即可！

select?uuid();

10.4、UUID_SHORT() 函數(shù)

在 MySQL 5.1 之后的版本，提供UUID_SHORT()函數(shù)，生成一個64位無符號整數(shù)，在java中可以用Long類型接受。另外，需要注意的是，server_id 的范圍必須為0-255，并且不支持 STATEMENT模式復制，否則有可能會產生重復的ID

select?UUID_SHORT();

同時，需要注意的是，UUID_SHORT()返回的是unsigned long long類型，在字段類型設置的時候，一定要勾選無符號類型，否則有可能生成的ID超過Long類型最大長度！

11、用戶權限

11.1、用戶管理

• 查詢所有用戶

select?*?from?mysql.user;

• 創(chuàng)建用戶

#?格式
CREATE?USER?'username'@'host'?IDENTIFIED?BY?'password';

#?例子，創(chuàng)建一個用戶名為admin,密碼123456，可以本地訪問的用戶
CREATE?USER?'admin'@'localhost'?IDENTIFIED?BY?'123456';

• 更改用戶密碼

#?格式
SET?PASSWORD?FOR?'username'@'host'?=?PASSWORD('newpassword');

#?例子，將用戶名admin,密碼修改為456789，可以本地訪問的用戶
SET?PASSWORD?FOR?'admin'@'localhost'?=?PASSWORD("456789");

• 刪除用戶

#?格式
DROP?USER?'username'@'host';

#?例子，刪除用戶名為admin的用戶
DROP?USER?'admin'@'localhost';

• 最后刷新操作，使操作生效

#刷新操作使其生效
flush?privileges

11.2、用戶權限管理

• 查詢用戶權限

#?格式
SHOW?GRANTS?FOR?'username'@'host'

#?查詢用戶名為?'root'@'%'的權限信息
SHOW?GRANTS?FOR?'root'@'%'

• 給用戶授予某種權限

#?格式
GRANT?privileges?ON?databasename.tablename?TO?'username'@'host'

說明：

• privileges：用戶的操作權限，如SELECT，INSERT，UPDATE、DELETE等，如果要授予所的權限則使用ALL
• databasename：數(shù)據(jù)庫名
• tablename：表名，如果要授予該用戶對所有數(shù)據(jù)庫和表的相應操作權限則可用*表示，如*.*
• username：用戶名
• host：可以訪問的域名

在給其他授權前，請先用管理員賬戶登錄！

1、設置用戶訪問數(shù)據(jù)庫權限

• 設置用戶testuser，只能訪問數(shù)據(jù)庫test_db，其他數(shù)據(jù)庫均不能訪問

grant?all?privileges?on?test_db.*?to?'testuser'@'localhost';

• 設置用戶testuser，可以訪問mysql上的所有數(shù)據(jù)庫

grant?all?privileges?on?*.*?to?'testuser'@'localhost';

• 設置用戶testuser，只能訪問數(shù)據(jù)庫testuser的表user_info，數(shù)據(jù)庫中的其他表均不能訪問

grant?all?privileges?on?test_db.user_info?to?'testuser'@'localhost';

2、設置用戶操作權限

• 設置用戶testuser，擁有所有的操作權限，也就是管理員

grant?all?privileges?on?*.*?to?'testuser'@'localhost';

• 設置用戶testuser，只擁有【查詢】操作權限

grant?select?on?*.*?to?'testuser'@'localhost';

• 設置用戶testuser，只擁有【查詢/插入/修改/刪除】操作權限

grant?select,insert,update,delete?on?*.*?to?'testuser'@'localhost';

3、設置用戶遠程訪問權限

• 設置用戶testuser，只能在客戶端IP為192.168.1.100上才能遠程訪問mysql

grant?all?privileges?on?*.*?to?'testuser'@'192.168.1.100';

• 設置所有用戶可以遠程訪問mysql，修改my.cnf配置文件，將bind-address = 127.0.0.1前面加#注釋掉

#?bind-address?=?127.0.0.1

注意：用以上命令授權的用戶不能給其它用戶授權，如果想讓該用戶可以授權，用以下命令！

GRANT?privileges?ON?databasename.tablename?TO?'username'@'host'?WITH?GRANT?OPTION;

在結尾加上WITH GRANT OPTION就可以了！

11.3、關于root用戶的訪問設置

可以使用如下命令，來一鍵設置root用戶的密碼，同時擁有所有的權限并設置為遠程訪問！

grant?all?privileges?on?*.*?to?'root'@'%'??identified?by?'123456';

如果想關閉root用戶遠程訪問權限，使用如下命令即可！

grant?all?privileges?on?*.*?to?'root'@'localhost'??identified?by?'123456';

最后使用如下命令，使其生效！

flush?privileges;

創(chuàng)建用戶并進行授權，也可以使用如下快捷命令！

#例如，創(chuàng)建一個admin用戶，密碼為admin
grant?all?privileges?on?*.*?to?'admin'@'%'?identified?by?'admin';

#刷新MySQL的系統(tǒng)權限相關表方可生效
flush?privileges;

最后需要注意的是：mysql8，使用強校驗，所以，如果密碼過于簡單，會報錯，密碼盡量搞復雜些！

十二、總結

本文主要圍繞 Mysql 中常用的語法進行一次梳理和介紹，這些語法大部分也同樣適用于其他的數(shù)據(jù)庫，例如 oracle、sqlserver、postgres 等等，在數(shù)據(jù)操作欄，除了分頁函數(shù)以外，基本都是通用的！

如果還有遺漏的地方，歡迎留言指出！

喜歡就三連呀

關注 Stephen，一起學習，一起成長。