YS_20190812_Oracle_01

多表查询

如何避免笛卡尔积？

1. 给数据库表起别名 如*emp e, dept d;*效率挺高

select e.ename,e.job,e.sal,d.deptno,d.dname from emp e,dept d where e.empno = d.deptno;

2. 左右连接

select * from dept;
select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno(+)=d.deptno;--左连接
select * from dept;
select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno(+);--右连接
-- 备注 ：+在左边表示的是左连接，查询的时候以右边的表为查询条件！
          -- +在右表示的是左连接，查询的时候以左边的表为查询条件！

cross…join

交叉连接，等价于 select * from emp,dept

select * from emp cross join dept;

natural…join

select * from emp natural join dept;

USING

表示的是哪个字段产生了笛卡尔积

select * from emp e JOIN dept d USING (deptno) where deptno=30;
select * from emp e JOIN dept d USING (deptno) where deptno=40;
select * from emp e JOIN dept d USING (deptno) where deptno=10;

join…on

select * from emp e join dept d on (e.deptno = d.deptno) where e.deptno=30;

组函数

1. COUNT() 求全部记录数
2. MAX()
3. AVG()
4. SUM()

分组函数

Group…by

--通过deptno在emp中分组查找deptno并统计总数
select deptno,COUNT(empno) from emp  GROUP BY deptno;

-- 按部门分组，并显示部门名称，以及部门员工数
select d.dname,COUNT(e.empno) from dept d,emp ewhere d.deptno=e.deptno GROUP BY d.dname;

--要求查出平均工资大于2000的部门编号和平均工资
select deptno,AVG(sal) from emp WHERE AVG(sal) >2000 GROUP BY deptno;

这是因为where只能对单行而不是行组进行过滤，要过滤行组要使用having。

HAVING

select deptno,AVG(sal) from emp HAVING AVG(sal) >2000 GROUP BY deptno;

--按工作分组同时求出工资的总和
select job,SUM(sal) from emp WHERE job<>’SALESMAN’ GROUP BY job; 
--对分组条件进行限制
select job,SUM(sal) from emp WHERE job<>’SALESMAN’ GROUP BY job HAVING SUM(sal)>5000; 

注意： 分组函数可以嵌套使用，但是在组函数嵌套的时候不能再出现分组条件的查询语句

子查询

--要求查询出比7654工资高的全部雇员信息
select * from emp where sal>1250;
select * from emp where sal>(select sal from emp where empno=7654)--子查询

--要求查询出比7654工资高，同时与7788从事相同工作的全部雇员信息
select * from emp where sal>(select sal from emp where empno=7654) 
and job =(select job from emp where empno=7788 )

数据库更新操作——CRUD

CREATE

create table myemp AS select * from emp;
--复制一份新表，数据备份

INSERT

select * from myemp;
Insert into myemp values(7899,'张三','清洁工',7930,to_date('1973-03-21','yyyy-mm-dd'),2000,0, 40);

UPDATE

UPDATE 表名称 set 要修改的字段=新值，要修改的字段=新值….;
UPDATE 表名称 set 要修改的字段=新值，要修改的字段=新值…WHERE 修改条件.;

update myemp set ename ='刘德华1' where empno=7899;

DELETE

删除全部: DELETE FROM 表名称
局部删除: DELETE FROM 表名称 WHERE 删除条件;

--CREATE TABLE EMP10 AS SELECT * FROM EMP WHERE DEPTNO=10;
select * from emp10;

--delete from emp10 where sal= 2450;

select * from  emp10;

事务操作

打开一个oracle终端，进行删除操作
DELETE FROM EMP10 WHERE SAL=2450;显示已经删除
然后再打开另外一个oracle终端，查询到sal=2450这条数据还在，证明这条数据并没有被删除，这就是oracle事务的概念。
事务处理: 就是保证数据操作的完整性，所有的操作要么同时成功要么同时失败。

我在plsql中查找emp10，发现emp10已经被删除了
为什么？

在实际工作中不能出现数据的假象，表面进行了CRUD操作，但数据根本就不存在。
commit和rollback
建议在每次数据的CRUD之后，都进行commit和rollback操作。可以保证数据的原子性，离子性。
commit：提交，要操作的的数据其实在栈顶准备，被操作，需要手动commit，
rollback：回退，数据按照原数据原样返回，且原路返回。（堆、栈不能改变）。

commit是什么，提交真实的数据？还是视图view，虚拟的数据？