Oracle基础学习

系统全局共享区  system global area   （SGA）：共享的内存空间，被看做数据库的一个大的缓冲池。可被oracle的各个进程公用。包括library 和 data dictionary 使用

PGA：是一个用户连接到数据库的缓冲区 的内存区域 

library Cache是sql语句分析后的结果保存到这个缓存中第二次直接用，减少时间 

database buffer Cache  对应所有文件中常用到的数据块。数据库的任何修改都在这完成。然后由dbwr 进程将修改后数据写入磁盘

重做日志文件。   对数据库的修改都先记录到日志文件中。再由lgwr进程写入磁盘

oracle 逻辑存储结构

database 数据库》tablespace 表空间》segment 段》ectent 区》oracle block块

oracle 赋予权限  grant  ... to    回收权限 revoke ... from 

cmd  登录oracle sqlplus / as sysdba 默认用Windows 用户验证登录

创建用户： create user a identified ba a ; 用户名 a  密码 a

赋予权限： grant create session to a;  赋予创建会话的权限

回收权限：revoke  create session from a; 回收会话功能 后  就不能登录数据库了

链接数据库：conn  用户名/密码

数据字典;数据库的信息，比如创建者，创建时间，所属表的信息

模式对象：oracle  数据库 工具

    daca ：数据库管理工具。创建删除数据库等。

    netmanager：建立对远程数据库的链接。配置服务和监听

    sql developer：图形化工具

    sql * plus:dos命令  远程数据库sqlplus 用户名/密码@testdb 只能32位客户端

    pl/sql deleloper:图形化工具 类似 sql developer

查询语句:select */列明  from  表 where 条件  group by  分组查询 order by 排序 关键字大小不敏感

别名 ： select 列明  as  别名 from 表明  有空格的别名得用引号

去重命名： distinct  比如：select distinct  */列名 from  表；

表达式的应用： 连接操作符   （列名 ||"工资是"|| 列名）这里查询的为一列 

where 子句： 字符串和日期 要用单引号  select * from a where name ='啊啊' 

                       日期： time>' dd-mon-yy' 日-月-年   全是两位

and or  not  in 

模糊查询： like '%字符串%'  %代表多个字符  _代表一个字符

查询空值   is  null  非空  is  not null

排序：order by 升序   asc 降序 desc  默认是升序 

select ename,job,mg,hiredate, sal salary from order by salary desc,hiredate asc 先根据sal的降序，sal相同时再 hiredate的升序排序 oracle 中 null默认是最大值

 

内置函数：

     字符串函数： chr(int) 对应的asc|| 码   比如 chr(65) 返回出来的是  A   这是单行函数

     contat(string1, string2)    链接字符串。连接string1和string2 可以是列明 比如 把表中                                           的 姓名和工资连接起来

     initcap(sring)  string 字符串的第一个变为大写

     lower(string) 全为小写 

     upper(string) 全为大写

    lpad(string,count[,char]) 让指定的字符char在字符串string的左边填充 比如lpad(name,10,'*')如果name不够十个在左边填充*

    rpad(string,count[,char]) 右边

    ltrim(string,char) 去掉左边的字符char

    rtrim(string,char) 去掉右边的char

    replace(string, char，char)替换字符 后面替换前面的

    substr(string , start,length) 获取子字符串 第三个到第六个   后面参数 3,6

    length(string) 获取长度

数字函数

    abs(value)绝对值

    ceil(value)返回大于或者等于value的最小整数

    foolr(value)返回小于或等于value的最大整数

    power(value,exponent) 求value的指数值  2的3次方

    round(value,preponent)对precision的精度四舍五入

   mod(value, divisor) 取余

时间函数

    日期:默认格式：dd-mon(中文) -yy

    add_months(date,count) 指定日期上增加count月

    last_day(date) 返回所在月的最后一天

    next_day(date,'星期几') 返回下一周的星期几的年月日 

    months_between（data1,data2）返回data1和data2 之间有几个月

    sysdate 获取当前系统日期

    current_timestamp 获取当前的日期和日期值 

    round (date,'year')对日期四舍五入 

    trunc(date,'year') 对日期进行截取 只要年份其他为一月一号

    day 是星期几  dd 日期 几号 

类型装换函数

    to_char(date,'format' ) 把日期 装换为指定格式 字符串和数字之间的转换

    to_date(string ,'format') 把字符串转换为时间

    to_number(string,'format') 字符串转int

    $9,999.99 表示：4个9表示 4位整数 .99表示 两位小数

通用函数：

   oracle 默认 一个为null相加都为null

    nvl(a,b) 如果a为null则返回b，否则返回a 

    nvl2(a,b,c)如果a不为null则返回b，否则返回c

    nullif(a,b)如果a和b 相等返回null,否则返回a

    coalesce() 返回清单中第一个不为空的值  参数为无数个

    decode(a,b,c,d,e,f)a等于b返回c  a等于d返回e  否则返回f  有无数个参数

分组查询

    avg ，max ，min，count，sum

    count(*) 条数  

    group by  分组 统计  以什么分组 比如 group by  年级  select 后的列明必须出现在group by之后。否则语法错误，并且不能在 where 语句后

select max(avg(sal)) from emp group by deptno;  先以deptno分组求 各个组的平均工资再得到最高 平均工资

连接查询：

    select * from emp e,dept d  得到笛卡尔积

    select * from emp e, dept d where e.deptno=d.deptno 进行筛选 后得到需要的数据

    链接查询得到新的 中间表  

    左外链接： select d.dname , e.ename from dept.d ,emp e where d.depeno= e.deptno(+);首先查询符合条件的 然后不符合条件的也会查出来

    右 外链接：  select d.dname , e.ename from dept.d ,emp e where d.depeno= e.deptno(+) ;首先查询符合条件的 然后不符合条件的也会查出来

       自连接: select e1.ename,e2.ename mgr  from emp e1,empe2 where e1.mgr = e2.empno;  自己连接自己

子查询;

    单行子查询：子查询查询出来的 只有一个记录比如得到一个人的工资

    having 外层查询语句

    多行子查询： 多行操作符： in  not in  any all 

   子查询有空  查询出来的都是空

    内联视图： 子查询作为一张零时的表。

    rownum 

    top-n分析：  select ename,sal from (select ename,sal from emp order by sal desc) where rownum <=10 

                      子查询先按降序 排列；得到一张零时表，然后再取前十；

    集合操作： 

        并 -union     交-intersecint 差-minus

pl/sql developer 

declare 这里声明变量

    x number := 100; 具体声明变量

begin

  dbms_output.put_line(x); 执行程序块的具体内容

end;

简单的pl/sql模块

 无名快实例

declare

  

begin

  dbms_output.put_line('当前时间是：'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));

end;

输出   当前时间是：2016-12-13 09:20:27

 注释 、 /*  */

声明变量

  变量名  变量类型 :=赋值

常量

    变量名  constant 变量类型 := 赋值

赋值方式

 :=    

select   into   比如：  select count(*) into y from emp;

赋值多个变量  select count(*) ，avg(sal) into y,x from emp;

if条件控制语句

    if<布尔表达式> then

         pl/sql 语句

    end if  

    if<布尔类型> then

        pl/sql语句

    elsif <布尔类型> then 

        pl/sql 语句

    else

        

    end if

case 条件控制语句

    case selector

        when 选择1 then 结果

        else 结果

    例子： select ename,sql, case

                                               when sal >=800 and sal<1200 then '800---1200'

                                               when sal>=1200 and sal <1600 then '1200--1600'

                                                else 'others'

                                               end  '工资结果'

                from emp;

      

 例子2

    选择器  类似java  中的 case    

   出现在 sql 语句中  而 if then  出现在pl/sql中

循环语句

    loop 执行语句 exit when<条件语句> end loop；

   例子 ：

    declare

        x number :=1

begin

    loop

    x:=x+1;

    exit when x=5;

    end loop;

end

while <条件> loop

    执行语句

end loop;

for 循环

for 计数器 in reverse  1..5 loop  1到5       reverse  表示从5到1  不写 就是  1到5 

    语句

end loop;

null 语句；  什么都不做

 异常处理：  预定义异常 非预定义异常 用户定义异常

    

语法结构 ：

     declare 

         salary sal into salary from emp;

        exception

            when too_many_rows then 

                dbms_output.put_line('too many rows');返回太多行了

           when too_data_found then

                dbms_output.put_line ('no data found') 没有找到数据

     end;

函数定义  一有名字的 pl/sql  程序块

语法定义:

    

 显示游标使用方法

declare

    cursor cur1 is select * from emp; 把查询的放入 cur1游标中    1.定义游标

    emp_record emp%rowtype;  emp的行记录类型 放emp表的一行记录      2.行记录

begin

  open cur1;    打开游标                                3.打开游标

  loop

    fetch cur1 into emp_record;  得到一行记录 游标向下移动        4.用行记录去读游标中的一行记录

    exit when cur1%notfound; 判断还有没有数据                  5.判断是否还有记录

    dbms_output.put_line(emp_record.ename);                    

 end loop;

 close cur1; 关闭游标                                    6.关闭游标

 end;

隐式游标
for rowdate in(select * from emp)

 loop 

    dbms_output.put_line(rowdata,ename);

end loop;

end;

和上面的显示游标 功能一样

参数游标

declare 

    cursor cur1(eno number) is select * from emp where empno = eno; 带参数 eno的游标 然后根据eno去查询

begin 

    open cur1(7499);  传递 7499这个参数过去

    loop

        fetch cur1 into emp_record;

        exit when cur1%notfound;

        dbms_output.put_line(emp_record.ename);

    end loop;

close cur1;

 游标的属性

    cursor_name%found 最近fetch  取值是够为空  返回 bool 类型的值

    cursor_name%notfound 与found相反

    cursor_isopen 查看游标是否打开

    cursor_name%rowcount  以及从游标读取的记录数；

存储过程

     create   or   replace   procedure  proc1  is

    salary number;

begin

  select sal into salary from emp where empno = 7499;

  dbms_output.out_line(salar)

end proc1;

调用存储过程

命令窗口  exec proc1; 

sql窗口下 

begin

  proc1;

end;  

一个存储过程调用另外一个存储过程：

  create   or   replace   procedure  proc1(eno in number,salary out number )  is

    --salary number;

begin

  select sal into salary from emp where empno = eno;

  dbms_output.out_line(salar)

end proc1;

in  输入

out 输出

in out  都可以

使用方法

包

包头 package   类似java中的接口  全是方法的声明

包体 packagebody 包头方法的实现

create or replace package pkg1 is

    function fu2(eno number) return varchar2;

    procedure prco2(eno number,empname out varchar2);

end pkg1;  

  create   or   replace   package   body  pkg1  is

--h函数

  function fu2(eno number) return varchar2 is

    empname varchar2(20);

  begin

    select ename into empname from emp where empno=eno;

    return empname;

  end fu2;

  

  --过程

    procedure prco2(eno number,empname out varchar2) is

    begin

      select ename into empname from emp where empno=eno;

    end prco2;

    --包里允许函数和过程的重载  参数个数不同

end pkg1;

包的管理

    刷包

表示year的：y 表示年的最后一位 yy 表示年的最后2位 yyy 表示年的最后3位 yyyy 用4位数表示年

表示month的：mm 用2位数字表示月；mon 用简写形式 比如11月或者nov ；month 用全称 比如11月或者november

表示day的：dd 表示当月第几天；ddd表示当年第几天；dy 当周第几天 简写 比如星期五或者fri；day当周第几天 全写

比如星期五或者friday。

表示hour的：hh 2位数表示小时 12进制； hh24 2位数表示小时 24小时

表示minute的：mi 2位数表示分钟

表示second的：ss 2位数表示秒 60进制

表示季度的：q 一位数 表示季度 （1-4）

另外还有ww 用来表示当年第几周 w用来表示当月第几周。

24小时制下的时间范围：00：00：00-23：59：59

12小时制下的时间范围：1：00：00-12：59：59

两个时间 相减得到的是天数 

得到月份  months_between(data1,data2)

时间直接减去数字   减去是天数

 各部门工资最高

select ename,sal,deptno from emp where (deptno,sal)

in (select deptno,max(sal) from emp group by deptno )  

查询当前的用户的表

select  * from user_tables

工资排第四

select * from ((select * from 

(select * from (select * from emp order by sal desc) 

where rownum<=4) order by sal)) where rownum=1;  

创建序列 

1、创建、删除

create sequence seq_newsId

increment by 1

start with 1

maxvalue 999999999;

得到序列的SQL语句

select seq_newsid.nextval from sys.dual;

删除序列的SQL

DROP SEQUENCE seq_newsId；

查看当前用户有哪些序列： select * from user_sequences