MySQL笔记（二）SQL经典实例（上）

 

小明写这篇文章的目的是为了记录阅读《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