Oracle学习系列4
时间：2021-07-01 10:21:17
帮助过：21人阅读
************************************************************************************
数据库更新操作：
分类：
查询操作：select
更新操作:insert ,update , delete
为了保存原始的emp表的信息，在进行增删改之前备份词表：
create table emp_bak as select * from emp ;
//将表emp结构和数据完整的复制出来
添加数据：
insert into table ( [ col1,col2,col3,. . .] )
values( 值1，值2，值3，...) ;
ex:
insert into emp(enpno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7899,
‘kevin_dfg‘,
‘captian‘,
7369,
‘14-2月-95‘，
100000，
300，
40) ;
select * from emp;
//查询记录是否添加
ex:插入新雇员，无领导，无奖金：
insert into emp(enpno, ename, job, hiredate, sal, deptno)
values (7879,
‘Dustin_fg‘,
‘captian‘,
‘14-2月-95‘，
10000，
40) ;
ex: 使用to_date()函数将字符串类型的数据变为date类型的数据
insert into emp(enpno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7899,
‘kevin_dfg‘,
‘captian‘,
7369,to_date(
‘2016-04-05‘,
‘yyyy-mm-dd ‘)，
100000，
300，
40) ;
更新数据：
update table
set 字段1=值1 ， 字段2=值2 ，... ；
//全部修改
update table
set 字段1=值1 ， 字段2=
值2 ，...
where 修改条件 ；
//局部修改（重点推荐）
ex： update emp
set comm=
1000
where empno=
7899 ;
ex: update emp
set mgr=
null
where empno=
7899 ;
ex:将7399，8899，7788的领导及奖金取消：
update emp
set mgr=
null, comm=
null
where empno IN(
7399,
8899,
7788 ) ;
删除数据：
delete from table ；//全部删除
delete from table
where 条件；
//局部删除
ex: delete from emp
where empno =
7899;
delete from emp
where emp IN(
7399,
8899,
7788 ) ;
where comm
is not
null ;
************************************************************************************
数据库的事务处理：
事务处理：保证数据的完整性，具有ACID特性
创建一张包含10部门的临时表：
create table emp10
as select *
from emp
where deptno=
10 ;
delete from emp10
where empno=
7772 ;
/**
在oracle中，每个连接到数据库上的用户都表示创建了一个session，一个session队数据库所做的修改不会立刻反映到数据库的真实数据之上，当session提交所有的操作之后，数据库才真正做出修改。
提交事务：commit
回滚事务：rollback
>>>>>>>>若事务已经提交了，则肯定无法回滚<<<<<<<<<<
*/
************************************************************************************
死锁：
一个session更新了数据库中的记录，其他session事无法立刻更新的，要等待对方提交后才允许更新
SQL语法练习：
1，列出至少有一个员工的所有部门：
a,列出所有部门的员工数量
select deptno ,count(empno)
from emp
group by deptno ;
b,列出部门人数大于1的所有部门编号
select deptno ,count(empno)
from emp
group by deptno having count(empno)>
1 ;
c , 通过部门表，查询出部门的信息即可：
select d.*
, ed.cou
from emp d, (
select deptno,count(empno) cou
from emp
group by deptno having count(empno) >
1
） ed
where d.deptno=
ed.deptno ;
2，列出新金比Smith多的所有员工：
a,先求出smith的工资：
select sal from emp where ename =
‘SMITH‘;
b,以上面的结果为条件，查询所有符合条件的雇员信息
select *
from emp
where sal >
(
select sal from emp where ename =
‘SMITH‘;
) ;
3,列出所有员工的姓名及其直接上级的姓名:
select e.ename, m.ename
from emp e, emp m
where e.mgr=m.empno(+
);
4,列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称：
a,查找mgr=
empno的同时还要比较hiredate，先查询编号，姓名
select e.empno, e.ename
from emp e, emp n
where e.mgr=m.empno and e.hiredate <
m.hiredate ;
b, 查询部门编号
select e.empno, e.ename ,d.dname
from emp e, emp n, dept d
where e.mgr=m.empno and e.hiredate <
m.hiredate
and e.deptno=
d.deptno;
5,列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门：
select d.deptno, d.dname, e.empno, e.ename
from dept d, emp e
where d.deptno=e.deptno(+
);
6,列出所有办事员的姓名及其部门名称，部门人数：
select e.ename, d.dname, ed.cou
from emp e, dept d, (
select deptno, count(empno) cou
from emp
group by deptno
) ed
where job=
‘CLERK‘ and e.deptno=d.deptno and ed.deptno=
e.deptno;
7，列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数：
select e.job, count(e.empno)
from emp e
where e.job
in(
select job
from emp
group by job having min(sal)>
1500
)
group by e.job ;
8，列出部门销售部工作的员工的姓名，假定不知道销售部的部门编号：
select ename
from emp
where deptno=
(
select deptno from dept
where dname=
‘SALES‘
) ;
9,列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级：
select e.empno, e.ename, s.grade, m.empno, m.ename, d.deptno, d.dname, d.loc
from emp e, dept d, emp m, salgrade s
where e.sal>
(
select avg(sal) from emp
)and e.deptno=
d.deptno
and e.mgr=m.empno(+
)
and e.sal between s.losal and s.hisal ;
10,列出与“SCOTT”从事相同工作的所有员工及部门名称：
select e.empno, e.ename, e.job, e.sal, d.dname, d.loc
from emp e, dept d
where job =(select job from emp
where ename=
‘SCOTT‘)
and ename !=
‘SCOTT‘
and e.deptno=
deptno ;
11，列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金：
select ename,sal
from emp
where sal IN(
select sal from emp where deptno=
30
)
and deptno !=
30 ;
12，列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称：
select e.ename, e.sal, d.dname, d.loc
from emp e, dept d
where e.sal >
ALL(
select sal from emp where deptno=
30
)
and e.deptno!=
30 and e.deptno =
d.deptno ;
13，列出每个部门工作的员工数量，平均工资和平均服务期限：
select d.dname, count(e.empno), avg(e.sal), avg(months_between(sysdate,e.hiredate)/
12) years
from emp e, dept d
where e.deptno=
d.deptno
group by d.dname ;
14,列出所有员工的姓名，部门名称和工资：
select e.ename, d.dname , e.sal
from emp e,dept d
where e.detpno=
d.deptno ;
15，列出所有部门的详细信息和部门人数：
select d.* ,nvl(ed.cou ,
0)
from dept d,(
select deptno dno,count(empno) cou from emp group by deptno
) ed
where d.deptno=ed.dno (+
);
16,列出各种工作的最低工资及从事此工作的雇员姓名：
select *
from emp
where sal IN (
select min(sal) from emp group by job
) ;
17，列出各个部门的manager的最低薪金：
select deptno ,min(sal)
from emp
where job=
‘MANAGER‘
group by deptno ;
18,列出所有员工的年工资，按年薪升序排序：
select ename, ( sal+NVL(comm,
0))*
12 income
from emp
order by income ;
19，查出某个员工的上级主管，并要求这些主管中的年薪不超过3000：
select distinct m.*
from emp e , emp m
where e.mgr=m.empno and m.sal >
3000 ;
20，求出部门名称中带“S“字符的部门员工的，工资合计，部门人数：
select deptno, sum(sal), count(empno)
from emp
where deptno
in(
select deptno from dept where dname like
‘%S%‘
)
group by deptno ;
21,给任职日期超过10年的人加薪10%
：
update emp
set sal=sal*
0.1
where months_between(sysdate,hiredate)/
12 >
10 ;
--------------------------------
commit ;
************************************************************************************
SUMMARY
1,多表查询，注意产生笛卡尔积
2，分组统计，所有统计函数只能在分组语句中使用
3，子查询：子查询可以在任意的位置上编写，多表查询，子查询，分组统计一起完成复杂查询
4，数据库的增删改
5，了解一下事务的处理方式，commit，rollback
Oracle学习系列4
标签：
