一、----------------------------DML----------------------------
---DML 数据操作语言
--DML 数组操作语言 insert into--update--delete
--创建表 简单的方式
--使用查询的结果集来创建一张表
create table temp as select * from emp
select * from temp
--删除表
drop table temp
select * from emp where 1<>1
--创建一张空表 只是复制了表的结构
create table temp as select * from emp where 1<>1
--插入数据
--只是将数据插入到内存中的表里了。并没有真正的写入到数据库中，需要提交数据
--只是将 数据插入到内存中，还没有真正的写到数据库磁盘上。需要做提交操作。
insert into temp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values('0001','张三丰','掌门',null,sysdate,5000,1000,10)
insert into temp (ename,empno,mgr,job,hiredate,sal,comm,deptno) values('张无忌','0002',null,'教主',sysdate,20000,500,10)
select * from temp
--提交
commit
--回滚
rollback
-- 不显示的声明要写入的字段,写入的数据的顺序必须和 表结构中的一致。
insert into temp values('0003','周芷若','师太',null,sysdate,2000,1000,10)
-- 只是对插入的数据指定某些值，必须指明要 插入的字段的值
insert into temp (empno,ename,job,mgr,hiredate,deptno) values('0004','小昭','剩女',null,sysdate,10)
--修改数据 update 必须要使用where 进行更新数据的条件过滤，如果对所有的数据更新，那么就写一个永远成立的条件即可
--将张无忌的mgr 设置为 张三丰
update temp set mgr='0001' where empno='0002'
select * from temp
--将所有的员工的工资都提升50%
update temp set sal=sal*1.5 where 1=1
--将所有奖金为null 的员工的奖金设置为0
update temp set comm=nvl(comm,0) where 1=1
--将没有领导的员工的领导设置为自己的编号
update temp set mgr=nvl(mgr,empno)
--删除 from 可以省略
delete from temp where ename='小昭'
select * from temp
--如果删除所有的数据，不加条件就ok
delete temp
--全部删除数据的时候可以使用 truncate 效率更高，不需要手动提交，自动提交
truncate table temp
--单表查询。
select * from emp
二、多表查询
--多表查询 sql92 标准
--查询所有员工的姓名，部门编号，部门名称
select * from emp
select * from dept
--笛卡尔集
select * from emp,dept
--如何消除笛卡尔集中的冗余的数据
--等值连接查询 根据两张表中同一个字段相等的值进行连接查询
select * from emp, dept where emp.deptno=dept.deptno
--查询所有员工的姓名，部门编号，部门名称
--如果显示的某些字段存在语义上的冲突（该字段存在于多张表中，需要指明是哪个表的哪个字段，使用 表名.字段名）
select ename, emp.deptno, dname
from emp, dept
where emp.deptno=dept.deptno
--可以给表起别名
-- 给表起别名 不能使用 as， 一旦给表起了别名，那么原来的表的名称就不能在使用了。
select ename,d.deptno, dname
from emp e, dept d
where e.deptno=d.deptno--两张表的连接的条件
--sql 92中，表的连接条件写在where子句中。
--非等值连接
--查询所有员工的姓名，职位，薪水，薪水等级 emp 和 salgrade 两个表的多表查询
--先看笛卡尔集
select * from salgrade
select * from emp , salgrade
--非等值连接
select *
from emp ,salgrade
where sal >=losal and sal <= hisal
select *
from emp e, salgrade s
where e.sal >=s.losal and e.sal <=s.hisal
--尽量将所有的显示的字段，都使用表名引用，表示该字段属于哪一张表，这样的效率更高。
select e.ename,e.job,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal
select e.ename , e.job , e.sal, s.grade
from emp e, salgrade s
where e.sal >=s.losal and e.sal <=s.hisal
--自连接
--查询 员工的编号，名字，员工的上级的编号，上级的名字
select * from emp
-- e1 员工表 e2 上级表
--笛卡尔集
select e1.empno,e1.ename,e1.mgr, e2.empno, e2.ename
from emp e1,emp e2
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno
--外连接
--左外连接
--查看不满足条件的记录
--查询 员工的编号，名字，员工的上级的编号，上级的名字，包含没有上级的员工
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno(+)--左外连接，将左边的表的所有的信息全部显示，包括不满足条件的。
--- 右外连接
--还显示了没有下级的员工的信息
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1,emp e2
where e1.mgr(+)=e2.empno--右外连接，将右边的表中的所有的数据全部显示，包括不满足条件的
--查询部门的所有信息，以及每个部门的平均工资，包含没有员工的部门
select * from dept
select d.*,avg(sal)
from emp e, dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc
--三表连接
--查询20号部门的员工的编号，姓名，薪水，部门名称，薪水等级。
select e.deptno,e.empno,e.ename,e.job,e.sal,d.dname,s.grade
from emp e,dept d,salgrade s
where d.deptno = 20 and e.deptno = d.deptno and e.sal between s.losal and s.hisal
三、----------------------------sql 92 sql 99----------------------------
--n张表，至少需要几个连接条件 n-1个连接条件
--sql 92中将 ，行过滤条件，和表连接条件，都写在了where 子句中。
--sql 99 和 92 的区别
--在sql92中，多表的连接条件和 行过滤条件都被写在了 where 子句中。
--在 sql99 中，where 子句只进行行过滤条件的表示，所有的多表之间的连接都通过其他的方式表示。
--交叉连接 cross join
-- 和 sql 92 中的笛卡尔集是一样的
--sql 92
select * from emp,dept
--sql99
select *
from emp e cross join dept d
--查询所有员工的姓名，部门编号，部门名称
--sql 92
select e.ename,e.deptno,d.dname
from emp e, dept d
where e.deptno=d.deptno
--自然连接 natural join 等值连接 进行连接的表之间 必须存在相同的字段。自动的进行 多表间的相同的字段的所有的字段的等值的连接
--sql99 如果需要显示 等值连接的字段的内容，那么该字段不能指定是哪个表中的字段，直接使用该字段名即可。
--优点：不需要指定连接的条件，自动指定使用多表间的相同的字段进行等值连接。
--缺点：如果多表之间存在多个相同的字段，那么所有的字段都会进行等值连接。缺乏灵活性。
select e.ename,deptno,d.dname
from emp e natural join dept d
--Using 子句 也是等值连接，使用Using 指明进行多表连接的时候，使用多表之间的哪个字段进行等值连接，
--使用Using 进行连接的，不能指明连接的字段属于哪个表
--查询所有员工的姓名，部门编号，部门名称
--inner 可以省略，内连接
--sql99
select e.ename,deptno,d.dname
from emp e join dept d
Using(deptno)
--on 子句连接 既可以是等值连接，也可以是非等值连接
--查询30部分的员工的编号，姓名，部门名称
--sql92
select d.deptno, e.empno,e.ename,d.dname
from emp e, dept d
where d.deptno=30 and e.deptno=d.deptno
--sql 99
select d.deptno,e.empno,e.ename,d.dname
from emp e join dept d--这两个表做连接
on e.deptno=d.deptno--指明两个表的连接的条件
where d.deptno=30
--查询10部门员工的姓名，薪水等级---不等值连接
--sql 92
select e.deptno,e.ename,s.grade
from emp e, salgrade s
where e.deptno=10 and e.sal between s.losal and s.hisal
--sql 99
select e.deptno,e.ename,s.grade
from emp e join salgrade s
on e.sal >=s.losal and e.sal <= s.hisal
where e.deptno=10
--外连接
--左外连接 left [outer] join 左边的表的不满连接条件的信息全部显示
--右外连接 right [outer] join 右边的表的不满连接条件的信息全部显示
--全外连接 full [outer] join 左边和右边的表的不满连接条件的信息全部显示
--sql 99 左外 包含没有上级的员工的信息
--sql 92
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno(+)
--sql 99
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1 left outer join emp e2
on e1.mgr=e2.empno
--sql 99 右外 包含没有下级的员工的信息
--sql 92
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1,emp e2
where e1.mgr(+)=e2.empno
--sql 99
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1 right join emp e2
on e1.mgr=e2.empno
--sql 99 全外 包含没有上下级的员工的信息
select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1 full join emp e2
on e1.mgr=e2.empno
--sql 99 查询部门的所有信息，以及每个部门的平均工资，包含没有员工的部门
--sql 92
select d.*,avg(sal)
from emp e ,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc
--sql 99
select d.* ,avg(sal)
from emp e right join dept d
on e.deptno=d.deptno
group by d.deptno,d.dname,d.loc
--查询20号部门的员工的编号、姓名、薪水、部门名称，薪水等级
--sql 92
select d.deptno, e.empno,e.ename,e.sal,d.dname,s.grade
from emp e, dept d, salgrade s
where d.deptno=20 and e.deptno=d.deptno and e.sal between s.losal and s.hisal
--sql 99 natural join + on ---自然连接的连接的条件的字段，不能指定表名
select deptno,e.empno,e.ename,e.sal,d.dname,s.grade
from
emp e natural join dept d
join salgrade s
on e.sal between s.losal and s.hisal
where deptno=20
--sql 99 Using + on
select deptno,e.empno,e.ename,e.sal,d.dname,s.grade
from
emp e join dept d
Using(deptno)
join salgrade s
on e.sal between s.losal and s.hisal
where deptno=20
--sql 99 on
select d.deptno,e.empno,e.ename,e.sal,d.dname,s.grade
from
emp e join dept d
on e.deptno=d.deptno
join salgrade s
on e.sal between s.losal and s.hisal
where d.deptno=20
四、子查询
---单行子查询
--只会得到一个结果的子查询
--查询所有比 CLARK 员工 工资高的员工
--先查询 CLARK 员工的工资
select sal from emp where ename='CLARK'--2450
select * from emp where sal > 2450 order by sal
--合二为一 子查询
--子查询的内容必须放在小括号中。在查询语句中的查询语句
select * from emp where sal > (select sal from emp where ename='CLARK') order by sal
--查询工资高于平均工资的员工的名字和工资
--先查询平均工资
select avg(sal) from emp
---
select ename, sal from emp where sal > (select avg(sal) from emp)
--查询和scott同一个部门的，但是比scott入职时间早的员工的信息
--先查询 scott 的部门编号
select deptno from emp where ename='SCOTT'--20
--查询scott 的入职时间
select hiredate from emp where ename='SCOTT'--87,4,19
--
select * from emp where deptno=(select deptno from emp where ename='SCOTT') and hiredate<(select hiredatefrom emp where ename='SCOTT')
--查询 和 scott 同一个部门，但是比scott工资低的员工的信息
--先查询 scott 的部门编号
select deptno from emp where ename='SCOTT'--20
--查询scott 的工资
select sal from emp where ename='SCOTT'--3000
--
select * from emp where deptno=(select deptno from emp where ename='SCOTT') and sal < (select sal from empwhere ename='SCOTT')
--查询 比scott 工资高或者入职时间比scott早的员工的编号 和姓名
--查询scott 的工资
select sal from emp where ename='SCOTT'--3000
--查询scott 的入职时间
select hiredate from emp where ename='SCOTT'--87,4,19
--
select empno, ename from emp
where sal > (select sal from emp where ename='SCOTT') or hiredate< (select hiredate from empwhere ename='SCOTT')
--多行子查询 ： 子查询的结果会有多个
--all ：和所有的查询的数据比较
--any ：和子查询的结果中的任意一个比较
--in ： 等于结果中的某一个
--查询工资低于任意一个 ‘CLERK’的工资的员工信息
--先查询 所有的‘CLERK’ 的员工的工资
select sal from emp where job='CLERK'
--
select * from emp where sal < any(select sal from emp where job='CLERK')
--查询工资 比所有的'SALESMAN'都高的员工的编号，姓名，工资
--先查询所有的'SALESMAN' 工资
select sal from emp where job='SALESMAN';
select empno,ename,sal from emp
where sal > all(select sal from emp where job='SALESMAN')
--查询20号部门的中职务和10号部门职务相同的员工的信息
--先查询10号部门的员工有那些职务
select distinct job from emp where deptno=10
select * from emp
--20 部门的人的职务在上面的集合中就可以
select * from emp
where deptno=20 and job in(select distinct job from emp where deptno=10)
