ORACLE授课笔记

1.NVL 函数
select case NVL(mission_pct,0)
When 0 THEN -1
else mission_pct
END "AA"
FROM employees
2 。

TO_CHAR
The following are number examples for the to_char function. to_char(1210.73, '9999.9')would return '1210.7'
to_char(1210.73, '9,999.99')would return '1,210.73'
to_char(1210.73, '$9,999.00')would return '$1,210.73'
to_char(21, '000099')would return '000021'
日期：
2.Round 四舍五入
3.To_date
前几天做了些关于oracle的数据库知识，总结了一些，网上查了一些，传上来，分享一下24小时的形式显示出来要用HH24
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
to_date() function
select to_date('2008-03-01 12:11:20','yyyy-MM-dd HH24:mi:ss') from dual;
1.日期格式参数含义说明
D 一周中的星期几
DAY 天的名字，使用空格填充到9个字符
DD 月中的第几天
DDD 年中的第几天
DY 天的简写名
IW ISO标准的年中的第几周
IYYY ISO标准的四位年份
YYYY 四位年份
YYY,YY,Y 年份的最后三位，两位，一位
HH 小时，按12小时计
HH24 小时，按24小时计
MI 分
SS 秒
MM 月
5 TO_NUMBER
使用TO_NUMBER函数将字符转换为数字
TO_NUMBER(char[, '格式'])
数字格式格式
9 代表一个数字
0 强制显示0
$ 放置一个$符
L 放置一个浮动本地货币符
. 显示小数点
, 显示千位指示符
1. 人员总工资薪资×13 + 佣金
1.每个人佣金调整为0.15
显示部门员工和他们的部门经理的名字
SELECT st_name "Employee", w.employee_id "EMP#", st_name "Manager", m.employee_id "Mgr#"
FROM employees w join employees m
ON (w.manager_id = m.employee_id);
显示经理之前聘用的员工。

显示部门，经理，员工
SELECT st_name, w.hire_date, st_name, m.hire_date FROM employees w, employees m
WHERE w.manager_id = m.employee_id
AND w.hire_date < m.hire_date;
显示部门的平均年薪并且显示名称地点。

使用round 函数
SELECT d.department_name "Name", d.location_id "Location", COUNT(*) "Number of People",
ROUND(AVG(salary),2) "Salary"
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name, d.location_id;
显示1995，1996，1997，1998 聘用员工的总数
SELECT COUNT(*) total,
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1995,1,0))"1995", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1996,1,0))"1996", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1997,1,0))"1997", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1998,1,0))"1998" FROM employees;
显示King部门下的（lastname）的员工平均工资
SELECT last_name, salary
FROM employees
WHERE manager_id = (SELECT employee_id
FROM employees
WHERE last_name = 'King');
显示平均工资最高的部门
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id);
显示员工数最多的部门
SELECT d.department_id, d.department_name, COUNT(*) FROM departments d, employees e
WHERE d.department_id = e.department_id
GROUP BY d.department_id, d.department_name HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM employees
GROUP BY department_id);
Rollback
SavePoint
Commit
把所有人的佣金提高一倍
Create Table
create table v as
SELECT employee_id, last_name, salary, commission_pct FROM employees
WHERE job_id LIKE '%REP%';
Insert into v
INSERT INTO reps_table (id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees
WHERE job_id LIKE '%REP%';
显示行政部的所有人员Executive
SELECT department_id, last_name, job_id
FROM employees
WHERE department_id IN (SELECT department_id FROM departments
WHERE department_name = 'Executive');
列出首字母是J K LM的员工名字substr last_nane
SELECT TO_CHAR(hire_date, 'Day')
FROM employees
显示聘用人数最多的是星期几（TO_CHAR(hire_date, 'DAY')）
SELECT last_name, TO_CHAR(hire_date, 'DAY') day
FROM employees
WHERE TO_CHAR(hire_date, 'Day') =
(SELECT TO_CHAR(hire_date, 'Day')
FROM employees
GROUP BY TO_CHAR(hire_date, 'Day')
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM employees
GROUP BY TO_CHAR(hire_date, 'Day')));
列出公司的员工进入公司那天的生日，（员工日历）按照日期大小排序。

TO_CAHR(hire_date,’DDD’)
SELECT last_name, TO_CHAR(hire_date, 'Month DD') BIRTHDAY FROM employees
ORDER BY TO_CHAR(hire_date, 'DDD');。