Oracle基础

2、Oracle是通过表空间来存储物理表的
    ⑴一个数据库实例可以有N个表空间
    ⑵每个数据库至少有一个表空间（system表空间）
    ⑶一个表空间只能属于一个数据库
    ⑷一个表空间下可以有N张表。有了数据库，就可以创建表空间
    ⑸每个表空间由一个或者多个文件组成，这些文件就是数据文件
    ⑹一个数据文件只能属于一个表空间

4、空间分为：永久表空间、临时表空间、UNDO表空间（存放被修改之前的数据）
    ⑴永久表空间：存放数据库永久的数据（表，视图等）
    ⑵临时表空间：存放数据库操作中中间执行的过程，执行结束后释放掉
    ⑶undo表空间：存储数据被修改前的数据

用户user

1、用户是在实例下建立的。不同实例中可以建相同名字的用户

2、表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或多个数据文件DBF中

3、oracle是由用户和表空间对数据进行管理和存储的。但是表数据不是由表空间去查询的，而是由用户去查询的。因为不同用户可以在同一个表空间建立相同名字的表

4、Oracle中，一个数据库拥有多个实例，一个实例可以拥有多个用户，而一个用户可以拥有多个表空间（注意表空间多个用户可以交叉使用，但是前提是要有权限！）

tip：
安装完Oracle后，它会自动的生成一些实例、用户和表空间，并在对应的表空间中生成一些自带的表(自动关联用户与表空间)。这些默认的表我们一般是用不到的，因此一般下我们需要自己创建用户、表空间、表来存放我们的数据，不然我们自己的数据(表)与自带的表放一起的话，就不怎么方便了

三，Oracle中基本数据类型

数据类型	参数	描述
char(n)	n =1 to 2000字节	定长字符串，n个字节，缺省为1个字节长（一个汉字为2字节）
varchar2(n)	n=1 to 4000字节	可变字符串，具体定义时指明的最大长度为n
number(m,n)	m=1 to 38 n=84 to 127	可变的数值列，如：number（3，0）输入432.432将会输出423
date	无	输出的格式为YYYY-MM-DD

tip：其他数据库类型基本上同SQLserver一致。

四，命令分类操作

1.DDL（数据定义语言）

代码如下：


CREATE TABLE ;--创建数据库

ALTER TABLE；--更改表格的结构

DROP TABLE；--删除表

CREATE INDEX；--在表上创建索引

DROP INDEX;--删除索引

2.DCL（数据控制语言）

代码如下：

--注！！！只能使用DBA账号进行如下操作：

--1.创建表空间：

CREATE TABLESPACE TABLESPACENAME --定义表空间名字 

DATAFILE 'C://CRACL'--定义表空间存储在磁盘的路径

SIZE 1M --指定表空间初始大小

AUTOEXTEND ON NEXT 32M MAXSIZE --设置表空间自动递增大小的最大值

UNLIMITED --设置无限制

LOGGING 

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;


--2.创建新的用户

CREATE USER USERNAME--新建用户

INENTIFIED BY "aa123"--设置口令

DEFAULT TABLESPACE TABLESPACENAME; --设置默认的表空间


--3.授权用户的登录权利

GRANT CONNECT ,RESOURCE TO USERNAME;

--同理，撤销权利如下：

REVOKE RESOURSE FROM USERNAME;


--4.对用户进行上锁与解锁

ALTER USER USERNAME ACCOUNT LOCK;--上锁

ALTER USER USERNAME ACCOUNT UNLOCK;--解锁


--5.授予某用户对表格操作权限

GRANT SELECT ON TABLENAME TO USERNAME;--查询

GRANT UPDATE ON TABLENAME TO USERNAME;--修改

commit--提交

rollback--回滚事件

3.DML(数据操作语言)

执行顺序：from->where->group by->having->select->order by

--创建表格

CREATE TABLE TABLENAME(

);

--插入数据

INSERT INTO TABLENAME VALUES();

--修改数据

UPDATE TABLENAME SET 修改的列+修改的数据 WHERE + 条件 ;

--删除数据

DELETE TABLE WHERE + 条件 ;

--查询

SELECT * FROM TABLENAME;

目前为止，主要的语法结果如下：

select distinct *|字段列表|表达式|函数 as别名
from 表名
where 过滤条件
group by要分组的字段列表
having过滤组
order by要排序的字段列表 asc|desc

五，PL/SQL

1.基本查询

SELECT * FROM TABLENAME WHERE + 条件;

SELECT 列名，列名 FROM TABLENAME WHERE + 条件; 

SELECT * FROM DUAL;--伪表

SELECT EMPNO,1*2 AS COUNT,'CMJ' AS NAME,DEPTNO FROM EMP;--伪列

2.高级查询

--模糊查询

SELECT * FROM TABLENAME WHERE NAME LIKE = '';

rowid与rownum

rowid它是一个伪列，不会实际存在表中，它是Oracle在读取表中数据行时，根据每行的物理地址编码而成的一个伪列。

rownum也是，它会根据返回记录生成一个序列化的数字，排序后的结果集的序列号，每一个结果集都有自己的序列号

3.pl/sql

-- pl/sql

declare -- 定义

    i number := 0;

begin
    -- 写代码

    -- 字符到数值的转换错误

    DBMS_OUTPUT.PUT_LINE('hello');--相当于Java的system.out.print（）

end;

--示例：

--取出emp表中7369员工的名字，入职日期

declare

    --%type列类型

    --%rowtype行类型

    my_date emp.hiredate%type;-- 和列的类型保持一致

    my_name emp.ename%type;-- 和列的类型保持一致

begin

    select HIREDATE, ename

    into my_date,my_name

    from emp

    where EMPNO = 7369;

    DBMS_OUTPUT.PUT_LINE('名字' || my_name || ',' || '日期' || my_date);

end;

4.三大循环类型

loop死循环
while循环
for循环

代码演示：

-- loop 死循环

declare

    i number := 0;

begin

    loop

        i := i + 1;

        DBMS_OUTPUT.PUT_LINE('我好帅');

        exit when i >= 100;

    end loop;

end;

-- while循环

declare

    i number := 0;

begin

    while i < 100

        loop

            i := i + 1;

            DBMS_OUTPUT.PUT_LINE(i);

        end loop;

end;

-- for循环  类似Java的foreach

begin

    for i in 1..100

        loop

            DBMS_OUTPUT.PUT_LINE(101 - i);

        end loop;--结束循环

end;

循环的使用案例

--使用for求1-100的和

declare 

    a number := 0;

begin

    for i in 1..100

        loop

            a := a + i;

        end loop;

    DBMS_OUTPUT.PUT_LINE('和' || a);

end;

5.函数

函数主要分为系统自带的函数、自定义函数

根据函数的返回结果，将函数分为单行函数（一条记录返回一个结果）、多行函数（多行记录返回一个结果）

日期函数

sysdate/currtent_date	当前时间
sysdate+-time	修改时间
add_months(d,x)	修改月份
last_day(d)	最后一天
months_between(date1,date2)	月份差
next_day(sysdate,星期数)	下个星期

转换函数

to_date	将字符串转换为日期
to_char	将日期以指定格式转换为字符串

其他函数

1.nvl

nvl(string1, string2)
-- 如果string1为null,则结果为string2的值，否则为string1的值

2.decode

decode(condition,case1,express1,case2,express2,... casen,expressn,expressionm)
-- decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

3.case

case [<表达式>]
when <表达式条件值1> then <满足条件时返回值1> 
[when <表达式条件值2> then <满足条件时返回值2> 
......
[else  <不满足上述条件时返回值>]]
end

--当<表达式>＝<表达式条件值1……n>时，返回对应<满足条件时返回值1……n>
--当<表达式条件值1……n>不为条件表达式时，与函数decode()相同，

代码展示

--创建一个函数：

-- 接收一个员工编号，

-- 返回工资'交税'还是'刚好'，还是'努力'

create or replace function test04(no number)

    return varchar2

as

    my_sal number;

    result varchar2(20);

begin

    select sal into my_sal from emp where EMPNO = no;

    my_sal := sign(my_sal - 3500);

    case my_sal

        when 1 then result := '胶水';

        when 0 then result := '刚好';

        else result := '女里';

        end case;

    return result;

end;

6.异常的抓捕


--异常的抓捕

--too_many_rows数据过多

--no_data_found数据未找到

declare

    my_date emp.hiredate%type;-- 和列的类型保持一致

begin

    select HIREDATE

    into my_date

    from emp

    where EMPNO = 10000;

exception

    when too_many_rows then DBMS_OUTPUT.PUT_LINE('行太多了，放不下啦');

    when no_data_found then DBMS_OUTPUT.PUT_LINE('已失踪');

end;

7.游标

-- 使用弱类型游标查看emp,dept表的数据

declare

    type myc is ref cursor;--弱类型就是说  这个游标可以为多表服务

    myt myc;

    mye emp%rowtype;

    myd dept%rowtype;

begin

    open myt for select * from emp;--手动指定了sql语句（动态游标）

        loop

            fetch myt into mye;

            exit when myt%notfound;

            DBMS_OUTPUT.PUT_LINE(mye.ENAME);

        end loop;

    close myt;

    open myt for select * from dept;--手动指定了sql语句（动态游标）

        loop

            fetch myt into myd;

            exit when myt%notfound;

            DBMS_OUTPUT.PUT_LINE(myd.DNAME);

        end loop;

    close myt;

end;

8.触发器的使用

什么时候调用触发器：

数据发生改变的时候(增删改),系统会自己调用

触发器按类型：

行级触发器: for each row(每一行都会触发)
语句级触发器:不写for each row(执行一次)

触发器触发的时候会建立两个临时表：new old

增：new
                     删：old
                     改：old->new

触发器顺序

before ：执行SQL语句前触发
after ：执行SQL语句后才会后触发

代码演示



--要求李斯文不能删除
create or replace trigger tgr_01

after delete on stuinfo --定义触发的时间与绑定的表

for each row --行级触发器

  begin

    if :old.stuname='李斯文' then

      raise_application_error(-20001,'不能删除该学生'); --抛出异常

    end if;

  end;

--测试

delete stuinfo where stuname='李斯文'

--要求插入的时候，特殊(马云)名字不能插入到表中

create or replace trigger tgr_02

after insert on stuinfo

for each row

  begin

         if :new.stuname='马云' then

           raise_application_error(-20001,'不能增加!!'); --抛出异常

         end if;

  end;

--测试

insert into stuInfo(stuNo, stuName, stuSex, stuAge,stuSeat,strAddress)

select 's25389', '马云1', '男', 18,1, '北京海淀' from dual

--要求梅超风不能修改

create or replace trigger tgr_03

after update on stuinfo

for each row

  begin

    if :old.stuname='梅超风' then

           raise_application_error(-20001,'不能修改!!'); --抛出异常

    end if;

  end;

--测试

update stuinfo set stuname='梅鹏中毒' where stuname='马云1'

--合并的触发器(增，删，改)

create or replace trigger tgr_04

after update or insert or delete on stuinfo

for each row

  begin

    --判断当前的操作是什么

    case 

      when inserting then --如果当前是增加操作

            if :new.stuname='马云' then

               raise_application_error(-20001,'不能增加!!'); --抛出异常

            end if;

      when deleting then --如果当前是删除操作

            if :old.stuname='李斯文' then

               raise_application_error(-20001,'不能删除该学生'); --抛出异常

            end if;

      when updating then --如果当前是修改操作

           if :old.stuname='梅超风' then

              raise_application_error(-20001,'不能修改!!'); --抛出异常

           end if;

    end case;

  end;

--级联触发器:当删除学生信息的时候，自动删除其成绩(张秋丽)

create or replace trigger tgr_05

before delete on stuinfo --在删除学生之前我先删除成绩

for each row

  begin

    --删除成绩表的数据

    delete from stumarks where stuno=:old.stuno;

  end;

--测试

delete stuinfo where stuname='张秋丽'


--创建序列

create sequence seq_401

-- nextval 下一个值

--currval 当前的值

create table tb_401(

       sid number primary key,

       sname varchar2(20)
)
--触发器与序列的使用

create or replace trigger tb_401_insert

before insert on tb_401

for each row

  begin

    --修改新增的数据

    --新增数据里面的sid改成序列的值

    -- :new.sid:=seq_401.nextval;                       oracle11的

    select seq_401.nextval into :new.sid from dual;-- oracle10的

  end;

--测试

insert into tb_401(sname) values('马云');

select * from bk_401

--7.制作删除数据备份(tb_401)

create table bk_401

as 

select * from tb_401 where 1=2

--删除tb_401的数据时,数据会插入到备份表

create or replace trigger tb_401_delete

before DELETE on tb_401

for each row

  begin

        --将被删除的数据(old)放到备份表

        insert into bk_401 values(:old.sid,:old.sname);

  end;
  
select * from bk_401

delete tb_401 where sid>10

以上就是Oracle的基础内容，部分与sqlserver重复的内容可能没有补充了，欢迎大家补充！

一麟yl

关注

3
点赞
踩
29

收藏

觉得还不错? 一键收藏
打赏
0
评论
Oracle基础

一，Oracle是什么？Oracle简而言之是一门数据语言。二，数据库的基本概念：数据库（Database，DB）数据库管理系统（Database Management System,DBMS）数据库系统（Database System,DBS）数据库管理员（Database Administrator,DBA）三，Oracle中得到基本数据类型：数据类型参数描述 char(n) n =1 to 2000字节定长字符...
复制链接

扫一扫