小明写这篇文章的目的是为了记录阅读《SQL经典实例》(安东尼-莫利纳罗著,刘春辉译)中的收获。这本书里面有MySQL、Oracle、Postgresql的语法实例。但本人目前用的是MySQL,所以,只摘录了MySQL的相关内容。样例SQL都在Navicat+MySQL8中测试过。我的文章只会收录我觉得有用的东西。对于一些特别基础的知识点和百度能解决的工具类知识点,我会略过或简单提一下。由于篇幅太长了,分为上下两部。
表的结构和数据
检索记录
1、实际开发中,不推荐select *,为了可读性,请指明select的每一列。(我这里是为了方便才写select * 的)
2、
select * from emp
-> where deptno=10 or comm is not null or sal <= 2000 and deptno=20;
- deptno等于10,或
- comm不空,或
- deptno等于20,并且,工资不高于2000的员工
select * from emp
-> where (deptno=10 or comm is not null or sal <= 2000) and deptno=20;
- deptno=10,或,comm不空,或,工资小于等于2000
- 并且,deptno=20
3、select指定特定的列名,可以过滤无关的数据。避免将时间浪费在检索不需要的数据上。
4、创建有意义的列名,别名
select sal as salary, comm as commission
from emp;
5、在where子句中引用别名列
例如,
select sal as salary, comm as commission from emp where salary < 5000;【1】
这样会报错。
select * from
(select sal as salary, comm as commission from emp) x
where salary < 5000;【2】
OK了!将查询包装为一个内嵌视图。where子句会比select子句先执行。所以【1】报错。因为from子句比where子句先执行,所以【2】OK。当表里的某些列命名不规范的时候,可以用这个技巧。
6、串联多列的值
将多列值合并为一列。
select concat(ename, ' works as a ', job) as msg
from emp
where deptno=10;
7、在select语句中使用条件逻辑
在select语句中针对查询结果值执行if-else操作。
select ename, sal,
case when sal <= 2000 then 'underpaid'
when sal >= 4000 then 'overpaid'
else 'OK'
end as status
from emp;
8、限定返回行数
限定查询结果的行数,不关心排序
select * from emp limit 5;
9、随机返回若干行记录
select ename, job from emp
order by rand() limit 5;
10、将null值转换为实际值
有一些行里面有null,但想在返回结果中将其替换为非null值。
select empno,coalesce(comm, 0) from emp limit 5;
11、查找匹配项
select ename, job from emp
where deptno in (10,20);
select ename, job from emp
where deptno in (10,20) and
(ename like '%I%' or job like '%ER');
查询结果排序
1、以指定顺序返回查询结果
可以不指定用于排序的列名,而用一个数值来代替该列。数值从1开始,从左到右匹配select列表里的列。
select ename, job, sal
from emp where deptno=10
order by sal desc;
等价于
select ename,job,sal
from emp where deptno=10
order by 3 desc;
2、多字段排序
select empno, deptno, sal, ename, job
from emp
order by deptno asc, sal desc;
3、依据子串排序
想按照一个字符串的特定部分排序查询结果。例如,从emp表中检索员工的名字和职位,并按照职位字段的最后3个字符对查询结果进行排序。
利用数据库中的子串函数,substr截取从指定起始位置开始到字符串结束的所有字符。
select ename, job
from emp
order by substr(job, length(job)-2);
4、排序时对null值的处理
emp表的comm列进行排序,但comm可能为null。想把null的行放在后面。
/*非null的comm升序,null的comm放在最后面*/
select ename, sal, comm
from
(select ename, sal, comm,
case when comm is null then 0 else 1 end as is_null
from emp) x