检索记录

检索表中所有行和列

Q：查看表中所有数据

select * from emp;

编写程序代码时最好还是指定字段名称，这样更容易理解。而且查询返回的列可能不符合预期。

从表中检索部分行

Q：查看表中满足特定条件的行

select * from emp where deptno = 10;

查找满足多个条件的行

-- 以下3个条件满足其一即可
-- condition 1. deptno = 10
-- condition 2. comm is not null
-- condition 3. sal <= 2000 and deptno = 20
select * from emp where deptno = 10 or comm is not null or sal <= 2000 and deptno = 20;

检索部分列

select ename, deptno, sal from emp

指定列名

select sal as salary
from emp

在where子句中引用别名

select *
from (
	select c1 as column1, c2 as column2
    from t1
) tt1
where column1 < 10;

拼接列值

postgres 使用||作为拼接运算符

select e.ename || ' WORK AS A ' || e.job as msg
from emp e
where e.deptno = 10

结果如下

msg                     |
------------------------+
CLARK WORK AS A MANAGER |
KING WORK AS A PRESIDENT|
MILLER WORK AS A CLERK  |

在select语句中使用条件逻辑

Q：员工薪水不超过2000 美元时返回消息 UNDEPAID，在员工薪水不低于4000美元时返回消息OVERPAID，在员工薪水为2000~4000美元时返回消息ok。

select ename, sal,
	case when sal <= 2000 then 'UNDERPAID'
		 when sal >= 4000 then 'OVERPAID'
		 else 'OK'
	end as status
from emp;

查询结果

ename |sal    |status   |
------+-------+---------+
SMITH | 800.00|UNDERPAID|
ALLEN |1600.00|UNDERPAID|
WARD  |1250.00|UNDERPAID|
JONES |2975.00|OK       |
MARTIN|1250.00|UNDERPAID|
BLAKE |2850.00|OK       |
CLARK |2450.00|OK       |
SCOTT |3000.00|OK       |
KING  |5000.00|OVERPAID |
TURNER|1500.00|UNDERPAID|
ADAMS |1100.00|UNDERPAID|
JAMES | 950.00|UNDERPAID|
FORD  |3000.00|OK       |
MILLER|1300.00|UNDERPAID|

限制返回的行数

select * from emp limit 5;

从表中随机返回n行数据

每次返回5行，但每行数据不一样

select ename, job from emp order by random() limit 5;

查找null

select * from emp where comm is null;

将null转化为实际值

使用函数COALESCE，大部分数据库都支持

select coalesce(comm,0) from emp;

使用CASE也可以

select case when comm is not null then comm else 0 end
from emp;

模式查找

即LIKE和%

select ename,job
from emp
where deptno in (10,20)
and (ename like '%I%' or job like '%ER%');