# oracle学习笔记（2）

SQL>conn  hr/passwd

SQL>select  *  from  tab;

--eg 最低工资的员工信息

select min(salary) from employees;

select last_name,salary

from employees

where salary = (select min(salary) from employees);

--另一种方法 rownumoralce中特有的）

select last_name,salary

from employees

where rownum <= 1

order by salary;

-- in

select max(salary) from employees group by department_id;

select last_name,salary

from employees

where salary in

(select max(salary) from employees group by department_id);

-- any  all

select employee_id,last_name,job_id,salary

from employees

where salary < any (select salary from employees where job_id = 'IT_PROG') and job_id <> 'IT_PROG';

select employee_id,last_name,job_id,salary

from employees

where salary < all (select salary from employees where job_id = 'IT_PROG') and job_id <> 'IT_PROG';

SQL函数（常用函数介绍,关键在于灵活运用）

--lower , initcap

select last_name,job_id,salary

from employees

where last_name = 'lee';

--where lower(last_name) = 'lee';

--where last_name = initcap('lee');

--substr,upper

select last_name,job_id,salary

from employees

--说明substr中的1表示位置，2为长度；且位置是从1开始。

from employees

where job_id = 'IT_PROG';

-- 都输出123456

.数字函数abs,round,trunk,mod

Select abs(-12.3) from dual;

------mod取模

select last_name, mod(salary,2000) from employees;

------round 四舍五入

select   round(16.167,0),round(16.167,2),round(16.167,-1),

round(16.167,-2) from dual;

------trunc 截取数字

select   trunc(16.167,0),trunc(16.167,2),trunc(16.167,-1),

trunc(16.167,-2) from dual;

select sysdate from dual;

select months_between('24-8-11' ,'02-10-89') from dual;

---extract 返回日期的某个域（如年，月，日,小时等）

select extract(year from sysdate) from dual;

--year 可以换成month,day

select extract(hour from sysdate) from dual;Error

Correct: select extract(hour from timestamp '2012-10-9 8:7:6') from dual;

.转换函数

--to_char  让数字或日期转换成字符串

select last_name,to_char(hire_date,'yyyy-mm-dd hh24:mi:ss')

from employees

where department_id = 90;

select to_char(sysdate, 'hh24:mi:ss') from dual;

--数字货币转换

select to_char(1099.22,'\$9,999,999.99') from dual;

select to_char(1099.22,'\$0,000,000.00') from dual;

select to_char(1099,'\$0,000.00') from dual;

select to_char(1099,'L9,999.99') from dual; --L 表示当地的货币符

--格式位数应大于实际数值的位数,否则无法显示

select to_char(1099,'L999.99') from dual;

--不够大时输出 ###############

select to_char(11234.1253,'\$9,999,999.99') money from dual;

-- to_date 将字符串转换成日期

select to_date('10-6-11') from dual;

--error ORA-01861: 文字与格式字符串不匹配

select to_date('2012-10-10') from dual;

select to_date('2012-10-10','yyyy-mm-dd') from dual;--correct

select to_date('2012-10-10','yyyy/mm/dd') from dual;--correct

select to_date('2012-10-10','mm/yyyy/dd') from dual;

--error  ORA-01843: 无效的月份

--to_number

select to_number('123') from dual;  --correct

select to_number('123') from dual; --error ORA-01722: 无效数字

select to_number('123','L999.00') from dual; --correct

select to_number('1,234.56','L9999.00') from dual; --correct

select to_number('1,234.56','L9999') from dual; --error

select to_number('1234.56','L9,999.00') from dual;--error!

.其他函数  nvl(), decode()

-- nvl

desc employees;

select last_name,salary,commission_pct,

(salary * (1 + nvl(commission_pct,0)))*12  total

from employees;

select last_name,salary,commission_pct,

nvl(to_char(commission_pct),'without')

from employees;

--nvl中的数值或字段类型一定要相同，如果不同，要像上例一样，用to_char这类函数转换成相同类型

--decode() 多值判断

--(1) 对于用户hr 表中employees 为例，如果job_id AD_VP ，工资为salary*1.1;

select last_name,job_id,salary,

from employees;

• 博文量
18
• 访问量
47310