Oracle下SQL基本操作(三)(6)
ALTER SESSION SET NLS_LANGUAGE = 'SIMPLIFIED CHINESE';
---LAST_DATE
SELECT LAST_DAY(TO_DATE('2010-9-11','YYYY-MM-DD')) FROM DUAL;
------ 转换函数
--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'的员工的平均工资
相关新闻>>
- 发表评论
-
- 最新评论 更多>>