今天看啥
    热点:

      天发国际娱乐官网:通过辅助抢票、爱心汽车、回家基金等丰富形式,开辟多条春节回家绿色安全通道,帮助在外游子实现春节团圆梦,打造招财进宝中国年!  即日起至2017年1月20日,参与活动即有机会免费领取爱心机票、回家基金、爱心汽车等大奖,招财进宝,喝金罐加多宝,更多详情请关注加多宝凉茶官方微信及扫描罐身二维码!活动热线电话:010-58295833转8103

      Oracle数据库常用函数使用示例,oracle数据库示例



      Oracle数据库基础简介及实践

      1、开始(p1~p2)
      2、SQL语句编写思路(p3)
      3、Oracle常用运算符介绍(p4~p5)
      4、DML语句介绍(p6~p11)
      5、Oracle常用函数介绍(p12~p30)
      6、SQL语句函数运用(p31~p34)
      7、通过日志获取SQL语句(p35~p49)
      8、使用case系统提交问题(p50~p51)
      9、学习资源及答疑(p52)
      10、结尾(p53)

       

      1、SQL语句编写思路
      1、首先确定最终输出结果的列,包括几个方面:
      1)这些列来自1个表还是多个表,如果是多个表则可能用到多表查询(等值、不等值、左连接、右连接、外连接、自连接)
      2)确定获取列的值,是直接查询得到,还是需要进行(函数、分组、运算、列的子查询)操作才能得到,列是否需要别名
      2、确定输出列的值和查询条件是否来自多个表,如果来自多个表则要用到多表查询
      3、确定输出列的值,是否可以直接查询获得还是要通过子查询才能获得,如果要用到子查询,则需要加上where
      4、根据输出列的结果和条件,判断是否要用到分组(group by),比如分类、统计、分组、最大、最小、平均、每个等字眼就需要用到group by
      5、是否对分组后的结果进行过滤,需要则要用到having
      6、是否对输出的结果进行排序,需要则要用到order by


      2、Oracle常用运算符介绍
      1、使用||可连接字符
      2、使用distinct可以消除重复行
      3、运算符:
      1) 算术(+ - * / )
      2) 比较(> >= < <= = != <> ) any(值1,值2...) all(值1,值2...) 不能单独使用,要配合>,>=,<,<=来使用
      3) 逻辑(and or not)

      4、SQL操作符:
      1) in(值1,值n)
      2) like '模式字符串':_代表任意一个字符,%代表0到n个字符
      3) between 数值1 and 数值2:不仅可用于数值,还可以用于日期时间
      4) is null、is not null:匹配空值,非空值。对于空值一定不能用=或!=
      5)union (取并集,重复的记录行只显示1行) 、union all(取并集)、intersect(取交集)、minus(取差集,返回左边表差集后的记录)
      5、伪列:
      rowid: Oracle内部对每个表的每一行都有一个唯一的标识
      rownum: Oracle对每次查询结果集的每一行记录都有一个行号,对rownum只能使用<或=


      3、 DML语句介绍
      1、查询(select)
      select distinct * |列 as 别名|表达式|函数|列运算|子查询
      from 表1 别名1,表n 别名n |子查询
      where 条件1(> >= < <= <>) 条件2 | 范围(between 条件1 and 条件2)
      In(子查询)
      like (条件_%)
      组合(or|and|not)
      子查询(select 语句)
      group by列
      having 分组函数(max,count,sum等) 运算符 普通值|子查询
      order by 列 别名 或 数字


      2、新增(insert into)
      insert into 表名(列名1,..) values(值1,..)
      --新增信息
      insert into check
      (PRIMARY_ID,
      NAME,
      DISTRICT,
      UPDATED_DATE)
      values
      (sys_guid(), sysdate);

      --只复制表结构加入了一个永远不可能成立的条件1=2,则此时表示的是只复制表结构,但是不复制表内容
      create table 用户名.目标表名 as select * from 用户名.源表名 where 1=2;

      --完全复制表(包括创建表和复制表中的记录)
      create table 用户名.目标表名 as select * from 用户名.源表名;

      --将多个表数据插入一个表中
      insert into 用户名.目标表名(字段1,字段n) (select 字段1,字段n)
      from 用户名.源表名 union all select 字段1,字段n from 表;


      3、修改(需加commit;关键字来提交)
      update 表名 set 列名=列改变值(where 条件表达式);
      update check_store cs set cs.DISTRICT='310113' where cs.NAME='数据包';
      commit;

      4、删除(需加commit;关键字来提交)
      delete from 表名 (where 条件表达式);
      delete from check_operate co where where co.NAME='数据包';
      commit;


      4、Oracle常用函数介绍
      1、日期字符函数
      1)to_char
      --2016-11-03
      select to_char(sysdate, 'yyyy-mm-dd') from dual;
      2)to_date
      select to_date(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual;
      3)to_number
      to_number(varchar2 or char,'format model')
      9:代表一个数字
      0:强迫0显示
      $:显示美元符号
      L:强制显示一个当地的货币符号
      .:显示一个小数点
      ,:显示一个千位分隔符号


      --1234.678
      select to_number('$1234.678', '$9999.999') from dual;
      --15
      select to_number('f', 'x') from dual;
      2、聚合函数
      1)最大值函数:max([distinct|all]x),最小值函数:min ([distinct|all]x)
      可以作用于数值型数据、字符串、日期时间数据类型的数据,max(null),min(null)返回null
      对字符型数据的最大值,是按照首字母由A~Z的顺序排列,越往后,其值越大。
      对于汉字则是按照其全拼拼音排列的,若首字符相同,则比较下一个字符,以此类推。
      对于日期时间类型的数据也可以求其最大/最小值,其大小排列就是日期时间的早晚,越早认为其值越小。


      2)求和函数sum([distinct|all]x),统计记录数函数:count(*|[distinct|all]x),求平均值函数:avg([distinct|all]x)
      count(*):将返回表格中所有存在的行的总数(包括null的行)
      count(列名):将返回表格中除去null以外的所有行的总数
      count(distinct+列名):与count(列名)相同
      3、特殊函数
      1) 唯一字符串函数: sys_guid()
      生成由32位长度由大写字母和数字组成的唯一字符串
      select sys_guid() from dual;
      结果:4061D1372CEA31E4E05318E01F0A5902


      第3个参数不填写会被填充空格
      2)左填充函数:lpad(string,padded_length,[pad_string])
      select lpad(1, 6, 't') from dual;--ttttt1
      select lpad(1234567, 6, 't') from dual;--123456
      3)右填充函数:lpad(string,padded_length,[pad_string])
      select rpad(1, 6, 't') from dual;--1ttttt
      select rpad('abcdefg', 6, 't') from dual;--abcdef
      4)随机包函数


      1) 返回0~1间的38位精度的随机数函数(包括0.0,不包括1.0):dbms_random.value
      --0.474199333601534
      select dbms_random.value from dual;
      2)返回a~b之间的随机数的函数(包括a=1,不包括b=10):dbms_random.value(1,10) :
      --4.63158069726374
      select dbms_random.value(1,10) from dual;
      3)产生正态分布的随机数的函数: dbms_random.normal


      /*
      注意:normal函数返回从正态分布的一组数。此正态分布标准偏差为1,期望值为0。
      这个函数返回的数值中有68%是介于-1与+1之间, 95%介于-2与+2之间,99%介于-3与+3之间
      */
      ---0.241547252937121
      select dbms_random.normal from dual;
      4)返回指定长度的的字符串的函数: dbms_random.string(opt char, len NUMBER)


      /*
      'u'或'U'–>返回大写字母
      'l'或'L'–>返回小写字母
      'a'或'A'–>大小写字母混合
      'x'或'X'–>大写字母和数字混合
      'p'或'P'–>任意可显示字符
      */
      --LSNB
      select dbms_random.string('u',4) from dual;
      5)返回一个随机数的函数: dbms_random.random


      --范围:-power(2,31) <= random < power(2,31)
      --35725665
      select dbms_random.random from dual;
      6)返回绝对值函数:abs
      --1949
      select abs(-1949) from dual;
      7)返回大于或等于给出数字的最小整数:ceil
      --7
      Select ceil(6.5) from dual;
      8)返回小于或等于给出数字的最大整数:floor
      --6
      Select floor(6.5) from dual;

       

      9) 返回保留指定位数的数字:round(number,digits)
      /*
      要四舍五入的数,digits是要小数点后保留的位数
      如果 digits 大于 0或不填写,则四舍五入到指定的小数位
      如果 digits 等于 0,则四舍五入到最接近的整数
      如果 digits 小于 0,则在小数点左侧进行四舍五入
      */
      --3.687
      select round(3.6873,3) from dual;


      10)nvl(expr1,expr2)
      expr1为null,返回expr2,否则返回expr1
      参数expr1、expr2可以是任何数据类型,但应该保持相同。
      若两者数据类型不一致,则Oracle数据库会隐式的转换其中一个的数据类型使其保持和另一个一致,若无法转换则会返回错误。
      --空
      select nvl(null,'空') from dual;
      --1
      select nvl('1','空') from dual;


      11)nvl2(expr1,expr2,expr3)
      如果expr1不是null值,则expr2,否则就返回expr3。
      参数可以返回任何数据类型的值,但是expr2和expr3不能是LONG型的数据类型
      --空
      select nvl2(null,'非空','空') from dual;
      --非空
      select nvl2('1','非空','空') from dual;


      12)nullif(expr1,expr2)
      参数expr1,expr2
      常量、列名、函数、子查询或算术运算符、按位运算符以及字符串运算符的任意组合,参数中不能有null。
      返回类型与第一个 expr1 相同
      如果两个表达式相等,则返回空值null
      如果两个表达式不相等,则返回expre1的值
      --
      select nullif(1,1) from dual;
      --1
      select nullif('1','') from dual;
      --1
      select nullif('1',' ') from dual;


      13)返回字符串位置的函数:instr(source,search, start_position,nth_appearance)
      source:源字符串
      search:被查找的字符串
      start_position:从源字符串中哪个位置开始查找字符串,可省略,默认为1,正整数,从左到右查找,负整数从右到左查找
      nth_appearance:第几次出现被查找的字符串,可不填写,默认为1,不能为负数
      也可以找到单个字符
      --8
      select instr('2016-11-03 16:24:20', '-', 1, 2) from dual;


      14) 返回截取后的字符串:substr(strings|express,start,[length])
      strings|express :被截取的字符串或字符串表达式
      start:从哪个位置开始截取,正数(在字符串的指定位置开始),负数(从字符串结尾的指定位置开始),0(在字符串中的第1个位置处开始)
      length:可选,指定要截取的字符串长度,缺省时返回字符表达式的值结束前的全部字符
      --01
      select substr('2016-11-03',2,2) from dual;
      --03
      select substr('2016-11-03',-2,2) from dual;
      --16-11-03
      select substr('2016-11-03',3) from dual;


      15) 值转换函数:decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
      条件可以为字段或表达式
      当条件等于值1时,函数翻译值1,条件等于值2时,函数翻译值2,都不符合时,返回缺省值
      -- 9大于3
      select decode(sign(9-3),1,'9大于3',-1,'9小于3',0,'9等于3','不知道') from dual;
      16)判断正负数函数:sign(变量1-变量2)
      根据(变量1-变量2)的值是0、正数、负数,返回0、1、-1


      17) translate(Str,fromStr,toStr)
      1、将Str中的字符串,替换后返回,按fromStr与toStr一一对应的方式,如果不能一一对应则被视为空值。
      2、如果fromStr字符串长度比toStr长,则fromStr字符串比toStr字符串,多出的字符将被删除。
      3、translate中的任何参数为null,那么结果也是null。
      --1 9XXX999 将数字转换为9,其他的大写字母转换为X,然后返回
      select translate('2KRW229',
      '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
      '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') result
      from dual;


      --2 2229 将数字保留,将其他的大写字母移除
      select translate('2KRW229',
      '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
      '0123456789') as result
      from dual;
      --3 我是Ch人,我爱Ch 按照字符来处理,不是按照字节来处理,如果toStr的字符数比fromStr多的话,多出的字符会被删除
      select translate('我是中国人,我爱中国', '中国', 'China') "result"
      from dual;


      --4 I m 中国ese, I love 中国 如果fromStr的字符数大于toStr,多出的字符会被删除
      select translate('I am Chinese, I love China', 'China', '中国') as "result"
      from dual;
      --5 如果参数为空或空字符串,整个返回null
      select translate('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', null) "结果"
      from dual;
      --6 **人 只显示账户中的姓名最后一个字,其余的用星号代替
      select translate('中国人',
      substr('中国人', 1, length('中国人') - 1),
      rpad('*', length('中国人'), '*')) as "结果"
      from dual;


      18)排序函数:
      1、row_number() over([partition by colum] order by colum)
      为有序组中的每一行返回一个唯一的排序值,序号由 order by 子句指定,从 1 开始,即使具有相等的值,排位也不同。
      partition by colum 按列值进行区分,各分组内在进行排序。
      2、dense_rank() over([partition by colum] order by colum)
      计算一个值在一个组中的地位,由 1 开头,具有相等值得行排位相同,并且排位是连续的。
      3、rank() over([partition by colum] order by colum)
      计算一个值在一个组中的地位,由 1 开头,具有相等值得行排位相同,序数随后跳跃相应的数值。


      5、SQL语句函数运用

      分组排序的示例
      --按日期统计(角色1) 用户的数量
      select trunc(cr.created_date, 'dd'),
      count(case
      when cr.role_id = 'rolea' then
      1
      else
      null
      end) as 角色1
      from check_role cr
      group by trunc(cr.created_date, 'dd')
      order by trunc(cr.created_date, 'dd') desc;


      使用伪列,字符串操作示例
      --返回最新版本号
      select flowId
      from (select row_number() over(partition by cr.key_ order by cr.version_ desc) rnum,
      substr(cr.key_,
      instr(cr.key_, '_', 1, 2) + 1,
      length(cr.key_)) cityId,
      cr.ID_ flowId,
      cr.version_
      from check_record cr
      where cr.key_ like 'check_test%')
      where rnum = 1;


      decode函数示例
      --我的消息表
      select decode(tn.type,1,'类型1',2,'类型2',3,'类型3',4,'类型4') as "产品类型", tn.user_id as "用户编号", count(*) as "消息条数"
      from test_news tn
      where tn.user_id is not null
      and tn.created_date >=
      to_date('2014-11-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss')
      group by decode(tn.type,1,'类型1',2,'类型2',3,'类型3',4,'类型4'), tn.user_id
      order by decode(tn.type,1,'类型1',2,'类型2',3,'类型3',4,'类型4') desc, tn.user_id desc, count(*) desc

       

      www.1click-soft.comtruehttp://www.1click-soft.com/oracle/1311385.htmlTechArticleOracle数据库常用函数使用示例,oracle数据库示例 Oracle数据库基础简介及实践 1、开始(p1~p2) 2、SQL语句编写思路(p3) 3、Oracle常用运算符介绍...

      相关文章

      相关搜索: oracle数据库

      帮客评论

      视觉看点
      百度 360 搜狗