sql查询汇总 2

今天终于在看完视频教程后 ，把内连接查询自己敲了一遍  感觉还不错  特整理如下

--内连接
--1 select * from A,B;
select * from emp,dept;--总共70行 11列
--2,select * from A,B where ...
select * from emp,dept where emp.deptno=dept.deptno;--14行 11列
--等价于
select * from emp "A" --相当于给emp表起了一个别名
,dept"B" where A.deptno=B.deptno;
--3 select * from A join B on...
--上面等价于
select * from emp "A" join dept "B" --join 是连接的意思
on A.deptno=B.deptno; --on是连接条件
--重新排序
select * from emp "A" join dept "B"
on A.deptno=B.deptno order by A.deptno;
--也可以指定字段的名称
select "E".ename "员工姓名","D".deptno "部门编号" from emp "E" join dept "D" on "E".deptno="D".deptno; --这个其实没什么实际意义

--4 把工资大于2000的员工的姓名和部门的名称输出
--sql 99标准
select emp.ename "员工姓名",dept.deptno "部门名称" from emp join dept on dept.deptno=emp.deptno where emp.sal>2000;
--在这里 where emp.sal>2000 理论上可以放在join前面 但实际上时不可以的
--下面用sql 92 标准实现同样的效果
select emp.ename "员工姓名",dept.deptno "部门名称" from emp,dept where emp.deptno=dept.deptno and emp.sal>2000;

--sql 92 在条件较少时和sql 99的性能差不多 但是 当条件较多时。sql99就显示出了他的优越性 请看下面的例子

--把工资大于2000的员工的姓名和部门的名称输出 和 工资的等级 (这是三个表的连接)
--sql92实现方式
select emp.ename "员工姓名" ,dept.deptno "部门编号",salgrade.grade "工资等级" from emp,dept,salgrade where emp.sal>2000 and (emp.sal>=losal
and emp.sal<=hisal) and emp.deptno=dept.deptno; --多个and连接 where 后面加上所有的链接条件 容易混乱

--sql 99实现方式
select emp.ename "员工姓名" ,dept.deptno "部门编号",salgrade.grade "工资等级" from emp join dept on emp.deptno=dept.deptno
join salgrade on emp.sal>=losal and emp.sal<=hisal where emp.sal>2000; --where不能再join前面 用join是一个一个连接的

--输出的是350行 14列 行数是乘积 列数是之和
select *
from emp "E"
join dept "D"
on 1=1
join salgrade "S"
on 1=1
--求出每个员工的姓名 部门编号 薪水 和 薪水的等级
select "E".ename "员工姓名","E".deptno "部门编号" ,"E".sal "员工工资","G".grade "工资等级" from emp "E"
join salgrade "G" on "E".sal>"G".losal and "E".sal<="G".hisal;

--查找每个部门的编号 该部门所有员工的平均工资 平均工资的等级
select "T".deptno "部门编号","T".avg_sal "平均工资","G".grade "平均工资等级"from
(select deptno,avg(sal) "avg_sal" from emp "E"group by deptno ) "T" --构造一个新表
join salgrade "G" on "T".avg_sal>="G".losal and "T".avg_sal<="G".hisal; --利用新表来连接 --99标准
--等价于

select "T".deptno "部门编号","T".avg_sal "平均工资","G".grade "平均工资等级"from
(select deptno,avg(sal) "avg_sal" from emp "E"group by deptno ) "T" ,salgrade "G"
where "T".avg_sal>="G".losal and "T".avg_sal<="G".hisal; --92标准

--等价于

select "T".deptno "部门编号","T".avg_sal "平均工资","G".grade "平均工资等级"from
salgrade "G" join (select deptno,avg(sal) "avg_sal" from emp "E"group by deptno ) "T"
on "T".avg_sal>="G".losal and "T".avg_sal<="G".hisal; --join连接的表交换顺序不会影响结果

-- 查找每个部门的编号 部门名称 该部门所有员工的平均工资 平均工资的等级
select "T".deptno "部门编号" , "D".dname "部门名称","T".avg_sal "部门平均工资","G".grade "工资等级" from
(select deptno,avg(sal) as "avg_sal" from emp group by deptno
) "T"
join dept "D" on "D".deptno="T".deptno
join salgrade "G" on "T".avg_sal>="G".losal and "T".avg_sal<="G".hisal;
--求出emp表中所有领导的信息
select * from emp
where empno in (select mgr from emp)
--求出emp表中所有非领导的信息
select * from emp
where empno not in (select mgr from emp)
--求出平均薪水最高的部门的编号和部门的平均工资
select top 1 deptno "部门编号",avg(sal) as "部门平均工资"
from emp group by deptno
order by avg(sal) desc;
--等价于
select "E".*
from (
select deptno, avg(sal) "avg_sal"
from emp
group by deptno
) "E"
where "E".avg_sal = (
select max("avg_sal") from (select deptno, avg(sal) "avg_sal" from emp group by deptno) "T"
)
--有一个人工资最低 把这个人排除掉
--剩下的人中工资最低的前3个人的姓名 工资 部门编号 部门名称 工资等级 输出
select "F".ename,"F".deptno,"F".sal,"D".dname,"S".grade from
(select top 3*from
(select top 4*
from emp order by sal) "T" order by "T".sal desc) "F"
join dept "D" on "D".deptno="F".deptno
join salgrade "S" on "F".sal>="S".losal and "F".sal<="hisal" order by "F".sal
--下面是郝斌老师写的 上面是我自己写的 我的果然更复杂
select top 3 "T".ename, "T".sal, "T".deptno, "D".dname, "S".grade
from (
select *
from emp "E" --153行
where sal > ( select min(sal) from emp )
) "T" --这里的"E"与153行的"E"是没有冲突的，因为范围不一样
join dept "D"
on "T".deptno = "D".deptno
join salgrade "S"
on "T".sal between "S".losal and "S".hisal
order by "T".sal asc

 

这是依靠 scott 数据库的  请到 http://www.17xuexiba.com/index.php/archives/611  下载该数据库

 

文件下载地址  ： 华为网盘下载   115网盘下载