黑马程序员c语言教程：多表查询.docx
1笛卡尔积部门表笛卡尔积产生结果:行数两个表相乘列数：行数相加原因条件等值连接select*from tabi, tab2 where tabl.a = tab2.a1 select count(e.ename) 2 from emp e, dept d 3* where e.deptno 二 d.deptno1014行。SQL>/COUNT(E.ENAME)14多表查询关键点：等值连接 eg:查询员工信息，员工号，姓名月薪，部门名称1 select e.empno, e.ename, e.sal, d.dname2 from emp e, dept d3* where e.deptno = d.deptnoSQL>/EMPNO ENAMESAL DNAME7369 SMITH7369 SMITH800 RESEARCH7499 ALLEN7499 ALLEN1600 SALES7521 WARD1250 SALES7566 JONES7654 MARTIN7698 BLAKE7782 CLARK7788 SCOTT7839 KING7844 TURNER7876 ADAMS7900 JAMES7902 FORD7934 MILLER2975 RESEARCH1250 SALES2850 SALES2450 ACCOUNTING3000 RESEARCH5000 ACCOUNTING1500 SALES1100 RESEARCH950 SALES3000 RESEARCH1300 ACCOUNTING已选择14行。不等值连接：eg：查询员工信息，员工号，姓名，月薪，薪水级别select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal >= s.losal and e.sal <=s.hisal select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal外连接eg:按部门统计员工人数：部门号部门名称各部门人数分析 1:10 20 30 = = = =分组分析2:因为各部门人数是在员工表中.多表查询步骤1select d.deptno, d.dname, count(e.empno)from dept d, emp e where d.deptno = e.deptno group by d.deptno, d.dnameDEPTNO DNAMECOUNT(E.EMPNO)10 ACCOUNTING320 RESEARCH530 SALES6步骤2外连接.分析:为什么40号部门没有统计出来原因：因员工表里面没有40号部门的员工(现象)where d.deptno = e.deptno (sqI)问题的本质404040=二= 期望：在连接条件不成立的条件下，也要把部门编号40给显示出来外连select d.deptno, d.dname, count(e.empno)from dept d, emp ewhere d.deptno = e.deptno (+)group by d.deptno, d.dname左外连接:(+)写在二号的右边自连接：一查询员工信息，老板信息显示：*的老板是*自连接：把一张表看成两张表启连接员工表的老板是老板表的员工select e.ename, b.ename©黑马程序员from emp e, emp b where e.mgr = b.empno select e.ename| '的老板是'b.enamefrom emp e, emp bwhere e.mgr = b.empno=需求把员工表的每一条记录都显示出来3select e.ename|的老板是'b.enamefrom emp e, emp bwhere e.mgr = b.empno (+)4 select s.ename| 1的老板是考 nvl( b.enameJ他自己')from emp e, emp bwhere e.mgr = b.empno (+)SMITH的老板是FORDMGR HIREDATESALMPNO ENAME JOBCOMM DEPTNO7369 SMITHCLERK7902 17-12 月-80800207499 ALLENSALESMAN7698 20-2 月-811600303007521 WARDSALESMAN7698 22-2 月-811250305007566 JONESMANAGER7839 02-4 月-812975207654 MARTINSALESMAN7698 28-9 月-8112507698 BLAKEMANAGER7839 01-5 月-8128507782 CLARK7788 SCOTT7839 KING7844 TURNER307876 ADAMS7900 JAMES7902 FORDMANAGERANALYSTPRESIDENTSALESMANCLERKCLERKANALYST7839 09-6 月-817566 19-4 月-8717-11 月-817698 08-9 月-817788 23-5 月-877698 03-12 月-817566 03-12 月-81245030005000150011009503000301020100203020
