mysql常用命令/语句学习二-CSDN博客

本文链接：https://blog.csdn.net/zyc_love_study/article/details/75082517

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