| oracle

Oracle学习笔记1

Oracle数据库是一个对象关系数据库管理系统（ORDBMS）。它通常被称为Oracle RDBMS或简称为Oracle，是一个收费的数据库。

基础部分

Oracle中distinct可以消除重复的行，如果指定多个列名，则要保证多列的数据皆重复才会消除。

select distinct name,sal from emp

Oracle中有独特的字符串连接符’||‘，相当于java里的’+’,MYSQL里可以用concat连接字符串。

select '姓名是:' || NAME || '的年龄为' || AGE from emp;

select concat(NAME,'是姓名'，AGE，'是年龄'    ) from emp;

查询列取别名时，若别名中有特殊字符或者关键字需要加上双引号。(其他地方大部分使用单引号）

select name "my name" from emp;

LIKE关键字使用时，若匹配的字符里有特殊字符，需要使用escape关键字
如，要查询员工姓名里有’%’字符的员工信息。

select * from emp where name like '%\%%' escape '\';

其中第一和最后一个%号都表示任意字符，而escape声明’\‘为转义字符，所以第二个%表示字符’%’。
也可以这样写:

select * from emp where name like '%#%%' escape '#';

所以我猜想在oracle里反斜杠并不代表转义字符的意思吧。。

在排序的时候，如果有空值，需要在末尾加上null的说明

select * from emp order by number desc nulls last;

表示null值放在末尾，还有nulls first.

oracle里有一个伪表用来做返回字符或者数值，叫做dual，在MYSQL中不用加from dual；

同样的，oracle里也有很多一样的函数

单行函数

  • concat(): 字符串连接

    select concat('hello','world') from dual;
  • substr():字符串的截取substr(str,0,3)表示从索引位置0（0和1效果相同）开始截取str，截取3个字符的长度。
  • length()：获取字符串长度
  • replace()：replace(str,’a’,’b’)表示把str里所有的a替换成b
  • round()：四舍五入round(num,2)，2表示保留2位小数0
  • ceil()：进1取整
  • trunc()：截断trunc(num,2)去掉小数点后2位的尾数
  • MOD()：求余MOD(NUM1,NUM2)
  • MONTHS_BETWEEN()：获得两个时间段中的月数，months_between(sysdate,finishdate)——sysdate表示系统时间
  • ADD_MONTHS()：获取几个月后的日期，add_months(sysdate,3)系统时间3个月后。
  • TO_CHAR：字符串转换函数
  • TO_NUMBER：数值转换函数
  • TO_DATE：日期转换函数

    如：
    TO_CHAR(date,’yyyy-mm-dd’)
    TO_CHAR(date,’yyyy’)等
    把月和日前面的0去掉要在后面的model前加上fm
    TO_CHAR(date,’fmyyyy-mm-dd’)
    TO_CHAR(sysdate,’d’)一个d代表一个星期的第几天，两个d代表一个月的第几天，三个d代表一年中的第几天
    TO_CHAR(sysdate,’day’)星期几–返回monday什么的，如果是dy，则返回简写–mon之类的

也就是这三种类型的相互转换函数
TO_CHAR(sal,’L9,999.99’),可以格式化货币，L表示本地货币

  • NVL()：空值处理，如果为空值就取后面的值为默认值，nvl(name,’Peter’)
  • NVL2()：nvl2(参数1，参数2，参数3)，如果参数1为空值，就返回参数3，否则返回参数2
  • nullif()：nullif(参数1，参数2)如果参数1等于参数2，那么就返回null，否则返回参数1
  • coalesce()：这个函数传入很多参数，然后返回第一个不为null的值

条件表达式：在SQL语句中使用IF-THEN-ELSE

CASE表达式：SQL99的语法，类似Basic，比较繁琐

select
    case name
        when 'Peter' then 'P'
        when 'Bob' then 'B'
        else 
            'C'
        end
from emp;

就是当name等于when里的值就改为then后面的值，这种写法通用于MYSQL和Oracle

DECODE函数：Oracle自己的语法，类似java，比较简洁

select decode(name,'Peter','P','Bob','B','C') from emp;

效果和上面一样

多行函数

  • max()：某列最大值
  • min()：某列最小值
  • count()：统计行数
  • avg()：平均数，注意空值影响
  • sum()：求和
    多行函数常与分组关键字Group by配合使用，另外having关键字在group by后执行，where后不能接多行函数。

就是说只能这样写

select 列名a，分组函数 from 表 group by 这个列名a having 条件

多表查询、子查询

多表查询

内外连接我在前面sql写过了，就写一些oracle不同的地方

全外连接为俩表都完全匹配。不加where条件即为外连接

内连接，加where条件

左（外）连接
显示左边表的全部，匹配右边的表
select e.empno,e.ename,m.empno,m.ename from emp e,emp m where e.mgr=m.empno(+)
在右边加+号表示用该表匹配左表，故为左连接。

右连接反之。

子查询

也就是在一个大的主查询里先做一个小的子查询供主查询使用。

例

select name,job,sal from emp 
where sal=(select min(sal) from emp)

子查询返回结果类型要和比较类型相同。

多行子查询
若子查询返回多行结果，应与多行比较符匹配：in、>any….

子查询返回空值可能会报错，这个时候需要使用exists()，括号里加入子查询语句，若查询结果为空，返回false

select * from emp where exists(select * from dept where deptno=1)

Rownum
当要查询第几行到第几行的数据时，我们使用rownum。

rownum是oracle预处理字段，默认标序是1，只有记录集已经满足条件后才会进行后续编号。这样你查询表时遍历第一条数据时rownum是1，不符合条件，继续遍历到第二条数据rownum仍为1，仍不符合条件，直至遍历完所有数据，都无数据返回。

查询第二行之后的数据

select * from(select rownum no ,id,name from student) where no>2;

rownum不能直接使用判断>的条件。
另外，rownum是按照插入顺序排列的，所以要按某列排序需要使用子查询

select rownum ,id,name from (select * from student order by name)

查询第n行到m行的数据

(select empno,ename from emp where rownum<m)

minus

(select empno,ename from emp where rownum<n)

集合运算

就是俩条语句的结果集的运算
有

  • UNION并集
  • intersect交集
  • minus差集

特征：
两边查询的字段数量、字段类型、顺序必须一致。

ESC