Oracle 学习笔记

Oracle 数据库基础

【最常用：】

数据字典：

Oracle存储所有的实例信息的表和视图的集合。Oracle 进程会在SYS模式中维护着些表和视图，也就是说数据字典的所有者为SYS用户，数据存放在SYSTEM表空间中。数据字典描述了实际数据是如何组织的。如一个创建者的信息。创建时间】所属表空间信息、用户访问权限信息等，对他们可以向处理其他数据库一样进行查询但不能进行任何修改。

Oracle 数据字典通常在创建和安装数据库是被创建的。Oracle 数据字典是Oracle数据库系统的工作基础，没有数据字典的支持，Oracle将不能进行任何工作。

数据字典：

数据字典中的表是不可以直接被访问，表中的数据是Oracle 系统存放的系统数据。二普通表存放的是用户数据，为了区分这些表，这些表的名字都是用”$”结尾，这些表属于SYS用户。

静态数据字典中的视图分为三类：

User_* 、all_*、dba_*

User_*:该视图存储了关于当前用户所拥有的对象的信息（即所有在该模式下的对象）

All_*:视图存储了当前用户所能够访问的对象的信息，而不是当前用户拥有的对象（与user_* 相比all_*并不需要拥有该对象，只需要拥有访问的权限即可。）

Dba_* :

该视图存储了数据库中所有对象的信息（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限）

示例：

1.User_tables: 主要描述当前用户拥有的所有表的信息，主要包括表名、表空间名。通过此图可以清楚地

SQL>Select * from user_tables;

2.user_indexes: 查询用户拥有哪些索引。

Select index_name from user_indexes;

3.user_procedures:查看存储过程

SELECT * FROM user_procedures;

4.user_sequence:查看序列

SELECT * FROM User_Sequences;

5.user_synonyms:查看同义词

SELECT * FROM User_Synonyms;

6.user_all_tables：查看该用户的所有表信息

SELECT * FROM User_All_Tables;

7.user_views:查看用户视图。

SELECT * FROM User_Views;

8.user_objects:查看所有对象‘

SELECT * FROM User_Objects;

9.user_catalog:查看所有catalog日志信息。

SELECT * FROM User_Catalog;

ALL_* :实例：

SYS模式下（用户）

查看该模式下的所有的表：

SELECT * FROM All_Tables;

查看该模式下的所有的用户：

SELECT * FROM All_Users;

查看该模式下的权限：

SELECT * FROM All_Tab_Privs;

【Oracle数据库简介】

Oracle 数据库是关系型数据库，安全性较高，可为大型数据库提供更好的支持

【Oracle 数据库的主要特点】

支持多用户、大事物量的事物处理。
在保持数据安全性和完整性方面性能优越。
支持分布式数据处理。将分布在不同物理位置的数据库用通信网络连接起来，在分布式数据库管理系统的控制下，组成一个逻辑上统一的数据库，完成数据处理任务。
具有可以移植性。Oracle 可以在Windows、Linux等多个操作系统平台上使用，而SqlServer只能在Windows 平台上运行。

l 【Oracle 基本概念】

全局数据库名：用于区分一个数据库的标识，在数据库的安装、创建新的数据库、创建控制文件、修改数据库结构、利用RMAN备份时都需要使用，它有数据库名称和域名构成，类似网络中的域名，使数据库的命名在整个网络中唯一。
数据库实例：每个启用的数据库都对应一个数据库实例，由这个实例来访问数据库中的数据。如果把数据库简单理解为硬盘上的文件，具有永久性，则数据库实例就是通过内存共享运行状态的一组服务器后台进程。
表空间：每个Oracle数据库都是由若干的表空间构成的，用户在数据库中建立的所有内容都被存储到表空间中。一个空间可以由多个数据文件组成。但一个文件只能属于一个表空间。与数据文件这种物理结构相比，表空间属于逻辑结构。
数据文件:通常是数据文件的扩展名是.dbf,是用于存储数据库的文件，如存储数据库表中的记录、索引、存储过程、视图、数据字典定义等。一个数据文件
控制文件：通常控制文件的扩展名是.ctl，是一个二进制文件。控制文件中存储的信息很多，其中包括数据文件和日志文件的名称和位置，一个数据库至少要一个以上的控制文件，个个控制文件内容相同，可以避免因为一个控制文件的损坏而无法启动数据库。
日志文件：通常日志文件的扩张名是.log，它记录了数据库的所有更改信息，并提供了一种数据恢复的机制，确保在系统崩溃时重新恢复数据。
模式和模式对象：模式是数据库对象（如表、索引等、也称为对象的集合）。Oracle会为每一个数据库用户创建一个模式，此模式为当前用户所拥有，和用户具有相同的名称。

【Windows下启动数据库】

【启动Oracle步骤：注意需要按照顺序】

开启OracleOraDb11g_home1TNSListener （监听服务），
开启OracleServiceSID服务

【Oracle常用的3个服务】

i. OracleServiceSID 此服务对应名为SID（系统标识符）的数据库实例创建的，其中SID是在安装Oracle 11g 时输入的数据库名称，该服务是默认的:如果此服没有启动，数据库客户端应用程序如SQL*Plus 连接数据库服务器时就会报错。

OracleOraDb11g_home1TNSListener(监听程序) ：要远程连接数据库服务时，客户端必须先连接驻留在数据库服务器上的监听进程。监听器接收客户端发出的请求，然后将请求传递给数据库服务，一旦建立连接客户端能和服务端直接建立通信，该服务只有在数据库需要远程服务是才需要。
OracleDBConsoleSID服务是数据库控制台服务，EMC(企业管理控制台)的服务程序（SID）随安装的数据库而不同，是采用浏览器打开的，用于使用Oracle企业管理器的程序。

【配置数据库】

在Oracle服务器端配置监听器（LISTENER）。
在客户端需要配置一个本地网络服务名（TNSNAME）。
Oracle 客户端与否服务器的连接就是采用本地网络服务名，另外还有Oracle名字服务器（Oracle Names Server）等

【Oracle数据类型】

字符数据类型：

CHAR: 可以存储字母数字值，这种数据类型的列长度可以是1到2000个字节。如果未指明，则默认其占用一个字节，如果用户输入的值小于指定的长度，数据库则用空格填充至固定长度。
VARCHAR2: 其实就是VARCHAR，只不过后面多了一个数字2，VARCHAR2就是VARCHAR的同义词，也称别名。数据类型大小在1至4000个字节，但是和 CHAR不同的一点是：当你定义了VARCHAR2长度为30，但是你只输入了10个字符，这时VARCHAR2不会像CHAR一样填充，在数据库中只有 10具字节。
NCHAR:即国家字符集，使用方法和CHAR相同，如果开发的项目需要国际化。那么数据类型选择NCHAR数据类型[eg:我们定义CHAR(1) 和NCHAR(1)类型的的两个字段字段长度为1个字节和一个字符（2个字节）分别插入‘a’ 是没有问题的，但是占用的字节数分别是1和2，如果分别插入的是‘的’，前者无法插入，而后者可以插入]。

2.数值数据类型：

1.NUMBER(P,S) 1>.NUMBER类型细讲：
Oracle number datatype 语法：NUMBER[(PRecision [, scale])]
简称：precision --> p
scale --> s

NUMBER(p, s)
范围： 1 <= p <=38, -84 <= s <= 127
保存数据范围：-1.0e-130 <= number value < 1.0e+126
保存在机器内部的范围： 1 ~ 22 bytes

有效为：从左边第一个不为0的数算起的位数。
s的情况：
s > 0
   精确到小数点右边s位，并四舍五入。然后检验有效位是否 <= p。
s < 0
   精确到小数点左边s位，并四舍五入。然后检验有效位是否 <= p + |s|。
s = 0
   此时NUMBER表示整数。

eg:
Actual Data   Specified As Stored As
----------------------------------------
123.89           NUMBER         123.89
123.89           NUMBER(3)     124
123.89           NUMBER(6,2)   123.89
123.89           NUMBER(6,1)   123.9
123.89           NUMBER(4,2)   exceeds precision (有效位为5, 5 > 4)
123.89           NUMBER(6,-2) 100
.01234           NUMBER(4,5)   .01234 (有效位为4)
.00012           NUMBER(4,5)   .00012
.000127       NUMBER(4,5)   .00013
.0000012      NUMBER(2,7)   .0000012
.00000123     NUMBER(2,7)   .0000012
1.2e-4           NUMBER(2,5)   0.00012
1.2e-5           NUMBER(2,5)   0.00001
123.2564      NUMBER        123.2564
1234.9876     NUMBER(6,2)   1234.99
12345.12345   NUMBER(6,2)   Error (有效位为5+2 > 6)
1234.9876     NUMBER(6)     1235 (s没有表示s=0)
12345.345     NUMBER(5,-2) 12300
1234567       NUMBER(5,-2) 1234600
12345678      NUMBER(5,-2) Error (有效位为8 > 7)
123456789     NUMBER(5,-4) 123460000
1234567890    NUMBER(5,-4) Error (有效位为10 > 9)
12345.58      NUMBER(*, 1) 12345.6
0.1           NUMBER(4,5)   Error (0.10000, 有效位为5 > 4)
0.01234567    NUMBER(4,5)   0.01235
0.09999       NUMBER(4,5)   0.09999

3.日期时间数据类型

一．DATE: Oracle使用固定7字节固定长度，每个字节分别存储纪、年、月、日、小时、分、和秒。日期时间数据类型的值为公元前4712年1月1日到公元9999年12月31日。Oracle中SYSDATE函数的功能是返回当前的日期和时间。

二．TIMESTAMP:用于存储日期的年月日及时间的小时、分、秒，其中精确到小数后6位。

4.LOB数据类型：

1.CLOB（Character LOB）:主要用于存储非结构化的XML文档，如新闻、内容介绍等含大量的文字内容的文档。

2.BLOB (Binary LOB：二进制文件): 可以存储较大的二进制对象，如图形、视频剪辑和声音剪辑等。

3.BFILE（Binary file）：二进制文件能够将二进制文件存储在数据库外部的操作系统文件中。BFile列存储一个BFILE定位器。指向位于服务器文件系统上的二进制文件。支持文件最大4GB.

4.NCLOB：用于储存大的NCHAR字符数据使用方法与CHAR相同。

【ROWID,ROWNUM】

解释：伪列就像Oracle中的一个表列，但实际上它并未储存在表中。伪列可以从表中查询但是不能插入、更新或是删除它们的兼职键值。

ROWID ：数据库中的每一行都有一行地址。ROWID伪列返回该行地址。可以使用ROWID值来定位表中的一行。通常情况下，ROWID值可以唯一地标识数据库中的一行。

主要用途：

能以最快的方式访问表中的一行。

能显示表的行是如何储存的。

可以作为表中的唯一标识。

SQL> SELECT ROWID,ENAME FROM SCOTT.emp;

结果：

ROWNUM：对于一个查询返回的一行，ROWNUM 伪列返回一个数值代表的次序。返回的第一行的ROWNUM值为1 ，返回的第二行的ROWNUM 的值为2，以此类推，通过使用ROWNUM伪列用户可以限制查询返回的行数。

实例 1：使用ROWNUM从表emp中提取10 条记录并显示序号。

SELECT e.*,ROWNUM FROM emp e where ROWNUM<11;

注意：

ROWNUM对于等于某值的查询条件

如果希望找到员工表中第一条员工的信息，可以使用 WHERE ROWNUM=1;作为条件。但是想找到大于1 的具体值时 eg: where ROWNUM=3;(4,5,6,….)都没用。

ROWNUM对于大于某值的查询条件

使用ROWNUM>n（n属于大于1的自然数）查不到数据因为：ROWNUM是一个总是从1 开始的伪列，Oracle认为这种条件不成立。

Eg: rownum>3 假设该条件能查出2条记录那么存在rownum=1,rownum=2但是没有大于或等于3的记录所以这种条件无效。

【SQL语言简介】

数据定义语言（DDL: data description language）:CREATE (创建)、ALTER(更改)、TRUNCATE(截断) 和DROP（删除）。
数据操纵语言(DML：data Manager language): INSERT(插入)、SELECT(选择)、DELETE(删除)、和UPDATE(更新)。
事务控制语言：(TCL: Tool Command Language):COMMIT(提交)、SAVEPOINT(保存点)和ROLLBACK(回滚)。
数据控制语言(DCL: data console language) :GRANT(授予)、REVOKE(回收)命令。

语法：

CREATE TABLE [schema.]table_name(

Column1 datatype,

……

)[TABLESPACE tablespace_name];

TRUNCATE TABLE <TABLE_NAME>;

DELECT TABLE <TABLE_NAME> WHERE ……;

Truncate 执行时，不记录日志所以处理速度很快。但不能恢复删掉的数据

DELETE 执行时记录日志所以较慢。

【DISTINCT】 :（不重复）自居筛除结果集中内容全部相同的行，仅保留一行。

--根据RowID 删除重复的行

DELETE (SELECT ROWID,b.* FROM emp b WHERE b.empno=7934) E WHERE E.ROWID='AAARc8AABAAAWWaAAA' ;

【Create AS】

--插入数据表结构相同

CREATE TABLE emp1 AS SELECT * FROM emp;

DROP TABLE emp1;

--指定列

CREATE TABLE emp1 AS SELECT ename,sal,comm FROM emp;

DROP TABLE emp1;

--只需要表结构

CREATE TABLE emp1 AS SELECT * FROM WHERE 1=2;

【排序】

Order by [column] asc(默认低—>高) ;

Order by [column] desc(高->低);

Eg：

--create 实例

CREATE TABLE stuInfo(

stuNo CHAR(6) NOT NULL, --学号

stuName VARCHAR2(20) NOT Null,--学员姓名

stuAge NUMBER(3,0) NOT NULL, --年龄

stuID NUMBER(18,0) NOT NULL, --身份证

stuSeat NUMBER(2,0) NOT NULL --座位号

);

--创建SEQUENCE 实现递增(自增长)

CREATE SEQUENCE seq_stuInfo;

--插入数据以便后续操作

INSERT INTO stuInfo VALUES ('Y2871','张三',18,'26231522214278899',12);

INSERT INTO stuInfo VALUES (seq_stuInfo.Nextval,'张三',19,'26231522214278895',11);

INSERT INTO stuInfo VALUES (seq_stuInfo.Nextval,'张三',20,'26231522214278895',13);

INSERT INTO stuInfo VALUES (seq_stuInfo.Nextval,'李四',20,'26231522214278895',14);

INSERT INTO stuInfo VALUES (seq_stuInfo.Currval,'李四',20,'26231522214278895',14);

测试插入的数据：

可以看到李四，数据是重复的。

去重复查询

SELECT distinct * FROM stuInfo

--查找不存在重复的列

SELECT stuNo,stuName,stuAge FROM stuInfo GROUP BY stuName,stuAge,stuNo HAVING (COUNT(stuName||stuAge)<2);

结果：

--删除stuName ，stuAge 列重复的行

DELETE FROM stuInfo

WHERE ROWID NOT IN

(

SELECT MAX(ROWID) FROM stuInfo GROUP BY stuName,stuAge

HAVING (Count(stuName||stuAge)>1)

UNION

SELECT MAX(ROWID) FROM stuInfo GROUP BY stuName,stuAge

HAVING (COUNT(stuName||stuAge)=1)

);

结果：

--TRUNCATE TABLE

TRUNCATE TABLE stuInfo ;

--删除表

DROP TABLE stuInfo;

--删除索引

DROP SEQUENCE seq_stuInfo;

--查看所有用户表

SELECT * FROM user_all_tables;

【事物控制语言实例】

--DROP TABLE DEPT2;

--步骤一（创建dept2表）：

CREATE TABLE DEPT2(

deptno NUMBER(2) PRIMARY KEY,

dname NVARCHAR2(10),

Loc VARCHAR2(13)

);

--步骤二：

INSERT INTO DEPT2 VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO DEPT2 VALUES (20,'RESEARCH','DALLAS');

INSERT INTO DEPT2 VALUES (30,'RSALES','CHICAGO');

INSERT INTO DEPT2 VALUES (40,'OPERATIONS','BISTION');

SELECT * FROM DEPT2;

COMMIT;

ROLLBACK;--前面提交了数据这执行无效：说明一旦数据 commit 提交后 rollback 回滚没有任何意义。

--步骤三：

INSERT INTO DEPT2 VALUES (50,'a','null');

INSERT INTO DEPT2 VALUES (60,'b','null');

SAVEPOINT a;

SELECT * FROM DEPT2;

INSERT INTO DEPT2 VALUES (70,'c','null');

INSERT INTO DEPT2 VALUES (80,'d','null');

ROLLBACK TO SAVEPOINT a;

SELECT * FROM DEPT2;

--结束后一定要提交数据该实例实现局部回滚

COMMIT;

【修改表的列】：

CREATE TABLE employee(

empno NUMBER(4) NOT NULL,

ename VARCHAR2(10) ,

job VARCHAR2(20),

mgr NUMBER(4),

hiredate DATE ,

sal NUMBER(7,2),

comm NUMBER(7,2),

deptno NUMBER(2)

);

--插入数据Scott.emp表的数据

INSERT INTO employee SELECT * FROM scott.emp;

SELECT * FROM employee;

--添加约束

ALTER TABLE employee

ADD CONSTRAINT fk_deptno

FOREIGN KEY (deptno) REFERENCES DEPT2(deptno);

--向表employee 表中添加empTel_no，empAddress 列。

ALTER TABLE employee

ADD (

empTel_no VARCHAR2(12),

empAddress VARCHAR2(20)

) ;

SELECT * FROM employee ;

--从表employee 表删除empTel_no，empAddress 列。

--1.从物理上删除实现

ALTER TABLE employee DROP (empAddress,empTel_no); --一次删除多个列

ALTER TABLE employee DROP COLUMN empTel_no; -- 一次删除一个列

--2. 从逻辑上删除实现

ALTER TABLE employee SET unused(empAddress,empTel_no);--设置要删除的列为不可用

ALTER TABLE employee DROP UNUSED COLUMNS CHECKPOINT 250;

【SQL操作符】

--员工表

CREATE TABLE employee2(

empNo CHAR(6) PRIMARY KEY,

empName VARCHAR2(10) NOT NULL,

empk CHAR(15)NOT NULL

);

--退休员工表

CREATE TABLE retireEmp(

rmpNo CHAR(6) PRIMARY KEY,

rmpName VARCHAR2(10) NOT NULL,

rmpk CHAR(15)NOT NULL

);

INSERT INTO employee2 VALUES ('k235','zt5','10年入职');

INSERT INTO employee2 VALUES ('k236','zt6','10年入职');

INSERT INTO employee2 VALUES ('k237','zt7','10年入职');

INSERT INTO employee2 VALUES ('k238','zt8','10年入职');

INSERT INTO employee2 VALUES ('k232','zt','返聘入职');

INSERT INTO employee2 VALUES ('k231','zt2','返聘入职');

INSERT INTO retireEmp VALUES ('k232','zt','工作20年');

INSERT INTO retireEmp VALUES ('k231','zt2','工作20年');

INSERT INTO retireEmp VALUES ('k233','zt3','工作20年');

INSERT INTO retireEmp VALUES ('k234','zt4','工作20年');

SELECT * FROM retireEmp;

SELECT * FROM employee2;

COMMIT;--提交数据

--Union 测试

SELECT empno,empName FROM employee2

UNION

SELECT rmpNo,rmpName FROM retireEmp;

--结果共8条数据排除了返聘的员工（补集：两表中五重复的数据）

--UnionAll 测试

SELECT empno,empName FROM employee2

UNION ALL

SELECT rmpNo,rmpName FROM retireEmp;

--结果返回10条记录（包括返聘的员工）（并集）

--INTERSECT 测试

SELECT empNo FROM employee2

INTERSECT

SELECT rmpNo FROM retireEmp;

--结果：2 条数据是两表中都存在的数据（交集）

--MINUS 测试 (减集)

SELECT empNo FROM employee2

MINUS

SELECT rmpNo FROM retireEmp;

--结果：4 条记录

--连接操作符

SELECT empNo||'_'||empName FROM employee2;

【SQL 函数】

--TO_CHAR(d|n [,fmt])函数其中d 是日期 n 是数字fmt 是指日期或数字的格式

SELECT TO_CHAR(Systimestamp,'yyyy-MM-dd HH24:MI:SS') FROM dual;

SELECT SYSTIMESTAMP FROM dual; --显示时区

SELECT SYSDATE FROM dual;--不显示时区

--TO_DATE(CHAR [,fmt)函数

SELECT TO_DATE('2014-03-29 08:11:23','yyyy-MM-dd HH24:MI:SS') FROM dual;

--注意：转化时如果char 的待转化的长度大于后面的格式则会报错小于没关系

--TO_NUMBER()函数开根

SELECT SQRT(to_NUMBER('100')) FROM dual;

--其他函数（相当于switch case 1：break； case 2: break;defalut : break;）

--NVL(exp1,exp2) :如果exp1值为null 则返回 exp2

--NVL2(exp1,exp2,exp3) : 如果exp1 的值为null 返回exp2 的值否则返回exp3 的值

--DECODE(value,if1,then1,if2,then2,else)

SELECT ename,sal+NVL(comm,0) sall,NVL2(comm,sal+comm,sal) sal2,

Decode(TO_CHAR(hiredate,'MM'),'01','一月','02','二月','03','三月''04','四月','11','十一月') mon

FROM employee;

--分析函数语法: 函数名（[参数]） over ([分区子句] [排序子句])

--函数名：要分析的函数名称参数：表示函数需传入的参数分区子句（PARTITION BY ）

--表示查询结果分成不同的组功能类似于Group BY 是分析函数的基础默认将结果作为一个分组。

--ROW_NUMBER

SELECT ename,deptno,sal,

RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) "RANK",

DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal Desc) "DENSE_RANK",

ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) "Row_NUMBER"

FROM employee;

【表空间】

定义：

Oracle 数据库包含逻辑结构和物理结构。数据库的物理结构之构成数据库的一组操作系统文件，数据库的逻辑结构是指描述数据组织方式的一组逻辑概念以及它们之间的关系。

表空间是数据库逻辑组件的重要组成部分，表空间可以存放各种应用对象，如表，索引，每个表空间由一个或多个数据文件组成。

使用表空间的目的：

对于不同的用户分配不同的表空间方便对用户数据库的操作，对模式对象的管理。

可以将不同数据库文件创建到不同的磁盘中，有利于管理空间，有利于提高 I/o性能。有利于纷纷和恢复数据文件。

表空间的分类：

类别	说明
永久性表空间	一般保存方式、视图、过程和索引等数据。SYSTEM SYSAUX USERS EXAMPLE表空间是默认安装的。
临时表空间	只用于保存系统中的短期活动的数据。如排序等
撤销表空间	用表帮助回退提交的事务，已经提交了的数据在这里不再是不可以恢复的了。一般不需要创建临时表空间、撤销表空间，除非把他们移到其他磁盘以提高性能。

--创建表空间

CREATE TABLESPACE spc_twoProgram

DATAFILE 'F:\DataFiles\twoProgram.DBF'

SIZE 10M

AUTOEXTEND OFF;

--删除表空间

DROP TABLESPACE spc_twoProgram ;--表空间没有数据

DROP TABLESPACE spc_twoProgram INCLUDING CONTENTS AND DATAFILES;--表空间存在表（数据）；

--修改表空间

ALTER TABLESPACE spc_twoProgram

DATAFILE 'F:\DataFiles\twoProgram.DBF'

SIZE 5M

AUTO OFF;

【--创建用户User】

CREATE USER sonsd

Identified BY sonsd

DEFAULT TABLESPACE spc_twoProgram ;

--第一次登录时强制需要修改密码

ALTER USER A_hr PASSWORD EXPIRE;

--创建用户tester

CREATE USER tester

IDENTIFIED BY tong

DEFAULT TABLESPACE spc_twoProgram ;

GRANT CONNECT,RESOURCE TO tester;

SELECT * FROM scott.emp;

--查询用户

SELECT *

FROM dba_users

WHERE username='user_name;

--查看表空间限额

SELECT *

FROM dba_ts_quotas

WHERE username='A_HR';

--修改用户密码

ALTER USER sonsd

Identified BY orcl

--更改表空间中的用户限额

ALTER USER A_hr QUOTA 20M ON tp_bak;

--删除用户 (无法删除当前连接的用户)

--解决办法：1.SELECT * FROM V$SESSION WHERE USERNAME='LGDB'

--2.看哪些电脑或应用在Connect

--3.Disconnect 那些用户 .

--4.再Drop

DROP USER sonsd CASCADE;

【数据库权限管理】

系统权限：

CREATE SESSION ：连接到数据库

CREATE TABLE ：创建表

CREATE VIEW ：创建视图

系统权限级联：

Admin 赋予-> 用户A 权限

用户A 赋予-> 用户B 权限

现在Admin 撤销用户A 的权限

:现在用户A 没有权限了

但是用户B 还有权限（不受影响）

对象权限：

对象权限级联：

Admin 赋予-> 用户A 权限

用户A 赋予-> 用户B 权限

现在Admin 撤销用户A 的权限

:现在用户A 没有权限了

但是用户B 也没有权限（受影响）

--Oracle 数据库用户获取权限的两种途径

--1.管理员直接向用户授予权限 2.管理员将权限赋予角色然后再将角色授予一个或多个用户。

--数据库用户安全设计原则

--1.授权按照最小分配原则

--2.用户要分为管理，应用，维护，备份四类用户。

--3.不允许使用Sys 和 system 用户建立数据库应用对象

--4.禁止Grant dba to user

--给用户赋权

GRANT CONNECT ,RESOURCE TO sonsd;

--撤销用户权限

REVOKE CONNECT ,RESOURCE FROM sonsd;

--允许用户访问 emp 表中的记录

GRANT SELECT ON scott.emp TO sonsd;

--允许用户更新emp 表中的记录

REVOKE UPDATE ON scott.emp FROM sonsd;

【序列】

--创建序列语法详解：

CREATE SEQUENCE seq_ag;

[START WITH INTEGER] --指定要生成的第一个序列号对于升序序列，其默认值为序列的最小值；对于降序其默认的值为列的最大值。

[INCREMENT BY INTEGER] --指定序号间的间隔默认为1 如果<0 则生成的序列将按降序排列，如果n>0 生成的序列按升序排列。

[MAXVALUE INTEGER|NOMAXVALUE] --指定序列可以生成的最大值 Integer 类型或没有最大值

[MINVALUE INTEGER|NOMINVALUE]

[CYCLE|NOCYCLE] --序列在到达最大值或最小值时将不能继续生成。

[CACHE INTEGER|NOCACHE] --使用CACHE 可以预先分配一组序列号，并将其保存在内存中，

--这样可以更加快速的访问序列号。用完再继续生成新的一组序列号

--使用序列的数据库在转移时要注意设置序列的初值，否则会出现错

注意：使用序列设置关键字时，在数据库迁移时需要特别注意，由于前一后表中已经存在数据，如果不需改序列的起始值，将会在表中插入重复的数据，违背主键的约束，所以在创建序列是需要修改序列的初始值。

--创建序列。从序号10开始，每次增加1，最大为2000，不循环，再增加会报错

CREATE SEQUENCE seq1

START WITH 10

INCREMENT BY 1

MAXVALUE 2000

NOCYCLE

CACHE 30;

--在玩具表中，需要标识列toyid作为标识，不需要有任何含义，可以作为主键

--创建toys表

CREATE TABLE toys(

toyid NUMBER NOT NULL,

toyname VARCHAR2(20),

toyprice NUMBER

);

--插入数据

INSERT INTO toys (toyid, toyname, toyprice)

VALUES (seq1.NEXTVAL, 'TWENTY', 25);

INSERT INTO toys (toyid, toyname, toyprice)

VALUES (seq1.NEXTVAL,'MAGIC PENCIL',75);

--查询数据

SELECT * FROM toys;

SELECT seq1.CURRVAL FROM dual;

--修改序列

ALTER SEQUENCE seq1

MAXVALUE 5000

CYCLE;

--删除序列

DROP SEQUENCE seq1;

--使用SYS_GUID函数

SELECT sys_guid() FROM dual;

--使用SYS_GUID()函数生成32位的唯一编码作为主键

SELECT SYS_GUID() FROM dual;

【同义词】

定义：

同义词即使对象的一个别名，不占用任何实际的存储空间，只是在Oracle的数据字典中保存其定义描述。在使用同义词时Oracle将会翻译为对应对象的名称。

注意：使用同义词时，需要获得对应的对象的访问权限。

一：对象（如表、）私有同义词、公有同义词是否可以三者同名？

对象名不能与私有同义词同名。

对象和公有同义词同名时，数据库优先选择对象作为目标。

私有同义词和公有同义词同名时，数据库优先选择私有同义词作为目标。

1.私有同义词：是有同义词自能被当前模式的用户访问，私有同义词名称不可与当前模式的对象名称相同。要在当前模式下创建私有同义词用户需要拥有CREATE SYNONYM系统权限，要在其他用户模式下创建私有同义词用户需要拥有CREATE ANY SYNONYM 系统权限。

语法:CREATE OR REPLACE SYNONYM [schema.]synonym_name

FOR [schema.]object_name;

Synonym_name 同义词的名称

Object_name 指定要为之创建的对象的名称。

Eg:

在A_oe模式下创建私有同义词访问A_hr模式下的employee表

关键代码：

创建同义词：

CREATE SYNONYM SY_EMP FOR A_hr.employee;

--访问同义词

SELECT * FROM public_sy_emp;

共有同义词：公有同义词可被所有的数据库访问公有同义词可以隐藏数据库对象所有者和名称并降低SQL语句的复杂性，要创建公有同义词用户必须拥有CREATE PPUBLIC SYNONYM 系统权限。

语法：

CREATE OR REPLACE PUBLIC SYNONYM sysnonym_name

FOR [schema.]object_name;

示例：

--在A_hr 模式下对员工表（employee）创建公有同义词public_sy_emp作为employee表的别名

CREATE OR REPLACE PUBLIC SYNONYM public_sy_emp For employee;

--在A_oe 下访问公有同义词。

SELECT * FROM public_sy_emp;

区别：

私有同义词只能在当前模式下访问，并且不能和当前模式名称相同。

公同义词可悲所有的数据库用户访问。

删除私有同义词

DROP SYNONMY SYN_NAME;

删除公有同义词

DROP PUBLIC SYNONMY SYN_NAME;

【索引】

介绍：索引是与表的关联的可选结构，是一种快速访问数据库的途径，可提高数据库性能。数据库可以明确地创建索引，以加快对表的执行SQL语句的速度，当索引键作为查询条件时，该索引将直接指向这些值的位置。即便删除索引，也无需修改任何SQL语句的定义。

索引的分类

物理分类	逻辑分类
分区或非分区索引	单列或组合索引
B树索引（标准索引）	唯一或非唯一索引
正常或反向索引	基于函数索引
位图索引

B树索引：

创建的语法：

CREATE [UNIQUE] INDEX index_name ON table_name (column_list)

[TABLESPACE tablespace_name];

Unique:指定唯一索引，默认为非唯一索引。

Index_name:索引的名称。

Table_name:表示为之创建索引的表名

Column_list:在其上创建索引的列名的列表，列之间用逗号隔开。

Tablespace_name:为索引指定表空间。

唯一索引：

定义索引的列中任何两行都没有重复值，唯一索引中的索引关键字只能指向表中的一行。在创建约束和创建唯一约束是都会创建一个与之对应的唯一索引。

非唯一索引：

单个关键字可以有多个与其关联的行。、

Eg:CREATE UNIQUE INDEX index_unique_grade ON salgrade(grade);

反向键索引：

与常规的B树索引相反，反向键索引在保持列顺序的同时反转索引的字节，

优点：对于连续增长的索引列，反转索引列，可以将索引数据分散在多个索引快间减少i/o瓶颈发生。

Eg:

CREATE INDEX index_reverse_empno ON employee(empno) REVESE;

位图索引：

它适合于低基数列（该列的值是有限的，理论上不会是无穷大）

优点：

对于大批即时查询，可以减少响应时间

相比其他索引技术，占用空间明显减少

即使在配置很低的终端硬件上，也能获得明显著的性能。

位图索引不应用在频繁发生INSERT ,UPDATE, DELETE 操作表上

Eg:

CREATE BITMAP INDEX index_bit_job ON employee(job);

创建索引的原则：

频繁搜索的列可以作为索引。

经常排序，分组

经常用作连接的列（主键/外键）

将索引放在一个单独的表空间中，不要放在有回退段，临时段和表的表空间中。

对大型索引而言，考虑使用NOLOGGING 子句创建大型索引。

根据业务数据发生频率，定期重新生成或重新组织索引，并进行碎片整理。

仅包含几个不同值的列不可以创建为B树索引。

不要再仅包含几列数据的表中创建索引。

--删除索引

DROP INDEX index_name;

--重建索引：(将反向键索引改为正常的索引)

ALTER INDEX index_name REBUILD NOREVERSE;

ALTER INDEX index_name REBUILD TABLESPACE new_tablespace_name;

【分区表】

分区表的优点：

改善表的查询性能，在对表进行分区的话，用户执行SQL查询时可以只访问表的特定分区而非整个表。

表更容易管理，因为分区表的数据存储在多个部分中，按分区加载和删除数据比在表中加载和删除容易。

便于备份和恢复，可以独立备份和恢复。

注意：分区表不能具有LONG 和LONGAW 数据类型的列。

Oracle分区方法

范围分区,表分区,列分区,合分区,隔分区

【范围分区】

CREATE TABLE rangeOrders

(

order_id NUMBER(12)

, order_date DATE NOT NULL

, order_mode VARCHAR2(8)

, customer_id NUMBER(6) NOT NULL

, order_status NUMBER(2)

, order_total NUMBER(8,2)

, sales_rep_id NUMBER(6)

, promotion_id NUMBER(6)

)

PARTITION BY RANGE (order_date)

(

PARTITION Part1 VALUES LESS THAN (to_date('2005-01-01', 'yyyy-mm-dd')),

PARTITION Part2 VALUES LESS THAN (to_date('2006-01-01', 'yyyy-mm-dd')),

PARTITION Part3 VALUES LESS THAN (to_date('2007-01-01', 'yyyy-mm-dd')),

PARTITION Part4 VALUES LESS THAN (to_date('2008-01-01', 'yyyy-mm-dd')),

PARTITION Part5 VALUES LESS THAN (to_date('2009-01-01', 'yyyy-mm-dd'))

);

--要查看每一分区的数据

SELECT * FROM rangeOrders partition(Part1);

SELECT * FROM rangeOrders partition(Part2);

SELECT * FROM rangeOrders partition(Part3);

SELECT * FROM rangeOrders partition(Part4);

SELECT * FROM rangeOrders partition(Part5);

--插入'2013/01/01'数据

insert into rangeOrders values (1001,to_date('2013-01-01','yyyy-mm-dd'),'direct',101,0,1000,153,null);

--报错后，修正

DROP TABLE rangeOrders;

CREATE TABLE rangeOrders

(

order_id NUMBER(12)

, order_date DATE NOT NULL

, order_mode VARCHAR2(8)

, customer_id NUMBER(6) NOT NULL

, order_status NUMBER(2)

, order_total NUMBER(8,2)

, sales_rep_id NUMBER(6)

, promotion_id NUMBER(6)

)

PARTITION BY RANGE (order_date)

(

PARTITION Part1 VALUES LESS THAN (to_date('2005-01-01', 'yyyy-mm-dd')),

PARTITION Part2 VALUES LESS THAN (to_date('2006-01-01', 'yyyy-mm-dd')),

PARTITION Part3 VALUES LESS THAN (to_date('2007-01-01', 'yyyy-mm-dd')),

PARTITION Part4 VALUES LESS THAN (to_date('2008-01-01', 'yyyy-mm-dd')),

PARTITION Part5 VALUES LESS THAN (MAXVALUE)

);

--要删除第三季度的数据

DELETE FROM rangeOrders partition(Part3);

【间隔分区】

--利用间隔分区将开始创建时没有分区的表创建为新的间隔分区表

--1.利用现有表Orders创建间隔分区表intervalOrders

CREATE TABLE intervalOrders

PARTITION BY RANGE(order_date)

INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))

(PARTITION P1 VALUES LESS THAN (to_date('2005-01-01','yyyy/mm/dd')))

AS SELECT * FROM Orders;

--2.查询分区情况

SELECT table_name,partition_name

FROM user_tab_partitions

WHERE table_name=UPPER('intervalOrders');

--3.向表插入'2013/01/01'数据

insert into intervalOrders values (1001,to_date('2013-01-01','yyyy-mm-dd'),'direct',101,0,1000,153,null);

--4.再次查询某一分区数据

【PL/SQL编程】

【pl/sql块】

结构

[DECLARE]

--声明部分：

BEGIN

--执行部分

[EXCEPTION]

--异常处理部分

END;

【变量和常量的声明】

Variable_name te_type[(size)] [:=init_value];

Variable_name:变量的名称

Date type :数据类型

size:指定变量的范围

init_value:指定变量的初值

Variable_name CONSTANT data_type :=value;

Eg：

===========================================================

| 给变量和常量声明赋值

============================================================

DECLARE

v_ename VARCHAR2(20);

v_rate NUMBER(7,2);

c_rate_incr CONSTANT NUMBER(7,2):=1.10;

BEGIN

--方法一：通过SELECT INTO给变量赋值

SELECT ename, sal* c_rate_incr INTO v_ename, v_rate

FROM employee

WHERE empno='7788';

--方法二：通过赋值操作符“:=”给变量赋值

v_ename:='SCOTT';

END;

【数据类型】

DECLARE

v_empno employee.empno%TYPE :=7788;

v_rec employee%ROWTYPE;

BEGIN

SELECT * INTO v_rec FROM employee WHERE empno=v_empno;

DBMS_OUTPUT.PUT_LINE

('姓名:'||v_rec.ename||'工资:'||v_rec.sal||'工作时间:'||v_rec.hiredate);

END;

===========================================================

| 显示变量v_counter的值，如果该变量小于10，则增加10并显示该变量改变后的值。

============================================================

DECLARE

v_counter NUMBER := 5;

BEGIN

DBMS_OUTPUT.PUT_LINE('v_counter的当前值为:'||v_counter);

IF v_counter >= 10 THEN

NULL; --为了使语法变得有意义,去掉NULL会报语法错误

ELSE

v_counter := v_counter + 10;

DBMS_OUTPUT.PUT_LINE('v_counter的改变后值为:'||v_counter);

END IF;

END;

【异常处理】

===========================================================

| 预定义异常

============================================================

DECLARE

v_ename employee.ename%TYPE;

BEGIN

SELECT ename INTO v_ename

FROM employee

WHERE empno=1234;

dbms_output.put_line('雇员名:'||v_ename);

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line('雇员号不正确');

WHEN TOO_MANY_ROWS THEN

dbms_output.put_line('查询只能返回单行');

WHEN OTHERS THEN

dbms_output.put_line('错误号：'||SQLCODE||'错误描述：'||SQLERRM);

END;

===========================================================

| 查询编号为7788的雇员的福利补助（comm列）。

============================================================

DECLARE

v_comm employee.comm%TYPE;

e_comm_is_null EXCEPTION; --定义异常类型变量

BEGIN

SELECT comm INTO v_comm FROM employee WHERE empno=7788;

IF v_comm IS NULL THEN

RAISE e_comm_is_null;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line('雇员不存在！错误为:'||SQLCODE||SQLERRM);

WHEN e_comm_is_null THEN

dbms_output.put_line('该雇员无补助');

WHEN others THEN

dbms_output.put_line('出现其他异常');

END;

【游标】

===========================================================

| 使用显式游标输出每个员工的姓名和薪水。

============================================================

DECLARE

name employee.ename%type;

sal employee.sal%type; --定义两个变量来存放ename和sal的内容

CURSOR emp_cursor IS

SELECT ename,sal

FROM employee;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO name,sal;

EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE

('第'||emp_cursor%ROWCOUNT||'个雇员:'||name||sal);

END LOOP;

CLOSE emp_cursor;

END;

| 循环游标的用法。

--显示雇员表中所有雇员的姓名和薪水

DECLARE

CURSOR emp_cursor IS

SELECT ename,sal FROM employee;

BEGIN

FOR emp_record IN emp_cursor LOOP

DBMS_OUTPUT.PUT_LINE

('第'||emp_cursor%ROWCOUNT||'个雇员：'

||emp_record.ename|| emp_record.sal);

END LOOP;

END;

===========================================================

| 多表查询更新时，更新表为锁定行所在表。

============================================================

DECLARE

CURSOR emp_cursor IS

SELECT ename,sal

FROM employee e INNER join dept d

ON e.deptno=d.deptno

FOR UPDATE OF sal;

v_emp emp_cursor%ROWTYPE;

BEGIN

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor;

END IF;

LOOP

FETCH emp_cursor INTO v_emp;

EXIT WHEN emp_cursor%NOTFOUND;

UPDATE employee

SET sal=sal+200

WHERE CURRENT OF emp_cursor;

END LOOP;

CLOSE emp_cursor;

END;

===========================================================

| 添加员工记录。

============================================================

CREATE OR REPLACE PROCEDURE add_employee(

eno NUMBER, --输入参数,雇员编号

name VARCHAR2, --输入参数,雇员名称

salary NUMBER, --输入参数,雇员薪水

job VARCHAR2 DEFAULT 'CLERK', --输入参数,雇员工种默认'CLERK'

dno NUMBER --输入参数,雇员部门编号

)

BEGIN

INSERT INTO employee

(empno,ename,sal,job,deptno)VALUES (eno,name,salary,job, dno);

END;

【存储过程】

| sql*plus下调用存储过程

--EXEC add_employee(1111,'MARY',2000,'MANAGER',10);

--EXEC add_employee(dno=>10,name=>'MARY',salary=>2000,eno=>1112, job=>'MANAGER');

--EXEC add_employee(1113,dno=>10,name=>'MARY',salary=>2000,job=>'MANAGER');

--EXEC add_employee(1114,dno=>10,name=>'MARY',salary=>2000);

===========================================================

| PL/SQL下调用存储过程

============================================================

BEGIN

--按位置传递参数

add_employee(2111,'MARY',2000,'MANAGER',10);

--按名字传递参数

add_employee(dno=>10,name=>'MARY',salary=>2000,eno=>2112, job=>'MANAGER');

--混合方法传递参数

add_employee(3111,dno=>10,name=>'MARY',salary=>2000,job=>'MANAGER');

--默认值法

add_employee(4111,dno=>10,name=>'MARY',salary=>2000);

END;

===========================================================

| 将示例8按照推荐规则修改。

============================================================

CREATE OR REPLACE PROCEDURE add_employee(

eno employee.empno%type, --输入参数,雇员编号

name employee.ename%type, --输入参数,雇员名称

salary employee.sal%type, --输入参数,雇员薪水

job employee.job%type DEFAULT 'CLERK', --输入参数,雇员工种默认'CLERK'

dno employee.deptno%type, --输入参数,雇员部门编号

on_Flag OUT number, --执行状态

os_Msg OUT VARCHAR2 --提示信息

)

BEGIN

INSERT INTO employee (empno,ename,sal,job,deptno)VALUES (eno,name,salary,job, dno);

on_Flag:=1;

os_Msg:='添加成功';

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

on_Flag:=-1;

os_Msg:='该雇员已存在。';

WHEN OTHERS THEN

on_Flag:=SQLCODE;

os_Msg:=SQLERRM;

END;

DECLARE

on_Flag NUMBER;

os_Msg VARCHAR2(100);

BEGIN

--按位置传递参数

add_employee(2111,'MARY',2000,'MANAGER',10,on_Flag,os_Msg);

dbms_output.put_line(on_Flag||os_Msg);

END;

posted @ 2014-11-24 21:45 ClodThunder 阅读(299) 评论(0) 收藏举报

刷新页面返回顶部

ClodThunder

世界上只有想不通的人，没有走不通的路。

Oracle 学习笔记

Oracle 数据库基础

公告