select 是唯一只读的语句，也是用得最多的一条。
教材分了五种：简单、筛选、分组、排序、多表——其实就是在讲五个子句：select、where、
group by、order by、join。
这一篇的重点不是语法，是四个坑：and／or 的优先级、null 不参与比较、
分组后不能选非聚合列、漏写连接条件得到笛卡尔积。四个都不报错。
4.2 节的 scott 四张表从这里开始真正派上用场。dept 和 emp 会一直用到第 4 章结束。
这一篇的四个坑，后面 13 章讲事务、18 章讲优化时还会再遇到。
现在踩一次，比以后在线上踩便宜。
一、简单查询：挑列
select 后面写什么，结果就有哪些列。
可以写列名、可以写 *、可以写表达式、可以起别名。
sal * 12 as 年薪 这一句说明两件事：select 后面能算，as 能给结果列改名。
别名带空格或特殊符号必须加双引号，纯中文不必须但加上更保险。
select * 在生产代码里要慎用，理由有两个：
一是表加了列，* 会多带你没要的；
二是表改了大字段（比如加了个存图片的列），* 会把一堆没用的数据拉过网络。
自己试的时候随便用，写进程序就写明列名。
二、筛选查询：挑行
where 后面是条件，只留满足的行。
=、>、between、in、like、is null，六种够用九成场景。
between 1000 and 2000 是闭区间，两头都含，等价于 >= 1000 and <= 2000。
别写成 between 2000 and 1000——那在 Oracle 里返回 0 行，不报错，是最难查的一类错。
like 里 % 是任意多个字符，_ 是恰好一个字符。
要找真的下划线，得写 like '%\_%' escape '\'，否则它会被当通配符，把整表都匹配上。
这个坑在做「按编号模糊查」时几乎必踩一次。
三、and 比 or 先算
where a or b and c 实际算的是 a or (b and c)，因为 and 优先级高于 or。
很多人凭语感读成 (a or b) and c——那是错的。
上面那段 Demo 里，不加括号查出了 6 行，加括号只剩 2 行。
差别就是「文员这一半要不要受工资限制」。
规矩很简单：where 里同时出现 and 和 or，一律加括号。
不加括号不算错，但你要在脑子里跑一遍优先级——而人脑跑不过编译器。
四、null 不参与任何比较
NULL 不是 0，也不是空字符串，它的意思是「不知道」。
不知道等不等于 300，所以 comm != 300 对 NULL 行既不成立也不不成立——结果是「未知」。
而 where 只留「真」的行，「未知」一律被丢掉。
这就是为什么 where comm != 300 拿不到那 10 行 comm 为空的记录。
很多人以为「不等于 300」应该包含「不知道多少」——SQL 的答案是「不包含」。
判断空只能用 is null / is not null，不能写 = null。
comm = null 永远不成立，因为「不知道等于不知道」还是不知道。
聚合函数也统一跳过 NULL：count(comm) 只数非空，avg(sal) 的分母是非空个数。
唯一例外是 count(*)，它数的是行，不看列。
想让 NULL 当 0 参与，得写 nvl(comm, 0)——但要清楚那是另一个口径了。
五、分组查询：把行压成组
group by 把行按某列的值分成几堆，每堆算一个聚合值。
分组之后，select 里只能出现两种东西：分组列、聚合函数。
写别的在 Oracle 里就是语法错（ORA-00979: 不是 GROUP BY 表达式）。
道理也简单：一堆行压成一行了，你要那一堆里哪一行的 ename？没有答案。
where 和 having 的分工要记死：
where 在分组前过滤「行」，having 在分组后过滤「组」。
带聚合的条件（count(*) > 4）只能写在 having 里——
因为 where 执行时还没有「组」这个概念。
六、排序查询：null 排哪头
order by 永远是最后执行的一步。
所以它能用别名（order by 年薪），而 where 不能——where 跑的时候别名还不存在。
多列排序：每列可以各自带 asc／desc，不写就是 asc。
order by deptno, sal desc 是「先按部门升序，部门内按工资降序」。
NULL 排哪头，是 Oracle 和 SQLite 最容易对不上的地方：
Oracle 升序时 NULL 排最后（当成无穷大），SQLite 升序时排最前。
同一条 SQL，两套引擎给出两份不同的结果。
要统一就别依赖默认。
Oracle 写 order by comm nulls last；SQLite 写 order by comm is not null, comm。
nulls first／nulls last 是 Oracle 的扩展语法，SQLite 不认。
七、多表关联：把两张表接起来
两种写法：隐式连接（表用逗号隔开、条件写 where 里）和 join … on。
结果一样，安全性不一样。
漏写连接条件时，隐式连接会给你一张笛卡尔积——14 行乘 4 行等于 56 行，语法正确、结果全错。
而 join 漏了 on 会直接报语法错，反而安全。
所以推荐 join … on，理由只有一个：让漏写条件变成编译期错误，而不是运行期错误。
left join 保住左表所有行，这是它和 inner join 唯一的区别。
dept 里的 OPERATIONS 一个人都没有，inner join 会把这一行整个丢掉，
left join 会给它一个人数为 0 的行。
报表里「空部门」和「部门不存在」是两件事，别混。
八、动手 Demo：五种查询配四个坑，全部真跑
这一段把教材的五种查询一次演完，每一种配一句结论。
重点在四个坑上：and／or 优先级、null 比较、分组选列、笛卡尔积。
四个坑都不报错。 这是它们最恶劣的地方——你只能靠「结果看着不对」发现。
import sqlite3
c = sqlite3.connect(":memory:")
c.execute("create table dept (deptno int, dname text, loc text)")
c.execute("create table emp (empno int, ename text, job text, "
"mgr int, sal real, comm real, deptno int)")
c.executemany("insert into dept values (?,?,?)",
[(10,'ACCOUNTING','NEW YORK'), (20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'), (40,'OPERATIONS','BOSTON')])
c.executemany("insert into emp values (?,?,?,?,?,?,?)", [
(7369,'SMITH','CLERK',7902,800,None,20),
(7499,'ALLEN','SALESMAN',7698,1600,300,30),
(7566,'JONES','MANAGER',7839,2975,None,20),
(7788,'SCOTT','ANALYST',7566,3000,None,20),
(7839,'KING','PRESIDENT',None,5000,None,10),
(7902,'FORD','ANALYST',7566,3000,None,20),
(7934,'MILLER','CLERK',7782,1300,None,10),
])
# 1 简单查询：挑列，能算表达式、能起别名
print(c.execute("select ename, sal, sal * 12 as 年薪 from emp").fetchall())
# 2 筛选查询：挑行
print(c.execute("select ename from emp where sal between 1000 and 2000").fetchall())
# 3 and 比 or 先算：不加括号会算错
print(c.execute("select ename from emp where job = 'CLERK' "
"or job = 'ANALYST' and sal > 2000").fetchall())
print(c.execute("select ename from emp where (job = 'CLERK' "
"or job = 'ANALYST') and sal > 2000").fetchall())
# 4 null 不参与比较：!= 300 拿不到 comm 为空的行
print(c.execute("select count(*) from emp where comm != 300").fetchall())
print(c.execute("select count(comm) from emp").fetchall())
# 5 分组 + 排序 + 多表
print(c.execute("select deptno, count(*) from emp group by deptno").fetchall())
print(c.execute("select count(*) from emp, dept").fetchall()) # 笛卡尔积
print(c.execute("select d.dname, count(e.empno) from dept d "
"left join emp e on e.deptno = d.deptno "
"group by d.dname").fetchall())
实跑输出：
--------------------------------------------------------------------
第十七课：检索数据——五种查询，五个坑
--------------------------------------------------------------------
**`select` 是 SQL 里唯一「只读」的语句，也是用得最多的一条。**
**它只有一个目的：从表里挑出你要的行和列。**
**教材分了五种：简单查询、筛选查询、分组查询、排序查询、多表关联查询。**
**这个分法其实是在讲 `select` 语句的五个子句：`select`、`where`、`group by`、`order by`、`join`。**
一、简单查询：挑列
--------------------------------------------------------------------
SQL> select * from dept
DEPTNO DNAME LOC
--------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
已选择 4 行
SQL> select deptno, dname from dept
DEPTNO DNAME
-----------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
已选择 4 行
SQL> select ename, sal, sal * 12 as 年薪 from emp where deptno = 20 order by empno
ENAME SAL 年薪
----------------
SMITH 800 9600
JONES 2975 35700
SCOTT 3000 36000
ADAMS 1100 13200
FORD 3000 36000
已选择 5 行
**`sal * 12 as 年薪` 说明两件事：`select` 后面能写表达式，`as` 能给结果列起名。**
**别名里带中文要加双引号吗？不必须，但带了更保险——空格和特殊符号必须带。**
**`select *` 在生产代码里要慎用。**
**表加了列，`*` 会多带你没要的；表改了大字段，`*` 会把一堆没用的数据拉过网络。**
**自己试的时候随便用，写进程序就写明列名。**
二、筛选查询：挑行
--------------------------------------------------------------------
SQL> select ename, job, sal from emp where job = 'SALESMAN' and sal > 1400 order by sal
ENAME JOB SAL
--------------------
TURNER SALESMAN 1500
ALLEN SALESMAN 1600
已选择 2 行
SQL> select ename, sal from emp where sal between 1000 and 2000 order by sal
ENAME SAL
-----------
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
已选择 6 行
SQL> select ename, job from emp where job in ('CLERK', 'ANALYST') order by job, ename
ENAME JOB
--------------
FORD ANALYST
SCOTT ANALYST
ADAMS CLERK
JAMES CLERK
MILLER CLERK
SMITH CLERK
已选择 6 行
SQL> select ename from emp where ename like 'S%' order by ename
ENAME
-----
SCOTT
SMITH
已选择 2 行
**`between 1000 and 2000` 是闭区间**（两头都含），**等价于 `>= 1000 and <= 2000`。**
**别写成 `between 2000 and 1000`——那在 Oracle 里返回 0 行，不报错，最难查。**
**`like 'S%'` 里的 `%` 是「任意多个字符」，`_` 是「恰好一个字符」。**
**要找真的下划线，得写 `like '%\_%' escape '\'`**，**否则它会被当成通配符，把整表都匹配上。**
坑一：and 比 or 先算
--------------------------------------------------------------------
SQL> select ename, job, sal from emp where job = 'CLERK' or job = 'ANALYST' and sal > 2000 order by job, ename
ENAME JOB SAL
-------------------
FORD ANALYST 3000
SCOTT ANALYST 3000
ADAMS CLERK 1100
JAMES CLERK 950
MILLER CLERK 1300
SMITH CLERK 800
已选择 6 行
**这条想查「文员，或者工资超过 2000 的分析员」。**
**但 `and` 的优先级高于 `or`，实际算的是「文员，或者（工资超 2000 的分析员）」。**
**上面结果里 `CLERK` 的工资有 800 的，说明「文员」这一半没受工资限制。**
SQL> select ename, job, sal from emp where (job = 'CLERK' or job = 'ANALYST') and sal > 2000 order by job, ename
ENAME JOB SAL
------------------
FORD ANALYST 3000
SCOTT ANALYST 3000
已选择 2 行
**加一对括号，结果就只剩 2 行了。**
**规矩：`where` 里同时出现 `and` 和 `or`，一律加括号。**
**不加括号不算错，但你要在脑子里跑一遍优先级——而人脑跑不过编译器。**
坑二：null 不参与任何比较
--------------------------------------------------------------------
SQL> select ename, comm from emp where comm is null order by ename
ENAME COMM
-----------
ADAMS NULL
BLAKE NULL
CLARK NULL
FORD NULL
JAMES NULL
JONES NULL
KING NULL
MILLER NULL
SCOTT NULL
SMITH NULL
已选择 10 行
SQL> select count(*) as 总人数, count(comm) as 有奖金的人数 from emp
总人数 有奖金的人数
-------------------
14 4
已选择 1 行
**`comm` 有 10 行是 NULL。**
**注意 `count(*)` 数行，`count(comm)` 只数非空的——14 对 4，差的就是那 10 个 NULL。**
SQL> select ename from emp where comm != 300 and comm is not null
ENAME
------
WARD
MARTIN
TURNER
已选择 3 行
SQL> select ename from emp where comm != 300
ENAME
------
WARD
MARTIN
TURNER
已选择 3 行
**上面两条差了一个 `comm is not null`，结果都是 3 行。**
**为什么一样？因为 `comm != 300` 遇到 NULL 时结果是「未知」，「未知」不是「真」，所以那 10 行被过滤掉了。**
**这条最反直觉：`where comm != 300` 不会返回「comm 是 NULL 的行」。**
**NULL 的意思是「不知道」，不知道等不等于 300，就无从判断。**
**要连 NULL 一起要，必须显式写 `or comm is null`。**
三、分组查询：把行压成组
--------------------------------------------------------------------
SQL> select deptno, count(*) as 人数, round(avg(sal), 2) as 平均工资 from emp group by deptno order by deptno
DEPTNO 人数 平均工资
--------------------
10 3 2916.67
20 5 2175
30 6 1566.67
已选择 3 行
SQL> select deptno, job, count(*) as 人数 from emp where deptno in (10, 20) group by deptno, job order by deptno, job
DEPTNO JOB 人数
---------------------
10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
20 ANALYST 2
20 CLERK 2
20 MANAGER 1
已选择 6 行
SQL> select deptno, count(*) as 人数 from emp group by deptno having count(*) > 4 order by deptno
DEPTNO 人数
-----------
20 5
30 6
已选择 2 行
**`group by` 之后，`select` 里只能出现两种东西：分组列、聚合函数。**
**写别的就是语法错（Oracle 报 `ORA-00979`）。**
SQL> select deptno, ename from emp group by deptno
DEPTNO ENAME
------------
10 CLARK
20 SMITH
30 ALLEN
已选择 3 行
**上面这条在 SQLite 上居然跑通了，返回 3 行、`ename` 是随便挑的一个。**
**这是 SQLite 的宽松，不是标准。Oracle 会直接报错。**
**别被它惯坏：真要每组挑一个，标准写法是 `min(ename)` 或窗口函数。**
**`where` 和 `having` 的分工要记死：**
**`where` 在分组前过滤「行」，`having` 在分组后过滤「组」。**
**`having count(*) > 4` 这种带聚合的条件，只能写在 `having` 里。**
四、排序查询：null 排哪头
--------------------------------------------------------------------
SQL> select ename, comm from emp where deptno = 30 order by comm
ENAME COMM
-----------
BLAKE NULL
JAMES NULL
TURNER 0
ALLEN 300
WARD 500
MARTIN 1400
已选择 6 行
SQL> select ename, comm from emp where deptno = 30 order by comm desc
ENAME COMM
-----------
MARTIN 1400
WARD 500
ALLEN 300
TURNER 0
BLAKE NULL
JAMES NULL
已选择 6 行
**这一组的顺序，是 Oracle 和 SQLite 最容易对不上的地方。**
**上面两条是 SQLite 的默认：升序把 NULL 排最前（`BLAKE`、`JAMES` 打头），降序把 NULL 排最后。**
**Oracle 正好相反：升序时 NULL 排最后（当成无穷大），降序时排最前。**
**所以同一条 `order by comm`，在两套引擎上拿到的是两份不同的结果。**
**要统一，就别依赖默认。**
**Oracle 里写 `order by comm nulls last`；SQLite 里写 `order by comm is not null, comm`**
（先按「是不是空」排，再按值排）。
**`nulls first`／`nulls last` 是 Oracle 的扩展，SQLite 不认。**
SQL> select ename, deptno, sal from emp order by deptno, sal desc
ENAME DEPTNO SAL
------------------
KING 10 5000
CLARK 10 2450
MILLER 10 1300
SCOTT 20 3000
FORD 20 3000
JONES 20 2975
ADAMS 20 1100
SMITH 20 800
BLAKE 30 2850
ALLEN 30 1600
TURNER 30 1500
WARD 30 1250
MARTIN 30 1250
JAMES 30 950
已选择 14 行
**多列排序：先按 `deptno` 升序，同一个部门内再按 `sal` 降序。**
**每列可以各自带 `asc`／`desc`，不写就是 `asc`。**
**`order by` 永远是最后执行的一步。**
**所以它能用别名（`order by 年薪`），而 `where` 不能——`where` 跑的时候别名还不存在。**
五、多表关联：把两张表接起来
--------------------------------------------------------------------
SQL> select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno and e.deptno = 10 order by e.ename
ENAME DNAME
-----------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
已选择 3 行
SQL> select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno where e.deptno = 10 order by e.ename
ENAME DNAME
-----------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
已选择 3 行
**上面两条结果一模一样，只是写法不同。**
**第一种叫「隐式连接」（表用逗号隔开、条件写在 `where` 里），第二种是 1992 标准里的 `join ... on`。**
**推荐第二种，理由只有一个：漏写连接条件时，第一种会给你一张笛卡尔积。**
SQL> select count(*) as 笛卡尔积行数 from emp, dept
笛卡尔积行数
------------
56
已选择 1 行
**14 × 4 = 56 行。**
**这不是报错，是「语法正确、结果全错」——最危险的一类错误。**
**写成 `join ... on` 而漏了 `on`，Oracle 会直接报语法错，反而安全。**
SQL> select d.deptno, d.dname, count(e.empno) as 人数 from dept d left join emp e on e.deptno = d.deptno group by d.deptno, d.dname order by d.deptno
DEPTNO DNAME 人数
----------------------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
已选择 4 行
**`left join` 保住了左表的所有行。**
**`dept` 里的 `OPERATIONS`（部门 40）一个人都没有，人数那一列给的是 0，行本身没被丢掉。**
**换成 `inner join`，这一行会直接消失**——**「空部门」和「部门不存在」在报表里是两件事，别混。**
sal * 12 as 年薪 直接算在 select 里，没经过任何程序。
这也是 SQL 的一个特点：表达式、函数、聚合都在数据库里算完再返回，
客户端拿到的是算好的结果，不是原始数据。
between 那一组查出 6 行，两头都含。
换成 between 2000 and 1000 会返回 0 行——语法没问题，逻辑反了，Oracle 不报错。
这类「反着写也能跑」的条件，是线上事故的常见来源。
and／or 那一组：不加括号 6 行，加括号 2 行。
多出来的 4 行全是文员——因为 job = 'CLERK' 这一半根本没受 sal > 2000 约束。
两行结果的差别，就是一对括号。
comm != 300 只拿到 3 行，而 comm 非空的其实有 4 行。
少的那个是 comm = 300 的 ALLEN（它被条件排除了，正确）。
真正要小心的是另外 10 个 NULL：它们也没进结果，但不是因为「不等于 300」，
而是因为「无法判断」。
count(*) 和 count(comm) 差出来的 10，就是那 10 个 NULL。
这是判断「这一列有多少空值」最直接的手法：两个数一减。
分组那一组三个部门分别是 3、5、6 人。
注意 select deptno, count(*) 里 deptno 是分组列，合法；换成 ename 就非法了。
SQLite 会放你过（随便挑一个），Oracle 会报 ORA-00979。
select count(*) from emp, dept 给出 56。
这不是报错，是笛卡尔积——每一行员工配每一个部门。
56 这个数一旦出现在你以为该是 14 的地方，第一反应就该是「连接条件漏了」。
left join 那一组里，OPERATIONS 的人数是 0 而不是消失。
count(e.empno) 数的是「匹配上的员工」，count(*) 会数「行」——空部门那一行虽然没员工，
但它自己是一行，所以 count(*) 会给 1。
这两个写法在报表里差得很远，选之前先想清楚要哪个。
30 秒实验
- 把
between 1000 and 2000改成between 2000 and 1000，确认返回 0 行。 然后把条件换成sal >= 2000 and sal <= 1000，也是 0 行—— 确认这不是between的毛病，是条件本身矛盾。 - 给
comm != 300加上or comm is null，看行数从 3 变成 13。 这一条最能说明「NULL 不是值，是一种状态」。 - 把
left join改成inner join，确认OPERATIONS那一行消失。 再改成right join，看哪边的行被保住。 - 用
select deptno, ename from emp group by deptno在 SQLite 上跑一次，再去 Oracle 上跑一次。 前者给结果，后者给ORA-00979。 这就是「宽松」和「严格」的差别。
常见误区
where里and／or混用不加括号。 优先级是and高， 但你的语感大概率读成从左到右——两边不一致时就出错，而且不报错。- 用
= null或!= null判断空。 永远不成立。 判断空只有is null/is not null两种写法。 group by之后还想选非聚合列。 SQLite 放过你，Oracle 报ORA-00979。 真要每组挑一个，写min()／max()，或者用窗口函数。- 多表查询漏写连接条件。 结果从 14 行变成 56 行，不报错，只会让你以为数据变多了。
养成习惯：多表查询写完先
count(*)对一下数量级。
自测三问（点开对答案）
1. `where a or b and c` 实际是怎么算的？ —— **`a or (b and c)`**，因为 `and` 优先级高于 `or`。 **要别的意思就必须加括号**——不加括号不算错，但结果很可能不是你要的。 2. 为什么 `where comm != 300` 拿不到 `comm` 为 NULL 的行？ —— 因为 NULL 表示「不知道」。 **「不知道等不等于 300」的结果是「未知」，而 `where` 只留「真」的行。** 要连空一起要， 得写 `or comm is null`。 3. `left join` 之后 `count(*)` 和 `count(e.列)` 在空部门上分别给什么？ —— `count(*)` 给 1（那一行存在），**`count(e.列)` 给 0（没有匹配的员工）。** **报表里要「该部门几个人」就用后者，要「有几行」才用前者。**下一篇讲系统函数： 字符、数字、日期、转换、聚合，五类各挑几个真会用到的。 重点会放在「同一个函数在两套引擎上结果不同」这类差异上。
- ☐ （本篇完，下一篇 4.4 Oracle 常用系统函数）
相关笔记
- 用户模式
- SQL 语言简介
- 数据字典
-
- 🐬 MySQL必知必会/00-索引
- 🏺 Oracle从入门到精通 · 索引