在实际场景中会碰到各种数据库的使用,这里记录一下sql语言,这里我的数据库是oracle
1.case…when…then…end
疫情下有跟健康码相关的接口,但是存入数据库中的是code,查询的时候需要翻译成实际信息,所以用到了case…when,类swich,但是使用起来还是有些注意事项。
- case相当于一个字段,所以紧跟在select后面,用逗号隔开
- 为空时的表示用is null,而不是
等于null - as后表示的是自定义的列名
SELECT
a.ID,
a.NAME,
a.CARD_NUMBER,
a.CREATE_TIME,
a.VACCINE_ARCHIVES,
(
CASE
WHEN a.hc = '0' THEN
'绿码'
WHEN a.hc= '1' THEN
'黄码'
WHEN a.hc= '2' THEN
'红码' ELSE '异常'
END
) AS hc,
(
CASE
WHEN a.peas= '0' THEN
'去过'
WHEN a.peas= '1' THEN
'未去过'
WHEN a.peas= '2' THEN
'信息不全' ELSE '异常'
END
) AS peas,
(
CASE
WHEN a.pecs= '0' THEN
'去过'
WHEN a.pecs= '1' THEN
'未去过'
WHEN a.pecs= '2' THEN
'信息不全' ELSE '异常'
END
) AS pecs,
( CASE WHEN a.nat IS NULL THEN '暂无记录' ELSE TO_CHAR( nat ) END ) AS nat,
( CASE WHEN a.nar IS NULL THEN '暂无记录' ELSE TO_CHAR( nar ) END ) AS nar
FROM
HEALTHY a
2.decode和Sign
这个函数是oracle的特有语法,对标case when,相对来说语法更加简洁。
DECODE(表达式,condition1,res1,condition2,res2):条件和结果根据自己需求可以不断增加,表达式=condition1的时候,结果为rest1;为condition2的时候,结果为rest2,以此类推。
还可以和Sign联合使用,SIGN(表达式),若表达式为正数返回1,负数返回-1,为0就返回0.
select
count(*),
SUM( DECODE( Result, '未登记', 0, 1 ) ) sumRes
from sys_user
3.start with…connect by prior…
查询树形结构的时候的一种使用,比如A公司有个a部门,里面包含了aa、ab、ac等子部门,我现在要根据他们的父部门来查询所有子部门。
dept:表示部门的表,其中id为唯一标志,然后father_pid表示他们的父级id
#查询父部门id为10的子部门的id的列表
select id from dept start with id = '10' connect by prior id = dept_pid
4.concat
这是一个可以把字段拼接的函数,比如我查询的是一个用户列表,我再把他们用顿号拼接。
select concat('',name,'、') as nameStr from user;
但是这样会存在一个问题,最后一个人名也会有顿号,所以这里采用group_cancat
select group_concat(name separator '、') as nameStr from user;
需求再次升级,需要把电话号码也拼接下,那就是组合使用concat和group_concat
select group_concat(concat(name,'-',tel) separator '、') as nameStr from user;
总结:concat和group_concat都是用在sql语句中做拼接使用的,但是两者使用的方式不尽相同,concat是针对以行数据做的拼接,而group_concat是针对列做的数据拼接,且group_concat自动生成逗号。
踩坑:group_concat默认长度是1024,假如有超出的场景我是临时扩大长度,就是在执行查询前先执行该方法。
public void modifyGroupConcatLength() {
// 设置 group_concat_max_len 为所需的长度,例如 100000
jdbcTemplate.execute("SET group_concat_max_len = 4294967295;");
}
常用
#将空记录的表名的字段属性进行修改(test是表名,par是字段,DATE是最终修改的属性值)
alter table test modify par DATE;
#查询范围内的,主要就是in后面跟(列表),结合一个select关联查询妙用无穷
select * from user where name in ('张三','李四');
#时间格式查询
select * from user where createTime > TO_DATE( '2021-10-09 00:00:00', 'YYYY-MM-DD hh24:mi:ss' )
#创建完表后增加主键(STUDENT表名,STUDENT_PK不重复即可,ID主键)
ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK PRIMARY KEY ( ID )
#修改字段长度(STUDENT 表名,NAME 字段)
ALTER TABLE STUDENT MODIFY NAME VARCHAR(100)
#更改数据库中某个列里的数据
update table_name set table_name.column_name = replace(table_name.column_name,'str1','str2');