前面学习的 单标 和 多表 的练习题总结
--单表查询
--1 查询当前用户下的所有表
select * from tab;
--2 查询雇员表中所有信息
select * from emp;
--3 查询雇员编号,姓名,工作,工资
select empno,ename,job,sal from emp;
--4 查询雇员编号,姓名,工作,工资,并显示中文(为列起别名)
select empno as "编号",ename as "姓名",job as "工作",sal as "工资" from emp;
select empno "编号",ename "姓名",job "工作",sal "工资" from emp;
--5 消除重复列,查询雇员工作种类
select distinct job from emp;
--6 字符串连接操作(||)
--查询雇员编号,姓名,工作.按以下格工显示:编号:7369,姓名:Smith,工作:Clerk
select '编号:'||empno||'姓名:'||ename||'工作:'||job from emp;
--7 查询列支持四则运算(年薪=(工资+奖金)*12), nvl(列名,默认值):如果该列值为空,则使用默认值
--查询雇员编号,姓名,工作,年薪
select empno,ename,job,(sal+nvl(comm,0))*12 from emp;
--8 Where条件查询
-- 查询工资大于1500的所有雇员
select * from emp where sal>1500
--查询可以得到奖金的所有雇员
select * from emp where comm is not null
--查询工资大于1500或可以得到奖金的雇员
select * from emp where sal>1500 or comm is not null
--查询工资大于1500并且可以领取奖金的雇员
select * from emp where sal>1500 and comm is not null
--查询工资不大于1500或者不可以领取奖金的雇员
select * from emp where sal<=1500 or comm is null
--查询工资在1500到3000的所有雇员信息
select * from emp where sal>=1500 and sal<=3000
select * from emp where sal between 1500 and 3000
--查询在1981年雇用的员工信息
select * from emp where hiredate like '%81'
select * from emp where to_char(hiredate,'yy')='81'
--查询雇员姓名中第二个字母为"M"的雇员 _ 代表任意1个字符, %代表是任意长度任意字符
select * from emp where ename like '_M%'
--查询雇员工资中带8这个数字的
select * from emp where sal like '%8%'
--查询编号是7369,7499,7521,7799的雇员信息
法一
select * from emp where empno=7369 or empno=7499 or empno=7521 or empno=7799;
法二:
select * from emp where empno in(7369,7499,7521,7799)
--查询雇员编号不是7369,7499,7521,7799的所有雇员信息
select * from emp where empno not in(7369,7499,7521,7799)
--查询雇员编号为7369的雇员信息
select * from emp where empno=7369 ;
--查询雇员编号不为7369的雇员信息
select * from emp where empno!=7369
select * from emp where empno<>7369
--查询雇员信息,按工资由低到高排序
select * from emp order by sal asc
--查询雇员信息,按工资由高到低排序
select * from emp order by sal desc
--查询工资大于1500并且没有奖金的员工信息,按工资由高到低排序
select * from emp where sal>1500 and comm is null order by sal desc
***************************************************************
--多表查询(等值链接,内连接,左链接,右连接, 自连接,全链接,交叉连接)
--思路:1.确定需要的数据(列), 2.确定所需的数据(列)在哪些表中 3.确定数据所在的表之间的关联关系(主外键的引用关系)
select * from emp;
select * from dept;
--查询员工的编号,名称,薪水和所在的部门编号,部门名称及部门位置
--等值链接
select empno,ename,sal,emp.deptno,dname,loc from emp,dept where emp.deptno=dept.deptno
--内连接 :inner join 查询所关联的表中条件相同的数据
select empno,ename,sal,dname,loc from emp inner join dept on (emp.deptno=dept.deptno)
--左连接: left join 查询以左表为主,右表为辅(左表将展现所有的数据,右边只展现有关联的数据)
select empno,ename,sal,dname,loc from emp left join dept on (emp.deptno=dept.deptno)
select empno,ename,sal,emp.deptno,dname,loc from emp,dept where emp.deptno=dept.deptno(+)
--右连接: right join 查询以右表为主,左表为辅(右表将展现所有的数据,左边只展现有关联的数据)
select empno,ename,sal,dname,loc from emp right join dept on (emp.deptno=dept.deptno)
select empno,ename,sal,emp.deptno,dname,loc from emp,dept where emp.deptno(+)=dept.deptno
--全链接: full join
select empno,ename,sal,dname,loc from emp full join dept on (emp.deptno=dept.deptno)
--交叉连接: cross join 笛卡尔乘积
select empno,ename,sal,dname,loc from emp cross join dept
select empno,ename,sal,dname,loc from emp,dept
******面试题很喜欢考自连接
--自连接: 查询员工姓名,经理姓名
select empno, ename,mgr from emp where empno=7935
select empno, ename from emp where empno=7782
select e1.empno,e1.ename,e2.ename from emp e1 inner join (select empno, ename from emp) e2 on(e1.mgr=e2.empno)
五、 组函数(聚合函数)
1 组函数介绍
1.1什么是组函数
组函数操作行集,给出每组的结果。组函数不象单行函数,组函数对行的集合进行操作,
对每组给出一个结果。这些集合可能是整个表或者是表分成的组
1.2组函数与单行函数区别
单行函数对查询到每个结果集做处理,而组函数只对分组数据做处理。
单行函数对每个结果集返回一个结果,而组函数对每个分组返回一个结果。
1.3组函数的类型
AVG 平均值
COUNT 计数
MAX 最大值
MIN 最小值
SUM 合计
1.4组函数的语法
图
1.5使用组函数的原则
用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。所有组函数忽略空值。为了用一个值代替空值,用 NVL、NVL2 或 COALESCE 函数。
2 组函数的使用
2.1使用 AVG 和 SUM 函数
AVG(arg)函数:对分组数据做平均值运算。
注意: arg:参数类型只能是数字类型。
SUM(arg)函数:对分组数据求和。
注意: arg:参数类型只能是数字类型。
2.1.1示例
求雇员表中的的平均薪水与薪水总额。
select avg(salary) ,sum(salary) from employees;
2.2使用 MIN 和 MAX 函数
MIN(arg)函数:求分组中最小数据。
arg:参数类型可以是字符、数字、日期。
MAX(arg)函数:求分组中最大数据。
arg:参数类型可以是字符、数字、日期。
2.2.1示例
求雇员表中的最高薪水与最低薪水。
select min(salary),max(salary) from employees;
MIN 和 MAX 函数,对字符串操作
日期也是相同
注意有时会显示错误: 不是单分组函数
2.3使用 COUNT 函数
COUNT 函数:返回一个表中的行数。
COUNT 函数有三种格式:
COUNT(*) / COUNT(expr) / COUNT(DISTINCT expr)
2.3.1COUNT(*)
返回表中满足 SELECT 语句标准的行数,包括重复行,包括有空值列的行。如果
WHERE 子句包括在 SELECT 语句中,COUNT(*) 返回满足 WHERE 子句条件的行数。
2.3.1.1 示例一 ---count(*)
返回查询结果的总条数。
select count(*) from employees;
2.3.2COUNT(expr)函数
返回在列中的由 expr 指定的非空值的数。
2.3.2.1 示例二 ---count(expr)
显示部门 80 中有佣金的雇员人数。
select count(commission_pct) from employees e where e.department_id = 80;
2.3.3COUNT(DISTINCT expr):
使用 DISTINCT 关键字禁止计算在一列中的重复值。
2.3.3.1 示例三 ---count(distinct())
显示 EMPLOYEES 表中不重复的部门数。
select count(distinct department_id) from employees ;
2.4组函数和 Null 值
所有组函数忽略列中的空值。
在组函数中使用 NVL 函数来处理空值。
2.4.1示例一
计算有佣金的员工的佣金平均值。
select avg(commission_pct) from employees;
2.4.2示例二
计算所有员工的佣金的平均值。
select avg(nvl(commission_pct,0)) from employees;
3 创建数据组(GROUP BY)
3.1什么是创建数据组
可以根据需要将查询到的结果集信息划分为较小的组,用 GROUP BY 子句实现。
图
3.2GROUP BY 子句 ---位于where 后面 ,order by 前面
图
GROUP BY 子句:GROUP BY 子句可以把表中的行划分为组。然后可以用组函数返回
每一组的摘要信息。
3.3使用分组原则
如果在 SELECT 子句中包含了组函数,就不能选择单独的结果,除非单独的列出现在 GROUP BY 子句中。如果未能在 GROUP BY 子句中包含一个字段列表,你会收到一个错误信息。
使用 WHERE 子句,你可以在划分行成组以前过滤行。(--也就是该在where给什么条件就给什么条件,不影响)
在 GROUP BY 子句中必须包含列,给列名 。
在 GROUP BY 子句中你不能用列别名。
默认情况下,行以包含在 GROUP BY 列表中的字段的升序排序。可以用 ORDER BY
子句覆盖这个默认值。
3.4GROUP BY 子句的使用
我们可以根据自己的需要对数据进行分组,在分组时,只要将需要做分组的列的列名添
加到 GROUP BY 子句后侧就可以。GROUP BY 列不必在 SELECT 列表中。
图
3.4.1示例一
求每个部门的平均薪水。
--department_id 因为投影的列已经在分组汉函数当中
select department_id , avg(salary) from employees e group by e.department_id;
注意: 不能用列的别名,但是可以给列的别名加前缀
如图:
3.5多于一个列的分组
图,重要
3.5.1示例一
显示在每个部门中付给每个工作岗位的合计薪水的报告。
select department_id,job_id,sum(salary)from employees group by
department_id,job_id order by department_id;
3.5.1的图是没有合并工作种类的求和薪水,下面这个图是和相同工作岗位后的求和薪水,按部门号升序排列
如果想对薪水再进行升序排列怎么办呢?
答:不能直接将department_id改成salary,因为要排序的列,并不是分组函数当中的列,所以此时,可以直接将前面的sum(salary)放在后面,因为他是经过组函数运算的数,相当于是对运算后的salary进行排序,这是可以的
3.6GROUP BY 子句的执行顺序
先进行数据查询,在对数据进行分组,然后执行组函数。
3.7非法使用 Group 函数的查询
在 SELECT 列表中的任何列必须在 GROUP BY 子句中。
在 GROUP BY 子句中的列或表达式不必在 SELECT 列表中。
3.8约束分组结果
3.8.1什么是 HAVING 子句
HAVING 语句通常与 GROUP BY 语句联合使用,用来过滤由 GROUP BY 语句返回的记录集。
HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足。
3.8.2HAVING 子句语法
图
3.8.3示例一
显示那些最高薪水大于 $10,000 的部门的部门号和最高薪水。
select e.department_id,max(e.salary) from employees e group by e.department_id having max(e.salary) > 10000;
3.8.4示例二
查询那些最高薪水大于 $10,000 的部门的部门号和平均薪水。
select e.department_id,avg(e.salary) from employees e group by e.department_id having max(e.salary) > 10000;
3.9嵌套组函数
在使用组函数时我们也可以根据需要来做组函数的嵌套使用。
3.9.1示例
显示部门中的最大平均薪水。
select max(avg(e.salary)) from employees e group by e.department_id;
4 组函数小节练习
4.1组函数在多行上计算,对每个组产生一个结果。True/False
答案:True
****4.2组函数在计算中包含空值。True/False
答案:False 组函数会忽略空值,如果需要空值参与计算,需要使用 nvl 函数处理空值。
4.3在分组计算中,WHERE 子句对行的限制在计算的前面。
True/False
答案:True
4.4显示所有雇员的最高、最低、合计和平均薪水,列标签分别为:
Maximum、Minimum、Sum 和 Average。四舍五入结果为最近的整数。
答案:
selectmax(salary),min(salary),sum(salary),avg(salary) from employees;
4.5修改上题显示每种工作类型的最低、最高、合计和平均薪水。
答案:
select max(salary),min(salary),sum(salary),avg(salary) from employees group by job_id;
4.6写一个查询显示每一工作岗位的人数。
答案:
select job_id, count(*) from employees group by job_id;
4.7确定经理人数,不需要列出他们,列标签是 Number of Managers。
答案:
select count(distinct manager_id) from employees ;
4.8写一个查询 显示最高和最低薪 水之间的差。列标签是DIFFERENCE。
答案:
select max(salary) - min(salary) from employees;
***4.9显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。排除最低薪水小于等于 $6,000 的组。按薪水降序排序输出。
答案:
select e.manager_id ,min(e.salary) from employees e where e.manager_id is not null
group by e.manager_id having min(e.salary) >6000 order by min(e.salary) desc;
4.10写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。
答案:
select d.department_name,d.location_id,count(*) ,avg(e.salary) from employees e ,
departments d where e.department_id = d.department_id group by
d.department_name ,d.location_id;
4.11创建一个查询显示雇员总数,和在 2001、2002、2003 和受雇的雇员人数。
创建适当的列标题。
答案:
select count(*) total,sum(decode(to_char(hire_date,'yyyy'),'2000',1,0))"2000" ,
sum(decode(to_char(hire_date,'yyyy'),'2001',1,0))"2001",sum(decode
(to_char(hire_date,'yyyy'),'2002',1,0))"2002",sum(decode(to_char(hire_date,'yyyy'),
'2003',1,0))"2003" from employees e;
4.12创建一个混合查询显示工作岗位和工作岗位的薪水合计,并且合计部门 20、50、80
和 90 的工作岗位的薪水。给每列一个恰当的列标题。
答案:
select job_id,sum(salary),sum(decode(department_id,20,salary))"Dep20",
sum(decode(department_id,50,salary))"Dep50",sum(decode(department_id,80,salary))"Dep
80" ,sum(decode(department_id,90,salary))"Dep 90"from employees group by job_id;
六、 子查询
1 子查询介绍
1.1什么是子查询
子查询是一个 SELECT 语句,它是嵌在另一个 SELECT 语句中的子句。
图
可以用组合两个查询的方法解决这个问题,放置一个查询到另一个查询中。内查询或子查询返回一个值给外查询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值。
1.2子查询语法
子查询 (内查询) 在主查询之前执行一次
子查询的结果被用于主查询 (外查询)
可以将子查询放在许多的 SQL 子句中,包括:
WHERE 子句
HAVING 子句
FROM 子句
2 使用子查询
2.1使用子查询的原则
子查询放在圆括号中。
将子查询放在比较条件的右边。
在单行子查询中用单行运算符,在多行子查询中用多行运算符。
2.1.1示例
谁的薪水比 Abel 高。
用内连接(自连接)实现:
select em.last_name,em.salary fromemployees abel,employees em where abel.last_name = 'Abel' and em.salary >abel.salary;
用子查询实现:
select em.last_name,em.salary from employees em where em.salary >(select m.salary
from employees m where m.last_name = 'Abel');
单行子查询:子查询语句只返回一行的查询? 多行子查询:子查询语句返回多行的查询
3.1单行子查询
仅返回一行
使用单行比较符主查询对子查询结果的单行比较运算符:
图
3.1.1示例一
显示那些 job ID 与雇员 141 相同的雇员的名字与 job ID。
select em.last_name,em.job_id from employees em where em.job_id = (select job_id
from employees e where e.employee_id = 141);
3.1.2示例二
显示 job ID 与雇员 141 相同,并且薪水 高于雇员 143 的那些雇员。
select e.last_name,e.job_id,e.salary from employees e where e.job_id = (select em.job_id from employees em where em.employee_id = 141) and e.salary > (select emp.salary from employees emp where emp.employee_id = 143);
3.2在子查询中使用组函数
在子查询中也可使用组函数。
3.2.1示例
显示所有其薪水等于最低薪水的雇员的 last name、job ID 和 salary。
select em.last_name,em.job_id,em.salary from employees em where em.salary =(select
min(salary) from employees);
3.3带子查询的 HAVING 子句
可以在 WHERE 子句中使用子查询,也可以在 HAVING 子句中使用子查询。
3.3.1示例
显示所有其最低薪水小于 部门 50 的最低薪水的部门号和最低薪水。
select em.department_id,min(em.salary) from employees em group by em.department_id having min(em.salary) > (select min(e.salary) from employees e where e.department_id = 50);
3.4 什么是子查询错误?
图
子查询错误:使用子查询的一个常见的错误是单行子查询返回了多行。
3.5 多行子查询
返回多于一行
使用多行比较符
主查询对子查询的多行比较运算符
图
在条件中也可使用 NOT 取反。
3.6用 在多行子查询中使用 IN 运算符
3.6.1 示例
查找各部门收入为部门最低的那些雇员。显示他们的名字,薪水以及部门 ID。
select e.last_name,e.department_id,e.salary from employees e where e.salary in(select min(em.salary) from employees em group by em.department_id);
3.7用 在多行子查询中使用 ANY 运算符
图
<ANY 意思是小于最大值。 >ANY 意思是大于最小值。
3.7.1 示例
显示工作岗位不是 IT_PROG 的雇员,并且这些雇员的的薪水少于 IT_PROG 工作岗位的雇员的 ID、名字、工作岗位和薪水。
select e.employee_id,e.last_name,e.job_id,e.salary from employees e
where e.job_id <> 'IT_PROG'and e.salary < any (select em.salary from
employees em where em.job_id = 'IT_PROG') ;
3.8用 在多行子查询中使用 ALL 运算符
图
<ALL 意思是小于最小值。>ALL 意思是大于最大值。
ANY 与 ALL 的区别:
ANY: >ANY 表示至少大于一个值,即大于最小值。
ALL: >ALL 表示大于每一个值,既大于最大值。
3.8.1 示例
显示那些薪水低于工作岗位 IT_PROG 的最低薪水,并且工作岗位不是 IT_PROG 的所有雇员。