MySQL 内置函数

山高自有客行路

已于 2024-12-13 09:41:33 修改

阅读量1.2k

点赞数 26

分类专栏： Mysql 文章标签： mysql 数据库

于 2024-12-12 16:50:57 首次发布

本文链接：https://blog.csdn.net/m0_50742275/article/details/144429146

版权

Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

字符串函数

concat(str1, str2, ...)

描述: 这个函数用于连接两个或多个字符串，返回一个新字符串。
语法: concat(str1, str2, ...)
注意点:
- 如果任意一个参数是null，则结果为null。
- 可以连接任意数量的字符串。

示例:

select concat('first name: ', first_name, ', last name: ', last_name) as full_name
from employees;

结果: 将first_name和last_name列中的值连接成完整的姓名格式。

lower(str)

描述: 将给定的字符串转换为小写。
语法: lower(str)

示例:

select lower(email) as lowercase_email
from users;

结果: 将所有用户的电子邮件地址转换为小写形式。

upper(str)

描述: 将给定的字符串转换为大写。
语法: upper(str)

示例:

select upper(country) as uppercase_country
from addresses;

结果: 将所有国家名称转换为大写形式。

substring(str, pos, len)

描述: 提取字符串的一部分。
语法: substring(str, pos[, len])
说明:
- pos: 开始位置（第一个字符的位置是1）。
- len: 要提取的长度（可选），如果省略，则提取到字符串末尾。

示例:

select substring(phone_number, 4, 3) as area_code
from contacts;

结果: 提取电话号码中第4位开始的3个字符作为区号。

replace(str, from_str, to_str)

描述: 在字符串str中查找所有出现的from_str并用to_str替换。
语法: replace(str, from_str, to_str)

示例:

select replace(description, 'old', 'new') as updated_description
from product_descriptions;

结果: 将产品描述中的"old"替换为"new"。

实际应用案例

假设我们有一个名为employees的表，包含员工的信息，包括他们的名字、姓氏和电子邮件。我们可以使用这些字符串函数来处理数据：

-- 创建一个虚拟的员工表
create table employees (
    id int auto_increment primary key,
    first_name varchar(50),
    last_name varchar(50),
    email varchar(100)
);

-- 插入一些示例数据
insert into employees (first_name, last_name, email) values
('john', 'doe', 'john.doe@company.com'),
('jane', 'smith', 'jane.smith@company.com');

-- 使用字符串函数处理数据
select 
    concat(first_name, ' ', last_name) as full_name,
    lower(email) as lowercase_email,
    substring(email, locate('@', email) + 1) as domain
from employees;

-- 更新员工的电子邮件地址，将域名从'company.com'改为'newdomain.com'
update employees
set email = replace(email, 'company.com', 'newdomain.com');

这段代码展示了如何创建一个简单的员工表，插入一些数据，并使用字符串函数生成完整的名字、将电子邮件地址转换为小写以及提取电子邮件的域名。最后还展示了如何更新电子邮件地址中的域名。

数字函数

abs(x)

描述: 返回x的绝对值。
语法: abs(x)
应用场景: 当你需要处理数值差异而不关心正负号时，例如计算两个数之间的差距。
示例:
```
select abs(-10) as absolute_value;
```
结果: 10

ceil(x)

描述: 返回大于等于x的最小整数值（向上取整）。
语法: ceil(x)
应用场景: 当你希望确保一个数值不会低于某个整数值时，例如计算需要多少个容器来装载物品。
示例:
```
select ceil(4.3) as ceiling_value;
```
结果: 5

floor(x)

描述: 返回小于等于x的最大整数值（向下取整）。
语法: floor(x)
应用场景: 当你希望确保一个数值不会超过某个整数值时，例如确定可以分配给用户的资源数量。
示例:
```
select floor(4.7) as floor_value;
```
结果: 4

round(x, d)

描述: 返回参数x的四舍五入数，保留d位小数。
语法: round(x, d)
应用场景: 当你需要对数值进行标准化处理，如货币金额通常需要保留两位小数。

示例:

select round(4.567, 2) as rounded_value;

结果: 4.57

mod(n, m)

描述: 返回n除以m后的余数。
语法: mod(n, m)
应用场景: 在循环或模式匹配中非常有用，比如判断一个数字是否为偶数。
示例:
```
select mod(10, 3) as remainder;
```
结果: 1

日期和时间函数

now()

描述: 返回当前的日期和时间。
语法: now()
应用场景: 记录事件发生的时间戳，或者在插入记录时自动添加当前时间。
示例:
```
select now() as current_datetime;
```
结果: 当前系统时间和日期（例如：2024-12-12 15:56:00）

date()

描述: 从 datetime 或 timestamp类型的值中提取日期部分。
语法: date(datetime)
应用场景: 如果你有一个包含日期和时间的数据，但你只关心日期（年、月、日），那么可以使用这个函数来去除时间部分。

示例:

select date('2024-12-12 15:56:00') as current_datetime;

结果: 每个事件的时间都被转换为仅包含日期的格式。（例如：2024-12-12 ）

date_format

描述: 按照指定的格式字符串来格式化日期或时间值。
语法: date_format(date,format)
应用场景: 可用于格式化日期或时间值。
常用格式说明符

%Y：四位数的年份 (0000-9999)

%y：两位数的年份 (00-99)

%m：两位数的月份 (01-12)

%c：月份，数字，没有前导零 (1-12)

%d：两位数的月份中的天数 (00-31)

%e：月份中的天数，数字，没有前导零 (0-31)

%H：小时 (00-23)

%h 或 %I：小时 (01-12)

%i：分钟 (00-59)

%S 或 %s：秒 (00-59)

%W：星期名 ('Monday'..'Sunday')

%a：缩写的星期名 ('Mon'..'Sun')

%p：AM或PM

%w：星期中的第几天 (0=Sunday ... 6=Saturday)

%j：一年中的第几天 (001-366)

示例:如果你有一个日期字段名为created_at，并且你想要以YYYY-MM-DD HH:MM:SS格式显示它，你可以使用如下查询：
```
select date_format(date ,'%Y-%m-%d') as formatted_date from your_table;
```
结果: 2024-12-12

curdate()

描述: 返回当前日期。
语法: curdate()
应用场景: 可用于创建当天的报告或筛选当天的数据。
示例:
```
select curdate() as current_date;
```
结果: 2024-12-12

curtime()

描述: 返回当前时间。
语法: curtime()
应用场景: 如果只需要记录时间部分，而不需要日期部分时。
示例:
```
select curtime() as current_time;
```
结果: 15:56:00

date_add(date, interval expr unit)

描述: 向日期添加一个时间间隔。
语法: date_add(date, interval expr unit)
应用场景: 用于计算未来的日期或过去的时间点，例如计算订单的预计交付日期。

示例:

select date_add('2024-12-12', interval 1 day) as next_day;

结果: '2024-12-13'

date_sub(date, interval expr unit)

描述: 向日期减去一个时间间隔。
语法: date_sub(date, interval expr unit)
应用场景: 用于计算未来的日期或过去的时间点，例如计算订单的预计交付日期。

示例:

select date_sub('2024-12-12', interval 1 day) as last_day;

结果: '2024-12-11'

datediff(expr1, expr2)

描述: 计算两个日期之间的天数差。
语法: datediff(expr1, expr2)
应用场景: 计算项目持续时间、客户年龄等。

示例:

select datediff('2024-12-25', '2024-12-12') as days_between;

结果: 13

timestampdiff(unit, datetime_expr1, datetime_expr2)

描述: 计算两个时间戳之间的差值，单位可以是年、月、日、小时、分钟或秒。
语法: timestampdiff(unit, datetime_expr1, datetime_expr2)
应用场景: 用于计算精确的时间差，例如会员资格的有效期。

示例:

select timestampdiff(year, '1980-01-01', curdate()) as age;

结果: 44 （假设当前年份为2024）

条件函数

if(condition, value_if_true, value_if_false)

描述: 如果条件成立则返回一个值，否则返回另一个值。
语法: if(condition, value_if_true, value_if_false)
应用场景: 简单的二元条件判断，例如判断用户是否为成年人。

示例:

select if(age >= 18, 'adult', 'minor') as age_group from users;

`CASE WHEN condition THEN result [WHEN condition THEN result ...] [ELSE result] END`

描述: 复杂的条件判断结构。

语法:

case 
  when condition then result
  ...
  else result
end

应用场景: 适用于多条件判断的情况，例如根据成绩评定等级。

示例:

select 
  case 
    when score >= 90 then 'a'
    when score >= 80 then 'b'
    when score >= 70 then 'c'
    else 'd'
  end as grade
from students;

聚合函数

count(column_name)

描述: 计算列中非null值的数量。
语法: count(column_name)
应用场景: 统计表中的记录数，例如统计用户总数。

示例:

select count(*) as total_users from users;

sum(column_name)

描述: 计算列中所有值的总和。
语法: sum(column_name)
应用场景: 计算总收入、总支出等。

示例:

select sum(salary) as total_salary from employees;

avg(column_name)

描述: 计算列中所有值的平均值。
语法: avg(column_name)
应用场景: 计算平均工资、平均成绩等。

示例:

select avg(salary) as average_salary from employees;

max(column_name)

描述: 返回列中的最大值。
语法: max(column_name)
应用场景: 找出最高工资、最高评分等。

示例:

select max(salary) as highest_salary from employees;

min(column_name)

描述: 返回列中的最小值。
语法: min(column_name)
应用场景: 找出最低工资、最低评分等。

示例:

select min(salary) as lowest_salary from employees;

实际应用案例

假设我们有一个名为sales的表，包含销售记录，包括销售日期和销售额。我们可以使用这些函数来处理数据：

-- 创建一个虚拟的销售表
create table sales (
    id int auto_increment primary key,
    sale_date date,
    amount decimal(10, 2)
);

-- 插入一些示例数据
insert into sales (sale_date, amount) values
('2024-11-01', 100.00),
('2024-11-02', 150.00),
('2024-12-01', 200.00);

-- 使用日期和时间函数处理数据
select 
    sale_date,
    date_add(sale_date, interval 1 year) as renewal_date,
    datediff(curdate(), sale_date) as days_since_sale
from sales;

-- 使用聚合函数分析数据
select 
    count(*) as total_sales,
    sum(amount) as total_amount,
    avg(amount) as average_sale_amount,
    max(amount) as max_sale_amount,
    min(amount) as min_sale_amount
from sales;

-- 使用条件函数生成销售状态
select 
    sale_date,
    amount,
    case 
        when amount > 200 then 'high'
        when amount between 100 and 200 then 'medium'
        else 'low'
    end as sale_status
from sales;

这段代码展示了如何创建一个简单的销售表，插入一些数据，并使用日期和时间函数来计算续订日期和自销售以来的天数。同时，还展示了如何使用聚合函数来统计销售总数、总金额、平均销售金额、最高和最低销售金额。最后，使用条件函数生成销售状态，帮助快速评估销售表现。