Oracle 数据库基础

标签：name stu -- 数据库 基础 索引 Oracle id

Oracle 数据库基础

DUAL表

dual是Oracle提供的最小的工作表，只有一行一列，具有某些特殊功用。

不论进行何种操作（不要删除记录），它都只有一条记录——'X'。

select * from dual;

用户

用户：对数据库的访问，需要以适当用户身份通过验证，并具有相关权限来完成一系列动作 。

SYS用户：缺省始终创建，且未被锁定，拥有数据字典及其关联的所有对象

SYSTEM用户：缺省始终创建，且未被锁定，可以访问数据库内的所有对象

模式(schema)：是某个用户拥有所有对象的集合。具有创建对象权限并创建了对象的用户称为拥有某个模式。

对象

TABLE(表)

常见表主要有分区表、索引组织表 、堆表三种表类型

• 分区表以单独的物理结构（分区）存储行，依据列的值分配行。

• 索引组织的表将索引和表的数据存储在一起。普通表的数据以无序（Heap）的方式存放在数据库中。而索引组织表按照主键进行排序，以二叉树的形式对表的数据进行存储。

• 堆表是我们在Oracle中最常使用的数据表，也是Oracle的默认数据表存储结构

  1、建表

  create table 表名(

  　　列名 数据类型,

  　　……

  );

  2、删除表：drop table 表名;

  3、添加列：alter table 表名 add(列名 数据类型);

  4、修改列：alter table 表名 rename column 原列名 to 列名;

  5、修改数据类型：alter table 表名 modify 列名 数据类型;

  6、删除列：alter table 表名 drop column 列名;

  7、添加注释

  　　添加表注释：comment on table 表名 is '表注释;

  　　添加字段注释：comment on column 表名.列名 is '列注释';

  8、添加约束

  　　添加主键约束：alter table 表名 primary key(列名);

  　　添加唯一约束：alter table 表名 constraint 约束名 unique(列名);

  　　（主键约束和唯一约束的区别:主键约束：唯一标识，不能为空。唯一约束：唯一标识，只能有一个值为空）

  　　非空约束：alter table 表名 modify(列名 constraints);

  9、插入数据：insert into(列名,……)values(数据,……)；

  　　注意，oracle中不能直接写入日期函数

  　　插入时间：to_date('2018-1-4 15:53:34','YYYY-MM-DD HH24:MI:SS')

  　　插入当前时间：sysdate

  --student表
  create table student(
  　　stu_id varchar2(10) primary key,
  　　stu_name varchar2(10) not null,
  　　stu_sex varchar2(3) not null,
  　　stu_birthday date,
  　　class_id number
  );
  --添加表注释
  comment on table student is '学生信息表';
  --字段添加注释
  comment on column student.stu_id is '学号(主键)';
  comment on column student.stu_name is '学生姓名';
  comment on column student.stu_sex is '学生性别';
  comment on column student.stu_birthday is '学生出生年月';
  comment on column student.class_id is '学生所在班级';
  
  --sclass表
  create table sclass(
  　　class_id number primary key,
  　　class_name varchar2(10) not null
  );
  comment on table sclass is '班级信息表';
  comment on column sclass.class_id is '班级编号';
  comment on column sclass.class_name is '班级名称';
  
  --添加外键
  alter table student add constraint fk_class_id foreign key(class_id) references sclass(class_id);
  
  --添加数据
  insert into sclass(class_id, class_name)values(1,'计应1401');
  insert into sclass(class_id, class_name)values(2,'计网1401');
  insert into sclass(class_id, class_name)values(3,'软件1401');
  insert into student(stu_id, stu_name, stu_sex, stu_birthday, class_id)values('A001','张珊','女',to_date('1995-10-02','yyyy-mm-dd'),1) ;
  insert into student(stu_id, stu_name, stu_sex, stu_birthday, class_id)values('A002','李思','女',to_date('1995-10-02','yyyy-mm-dd'),1) ;
  insert into student(stu_id, stu_name, stu_sex, stu_birthday, class_id)values('A003','王武','女',to_date('1996-10-02','yyyy-mm-dd'),2) ;
  insert into student(stu_id, stu_name, stu_sex, stu_birthday, class_id)values('A004','赵柳','女',to_date('1996-12-02','yyyy-mm-dd'),3) ;
  insert into student(stu_id, stu_name, stu_sex, stu_birthday, class_id)values('A005','赵柳','女',sysdate,3) ;
  

VIEW（视图）

视图既不分配存储空间，也不包含数据。而是通过定义一个查询，从它所引用的基表中提取或派生出数据。视图基于其他对象，除了只需要在数据字典中存储定义视图的查询，它不需要其他存储

视图其实是一个虚拟的表，它的数据其实来自于表。如果更改了视图的数据，表的数据也自然会变化，更改了表的数据，视图也自然会变化。一个视图所存储的并不是数据，而是一条SQL语句

创建或修改视图语法

CREATE [OR REPLACE] [FORCE] VIEW view_name 
AS subquery 
[WITH CHECK OPTION ] 
[WITH READ ONLY]

选项解释：

OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图;

FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图;

subquery ：一条完整的 SELECT 语句，可以在该语句中定义别名;

WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束;

WITH READ ONLY ：该视图上不能进行任何 DML 操作。

删除视图语法

DROP VIEW view_name

SEQUENCES（序列）

序列是生成唯一整数值的结构。序列创建之后，可以通过序列队形的currval和nextval两个"伪列"，分别访问该序列的当前值和下一个值，currval必须在nextval调用之后才能使用

定义序列

CREATE SEQUENCE <序列名>
	[MAXVALUE N|NOMAXVALUE]
	[MINVALUE N|NOMINVALUE]
[START WITH N] 
[INCREMENT BY N]
[CACHE N|NOCACHE]
[CYCLE|NOCYCLE];

参数说明：

INCREMENT BY：定义序列的步长，N如果为正值，表示序列是一个递增序列；N如果为负值，表示序列是一个递减序列；如果省略，则默认值为1。
START WITH：定义序列的起始值，如果省略，则默认值为1。
MAXVALUE：定义序列生成器能产生的最大值。选项 NOMAXVALUE 是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方；对于递减序列，最大值是-1。
MINVALUE：定义序列生成器能产生的最小值。选项 NOMAXVALUE 是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是-10的26次方；对于递增序列，最小值是1。
CYCLE | NOCYCLE：表示当序列生成器的值达到限制值后是否循环。CYCLE 代表循环，NOCYCLE 代表不循环。
CACHE：定义存放序列的内存块的大小，默认为20。NOCACHE 表示不对序列进行内存缓冲。

创建触发器

CREATE[OR REPLACE] TRIGGER <触发器名称>
	BEFORE INSERT  -- 触发条件：当向表 XXX 执行事件 insert or update or delete操作时间 after/before 触发此触发器
	ON <表名>      -- 作用的表 on tablename
	FOR EACH ROW  -- 指定是否对受影响的每行都执行触发器，即行级触发器，如果不使用此子句，则为语句级触发器
-- 触发器开始
BEGIN
	-- 触发器主体内容，在此是取得 <序列名> 的下一个值插入到表 XXXXX 中的 <主键> 字段中
	SELECT <序列名>.NEXTVAL INTO :NEW.<主键> FROM DUAL;
END;


注释：
before和after：指在事件发生之前或之后激活触发器。
instead of：如果使用此子句，表示可以执行触发器代码来代替导致触发器调用的事件。
insert、delete和update：指定构成触发器事件的数据操纵类型，update还可以制定列的列表。
referencing：指定新行（即将更新）和旧行（更新前）的其他名称，默认为new和old。
触发器名称：指要创建触发器的表或视图的名称。
for each row：指定是否对受影响的每行都执行触发器，即行级触发器，如果不使用此子句，则为语句级触发器。
when：限制执行触发器的条件，该条件可以包括新旧数据值得检查。
declare---end：是一个标准的PL/SQL块。

declare              -- trigger 的主题
begin
insert into student_state(SSID,Ssstate) values(:NEW.SID,:NEW.SID);
end;

获取上一个插入数据的id

-- 创建表
CREATE TABLE "STUDENT"
(
    "S_ID"        NUMBER (20,0) NOT NULL ENABLE,
    "S_NO"        NUMBER (20,0) DEFAULT '',
    "S_NAME"      VARCHAR2 (255) DEFAULT '',
    "S_SEX"       CHAR(1) DEFAULT '0',
    "S_BIRTHDAY"  DATE,
    "S_CLASS"     NUMBER (20,0) DEFAULT '',
    "REMARK"      VARCHAR2(300) DEFAULT '',
    "DEL_FLAG"    CHAR(1) DEFAULT '0',
    "CREATE_BY"   VARCHAR2 (64) DEFAULT '',
    "CREATE_TIME" DATE,
    "UPDATE_BY"   VARCHAR2 (64) DEFAULT '',
    "UPDATE_TIME" DATE
);

COMMENT ON TABLE STUDENT IS '学生表';

ALTER TABLE STUDENT
    ADD CONSTRAINT PK_S_NO PRIMARY KEY (S_NO);

COMMENT ON COLUMN STUDENT.S_ID IS 'ID';
COMMENT ON COLUMN STUDENT.S_NO IS '学号';
COMMENT ON COLUMN STUDENT.S_NAME IS '学生姓名';
COMMENT ON COLUMN STUDENT.S_SEX IS '学生性别';
COMMENT ON COLUMN STUDENT.S_BIRTHDAY IS '学生出生年月';
COMMENT ON COLUMN STUDENT.S_CLASS IS '学生所在班级';
COMMENT ON COLUMN STUDENT.REMARK IS '备注';
COMMENT ON COLUMN STUDENT.DEL_FLAG IS '删除标志（0代表存在 2代表删除）';
COMMENT ON COLUMN STUDENT.CREATE_BY IS '创建者';
COMMENT ON COLUMN STUDENT.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN STUDENT.UPDATE_BY IS '更新者';
COMMENT ON COLUMN STUDENT.UPDATE_TIME IS '更新时间';

-- 创建序列
CREATE SEQUENCE SEQ_STUDENT_S_ID
    MINVALUE 1
    MAXVALUE 9999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

-- 创建触发器
CREATE TRIGGER TRG_STUDENT_S_ID
    BEFORE INSERT ON STUDENT
    FOR EACH ROW
BEGIN
    SELECT SEQ_STUDENT_S_ID.NEXTVAL INTO :NEW.S_ID FROM DUAL;
END;

-- 获取上一个插入数据的id 

DECLARE  NEW_ID NUMBER(20);
begin
insert into STUDENT (S_NO, S_NAME) values(009, '张XXX') RETURNING S_ID INTO NEW_ID;
COMMIT;
insert into SCLASS(CLASS_NAME) values(NEW_ID);
end;

索引

索引相当于一本书的目录，能过提供检索的速度，如果某一列需要经常作为查询条件，则有必要为其创建索引，能显著提供效率。Oracle数据库会为表的主键和包含唯一约束的列自动创建索引。

b-tree索引

Oracle数据库中最常见的索引类型是b-tree索引，也就是B-树索引，以其同名的计算科学结构命名。CREATE
INDEX语句时，默认就是在创建b-tree索引。没有特别规定可用于任何情况。

创建索引

create index on () [tablespace];

简单索引：create index BOOK_INFO_INDEX_1 on BOOK_INFO(ISBN);

复合索引：create index BOOK_INFO_INDEX_1 on BOOK_INFO(COLUMN1,COLUMN2,COLUMN3)；
注意：只有当column1字段作为查询条件之一时，该索引才会有效。与三者之间的顺序没有关系

并不是建立索引后，每次查询都会生效，一般情况下，只有当查询结果小于总数量的10%左右时，索引才会有效，否则是全表扫描

重置索引

alter index rebuild;

删除索引

drop index ;

强制使用索引

select /+INDEX(t )/ t.* from where t.column_name='';

如：
select /+INDEX(t BOOK_INFO_INDEX_1 )/ t.* from BOOK_INFO t where t.ISBN='201902';

通过查看sql执行计划，了解sql语句是否启用索引

方法一：在plsql中新建Command窗口，按下面的步骤执行

• 生成执行计划

explain plan for select * from BOOK_INFO where ISBN='123456'；
备注：explain plan for后面为要生成执行计划的查询语句

• 查看执行计划结果

select * from table(dbms_xplan.display);

• 查看索引是否使用

index range scan为索引范围扫描，TABLE ACCESS FULL为全表扫描

方法二：在plsql中新建SQL窗口，在窗口中写入查询语句，然后按F5即可；

查看表索引信息

select * from user_indexes where table_name='BOOK_INFO' ;

select * from user_ind_columns where table_name ='BOOK_INFO' ;

查看表索引是否启用

• 开启索引监控 alter index monitoring usage;

alter index BOOK_INFO_INDEX_1 monitoring usage;

• 查看

select * from v$object_usage;

• 关闭 alter index nomonitoring usage;

alter index BOOK_INFO_INDEX_1 nomonitoring usage;

位图索引(bitmap index)

位图索引特定于该列只有几个枚举值的情况，比如性别字段，标示字段比如只有0和1的情况。

基于函数的索引

比如经常对某个字段做查询的时候是带函数操作的，那么此时建一个函数索引就有价值了。

分区索引和全局索引

这2个是用于分区表的时候。前者是分区内索引，后者是全表索引

反向索引（REVERSE）

这个索引不常见，但是特定情况特别有效，比如一个varchar(5)位字段(员工编号)含值
（10001,10002,10033,10005,10016..）
这种情况默认索引分布过于密集，不能利用好服务器的并行
但是反向之后10001,20001,33001,50001,61001就有了一个很好的分布，能高效的利用好并行运算。

HASH索引

HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。使用HASH索引必须要使用HASH集群。

函数

函数是作为数据库对象存储在oracle数据库中，函数又被称为PL/SQL子程序。oracle处理使用系统提供的函数之外，用户还可以自己定义函数。函数通常被作为一个表达式来调用或存储过程的一个参数，具有返回值。通常用于返回特定的数据。

概述

函数与存储过程的不同点

• 函数就是一个有返回值的过程，且是必须。

• 存储过程只能作为一个plsql语句调用，而函数不但可以作为plsql语句调用，符合约束的函数还可以作为sql表达式的一部分使用。

• 对于无参函数的定义和调用都没有圆括号，但无参存储过程需要。

函数与存储过程的相同点

• 都存储在数据库中，并且可在块中调用，代码都有定义部分、可执行部分、异常处理部分。

• 都有in,out,in out三种参数，都可以使用缺省值，都可以通过out模式返回一个或多个值。 （

• 都可以使用位置表示法和名称表示法。

函数参数的传递方式

无参函数

create or replace function fun_dtime return varchar2
as
begin
return to_char(sysdate,'yyyy"年"mm"月"dd"日"');
end;

调用
begin
dbms_output.put_line(fun_dtime);
end;

带输入输出参数的函数

create or replace function fun_info(i_eno number,o_title out varchar2,salch in out number) return varchar2
as
name emp.ename%type;
begin
select ename into name from emp where empno=i_eno;
update emp set sal=sal+salch where empno=i_eno returning job,sal into o_title,salch;
return name;
end;

调用
declare
v_eno number:=7369;
vn emp.ename%type;
vj emp.job%type;
vs emp.sal%type;
begin
vs:=100;
vn:=fun_info(v_eno,vj,vs);
dbms_output.put_line('姓名' || vn || '岗位' || vj || '新工资' || vs);
end;

3.删除函数

drop function fun_info;

存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程

存储过程创建语法：
       create or replace procedure 存储过程名（param1 in type，param2 out type）
as
变量1 类型（值范围）;
变量2 类型（值范围）;
Begin
    Select count(*) into 变量1 from 表A where列名=param1；
    If (判断条件) then
       Select 列名 into 变量2 from 表A where列名=param1；
       Dbms_output。Put_line(‘打印信息’);
    Elsif (判断条件) then
       Dbms_output。Put_line(‘打印信息’);
    Else
       Raise 异常名（NO_DATA_FOUND）;
    End if;
Exception
    When others then
       Rollback;
End;
 
注意事项：
1、存储过程参数不带取值范围，in表示传入，out表示输出。
2、变量带取值范围，后面接分号。
3、在判断语句前最好先用count(1)函数判断是否存在该条操作记录。
4、用select...into...给变量赋值。
5、在代码中抛异常用 raise+异常名。

SYNONYM（同义词）

同义词是方案对象的别名,它不占储存的空间,目的是在Oracle中为表或者视图、序列、PL/SQL程序单元、用户自定义对象或其他的同义词创建友好的名称。

创建同义词

CREATE [OR REPLACE] [PUBLIC] SYSNONYM [schema.]synonym_name

FOR [schema.]object_name

语法解析：
① CREATE [OR REPLACE：]表示在创建同义词时，如果该同义词已经存在，那么就用新创建的同义词代替旧同义词。
② PULBIC：创建公有同义词时使用的关键字，一般情况下不需要创建公有同义词。
③ Oracle中一个用户可以创建表、视图等多种数据库对象，一个用户和该用户下的所有数据库对象的集合称为Schema(中文称为模式或者方案)，用户名就是Schema名。一个数据库对象的全称是：用户名.对象名，即schema.object_name。
如果一个用户有权限访问其他用户对象时，就可以使用全称来访问

删除同义词

DROP [PUBLIC] SYNONYM [schema.]sysnonym_name

语法解析：
① PUBLIC：删除公共同义词。
② 同义词的删除只能被拥有同义词对象的用户或者管理员删除。
③ 此命令只能删除同义词，不能删除同义词下的源对象。
注：如果在项目中要重复建表和建同义词，最好先删除同义词，然后再删除表结构；否则oracle执行脚本时会报错；

DATABASE LINK（数据链路）

当需要跨越本地数据库，访问远程数据库的数据时，oracle提供了dblink的方式，让我们可以很方便访问远程数据库像本地一样方便。

授权

在创建DB link之前，我们需要判断，登陆的用户是否具备创建DB link 的权限，所以我们执行以下的语句(用test用户登陆orcl)：

源数据库A用户名：user1;密码：123；目标数据库B用户名：user2;密码：1234
-- 查看用户user1是否有dblink的权限
select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='user1';
 如果查询有返回行，则表示具备创建database link权限，否则，则需要使用sys登陆orcl为test用户赋予创建权限
-- 给user1用户授予创建dblink的权限
grant create public database link to user1;

创建DBLINK

建立从源数据库A到目标数据库B的dblink
-- 注意一点，如果密码是数字开头，用“”括起来
-- dblink_public代表database link的名称
create public database link dblink_public connect to user2 identified by "1234" using 'IP:端口/service_name';

数据传输

--如果想将源数据库A中的表插入到目标数据库B中，需要在目标数据库B中建立数据表。
--在目标数据库B中使用如下语句建表：
create table user2.test(
global_index VARCHAR2(128), 
chronic_diagnosis_time TIMESTAMP(6),
chronic_label int,
);
--在源数据库A中使用如下语句。将源数据A中的数据插入到目标数据库B中：
insert into user2.test@dblink_public
select * 
from user1.test_old;
commit;#提交插入语句。

--如果想将目标数据库B中的表插入到目标数据库B中，需要在源数据库A中建立数据表。
--在源数据A中使用如下语句建表：
create table user1.test_1(
global_index VARCHAR2(128), 
chronic_diagnosis_time TIMESTAMP(6),
chronic_label int,
);
--在源数据库A中使用如下语句。将目标数据库B中数据插入到源数据库A：
insert into user1.test_1
select * 
from user2.test_2@dblink_public;
commit;#提交插入语句。

标签：name,stu,--,数据库,基础,索引,Oracle,id
From： https://www.cnblogs.com/fuqian/p/17277879.html