oracle10g学习笔记

1.Sequence

create table liuming

(

id integer NOT NULL,

name varchar(20) NOT NULL

) 

delete from liuming

create sequence lm increment by 01 创建sequence

insert into liuming values(1,'liuxy')

insert into liuming values(2,'dengkm')

insert into liuming values(3,'yangjj')

insert into liuming values(4,'kongtt')

insert into liuming values(lm.nextval,'liuming');使用序列sequence lm

update liuming set name='miaojj'where id=6

update liuming set name='jiangc'where id=7

update liuming set name='xieys'where id=8

create  table  fromliuming 

(id integer not null primary key,

lid integer not null,

salary number(5,1))

2.外键：

添加外键：alter table fromliuming add constraint FK00001

foreign key(lid) from liuming (id)  

删除外键alter   table  fromliuming   drop  constraint   FK00001 

练习：删除id小于平均值的：Delete  from liuming where  id  > ( select avg(id)  from liuming)

3.Rownum

rownum 大于不成立，等于1成立，小于成立。

查看出的记录小于6：select rownum, name from liuiming where rownum<6;

select rownum,name

from (select rownum no,name from liuiming )where no BETWEEN 10 and 20;

查第10到20条记录

select rownum,name from ( select name from liuiming order by name )

查询rwonum大于2，小于6的记录，并且按从小到大排序。

select *from (select rownum,name 

from (select name from liuming group by name)

)where rownum >2 and rownum<6出错

rowmun必须用别名,否则不能使用。

select *from (select rownum no,name 

from (select name from liuming group by name)

)where no >2 and no<6 正确

4.rowid—查相同记录

select rowid, name from liuming

5.导出  导入

Exp：导出数据表CMD命令：

exp scott/tiger@orcl file=’d:/lium.txt’ tables=liuming full=y

exp scott/tiger@orcl file=’d:/lium.txt’ tables=liuming

exp scott/tiger@orcl file=’d:/lium.txt’ ower=scott

Imp：导入数据表：imp system/pass99@orcl file='D:/liuming.txt' tables=liuming

C:/Program Files/MySQL/MySQL Server 5.0/bin>imp system/pass99@orcl file='D:/liuming.txt' tables=liuming

Import: Release 10.2.0.1.0 - Production on 星期六 5月 22 16:27:10 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

经由常规路径由 EXPORT:V10.02.01 创建的导出文件

警告: 这些对象由 SCOTT 导出, 而不是当前用户

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入

. 正在将 SCOTT 的对象导入到 SYSTEM

. 正在将 SCOTT 的对象导入到 SYSTEM

. . 正在导入表                       "LIUMING"导入了           1 行

成功终止导入, 没有出现警告。

本地exp,imp:

1.新建table  db

exp   scott/pass99@orcl   file =‘d:/db.dmp’    tables =db

2.删除新建的db

imp scott/pass99@orcl file =‘d:/db.dmp’ tables =db

2010年5月21日星期五

CMD命令：

sqlplus hr/pass99@//172.19.200.101/orcl

进入hr用户的数据库

1.NVL函数

统计员工的年工资：

select  round(employees.salary * 13 +

NVL(commission_pct,0)*employees.salary * 13) "total"

from employees

1保留小数点后一位，-1保留到十位

练习:用NVL将employees中commission_pct 中为的null去掉

select * from employees

where NVL(commission_pct,0)!=0

2.case - when -then --else- end

 例子：select case job_id 

when  'AD_PRES'    then 'P'

when   'AD_VP'   then 'V'

else 'UNKNOWN'

end  "TITLE"

from jobs

3.to_char

 to_char 函数的数值举例：

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'

一般我们常用的参数是YY，DD，MM等。详细解释在后面。

4.dual是空表，永远只有一行一列,是一个虚表只是为了满足我们的需要，

SELECT 100/6 from dual

select sysdate from dual数据库查出的时间是本地的标准时间，机器的设置不同输出的结果是不同的。

练习：95年到99年每年分别进了多少员工？

      或者：显示1995,1996,1997,1998聘用员工的总数

select the_year,count(*) from 

(select case to_char(hire_date,'YY')

when '96' then '1996'

when '97' then '1997'

when '98' then '1998'

when '99' then '1999'

else 'U'

end the_year

from employees

)othertable

where the_year<>'U'

group by the_year

5.to_number用的较少

select  to_number('97','99')from dual

select '98'+'97' from dual

select '98'+97 from dual

select to_number('97','99')+to_number('95','99')from dual

6.连接符号：||

select 'the user name is '||first_name|| last_name||'and salary is '

|| to_char(salary,'9999999.99')from employees

可以输出一句话：the user name is PatFayand salary is     6000.00

练习：显示部门员工和部门经理的名字

select w.last_name EMPLOYEE, w.employee_id EMP_ID, m.last_name MANAGER,m.manager_id MANR_ID

from employees w join employees m

on w.manager_id=m.employee_id

查找出所有部门中平均工资最高的

第一种：select max(avg_salary) FROM(

select  avg(salary) avg_salary from employees

group by department_id

)

第二种：select avg_salary from

(select  avg(salary) avg_salary from employees

group by department_id

order by avg_salary desc

)

where rownum=1

第三种：select department_id,avg(salary) from employees

group by department_id

having avg(salary)=(select max(avg(salary))from employees

group by department_id)

显示king部门下的员工平均工资

select avg(salary) , department_id from employees

where department_id in(

select department_id from employees

where last_name='King' and manager_id is not null)

group by department_id

显示每个部门的员工的总数并按总数排序

第一种

select * from 

(

select department_id, count(*) from employees

group by department_id

order by count(*) desc

)

where rownum=1

第二种

select department_id ,count(*)  from employees

group by department_id

having count(*)=(select max(count(*)) from employees

group by department_id)

select max(savg), bb from 

(select department_id,avg(salary) savg, 1 bb from employees

group by department_id)

group by bb

2010年5月21号星期五  下午

1.事务回滚、提交

例：delete from V;

ROLLBACK

savepoint norecord

insert into v values (5,'lm',200,0.20)

savepoint r1

insert into v values (6,'dkm',300,0.30)

savepoint r2

rollback r1

rollback to SAVEPOINT r1

delete from V;

commit；

select * from V

create table V1 as (select employee_id, last_name, salary, commission_pct

 from employees

 where job_id like'%REP%')

insert into  V 

(select employee_id, last_name, salary, commission_pct

from employees

where job_id like'%REP%') 

update employees 

set commission_pct= commission_pct*2

rollback；

2.修改值：

SQL> update employees set salary=(select salary from employees where employee_id=100), job_id=(select job_id from employees where employee_id=100) where employee_id=203;

已更新 1 行。

SQL> select salary , job_id from employees where employee_id=203;

    SALARY JOB_ID

---------- --------------------

     24000 AD_PRES

SQL> commit;

SQL> update employees set salary=&salary, job_id='&job_id'

  2  where employee_id=203;

输入 salary 的值:  6500

输入 job_id 的值:  HR_REP

原值    1: update employees set salary=&salary, job_id='&job_id'

新值    1: update employees set salary=6500, job_id='HR_REP'

3.左连接：left join

显示行政部的所有人员

select * from employees

left join departments on departments.department_id=employees.department_id

where departments.department_name='Executive'

4.函数substr

列出首字母是JKML的员工名字substr last_name

select * from employees

where substr(last_name,1,1) in ('J','K','L','M')

select * from employees

where last_name like 'J%'

or last_name like 'K%'

or last_name like 'M%'

or last_name like 'L%'

练习：聘用人数最多的是星期几select to_char（hire_date，Day）

alter session set nls_date_language='american'

select * from (

select count(*) no,to_char(hire_date, 'Day') from employees

group by to_char(hire_date, 'Day')

order by no desc)

where rownum=1

列出公司的员工日历，按照日期大小排序

select last_name, hire_date

from employees

order by to_char(hire_date, 'DDD')

统计一下每个国家的工作人员的总数

第一种：

select c.country_name, c.country_id,count(*)

from locations l, countries c, departments d, employees e

where l.location_id= d.location_id

and e.department_id= d.department_id

and l.country_id= c.country_id

group by c.country_name, c.country_id

第二种：

select c.country_name, c.country_id,count(*)

from countries C

right join locations l on l.country_id= c.country_id

right join departments d on l.location_id= d.location_id

right join employees e on e.department_id= d.department_id

group by c.country_name, c.country_id

第三种：

select  l.country_id,count(*)

from locations l

full join countries C on l.country_id= c.country_id

full join departments d on l.location_id= d.location_id

full join employees e on e.department_id= d.department_id

where e.employee_id is not null

group by  l.country_id

第四种：

select  c.country_id,count(*)

from employees e

left join departments d on e.department_id= d.department_id

left join locations l on l.location_id= d.location_id

left join countries c on l.country_id= c.country_id

group by  c.country_id

练习题目：

用NVL将employees中commission_pct 中为null的项去掉

95年到99年每年分别进了多少员工？

显示1995,1996,1997,1998聘用员工的总数

显示部门员工和部门经理的名字

查找出所有部门中平均工资最高的

显示king部门下的员工平均工资

显示每个部门的员工的总数并按总数排序

显示行政部的所有人员

列出首字母是JKML的员工名字substr last_name

聘用人数最多的是星期几select to_char（hire_date，Day）

列出公司的员工日历，按照日期大小排序

统计一下每个国家的工作人员的总数