MySQL作为国内中小企业最流行的关系型数据库,在业务中经常会被用到。如果要说有什么奇巧,真的不是一两篇文章能够概述的
在这里挑选了一些在实际开发中可能会经常用到的一些技巧,简单地来说说。
01列拼接问题
在实际项目中,经常需要将某一列的内容拼接并返回,特别是一些归集的报表导出的业务中。这里创建1张类似如下的数据表,并插入相关的数据:
create table user ( id int primary key auto_increment, username varchar(20), age int ); insert into user (username,age) values ('张三',20),("李四",25),("王五",26),("李四",20);
现在我们想将user表中用户筛选出来进行拼接并返回。此时,我们可以借助MySQL的group_concat函数进行拼接,可以写出1条类似如下的语句:
mysql>select group_concat(username) user from user;
查询结果为:
| user | ||||
| 张三,李四,王五,李四 |
该语句查询出每行的用户名称,之后将其进行拼接。然而,由于数据表中用户李四存在2条记录,因此导致最终结果出现了重复的记录。而实际开发需求中更多的是,要求对最终的结果进行去重,此时就用到了1个比较有意思的技巧。
我们稍作修改,在group_concat函数的筛选的列之前添加1个distinct关键字,此时可以得到:
mysql>select group_concat(distinct username) user from user;
结果如下所示:
| user | ||||
| 张三,李四,王五 |
可以发现列值去重了,得到我们期望的结果。
02绘制简单直方图
一般业务中,常常会遇到的1个需求就是对用户进行分组,比如查询某个用户最近几个月的消费情况。这里进行简化处理,假设只查询user表中用户出现的次数,此时可以借助group by语句来操作:
select username,count(id) num from user group by username order by num desc user
其结果类似如下:
| username | num |
| 李四 | 2 |
| 张三 | 1 |
| 王五 | 1 |
其中用户李四出现了2次,而其他的用户只出现了1次。有些时候,为了能方便的查看出差别,一般会采用图形化的方式进行处理。此时,可以借助MySQL中repeat函数对某个字符串重复多次的方式,进行简单的ASCII图形化展示。
此时可以写出1个类似如下的SQL语句:
mysql> select b.username,repeat("*",b.num) star from (select username,count(id) num from user group by username order by num desc) b
结果如下所示:
| username | star |
| 李四 | ** |
| 张三 | * |
| 王五 | * |
这里涉及到1个子查询的知识点,就不展开说明了。简单的说,我们可以让我们的数据来自另1个select子语句,但是需要给该子语句1个别名,否则MySQL会抛出异常。
我们将得到的统计次数传递给repeat函数,从而让其根据结果进行渲染,从而得到1个简单的柱状图。如果数据量更多的话,效果会更加明显。
03简化表关联
在关系型数据库中,常常会通过2个及多个表之间的关联来完成复杂的业务需求。对于之前的逻辑,我们新增1个类似如下的关联表:
create table information ( id integer, post varchar(20) ); insert into information (id,post) values (1,'大学教授'),(2,"IT架构师"),(3,"CEO"),(4,"操盘手");
通过用户的id进行2个表之间数据的关联。现在,我们想查询每个用户的信息,包括其名字、年龄及职位。于是有如下的SQL语句:
mysql> select a.id,a.username,a.age,b.post from user a join information b on a.id = b.id
此时可以得到如下的结果:
| id | username | age | post |
| 1 | 张三 | 20 | 大学教授 |
| 2 | 李四 | 25 | IT架构师 |
| 3 | 王五 | 26 | CEO |
| 4 | 李四 | 20 | 操盘手 |
4 rows in set
在MySQL中在关联查询时如果2个表中的关联字段是相同的话,可以通过using子句进行简化,我们可以将上述的SQL修改为:
mysql> select a.id,a.username,a.age,b.post from user a join information b using (id);
其结果为:
| id | username | age | post |
| 1 | 张三 | 20 | 大学教授 |
| 2 | 李四 | 25 | IT架构师 |
| 3 | 王五 | 26 | CEO |
| 4 | 李四 | 20 | 操盘手 |
通过using子句我们简化了SQL中 on a.id=b.id这段的语句。
当然,实际开发中还有其他一些技巧,有时间再分享。
本文作者：本人笔名玉面玲珑颜如玉,1个多年滚打于Web开发的研发工程师。熟悉PHP、Java、C++等编程语言,以编程作为乐趣。
声明：本文为 脚本之家专栏作者 投稿，未经允许请勿转载。
