柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) SQL中各種函數(shù)的用法

http://yzkb.51969.com/

一、SQL中的array_agg函數(shù)介紹與示例代碼

1.在MySQL數(shù)據(jù)庫(kù)中，array_agg是一種非常有用的聚合函數(shù)，用于將一列數(shù)據(jù)聚合為一個(gè)數(shù)組。

2.array_agg函數(shù)的概述 array_agg函數(shù)將一列數(shù)據(jù)聚合為一個(gè)數(shù)組，并返回此數(shù)組。這一功能在某些情況下非常有用，例如當(dāng)我們需要將某一列的數(shù)據(jù)合并為一個(gè)數(shù)組以便于處理時(shí)。在MySQL中，array_agg函數(shù)是通過(guò)使用GROUP BY語(yǔ)句結(jié)合GROUP_CONCAT函數(shù)來(lái)實(shí)現(xiàn)的。 同理在PostgreSQL（瀚高數(shù)據(jù)庫(kù)）中可以使用array_agg函數(shù)來(lái)進(jìn)行處理，而在人大金倉(cāng)數(shù)據(jù)庫(kù)中可以使用ARRAY_agg和group_concat可以達(dá)到同樣的效果。

SELECT item_類(lèi)別,ARRAY_agg(item_商品名稱(chēng)) FROM TLK_物品名稱(chēng)管理 GROUP BY item_類(lèi)別;

SELECT item_類(lèi)別,group_concat(item_商品名稱(chēng)) FROM TLK_物品名稱(chēng)管理 GROUP BY item_類(lèi)別;

人大金倉(cāng)數(shù)據(jù)庫(kù) PostgreSQL。

array_agg函數(shù)的注意事項(xiàng) 在使用array_agg函數(shù)時(shí)，需要注意以下幾點(diǎn)：

array_agg函數(shù)只能在GROUP BY語(yǔ)句中使用，用于對(duì)數(shù)據(jù)進(jìn)行分組聚合。 array_agg函數(shù)返回的數(shù)組中的元素順序是不確定的。如果需要按照特定的順序返回?cái)?shù)組，可以使用ORDER BY子句對(duì)數(shù)據(jù)進(jìn)行排序。 array_agg函數(shù)只能在MySQL 5.7及以上的版本中使用。如果你的MySQL版本較低，可以考慮升級(jí)到較新的版本。

二、常見(jiàn)的聚合函數(shù)

1.概念

聚合函數(shù)通常作用于一組數(shù)據(jù)，并對(duì)一組數(shù)據(jù)返回一個(gè)值。 2.常見(jiàn)的聚合函數(shù)的類(lèi)型 AVG(),SUM(),MAX(),MIN(),COUNT()

AVG(),SUM()：數(shù)值

①查詢(xún)員工表的平均工資以及員工表的總工資

select avg(salary) “avg”,sum(salary) “sum” from employees ②對(duì)于字符串結(jié)果是0

MAX(),MIN()：數(shù)值，字符串，日期

①查詢(xún)員工表的最高的工資和最低的工資

select max(salary) “max”, min(salary) “min” from employees ②對(duì)于字符串來(lái)說(shuō)，可以查找到首字母最大的名字，最小的名字

select max(last_name) “max”, min(last_name) “min” from employees

COUNT()

①作用：計(jì)算指定的字段的個(gè)數(shù)

②count(*),count(1),count(字段名)的區(qū)別？

count()和count(1)能查詢(xún)表中有多少條記錄。 count(字段名)查詢(xún)?cè)撟侄蚊路强盏挠涗洝?③ SELECT count(),count(1),count(commission_pct) FROM employees

6.查詢(xún)員工表的平均獎(jiǎng)金率。

avg(commission_pct)=sum(commission_pct)/count(commission_pct) 只計(jì)算員工有獎(jiǎng)金的，除以有獎(jiǎng)金的總?cè)藬?shù)。所以avg(commission_pct)是錯(cuò)誤的。應(yīng)該除以總?cè)藬?shù)SUM(commission_pct)/count(*)正確

count(*),count(1),count(非空字段名)哪個(gè)效率高。

①如果使用的MyISAM的存儲(chǔ)引擎,三者效率相同都是O(1) 引擎內(nèi)部有一計(jì)數(shù)器在維護(hù)著行數(shù)

②如果使用的InnoDB的存儲(chǔ)引擎，三個(gè)效率count(*) = count(1) > count(非空字段名)

count(*),count(1)直接讀行數(shù)，復(fù)雜度是O(n), innodb真的要去數(shù)一遍。但好于具體的count(列名)

三、類(lèi)型轉(zhuǎn)換函數(shù)

1、常見(jiàn)的類(lèi)型轉(zhuǎn)換函數(shù)有CONVERT ,cast(item_入庫(kù)數(shù)量 as BIGINT),(item_入庫(kù)數(shù)量::BIGINT).

四、其他函數(shù)

1、concat（）:字符串拼接 特別注意：NULL和任何的數(shù)據(jù)concat拼接,結(jié)果都是NULL；（經(jīng)測(cè)試，瀚高數(shù)據(jù)庫(kù)和人大金倉(cāng)數(shù)據(jù)庫(kù)，當(dāng)拼接了null值時(shí)，會(huì)拼接到空值，其余有值的字段正常顯示）。 字段拼接中間以’,‘間隔（經(jīng)測(cè)試，瀚高數(shù)據(jù)庫(kù)和人大金倉(cāng)數(shù)據(jù)庫(kù)中間沒(méi)有’,'拼接時(shí)也可以正常執(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ù)庫(kù)目前測(cè)試用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;—這個(gè)用法在MySQL中可以用。

#若commission_pct為null,返回0，否則返回commission_pct

SELECT IFNULL(commission_pct,0) as 獎(jiǎng)金率；

根據(jù)上面兩個(gè)函數(shù)，示例：拼接可能為null的數(shù)據(jù)字段值，應(yīng)該使用ifnull()+concat()

#為了防止commission_pct為null,導(dǎo)致concat（）拼接結(jié)果為null，所以使用ifnull（）判斷

SELECT CONCAT(first_name,',',last_name,',',job_id,',',IFNULL(commission_pct,0))as OUT_PUT

FROM employees;

4.特別提示：+號(hào)與concat()

#+號(hào)，在sql中，如果拼接字段是字符串?dāng)?shù)據(jù)，它會(huì)轉(zhuǎn)換為數(shù)據(jù)類(lèi)型，作算術(shù)運(yùn)算；

#如果不能轉(zhuǎn)換為數(shù)據(jù)，就為0；+號(hào)和concat()函數(shù)不同；+號(hào)雖然在java或python中可以拼接字符，

#但在sql中，是向算術(shù)傾向的；

select (dist_jk_id+log_id) as id from distribute_logs;

經(jīng)測(cè)試，字符串類(lèi)型的數(shù)字相加會(huì)報(bào)錯(cuò)； 5.is/is not null/=null 特別注意：=號(hào)不能判斷一個(gè)值是null;需要使用：IS;但是注意，is、is not只能用來(lái)判斷字段值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.安全等于：<=> 特別注意：<=>這個(gè)符號(hào)可以判斷任何字段值是否等于，包括上面的值為null的情況；

7.LENGTH(str)：字符串長(zhǎng)度；upper：轉(zhuǎn)大寫(xiě)；lower：轉(zhuǎn)小寫(xiě)；

8.substr(str,a，b)：在str中從索引a開(kāi)始,截取b個(gè)字符 特別注意：sql的索引從1開(kāi)始

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ù)庫(kù)中可以通過(guò)構(gòu)建函數(shù)。（目前沒(méi)有構(gòu)建成功，等后續(xù)成功了在做補(bǔ)充） 參照此地址中的構(gòu)建方法。

instr(str1，str2) 返回str2字符串在str1中的起始索引,如果沒(méi)有就返回0

SELECT INSTR("楊不悔愛(ài)上了殷六俠","殷六俠") AS out_put;#返回：7

構(gòu)建方法：

-- 實(shí)現(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;

-- 實(shí)現(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;

-- 實(shí)現(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ù)接受三個(gè)參數(shù)：

source 是一個(gè)你想要替換的字符串。

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ù)有兩個(gè)功能： 1，如果長(zhǎng)度不夠指定的長(zhǎng)度，就在左邊填充字符串, 2，如果長(zhǎng)度超出了指定的長(zhǎng)度，就把右邊截掉。 語(yǔ)法格式 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 返回當(dāng)前系統(tǒng)日期+時(shí)間 SELECT NOW(); #curdate 返回當(dāng)前系統(tǒng)日期,不帶時(shí)間 SELECT CURDATE(); #curtime 返回當(dāng)前系統(tǒng)時(shí)間,不帶日期 SELECT curtime(); #可以指定顯示：年、月、日、小時(shí)、分鐘、秒（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：返回兩個(gè)日期相差的天數(shù) select DATEDIFF(‘2020-01-02’,‘2020-01-01’);#返回：1

17、流程控制函數(shù)：if、case #if(表達(dá)式1，表達(dá)式2，表達(dá)式3)：1成立返回2，否則返回3 SELECT if(10<5,“對(duì)”,“錯(cuò)”); SELECT last_name,commission_pct,IF(commission_pct IS NULL,“沒(méi)獎(jiǎng)金，哈哈”,“有獎(jiǎng)金，吃肉”)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直接跳過(guò)；

#sum:求和、avg 平均值、max 最大值、min 最小值、count 計(jì)算個(gè)數(shù) SELECT SUM(salary) from employees; SELECT SUM(salary) 求和,avg(salary) 平均,MIN(salary) 最小,MAX(salary) 最大,COUNT(salary) 個(gè)數(shù) FROM employees; #注意： #1.聚合函數(shù)中sum、avg一般用于處理數(shù)值型；max、min、count可以處理任何類(lèi)型； #2.NULL類(lèi)型不參與聚合函數(shù)運(yùn)算,即：在執(zhí)行這幾種方法時(shí)，null被忽略跳過(guò); #3.可以和關(guān)鍵字distinct搭配，實(shí)現(xiàn)去重的聚合 SELECT sum(DISTINCT salary),sum(salary) FROM employees; #可以看出加了distinct的要小很多 #count()函數(shù)最常用：下面兩種均是統(tǒng)計(jì)表中所有行數(shù)：或常量 SELECT COUNT() FROM employees; SELECT COUNT(1) from employees;#在count()參數(shù)添加常量

柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) SQL中各種函數(shù)的用法

http://yzkb.51969.com/

推薦文章