中文乱码问题解决
设置环境变量NLS_LANG,值为SIMPLIFIED CHINESE_CHINA.ZHS16GBK
exists和not exists
exists : 存在即为真
not exists : 不存在结果集即为真
如:
表A 表B
ID NAME ID AID NAME
1 A1 1 1 B1
2 A2 2 2 B2
3 A3 3 2 B3
表A和表B是1对多的关系 A.ID => B.AID
SELECT ID,NAME FROM A WHERE EXIST (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
1 A1
2 A2
原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=1)
--->SELECT * FROM B WHERE B.AID=1有值返回真所以有数据
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
--->SELECT * FROM B WHERE B.AID=2有值返回真所以有数据
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
--->SELECT * FROM B WHERE B.AID=3无值返回假所以没有数据
NOT EXISTS:
SELECT ID,NAME FROM A WHERE NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
3 A3
排序 order by
desc : 降序
asc : 升序(默认)
判空问题
两种方式如下:
- nvl(字段名,'*') != '*'
- case when 字段名 is null then '*' else 字段名 end != '*'
greatest 函数 :取最大值
可把负数转为0
select greatest(23,45,56) from dual ;
结果:56
select * from greatest(-23 ,0 ) from dual;
结果: 0
regexp_like(模糊查询)
regexp_like(字段名,内容,‘i’);
注: 内容不用加%
‘i’: 不用区分大小写
‘c’: 需要区分大小写
select * from tsecurity t where regrep_like(t.security_code, 'r0','i');
日期加减转换(便于日期比较)
select to_number(to_char(to_date(t.business_date,'yyyyMMdd')- 0,'yyyyMMdd')) from tmarket t;