数据库复习--以习题形式分析所用到的知识点
以习题的形式分析其中的知识点:
1、查询所有员工姓名包含S或s的员工信息及部门名称和薪水等级
首先判断出的是:需要分步骤解决该问题。是以姓名中包含S或s的员工展开的信息查询,其他的信息都是根据该结果集衍生出来的。
难点在:部门名称、薪水等级、员工信息在三张表中,所以这是多表格的信息查询。
解决的思路:
先分解,再合并。
查询出员工姓名包含S或s的员工信息。
select * from emp where ename like 'S' or ename like 's'
接下来的思路就简单为:以得到的结果集与部门表、薪水表依次做联表查询
这里是等值连接查询。
查询部门名称需要该员工所在的部门编号
select t.*,dept.name from t,dept where t.deptno = dept.deptno ;
查询一个员工的薪水等级
select t2.*,salgrade.grade from (
select t.*,dept.name from t,dept where t.deptno = dept.deptno ;
)t2,salgrade where t2.sal between salgrade.losal and salgrade.hisal;
由此该sql语句可以写为:
select t2.*,salgrade.grade from (
select t.*,dept.dname from (
select * from emp where ename like '%S%' or ename like '%s%'
)t,dept where t.deptno = dept.deptno
)t2,salgrade where t2.sal between salgrade.losal and salgrade.hisal;
先将自己在这个尝试的过程中将自己的错误记录下来。
应用的知识点:
1、模糊查询like 2、连接查询 3、逻辑查询--or、between and
2.查询20部门的所有员工的员工姓名,实际收入
select ename,sal+nvl(comm,0) from emp where deptno=20;
这道题很简单。因为未涉及到多表查询。
3.统计平均薪水最高的部门名称。
题目分析:
第一级思考: 先思考怎样选择出平均薪水最高的部门,再来选择出部门名称。
第二级思考: 所有部门平均薪水怎么计算,通过排序取出最高部门部门编号。
第三级思考: 部门薪水在emp表中,部门名称是在部门表中,所以该查询是多表查询的结果。
第一步:先解决求出平均薪水最高的部门名称并进行排序:
select dname,round(avg(nvl(emp.sal,0)),0) sal from emp,dept where emp.deptno(+)=dept.deptno group by dname order by sal desc;
下面有几个点要解释的是:
1>左、右连接
选择出同一部门的员工信息是:where emp.deptno(+)=dept.deptno 右连接,即以右表为基准,会查询右表中的所有记录。举例来说明一下:
A/B表,数据及字段名如下:
A(id,name)
表中数据:(1,小明)(2,小红)(3,小强)
B(id,name)
表中数据:(1,小学生)(2,中学生)(4,大学生)
左连接结果:
select A.*,B.* from A left join B on A.id=B.id;
1 小明1 小学生
2 小红 2 中学生
3 小强 NULL NULL
右链接结果:
select A.*,B.* from A right join B on A.id=B.id;
1 小明1 小学生
2 小红 2 中学生
NULL NULL 4 大学生
在上面的查询结果中我们可以看到两次查询的结果是不同的,非常好的体现了左右连接的区别。分析原因:
因为左连接是查询左表即A表的所有的记录,这里的记录选择出的结果是A表的三条记录都有,B表的一条记录没有,其中的条件是A.id=B.id,因为A表中没有id为4的数据,所以没有显示。由此可知在选择左外连接中限制的是右表B的记录的显示。
我们从第二条的选择结果可以得到相验证的结论,那就是右连接将限制左表A的记录显示。
在实际应用中比如:用户表和用户的购物记录表,查询用户的购物记录时,我们选择将用户表作为基准表,连接购物信息表连接查询。因为用户中会有没有购物记录的,而购物记录表中都会有用户的信息。
至于连接方式的确定是根据选择基准表的变化而变化的。
2>group by分组查询与分组函数 max() min() avg() count() sum()
需要注意的是如果选择group by 分组查询:group by 字段名 。
如果select中出现分组函数,则不能再出现非分组函数|列名。
举例为:select ename,avg(sal) from emp; 则会报错:不是单组分组函数
分组查询中,select之后的所有不在分组函数中的列名,必须出现在group by之后
举例:select deptno,ename,avg(sal) from emp group by deptno;则会报错:不是 GROUP BY 表达式
3>其他的就是零散的知识点:
四舍五入函数round,nvl()函数,order by a desc/order by b asc 排序
第二步:将第一步的结果集取别名为t,选择出需要的结果
select t.sal,t.dnam from t where rownum<2;
这里重点就是伪列的应用,伪列也会在分页中应用到。其有固定的字段名为rownum .
以上三题呢,已经看到了一些知识点的应用,总结一下:
对于整体的难度会集中在连表查询,多表查询上。对于解决问题的思路是:先从最简单的开始思考,分步写,并对前面所写的结果集作为新的查询集合来处理,就是子查询的过程。