oracle的学习笔记--NVL()函数的使用技巧

一、给列起别名的规则：特殊字符（#$除外）、数字、空格需要加双引号处理；中文加不加双引号都可以；as关键字可以省略。

-- 查询员工姓名，员工编号，员工的工作。
SELECT empno AS "员工编号",ename "员工姓名",job 工作 FROM emp;
select empno as "员工编号",ename "员工姓名",job 工 作 from emp;--语法错误 ,别名“工 作”中出现空格没有加双引号
select empno as "员工编号",ename "员工姓名",job "工 作" from emp;--正确，出现空格加双引号
select empno as "员工编号",ename "员工@姓名",job "工 作" from emp;--正确，出现特殊字符加双引号
select empno as "员工编号",ename "123",job "工 作" from emp;--正确，出现数字加双引号
select empno as 员工$编号,ename "123",job "工 作" from emp;--正确，特殊字符中（#，$）除外

二、NVL()函数的使用：要记住一点，null值参与运算，结果为空

emp表：有员工没有奖金

--计算一下员工的年薪(月薪x12+奖金)
SELECT sal*12+comm 年薪 FROM emp;

未使用nvl函数的情况，查询结果如下：

--nvl(v1,v2),其中v1是原始值，v2是当为空时参与计算的值
SELECT sal*12+NVL(comm,0) AS 年薪 FROM emp;

使用nvl函数的情况下，查询结果如下：

三、distinct关键字和oracle特有的连接符“||”

需求：在一列中显示员工编号和员工姓名；方法：使用oracle特有的连接符

select 'empno:'||empno||' '||'ename:'||ename 个人简介 from emp;

查询结果：

distinct去重关键字，在查询结果中去掉重复的

select distinct job from emp;

四、条件查询（目的是过滤出满足条件的结果）

where是条件关键字，后面加条件

比较运算符：> < >= <= = <> !=

逻辑运算符：and or not

其余运算表达式：like模糊查询 % 匹配0个或多个字符

in not in 判断范围

between and 判断区间，包含边界

is null,is not null 判断是否为空值

---查询工作不是 MANAGER 的员工信息
SELECT * FROM emp WHERE job !='MANAGER';
SELECT * FROM emp WHERE job <>'MANAGER';
--查询工作是领导MANAGER 并且工资> 2000的员工信息
SELECT * FROM emp WHERE job='MANAGER' AND sal>2000;
--查询工资>2000 或者 工作是MANAGER的员工信息
SELECT * FROM emp WHERE sal>2000 OR job='MANAGER';
--查询员工姓名包含M的员工信息
SELECT * FROM emp WHERE ename LIKE '%M%';
--查询员工姓名第二位M的员工信息
SELECT * FROM emp WHERE ename LIKE '_M%';
--查询员工工作是MANAGER 或者工作室PRESIDENT
SELECT * FROM emp WHERE job='MANAGER' OR job='MANAGER';
--使用in来实现
SELECT * FROM emp WHERE job IN('MANAGER','PRESIDENT');
--查询工资在1500 和3000之间的员工信息
SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;
--查询有奖金的员工信息
SELECT * FROM emp WHERE comm IS NOT NULL;
--null不等于任何值 如果对null判断 比较判断 UNKUOW
SELECT * FROM emp WHERE comm=NULL;

五、排序关键字order by关键字的使用

--将员工的数据按照工资从小到大排序
SELECT * FROM emp ORDER BY sal;
--将员工数据按照奖金倒叙排序
SELECT * FROM emp ORDER BY comm DESC;
--null倒序排序 null在上面 指定null到末尾 nulls last
SELECT * FROM emp ORDER BY comm DESC NULLS LAST;

六、数据库函数：单行函数（数值、字符、日期、转换、通用函数），多行函数（max,min,sum,count,avg）

1.四舍五入 round(v1,v2) v1是原始的数值 v2是保留的小数位数（v2默认数值是0）
select round(43.726) from dual;
select round(43.726,0) from dual;--44
select round(43.726,1) from dual;--43.7
select round(43.726,2) from dual;--43.73
select round(43.726,-1) from dual;--40
select round(43.726,-2) from dual;--0
select round(53.726,-2) from dual;--100

2.--截取 trunc(v1,v2) v1是原始的数值 v2是保留的小数位数（ v2默认数值是0）
--43.726
select trunc(43.726) from dual;
select trunc(43.726,0) from dual;--43
select trunc(43.726,1) from dual;--43.7
select trunc(43.726,2) from dual;--43.72
select trunc(43.726,-1) from dual;--40
select trunc(43.726,-2) from dual;--0
select trunc(53.726,-2) from dual;--0

3.--mod取余数
select mod(10,3) from dual; --1

4.获取字符的长度 length(v1)
--abcde
select length('abcde') from dual;

5.截取字符串 substr(v1,v2,v3) v1原始字符串 v2开始截取位置 v3截取长度
--截取字符串 从0开始和1开始都是第一位开始截取
select substr('abcde',0,2) from dual; -- ab
select substr('abcde',1,2) from dual; --ab
select substr('abcde',-1,2) from dual; -- e
select substr('abcde',-2,2) from dual; -- de

6.替换字符串 replace(v1,v2,v3)v1原始字符串 v2被替换的字符 v3替换后的字符
--替换字符串 hello 替换匹配的所有字符
select replace('hello','l','o') from dual; -- heooo
select replace('hello','he','l') from dual; -- lllo
select replace('hello','he','') from dual; -- llo

7.大小写转换 upper() lower()
select * from emp;
select * from emp where ename = 'smith'
select * from emp where ename = 'sMIth'
select * from emp where ename = upper('smith');
select * from emp where ename = upper('Smith');
select * from emp where ename = upper('SMITH');

8.去除空格 trim()

--去除空格__abc_de__ 去除字符串两端的空格
select trim(' abc de ') from dual;--abc_de

七、日期函数

--查看系统当前时间

SELECT SYSDATE FROM dual;

--增加月份，查询3个月以后的日期
SELECT add_months(SYSDATE,3) FROM dual;
--计算所有员工到目前为止，入职了多少个月
SELECT months_between(SYSDATE,hiredate) FROM emp;
----计算所有员工入职的天数 日期和日期做计算 结果为天数
SELECT ROUND(SYSDATE-hiredate) FROM emp;

八、转换函数

--字符串转数字
SELECT to_number('123')+1 FROM dual;
--数字转字符串
select to_char(sal,'$99,999.00') from emp;
--日期转字符串
SELECT to_char(SYSDATE,'yyyy-mm-dd') FROM dual;
SELECT to_char(SYSDATE,'hh:mi:ss') FROM dual;
SELECT to_char(SYSDATE,'yyyy-MM-dd HH:mi:ss') FROM dual;
--字符串转日期
SELECT to_date('2017-05-03','yyyy-mm-dd') FROM dual;
SELECT to_date('2017-05-03 6:08:32','yyyy-mm-dd hh:mi:ss') FROM dual;--小时只能取1-12的值
SELECT to_date('2017-05-03 16:08:32','yyyy-mm-dd hh24:mi:ss') FROM dual;--小时可以取1-24的值

九、聚合函数和分组函数的使用

---查询每个部门的平均工资
select * from emp;
SELECT avg(sal) from emp group by deptno;
--显示部门编号
select deptno,avg(sal) from emp group by deptno;
--加入其余的列是不可以的，使用group by，select语句只能查询分组的列和聚合函数
select job,deptno,avg(sal) from emp group by deptno;
--使用多个列做分组 必须多个列的数值一致 才为一组
select job,deptno,count(*) from emp group by deptno,job;
--查询部门平均工资>2000的部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
--mySql分组可以起别名过滤
select deptno,avg(sal) s from emp group by deptno having s >2000;