《SQL CookBook》 简单查询汇总

2019独角兽企业重金招聘Python工程师标准>>>

    下载了《SQL CookBook》，认真专研一下，基础是很重要的，是时候拾回那些悄悄溜走的知识了。

1.       查询结果排序

 

1.1    显示部门10中的员工名字、职位和工资，并按照工资的升序排列。

select ename,job,sal from emp order by sal asc;

 

1.2    在EMP表中，首先按照DEPTNO的升序排序，然后按照工资的降序排列。

select empno,deptno,sal,ename,job from emp order by deptno asc,sal desc

 

1.3 从EMP表中返回员工名字和职位，并且按照职位字段的最后两个字符排序。

select ename,job from emp order by substr(job,length(job)-2);

 

1.3    在EMP中根据COMM排序结果，处理控制排在最后。

select ename,sal,comm from ( select ename,sal,comm ,case when comm is null  then 1 else 0 end as is_null from emp) x order by is_null,comm;

 

1.4    如果JOB 是“SALESMAN”,要根据COMM来排序。否则，根据SAL排序。

select ename,sal,job,comm

    -> from emp

    -> order by

    -> case when job='SALESMAN' then comm

-> else sal end;

 

2.      操作多个表

 

2.1    要显示EMP表部门10中员工的名字和部门编号，以及DEPT表中每个部门的名字和部门编号。

mysql> select ename as ename_and_dname,deptno from emp where deptno=10

    -> union all

    -> select line,null from t1

    -> union all

-> select dname,deptno from dept;

 

2.2    显示部门10 中所有员工的名字，以及每个员工所在部门的工作地点。

select ename,loc rom emp e,dept d where e.deptno = d.deptno and e.deptno = 10;

 

select ename,loc from emp e join dept d on(e.deptno=d.deptno) where e.deptno = 10; （等值内联）

2.3    从表DEPT中查找在表EMP中不存在数据的所有部门。

mysql> select deptno

    -> from dept d

    -> where not exists

-> (select null from emp e where d.deptno=e.deptno);

 

子查询关注的不是返回结果，而是两个表之间的关系，所以是查询null；

1.       有对应关系，则有返回，not exists 为 false，对于最外层查询则无结果；

2.       没对应关系，则无返回，not exists 为 true，对于最外层查询则保留不存在的结果。

 

2.4查找没有职员的部门；

mysql> select d.*

    -> from dept d left join emp e

    -> on(d.deptno = e.deptno)

-> where e.deptno is null;

 

返回一个表中的所有行，以及另一个表中与公共列匹配或者不匹配的行，然后保存不匹配的行。

 

2.5    返回所有员工信息、他们的工作部门地点及所获得的奖励。

Mysql> select ename,loc,received

->from emp e join dept d on(e.deptno = d.deptno)

->left join emp_bonus eb on(e.empno = eb.empno);

 

标量子查询（不会危及到当前结果而获取额外数据的一种简单方法；要求标量子查询返回的是标量值）

mysql> select e.ename,d.loc,

    -> (select received from emp_bonus eb where eb.empno = e.empno)

    -> from emp e,dept d

    -> where e.deptno = d.deptno

-> order by 2;

 

2.6    查找在部门10中所有员工的工资合计和奖金合计。（并不是所有人都有奖金）

 

考虑是否有重复工资的计算，因为奖金表可能有一名员工有两个奖金纪录；

考虑部门里是否有没有奖金的员工，也要算入部门的总工资。

mysql> select deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonus

    -> from (

    -> select e.deptno,e.sal,e.sal*case

    -> when eb.type is null then 0

    -> when eb.type = 1 then .1

    -> when eb.type = 2 then .2

    -> else .3

    -> end as bonus

    -> from emp e left join emp_bonus eb

    -> on(e.empno = eb.empno)

-> where e.deptno = 10) x;

 

2.7    需要在表EMP中查找出所有比”WARD” 提成（COMM）低的员工，提成为NULL的员工也应包括在其中；

coalesce()函数 [keules]

mysql> select ename,comm, coalesce(comm,0)

    -> from emp

    -> where coalesce(comm,0) < (select comm from emp where ename = 'WARD');

转载于:https://my.oschina.net/kingchung/blog/35382