实验5 SQL高级查询资料

1、实验(shyn)五 SQL高级查询实验目的：本次实验旨在使学生熟练掌握与运用连接查询与子查询。实验要求:理解数据库设计的三个范式的意义；理解连接的基本意义：通过联接，可以从两个或多个表中根据各个表之间的逻辑关系来检索数据。联接通过以下(yxi)方式定义两个表在查询中的关联方式：(1)指定每个表中要用于联接的列。(典型的联接条件在一个表中指定一个外键，而在另一个表中指定与其关联的键。)(2)指定用于比较各列的值的比较运算符(如 = 或 ) (3)当2个表的连接条件所涉及的属性一致时可使用自然连接理解和掌握集合的操作。子查询是一种SELECT语句的使用方法，它嵌套在SELECT、UPDATE、DE

2、LETE、INSERT语句或其他的子查询语句中。使用子查询是因为DML的操作依赖于子查询的结果。共十三页实验环境： Oracle 10g实验步骤：使用scott模式登录数据库查询所有员工及其所在部门的信息（emp，dept）使用左外连接查询部门及其部门所拥有的员工的信息（emp，dept）分别使用右外连接、外连接和交叉连接进行操作。查询员工姓名、部门、工资(gngz)、工资(gngz)级别等信息，员工的工资(gngz)级别必须处于工资(gngz)水平级别最低等级1。查询高于平均工资的员工信息。共十三页查询高于平均工资的员工及其部门信息(xnx)。查询尚没有员工的部门信息。查询所有员工的工资等级

3、 。查询和SCOTT同一个职位(job)的所有员工的员工名、部门名和工资等级 共十三页实际操作：Conn scott/tigerorcl (或conn scott/tiger)这是不同的连接方式，前面是使用网络服务名（连接名）进行连接，这种连接是通过internet进行连接的，所以该网络服务名所指向的数据库的监听器必须启动并监听，后一种连接方式是连接由oracle_sid 指定的例程名，不是使用网络连接而是本地连接（通过Oracle Net的支持），所以监听器是否启动都不受影响。Select * from empinner join depton emp.deptno = dept.deptn

4、o;自然连接：Select * from emp natural join dept;内连接表的结果集与查询表、连接表的次序无关，请同学们验证。输入并执行语句：Set linesize 200; 再执行查询语句，观察输出的结果格式(g shi)的区别。共十三页Select * from deptleft outer join empon emp.deptno = dept.deptno;同学们再执行一次右外连接查询，仔细观察两个结果集的区别(qbi)；同学们自己动手共十三页Select e.ename,d.dname,e.sal,s.grade from emp einner join dep

5、t don e.deptno = d.deptnoinner join salgrade son (e.sal between s.losal and s.hisal) and s.grade = 1;共十三页select * from empwhere sal select avg(sal) from empselect * from empjoin depton emp.deptno = dept.deptnowhere sal (select avg(sal) from emp);select * from deptwhere deptno not in (select deptno f

6、rom emp)select e.ename,g.grade from emp e join salgrade gon e.sal between g.losal and g.hisal 共十三页select e.ename,d.dname,g.grade from emp e join dept don e.deptno=d.deptnojoin salgrade gon e.sal between g.losal and g.hisalwhere job=(select job from emp where ename=SCOTT)共十三页Oralce一些特殊用法：select e.ena

7、me,d.dname from emp e join dept d using(deptno) = select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;使用using子句的条件 1、查询必须(bx)是等值连接 2、等值连接中的列名必须(bx)是相同的使用rownum查询前5个员工序号和姓名 select rownum,ename from emp where rownum=5 根据ename进行排序 select rownum,ename from emp where rownum=5order by 2;共

8、十三页使用ANY、SOME、ALL操作符， Any,Some用来对子查询返回值列表中的任意值进行比较 ALL用来对子查询返回值列表中的所有值进行比较 在ANY,Some和ALL操作符之前必须使用比较运算符查询工资(gngz)低于salgrade表中低级工资(gngz)的员工名和其工资(gngz) select ename,sal from emp where salany(select losal from salgrade);查询工资低于salgrade表中所有的低级工资的员工名和其工资 select ename,sal from emp where salall(select losal

9、from salgrade);共十三页使用exists 和 not exists 检查行的存在性查询(chxn)可以管理其他员工的员工姓名select ename from empwhere exists(select 1 from emp inner where emp.empno=inner.mgr) 查询没有员工的部门号select deptno from dept where not exists(select 1 from emp where dept.deptno=emp.deptno) 共十三页使用集合操作（SQL Server中只是支持Union和Union all）将两个或多个查询的结果组合在一起 union ：并集，消除重复 union all ：并集，保留重复 intersect：交集(jioj)，返回两个查询共有的行