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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了JAVA程序设计(20)-----查询信息的数据库代码，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3631字，纯文字阅读大概需要6分钟。

内容图文

增删改查 据说查询是最困难的……各种组合查询 联表查询 #0. 查询最高工资及其对应员工姓名select ename, sal from empwhere sal=(select max(sal) from emp);#如果有多个员工都是最高工资下面的方式将失效select ename, sal from emp ORDER BY sal desc lim

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

#0. 查询最高工资及其对应员工姓名
select ename, sal from emp
where sal=(select max(sal) from emp);

#如果有多个员工都是最高工资下面的方式将失效
select ename, sal from emp ORDER BY sal desc limit 0, 1;

#补充1：能否不使用聚合函数查出最高工资及其对应员工姓名
select ename, sal from emp
where sal=(select sal from emp order by sal desc limit 0,1);

#补充2：既不用排序也不用聚合函数查出最高工资及其对应员工姓名
select ename, sal from emp
where sal not in 
(select distinct t1.sal from emp as t1
inner 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 emp
group by dno) as t1, dept as t2
where t1.dno=t2.dno;	#没有联接条件将产生笛卡尔积

SELECT dname as 部门名称, 总人数 FROM
(select dno, count(dno) as 总人数 from emp
group by dno) as t1 INNER JOIN dept as t2
on t1.dno=t2.dno;

#补充：把没有员工的部门也显示出来
SELECT dname as 部门名称, if(total is null, 0, total) as 总人数 
FROM (select dno, count(dno) as total from emp
group by dno) as t1 RIGHT JOIN dept as t2
on 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 t1
on t1.dno=t2.dno;

#3.【本文来自鸿网互联 (http://www.68idc.cn)】 求挣最高薪水的员工(boss除外)的姓名
select ename, sal from emp
where sal=(select max(sal) from emp where mgr is not null);

#4. 查询薪水超过平均薪水的员工的姓名和工资
select ename, sal
from 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 t2
where 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 t2
on 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 t2
on 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, avgSal
from (select dno, avg(sal) as avgSal from emp
group by dno) t1 
where avgSal=(select max(avgSal) from 
(select dno, avg(sal) as avgSal from emp group by dno) as t2)) as t3
inner 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;

内容总结

以上是互联网集市为您收集整理的JAVA程序设计(20)-----查询信息的数据库代码全部内容，希望文章能够帮你解决JAVA程序设计(20)-----查询信息的数据库代码所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端