mysql常用命令/语句学习二

暂且将我们遇到的常用的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> select 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