MySQL日记

2018-10-03 11:44:59
Reading:331

一、在只需要一条记录、且搜索的字段没有索引的情况下,使用 LIMIT 1 避免全表扫描,提高效率。

二、正确使用子查询:select sum(字段x) from (SELECT 字段x from 表1 where lx=9 limit 0,7) a;

三、MySQL字符串连接。

1、concat和null:concat(1,'22','你好',NULL) 结果将为 NULL。
2、concat_ws可以忽略null:('-',1,'22','你好',null) 结果为1-22-你好。
3、CONCAT_WS(separator,str1,str2,…):参数可以是字段名或具体值。如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。(只保留非空值)

四、MySQL中group_concat函数
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])

1、基本查询
mysql> select * from aa;
+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200   |
|3 | 500   |
+------+------+
6 rows in set (0.00 sec)

2、以id分组,把name字段的值打印在一行,逗号分隔(默认)
mysql> select id,group_concat(name) from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)

3、以id分组,把name字段的值打印在一行,分号分隔
mysql> select id,group_concat(name separator ';') from aa group by id;
+------+----------------------------------+
| id| group_concat(name separator ';') |
+------+----------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500   |
+------+----------------------------------+
3 rows in set (0.00 sec)

4、以id分组,把去冗余的name字段的值打印在一行,逗号分隔
mysql> select id,group_concat(distinct name) from aa group by id;
+------+-----------------------------+
| id| group_concat(distinct name) |
+------+-----------------------------+
|1 | 10,20|
|2 | 20   |
|3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)

5、以id分组,把name字段的值打印在一行,逗号分隔,以name排倒序
mysql> select id,group_concat(name order by name desc) from aa group by id;
+------+---------------------------------------+
| id| group_concat(name order by name desc) |
+------+---------------------------------------+
|1 | 20,20,10   |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)

About

When I was young
I'd listen to the radio
Waiting for my favorite songs
When they played I'd sing along
It made me smile
Those were such happy times
And not so long ago
How I wondered where they'd gone
But they're back again
Just like a long lost friend
All the songs I loved so well