Oracle入门笔记(三)

一、系统函数

 

系统函数: 
    * Oracle中已经定义好的能够直接使用的函数

数值函数:
    abs() :取绝对值
    *sign() : 取符号
    ceil() : 取大于或等于给定值的最小整数
    floor() : 取小于或等于给定值的最大整数
    power(x,y) 
    mod(d1,d2) : 取余数

    ***取指定位数小数
    round(d1,d2) 
    trunc(d1,d2)
       * 如果只有一个参数,则保留整数部分
       * 第二个参数指定小数位可以有多少位
       * round能够做四舍五入
         trunc直接舍去

字符函数:
    upper()
       * 将字符全部转换成大写 
    lower()
       * 将字符全部转为小写
    initcap()
       * 将字符的首字母转换为大写
    length()
       * 求字符串的长度
    substr(c1,c2,c3)
       * 截取字符串
         c1 被截取的字符串
	 c2 从哪个位置开始截取
	 c3 截取多长(默认截取到最后)
    instr(c1,c2,c3,c4)
       * 索引字符串位置
         c1 被索引的字符串
	 c2 希望查询到的字符
	 c3 从哪个位置开始查找  默认1
	 c4 第几次出现  默认1
    concat(c1,c2) 不推荐使用
       * 将两个字符连接成为一个字符串
       * 使用 || 来拼接字符串
    
    lpad(c1,c2,c3) 左侧补全到指定位数
       * c1 是需要补全的字符
         c2 是要补全到多少位
	 c3 是以哪个字符来补全
    rpad(c1,c2,c3) 右侧补全到指定位数

    ltrim(c1,c2) 左侧祛除指定的字符
       * c2如果不出现，默认除去空格
    rtrim(c1,c2) 右侧祛除指定的字符
    trim(c1)
       * 两侧祛除空格
    trim(c1 from c2)
       * 从c2的两侧，移除指定的c1

    replace(c1,c2,c3) 完全替换
        * 按照查询到的c2，完全替换成为c3
    translate(c1,c2,c3) 按位替换
        * 按照c2与c3对应的字符来，来逐位替换
       * c1 :被查找的字符串
       * c2 :希望替换的字符串
       * c3 :被替换成的字符串

---------------------------------------------------
表Test:
  编号tid   手机号phone 
   10       13111002200 
   12       13556785678
   13       18645444544
-- 查询Test表中的手机好,用'*'遮盖中间四位显示
     select 
       substr(phone,1,3) || '****'|| substr(phone,8)
       from test;

   select 
     rpad(substr(phone,1,3),7,'*') || substr(phone,8)
	from test;

日期函数:
    * 日期相加无意义
    * 日期相减可以算出相差多少天  单位：天
    * 指定日期所在月份的最后一天
       last_day(日期)
    * 在指定日期的基础上，增加指定月份
       add_months(日期,+/-整数)
    * 计算两个日期之间的月份
       months_between(日期1,日期2)
    * 日期可以使用
        round  中午过后算明天
	trunc
    * 得到指定星期的下一天的日期
        next_day(日期,'星期日')

转换函数:
 to_date('2015-12-10','yyyy-mm-dd')
   * 将c1的字符格式的日期转换为日期类型

	yyyy :年
	mm  : 月
	month :带有'月'份的月
	ddd : 年中的第几天
	dd  : 月中的第几天
	d   : 星期中的第几天
	hh  :12小时制
	hh24 :24小时制
	mi  :分钟
	ss  :秒

to_char(sysdate,'yyyy/month')
   * 将日期按照c2中指定的格式截取成为字符串

	--截取'2012/08/09 12:45:59',截取其中的小时
	select 
	   to_char( 
		to_date('2012/08/09 13:45:59', 'yyyy/mm/dd hh24:mi:ss'),'hh')
	from dual;

	--计算'2012/08/09'是今年的第几天
	select 
	 to_char( to_date('2012/08/09','yyyy/mm/dd'),'ddd')
	from dual;

to_char('字符数字','格式')
    *按照c2指定的格式，将c1中的数字格式化
    $ :国际货币通用符号
    L :本地国家货币符号

to_char(1234)   --> '1234'

to_number('1234')  -- 1234

select * from emp where 1='1';
select * from emp where 1=to_number('1'); 效率高

--------------------------------------------------
Exec:
  查询员工表emp中所有在1988年入职的员工信息
 select * from emp 
    where to_char(hiredate,'yyyy')='1988';

分支函数:
 
 decode(c1,c2,c3)
 decode(c1,c2,c3,c4)
 decode(c1,c2,c3,c4,c5,c6,c7,....,Cx,Cx+1)
 decode(c1,c2,c3,c4,c5....,Cx,Cx+1,Cx+2)
   * decode中的参数，c1是某个条件值，
      从第二个参数开始，没两个参数看作是一组数，
      拿每一组数的第一个参数来跟c1比较，
      如果相同则返回该组的第二个参数，否则继续往下判断
   * 如果参数个数是奇数个,则默认的返回值是null
     如果参数个数是偶数个,则默认的返回值是最后的参数
 
Exec:   
-- 查询员工姓名 
-- 如果员工的名字长度是5 则工资增加1000
-- 如果员工姓名长度是6 ，则工资增加2000
-- 如果员工姓名长度是其他，则工资减去500
select ename,length(ename),sal,
      decode(length(ename),5,sal+1000,
                    6,sal+2000,sal-500) newsal
   from emp;


case when then else end :
 第一种写法：
   case : 条件取值开始
    when 条件判断
     then 返回值
    when 条件判断
     then 返回值
     ...
    else 返回值
   end ; 条件取值结束
 
  select ename,length(ename),sal,
     (case when length(ename)>=6 then sal+2000
           when length(ename)>=5 then sal+1000
	    else sal-500 
	end) newsal 
    from emp;

 第二种写法：
    case 条件判断的值A
      when 条件B
       then 返回值1
      when 条件C
       then 返回值2
      ....
      else 返回值x
     end;

  select ename,length(ename),sal,
     (case length(ename)
       when 5 then sal+1000
           when 6 then sal+2000
	    else sal-500 
	end) newsal 
    from emp;

--------------------------------------------------
Exec:
  -- 查询，如果该列上对应的学科是'语文' 学科
  -- 则返回对应的成绩，如果该列上不是语文，则返回0\
  -- 数学列 英语列

select sid,name,subject,
decode('语文',subject,score,0) "语文",
(case subject 
   when '数学' then score 
     else 0 end) 数学,
(case 
    when subject='英语' then score 
      else 0 end) 英语
from point; 

单行函数：数值函数  字符函数 日期函数 转换函数 取值函数..

去空函数： 
 nvl(c1,c2)
   * 如果c1是空值，则返回c2
 nvl2(c1,c2,c3)
   * 如果c1不是空值，则返回c2,如果c1是空值，则返回c3


----------------------------------------------------

**
 如果不是分组语句：
 单行函数(包括普通的列名)
    不要和多行函数出现在同一个查询语句中


聚组函数:多行函数
   * 从一个范围中查询某个列最大最小....
max() 最大值
min() 最小值
avg() 平均值
sum() 求和值
count() 统计条数

-- 查询员工表的最大工资是多少

二、符号常用的关键字

 

 

比较运算符:
 >  <  >= <=   != 或者 <>

赋值:
 :=   过程中常使用的赋值符号 

条件判断:
 =  : 放在where或者 having之后表示条件判断
      放在set 之后表示赋值

与空判断的符号:
  is null  : 是一个空
  is not null : 不是空

between: 闭合区间
 -- 查询工资在2000到3000之间的记录：
 select * from emp where sal>=2000 and sal<=3000;

 select * from emp where sal between 2000 and 3000;

模糊查询的关键字:
  like : 表示用匹配一个字符串
 匹配符:
   % : 用来匹配任意位任意字符
   _ : 用来匹配一位上的任意字符

逃离符: escape
   * 使用在一些特殊情况的匹配中 % _
   * 逃离符表示之后指定的字符作为一个普通字符

      where A like '%-_%' escape '-';

   * 表让A匹配like之后的字符:
     而 '%-_%'  第一个% :代表任意位任意字符
                 - : 代表逃离符的标志
		 _ : 代表普通的一个字符 没有匹配的含义 
		第二个%: 代表任意位任意字符

where 之后如果有多个条件:
  and :表示条件必须同时成立
  or : 表示条件满足其中的一个就算成立

	-- 查询工资大于1000并且工种为'CLERK'的员工记录:
	select * from emp where sal>1000 and job='CLERK';

	-- 查询工资大于3000或者工资小于1000的员工信息:
	select * from emp where sal>3000 or sal<1000;

Distinct: 在查询中去除重复数据
	-- 查询有多少中不同的工作
	select distinct job from emp;
	 
	distinct 能够根据多个列的组合来筛选不重复的数据:
	select distinct job,mgr from emp;