oracle4

内连接

and where都允许,先过滤后连接

 

外连接分两种情况

and 在外连接之前,先对匹配表进行过滤,用关键字and

where 在外连接之后,需要通过匹配表的列对外连接结果集过滤时,用关键字where

对驱动表的过滤必须用关键字where

 

连接

从结果集的产生分类

cross join

inner join

       等值连接(两张不同表,描述共同属性的列)

       非等值连接 (between and)

           自连接(同一张表的不同列有关系)

outer jion

       等值连接(两张不同表,描述共同属性的列)

       非等值连接 (between and)

           自连接(同一张表的不同列有关系)

DML

insert into values 只能插入一条记录

insert into tabname values ('a','b',null,1)顺序(desctabname)

insert into tabname(c1,c2) values ('a','b')

insert into tabname select 插入多条记录

 

ORA-00947: not enough values

插入的值比表里的列少

ORA-00913: too many values

插入的值比表里的列多

 

rownum 伪列记录号,行号

rownum必须从1开始才能选出记录.

分页问题

第一页 whererownum <= 10

第二页

列出第3条至第6条记录

select rn,ename,salary

from ( select rownum rn,ename,salary

      from emp_hiloo

      where rownum <= 6)

where rn >= 3

排名问题

工资最高的前3名员工?

select rownum,ename,salary

from (select ename,salary

     from emp_hiloo

     order by salary desc)

where rownum <= 3

 

update

where 同select,确定哪些记录应该修改

set colname = value

set bonus = null  =表示赋值

where bonus = null   =表示相等 false

 

delete删除一条记录

delete from tabname

where 同 select 确定哪些记录应该删除

 

DDL

数据类型,约束

date

char 有缺省宽度为1,按定义长度存,取值若固定,用char,对空格不敏感

varchar2 必须定义宽度,按实际长度存,取值不固定,用varchar2,对空格敏感

 

DCL

grant

revoke

usera userb

如果用户userb想select用户usera下的某张表

connect usera/usera

grant select on tab1 to userb

connect userb/userb

select count(*) from usera.tab1;

如果用户usera不想userb select他下的某张表

connect usera/usera

revoke select on tab1 from userb;

 

TCl

transaction 事务

事务结束 commit(提交,入库)  rollback(回滚,取消操作)

DML 把旧数据放入rollback segment(回滚段 公共资源),

当你commit或者rollback,即事务结束回滚段里的数据释放.

 

commit/rollback

DMLS

commit/rollback

DDL是自动提交的

 

事务的并发操作,操作同一张表,定义事务的隔离级别,缺省事务的隔离级别read committed.读已经提交了的数据和本session正在处理的数据.

 

DML加锁方式

加锁粒度(级别) 表行

锁性质  共享排他

表级共享锁

行级排他锁

              表级共享锁    行级排他锁

s1           ok           ok

s2           ok           wait

s3           ok           ok

 

DDL加锁方式 DDL排他锁

ORA-00054: resource busy(资源忙,表忙,DML操作表) andacquire获得 with NOWAIT不等待 specified 指定

 

为什么要commit,rollback

1 你操作的数据其他session看不见

2 你加在表上的锁不释放,会阻塞其他人的操作

3 你占用的回滚段资源不释放

 

create table

课堂练习

1 哪些部门没有员工zhangwuji

select d.dname

from emp_hiloo e rightjoin dept_hiloo d

on e.deptno = d.deptno

and e.ename ='zhangwuji'

where e.empno is null

 

select d.dname

from (selectdeptno,ename from emp_hiloo

       where ename = 'zhangwuji')  e right join dept_hiloo d

on e.deptno = d.deptno

where e.empno is null

 

2 列出哪些员工的工资是3,5级

select e.ename,e.salary,s.grade

from emp_hiloo e join salgrade_hiloo s

on e.salary between s.lowsal and s.hisal

and s.grade in (3,5)

3 列出各个级别的人数(不包含0级)

select s.grade,count(s.grade)

from emp_hiloo e join salgrade_hiloo s

on e.salary between s.lowsal and s.hisal

group by s.grade

4 列出各个级别的人数(包含0级)

select s.grade,count(e.empno)

from emp_hiloo e right join salgrade_hiloos

on e.salary between s.lowsal and s.hisal

group by s.grade

 

5 用sql脚本,实现转账功能,创建一张账户表,两列(账号,余额),插入两条记录'a' 4000,'b' 100,完成转账,从账户'a'转1500元钱到账户'b'.

 

编写和运行sql脚步

1 在linux写完脚步文件,全选,拷贝,在26上用vi test.sql,按i,粘贴,按ESC,按:wq!,脚步文件里是sql语句,每条语句后有;

2 必须在test.sql的目录下执行,

%sqlplus openlab/open123 @test.sql,

或者在文件名前加上所在路径

sqlplus openlab/open123 @../test.sql

 

63月份入职的员工

SQL> select ename,hiredate

 2  from emp_hiloo

 3  whereto_char(hiredate,'fmMONTH') = 'MARCH';

 

 

 

课外练习

1 zhangwuji的领导是谁? (用 in)

select ename from emp_hiloo

where empno in (select mgr from emp_hiloo

                   where ename = 'zhangwuji')

2 zhangwuji领导谁?(用 in)

select ename from emp_hiloo

where mgr in (select empno from emp_hiloo

                where ename = 'zhangwuji')

3 哪些员工的工资比同职位的平均工资高?

select e.ename,e.salary

from emp_hiloo o

where salary > ( selectround(avg(salary)) from emp_hiloo i

                    where o.job = i.job)

 

select e.ename,e.job,e.salary,a.savg

from emp_hiloo e  join

         (select job,round(avg(salary)) savg

          from emp_hiloo

          group by job) a

on e.job = a.job

and e.salary > a.savg

4 zhangwuji的领导是谁? (用 inner join)

select e.ename,m.ename

from emp_hiloo e join emp_hiloo m

on e.mgr = m.empno

and e.ename = 'zhangwuji'

5 zhangwuji领导谁?(用 inner join)

select e.ename,m.ename

from emp_hiloo e join emp_hiloo m

on e.mgr = m.empno

and m.ename = 'zhangwuji'

6 各个部门的平均工资,列出部门名称,平均工资

select d.dname,round(avg(e.salary))

from emp_hiloo e join dept_hiloo d

on e.deptno = d.deptno

group by d.dname ---

select max(d.dname),round(avg(e.salary))

from emp_hiloo e join dept_hiloo d

on e.deptno = d.deptno

group by d.deptno

 

select d.dname,a.savg

from dept_hiloo d join

           (selectdeptno,round(avg(salary)) savg

            from emp_hiloo

            group by deptno) a

on a.deptno = d.deptno

 

7 哪些部门没有员工(用 outer join 用 not in)

select dname from dept_hiloo

where deptno not in (select deptno fromemp_hiloo)

 

select d.dname

from emp_hiloo e right join dept_hiloo d

on e.deptno = d.deptno

where e.empno is null

 

课后练习

1 列出按工资排名的第3名至第6名员工?