Oracle11g学习四多表查询、左右连接、SQL1999语法、统计函数及分组查询.pdf

《Oracle11g学习四多表查询、左右连接、SQL1999语法、统计函数及分组查询.pdf》由会员分享，可在线阅读，更多相关《Oracle11g学习四多表查询、左右连接、SQL1999语法、统计函数及分组查询.pdf（4页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、一、多表查询 多表查询的语法：SELECT DISTINCT*|列 别名,列|别名,.FROM 表名称 别名,表名称 别名,.WHRER 条件(s)ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC;在多表查询之间一定要进行表中数据量的查询，以避免在大数据量的情况下操作避免造成响应太慢。使用 COUNT()函数进行查询，例：SELECT COUNT(*)FROM emp;在多表查询中，不同的表中有可能有相同的字段名称，此时访问这些字段必须加上表名称，即：表名称.字段，由于表名称很长此时可以给表起一个别名。例：查询每一位雇员的编号、姓名、职位、部门名称、位置。第一步：确定数

2、据表：emp：雇员标号、姓名、职位 dept：部门名称、位置 第二步：确定两表的关联字段：emp.deptno/dept.deptno SELECT e.empno,e.ename,e.job,d.dname,d.loc FROM emp e,dept d WHERE d.deptno=e.deptno;例：查询出每一位雇员的姓名、职位、领导的姓名。第一步：确定数据表：emp:雇员姓名、职位 emp:领导的姓名 第二步：确定量表的关联字段：emp.mgr/emp.deptno SELECT e.ename,e.job,m.ename FROM emp e,emp m WHERE e.mgr=e

3、.empno;例：查询每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门及位置 第一步：确定数据表：emp:雇员姓名、编号、基本工资、职位 emp:领导姓名 dept:部门、位置 第二步：关联字段：雇员和领导-emp.mgr=emp.empno 雇员和部门-emp.deptno=dept.deptno SELECT e.empno,e.ename,e.sal,e.job,m.empno,d.dname,d.loc FROM emp e,emp m,dept d WHERE e.mgr=m.empno AND e.deptno=d.deptno;例：查询出每一位雇员的编号，姓名，工资，部门名

4、称，工资所在公司等级 SELECT e.empno,e.ename,e.sal,d.dname,s.grade FROM emp e,dept d,salgrade s WHERE e.deptno=d.deptno AND sal BETWEEN s.losal AND s.hisal;二、左右连接 左右连接指的是查询判断条件的参考方向。在如下例子中：SELECT*FROM emp e,dept d WHERE e.deptno=d.deptno;输出结果中 dept缺少 40的部门，这是因为 emp表中没有一条记录是属于 40的，所以不会显示 40 部门信息，即：现在查询以 emp表为参考

5、，如果现在要显示 40部门则使用如下 SQL语句：SELECT*FROM emp e,dept d WHERE e.deptno(+)=d.deptno;现在 40部门显示了，即参考方向改变了。(+)=:放在等号左边，表示右连接，即放在字段右边：=(+):放在等号右边，表示左连接，即放在字段右边；-不必刻意区分，根据查询结果而定 这是 Oracle数据库独有的，其他数据库不能够采用。三、SQL1999语法 SQL也提供了表左右连接的操作；SQL1999语法：SELECT table1.column,table2.column FROM table1 CALSS JOIN table2|NATU

6、RAL JOIN table2|JOIN table2 USING(column_name)|JOIN table2 ON(table1.column_name=table2_column)|LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name);以上属于多语法联合，一下进行说明：(1)交叉连接（CROSS JOIN）：用于产生笛卡尔积，笛卡尔积并不是无用的内容，在某些情况下还是有用的。SELECT*FROM emp CROSS JOIN dept;(2)自然连接（NATURAL JOIN）：自动寻

7、找关联字段，消除笛卡尔积 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;右（外）连接：RIGHT OUTER JOIN.ON;

8、全（外）连接：FULL OUTER JOIN.ON;-把两张表中都没有的数据都显示 例：SELECT*FROM emp RIGHT OUTER JOIN dept ON(emp.deptno=dept.deptno);四、统计函数及分组查询 1、统计函数有如下几个：COUNT():查询表中的数据记录，AVG():平均值，SUM()：求和，MAX():求最大值，MIN():求最小值。例：统计全公司雇员的每个月支出的平均工资及总工资。SELECT COUNT(empno),SUM(sal),AVG(sal)FROM emp;例：统计雇员中最高工资和最低工资 SELECT MAX(sal),MIN(

9、sal)FROM emp;注意：使用 COUNT()函数查询表中的记录，若表示空表，则 COUNT返回 0 ，则使用其他统计函数查询空表返回NULL,COUNT()函数永远返回一个数字。2、分组统计 分组的前提：数的某些字段存在重复分组才有意义。分组使用 GROUP BY字句完成，此时 SQL语句如下：SELECT DISTINCT*|分组字段 1 别名,分组字段 2 别名|统计函数 FROM 表名称 别名,表名称 别名,.WHERE 条件(s)GROUP BY 分组字段 1，分组字段 2，.ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC;例：按部门编号分组，求出每个

10、部门的人数，平均工资 SELECT deptno,COUNT(empno),AVG(sal)FROM emp GROUP BY deptno;例：按照职位分组，求出每个职位的最高和最低工资 SELECT job,MAX(sal),MIN(sal)FROM emp GROUP BY job;注意：a.统计分组函数可以单独使用，但是不能出现其他的查询字段 正确：SELECT COUNT(empno)FROM emp;错误：SELECT empno,COUNT(empno)FROM emp;b.进行分组时，SELECT字段只能出现分组字段和统计函数，其他字段不能出现 正确：SELECT job,CO

11、UNT(empno),AVG(sal)SELECT COUNT(empno)FROM emp;错误：SELECT deptno,job,COUNT(empno),AVG(sal)SELECT COUNT(empno)FROM emp;c.分组查询允许嵌套，但是嵌套之后在 SELECT字句不在允许出现任何其他字段 例：按照职位分组，查询统计最高的平均工资 正确：SELECT MAX(AVG(sal)FORM emp GROUP BY job;错误：SELECT job,MAX(AVG(sal)FORM emp GROUP BY job;例：查询出每个部门的名称、部门人数，平均工资 加入多表查询和

12、左右链接 SELECT d.dname,COUNT(e.empno),NVL(AVG(e.sal),0)FROM dept d,emp e WHERE d.deptno(+)=e.deptno GROUP BY d.dname;分组操作也可用于多字段分组：例：查询显示每个部门编号，名称，位置，部门人数，平均工资 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、HA

13、VING字句 HAVING字句使用在 GROUP BY分组之后的过滤条件下，HAVING的 SQL语法：SELECT DISTINCT*|分组字段 1 别名,分组字段 2 别名|统计函数 FROM 表名称 别名,表名称 别名,.WHERE 条件(s)GROUP BY 分组字段 1，分组字段 2，.HAVING 分组后过滤条件（可以使用统计函数）ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC;WHERE和 HAVING的区别：WHERE:位于 GROUP BY操作之前，表示从全部数据中筛选出部分数据，在WHERE字句中不能使用统计函数 HAVING:位于 GROUP BY操作之后，表示从分组之后的数据中再进行筛选，可以在 HAVING字句中使用统计函数 例：输出部门的详细信息，并且要求这些部门的平均工资大于 2000 SELECT d.dname,COUNT(e.empno),NVL(AVG(e.sal),0)FROM dept d,emp e WHERE d.deptno(+)=e.deptno GROUP BY d.dname HAVING AVG(e.sal)2000;*by jixiangrurui 转载请注明出处*