接着我们昨天总结的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所在月的最后一天