其他函数的使用

介绍 MySQL中其他函数的使用，主要有以下几大类型：

  1. 字符函数；
  2. 数学函数；
  3. 日期时间函数；
  4. 流程控制类函数；
  5. 自定义函数。

字符函数

任务描述

本关任务：以首字母大写，其他字母小写的方式显示所有员工的姓名。

相关知识

为了完成本关任务，你需要掌握常用字符函数的使用。

下面为字符串的常用函数：

函数 作用
CONCAT() 字符串的拼接
CONCAT_W 使用指定的字符将目标字符串进接
FORMAT() 数字格式化
LOWER() 串全部转换为小写
UPPER() 串全部转换为大写
LEFT() 左侧的字符串
RIGHT() 右侧的字符串
LENGTH() 计算字符串的长度
LTRIM() 前导的空格
RTRIM() 后续的空格
TRIM() 前导和后续的空格
SUBSTRING() 字符串截取
SUBSTRING_INDEX() 分隔字符串
REPLACE() 字符串替换
[NOT] LIKE 模式匹配

CONCAT()与CONCAT_WS()

他们的作用都是将字符进行连接，但是他们之间也有一点小区别。下面是使用CONCAT()将My和SQL字符进行连接：

CONCAT_WS()代表使用指定字符进行连接，最少需要三个参数，第一个参数为指定连接符：

LEFT()与RIGHT()

他们是进行左右侧的字符获取，需要两个参数，第一个参数告诉他从哪个字符串中获取，第二个参数是获取的长度：

LTRIM()、RTRIM() 与 TRIM()

这三个函数都是可以去掉字符串中含有的空格，我们先使用LENGTH()函数来统计含有空格的字符串的长度，分别去掉其中字符前导、后导和全部的空格，下面我们来看一下他们的区别：

另外TRIM()函数出除了能删除空格以外，还能删除字符串中自定字符：

上面操作就是将??MySQL???中的前导?删除，若要删除后导的?，只需将LEADING替换成TRAILING，若要将其中的?都删掉，替换成BOTH关键字即可。

SUBSTRING()、SUBSTRING_INDEX()与REPLACE()

REPLACE()函数
思考：那如果在MySQL中间也出现了?号，还能使用上面的函数进行删除吗？

答疑：很显然使用之前学到过的函数是不能将中间部分的?删除，那么我们如何将中间的?去掉呢？就是使用REPLACE()函数进行字符串的替换。

表示将字符串中的?全部替换成''，但使用时并不表示只能一个字符替换一个字符，可以一对多形式或多对一形式：

SUBSTRING()函数
SUBSTRING()函数是用于字符串的截取：

表示从第一位（注意第一位为1而非0）开始截取MySQL的二位字符。

**SUBSTRING_INDEX()**函数
SUBSTRING_INDEX()函数是用于通过特殊符号将字符串分隔的：

表示将字符串www.educoder.net通过.进行分隔，可从前后取字符串。

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充，具体任务如下：

  • 以首字母大写，其他字母小写的方式显示所有员工的姓名。
    employee员工表数据结构如下：
Id Name
1 lYN
2 wZQ
3 xLC
4 aLW
5 lZY

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

1
2
3
4
5
6
7
8
9
+------+
| Name |
+------+
| Lyn  |
| Wzq  |
| Xlc  |
| Alw  |
| Lzy  |
+------+

我的代码

1
2
3
4
5
#请在此添加实现代码
########## Begin ##########
select concat ( upper(left(Name,1)) , lower(right(Name,2) )) as Name from employee ;
########## End ##########

数学函数

任务描述

本关任务：根据公式修改学生的最终成绩。

相关知识

本章节我们主要介绍数值运算符和比较运算符与函数的使用。

数值运算符

我们常说的“加减乘除”就是数值运算符，例如：

下面是常与数值结合使用的函数：

函数 说明
CEIL() 进一（向上）取整
FLOOR() 舍一（向下）取整
POWER() 幂运算
SQRT() 平方根运算
ROUND() 四舍五入
ABS() 绝对值计算
RAND() 生产0-1的随机数
TRUNCATE() 数字截取
DIV 整数除法
MOD 取余数(取模)

CEIL()和FLOOR()
这两个函数都是对值进行取整，CEIL()函数是向上取整，FLOOR()是向下取整：

DIV和MOD
我们可以直接对数值使用/（除法）或%（取余），但仅此是计算远远不及我们的需求。

因此还提供了DIV整数除法函数：

但MOD取模函数与%计算是等价的：

ROUND()、POWER()、SQRT()和TRUNCATE()

  • ROUND()函数：四舍五入， 下面示例为：

  • POWER()函数：幂运算， 下面示例为2^3=8：

  • SQRT()函数：平方根运算， 下面示例为：

  • TRUNCATE()函数：数字截取，只做截取，不发生四舍五入， 下面示例为：

比较运算符

除了数值运算符，MySQL中还有比较运算符：

函数 说明
[NOT] BETWEEN…AND… [不]在范围内
[NOT] IN() [不]在列出值范围内
IS [NOT] NULL [不]为空

  • BETWEEN...AND...：指定一个范围，只要选择的数字在这个范围内则为1否则为0；

  • IN()：指定列出值，只要选择的数字存在其中则为1否则为0；

  • IS NULL：判断字段是否为空。

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充，具体任务如下：

  • 根据下面表达式修改学生的最终成绩，结果使用四舍五入保留两位小数：

Score表结构如下：

字段名 说明
s_name 姓名
s_score 成绩

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+-----------+---------+
| s_name    | s_score |
+-----------+---------+
| Amanda    |   72.93 |
| Evie      |   12.63 |
| Jennifer  |   82.43 |
| Katherine |   63.12 |
| Megan     |   59.02 |
| Oink      |   27.43 |
| Priti     |   43.33 |
| Reiko     |   88.93 |
| Sarah     |   92.33 |
| Sheila    |   81.23 |
| Wallis    |   74.62 |
| Yetta     |   72.93 |
+-----------+---------+

注意：求完根号后就四舍五入取两位小数，与 s_score 相减后再截取两位小数做最终输出。

我的代码

1
2
3
4
5
#请在此添加实现代码
########## Begin ##########
update Score set s_score = truncate( s_score-round(sqrt( (power(4,4)-power(3,3) ) / 4 ),2) ,2) ;

########## End ##########

日期时间函数和流程控制类函数

任务描述

本关任务：根据要求编写符合输出条件的SQL。

相关知识

略

日期函数

本章节介绍MySQL中的日期时间函数，下面列出常用的函数：

函数 说明
NOW() 当前日期和时间
CURDATE() 当前日期
CURTIME() 当前时间
DATE_ADD() 日期变化
DATEDIFF() 日期差值
DATE_FORMAT() 日期格式化
YEAR() 返回日期的年份
MONTH() 返回日期的月份
WEEK() 返回日期的周号

NOW()、CURDATE()和CURTIME()

  • NOW()返回当前日期和时间；

  • CURDATE()则单独返回当前日期，CURTIME()则返回当前时间。

DATE_ADD()、DATEDIFF()和DATE_FORMAT()

  • DATE_ADD()从函数名称来看会想当然的认为是时间的添加，但其实可以添加也可以减少，因此我们将其说明为日期的变化，第一个参数为指定时间，第二个参数为增加（减少）的单位值（YEAR、MONTHS、WEEK、DAY）；

  • DATEDIFF()函数则返回二个日期之间的差值；

  • DATE_FORMAT()函数为日期格式化，也就是将我们常见的日期格式转换成另一种格式。

以下是在format字符串中常用的说明符，更多的说明符可在 MySQL 官方文档中进行查看：

格式 描述
%d 月的天，数值（00-31）
%H 小时 （00-23）
%h 小时 （01-12）
%m 月，数值（00-12）
%p AM 或 PM
%s 秒（00-59）
%T 时间，24-小时（hh:mm:ss）
%Y 年，4 位

YEAR()和MONTH()

流程控制类函数

函数 说明
IF() 判断，流程控制
IFNULL() 字段值是否为空
CASE 搜索语句

  • IF()函数：IF(a,b,c) 如果a为真返回b，否则返回c；

  • IFNULL()函数：IFNULL(a,b) 如果a不为null返回a，否则返回b；

  • CASE：CASE WHEN a then b end 判断如果a为真返回b。

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充，具体任务如下：

  1. 查询所有学生的出生年份及至2019-01-01时的年龄；
  2. 查询所有课程中的最高分、最低分、平均分和及格率，按课程id升序输出。

Student表结构：

字段名 说明
s_id 学生id
s_name 学生姓名
s_birth 学生生日
s_sex 学生性别

Course表结构：

字段名 说明
c_id 课程id
c_name 课程名

Score表结构：

字段名 说明
s_id 学生id
c_id 课程id
s_score 学生成绩

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
#请在此添加实现代码
########## Begin ##########

##########  查询学生出生年份及年龄 ##########
select year(s_birth) as year ,(2019-year(s_birth)) as '年龄' from Student;


##########  查询课程的最高分、最低分、平均分和及格率 #########
select Score.c_id as '课程id',c_name as '课程名', max(s_score) as '最高分',min(s_score) as '最低分',round(avg(s_score),2) as '平均分' ,round((count(Score.s_score>=60 or null)/count(s_score))*100,2) as '及格率' from Student,Course,Score where Score.c_id = Course.c_id group by Score.c_id;


########## End ##########

自定义函数

任务描述

本关任务：定义一个函数，输出三个数中的最大值。

相关知识

我们学习了MySQL中的内置函数，但是我们可能经常需要功能，但这些功能系统并未提供，那么，就需要自定义，下面我们就一起来学习MySQL的自定义函数。

自定义函数

用户自定义函数（user-defined function， UDF）是一种对MySQL扩展的途径，其用法与内置函数相同。

自定义函数和内置函数一样，具有两个必备条件：

  • 参数：不一定要有；
  • 返回值：一定要有。

函数可以返回任意类型的值，同样可以接收这些类型的参数，且函数的参数和返回值之间没有必然的内在联系。

创建自定义函数SQL：

1
2
3
4
5
6
7
8
delimiter //　　#如果函数体只有一条语句, begin和end可以省略, 同时delimiter也可以省略
　　create function 函数名(形参列表) returns 返回类型　　#注意是retruns
　　begin
　　　　函数体　　　　#函数内定义的变量如：set @x = 1; 变量x为全局变量，在函数外面也可以使用
　　　　返回值
　　end
　　//
delimiter ;

关于函数体:

  1. 函数体由合法的SQL语句构成；
  2. 函数体可以是简单的SELECT或INSERT语句；
  3. 函数体如果为复合结构则使用begin...end语句；
  4. 复合结构可以包含声明，循环，控制结构。
    自定义无参函数
    例如我们在上一章节中学习了系统自带的NOW()函数，他的输出形式是固定的，但是现在我想把输出用汉语表示，我们可以使用DATA_FORMAT()函数：

但是如果我要经常使用到这个功能，难道要每次都来写吗？ 这样显然很麻烦，现在我们就将这个过程来封装成一个自定义函数，分析可知函数中只需含一句查询 SQL ，因此我们首次采用省略delimiter和begin...end的写法：

1
2
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');

这样我们每次想要使用功能的时候就可以直接查询了，便捷了很多，例如：

自定义带参数的函数
上面的自定义函数为无参类型的，现在我们来定义一个计算两个数的和的函数：

1
2
3
4
5
6
7
delimiter //
CREATE FUNCTION f2(a int, b int) RETURNS int
begin
    return a + b;
end
//
delimiter ;

删除自定义函数
当我们不再需要自定义的函数时，我们可以使用删除 SQL 将其删除：

1
drop function [if exists] function_name;

自定义函数中定义变量

定义全局变量
赋值的操作符：=或:=。
作用域：针对于当前会话（连接）有效，同于会话变量的作用域，应用在任何地方，也就是begin...end里面或begin...end外边。

  • 声明变量并初始化

    1
    2
    SET @用户变量名 = 值;
    SET @用户变量名 := 值;

    定义局部变量
    作用域：仅仅在定义它的begin...end中有效。

  • 声明

    1
    2
    DECLARE 变量名 类型;
    DECLARE 变量名 类型 DEFAULT 值;

    赋值

    1
    2
    SET 局部变量名 = 值;
    SET 局部变量名 := 值;

流程控制

自定义函数中可以使用流程控制来控制语句的执行。

MySQL 中可以使用IF语句、CASE语句、和WHILE语句等来进行流程控制：

  • IF语句
    IF语句用来进行条件判断。根据是否满足条件，将执行不同的语句。其语法的基本形式如下：

    1
    2
    3
    4
    IF search_condition THEN statement_list
       [ELSEIF search_condition THEN statement_list] ...
       [ELSE statement_list]
    END IF;

  • CASE语句
    也用来进行条件判断，其可以实现比IF语句更复杂的条件判断。CASE语句在上一章节中讲过了，这里就不做说明了。

  • WHILE语句
    WHILE语句是当满足条件时，执行循环内的语句。WHILE语句的基本语法形式如下：

    1
    2
    3
    [begin_label:] WHILE search_condition DO
    statement_list
    END WHILE [end_label]

    begin_label参数和end_label参数分别表示循环开始和结束的标志，两个标志必须相同，而且都可以省略；statement_list参数表示需要循环执行的语句。

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充，具体任务如下：

  • 定义一个函数，输出三个数中的最大值，函数形式为：
  • fn_three_max(param_1 int, param_2 int, param_3 int)。

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

1
2
3
4
5
+--------+
| maxNum |
+--------+
|    999 |
+--------+

我的代码

1
2
3
4
5
6
7
#请在此添加实现代码
########## Begin ##########
create function fn_three_max(param_1 int,param_2 int, param_3 int)returns int
    return 999;

########## End ##########