文章为原创,有很多是从网上看来的,但是时间过长已经忘记从哪里找的了
文章目录
sql加上分页突然效率下降很多
原因是因为没有加上排序,分页的时候会默认排序,加上排序就不会出现这样的问题了。
select *
FROM (select a.*, ROWNUM rn
FROM (
--查询sql
) a
WHERE ROWNUM <= 10)
WHERE rn > 0;
Orcal数据库中的函数
case when then else end
–case语句的种类:
1.简单case语句
语法:
case exp when comexp then returnvalue
…
when comexp then returnvalue
else returnvalue
end
case到end之间相当于一个具体的值,可以做运算,取别名,嵌套case 等等。
只要把case到end当作一个运算结果的表达式就可以了。
举例:
select cust_last_name,
case credit_limit when 100 then ‘low’
when 5000 then ‘high’
else ‘medium’
end
from customers;
2.搜索case语句
语法:
case when boolean then return value
…
when boolean then return value
else retur nvalue
end
举例:select case when id between 1 and 10 then ‘low’
when id between 20 and 30 then ‘mid’
when id between 40 and 50 then ‘high’
else ‘unknow’
endfrom product;
–简单case和搜索case之间的区别:
- 简单case只能是when后面的表达式完全匹配case后的表达式,相当于 =,所以也不能匹配null。2. searched case可以作为比较条件,那么可以使用like、!=、between …and、<、=、is null、is not null等,比简单case的使用更加广泛,完全可以替代简单case。
–注意事项:
1.case 表达式返回的是一个确定的value,若前面的都不匹配,则返回else中的项.
2.简单case 中的表达式,when 后面的表达式类型应该全部保持一致.
3.所有的then 后面的return_value类型要保持一致.
4.对于简单case 表达式,也就是case 表达式 when…那么when null 总是取不到。也就是case 后面的表达式如果值为null,不会与when null 匹配,只会与else匹配.
5.对于searched case来说,有自动类型转换,只要条件成立就可以。
如:select case when 1=‘1’ then 1 end from dual; 其中1='1’条件成立
值得一提的是: sql中的case语句与pl/sql中的case语句的不同之处:
前者的else不是必须的,如果没有匹配就返回null;后者的else不写,则报case_not_found异常.
–case中嵌套子查询Case语句中可以使用子查询,但是必须返回一行,不可以是多行.
如:select case (select count() as s1 from t1 where a = 1)
when (select count() as s2 from t1, t2 where t1.a = t2.a and t2.a = 1) then ‘相等’
else ‘不相等’
end
from dual;
此段为引用,时间过长忘记引用谁的了
union 和union all区别
Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
Union All:对两个结果集进行并集操作,包括重复行,不进行排序;
grouping 函数
GROUPING函数可以接受一列,返回0或者1。如果列值为空,那么GROUPING()返回1;如果列值非空,那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时,GROUPING()就非常有用。
1、在ROLLUP中对单列使用GROUPING()
SQL> select division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
DIV SUM(SALARY)
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
加上GROUPING来看看
SQL> select grouping(division_id),division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
GROUPING(DIVISION_ID) DIV SUM(SALARY)
0 BUS 1610000
0 OPE 1320000
0 SAL 4936000
0 SUP 1015000
1 8881000
可以看到,为空的地方返回1,非空的地方返回0。
decode函数的用发
1.decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
该函数的含义如下:
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
…
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
示例:
SELECT ID,DECODE(inParam,‘para1’,‘值1’ ,‘para2’,‘值2’,‘para3’,‘值3’,‘para4’,‘值4’,‘para5’,‘值5’) name FROM bank
#如果第一个参数inParam=='para1’那么那么select得到的那么显示为值1;
#如果第一个参数inParam=='para2’那么那么select得到的那么显示为值2;
#如果第一个参数inParam=='para3’那么那么select得到的那么显示为值3;
#如果第一个参数inParam==‘para4’那么那么select得到的那么显示为值4;
#如果第一个参数inParam==‘para5’那么那么select得到的那么显示为值5;
#都不相等就为’’
2.decode(字段或字段的运算,值1,值2,值3)
这个函数运行的结果是,当字段或字段的运算的值等于值1时,该函数返回值2,否则返回值3,当然值1,值2,值3也可以是表达式,这个函数使得某些sql语句简单了许多
示例:
SELECT ID,DECODE(inParam,‘beComparedParam’,‘值1’ ,‘值2’) name FROM bank
#如果第一个参数inParam==‘beComparedParam’,则select得到的name显示为值1,
#如果第一个参数inParam!=‘beComparedParam’,则select得到的name显示为值2
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1
例如:
变量1=10,变量2=20
则sign(变量1-变量2)返回-1,decode解码结果为“变量1”,达到了取较小值的目的。
Substr字符截取
1、substr函数格式 (俗称:字符截取函数)
格式1: substr(string string, int a, int b);
格式2:substr(string string, int a) ;
解析:
格式1:
1、string 需要截取的字符串
2、a 截取字符串的开始位置(注:当a等于0或1时,都是从第一位开始截取)
3、b 要截取的字符串的长度
格式2:
1、string 需要截取的字符串
2、a 可以理解为从第a个字符开始截取后面所有的字符串。包括a
2、实例解析
格式1:
复制代码
1、select substr(‘HelloWorld’,0,3) value from dual; //返回结果:Hel,截取从“H”开始3个字符
2、select substr(‘HelloWorld’,1,3) value from dual; //返回结果:Hel,截取从“H”开始3个字符
3、select substr(‘HelloWorld’,2,3) value from dual; //返回结果:ell,截取从“e”开始3个字符
4、select substr(‘HelloWorld’,0,100) value from dual; //返回结果:HelloWorld,100虽然超出预处理的字符串最长度,但不会影响返回结果,系统按预处理字符串最大数量返回。
5、select substr(‘HelloWorld’,5,3) value from dual; //返回结果:oWo
6、select substr(‘Hello World’,5,3) value from dual; //返回结果:o W (中间的空格也算一个字符串,结果是:o空格W)
7、select substr(‘HelloWorld’,-1,3) value from dual; //返回结果:d (从后面倒数第一位开始往后取1个字符,而不是3个。原因:下面红色 第三个注解)
8、select substr(‘HelloWorld’,-2,3) value from dual; //返回结果:ld (从后面倒数第二位开始往后取2个字符,而不是3个。原因:下面红色 第三个注解)
9、select substr(‘HelloWorld’,-3,3) value from dual; //返回结果:rld (从后面倒数第三位开始往后取3个字符)
10、select substr(‘HelloWorld’,-4,3) value from dual; //返回结果:orl (从后面倒数第四位开始往后取3个字符)
复制代码
(注:当a等于0或1时,都是从第一位开始截取(如:1和2))
(注:假如HelloWorld之间有空格,那么空格也将算在里面(如:5和6))
(注:虽然7、8、9、10截取的都是3个字符,结果却不是3 个字符; 只要 |a| ≤ b,取a的个数(如:7、8、9);当 |a| ≥ b时,才取b的个数,由a决定截取位置(如:9和10))
格式2:
复制代码
11、select substr(‘HelloWorld’,0) value from dual; //返回结果:HelloWorld,截取所有字符
12、select substr(‘HelloWorld’,1) value from dual; //返回结果:HelloWorld,截取所有字符
13、select substr(‘HelloWorld’,2) value from dual; //返回结果:elloWorld,截取从“e”开始之后所有字符
14、select substr(‘HelloWorld’,3) value from dual; //返回结果:lloWorld,截取从“l”开始之后所有字符
15、select substr(‘HelloWorld’,-1) value from dual; //返回结果:d,从最后一个“d”开始 往回截取1个字符
16、select substr(‘HelloWorld’,-2) value from dual; //返回结果:ld,从最后一个“d”开始 往回截取2个字符
17、select substr(‘HelloWorld’,-3) value from dual; //返回结果:rld,从最后一个“d”开始 往回截取3个字符
复制代码
(注:当只有两个参数时;不管是负几,都是从最后一个开始 往回截取(如:15、16、17))
Date数据类型比较大小
to_char(account_date,‘yyyy-MM-dd hh24:mi:ss’) > ‘2015-06-06’
查询100-150记录
比如想取出100-150条记录,按照tname排序
select tname,tabtype from (
select tname,tabtype,row_number() over ( order by tname ) rn from tab
) where rn between 100 and 150;
将一个表数据恢复到某个时间段
delete from user;
commit;
insert into userselect * from user as of timestamp to_timestamp(‘2021-05-10 09:30:00’, ‘yyyy-MM-dd HH:mi:ss’);
两个日期中有那些月
主要应用于查询连个月的数据,然后根据月分组
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(#{model.起始日期}, 'yyyy-MM'), ROWNUM - 1),'yyyy-MM') TIAN_BAO_RI_QI
FROM DUAL
CONNECT BY ROWNUM <=
MONTHS_BETWEEN(TO_DATE(#{model.结束日期}, 'yyyy-MM'),
TO_DATE(#{model.起始日期}, 'yyyy-MM')) + 1
查询表结构
select t.COLUMN_ID 序号,
t.COLUMN_NAME 代码,
t.DATA_TYPE||’(’||t.DATA_LENGTH||’)’ 数据类型 ,
c.COMMENTS 注释,
case when t.column_name=t2.column_name then ‘TRUE’ else ‘FALSE’ end 主键,
case when t.NULLABLE = ‘N’ then ‘TRUE’
WHEN t.NULLABLE = ‘Y’ THEN ‘FALSE’ END AS 非空
from user_tab_columns t
LEFT JOIN user_col_comments c ON t.table_name = c.table_name and t.column_name = c.column_name
left join (select b.table_name,b.column_name from user_cons_columns b
join user_constraints c on c.CONSTRAINT_NAME=b.CONSTRAINT_NAME
where c.constraint_type =‘P’) t2
on t.table_name=t2.table_name
where
t.table_name = ‘表名’
order by t.COLUMN_ID;