4.4、对结果进行排序（ORDER BY子句）（重点）

       在SQL中可以使用ORDER BY子句对查询的结果进行排序，

例如，现在使用查询全部的语句：

SELECT * FROM emp;

       此时，从查询结果可以发现，是按照雇员的编号进行排序的，那么此时如果要对使用指定的列进行排序，则就必须使用ORDER BY语句，语法格式如下：

SELECT {DISTINCT}*｜具体的列 别名 FROM 表名称 {WHERE 条件(s)} {ORDER BY 排序的字段1，排序的字段2 ASC|DESC}

范例：要求按照工资由低到高排序

SELECT * FROM emp ORDER BY sal;

       之前是按照由低到高的顺序完成，是采用升序的形式，现在要求使用降序的形式完成。实际上如果在排序的时候没有指定排序规则，则默认的排序规则是升序排列。

SELECT * FROM emp ORDER BY sal ASC;

       要想使用降序的方式完成，则使用DESC即可。

SELECT * FROM emp ORDER BY sal DESC;

范例：要求查询出10部门的所有雇员信息，查询的信息按照工资由高到低排序，如果工资相等，则按照雇用日期有早到晚排序。

       ·此时存在两个排序条件，第一个是降序，第二个升序

SELECT * FROM emp WHERE deptno=10 ORDER BY sal DESC,hiredate ASC;

       排序的操作肯定是放在整个SQL语句的最后执行。

4.5、单行函数（重点）

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

单行函数语法：

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

参数说明：

       ·function_name：函数名称

       ·column：数据库列名

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

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

单行函数分类：

       ·字符函数：接受字符输入并且返回字符或数值

       ·数值函数：接受数值输入并返回数值

       ·日期函数：对日期型数据进行操作

       ·转换函数：从一种数据类型转换为另一种数据类型

       ·通用函数：NVL函数、DECODE函数

 

4.5.1、字符函数

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

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

SELECT UPPER('smith') FROM dual;

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

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

SELECT * FROM emp WHERE ename=UPPER('Smith');

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

SELECT LOWER('HELLO WORLD') FROM dual;

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

SELECT INITCAP('HELLO WORLD') FROM dual;

范例：使用此函数将雇员表中的雇员姓名变为开头字母大写

SELECT INITCAP(ename) FROM emp;

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

SELECT CONCAT('hello','world') FROM dual;

       此时已经完成了连接，但是此种方式肯定不如“||“好使。

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

       ·字符串截取：substr()

       ·字符串长度：length()

       ·内容替换：replace()

SELECT substr('hello',1,3) 截取字符串,        length('hello') 字符串长度,        replace('hello','l','x') 字符串替换 FROM dual;

       但是在substr()函数的时候有一点需要提醒大家注意，跟面试有关：

              ·Oracle中substr()函数的截取点是从0还是从1开始。

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

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

       ·因为雇员姓名的字符串长度不一样，所以只能求出整个的长度再减去2，这样进行截取操作。

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

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

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

4.5.2、数值函数

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

              ·四舍五入：ROUND（）

              ·截断小数位：TRUNC（）

              ·取余（取模）：MOD（）

范例：执行四舍五入操作

SELECT ROUND(789.536) FROM dual;

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

范例：保留两位小数

SELECT ROUND(789.536,2) FROM dual;

       在使用ROUND()函数中还有一点非常有意思，可以直接对整数进行四舍五入的进位。

SELECT ROUND(789.536,-2) FROM dual;

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

范例：验证TRUNC()函数

SELECT TRUNC(789.536) FROM dual;

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

SELECT TRUNC(789.536,2) FROM dual;

范例：使用负数表示位数

SELECT TRUNC(789.536,-2) FROM dual;

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

SELECT MOD(10,3) FROM dual;

4.5.3、日期函数

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

       ·日期 – 数字 = 日期

       ·日期 + 数字 = 日期

       ·日期 – 日期 = 数字(天数)

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

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

SELECT SYSDATE FROM dual;

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

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

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

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

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

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

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

范例：验证MONTHS_BETWEEN()函数

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

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

范例：验证ADD_MONTHS()函数

SELECT ADD_MONTHS(SYSDATE,4) FROM dual;

范例：验证NEXT_DAY()函数

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

SELECT NEXT_DAY(SYSDATE,'星期一') FROM dual;

范例：验证LAST_DAY()函数

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

SELECT LAST_DAY(SYSDATE) FROM dual;

4.5.4、转换函数（重点）

       转换函数主要有以下几种：

              ·TO_CHAR()：转换成字符串

              ·TO_NUMBER()：转换成数字

              ·TO_DATE()：转换成日期

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

SELECT empno,ename,hiredate FROM emp;

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

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

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

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

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-04-19

SELECT empno,ename,TO_CHAR(hiredate,'yyyy-mm-dd') FROM emp;

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

SELECT empno,ename,TO_CHAR(hiredate,'fmyyyy-mm-dd') FROM emp;

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

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

SELECT empno,ename,sal FROM emp;

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

       ·9：表示一位数字

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

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

              ·$：表示美元

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

SELECT empno,ename,TO_CHAR(sal,'$99,999') FROM emp;

SELECT empno,ename,TO_CHAR(sal,'L99,999') FROM emp;

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

SELECT TO_NUMBER('123')+TO_NUMBER('123') FROM dual;

       以上就表示把字符串变为数字，之后进行数字的加法操作。

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

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

SELECT TO_DATE('2009-02-16','yyyy-mm-dd') FROM dual;

4.5.5、通用函数

范例：要求求出每个雇员的年薪

       ·求出年薪的时候应该加上奖金的，格式(sal+comm)*12

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

       之所以造成这样的结果，是因为有些雇员的奖金是NULL，NULL值计算之后结果还是NULL。所以，面对这样的情况，就可以使用NVL()函数，可以将一个指定的null值变为指定的内容。

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

       如果需要进行计算的时候，对于null必须使用NVL()函数进行一个转换操作。

       DECODE()函数(重要)，此函数是在面试时最有可能问到的问题。DECODE()类似于IF…ELSEIF…ELSE语句。

语法：

DECODE(col/expression，search1，result1[，search2，result2][，default])

说明：

col/expression：为列明或表达式 search1、search2…searchi：为用于比较的条件 result1、result2…resulti为返回值 如果col/expression和searchi相比较，结果相同的话，则返回resulti，如果没有与col/expression相匹配的结果，则返回默认值default。

范例：验证DECODE()函数

SELECT DECODE(1,1,'内容是1',2,'内容是2',3,'内容是3') FROM dual;

       从运行结果上看，程序将内容进行更改，显示的结果变了。

可以直接在emp表上使用此函数，例如，现在有如下的要求：

       ·雇员的工作：

              |- ANALYST：分析员

              |-CLERK ：业务员

|- MANAGER：经理

              |- PRESIDENT：总裁

|- SALESMAN：销售人员

范例：要求查询出雇员的编号、姓名、雇用日期及工作，将工作替换成以上的信息

SELECT  empno 雇员编号,ename 雇员姓名,hiredate 雇用日期,        DECODE(job,'ANALYST','分析员','CLERK',               '业务员','MANAGER','经理','PRESIDENT',               '总裁','SALESMAN','销售人员') 职位 FROM emp;

       从运行结果上发现，使用DECODE()函数已经让输出的职位信息有所更改。