SQL-cookbook

2022-04-07 04:06:17

检索记录

WHERE子句中AND优先执行
在WHERE子句中引用别名列

将查询结果包装为内嵌视图，这样就可以使用别名列

select *
from (select sal as salary,comm as commission from emp) x
where salary < 5000;

串联多列的值

## DB2、Oracle、PostgreSQL
select ename||‘work as a‘||job as msg from emp where deptno = 10;
## MySQL
select concat(ename,‘work as a‘,job) msg from emp where deptno = 10;
## SQL Server
select ename+‘work as a‘+job as msg from emp where deptno = 10;

SELECT语句中使用条件逻辑

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

限定返回行数

## DB2 - 使用 FETCH FIRST 子句
## MySQL、PostgreSQL - 使用 LINMIT 子句
## Oracle - 在 WHERE 子句中限制 ROWNUM 的值获取指定行数的结果集
## SQL Server - 使用 TOP 关键词限定返回行数

随机返回若干行记录

## DB2

## MySQL
select ename,job from emp order by rand() limit 5;
## PostgreSQL

## Oracle
select * from (select ename,job from emp order by dbms_random.value())
where rownum <= 5;
## SQL Server

查找Null值

## 判断特定列是否为null 必须用 IS NULL
## 替换null值
select *,coalesce(comm,0) from emp;
## 也可以使用 case

模糊匹配

LIKE ‘%‘ 匹配多个字符
LIKE ‘_‘ 匹配当个字符

查询结果排序

根据子串排序

order by substr(col_name, length(col_name-2))

对含有字母和数字的列排序
排序时对Null值处理

MySQL默认升序排序Null值在前

select * from
    (select ename,sal,comm,
     case when comm is null then 0 else 1 end as is_null from emp) x 
order by is_null desc,comm;

## Oracle9i+
# NULLS FIRST/LAST

依据条件逻辑动态调整排序项

CASE WHEN... THEN... ELSE... END

多表查询

插入、更新和删除

复制数据到另一个表

insert into A()
select * from B;

复制表定义

# CTAS语句
create table c
as select * from D where 1=0;

多表插入