时间：2021-07-01 10:21:17 帮助过：9人阅读
- selectnvl(<span style="color: #ff00ff">sum</span>(t.dwxhl),<span style="color: #800000; font-weight: bold">1</span>)fromtb_jhdetwherezydm<span style="color: #808080">=-</span><span style="color: #800000; font-weight: bold">1</span>
这里关心的nvl的用法，nvl（arg,value）代表如果前面的arg的值为null那么返回的值为后面的value
ESCAPE
escape关键字经常用于使某些特殊字符，如通配符：‘%’，‘——’转义为他们原来的字符的意义，被定义的转义字符通常使用‘\’，但是有时候也可以使用其他符号。
转义字符为d，没有实现转义功能;
- SQL<span style="color: #808080">></span><span style="color: #0000ff">select</span><span style="color: #808080">*</span>fromtestwheretestlike<span style="color: #ff0000">‘</span><span style="color: #ff0000">sddd_%</span><span style="color: #ff0000">‘</span><span style="color: #0000ff">escape</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">d</span><span style="color: #ff0000">‘</span>;
查找包含所有‘_‘的字段。
- SQL<span style="color: #808080">></span><span style="color: #0000ff">select</span><span style="color: #808080">*</span>fromtestwheretestlike<span style="color: #ff0000">‘</span><span style="color: #ff0000">%\_%</span><span style="color: #ff0000">‘</span><span style="color: #0000ff">escape</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">\</span><span style="color: #ff0000">‘</span>;
连接字符串 concat ||
- <span style="color: #008080">--</span><span style="color: #008080">concat只能连接两个字符串，连接多个需要嵌套调用不方便</span>
- SQL<span style="color: #808080">></span>selectconcat(<span style="color: #ff0000">‘</span><span style="color: #ff0000">aa</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">bb</span><span style="color: #ff0000">‘</span><span style="color: #000000">)fromdual;
- CONCAT(</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">AA</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">BB</span><span style="color: #ff0000">‘</span><span style="color: #000000">)
- </span><span style="color: #008080">--</span><span style="color: #008080">---------------</span>
- <span style="color: #000000">aabb
- </span><span style="color: #008080">--</span><span style="color: #008080">||直接连接多个字符串</span>
- SQL<span style="color: #808080">></span><span style="color: #0000ff">select</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">aa</span><span style="color: #ff0000">‘</span><span style="color: #808080">||</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">bb</span><span style="color: #ff0000">‘</span><span style="color: #808080">||</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">cc</span><span style="color: #ff0000">‘</span><span style="color: #000000">fromdual;
- </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">AA</span><span style="color: #ff0000">‘</span><span style="color: #808080">||</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">BB</span><span style="color: #ff0000">‘</span><span style="color: #808080">||</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">CC</span><span style="color: #ff0000">‘</span>
- <span style="color: #008080">--</span><span style="color: #008080">--------------</span>
- <span style="color: #000000">aabbcc
- </span>
字符串截取
substr(字符串,截取开始位置,截取长度) //返回截取的字
- <span style="color: #000000">substr(</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">HelloWorld</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">0</span>,<span style="color: #800000; font-weight: bold">1</span>)<span style="color: #808080">//</span>返回结果为<span style="color: #ff0000">‘</span><span style="color: #ff0000">H</span><span style="color: #ff0000">‘</span><span style="color: #808080">*</span>从字符串第一个字符开始截取长度为1的字符串
长度计算函数 length()与lengthb()
- SQL<span style="color: #808080">></span>selectlength(<span style="color: #ff0000">‘</span><span style="color: #ff0000">新春快乐</span><span style="color: #ff0000">‘</span><span style="color: #000000">)fromdual;
- LENGTH(</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">新春快乐</span><span style="color: #ff0000">‘</span><span style="color: #000000">)
- </span><span style="color: #008080">--</span><span style="color: #008080">----------------</span>
- <span style="color: #000000">
- SQL</span><span style="color: #808080">></span>selectlengthb(<span style="color: #ff0000">‘</span><span style="color: #ff0000">新春快乐</span><span style="color: #ff0000">‘</span><span style="color: #000000">)fromdual;
- LENGTHB(</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">新春快乐</span><span style="color: #ff0000">‘</span><span style="color: #000000">)
- </span><span style="color: #008080">--</span><span style="color: #008080">-----------------</span>
- <span style="color: #000000">
- 区别：length求得是字符长度，
- lengthb求得是字节长度。 ?</span>
INSTR函数
-- 在指定的字符串中检索指定字符所在的索引位置
- <span style="color: #000000">
- SQL</span><span style="color: #808080">></span>selectinstr(<span style="color: #ff0000">‘</span><span style="color: #ff0000">hello</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">l</span><span style="color: #ff0000">‘</span><span style="color: #000000">)fromdual;
- INSTR(</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">HELLO</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">L</span><span style="color: #ff0000">‘</span><span style="color: #000000">)
- </span><span style="color: #008080">--</span><span style="color: #008080">----------------</span>
- <span style="color: #000000">
- 在hellowolrd中截取出来world字符串？
- SQL</span><span style="color: #808080">></span>selectsubstr(<span style="color: #ff0000">‘</span><span style="color: #ff0000">helloworld</span><span style="color: #ff0000">‘</span>,instr(<span style="color: #ff0000">‘</span><span style="color: #ff0000">helloworld</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘‘</span><span style="color: #000000">))fromdual;
- SUBSTR
- </span><span style="color: #008080">--</span><span style="color: #008080">----</span>
- <span style="color: #000000">world
- 已选择1行。
- SQL</span><span style="color: #808080">></span>selectsubstr(<span style="color: #ff0000">‘</span><span style="color: #ff0000">helloworld</span><span style="color: #ff0000">‘</span>,instr(<span style="color: #ff0000">‘</span><span style="color: #ff0000">helloworld</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘‘</span>)<span style="color: #808080">+</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)fromdual;
- SUBST
- </span><span style="color: #008080">--</span><span style="color: #008080">---</span>
- <span style="color: #000000">world
- 已选择1行。
- lpad() rpad()
- 参数1：指定原始的串 参数2：固定长度 参数3：填充的字符</span>
SQL>selectlpad(‘jack‘,10,‘$‘)fromdual; LPAD(‘JACK‘,10,‘$‘) ---------- $$$$$$jack selectrpad(‘jack‘,10,‘$‘)fromdual; ----------- jack$$$$$$
- <span style="color: #000000"> </span>
Next_day
计算出来下一个星期一是几号?
- selectnext_day(sysdate,<span style="color: #ff0000">‘</span><span style="color: #ff0000">星期一</span><span style="color: #ff0000">‘</span><span>)fromdual;</span>
round 四舍五入
NVL (expr1, expr2)：
expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致
NVL2 (expr1, expr2, expr3) ：
xpr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
NULLIF (expr1, expr2)：
相等返回NULL，不等返回expr1
decode()
主要作用：将查询结果翻译成其他值（即以其他形式表现出来，以下举例说明）；
使用方法：
Select decode（columnname，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）
From talbename
Where …
其中columnname为要选择的table中所定义的column，
·含义解释：
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)的理解如下：
if（条件==值1） then return(翻译值1) elsif（条件==值2） then return(翻译值2) ...... elsif（条件==值n） then return(翻译值n) else return(缺省值) endif注：其中缺省值可以是你要选择的column name 本身，也可以是你想定义的其他值，比如Other等；
表复制快速的建立的表结构？
- <span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span> emp10 <span style="color: #0000ff">as</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">where</span> <span style="color: #800000; font-weight: bold">1</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">12</span><span style="color: #000000">;
- </span><span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span> emp30 <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">where</span> deptno<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">30</span>;
Oracle 一些函数的使用
标签：round ring convert com mp3 world date isp sum