SQL

/* very very important! */ select ename, deptno from emp; select deptno, dname from dept; -------------------------------------------------------------------------------------- --老语法：---------------------------------------------------------------------------- -------------------------------------------------------------------------------------- --等值连接：求员工姓名以及员工所在部门的名字同时显示出来 select ename,dname from emp,dept where emp.deptno = dept.deptno --非等值连接：要求每位雇员的薪水等级 select emp.empno,emp.ename,emp.sal,salgrade.grade from emp ,salgrade where emp.sal between salgrade.losal and salgrade.hisal; --取别名 select a.empno,a.ename,a.sal,b.grade from emp a ,salgrade b where a.sal between b.losal and b.hisal; --跨3个表：求工作职位是’PRESIDENT’的雇员姓名，部门名称和薪水等级时 --使用通配符查询结果会有2个deptno,一个为emp表中的，一个为dept表中的！ --如果以后使用到deptno作为查询入口，可能会引起异常发生 select * from emp a,dept b,salgrade c where a.deptno=b.deptno and a.sal between c.losal and c.hisal and job = 'PRESIDENT' --给表起别名 select a.*,b.dname,c.* from emp a,dept b,salgrade c where a.deptno=b.deptno and a.sal between c.losal and c.hisal and job = 'PRESIDENT' --也可以同一个表做跨表连接：求每位员工的姓名，及其上级经理的姓名 select a.empno,a.ename,a.mgr,b.ename manager_name,b.empno manager_deptno,b.mgr his_manager from emp a, emp b where a.mgr = b.empno select ename from emp where empno = 7839 -------------------------------------------------------------------------------------- --新语法------------------------------------------------------------------------------ --在SQL1992的语法规则中，语句过滤的条件和表连接的条件都被放在了where子句中，当条件过多时，容易造成混淆， --SQL1999修正了这个缺点，将连接条件和数据过滤条件区分开来， -------------------------------------------------------------------------------------- A join B on ... --交叉连接 --结果会产生这两张表的笛卡尔乘积 select * from emp cross join dept --要用deptno作为等值连接条件，我们可以这样写(using) select * from emp join dept using(deptno) --相当于(on) select * from emp join dept on emp.deptno = dept.deptno --也可以用于非等值连接 --求每位雇员的薪水等级 select ename,grade from emp a join salgrade b on a.sal between b.losal and b.hisal --多个join,where组合使用 --(求工作职位是’PRESIDENT’的雇员姓名，部门名称和薪水等级时) select a.ename,b.dname,c.grade from emp a join dept b on a.deptno=b.deptno join salgrade c on a.sal between c.losal and c.hisal where job = 'PRESIDENT' --也可以同一个表做跨表连接：求每位员工的姓名，及其上级经理的姓名 select e1.ename,e1.mgr,e2.empno,e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno --外连接--取出表中连接不到一起的多余的数据 --left inner join可以缩写成inner join 也可以缩写成join，意思是左内。 --update emp set deptno=null where ename='SMITH'; --commit; --左内连接 --左外连接（以左边的表为中心，逐条拿出数据与右表匹配，确保左边表中每条数据都能匹配到数据，即使匹配到为空） select e1.ename,e1.mgr,e2.empno,e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno select e1.ename,e1.mgr,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+) --右外连接 select e1.ename,e1.mgr,e2.empno,e2.ename from emp e1 right join emp e2 on e1.mgr = e2.empno select e1.ename,e1.mgr,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr(+) = e2.empno --右外连接转换为左外连接 select e1.ename,e1.mgr,e2.empno,e2.ename from emp e1 left join emp e2 on e1.empno = e2.mgr --全外连接 select e1.ename,e1.mgr,e2.empno,e2.ename from emp e1 full join emp e2 on e1.mgr = e2.empno -----特殊说明--------------------------------------------------------------------------- --没有全内连接 --没有右内连接 ----想想相当于什么？ --outer也可以省略，简写为left join , right join , full join --对于左内连接，可以写成join 或者inner join，不能写成left join，也不能写成left inner join --什么时候用外连接呢?比如领导向你要所有学生的列表,顺便把所属的班级也列出来,就需要外连接 --还可以画图来理解 -------------------------------------------------------------------------------------- --在Where语句中使用子查询--子查询结果当做一个结果(where)或者一张表(from) ----------------------------------------------------------------- --雇员中最高薪水的人员名称 --1,先求出最高薪水 --2,再求雇员中最高薪水的人员名称 select max(sal) from emp; select ename from emp where sal = (select max(sal) from emp); --有哪些人的薪水是在整个雇员的平均薪水之上的 select round(avg(sal),2) from emp; select ename,sal from emp where sal >= (select avg(sal) from emp); ----------------------------------------------------------------- --雇员中哪些人是经理人 --1,首先查询mgr中有哪些号码 --2,再看有哪些人员的号码在此出现 select ename from emp where empno in (select distinct mgr from emp); --雇员中哪些人不是经理人 --not in 如果结果集中存在null值，则整个结果为空！！！ select ename from emp where empno not in(select distinct mgr from emp); select ename from emp where empno not in(select distinct mgr from emp where mgr is not null); ----------------------------------------------------------------- --在From子句中使用子查询 ------------------------------------------------------------------ --部门平均薪水的等级 --1,首先将每个部门的平均薪水求出来 --2,然后把结果当成一张表，再用这张结果表和salgrade表做连接，以此求得薪水等级 select avg(sal) from emp group by deptno; select deptno,avg_sal,grade from (select deptno,avg(sal)avg_sal from emp group by deptno)t,salgrade s where t.avg_sal between s.losal and s.hisal; select deptno,avg_sal,grade from (select deptno,avg(sal)avg_sal from emp group by deptno)t join salgrade s on t.avg_sal between s.losal and s.hisal; ----------------------------------------------------------------- --每个部门最高薪水的人员名称 --1,首先将每个部门的最高薪水求出来 --2,然后把结果当成一张表，再用emp和这张结果表做连接，以此求得每个部门最高薪水的人员名称 select deptno,max(sal) from emp group by deptno; select emp.deptno,ename,sal from emp join (select deptno,max(sal)max_sal from emp group by deptno)t on emp.sal = t.max_sal and emp.deptno = t.deptno; select ename,sal from emp where sal >= 3000; ----------------------------------------------------------------- --课堂练习：哪些人的薪水在部门的平均薪水之上（和上面的例子的思路一样。） --1,首先将每个部门的平均薪水求出来 --2,然后把结果当成一张表，再用emp和这张结果表做连接，以此求得哪些人的薪水在部门的平均薪水之上 select deptno,avg(sal)dept_avg_sal from emp group by deptno; --比较sal>=avg_sal必须附加条件：在同一个部门中进行判断--->emp.deptno = t.deptno select emp.ename,emp.sal,dept_avg_sal from emp,(select deptno,avg(sal)dept_avg_sal from emp group by deptno)t where emp.sal >= t.dept_avg_sal; --一定要在同一个部门下比较薪水才有意义！ select emp.ename,emp.sal,dept_avg_sal from emp,(select deptno,avg(sal)dept_avg_sal from emp group by deptno)t where emp.sal >= t.dept_avg_sal and emp.deptno = t.deptno; ----------------------------------------------------------------- --求部门中(所有人的)平均的薪水等级,形式如： -- deptno avg_grade -- 10 3.67 -- 20 2.8 -- 30 2.5 --1,先求每个人的薪水等级 --2,再按照部门分组,求平均数 select emp.ename,grade from emp join salgrade on emp.sal between salgrade.losal and salgrade.hisal; --需要注意的是，外层的select查询的字段是子查询中的虚拟表中的字段 --所以，外层使用到的查询字段必须在虚拟表中查询出来 select t.deptno,avg(t.grade) from (select deptno,grade from emp join salgrade on emp.sal between salgrade.losal and salgrade.hisal)t group by t.deptno; ------------------------------------------------------------------------------------------ --使用伪字段:rownum，----------------------分页，很重要！ -----------------