暂且将我们遇到的常用的mysql函数按照操作对象分为两类: 单行函数(只对一行数据起作用)/多行函数(对一组数据操作处理,返回一行).
命令和函数的关系
1: 命令为我们完成各种操作
2: 函数是为了加工数据
3: 函数不会输入显示, 所以要想看到一个函数的运算结果, 就必须把它放在命令中, 用命令显示函数的结果
简单结束几个我目前常用/常见的函数(后期还有整理/更新)
一 单行函数: 字符函数, 日期函数, 数字函数
1: 字符函数
lower ——-转换为小写
upper———-转换为大写
initcap———转换为首字母大写,其他的字符小写
concat———连接函数
substr———返回子串
length——–返回字符串长度
instr(column|expression, “string” [,m] [,n] )
LPAD(column|expression,n,’string’)
RPAD(column|expression,n,’string’)
trim(leading/trailing/both trim_character from trim_source)
replace(text, search_string, replacement_string)
lower ——-转换为小写
mysql> select lower(name), birth from pet;
+-------------+------------+
| lower(name) | birth |
+-------------+------------+
| fluffy | 1993-02-04 |
| claws | 1994-03-17 |
| buffy | 1989-05-13 |
| fang | 1990-08-27 |
| bowser | 1979-08-31 |
| chirpy | 1997-12-09 |
| whistler | 1997-12-09 |
| slim | 1996-04-29 |
| puffball | 1999-03-30 |
+-------------+------------+
9 rows in set (0.00 sec)
concat———连接函数
mysql> select concat("good", "morning") from pet;
+---------------------------+
| concat("good", "morning") |
+---------------------------+
| goodmorning |
| goodmorning |
| goodmorning |
| goodmorning |
| goodmorning |
| goodmorning |
| goodmorning |
| goodmorning |
| goodmorning |
+---------------------------+
9 rows in set (0.00 sec)
substr———返回子串
mysql> select substr("morning", 1,4) from pet;
+------------------------+
| substr("morning", 1,4) |
+------------------------+
| morn |
| morn |
| morn |
| morn |
| morn |
| morn |
| morn |
| morn |
| morn |
+------------------------+
9 rows in set (0.00 sec)
length——–返回字符串长度
mysql> select length("morning") from pet;
+-------------------+
| length("morning") |
+-------------------+
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
+-------------------+
9 rows in set (0.00 sec)
instr(column|expression, “string” [,m][,n])
在 column|expression中从第m个字符开始,第n次出现string字符串的位置. 默认m,n为1.
mysql> select instr(name, "buffy") from pet;
+----------------------+
| instr(name, "buffy") |
+----------------------+
| 0 |
| 0 |
| 1 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+----------------------+
9 rows in set (0.00 sec)
LPAD(column|expression,n,’string’)
RPAD(column|expression,n,’string’)
左, 右用若干个string填充 column|expression, 使得字符串长度为n.
mysql> select LPAD(name,20,'string') from pet;
+------------------------+
| LPAD(name,20,'string') |
+------------------------+
| stringstringstFluffy |
| stringstringstrClaws |
| stringstringstrBuffy |
| stringstringstriFang |
| stringstringstBowser |
| stringstringstChirpy |
| stringstringWhistler |
| stringstringstriSlim |
| stringstringPuffball |
+------------------------+
9 rows in set (0.00 sec)
trim(leading/trailing/both trim_character from trim_source)
从trim_source)的 leading/trailing/both 头/尾/头尾 删除和 trim_character一样的字符
可以利用这个函数来删除空格
mysql> select trim(both ' ' from ' a b c ') from pet;
+-------------------------------+
| trim(both ' ' from ' a b c ') |
+-------------------------------+
| a b c |
| a b c |
| a b c |
| a b c |
| a b c |
| a b c |
| a b c |
| a b c |
| a b c |
+-------------------------------+
9 rows in set (0.00 sec)
mysql> select trim(both '19' from birth ) from pet;
+-----------------------------+
| trim(both '19' from birth ) |
+-----------------------------+
| 93-02-04 |
| 94-03-17 |
| 89-05-13 |
| 90-08-27 |
| 79-08-31 |
| 97-12-09 |
| 97-12-09 |
| 96-04-29 |
| 99-03-30 |
+-----------------------------+
9 rows in set (0.00 sec)
replace(text, search_string, replacement_string)
将text中的search_string替换成replacement_string.
mysql> select replace('adcfg', 'cf','123') from pet;
+------------------------------+
| replace('adcfg', 'cf','123') |
+------------------------------+
| ad123g |
| ad123g |
| ad123g |
| ad123g |
| ad123g |
| ad123g |
| ad123g |
| ad123g |
| ad123g |
+------------------------------+
9 rows in set (0.00 sec)
如果其中的replacement_string为null,那就是删除的意思了.
2: 数字函数
round 按照指定的精度四舍五入
trunc 按照指定的精度截断
mod 返回余数
round (45.923, 2)——————45.92
round (45.923, 0)——————46
round (45.923, -1)——————50
trunc(45.923, 2)———————45.92
trunc(45.923, 0)———————45
trunc(45.923, -1)———————40
mod(1600,300)———————–100
3: 日期函数
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> as age
-> from pet;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Fluffy | 1993-02-04 | 2017-07-13 | 24 |
| Claws | 1994-03-17 | 2017-07-13 | 23 |
| Buffy | 1989-05-13 | 2017-07-13 | 28 |
| Fang | 1990-08-27 | 2017-07-13 | 26 |
| Bowser | 1979-08-31 | 2017-07-13 | 37 |
| Chirpy | 1997-12-09 | 2017-07-13 | 19 |
| Whistler | 1997-12-09 | 2017-07-13 | 19 |
| Slim | 1996-04-29 | 2017-07-13 | 21 |
| Puffball | 1999-03-30 | 2017-07-13 | 18 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
1 row in set (0.00 sec)
二 多行函数
avg(distinct|all|n)
count(distinct|all|n)
max(distinct|all|expr)
min(distinct|all|expr)
sum(distinct|all|n)
mysql> select avg(age) from petnew;
+----------+
| avg(age) |
+----------+
| 23.8889 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from petnew where age > 19;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE species = 'dog' OR species = 'cat'
-> gROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
+---------+------+----------+
4 rows in set (0.00 sec)
mysql> sel
ect name , birth, age from petnew where age = (select max(age) from petnew);
+--------+------------+------+
| name | birth | age |
+--------+------------+------+
| Bowser | 1979-08-31 | 37 |
+--------+------------+------+
1 row in set (0.00 sec)
mysql> select min(age) from petnew;
+----------+
| min(age) |
+----------+
| 18 |
+----------+
1 row in set (0.00 sec)
mysql> select sum(age) from petnew;
+----------+
| sum(age) |
+----------+
| 215 |
+----------+
1 row in set (0.00 sec)
这一篇主要讲了几个函数, 其实也不需要多了解, 大概知道意思, 有用到的时候查选下用法和例子就可以了.
useful reference:
http://www.cnblogs.com/cocos/archive/2011/05/06/2039469.html
http://www.mysqltutorial.org/mysql-functions.aspx