柚子快報邀請碼778899分享：數(shù)據(jù)庫 SQL中各種函數(shù)的用法
柚子快報邀請碼778899分享：數(shù)據(jù)庫 SQL中各種函數(shù)的用法
一、SQL中的array_agg函數(shù)介紹與示例代碼
1.在MySQL數(shù)據(jù)庫中，array_agg是一種非常有用的聚合函數(shù)，用于將一列數(shù)據(jù)聚合為一個數(shù)組。
2.array_agg函數(shù)的概述 array_agg函數(shù)將一列數(shù)據(jù)聚合為一個數(shù)組，并返回此數(shù)組。這一功能在某些情況下非常有用，例如當我們需要將某一列的數(shù)據(jù)合并為一個數(shù)組以便于處理時。在MySQL中，array_agg函數(shù)是通過使用GROUP BY語句結(jié)合GROUP_CONCAT函數(shù)來實現(xiàn)的。 同理在PostgreSQL（瀚高數(shù)據(jù)庫）中可以使用array_agg函數(shù)來進行處理，而在人大金倉數(shù)據(jù)庫中可以使用ARRAY_agg和group_concat可以達到同樣的效果。
SELECT item_類別,ARRAY_agg(item_商品名稱) FROM TLK_物品名稱管理 GROUP BY item_類別;
SELECT item_類別,group_concat(item_商品名稱) FROM TLK_物品名稱管理 GROUP BY item_類別;
人大金倉數(shù)據(jù)庫 PostgreSQL。
array_agg函數(shù)的注意事項 在使用array_agg函數(shù)時，需要注意以下幾點：
array_agg函數(shù)只能在GROUP BY語句中使用，用于對數(shù)據(jù)進行分組聚合。 array_agg函數(shù)返回的數(shù)組中的元素順序是不確定的。如果需要按照特定的順序返回數(shù)組，可以使用ORDER BY子句對數(shù)據(jù)進行排序。 array_agg函數(shù)只能在MySQL 5.7及以上的版本中使用。如果你的MySQL版本較低，可以考慮升級到較新的版本。
二、常見的聚合函數(shù)
1.概念
聚合函數(shù)通常作用于一組數(shù)據(jù)，并對一組數(shù)據(jù)返回一個值。 2.常見的聚合函數(shù)的類型 AVG(),SUM(),MAX(),MIN(),COUNT()
AVG(),SUM()：數(shù)值
①查詢員工表的平均工資以及員工表的總工資
select avg(salary) “avg”,sum(salary) “sum” from employees ②對于字符串結(jié)果是0
MAX(),MIN()：數(shù)值，字符串，日期
①查詢員工表的最高的工資和最低的工資
select max(salary) “max”, min(salary) “min” from employees ②對于字符串來說，可以查找到首字母最大的名字，最小的名字
select max(last_name) “max”, min(last_name) “min” from employees
COUNT()
①作用：計算指定的字段的個數(shù)
②count(*),count(1),count(字段名)的區(qū)別？
count()和count(1)能查詢表中有多少條記錄。 count(字段名)查詢該字段名下非空的記錄。 ③ SELECT count(),count(1),count(commission_pct) FROM employees
6.查詢員工表的平均獎金率。
avg(commission_pct)=sum(commission_pct)/count(commission_pct) 只計算員工有獎金的，除以有獎金的總?cè)藬?shù)。所以avg(commission_pct)是錯誤的。應該除以總?cè)藬?shù)SUM(commission_pct)/count(*)正確
count(*),count(1),count(非空字段名)哪個效率高。
①如果使用的MyISAM的存儲引擎,三者效率相同都是O(1) 引擎內(nèi)部有一計數(shù)器在維護著行數(shù)
②如果使用的InnoDB的存儲引擎，三個效率count(*) = count(1) > count(非空字段名)
count(*),count(1)直接讀行數(shù)，復雜度是O(n), innodb真的要去數(shù)一遍。但好于具體的count(列名)
三、類型轉(zhuǎn)換函數(shù)
1、常見的類型轉(zhuǎn)換函數(shù)有CONVERT ,cast(item_入庫數(shù)量 as BIGINT),(item_入庫數(shù)量::BIGINT).
四、其他函數(shù)
1、concat（）:字符串拼接 特別注意：NULL和任何的數(shù)據(jù)concat拼接,結(jié)果都是NULL；（經(jīng)測試，瀚高數(shù)據(jù)庫和人大金倉數(shù)據(jù)庫，當拼接了null值時，會拼接到空值，其余有值的字段正常顯示）。 字段拼接中間以’,‘間隔（經(jīng)測試，瀚高數(shù)據(jù)庫和人大金倉數(shù)據(jù)庫中間沒有’,'拼接時也可以正常執(zhí)行）
select concat(dist_type,',',dist_jk_id,dist_name,dist_interdddress,dist_address) as list from distribute_logs
或者
select concat(dist_type,',',dist_jk_id,',',dist_name,',',dist_interdddress,',',dist_address) as list from distribute_logs
2.判斷null值，并返回指定字符 瀚高數(shù)據(jù)庫目前測試用case可以用。
select CASE when dist_database is null then '0' else dist_database end from distribute_logs
聯(lián)合使用：
select concat((***CASE when dist_database is null then '' else dist_database end***),',',dist_name) as list from distribute_logs
ifnull(str1,str2):若str1為null,返回str2;否則，返回str1;—這個用法在MySQL中可以用。
#若commission_pct為null,返回0，否則返回commission_pct
SELECT IFNULL(commission_pct,0) as 獎金率；
根據(jù)上面兩個函數(shù)，示例：拼接可能為null的數(shù)據(jù)字段值，應該使用ifnull()+concat()
#為了防止commission_pct為null,導致concat（）拼接結(jié)果為null，所以使用ifnull（）判斷
SELECT CONCAT(first_name,',',last_name,',',job_id,',',IFNULL(commission_pct,0))as OUT_PUT
FROM employees;
4.特別提示：+號與concat()
#+號，在sql中，如果拼接字段是字符串數(shù)據(jù)，它會轉(zhuǎn)換為數(shù)據(jù)類型，作算術(shù)運算；
#如果不能轉(zhuǎn)換為數(shù)據(jù)，就為0；+號和concat()函數(shù)不同；+號雖然在java或python中可以拼接字符，
#但在sql中，是向算術(shù)傾向的；
select (dist_jk_id+log_id) as id from distribute_logs;
經(jīng)測試，字符串類型的數(shù)字相加會報錯； 5.is/is not null/=null 特別注意：=號不能判斷一個值是null;需要使用：IS;但是注意，is、is not只能用來判斷字段值null
select * from distribute_logs where dist_password=null
select * from distribute_logs where dist_password is null
select * from distribute_logs where dist_database is not null
6.安全等于：<=> 特別注意：<=>這個符號可以判斷任何字段值是否等于，包括上面的值為null的情況；
7.LENGTH(str)：字符串長度；upper：轉(zhuǎn)大寫；lower：轉(zhuǎn)小寫；
8.substr(str,a，b)：在str中從索引a開始,截取b個字符 特別注意：sql的索引從1開始
select LENGTH(dist_tabaddress), dist_tabaddress,SUBSTRING(UPPER(dist_tabaddress),2,24) from distribute_logs where dist_database is not null
9.instr函數(shù)，instr(str1，str2) 這是orcle的函數(shù)，在瀚高數(shù)據(jù)庫中可以通過構(gòu)建函數(shù)。（目前沒有構(gòu)建成功，等后續(xù)成功了在做補充） 參照此地址中的構(gòu)建方法。
instr(str1，str2) 返回str2字符串在str1中的起始索引,如果沒有就返回0
SELECT INSTR("楊不悔愛上了殷六俠","殷六俠") AS out_put;#返回：7
構(gòu)建方法：
-- 實現(xiàn)1
CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS
$$ DECLARE pos integer;
BEGIN
pos := instr($1, $2, 1);
RETURN pos;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
-- 實現(xiàn)2
CREATE FUNCTION instr(string varchar,
string_to_search varchar,
beg_index integer) RETURNS integer AS
$$ DECLARE pos integer NOT NULL DEFAULT 0;
temp_str varchar;
beg integer;
length integer;
ss_length integer;
BEGIN
IF beg_index > 0 THEN
temp_str := substring(string FROM beg_index);
pos := position(string_to_search IN temp_str);
IF pos = 0 THEN
RETURN 0;
ELSE
RETURN pos + beg_index - 1;
END IF;
ELSIF beg_index < 0 THEN
ss_length := char_length(string_to_search);
length := char_length(string);
beg := length + beg_index - ss_length + 2;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length);
pos := position(string_to_search IN temp_str);
IF pos > 0 THEN
RETURN beg;
END IF;
beg := beg - 1;
END LOOP;
RETURN 0;
ELSE
RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
-- 實現(xiàn)3
CREATE FUNCTION instr(string varchar,
string_to_search varchar,
beg_index integer,
occur_index integer) RETURNS integer AS
$$ DECLARE pos integer NOT NULL DEFAULT 0;
occur_number integer NOT NULL DEFAULT 0;
temp_str varchar;
beg integer;
i integer;
length integer;
ss_length integer;
BEGIN
IF beg_index > 0 THEN
beg := beg_index;
temp_str := substring(string FROM beg_index);
FOR i IN 1 .. occur_index LOOP
pos := position(string_to_search IN temp_str);
IF i = 1 THEN
beg := beg + pos - 1;
ELSE
beg := beg + pos;
END IF;
temp_str := substring(string FROM beg + 1);
END LOOP;
IF pos = 0 THEN
RETURN 0;
ELSE
RETURN beg;
END IF;
ELSIF beg_index < 0 THEN
ss_length := char_length(string_to_search);
length := char_length(string);
beg := length + beg_index - ss_length + 2;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length);
pos := position(string_to_search IN temp_str);
IF pos > 0 THEN
occur_number := occur_number + 1;
IF occur_number = occur_index THEN
RETURN beg;
END IF;
END IF;
beg := beg - 1;
END LOOP;
RETURN 0;
ELSE
RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
12.trim：去除前后空格或其它指定字符
select trim(dist_tabaddress) from distribute_logs
13.replace函數(shù)： REPLACE(source, old_text, new_text );
REPLACE() 函數(shù)接受三個參數(shù)：
source 是一個你想要替換的字符串。
old_text 是您要搜索和替換的文本。 如果 old_text 在字符串中多次出現(xiàn)，它的所有出現(xiàn)都將被替換。
new_text 是將替換舊文本 (old_text) 的新文本。
select REPLACE(dist_tabaddress,'9999','44') from distribute_logs;
14.lpad()函數(shù)
PostgreSQL中的lpad()函數(shù)有兩個功能： 1，如果長度不夠指定的長度，就在左邊填充字符串, 2，如果長度超出了指定的長度，就把右邊截掉。 語法格式 lpad(string,length[,fill_text]) 示例：
select lpad(dist_tabaddress,45,'44') from distribute_logs;
select lpad(dist_tabaddress,12) from distribute_logs;
now()、curdate()、str_to_date()、date_format()、datediff #日期函數(shù) #now 返回當前系統(tǒng)日期+時間 SELECT NOW(); #curdate 返回當前系統(tǒng)日期,不帶時間 SELECT CURDATE(); #curtime 返回當前系統(tǒng)時間,不帶日期 SELECT curtime(); #可以指定顯示：年、月、日、小時、分鐘、秒（year、month、day、hour、minute、second） SELECT YEAR(NOW()) 年; SELECT SECOND(NOW()) 秒; #str_to_date:將日期格式的字符轉(zhuǎn)換成指定格式的日期 SELECT STR_TO_DATE(‘9-9-2020’,‘%d-%m-%Y’) as 年月日; #date_format:將日期轉(zhuǎn)換成字符 SELECT DATE_FORMAT(NOW(),‘%Y年%m月%d日’) as 年月日; #datediff：返回兩個日期相差的天數(shù) select DATEDIFF(‘2020-01-02’,‘2020-01-01’);#返回：1
17、流程控制函數(shù)：if、case #if(表達式1，表達式2，表達式3)：1成立返回2，否則返回3 SELECT if(10<5,“對”,“錯”); SELECT last_name,commission_pct,IF(commission_pct IS NULL,“沒獎金，哈哈”,“有獎金，吃肉”)FROM employees; #case SELECT salary 合同工資, department_id, CASE department_id WHEN 30 THEN salary1.1 WHEN 40 THEN salary1.2 WHEN 50 THEN salary*1.3 ELSE 0 END as 新工資 FROM employees;
18：聚合函數(shù)：sum、、max 、min 、count 特別注意：聚合函數(shù)都忽略null值，即：遇到null直接跳過；
#sum:求和、avg 平均值、max 最大值、min 最小值、count 計算個數(shù) SELECT SUM(salary) from employees; SELECT SUM(salary) 求和,avg(salary) 平均,MIN(salary) 最小,MAX(salary) 最大,COUNT(salary) 個數(shù) FROM employees; #注意： #1.聚合函數(shù)中sum、avg一般用于處理數(shù)值型；max、min、count可以處理任何類型； #2.NULL類型不參與聚合函數(shù)運算,即：在執(zhí)行這幾種方法時，null被忽略跳過; #3.可以和關鍵字distinct搭配，實現(xiàn)去重的聚合 SELECT sum(DISTINCT salary),sum(salary) FROM employees; #可以看出加了distinct的要小很多 #count()函數(shù)最常用：下面兩種均是統(tǒng)計表中所有行數(shù)：或常量 SELECT COUNT() FROM employees; SELECT COUNT(1) from employees;#在count()參數(shù)添加常量
柚子快報邀請碼778899分享：數(shù)據(jù)庫 SQL中各種函數(shù)的用法
推薦文章
本文內(nèi)容根據(jù)網(wǎng)絡資料整理，出于傳遞更多信息之目的，不代表金鑰匙跨境贊同其觀點和立場。
轉(zhuǎn)載請注明，如有侵權(quán)，聯(lián)系刪除。
Kaufland外貿(mào)優(yōu)選港
