Oracle学习——第四讲（增删改查）

Oracle 通用表

/*

emp 表                             dept  部门表                 salgrade 表

empno 员工编号               deptno  部门编号          grade  工资等级

ename  员工姓名             dname  部门名称          losal    工资范围下限

job       职位                      loc 工作地点                 hisal   工资范围上限

mgr     经理编号

hiredate  入职日期

sal        薪金

comm  奖金

deptno  部门编号

*/

Oracle实例演练

--组函数嵌套MAX  MIN  SUM AVG  COUNT

--与单行函数不同，组函数只能嵌套两层

SELECT MAX(AVG(sal))

FROM emp

GROUP BY deptno

--显示经理号码和经理姓名，这个经理所管理员工的最低

--工资，没有经理的KING也要显示，不包括最低工资小于

--3000的，按最低工资由高到低排序。

SELECT e.mgr, m.ename, MIN(e.sal)

FROM emp e, emp m

WHERE e.mgr = m.empno(+)

GROUP BY  e.mgr, m.ename

having MIN(e.sal) >= 3000

ORDER BY MIN(e.sal) DESC

--查询工资比Jones工资高的员工信息？

--括号里的子查询先执行

SELECT *

FROM emp

WHERE sal >  (SELECT sal FROM emp WHERE ename = 'JONES')

--或者

SELECT e.ename, e.sal

FROM emp e, emp j

WHERE j.ename = 'JONES' AND e.sal > j.sal

--查询工资最低的员工姓名

SELECT ename

FROM emp

WHERE sal = (SELECT MIN(sal) FROM emp)

--子查询可以嵌套在

--WHERE子句，

--HAViNG子句，

--FROM子句中

--查询部门平均工资高于20部门平均工资的部门信息

SELECT deptno, AVG(sal)

FROM emp

GROUP BY deptno

Having AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno = 20)

--查询哪个部门的员工人数高于各部门平均人数。

SELECT deptno, COUNT(empno)

FROM emp

GROUP BY deptno

HAVING COUNT(empno) >(SELECT AVG(COUNT(empno)) FROM emp GROUP by deptno)

--查询部门人数大于所有部门平均人数的的部门

--编号，部门名称，部门人数

SELECT d.deptno, dname, COUNT(empno)

FROM emp e, dept d

WHERE e.deptno = d.deptno

GROUP BY d.deptno, dname

HAVING COUNT(empno) > (SELECT AVG(COUNT(empno)) FROM emp GROUP BY deptno)

--多行子查询

--IN

--ANY

--ALL

--查询是经理的员工姓名

SELECT *

FROM emp

WHERE empno IN(SELECT mgr FROM emp)

--查询不是经理的员工姓名

--空值不能用于IN语句中

SELECT *

FROM emp

WHERE empno NOT IN(SELECT mgr FROM emp)

SELECT *

FROM emp

WHERE empno NOT IN(SELECT DISTINCT mgr FROM emp)

SELECT *

FROM emp

WHERE empno NOT IN(7839, NULL, 7782, 7689, 7902)

SELECT *

FROM emp

WHERE empno <>7839 AND empno <> NULL....

--正确，去空值

SELECT *

FROM emp

WHERE empno NOT IN(SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL)

--ANY：表示和子查询的任意一行结果进行比较，有一个满足条件即可。

--< ANY：表示小于子查询结果集中的任意一个，即小于最

                   -- 大值就可以。

--• > ANY：表示大于子查询结果集中的任意一个，即大于最

                  --小值就可以。

--• = ANY：表示等于子查询结果中的任意一个，即等于谁都

                  --可以，相当于IN。

                 

--工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资。

SELECT ename, empno, job, sal

FROM emp

WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 10)

--ALL:表示和子查询的所有行结果进行比较，每一行必须都

          --满足条件。

--• < ALL:表示小于子查询结果集中的所有行，即小于最小值。

--• > ALL:表示大于子查询结果集中的所有行，即大于最大值。

--• = ALL :表示等于子查询结果集中的所有行,即等于所有值，通常无意义。

--工资比10部门所有员工工资高的员工编号，姓名，职位，工资。

SELECT empno, ename,job, sal

FROM emp

WHERE sal > ALL (SELECT sal FROM emp WHERE deptno= 10)

--多列子查询（通常用IN完成）

--查询出和1981年入职的任意一个员工的部门和

--职位完全相同员工姓名、部门、职位、入职日

--期,不包括1981年入职员工。

--（deptno, job）--多列子查询

SELECT ename, deptno, job, hiredate

FROM emp

WHERE (deptno, job) IN(SELECT deptno, job FROM emp WHERE to_char(hiredate,'YYYY') = '1981')

AND to_char(hiredate, 'YYYY') <>'1981'

--区分下面(错误XXX)

SELECT ename, deptno, job, hiredate

FROM emp

WHERE deptno IN(SELECT deptno FROM emp WHERE to_char(hiredate,'YYYY') = '1981')

AND job IN(SELECT job FROM emp WHERE to_char(hiredate,'YYYY') = '1981')

AND to_char(hiredate, 'YYYY') <>'1981'

--查询出和1981年入职的任意一个员工的部门或

--职位相同员工姓名、部门、职位、入职日期,不

--包括1981年入职员工

SELECT ename deptno job hiredate

FROM emp

 WHERE (deptno IN (SELECT deptno

                                   FROM    emp

                                   WHERE  to_char(hiredate,'YYYY')='1981')

   OR job IN                   (SELECT job

                                         FROM emp

                                           WHERE to_char(hiredate,'YYYY')='1981'))

 AND to_char(hiredate,'YYYY')<>'1981');

 --FROM子句中使用子查询

 -- 查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资

 SELECT  ename, sal, e.deptno, avgsal

 FROM emp e, (SELECT deptno, AVG(sal) avgsal

                          FROM emp

                          GROUP by deptno) f

  WHERE e.deptno = f.deptno

  AND sal > avgsal

  --数据操作与事务控制（增，删，改）

  --向表中插入一条数据

  INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)

  VALUES (9521, '华1', '1童', 7781, '01-5月-1981',1901, 5, 20)

  --列的顺序要求与插入的列的数据顺序一致

  INSERT INTO emp

  VALUES (9521, '华1', '1童', 7781, '01-5月-1981',1901, 5, 20)

  --一次性插入多行数据

  --创建一个和emp相同的表emp1

  CREATE TABLE emp1

  AS

  SELECT * FROM emp

 --创建一个空表

    CREATE TABLE emp2

  AS

  SELECT * FROM emp where 1 = 0

 --向表2中完全插入表1

 INSERT INTO emp2

 SELECT * FROM emp where deptno = 10

 --只插入两列

 INSERT INTO  emp2(empno, ename)

 SELECT empno, ename FROM emp where deptno = 20

 --修改数据

 --update

 --SET：进行修改

 --WHERE:条件

 UPDATE emp2

 SET deptno = 20

 where deptno is NULL

--修改工资

  UPDATE emp2

 SET sal = 500

 where deptno= 20

 --工资涨500

 UPDATE emp2

 SET sal = sal + 500

 --将部门编号等于20的工资全部清空

 UPDATE emp2

 SET sal = NULL

 UPdate emp2

 set sal = sal +(SELECT AVG(sal) FROM emp2)

 WHERE deptno = 20

 SELECT * fROM emp2

 --删除数据

 delete FROM emp2

 WHERE deptno = 20

 --删除工作为clerk

 delete FROM emp2

 WHERE job = 'CLERK'

 ---事务处理语言（隐式提交）

 --commit：提交（增，删，改：全部生效，即保存）

 --ROLLBACK: 撤销 增，删，改操作

 --一旦提交commit后，rollback不能生效

 --创建表

 --create table

 CREATE table student(

 学号 NUMBER(10),

 姓名 VARCHAR2(20),                  --不超过10个汉字

 性别 CHAR(2) DEFAULT '男',          --默认值：男

 身份证号 char(18),

 出生日期 date

 )

--向表中插入数据

INSERT INTO student(学号, 姓名) VALUES(9527, '华安')

--利用子查询的方式创建表

CREATE TABLE emp3

AS

SELECT empno, ename FROM emp

CREATE TABLE emp4

AS

SELECT * FROM emp

--创建一个空表

CREATE TABLE emp5

AS

SELECT * FROM emp WHERE 1 = 0

--建两列数据

CREATE TABLE emp6

AS

SELECT ename  姓名, empno 编号 FROM emp

--等同于

Create TABLE emp7(姓名,编号)

AS

SELECT ename  姓名, empno 编号 FROM emp

CREATE TABLE emp9(编号, 平均工资)

AS

SELECT deptno, AVG(sal) FROM emp GROUP BY deptno

SELECT *

FROM emp4

--修改表

--ALTER TABLE

--ADD添加数据

ALTER TABLE emp4 ADD(学号 NUMBER(10), 姓名 VARCHAR2(20))

--modify修改数据

--(修改学号长度)

ALTER TABLE emp4 MODIFY(学号 NUMBER(11))

--(修改学号默认值)

ALTER TABLE emp4 MODIFY(学号 DEFAULT 1234567)

--插入数据(提交)

INSERT INTO emp4(empno) VALUES(9988)

--删除数据

ALTER TABLE emp4 DROP(学号,姓名)

--删除表

DROP TABLE emp4