MySQL学习记录——구 复合查询

---

1、基本查询

看一些例子，不关心具体内容，只看写法

//查询工资高于500或岗位为MANAGER的雇员, 同时还要满足他们的姓名首字母为大写的J
select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';
select * from emp where (sal>500 or job='MANAGER') and substring(ename, 1, 1)=='J';
//按照部门号升序而雇员的工资降序排序
select ename, sal, deptno from emp order by deptno asc, sal desc;
//年薪降序排序
select ename, sal, comm, sal*12+ifnull(comm, 0) 年薪 from rmp order by 年薪 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(*) 人数, format(avg(sal),2) 平均工资 from emp group by job;

2、多表查询

从之前scott库中的emp和dept两个表来查询。

接下来要对两个表进行穷举组合，这也就是在做笛卡尔积。

//显示雇员名、雇员工资以及所在部门的名字
select ename,sal,dname from emp, dept where emp.deptno=dept.deptno;
//部门号为10的部门名, 员工名和工资
select ename, sal, dname, dept.deptno from emp, dept where emp.deptno=dept.deptno and emp.deptno=10;
//显示各个员工的姓名, 工资和工资级别
select ename, sal, grade, losal, hisal from emp, salgrade where sal between losal and hisal;

3、自连接

自己和自己做笛卡尔积

对emp表做笛卡尔积，暂命名为e1和e2

select * from emp e1, emp e2;

select e2.ename, e2.empno from emp e1, emp e2 where e1.ename='FORD' and e1.mgr=e2.empno;

4、子查询

子查询是指嵌套在其它sql语句中的select语句，也叫嵌套查询。多表问题的解决办法就是将多表转为单表。

---

1、多行子查询

//显示和SMITH同一部门的员工
select * from emp where deptno=(select deptno 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;
//显示工资比部门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);

---

2、多列子查询

//查询和SMITH的部门和岗位完全相同的所有雇员, 不含SMITH本人
select * from emp where (deptno, job) in (select deptno, job from emp where ename='SMITH') and ename <> 'SMITH';

查询出来的临时结构也是表结构，所以可以把它们as成一个表，然后对这个表再做操作。

---

3、from句中的子查询

//显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
select ename, emp.deptno from emp, (select deptno, avg(sal) myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal > tmp.myavg;

select * from dept, (select ename, emp.deptno from emp, (select deptno, avg(sal) myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal > tmp.myavg) t1;

select * from dept, (select ename, emp.deptno from emp, (select deptno, avg(sal) myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal > tmp.myavg) t1 where t1.deptno=dept.deptno;

select t1.ename, dept.loc, t1.deptno from dept, (select ename, emp.deptno from emp, (select deptno, avg(sal) myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal > tmp.myavg) t1 where t1.deptno=dept.deptno;

//查找每个部门工资最高的人的姓名、工资、部门、最高工资
select deptno, max(sal) from emp group by deptno;

select * from emp t1, (select deptno, max(sal) from emp group by deptno) t2 where t1.deptno = t2.deptno;

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;

//显示每个部门的信息(部门名、编号、地址)和人员数量
select deptno, count(*) dept_num from emp group by deptno;

select * from dept t1, (select deptno, count(*) dept_num from emp group by deptno) t2;

select * from dept t1, (select deptno, count(*) dept_num from emp group by deptno) t2 where t1.deptno=t2.deptno;

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;

5、合并查询

多个select结果合并。

union得到两个结果集的并集，并去掉重复行；union all则不去重。

//将工资大于2500或职位是MANAGER的人找出来
select * from emp where sal > 2500 union all select * from emp where job='MANAGER';

结束。