之前也用到过一些主流数据库,像Oracle、Sql server、Mysql等。但都是一些相对简单的语句用法和常见函数,对此有时间强化一下,同时做一下记录与大家分享,方便自己复习。
1、拼接字符串:(举例:将用户名和密码用 WORKS AS A 拼接成一个字符串展示)
DB2、Oracle、PostgreSQL将 || 双数线直接用作拼接:注意DB2类型转换含有数字类型时,需要添加显示转换,例如PASSWD 则需要cast(PASSWD as char(10))
Select USER_ID || ' WORKS AS A ' || PASSWD FROM T_USER ;
Mysql使用函数concat:
Select concat(USER_ID, ' WORKS AS A ' ,PASSWD) FROM T_USER ;
Sql Server使用运算符+拼接:
Select USER_ID+ ' WORKS AS A ' +PASSWD FROM T_USER ;
2、 在数据库里执行if_else操作使用case _when_tnen_……_else_end实现
SELECT USER_ID ,PASSWD, CASE WHEN USER_ID ='sa'
THEN '管理员' ELSE '学生' END AS Role
FROM T_USER ;
如果没有写else则返回满足条件结果为null
3、限制返回行数:(分别展示查询前5行和10~20行的写法)
DB2: select * from T_USER fetch first 5 rows only;
使用开窗函数:select * from (select row_number() over(order by USER_ID ) ID,T.* from T_USER T) where ID between 11 and 20;
Mysql和PostgreSql:select * from T_USER limit 5;
Mysql查10~20:select * from T_USER limit 9,10;(索引从0开始)
PostgreSql查10~20:select * from T_USER order by USER_ID desc limit 10 offset 9;
Oracle:select * from T_USER where rownum<=5;
select * from (select t.*,rownum num from T_USER t where rownum <= 20) where num >=10;
或 select * from T_USER where rownum<=20 and rownum>=10;
Sqlserver:select top 5 * from T_USER ;
select* from T_USER order by 1 offset 10 rows fetch next 10 rows only;
扩展:开窗函数:
开窗函数可以为每组返回多个值,因为开窗函数所执行聚合计算的行集组是窗口。
用于
;分区排序
动态GROUP BY
;TOP N多条数据;累计计算;层次查询。
开窗函数=分析函数+Over();
格式为:分区(partition by
),排序(order by
),范围(rows between
或range between
)
partition by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组。
partition by与group by不同之处在于前者返回的是分组里的每一条数据,并且可以对分组数据进行排序操作。后者只能返回聚合之后的组的数据统计值的记录(不返回整行数据)。先group by后having后order by
例如: row_number() over(partition by … order by …)
rank() over(partition by … order by …)
dense_rank() over(partition by … order by …)
count() over(partition by … order by …)
max() over(partition by … order by …)
min() over(partition by … order by …)
sum() over(partition by … order by …)
avg() over(partition by … order by …)
first_value() over(partition by … order by …)
last_value() over(partition by … order by …)
lag(字段,偏移位数) over(partition by … order by …) (这两个是偏移函数,是指在原来的基础上查找偏移多少位数)
lead() over(partition by … order by …) 等等
4、随机返回行数:(随机返回5行数据)
DB2:使用内置函数rand、order by 和 fetch:
select USER_ID,PASSWD from T_USER order by rand() fetch first 5 rows only;
Mysql:使用内置函数rang、limit、和order by:
select USER_ID,PASSWD from T_USER order by rand() limit 5;
PostgreSQL:使用内置函数random、limit和order by:
select USER_ID,PASSWD from T_USER order by random() limit 5;
Sqlserver:使用内置函数newid、top和order by:
select top 5 USER_ID,PASSWD from T_USER order by newid();
Oracle:结合使用(dbms_random中的)内置函数value、order by子句和内置函数rownum:
select * from (select USER_ID,PASSWD from T_USER order by dbms_random.value() ) where rownum <=5;
注意:order by 可以根据函数的返回值来调整结果集的排列顺序。
扩展:dbms_random.value()是dbms_random是一个可以生成随机数值或者字符串的程序包。
这个包有initialize()、seed()、terminate()、value()、normal()、random()、string()等几个函数,此处具体介绍一下.value(),其他函数可以自行百度(篇幅有限不多赘述)
dbms_random.value()放在from之前查询可以随机产生(0,1)之间的数,但是放在order by 之后则是为结果集的每一行计算一个随机数,从而得到一个随机的顺序。(即查找随机游标)
但是如果是大数据量随机查询的话可以考虑使用样本的方法sample(),例如:
SELECT * FROM T_USER sample(99) where rownum <= 5;
5、查找NULL值
select USER_ID,PASSWD from T_USER where PASSWD is null;或is not null
6、将NULL转换为实际值
(1)select USER_ID,coalesce(PASSWD,'空') as PASSWD from T_USER ;
coalesce()可以将一个或多个值作为参数,并返回一个非NULL值,比较数值是可以将coalesce放在where条件里。
(2)或者使用 case when加is null做判断
(3)decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)类似于if else用法DECODE(t.ID,NULL,0,t.ID)。个人理解为decode(字段,条件,值1,值2)
(4)NULLIF ( expression1 , expression2 )如果两个指定的表达式相等,则返回空值。
(5)NVL(eExpression1, eExpression2) 从两个表达式返回一个非 null 值。
7、查找符合条件数据:
(1)准确查找范围值
select * from T_CLASS_GROUP where CLASSID in (1,3)
(2)模糊查询:使用like 运算符 和% 通配符
select * from T_USER where USER_ID like '%de%';
文章还会持续更新后续,喜欢的小伙伴请收藏。