Oracle分页问题

Rowid 记录的记录：索引在跟Rowid打交到

Rownum=1,<number,<=number

Rownum 是在内存中的临时值

有order by 按照order by的顺序，没有的话就按照存储顺序。

分页：

Select

rn,id

from (select rownum rn, id from s_emp where rownum<=20)

where rn between 10 and 20;

Top n：

Select first_name , salary

from

(select * from s_emp where salary is not null order by salary desc[1stClaymo1])

where rownum<=5;

Select * from s_emp where rownum[1stClaymo2] < = 5 order by salary

Where子句中不能用列别名，select 先看where再看要查询的字段，所以别名还没有出现。

select id, dept_id, salary, case when dept_id = 31[1stClaymo3] then salary*1.1

when dept_id = 32 then salary*1.2

end "new salary"

from s_emp

case when 各种数据库基本都支持

decode函数[1stClaymo4]

第一个参数，如果值等于第二个参数，返回第三个值

不等，判断是不是等于第四个，如果等，则返回第五个……

依次类推，如果都不等则返回默认值，如果没有默认值，则返回空

应用：行列转置问题

例题：各个部门不同职位的平均工资

select dept_id, title, avg(salary) from s_emp group by dept_id,title

select

job,avg[1stClaymo5](decode(dept_id,10,salary) ) "dept_10",

avg(decode(dept_id,20,salary) ) "dept_20"

from s_emp

group by[1stClaymo6] job

select

job,count[1stClaymo7](decode(dept_id,10,salary) ) "dept_10",

count(decode(dept_id,20,salary) ) "dept_20"

from s_emp

group by job

作业：

把count值为0的变成null

把一个同学的成绩转置过来

select

d.name, r.name, avg(e.salary)

From

s_emp e, s_dept d, s_region r

Where

e.dept_id = d.id and d.region_id = r.id

group by e.name, r.name[1stClaymo8]

Set head off

Set feed off

Set echo off

Spool xxx.sql

Sql命令

Spool off

处理乱码：

处理的字符集要和数据库的字符集一样。

数据库本身的字符集，client端的字符集要和数据库的一样。

乱码：

数据库中没有乱，但是显示端的字符集不正确，设置显示端的字符集和数据库一致就可以了。

存如数据库的时候就是乱的。

$ORACLE_HOME/rdbms/demo/summit2.sql

setenv NLS_LANG AMERICAN_AMERICA.US7ASCII

Select dump(first_name) from s_emp where dept_id=42

关联子查询：主表和子表建立连接

Select * from s_emp where salary = (select min(salary) from s_emp);

找出谁的工资比本部门的平均工资高：

关联子查询：

select

first_name, salary

From

s_emp o

where

salary >(select avg(salary) from s_emp e where e.dept_id = o.dept_id);[1stClaymo9]

非关联子查询：

select

sa.first_name, sa.salary

from

(select dept_id, avg(salary) avgs from s_emp) sa, s_emp e

where

sa.dept_id = e.dept_id and e.salary > sa.avgs[1stClaymo10]

哪个部门没有员工：

非关联子查询：

select

deptno, dname

from

dept

where

deptno not in (select deptno from emp where deptno is not null);

外连接：

select

s.deptno, d.name, e.deptno

from

emp e, dept d

where

e.deptno(+) =d.deptno and e.deptno is null

Not exists

select

d.deptno, d.dname

from

dept d

where

not exists (select 1 from emp e where e.deptno = d.deptno)

not in

outer join

not exists

union 排序排重

union all 就是累积所有记录

intersect

A minus B 在A中不在B中的。

select rownum,id from s_emp where rownum<=20

minus

select rownum,id from s_emp where rownum<=10

in

inner join

exist

intersect

select * from test@aaaa[1stClaymo11]

drop database link dbln1[1stClaymo12]

create database link dbln1

Connect to hr[1stClaymo13] indentifed by hr

using 'tarena23[1stClaymo14]'

Select conut(*) from test@dbln1

写绑定变量，不要写直接的文字值

PL/SQL支持静态SQL

经常执行的语句放在内存中，使用数据库时，语句的形式要一样(大小写一样，最好用同构的)。

多对多关系

---

这里给rownum起别名无用

取自order by 之前

可以放任何表达式

与case when的作用差不多

不处理空值

Select 子句中只能是组函数和group by后跟的字段

统计个数的，如果有空的情况则count的值是0

Select

max(d.name), max(r.name), avg(e.salary)

From

s_emp e, s_dept d, s_region r

Where

e.dept_id = d.id and d.region_id = r.id

group by e.dept_id

子查询要执行多次，效率太差了

效率高，子查询只执行一次

数据库链

建立在client端

与远程数据连接时使用的用户名

$ORACLE_HOME/network/admin 下的tnsnames.ora