Oracle11g学习笔记二

*注：此笔记为个人在学习Oracle时从教学视频、参考书上摘录整理而成，纯手打完成，如需转载麻烦表明出处，附上连接（http://blog.csdn.net/sherkyoung/article/details/25988225），谢谢！

DML（数据管理语言）

一、多表查询

1、基本概念

再之前使用的查询操作之中，都是从一张表中进行搜索，那么如果有一个查询语句需

显示多张表的数据，则就必须应用到多表查询操作，而多表查询操作的语法如下：

SELECT [DISTNCT] *|字段 [别名] [，字段 [别名]]

FROM 表名称[别名][,表名称[别名]，……]

[WHERE 条件（s）] 

[ORDER BY字段[ASC | DESC]][,字段[ASC | DESC]];

这与之前的操作相差不多，仅是在指定查询的表的范围的时候增加了需要一起查询的表名称或者别名。

但是如果进行多表查询之前，必须先查询出几个数据——雇员表和部门表中的数据量，这个操作可以通过COUNT()函数来完成。这个函数会返回该表所含的记录数量。

SELECT COUNT(*) FROM emp ;

SELECT COUNT(*) FROM dept ;

 

2、笛卡尔基

下面完成一个基本的多表查询：

SELECT * FROM emp,dept ;

执行完这个语句之后回显56行记录，但是emp表只有14条记录而dept表只有4条记录。实际上控制台回显的是14*4条记录，如图：

 

上述的问题则是数据库中的笛卡尔积即多张数据表的沉积的意思。

最简单的解决办法就是关联字段的形式，而emp表和dept表之间存在关联字段，可以从这个字段的判断开始。

 

此时，需要注意的是：再多表查询时，不同的表含有相同的字段名称的时候访问这些字段必须加上表名称即变成：表.字段。

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno ;

此时的查询结果回显中已经不再会有笛卡尔积，但是这只是在显示的时候进行过滤，所以在数据量比较大的时候这种方法奇差无比。 

同时，在开发中常使用别名来替代表名称：

SELECT * FROM emp e,dept d

WHERE e.deptno=ddeptno ;

这样在表名称比较长的时候可以非常方便的进行操作。

 

3、基本方法(步骤)

范例：查询出以为雇员的编号、姓名、职位、部门名称、位置。

a) 确定所需要的数据仪表

|-emp表：可以查询出雇员的编号、姓名、职位

|-dept表：可以查询除部门的名称和位置

b) 确定数据表的关联字段：emp.deptno=dept.deptno ;

SELECT e.empno,e.ename,e.job,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno=d.deptno ;

 

范例：要求查询出每一位雇员的姓名、职位、领导的姓名

A）确定所需要的表：

|-emp表（雇员）：取得雇员的姓名、职位、领导编号；

|-emp表（领导）：取得雇员的姓名（领导的姓名）；

B）确定关联字段：emp.mgr=memp.empno (雇员的领导编号=领导（雇员的）的雇员编号)

第一步：查询每一位雇员的姓名和职位

SELECT e.ename,e.job

FROM emp e ;

第二步：查询领导信息，加入自身关联

SELECT e.ename,e.job,m.ename

FROM emp e,emp m

WHERE e.mgr=m.empno

 

范例：查询出每个雇员的编号、姓名、基本工资、职位、领导的姓名部门的名称及位置

SELECT e.empno,e.ename,e.sal,e.job,m.ename,d.dname LEADERNAME,d.loc LOCALTION

FROM emp e,emp m,dept d

WHERE d.deptno=e.deptno AND e.mgr=m.empno ;

 

 

范例：求出每一个雇员的编号、姓名、工资、部门名称、工资等级

SELECT e.empno,e.ename,e.sal,d.dname,s.grade

FROM emp e,dept d,salgrade s

WHERE (e.deptno=d.deptno) AND (e.sal BETWEEN s.losal AND s.hisal) ;

4、左右连接

这是Oracle数据库独有的指的是查询判断条件的参考方向

a) (+)=：表示右链接

b) =(+)：表示左连接

不用可以区分是左还是右，只需要根据查询结果而定，如果发现有些需要的数据没有显示出来就使用此符号改变连接方向。

 

 

二、SQL：1999语法

除了上述的表连接操作之外，在SQL语法之中，也提供了一套用于表连接的操作SQL，格式如下：

SELECT table1.column,table2.column FROM table1 [CROSS JOIN table2] | [NATURAL JOIN table2] | [JOIN table2 USING(column name)] | [JOIN table2 ON(table1.column_name=table2.column_name)] | [LEFT|RIGHT|FULL.OUTER JOIN table2 ON(table.column_name=table2.column_name)] ;

以上实际上属于多个语法的联合，下面分块说明语法的使用。

 

1、交叉连接（CROSS JOIN）：用于产生笛卡尔积

SELECT * FROM emp CROSS JOIN dept ;

笛卡尔积本身并不是属于无用的内容，在某些情况下还是需要应用的。

 

2、自然连接（NATURAL JOIN）：自动找到匹配的关联字段，消除笛卡尔积

SELECT * FROM emp NATURAL JOIN dept ;

但是并不是所有的字段都是关联字段，设置关字段需要通过约定指定；

 

3、JOIN...USING子句：用户指定一个消除笛卡尔积的关联字段

SELECT * FROM emp JOIN dept USING(deptno) ;

 

4、JOIN...ON子句：用户指定一个可以消除笛卡尔积的关联条件

SELECT * FROM emp JOIN dept ON(emp.deptno=dept.deptno) ;

 

5、连接方向的改变：

|-左（外）连接：LEFT OUTER JOIN...ON ;

|-右（外）连接：ROGHT OUTER JOIN...ON ;

|-全（外）连接：FULL OUTER JOIN...ON ;   —>把两张表中没有的数据都显示

SELECT * FROM emp RIGHT OUTER JOIN dept ON(emp.deptno=dept.deptno) ;

*在Oracle之外的数据库，都是用以上的SQL：1999语法。

 

三、统计函数及分组查询（重点，难点）

1、统计函数

之前学过的一个COUNT()函数，实际上就是一个统计函数，常用的统计函数有：

|-COUNT()：查询表中的数据记录；

|-AVG()：求出平均值；

|-SUM()：求和；

|-MAX()：求出最大值 ；

|-MIN()：求出最小值；

范例：测试COUNT()、AVG()、SUM()，统计出公司所有的雇员，每个月支付的工资和平均工资：

SELECT COUNT(*) 公司人数,SUM(sal) 每月支付工资总数,AVG(sal) 平均工资数 ;

注意：COUNT()函数主要功能是进行数据的统计，但是一张表内如果没有记录，COUNT()函数依然还会返回数据，只是返回的数据是0.

SELECT COUNT (enname) FROM BONUS ;

如果使用其他的函数，则有可能返回null，但COUNT()肯定会返回一个具体的数值。

 

2、分组统计

当数据有重复的时候，分组才有意义。而如果想要分组，则需要使用GROUP BY子句来完成，此时的SQL语法如下：

SELECT [DISTNCT] *|分组字段1 [别名] [，分组字段2 [别名],...] | 统计函数

FROM 表名称[别名][,表名称[别名]，……]

[WHERE 条件（s）] 

[GROUP BY 分组字段1[,分组字段2,...]]

[ORDER BY字段[ASC | DESC]][,字段[ASC | DESC]];

范例：按照部门分组，求出每个部门的人数，平均工资

SELECT deptno,COUNT(deptno),AVG(sal)

FROM emp

GROUP BY deptno ;

范例：按照只为分组求出每个职位的最高和最低工资。

SELECT job,COUNT(job),MIN(sal),MAX(sal)

FROM emp

GROUP BY job ;

注意：一旦分组之后对语法产生了新的限制，对于分组也有以下要求：

|-分组函数可以在没有分组的时候单独使用，可是不能出现其他的查询字段；

|-如果现在要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他字段不能出现

|-分组函数允许嵌套，但是嵌套之后的分组函数的查询之中，不能再出现任何其他字段。

范例：查询出每个部门的名称、部门的人数、平均工资。

l 确定所需要的数据表：

|-dept表：每个部门的名称、位置；

|-emp表：统计出部门的人数、平均工资 ；

l 确定已知的关联字段：emp.deptno=dept.deptno

l 将两张表关联起来

SELECT d.dname,e.empno,e.sal

FROM dept d,emp e

WHERE d.deptno=e.deptno (+) ;

 

此时的查询结中也是存在这数据的重复，以往都是在实体表中直接进行分组，现在上图中的表为返回结果的虚拟表。但是这都不影响分组操作。只要数据存在这重复就可以进行分组。

 

到这里的操作都是针对于单字段分组的，而实际情况下也可以实现多字段的分组。

范例：查询出每个部门的名称、部门的人数、平均工资。

l 确定所需要的数据表：

|-dept表：每个部门的名称、位置；

|-emp表：统计出部门的人数、平均工资 ；

l 确定已知的关联字段：emp.deptno=dept.deptno

l 将两张表关联起来

 

此时的返回结果上依然后大量的重复数据，而且平均的分在了三列之上（deptno，dname，loc），那么就可以在分组的字段中写上3个字段。

SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),NVL(AVG(e.sal),0)

FROM dept d,emp e

WHERE d.deptno=e.deptno (+) 

GROUP BY d.deptno,d.dname,d.loc;

 

以上就是多字段分组操作，但是不管是单字段还是多字段，一定都要有一个前提，那就是重复数据！

 

3、WHERE子句中不能使用统计函数

范例：统计出每个部门的详细信息，并要求这些部门的平均工资高于2000；

在以上程序的基础上完成改良，在之前唯一学习的限定查询语法只有WHERE语法，所以下面用WHERE完成要求。

SELECT d.deptno,d.dname,d.loc,COUNT(empno),AVG(sal+NVL(comm,0))

FROM emp e,dept d

WHERE d.deptno=e.deptno(+) AND AVG(e.sal)>2000 

GROUP BY d.deptno,d.dname,d.loc ;

执行这个语句时会报错:

 

 

错误提示的意思就是不能在WHERE语句之中使用统计函数。

WHERE是在GROUP语句之前执行，而GROUP又是在所有数据之中进行处理，所以WHERE语句就没用了，所以会报错。

这是就可以使用HAVING子句来实现。

 

注意：WHERE和HAVING之间的区别：

l WHERE：是在执行GROUP BY 操作之前进行过滤，表示从全部数据之中筛选出部分数据，在WHERE子句中不能使用统计函数；

l HAVING：是在GROUP BY 子句操作之后进行再次过滤，可以在HAVING子句之中使用统计函数。

范例：显示非销售人员的工作名称以及从事同一工作的雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于￥5000，输出结果按月工资的合计升序排列：

分析：

第一步：查询出所有的非销售人员的信息；

第二步：按照职位分组，并且用SUM()函数统计月工资总和；

第三步：月工资的合计是通过统计函数查询的，所以现在这个对分组之后的过滤要使用HAVING子句完成。

第四步：按照升序排序

SELECT job,SUM(sal) sum 

FROM emp

WHERE job!='SALESMAN'

GROUP BY job 

HAVING SUM(sal)>5000

ORDER BY sum ;

以上题目融合了分组操作大部分的语法使用。

 

 

 

 

 

 

 

 

四、子查询（核心）

子查询=简单查询+限定查询+多表查询+统计查询的综合体

多表查询并不被建议使用，多表查询的最有力的替代者 就是子查询，所以子查询在开发中使用的相当的多；

所谓的子查询就是在一个查询中嵌套了多个其他若干查询，语法如下：

SELECT [DISTNCT] *|分组字段1 [别名] [，分组字段2 [别名],...] | 统计函数(

SELECT [DISTNCT] *|分组字段1 [别名] [，分组字段2 [别名],...] | 统计函数

FROM 表名称[别名][,表名称[别名]，……]

[WHERE 条件（s）] 

[GROUP BY 分组字段1[,分组字段2,...]]

[ORDER BY字段[ASC | DESC]][,字段[ASC | DESC]];)

FROM 表名称[别名][,表名称[别名]，……](

SELECT [DISTNCT] *|分组字段1 [别名] [，分组字段2 [别名],...] | 统计函数

FROM 表名称[别名][,表名称[别名]，……]

[WHERE 条件（s）] 

[GROUP BY 分组字段1[,分组字段2,...]]

[ORDER BY字段[ASC | DESC]][,字段[ASC | DESC]];)

[WHERE 条件（s）] (

SELECT [DISTNCT] *|分组字段1 [别名] [，分组字段2 [别名],...] | 统计函数

FROM 表名称[别名][,表名称[别名]，……]

[WHERE 条件（s）] 

[GROUP BY 分组字段1[,分组字段2,...]]

[ORDER BY字段[ASC | DESC]][,字段[ASC | DESC]];)

[GROUP BY 分组字段1[,分组字段2,...]]

[ORDER BY字段[ASC | DESC]][,字段[ASC | DESC]];

理论上子查询可以出现在查询语句的任何地方，一般情况下子查询出现在WHERE和FROM语句之后比较多。

 

个人经验：（如果子查询出现在下面的子句之后）

l WHERE：子查询一般只返回单行单列、多行单列、单行多列的数据

l FROM：子查询一般返回多行多列数据，作为临时表出现

 

范例：要求查询工资比SMITH高的所有员工的全部信息。

第一步：查询SMITH的工资：

SELECT sal FROM emp WHERE ename=’SMITH’ ;

第二步：因为返回的数据是单行单列，所以将数据插入WHERE子句之后：

SELECT * FROM emp

WHERE sal>(

SELECT sal

FROM emp

WHERE ename=’SMITH’);

范例：查询超出公司平均工资的全部雇员信息

第一步：查询公司的平均工资

第二步：由于是返回数据是单行单列所以子查询跟在WHERE子句后面

SELECT * FROM emp

WHERE sal>(

SELECT AVG(sal) 

FROM emp );

以上范例均为返回单行单列数据，也可以返回单行多列（很少见）、多行单利或多行多列的数据，下见范例：

SELECT * FROM emp

WHERE (ename,job)=(

SELECT ename,job

FROM emp

WHERE ename=’ALLEN’) ;

返回数据是单行多列的情况并不多见，但是返回数据是多行单列很是常见，这时需要3种判断符：IN、ANY、ALL

1、判断符：用于指定一个子查询的判断范围

这个判断符的使用实际上和之前的IN操作是一样的，但是范围由子查询指定了

SELECT * FROM emp

WHERE sal IN(

SELECT sal

FROM emp

WHERE job=’MANAGER’) ;

注意：在使用IN的时候注意NOT IN的问题，在使用NOT IN的子查询之中，如果有有一个内容是null值的话，则不会查询出任何结果。因为NOT IN表示查询所有，为了防止此类安全漏洞Oracle设计为完全不查询/显示数据。

 

2、ANY操作符：与每一个内容相匹配，有三种匹配方式

l =ANY：功能和IN操作符完全一致

SELECT * FROM emp

WHERE sal =ANY(

SELECT sal

FROM emp

WHERE job='MANAGER') ;

l >ANY:比子查询中返回记录最小的还要大的数据（不包含）；

SELECT * FROM emp

WHERE sal >ANY(

SELECT sal

FROM emp

WHERE job='MANAGER') ;

l <ANY:比子查询中返回记录最大的还要小的数据（不包含）；

SELECT * FROM emp

WHERE sal <ANY(

SELECT sal

FROM emp

WHERE job='MANAGER') ;

 

3、ALL操作符：与每一个内容相匹配，有两种形式：

l >ALL:比子查询中返回的最大的记录还要大

SELECT * FROM emp

WHERE sal >ALL(

SELECT sal

FROM emp

WHERE job='MANAGER') ;

 

l <ALL:比子查询返回的最小数据还要小

SELECT * FROM emp

WHERE sal <ANY(

SELECT sal

FROM emp

WHERE job='MANAGER') ;

以上所有的子查询都是在WHERE子句中出现的，那么下面来观察FROM子句中出现的子查询，在这个子查询一般都返回的是多行多列的数据，但作是一张临时表来处理。

范例：查询出每个部门的名称、位置、编号、部门人数、平均工资

回顾：以前采用的是多字段分组统计

SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) COUNT,NVL(AVG(e.sal),0) AVGSAL

FROM emp e,dept d

WHERE d.deptno=e.deptno(+)

GROUP BY d.deptno,d.dname,d.loc 

ORDER BY d.deptno;

这个时候实际上产生了笛卡尔积，产生了56条记录，下面是新的解决方案：子查询。所有的子查询只能在GROUP BY中，所以在子查询中负责统计数据，而在外部数据之中负责将统计数据和dept表数据相统一。

SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg

FROM dept d,(

SELECT deptno,COUNT(empno) count,AVG(sal) avg

FROM emp

GROUP BY deptno) temp

WHERE d.deptno=temp.deptno(+) ;

现在的程序中所操作的数据量：

|-子查询中的统计记录是14条，最终统计的显示记录是3条；

|-dept表之中一共有4条记录；

|-如果现在产生的笛卡尔积的话只有12条记录，再加上雇员的14条记录一共才26条记录

综上，子查询的性能的确优于多表查询。

 

经验：大部分情况之下，如果最终的查询结果之中需要出现SELECT子句，但是又不能直接使用函数的时候就在子查询中统计信息，即：有复杂统计的地方大部分都需要子查询。

五、数据库的更新操作

DML语法操作之中，除了查询之外还有数据库的更新操作，数据的更新操作主要是指：添加、修改、删除数据。但是考虑到emp表以后还要继续使用，所以下面现将emp表复制一份，输入如下指令：

CREATE TABLE myemp AS SELECT * FROM emp ;

这种语法是Oracle中支持的操作，其他的数据库不一样。

 

 

一、数据增加

如果现在想要实现数据库的增加操作，则可以使用如下的语法完成：

INSERT INTO 表名称[(字段1，字段2，...)] VALUES(值1，值2，...) ;

如果想要进行增加数据的话，一下集中数据类型要分别处理：

|-增加的是数字：直接编写数字，例如：123 ；

|-增加字符串：字符串：字符串应该使用’’(单引号)进行申明；

|-增加DATE数据：

|-第一种：可以按照已有的字符串的格式编写字符串，例如“17-12 月-80”

|-第二种：利用TO_DATE()函数将字符串变为DATE型数据；

|-第三种：荣国设置的时间如当前系统时间，则使用SYSDATE;

可是对于数据的增加也有两种操作格式：完整型、简便型；

范例：增加数据——完整语法编写

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

VALUES(8888,’张三’,TODATE(‘1960-08-17’,’yyyy-mm-dd’),8000,7369,’清洁工’，1000)

 

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

VALUES(8888,'李四',SYSDATE,3000,7369,'clearer',30) ;

 

范例：增加数据——简便即不写增加的数据的列名称

虽然这种方法看似节省了代码，但在实际的开发中，这样操作很不利于程序的维护。因为要求添加的数据必须如原表的数据在顺序、类型、数量上完全一致才行。

 

 

二、数据修改

如果是想修改已有的数据，则可以按照如下的语法进行：

UPDATE  表名称 SET 更新字段1=更新值1，更新字段2=更新值2……[WHERE 更新条件(s)]

范例：更新雇员编号是7369的基本工资为5000，奖金是2000，职位是manager，雇佣日期改为今天；

UPDATE myemp SET sal=5000,comm=2000,job=’MANAGER’.hiredate=SYSDATE WHERE empno=7369 ;

 

范例：所有人的工资上涨50%

UPDATE myemp SET sal=sal*1.5 ;

在更新时，如果不添加WHERE条件的话便会将对表中的所有记录进行更新。这样做是相当不好及危险的。当表中的数据量较大时（3000-5000时明显表现出来），速度和性能会明显的下降。

 

三、删除数据

当某些数据不需要之后，便可以DELETE语句将其删除，语法如下：

DELETE FROM 表名称 [WHERE 删除条件(s)] ;

  与更新一样，如果未添加删除条件的话，便会删除所有记录。

范例：删除所有在1987年雇佣的雇员

DELETE FROM myemp WHERE TO_CAHR(hiredate,’yyyy’)=1987 ;

一定记住，如果删除的时候没有匹配的条件的数据存在，则更新数据量为’0条’，更新操作也是如此；

范例：删除表中的全部记录

DELETE FROM myemp ；

一般对于删除操作而言，尽可能的减少去使用，包括以后进行系统开发的时候，对于所有的删除操作也建议大家给出一个确认的提示框，以防止用户误删。

 

 

 

六、事务处理

对于数据表的操作，很明显查询要比操作更加的安全，因为更新操作有可能会出现错误，以导致没有按照既定的要求正确的完成我们的更新操作。

但是在很多时候，更新会由多条指令共同完成，例如：银行的转账方法

|-判断A的账户上是否有5000w

|-判断B账户是否存在并且状态是否正常

|-从A的账户上移走5000w

|-从B的账户上增加5000w

|-向银行支付手续费用5000；

以上的五个数据操作是一个整体，可以理解为一个完整的业务，如果

这里面的第三点出错了，所有的其他操作都不应该执行，并且回归到最初的状态

所有的事务处理操作都是针对每一个session进行的，在Oracle数据库中把每一个连接到数据库上的用户都称为一个session，每一个session之间彼、此独立，不会进行任何通讯而且每一个session独享自己的事务控制。而事务控制之中主要使用两个命令：

l 事务的回滚：ROLLBACK更新操作回到原点

l 事务的提交：COMMIT，真正的发出更新操作，一旦提交就无法回滚

但是这样也会出现一些问题，例如：

某一个session在更新数据库之前还没有提交事务，其他session是无法更新的，必须等到之前的session提交之后才可以；