基本查询
查询工资高于500 或 岗位是MANGER的雇员,同时还要满足他们的姓名首字母为大写J
select ename from emp where (sal > 500 OR job = 'MANGER') and substring(ename,1,1) = 'J';
按照部门号升序、工资降序排序
select * from emp order by deptno asc,sal desc;
使用年薪降序排序
select sal*12+ifnull(comm,0) as sum from emp order by sum desc;
显示工资最高的员工的名字和工作岗位
select * from emp where sal = (select max(sal) from emp);
显示工资高于平均工资的员工信息
select * from emp where sal > (select avg(sal) from emp);
显示每个部门的平均工资和最高工资
select deptno,format(max(sal),2) 最高 , format(avg(sal),2) 平均 from emp group by deptno;
显示平均工资低于2000的部门号和它的平均工资
select deptno,avg(sal) 平均 from emp group by deptno having 平均 > 2000;
显示每种岗位的员工总数,以及平均工资
select job,count(*) 人数,avg(sal) 平均工资 from emp group by job;
多表查询
显示雇员名、雇员工资、以及员工所在部门的名字
两张表进行笛卡尔积。即用第一张表的一条数据和第二张表的所有数据进行组合。
select emp.ename,sal,dname from emp,dept where emp.deptno=dept.deptno;
显示各个员工的姓名、工资、工资级别
select ename,sal,grade from emp,salgrade where sal between losal and hisal;
自连接
select * from salgrade as t1,salgrade as t2;
显示员工FORD的上级领导的编号和姓名
//子查询
select mgr from emp where ename = 'FORD';
select ename,emptno from emp where empno = (select mgr from emp where ename = 'FORD');
//自连接
select * from emp e1,emp e2 where e1.name='FORD' and e1.mgr=e2.empno;
子查询
嵌入在其他sql语句中的select语句,也叫嵌套查询
单行子查询
显示与SMITH同一部门的员工
select dptno from emp where ename = 'SMITH';
select * from emp where deptno=(select dptno from emp where ename = 'SMITH')
多行子查询
查询和10号部门的工作岗位相同的雇员的名字、岗位、工资、部门号,但不包含10号自己的
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno <> 10;
还要员工的部门名字:
select * from (select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno <> 10) as tmp , dept where dept.deptno=tmp.deptno;
显示工资比部门30号 所有员工的工资高的员工的姓名、工资、和部门号
select * from emp where sal > (select max(sal) from emp where deptno=30);
select * from emp where sal > all(select distinct sal from emp where deptno=30);
显示工资比30号部门的任意员工的工资高的员工的姓名工资和部门号
select * from emp where sal > any(select distinct sal from emp where deptno=30);
多列子查询
查询和SMITH部门和岗位完全相同的雇员,不含SMITH本人
select * from emp where (dept,job) in (select deptno,job from emp where ename='SMITH') and ename <> 'SMITH';
在from语句中使用子查询
显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
//1.每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
//2.进行笛卡尔积
select * from emp,(select deptno,avg(sal) from emp group by deptno) as tmp;
//3.筛选正确的部门
select * from emp,(select deptno,avg(sal) from emp group by deptno) as tmp where emp.deptno = tmp.deptno;
//4.单表查询
select * from emp,(select deptno,avg(sal) myavg from emp group by deptno) as tmp where emp.deptno = tmp.deptno and emp.sal > tmp.myavg;
查看这一些人的办公地点:
1.上面的查询结果和dept表进行笛卡尔积
2.筛选合适的行
3.按条件得到要求的
查找每个部门工资最高的人的姓名、工资、部门、最高工资
//1.每个部门的最高工资
select deptno,max(sal) from emp group by deptno;
//2.和emp表笛卡尔积,找到谁是最高的那个人
select ename,sal,t1.deptno,mymax from emp t1, (select deptno,max(sal) mymax from emp group by deptno) t2 where t1.deptno = t2.deptno and t1.sal = t2.mymax;
//3.
显示每个部门的信息(部门名、编号、地址)和人员数量
- 使用子查询
//各部门人员数量
select deptno,count(*) dept_num from emp group by deptno;
//跟dept表进行笛卡尔积
select t1.dname,t1.loc,t2.dept_num,t1.deptno from dept t1,(select deptno,count(*) dept_num from emp group by deptno) t2 where t1.deptno=t2.deptno;
多表查询的本质:将多表转换成单表。
合并查询
union
该操作符用于取得两个结果集的并集,当使用该操作符时,会自动去掉结果集中的重复行(自动去重)。要拼接的时候要满足两个列一致,否则不能拼。
将工资大于2500 或 职位是MANAGER的人找出来
select * from emp where job = 'MANNAGER' union select * from emp where job = 'MANAGER';
union all
该操作符不会对满足多个条件的数据进行去重,而是保留。