您好,登錄后才能下訂單哦!
本篇文章為大家展示了如何中MySQL數(shù)據(jù)庫中使用CONCAT拼接函數(shù),內(nèi)容簡明扼要并且容易理解,絕對(duì)能使你眼前一亮,通過這篇文章的詳細(xì)介紹希望你能有所收獲。
一、concat函數(shù)相關(guān)的幾種用法
1-1、函數(shù):concat(str1,str2,…)
concat 函數(shù)一般用在SELECT 查詢語法中,用于修改返回字段內(nèi)容,例如有張LOL英雄信息表如下
mysql> select * from `LOL`; +----+---------------+--------------+-------+ | id | hero_title | hero_name | price | +----+---------------+--------------+-------+ | 1 | D刀鋒之影 | 泰隆 | 6300 | | 2 | X迅捷斥候 | 提莫 | 6300 | | 3 | G光輝女郎 | 拉克絲 | 1350 | | 4 | F發(fā)條魔靈 | 奧莉安娜 | 6300 | | 5 | Z至高之拳 | 李青 | 6300 | | 6 | W無極劍圣 | 易 | 450 | | 7 | J疾風(fēng)劍豪 | 亞索 | 450 | +----+---------------+--------------+-------+ 7 rows in set (0.00 sec)
我需要返回一列:英雄稱號(hào) - 英雄名稱 的數(shù)據(jù),這是就用到了concat函數(shù),如下:
SELECT CONCAT(hero_title,' - ',hero_name) as full_name, price from `LOL`;
mysql> SELECT CONCAT(hero_title,' - ',hero_name) as full_name, price from `LOL`; +------------------------------+-------+ | full_name | price | +------------------------------+-------+ | D刀鋒之影 - 泰隆 | 6300 | | X迅捷斥候 - 提莫 | 6300 | | G光輝女郎 - 拉克絲 | 1350 | | F發(fā)條魔靈 - 奧莉安娜 | 6300 | | Z至高之拳 - 李青 | 6300 | | W無極劍圣 - 易 | 450 | | J疾風(fēng)劍豪 - 亞索 | 450 | +------------------------------+-------+ 7 rows in set (0.00 sec)
如果拼接的參數(shù)中有NULL,則返回NULL;如下:
SELECT CONCAT(hero_title,NULL,hero_name) as full_name, price from `LOL`;
mysql> SELECT CONCAT(hero_title,'NULL',hero_name) as full_name, price from `LOL`; +-------------------------------+-------+ | full_name | price | +-------------------------------+-------+ | D刀鋒之影NULL泰隆 | 6300 | | X迅捷斥候NULL提莫 | 6300 | | G光輝女郎NULL拉克絲 | 1350 | | F發(fā)條魔靈NULL奧莉安娜 | 6300 | | Z至高之拳NULL李青 | 6300 | | W無極劍圣NULL易 | 450 | | J疾風(fēng)劍豪NULL亞索 | 450 | +-------------------------------+-------+ 7 rows in set (0.00 sec)
不好意思,上面是我弟弟寫的,正確的如下:
mysql> SELECT CONCAT(hero_title,NULL,hero_name) as full_name, price from `LOL`; +-----------+-------+ | full_name | price | +-----------+-------+ | NULL | 6300 | | NULL | 6300 | | NULL | 1350 | | NULL | 6300 | | NULL | 6300 | | NULL | 450 | | NULL | 450 | +-----------+-------+ 7 rows in set (0.00 sec)
1-2、函數(shù):concat_ws(separator,str1,str2,…)
??CONCAT_WS() 函數(shù)全稱: CONCAT With Separator ,是CONCAT()的特殊形式。第一個(gè)參數(shù)(separator)是其它參數(shù)的分隔符。分隔符的位置在要連接的兩個(gè)字符串之間。分隔符可以是一個(gè)字符串,也可以是其它字段參數(shù)。
需要注意的是:
??如果分隔符為 NULL,則結(jié)果為 NULL;但如果分隔符后面的參數(shù)為NULL,只會(huì)被直接忽略掉,而不會(huì)導(dǎo)致結(jié)果為NULL。
好了,我們依舊用上面的LOL表,連接各字段,以逗號(hào)分隔:
select concat_ws(',',hero_title,hero_name,price) as full_name, price from `LOL`;
mysql> select concat_ws(',',hero_title,hero_name,price) as full_name, price from `LOL`; +---------------------------------+-------+ | full_name | price | +---------------------------------+-------+ | D刀鋒之影,泰隆,6300 | 6300 | | X迅捷斥候,提莫,6300 | 6300 | | G光輝女郎,拉克絲,1350 | 1350 | | F發(fā)條魔靈,奧莉安娜,6300 | 6300 | | Z至高之拳,李青,6300 | 6300 | | W無極劍圣,易,450 | 450 | | J疾風(fēng)劍豪,亞索,450 | 450 | +---------------------------------+-------+ 7 rows in set (0.00 sec)
分隔符后的拼接參數(shù)為NULL時(shí),直接忽略,不會(huì)影響整體結(jié)果,如下:
select concat_ws(',',hero_title,NULL,hero_name) as full_name, price from `LOL`;
mysql> select concat_ws(',',hero_title,NULL,hero_name) as full_name, price from `LOL`; +----------------------------+-------+ | full_name | price | +----------------------------+-------+ | D刀鋒之影,泰隆 | 6300 | | X迅捷斥候,提莫 | 6300 | | G光輝女郎,拉克絲 | 1350 | | F發(fā)條魔靈,奧莉安娜 | 6300 | | Z至高之拳,李青 | 6300 | | W無極劍圣,易 | 450 | | J疾風(fēng)劍豪,亞索 | 450 | +----------------------------+-------+ 7 rows in set (0.00 sec)
分隔符為NULL時(shí),結(jié)果返回NULL,如下:
select concat_ws(NULL,hero_title,hero_name,price) as full_name, price from `LOL`;
mysql> select concat_ws(NULL,hero_title,hero_name,price) as full_name, price from `LOL`; +-----------+-------+ | full_name | price | +-----------+-------+ | NULL | 6300 | | NULL | 6300 | | NULL | 1350 | | NULL | 6300 | | NULL | 6300 | | NULL | 450 | | NULL | 450 | +-----------+-------+ 7 rows in set (0.00 sec)
1-3、函數(shù):group_concat(expr)
group_concat ( [DISTINCT] 字段名 [order by 排序字段 ASC/DESC] [Separator ‘分隔符'] )
??group_concat函數(shù)通常用于有g(shù)roup by的查詢語句,group_concat一般包含在查詢返回結(jié)果字段中。
??是不是group_concat函數(shù)的公式看著還挺復(fù)雜的?我們一起看看,上方公式中 [] 括號(hào)是可選項(xiàng),表示可用可不用;
1.[DISTINCT]
:對(duì)拼接的參數(shù)支持去重功能;
2.[Order by]
:拼接的參數(shù)支持排序功能;
3.[Separator]
:這個(gè)你很熟悉了,支持自定義'分隔符',如不設(shè)置默認(rèn)為無分隔符;
??好了,下面讓我們來進(jìn)入開心的測(cè)試環(huán)節(jié)吧~ 還是用這張LOL表,別問為什么。或許這就是青春吧!還記的那年通宵五排,大龍團(tuán)的時(shí)候網(wǎng)吧停電了…
又跑題了。。不好意思。
mysql> select * from `LOL`; +----+---------------+--------------+-------+ | id | hero_title | hero_name | price | +----+---------------+--------------+-------+ | 1 | D刀鋒之影 | 泰隆 | 6300 | | 2 | X迅捷斥候 | 提莫 | 6300 | | 3 | G光輝女郎 | 拉克絲 | 1350 | | 4 | F發(fā)條魔靈 | 奧莉安娜 | 6300 | | 5 | Z至高之拳 | 李青 | 6300 | | 6 | W無極劍圣 | 易 | 450 | | 7 | J疾風(fēng)劍豪 | 亞索 | 450 | +----+---------------+--------------+-------+ 7 rows in set (0.00 sec)
??舉個(gè)場(chǎng)景,我們要區(qū)分出各個(gè)價(jià)格段的英雄來,如果不用group_concat的話,或許只能用order by區(qū)分了。
select * from `LOL` order by price desc;
mysql> select * from `LOL` order by price desc; +----+---------------+--------------+-------+ | id | hero_title | hero_name | price | +----+---------------+--------------+-------+ | 1 | D刀鋒之影 | 泰隆 | 6300 | | 2 | X迅捷斥候 | 提莫 | 6300 | | 4 | F發(fā)條魔靈 | 奧莉安娜 | 6300 | | 5 | Z至高之拳 | 李青 | 6300 | | 3 | G光輝女郎 | 拉克絲 | 1350 | | 6 | W無極劍圣 | 易 | 450 | | 7 | J疾風(fēng)劍豪 | 亞索 | 450 | +----+---------------+--------------+-------+ 7 rows in set (0.00 sec)
但是這樣很不直觀啊,我想一行都看到,怎么辦?
SELECT GROUP_CONCAT(hero_title,' - ',hero_name Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price desc;
這時(shí)GROUP_CONCAT函數(shù)就輕松的幫你解決了這個(gè)問題???,舒服不~
注釋:這里我是拼接了(hero_title,' - ‘,hero_name)這三個(gè)參數(shù),分隔符設(shè)為:',',根據(jù)價(jià)格來分組,根據(jù)價(jià)格來排的序。效果如下
mysql> SELECT GROUP_CONCAT(hero_title,' - ',hero_name Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price desc; +------------------------------------------------------------------------+-------+ | full_name | price | +------------------------------------------------------------------------+-------+ | D刀鋒之影 - 泰隆,X迅捷斥候 - 提莫,F發(fā)條魔靈 - 奧莉安娜,Z至高之拳 - 李青 | 6300 | | G光輝女郎 - 拉克絲 | 1350 | | W無極劍圣 - 易,J疾風(fēng)劍豪 - 亞索 | 450 | +------------------------------------------------------------------------+-------+ 3 rows in set (0.00 sec)
如果按價(jià)格(price)從小到大排序,只需控制外層ORDER BY即可,如下:
SELECT GROUP_CONCAT(hero_title,' - ',hero_name Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price asc;
mysql> SELECT GROUP_CONCAT(hero_title,' - ',hero_name Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price asc; +-------------------------------------------------------------------------+-------+ | full_name | price | +-------------------------------------------------------------------------+-------+ | W無極劍圣 - 易,J疾風(fēng)劍豪 - 亞索 | 450 | | G光輝女郎 - 拉克絲 | 1350 | | D刀鋒之影 - 泰隆,X迅捷斥候 - 提莫,F發(fā)條魔靈 - 奧莉安娜,Z至高之拳 - 李青 | 6300 | +-------------------------------------------------------------------------+-------+ 3 rows in set (0.00 sec)
??那么GROUP_CONCAT函數(shù)中的order by 排序怎么用?是用在了拼接字段的排序上,如根據(jù)hero_title進(jìn)行排序拼接,如下:
SELECT GROUP_CONCAT(hero_title,' - ',hero_name order by hero_title Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price asc;
mysql> SELECT GROUP_CONCAT(hero_title,' - ',hero_name order by hero_title Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price asc; +-------------------------------------------------------------------------+-------+ | full_name | price | +-------------------------------------------------------------------------+-------+ | J疾風(fēng)劍豪 - 亞索,W無極劍圣 - 易 | 450 | | G光輝女郎 - 拉克絲 | 1350 | | D刀鋒之影 - 泰隆,F發(fā)條魔靈 - 奧莉安娜,X迅捷斥候 - 提莫,Z至高之拳 - 李青 | 6300 | +-------------------------------------------------------------------------+-------+ 3 rows in set (0.00 sec)
上述內(nèi)容就是如何中MySQL數(shù)據(jù)庫中使用CONCAT拼接函數(shù),你們學(xué)到知識(shí)或技能了嗎?如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備,歡迎關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。