第1章 Oracle教程_oracle查询每个员工的姓名、职位以及他们的直接上级的姓名(如果有的话),并按员工-程序员宅基地

技术标签： JAVA Oracle SQL

第一章 Oracle教程

简单查询语句（重点）

简单查询指的是将数据表中的全部内容查询出来进行显示，语法如下：

SELECT * | 列名称别名,列名称别名

FROM 表名称别名 ;

范例：查询雇员的全部信息 —— 由于此处是将所有的数据一起查询出来，那么直接编写以下语句即可：

SELECT * FROM emp ;

如果现在希望只查询部分的内容，则直接将“*”替换成具体的字段即可。

范例：要求查询出一个雇员的编号、姓名、基本工资 —— empno、ename、sal

SELECT empno,ename,sal FROM emp ;

我是中国人不懂英文，要求把以上的查询出来的字段的显示名称替换成中文，那么肯定是为最终查询出的显示列起一个别名。

范例：为查询结果起别名

SELECT empno 雇员编号,ename 雇员姓名,sal 基本工资 FROM emp ;

以上的替换在开发中没有任何的意义，只是为了说明别名的显示效果而已。所以在实际的工作中一定要尽可能的避免中文。

现在要求可以按照以下的一种形式显示查询结果：

编号是7369的雇员，姓名是：SMITH，每月的基本工资是800。

如果要想按照以上的要求显示，则肯定有部分数据是活动的，有一些数据是固定的，则要规定格式。

通过上面的要求可以发现，实际上现在做的就是一个字符串的连接功能，让一些固定的内容和查询的结果一起进行重新连接，并进行最终的显示。

如果要进行字符串的连接可以使用“||”完成，但是，如果连接的内容不是字段而是字符串的话必须使用“'”括起来。

SELECT '编号是' || empno || '的雇员，姓名是：' || ename || '，每月的基本工资是' || sal || '。' emp_info FROM emp ;

但是，在进行简单查询的时候本身也存在这一个问题，如果现在要求查询出所有的工作。

SELECT job FROM emp ;

这个时候发现有很多的信息重复了，因为职位本身就是重复的，那么此时，可以将SQL语法替换成以下的形式：

SELECT [DISTINCT] * | 列名称别名,列名称别名

FROM 表名称别名 ;

范例：消除重复内容

SELECT DISTINCT job FROM emp ;

但是，以上的代码有一点需要注意，如果现在假设显示出了很多列的内容，则只有在很多列的内容完全重复的时候才能够消除掉。

范例：观察如下的程序

SELECT DISTINCT empno,job FROM emp ;

在简单查询语句中也可以进行各种数学运算。

范例：要求显示一个雇员的编号、姓名，基本的年收入

SELECT empno,ename,sal*12 income FROM emp ;

但是，国家要求每个人上税，要求每个人每月要上缴5%的个人所得税。

SELECT empno,ename,(sal-0.05*sal)*12 income FROM emp ;

限定查询（重点）

之前的查询是将所有的内容全部取出，但是，有时候需要对查询结果进行过滤，例如：要求只显示工资高于2000的雇员信息，很明显，则此时肯定不能将全部的结果显示，那么这种情况下就必须使用限定查询，限定查询的语法如下：

SELECT [DISTINCT] * | 列名称别名,列名称别名

FROM 表名称别名

[WHERE 条件(s)];

在编写此查询的时候条件可以有任意多个，那么就需要对条件进行连接，使用AND、OR进行连接。

范例：要求显示基本工资大于2000的全部雇员信息

SELECT * FROM emp WHERE sal>2000 ;

范例：要求显示基本工资在2000~3000范围之中的雇员

· 这个时候应该存在两个条件：sal>=2000，第二个条件是sal<=3000，肯定要同时满足，那么就可以使用AND连接。

SELECT * FROM emp WHERE sal>=2000 AND sal<=3000 ;

范例：要求显示20部门的所有雇员的姓名和基本年工资

SELECT ename,sal*12 FROM emp WHERE deptno=20 ;

范例：要求显示所有雇员的信息，但是每个显示的雇员的年薪在20000以上。

SELECT * FROM emp WHERE (sal*12)>20000;

以上使用的也是各个关系运算符：>、>=、<、<=、=、!=、<>

范例：要求查询不是20部门雇员的信息

SELECT * FROM emp WHERE deptno<>20 ;

SELECT * FROM emp WHERE deptno!=20 ;

除了以上的关系之外，在SQL语句中也定义了许多操作的条件过滤符，这些符号必须重点掌握。

1、 处理null

null表示暂时没有内容，与一个空的字符串（''）是两个概念。例如：有些雇员有奖金，有些雇员没有奖金。

如果要想处理空，可以使用两种符号：IS NOT NULL、IS NULL。

范例：查询所有领取奖金的雇员

· 如果comm的内容不为空或者comm的内容不是0，应该是一个或的关系，有一个满足即可。

SELECT * FROM emp WHERE NOT (comm IS NULL OR comm=0) ;

· 下面换种做法：

SELECT * FROM emp WHERE comm IS NOT NULL AND comm<>0 ;

2、 BETWEEN…AND

范例：要求查询雇员的信息，雇员的基本工资在1500~2500之间

SELECT * FROM emp WHERE sal>=1500 AND sal<=2500 ;

也可以将以上的结果按照BETWEEN…AND进行修改，在BETWEEN的后面放上最小的取值，AND的后面放上最大的取值。

SELECT * FROM emp WHERE sal BETWEEN 1500 AND 2500 ;

但是，在BETWEEN…AND上也可以进行日期范围的查询。

范例：要求查询出所有在1981年雇佣的雇员

SELECT * FROM emp WHERE hiredate BETWEEN '03-1月 -81' AND '31-12月 -81' ;

3、 IN操作符

IN操作符表示的是一个范围

范例：要求查询出雇员编号是7369、7566、7788的雇员信息

SELECT * FROM emp WHERE empno=7369 OR empno=7566 OR empno=7788 ;

范例：要求查询出雇员编号不是7369、7566、7788的雇员信息

SELECT * FROM emp WHERE empno<>7369 AND empno<>7566 AND empno<>7788 ;

但是，以上的写法太麻烦了，所以可以借助于IN操作符。

SELECT * FROM emp WHERE empno IN (7369,7566,7788) ;

对于取反的操作，直接使用NOT IN即可：

SELECT * FROM emp WHERE empno NOT IN (7369,7566,7788) ;

以上的操作符例如在网站购物车中使用的较多。

4、 LIKE操作符

LIKE表示的是进行模糊查询的操作，在LIKE子句之中，有以下两种匹配符：

· 匹配一个字符：_

· 匹配一组字符：%

范例：要求查询雇员姓名中包含字母A的雇员信息，那么这个时候可能在开头、结尾、中间，所以此时就只能使用%

SELECT * FROM emp WHERE ename LIKE '%A%' ;

范例：要求查询出雇员姓名的第二个字符是M的雇员信息

SELECT * FROM emp WHERE ename LIKE '_M%' ;

而且在使用LIKE子句查询的时候有一点也特别重要，即：在进行查询时，LIKE可以在任意的数据类型的字段上使用，例如：数字或者是日期。

范例：在数字中使用LIKE

SELECT * FROM emp WHERE empno LIKE '%6%' OR sal LIKE '%6%' ;

范例：在日期中使用LIKE —— 要求，查询出所有在1981年雇佣的雇员

SELECT * FROM emp WHERE hiredate LIKE '%81%' ;

重点：在使用LIKE进行查询的时候，如果查询的内容中不包含任何的关键字（'%%'）则表示查询全部。

SELECT * FROM emp WHERE ename LIKE '%%' ;

对结果排序（重点）

在进行数据查询时，有时候需要对查询的结果进行排序的显示操作。例如：现在执行以下的语句：

SELECT * FROM emp WHERE ename LIKE '%%' ;

本身发现工资并没有任何的顺序，那么就可以在查询语句中加入ORDER BY子句，此时的语法如下：

SELECT [DISTINCT] * | 列名称别名,列名称别名

FROM 表名称别名

[WHERE 条件(s)]

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

在ORDER BY中的排序字段后面的两个选项：

· ASC：按照升序排列，也是默认的排列方式

· DESC：按照降序排列

范例：对显示结果按照工资由高到低排序

SELECT * FROM emp ORDER BY sal DESC ;

范例：对显示结果按照工资由高到低排序，如果工资相等，则按照雇佣日期将雇佣日期早的放在前面

SELECT * FROM emp ORDER BY sal DESC,hiredate ;

字符函数

字符函数肯定是和字符串的操作有关的函数，常用的字符函数有：UPPER()、LOWER()、INITCAP()、LENGTH()、REPLACE()、SUBSTR()。

范例：验证函数

SELECT UPPER('hello'),LOWER('HELLO') FROM DUAL ;

由于在Oracle中的任何的显示都要符合于SQL的查询标准，所以为了验证方便，在Oracle中提供了一张DUAL的虚拟表。

范例：要求查询出姓名是smith的雇员信息

SELECT * FROM emp WHERE ename='smith' ;

此种查询的时候，在Oracle中是要区分大小写的，那么很明显，如果现在是由用户自己输入的话，那么肯定不会过多的考虑大小写的问题，那么此时就可以依靠函数帮忙。

SELECT * FROM emp WHERE ename=UPPER('smith') ;

范例：要求显示出雇员姓名正好是5个字符长度的雇员姓名和姓名长度

SELECT ename,LENGTH(ename) FROM emp WHERE LENGTH(ename)=5 ;

范例：要求以首字母大写的形式显示所有雇员的姓名 —— INITCAP()

SELECT INITCAP(ename) FROM emp ;

范例：字符串截取 —— SUBSTR()

· 从开头截取到结尾：要指定开始点

SELECT SUBSTR('hello',3) FROM dual ;

· 截取中间部分：要同时指定开始点和截取的长度

SELECT SUBSTR('hello',3,2) FROM dual ;

问题：SUBSTR如果截取的话下标是从0开始还是从1开始？

· 两个都可以，即可以从0也可以从1。

SELECT SUBSTR('hello',0,2) FROM dual ;

思考：要求显示所有雇员姓名的后三个字符

· 按照正常的思路：求出长度-2，之后开始截取

SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp ;

· 以上代码麻烦，但是算是标准做法，在oracle中对于substr()函数也可以倒着截取。

SELECT ename,SUBSTR(ename,-3) FROM emp ;

范例：替换操作，将所有姓名中带有“M”的替换成“X”

SELECT ename,REPLACE(ename,'M','X') FROM emp ;

实际上以上的这些字符函数的名称在各个数据库中都是可以使用的，因为现在一个新的产品如果要想发展的话，则必须迎合使用者的口味。

数值函数

在Oracle中提供了以下的三个常用的数值函数：

· ROUND()：四舍五入

· TRUNC()：截取小数

· MOD()：取模

范例：观察ROUND的使用

SELECT ROUND(789.678) FROM dual ; à 默认情况下，小数点后面的都要进位，结果是：790

范例：指定小数位

SELECT ROUND(789.678,2) FROM dual ; à 保留两位小数，结果是：789.68

范例：负数操作

SELECT ROUND(-789.678) FROM dual ; à 保留两位小数，结果是：-790

范例：指定负范围

SELECT ROUND(789.678,-2) FROM dual ; à 保留两位小数，结果是：800

范例：观察TRUNC的使用

SELECT TRUNC(879.789) FROM dual ;

范例：指定负范围

SELECT TRUNC(879.789,-2) FROM dual ;

范例：取模

SELECT MOD(10,3) FROM dual ;

范例：要求计算出每个雇员的日薪金，忽略余数

SELECT ename,ROUND(sal/30,2) FROM emp ;

日期函数

对于日期在Oracle中也有一些函数的支持，可以使用如下的几个函数：

· MONTHS_BETWEEN：求出两个日期间的月数

· ADD_MONTHS()：在指定日期上增加指定的月份，求出新的日期

· NEXT_DAY()：求出下一个日期的今天

但是，如果要想操作日期有一个特别重要的概念需要首先掌握 —— 如何求出当前日期？

SELECT sysdate FROM dual ;

范例：要求求出下一个星期一是那号 —— next_day()

SELECT NEXT_DAY(sysdate,'星期一') FROM dual ;

范例：验证MONTHS_BETWEEN()函数 —— 求出所有雇员的雇佣月数

SELECT empno,ename,TRUNC(MONTHS_BETWEEN(sysdate,hiredate)) FROM emp ;

范例：验证ADD_MONTHS()函数

SELECT ADD_MONTHS(sysdate,4) FROM dual ;

在指定日期上增加指定的月份求出之后的日期。

在日期的操作中除了以上的提供的功能之外，还有三个公式需要掌握：

· 日期 – 日期 = 数字（天数）

· 日期 + 数字 = 日期

· 日期 – 数字 = 日期

范例：要求显示每个雇员雇佣的天数

SELECT empno,ename,SYSDATE-hiredate FROM emp ;

范例：求出三天后的日期

SELECT sysdate+3 FROM dual ;

例如：去某些论坛注册，要求3天后可以激活，那么就可以通过此类函数完成功能。

转换函数（重点）

在Oracle中提供了三种转换函数的定义：TO_CHAR()、TO_DATE()、TO_NUMBER()，这三种转换函数中，以TO_CHAR()函数最为重要。

范例：先观察以下程序的问题

SELECT empno,ename,hiredate FROM emp ;

以上的查询完成之后会发现日期的显示非常的不舒服，至少不符合于中国的显示习惯：年-月-日，那么这个时候就可以使用TO_CHAR()函数进行转换。

范例：观察转换操作

SELECT empno,ename,TO_CHAR(hiredate,'yyyy-mm-dd') FROM emp ;

范例：要求查询出在任何年份2月雇佣的雇员

SELECT * FROM emp WHERE TO_CHAR(hiredate,'mm')=2 ;

但是，在以上的转换中，也需要注意，在编写格式的时候可以使用“fm”去掉前导0。

SELECT TO_CHAR(sysdate,'fmyyyy-mm-dd') FROM dual ;

很明显的可以发现，现在显示的时候，所以显示数据的前导0已经消失了。

TO_CHAR()函数除了可以进行日期格式的转换之外，也可以进行数字格式的转换。

SELECT TO_CHAR(78463783912,'999,999,999,999,999,999') FROM dual ;

如果现在是要进行货币显示的话，则在前面可以加入“L”，表示使用本地的货币。

SELECT TO_CHAR(78463783912,'L999,999,999,999,999,999') FROM dual ;

如果要将一个字符串变为一个日期的话，则可以使用TO_DATE()函数完成。

SELECT TO_DATE('1978-09-19','yyyy-mm-dd') FROM dual ;

可以将一个字符串变为一个数字，则使用TO_NUMBER()。

SELECT TO_NUMBER('1') + TO_NUMBER('2') FROM dual ;

但是，由于Oracle足够智能，所以以上的即使不使用TO_NUMBER()函数也可以正常完成。

SELECT '1' + '2' FROM dual ;

以上只是Oracle自己提供的一个特点，本身没有其他的通用性。

通用函数（重点）

在Oracle中提供了两个重要的通用函数：NVL()、DECODE()。

范例：要求计算每个员工的年薪，这个时候的计算要求加上每个月的奖金

SELECT empno,ename,(sal+comm)*12 income FROM emp ;

以上的计算结果并不能真正的反映出年薪，因为有的雇员的奖金为null，那么如果使用了null直接操作的话，最终的结果就是null，即：不要工资了，那么这种情况下就需要将null转换，如果为null按照0计算，那么就要靠NVL()函数。

SELECT empno,ename,(sal+NVL(comm,0))*12 income FROM emp ;

DEOCDE()函数：Oracle一个具备最大特点的函数。

DECODE()函数的语法如下：

DECODE( 列名称 | 具体值 , 判断值1, 显示内容1, 判断值2, 显示内容2,…/)

范例：观察DECODE()函数的使用

SELECT DECODE(2,1,'内容是一',2,'内容是二',3,'内容是三') FROM dual ;

那么下面就通过以上的函数完成一个功能，例如：现在在emp中有很多的职位，要求替换成中文：

· 办事员：CLERK

· 销售人员：SALESMAN

· 经理：MANAGER

· 分析员：ANALYST

· 总裁：PRESIDENT

SELECT empno,ename,DECODE(job,'CLERK','办事员','SALESMAN','销售人员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') FROM emp ;

本函数一定要重点掌握。

多表查询的基本概念

如果是多表查询，只是在FROM子句之后多增加了几张表而已，此时的SQL语法如下：

SELECT [DISTINCT] * | 列名称别名,列名称别名

FROM 表名称别名,表名称别名,表名称别名

[WHERE 条件(s)]

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

但是如果要想进行多表查询本身也存在着一些问题，例如，在进行多表查询前先分别查询一下dept和emp表。

范例：查询部门表

SELECT * FROM dept ;

SELECT COUNT(*) FROM dept ;

范例：查询雇员表

SELECT COUNT(*) FROM emp ;

现在部门表中存在了4条记录，对于雇员表中存在了14条记录，从显示的要求来看，如果现在两张表一起查询，最多应该显示14条，但是：

SELECT * FROM emp,dept ;

现在发现一共查询出了56条（4 * 14 = 56）记录，这实际上就是数据库本身的操作机制决定的，此种结果在数据库中称为笛卡尔积，可以发现，由于笛卡尔积的存在，所以多表查询本身的性能并不高，但是如果两张表的数据量小的话，那么使用多表查询还是可以接受的。

但是，现在的显示结果并不正确，那么肯定应该去掉笛卡尔积，那么如果要想去掉此内容，则肯定要通过条件过滤完成，对于相关联的字段加入条件验证。现在发现在emp表dept表中都存在deptno字段。

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

此时，笛卡尔积已经成功的消除掉了，发现，如果多表查询，则肯定要编写关联条件。但是以上的代码也存在问题，如果现在一个表名称非常的长，例如：zhongguo_beijing_renmin_biao。按照之前的写法是通过“表名称.字段名称”的形式访问的话，那么肯定不方便，所以一般在进行多表查询的时候都会为每一张表起一个别名，以后直接通过“别名.字段名称”访问即可，下面为程序起别名：

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

范例：要求查询出一个雇员的编号、姓名、工作、基本工资，部门名称

· 通过以上的要求可以发现，对于编号、姓名、工作、基本工资可以直接从emp表中查询

· 对于部门名称，则肯定需要从dept表

SELECT e.empno,e.ename,e.job,e.sal,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno ;

范例：要求查询出每个雇员的姓名极其上级领导的姓名

· 在emp表中存在的mgr字段实际上表示的就是一个雇员的上级领导的编号，因为领导本身也是雇员。

· 由于领导姓名也存在部门表之中，所以很明显这种关联的查询属于自身关联。

· 条件：雇员的领导编号 = 领导的雇员编号

SELECT e.ename,m.ename

FROM emp e,emp m

WHERE e.mgr=m.empno ;

范例：要求查询出每个雇员的编号、姓名、基本工资以及工资所在公司的工资等级

· 先确定要使用的表：emp、salgrade

· 既然是多表关联，则肯定要想办法去掉笛卡尔积，就是找关联字段。

· 工资等级表中的范围就是一个雇员薪金的等级。

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

FROM emp e,salgrade s

WHERE e.sal BETWEEN s.losal AND s.hisal ;

· 实际上过滤条件，也可以通过一个公式来进行计算，指定范围。

范例：要求查询出每个雇员的编号、姓名、基本工资所在的部门名称及部门位置以及工资所在公司的工资等级

· 很明显应该使用三张表关联在一起

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

FROM emp e,dept d,salgrade s

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

范例：要求查询出每个雇员的编号、姓名、基本工资所在的部门名称及部门位置以及工资所在公司的工资等级，领导的编号、姓名

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

FROM emp e,emp m,dept d,salgrade s

WHERE e.sal BETWEEN s.losal AND s.hisal AND e.deptno=d.deptno AND e.mgr=m.empno ;

实际上多表查询中，就要求根据其提出的具体形式，找到所要的表，并且从表中去除掉笛卡尔积（就是加入条件）。

左、右连接问题

现在，观察如下的一段代码：

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

以上的查询只显示出了3个部门，但是却没有40部门，因为现在的去掉笛卡尔积的条件是雇员的部门编号=部门编号，没有一个雇员是40部门的雇员，所以肯定此条件中不可能出现40部门。那么如果要想出现，则必须改变连接的标准。

SELECT * FROM emp e,dept d WHERE e.deptno(+)=d.deptno ;

实际上以上的程序就表示加入了右连接，如何去区分左、右连接

· 如果(+)在等号的右边表示的是左连接。

· 如果(+)在等号的左边表示的是右连接。

但是，实际中没有必要去区分是左还是右连接，只要是想要查询的内容没有出现，就加入“(+)”符号。

范例：观察以下的程序

SELECT e.ename,m.ename

FROM emp e,emp m

WHERE e.mgr=m.empno(+) ;

在使用oracle的开发中，只要是进行了多表查询，如果发现有的内容没能正确显示的话，那么就要加入以上的处理。

组函数

组函数实际上应该称为分组统计函数，是用于进行统计的，例如：之前使用的COUNT()本身就属于一个组函数，那么组函数一共有以下几个：

· SUM()：求和

· AVG()：求平均值

· MAX()：最大值

· MIN()：最小值

对于求和求平均的操作只能在数字上应用，所以使用的时候必须注意：

范例：要求统计出所有的雇员人数、平均工资

SELECT COUNT(empno),AVG(sal) FROM emp ;

范例：求出每个月的工资总和

SELECT ROUND(SUM(sal),-4) FROM emp ;

范例：求出最高和最低工资

SELECT MAX(sal),MIN(sal) FROM emp ;

对于组函数，用到时候使用即可。

分组统计

分组统计：把相似的内容进行归类得出统计的结果。

例如：在站队的时候可以将难生分为一队，女生分为一队。可以发现，一个分组中，必定存在某些相同的内容。

分组统计的语法如下：

SELECT [DISTINCT] * | 列名称别名,列名称别名

FROM 表名称别名,表名称别名,表名称别名

[WHERE 条件(s)]

[GROUP BY 分组条件]

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

由于分组必然包含重复内容，所以在进行条件设置的时候，肯定是选择会重复的内容进行划分。

范例：求出每个部门的雇员人数

SELECT deptno,COUNT(empno)

FROM emp

GROUP BY deptno ;

以上的程序确实完成了分组，也按照重复的时候才有可能分组的要求，但是在进行分组查询的时候本身也是有严格要求的，在查询结果中只能出现分租条件和统计函数，如以下的代码就是错误的：

SELECT deptno,COUNT(empno),ename

FROM emp

GROUP BY deptno ;

另外，需要注意的是，如果现在要使用分组函数，只能单独使用，而不能出现任何的其他字段：

SELECT deptno,COUNT(empno) FROM emp ;

所以只要是分组函数要么单独使用，要么结合在分组统计中出现。

范例：要求求出每个工作的最高和最低工资

· 由于是按照工资统计，所以肯定要按照job进行分组。

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

FROM emp

GROUP BY job ;

范例：要求求出每个工作的平均工资，并要求这些工资的总额大于3000

· 先进行分组：

SELECT job,AVG(sal)

FROM emp

GROUP BY job ;

· 工资大于3000，从之前学到的概念上讲，只能在WHERE编写条件

SELECT job,AVG(sal)

FROM emp

WHERE AVG(sal)>3000 ;

GROUP BY job ;

但是，在执行的时候出现了以下的问题：

ORA-00934: 此处不允许使用分组函数

现在发现，分组条件根本就无法使用在WHERE子句之中，如果要在分组中使用条件的话，则需要在HAVING子句中完成，语法如下：

SELECT [DISTINCT] * | 列名称别名,列名称别名

FROM 表名称别名,表名称别名,表名称别名

[WHERE 查询条件(s)]

[GROUP BY 分组条件 [HAVING 分组条件(s)]]

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

使用HAVING进行程序的修改：

SELECT job,AVG(sal)

FROM emp

GROUP BY job HAVING AVG(sal)>3000 ;

如果要是使用分组函数进行条件过滤的话，则只能在HAVING子句中出现，而其他的查询条件可以直接在WHERE子句中使用。

思考题：

要求显示所有的非销售人员的工作名称以及从事同一工作的雇员的月工资的总和，并且要求月工资的合计大于5000，显示的结果按照月工资的合计的降序排列。

由于在实际中查询是最复杂的，所以面对问题，要采用逐步的细分操作。

步骤一：显示非销售人员的工作名称

SELECT job FROM emp WHERE job<>'SALESMAN' ;

步骤二：在以上的查询中加入分组条件

SELECT job,SUM(sal) FROM emp WHERE job<>'SALSMAN'

GROUP BY job ;

步骤三：肯定要求工资合计大于5000，此处肯定要使用分组函数，编写HAVING子句

SELECT job,SUM(sal) FROM emp WHERE job<>'SALSMAN'

GROUP BY job HAVING SUM(sal)>5000 ;

步骤四：对查询的结果进行排序

SELECT job,SUM(sal) sum

FROM emp WHERE job<>'SALSMAN'

GROUP BY job HAVING SUM(sal)>5000

ORDER BY sum DESC ;

在分组函数中，可以对分组函数进行嵌套，但是嵌套之后的分组函数的查询中不能出现任何的其他字段。

范例：求出月工资合计最高的工资

SELECT MAX(SUM(sal))

FROM emp

GROUP BY job ;

子查询（重点）

子查询是在实际的开发中使用的最多的一种查询模式，使用子查询可以提升查询的效率。

子查询就是在一个查询语句之中嵌套其他的查询语句，子查询可以出现在查询语句的各个位置上。

SELECT [DISTINCT] * | 列名称别名,列名称别名,

(

SELECT [DISTINCT] * | 列名称别名,列名称别名

FROM 表名称别名,表名称别名,表名称别名

[WHERE 查询条件(s)]

[GROUP BY 分组条件 [HAVING 分组条件(s)]]

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

)

FROM 表名称别名,表名称别名,表名称别名

(

SELECT [DISTINCT] * | 列名称别名,列名称别名

FROM 表名称别名,表名称别名,表名称别名

[WHERE 查询条件(s)]

[GROUP BY 分组条件 [HAVING 分组条件(s)]]

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

)

[WHERE 查询条件(s) (

SELECT [DISTINCT] * | 列名称别名,列名称别名

FROM 表名称别名,表名称别名,表名称别名

[WHERE 查询条件(s)]

[GROUP BY 分组条件 [HAVING 分组条件(s)]]

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

)]

[GROUP BY 分组条件 [HAVING 分组条件(s)]]

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

一般在FROM或WHERE子句中出现子查询的几率较大。

范例：要求查询出工资比7788还要高的雇员的信息

· 此时肯定要先知道7788的工资是多少。

SELECT sal FROM emp WHERE empno=7788 ;

· 编写子查询：

SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE empno=7788) ;

范例：要求查询出与7566从事同一工作的雇员信息

· 首先查询出7566的工作

SELECT job FROM emp WHERE empno=7566 ;

· 编写子查询，以上面的查询结果作为查询条件

SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE empno=7566) AND empno<>7566;

范例：要求查询出工资高于公司平均工资的雇员信息

· 求出公司的平均工资：分组统计

SELECT AVG(sal) FROM emp ;

· 编写子查询，将上面的查询作为查询的条件

SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp) ;

范例：要求查询出工资大于7521，并且和7902从事同样工作的雇员信息

· 求出7521的工资：

SELECT sal FROM emp WHERE empno=7521 ;

· 求出7902的工作

SELECT job FROM emp WHERE empno=7902 ;

· 做子查询，肯定要同时满足两个条件

SELECT * FROM emp WHERE

sal>(SELECT sal FROM emp WHERE empno=7521) AND

job=(SELECT job FROM emp WHERE empno=7902) AND

empno NOT IN(7902,7521);

以上的几个子查询都是在WHERE子句中编写的，而且所有的子查询必须使用“()”括起来，从子查询的使用角度来看，主要分为三种情况的查询：

· 单列子查询：返回的结果是一行一列的一个内容，使用几率较高

· 单行子查询：返回的结果是一行多列的内容，一般使用较少

· 多行子查询：返回的结果是多条记录，基本上都是以单列的形式返回的

范例：要求查询出工资最高的雇员信息

· 求出所有雇员中工资最高是多少

SELECT MAX(sal) FROM emp ;

· 以上面的查询结果作为子查询继续查询

SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp) ;

范例：要求查询出每个部门的名称、部门的雇员数、平均工资

· 步骤一：先确定要使用的表：dept、emp

· 先不去考虑是否分组，先把一些基本信息查询出来。

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

FROM emp e,dept d

WHERE e.deptno=d.deptno ;

· 现在的返回结果中是一张临时表，之后发现现在的dname的显示重复了，既然重复了，那么就可以分组。

SELECT d.dname,COUNT(*),AVG(e.sal)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY d.dname ;

在开发中不管是一张具体的表还是一张根据查询结果返回的临时表，那么只要是存在了重复，就都允许分组。

· 但是以上的代码并不完整，因为还有40部门没有出现，要进行左、右连接的处理

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

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno

GROUP BY d.dname ;

范例：要求查询出每个部门的完整信息（编号、名称、位置，部门人数、平均工资）

· 在执行分组的时候，可以显示的是：分组条件、组函数

· 由于现在要显示的信息较多，所以现在对于这种包含统计信息的时候就可以将这些统计信息先定义成一张临时表

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

FROM emp GROUP BY deptno ;

· 以上的查询实际上可以返回的就是一张临时表，这种临时表肯定有一个字段可以与一些具体表或者是临时表一一对应上，本程序中的临时表就可以通过deptno进行对应。

SELECT d.deptno,d.dname,d.loc,NVL(temp.count,0),NVL(temp.avg,0)

FROM dept d,(SELECT deptno dno,COUNT(empno) count,AVG(sal) avg

FROM emp GROUP BY deptno) temp

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

子查询可以出现在任意的位置上，以上的查询也是在整个查询中的难点，永远考虑在分组的时候不能出现过多的查询结果，一旦出现的话，就只能先依靠子查询将统计信息取出，之后作为临时表以待以后继续使用。

对于子查询的操作中还有以下的三种符号需要注意：IN、ANY、ALL

1、 IN操作符

· 表示一个查询的范围，与最早的IN的功能是完全一样的。

范例：求出每个部门最低工资的雇员信息

· 每个部门都会存在一个最低工资，所以，此时查询的返回结果是多行查询

SELECT MIN(sal) FROM emp GROUP BY deptno ;

· 使用IN操作符

SELECT * FROM emp WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno) ;

2、 ANY操作符

· ANY操作符使用起来比较麻烦，因为存在三种可用的情况：

|- =ANY：功能与IN一样\

SELECT * FROM emp WHERE sal =ANY (SELECT MIN(sal) FROM emp GROUP BY deptno) ;

|- >ANY：比子查询中最小的值要大

SELECT * FROM emp WHERE sal >ANY (SELECT MIN(sal) FROM emp GROUP BY deptno) ;

|- <ANY：比子查询最大的值要小

SELECT * FROM emp WHERE sal <ANY (SELECT MIN(sal) FROM emp GROUP BY deptno) ;

3、 ALL操作符

· ALL操作符使用起来也存在两种情况：

|- >ALL：比最大的还要大

SELECT * FROM emp WHERE sal >ALL (SELECT MIN(sal) FROM emp GROUP BY deptno) ;

|- <ALL：比最小的还要小

SELECT * FROM emp WHERE sal <ALL (SELECT MIN(sal) FROM emp GROUP BY deptno) ;

子查询补充

1.如何显示高于自己部门平均工资的员工的信息。

（1）查询出各个部门的平均工资和部门号。

select deptNO,avg(sal)mysal from emp group by deptNO;

（2）把上面的查询看成是一张子表。

select a2.ename,a2.sal,a2.deptNO,a1.mysal from emp a2,(select deptNO,avg(sal)mysal from emp group by deptNO) a1 where a2.deptNO=a1.deptNo and a2.sal>a1.mysal;

在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫内嵌式视图，当在from子句中使用子查询时，必须给子查询指定别名。给表取别名不能加as。给列加别名可以加as。

2.oracle的分页一共有三种方式。（根据rowId，按分析函数来分，按rownum来分。）

(1) select a1.*,rownum rn from (select * from emp) a1;

注：rownum分页。rownum给每一行分配一个行号。

3.显示emp表的前十行：

（1）select a1.*,rownum rn from (select * from emp) a1 where rownum<=10;

（select * from emp）虚拟表可以看成个一个真实的表，where限制语句里面的条件需要是虚拟表里的面存在的。

注：这里rownum是红色部分表中隐含的列。可以通过下面查询语句进行验证

（2）select a2.*,rownum rn from (select a1.*,rownum from emp a1)a2 where rownum<=10;

注：这里rn不能做条件条件是之前就存在的，才能用作条件。

4.显示emp表5--10条：

select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6;

注： rownum在子查询中where之后只能出现一次，where rownum>=5 and rownum<=10是错的。

5.指定查询列，只需修改最里层的子查询。

select * from (select a1.*,rownum rn from (select ename,sal from emp) a1 where rownum<=10) where rn>=6;

6.如何排序，只需修改最里层的子查询。

select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal desc) a1 where rownum<=10) where rn>=6;

7.显示第四条到第九条：

select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal desc) a1 where rownum<=9) where rn>=4;

查询其他的表的话，只可把表名给改了就行了。

8.显示emp表有多少行

select count（*）from emp；

9.用查询结果创建新表。这个命令是一种快捷的建表方法。

create table mytable(id,name,sal,job,deptNO) as select empNO,ename,sal,job,deptNO from emp;

10.合并查询。

union并集。该操作用于取得两个结果的并集。当使用该操作时，会自动去掉结果集中重复行。

select ename,sal,job from emp where sal>2500

union

select ename,sal,job from emp where job='MANAGER';

11.union all合并查询但不会去掉重复行。

12.minus，如果a包括b，那么显示a中除b的部分。

ROWNUM（绝对重点）

在进行数据的查询中实际上是存在了一个伪列（不是一个真实存在的列），这个列用于表示每一行记录的行数，都使用ROWNUM表示出来。

范例：观察如下的查询

SELECT ROWNUM,empno,ename,sal,job,hiredate FROM emp ;

这个时候，在每一行记录的前面自动存在了一个编号，这个编号中的ROWNUM本身并不是存在表中的，而是会根据查询的结果在每一行中自动加入。那么利用ROWNUM就可以完成数据的部分显示功能。

范例：显示前5条记录

SELECT ROWNUM,empno,ename,sal,job,hiredate FROM emp WHERE ROWNUM<=5 ;

范例：显示中间5条

SELECT ROWNUM,empno,ename,sal,job,hiredate FROM emp WHERE ROWNUM<=10 AND ROWNUM>5;

但是，遗憾的是，此时并不能显示结果，所以ROWNUM只能出现一次，如果想真正的达到数据的部分显示，需要通过子查询完成。基本原理：先取出前面的10条记录，在从这10条记录中取出最后的5条。

SELECT * FROM (SELECT ROWNUM rn,empno,ename,sal,job,hiredate FROM emp WHERE ROWNUM<=10) temp WHERE temp.rn>5 ;

范例：显示最后的四条

SELECT * FROM (SELECT ROWNUM rn,empno,ename,sal,job,hiredate FROM emp WHERE ROWNUM<=15) temp WHERE temp.rn>10 ;

ROWNUM在以后的开发中主要用于分页的操作显示，而且也是一个Oracle提供的重要特点。

数据的增加

如果现在要想进行数据的增加操作，则可以按照如下的语法进行：

INSERT INTO 表名称 [(字段1,字段2,...)] VALUES (内容1,内容2,...) ;

但是以上的语法有一点需要注意的是：在进行插入数据的时候要注意数据的类型：

· 如果是字符串的话，则必须使用“'”括起来

· 如果是数字的话，则直接出现

· 如果是日期，可以按照已有的日期格式编写字符串，或者使用TO_DATE()函数

范例：向myemp表中增加一条新的记录

INSERT INTO myemp(empno,ename,job,hiredate,mgr,sal,comm,deptno) VALUES(8888,'张三','看门大爷','17-12月-79',7369,3000,1000,40) ;

此时已经增加了一条新的记录，以上属于标准的语法，也有一种简便的语法，本身不推荐使用：

INSERT INTO myemp VALUES(6666,'张三','看门大爷',7369,'17-12月-79',3000,1000,40) ;

如果要在数据中插入null的话，则使用完整的格式会比较方便。

范例：增加记录，部门编号和领导编号为null

· 完整做法：

INSERT INTO myemp(empno,ename,job,hiredate,sal,comm) VALUES(9898,'张三','看门大爷','17-12月-79',3000,1000) ;

· 简便做法：

INSERT INTO myemp VALUES(6688,'张三','看门大爷',null,'17-12月-79',3000,1000,null) ;

对于日期的话，最好的形式还是利用TO_DATE()函数完成，因为比较方便。

INSERT INTO myemp(empno,ename,job,hiredate,mgr,sal,comm,deptno) VALUES(8756,'张三','看门大爷',TO_DATE('1987-09-19','yyyy-mm-dd'),7369,3000,1000,40) ;

如果现在的要求的日期是当前日期的话，则直接使用sysdate即可：

INSERT INTO myemp(empno,ename,job,hiredate,mgr,sal,comm,deptno) VALUES(7756,'张三','看门大爷',sysdate,7369,3000,1000,40) ;

数据的修改

如果要对已有的数据进行修改，则可以使用如下的语法完成：

UPDATE 表名称 SET 字段1=内容1,字段2=内容2,….[WHERE 修改条件]

对于修改条件呢，一般而言都要写上，因为如果不写的话，则表示对所有的数据都要修改。

范例：修改已有的数据，将雇员编号为7369的雇员的基本工资修改为3000，奖金为3000

UPDATE myemp SET sal=3000,comm=3000 WHERE empno=7369 ;

如果现在没有写条件，则全部人的工资都要变化。

UPDATE myemp SET sal=3000,comm=3000 ;

数据的删除

当不需要某些数据的时候可以直接通过删除完成，删除的语法如下：

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

范例：删除雇员编号是8888、6666的雇员信息

DELETE FROM myemp WHERE empno IN(8888,6666) ;

如果现在没有编写删除条件的话，则表示删除全部的数据：

DELETE FROM myemp ;

事务处理（重点）

事务处理在实际的开发中是一个非常敏感的话题，所谓的事务处理就是指一组相关的操作，要么同时成功，要么同时失败。具备了一体性。

在讲解事务的操作之前，先来研究以下的一个问题：在Oracle数据库之中，对于每一个连接到Oracle数据库上的用户实际上对于Oracle来讲都表示创建了一个SESSION（会话）。

在执行数据库更新的操作中，如果一个session现在更新了某些数据，在没有真正提交前实际上数据真正的内容是不会被更改的。

因为当发现操作有错误的时候可以通过回滚进行恢复，使用rollback命令。如果要想让一组更新操作真正执行的话，则要将事务进行提交，使用commit指令。

但是，这样一来就会造成另外一种问题，这种问题就称为死锁，一个SESSION等待着另外一个SESSION更新完毕后才执行。

查询案例（重点）

1、列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。

· 先求出部门人数大于1的所有部门编号，直接通过emp表进行分组统计

SELECT deptno FROM emp GROUP BY deptno HAVING COUNT(empno)>=1 ;

· 如果要想找到部门的名称则肯定需要dept表，而且查询的条件必须符合于上面的返回结果

SELECT * FROM dept

WHERE deptno IN (SELECT deptno FROM emp GROUP BY deptno HAVING COUNT(empno)>1) ;

· 但是以上的求法可以变为另外一种形式。

SELECT * FROM dept d,(SELECT deptno dno FROM emp GROUP BY deptno HAVING COUNT(empno)>1) temp

WHERE d.deptno=temp.dno ;

· 要想求出平均工资、最高工资、最低工资，肯定依然需要分组

SELECT d.deptno,d.dname,d.loc,temp.avg,temp.min,temp.max

FROM dept d,(SELECT deptno dno,AVG(sal) avg,MIN(sal) min,MAX(sal) max FROM emp GROUP BY deptno HAVING COUNT(empno)>1) temp

WHERE d.deptno=temp.dno ;

2、列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。

· 求出SMITH和ALLEN的薪金

SELECT sal FROM emp WHERE ename IN ('SMITH','ALLEN') ;

· 比他们的工资要高，>ANY

SELECT * FROM emp

WHERE sal >ANY (SELECT sal FROM emp WHERE ename IN ('SMITH','ALLEN')) ;

· 要求有部门名称，增加dept表

SELECT e.empno,e.ename,d.dname FROM emp e,dept d

WHERE sal >ANY (SELECT sal FROM emp WHERE ename IN ('SMITH','ALLEN')) AND e.deptno=d.deptno ;

· 增加领导的姓名，就是自连接

SELECT e.empno,e.ename,d.dname,m.ename

FROM emp e,dept d,emp m

WHERE e.sal >ANY (SELECT sal FROM emp WHERE ename IN ('SMITH','ALLEN')) AND e.deptno=d.deptno AND e.mgr=m.empno(+) ;

3、列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。

· 自身关联

SELECT e.empno,e.ename,m.empno,m.ename,(m.sal+NVL(m.comm,0))*12 income

FROM emp e,emp m

WHERE e.mgr=m.empno

ORDER BY income DESC ;

4、列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。

· 列出受雇日期早于其直接上级的所有员工的编号、姓名，hiredate

SELECT e.empno,e.ename,m.empno,m.ename

FROM emp e,emp m

WHERE e.mgr=m.empno(+) AND e.hiredate<m.hiredate ;

· 部门名称和部门位置，需要查询dept

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

FROM emp e,emp m,dept d

WHERE e.mgr=m.empno(+) AND e.hiredate<m.hiredate AND e.deptno=d.deptno ;

· 要想求出部门人数，则肯定需要分组统计，但是以上的查询中如果出现分组统计已经很困难，所以使用子查询

SELECT e.empno,e.ename,m.empno,m.ename,d.dname,d.loc,temp.count

FROM emp e,emp m,dept d,(SELECT deptno dno,COUNT(empno) count FROM emp GROUP BY deptno) temp

WHERE e.mgr=m.empno(+) AND e.hiredate<m.hiredate AND e.deptno=d.deptno AND temp.dno=e.deptno;

5、列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。

· 进行两张表的关联查询，同时按照部门名称分组，但是需要左、右连接问题。

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

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno

GROUP BY d.dname ;

6、列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。

· 既然现在要找到部门名称，则肯定查询dept表，雇员姓名是emp表

SELECT e.ename,d.dname

FROM emp e,dept d

WHERE e.deptno=d.deptno AND e.job='CLERK' ;

· 部门人数肯定需要进行分组，肯定还是在FROM之后编写子查询

SELECT e.ename,d.dname,temp.count

FROM emp e,dept d,(SELECT deptno dno ,COUNT(empno) count FROM emp GROUP BY deptno) temp

WHERE e.deptno=d.deptno AND e.job='CLERK' AND e.deptno=temp.dno ;

· 工资等级：引入salgrade表

SELECT e.ename,d.dname,temp.count,s.grade

FROM emp e,dept d,(SELECT deptno dno ,COUNT(empno) count FROM emp GROUP BY deptno) temp,salgrade s

WHERE e.deptno=d.deptno AND e.job='CLERK' AND e.deptno=temp.dno AND e.sal BETWEEN s.losal AND s.hisal ;

7、列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工资。

· 先找到工作，从emp表中查询

SELECT job

FROM emp

GROUP BY job

HAVING MIN(sal)>1500 ;

· 从使此工作的雇员人数

SELECT job,COUNT(empno) count FROM emp GROUP BY job HAVING MIN(sal)>1500 ;

· 加入部门的名称、位置

SELECT e.job,temp.count,d.dname,d.loc,temp.avg

FROM emp e,(SELECT job,COUNT(empno) count,AVG(sal) avg FROM emp GROUP BY job HAVING MIN(sal)>1500) temp,dept d

WHERE temp.job=e.job AND e.deptno=d.deptno ;

8、列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。

· 要求出销售部门的编号，找到dept表

SELECT deptno FROM dept WHERE dname LIKE '%SALES%' ;

· 以上面的查询为条件，求出雇员信息和部门名称

SELECT e.ename,e.sal,e.hiredate,d.dname

FROM emp e,dept d

WHERE e.deptno=(SELECT deptno FROM dept WHERE dname LIKE '%SALES%') AND e.deptno=d.deptno ;

9、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。

· 求出公司的平均薪金

SELECT AVG(sal) FROM emp ;

· 找到所有的雇员，查询emp表

SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp) ;

· 所在的部门名称和位置，加入dept表

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

FROM emp e,dept d

WHERE sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno ;

· 找到上级领导，加入自身关联

SELECT e.empno,e.ename,d.dname,m.ename

FROM emp e,dept d,emp m

WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno ;

· 工资等级，加入salgrade表

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

FROM emp e,dept d,emp m,salgrade s

WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisal ;

10、列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。

· 找到SCOTT从事的工作

SELECT job FROM emp WHERE ename='SCOTT' ;

· 找到雇员的姓名和部门名称

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

FROM emp e,dept d

WHERE e.job=(SELECT job FROM emp WHERE ename='SCOTT') AND e.deptno=d.deptno AND e.ename<>'SCOTT' ;

· 部门的人数

SELECT e.empno,e.ename,d.dname,temp.count

FROM emp e,dept d,(SELECT deptno dno,COUNT(empno) count FROM emp GROUP BY deptno) temp

WHERE e.job=(SELECT job FROM emp WHERE ename='SCOTT') AND e.deptno=d.deptno AND e.ename<>'SCOTT' AND e.deptno=temp.dno ;

约束（绝对重点）

在数据库之中，对于所有的数据的有效性必须有所保证，那么就要依靠约束完成功能，在SQL语句之中定义了以下的五种约束：

· 非空约束：一个字段的内容不允许设置成null

· 主键约束：包含了非空，内容不允许重复

· 唯一约束：内容不允许重复

· 检查约束：设置一个检查的条件，如果满足，则插入

· 主-外键约束：是在两张表中作为父子关系的约束。

在数据库中约束一般都会在建表的时候创建，当然也允许增加或删除约束，只是一般不这么做。

3.2.1、非空约束

非空约束本身很好理解，就是在某一个列上的内容不允许设置成null，如果为null，则无法插入。

范例：现在有如下一张表

DROP TABLE member ;

PUGRE RECYECLEBIN ;

CREATE TABLE member(

pid NUMBER ,

name VARCHAR2(50) NOT NULL

) ;

在表中name字段由于使用了NOT NULL所以在增加内容的时候，里面的内容不能是null。

范例：增加错误的数据

INSERT INTO member(pid,name) VALUES (10001,null) ;

由于name字段不能为空，所以如果增加了null的话，则在插入时会出现如下的错误提示：

ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."NAME")

而且从以上的错误提示中可以明确的发现是那个用户的表的字段出现了问题。

3.2.2、主键约束

主键约束用于定义不能重复的数据列，本身已经包含了非空约束，例如：人员编号应该是不重复的数据，而且不能为空，那么，此时肯定就需要使用主键约束。

范例：创建主键约束

DROP TABLE member ;

PUGRE RECYECLEBIN ;

CREATE TABLE member(

pid NUMBER PRIMARY KEY NOT NULL,

name VARCHAR2(50) NOT NULL

) ;

范例：插入正确的数据

INSERT INTO member(pid,name) VALUES (100001,'张三') ;

范例：插入重复的pid

INSERT INTO member(pid,name) VALUES (100001,'李四') ;

出现以下的错误提示：

ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005285)

范例：插入null

INSERT INTO member(pid,name) VALUES (null,'李四') ;

出现以下的错误提示：

ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."PID")

通过实验可以发现，主键就是不能重复的，并且不能为空的，但是在以上显示的时候，非空约束上显示出了错误的列，但是不能重复的唯一列上显示的是“SCOTT.SYS_C005285”，从此信息中根本就无法知道那块出现了错误。那么对于以后查找错误肯定不方便，此时，如果希望明确的知道那个地方出现了错误，则使用另外一种语法创建约束：

DROP TABLE member ;

PUGRE RECYECLEBIN ;

CREATE TABLE member(

pid NUMBER ,

name VARCHAR2(50) NOT NULL ,

CONSTRAINT member_pid_pk PRIMARY KEY(pid)

) ;

此时，已经为约束起了一个名字，则以后再违反约束的时候，将提示以下的信息：

ORA-00001: 违反唯一约束条件 (SCOTT.MEMBER_NAME_PK)

此时，显示的信息就是约束的名称，一般在建立表的时候如果有了明确要求，一定要为每一个约束起一个名字，这样以后出现问题可以快速解决。

但是，对于一张表中也可以同时设置多个主键，这种做法称为复合主键。

CONSTRAINT member_name_pk PRIMARY KEY(pid,name)

不过，正常的设计者是100%不会使用这种语法的。

3.2.3、唯一约束

唯一约束只是表示的是数据列上的内容不能重复，但是与主键约束不同的是，唯一约束中的允许有一个内容为null。

在SQL中使用UNIQUE表示唯一约束，例如：人的身份证肯定是唯一的，那么就可以使用唯一约束。

范例：建立唯一约束

DROP TABLE member ;

PUGRE RECYECLEBIN ;

CREATE TABLE member(

pid NUMBER ,

name VARCHAR2(50) UNIQUE ,

CONSTRAINT member_name_pk PRIMARY KEY(pid)

) ;

现在根本就不允许设置相同的名字。

范例：增加正确的数据

INSERT INTO member(pid,name) VALUES (10001,null) ;

INSERT INTO member(pid,name) VALUES (10002,'张三') ;

范例：插入重复的数据

INSERT INTO member(pid,name) VALUES (10003,'张三') ;

此时提示以下的错误：

ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005289)

可是，上的错误信息显示的也并不明确，那么如果需要一个明确的内容，依然要通过CONSTRAINT指定约束的名称。

DROP TABLE member ;

PUGRE RECYECLEBIN ;

CREATE TABLE member(

pid NUMBER ,

name VARCHAR2(50) ,

CONSTRAINT member_pid_pk PRIMARY KEY(pid) ,

CONSTRAINT member_name_uk UNIQUE(name)

) ;

此时，再次插入错误的内容，则可以显示如下的信息：

ORA-00001: 违反唯一约束条件 (SCOTT.MEMBER_NAME_UK)

3.2.4、检查约束

检查约束是指在数据保存的时候对数据进行条件的过滤，例如：一个人的年龄0~150岁，一个人的性别只能是男、女、中。

范例：编写检查约束

DROP TABLE member ;

PUGRE RECYECLEBIN ;

CREATE TABLE member(

pid NUMBER ,

name VARCHAR2(50) ,

age NUMBER(3) ,

sex VARCHAR2(2) ,

CONSTRAINT member_pid_pk PRIMARY KEY(pid) ,

CONSTRAINT member_name_uk UNIQUE(name) ,

CONSTRAINT member_age_ck CHECK(age BETWEEN 0 AND 150) ,

CONSTRAINT member_sex_ck CHECK(sex IN ('男','女','中'))

) ;

范例：插入错误的年龄

INSERT INTO member(pid,name,age,sex) VALUES (10001,'张三',999,'男') ;

此时，提示如下错误：

ORA-02290: 违反检查约束条件 (SCOTT.MEMBER_AGE_CK)

范例：插入错误的性别

INSERT INTO member(pid,name,age,sex) VALUES (10001,'张三',99,'未') ;

此时，提示如下错误：

ORA-02290: 违反检查约束条件 (SCOTT.MEMBER_SEX_CK)

这个时候就是对数据的增加进行了检查，合格了向里面插入。

3.2.5、主-外键约束（重点）

在五种约束中，以上的四种约束实际上都是针对于一张表的约束，那么如果现在需要做两张表的关联约束的话，就需要使用主-外键约束完成。

范例：有如下两张表

DROP TABLE member ;

DROP TABLE book ;

PUGRE RECYECLEBIN ;

CREATE TABLE member(

pid NUMBER PRIMARY KEY ,

name VARCHAR2(60) NOT NULL

) ;

CREATE TABLE book(

bid NUMBER PRIMARY KEY ,

title VARCHAR2(50) NOT NULL ,

pid NUMBER

) ;

范例：下面插入正确的数据

INSERT INTO member(pid,name) VALUES (1,'张三') ;

INSERT INTO member(pid,name) VALUES (2,'李四') ;

INSERT INTO book(bid,title,pid) VALUES (10,'JAVA 基础',1) ;

INSERT INTO book(bid,title,pid) VALUES (20,'JAVA 基础',2) ;

以上的数据存在了对应的关系，所以符合于正确的数据要求，但是此时，由于缺少了一种约束，所以以下的数据也可以插入进去：

INSERT INTO book(bid,title,pid) VALUES (30,'JAVA WEB',99) ;

此时99号成员并不存在，所以，以上的数据根本就不合法，那么这种情况一张表的数据取值范围由其他表决定，就只能通过主-外键关系完成设置。

DROP TABLE member ;

DROP TABLE book ;

PUGRE RECYECLEBIN ;

CREATE TABLE member(

pid NUMBER PRIMARY KEY ,

name VARCHAR2(60) NOT NULL

) ;

CREATE TABLE book(

bid NUMBER PRIMARY KEY ,

title VARCHAR2(50) NOT NULL ,

pid NUMBER ,

CONSTRAINT member_book_pid_fk FOREIGN KEY(pid) REFERENCES member(pid)

) ;

此时，再次插入错误的数据，将出现以下的提示：

ORA-02291: 违反完整约束条件 (SCOTT.MEMBER_BOOK_PID_FK) - 未找到父项关键字

但是，如果要想设置外键的话，有一点也必须注意，即：主表中的对应的键必须是主键。

但是，此时一个新的问题就产生了，例如：删除member表中的第1条数据，此时第1条数据对应着book表中的一条数据。

DELETE FROM member WHERE pid=1 ;

此时，出现以下的错误提示：

ORA-02292: 违反完整约束条件 (SCOTT.MEMBER_BOOK_PID_FK) - 已找到子记录

由于在book表中现在依然存在着对第一个表的外键引用，那么现在肯定无法删除父表的内容，此时，只有先将子表的数据删除之后，才能删除主表的数据。

如果现在存在了主-外键关系，那么在删除表的时候必须先删除子表再删除父表。

范例：删除主表

DROP TABLE member ;

提示以下的错误信息：

ORA-02449: 表中的唯一/主键被外键引用

此时，只有先把子表删除之后，才能再删除父表。

以上的两个问题本身设计的非常的合理，因为要保证数据的完整性，但是从操作上看就麻烦了。

1、对于删除表的操作，在删除之前还需要先找到子表再删除，麻烦。

2、如果现在假设一个父表中的数据已经消失了，则子表的数据也无效的情况下，先删子表再删除父表的话就麻烦多了。

范例：为了解决删除问题，在Oracle中增加了强制删除的操作

DROP TABLE member CASCADE CONSTRAINT ;

范例：如果现在主表中的相关记录删除了，希望子表一起删除掉，则可以设置级联删除

DROP TABLE member CASCADE CONSTRAINT ;

DROP TABLE book CASCADE CONSTRAINT ;

PUGRE RECYECLEBIN ;

CREATE TABLE member(

pid NUMBER PRIMARY KEY ,

name VARCHAR2(60) NOT NULL

) ;

CREATE TABLE book(

bid NUMBER PRIMARY KEY ,

title VARCHAR2(50) NOT NULL ,

pid NUMBER ,

CONSTRAINT member_book_pid_fk FOREIGN KEY(pid) REFERENCES member(pid) ON DELETE CASCADE

) ;

范例：删除父表数据

DELETE FROM member WHERE pid=1 ;

这个时候，可以发现，连同子表的对应数据将一起删除。

功能要求

3.4.1、建表

DROP TABLE grade ;

DROP TABLE sporter ;

DROP TABLE item ;

PURGE RECYCLEBIN ;

CREATE TABLE sporter(

sporterid NUMBER PRIMARY KEY ,

name VARCHAR2(50) NOT NULL ,

sex VARCHAR2(2) CHECK(sex IN ('男','女')) ,

department VARCHAR2(50) NOT NULL

) ;

CREATE TABLE item(

itemid VARCHAR2(4) PRIMARY KEY ,

itemname VARCHAR2(50) NOT NULL ,

location VARCHAR2(200) NOT NULL

) ;

CREATE TABLE grade(

sporterid NUMBER REFERENCES sporter(sporterid) ,

itemid VARCHAR2(4) REFERENCES item(itemid) ,

mark NUMBER

) ;

3.4.2、数据

运动员（1001, 李明，男，计算机系

1002，张三，男，数学系

1003, 李四，男，计算机系

1004, 王二，男，物理系

1005, 李娜，女，心理系

1006, 孙丽，女，数学系）

INSERT INTO sporter(sporterid,name,sex,department) VALUES (1001,'李明','男','计算机系') ;

INSERT INTO sporter(sporterid,name,sex,department) VALUES (1002,'张三','男','数学系') ;

INSERT INTO sporter(sporterid,name,sex,department) VALUES (1003,'李四','男','计算机系') ;

INSERT INTO sporter(sporterid,name,sex,department) VALUES (1004,'王二','男','物理系') ;

INSERT INTO sporter(sporterid,name,sex,department) VALUES (1005,'李娜','女','心理系') ;

INSERT INTO sporter(sporterid,name,sex,department) VALUES (1006,'孙丽','女','数学系') ;

项目（ x001, 男子五千米，一操场

x002，男子标枪，一操场

x003, 男子跳远，二操场

x004, 女子跳高，二操场

x005, 女子三千米，三操场）

INSERT INTO item(itemid,itemname,location) VALUES ('x001','男子五千米','一操场') ;

INSERT INTO item(itemid,itemname,location) VALUES ('x002','男子标枪','一操场') ;

INSERT INTO item(itemid,itemname,location) VALUES ('x003','男子跳远','二操场') ;

INSERT INTO item(itemid,itemname,location) VALUES ('x004','女子跳高','二操场') ;

INSERT INTO item(itemid,itemname,location) VALUES ('x005','女子三千米','三操场') ;

积分（

1001, x001, 6

1002, x001, 4

1003, x001, 2

1004, x001, 0

1001, x003, 4

1002, x003, 6

1004, x003, 2

1005, x004, 6

1006, x004, 4)

INSERT INTO grade(sporterid,itemid,mark) VALUES (1001,'x001',6) ;

INSERT INTO grade(sporterid,itemid,mark) VALUES (1002,'x001',4) ;

INSERT INTO grade(sporterid,itemid,mark) VALUES (1003,'x001',2) ;

INSERT INTO grade(sporterid,itemid,mark) VALUES (1004,'x001',0) ;

INSERT INTO grade(sporterid,itemid,mark) VALUES (1001,'x003',4) ;

INSERT INTO grade(sporterid,itemid,mark) VALUES (1002,'x003',6) ;

INSERT INTO grade(sporterid,itemid,mark) VALUES (1004,'x003',2) ;

INSERT INTO grade(sporterid,itemid,mark) VALUES (1005,'x004',6) ;

INSERT INTO grade(sporterid,itemid,mark) VALUES (1006,'x004',4) ;

3.4.3、要求

1、求出目前总积分最高的系名，及其积分。

· 确定要使用的表：积分在grade表中，求出系名称在sporter表中。

SELECT * FROM (SELECT s.department,SUM(g.mark) sum

FROM sporter s,grade g

WHERE s.sporterid=g.sporterid

GROUP BY s.department

ORDER BY sum DESC) temp

WHERE ROWNUM=1 ;

2、找出在一操场进行比赛的各项目名称及其冠军的姓名

SELECT s.name,g.mark,g.itemid,i.itemname

FROM sporter s,grade g,(SELECT g.itemid iid,MAX(g.mark) max

FROM grade g,item i

WHERE g.itemid=i.itemid AND i.location='一操场'

GROUP BY g.itemid) temp,item i

WHERE s.sporterid=g.sporterid AND temp.iid=g.itemid AND g.mark=temp.max

AND i.itemid=g.itemid ;

3、找出参加了张三所参加过的项目的其他同学的姓名

· 观察张三参加了那些个项目

SELECT DISTINCT s.name

FROM grade g,sporter s

WHERE g.itemid IN (SELECT g.itemid

FROM sporter s,grade g

WHERE s.name='张三' AND s.sporterid=g.sporterid)

AND s.sporterid=g.sporterid AND s.name<>'张三';

4、经查张三因为使用了违禁药品，其成绩都记0分，请在数据库中作出相应修改。

UPDATE grade SET mark=0 WHERE sporterid=(SELECT sporterid FROM sporter WHERE name='张三') ;

5、经组委会协商，需要删除女子跳高比赛项目。

DELETE FROM item WHERE itemname='女子跳高' ;

集合操作（理解）

在数学的计算操作中，应该学习过交、差、并、补的概念，在SQL语句中同样存在这种集合操作：

· UNION：将多个查询结果合并，不显示重复内容；

· UNION ALL：将多个查询结果合并，显示重复内容；

· INTERSECT：返回多个查询结果中相同的部分；

· MINUS：返回多个查询结果中不同的部分。

为了开发方便，首先建立一张只包含20部门雇员信息的表：

CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20 ;

范例：验证UNION操作

SELECT * FROM emp

UNION

SELECT * FROM emp20 ;

范例：验证UNION ALL操作

SELECT * FROM emp

UNION ALL

SELECT * FROM emp20 ;

范例：验证INTERSECT

SELECT * FROM emp

INTERSECT

SELECT * FROM emp20 ;

返回的是交集。

范例：验证MINUS

SELECT * FROM emp

MINUS

SELECT * FROM emp20 ;

对于集合操作，如果使习惯了就使，不习惯，按照最早的方式编写代码。

视图（重点）

在实际的开发中，除了表之外，视图是使用最多的，而且从实际开发来看，视图的数量绝对要比表的数量多。

视图：视图就是封装了一条复杂的SQL语句，以后直接通过查询视图，就可以完成复杂的查询功能了，创建视图使用如下的语法完成：

CREATE [OR REPLACE] VIEW 视图名称 AS 子查询 ;

范例：创建一张只包含10部门信息的视图

CREATE VIEW myview AS SELECT * FROM emp WHERE deptno=10 ;

范例：下面再将一条复杂的查询作为视图创建的语句

DROP VIEW myview ;

CREATE VIEW myview AS (SELECT s.grade,COUNT(e.empno) count,AVG(e.sal) avg

FROM emp e,salgrade s

WHERE e.sal BETWEEN s.losal AND s.hisal

GROUP BY s.grade) ;

当然，在视图的创建语法中也存在了一种替换视图的操作，如果视图已经存在了，则将其删除后重新创建，就不用手工删除了。

CREATE OR REPLACE VIEW myview AS SELECT * FROM emp WHERE deptno=10 ;

通过视图已经的确简化了开发人员的查询操作，但是在使用视图的时候也会存在更新的问题，下面通过代码来看。

上面的语法表示的是创建了一张只包含10部门雇员信息的视图，那么这个时候deptno=10就表示的是创建视图的条件，但是由于现在的视图没有任何的说明，所以，可以直接更新此条件。

范例：更新视图

UPDATE myview SET deptno=40 WHERE empno=7782 ;

更新视图之后，会连同数据表中的数据一起更新，但是这样明显不合适，因为视图的创建条件实在是不应该被更改，要更改去该具体的表，因为视图本身就是为查询准备的，那么在创建视图的时候就加入一个WITH CHEK OPTION参数。

CREATE OR REPLACE VIEW myview AS SELECT * FROM emp WHERE deptno=10 WITH CHECK OPTION ;

此时，已经无法更新视图的创建条件了，但是其他的列呢？

UPDATE myview SET sal=9999 WHERE empno=7782 ;

现在的操作同样不合适，因为视图本身就只能作为查询，此时，可以加入WITH READ ONLY参数来进行限制。

CREATE OR REPLACE VIEW myview AS SELECT * FROM emp WHERE deptno=10 WITH READ ONLY ;

WITH READ ONLY表示只读的含义，则任何的字段都无法再进行更新了。

视图和存储过程区别

存储过程涉及很多的数据处理，整个是一个复杂的过程。它可以接收参数，相当于一个函数。主要目的是用来处理数据。

而视图是把现在有数据组合成新的形式展示出来，相当于一张虚拟的表，在运行时可以当作表Table来进行查询。总之视图的目的是用来呈现数据。

序列（重点）

在Access数据库之中存在一种称为自动增长列的字段，里面的内容可以进行自动的增长操作，但是在Oracle中本身并没有直接提供这种数据类型，那么如果要想完成数据的自动增长操作，就只能使用序列（SEQUENCE）完成。

如果要想创建序列，可以使用如下的语法完成：

语法一：简便形式，使用最多的一种形式

CREATE SEQUENCE 序列名称 ;

语法二：完整形式

CREATE SEQUENCE 序列名称

INCREMENT BY 每次的增长大小

START WITH 序列的开始大小

MAXVALUE 最大值 | NOMAXVALUE

MINVALUE 最小值 | NOMINVALUE

CYCLE | NOCYCLE |

CACHE 大小 | NO CACHE ;

范例：创建序列

CREATE SEQUENCE myseq ;

如果要想删除序列直接使用DROP SEQUENCE语法即可。

一个序列创建完成之后就需要使用，在序列中存在了以下的两个变量：

· nextval：表示取得序列的下一个内容

· currval：表示取得序列当前值

范例：建立一张新的表

CREATE TABLE mytab(

next NUMBER ,

curr NUMBER

) ;

下面编写SQL语句，执行序列的插入：

INSERT INTO mytab(next,curr) VALUES (myseq.nextval,myseq.currval) ;

在默认情况下一个序列是从0开始的，而且每次增长是1，那么下面也可以修改。

DROP SEQUENCE myseq ;

CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 1 ;

在进行序列的操作中也可以使用循环序列，例如：现在有一个列的取值范围只能在1、3、5、7、9间变化，那么这个时候就需要设置循环的序列。

create sequence MYSEQ minvalue 1 maxvalue 9 start with 1 increment by 2 cache 2 cycle;

下面再次测试此序列

SELECT myseq.nextVal FROM dual ;

在Oracle的开发中，只要是自动增长的操作就是序列，序列中有两个参数：nextval、currval。

数据库的设计原则就只有一个：“数据库的表关联查询越少越好。”

同义词（理解）

回顾：之前如果要想显示系统信息查询的是dual表

SELECT sysdate FROM dual ;

但是dual是那个用户的表？因为在正常情况下，一个表的全名是“用户名.表名称”，但是dual在使用的时候并没有给出用户名。

通过查找发现，dual表实际上是属于sys用户下的，但是理论上讲，现在在访问表的时候应该是通过“sys.dual”，但是为什么这个时候却不用加上sys呢？这个实际上就是一个同义词的作用，dual是sys.dual的同义词，所以在访问的时候直接访问dual即可。

在正常情况下如果sys用户要想访问scott.emp，需要输入用户名：

SELECT * FROM scott.emp ;

下面就可以将scott.emp起一个别名，作为同义词的使用，创建同义词的操作如下：

CREATE SYNONYM 同义词名称 FOR 用户.表名称 ;

范例：创建scott.emp的同义词

CREATE SYNONYM myemp FOR scott.emp ;

以后，直接通过myemp就可以访问到scott.emp的表了。

SELECT * FROM myemp ;

删除同义词依然使用DROP语句完成：

DROP SYNONYM myemp ;

索引（理解）

所以实际上在开发中是提升查询性能的一种操作，但是只是提升了查询的性能。

在正常情况下，如果要执行一条查询语句的话，是采用逐行扫描的形式完成的，那么如果此时，假设以雇员编号为主，要求查询出雇员编号大于7800的所有雇员信息。

SELECT * FROM myemp WHERE empno>7800 ;

如果数据量大，则最好通过索引完成性能的提升，在Oracle中提供了很多的索引，包括B*Tree、位图索引。

在默认情况通过默认的语句建立的索引都是B树索引，采用BT（Binary Tree）算法，这样的话可以减少逐行扫描的操作。因为BT本身属于一种排序算法，实际上一旦建立之后就会在相关的列上将所有的数据进行排序，排序的原理如下：

一旦建立了索引之后本身也会出现问题，因为在Oracle的内部必须始终维护这样的一颗树出来，所以数据库在执行更新操作的时候性能会很低，因为要维护这课BT树，一般索引在修改不是很频繁的地方才会使用，创建BT索引的语法如下，例如：在emp的sal上创建索引

CREATE INDEX emp_sal_ind ON emp(sal) ;

提升是对于有几百万条数据之上的性能提升会很明显，但是一般对于索引的操作都是由DBA完成的。

用户管理（了解）

在Oracle中除了几个内建的用户之外，也可以根据自己的需要定义自己的用户名和密码，但是如果要想创建新用户的话，那么就需要使用DBA的管理员权限（system、sys用户）。

但是在讲解之前必须强调的是，一般开发人员很少负责此块内容，这些全部是由DBA完成。

范例：创建新用户可以使用如下的语句，创建一个mldn的用户

CREATE USER mldn IDENTIFIED BY mldnjava ;

创建了一个mldn的用户，同时此用户的密码是：mldnjava，但是很遗憾的是，一个新创建好的用户根本就无法直接使用，因为如果要想进行数据库的登陆操作，则需要“CREATE SESSION”权限，所以下面需要对用户进行授权。

范例：将CREATE SESSION的权限赋予mldn用户

GRANT CREATE SESSION TO mldn ;

此时，已经可以使用mldn用户进行登陆，但是登陆之后却什么也干不了，例如：创建一张新的表。

CREATE TABLE mytab(name VARCHAR2(50)) ;

但是，由于mldn用户本身没有任何的权限，所以现在根本就无法创建表，那么下面再把创建表的权限给mldn用户：

GRANT CREATE TABLE TO mldn ;

以上的操作太麻烦了，如果按照此种方式授权管理的话，那么肯定非常的辛苦，所以在Oracle中为了解决这种问题，专门提供了两种角色，这两种角色中包含了各种权限，角色是：CONNECT、RESOURCE，表示连接和资源的角色。

GRANT CONNECT,RESOURCE TO mldn ;

将俩功能个角色赋予之后，下面直接创建表发现已经可以成功创建了。

但是有一天发现，mldn的用户的密码丢了，DBA将mldn的用户密码修改了一下，改成了hello

ALTER USER mldn IDENTIFIED BY hello ;

但是后来又有一天，mldn的用户因为过失操作将数据库的一张重要的表删除了，为了防止他再出现错误，将此用户锁定了，管理员做了如下的操作：

ALTER USER mldn ACCOUNT LOCK ;

后来经过了一年的反思，mldn的用户发现以后可以不犯错了，所以DBA又将其解锁了。

ALTER USER mldn ACCOUNT UNLOCK ;

经过了一段使用之后，DBA为了防止某些人再丢失密码，所以将mldn的用户的密码变成无效了，这样在用户第一次登陆之后就需要修改密码。

ALTER USER mldn PASSWORD EXPIRE ;

但是这个新的用户创建完成之后，他想访问scott.emp，于是发出了如下的命令：

SELECT * FROM scott.emp ;

但是，遗憾的是现在根本就无法进行查询的操作，因为根本就没有将scott.emp表的查询权限给mldn用户。

范例：将查询和增加的权限给mldn用户

GRANT SELECT,INSERT ON scott.emp TO mldn ;

后来，这个mldn用户由于喜欢随意删除表及数据，对公司的造成很大的影响，那么决定开除了。

范例：回收scott.emp用户的权限

REVOKE SELECT,INSERT ON scott.emp FROM mldn ;

范例：回收系统权限

REVOKE CONNECT,RESOURCE,CREATE SESSION,CREATE TABLE FROM mldn ;

此时，mldn用户没有任何的权限了。既然这个人要走了，那么也没用了，删了吧。。。

DROP USER mldn CASCADE ;

数据库备份（理解）

数据库管理中最重要的部分就是数据库的备份和恢复。

数据库的备份有很多种，例如：纯粹的数据备份、归档备份（冷备份）、非归档备份。

在oracle安装完成之后会提供两个命令：exp、imp。专门负责数据的导出和导入。

1、在硬盘中建立一个文件夹，此文件夹中用于保存具体的导出数据。D:\data

2、通过命令行方式进入到此文件夹，D:\data>

3、输入exp进行数据的导出

4、下面为了验证数据可以成功的恢复，所以先将所有的表和视图都删除干净

5、在命令行方式下进入到此目录之中，输入imp，因为这个文件夹存放了导出的全部数据

在一个数据库中，以上的操作只是将一个用户的数据信息备份出来，但是在整个数据库中有很多个用户，很多个表，所以以上根本就无法在多用户下使用，如果要想进行真正的备份，则必须备份以下的几块内容：

1、控制文件，控制Oracle的各种操作环境，一般会保存在多个不同的物理磁盘上。

2、重做日志：所有的系统恢复的操作都在里面进行

3、数据文件：保存所有的具体信息的

4、参数文件：pfile、spfile

要想备份以上的内容，则首先一定要通过数据字典表进行查找，但是此时只能靠sys用户完成。

CONN sys/change_on_install AS SYSDBA ;

1、查询所有的控制文件

SELECT * FROM v$controlfile ;

当然，以上的配置文件的信息实际上都是在PFile文件中保存的。

2、找到所有的重做日志文件

SELECT * FROM v$logfile ;

3、备份所有的数据文件：

SELECT * FROM dba_data_files ;

4、备份参数文件：

show parameter pfile ;

一般对于数据库的备份操作，都是由专门的DBA负责处理的，由于Oracle的体系较为复杂，所以在以后的开发中，一般都会专门配置一个Oracle的DBA，包括DB2数据库也一样，也会专门配置一个DBA专门负责的。

一个好的DBA，基本上每天都要不断的重复数据库的备份及恢复操作。

数据库设计三范式（理解）

为了保证数据库在使用的时候性能可以更高，所以对于数据库的本身也有自己的设计要求，主要用到的就是三范式。

以下所讲解的所有设计范式都只是个参考。

第一范式

数据表中的每个数据列的内容不允许再分。

例如：下面有这样一张数据库创建脚本

CREATE TABLE mytab(

id NUMBER PRIMARY KEY ,

name VARCHAR(2) NOT NULL ,

address VARCHAR(200)

) ;

INERT INTO mytab(id,name,address) VALUES (1,'张三','北京市西城区xxx 100086 ') ;

在本设计之中，对于address列实际上可以继续划分：城市、城区、具体地址、邮政编码。

所以以上的设计不符合于第一设计范式，因为还可以继续再分。

CREATE TABLE mytab(

id NUMBER PRIMARY KEY ,

name VARCHAR(2) NOT NULL ,

city VARCHAR2(50) ,

area VARCHAR2(50) ,

address VARCHAR2(200) ,

zipcode VARCHAR2(8)

) ;

当然，本设计中不能对日期起作用，有的人设计数据库的时候将年、月、日分成了三个字段。

第二范式

某一个侯选关键字段不依赖于其他字段。

范例：下面有如下一张表，按照第一范式设计如下

CREATE TABLE selectcourse(

stuid NUMBER ,

stuname VARCHAR2(50) ,

stuage NUMBER ,

cname VARCHAR2(50) ,

credit NUMBER ,

score NUMBER

) ;

本设计符合于第一范式，因为每个字段确实无法再分了，但是这种设计存在问题：

INSERT INTO selectcourse(stuid,stuname,stuage,cname,credit,score) VALUES (1,'张三',20,'Java',2,90) ;

INSERT INTO selectcourse(stuid,stuname,stuage,cname,credit,score) VALUES (2,'李四',20,'Java',2,99) ;

INSERT INTO selectcourse(stuid,stuname,stuage,cname,credit,score) VALUES (1,'张三',20,'Oracle',1,80) ;

本设计中存在以下的问题：

1、学生编号存在重复，因为一个学生可以参加多门课程；

2、课程信息重复；

3、如果一门课程没有一个学生参加，这门课程将不再存在。

此时，按照第一范式设计的数据表存在问题太多，所以要使用第二范式去修改此设计。

CREATE TABLE student(

stuid NUMBER PRIMARY KEY,

stuname VARCHAR2(50) ,

stuage NUMBER

) ;

CREATE TABLE course(

cid NUMBER PRIMARY KEY ,

cname VARCHAR2(50) ,

credit NUMBER

) ;

CREATE TABLE studentcourse(

stuid NUMBER REFERENCES student(stuid) ON DELETE CASCADE ,

cid NUMBER REFERENCES course(cid) ON DELETE CASCADE ,

score NUMBER

) ;

如果此时按照以上的设计的话，则插入数据如下：

INSERT INTO student(stuid,stuname,stuage) VALUES (1,'张三',20) ;

INSERT INTO student(stuid,stuname,stuage) VALUES (2,'李四',20) ;

INSERT INTO course(cid,cname,credit) VALUES (1001,'Java',2) ;

INSERT INTO course(cid,cname,credit) VALUES (1002,'Oracle',1) ;

INSERT INTO course(cid,cname,credit) VALUES (1003,'WEB',2) ;

INSERT INTO studentcourse(stuid,cid,score) VALUES (1,1001,90) ;

INSERT INTO studentcourse(stuid,cid,score) VALUES (2,1001,99) ;

INSERT INTO studentcourse(stuid,cid,score) VALUES (1,1002,80) ;

以上的设计确实解决了之前的问题。

第三范式

现在看这样一种情况：一个公司有多个雇员，如果此时这种问题按照第一范式肯定无法实现，那么如果按照第二范式呢？

CREATE TABLE employy(

empid NUMBER PRIMARY KEY,

empname VARCHAR2(50) ,

empage NUMBER ,

empsal NUMBER

) ;

CREATE TABLE company(

cid NUMBER PRIMARY KEY ,

cname VARCHAR2(20)

) ;

CREATE TABLE company_employee(

empid NUMBER REFERENCES employee(empid) ON DELETE CASCADE ,

cid NUMBER REFERENCES company(cid) ON DELETE CASCADE

) ;

本设计符合于第二范式，但是问题也就来了，如果采用了这种设计的话，则意味着一个雇员可以同时在多个公司同时上班，所以发现第二范式根本就无法解决问题，那么就要使用第三范式

CREATE TABLE company(

cid NUMBER PRIMARY KEY ,

cname VARCHAR2(20)

) ;

CREATE TABLE employy(

empid NUMBER PRIMARY KEY,

empname VARCHAR2(50) ,

empage NUMBER ,

empsal NUMBER ,

cid NUMBER REFERENCES company(cid) ON DELETE CASCADE

) ;

此种设计和部门及雇员表的操作是一样的，所以这种设计属于第三范式。从实际开发来看，一对多的关系出现的是最多的。

以上的三个范式只是参考，在实际的开发中不能全部照搬，数据库的设计原则就只有一个：“数据库的表关联查询越少越好。”

数据库的设计工具：Sybase PowerDesigner

只要是数据库的设计工作，包括开发人员，肯定都会使用PowerDesginer进行数据库的设计开发的。

选择物理数据模型，同时，选择要开发的数据库。下面使用此工具还原dept和emp表。

还原出来之后，使用PowerDesigner工具的最大好处是还可以进行数据库创建脚本的生成，还可以生成测试数据。

Oracle的MERGE INTO使用

使用说明1

动机：

想在Oracle中用一条SQL语句直接进行Insert/Update的操作。

说明：

在进行SQL语句编写时，我们经常会遇到大量的同时进行Insert/Update的语句，也就是说当存在记录时，就更新(Update)，不存在数据时，就插入(Insert)。

实战：

接下来我们有一个任务，有一个表T，有两个字段a,b，我们想在表T中做Insert/Update,如果存在，则更新T中b的值，如果不存在，则插入一条记录。在Microsoft的SQL语法中，很简单的一句判断就可以了，SQL Server中的语法如下：

if exists(select 1 from T where T.a='1001' ) update T set T.b=2 Where T.a='1001' else insert into T(a,b) values('1001',2);

以上语句表明当T表中如果存在a='1001' 的记录的话，就把b的值设为2，否则就Insert一条a='100'，b=2的记录到T中。

但是接下来在Oracle中就遇到麻烦了，记得在Oracle 9i之后就有一条Merge into 的语句可以同时进行Insert 和Update的吗，Merge的语法如下：

MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
    UPDATE table_name
    SET col1 = col_val1,
        col2     = col2_val
WHEN NOT MATCHED THEN
    INSERT (column_list) VALUES (column_values);

上面的语法大家应该都容易懂吧，那我们按照以上的逻辑再写一次。

MERGE INTO T T1
USING (SELECT a,b FROM T WHERE t.a='1001') T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
UPDATE SET T1.b = 2
WHEN NOT MATCHED THEN
INSERT (a,b) VALUES('1001',2);

以上的语句貌似很对是吧，实际上，该语句只能进行更新，而无法进行Insert，错误在哪里呢？

其实在Oracle中Merge语句原先是用来进行整表的更新用的，也就是ETL工具比较常用的语法，重点是在Using上。

用中文来解释Merge语法，就是：

在alias2中Select出来的数据，每一条都跟alias1进行 ON (join condition)的比较，如果匹配，就进行更新的操作(Update),如果不匹配，就进行插入操作(Insert)。

因此，严格意义上讲，”在一个同时存在Insert和Update语法的Merge语句中，总共Insert/Update的记录数，就是Using语句中alias2的记录数。”

以上这句话也就很好的解释了在上面写的语句为何只能进行Update，而不能进行Insert了，因为都Select不到数据，如何能进行Insert呢:)

接下来要改成正确的语句就容易多了，如下：

MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN
INSERT (a,b) VALUES(T2.a,T2.b);

查询结果，OK!

注意：

如果不懂Merge语句的原理，Merge语句是一条比较危险的语句，特别是在您只想更新一条记录的时候，因为不经意间，你可能就把整表的数据都Update了一遍.....汗!!!

我曾经犯过的一个错误如下所示，大家看出来是什么问题了吗？

MERGE INTO T T1
USING (SELECT Count(*) cnt FROM T WHERE T.a='1001') T2
ON (T2.cnt>0)
WHEN MATCHED THEN
UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN
INSERT (a,b) VALUES(T2.a,T2.b);

使用说明2

Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作. MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.在Oracle 10g中MERGE有如下一些改进：

1、UPDATE或INSERT子句是可选的

2、UPDATE和INSERT子句可以加WHERE子句

3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表

4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行

语法:

MERGE HINT INTO SCHEMA . TABLE T_ALIAS

USING SCHEMA . { TABLE | VIEW | SUBQUERY } T_ALIAS

ON (CONDITION)

WHEN MATCHED THEN MERGE_UPDATE_CLAUSE

WHEN NOT MATCHED THEN MERGE_INSERT_CLAUSE / MERGE_DELETE_CLAUSE;

联想:
merge into 是特有的功能，相当于在 MSSQL中的

if exists(...)

update table

else

Insert into table.

merge into 语法不仅没有if exists语法啰嗦，而且比if exists 还要高效很多，常用来在oracle之间同步数据库表。

例子：

1、创建测试表及数据

DROP TABLE PRODUCTS;

DROP TABLE NEWPRODUCTS;

create table PRODUCTS

(

PRODUCT_ID INTEGER,

PRODUCT_NAME VARCHAR2(60),

CATEGORY VARCHAR2(60)

);

insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');

insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');

insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');

insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');

insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');

commit;

create table NEWPRODUCTS

(

PRODUCT_ID INTEGER,

PRODUCT_NAME VARCHAR2(60),

CATEGORY VARCHAR2(60)

);

insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');

insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');

insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');

insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');

commit;

2、匹配更新

MERGE INTO PRODUCTS P

USING NEWPRODUCTS NP

ON (P.PRODUCT_ID = NP.PRODUCT_ID)

WHEN MATCHED THEN

UPDATE

SET P.PRODUCT_NAME = NP.PRODUCT_NAME,

P.CATEGORY = NP.CATEGORY;

SELECT * FROM PRODUCTS;

SELECT * FROM NEWPRODUCTS;

3、不匹配插入

MERGE INTO PRODUCTS P

USING NEWPRODUCTS NP

ON (P.PRODUCT_ID = NP.PRODUCT_ID)

WHEN NOT MATCHED THEN

INSERT

(PRODUCT_ID

,PRODUCT_NAME

,CATEGORY)

VALUES

(NP.PRODUCT_ID

,NP.PRODUCT_NAME

,NP.CATEGORY);

SELECT * FROM PRODUCTS;

SELECT * FROM NEWPRODUCTS;

4、匹配带where/on更新

MERGE INTO PRODUCTS P

USING NEWPRODUCTS NP

ON (P.PRODUCT_ID = NP.PRODUCT_ID)

WHEN MATCHED THEN

UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME WHERE P.CATEGORY = NP.CATEGORY;

MERGE INTO PRODUCTS P

USING NEWPRODUCTS NP

ON (P.PRODUCT_ID = NP.PRODUCT_ID AND P.CATEGORY = NP.CATEGORY)

WHEN MATCHED THEN

UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME;

SELECT * FROM PRODUCTS;

SELECT * FROM NEWPRODUCTS;

SELECT *

FROM PRODUCTS A

INNER JOIN NEWPRODUCTS B

ON A.PRODUCT_ID = B.PRODUCT_ID

AND A.CATEGORY = B.CATEGORY;

5、匹配带where更新、插入

MERGE INTO PRODUCTS P

USING NEWPRODUCTS NP

ON (P.PRODUCT_ID = NP.PRODUCT_ID)

WHEN MATCHED THEN

UPDATE

SET P.PRODUCT_NAME = NP.PRODUCT_NAME,

P.CATEGORY = NP.CATEGORY

WHERE P.CATEGORY = 'DVD'

WHEN NOT MATCHED THEN

INSERT

(PRODUCT_ID

,PRODUCT_NAME

,CATEGORY)

VALUES

(NP.PRODUCT_ID

,NP.PRODUCT_NAME

,NP.CATEGORY) WHERE NP.CATEGORY != 'BOOKS';

SELECT * FROM PRODUCTS;

SELECT * FROM NEWPRODUCTS;

6、 ON常量表达式

MERGE INTO PRODUCTS P

USING NEWPRODUCTS NP

ON (1 = 0)

WHEN NOT MATCHED THEN

INSERT

(PRODUCT_ID

,PRODUCT_NAME

,CATEGORY)

VALUES

(NP.PRODUCT_ID

,NP.PRODUCT_NAME

,NP.CATEGORY) WHERE NP.CATEGORY = 'BOOKS';

SELECT * FROM PRODUCTS;

SELECT * FROM NEWPRODUCTS;

7、匹配删除、不匹配插入

MERGE INTO PRODUCTS P

USING NEWPRODUCTS NP

ON (P.PRODUCT_ID = NP.PRODUCT_ID)

WHEN MATCHED THEN

UPDATE

SET P.PRODUCT_NAME = NP.PRODUCT_NAME,

P.CATEGORY = NP.CATEGORY DELETE

WHERE (P.CATEGORY = 'ELECTRNCS')

WHEN NOT MATCHED THEN

INSERT

(PRODUCT_ID

,PRODUCT_NAME

,CATEGORY)

VALUES

(NP.PRODUCT_ID

,NP.PRODUCT_NAME

,NP.CATEGORY);

SELECT * FROM PRODUCTS;

SELECT * FROM NEWPRODUCTS;

8、源表为子查询(自联接)

MERGE INTO PRODUCTS P

USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1501) B

ON (B.CO <> 0)

WHEN MATCHED THEN

UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1501

WHEN NOT MATCHED THEN

INSERT

(PRODUCT_ID

,PRODUCT_NAME

,CATEGORY)

VALUES

(1501

,'KEBO'

,'NBA');

MERGE INTO PRODUCTS P

USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1508) B

ON (B.CO <> 0)

WHEN MATCHED THEN

UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1508

WHEN NOT MATCHED THEN

INSERT

(PRODUCT_ID

,PRODUCT_NAME

,CATEGORY)

VALUES

(1508

,'KEBO'

,'NBA');

SELECT * FROM PRODUCTS;

优点：

— 避免了分开更新

— 提高性能并易于使用

— 在数据仓库应用中十分有用

— 使用merge比传统的先判断再选择插入或更新快很多

需要注意的地方：

1、从语法条件上看（ ON (join condition) ），merge into也是一个危险的语法。如果不慎重使用，会将源表全部覆盖到目标表，既危险又浪费效率，违背了增量同步的原则。所以在设计表结构时，一般每条记录都有“更新时间”的字段，用目标表“最大更新时间”判断源表数据是否有更新和新增的信息。

2、更新的字段，不允许有关联条件的字段（join condition）。比如条件是 A.ID=B.ID，那么使用“SET A.ID=B.ID”将报出一个莫名其妙的提示错误。

例子

MERGE INTO t_cif_sc_relation_in T1

USING (SELECT 8888 AS use_relation_id,5555 AS info_id FROM dual) T2

ON ( T1.use_relation_id=T2.use_relation_id)

WHEN MATCHED THEN

UPDATE SET T1.info_id = T2.info_id

WHEN NOT MATCHED THEN

INSERT (use_relation_id,info_id) VALUES(T2.use_relation_id,T2.info_id);

说明

操作表t_cif_sc_relation_in，如果表中字段use_relation_id等于8888，那么就修改字段info_id为5555，如果字段use_relation_id中没有8888，那么就添加一条数据

查询很慢，建视图

建造同义词

create public synonym sn_bbk_ec_product for ec.t_ec_product;

分为ec库和ec_soar库

原来的SQL,是在ec_soar上面建造同义词来查询

SELECT

EO.ORDER_NO as orderNo,

EID.INSURED_NAME as insuredName,

EID.POLICY_NO as policyNo,

EI.insurance_id as insuranceId,

PI.STATISTICS_TIME as statisticsTime,

P.PRODUCT_NAME as productName,

EH.HOLDER_NAME as name,

EH.HOLDER_MOBILE as cel,

EI.INSURED_PREMIUM as insuredPremium,

EO.order_status as policyState,

EI.is_done as isDone,

PI.PAY_TYPE as payType

FROM

SN_BBK_EC_ORDER EO,SN_BBK_EC_PRODUCT P,SN_BBK_EC_INSURANCE EI,RL_PROY_INSURANCE PI,

SN_BBK_EC_HOLDER EH,SN_BBK_EC_INSURED EID

WHERE

PI.PROY_AGENT_ID = #{agentId,jdbcType=NUMERIC}

AND EO.ORDER_ID = EI.INSURANCE_ID

AND PI.INSURANCE_ID = EI.INSURANCE_ID

AND EI.PRODUCT_ID = P.PRODUCT_ID AND EH.HOLDER_ID=EI.HOLDER_ID

AND EID.INSURANCE_ID = EI.INSURANCE_ID

AND EO.IS_DEL = '0'

AND TO_CHAR(PI.STATISTICS_TIME,'yyyy-mm-dd')>= #{statisticsTimeFrom}

</if>

AND TO_CHAR(PI.STATISTICS_TIME,'yyyy-mm-dd')<= #{statisticsTimeTo}

</if>

AND EO.ORDER_STATUS = #{policyState,jdbcType=VARCHAR}

</if>

AND EI.IS_DONE = #{isDone,jdbcType=VARCHAR}

</if>

ORDER BY PI.STATISTICS_TIME DESC

</select>

修改之后

在ec库建造视图，说明RL_PROY_INSURANCE@dl_soar PI为ec_soar上面的表

CREATE OR REPLACE VIEW V_BBK_insurance AS

SELECT

EO.ORDER_NO as orderNo,

EID.INSURED_NAME as insuredName,

EID.POLICY_NO as policyNo,

EI.insurance_id as insuranceId,

P.PRODUCT_NAME as productName,

EH.HOLDER_NAME as name,

EH.HOLDER_MOBILE as cel,

EI.INSURED_PREMIUM as insuredPremium,

EO.order_status as policyState,

EI.is_done as isDone,

PI.STATISTICS_TIME as statisticsTime,

PI.PAY_TYPE as payType,

PI.PROY_AGENT_ID

FROM T_EC_ORDER EO,

T_EC_PRODUCT P,

T_EC_INSURANCE EI,

T_EC_HOLDER EH,

T_EC_INSURED EID,

RL_PROY_INSURANCE@dl_soar PI

WHERE

EO.ORDER_ID = EI.INSURANCE_ID

AND EI.PRODUCT_ID = P.PRODUCT_ID

AND EH.HOLDER_ID = EI.HOLDER_ID

AND EID.INSURANCE_ID = EI.INSURANCE_ID

AND PI.INSURANCE_ID = EI.INSURANCE_ID

AND EO.IS_DEL = '0'

AND PI.PROY_AGENT_ID is not null

在ec_soar执行SQL

SELECT

V.orderNo,

V.insuredName,

V.policyNo,

V.insuranceId,

V.productName,

V.name,

V.cel,

V.insuredPremium,

V.policyState,

V.isDone,

V.statisticsTime,

V.payType

FROM V_BBK_insurance@dl_ec V

WHERE V.PROY_AGENT_ID = #{agentId,jdbcType=NUMERIC}

AND TO_CHAR(V.statisticsTime,'yyyy-mm-dd')>= #{statisticsTimeFrom}

</if>

AND TO_CHAR(V.statisticsTime,'yyyy-mm-dd')<= #{statisticsTimeTo}

</if>

AND V.policyState = #{policyState,jdbcType=VARCHAR}

</if>

AND V.isDone = #{isDone,jdbcType=VARCHAR}

</if>

ORDER BY V.statisticsTime DESC

</select>

在ec创建DL_SOAR 的DBLINK

-- Create database link

create database link DL_SOAR

connect to EC_SOAR

using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.168.21)(PORT=1521)))(CONNECT_DATA=

(SERVICE_NAME=EC)))';

在ec_soar创建dl_ec的DBLINK

-- Create database link

create database link DL_EC

connect to EC

using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.168.21)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=EC)))';

创建同义词

-- Create the synonym

create or replace public synonym SN_BBK_COMP_INFO

for TB_COMP_INFO@DL_SOAR;

create public synonym sn_bbk_ec_product for ec.t_ec_product;

可以直接查询

select * from sn_bbk_ec_product;

表的创建及管理（重点）

此操作属于DDL，属于定义了数据库的操作对象，在讲解之前先回顾一下，一个完整的表名称格式：“用户名.表名称”，也强调，用户名也可以称为模式（Schema）。如果现在直接使用一个用户登陆，则默认创建的表就是此用户下的表，其他用户要是想访问，则肯定需要通过“用户名.表名称”的形式访问。

(1)主要的数据类型

在数据库中主要是用来保存数据的，那么所有的数据实际上都存在其相应的数据类型，以后再执行更新操作的时候，操作的类型必须与设置表的类型相统一。

No.	数据类型	描述
1	VARCHAR2(n)	表示存放的是字符串数据，其中n表示存放的长度
2	NUMBER(n)	表示存放的内容是整数，其中n表示数字的最大位数，可以使用INT代替
3	NUMBER(m,n)	表示存放的内容是小数，其中小数位是n位，整数m-n位，可以使用FLOAT代替
4	DATE	存放的是日期，必须按照标准格式存放
5	CLOB	大文本数据，用于保存海量文字信息，最多可以保存4G
6	BLOB	二进制大数据，可以保存文字、图片、电影，最多可以保存4G

其中对于BLOB保存电影、图片的形式比较少见。

(2)创建表

在SQL语句中，可以使用如下的语法进行表的创建操作：

CREATE TABLE 表名称(

字段名称字段类型 [DEFAULT 默认值] ,

字段名称字段类型 [DEFAULT 默认值]

) ;

但是，在编写表名称的时候也需要注意，如果是oracle关键字的话，那么肯定无法使用。下面利用此语法创建一个member（成员）的表，表的主要字段如下：

· pid：表示一个人员的编号，使用NUMBER表示

· name：表示姓名，使用VARCHAR2()表示即可

· birthday：表示一个人的生日，生日肯定是DATE型数据

范例：创建member表

CREATE TABLE scott.member(

pid NUMBER(6) ,

name VARCHAR2(50) DEFAULT '无名氏' ,

birthday DATE DEFAULT sysdate

) ;

表创建完成之后，下面向表中增加两条记录：

INSERT INTO member(pid,name,birthday) VALUES (100001,'张三',TO_DATE('1987-09-19','yyyy-mm-dd')) ;

INSERT INTO member(pid,birthday) VALUES (100002,TO_DATE('1987-09-19','yyyy-mm-dd')) ;

INSERT INTO member(pid) VALUES (100003) ;

(3)复制表

在Oracle中复制表可以使用CREATE TABLE完成，但是在编写的时候需要编写一个子查询。

范例：复制member表

CREATE TABLE mymember1 AS SELECT * FROM member ;

此时，是将表的结构和数据一起进行复制，但是如果现在只复制表结构，而不复制数据的话，则在子查询的编写中就可以写一个永远不可能实现的条件。

CREATE TABLE mymember2 AS SELECT * FROM member WHERE 1=2 ;

(4)删除表

如果一张表不再使用的话，则可以直接删除，但是需要注意的是，表的删除数据删除对象，是不能依靠事务恢复的，语法如下：

DROP TABLE 表名称 ;

在任何的数据库之中，都不会提供删除全部表的操作。

范例：删除mymember1和mymember2的表

DROP TABLE mymember1 ;

DROP TABLE mymember2 ;

(5)Oracle对表的管理支持（了解）

在Oracle中可以直接为一个表重命名，使用语法如下：

RENAME 旧的表名称 TO 新的表名称 ;

范例：将member的表名称修改为person

RENAME member TO person ;

在正常情况下，一张表中的数据如果使用了DELETE删除之后是可以通过事务恢复的。

但是，如果在开发中使用了截断表的操作，则是不可能依靠事务恢复的，因为一旦截断之后，此表上的所有资源都将消失，例如：相关的索引、约束等等都将消失，截断表的操作如下：

TRUNCATE TABLE 表名称 ;

范例：将person表截断

TRUNCATE TABLE person ;

(6)修改表结构（了解）

在SQL语句中可以对一个已经存在的表修改其结构，但是从实际的开发来看，这种做法并不明智，是100%不推荐的。

如果在不推荐使用的情况下依然要修改表结构的话，则有以下两种方式完成：

· 如果是还没有使用的项目，直接把删了重新建

· 如果是已经使用的项目，则可以再单独增加一个表，用于保存新的数据列

IBM DB2是世界上性能最好的数据库，在DB2数据库之中根本就不允许去修改表结构。如果要想修改表结构，则要使用如下的语法完成：

语法一：在已有的表中增加新的列

ALTER TABLE 表名称 ADD (字段名称字段类型 DEFAULT 默认值) ;

范例：在person表中增加一个性别的列，默认是“男”

ALTER TABLE person ADD (sex VARCHAR2(2) DEFAULT '男') ;

现在由于已经指定了默认值，则所有的数据都会自动修改为默认值进行显示，而如果增加的列本身没有默认值的话，那么则内容就是null。

ALTER TABLE person ADD (address VARCHAR2(200)) ;

语法二：修改已有的列 —— 当发现表中的某个列的长度或类型不合适的时候，可以进行修改

ALTER TABLE 表名称 MODIFY(字段名称字段类型 DEFAULT 默认值) ;

范例：修改sex列，让其默认值是“女”

ALTER TABLE person MODIFY (sex VARCHAR2(20) DEFAULT '女') ;

下面向表增加一条新的数据：

INSERT INTO person(pid) VALUES (100005) ;

(7)闪回技术（了解）

Flashback是在Oracle 10g之后增加的一个新功能，目的是为了保证误删除表所带来的危害。

在之前使用DROP删除表的时候会发现存在一个临时文件：BIN$+O+aoVI8RJaLoJqYmAvObQ==$0

在Oracle 10g之后所有的表实际上删除的时候都被放回到了回收站里去。

范例：查看回收站

SHOW RECYCLEBIN ;

从回收站中可以发现，由于之前删除了三张表，所以现在在回收站中就存在了三张表的临时文件，既然存在的话，那么就可以直接通过回收站进行还原，还原一张表的语法如下：

FLASHBACK TABLE 表名称 TO BEFORE DROP ;

范例：恢复person表

FLASHBACK TABLE person TO BEFORE DROP ;

如果现在要删除一张表但是不希望放回到回收站之中的话，可以在删除表的时候增加一个选项：

DROP TABLE person PURGE ;

如果现在希望从回收站中删除一张表，则可以使用如下的语句：

PURGE TABLE mymember1 ;

如果现在希望清空回收站的话，则可以使用如下的语句完成：

PURGE RECYCLEBIN ;

查找到被锁的表，并解锁

select 'alter system kill session x'||sid||','||serial#||'x' from (SELECT A.OWNER 方案名,

A.OBJECT_NAME 表名,

B.XIDUSN 回滚段号,

B.XIDSLOT 槽号,

B.XIDSQN 序列号,

B.SESSION_ID 锁表SESSION_ID,

B.ORACLE_USERNAME 锁表用户名,

decode(D.type,

'XR',

'NULL',

'RS',

'SS(Row-S)',

'CF',

'SS(Row-S)',

'TM',

'TABLE LOCK',

'PW',

'TABLE LOCK',

'TO',

'TABLE LOCK',

'TS',

'TABLE LOCK',

'RT',

'ROW LOCK',

'TX',

'ROW LOCK',

'MR',

'S(Share)',

NULL) 锁定方式,

C.MACHINE 用户组,

C.TERMINAL 机器名,

B.OS_USER_NAME 系统用户名,

B.PROCESS 系统进程id,

DECODE(C.STATUS, 'INACTIVE', '不活动', 'ACTIVE', '活动') 活动情况,

C.SERVER,

C.SID,

C.SERIAL#,

C.PROGRAM 连接方式,

C.LOGON_TIME

FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C, v$lock d

WHERE (A.OBJECT_ID = B.OBJECT_ID)

AND (B.PROCESS = C.PROCESS)

and C.sid = d.sid

and B.LOCKED_MODE = D.LMODE

ORDER BY 1, 2)

再执行

alter system kill session '70,46431';

case when说明

select case

when t.product_types='00' then '小贷产品'

when t.product_types='01' then '阳阳贷款'

when t.product_types='02' then '豪车贷款'

end

from BS_PRODUCT t

查询结果如下

本文链接：https://blog.csdn.net/forrestzsb/article/details/81875591

原作者删帖不实内容删帖广告或垃圾文章投诉

智能推荐

AVFrame&AVPacket_天天av-程序员宅基地

文章浏览阅读1.5w次。AVFrame：（ This structure describes decoded (raw) audio or video data. AVFrame must be allocated using av_frame_alloc(). Note that this only allocates the AVFrame itself, the buffers for the data mus_天天av

Java经典例题07：用100元人民币兑换10元、5元、1元的纸币_编程把100元换成1元5元10元-程序员宅基地

文章浏览阅读3.5k次，点赞2次，收藏12次。解题思路分析：1.100元兑换10元纸币，可以兑换10张，但每种纸币都要有，所以最多只能兑换9张，最少兑换1张。则初始值为1；循环条件小于10或者小于等于9。2.100元兑换5元纸币，可以兑换20，但每种纸币都要有，所以最多只能兑换19张，最少兑换1张。初始值为1；循环条件小于20或者小于等于19。3.100元兑换1元纸币，可以兑换100张，但每种纸币都要有，所以最多只能兑换99张，最少兑换1张。则初始值为1；循环条件小于100或者小于等于99。_编程把100元换成1元5元10元

猜三次年龄_找人猜三次年龄-程序员宅基地

文章浏览阅读450次。1、允许用户最多尝试三次2、每尝试三次后，如果还没猜对，就问用户是否继续玩，如果回答Y，y，就继续猜三次，以此往复，如果回答N，n，就直接退出times=0count=3while times<=3:age=int(input(‘请输入年龄：’))if age == 18:print(‘猜对了’)breakelif age > 18:print(‘猜大了’)else:print(‘猜小了’)times+=1if times3:choose = input(‘继续猜Y_找人猜三次年龄

SDOI2017 Round2 详细题解-程序员宅基地

文章浏览阅读152次。这套题实在是太神仙了。。做了我好久。。。好多题都是去搜题解才会的 TAT。剩的那道题先咕着，如果省选没有退役就来填吧。「SDOI2017」龙与地下城题意丢 $Y$ 次骰子，骰子有 $X$ 面，每一面的概率均等，取值为 $[0, X)$ ，问最后取值在 $[a, b]$ 之间的概率。一个浮点数，绝对误差不超过 $0.013579$ 为正确。数据范围每组数据有 \...

嵌入式数据库-Sqlite3-程序员宅基地

文章浏览阅读1.1k次，点赞36次，收藏25次。阅读引言：本文将会从环境sqlite3的安装、数据库的基础知识、sqlite3命令、以及sqlite的sql语句最后还有一个完整的代码实例，相信仔细学习完这篇内容之后大家一定能有所收获。

C++ Builder编写WinForm从Web服务器下载文件-程序员宅基地

文章浏览阅读51次。UnicodeString templateSavePath = ChangeFileExt(ExtractFilePath(Application->ExeName),"tmp.doc");IdAntiFreeze1->OnlyWhenIdle = false;//设置使程序有反应.TMemoryStream *templateStream ;templateStre..._c++webserver下载文件