02_SQL语句(二)

(三)、对查询结果排序  (ORDER BY 子句)

语法格式：

SELECT {DINTINCT} *|具体的列 别名 FROM 表名 ｛WHERE条件(S)｝{ORDER BY 排序的字段1 ASC|DESC,排序的字段2  ASC|DESC}

1.       升序排序 (ASC 一般默认的就是升序,即不写排序类型时默认是升序)

例：查询出emp表的内容 并按薪水 升序排列

SELECT * FROM emp ORDER BY sal ;

SELECT * FROM emp ORDER BY sal ASC;

 

2.       降序排列 (在排序字段后加DESC)

例：查询出emp表的内容 并按薪水降序排列

SELECT * FROM emp ORDER BY sal DESC;

 

3.       多个排序排序条件  此时先按第一个条件排序 第一个条件的内容相等 在按第二个条件排序

例：查询出emp表的内容 先按薪水由高到低排序 如果薪水一样 则按雇佣日期 由早到晚排序

SELECT * FROM emp ORDER BY sal DESC，hiedate ASC;

 

(四)、单行函数

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

单行函数语法：

Function_name(column | expression,[arg1,arg2…..])

参数说明：

 Function_name：函数名称

 Column：数据库列名

 Expression：字符串或计算表达式

 arg1 arg2 ：在函数中使用的参数

 

1、单行函数的分类：

       |--- 字符函数 接受字符输入并返回字符或数值

       |--- 数值函数 接受数值输入并返回数值

       |--- 日期函数 对日期数据进行操作

       |--- 转换函数 从一种数据类型转换为另一种数据类型

       |--- 通用函数 NVL函数 DECODE函数

 

2、字符函数

专门处理字符的函数 字符的大小写变更 求出字符的长度

(1)、将小写变为大写 UPPER(‘’)

SELECT UPPER(‘smith’) FROM DUAL; àSMITH

 

(2)、将大写变小写  LOWER(‘’)

SELECT LOWER(‘SMITH’) FROM DUAL; àsmith

 

(3)、将单词的第一个字符大写 INITCAP(“”) 注：使用词语不论输入的字符是什么样的大小写格式 输出的均是首字符大写 其余小写的形式

SELECT INITCAP(‘SMITH’) FROM DUAL; àSmith

例：将雇员表中的雇员姓名变为首字母大写

SELET INITCAP(ename) FROM emp;

 

(4)、字符串除了可以使用  || 连接外 还可以使用CONCAT( ) 进行连接

SELECT CONCAT(‘Hello’,’World’) FROM DUAL; à使用CONCAT() 进行字符串连接

SELECT ‘HELLO’||’WORLD’ FROM DUAL; à使用 || 进行字符串的连接操作

但是可以看出 CONCAT()并没用 || 好用

 

(5)、字符串的长度LENGTH(‘xxxx’)

SELECT LENGTH(‘Hello’) FROM DUAL;

 

(6)、字符串的替换 REPLACE(‘xxx’,’被替换的内容’,’替换的内容’)

SELECT REPLACE(‘Hello’,’H’,’W’) FROM DUAL;

注：如果被替换的内容不在字符串中 SQL语句可以执行 但不会产生替换效果

SELECT REPLACE(‘Hello’,’a’,’r’);

Hello将不会变化

(7)、截取SUBSTR(‘Hello’,n,m)   表示从第n位开始截取 截取m位

例:SELECT SUBSTR(‘Hello’,1,3) 截取,LENGTH(‘Hello’) ;     

注：关于SubStr()面试相关的问题

ORACLE中SUBSTR()的截取点是从0还是 1开始

       |--从0 或 1 开始都是一样的 因为 oracle比较智能

 

★     例：查询出所有雇员的姓名及姓名的后三个字符

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

注：如果从第n为开始截取 不写截取多少位 那么默认从第n位一致截取到最后

 

可以看出因为每个雇员姓名的长度不一样 所以需要求出姓名的长度在减去2,这样比较麻烦

★     例：查询出所有雇员的姓名及姓名的倒数第二个字符

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

为了方便的进行字符串的截取 oracle的substr()函数提供了更加方便的机制 可以采用倒着截取的方式，只有输入的位数是负数 就表示倒着截取

 

 

 

4、日期函数

Oracle提供了很多和日期操作有关的函数，包括加减日期 但日期进行加减结果会有一些规律

       |-- 日期+/-数字 = 日期

       |-- 日期+/-日期 = 数字(天数)

在Oracle中当前的日期使用 SYSDATE表示 SELECT SYSDATE FRON DUAL;

例：查询出10部门的雇员进入公司的星期数

SELECT ename,ROUND((SYSDATE-hiredate)/7) FROM emp WHERE deptno=10;

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

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

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

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

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

 

(1)、MONTHS_BETWEEN(日期1，日期2)

       例：查询出雇员被雇佣的月数

       SELECT ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp;

      

(2)、ADD_MONTHS(日期，月数) 日期+数字 = 日期

       例：在现在的日期上加上3个月

       SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL; à   2009-12-16 19:37:58

 

(3)、NEXT_DAY(日期，给定日期) 求出下一个给定的日期

       例：求出从今天开始下一个星期一的日期

       SELECT NEXT_DAY(SYSDATE，’星期一’)  FROM DUAL;

       注：星期数不能写成 星期天 否则无效

 

(4)、LAST_DAY（）求出一个日期的最后一天

       例：求出本月的最后一天

       SELECT LAST_DAY(SYSDATE) FROM DUAL;

 

 

 

 

 

 

 

 

5、转换函数

       |-- TO_CHAR() 转换成字符串

       |-- TO_NUMBER() 转换成数字

       |-- TO_DATE() 转换成日期

 

(1)、TO_CHAR()函数   使用在日期和数字上

|||-- TO_CHAR() 函数用在日期上 

||-- TO_CHAR() 可以进行日期的拆分 拆分的时候注意日期的通配符,通配符要加单引号

|-- yyyy 年  TO_CHAR(hiredate,’yyyy’)

|-- MM 月  TO_CHAR(hiredate,’MM’)

|-- dd 日    TO_CHAR(hiredate,’dd’)

                例：查询出emp表雇员的雇佣日期 并将其拆分

        SELECT ename，TO_CHAR(hiredate，’yy’) year，TO_CHAR(hiredate，’MM’)  month，

        TO_CHAR(hiredate，’dd’)  day  FROM emp;

||-- TO_CHAR()  使用进行日期显示的转换功能

        |-- Oracle的默认格式 19-4 月 -87

        |-- chinese的习惯格式 1987-04-19

例：查询出emp表雇员雇佣日期 并以 yyyy-MM-dd格式显示

SELECT ename,TO_CHAR(hiredate,’yyyy-MM-dd’) FROM emp;

注：MM表示如果该月不足10月会显示前导0 如果要去掉签到0 需要加fm

SELECT ename,T0_CHAR(hiredate,’fmyyyy-MM-dd’) FROM emp;

 

|||-- TO_CHAR() 用在数字上

||-- TO_CHAR() 可以在数字中加入一些符号，以分割太长的数字 以对数字进行格式化

    |-- 9： 表示以为数字

    |-- $：表示美元

    |--       L：表示本地的货币  

        例：查询出雇员的薪水 三位一组 以，隔开

        SELECT ename，TO_CHAR(sal，’$99，999’)  sal  FROM  emp;

 

(2)、TO_NUMBER()   将字符串变为数字

        例：SELECT  TO_NUMBER(‘123’) + TO_NUMBER(‘234’)   FROM  DUAL; à357

        上面是将字符串变为数值类型并相加

 

(3)、TO_DATE()   将一个字符串变为 DATE类型

        例：将 2009.9.16转换为 date类型

SELECT  TO_DATE(‘2009.9.16’，’yyyy-MM-dd’)  FROM  DUAL; à 2009-09-16

 

 

 

6、通用函数

       |-- NVL 函数()   可以指定一个null值为指定内容 如果要进行计算 对于 NULL值 必须使用NVL函数

       |-- DECODE 函数()  类似于if()..elseif() 语句

 

       (1)、NVL()函数

              例：要求求出部门雇员的年薪

              SELECT ename，（sal+comm）*12 income FROM  emp  WHERE deptno=30;

             

ENAME          INCOME     ENAME          INCOME ----------           ----------         ----------               ---------- ALLEN           22800          ALLEN               22800                    WARD            21000          WARD                21000 MARTIN        31800         MARTIN            31800 BLAKE           34200         BLAKE TURNER        18000         TURNER            18000 JAMES           11400         JAMES

            

可以看出 一些员工的年薪没有显示出来 那是因为一些雇员的奖金为空的原因 此时要进行计算就要使用NVL函数了

              SELECT  ename （sal+NVL（comm，0））*12  income FROM emp WHERE   deptno=30;       

 

       (2)、DECODE（）函数 

DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。

例：查询出雇员的薪水 如果薪水在2000以下的 加薪20%, 如果薪水在2000元以上的 加薪15%，如果为2000的则 加薪15%

SELECT ename,DECODE(sign(sal-2000)，1，sal*1.5，-1，sal*2，0，sal*1.5)  newsal  FROM emp;

注：SIGN(数值) 函数根据里面的值是 0 ,正数 ,负数 分别返回 0 ，1，-1

例：将emp表中的雇员的职位都换成中文

SELECT ename，DECODE(job,’CLERK’,’业务员’,’SALSMAN’,’销售人员’,’MANAGER’,’经理’,’ANALYST’,’分析员’,’PRESIDENT’,’总裁’) FROM emp  WHERE deptno=30;

ENAME      DECODE(J ---------- -------- ALLEN      销售人员 WARD       销售人员 MARTIN     销售人员 BLAKE      经理 TURNER     销售人员 JAMES      业务员