Oracle单表查询与函数的使用

1、单表查询

    1.1、最基本的单表查询

        实例1、查询每个雇员的编号、姓名、职位、工资

SELECT empno, ename, job, sal
FROM emp;

        实例2、查询雇员编号，雇员姓名，雇员工资。结果以 雇员编号：empno    雇员姓名：ename    雇员工资：sal 形式显示

SELECT '雇员编号：' || empno empno, '雇员姓名：' || ename ename, '雇员工资' || sal sal
FROM emp;

1.2、where关键字

        实例1、要求查询出所有基本工资高于1500的雇员信息(关系运算符运用)

SELECT *
FROM emp 
WHERE sal>1500;

        实例2、查询出工资范围在1500-3000之间的雇员信息(逻辑运算运用)

SELECT * 
FROM emp 
WHERE sal>=1500 AND sal<=3000;

       实例3、查询领取佣金的雇员信息(空判断)

SELECT * 
FROM emp 
WHERE comm IS NOT NULL;

        实例4、查询出雇员编号是7369、7566、7788、9999的雇员操作（in关键字）

SELECT * 
FROM emp 
WHERE empno IN(7369,7566,7788,9999);

        注意：不允许在使用NOT IN查询时，查询内容为NULL。

        实例5、查询姓名是以字母以A开头的雇员信息（模糊查询）

SELECT * 
FROM emp 
WHERE ename LIKE 'A%';

1.3、distinct关键字

     作用：去除查询结果中重复的元素。

     实例1、查询员工部门编号（单列）

select distinct deptno from emp;

     实例2、查询员工部门编号和员工薪水（多列）

select distinct deptno,sal from emp;

1.4、group by 关键字 

    实例1、显示每个部门的最高工资，人数

select max(sal),count(*),deptno from emp group by deptno;

    注意：进行分组之后，查询的条件 只能是 多行函数 和 分组的那个字段 

     实例2、显示每个部门的不同岗位的人数并根据部门编号从小到大显示

select deptno,lower(job),count(*) from emp group by deptno,lower(job) order by deptno

1.5、having关键字

    实例1、显示部门人数超过4人的部门编号

select count(*),deptno from emp group by deptno having count(*)>4

    注意：where在分组前过滤的  having在分组后过滤

     实例2、统计各部门的最高工资，排除最高工资小于3000的部门

select deptno,max(sal) from emp group by deptno having max(sal)>=3000

1.6、order by 关键字

        实例1、要求按照工资由高到低排序，如果工资相同，则按照雇佣日期由早到晚排序

SELECT * 
FROM emp 
ORDER BY sal DESC,hiredate ASC;

        实例2、查询每个雇员的编号、姓名、年薪，按照年薪由低到高排序。

SELECT empno,ename,sal*12 income 
FROM emp 
ORDER BY income DESC;

        注意：在整个SQL查询结构之中，只有ORDER BY子句可以调用SELECT定义的别名，ORDER BY子句是在整个SQL查询结构之中最后执行的字句。

 

2、函数的使用

     Oracle中的函数根据处理场景不同可分为单行函数和多行函数。

    2.1、单行函数（对一行结果进行处理）

        常用的单行函数有字符函数、数值函数、日期函数、转换函数、通用函数。

      2.1.1  字符串函数

No	函数名称	返回类型	描述
1	UPPER(列|字符串)	字符串	将传入的字符串变成大写形式
2	LOWER(列|字符串)	字符串	将传入的字符串变成小写形式
3	INITCAP(列|字符串)	字符串	开头首字母大写，其他的字母变为小写
4	LENGTH(列|字符串)	数字	取得指定字符串的长度
5	SUBSTR(列|字符串，开始索引，[长度])	字符串	进行字符串的截取，如果没有设置长度，表示从截取全部
6	REPLACE(列|字符串，旧内容，新内容)	字符串	将指定的字符串数据以新数据替换旧数据

                实例1、将所有的雇员姓名以首字母大写的形式保存

SELECT INITCAP(ename) 
FROM emp;

        2.1.2  数值函数

No	函数名称	返回类型	描述
1	ROND(列|数字 [,小数位])	数字	实现数据的四舍五入，可以设置保留小数位
2	TRUNC(列|数字 [,小数位])	数字	实现数据的截取（不进位）
3	MOD(列|数字 [,小数位])	数字	求模

 

                实例1、使用ROUND函数

                    小数位 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分，并四舍五入。如果为负数则表示从小数点开始左边的位数，相应整数数字进行四舍五入，小数被去掉。

                    

                实例2、TRUNC()函数

                    小数位 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分。如果为负数则表示从小数点开始左边的位数，相应整数变为0，小数被去掉。

                    

                实例3、MOD()函数

                    

        2.1.3  日期函数

No	函数名称	返回类型	描述
1	ADD_MONTHS(列|(日期，月数))	日期	在指定日期上增加若干个月之后的日期
2	MONTHS_BETWEEN(列|日期，列|日期)	数字	返回两个日期之间的所经历的月数
3	LAST_DAY(列|日期)	日期	取得指定日期所在月的最后一天
4	NEXT_DAY(列|日期，星期X)	日期	返回下一个指定的一周时间数对应的日期

            实例1、计算所有雇员到今天为止雇佣的月数

SELECT MONTHS_BETWEEN(SYSDATE,hiredate) 
FROM emp;

            实例2、计算当前时间所在月的最后一天的日期

SELECT LAST_DAY(SYSDATE) 
FROM dual;

        2.1.4  转换函数

No	函数名称	返回类型	描述
1	TO_CHAR(列|日期|数字，转换格式)	字符串	将日期或数字格式化为指定结构的字符串
2	TO_DATE(列|字符串，转换格式)	日期	按照指定的转换格式编写字符串后将其变为日期型数据
3	TO_NUMBER(列|字符串)	数字	将字符串变为数字

            实例1、格式化日期时间

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss')
FROM dual;

            实例2、格式化数字

SELECT TO_CHAR(772333,'L999,,999,999,999,999') 
FROM dual;

        2.1.5  通用函数

No	函数名称	返回类型	描述
1	NVL(列|NULL，默认值)	数字	如果传入的内容是null，则使用默认数值处理，如果不是null，使用原始数据处理
2	DECODE(列}字符串|数值，比较内容1，显示内容1，比较内容2，显示内容2，...[默认显示内容])	数据类型	设置的内容会与每一个比较内容进行比较，如果内容相同，则会使用显示内容进行输出，如果都不相同，则使用默认信息输出

            1、NVL()函数            

                有时为了保证计算结果的准确性，必须将null替换为0，那么这就属于NVL()函数作用范畴了。 

                实例、查询每个雇员的编号、姓名、基本工资、佣金、年薪(null处理)

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

            2、decode()函数

                decode()函数函数类似于程序的if...else，但是与if...else不同的是，此处不能够判断关系，只能够判断内容是否相同。

                实例、将所有的职位信息替换为中文显示

SELECT ename,job,DECODE(job,'CLERK','办事员',SALESMAN','销售','PRESIDENT','总裁','MANAGER','经理','ANALYST','分析员','***') 
FROM emp;

     2.2、多行函数（对多行结果进行处理）

           常用的多行函数有sum()、avg()、count()、max()、min()

         实例1、查询雇员总人数

select count(*) from emp;

         实例2、查看员工的最高工资，最低工资，总工资，人数，平均工资

select max(sal),min(sal),sum(sal),count(*),avg(sal),sum(sal)/count(*) from emp;

注意：在没有分组的情况下，多行函数不能和普通字段或者单行函数结合使用！

3、集合操作（交并差）

 3.1、交集（intersect）

实例：查询emp表ename中既含’A‘又含有‘M’

select * from emp where ename like '%A%' 
intersect 
select * from emp where ename like '%M%'; 

3.2、并集（union/union all）

        union求并集，公共部分只有包含一次

        实例：查询emp表ename中含’A‘或含有‘M’

select * from emp where ename like '%A%' 
union 
select * from emp where ename like '%M%'; 

        union all求集并，公共部分只有包含二次

        实例：查询emp表ename中含’A‘或含有‘M’

select * from emp where ename like '%A%' 
union all 
select * from emp where ename like '%M%'; 

    3.3、差集（minus ）

     实例：查询emp表sal从700到1200 

select * from emp where sal between 700 and 1300 
minus 
select * from emp where sal between 1200 and 1400;