1.group_concat()函数
该函数返回来自一个组的指定字段的连接的非NULL值的字符串结果。
group_concat()会计算哪些行属于同一组,将属于同一组的列显示出来。要返回哪些列,由函数参数(就是字段名)决定。分组必须有个标准,就是根据group by指定的列进行分组。
举例,如test表:
例1:select group_concat(id) from test;
例2:select group_concat(id) from test group by category_id;
结果去test中去查找哪些值是一样的,如果相等,就全部列出来,以逗号分割进行列出
可见,group_concat()只有与group by语句同时使用才能产生效果。
例3:select group_concat(id order by date desc) from test group by category_id;
指定返回列的同时,还可以指定列的排列顺序
2.截取字符串函数
举例,如test表:
(1)从左开始截取字符串 left(str, length) 注:str为被截取字段,length为截取长度
例:select left(content,20) as abstract from test; 从左开始顺序截取20个
(2)从右开始截取字符串 right(str, length) 注:str为被截取字段,length为截取长度
例:select right(content,20) as abstract from test; 从右开始倒序截取20个
(3)截取字符串
substring(str, pos)
substring(str, pos, length)
注:str为被截取字段,pos为从第几位开始截取,length为截取长度
例1:select substring(content,5) as abstract from test;
或:select substring(content from 5) as abstract from test;
(使用 FROM的格式为标准 SQL 语法)
从第5位开始截取字符串直到字符串末尾
例2:select substring(content,5,10) as abstract from test;
从第5位开始截取10个
例3:select substring(content,-5) as abstract from test;
如果位数是负数如-5 则是从后倒数位数
(4)按关键字截取字符串
substring_index(str,delim,count)
注:str为被截取字段,delim为关键字,count为关键字出现的次数
例1:select substring_index("blog.jb51.COMMIT.COM.cn.ALTER",".",2) as abstract
例2:select substring_index("blog.jb51.COMMIT.COM.cn.ALTER",".",-2) as abstract
如果关键字出现的次数是负数如-2 则是从后倒数
例3:假定有一张表,其中有个字段是 email 保存了电子邮箱,然后,我想统计每种邮箱的占比情况。我们知道电子信箱的格式是: account@domain ,要实现上面的统计,必须取得 email
字符串中 @ 后面的域名部分。可使用 substring_index 函数实现:
表test_import内容如下:
select substring_index(email,'@',-1) as domain from test_import;
select substring_index(email,'@',-1) as domain from test_import group by 1 limit 10;
其中group by 1中的1表示结果集的第一列,limit 10表示只显示结果集的前10行
select substring_index(email,'@',-1) as domain,count(distinct email) as count from test_import where email like '%@%' group by 1 order by 2 desc limit 10;
select
substring_index(email,'@',-1) as domain,
count(distinct email) as count,
count(distinct email)
/ (select count(distinct email) from test_import where email like '%@%')
* 100 as percent
from test_import
where email like '%@%'
group by 1
order by 2 desc
limit 10;
3.MySql常用函数
(1)数学函数
ABS(x) 返回x的绝对值
BIN(x) 返回x的二进制(OCT返回八进制,HEX返回十六进制)
CEILING(x) 返回大于x的最小整数值
EXP(x) 返回值e(自然对数的底)的x次方
FLOOR(x) 返回小于x的最大整数值(只返回整数,小数部分舍弃)
GREATEST(x1,x2,...,xn) 返回集合中最大的值
LEAST(x1,x2,...,xn) 返回集合中最小的值
LN(x) 返回x的自然对数(也就是计算e的多少次方等于x值)
LOG(x,y) 返回以x为底,y的对数
MOD(x,y) 返回x/y的模(余数)
PI() 返回pi的值(圆周率)
RAND() 返回0到1内的随机数(小数点后保留16位,不含0和1),可以通过
提供一个参数(种子)使RAND()随机数生成器生成一个指定的值
ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
SIGN(x) 返回代表数字x的符号的值(这个符号为正数1、负数-1或0)
SQRT(x) 返回一个数的平方根
TRUNCATE(x,y) 返回数字x截短为y位小数的结果
mysql> select abs(-1);
+---------+
| abs(-1) |
+---------+
| 1 |
+---------+
mysql> select bin(10);
+---------+
| bin(10) |
+---------+
| 1010 | -- 1*23+1*21
+---------+
mysql> select oct(10);
+---------+
| oct(10) |
+---------+
| 12 | -- 1*81+2*80
+---------+
mysql> select hex(10);
+---------+
| hex(10) |
+---------+
| A | -- 10*160
+---------+
mysql> select ceiling(5.2);
+--------------+
| ceiling(5.2) |
+--------------+
| 6 |
+--------------+
mysql> select exp(1);
+-------------------+
| exp(1) |
+-------------------+
| 2.718281828459045 | -- 即e的值
+-------------------+
mysql> select floor(5.2);
+------------+
| floor(5.2) |
+------------+
| 5 |
+------------+
mysql> select greatest(2.4,5,1,3.3,7.8);
+---------------------------+
| greatest(2.4,5,1,3.3,7.8) |
+---------------------------+
| 7.8 |
+---------------------------+
mysql> select least(2.4,5,1,3.3,7.8);
+------------------------+
| least(2.4,5,1,3.3,7.8) |
+------------------------+
| 1 |
+------------------------+
mysql> select ln(1);
+-------+
| ln(1) |
+-------+
| 0 |
+-------+
mysql> select log(2,4);
+----------+
| log(2,4) |
+----------+
| 2 | -- log(2,4)(即计算2的多少次方等于4)
+----------+
mysql> select mod(10,4);
+-----------+
| mod(10,4) |
+-----------+
| 2 |
+-----------+
mysql> select pi();
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
mysql> select rand(); -- 0~1之间的随机数(小数点后16位)
+--------------------+
| rand() |
+--------------------+
| 0.5298497784229459 |
+--------------------+
mysql> select rand()*100; -- 1~100之间的随机数(小数点后16位)
+-------------------+
| rand()*100 |
+-------------------+
| 99.52048547521278 |
+-------------------+
mysql> select floor(rand()*100); -- 1~100之间的整数
+-------------------+
| floor(rand()*100) |
+-------------------+
| 40 |
+-------------------+
mysql> select round(rand()*100,2);
+---------------------+
| round(rand()*100,2) |
+---------------------+
| 95.99 |
+---------------------+
mysql> select sign(0.1);
+-----------+
| sign(0.1) |
+-----------+
| 1 |
+-----------+
mysql> select sign(-200);
+------------+
| sign(-200) |
+------------+
| -1 |
+------------+
mysql> select sign(0);
+---------+
| sign(0) |
+---------+
| 0 |
+---------+
mysql> select sqrt(3);
+--------------------+
| sqrt(3) |
+--------------------+
| 1.7320508075688772 |
+--------------------+
mysql> select truncate(5.6785,2);
+--------------------+
| truncate(5.6785,2) |
+--------------------+
| 5.67 |
+--------------------+
转载于:https://blog.51cto.com/janniexx/1404956