Oracle数据库基础（二）

今天的内容主要是增删改查中的增加，删除，修改，以及多表查询，由于增删改实现比较简单，在此只附代码。

增、删、改（insert、delete、update）

insert，往表中插入数据，整行插入
    语法格式：
            insert into 表名[(列名)] values(列值)
update，修改数据
    语法格式：
            update 表名 set 要修改的列名=值 [可以有多组修改值] [where ...]
delete，删除
    语法格式：
            delete [from] 表名[where ...]
    还有一个drop + 表名 可以删除整个表，无法回滚
使用方法见代码：

--insert 插入
--向员工表中插入一条数据
insert into emp values(7894,'张九歌','老总',1000,to_date('1980/12/01','yyyy/mm/dd'),6000,500,10);
--只插入部分数据
insert into emp(empno,ename) values(8001,'张八止');

--创建一个临时表
create table temp_emp 
as 
select * from emp where 1=2;
--查询结果插入到temp
insert into temp_emp select * from emp;

--update  更新，或者叫修改
--把名叫张三丰的姓名改成张四风
update temp_emp set ename = '张四风' where ename = '张九歌';
-- 把员工编号为8001的员工的空的信息补全
update temp_emp set job = '总经理' ,mgr = 7900 ,hiredate = '1-1月-1987', sal = 8000, comm = 1000, deptno = 10 where empno = 8001;
-- 把部门为20的所有员工工资加500
update temp_emp set sal = sal+500 where deptno = 20;


--delete   删除
-- 删除临时表中员工编号为9528的员工
delete from temp_emp where empno = 7777;
-- 删除临时表中的所有信息
delete temp_emp;
-- 删除表（包含数据）
drop table temp;

SQL92多表连接查询

语法格式：
    select table1.column,table2.column
    from table1,table2
    where...
SQL92多表连接查询是比较古老的多表联查方式，所以这里仅作了解。
特点：
    在where 子句中写入连接条件，多个表中有重名列，需要在列名前加上表名作为前缀。
它的连接类型有等值，非等值，和外连接，外连接又分为左外联、右外联和自连接。
需要注意的是，如果没有where子句，那么结果就会出现笛卡尔集的形式，所以应保证所有连接都有where子句。
下面附上几种类型的连接实现代码：

--sql92多表连接查询
--查询员工表的员工姓名， 员工职位，员工工资，所在部门编号及名称
select emp.ename, emp.job, emp.sal, dept.deptno, dept.dname
  from emp, dept
 where emp.deptno = dept.deptno;
--查询员工所有信息和部门名称
select emp.*, dept.dname from emp, dept where emp.deptno = dept.deptno;
--带条件查询，查询工资大于1500的员工信息和部门名称
select e.*, d.dname
  from emp e, dept d
 where e.deptno = d.deptno
   and e.sal > 1500;

--非等值查询，员工姓名，工资等级
select e.ename, s.grade
  from emp e, salgrade s
 where e.sal >= s.losal
   and e.sal <= s.hisal;

--外联
--左外联
select emp.ename, emp.job, emp.sal, dept.deptno, dept.dname
  from emp, dept
 where emp.deptno = dept.deptno(+);
--右外联
select emp.ename, emp.job, emp.sal, dept.deptno, dept.dname
  from emp, dept
 where emp.deptno(+) = dept.deptno;

--自连
--显示职员和其经理的信息
select * from emp e1, emp e2 where e1.mgr = e2.empno;
--显示职员和其经理的名字
select e1.ename 职员, e2.ename 上司
  from emp e1, emp e2
 where e1.mgr = e2.empno;

SQL99多表连接查询

通用语法格式：

    select 字段列表
    from table1
    [cross join table2]/               1.交叉连接
    [natrural join table2]/            2.自然连接
    [join table2 using(字段名)]/        3.using字段
    [join table2 on (table1.column_name = table2.column_name)]/                                                               
                                       4.on子句                                                             
    [(left/right/full)join table2 on (table1.column_name = table2.column_name)]/                  5.左/右/全连接

种类：

内联，inner join，返回两个都一一对应的列表

--inner 
select e1.ename, e1.sal, e1.deptno, d.dname
  from emp e1
 inner join dept d
    on e1.deptno = d.deptno;

外联，左右全连接

--left 左外联
select e1.ename, e1.sal, e1.deptno, d.dname
  from emp e1
  left join dept d
    on e1.deptno = d.deptno;
--右外联
select e1.ename, e1.sal, e1.deptno, d.dname
  from emp e1
 right join dept d
    on e1.deptno = d.deptno;
--全外联
select e1.ename, e1.sal, e1.deptno, d.dname
  from emp e1
  full join dept d
    on e1.deptno = d.deptno;
delete from emp where emp.ename = '张八止';
--查询员工姓名和部门名称和其经历的名称和经理的部门名称
select e1.ename, d1.dname, e2.ename，d2.dname
  from emp e1
  left join dept d1
    on e1.deptno = d1.deptno
  left join emp e2
    on e1.mgr = e2.empno
  left join dept d2
    on e2.deptno = d2.deptno;

交叉连接，cross join 相当于笛卡尔集

--交叉连接
select * from emp cross join dept;

自然连接，natural join ，系统自己寻找对应的列进行连接

--自然连接
select * from emp natural join dept;

还有前边提到的using、on

--using 子句
select * from emp join dept using (deptno);
--on 子句
select * from emp e1 join dept d on e1.deptno = d.deptno;
--查询员工姓名部门名称和部门编号
select e.ename, d.dname, d.deptno
  from emp e
  join dept d
    on e.deptno = d.deptno;
--使用on实现自连
select *
  from emp e1
  join emp e2
    on e1.mgr = e2.empno
  join emp e3
    on e2.mgr = e3.empno;

简单的子查询

一个查询的结果作为另一个查询的条件称为子查询。

语法格式：
select 字段列表 from table 
where operator(select........)

特点：
子查询在主查询之前执行一次
主查询使用子查询的结果。

注意：
子查询是未知值是应考虑使用子查询；
子查询必须在小括号内；
建议把子查询放在比较条件的右边

--子查询
--查询工资高于平均工资的雇员名字和工资。
select ename, sal from emp where sal > (select avg(sal) from emp);
--查询和SCOTT同一部门且比他工资低的雇员名字和工资。
select ename, sal
  from emp
 where deptno = (select deptno from emp where ename = 'SCOTT')
   and sal < (select sal from emp where ename = 'SCOTT');

多行子查询

有三个运算符记一下，all，any和in，使用方式见代码

-- 多行子查询 查询和scott,smith,clark在同一个部门的员工信息并不包含s,s,c;
select *
  from emp
 where deptno in
       (select deptno from emp where ename in ('SCOTT', 'SMITH', 'CLARK'))
   and ename not in ('SCOTT', 'SMITH', 'CLARK');
--查询工资最高的雇员名字和工资。
select ename, sal from emp where sal = (select max(sal) from emp);
--查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息
select *
  from emp
 where job = (select job from emp where ename = 'SCOTT')
   and hiredate < (select hiredate from emp where ename = 'SCOTT');
--查询工资比SCOTT高或者雇佣时间比SCOTT早的雇员的编号和名字
select empno, ename
  from emp
 where job = (select job from emp where ename = 'SCOTT')
    or hiredate < (select hiredate from emp where ename = 'SCOTT');

下面是一些思考题及答案

--查询工资低于任意一个“CLERK”的工资的雇员信息。
select *
  from emp
 where sal < any (select sal from emp where job = 'CLERK');
--查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
select empno, ename, sal
  from emp
 where sal > all (select sal from emp where job = 'SALESMAN');
--查询部门20中职务同部门10的雇员一样的雇员信息。
select *
  from emp
 where deptno = 20
   and job in (select job from emp where deptno = 10);
--查询在雇员中有哪些人是经理人
select ename
  from emp
 where empno = any (select distinct mgr
          from emp
         where mgr is not null
            or mgr != '');
--找出部门编号为20的所有员工中收入最高的职员
select ename
  from emp
 where deptno = 20
   and sal = (select max(sal) from emp where deptno = 20);
--查询每个部门平均薪水的等级
--1  每个部门的平均薪水
select a.deptno, grade
  from salgrade, (select deptno, avg(sal) b from emp group by deptno) a
 where a.b >= losal
   and a.b < hisal;