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名员工?
