数据库oracle笔记2.1、上次课程的主要知识点

1、1、课程名称：Oracle2、知识点2.1、上次课程的主要知识点1、 Oracle 中提供的三个主要用户：超级管理员：sys/chang_on_install普通管理员：system/manager普通用户：scott/tiger，此用户在使用的时候需要先进行第（1）页2、SQLPLUSW 命令：设置行显示长度：SET LINESIZE 长度设置页显示行数：SET PAGESIZE 行数ed、/查询语句：3、4、单行函数： TO_CHAR()函数、日期函数、NVL()、DECODE()2.2、本次预计讲解的知识点1、2、3、4、5、多表查询，SQL:1999 语法对多表查询的支持分组统计及统计

2、函数的使用子查询，并结合多表查询，分组统计做复杂查询数据库的更新操作事务处理及数据库死锁3、具体内容3.1、多表查询3.1.1、多表查询的基本语法（重点）之前在查询的时候查询的都是一表查询语法如下：的数据，如果现在使用一张以上的表，则就称为多表查询，多范例：下面使用了多表查询，同时查询 emp 和 dept 表第（2）页SELECT * FROM emp,dept ;SELECT DISTINCT * | 查询列 1 别名 1,查询列 2 别名 2, FROM 表名称 1 别名 1 , 表名称 2 别名 2 ,WHERE 条件(s)ORDER BY 排序字段 ASC|DESC , 排序字段 A

3、CS | DESC ,SELECT DISTINCT * | 查询列 1 别名 1,查询列 2 别名 2, FROM 表名称 别名WHERE 条件(s)ORDER BY 排序字段 ASC|DESC , 排序字段 ACS | DESC ,以上的查询使用了两进行同时查询。但是，从查询结果上可以发现，返回的数据是 56 条，但是 emp 表一共才 14 条，dept 表一共才 4 条。范例：查询 emp 表中的数范例：查询 dept 表中的数56 条= emp 表的 14 条* dept 表的 4 条。在使用多表查询的时候会产生积。如果表的数据越多，那么积就会越到。如果现在假，100005。所以多表

4、查询在开发中基本上是不建议过多的使用。设有 5，每有 100000 条要想去掉积则必须使用字段进行关联的操作。第（3）页SELECT COUNT(*) FROM dept ;SELECT COUNT(*) FROM emp ;在 emp 表中存在一个 deptno 的字段，在 dept 表中也存在 deptno 的字段，而且可以发现 emp 表中的deptno 的取值范围都在 dept 表的 deptno 规定的范围之中。emp 中的 deptno = dept 中的 deptno，属于关联字段。在多表查询中加入 WHERE 语句，就可以消除掉范例：修改之前的查询操作积。第（4）页SELECT

5、 * FROM emp,deptWHERE emp.deptno=dept.deptno ;此时，只有 14 行了，消除掉了积。但是此时又存在一个新的问题，如果现在表名称过长的话？所以在使用的时候一般会为表起别名。如果要使用到多表查询的时候，最好指定别名。范例：要求查询出雇员的、雇员的、部门的、部门名称及部门位置第（5）页SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept dWHERE e.deptno=d.deptno ;SELECT * FROM emp e,dept dWHERE e.deptno=d.deptno

6、;范例：要求查询出每个雇员的、工作、雇员的直接的在 emp 表中的 mgr 字段使用没有使用过，其表示一个雇员的的。那么如果现在要想查，则肯定要将 emp 表与 emp 表自己进行关联。询一个雇员的范例：要求进一步扩展之前的程序，将雇员所在的部门名称同时列出 部门名称在 dept 表中存在定义。第（6）页SELECT e.ename,e.job,m.ename FROM emp e,emp mWHERE e mgr=m.empno ;思考：现在要求查询出每个雇员的名及工资所在公司的等级。、工资、部门名称，工资在公司的等级（salgrade），及其的姓先确定工资等级表的内容（查询 salgrad

7、e 表）分解：查询出每个雇员的姓名、工资、部门名称，工资在公司的等级（salgrade）第（7）页SELECT e.ename,e.sal,d.dname,s.grade FROM emp e,dept d,salgrade sWHERE e.deptno=d.deptno AND e.sal BETN s.losal AND s hisal;SELECT * FROM salgrade ;SELECT e.ename,e.job,m.ename,d.dname FROM emp e,emp m,dept dWHERE e mgr=m.empno AND e.deptno=d.deptno ;

8、此时，雇员的工资等级已经求出来了。分解：其的及工资所在公司的等级第（8）页SELECT e.ename,e.sal,d.dname,s.grade,m.ename,m.sa.grade FROM emp e,dept d,salgrade s,emp m,salgrade msWHERE e.deptno=d.deptno ANDe.sal BETN s.losal AND s hisal AND e mgr=m.empno AND m.sal BETN ms.losal AND ms hisal;进一步思考：现在要求按照以下的样式显示工资等级1：第五等工资2：第四等工资3：第三等工资4：第二

9、等工资5：第一等工资此时肯定只能使用 DECODE()函数第（9）页SELECT e.ename,e.sal,d.dname,DECODE(s.grade,1,第五等工资,2,第四等工资,3,第三等工资,4,第二等工资,5,第一等工资),m.ename,m.sal,DECODE(ms.grade,1,第五等工资,2,第四等工资,3,第三等工资,4,第二等工资,5,第一等工资)FROM emp e,dept d,salgrade s,emp m,salgrade ms WHERE e.deptno=d.deptno ANDe.sal BETN s.losal AND s hisal AND e

10、mgr=m.empnoAND m.sal BETN ms.losal AND ms hisal;3.1.2、左、右连接（重点）现在在 dept 表中存在四条数据：现在将 emp 和 dept 表关联查询，查询一下指定的字段部门表中一共存在四个部门的信息，但是此时只列出了 3 个。因为在雇员表中并没有指定 40 部门的雇员。第（10）页SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept dWHERE e.deptno(+)=d.deptno ;SELECT e.empno,e.ename,d.deptno,d.dname,d

11、.loc FROM emp e,dept dWHERE e.deptno=d.deptno ;SELECT * FROM dept ;可以发现，此时 40 部门已经出现了。所以，此时就使用到了右连接，证明以下的规律：(+)在=左边表示右连接(+)在=右边表示左连接以上的（+）写与不写都是一样的，肯定是无法显示 40 部门信息的。左、右连接在一般的开发中使用较多，实际上之前的查找雇员使用左、右连接了。及每一位雇员的时候就应该第（11）页SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept dWHERE e.deptno=d.d

12、eptno(+) ;范例：查找雇员的、及其的、（A）未加左连接（B）加入左连接发现查询出来的结果缺少了以下的一行：因为其是最高，所以 mgr 为空。加入左连接之后可以发现 KING 出现了。3.3.3、SQL:1999 语法对SQL的支持（了解）SQL:1999 语法格式：范例：交叉连接（CROSS JOIN）：产生积第（12）页SELECT * FROM emp CROSS JOIN dept ;SELECT table1.column,table2.column FROM table1 CROSS JOable2| NATURAL JOable2|JOable2 USING(column_

13、name)|JOable2 ON(table1.column_name=table2.column_name)|LEFT|RIGHT|FULL OUTER JOable2 ON(table1.column_name=table2.column_name);SELECT e.empno,e.ename,m.empno,m.ename FROM emp e,emp mWHERE e mgr=m.empno(+) ;7839 KING17-11 月-81500010SELECT e.empno,e.ename,m.empno,m.ename FROM emp e,emp mWHERE e mgr =

14、 m.empno ;查询出了 56 行，所以已经产生了积。范例：自然连接（NATURAL JOIN）：自动进行关联字段的匹配范例：USING 子句：直接关联的操作列第（13）页SELECT * FROM emp e JOIN dept d USING(deptno)WHERE deptno=30 ;SELECT * FROM emp NATURAL JOIN dept ;范例：ON 子句，用户自己编写连接的条件范例：左连接（左外连接）、右连接（右外连接）：LEFT JOIN，RIGHT JOIN第（14）页SELECT e.ename,d.deptno,d.dname,d.loc FROM e

15、mp e RIGHT OUTER JOIN dept dON(e.deptno=d.deptno) ;SELECT * FROM emp e JOIN dept d ON(e.deptno=d.deptno)WHERE deptno=30 ;3.2、组函数及分组统计（重点）分组：例如，把所有的男生分为一组，如果想求出每一组的平均身高，平均分为一组。等，这就需要使用分组函数。3.2.1、组函数在 SQL 中常用的组函数有以下几个：COUNT()：求出全部的数MAX()：求出一组中的最大值 MIN()：求出最小值AVG()：求出平均值SUM()：求和范例：COUNT()函数（A）COUNT()函数

16、(B) MIN()函数范例：MAX()、MIN()，求最大最小值，一般是针对于数字的应用上。 求出所有员工的最低工资 求出所有员工的最高工资（A）MAX()函数（B）SUM()函数第（15）页SELECT MAX(sal) FROM emp ;SELECT MIN(sal) FROM emp ;SELECT COUNT(empno) FROM emp ;范例：求和及平均值 求出 20 部门中的总工资： 求出所有员工的平均工资3.2.2、分组统计要想使用分组统计，则首先应该固定其语法，使用 GROUP BY 进行分组，此时 SQL 语法格式如下：范例；求出每个部门的雇员数量。肯定应该按照部门划分

17、，按 deptno 分组范例：求出每个部门的平均工资第（16）页SELECT deptno,AVG(sal) FROM empGROUP BY deptno ;SELECT deptno,COUNT(empno) FROM empGROUP BY deptno ;SELECT DISTINCT * | 查询列 1 别名 1,查询列 2 别名 2, FROM 表名称 1 别名 1 , 表名称 2 别名 2 ,WHERE 条件(s)GROUP BY 分组条件ORDER BY 排序字段 ASC|DESC , 排序字段 ACS | DESC ,SELECT AVG(sal) FROM emp ;SEL

18、ECT SUM(sal) FROM emp WHERE deptno=20 ;：观察以下代码以上代码不能正确执行，是因为：1、 如果程序中使用了分组函数，则有两种可以使用的情况：程序中存在了 GROUP BY，并指定了分组条件，这样可以将分组条件一起查询出来。如果不使用分组的话，则只能单独的使用分组函数。2、 在使用分组函数的时候，不能出现分组函数和分组条件之外的字段。此时，提示 empno 不是 GROUP BY 表达式，所以无法使用。第（17）页SELECT deptno,empno,COUNT(empno) FROM empGROUP BY deptno ;SELECT deptno,C

19、OUNT(empno) FROM emp ;范例：按部门分组，并显示部门的名称，及每个部门的员工数范例：要求显示出平均工资大于 2000 的部门 条件：AVG(sal) 2000和平均工资分组函数只能在分组中使用，不允许在 WHERE 语句之中出现，那么如果现在假设要指定分组的条件，则只能通过第二种条件的指令：ING，此时的 SQL 语法格式：第（18）页SELECT DISTINCT * | 查询列 1 别名 1,查询列 2 别名 2, FROM 表名称 1 别名 1 , 表名称 2 别名 2 ,WHERE 条件(s)GROUP BY 分组条件 ING 分组条件ORDER BY 排序字段 A

20、SC|DESC , 排序字段 ACS | DESC ,SELECT deptno,AVG(sal) FROM empWHERE AVG(sal)2000GROUP BY deptno ;SELECT d.dname,COUNT(e.empno) FROM dept d,emp eWHERE d.deptno=e.deptnoGROUP BY d.dname ;范例：使用ING 完成以上的操作范例：显示非销售工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列：1、显示全部的非销售：jobSALESMAN2、按工作分组

21、，同时求出工资的总和第（19）页SELECT job,SUM(sal) FROM empWHERE jobSALESMANGROUP BY job ;SELECT *FROM empWHERE jobSALESMAN ;SELECT deptno,AVG(sal) FROM empGROUP BY deptnoING AVG(sal)2000 ;3、对分组的条件进行限制，工资总和大于 50004、使用排序，按升序排序分组的简单原则： 只要一列上存在重复的内容才有可能考虑到分组第（20）页SELECT job,SUM(sal) su FROM empWHERE jobSALESMANGROUP

22、BY jobING SUM(sal)5000 ORDER BY su ;SELECT job,SUM(sal) FROM empWHERE jobSALESMANGROUP BY jobING SUM(sal)5000 ;注意： 分组函数可以嵌套使用，但是在组函数嵌套使用的时候不能再出现分组条件的查询语句。范例：求出平均工资最高的部门工资错误的代码：3.3、子查询子查询：在一个查询的内部还包括另外一个查询，则此查询称为子查询，子查询的格式：第（21）页SELECT DISTINCT * | 查询列 1 别名 1,查询列 2 别名 2, FROM 表名称 1 别名 1 , 表名称 2 别名 2

23、,(SELECT DISTINCT * | 查询列 1 别名 1,查询列 2 别名 2, FROM 表名称 1 别名 1 , 表名称 2 别名 2 ,WHERE 条件(s)GROUP BY 分组条件 ING 分组条件ORDER BY 排序字段 ASC|DESC , 排序字段 ACS | DESC ,) 别名,WHERE 条件(s)SELECT MAX(AVG(sal)FROM empGROUP BY deptno ;SELECT deptno,MAX(AVG(sal) FROM empGROUP BY deptno ;范例：要求查询出比 7654 工资要高的全部雇员的信息要首先清楚的知道 76

24、54 雇员的工资是多少之后要以以上的结果最为后续查询的依据，只要是其他的工资大于 sal，则表示符合条件。所有的子查询必须在“()”中编写代码。子查询在操作中有分为以下三类：单列子查询：返回的结果是一列的一个内容，出现几率最高单行子查询：返回多个列，有可能是一条完整的多行子查询：返回多条第（22）页SELECT * FROM empWHERE sal(SELECT sal FROM emp WHERE empno=7654) ;SELECT sal FROM emp WHERE empno=7654 ;(SELECT DISTINCT * | 查询列 1 别名 1,查询列 2 别名 2, FR

25、OM 表名称 1 别名 1 , 表名称 2 别名 2 ,WHERE 条件(s)GROUP BY 分组条件 ING 分组条件ORDER BY 排序字段 ASC|DESC , 排序字段 ACS | DESC ,)GROUP BY 分组条件 ING 分组条件ORDER BY 排序字段 ASC|DESC , 排序字段 ACS | DESC ,范例：要求查询出工资比 7654 高，同时与 7788 从事相同工作的全部雇员信息。查询出 7654 的工资：（A）查询出 7654 的工资（B）查询出 7788 的工作查询出 7788 的工作之后把两个条件进行综合查找范例：要求查询出工资最低的雇员 求出最低工资

26、：、工作、工资 以最低工资为条件进行下一步的查询第（23）页SELECT * FROM emp WHERE sal=(SELECT MIN(sal) FROM emp) ;SELECT MIN(sal) FROM emp ;SELECT * FROM empWHERE sal(SELECT sal FROM emp WHERE empno=7654) AND job=(SELECT job FROM emp WHERE empno=7788) ;SELECT job FROM emp WHERE empno=7788 ;SELECT sal FROM emp WHERE empno=7654

27、;思考：要求查询出：部门名称、部门的员工数，部门的平均工资，部门的最低收入雇员的程序需要两关联：dept、emp1、如果要想求出每个部门的员工数量，及平均工资，则肯定要使用分组统计，按 deptno 进行分组，2、但是如果要想查出来部门的名称，则需要与 dept 表进行关联。第（24）页SELECT d.dname,ed.c,ed.a FROM dept d,(SELECT deptno,COUNT(empno) c,AVG(sal) a FROM empGROUP BY deptno) edWHERE d.deptno=ed.deptno ;SELECT deptno,COUNT(empno

28、),AVG(sal) FROM empGROUP BY deptno ;3、求出最低收入的雇员如果此时，在一个部门之中同时存在两个工资最低的雇员，则程序就会出现错误。在子查询中，存在以下三种查询的操作符号：INANYALLIN 操作符，指定一个查询的范围范例：求出每个部门的最低工资的雇员信息 每个部门的最低工资，返回的值肯定是多个，所以此时可以使用 IN 指定一个操作的范围。第（25）页SELECT * FROM empWHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno) ;SELECT d.dname,ed.c,ed.a,e.enam

29、e FROM dept d,(SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) min FROM empGROUP BY deptno) ed,emp eWHERE d.deptno=ed.deptno AND e.sal=ed min ;ANY 操作：=ANY：与 IN 的操作符功能完全一样ANY：比里面最小的值要大ANY：比最大的值要小所有人的工资都小于 1300。第（26）页SELECT * FROM empWHERE sal ANY (SELECT MIN(sal) FROM emp GROUP BY deptno) ;SELECT *

30、FROM empWHERE sal =ANY (SELECT MIN(sal) FROM emp GROUP BY deptno) ;ALL 操作：ALL：比最大的值要大ALL：比最小的值要小对于子查询来讲，还可以进行多列子查询，一个子查询中同时返回多个查询的列。第（27）页SELECT *FROM empWHERE (sal,NVL(comm,-1) IN (SELECT sal,NVL(comm,-1) FROM emp WHERE deptno=20) ;SELECT * FROM empWHERE sal ALL (SELECT MIN(sal) FROM emp GROUP BY d

31、eptno) ;3.4、数据库更新操作（重点）数据库的主要操作分为两种：数据库的查询操作：SELECT数据库的更新操作：INSERT（增加）、UPDATE（修改，更新）、DELETE（删除）此时，为了保存原始的 emp 表的信息，在进行增加、修改、删除操作之前先将此表一份： 此语句执行之后，会将表结构及数据完整的出来。此时，数据已经出来。3.4.1、添加数据添加数据的语法：范例：为 myemp 表中添加一条新的 按照标准的做法完成（）第（28）页INSERTO myemp(empno,ename,job,mgm,deptno)VALUES (7899,清洁工,7369,14-2 月-1995,

32、9000,300,40) ;INSERTO 表名称(字段名称 1，字段名称 2,) VALUES (值 1,值 2,.) ;SELECT * FROM myemp ;CREATE TABLE myemp AECT * FROM emp ;创建完成之后，下面进行查询操作，观察是否已经在 myemp 表之中：必须再次提醒的是： 对于数字，不用加“”，而字符串必须加上“”。 使用简略写法（并不），因为现在是要添加所有字段的内容，所以此处可以不写上任何的字段名称，只要值的数量及顺序与数据表中的一致即可。范例：现在要求一个新的雇员，但是此雇员暂时没有，没有奖金 第一种做法：不明确的写出要的字段第（29）

33、页INSERTO myemp(empno,ename,job,hiredate,sal,deptno)VALUES (7889,清洁工,14-2 月-1995,9000,40) ;INSERTO myemp VALUES (8899,清洁工,7369,14-2 月-1995,9000,300,40) ;SELECT * FROM myemp ;从查询结果中可以发现，新的数据里面没有，没有奖金 第二种做法：如果时没有明确的写出字段名称的话，则使用 null 表示其具体内容。之前数据的时候，日期的格式是使用了表中固定好的一种格式，如果现在有这样的一个日期“2009-01-16”日期格式，那么现在如

34、何把这种格式的日期进去呢？ 使用 TO_DATE()函数，将一个字符串类型的数据变为 DATE 类型的数据。第（30）页INSERTO myemp(empno,ename,job,hiredate,sal,deptno)VALUES (8888,保洁工,TO_DATE(2009-01-16,yyyy-mm-dd),9000,40) ;INSERTO myemp VALUES (8889,清洁工,null,14-2 月-1995,9000,null,40) ;3.4.2、修改数据在 SQL 语法中使用 UPDATE 语句就可以完成数据的修改功能，此语句的语法如下： 但是，从一般的开发角度上将，修

35、改操作一般都加入修改条件。范例：现在将 myemp 表中的所有雇员的佣金修改为 1000 修改全部从运行结果中可以发现，一共更新了 19 行，下面查询 myemp 表。第（31）页UPDATE myemp SET comm=1000 ;修改全部：UPDATE 表名称 SET 要修改的字段=新值,要修改的字段=新值,. ;修改局部：UPDATE 表名称 SET 要修改的字段=新值,要修改的字段=新值,. WHERE 修改条件 ;例如：将为 7899 的雇员的工资修改为 5000 指定更新条件第（32）页UPDATE myemp SET sal=5000 WHERE empno=7899;范例：将

36、为 7899 的雇员的取消： 在进行数据库更新的操作时，一定要写上更新的条件，批量更新。范例：将 7369、8899、7788 的及奖金取消第（33）页UPDATE myemp SETm=null WHERE empno IN (7369,8899,7788);UPDATE myemp SET mgr=null WHERE empno=7899;3.4.3、删除数据在 SQL 语句中可以使用 DELETE 命令删除，语法格式如下：范例：删除是 7899 的雇员信息范例：删除是 8899、7889、8889、8888 的雇员信息第（34）页DELETE FROM myemp WHERE empn

37、o IN (8899,7889,8889,8888) ;DELETE FROM myemp WHERE empno=7899 ;删除全部：DELETE FROM 表名称 ;删除局部：DELETE FROM 表名称 WHERE 删除条件 ;范例：删除掉全部领取奖金的雇员范例：删除表的全部内容 删除的时候不指定删除条件，则表示删除表中的全部数以上的删除全部的做法，开发中一般不是很建议，基本上在执行删除命令的时候都要指定删除条件。3.5、事务处理范例：创建一个只包含 10 部门雇员的一张临时表范例：删除emp10 表中的 7782 雇员第（35）页DELETE FROM emp10 WHERE em

38、pno=7782 ;CREATE TABLE emp10 AECT * FROM emp WHERE deptno=10 ;DELETE FROM myemp ;DELETE FROM myemp WHERE comm IS NOT NULL ;从当前的窗口中发现，此数据已经被删除了。但是，现在开启第二个窗口，再次查询 emp10 表，观察效果，发现 7782 的数据还存在，证明没有删除，实际上这就是 Oracle 中的事务处理概念。事务处理：所谓的事务处理就是保证数据操作的完整性，所有的操作要么同时成功，要么同时失败。在 Oracle 中对于每接建立一个 ses，即：每接到数据库的窗口（sq

39、lplus、sqlplusw）连接之后实际上都会与数据库的连接到数据库上的用户都表示创建了一个 ses。一个 ses对数据库所做的修改，不会立刻反映到数据库的真实数据之上，是允许回滚的，当一个ses提交所有的操作之后，数据库才真正的作出修改。在数据库的操作中提供了以下的两个主提交事务：commit ;回滚事务：rollback ;令完成事务的处理：|- 如果数据已经被提交了，则肯定无法回滚在 Oracle 中关于事务的处理上也会存在一种死锁的概念。 一个 ses如果更新了数据库中的，其他 ses之后才允许更新。是无法立刻更新的，要等待对方提交4、查询练习1、 列出至少有一个员工的所有部门。 求出所有部门的员工数量第（36）页SELECT deptno,COUNT(empno) FROM empGROUP BY deptno ;列出部门人数大于 1 的所有部门通过部门表，查询出部门的信息即可2、 列出薪金比“SMITH”多的所有员工。 求出 SMITH 的工资 以上面的结果为条件，查询所有符合条件的雇员信息第（37）页SELECT * FROM empWHERE sal(SELECT sal FROM emp WHERE ename=SMITH) ;SELECT sal FROM emp WHERE ename=SMITH ;SELECT d.*,ed.co