mysql行号函数_【笔记】MySQL-常见函数

一、单行函数

——字符函数

length、concat、upper、lower、substr、instr、trim、lpad、rpad、replace

——数学函数

round、ceil、floor、truncate、mod、rand

——日期函数

now、curdate、curtime、year、month、monthname、day、hour、minute、second、str_to_date、date_format、datediff

——其他函数

version、database

——控制函数

if、case

二、分组函数

sum、avg、max、min、count

概念：类似Python的函数，将一组逻辑语句封装在函数体中，对外暴露函数名。此处学习的是系统定义好的内置函数，直接调用即可。

好处：1、隐藏了实现细节，直接使用，不用关注内部实现；2、提高了代码的重用性

调用方法：select 函数名(实参列表) ；

分类：1、单行函数；如concat、length。给一个值，返回一个值。

2、分组函数；做统计使用，又称为统计函数、聚合函数、组函数。给一组值，经过统计或处理返回一个值。

单行函数

1.字符函数

(1)length(str)：获取参数值的字节个数

SELECT LENGTH('join')

》4

(2)concat(str1,str2,...)：拼接字符串

SELECT CONCAT(studentId,'_',studentName) FROM student;

(3)upper(str)，lower(str)：将字符串改为大写/小写

SELECT CONCAT(UPPER(studentName),'+',LOWER(studentName)) FROM student;

(4)substr/substring：分割字符串

注意：MySQL中索引从1开始

SUBSTR(str,pos)：截取pos开始后面所有字符，包含pos

SELECT SUBSTR('一二三四五六七',3)

-》三四五六七

SELECT SUBSTR(str,pos,len)：截取pos处开始指定len长度的字符

-- 从第三位开始取3个字符SELECT SUBSTR('一二三四五六七',3,3)

-》三四五

-- 姓名中首字母大写，其他小写SELECT

CONCAT(UPPER(SUBSTR(studentName,1,1)),LOWER((SUBSTR(studentName,2))))

FROM student;

-》Betty

(5)INSTR(str,substr)：返回子字符串substr在字符串str中的起始索引；多个返回第一个索引，找不到返回0；

SELECT INSTR('一二三四五六七','二');

-》2

(6)trim([remstr FROM] str)：去掉字符串首尾remstr字符串，省略则去掉首尾空格，类似Python中的strip；

SELECT TRIM(' nihao ');

-》nihao

SELECT TRIM('a' FROM 'aaaabbbbbccccaaaa');

-》bbbbbcccc

SELECT TRIM('1112' FROM '111234');

-》34

(7)lpad(str,len,padstr)/rpad(str,len,padstr)：左边/右边填充指定字符padstr达到总长度为len；若str长度超过len，则右侧部分截断(无论lpad还是rpad都是截断右侧)；

SELECT LPAD('一二三四五六七',10,'*');

-》***一二三四五六七

SELECT LPAD('一二三四五六七',5,'*');

-》一二三四五

SELECT rPAD('一二三四五六七',10,'ab');

-》一二三四五六七aba

(9)replace(str,str1,str2)：把str字符串中的str1全部替换为str2；

SELECT REPLACE('大宝大宝爱上了小宝小宝','大宝','二宝');

-》二宝二宝爱上了小宝小宝

2.数学函数

(1)round(X,D)：四舍五入函数，把X四舍五入，保留D位小数，D省略则四舍五入为整数，负数也可以四舍五入，按照绝对值四舍五入后取负数；

SELECT ROUND(1.23);

-》1

SELECT ROUND(1.56);

-》2

SELECT ROUND(1.5655,2);

-》1.57

SELECT ROUND(-1.5655,2);

-》1.57

(2)ceil(X)： 向上取整，返回>=该参数的最小整数，负数也是同样；

SELECT CEIL(1.5);

-》2

SELECT CEIL(1.00);

-》1

SELECT CEIL(-1.50);

-》-1

(3)floor(X)：向下取整，返回<=该参数的最小整数，负数也同样；

SELECT FLOOR(-1.50);

-》-2

SELECT FLOOR(1.50);

-》1

(4)truncate(X,D)：截断X，小数点后保留D位；

SELECT TRUNCATE(1.699,1);

-》1.6

SELECT TRUNCATE(-1.699,1);

-》-1.6

(5)mod(N,M)：取余数，同N%M；

SELECT MOD(10,3);

-》1

SELECT 10%3;

-》1

SELECT MOD(-10,3);

-》-1

(6)rand()：获取随机数，返回0-1之间的小数

3.日期函数

(1)now()：返回当前的系统日期+时间

SELECT NOW()

-》2020-03-04 11:22:51

(2)curdata()：返回当前日期，不包含时间

SELECT CURDATE()

-》2020-03-04

(3)curtime()：返回当前时间，不包含日期

SELECT CURTIME()

-》11:24:38

(4)获取指定的部分，年、月、日、小时等

SELECT YEAR(CURDATE());# 获取年份

-》2020

SELECT YEAR(NOW());

-》2020

SELECT YEAR('1998-1-5');

-》1998

SELECT MONTH('1998-1-5');# 获取月份

-》1

SELECT MONTHNAME('1998-1-5');#获取英文月份

-》January

SELECT DAY('1998-1-5');# 获取日期

-》5

SELECT DAYNAME('2020-3-6');# 获取日期的星期几

-》Friday

SELECT HOUR(NOW());#获取小时

-》11

SELECT MINUTE(NOW());#获取分钟

SELECT SECOND(NOW());#获取秒

(5)(常用)str_to_data(str,format)：将日期格式的字符转换成指定格式的日期

SELECT STR_TO_DATE('9-13-98','%m-%d-%y');

-》1998-09-13

(6)data_format(date,format)：将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日')

-》20年03月04日

(7)datediff(expr1,expr2)：返回expr1日期和expr2的日期差的天数；

SELECT DATEDIFF('20170509','20180509');

-》-365

SELECT DATEDIFF(NOW(),'1995-1-1');

->>9194

4.其他函数(使用较少)

(1)version()：查看版本号

SELECT VERSION();

-》8.0.17

(2)database()：查看当前使用数据库

SELECT DATABASE();

-》school

(3)user()：查看当前用户

SELECT USER();

->root@localhost

5.流程控制函数

(1)if(expr1,expr2,expr3)函数：if else效果，条件1expr1为true，返回expr2，False返回expr3；

SELECT IF(10>5,'大','小');

-》大

(2)case函数使用1：适用于等值判断；case可以作为一个表达式使用，也可以作为一个语句

#case语法

case 要判断的字段或表达式

when 常量1 then 要显示的值1或语句1;#值后无需加;

when 常量2 then 要显示的值2或语句2;

...

else 要显示的值或语句;

end

案例：(case作为一个表达式)

#查询员工的工资，要求部门号=30，显示工资为1.1倍

#部门号=40，工资为1.2倍

#其他部门为原工资

select salary as 原始工资,department_id,

-----

case department_id

when 30 then salary*1.1

when 40 then salary*1.2

else salary

end as 新工资

-----

from employees;

case函数使用2：适用于区间判断；类似多重if，与使用1区别是case后面不加内容

#case语法

case #后面不加内容

when 条件1 then 要显示的值1 或语句1;

when 条件2 then 要显示的值2 或语句2;

...

else 要显示的值n 或语句n;

end

案例：

#如果工资>20000，显示A级别；>15000，显示B级别；其他显示C级别

------

select salary,

case

when salary>20000 then 'A'

when salary>15000 then 'B'

else 'C'

end as 工资级别

------

from employees;

分组函数——参数支持哪些类型？

sum、avg数值型；(字符、日期均不建议，计算无意义)

max、min数值、字符、日期均支持，因为可以进行排序，取排序后的最大最小值；

count也是任何类型均可，计算的非null值

——是否支持null？(null加任意值结果均为null)

sum、avg、count、max、min均计算时忽略null值

——和关键字distinct搭配

sum、avg、count均支持distinct搭配使用，代表去重后再计算

——count函数详细介绍

count(字段/*/1); *统计所有行数。1加了一列1，统计1的个数，也可以写2。效率的话和存储引擎有关系，count(字段)最慢，因为中间加了判断过程，需要看列是否为null。

——和分组函数一同查询的字段需要是group by后的，其他的因为分组返回返回一个值，未分组的字段显示无参考意义。

1.sum求和

2.avg求平均数

3.max求最大值

4.min求最小值

5.count计算个数

SELECT

max(studentAge) 最大值,

min(studentAge) 最小值,

avg(studentAge) 平均值,

sum(studentAge) 求和,

SUM(DISTINCT(studentAge)) 去重求和

FROM student;