跳至主要內容

1.16 SQL函数 🎉

刘春龙...大约 14 分钟数据库mysql

1.16 SQL函数 🎉

函数是 SQL 的一个非常强有力的特性,函数能够用于下面的目的:

  • 执行数据计算
  • 修改单个数据项
  • 操纵输出进行行分组
  • 格式化显示的日期和数字
  • 转换列数据类型

SQL 函数有输入参数,并且总有一个返回值。

函数分类

单行函数仅对单个行进行运算,并且每行返回一个结果。常见的函数类型有字符、数字、日期、转换

多行函数能够操纵成组的行,每个行组给出一个结果,这些函数也被称为组函数。

单行函数 💎

单行函数分类

字符函数 👻

大小写处理函数

函数描述实例
LOWER(s)|LCASE(s)将字符串 s 转换为小写将字符串 OLDLU转换为小写:SELECT LOWER("OLDLU"); -- oldlu
UPPER(s)|UCASE(s)将字符串s转换为大写将字符串 oldlu转换为大写:SELECT UPPER("oldlu"); -- OLDLU

示例:显示雇员 Davies 的雇员号、姓名和部门号,将姓名转换为大写。

select employee_id,UPPER(last_name),department_id from employees where last_name = 'davies';

字符处理函数

函数描述实例
LENGTH(s)返回字符串 s 的长度返回字符串oldlu的字符数SELECT LENGTH("oldlu"); --5;
CONCAT(s1,s2...sn)字符串 s1,s2 等多个字符串合并为一个字符串合并多个字符串SELECT CONCAT("sxt ", "teacher ", "oldlu"); --sxt teacher oldlu;
LPAD(s1,len,s2)在字符串 s1 的开始处填充字符串 s2,使字符串长度达到 len将字符串 x 填充到 oldlu字符串的开始处:SELECT LPAD('oldlu',8,'x'); -- xxxoldlu
LTRIM(s)去掉字符串 s 开始处的空格去掉字符串 oldlu开始处的空格:SELECT LTRIM(" oldlu") ;-- oldlu
REPLACE(s,s1,s2)将字符串 s2 替代字符串 s 中的字符串 s1将字符串 oldlu 中的字符 o 替换为字符 O:SELECT REPLACE('oldlu','o','O'); --Oldlu
REVERSE(s)将字符串s的顺序反过来将字符串 abc 的顺序反过来:SELECT REVERSE('abc'); -- cba
RPAD(s1,len,s2)在字符串 s1 的结尾处添加字符串 s2,使字符串的长度达到 len将字符串 xx填充到 oldlu字符串的结尾处:SELECT RPAD('oldlu',8,'x'); -- oldluxxx
RTRIM(s)去掉字符串 s 结尾处的空格去掉字符串 oldlu 的末尾空格:SELECT RTRIM("oldlu "); -- oldlu
SUBSTR(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串从字符串 OLDLU中的第 2 个位置截取 3个 字符:SELECT SUBSTR("OLDLU", 2, 3); -- LDL
SUBSTRING(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串从字符串 OLDLU中的第 2 个位置截取 3个 字符:SELECT SUBSTRING("OLDLU", 2, 3); --LDL
TRIM(s)去掉字符串 s 开始和结尾处的空格去掉字符串 oldlu 的首尾空格:SELECT TRIM(' oldlu ');--oldlu

示例:显示所有工作岗位名称从第 4 个字符位置开始,包含字符串 REP的雇员的ID信息,将雇员的姓和名连接显示在一起,还显示雇员名的的长度,以及名字中字母 a 的位置。

SELECT employee_id, CONCAT(last_name,first_name) NAME, job_id, LENGTH(last_name),INSTR(last_name, 'a') "Contains 'a'?" FROM employees WHERE SUBSTR(job_id, 4) = 'REP';

数字函数 👻

函数名描述实例
ABS(x)返回 x 的绝对值返回 -1 的绝对值:SELECT ABS(-1) -- 返回1
ACOS(x)求 x 的反余弦值(参数是弧度)SELECT ACOS(0.25);
ASIN(x)求反正弦值(参数是弧度)SELECT ASIN(0.25);
ATAN(x)求反正切值(参数是弧度)SELECT ATAN(2.5);
ATAN2(n, m)求反正切值(参数是弧度)SELECT ATAN2(-0.8, 2);
AVG(expression)返回一个表达式的平均值,expression 是一个字段返回 Products 表中Price 字段的平均值:SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x)返回大于或等于 x 的最小整数SELECT CEIL(1.5) -- 返回2
CEILING(x)返回大于或等于 x 的最小整数SELECT CEILING(1.5); -- 返回2
COS(x)求余弦值(参数是弧度)SELECT COS(2);
COT(x)求余切值(参数是弧度)SELECT COT(6);
COUNT(expression)返回查询的记录总数,expression 参数是一个字段或者 * 号返回 Products 表中 products 字段总共有多少条记录:SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x)将弧度转换为角度SELECT DEGREES(3.1415926535898) -- 180
n DIV m整除,n 为被除数,m 为除数计算 10 除于 5:SELECT 10 DIV 5; -- 2
EXP(x)返回 e 的 x 次方计算 e 的三次方:SELECT EXP(3) -- 20.085536923188
FLOOR(x)返回小于或等于 x 的最大整数小于或等于 1.5 的整数:SELECT FLOOR(1.5) -- 返回1
GREATEST(expr1, expr2, expr3, ...)返回列表中的最大值返回以下数字列表中的最大值:SELECT GREATEST(3, 12, 34, 8, 25); -- 34返回以下字符串列表中的最大值:SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob
LEAST(expr1, expr2, expr3, ...)返回列表中的最小值返回以下数字列表中的最小值:SELECT LEAST(3, 12, 34, 8, 25); -- 3返回以下字符串列表中的最小值:SELECT LEAST("Google", "Runoob", "Apple"); -- Apple
LN返回数字的自然对数,以 e 为底。返回 2 的自然对数:SELECT LN(2); -- 0.6931471805599453
LOG(x) 或 LOG(base, x)返回自然对数(以 e 为底的对数),如果带有 base 参数,则 base 为指定带底数。SELECT LOG(20.085536923188) -- 3 SELECT LOG(2, 4); -- 2
LOG10(x)返回以 10 为底的对数SELECT LOG10(100) -- 2
LOG2(x)返回以 2 为底的对数返回以 2 为底 6 的对数:SELECT LOG2(6); -- 2.584962500721156
MAX(expression)返回字段 expression 中的最大值返回数据表 Products 中字段 Price 的最大值:SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)返回字段 expression 中的最小值返回数据表 Products 中字段 Price 的最小值:SELECT MIN(Price) AS MinPrice FROM Products;
MOD(x,y)返回 x 除以 y 以后的余数5 除于 2 的余数:SELECT MOD(5,2) -- 1
PI()返回圆周率(3.141593)SELECT PI() --3.141593
POW(x,y)返回 x 的 y 次方2 的 3 次方:SELECT POW(2,3) -- 8
POWER(x,y)返回 x 的 y 次方2 的 3 次方:SELECT POWER(2,3) -- 8
RADIANS(x)将角度转换为弧度180 度转换为弧度:SELECT RADIANS(180) -- 3.1415926535898
RAND()返回 0 到 1 的随机数SELECT RAND() --0.93099315644334
ROUND(x)返回离 x 最近的整数SELECT ROUND(1.23456) --1
SIGN(x)返回 x 的符号,x 是负数、0、正数分别返回 -1、0 和 1SELECT SIGN(-10) -- (-1)
SIN(x)求正弦值(参数是弧度)SELECT SIN(RADIANS(30)) -- 0.5
SQRT(x)返回x的平方根25 的平方根:SELECT SQRT(25) -- 5
SUM(expression)返回指定字段的总和计算 OrderDetails 表中字段 Quantity 的总和:SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x)求正切值(参数是弧度)SELECT TAN(1.75); -- -5.52037992250933
TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值(与 ROUND 最大的区别是不会进行四舍五入)SELECT TRUNCATE(1.23456,3) -- 1.234

ROUND(column|expression, n) 函数

ROUND 函数四舍五入列、表达式或者 n 位小数的值。如果第二个参数是 0 或者缺少,值被四舍五入为整数。如果第二个参数是 2值被四舍五入为两位小数。如果第二个参数是–2,值被四舍五入到小数点左边两位。

SELECT ROUND(45.923,2), ROUND(45.923,0),ROUND(45.923,-1);

TRUNCATE(column|expression,n) 函数

TRUNCATE函数的作用类似于 ROUND 函数。如果第二个参数是 0 或者缺少,值被截断为整数。如果第二个参数是 2,值被截断为两位小数。如果第二个参数是–2,值被截断到小数点左边两位。与 ROUND 最大的区别是不会进行四舍五入。

SELECT TRUNCATE(45.923,2);

使用MOD(m,n) 函数

MOD 函数找出m 除以n的余数。

示例:所有job_id是SA_REP的雇员的名字,薪水以及薪水被5000除后的余数。

SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = 'SA_REP';

日期函数 👻

在MySQL中允许直接使用字符串表示日期,但是要求字符串的日期格式必须为:‘YYYY-MM-DD HH:MI:SS’ 或者‘YYYY/MM/DD HH:MI:SS’;

函数名描述实例
CURDATE()返回当前日期SELECT CURDATE(); -> 2018-09-19
CURTIME()返回当前时间SELECT CURTIME(); -> 19:59:02
CURRENT_DATE()返回当前日期SELECT CURRENT_DATE(); -> 2018-09-19
CURRENT_TIME()返回当前时间SELECT CURRENT_TIME(); -> 19:59:02
DATE()从日期或日期时间表达式中提取日期值SELECT DATE("2017-06-15"); -> 2017-06-15
DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32
DAY(d)返回日期值 d 的日期部分SELECT DAY("2017-06-15"); -> 15
DAYNAME(d)返回日期 d 是星期几,如 Monday,TuesdaySELECT DAYNAME('2011-11-11 11:11:11') ->Friday
DAYOFMONTH(d)计算日期 d 是本月的第几天SELECT DAYOFMONTH('2011-11-11 11:11:11') ->11
DAYOFWEEK(d)日期 d 今天是星期几,1 星期日,2 星期一,以此类推SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6
DAYOFYEAR(d)计算日期 d 是本年的第几天SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315
HOUR(t)返回 t 中的小时值SELECT HOUR('1:2:3') -> 1
LAST_DAY(d)返回给给定日期的那一月份的最后一天SELECT LAST_DAY("2017-06-20"); -> 2017-06-30
MONTHNAME(d)返回日期当中的月份名称,如 NovemberSELECT MONTHNAME('2011-11-11 11:11:11') -> November
MONTH(d)返回日期d中的月份值,1 到 12SELECT MONTH('2011-11-11 11:11:11') ->11
NOW()返回当前日期和时间SELECT NOW() -> 2018-09-19 20:57:43
SECOND(t)返回 t 中的秒钟值SELECT SECOND('1:2:3') -> 3
SYSDATE()返回当前日期和时间SELECT SYSDATE() -> 2018-09-19 20:57:43
TIMEDIFF(time1, time2)计算时间差值SELECT TIMEDIFF("13:10:11", "13:10:10"); -> 00:00:01
TO_DAYS(d)计算日期 d 距离 0000 年 1 月 1 日的天数SELECT TO_DAYS('0001-01-01 01:01:01') -> 366
WEEK(d)计算日期 d 是本年的第几个星期,范围是 0 到 53SELECT WEEK('2011-11-11 11:11:11') -> 45
WEEKDAY(d)日期 d 是星期几,0 表示星期一,1 表示星期二SELECT WEEKDAY("2017-06-15"); -> 3
WEEKOFYEAR(d)计算日期 d 是本年的第几个星期,范围是 0 到 53SELECT WEEKOFYEAR('2011-11-11 11:11:11') -> 45
YEAR(d)返回年份SELECT YEAR("2017-06-15"); -> 2017

示例一:向 employees 表中添加一条数据,雇员ID:300,名字:kevin ,email:kevin@sxt.cn ,入职时间:2049-5-1 8:30:30,工作部门:‘IT_PROG’。

insert into employees(EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID) values(300,'kevin','kevin@sxt.cn','2049-5-1 8:30:30','IT_PROG');

转换函数 👻

隐式数据类型转换

隐式数据类型转换是指MySQL服务器能够自动地进行类型转换。如:可以将标准格式的字串日期自动转换为日期类型。

MySQL字符串日期格式为:‘YYYY-MM-DD HH:MI:SS’ 或 ‘YYYY/MM/DD HH:MI:SS’;

显示数据类型转换

显示数据类型转换是指需要依赖转换函数来完成相关类型的转换。如:

  • DATE_FORMAT(date,format) 将日期转换成字符串;

  • STR_TO_DATE(str,format) 将字符串转换成日期;

示例一:向 employees 表中添加一条数据,雇员ID:400,名字:oldlu ,email:oldlu@sxt.cn ,入职时间:2049 年 5 月 5 日,工作部门:‘IT_PROG’。

insert into employees(EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID) values(400,'oldlu','oldlu@sxt.cn', STR_TO_DATE('2049 年 5 月 5 日','%Y 年%m 月%d 日'),'IT_PROG');

示例二:查询 employees 表中雇员名字为 King 的雇员的入职日期,要求显示格式为 yyyy 年 MM 月 dd 日。

select DATE_FORMAT(hire_date,'%Y 年%m 月%d 日') from employees where last_name = 'King';

通用函数 👻

函数名描述实例
IF(expr,v1,v2)如果表达式 expr 成立,返回结果 v1;否则,返回结果 v2。SELECT IF(1 > 0,'正确','错误') ->正确
IFNULL(v1,v2)如果 v1 的值不为 NULL,则返回 v1,否则返回 v2。SELECT IFNULL(null,'Hello Word') ->Hello Word
ISNULL(expression)判断表达式是否为 NULLSELECT ISNULL(NULL); ->1
NULLIF(expr1, expr2)比较两个参数是否相同,如果参数 expr1 与 expr2 相等 返回 NULL,否则返回 expr1SELECT NULLIF(25, 25); ->
COALESCE(expr1, expr2, ...., expr_n)返回参数中的第一个非空表达式(从左向右)SELECT COALESCE(NULL, NULL, NULL, 'bjsxt.com', NULL, 'google.com'); -> bjsxt.com
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result ENDCASE 表示函数开始,END 表示函数结束。如果 condition1 成立,则返回 result1, 如果 condition2 成立,则返回 result2,当全部不成立则返回 result,而当有一个成立之后,后面的就不执行了。SELECT CASE 'oldlu' WHEN 'oldlu' THEN 'OLDLU' WHEN 'admin' THEN 'ADMIN' ELSE 'kevin' END;

示例一:查询部门编号是50或者80的员工信息,包含他们的名字、薪水、佣金。在income列中,如果有佣金则显示‘SAL+COMM’,无佣金则显示'SAL'。

SELECT last_name, salary, commission_pct, if(ISNULL(commission_pct),'SAL','SAL+COMM') income FROM employees WHERE department_id IN (50, 80);

示例二:计算雇员的年报酬,你需要用 12 乘以月薪,再加上它的佣金 (等于年薪乘以佣金百分比)。

SELECT last_name, salary, IFNULL(commission_pct, 0), (salary*12) +(salary*12*IFNULL(commission_pct, 0))AN_SAL FROM employees;

示例三:查询员工表,显示他们的名字、名字的长度该列名为expr1,姓氏、姓氏的长度该列名为expr2。在result列中,如果名字与姓氏的长度相同则显示空,如果不相同则显示名字长度。

SELECT first_name, LENGTH(first_name) "expr1",last_name, LENGTH(last_name) "expr2",    NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM employees;

示例四:查询员工表,显示他们的名字,如果 COMMISSION_PCT 值是非空,显示它。如果COMMISSION_PCT 值是空,则显示 SALARY 。如果 COMMISSION_PCT 和SALARY 值都是空,那么显示 10。在结果中对佣金列升序排序。

SELECT last_name,COALESCE(commission_pct, salary, 10) comm FROM employees ORDER BY commission_pct;

示例五:查询员工表,如果 JOB_ID 是 IT_PROG,薪水增加 10%;如果 JOB_ID 是 ST_CLERK,薪水增加 15%;如果 JOB_ID 是 SA_REP,薪水增加 20%。对于所有其他的工作角色,不增加薪水。

1SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees;

多行函数 💎

聚合函数也称之为多行函数,组函数或分组函数。聚合函数不象单行函数,聚合函数对行的分组进行操作,对每组给出一个结果。如果在查询中没有指定分组,那么聚合函数则将查询到的结果集视为一组。

函数名描述实例
AVG(expression)返回一个表达式的平均值,expression 是一个字段返回 Products 表中Price 字段的平均值:SELECT AVG(Price) AS AveragePrice FROM Products;
COUNT(expression)返回查询的记录总数,expression 参数是一个字段或者 * 号返回 Products 表中 products 字段总共有多少条记录:SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
MAX(expression)返回字段 expression 中的最大值返回数据表 Products 中字段 Price 的最大值:SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)返回字段 expression 中的最小值返回数据表 Products 中字段 Price 的最小值:SELECT MIN(Price) AS MinPrice FROM Products;
SUM(expression)返回指定字段的总和计算 OrderDetails 表中字段 Quantity 的总和:SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

使用聚合函数的原则

  • DISTINCT 使得函数只考虑不重复的值;
  • 所有聚合函数忽略空值。为了用一个值代替空值,用 IFNULL 或 COALESCE 函数。

AVG和SUM 函数 👻

AVG(arg)函数

  • 对分组数据做平均值运算。

  • arg:参数类型只能是数字类型。

SUM(arg)函数

  • 对分组数据求和。

  • arg:参数类型只能是数字类型。

示例:计算员工表中工作编号含有REP的工作岗位的平均薪水与薪水总和。

SELECT AVG(salary),SUM(salary) FROM employees WHERE job_id LIKE '%REP%';

MIN和MAX函数 👻

MIN(arg)函数

  • 求分组中最小数据。

  • arg:参数类型可以是字符、数字、 日期。

MAX(arg)函数

  • 求分组中最大数据。

  • arg:参数类型可以是字符、数字、 日期。

示例:查询员工表中入职时间最短与最长的员工,并显示他们的入职时间。

SELECT MIN(hire_date), MAX(hire_date) FROM employees;

COUNT函数 👻

返回分组中的总行数。

COUNT 函数有三种格式:

  • COUNT(*):返回表中满足 SELECT 语句的所有列的行数,包括重复行,包括有空值列的行。

  • COUNT(expr):返回在列中的由 expr 指定的非空值的数。

  • COUNT(DISTINCT expr):返回在列中的由 expr 指定的唯一的非空值的数。

使用 DISTINCT 关键字

  • COUNT(DISTINCT expr) 返回对于表达式 expr 非空并且值不相同的行数

  • 显示 EMPLOYEES 表中不同部门数的值

示例一:显示员工表中部门编号是80中有佣金的雇员人数。

SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;

示例二:显示员工表中的部门数。

SELECT COUNT(DISTINCT department_id) FROM employees;

组函数和 Null 值

在组函数中使用 IFNULL 函数

SELECT AVG(IFNULL(commission_pct, 0)) FROM employees;
上次编辑于:
贡献者: 刘春龙
评论
  • 按正序
  • 按倒序
  • 按热度
Powered by Waline v2.15.7