========================================别名引用=======================================
=======================================================================================
将取别名的查询作为内联视图,便可以在外部查询中引用其中的别名。
(聚集函数、标量子查询、视窗查询、别名)
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