查询

《Oracle 数据库》 Copyright 2008, Oracle HJC Chapter 6 – Page 1 第 6 章 数据查询 第 6 章 数据查询 本章重点介绍 Oracle 数据库的查询技术。所谓查询，就是从已经存在的 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 中检索数 据，并显示检索的结果。查询按功能分成多种类型。查询语句在 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf 写上也有特定的语法 要求。 基本查询 查询结果的排序 条件查询 高级查询 Oracle 2008 HJC 第 6 章 数据查询 Copyright 2008, Oracle HJC Chapter 6 – Page 2 6.1 基本查询 【训练 1】指定检索字段。 Select * from emp; Select * from scott.emp; Select deptno,dname form dept; 【训练 2】显示行号。 Select rownum,ename from emp; [说明] 显示的行号是查询结果的行号，数据在数据库中是没有行号的。 【训练 3】显示计算列。 Select ename,sal,sal*(1+20/100) from emp; 【训练 4】在查询中使用列别名。 Select ename as 名称,sal 工资 from emp; 【训练 5】在列名上使用双引号。 Select ename as "Name",sal*12+5000 as "年度工资(加年终奖)" from emp; [说明] 别名"Name"中有大小写区别,别名"年度工资(加年终奖)"中出现了括号,属于 特殊符号,所以需要使用双引号。 【训练 6】连接运算符的使用。 Select ename||':'||job as 雇员和职务表 from emp; 【训练 7】在查询中使用字符串常量。 Select ename||' IS '||job AS " 雇员和职务表" from emp; 【训练 8】消除重复行。 Select distinct job from emp; 《Oracle 数据库》 Copyright 2008, Oracle HJC Chapter 6 – Page 3 6.2 查询结果的排序 【训练 1】升序排序。 Select ename,sal from emp order by sal; [说明] 若省略 ASC 和 DESC,默认为 ASC,即升序排列。 【训练 2】降序排序。 Select ename,hiredate From emp Order by hiredate desc; 【训练 3】多列排序。 Select ename,deptno,hiredate From emp Order by deptno,hiredate; 【训练 4】在排序中使用别名。 Select empno,ename,sal*12 as total From emp Order by total; 第 6 章 数据查询 Copyright 2008, Oracle HJC Chapter 6 – Page 4 6.3 条件查询 【训练 1】简单查询: > < >= <= = <> Select ename,job,sal From emp Where job=’SALESMAN’; [说明] 字符串和日期型数据的是包含在单引号中的。 【训练 2】简单查询：显示工资大于等于 3000 的雇员姓名、职务和工资。 Select ename 姓名,job 职务,sal 工资 From emp Where sal>=3000; 【训练 3】简单查询：显示 1982 年以后雇佣的职员姓名和雇佣时间。 Select ename,hiredate From emp Where hiredate>=’1-1 月-82’; 【训练 4】复合条件查询：逻辑与-AND Select ename,job,sal From emp Where sal>1000 and sal<2000; 【训练 5】复合条件查询：逻辑或-OR Select * From emp Where job=’CLERK’ OR job=’MANAGER’; 【训练 6】复合条件查询：逻辑非-NOT Select * From emp Where NOT deptno=10; 【训练 7】复合条件查询：逻辑或和逻辑与：显示部门 10 和部门 20 中工资小于 1500 的雇员。 Select * From emp Where (deptno=10 or deptno=20) and sal<1500; 【训练 8】条件特殊表示法：[NOT] BETWEEN…AND…的用法。 Select * From emp Where sal between 1000 and 2000; [说明] between 包括边界，not between 不包括边界。 【训练 9】条件特殊表示法：[NOT] IN (…)的用法。 Select * 《Oracle 数据库》 Copyright 2008, Oracle HJC Chapter 6 – Page 5 From emp Where job in (‘SALESMAN’,’CLERK’,’MANAGER’); 【训练 10】条件特殊表示法：[NOT] LIKE 匹配模式 的用法。 Select * From emp Where ename like ‘S%’; Select * From emp Where ename like ‘_A%’; [说明] “_”代表任意一个字符; “%”代表任意字符,个数任意。 【训练 11】条件特殊表示法：判断空值 NULL,用法: IS [NOT] NULL。 Select ename,mgr From emp Where mgr is null; 第 6 章 数据查询 Copyright 2008, Oracle HJC Chapter 6 – Page 6 6.4 高级查询 6.4.1 多表联合查询 两个表的连接有四种连接方式：相等连接、不等连接、外连接和自连接。 【训练 1】相等连接。 --显示雇员的名称和所在的部门的编号和名称 Select emp.ename 姓名,emp.deptno 部门编号,dept.dname 部门名称 From emp,dept Where emp.deptno=dept.deptno; --使用表别名 Select ename 姓名,e.deptno 部门编号,dname 部门名称 From emp e,dept d Where e.deptno=d.deptno; --显示工资大于 3000 的职工的姓名、工资和所在的部门名称。 Select ename,sal,dname From emp,dept Where emp.deptno=dept.deptno and sal>3000; 【训练 2】外连接：外连操作符为(+),它可以出现在相等连接条件的左侧或右侧。 --使用外连显示不满足相等条件的记录。 --显示雇员的姓名、工资和所在的部门名称及没有任何雇员的部门。 Select ename 姓名,sal 工资,dname 部门名称 From emp,dept Where emp.deptno(+)=dept.deptno; --显示雇员的姓名、工资和所在的部门名称及没有属于任何部门的雇员 Select ename 姓名,sal 工资,dname 部门名称 From emp,dept Where emp.deptno=dept.deptno(+); 【训练 3】不等连接。 --显示 salgrade 表的结构 desc salgrade 名称 是否为空? 类型 -------------------------------- -------- ----------- GRADE NUMBER LOSAL NUMBER HISAL NUMBER --显示 salgrade 表的记录 select * from salgrade; GRADE LOSAL HISAL 《Oracle 数据库》 Copyright 2008, Oracle HJC Chapter 6 – Page 7 ----- ---------- ---------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 --显示雇员名称，工资和所属工资等级 select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal; [说明] 通过将雇员工资与工资上下限范围的比较，取得工资的等级，并在查询结果中 显示出雇员的工资等级。 【训练 4】自连接：一个表同本身进行的连接。 --显示雇员姓名和雇员的经理姓名。 select worker.ename||' 的经理是 '||manager.ename as 雇员经理 from emp worker,emp manager where worker.mgr=manager.empno; 6.4.2 统计查询 常用的组函数： AVG 求平均值 COUNT 求统计值，返回非空行数，*表示返回所有行 MAX 求最大值 MIN 求最小值 SUM 求和 STDDEV 求标准偏差，是根据差的平方根得到的 VARIANCE 求统计方差 【训练 1】统计查询。 --统计雇员总人数 Select count(*) from emp; --求有佣金的雇员人数 Select count(comm) from emp; --求部门 10 的雇员的平均工资 Select avg(sal) from emp where deptno=10; --求最晚和最早雇佣的雇员的雇佣日期 Select max(hiredate),min(hiredate) From emp; --求雇员表中不同职务的个数 Select count(distinct job) 第 6 章 数据查询 Copyright 2008, Oracle HJC Chapter 6 – Page 8 From emp; 【训练 2】分组统计。 --按职务统计工资总和 Select job,sum(sal) From emp Group by job; [说明] 在查询列中，不能使用分组列以外的其他列，否则会产生错误信息。 【训练 3】多列分组统计。 --按部门和职务分组统计工资总和 Select deptno,job,sum(sal) From emp Group by deptno,job; [说明] 该查询统计每个部门中每种职务的工资总合。 【训练 4】分组统计结果限定。 --统计各部门的最高工资，排除最高工资小于 3000 的部门 Select deptno,max(sal) From emp Group by deptno Having max(sal)>=3000; 【训练 5】分组统计结果排序。 --按职务统计工资总合并排序 Select job 职务,sum(sal) 工资总合 From emp Group by job Order by sum(sal); 【训练 6】组函数的嵌套使用。 --求各部门平均工资的最高值 Select max(avg(sal)) 最高工资 From emp Group by deptno; 6.4.3 子查询 【训练 1】单行子查询。 --查询比 SCOTT 工资高的雇员名字个工资 Select ename,sal From emp Where sal>(select sal from emp where ename=’SCOTT’); --查询和 SCOTT 同一部门且比他工资低的雇员的名字和工资 《Oracle 数据库》 Copyright 2008, Oracle HJC Chapter 6 – Page 9 Select ename,sal From emp Where sal<(select sal from emp where ename=’SCOTT’) And deptno=(select deptno from emp where ename=’SCOTT’); --查询工资高于平均工资的雇员名字和工资 Select ename,sal from emp where sal>(select avg(sal) from emp); 【训练 2】多行子查询：一般要使用 IN、ANY 和 ALL。 --查询工资低于任何一个“CLERK”的工资的雇员信息 Select empno,ename,job,sal From emp Where sal’CLERK’; --查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资 Select empno,ename,sal From emp Where sal>all(select sal from emp where job=’SALESMAN’); --查询部门 20 中职务同部门 10 的雇员一样的雇员信息 Select empno,ename,sal From emp Where job in (select job from emp where deptno=10) and deptno=20; --查询职务和“SCOTT”相同，比“SCOTT”雇佣时间早的雇员信息 Select empno,ename,job From emp Where job=(select job from emp where ename=’SCOTT’) and hiredate<(select hiredate from emp where ename=’SCOTT’); 【训练 3】多列子查询。 --查询职务和部门与 SCOTT 相同的雇员的信息 select empno,ename,job from emp where (job,deptno)=(select job,deptno from emp where ename='SCOTT'); 【训练 4】在 FROM 从句中使用子查询。 --查询职员表中排列在第 6~9 位置上的雇员 select num,ename,sal from (select rownum as num,ename,sal from emp where rownum<=9) where num>=6; 6.4.4 集合运算 Oracle 共有 4 个集合操作： 第 6 章 数据查询 Copyright 2008, Oracle HJC Chapter 6 – Page 10 UNION 并集，合并两个操作的结果，去掉重复的部分 UNION ALL 并集，合并两个操作的结果，保留重复的部分 MINUS 差集，从前面的操作结果中去掉与后面操作结果相同的部分 INTERSECT 交集，取两个操作结果中相同的部分 【训练 1】使用集合的并运算。 --查询部门 10 和部门 20的所有职员 select deptno,job from emp where deptno=10 union select deptno,job from emp where deptno=20; 【训练 2】使用集合的交运算。 --查询部门 10 和 20 中是否有相同的职务和工资 select job,sal from emp where deptno=10 intersect select job,sal from emp where deptno=20; 【训练 3】使用集合的差运算。 --查询只在部门表中出现，但没有在雇员表中出现的部门编号 select deptno from dept minus select deptno from emp; 《Oracle 数据库》 Copyright 2008, Oracle HJC Chapter 6 – Page 11 综合练习 6 综合练习 6 设计针对 emp 和 dept 表的综合查询： 1、显示人数最多的部门名称。 2、显示各部门的平均工资、最高工资、最低工资和总工资列 表，并按平均工资高低顺序排列。 Oracle 2008 HJC 第6章 数据查询 6.1 基本查询 6.2 查询结果的排序 6.3 条件查询 6.4 高级查询 6.4.1 多表联合查询 6.4.2 统计查询 6.4.3 子查询 6.4.4 集合运算 综合练习6