建站
咨詢
售后
建站咨詢
新聞中心
在上篇《??常用數(shù)據(jù)庫 SQL 命令詳解(上)??》文章中，主要介紹上半部分內(nèi)容，今天我們來介紹下半部分內(nèi)容！
10年積累的成都網(wǎng)站設計、網(wǎng)站制作經(jīng)驗，可以快速應對客戶對網(wǎng)站的新想法和需求。提供各種問題對應的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡服務。我雖然不認識你，你也不認識我。但先網(wǎng)站制作后付款的網(wǎng)站建設流程，更有連城免費網(wǎng)站建設讓你可以放心的選擇與我們合作。
一、函數(shù)
1.1 常用函數(shù)列表
1.2 自定義函數(shù)語法介紹
(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ù)體
(2) 編輯函數(shù)
ALTER FUNCTION fn_name [characteristic...]
參數(shù)說明：
- fn_name：自定義函數(shù)名稱
- func_parameter: param_name type
- characteristic: LANGUAGE SQL
(3) 刪除函數(shù)
DROP FUNCTION [IF EXISTS] fn_name;
參數(shù)說明：
- fn_name：自定義函數(shù)名稱
- func_parameter: param_name type
(4) 查看函數(shù)語法
SHOW FUNCTION STATUS [LIKE 'pattern']
參數(shù)說明：
- pattern：函數(shù)名稱
示例：
SHOW FUNCTION STATUS LIKE 'user_function';
(5) 查看函數(shù)的定義語法
SHOW CREATE FUNCTION fn_name;
參數(shù)說明：
- fn_name：自定義函數(shù)名稱
1.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;
(2) 插入數(shù)據(jù)
INSERT INTO t_user (user_name, age)
VALUES('張三',24),('李四',25),('王五',26),('趙六',27);
(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ù)處理結(jié)果
RETURN userName;
END;
-- 函數(shù)創(chuàng)建定界符
DELIMITER ;
(4) 調(diào)用函數(shù)
//查詢用戶ID為1的信息
SELECT user_function(1);
(5) 刪除函數(shù)
DROP FUNCTION IF EXISTS user_function;
二、存儲過程
2.1 創(chuàng)建語法
CREATE PROCEDURE 存儲過程名([[IN |OUT |INOUT ] 參數(shù)名 數(shù)據(jù)類形...])
過程與創(chuàng)建函數(shù)類似，其中的聲明語句結(jié)束符，可以自定義:
DELIMITER $$
或
DELIMITER //
參數(shù)說明：
- IN 輸入?yún)?shù)：表示該參數(shù)的值必須在調(diào)用存儲過程時指定，在存儲過程中修改該參數(shù)的值不能被返回，為默認值
- OUT 輸出參數(shù)：該值可在存儲過程內(nèi)部被改變，并可返回
- INOUT 輸入輸出參數(shù)：調(diào)用時指定，并且可被改變和返回
創(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 ;
2.2 存儲過程調(diào)用
-- @out為輸出參數(shù)
CALL user_procedure(1, @out);
輸出結(jié)果：
張三
2.3 存儲過程刪除
DROP PROCEDURE [IF EXISTS] proc_name;
刪除示例：
DROP PROCEDURE IF EXISTS user_procedure;
2.4 存儲過程和函數(shù)的區(qū)別
- 函數(shù)只能通過return語句返回單個值或者表對象。而存儲過程不允許執(zhí)行return，但是通過out參數(shù)返回多個值。
- 函數(shù)是可以嵌入在sql中使用的,可以在select中調(diào)用，而存儲過程不行。
- 函數(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ā)器中定義的語句集合。
3.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語句！
3.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字段指定查詢。
3.3 刪除觸發(fā)器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
示例如下：
DROP TRIGGER IF EXISTS trig1
刪除觸發(fā)器之后最好使用上面的方法查看一遍。
3.4 總結(jié)
觸發(fā)器盡量少的使用，因為不管如何，它還是很消耗資源，如果使用的話要謹慎的使用，確定它是非常高效的：觸發(fā)器是針對每一行的;對增刪改非常頻繁的表上切記不要使用觸發(fā)器，因為它會非常消耗資源。
四、序列
在 MySQL 中，可以有如下幾種途徑實現(xiàn)唯一值：
- 自增序列
- 程序自定義
- UUID() 函數(shù)
- UUID_SHORT() 函數(shù)
4.1 自增序列
在mysql中，一般我們可以給某個主鍵字段設置為自增模式，例如：
#創(chuàng)建一個表test_db，字段內(nèi)容為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!
4.2 程序自定義
當然，為了避免出現(xiàn)這種情況，有的大神就自己單獨創(chuàng)建了一張自增序列表，單獨維護，這樣就不會出現(xiàn)在分表的時候出現(xiàn)相同的ID！
實現(xiàn)過程也很簡單！
創(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;
創(chuàng)建–取當前值的函數(shù)：
BEGIN
DECLARE value INTEGER;
SET value = 0;
SELECT current_value INTO value
FROM sequence
WHERE name = seq_name;
RETURN value;
END
創(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 ;
創(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ù)調(diào)用，測試如下：
# 添加一個sequence名稱和初始值，以及自增幅度
INSERT INTO sequence VALUES ('testSeq', 0, 1);
#設置指定sequence的初始值
SELECT SETVAL('testSeq', 10);
#查詢指定sequence的當前值
SELECT CURRVAL('testSeq');
#查詢指定sequence的下一個值
SELECT NEXTVAL('testSeq');
這方案，某種情況下解決了分表的問題，但是如果分庫還是會出現(xiàn)相同的ID！
4.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ù)調(diào)用即可！
select uuid();
4.4 UUID_SHORT() 函數(shù)
在 MySQL 5.1 之后的版本，提供UUID_SHORT()函數(shù)，生成一個64位無符號整數(shù)，在java中可以用Long類型接受。另外，需要注意的是，server_id 的范圍必須為0-255，并且不支持 STATEMENT模式復制，否則有可能會產(chǎn)生重復的ID：
select UUID_SHORT();
同時，需要注意的是，UUID_SHORT()返回的是unsigned long long類型，在字段類型設置的時候，一定要勾選無符號類型，否則有可能生成的ID超過Long類型最大長度!
五、用戶權限
5.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
5.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 test_db.* 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;
在結(jié)尾加上WITH GRANT OPTION就可以了！
5.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，使用強校驗，所以，如果密碼過于簡單，會報錯，密碼盡量搞復雜些！
六、總結(jié)
本文主要圍繞 Mysql 中常用的語法進行一次梳理和介紹，這些語法大部分也同樣適用于其他的數(shù)據(jù)庫，例如 oracle、sqlserver、postgres 等等，在數(shù)據(jù)操作欄，除了分頁函數(shù)以外，基本都是通用的！
網(wǎng)頁標題：常用數(shù)據(jù)庫 SQL 命令詳解（下）
路徑分享：http://fisionsoft.com.cn/article/ccejohh.html
