oracle基础总结（三）

接着我们昨天总结的SQL查询介绍往下写了，昨天SQL查询介绍写到了条件查询，那

么我开始介绍排序查询。

     4.排序查询：

          在select语句中，可以使用ORDER BY子句实现对象查询的结果集进行排序，使用ORDER BY子句的语法形式如下：

        SELECT     *|{[DISTINCT] column|expression [alias],...}

FROM       table

[WHERE     condition(s)]

[ORDER BY  {column, expr, alias} [ASC|DESC]];

使用 ORDER BY 子句排序

ASC（ascend）: 升序

DESC（descend）: 降序

ORDER BY 子句在SELECT语句的结尾。

//查询员工信息按照部门的编号进行升序排列   默认的是 asc

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by deptno;

//查询员工信息按照部门的编号进行降序排列   使用desc

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by deptno desc;

//查询部门为20的员工信息并按照员工的编号进行升序排列

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp  where deptno=20 order by empno asc;

备注：可以与条件语句结合使用但order by子句放在最后

//查询员工信息并按照员工的部门编号升序并且编号进行降序排列

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp  order by deptno asc,empno desc;

备注：理解思路:首先查询员工信息按照部门的编号进行升序,然后每个部门中的员工按照员工的编号进行降序排列

     在SELECT语句中使用ORDER BY子句时，受影响的只是查询结果集中的数据，它并没有影响到数据的实际储存位置。

通过学习可以完成 使用where子句、between、like、in、null、not 、or、and等来过滤数据，也可以使用order by子句进行对查询结果排序数据。

5：分组查询：

        分组查询其实就是分组函数也可以叫做统计函数.分组函数作用于一组数据，并对一组数据返回一个值.

常见的分组函数有：

函数名称	函数描述
Count	返回找到的记录数
Min	返回一个数字列或计算列的最小值
Max	返回一个数字列或计算列的最大值
Sum	返回一个数字列或计算列总和
avg	返回一个数字列或计算列的平均值

分组函数的语法：

SELECT              [column,] group_function(column), ...

FROM                             table

[WHERE         condition]

[GROUP BY   column]

[ORDER BY    column];

//返回总记录数   //* 代表的是：一条记录

SQL> select count(*) from emp;

//返回comm不为空的总记录数

SQL> select count(comm) from emp;

//COUNT(DISTINCT expr) 返回 expr非空且不重复的记录总数

SQL> select count(distinct(sal)) from emp;

注意：组函数忽略空值。

//返回所有员工的平均工资  

SQL> select avg(nvl(sal,0)) from emp;

注意：NVL函数使分组函数无法忽略空值

//返回员工编号最小值  

SQL> select min(empno) from emp;

//返回员工工资最大值

SQL> select max(sal) from emp;

//求该月本公司发出的工资总额

SQL> select sum(comm)+sum(sal) from emp;

SQL> select sum(nvl(sal,0)+nvl(comm,0)) from emp;

 

Group by子句

Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。 如果在查询的过程中需要按某一列的值进行分组,以统计该组内数据的信息时,就要使用group by子句。不管select是否使用了where子句都可以使用group by子句。

 

注意：group by子句一定要与分组函数结合使用,否则没有意义。

//求出每个部门的员工人数

SQL> select deptno,count(*) as "人数" from emp group by deptno;

//求出每个部门的员工的平均工资

SQL> select deptno,avg(nvl(sal,0)) from emp group by deptno;

//注意：group by 子句中的列不必包含在SELECT 列表中

SQL> select avg(nvl(sal,0)) from emp group by deptno;

 

//求出某个部门中相同职位的员工人数   group by 后可以跟多个分组的字段

SQL> select deptno,job,count(*) from emp group by deptno,job order by deptno;

 

非法使用组函数

所用包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中。

举例：

SQL> select empno,count(job) from emp;

正确写法如下：

SQL> select empno,count(job) from emp group by empno;

1、              不能在 WHERE 子句中使用组函数（注意）。

SQL> select deptno from emp where count(job)>0 group by deptno;

备注：ORA-00934: 此处不允许使用分组函数

 

 

Having 子句

    HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。

备注：having子句通常与group by子句结合使用

 

语法：

SELECT         column, group_function

FROM                           table

[WHERE       condition]

[GROUP BY group_by_expression]

[HAVING      group_condition]

[ORDER BY column];

//查询部门的员工人数大于五部门编号

SQL> select deptno,count(*) from emp group by deptno having count(*)>5;

 

备注：分组函数可以嵌套

 

总结：整个查询语句的语法格式如下：

SELECT         column, group_function(column)

FROM                           table

[WHERE       condition]

[GROUP BY group_by_expression]

 

      

 

 

SQL*Plus中使用函数

   下面我们开始介绍在SQL*Plus中使用函数，无论什么样的计算机语言，都提供了大大量的函数，使用这些函数可以大大提高计算机语言的运算、判断功能。在PL/SQL语言中，通过使用函数，可以大大增强SELECT语言操作数据库数据的功能。在oracle系统中，提供了字符串函数、数字函数、时间函数和转换函数。

一、字符串函数：

      字符串函数是Oracle系统中广泛使用的函数。在使用字符串函数时，其输入值一定是字符数据，其输出结果可以是字符类型的数据，也可以是数字类型的数据。下面试一些常用的字符串处理函数：

         LOWER:将字符串string的全部字母转换成小写

UPPER：将字符串string的全部字母转换成大写

INITCAP：将字符串string的首字母转换成大写

CONCAT：将俩个字符串连接成一个

SUBSTR：截取  (参数，开始，数目)

LENGTH：返回字符串的长度

INSTR：（参数，字母） 返回字母出现的位置

LPAD：（参数,长度，在前补齐参数字母）

 RPAD：（参数，长度,在后补齐参数字母）

TRIM ：

REPLACE：（参数,参数[,参数]）:第一个参数操作数,第二是要查找的字符,第三个是替换的字符，如果没有第三个就删除查找的字符。

案例：

//查询名称为scott的员工信息 （不区分大小写）

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp  where lower(ename)='scott';

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp  where upper(ename)='SCOTT';

//查询员工信息 把员工名称与工作连接在一起

SQL> select empno,concat(ename,job),mgr,hiredate,sal,comm,deptno from emp;

//查询员工信息 把员工名称与工作连接在一起

         SQL> select empno,concat(ename||'is work:',job),mgr,hiredate,sal,comm,deptno from emp;

//查询员工名称中含有O字符的位置

SQL> select empno,instr(ename,'O'),job,mgr,hiredate,sal,comm,deptno from emp;

//查询员工名称中含有O字符的位置并且求出了员工名称字符长度

SQL> select empno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from emp;

//在查询上面结果中过滤出员工工作从第三字母开始为ERK员工信息

SQL> select empno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from emp where substr(job,3)='ERK';

备注：当含有两个参数的时候,从开始位置直接到参数结束的结束为止

//在效果同上的同时指明了截取个数为3

         SQL> select empno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from emp where substr(job,3,3)='ERK';

//查询员工信息  薪资是10位位数不够在左部分补*填充

SQL> select empno,ename,job,mgr,hiredate,LPAD(sal,10,'*'),comm,deptno from emp;

//查询员工信息  薪资是10位位数不够在右部分补*填充

SQL> select empno,ename,job,mgr,hiredate,RPAD(sal,10,'*'),comm,deptno from emp;

//查询员工信息  把员工名称中含有S字符去除掉

SQL> select empno,TRIM('S' from ename),job,mgr,hiredate,10,comm,deptno from emp;

等效于：

SQL> select empno,TRIM( both 'S' from ename),job,mgr,hiredate,10,comm,deptno from emp;

//查询员工信息  把员工名称中前面有S字符去除掉

SQL> select empno,TRIM( Leading 'S' from ename),job,mgr,hiredate,10,comm,deptno from emp;

//等效于

SQL> select empno,LTRIM( ename,'S'),job,mgr,hiredate,10,comm,deptno from emp;

//查询员工信息  把员工名称中后面有S字符去除掉

SQL> select empno,TRIM( trailing 'S' from ename),job,mgr,hiredate,10,comm,deptno from emp;

等效于：

SQL> select empno,RTRIM( ename,'S'),job,mgr,hiredate,10,comm,deptno from emp;

二、数字函数：

     当所检索的数据为数字数据类型是，可以使用数字函数进行数学计算。在Oracle系统中，PL/SQL语言支持一下数字函数：

      ROUND: 四舍五入

            ROUND(45.926, 2)               45.93

TRUNC:     截断

            TRUNC(45.926, 2)               45.92

MOD: 求余

            MOD(1600, 300)                 100

ABS：绝对值

CEIL：返回大于或等于value的最小整数

FLOOR：返回小于或等于value的最大整数

SQRT :返回value的平方根 负数无意义。

案例：

//四舍五入 结果为46

SQL> select round(45.56) from dual;

//绝对值 结果为45.56

SQL> select abs(-45.56) from dual;

//大于等于最小整数 结果为-45

SQL> select ceil(-45.56) from dual;

//小于等于最大整数 结果为-46

SQL> select floor(-45.56) from dual;

//求余数 结果为300

SQL> select mod(1800,500) from dual

//截取的数的操作数是正数的情况下：只操作小数位  结果为1800.11

SQL> select trunc(1800.11111,2) from dual;

//截取的数的操作数是负数的情况下：操作的是小数点之前的位,把操作位小数点之前的位数全部改写成0. 结果就是1000

SQL> select trunc(1899.11111,-3) from dual;

 

三、日期时间函数：

     日期也是数据库中使用比较频繁的一种数据。在oracle系统中，按照数字格式来储存日期数据。Oracle系统的日期格式默认为 DD-MON-YY。日期时间函数用来返回当前系统的当前日期和时间，以及对日期和时间类型的数据进行处理运算。在Oracle系统中常用的日期时间函数如下：

         add_months(date,count);在指定的日期上增加count个月

last_day(date);返回日期date所在月的最后一天<span la