MySQL函数
一、字符串函数
函数是指一段可以直接被另一段程序调用的程序或代码。
1.1、常见字符串函数
- CONCAT
SELECT CONCAT('Hello', 'MySQL'); |
此查询将连接两个字符串并返回结果“HelloMySQL”。
- LOWER
SELECT LOWER('Hello'); |
该函数用于将字符串转换为小写。
- UPPER
SELECT UPPER('Hello'); |
该函数用于将字符串转换为大写。
- LPAD
SELECT LPAD('01', 5, '.'); |
在此查询中,“01”是原始字符串,5 是填充后生成的字符串的所需长度,“.”是填充字符。这将生成一个总长度为 5 个字符的字符串,并在“01”的左侧添加句点 ('.')。
- RPAD
SELECT RPAD('01', 5, '.'); |
在 MySQL 中使用句点 ('.') 右填充字符串“01”,直到生成的字符串的总长度为 5 个字符。
- TRIM
SELECT TRIM(' Hello MySQl '); |
其中前和尾部空格已被删除。如果您只想从左边 () 或右边 () 中删除空格,可以使用函数:LTRIM和RTRIM。
-- 删除前(左)空格 SELECT LTRIM(' Hello MySQl '); -- 删除尾(右)空格 SELECT RTRIM(' Hello MySQl '); |
SELECT SUBSTRING('Hello MySQL', 1, 5); |
从第一个字符 ('H') 开始并延伸到接下来的 5 个字符的子字符串。
1.2、案例示范
由于业务需求变更,企业员工的工号,统一为5位数,目前不足5位数的全部在前面补0。比如:1号员工的工号应该为00001。
UPDATE employee SET WorkNo= LPAD(WorkNo, 5, '0'); |
这将更新员工表中的工号,确保每个工号都是5位数,不足的地方在前面用0填充。
二、数值函数
2.1、常见数值函数
- CEIL
SELECT CEIL(1.5); |
将数值向上舍入到最接近的整数。在 的情况下,它会将 1.5 四舍五入到最接近的整数即2。
- FLOOR
SELECT FLOOR(1.5); |
将数值向下舍入到最接近的整数,它将 1.5 四舍五入到最接近的整数,即 1。
- MOD
SELECT MOD(3, 4); |
用于查找除法运算的余数,因为 3 除以 4 等于 0,余数为 3。
- RAND
SELECT RAND(); |
生成一个介于 0(含)和 1(不含)之间的随机浮点数,它将返回一个介于 0 和 1 之间的随机数。结果将类似于 ,其中小数点后的数字将在每次运行查询时发生变化。
- 示例:通过数据库的函数,生成一个六位数的随机验证码
SELECT LPAD(ROUND(RAND() * 1000000, 0), 6, '0'); |
生成一个介于 0 和 1,000,000 之间的随机数,然后使用该函数将结果左填充为零,以确保其长度为 6 位。
三、日期函数
3.1、常见日期函数
- CURDATE()
SELECT CURDATE(); |
以“YYYY-MM-DD”格式返回当前日期,其中“YYYY”表示年份,“MM”表示月份,“DD”表示日期。
- CURTIME()
SELECT CURTIME(); |
以“HH:MM:SS”格式返回当前时间,其中“HH”表示小时 (00-23),“MM”表示分钟(00-59),“SS”表示秒 (00-59)。
- NOW()
SELECT NOW(); |
以“YYYY-MM-DD HH:MM:SS”格式返回当前日期和时间。
- YEAR,MONTH,DAY
SELECT YEAR(NOW()) AS current_year, MONTH(NOW()) AS current_month, DAY(NOW()) AS current_day; |
获取当前的年、月和日。
- DATE_ADD
SELECT DATE_ADD(NOW(), INTERVAL 70 DAY); |
这将在当前日期和时间上增加 70 天,并返回结果。
- DATEDIFF
SELECT DATEDIFF('2023-11-18', '2023-10-07'); |
这将返回 2023 年 11 月 18 日至 2023 年 10 月 7 日之间的天数。结果将是一个数值,表示以天为单位的差异。如果第一个日期晚于第二个日期,则结果为正;如果第一个日期更早,结果将为负数。
3.2、案例示范
查询所有员工的入职天数,并根据入职天数倒序排序
SELECT Name, entry_date, DATEDIFF(NOW(), entry_date) AS entrydays FROM employee ORDER BY entrydays DESC; |
四、流程控制函数
4.1、常见流程控制函数
- IF
SELECT IF(true, 'OK', 'Error'); |
该函数检查条件,并且由于它是 true,因此它返回第二个参数 ('OK')。如果条件为 false,它将返回第三个参数 ('Error')。
- IFNULL
SELECT IFNULL('OK', 'Default'); |
MySQL中的函数用于处理NULL值。如果第一个参数不是 NULL,则返回第一个参数;否则,它将返回第二个参数。在您的示例中,由于第一个参数是字符串“OK”(不是 NULL),因此该函数将返回“OK”。
- CASE WHEN THEN ELSE END
SELECT name, CASE region WHEN 'BeiJing' THEN 'No.1' WHEN 'ShangHai' THEN 'No.1' ELSE 'No.2' END AS result FROM employee; |
需求:查询employee表的员工姓名和地址(北京/上海 --->一线城市;其他--->二线城市)
4.2、案例示范
统计班级各个学员的成绩,展示的规则如下:>= 85,展示优秀;>=60,展示及格;否则,展示不及格。
SELECT id, name, CASE WHEN math >= 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END AS '数学', CASE WHEN english >= 85 THEN '优秀' WHEN english >= 60 THEN '及格' ELSE '不及格' END AS '英语', CASE WHEN chinese >= 85 THEN '优秀' WHEN chinese >= 60 THEN '及格' ELSE '不及格' END AS '语文' FROM score; |