《李兴华Oracle学习笔记》由会员分享，可在线阅读，更多相关《李兴华Oracle学习笔记（59页珍藏版）》请在金锄头文库上搜索。
1、1234567图表 1891011121314多表查询的基本语法多表查询的基本语法查一张以上的表，就叫做多表查询 例子：查询出雇员名称，部门名称和部门所在地的（一般多表查询要用别名）15统计记录数：统计记录数：查询 emp 有多少条纪录左右连接（重点）左右连接（重点）select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno; 部门一共四个，这里只查询出三个，因为在雇员表中没有指定 40 部门的雇员，所以在消除 笛卡尔乘机的时候没有条件符合 40，如果喜欢 40 部门显示出来，就
2、要用左右连接了。16select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno(+)(+)(+)=d.deptno;（+）在左边，表示以右边的表为准，表示右链接。40 部门出来了，所以此时就用到了有连接，证明以下规律 （+）在左表示右连接 （+）在右表示左连接.SQL：1999 对对 SQL 的支持（了解）的支持（了解）范例：交叉连接（范例：交叉连接（cross join）产生笛卡尔积）产生笛卡尔积17select * from empt CROSS JOIN dept; 查询结果 产生笛卡尔积
3、CREATE TABLE EMP10 AS SELECT * FROM EMP WHERE DEPTNO=10; select * from emp NATURAL JOIN dept; 自动进行匹配 范例：范例：USING 子句，直接关联的操作列子句，直接关联的操作列 select * from emp e JOIN dept d USING (deptno) where deptno=30;把两张表的详细信息进行打印输出 范例：范例：ON 子句子句 自己编写连接条件自己编写连接条件 select * from emp e JOIN dept d ON (e.deptno=d.deptno)
4、 where e.deptno=30; 范例：左连接（左外连接）右连接（右外连接）范例：左连接（左外连接）右连接（右外连接）LEFT JOIN RIGHT JOIN组函数和分组统计（重点）组函数和分组统计（重点）组函数组函数在 SQL 常用组函数有如下几个： COUNT()求全部记录数 MAX()求最大记录数 MIN()求最小记录数 AVG()平均 SUM()求和分组统计分组统计GROUP BYselect deptno,COUNT(empno) from emp GROUP BY deptno;算出部门表的平均工资：算出部门表的平均工资：18select AVG(sal) from emp
5、; 算出每个部门的平均工资：算出每个部门的平均工资： Select deptno,AVG(sal) from emp ;之所以会出现这个错误是因为数据库不知道怎样在结果集中处理 deptno 列。 考虑一下：这个查询既试图使用 AVG 聚合函数对多 行记录进行操作，却又试图 从每行中获得 deptno 列的值；这两个操作是不可能同时完成的。此时必须提供 一个 GROUP BY 子句告诉数据库将 deptno 列相同的行分组在一起，然后数据库 就可以将这些组中的行传递给 AVG 函数。警告：如果查询中包含聚合函数，而所选择的列并不在聚合函数中，那么这些列就必 须在 GROUP BY 子句中。按部
6、门分组，并显示部门名称，以及部门员工数按部门分组，并显示部门名称，以及部门员工数 select d.dname,count(e.empno) from dept d,emp e where d.deptno=e.deptno GROUP BY d.dname;要求查出平均工资大于要求查出平均工资大于 2000 的部门编号和平均工资的部门编号和平均工资 select deptno,AVG(sal) from emp WHERE AVG(sal) 2000 GROUP BY deptno;之所以会出现这个错误是因为 WHEREWHEREWHERE 子句只能用来对单行而不是行组进行过滤子句只能用来对
7、单行而不是行组进行过滤子句只能用来对单行而不是行组进行过滤子句只能用来对单行而不是行组进行过滤。要过。要过 滤行组，可以使用滤行组，可以使用 HAVING 子句。子句。19范例：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事范例：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事 同一工作的雇员的月工资合计大于同一工作的雇员的月工资合计大于￥5000。输出结果按月工资的合计升序排列。输出结果按月工资的合计升序排列 1.显示全部的非销售人员：显示全部的非销售人员：jobsalesman; 2.按工作分组同时求出工资的总和按工作分组同时求出工资的总和
8、 Select job,SUM(sal) from emp WHERE jobSALESMAN GROUP BY job HAVING SUM(sal)5000; 4.使用排序，按升序排列使用排序，按升序排列 Select job,SUM(sal) su from emp WHERE job5000 order by su; 分组的简单原则：分组的简单原则： 只要一列上存在重复的内容才考虑用分组 注意：注意：分组函数可以嵌套使用，但是在组函数嵌套的时候不能再出现分组条件的查询语句 范例：范例：求出平均工资最高的部门 错误代码： Select deptno,MAX(AVG(sal) from e
9、mp GROUP BY deptno; Select MAX(AVG(sal) from emp GROUP BY deptno;(正确)20子查询子查询范例：要求查询出比范例：要求查询出比 7654 工资高的全部雇员信息工资高的全部雇员信息 首先：要知道 7654 雇员的工资是多少然后：以此查询结果为查询依据，只要其他工资大于 sal，则表示符合条件首先首先：查询出比查询出比 7654 工资高的全部雇员信息工资高的全部雇员信息 select * from emp where sal(select sal from emp where empno=7654); 其次：与其次：与 7788 工作
10、一样工作一样 Select job from emp where empno=7788 所以：select * from emp where sal(select sal from emp where empno=7654) and job= （Select job from emp where empno=7788） ；;21数据库更新操作数据库更新操作数据库的主要操作分为两种： 1.数据库的查询操作 SELECT 2.数据库的更新操作 uUPDATE， DELETE， INSERT 此时为了保存原始的 emp 表的信息，在进行更新 删除 插入表前先将表复制一份 Create table m
11、yemp AS select * from emp; 此时数据已经复制出来添加数据添加数据Insert into emp(empno,ename,job,hiredate,sal ,deptno) Values (7899,张三,清洁工,20-2 月-2000,9000, 40); 使用简略写法使用简略写法(并不推荐并不推荐)，因为现在是要添加所有字段的内容，所以可以不写上任何字段，因为现在是要添加所有字段的内容，所以可以不写上任何字段 名称，只要值的数量和顺序和数据库表中的顺序一致。名称，只要值的数量和顺序和数据库表中的顺序一致。 Insert into myemp values(7899,
12、张三,清洁工，9000, 40); 之前插入数据的时候，日期的格式是使用了表中固定好的格式，如果现在有这样一个日期之前插入数据的时候，日期的格式是使用了表中固定好的格式，如果现在有这样一个日期” 2009-10-10”日期格式，那么现在如何把这种格式的日期插入进去呢？日期格式，那么现在如何把这种格式的日期插入进去呢？ 使用使用 TO_DATE()函数，将一个字符串类型的数据变为函数，将一个字符串类型的数据变为 DATE 类型的数据。类型的数据。 Insert into myemp(empno,ename,job,hiredate,sal ,deptno) Values (7899,张三,清洁工
13、,TO_DATE(2009-07-19,yyyy-mm-dd),9000, 40);修改数据修改数据UPDATE 表名称 set 要修改的字段=新值，要修改的字段=新值.; UPDATE 表名称 set 要修改的字段=新值，要修改的字段=新值WHERE 修改条件.;22修改数据修改数据删除全部删除全部: DELETE FROM 表名称 局部删除局部删除: DELETE FROM 表名称 WHERE 删除条件;事物处理事物处理范例：创建一张只包含范例：创建一张只包含 10 部分雇员的一张临时表部分雇员的一张临时表 CREATE TABLE EMP10 AS SELECT * FROM EMP W
14、HERE DEPTNO=10;打开一个 oracle 终端，进行删除操作 DELETE FROM EMP10 WHERE SAL=2450;显示已经删除然后再打开另外一个 oracle 终端，查询到 sal=2450 这条数据还在，证明这条数据并没有被 删除，这就是 oracle 事务的概念。 事务处理: 就是保证数据操作的完整性，所有的操作要么同时成功要么同时失败。在 ORACLE 中对每一个连接到数据库中的窗口，都会与数据库建立一个 Session。一个 Session 对数据库所做得修改不会马上反应到数据库的真实数据之上。是允许回滚的，当一 个 Session 提交所有操作之后，数据库才
15、真正做出修改。 进行同样的删除操作 DELETE FROM EMP10 WHERE SAL=2450; 结果它停住了，等待第一个终端操作结束再动。-这就是 ORACLE 死锁死锁 提交事务：提交事务：COMMIT 回滚：回滚：Roolback23ORACLE 常用命令常用命令查看所有表查看所有表select table_name from user_tables;显示表结构显示表结构 describe nchar_tst（nchar_tst 为表名） 查询练习查询练习 1.列出至少有一个员工的所有部门信息列出至少有一个员工的所有部门信息 第一步: 列出所有部门的员工数量Select deptn
16、o ,count(empno) from emp group by deptno;第二步:列出员工大于 1 的部门Select deptno ,count(empno) from emp group by deptno HAVING COUNT(empno)1;第三步:通过多表关联查，把子查询做为一个查询出来 select d.*,ed.cou FROM dept d,(SELECT deptno,COUNT(empno) cou FROM emp GROUP BY deptno HAVING COUNT(empno)1) ed WHERE d.depno=ed.deptno ;Comment U1: Comment MS2: 查找 e 表的直属领 导编号等于 m 表的员工,也就是说 m 表是领导表,m 表的雇佣日期晚于 e 表242.列出薪金比列出薪金比 SMITH 多的所有员工多的所有员工 第一步：求出 SMITH 的工资 SELEC
