【学习总结】SQL的学习-4-常用函数介绍

参考链接

目录

=======================================================

合并字符串函数

概述

  • 字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。

合并字符串函数concat

  • 语法：concat(s1,s2,...,sn)

  • 解析

    • 该函数会将传入的参数连接起来返回合并的字符串类型的数据。

    • 如果其中一个参数为null，则返回值为null。

  • 合并字符串（即数据不是来自表，而是直接手动键入的）：

    • 示例1：select concat ('huang','lao','shi'), concat('huang','lao','shi',null) -- 直接输出语句中的字符串

    • 示例2：select concat(curdate(),12.2);

  • 将字段合并输出

    • 示例：select concat (name, "|", house_location) from houses;

合并字符串函数concat_ws

  • 语法：concat_ws(seq, s1, s2, ... , sn)

  • 解析

    • 本函数与concat()的区别：多了一个表示分隔符的seq参数，不仅将传入的其他参数连接起来，而且会通过分隔符将各个字符串分隔开来。

    • 分隔符可以是一个字符，也可以是其他参数。如果分隔符为null，则返回结果为null。函数会忽略任何分隔符后的参数null。

  • 使用分隔符合并字符串并输出的示例

    • 示例1：select concat_ws('-','020','8331234567');

    • 示例2：select concat_ws('-','020','8331234567'); -- 连接多个字符串

    • 示例3：select concat_ws('null','020','8331234567'), concat_ws('-','020','null','8331234567'); -- null的处理

=======================================================

比较字符串大小的函数

获取字符串长度的函数length

  • 关键字：

    • 获取字符串长度函数：length()

    • 获取字符数函数：char_length()

  • 语法：

    • length(str)：一个汉字算3个字符，一个数字或字母算一个字符。

    • char_length(str)：不管是汉字还是数字或字母，都算一个字符。

  • 示例：

    • 示例1：对比汉字和字母数字的不同结果

    • 示例2：数字的结果对比

    • 示例3：对负数的处理。注：ceil(n) 取大于等于数值n的最小整数；

比较字符串大小的函数strcmp

  • 关键字：strcmp()

  • 语法：strcmp(str1, str2)

  • 解析：

    • 如果str1>str2，则返回1；如果str1<str2，则返回-1；如果str1==str2，则返回0。

  • 示例：(根据安装时的配置，遵循utf-8字符串的规律，基于不同字符集，可能结果不同)

    • 示例1：等长，且相同大小写的字母比较

    • 示例2：相同字母的大小写比较

    • 示例3：不等长的字母比较

    • 示例4：单个数字比较

    • 示例5：不等长的数字比较

    • 示例6：单个中文字符比较

=======================================================

大小写转换函数

将字母转换为大写的函数upper和ucase

  • 语法：

    • upper(s)

    • ucase(s)

将字母转换为小写的函数lower和lcase

  • 语法：

    • lower(s)

    • lcase(s)

示例

  • 字母转换对比：结果一致

  • 只有非字母符号时：直接输出，结果不变

  • 字母与非字母组合：字母正常转换，非字母不变

=======================================================

查找字符串函数

返回字符串位置的函数find_in_set

  • 关键字：find_in_set()

  • 语法：find_in_set(str1, str2)

  • 解析：

    • 该函数会返回在字符串str2中与str1相匹配的字符串的位置，参数str2字符串中包含若干个用逗号隔开的字符串。

  • 示例：


返回指定字符串位置的函数field

  • 关键字：field()

  • 语法：field(str, str1, str2...)

  • 解析：

    • 该函数返回第一个与字符串str匹配的字符串的位置。

  • 区别：

    • 字符串个数：find_in_set只有两个字符串，而field可以传入多个字符串。
    • 空格：find_in_set空格在任意位置都有效，而field放在前面的空格有效，放在后面的空格可忽略。

  • 示例：

返回指定位置的字符串的函数elt

  • 关键字：elt()

  • 语法：elt(n,str1,str2,...);

  • 解析：

    • 返回指定位置n的字符串

  • 注意：

    • 如果n超过了字符串个数的范围，(大于或小于)，则返回null；

    • 对于空格：任意位置的空格(前面的空格或者后面的空格)都算在返回结果内，不会自动删除。

  • 示例：


=======================================================

字符串截取与替换函数

从左边或右边截取子字符串的函数left和right

  • 语法：left(str, num), right(str, num)

  • 解析：

    • 返回字符串str中包含前num个字母(从左边数)、(从右边数)的字符串，并按照原顺序返回结果。

    • 参数num超出正常范围时：返回空白，没有null的空白那种。

  • 示例：


截取指定位置和长度的字符串的函数substring

  • 语法1：substring(str, num, len)

  • 解析：

    • 返回字符串str中的第num个位置开始，且长度为len的子字符串。

  • 示例：

  • 语法2：mid(str, num, len)

  • 解析：

    • substring(str,pos,len)的同义词，参数从1开始，不是从0开始。

  • 示例：

替换字符串的函数replace

  • 语法：replace(str, substr, newstr)

  • 解析：

    • 将字符串str中的子字符串substr用字符串newstr来替换。

  • 示例：

=======================================================

获取整数函数

返回大于或等于数值x的最小整数的函数ceil

  • 语法：ceil(x)

  • 示例：

返回小于或等于数值x的最大整数的函数floor

  • 语法：floor(x)

  • 示例：

截取数值函数truncate

  • 语法：truncate(x,y)

  • 解析：

    • 返回数值x，保留小数点后y位。

  • 注意：

    • y为正数即取小数点，y为负数即往整数位截取；简单粗暴直接截取，并不进行四舍五入。

  • 示例：

函数返回值x经过四舍五入操作后的数值的函数round

  • 语法：round(x)；round(x,y)

  • 解析：

    • x表示原数值，y表示要保留的小数位数。y可以省略。

  • 注意：

    • 负数：数值位按照正数处理，不按照负数的数学规则。

  • 对比：round与truncate

    • round：截取并四舍五入；truncate：直接截取，不四舍五入。

  • 示例：



=======================================================

获取日期和时间的函数

获取当前日期和时间的函数now和sysdate

  • 关键字：

    • now(); sysdate(); current_timestamp(); current_date(); current_time();

  • 语法：

    • 使用select 函数名(); -- 来查看时间函数的返回值

  • now():返回该条语句(而不是该函数)运行时的具体日期时间。

  • sysdate()：与now()类似。

  • 三个current时间函数：结果同now，不变。

    • current_timestamp()：获取当前时间戳，包括年月日+时分秒。

    • current_date()：获取当前日期，包括年月日。

    • current_time()：获取当前时间，包括时分秒。

  • 辨析：

    • now()：在语句执行开始时，值就得到了，后面再取，值不变；

    • sysdate()：在函数执行时动态得到值，后面再取，值会变。

  • 示例：


获取日期和时间各部分值year和hour等




=======================================================

格式化函数

format函数

  • 语法与解析：

    • 格式化数字：format(x,y)：把x格式化为以逗号隔开的数字序列，y是结果的小数位数。

    • 格式化时间：date_format(date,fmt), time_format(time,fmt) ：

      • 依照字符串fmt格式化时间：'%+字母+分隔符+%+字母+分隔符+...'
      • 不同字母代表不同含义：y-年，m-月，d-日，w-星期，r-带AM,PM的时分秒，p-仅am/pm, h-i-s：时分秒，等。
      • 注意：相同字母的不同大小写，对应不同的输出格式。

  • 注意：format-数字时会进行四舍五入。

  • 示例







去除首尾空格函数trim

  • 关键字：

    • ltrim(str), rtrim(str), trim(str)

  • 解析：

    • 返回去掉开始处、结束处、首尾空格的字符串

  • 示例

=======================================================

聚合函数

关键字 avg sum count min max

语法与解析

  • 注意：

    • 1、格式：select + 函数名(参数名)

    • 2、配合group by进行分组，注意字段有重复元素和无重复元素得到的结果的不同。

示例



=======================================================

控制流函数

条件IF

  • 语法：IF(test, t, f)

  • 解析：如果test为真，返回t；否则，返回f。

  • 示例：

    • SELECT IF(1<10, 2, 3); -- 输出：2

判非空IFNULL

  • 语法：IFNULL(arg1, arg2)

  • 解析：如果arg1不是空，则返回arg1；否则，返回arg2。

    • 注：参数可以是select语句。

  • 示例：


相等则返回空NULLIF

  • 语法：NULLIF(arg1, arg2)

  • 解析：如果arg1=arg2，则返回null；否则，，返回arg1。

  • 示例：

组合判断1-case-when

  • 语法：CASE WHEN[test1] THEN [result1] WHEN..THEN.. ELSE [default] END

    • 注意：此处的when相当于switch-case里的每个case，可以组合为when...when....when...else；注意else别放在when中间。

  • 解析：如果testN为真，则返回resultN；否则，返回default。

    • 注：如果有多个符合，则返回第一个匹配的testN对应的结果。

  • 示例：

组合判断2-case-xx-when

  • 语法：CASE[test] WHEN[val1] THEN [result1] WHEN..THEN.. ELSE [default] END

  • 解析：如果test和valN相等，则返回resultN，否则返回default。

    • 注意：如果有多个符合，则返回第一个匹配的testN对应的结果。

  • 示例：


=======================================================

系统信息函数

查询系统函数version-database-user等

  • 语法与解析

    • 返回数据库版本号：version()

    • 返回当前数据库名：database()

    • 返回当前用户名：user(), system_user(), session_user(), current_user()

  • 示例：





END

posted @ 2020-04-23 14:32  anliux  阅读(466)  评论(0)  编辑  收藏  举报