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