mysql之常见函数

一 格式

select  函数名()  [from 表名]

二 聚合函数（又称统计函数,分组函数），用来做统计

SUM() 求和，AVG() 求平均：只能处理数值型数据，忽略null值

MAX() 求最大值，MIN() 求最小值：能处理任何类型的数据，忽略null值

COUNT() 统计非null值的数量：能处理任何类型的数据，忽略null值

1. 单独使用

select SUM(salary) from employees  as out_put;     out_put=691400

select MAX(salary) from employees as out_put;     out_put=24000

select MIN(salary) from employees as out_put;      out_put=2100

select AVG(salary) from employees as out_put;      out_put=6461.682243

select COUNT(salary) from employees as out_put;     out_put=107

select SUM(salary) as 和,MAX(salary) 最大,MIN(salary) 最小, AVG(salary) 平均,COUNT(salary) as 数量 from employees;

2. 和DISTINCT配合使用

select SUM(DISTINCT salary) from employees;   先去重再计算总和

select COUNT(DISTINCT salary) from employees;   先去重再计算个数

其他的聚合函数也可以和DISTINCT配合使用

3. COUNT的详细介绍

select count(salary) from employees;   #统计所有salary字段不为空的行数
select count(*) from employees;           #统计表中所有行数，因为没一行都会至少有一个字段不为空，最常用
select count(1) from employees;          #统计表中所有行数，它是给给表加一列1，统计总行数

4. 和聚合函数一起使用的字段有限制

select AVG(salary), employee_id from employees;     X会报错

和聚合函数一起使用的字段一般是GROUP BY （后面会深入讲）后面的字段

三 单行函数，用来做处理

1. 字符函数

1）LENGTH()，用于获取参数的字节个数

select LENGTH("mary");     

select LENGTH("杰克")；

查询出来的结果和字符编码有关

2）CONCAT(),用于拼接字符串

select CONCAT("jack","loves","rose");

3) UPPER() 和 LOWER()，获取字符串的大小写

select UPPER("JACKrose");
select LOWER("JACKrose");

select CONCAT(UPPER('Jack'),LOWER('Rose'));

4）SUBSTR(), 获取字符串的子串

要注意在mysql中字符串的索引是从1开始的，且SUBSTR() 函数包括首尾

select SUBSTR('helloworld',4) as out_put;  out_put=‘loworld’   #截取从指定索引处及其后面所有子串

select SUBSTR('helloworld',2,4) as out_put;  out_put='ello'      #截取从指定索引处的指定长度的子串

5）INSTR(str1,str2),返回子串第一次出现的索引，如果没出现过则返回值为0

select INSTR('老鼠爱大米哈哈','大米') as out_put;     out_put=4

select INSTR('老鼠爱大米哈哈大米','大米') as out_put;     out_put=4

select INSTR('老鼠爱大米哈哈大米','小米') as out_put;     out_put=0

6) TRIM(), 去掉字符串两端的指定字符，默认为去掉两端的空格

select TRIM('   你今天学习了吗    ') as out_put;        out_put='你今天学习了吗'

select TRIM('a' from 'aaaaa你今天学习了吗aaaa')  as out_put;     out_put='你今天学习了吗'

7） LPAD(str1,length,str2), 将用指定字符串str2将字符串str1左填充为指定长度的字符串

select LPAD('也有梦想',8,'咸鱼') as out_put;     out_put='咸鱼咸鱼也有梦想'

select LPAD('也有梦想',7,'咸鱼') as out_put;     out_put='咸鱼咸也有梦想'

select LPAD('也有梦想',2,'咸鱼') as out_put;     out_put='也有'

8) RPAD(str1,length,str2), 将用指定字符串str2将字符串str1右填充为指定长度的字符串

select LPAD('也有梦想',8,'咸鱼') as out_put;     out_put='也有梦想咸鱼咸鱼'

select LPAD('也有梦想',7,'咸鱼') as out_put;     out_put='也有梦想咸鱼咸'

select LPAD('也有梦想',2,'咸鱼') as out_put;     out_put='也有'

9）REPLACE(str,from_str,to_str)， 替换,如果要替换的子串不存在，则不替换

select REPLACE('如果我是程序员你愿意嫁给我吗?','程序员','DJ') as out_put；     out_put='如果我是DJ你愿意嫁给我吗?'

select REPLACE('如果我是程序员你愿意嫁给我吗?','宇航员','DJ') as out_put；     out_put='如果我是程序员你愿意嫁给我吗?'     

2. 数学函数

1）ROUND(X) 和 ROUND(X,D)

四舍五入，也可以做指定保留位数的四舍五入

select ROUND(1.65) as out_put;     out_put=2
select ROUND(1.12) as out_put;     out_put=1

select ROUND(1.9243,2) as out_put;     out_put=1.92

2) CEIL(X), 向上取整,取大于等于X的最小整数

    FLOOR(X),向下取整，取小于等于该数的最大整数

select CEIL(1.93) as out_put;     out_put=2
select CEIL(-2.76) as out_put;     out_put=-2

select CEIL(1.00) as out_put;     out_put=1

3）TRUNCATE(X,D)，按照指定位数截断

select TRUNCATE(1.9342,2) as out_put;     out_put=1.93

select TRUNCATE(-1.9342,2) as out_put;     out_put=-1.93

4) MOD(X,Z) 取余     a%b=a/b*b

select MOD(10,3) as out_put;     out_put=1

select MOD(-10,3) as out_put;     out_put=-1

select MOD(-10,-3) as out_put;     out_put=-1

select MOD(10,-3) as out_put;     out_put=1

3. 日期函数

1）NOW() 返回当前日期和时间

select NOW() as out_put;     out_put=2020-04-08 21:15:27

2) CURDATE()  返回当前日期

select CURDATE() as out_put;     out_put=2020-04-08

3) CURTIME()  返回当前时间

select CURTIME() as out_put;     out_put=21:15:27

4）YEAR()  MONTH() MONTHNAME() DAY() HOUR() MINUTE() SECOND获取指定的年、月、日、时、分、秒

select YEAR(NOW()) as out_put;     out_put=2020

select YEAR('2020-04-08 21:15:27') as out_put;     out_put=2020

select MONTH('2020-04-08 21:15:27') as out_put;     out_put=4

select MONTHNAME('2020-04-08 21:15:27') as out_put;     out_put=April

......

5) DATEDIFF(expr1,expr2),求两个日期相差的天数

select  DATEDIFF(MAX(hiredate),MIN(hiredate)) as difference from employees;
 

6) STR_TO_DATE(str,format) 将指定格式的字符串转换为日期

select STR_TO_DATE('09-13-2020','%m-%d-%Y') as 月日年；  月日年=2020-09-13

这个函数是有实际的应用场景的，比如我们要在employees表中查询出来入职日期为2015年7月18日的员工信息，那我们可能是从网页上获得用户输入的这个值，该值是字符串类型的，字符串类型的值有一种自动转换格式，即‘1993-10-12’年月日，如果符合这个格式，可以自动转换，否则需要我们手动转换

7）DATE_FORMAT(date,format) 将日期转换为指定格式的字符串

select DATE_FORMAT(NOW(),'%d日--%m月--%Y年') as out_put;     out_put=08日--04月--2020年

实际应用场景如下：

select last_name,DATE_FORMAT(hiredate,'%Y年%m月%d日') as 入职日期 from employees where commission_pct is not null;

4. 流程控制函数（补充）

1) IF(expr1,expr2,expr3) ,能达到IF...ELSE的效果

select IF(10>5,'大','小') as out_put;     out_put='大'

select last_name,commission_pct, IF(commission_pct is null,'没奖金，需要努力啊', '有奖金，再接再厉啊') as 备注 from employees;

2）CASE表达式有两种使用情况

                                               

select                        #案例1
last_name, department_id, salary as 原工资,
CASE department_id
    WHEN 30 THEN salary*1.1
    WHEN 40 THEN salary*1.2
    WHEN 50 THEN salary*1.3
    ELSE salary
END as 新工资
from employees order by department_id;

select last_name,salary,                   #案例2
CASE 
WHEN salary>20000 THEN 'A级别'
WHEN salary>15000 THEN 'B级别'
WHEN salary>10000 THEN 'C级别'
ELSE 'D级别'
END as 工资级别
from employees order by salary DESC;

5. 其他函数（补充）

1)VERSION() 数据库的版本

select VERSION() as version;     version=8.0.11

2) DATABASE() 当前使用的数据库

select DATABASE() as database;     database=myemployees

3) USER()

select USER() as user;     user=root@localhost