MYSQL基础之函数

单行函数

数据库系统中，每个数据库之间唯一不同的最大区别点就在于对函数的支持上，使用函数就可以完成一系列的操作功能。

      单行函数语法：

               function_name(column| expression, [arg1, arg2, …])

               参数说明：

               · function_name：函数名称

               · column：数据库列名

               · expression：字符串或计算表达式

               · arg1， arg2：在函数中使用参数

      单行函数分类：

• 字符函数：接受字符输入并且返回字符或数值
• 数值函数：接受数值输入并返回数值
• 日期函数：对日期型数据进行操作
• 转换函数：从一种数据类型转换为另一种数据类型
• 通用函数：NVL函数、DECODE函数

      Ø  字符函数

                是专门处理字符的，例如，可以将大写字符变为小写字符，还能求出字符的长度。

       例：将小写字母变为大写字母

             l  SELECT UPPER(‘smith’) FROM emp ;

             输出如下：

            l  SELECT UPPER(‘smith’) FROM DUAL ;

            输出如下：

      例：一般用户在查询一个姓名的时候有可能考虑到这个人的姓名是大写字母存储的还是小写字母存储的呢：

            l  那么此时，为了方便用户的使用就可以使用upper()函数来完成。

            l  SELECT * FROM emp WHERE ename = UPPER(‘Smith’) ;

            输出如下：

            这样不管输入的名字的大小写通过UPPER()函数全部转换成大写进行处理。

            还可以使用lower()函数讲一个字符串变为小写字母表示。

            l  SELECT LOWER(‘HELLO WORLD’) FROM dual ；

            输出如下：

            还可以使用initcap()函数将单词的第一个字母大写。

            l  SELECT INITCAP(‘HELLO WORLD’) FROM dual；

            输出如下：

      例：使用initcap()函数将雇员表中的雇员姓名变为开头字母大写。

           l  SELECT INITCAP(ename) FROM emp ；

           输出如下：

           字符串除了可以使用“||”连接之外，还可以使用CONCAT()函数进行连接操作。

           l  SELECT CONCAT(‘hello’, ‘world’) FROM dual ;

           输出如下：

           此时是以一种完整的字符串输出的，但是此种方式肯定不如“||”好使。

      例：在字符串中可以进行字符串的截取、求出字符串的长度、进行指定内容的替换。

           l  字符串截取：substr()

           l  字符串长度：length()

           l  内容替换：replace()

           l  SELECT substr(‘hello’, 1, 3) 截取字符串,

                               length(‘hello’) 字符串长度,

                               replace(‘hello’, ‘l’, ‘x’) 字符串替换

                               FROM DUAL ;

           输出如下：

• 清屏操作：clear scr ;
• 注意：在Qracle中，substr()函数的截取点是从0还是从1开始，

                   答：从0或1开始效果是一样的，因为Oracle比较智能。

      例：要求显示所有雇员的姓名及姓名的后三个字符。

           l  因为雇员姓名的字符串长度不一样，所以只能求出整个的长度再减去2。

           l  SELECT ename, SUBSTR(ename, LENGTH(ename)-2) FROM emp ;

           输出如下：

           此时，功能已经实现了，但是操作起来比较麻烦。实际上在substr()函数中提供了一种非常方便的机制，可以采用倒截取的方式，只要输入的位置是负数就表示倒着进行。

           l  SELECT ename, SUBSTR(ename, -3, 3) FROM emp ;

           输出如下：

      Ø  数值函数

                  数值函数主要是包含以下几种：

                          四舍五入：ROUND()

                          截断小数位：TRUNC()

                          取余（取模M）：OD()

      例：执行四舍五入操作

           l  SELECT ROUND(789.536) FROM dual ;

           输出如下：

           当然，在ROUND()函数中也可以指定四舍五入的位数。

      例：保留两位小数

           l  SELECT ROUND(789.536， 2) FROM dual ;

           输出如下：

           l  SELECT ROUND(789.536， -2) FROM dual ;

           输出如下：

           TRUNC()与ROUND()不同的是，在TRUNC()操作中，不会保留任何的小数，而且小数点也不会执行四舍五入的操作。

      例：验证TRUNC()函数

           l  SELECT TRUNC(789.536) FROM dual ;

           输出如下：

      例：通过TRUNC()也可以指定小数点的保留位数。

           l  SELECT TRUNC(789.536， 2) FROM dual ;

           输出如下：

           l  SELECT TRUNC(789.536， -2) FROM dual ;

           输出如下：

      例：使用MOD()函数可以进行取余操作

           l  SELECT MOD(10, 3) FROM dual ;

           输出如下：

      Ø  日期函数

                在Oracle中提供了很多与日期操作相关的函数，包括加减日期等等。但是在日期进行加或减的时候有一些规律：

                        日期 – 数字 = 日期

                        日期 + 数字 = 日期

                        日期 – 日期 = 数字（天数）

       例：显示10部门雇员进入公司的星期数。

·           如果要想完成此操作，则首先必须知道当前的日期，在Oracle中可以通过以下的操作求出当前日期，使用sysdate表示。

            l  SELECT SYSDATE FROM dual ;

            输出如下：

·          求出星期数：当前日期 –雇佣日期 =天数 / 7 = 星期数

            l  SELECT empno, ename, (SYSDATE – hiredate) / 7 FROM emp ;

           输出如下：

           此时，星期处显示的都是小数，这是不符合实际情况的。那么，我们对结果进行四舍五入操作

           l  SELECT empno, ename ROUND((SYSDATE – hiredate) / 7) FROM emp ;

           输出如下：

           在Oracle中提供了一下的日期函数支持：

                    MONTHS_BETWEEN()：求出给定日期范围的月数

                    ADD_MONTHS()：在指定日期上加上指定的月数，求出之后的日期

                    NEXT_DAY() ：下一个的今天是那一个日期

                    LAST_DAY()：求出给定日期的最后一天日期

      例：验证MONTHS_BETWEEN()

           l  SELECT empno, ename, MONTHS_BETWEEN(sysdate, hiredate) FROM emp ;

           输出如下：

           程序查询时包含了小数点，可以使用ROUND()进行四舍五入操作

      例：验证ADD_MONTHS()函数

           l  SELECT ADD_MONTHS(SYSDATE, 4) FROM dual ;

           输出如下：

      例：验证NEXT_DAY()函数

·          此函数求出下一次给定的日期数

           l  SELECT NEXT_DAY(SYSDATE, ‘星期一’) FROM dual ;

           输出如下：

      例：验证LAST_DAY()函数

·          求出一个日期的最后一天

            l  SELECT LAST_DAY(SYSDATE) FROM dual ;

            输出如下：

      Ø  转换函数

                  为了完成不同数据类型之间的显式转换，Oracle提供了三种常用的转换函数：

                         TO_CHAR()：转换成字符串

                         TO_NUMBER()：转换成数字

                         TO_DATE()：转换成日期

      例：查询所有雇员的雇员编号、姓名、雇佣日期

           l  SELECT empno, ename hiredate FROM emp ;

           输出如下：

           但是，现在要求可以将年、月、日进行分开，此时就可以使用TO_CHAR()函数进行拆分，拆分的时候必须指定拆分的通配符：

                  年：使用y表示； 年是四位的数字，所以使用yyyy表示

                  月：使用m表示； 月是两位的数字，所以使用mm表示

                  日：使用d表示 ； 日是二位的数字，所以使用dd表示

          l  SELECT empno, ename, TO_CHAR(hiredate, 'yyyy') year,

                              TO_CHAR(hiredate, 'mm') months,

                              TO_CHAR(hiredate, 'dd') day

                              FROM emp ;

           输出如下：

           还可以使用TO_CHAR()函数进行日期转换功能。

                    Oracle中默认地日期格式：19-4 月 -87

                    中国人的喜欢格式：1987- 4-19

           l  SELECT empno, ename, TO_CHAR(hiredate, ‘yyyy-mm-dd’) FROM emp ;

           输出如下：

           从运行结果中可以发现，如果是5月，则会使用05表示。那么这个0称为前导0，如果不希望显示前导0的话，则可以使用fm去掉这些0.

           l  SELECT empno, ename, TO_CHAR(hiredate, ‘fmyyyy-mm-dd’) FROM emp ;

           输出如下：

           TO_CHAR()函数除了可以用在日期上，也可以用在数字上。

      例：要查询全部的雇员编号、姓名、工资

           l  SELECT empno, ename, sal FROM emp ;

           输出如下：

           最好在数字中加入一些符号，以分割太长的数字，一般中国使用“，”，所以，此时，可以使用TO_CHAR()函数进行格式化：

·          9：表示一位数字

           l  SELECT empno, ename, TO_CHAR(sal, ’99,999’) FROM emp ;

           输出如下：

           如果，此时希望数字可以明确的表示出区域，可以使用一下两种符号：

·          $：表示美元

·          L：表示Local的缩写，以本地的语言进行金额的显示

           l  SELECT empno, ename TO_CHAR(sal, ‘$99,999’) FROM emp ;

           输出如下：

           如果想以本地的格式显示：

           l  SELECT empno, ename, TO_CHAR(sal, ‘L99,999’) FROM emp ;

           输出如下：

           TO_NUMBER是可以将字符串便为数字的一种函数

      例：将字符串变为数字，之后进行数字的加法操作。

           l  SELECT TO_NUMBER(‘123’) + TO_NUMBER(‘123’) FROM dual ;

           输出如下：

           TO_DATE()函数可以将一个字符串变为DATE类型的数据。

     例：2009-2-16是一个字符串，现在要变为DATE类型。

           l  SELECT TO_DATE(‘2009-02-16’, ‘yyyy-mm-dd’) FROM dual ;

           输出如下：

      Ø  通用函数

      例：求出每个雇员的年薪，格式：(sal + comm) * 12

           l  SELECT empno, ename, (sal + comm.) * 12 FROM emp ;

           输出如下：

           我们要输出所有雇员的一年的收入（有奖金的要加上奖金，没奖金的就不加了），但是，这里只输出了有奖金的那部分雇员，而没有奖金的雇员就没有输出，出现这种情况，是因为有些雇员的奖金是NULL,NULL值计算之后结果还是NULL, 所以面对这样的情况，就可以使用NVL函数，可以将一个指定的NULL值变为指定的内容。

           l  SELECT empno, ename, NVL(comm., 0), (sal + NVL(comm., 0)) * 12income FROM emp ;

           输出如下：