JAVA程序设计(20)----- 查询信息的数据库代码

增删改查 据说查询是最困难的……各种组合查询 联表查询 

#0. 查询最高工资及其对应员工姓名select ename, sal from empwhere sal=(select max(sal) from emp);#如果有多个员工都是最高工资下面的方式将失效select ename, sal from emp ORDER BY sal desc limit 0, 1;#补充1：能否不使用聚合函数查出最高工资及其对应员工姓名select ename, sal from empwhere sal=(select sal from emp order by sal desc limit 0,1);#补充2：既不用排序也不用聚合函数查出最高工资及其对应员工姓名select ename, sal from empwhere sal not in (select distinct t1.sal from emp as t1inner join emp as t2 on t1.sal<t2.sal);#1. 计算每位员工的年薪select ename as 姓名, (sal+if(comm is null, 0, comm))*12 as 年薪 from emp order by 年薪 DESC;#2. 统计有员工的部门的人数select dname as 部门名称, 总人数 from(select dno, count(dno) as 总人数 from empgroup by dno) as t1, dept as t2where t1.dno=t2.dno;#没有联接条件将产生笛卡尔积SELECT dname as 部门名称, 总人数 FROM(select dno, count(dno) as 总人数 from empgroup by dno) as t1 INNER JOIN dept as t2on t1.dno=t2.dno;#补充：把没有员工的部门也显示出来SELECT dname as 部门名称, if(total is null, 0, total) as 总人数 FROM (select dno, count(dno) as total from empgroup by dno) as t1 RIGHT JOIN dept as t2on t1.dno=t2.dno;SELECT dname as 部门名称, if(total is null, 0, total) as 总人数 FROM dept as t2 LEFT JOIN (select dno, count(dno) as total from emp group by dno) as t1on t1.dno=t2.dno;#3. 求挣最高薪水的员工(boss除外)的姓名select ename, sal from empwhere sal=(select max(sal) from emp where mgr is not null);#4. 查询薪水超过平均薪水的员工的姓名和工资select ename, salfrom emp where sal>(select avg(sal) from emp);#5. 查询薪水超过其所在部门平均薪水的员工的姓名、部门名称和工资#where写法select ename, dname, t3.sal from(select eno, t1.dno, sal from emp as t1,(select dno, avg(sal) as avgSal from emp group by dno) as t2where t1.dno=t2.dno and sal>avgSal) as t3, emp as t4, dept as t5 where t3.eno=t4.eno and t5.dno=t3.dno;#inner join写法select ename, dname, t3.sal from(select eno, t1.dno, sal from emp as t1 inner join(select dno, avg(sal) as avgSal from emp group by dno) as t2on t1.dno=t2.dno and sal>avgSal) as t3 inner join emp as t4 on t3.eno=t4.eno inner join dept as t5 on t5.dno=t3.dno;#6. 查询部门中薪水最高的人姓名、工资和所在部门名称select ename, dname, t3.sal from(select eno, t1.dno, sal from emp as t1 inner join(select dno, max(sal) as maxSal from emp group by dno) as t2on t1.dno=t2.dno and sal=maxSal) as t3 inner join emp as t4 on t3.eno=t4.eno inner join dept as t5 on t5.dno=t3.dno;#7. 哪些人是主管select * from emp where eno in (select distinct mgr from emp);select * from emp where eno=any(select distinct mgr from emp);#补充：哪些人不是主管select * from emp where eno not in (select distinct mgr from emp where mgr is not null);#8. 求平均薪水最高的部门的名称和平均工资select dname as 部门名称, avgSal as 平均工资 from(select dno, avgSalfrom (select dno, avg(sal) as avgSal from empgroup by dno) t1 where avgSal=(select max(avgSal) from (select dno, avg(sal) as avgSal from emp group by dno) as t2)) as t3inner join dept as t4 on t3.dno=t4.dno;#9. 求薪水最高的前3名雇员select * from emp order by sal desc limit 0,3;#10.求薪水排在第4-6名雇员select * from emp order by sal desc limit 3,3;