999') 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'的员工的平均工资
SELECT EMP.DEPTID, AVG(EMP.SALARY) FROM EMPLOYEE EMP
WHERE EMP.QUALID = '1'
GROUP BY EMP.DEPTID
----统计每种学位的平均工资
SELECT EMP.QUALID,AVG(EMP.SALARY) FROM EMPLOYEE EMP
GROUP BY EMP.QUALID
---统计每个部门的总年薪
SELECT EMP.DEPTID,SUM(COALESCE(EMP.SALARY + EMP.COMMISSION, EMP.SALARY,EMP.COMMISSION,0)) * 12 总年薪 FROM EMPLOYEE EMP
GROUP BY EMP.DEPTID
SELECT * FROM EMPLOYEE;
---统计每个部门 员工资金大于1000 的最小工资 再按
SELECT EMP.DEPTID ,MIN(EMP.SALARY) 最小工资
FROM EMPLOYEE EMP
WHERE EMP.COMMISSION > 1000
GROUP BY EMP.DEPTID
--- ORDER BY 最小工资 desc;
----员工姓名 部门名
SELECT EMP.NAME,DEP.DEPTNAME
FROM EMPLOYEE EMP,DEPARTMENT DEP
WHERE EMP.DEPTID = DEP.DEPTID
----员工姓名 学位名
SELECT EMP.NAME,QUA.QUALNAME
FROM EMPLOYEE EMP,QUAFICATION QUA
WHERE EMP.QUALID = QUA.QUALID
-----SQL 1999新连接标准
SELECT EMP.EMPLOYEEID, DEPT.DEPTID
FROM EMPLOYEE EMP
CROSS JOIN DEPTMENT DEPT;
SELECT EMP.EMPLOYEEID, DEPTID
FROM EMPLOYEE EMP
NATURAL JOIN DEPTMENT DEPT;
SELECT EMP.EMPLOYEEID, DEPTID
FROM EMPLOYEE EMP
JOIN DEPTMENT DEPT USING (DEPTID);
----教学部的员工姓名 ,学位名,工资等级 SELECT * FROM EMPLOYEE;
--- SELECT * FROM EMPLOYEE FOR UPDATE
SELECT EMP.NAME,QUA.QUALNAME, LEV.LEVELNO,QUA.QUALID
FROM EMPLOYEE EMP, QUAFICATION QUA, EMPLEVEL LEV , DEPARTMENT DEP
WHERE DEP.DEPTNAME = '教学部'
AND EMP.QUALID = QUA.QUALID
AND EMP.SALARY BETWEEN LEV.LOWSALARY AND LEV.L