Oracle下SQL基本操作(三)(6)

------ 转换函数
--TO_NUMBER
SELECT TO_NUMBER('12')  FROM DUAL;
SELECT TO_NUMBER('12','9999')  FROM DUAL;
SELECT TO_NUMBER('12','9999.999')  FROM DUAL;
SELECT TO_NUMBER('12','9999')  FROM DUAL;
---会自动转换,所以to_number已经没什么用了
SELECT '12'* 2 FROM DUAL;
----TO_CHAR
SELECT TO_CHAR(12) FROM DUAL;
SELECT TO_CHAR(12,'9999') FROM DUAL;--带空格
SELECT TO_CHAR(12,'FM9999') FROM DUAL;--去掉空格
SELECT TO_CHAR(12,'09999') FROM DUAL;--带0
SELECT TO_CHAR(12,'$9999') FROM DUAL;--带$
SELECT TO_CHAR(12,'FM$9999') FROM DUAL;
SELECT TO_CHAR(12,'FML9999') FROM DUAL;
SELECT TO_CHAR(123456,'$9999.00') FROM DUAL;--####
SELECT TO_CHAR(12.123,'$9999.00') FROM DUAL;---四舍五入
SELECT TO_CHAR(TO_DATE('2010-09-11','YYYY-MM-DD'),'MM-DD-YYYY HH12:MI:SS AM')   FROM DUAL;
SELECT TO_DATE('2010-09-11 12:10:57','YYYY-MM-DD HH24:MI:SS')   FROM DUAL;
--日期中没写时间,to_date控制不会显示
SELECT TO_DATE('2010-09-11 ','YYYY-MM-DD HH24:MI:SS')   FROM DUAL;
---日期中没写时间,to_char控制会显示
SELECT TO_CHAR(TO_DATE('2010-09-11 ','YYYY-MM-DD HH24:MI:SS'),
'MM-DD-YYYY HH24:MI:SS')   FROM DUAL;
--如果不要格式控制,可以直接
SELECT SUBSTR(123456,2,3) FROM DUAL;

---其他函数
----NVL,两个参数,如果薪水为空,则返回资金
SELECT  NVL(EMP.SALARY,EMP.COMMISSION)   FROM EMPLOYEE EMP;
-----NVL2三个参数
SELECT  EMP.SALARY,NVL2(EMP.HIREDATE,EMP.SALARY,EMP.COMMISSION)   FROM EMPLOYEE EMP;
---COALESCEL
SELECT EMP.SALARY,EMP.COMMISSION,COALESCE(NULL,EMP.SALARY,EMP.COMMISSION,0) FROM EMPLOYEE EMP;
 
----用于可以手动修改
SELECT * FROM EMPLOYEE FOR UPDATE;
---多行函数,分组函数
----MAX,MIN,AVG,SUM 忽略空值
---COUNT,包括空行
SELECT MAX(EMP.SALARY),MIN(EMP.SALARY),AVG(EMP.SALARY),
SUM(EMP.SALARY), COUNT(*) FROM EMPLOYEE EMP;
---
SELECT COUNT(EMP.HIREDATE) 列中值的个数   FROM EMPLOYEE EMP;

---WHERE,GROUP BY,HAVING
---WHERE为GROUP BY服务,GROUP BY 为HAVING服务,HAVING 再控制最后的显示结果
SELECT EMP.DEPTID,  MAX(EMP.SALARY) 最大工资  FROM EMPLOYEE EMP
WHERE  EMP.DEPTID IN('01','02')---1首先选择符合DEPTID的行
GROUP BY EMP.DEPTID---2对符合条件的行进行分组
HAVING MAX(EMP.SALARY) >= 4000----3对要显示的分组进行限制
ORDER BY 最大工资 DESC;
SELECT * FROM EMPLOYEE;
SELECT * FROM EMPLOYEE FOR UPDATE;
--分部门统计学位为'1'的员工的平均工资