MySQL数据库学习-基础函数

2025-08-04

函数是一段可以被直接调用的程序或者代码。

字符串函数
CONCAT(S1,S2,...,SN)：字符串拼接；
LOWER(str)：全部转换为小写；
UPPER(str)：全部转换为大写；
LPAD(str,n,pad)：左填充，用字符串pad对str的左边进行填充，达到n个字符串的长度，例如LPAD(01,5,st)——sts01。
RPAD(str,n,pad)：右填充，用字符串pad对str的右边进行填充，达到n个字符串的长度；
TRIM(str)：去掉字符串的头部和尾部的空格；
SUBSTRING(str,start,len)：返回从字符串str从start位置起的len个长度的字符串。
数值函数
CEIL(x)：向上取整；
FLOOR(x)：向下取整；
MOD(x,y)：取余；
RAND()：随机0-1数；
ROUND(x,y)：四舍五入；
日期函数
CURDATE()：当前日期；
CURTIME()：当前时间；
NOW()：当前的日期+时间；
YEAR(date)：获取年份；
MONTH(date)：获取月份；
DAY(date)：获取日；
DATE_ADD(date, INTERVAL expr type)：返回一个日期/时间值加上一个时间间隔expr后的时间值；
DATEDIFF(date1,date2)：date1到date2之间的天数；
流程函数
IF(value, t, f)：如果value为true，则返回t，否则返回f。
IFNULL(value1,value2)：如果value1不为空，返回value1，否则返回value2。
CASE WHEN [val1] THEN [res1] ...ELSE [dafault] END：如果val1为true，返回res1，...否则返回default默认值。
CASE [expr] WHEN [val1] THEN [res1] ...ELSE [dafault] END：如果expr值等于val1，返回res1，...否则返回default默认值。

题目

补充 due_date 和 completion_date 数据：

你之前插入的那些任务，很多 due_date 都是 null。现在，请为所有 due_date 为 null 且 status 不是 'Done' 的任务，随机生成一个在当前日期到未来两个月之间的 due_date。

修改邮箱，比如qq.com或者163.com——已经修改了wangwu email为qq.com，zhaoliu email为163.com。

任务一：字符串与日期函数应用 (String, Date Functions)

用户邮箱域名分析：

你想统计一下公司内部有多少种不同的邮箱域名。查询 users 表，列出所有不重复的邮箱域名（例如，hongcha@example.com 的域名就是 example.com）。

提示：你需要用到 SUBSTRING 和 LOCATE 或 INSTR 函数来找到 @ 符号的位置。

项目名称标准化显示：

在报表展示时，你希望所有项目名称都能以大写字母开头。查询 projects 表，显示项目名称，但要求所有项目名称的首字母大写，其余小写。例如，'smart factory os development' 应该显示为 'Smart factory os development'。

提示：结合 UPPER, LOWER, SUBSTRING 来实现。

任务截止日期概览：

你想要一个快速的概览，看看每个任务的截止日期是哪年哪月。查询 tasks 表，显示任务名称，以及它的截止日期的年份和月份（格式如 "2023年9月"）。

提示：使用 YEAR(), MONTH() 和 CONCAT()。

任务状态优先级分类：

现在你想要一个更直观的任务优先级视图。查询 tasks 表，显示任务名称，以及一个根据其 status 字段生成的优先级描述：

'Blocked' 状态的任务显示为 'Urgent: Action Required'

'In Progress' 的显示为 'Normal: Ongoing'

'New' 或 'Open' 的显示为 'Low: Pending'

'Done' 的显示为 'Completed'

其他未知状态（如果有的话）显示为 'Unknown Status'

提示：这是 CASE WHEN 语句的典型应用场景。

负责人状态智能显示：

在展示任务列表时，如果 assigned_to_user_id 为空，你希望显示 'Unassigned'，否则显示分配人的用户名。查询 tasks 表，显示任务名称，以及其负责人（如果是未分配，则显示 'Unassigned'，否则显示对应的用户id）。

提示：使用 IFNULL() 或 CASE WHEN。

项目负责人空缺预警：

你非常关心项目有没有负责人。查询 projects 表，显示项目名称和项目负责人，如果负责人为空，显示 '!!!Missing Responsible PM!!!'。

提示：同样是 IFNULL() 或 CASE WHEN 的应用。

代码


use industrial_project_management;

update tasks 
	set due_date=DATE_ADD('2025-08-04', INTERVAL round(rand()*60) day )
	where due_date is null and status!='Done';

select distinct
lower(substring(email,locate('@',email)+1))
from users;

SELECT
    CONCAT(UPPER(SUBSTRING(project_name, 1, 1)), LOWER(SUBSTRING(project_name, 2))) AS standardized_project_name
FROM projects;

select
task_name,year(due_date),month(due_date)
from tasks;

SELECT
    task_name,
    CASE
        WHEN status = 'Blocked' THEN 'Urgent: Action Required'
        WHEN status = 'In Progress' THEN 'Normal: Ongoing'
        WHEN status IN ('New', 'Open') THEN 'Low: Pending' -- 使用 IN 关键字
        WHEN status = 'Done' THEN 'Completed'
        ELSE 'Unknown Status'
    END AS task_priority
FROM tasks;

select 
task_name,ifnull(assigned_to_user_id,'Unassigned')
from tasks;

select 
project_name,ifnull(responsible_user_id,'Unassigned')
from projects;

← 返回