Oracle基本用法常用函数集合运算序列

最近博主换了业务部门，偶然发现之前的Oracle基础有点薄弱，遂整理了一下Oracle视图、伪劣、常用函数、集合、运算、序列、索引以及同义词，现将整理如下：

查看表结构

可以直接  直接在表名上右键“查看”，即可看到表结构 或者 plsql COMMAND WINDOW 

DESCRIBE + tablename  或者  desc + tablename  即可查看表结构

常用函数

lower     转小写  
          select lower(last_name) from employees
          select lower('HELLOWORLD') from dual          
upper     转大写
          select upper(last_name) from employees
          select upper('helloworld') from dual          
initcap  首字母转大写   
          select initcap('helloworld') from dual        
CONCAT   连接  相当于  ||
          select concat(first_name,last_name) from employees     
SUBSTR   截取
          select substr(last_name,2,3) from employees   从第2个字符开始，截取3个字符  
LENGTH  求长度
         select length(last_name) from employees    
INSTR  
      select instr('helloworld','w') from dual  ----> 6  w在helloworld中第一次出现的位置
LPAD | RPAD   左填充 | 右填充
      select lpad('hello',10,'*') from dual   ----->  *****hello
      select rpad('hello',10,'*') from dual   ----->  hello*****  
TRIM   去空格(去两侧空格)
      select trim('   hell o    ') from dual   ---->hell o     
REPLACE 替换
       select replace ('zhangsan','a','*') from dual  
ROUND: 四舍五入指定小数的值
        ROUND(45.926)         46
        ROUND(45.926, 2)      45.93
        ROUND(45.926, -1)     50
        ROUND(45.926, -2)     0      
TRUNC: 截断指定小数的值
        TRUNC(45.926, 2)      45
        TRUNC(45.926, 2)      45.92
        TRUNC(45.926, -1)      40
        TRUNC(45.926, -2)      0      
MOD: 返回除法的余数
        MOD(1600, 300)        100        
sysdate   系统当前时间
      select sysdate from dual        
MONTHS_BETWEEN ('01-9月-95','11-1月-94')   计算两个时间之间相隔多少个月
ADD_MONTHS ('11-1月-94',6)   在时间上加上相应的月数
NEXT_DAY (sysdate,'星期五')   SELECT NEXT_DAY(sysdate,   'MONDAY ') FROM DUAL;      
LAST_DAY(sysdate)    当前参数所给时间所在月份的最后一天的日期

转换函数

to_char日期转字符   
     select to_char(sysdate,'yyyy-mm-dd') from dual
     select to_char(sysdate,'year-mm-dd') from dual
     select to_char(sysdate,'year-month-dd') from dual    
     数字转字符
     select to_char(1000,'$99,999.00') from dual
     select to_char(1000,'L99,999.00') from dual  
     select to_char(1000,'$00,999.00') from dual
to_number字符转数字
     select to_number('999')+1 from dual  ---->1000
     select '999'+1 from dual ---------------->1000
     select to_number('aaa')+1 from dual  ----> 报错
to_date字符转日期：
    select to_date('1999-10-10','yyyy-mm-dd') from dual
    select to_date('1999-10-10 11:11:11','yyyy-mm-dd hh:mi:ss') from dual  
NVL (expr1, expr2)   判断expr1是否为null。如果不为null，返回expr1。 如果为null  返回expr2.----> 相当于MySQL的ifnull函数
NVL2 (expr1, expr2, expr3)  判断expr1是否为null。如果不为null，返回expr2。  如果为null返回expr3
NULLIF (expr1, expr2)   如果 expr1 不等于expr2 返回 expr1 。 expr1 == expr2 返回null
COALESCE (expr1, expr2, ..., exprn)   返回第一个不是null的表达式   
CASE 表达式：
select last_name,salary,department_id, case department_id when 50 then salary*1.5
                                                          when 90 then salary*2
                                                          else salary end newsalary from employees
判断 department_id 如果为50 salary*1.5
                   如果为50 salary*2
DECODE函数 select last_name,salary,department_id, decode(department_id,50,salary*1.5, 90,salary*2,salary) from employees

伪列

 rowid --- > 这条数据在数据文件中的偏移量（可以简单理解为在数据文件中的地址）----> 可以提高查询效率！！！！
 select * from employees where rowid='AAAMiZAAFAAAAA4AAA'
 rownum
  1 select rownum,e.* from employees e where rownum<=5  -----  取前5条数据  
  2 取工资排在前5位的员工姓名和工资
     select t.* from (select e.last_name,e.salary from employees e order by e.salary desc) t where rownum<=5 
  3 取employees表的中第3条到第8条数据      
      select rownum,e.* from employees e where rownum>5  ----> 查不到任何数据 
      （因为每加一个rownum判断一次条件，当第一条件数据添加rownum时，添加的是1,1不满足查询条件，这条数据被抛弃，下一条添加rownum时，添加的还是1  ....）    
      因此
      select rownum,e.* from employees e where rownum between 3 and 8  ----> 查不到数据
      select t.* from(select rownum rn,e.* from employees e) t where t.rn between 3 and 8
  4 取工资排在第3位到第5位的员工姓名和工资
    select ttt.* from (select t.*,rownum rn from (select e.last_name,e.salary from employees e order by e.salary desc) t)ttt 
    where ttt.rn between 3 and 5

聚合函数    

多表查询       
          左外连接    left outer join             等值连接     条件1=条件2(+)
          全外连接      full outer join    
子查询
创建和管理表  DDL
约束  语句创建约束
        create table aaa(     
            id number primary key,
            name vachar2(20) not null,
            email varchar2(20) unique,
            age number check (age>=10 and age<=100),
            dept_id number references dept(id),
                      
            constraint aaa_pk primary key(id),
            constraint aaa_un unique(email),
            constraint aaa_chk check (age>=10 and age<=100),
            constraint aaa_fk foreign key(dept_id) references dept(id)   
        )

        alter table aaa add constraint aaa_pk primary key(id)
        alter table aaa modify name vachar2(20) not null
        alter table aaa add constraint aaa_un unique(email),
        alter table aaa add constraint aaa_chk check (age>=10 and age<=100),
        alter table aaa add constraint aaa_fk foreign key(dept_id) references dept(id)

        
视图

 一个或多个表的部分数据组成的一个新的数据库对象

    1 限制数据访问
    2 简化复杂查询
    create view v_emp_dept80 as   select 。。。。。
    
简单视图   DML操作 ---->  形成视图的表中的数据也会改变 
复杂视图 
内建视图：   from 后的子查询    
伪列：  
        rowid
        rownum   top n   
        rownum 不能使用 >     
=======================================================================================================        
序列：  
    create sequence  first_seq  
      increment by 1
      start with 100 
使用序列：
     查看序列值
      select first_seq.nextval from dual  ---  >  序列的下一个值
      select first_seq.currval from dual  ---  >  序列的当前值 
    添加数据时使用序列：
       insert into ttt values (first_seq.nextval,'aaa',20)
索引：  就相当于书的目录 （字典）作用  提高查询效率    
创建索引：
自动：在一个表的定义中，当定义一个 PRIMARY KEY，或 UNIQUE 约束时，一个唯一索引被自动创建
手动：用户能够在列上创建非唯一的索引来加速对行的访问
语句创建索引：
    create index last_name_index on employees (last_name)
添加索引可以提高查询效率，那么为什么不把所有列都添加索引呢？
    索引只能提高查询效率，会影响增删改的效率
    所以一般什么列上添加索引  ---->   经常根据这个列查询，很少对这个列进行增删改
同义词：
     create synonym stu for student  给student创建了一个 同义词 stu   
        以后对stu和student进行操作完全相同      
=======================================================================        
DDL 
DML
DQL
DCL  数据控制语言
---------------------------------------------------------------------------------------------
创建用户：  
          create user zhangsan identified by zhangsan         
给用户分配系统权限
         grant  create session,create table,create view,create sequence to zhangsan    
-------------------------------------------------------------------------------------------
创建角色：         create role manager
        
给角色分配权限
         grant     create session,create table,create view,create sequenc to manager        
给用户分配角色
        grant manager to lisi
--------------------------------------------------------------------------------------
修改密码
alter user zhangsan identified by aaa
-----------------------------------------------------------------------------------------
分配对象权限
grant 对象权 限1，对象权限2 ... on 对象 to 用户 | 角色 | public
grant select,delete on departments  to lisi
撤销对象权限
revoke select,delete on departments  from lisi
==============================================================================================

集合运算

    A   (1,2,3)
    B   (2,3,4)
    A B 并集   (1,2,3,4)   完全联合 (1,2,3,2,3,4) 

    uion和union all的主要区别是union all是把结果集直接合并在一起，而是将union 是把union all后的结果进行一次distinct，去除重复记录后的结果
    (select employee_id,last_name,salary from employees where employee_id between 100 and 150) 
          union
    (select employee_id,last_name,salary from employees where employee_id between 120 and 180)
    (select employee_id,last_name,salary from employees where employee_id between 100 and 150)
          union all
    (select employee_id,last_name,salary from employees where employee_id between 120 and 180)
    A B 的交集  (2,3)
select employee_id,last_name,salary from employees where employee_id between 100 and 150) 
   intersect
(select employee_id,last_name,salary from employees where employee_id between 120 and 180)
    A-B 的差集    (1)
select employee_id,last_name,salary from employees where employee_id between 100 and 150) 
   minus
(select employee_id,last_name,salary from employees where employee_id between 120 and 180)
******  order by 只能放到最后

分级查询

--查询 Lorentz 的上级
select last_name from employees where employee_id=
(select manager_id from employees where last_name='Lorentz')
-- 查询 Lorentz 的所有上级  -------分级查询
select last_name from employees start with last_name='King' connect by prior  manager_id = employee_id
从顶向下    ----》Column1 = Parent Key
                  Column2 = Child Key
从底向上    ----->
               Column1 = Child Key
               Column2 = Parent Key
数据库对象：
table
view 
sequence  序列     产生一个递增的数字   添加时为主键列赋值
     create sequence aaa_seq
        start with  1
        increment by 1
        cache
        cycle
        max
        min
            
     select aaa_seq.nextval from dual        
     select aaa_seq.currval from dual
     
     insert into users values(aaa_seq.nextval,...)
index   根据添加了索引列进行查询，提高查询效率
     数据库本身添加的   添加了主键约束   唯一性约束的列上 oracle添加了索引 
     用户自定义的索引   create index name_idx on employees(name) 
     添加索引可以提高查询效率，但是会降低增删改的效率
synonym    就是起别名      
分级查询（oracle特有的）  start with   
     
    connect by prior    从低向上     child = parent
                        从定向下     parent=child         
PLSQL的语法    
自定义函数
    create or replace function fun(str in varchar,...) return varchar is
       。。。
    begin
       return 
    end;
存储过程    有参数，没有返回值       有out类型的参数--->类似于 函数的返回值
     create or replace procedure zhuanzhang(id1 in number,... ,res out varchar) is
     begin
     end