暂无 |

2 函式

在你在执行查询或维护资料的时候,可能会有下列这个比较特殊的需求:

mysql_04_snap_13

以这样的需求来说,你当然不用自己去计算两个日期之间的天数,MySQL提供许多不同的函式(functions),可以完成这类的需求,不论在执行查询或维护的叙述中,都可以使用这些函式。函式基本的用法会像这样:

mysql_04_snap_14

注:MySQL规定函式预设的写法是函式名称和左括号之间不可以有任何空格,否则会造成错误;你可以执行「SET sql_mode='IGNORE_SPACE'」,这个设定让你可以在函式名称和左括号之间加入空格也不会出错。

以上列「计算两个日期之间的天数」来说,就会在查询叙述中使用到这样的函式:

mysql_04_snap_15

MySQL提供的函式非常多,你不用把每一个函式的名称和用法都背起来,就算是为了参加认证考试也一样。这个章节只有介绍「部份」函式,并不是全部,所以你在了解这章讨论的函式以后,需要到MySQL参考手册中的「Chapter 12. Functions and Operators」,进一步认识MySQL还有提供哪一些函式。

2.1 字串函式

字串资料的处理是一种很常见的工作,处理字串的函式也非常多,所以这里使用分类的方式来介绍。下列是处理字串内容的相关函式:

  • LOWER(字串):将[字串]转换为小写
  • UPPER(字串):将[字串]转换为大写
  • LPAD(字串1, 长度, 字串2):如果[字串1]的长度小于指定的[长度],就在[字串1]左边使用[字串2]补满
  • RPAD(字串1, 长度, 字串2):如果[字串1]的长度小于指定的[长度],就在[字串1]右边使用[字串2]补满
  • LTRIM(字串):移除[字串]左边的空白
  • RTRIM(字串):移除[字串]右边的空白
  • TRIM(字串):移除[字串]左、右的空白
  • REPEAT(字串, 个数):重复[字串]指定的[个数]
  • REPLACE(字串1, 字串2, 字串3):将[字串1]中的[字串2]替换为[字串3]

「LPAD」与「RPAD」在处理报表资料的时候,很常用来控制报表内容的格式。例如下列的需求:

mysql_04_snap_16

使用「LPAD」函式让查询后得到的字串内容向右对齐:

mysql_04_snap_17

下列是截取字串内容的函式:

  • LEFT(字串, 长度):传回[字串]左边指定[长度]的内容
  • RIGHT(字串, 长度):传回[字串]右边指定[长度]的内容
  • SUBSTRING(字串, 位置):传回[字串]中从指定的[位置]开始到结尾的内容
  • SUBSTRING(字串, 位置, 长度):传回[字串]中从指定的[位置]开始,到指定[长度]的内容

下列是一个测试这些函式的查询叙述:

mysql_04_snap_18

下列是连接字串的函式:

  • CONCAT(参数[,…]):传回所有参数连接起来的字串
  • CONCAT_WS(分隔字串, 参数[,…]):传回所有参数连接起来的字串,参数之间插入指定的[分隔字串]

你可以使用「||」运算子连接字串,「CONCAT」函式也可以完成同样的需求。唯一的差异是要先设定「sql_mode」为「PIPES_AS_CONCAT」后,才可以使用「||」运算子连接字串;而「CONCAT」函式不用执行任何设定就可以连接字串。

「CONCAT_WS」函式提供一种比较方便的字串连接功能,例如下列这个使用「||」运算子连接字串的查询叙述:

mysql_04_snap_19

改成使用「CONCAT_WS」函式的话,就会比较简单一些:

mysql_04_snap_20

注:「CONCAT」与「CONCAT_WS」两个函式的参数可以接受任何型态的资料,它们都会把全部的资料转为字串后连接起来;「CONCAT」函式的参数中如果有「NULL」值,结果会是「NULL」;「CONCAT_WS」函式的参数中如果有「NULL」值,「NULL」值会被忽略。

下列是取得字串资讯的函式:

  • LENGTH(字串):传回[字串]的长度(bytes)
  • CHAR_LENGTH(字串):传回[字串]的长度(字元个数)
  • LOCATE(字串1, 字串2):传回[字串1]在[字串2]中的位置,如果[字串2]中没有[字串1]指定的内容就传回0

使用「LENGTH」函式可以完成类似「国家名称长度排行榜」的查询:

mysql_04_snap_21

注:「LENGTH」与「CHAR_LENGTH」的差异在「第六章、字元集与资料库」与「第七章、储存引擎与资料型态」中会详细的讨论。

如果有需要的话,你也会搭配许多函式来完成你的工作,例如:

mysql_04_snap_22

上列的叙述可以查询「名称是一个单字以上的国家」。

2.2 数学函式

下列是数值舍去与进位的函式:

  • ROUND(数字):四舍五入到整数
  • ROUND(数字, 位数):四舍五入到指定的位数
  • CEIL(数字)、CEILING(数字):进位到整数
  • FLOOR(数字):舍去所有小数
  • TRUNCATE(数字, 位数):将指定的[数字]舍去指定的[位数]

下列是一个测试这些函式的查询叙述:

mysql_04_snap_23

在这些函式中,「TRUNCATE」函式的用法会比较不一样:

mysql_04_snap_24

下列是算数运算的函式:

  • PI():圆周率
  • POW(数字1, 数字2)、POWER(数字, 数字2):[数字1]的[数字2]次方
  • RAND():乱数
  • SQRT(数字):[数字]的平方根

每次使用「RAND」函式的时候,它都会传回一个大于等于0而且小于等于1的小数数字,通常会把它称为「乱数」,这个数值是由MySQL随机产生的。如果你的叙述中需要一个固定范围内的乱数,可以搭配「RAND」函式套用下列的公式来产生:

mysql_04_snap_25

使用「RAND」函式也可以完成「随机查询」的需求:

mysql_04_snap_26

注:MySQL还有提供的许多不同应用的数学函式,例如三角函式,你可以查询MySQL参考手册中的「12.4.2. Mathematical Functions」。

2.3 日期时间函式

下列是取得日期与时间的函式:

  • CURDATE():取得目前日期,相同功能:CURRENT_DATE、CURRENT_DATE()
  • CURTIME():取得目前时间,相同功能:CURRENT_TIME、CURRENT_TIME()
  • YEAR(日期):传回[日期]的年
  • MONTH(日期) 数字传回[日期]的月
  • DAY(日期):传回[日期]的日,相同功能:DAYOFMONTH()
  • MONTHNAME(日期):传回[日期]的月份名称
  • DAYNAME(日期):传回[日期]的星期名称
  • DAYOFWEEK(日期):传回[日期]的星期,1到7的数字,表示星期日、一、二…
  • DAYOFYEAR(日期):传回[日期]的日数,1到366的数字,表示一年中的第几天
  • QUARTER(日期):传回[日期]的季,1到4的数字,代表春、夏、秋、冬
  • EXTRACT(单位FROM 日期/时间):传回[日期]中指定的[单位]资料
  • HOUR(时间):传回[时间]的时
  • MINUTE(时间):传回[时间]的分
  • SECOND(时间):传回[时间]的秒

「CURDATE」与「CURTIME」可以取得目前伺服器的日期与时间,搭配其它函式就可以完成下列的「建国最久的国家排行」查询:

mysql_04_snap_27

「EXTRACT」函式用来取得日期时间资料的指定「单位」,例如日期中的月份,使用的「单位」与这一章之前在「日期与时间值」中讨论的一样,这个函式让你不用记太多「YEAR」或「MONTH」这类函式的名称:

mysql_04_snap_28

下列是计算日期与时间的函式:

  • ADDDATE(日期, 天数):传回[日期]在指定[天数] 以后的日期
  • ADDDATE(日期, INTERVAL 数字单位):传回[日期]在指定[数字]的[单位]以后的日期
  • ADDTIME(日期时间, INTERVAL数字单位):传回[日期时间]在指定[数字]的[单位]以后的日期时间
  • SUBDATE(日期, 天数):传回[日期]在指定[天数] 以前的日期
  • SUBDATE(日期, INTERVAL 数字单位):传回[日期]在指定[数字]的[单位]以前的日期
  • SUBTIME(日期时间, INTERVAL数字单位):传回[日期时间]在指定[数字]的[单位]以前的日期时间
  • DATEDIFF(日期1, 日期2):计算两个日期差异的天数

在计算日期方面的函式,MySQL也提供两种不同的用法:

mysql_04_snap_29

上列函式中使用的「单位」与这一章之前在「日期与时间值」中讨论的一样。

2.4 流程控制函式

在处理一般工作的时候,使用各种SQL叙述与函式,通常就可以完成你的需求;可是在实际的应用上,难免会遇到类似下列这样比较复杂一点的需求:

mysql_04_snap_30

像这种依照条件判断结果而显示不同资料的需求,可以使用下列这个「IF」函式来处理:

mysql_04_snap_31

使用「IF」函式可以在查询的时候,依照员工进公司的日期判断是资深或是一般员工:

mysql_04_snap_32

如果要依照资深员工与一般员工计算不同的奖金,也可以使用「IF」函式来完成:

mysql_04_snap_33

「IF」函式可以用来判断一个条件「成立」或「不成立」两种状况的需求;但是像下列的需求就不适合使用「IF」函式了:

mysql_04_snap_34

如果要完成多种条件的判断,就要使用下列的「CASE」语法,它应该不能算是一个函式,因为它的长像实在不像是一个函式:

mysql_04_snap_35

套用上列的语法,就可以判断出所有员工的新资等级:

mysql_04_snap_36

在「CASE」的语法中,要判断一种条件就使用一个「WHEN」来完成;如果有「所有条件以外」的情况要处理的话,就可以使用「ELSE」来处理:

mysql_04_snap_37

如果要依照员工新资等级计算不同的奖金,也可以使用「CASE」语法来完成这个需求:

mysql_04_snap_38

「CASE」除了上列介绍的语法外,还有另外一种写法可以处理一些比较特别的需求,例如下列七大洲的名称与缩写对照表:

  • Asia:AS
  • Europe:EU
  • Africa:AF
  • Oceania:OA
  • Antarctica:AN
  • North America:NA
  • South America:SA

如果要在SQL叙述中有类似这样的需求,就可以使用下列这种「CASE」的语法:

mysql_04_snap_39

套用上列的语法就可以完成这样的查询:

mysql_04_snap_40

以上列的查询来说,你也可以换成这样的写法:

SELECT Name, Continent,
CASE
WHEN Continent='Asia' THEN 'AS'
WHEN Continent='Europe' THEN 'EU'
WHEN Continent='Africa' THEN 'AF'
WHEN Continent='Oceania' THEN 'OA'
WHEN Continent='Antarctica' THEN 'AN'
WHEN Continent='North America' THEN 'NA'
WHEN Continent='South America' THEN 'SA'
END ContinentCode
FROM country

经由这样的对照,应该可以很容易看得出来,使用哪一种写法来完成这个查询会好一些。

2.5 其它函式

  • IFNULL(参数, 运算式):如果[参数]为NULL就传回[运算式]的值;否则传回[参数]的值
  • ISNULL(参数):如果[参数]为NULL就传回TRUE;否则传回FALSE

当资料库中有「NULL」资料出现的时候,就可能会发生下列这样奇怪的结果:

mysql_04_snap_41

所以要得到正确的结果,就要使用「IFNULL」函式来特别处理NULL值的运算:

mysql_04_snap_42

「ISNULL」函式用来判断一个指定的资料是否为「NULL」,它的效果跟之前在「第三章、基础查询、条件比较」中讨论的「IS NULL」和「IS NOT NULL」运算子是一样的,你可以自己决定要使用哪一种来执行判断。

0

python教程
java教程
php教程
php+mysql教程
ThinkPHP教程
MySQL
css
javascript
Django教程

发表评论

    评价:
    验证码: 点击我更换图片
    最新评论