常用SQL語(yǔ)句，看這篇就夠了

一、摘要

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

具體內(nèi)容主要有以下幾個(gè)部分：

• 庫(kù)操作
• 表操作
• 數(shù)據(jù)操作
• 運(yùn)算符
• 視圖
• 函數(shù)
• 存儲(chǔ)過(guò)程
• 觸發(fā)器
• 序列
• 用戶權(quán)限

二、庫(kù)操作

2.1、新增庫(kù)

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

CREATE?DATABASE?IF?NOT?EXISTS?`庫(kù)名`?default?charset?utf8mb4?COLLATE?utf8mb4_unicode_ci;

例子：

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

2.2、修改庫(kù)名

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

方法一

RENAME?database?olddbname?TO?newdbname

這個(gè)語(yǔ)法在 mysql-5.1.7 中被添加進(jìn)來(lái)，到了mysql-5.1.23又去掉了，官方不推薦，會(huì)有丟失數(shù)據(jù)的危險(xiǎn)！

方法二

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

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

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

例子：

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

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

方法三

直接跑一個(gè) shell 腳本！

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

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的簡(jiǎn)稱，123456表示數(shù)據(jù)庫(kù)密碼值！

2.3、刪除庫(kù)名

刪除庫(kù)，比較簡(jiǎn)單，直接刪除即可！

DROP?DATABASE?db1;

2.4、使用庫(kù)

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?'手機(jī)號(hào)',
??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)建時(shí)間',
??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?'更新時(shí)間',
??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?'用戶手機(jī)號(hào)';

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、關(guān)鍵字查詢

• and 查詢

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

• or 查詢

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

• in 查詢（參數(shù)個(gè)數(shù)不能超過(guò)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

• 內(nèi)連接查詢

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)計(jì)學(xué)生總數(shù)

select?count(id)?from?ts_user

• 查詢學(xué)生最大的年紀(jì)

select?max(age)?from?ts_user

• 查詢學(xué)生最大的年紀(jì)

select?min(age)?from?ts_user

• 查詢各個(gè)學(xué)生各項(xiàng)成績(jī)的總和

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

• 查詢各個(gè)學(xué)生各項(xiàng)成績(jī)的平均分

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

• 查詢各個(gè)學(xué)生各項(xiàng)成績(jī)的平均分大于100的學(xué)生信息

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、 刪除操作

#?刪除表全部?jī)?nèi)容
delete?from?ts_user

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

五、運(yùn)算符

MySQL 主要有以下幾種運(yùn)算符：

• 算術(shù)運(yùn)算符
• 比較運(yùn)算符
• 邏輯運(yùn)算符
• 位運(yùn)算符

5.1、算術(shù)運(yùn)算符

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

5.2、比較運(yùn)算符

SELECT 語(yǔ)句中的條件語(yǔ)句經(jīng)常要使用比較運(yùn)算符。通過(guò)這些比較運(yùn)算符，可以判斷表中的哪些記錄是符合條件的。比較結(jié)果為真，則返回 1，為假則返回 0，比較結(jié)果不確定則返回 NULL。

說(shuō)明：mysql中，IN 語(yǔ)句中參數(shù)個(gè)數(shù)是不限制的。不過(guò)對(duì)整段 sql 語(yǔ)句的長(zhǎng)度有了限制，最大不超過(guò) 4M！

5.3、邏輯運(yùn)算符

邏輯運(yùn)算符用來(lái)判斷表達(dá)式的真假。如果表達(dá)式是真，結(jié)果返回 1。如果表達(dá)式是假，結(jié)果返回 0。

5.4、位運(yùn)算符

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

5.5、運(yùn)算符優(yōu)先級(jí)

說(shuō)明：在無(wú)法確定優(yōu)先級(jí)的情況下，可以使用圓括號(hào)()來(lái)改變優(yōu)先級(jí)，并且這樣會(huì)使計(jì)算過(guò)程更加清晰。

六、視圖

視圖（view）是一種虛擬存在的表，是一個(gè)邏輯表，本身并不包含數(shù)據(jù)。作為一個(gè)select語(yǔ)句保存在數(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ù)說(shuō)明：

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

基本格式：

create?view?<視圖名稱>[(column_list)]
???????as?select語(yǔ)句
???????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語(yǔ)句查看視圖信息

show?create?view?v_user;

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

select?*?from?v_user;

6.3、刪除視圖

刪除視圖是指刪除數(shù)據(jù)庫(kù)中已存在的視圖，刪除視圖時(shí)，只能刪除視圖的定義，不會(huì)刪除數(shù)據(jù)，也就是說(shuō)不動(dòng)基表：

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

刪除示例：

drop?view?IF?EXISTS?v_user;

七、函數(shù)

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

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

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

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

參數(shù)說(shuō)明：

• 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ù)說(shuō)明：

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

7.2.3、刪除函數(shù)

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

參數(shù)說(shuō)明：

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

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

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

參數(shù)說(shuō)明：

• pattern：函數(shù)名稱

示例：

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

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

SHOW?CREATE?FUNCTION?fn_name;

參數(shù)說(shuō)明：

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

7.3、實(shí)例操作介紹

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

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)建一個(gè)函數(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ù)處理結(jié)果
??RETURN?userName;
END;

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

7.3.4、調(diào)用函數(shù)

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

7.3.5、刪除函數(shù)

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

八、存儲(chǔ)過(guò)程

8.1、創(chuàng)建語(yǔ)法

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

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

DELIMITER?$$
或
DELIMITER?//

參數(shù)說(shuō)明：

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

創(chuàng)建一個(gè)查詢用戶信息的存儲(chǔ)過(guò)程示例：

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、存儲(chǔ)過(guò)程調(diào)用

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

輸出結(jié)果：

張三

8.3、存儲(chǔ)過(guò)程刪除

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

刪除示例：

DROP?PROCEDURE?IF?EXISTS??user_procedure;

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

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

九、觸發(fā)器

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

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

定義語(yǔ)法

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ù)說(shuō)明：

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

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

• tigger_event詳解：

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

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

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

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

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

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

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

CREATE?TRIGGER?觸發(fā)器名?BEFORE|AFTER?觸發(fā)事件
ON?表名?FOR?EACH?ROW
BEGIN
????????執(zhí)行語(yǔ)句列表
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?;

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

9.2、查詢觸發(fā)器

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

SHOW?TRIGGERS;

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

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

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

9.3、刪除觸發(fā)器

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

示例如下：

DROP?TRIGGER?IF?EXISTS?trig1

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

9.4、總結(jié)

觸發(fā)器盡量少的使用，因?yàn)椴还苋绾?，它還是很消耗資源，如果使用的話要謹(jǐn)慎的使用，確定它是非常高效的：觸發(fā)器是針對(duì)每一行的；對(duì)增刪改非常頻繁的表上切記不要使用觸發(fā)器，因?yàn)樗鼤?huì)非常消耗資源。

10、序列

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

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

10.1、自增序列

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

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

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

#?將id主鍵設(shè)置為自增長(zhǎng)模式
alter?table?test_db?modify?id?int?auto_increment;

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

10.2、程序自定義

當(dāng)然，為了避免出現(xiàn)這種情況，有的大神就自己?jiǎn)为?dú)創(chuàng)建了一張自增序列表，單獨(dú)維護(hù)，這樣就不會(huì)出現(xiàn)在分表的時(shí)候出現(xiàn)相同的ID！

實(shí)現(xiàn)過(guò)程也很簡(jiǎn)單！

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

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

• 2、創(chuàng)建–取當(dā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)建–取下一個(gè)值的函數(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)建–更新當(dā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?;

• 最后，直接通過(guò)函數(shù)調(diào)用，測(cè)試如下

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

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

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

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

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

10.3、UUID() 函數(shù)

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

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

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

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

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

使用方法也很簡(jiǎn)單，在sql可以直接當(dāng)成函數(shù)調(diào)用即可！

select?uuid();

10.4、UUID_SHORT() 函數(shù)

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

select?UUID_SHORT();

同時(shí)，需要注意的是，UUID_SHORT()返回的是unsigned long long類型，在字段類型設(shè)置的時(shí)候，一定要勾選無(wú)符號(hào)類型，否則有可能生成的ID超過(guò)Long類型最大長(zhǎng)度！

11、用戶權(quán)限

11.1、用戶管理

• 查詢所有用戶

select?*?from?mysql.user;

• 創(chuàng)建用戶

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

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

• 更改用戶密碼

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

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

• 刪除用戶

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

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

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

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

11.2、用戶權(quán)限管理

• 查詢用戶權(quán)限

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

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

• 給用戶授予某種權(quán)限

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

說(shuō)明：

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

在給其他授權(quán)前，請(qǐng)先用管理員賬戶登錄！

1、設(shè)置用戶訪問(wèn)數(shù)據(jù)庫(kù)權(quán)限

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

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

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

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

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

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

2、設(shè)置用戶操作權(quán)限

• 設(shè)置用戶testuser，擁有所有的操作權(quán)限，也就是管理員

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

• 設(shè)置用戶testuser，只擁有【查詢】操作權(quán)限

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

• 設(shè)置用戶testuser，只擁有【查詢/插入/修改/刪除】操作權(quán)限

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

3、設(shè)置用戶遠(yuǎn)程訪問(wèn)權(quán)限

• 設(shè)置用戶testuser，只能在客戶端IP為192.168.1.100上才能遠(yuǎn)程訪問(wèn)mysql

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

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

#?bind-address?=?127.0.0.1

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

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

在結(jié)尾加上WITH GRANT OPTION就可以了！

11.3、關(guān)于root用戶的訪問(wèn)設(shè)置

可以使用如下命令，來(lái)一鍵設(shè)置root用戶的密碼，同時(shí)擁有所有的權(quán)限并設(shè)置為遠(yuǎn)程訪問(wèn)！

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

如果想關(guān)閉root用戶遠(yuǎn)程訪問(wèn)權(quán)限，使用如下命令即可！

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

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

flush?privileges;

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

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

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

最后需要注意的是：mysql8，使用強(qiáng)校驗(yàn)，所以，如果密碼過(guò)于簡(jiǎn)單，會(huì)報(bào)錯(cuò)，密碼盡量搞復(fù)雜些！

十二、總結(jié)

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

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

喜歡就三連呀

關(guān)注 Stephen，一起學(xué)習(xí)，一起成長(zhǎng)。