MySQL-5函数
概述
- 在MySQL中,为了提高代码重用性和隐藏实现细节,MySQL提供了很多函数。
- 函数可以理解为别人封装好的模板代码。
分类
- 聚合函数
- 数学函数
- 字符串函数
- 日期函数
- 控制流函数
- 窗口函数
聚合函数
概述
聚合函数主要由:count,sum,min,max,avg和group_concat()组成
group_concat():用户实现行的合并
用法:
- 首先根据group by指定的列进行分组,
- 并用分隔符分隔,
- 将同一个分组中的值连接起来,
- 返回一个字符串结果
格式
group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])
说明
- 使用distinct可以排除重复值
- 如果需要对结果中的值进行排序,可以使用order by 子句
- separator是一个字符串,默认为逗号
操作
创建数据库和表
create database mydb4;
use mydb4;
create table emp(
emp_id int primary key auto_increment comment '编号',
emp_name char(20) not null default '' comment '姓名',
salary decimal(10,2) not null default 0 comment '工资',
department char(20) not null default '' comment '部门'
);
向表中插入值
insert into emp(emp_name,salary,department)
values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),
('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),
('刘云鹏',7800,'销售部');
实验
-- 将所有员工的名字合并成一行
select group_concat(emp_name) from emp;
-- 指定分隔符合并
select department,group_concat(emp_name separator ';' ) from emp group by department;
-- 指定排序方式和分隔符
select department,group_concat(emp_name order by salary desc separator ';' ) from emp group by department;
-- 按部门进行分组后,再按工资进行排序
数学函数
函数名 |
描述 |
实例 |
ABS(x) |
返回 x 的绝对值 |
返回 -1 的绝对值: SELECT ABS(-1) – 返回1 |
CEIL(x) |
返回大于或等于 x 的最小整数 |
SELECT CEIL(1.5) – 返回2 |
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 |
函数名 |
描述 |
实例 |
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 |
函数名 |
描述 |
实例 |
RAND() |
返回 0 到 1 的随机数 |
SELECT RAND() --0.93099315644334 |
ROUND(x) |
返回离 x 最近的整数(遵循四舍五入) |
SELECT ROUND(1.23456) --1 |
ROUND(x,y) |
返回指定位数的小数(遵循四舍五入) |
SELECT ROUND(1.23456,3) –1.235 |
TRUNCATE(x,y) |
返回数值 x 保留到小数点后 y 位的值(与 ROUND 最大的区别是不会进行四舍五入) |
SELECT TRUNCATE(1.23456,3) – 1.234 |
字符串函数
函数 |
描述 |
实例 |
CHAR_LENGTH(s) |
返回字符串 s 的字符数 |
返回字符串 RUNOOB 的字符数 SELECT CHAR_LENGTH(“RUNOOB”) AS LengthOfString; |
CHARACTER_LENGTH(s) |
返回字符串 s 的字符数 |
返回字符串 RUNOOB 的字符数 SELECT CHARACTER_LENGTH(“RUNOOB”) AS LengthOfString; |
CONCAT(s1,s2…sn) |
字符串 s1,s2 等多个字符串合并为一个字符串 |
合并多个字符串 SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", “Facebook”) AS ConcatenatedString; |
CONCAT_WS(x, s1,s2…sn) |
同 CONCAT(s1,s2,…) 函数,但是每个字符串之间要加上 x,x 可以是分隔符 |
合并多个字符串,并添加分隔符: SELECT CONCAT_WS("-", “SQL”, “Tutorial”, “is”, “fun!”)AS ConcatenatedString; |
FIELD(s,s1,s2…) |
返回第一个字符串 s 在字符串列表(s1,s2…)中的位置 |
返回字符串 c 在列表值中的位置: SELECT FIELD(“c”, “a”, “b”, “c”, “d”, “e”); |
函数 |
描述 |
实例 |
LTRIM(s) |
去掉字符串 s 开始处的空格 |
去掉字符串 RUNOOB开始处的空格: SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB |
MID(s,n,len) |
从字符串 s 的 n 位置截取长度为 len 的子字符串,同 SUBSTRING(s,n,len) |
从字符串 RUNOOB 中的第 2 个位置截取 3个 字符: SELECT MID(“RUNOOB”, 2, 3) AS ExtractString; – UNO |
POSITION(s1 IN s) |
从字符串 s 中获取 s1 的开始位置 |
返回字符串 abc 中 b 的位置: SELECT POSITION(‘b’ in ‘abc’) – 2 |
REPLACE(s,s1,s2) |
将字符串 s2 替代字符串 s 中的字符串 s1 | <