Oracle函数续与连接查询

姜1490

alter user scott account lock;
alter user scott account unlock;
alter user scott identified by tiger
重要的单行函数:
NVL(*),NVL2,NULLIF,COLLASCE,CASE,DECODE(*).
decode(expr1,if1,then1,...,default)
case when .. then
else defaultvalue
end.
collasce (xx,xx,xx,0)
NULLIF(a,b)
NVL2(a,b,c)
转换函数:
to_char()
to_number(11.111,'$99.99')
to_date(xxxx,'yyyy-MM-dd')
都可以指定格式.
DD:月里第几天
D:星期第几天
Mon,MONTH,MM -> 几月
RR:年份定义
SUBSTR(a,b/*+|-*/,c)
INSTR(a,b,c,d)
TRIM(leading|both|trailing)
LPAD
RPAD(a,10/*补齐几位*/,'*')
LOWER
UPPER
INITCAP
REPLACE
LENGTH

MOD(m,n)
round(m,n) vs sql server round
trunc(m,n) vs sql server trunc
ceil()
floor()
abs()
sort()
power()

日期函数:
add_months
months_between
next_day:当前日期的下一个星期几是哪一天
last_day:当前月的最后一天.
round(date,'year')
trunc(...
sysdate   vs sql server getdate()

'23' + 12 = 35
date +/- number/(60/24)
date - date
date + date // 不可以加的出来

列的别名只能出现select order 中,不能出现在where中
表的别名能出现在任何地方
order by a desc, b desc
distinct
列别名有特殊字符加双引号.后面使用也要带上双引号
-----------
连接查询
笛卡尔积:from a,b
a inner join b
外连接,自连接

select e.last_name,e.job_id,d.department_id from employees e,departments

d,locations l where e.department_id=d.department_id and

d.location_id=l.location_id and l.city='Toronto'

select e.last_name,e.job_id,d.department_id from ( employees e join

departments d on e.department_id=d.department_id) join locations l on

d.location_id=l.location_id where l.city='Toronto'

select e.last_name,e.job_id,d.department_id from employees e,departments d

where e.department_id  in ( select department_id  from departments dd join

locations l on dd.location_id=l.location_id where l.city='Toronto') and

e.department_id=d.department_id

select d.department_name, count(*) from employees e
join departments d
on e.department_id=d.department_id
group by e.department_id,d.department_name
having count(e.department_id) =
(
select max(count(*)) from employees group by department_id
)
----------------------------------------------
数据库事务处理
独占锁锁行,共享锁锁表

事务隔离级别:
脏读

不可重复读

幻读
隔离级别:
read uncommited
read commited
isolation_repeatable_read
islation_serializable