1.varchar2(20)
支持数字、英文字符长度:20
支持汉字长度:10
EX1: 字段“item1”定义长度varchar2(7)
可以尝试:’1234567‘ 插入到 item1字段,成功
‘一二三四五六七’ 插入到item1字段,报错:长度为14,最大值是7
这里的长度是14,14是如何得来的呢?
select lengthb('一二三四五六七') from dual;
执行结果:
如果,想要将一个字符串插入到某个字段(如果前7个字段都是英文字符,数字,就取前七个数字英文),该字段长度varchar2(7),超过长度,默认为‘0100010’,在长度范围内就取原始值
select case when lengthb(nvl(substr('一二三四五六七',0,7),'0100010'))>7 then '0100010' else nvl(substr('一二三四五六七',0,7),'0100010') end from dual;
-- 运行结果:0100010
select case when lengthb(nvl(substr('一二',0,7),'0100010'))>7 then '0100010' else nvl(substr('一二',0,7),'0100010') end from dual;
-- 运行结果:一二
2.空字段null
创建两张测试表:
Create table test007(
id varchar2(7),
name varchar2(7)
);
Create table test008(
id varchar2(7),
name varchar2(7)
);
插入数据:
insert into test007 values('1','');
insert into test007 values('2','');
insert into test008 values('1','');
insert into test008 values('2','');
查询两张表中,完全一致的记录
SELECT * from test007 a ,test008 b where a.name=b.name and a.id=b.id;
执行结果:
查询id相等,name不等的数据:
SELECT * from test007 a ,test008 b where a.name<>b.name and a.id=b.id;
执行结果:
因此,在查询中,空字段既不会认为是相等,也不能简单地认为不等
若业务上认为都为空的数据表示相等,可将可能为空的字段默认为一个值进行:
SELECT * from test007 a ,test008 b where nvl(a.name,0)=nvl(b.name,0) and a.id=b.id;
执行结果:
或,使用下面的方法:
SELECT * from test007 a ,test008 b where ((a.name is null and b.name is null) or (a.name = b.name)) and a.id=b.id;
执行结果:
下面看下,同一张表空字段分组:
1.首先,向test007表中插入两条数据
insert into test007 values('3','非空');
insert into test007 values('4','');
当前,test007表中name字段为空的记录有三条,name非空记录有一条,现根据name字段分组,查询相应记录条数
select name,count(id) from test007 group by name;
执行结果:
name:空记录3条,非空记录1条。name空字段默认相等,与上述逻辑有偏差,为什么!!!我不知道......有能帮忙解答的么?
只怪自己接触太浅....
3.排序order by
先看个SQL吧
select distinct (select codename
from ldcode
where codetype = 'selltype'
and code = (select selltype from lccont where contno = a.contno)) 销售方式,
b.contno 保单号,
b.prtno 投保单号
from ljapayperson a
left join lcpol b
on a.polno = b.polno
where b.salechnl = '08'
and exists (select 1
from lccont
where contno = b.contno
and signdate >= trunc(sysdate, 'mm'))
order by (select codename
from ldcode
where codetype = 'selltype'
and code =
(select selltype from lccont where contno = a.contno)),b.contno;
这是一个报表的简化SQL,现在要求按子查询的结果及其中一个表的字段值进行排序,执行结果:
提示:不是selected表达式,原因是,排序的子查询条件中a表的字段contno,没有在查询结果中展示,无法按该字段进行排序,即使a表与b表对应字段相等,解决方案:
1.将查询结果项 b.contno 改为 a.contno,排序条件中b.contno 改为 a.contno
2.将排序条件的子查询中a.contno 改为b.contno
以此类推:使用order by时,order by的条件一定是在查询结果中的其中一项
4.分组group by
分组group by,说起来与order by很像,以刚刚的test007为例:
SELECT id,name FROM test007 group by name;
执行结果:
原因:使用group by进行分组时,在查询结果中展示的数据,都必须在group by后出现,聚合函数除外
常见的聚合函数:
1.求最大值函数max()
2.求最小值函数min()
3.求平均值函数avg()
4.求记录数函数count()
5.求和函数sum()
不常用的聚合函数
6.求中位数函数median()
7.求标准差函数stddev()
8.求协方差函数variance()
修改SQL:
1.group by添加分组条件
SELECT id,name FROM test007 group by name,id;
查询结果:
2.查询结果添加聚合函数
select max(id),name from test007 group by name;
查询结果:
分组后再进行排序筛选数据,取各组的第一条数据,要如何处理?
ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 排序字段 DESC) 作为新表的字段行数
示例,在test007表中插入数据:
insert into test007 values('5','非空');
insert into test007 values('6','第三组');
insert into test007 values('7','第三组');
insert into test007 values('8','第四组');
insert into test007 values('9','第四组');
insert into test007 values('10','第四组');
当前test007表中数据如下:
现要按name分组,按id倒序排序,取各组的前两项id(请注意,id的类型是varchar2(),10比9小哦)
首先根据name分组,id倒序,并对每组的数据标注序号
select row_number() over(partition by name order by id desc),id,name from test007;
执行结果:
再对结果进行筛选
select * from (select row_number() over(partition by name order by id desc) as rn,id,name from test007) m where m.rn<3;
哦了!
这次先写这么多吧...