初学者容易犯的错误，SQL中的基本操作问题（mysql）一

=======================================================================================
========================================别名引用=======================================
=======================================================================================
将取别名的查询作为内联视图，便可以在外部查询中引用其中的别名。
(聚集函数、标量子查询、视窗查询、别名)
select * 
  from (
select sal as salary, comm as commission
  from emp
) x
where salary < 5000;

因为 where 子句是在select之前进行处理的，在where处理之前别名并不存在，
然而from子句是在where之前进行的，所以别名已经存在，可以进行引用。


=======================================================================================
===========================链接多列内容为一列（concat）================================
=======================================================================================
SELECT
concat(t.guojia, t.`name`, t.sex, t.wuli) as massage
FROM
sanguo AS t
where t.guojia = 1

=======================================================================================
=========在select语句中使用条件逻辑（数值执行if-else操作）==========
=======================================================================================
select 
t.`name`,
t.wuli,
case when t.wuli <= 70 then 'yiban'
when t.wuli >= 90 then 'chuse'
else 'OK'
end as statusdata
from 
sanguo as t;

case表达式可以针对查询的返回值执行条件逻辑，可以给case取别名，是程序更易读。
else子句是可选的，如果没有则会在不满足的条件行，case表达式返回null。

=======================================================================================
================================从表中随机查询n行数据================================
=======================================================================================
SELECT 
t.wuli
FROM
sanguo AS t
ORDER BY
RAND() LIMIT 10;

=======================================================================================
================================空值null的查询和转化================================
=======================================================================================
查询空值必须使用：is null (is not null)
如：select 
t.wuli 
    from 
sanguo as t 
    where t.guojia is null;



使用coalesce函数用实际的值来替换空值null；（为空则替换，否则保持原值。）
SELECT
COALESCE (t.wuli, 0)
FROM
sanguo AS t;


用case替换：
select 
(case 
   when t.wuli is null then 0
   else t.wuli 
end)
from sanguo as t;

=======================================================================================
=====================================处理排序中的空值null================================
=======================================================================================
解决方法：通过case语句添加标记列来处理；null值是排在前面还是后面。
select name, sal, comm
  from (
select ename,sal,comm,
case when comm is null then 0 else 1 end as is_null
from emp
) x
order by x.is_null desc, comm;

=======================================================================================
================根据数据项的键排序（根据某些条件逻辑排须）=============================
=======================================================================================
select ename, sal, job, comm
  from emp
order by 
  case when job = 'SALESMAN' then comm else sal end;

select ename, sal, job, comm
case when job = 'SALESMAN' then comm else sal end
  from emp
order by 5;


=======================================================================================
================从一个表中查找另一个表中没有的值=============================
=======================================================================================
mysql：利用子查询
select deptno
  from dept 
where deptno not in (select deptno from emp);

由于in相当于逻辑or容易受null值的影响。
解决方法：
select d.deptno
  from dept d
where not exists (select null 
from emp e 
 where d.deptno = e.deptno)

not exists、exists将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。

=======================================================================================
========================在一个表中查找与其他表不匹配的记录=============================
=======================================================================================
解决方法：首先返回一个表中的所有行，以及另一个表中与公共列匹配的行或不存在匹配的行，然后仅保留不匹配的行。
（outer 可选）
select d.*
  from dept d left outer join emp e
    on (d.deptno = e.deptno)
where e.deptno is null;

匹配不成功的将用null补充。这种方案中使用外链接，然后保留不匹配的记录。这种操作有时也被称为反链接。


=======================================================================================
========================向查询中增加链接而不影响其他链接=============================
=======================================================================================
当查询了一部分结果后，要再增加一个字段信息，直接判断会影响前面的结果
解决方法1（外链接）：
select e.ename, d.loc, eb.received
  from emp e join dept d
    on (e.deptno = d.deptno)
  left join emp_bonus eb   #外链接一个字段附加信息
    on (e.empno = eb.empno)
order by 2;

解决方法2（标量子查询（放在select中的子查询）也是一个关联查询）：
select e.ename, d.loc, 
(select eb.recived from emp_bonus eb 
  where eb.empno = e.empno) as recived
  from emp e, dept d
where e.deptno = d.deptno
order by 2;

参考文献：SQL.Cookbook