一.Oracle基础知识

1.Oracle简介

Oracle是一个数据库管理系统，是Oracle公司的核心产品。

Oracle数据库的主要特点如下：

>支持多用户，大事务量的事务处理
	>
	>在保持数据安全性和完整性方面性能优越
	>
	>支持分布式数据处理
	>
	>具有可移植性。

12c增加了big data、data optimization、manageability、high availability等一些新功能。

2.Oracle基本概念

Oracle_基础图片

1.数据库

>这里数据库不是通常情况下我们所说的数据库，而是Oracle的一个企业名词。它是磁盘上存储的数据的集合，在物理上表现为数据文件、日志文件和控制文件等，在逻辑上以表空间形式存在。使用时，必须首先创建数据库，然后使用。

2.全局数据库名

全局数据库名是用于区分一个数据库的标识，在安装数据库、创建数据库、创建控制文、修改数据库结构，利用RMAN备份时都需要使用。它由数据库名和域名构成的，类似网络中的域名，使数据库的命名在整个网络环境中唯一。

3.数据库实例

每个启动的数据库都对应一个数据库实例，由这个实例来访问数据库中数据。如果把数据库简单的理解为磁盘上的文件，具有永久性，则数据库实例就是通过内存共享运行状态的一组服务器后台进程。

4.表空间

每个Oracle数据库都是由若干个表空间构成的，用户在数据库中建立的所有内容都被存储到表空间。一个表空间可以由多个数据文件组成，但一个数据文件只能属于一个空间。与数据文件这种物理结构相比，表空间属于数据库的逻辑结构。

图片

5.数据文件

数据文件的扩展名是：dbf，是用于存储数据库数据的文件

一个数据文件中可能存储很多个表的数据，而一个表的数据也可能存放在多个数据文件中，即数据库名和数据文件不存在一对一的关系。

6.控制文件

控制文件的扩展名是：ctl，是一个二进制文件。控制文件中存储的信息很多，其中包括数据文件和日志文件的名称和位置。控制文件是数据库启动及运行所必需的文件。当Oracle读写数据时，要根据控制文件的信息查找数据文件。

7.日志文件

日志文件的扩展名是.log，它记录了数据的所有更改信息，并提供了一种数据恢复机制，确保在系统崩溃或其他意外出现意外出现后重新恢复数据库。

在Oracle数据库中，日志文件是成组使用的，每个日志文件组成可以有一个或多个日志文件。在工作过程中，多个日志文件组之间循环使用，当一个日志文件组写满后，会转向下一个日志文件组。

8.模式和模式对象

模式是数据库对象（如表、索引等，也称模式对象）的集合。Oracle会为每个数据库用户创建一个模式，此模式为当前用户所拥有，和用户具有相同的名称。

3.数据库服务简介

共有7个服务，这七个服务的含义分别为：

Oracle ORCL VSS Writer Service： Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service）能够让存储基础设备（比如磁盘，阵列等）创建高保真的时间点映像，即映射拷贝（shadow copy）。它可以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。（非必须启动）

OracleDBConsoleorcl：Oracle数据库控制台服务，orcl是Oracle的实例标识，默认的实例为orcl。在运行Enterprise Manager（企业管理器OEM）的时候，需要启动这个服务。（非必须启动）

OracleJobSchedulerORCL： Oracle作业调度（定时器）服务，ORCL是Oracle实例标识。（非必须启动）

OracleMTSRecoveryService：服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。（非必须启动）

OracleOraDb11g_home1ClrAgent： Oracle数据库 .NET扩展服务的一部分。（非必须启动）

OracleOraDb11g_home1TNSListener：监听器服务，服务只有在数据库需要远程访问的时候才需要。（非必须启动，下面会有详细详解）。

OracleServiceORCL：数据库服务(数据库实例)，是Oracle核心服务该服务，是数据库启动的基础，只有该服务启动，Oracle数据库才能正常启动。(必须启动)

4.连接数据库

1.SQL*Plus工具

Oracle的SQLPlus是与Oracle数据库进行交互的客户端工具。在SQL*Plus中，可以运行SQLPlus命令与SQL语句

SQLPlus命令(win+R运行 sqlplus /nolog)

#登录系统管理员
conn / as sysdba;

#开始记录（结束才能保存）
spool d:\XX.txt

#记录结束
spool off;

#清屏命令
host cls 
clear screen
clear SCR

#显示行宽
show linesize

#设置行宽
set linesize 120

#设置列宽（a代表一个字符,8代表有8个a,9代表一个数字）
col 字段名 for a8
col 字段名 for 9999

#ed写命令执行文件   /执行命令文件
ed
/

#错误修改
SQL> select empno,ename,sal
  2  form emp;
form emp
     *
第 2 行出现错误: 
ORA-00923: 未找到要求的 FROM 关键字 

SQL> 2
  2* form emp
SQL> c/form/from
  2* from emp
SQL> /

2.PL/SQL Developer工具

连接：

Normal：普通用户

sysOper：数据库操作员--主要包括打开、关闭、备份、恢复数据库服务器等

sysDBA：数据库管理员--主要包括打开数据库服务器、关闭数据库服务器、备份数据库服务器、恢复数据库服务器、日志归档、管理功能、创建数据库等。

二.Oracle数据类型

1.字符数据类型

字符类型主要包括VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2等。

（1）VARCHAR2类型：用于存储可变长度的字符串，其语法格式VARCHAR2(maxlength)，参数maxlength的最大值可以是32767字节。

注意：数据库类型的VARCHAR2的最大长度是4000字节，所以一个长度大于4000字节的PL/SQL类型VARCHAR2变量不可以赋值给数据库中的一个VARCHAR2变量，而只能赋值给LONG类型的数据库变量。

（2）CHAR类型：CHAR类型表示指定长度的字符串，其语法格式CHAR(maxlength)。

（3）LONG类型：LONG类型表示一个可变的字符串，最大长度是32767字节，而数据库类型的LONG最大长度可达2GB，所以任何字符串变量都可以赋值给它（LONG类型不建议使用，而建议使用CLOB）。

（4）NCHAR数据类型：即国家字符集，使用方法和CHAR相同，如果开发的项目需要国际化，那么数据类型选择NCHAR数据类型。NCHAR和CHAR的区别在于NCHAR用来存储Unicode字符集类型，即双字符集类型。例如：我们定义CHAR(11)和NCHAR(11)类型的两个字段，字段长度为1字节和1个字符（2个字节），分别插入'a'和'a'是没有问题的，但是占用的字节数分别是1和。如果占用的字节数分别是1和2。如果分别插入‘的’和‘的’，则前者无法正常插入，而后者可以。

（5）NVARCHAR2与NCHAR类似，在使用上，NVARCHAR2存储需要国际化的可变长度字符串

2.数值数据类型

数值类型主要包括NUMBER、PLS_INTEGER、BINARY_INTEGER这3中基本类型。其中，NUMBER类型的变量可以存储正数、负数、零、定点数和精度为38位的浮点数；而PLS_INTEGER、BINARY_INTEGER类型的变量只存储整数。

#p为精度，表示有效数字，在1~38之间有效数字
#s为范围，表示小数点右边数字的位数，它在-84~+127之间
#定位数
NUMBER(p)

#浮点数
NUMBER(p,s)
NUMBER(p=38,s=0)

3.日期时间数据类型

日期时间数据类型用于存储日期值和时间值

（1）DATE数据类型

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

（2）TIMESTAMP数据类型

TIMESTAMP数据类型用于存储日期的年、月、日，以及时间的小时、分和秒，其中秒值精确到小数点后6位，该数据类型同时包含时区信息。SYSTIMESTAMP函数的功能是返回当前日期，时间和时区。

4.LOB数据类型

LOB又称“大对象”数据类型。该数据类型可以存储多达4GB的废结构化信息，如声音和视频剪辑等。LOB允许对数据进行高效、随机、分段的访问。LOB可以是外部的，也可以是内部的，这取决于它相对于数据库的位置。LOB数据类型有CLOB、BFILE、BLOB和NCLOB。

（1）CLOB

CLOB（CHaracter LOB，字符LOB）能够存储大量字符类型。该数据类型可以存储单字节字符数据和多字节字符数据，主要用于存储非结构化的XML文档，如新闻、内容介绍等含大量文字内容的文档。

（2）BLOB

BLOB（BinaryLOB1，二进制LOB）可以存储较大的二进制对象，如图像、视频剪辑和声音剪辑等

（3）BFILE

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

（4）NCLOB

NCLOB数据类型用于存储大的NCHAR字符数据。NCLOB数据类型同时支持固定宽度字符和可变宽度字符（Unicode字符数据）。大字符对象的大小不大于4GB。NCLOB类型的使用方法与CLOB类型。

Oracle中的表可以有多个LOB列，每个LOB列可以是不同的LOB类型。

5.特殊数据类型

为了提高用户的编程效率和解决复杂的业务逻辑需求，PL/SQL语言除了可以使用Oracle规定的基础数据类型外，还提供了3种特殊的数据类型，但这3种类型仍然是建立在基本数据类型基础之上的。

%TYPE类型

使用%TYPE关键字可以声明一个与指定列名称相同的数据类型，它通常紧跟在指定列名的后面。

RECORD类型

单词RECORD有“记录”之意，因此RECORD类型也称作“记录类型”，使用该类型的变量可以存储由多个列值组成的一行数据。在声明记录类型变量之前，首先需要定义记录类型，然后才可以声明记录类型的变量。记录类型是一种结构化的数据类型，它使用type语句进行定义，在记录类型的定义结构中包含成员变量及其数据类型，其语法格式如下：

type record_type is record ( var_member1 data_type [not null][:=default_value], … var_membern data_type [not null][:=default_value] ) record_type：表示要定义的记录类型名称。

var_member1：表示该记录类型的成员变量名称。

data_type：表示成员变量的数据类型。

3 .%ROWTYPE类型

%ROWTYPE类型的变量结合了%TYPE类型和RECORD类型变量的优点，它可以根据数据表中行的结构定义一种特殊的数据类型，用来存储从数据表中检索到的一行数据。它的语法形式很简单，如下所示：

rowVar_name table_name%rowtype; rowVar_name：表示可以存储一行数据的变量名。

table_name：指定的表明。

6.伪列

伪列就像Oracle中的一个表列，但实际上它并未存储在表中。伪列可以从表中查询，但是不能插入、更新和删除它们的值。这里主要讲解ROWID和ROWNUM。

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

ROWID伪列有以下重要的用途。
1.能以最快的方式访问表中的一行

2.能显示表的行是如何存储的

3.可以作为表中行的唯一标识

如：可以使用SELECT语句查询ROWID值
SQL>SELECT ROWID,eName
	FROM SCOTT.emp
	WHERE eName="SMITH"
2.ROWNUM

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

如：从emp表中提取10条记录并显示序号
SQL>SELECT emp.*,ROWNUM
	FROM SCOTT.emp
	WHERE ROWNUM<11
ROWNUM查询结果

查询条件结果

ROWNUM对于等于某值的查询条件等于1，可以查到第一条数据，但等于2则查不到

ROWNUM对于大于某值的查询条件查不到，原因是ROWNUM总是从第一个开始（>2)

ROWNUM对于大于某值的查询条件小于11，得到10条记录

查询条件	结果
ROWNUM对于等于某值的查询条件	等于1，可以查到第一条数据，但等于2则查不到
ROWNUM对于大于某值的查询条件	查不到，原因是ROWNUM总是从第一个开始（>2)
ROWNUM对于大于某值的查询条件	小于11，得到10条记录

伪表 dual

SQL>select 5+3 from dual； --作用：不存储主题数据，主要功能是用来做查询

三.SQL语言简介

1.数据定义语言DDL

CREATE（创建）、ALTER（更改）、TRUNCATE（截断）、DROP（删除）

（1）CREATE TABLE命令（省略）

（2）TRUNCATE TABLE命令
语法：
SQL>TRUNCATE TABLE <tablename>

##2.数据操纵语言DML

INSERT（插入）、SELECT（查询）、DELETE（删除）、UPDATE（更新）

STUNO	STUNAME	STUAGE	STUSEAT
1	张三	18	1
2	李四	20	2
3	王五	15	3
4	张三	18	4
5	张三	20	5

1.从语法的角度介绍DML语言操作

（1）选择无重复的行

要防止选择重复的行，可以在SELECT命令中包含DISTICT子句

（2）带条件和排序的SELECT命令

要从表中选择特定的行，可以在SELECT命令中包含WHERE子句。ORDER by ASC/DESC

（3）使用列别名

列别名是为列表达式提供的另一个名称，位于列表达式后面，并显示在列表达式标题中。列表达式不影响列的实际名称 as

（4）利用现有的表创建新表
SQL>CREATE TABLE <newtable>
	AS
	SELECT{* | column(s)}
	FROM <oldtable> [WHERE <condition>]
	
--只复制表结构时，可以加上WHERE 1=2
2.从使用技巧的角度介绍DML语言操作

（1）查看表中行数
SQL>SELECT COUNT(*) FROM stuinfo;  --效率低
SQL>SELECT COUNT(1) FROM stuinfo;  --效率高
（2）取出stuName，stuAge
SQL> SELECT stuName,stuAge 
          FROM stuInfo
          GROUP BY stuName,stuAge 
          HAVING(COUNT(stuName||stuAge) <2);
（3）删除stuName，stuName列重复的行（只保留一行）
SQL>DELETE 
          FROM stuInfo 
          --4.获得所有重复记录的ROWID
          WHERE ROWID NOT IN( 
              			--1.查找重复的记录，只保留一条
          		           SELECT MAX(ROWID) 
                   	     FROM stuInfo
                  	     GROUP BY stuName,stuAge 
                             HAVING(COUNT(stuAge||stuAge)>1)
              			--3.合并结果集，获得所有符合条件且不重复的记录
		                 UNION
              			--2.查找不重复的记录
		                 SELECT max(ROWID)
            		     FROM Stuinfo
		                 GROUP BY stuName,stuAge 
            		     HAVING(COUNT(stuAge||stuAge)=1)     

3.事务控制语言TCL

COMMIT（提交）、SAVEPOINT（保存点）、ROLLBACK（回滚）

组成

COMMIT ：提交事务，即把事务中对数据库的修改进行永久保存

ROLLBACK ：回滚事务，即取消对数据库所作的任何修改

SAVEPOINT ：在事务中创建存储点

ROLLBACK TO <SavePoint_Name>：将事务回滚到存储点
SQL>INSERT INTO dept VALUES(50,'a',null);
SQL>INSERT INTO dept VALUES(60,'b',null);
SQL>SAVEPOINT a;
SQL>INSERT INTO dept VALUES(70,'c',null);
SQL>ROLLBACK TO SAVEPOINT a;--回滚到a存储点
SQL>COMMIT;--执行commit，不包含第四行数据
SQL>SELECT * FROM dept;

4.数据控制语言DCL

GRANT（授权）、REVOKE（回收）

数据控制语言为用户提供权限控制命令

四.SQL操作符

##1.算术操作符

+、-、*、/

##2.比较运算符

=、！=、>、<、>=、<=、BETWEEN....AND、IN、LIKE、IS NULL

SQL>select * from emp where id is null;  ---null 永远！=null
SQL>select * from emp where id=null;--错误XXX

between :含有边界小值在前,大值在后

SQL>select * from emp where sal between 1000 and 2000;

in在集合中

SQL>select * from emp where deptno not in(10,20)

5.模糊查询方式

通配符 _任意一个字母

%:任意0-n个字母

#若使用模糊查询字段中值，值中带下划线，需要用 '\' escape '\'转义
SQL>select * from emp where ename like '%\_%' escape '\'

3.逻辑运算符

AND、OR、NOT

4.集合操作符

集合操作符将两个查询的结果组合成一个结果集。可以在SQL中使用下面的结合操作符来组合多个查询中的行

UNION（联合）：用来求两个集合的并集，并去掉重复值

UNION ALL（联合所有）：用来求两个集合的并集

INTERSECT（交集）：用来求两个集合的交集，并去掉重复值

MINUS（减集）：用来求在第一个集合中存在，而在第二个集合中不存在的记录，并去掉重复值

使用集合操作符连接起来的SELECT语句中列遵循以下规则：

通过集合操作符连接的各个查询具有相同的列数，而且对应列的数据类型必须兼容

这种查询不应含有LONG类型的列。

5.连接操作符

连接操作符（||）用于将两个或多个字符串合并成一个字符串，或者将一个字符串与一个数值合并在一起。

SQL>select a||'_'||b from emp;
SQL>select concat(EMPNO,ENAME) from emp;

优先级

运算符	级别
算术运算符(即‘+',‘-'，‘*',‘/')	1
连接运算符（即‘\|\|'）	2
比较运算符（即‘>'，‘>='，‘<'，‘<='，‘<>'）	3
Is [not] null,[not] like,[not] in	4
[not] between-and	5
not	6
and	7
or	8

五.SQL函数

Oracle SQL提供了用于执行特定操作的专用函数。（划分为3个）

单行函数：日期函数、数字函数、字符函数、转换函数、其他函数

聚合函数（分组函数）

分析函数

##1.单行函数:

（1）操作数据对象（2）接受参数返回一个结果（3）只对一行进行变换（4）每行返回一个结果（5）可以转换数据类型（6）可以嵌套（7）参数可以是一列或一个值

（1）转换函数

将值从一种数据类型转换为另一种数据类型。

常见转换函数

函数功能

TO_CHAR() 转换成字符串类型

TO_DATE() 转换成日期类型

TO_NUMBER 转换成数值类型

函数	功能
TO_CHAR()	转换成字符串类型
TO_DATE()	转换成日期类型
TO_NUMBER	转换成数值类型

#to_char()
SQL>select to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') from dual;--2018-10-15 07:10:00
SQL>select to_char(sal,'L9,999.9')from emp; --L9,999,9 规定写法 ￥12542

#to_date()
SQL>select to_date('2018-07-10','yyyy-mm-dd') from dual;

#to_number()
SQL>select sqrt(to_number('100')) from dual; --sqrt() 求平方根

（2）分析函数

分析函数是对一组查询结果进行运算，然后获得结果。

类似聚合函数，区别在于分析函数每个组返回多行，聚合函数每组返回一行

语法：

函数名([参数]) OVER ( [分区子句]

[ 排序语句])

在语法中：

函数名表示分析函数的名称

参数表示函数需要传入的参数

分区子句（PARTITION BY）表示将查询结果分为不同的组，功能类型GROUP By语句，是分析函数工具的基础，默认将每个分区进行排序。

RANK、DENSE_RANK、ROW_NUMBER函数用于为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。

1.ROW_NUMBER函数

表示返回一个唯一的值，当遇到相同数据时，排名按照记录集中记录的顺序依次递增。

2.DENSE_ANK函数

表示返回一个唯一的值，当遇到相同数据时，所有相同数据的排名都是一样的。

3.RANK函数

表示返回一个唯一的值，当遇到相同的数据时，所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

（3）字符函数

大小写控制函数：lower、upper、initcap

SQL>select lower('Hello world')"小写",
   >upper('Hello world')"大写", 
   >initcap('hello')"首字母大写"
   >from dual;

字符控制函数：concat、substr、length、lengthb、instr、lpad、rpad、trim、replace

#concat 字符拼接
SQL>select concat('a','b') as "字符拼接" from dual;

#substr 字符截取
SQL>select substr('hello world',5) from dual;
SQL>select substr('hello world',5,8) from dual;
SQL>select substr('hello world',8,5) from dual;--以8开始截取，5自动省略

#length 长度(以字符)  lengthb (以字节)
SQL>select length('hello') from dual;
SQL>select lengthb('hello') from dual;

#instr(a,b) 在a中查找b的位置
SQL>select instr('hello','e') from dual;

#lpad左填充，rpad右填充  填充到10位，不够以*代替
SQL>select lpad('aa',10,'*') from dual;
SQL>select rpad('aa',10,'*') from dual;

#trim 去除字符
SQL>select trim('a' from 'abac') from dual;--去除abac中第一个a

#replace() 替换字符
SQL>select replace('abac','a','r') from dual;--替换abac中所有a字符为r

（4）数字函数

round、trunc、mod

#round 四舍五入
SQL>select round(15.659,2) from dual; --15.66   2代表小数点位数
SQL>select round(15.655,-1) from dual;--20   保留百位及以上位，个位以0替换

#trunc 截断
SQL>select trunc(15.655,2) from dual;--15.65
SQL>select trunc(15.655,-1) from dual;--10

#mod(a,b) a/b的余数
SQL>select mod(45,2) from dual;--1

（5）日期函数

#sysdate 返回当前时间
SQL> select sysdate from dual;

#昨天 今天 明天
SQL>select (sysdate-1)"昨天",sysdate "今天",(sysdate+1)"明天" from dual;

#add_months 添加月数
SQL>select add_months(sysdate,1) from dual;--1代表月数

#last_day 月的最后一天
SQL>select last_day(sysdate) from dual;

#next_day 下个星期几的日期
SQL>select next_day(sysdate,'星期一') from dual; --星期一是规定，星期日也是

#months_between 两个时间相差的月数
SQL>select months_between('第一个日期','第二个日期') from dual;

#round 日期四舍五入
SQL>select round(sysdate) from dual; --2018/10/16

（6）其他函数

常用的其他函数

函数功能

NVL(exp1,exp2) 如果exp1的值为NULL，返回exp2的值，否则返回exp1的值

NVL2(exp1,exp2,exp3) 如果exp1的值为NULL，则返回exp3的值，否则返回exp2的值

DECODE(value,if1,then1,if2,then2) 如果value的值为if1，则返回then1；以此类推；否则返回else的值

NULLIF(expr1, expr2) 如果两个表达式不相等，NULLIF 返回第一个 expression1 的值。如果两个表达式相等，NULLIF 返回空值NULL。

COALESCE(expr1, expr2) COALESCE返回参数列表中第一个非空表达式。必须指定最少两个参数。如果所有的参数都是null，则返回null。

函数	功能
NVL(exp1,exp2)	如果exp1的值为NULL，返回exp2的值，否则返回exp1的值
NVL2(exp1,exp2,exp3)	如果exp1的值为NULL，则返回exp3的值，否则返回exp2的值
DECODE(value,if1,then1,if2,then2)	如果value的值为if1，则返回then1；以此类推；否则返回else的值
NULLIF(expr1, expr2)	如果两个表达式不相等，NULLIF 返回第一个 expression1 的值。如果两个表达式相等，NULLIF 返回空值NULL。
COALESCE(expr1, expr2)	COALESCE返回参数列表中第一个非空表达式。必须指定最少两个参数。如果所有的参数都是null，则返回null。

#nvl
SQL>select nvl(null,1) from dual; --1

#nvl2
SQL>select nvl2(null,null,3) from dual; --3

#decode
SQL>select decode(1,3,'aa',1,'bb') from dual;--bb

#nullif
SQL>select nullif('a','aa') from dual;--null

#coalesce
SQL>select coalesce(null,3+2) from dual;--5

六.表空间和用户权限

##1.表空间

Oracle数据库包含逻辑结构和物理结构。

物理结构是指构成数据库的一组操作系统文件。

逻辑结构是指描述数据组织方式的一组逻辑概念及它们之间的关系。

表空间是逻辑结构的一个重要组件。表空间可以存放各种应用对象，如表、索引。

而每个表空间由一个或多个数据文件组成。

（1）表空间分类

类别说明

永久性表空间一般保存表、视图、过程和索引等的数据。SYSTEM、SYSAUX、USERS、EXAMPLE表空间是默认安装的

临时性表空间只用于保存系统中短期活动的数据，如排序数据等

撤销表空间用来帮助回退未提交的事务，已提交了的数据在这里是不可以恢复的。一般不需要建临时和撤销表空间，除非把它们转移到其他磁盘中以提高性能

（2）表空间的目的

使用表空间的目的为：

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

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

（3）创建表空间
语法：
SQL>create tablespacename tablename datafile 'filename' [size integer[K|M]]
	[autoextend [off|on]]
tablespacename：创建的表名称

datafile：指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔

filename：数据文件的路径和名称

size：文件大小

autoextend：子句用来启动或禁用数据文件的自动扩展，设置为ON 则空间使用完毕会扩展。

例：
#创建表空间     system用户
SQL> create tablespace haha_test datafile 'd:\data\haha.dbf' size 60m;
（4）查询表空间
SQL> select * from dba_data_files;
（5）修改表空间大小
SQL> alter database datafile 'd:\data\haha.dbf' resize 80m;
（6）给表空间添加数据文件
SQL> alter tablespace haha_test add datafile 'd:\data\haha1.dbf' size 80m    		autoextend on;
（7）删除表空间
#删除表空间名称
SQL> drop tablespace haha_test;
#删除表空间并且物理文件
SQL> drop tablespace haha_test
     include contents;

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

2.自定义用户管理

Oracle中默认三个用户Sys、System、Scott，其中Sys和System是系统用户

1.Sys用户

Sys用户是超级用户，数据库中的数据字典和视图都存储在SYS用户中。

数据字典存储了用来管理数据对象的所有信息，是Oracle数据库中非常重要的系统信息。

Sys用户主要用来维护系统信息和管理实例。

Sys用户只能以SYSOPER或SYSDBA角色登录

2.System用户

这是Oracle中默认的系统管理员，拥有DBA权限。该用户拥有Oracle管理工具使用的内部表和视图。通常System用户管理Oracle数据库的用户、权限和存储等。

不建议在System用户下创建用户表。

System用户不能以SYSOPER或SYSDBA角色登录系统，只能默认

3.Scott用户

这是Oracle数据库时创建的示例用户

在Oracle 12c中，scott用户被取消，需要自己创建
--登录系统用户
SQL>conn / as sysdab;

--删除已有的用户(可选) 
SQL>DROP USER c##scott cascade;

--新建用户，授予权限，连接
--用户名前加c##是12c中的新特性 
SQL>CREATE USER c##scott identified by 123456 ;           
SQL>GRANT CREATE SEQUENCE to c##scott ;
SQL>GRANT UNLIMITED TABLESPACE to c##scott ;
SQL>GRANT CREATE SESSION to c##scott ;
SQL>GRANT CREATE SYNONYM to c##scott ;
SQL>GRANT ALTER SESSION to c##scott;
SQL>GRANT CREATE DATABASE LINK to c##scott;
SQL>GRANT CREATE VIEW to c##scott;
SQL>GRANT CREATE TABLE to c##scott;
SQL>ALTER USER c##scott DEFAULT TABLESPACE USERS;
SQL>ALTER USER c##scott TEMPORARY TABLESPACE TEMP;
SQL>CONN c##scott/123456;

--修改密码
SQL>alter user 用户名 identified by 新密码;

--删除表空间
SQL>drop user 用户名称 cascade;
--删除表空间
SQL>drop tablespace 表空间名称 including contents and datafiles cascade constraint;

（1）创建用户并给用户分配表空间

语法：
create USER user
identified by password
[default TABLESPACE tablespace]
[default TABLESPACE tablespace]

user:用户名，用户名必须是一个标识符，12c中创建用户需要在用户名前加c## password:用户密码，必须是一个标识符，且不区分大小写 default TABLESPACE或default TABLESPACE为用户确定默认表空间或临时表空间

例：
--创建用户，这个用户60m的表空间
SQL>create user haha identified by 123
   >default tablespace haha_test
   >temporary tablespace temp --临时表空间
   >quota unlimited on to haha --无底线
   >quota 10m on tp_bak --增长10m
   >password expire --设置口令;
   
--查询表空间
SQL>  select * from dba_users where username = 'HAHA'

--查看表空间限额
SQL>select * from dba_ts_quotas where username ='HAHA';

--更改表空间限额 60m
SQL>alter user haha quota 10m on haha_test;

（2）用户授权

System用户
--授权  connect:登录 resource：操作
SQL>grant connect,resource to haha;
--撤销授权
SQL>revoke connect,resource to haha;
--使用haha查看scott用户下的表
SQL>grant select on scott.emp to haha; --查询
SQL>grant update on scott.emp to haha; --修改
SQL>select * from scott.emp for update;

七.序列

序列是用来生成唯一、连续的整数的数据库对象。

序列通常用来自动生成主键或唯一键的值。

序列可以按升序、降序排列

1.创建序列

语法：
	create sequence sequence_name
		[start with integer]
		[increment by integer]
		[maxvalue integer|nomaxvalue]
		[minvalue integer|nominvalue]
		[cycle|nocycle]
		[cache integer|nocache]
解释：
1.start with：指定要生成的第一个序列，对于升序序列，默认值为序列的最小值，降序反之。
2.increment by：用于指定序列之间的间隔。默认1，升降序通过正负设置。
3.maxvalue：指定序列的最大值。
4.nomaxvalue：设置之后，升序的最大值为10的27次方，降序的最大值为-1.
5.minvalue：指定序列的最小值。
6.nominvalue：设置之后，升序的最小值为1，降序的最小值为-10的26次方。
7.cycle：指定序列在达到最大值或最小值后，将继续从头开始生成值。
8.nocycle：指定序列在达到最大值或最小值后，将停止生成。默认
9.cache：使用cache选项可以预先分配一组***，并将其保留在内存中，这样就可以更快访问。当用完缓存中的所有***时，Oracle将生成另一组数值，并将其保留在缓存中。
10.nocache：使用nocache：与上反之。

案例：

SQL> create table toy(
  2  toyid number not null,
  3  toyname varchar2(20)
  4  )；

#创建序列
SQL>create sequence seq
	start with 10
	increment by1
	maxvalue 2000
	nocycle
	cache 30;

2.访问序列

创建序列了之后，可以通过nextval和currval伪列来访问该序列。

1.nextval：创建序列后第一次使用nextval时，将返回该序列的初始值。以后再引用nextval时，将使用increment by子句来增加序列值，并返回这个新值。

2.currval：返回序列的当前值，即最后一次引用nextval时返回的值

案例：

#插入数据
SQL>insert into toys(toyid,toyname) values(seq.nextval,'AA');

#查看序列的当前值
SQL>select seq.currval from dual;

3.更改序列

alter sequence命令用于修改序列定义。

1.设置或删除minvalue或maxvalue

2.修改增量值

3.修改缓存中***的数目

语法：

SQL>alter sequence [schema.]sequence_name
	[increment by integer]
	[maxvalue integer|nomaxvalue]
	[minvalue integer|nominvalue]
	[cycle|nocycle]
	[cache integer|nocache]
注意：不能修改start with的值。

4.删除序列

drop sequence命令用于删除序列。还可以使用重新开始序列，方法是先删除序列，然后再重新创建该序列。

语法：

SQL>drop sequence [schema.]sequence_name;

案例：

SQL>drop sequence seq;

5.使用序列

可以使用序列设置Oracle的主关键字，所得的值为从定的起点开始的一系列整数值。

序列所生成的数字只能保证在单个实例里是唯一的，不适合将其作为并行或远程环境里的关键字。

还可以使用SYS_GUID函数生成32位的唯一编码作为主键。它源自不需要对数据库进行访问的时间戳和机器标识符，这会保证创建的标识符在每个数据库里都是唯一的。

案例:

SQL>select SYS_GUID from dual;

八.同义词

1.用途

1.简化SQL

2.隐藏对象的名称和所有者

3.为分布式数据库的远程访问提供了位置透明性

4.提供对象的公共访问

2.同义词分类

同义词分私有同义词和公有同义词

1.私有同义词

私有同义词只能被当前模式的用户访问，且私有同义词名称不可与当前对象的名称相同。

要在当前模式下创建私有同义词，用户必须拥有create synonym系统权限。

要在其他用户模式创建同义词，用户必须拥有create any synonym系统权限

语法：

SQL>create [or replace] synonym [schema.]synon_name
	for [schema.]object_name;

解释：
1.or replace：表示在同义词存在的情况下替换该同义词
2.synonym_name：表示要创建的同义词的名称
3.object_name：指定要为之创建同义词的对象的名称

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

--获得访问A_hr模式下的employ表的权限
SQL>grant select on A_hr.employee for A_oe

--创建同义词SY_EMP   A_oe用户
SQL>create synonym SY_EMP for A_hr.employee

--访问同义词
SQL>select * from SY_EMP;

--注意:在创建同义词的时候，如用户无创建同义词的权限，需要先赋予权限
SQL>grant create synonym to A_oe;

2.公有同义词

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

语法：

SQL>grant [or replace] public synonym for [schema.]object_name;

案例：

--在A_hr模式下创建公有同义词public_emp作为A_hr用户employee的表的别名
SQL>grant public synonym public_emp for employee;
--在A_oe模式下访问公有访问同义词
SQL>select * from public_emp;

3.删除同义词

语法：

SQL>drop [public] synonym [schema.]synonym_name;

案例:

--删除私有同义词
SQL>drop synonym A_oe.SY_EMP;

--删除公有同义词
SQL>drop public synonym A_hr.public_emp;

--在此之前，用户需要拥有grant drop [public] synonym系统权限
SQL>grant drop [public] synonym to 用户

4.两者区别

1.私有同义词只能在当前模式下访问，且不能与当前模式的对象同名。

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

注意：对象与私有同义词不能同名；对象和公有同义词同名时，数据库优先选择对象作为目标，私有同义词和公有同义词同名时，数据库优先选择私有同义词作为目标。

九.索引

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

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

1.B树索引

B树索引通常也称为标准索引。索引的顶部为根，其中包含指向索引中下一级的项。下一级为分支块，分支块又指向索引中下一级的块。最低一级为叶节点。叶块为双向链接，有助于按关键字的升序和降序扫描索引。

语法：

SQL>create [unique] index index_name on table_name(column_list)
	[tablespace tablespace_name];
解释：
1.unique：指定唯一索引，默认非索引
2.index_name：指所创建索引的名称
3.table_name：指为之创建索引的表名。
4.column_list：在其上创建索引的列名的列表，可以基于多列创建索引，列之间用逗号分隔
5.tablespace_name：为索引指定表空间

2.唯一索引和非唯一索引

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

2.非唯一索引：单个关键字可以有多个与其关联的行

3.反向键索引

与常规B树索引相反，反向键索引在保持列顺序的同时反转索引列的字节。反向键索引通过反转索引键的数据值来实现。其优点是对于连续增长的索引列，反转索引列可以将索引数据分散在多个索引块之间，减少I/O瓶颈的发生。

案例:

#为员工编号empno列创建反向键索引
SQL>create index idnex_reverse_empno on employee(empno) reverse;

4.位图索引

其优点在于最适合低基数列（即该列的值是有限的，理论上不会是无穷大）。

优点：

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

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

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

位图索引不应当频繁发生insert、update、detele操作上。这些DML操作在性能方面的代价高。

位图索引最适合于数据仓库和决策支持系统

案例：

--在员工表中，为工种（job）列创建索引
SQL>create bitmap index index_bit_job on employee(job);

5.其他索引

1.组合索引：在表内多列上创建。索引中的列不必与表中的列顺序一致，也不必相互邻接。

2.基于函数的索引：若使用的函数或表达式涉及正在建立索引的表中的一列或多列，则创建基于函数的索引。可以将基于函数的索引创建为B树或位图索引。

案例:

#在员工（employee）表中，为员工名称（ename）列创建大写函数索引
SQL>create index index_name on employee(UPPER(ename));

6.删除索引

1.drop index 语句用于删除索引

--删除员工（employee）表中的index_bit_job位图索引
SQL>drop index index_bit_job

2.何时删除索引

1.应用程序不再需要索引

2.执行量加载前。为了提高性能，更加有效的使用索引空间

3.索引已损坏

7.重建索引

1.alter index...rebuild语句用于重建索引

#将反向键索引更改为正常的B树索引
SQL>alter index index_reverse_empno rebuild noreverse;

2.何时使用重建索引

1.用户表被移动到新的表空间后，表上的索引不是自动转移，此时需将索引移到指定表空间。
SQL>alter index index_name rebuild tablespace tablespace_name;
2.索引中包含很多已删除的项。对表进行频繁删除，造成索引空间浪费，可以重建索引。

3.需要将现有的正常索引转换成反向键索引

8.创建索引原则：

1.表中导入数据后再创建索引，否则每次表中插入数据时都必须更新索引

2.在适当的表和字段上创建索引

如果经常检索的数据少于表中的15%，需要创建索引

限制表中索引的数目

3.索引越多，在修改表时对索引做出修改的工作量越大

十.分区表

Oracle允许用户把一个表中的所有行分为几个部分，并将这些部分存储在不同的位置。被分区的表称为分区表，分成的每个部分称为一个分区。

对于大批量的表，分区很好用，其优点：

1.提高查询表的性能，只需查询表中的特定分区

2.表更容易管理，按分区查询和删除更容易

3.便于备份和恢复，可以独立备份和恢复文件

4.提高数据安全性，将不同的分区分布在不同的磁盘，可以减小所有分区的数据同时损坏的可能性

符合以下条件就可以建分区表

1.数据量大于2GB

2.已有的数据和新添的数据有明显的界限划分

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

1.分区表的分类

Oracle提供分区方法有以下几种，范围分区、列表分区、散列分区、复合分区、（间隔分区和虚拟列分区-->11g）等

(1)范围分区

以列的值的范围作为分区的划分条件，将记录存放到列值所在的range分区中

语法：

SQL>PARTITION BY RANGE (column_name)
	(
  		PARTITION part1 VALUE LESS THAN (range1),
 	 	PARTITION part2 VALUE LESS THAN (range2),
  		...
  		[PARTITION partN VALUE LESS THAN (MAXVALUE)]
	);

案例：

--创建表sales1
SQL>CREATE TABLE SALES1
	(
   		SALES_ID NUMBER,
   		PRODUCT_ID VARCHAR2(5),
   		SALES_DATE DATE NOT NULL
	)
	
--插入数据
SQL>insert into sales1 values(2000,'1',to_date('2013-05-16','yyyy-mm-dd'));
SQL>insert into sales1 values(2000,'2',to_date('201-11-16','yyyy-mm-dd'));

--进行分区
SQL>PARTITION BY RANGE (SALES_DATE)
	(
  	PARTITION P1 VALUES LESS THAN (to_date('2013-04-1', 'yyyy-mm-dd')),1900~2013-04-1
 	PARTITION P2 VALUES LESS THAN (to_date('2013-07-1', 'yyyy-mm-dd')),13-4-1~13-7-1
  	PARTITION P3 VALUES LESS THAN (to_date('2013-10-1', 'yyyy-mm-dd')),13-7-1~13-10-1
  	PARTITION P4 VALUES LESS THAN (to_date('2014-01-1', 'yyyy-mm-dd')),13-10-1~14-1-1
   	PARTITION P5 VALUES LESS THAN (maxvalue)
	);
--查询P2范围的数据
SQL>select * from sales1 partition(P2);   --结果为插入的第一条数据

--删除P2范围的数据
SQL>delete  from sales1 partition(P4);    --结果删除了插入的第二条数据

(2)间隔分区

间隔分区是11g版本新引入的分区方法，是范围分区的一种增强功能，可以实现范围分区的自动优化。

优点：在不需要创建表时就将所有分区划分清除。间隔分区随着数据的增加会划分更多的分区，并自动创建新的分区。

语法：

SQL>PARTITION BY RANGE(column_name)
		INTERVAL(NUMTOYMINTERVAL(n, 'interval_unit'))
		(PARTITION P1 VALUES LESS THAN (range1));
解释：
1.INTERVAL代表“间隔”，按照后面括号中的定义间隔添加分区
2.NUMTOYMINTERVAL(n, 'interval_unit') 函数
		将n转换成interval_unit所指定的值
		interval_unit可以为: YEAR, MONTH

1.创建间隔分区表：

--创建间隔分区表
CREATE TABLE SALES2
(
   SALES_ID NUMBER,
   PRODUCT_ID VARCHAR2(5),
   SALES_DATE DATE NOT NULL
)
		PARTITION BY RANGE(SALES_DATE)
		INTERVAL(NUMTOYMINTERVAL(3,'MONTH'))
		(PARTITION P1 VALUES LESS THAN (to_date('2013-04-1','yyyy/mm/dd')));
--插入数据
	INSERT INTO sales2 VALUES (1,'a',to_date('2013-08-1'),10,'1');
--获得分区情况
	SELECT table_name,partition_name 
   	  FROM user_tab_partitions 
	 WHERE table_name=UPPER('sales2');
--查询输出结果，系统自动根据输入数据情况创建新分区“SYS_P82”
   	TABLE_NAME	PARTITION_NAME
	----------------------------
	SALES2	     P1
	SALES2	     SYS_P82
--查询分区数据
	SELECT * FROM sales2 PARTITION(sys_P82);

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

/*准备工作*/
--1.创建普通SALES表
CREATE TABLE SALES
(
   SALES_ID NUMBER,
   PRODUCT_ID VARCHAR2(5),
   SALES_DATE DATE NOT NULL
);

--2.自行向SALES表插入数据

/*实施步骤*/
--1.创建间隔分区表SALES3
CREATE TABLE SALES3
		PARTITION BY RANGE(SALES_DATE)
		INTERVAL(NUMTOYMINTERVAL(3,'MONTH'))
		(PARTITION P1 VALUES LESS THAN (to_date('2013-04-1','yyyy/mm/dd')))
        AS SELECT * FROM SALES; --SALES表为已经创建的表

--2.查询分区情况
	SELECT table_name,partition_name 
   	  FROM user_tab_partitions 
	 WHERE table_name=UPPER('sales3');
--3.查询某一分区数据
--4.自行向SALES3表插入数据
--5.再次查询某一分区数据

注意：

1.字符串大小敏感

Oracle中存在大小写敏感，mysql不存在

2.日期格式敏感

---修改日期格式

sql>alter session set NLS_DATE_FORMAT ='yyyy-mm-dd';

一.Oracle基础知识

1.Oracle简介

Oracle是一个数据库管理系统，是Oracle公司的核心产品。

Oracle数据库的主要特点如下：

>支持多用户，大事务量的事务处理
	>
	>在保持数据安全性和完整性方面性能优越
	>
	>支持分布式数据处理
	>
	>具有可移植性。

12c增加了big data、data optimization、manageability、high availability等一些新功能。

2.Oracle基本概念

Oracle_基础

1.数据库

>这里数据库不是通常情况下我们所说的数据库，而是Oracle的一个企业名词。它是磁盘上存储的数据的集合，在物理上表现为数据文件、日志文件和控制文件等，在逻辑上以表空间形式存在。使用时，必须首先创建数据库，然后使用。

2.全局数据库名

全局数据库名是用于区分一个数据库的标识，在安装数据库、创建数据库、创建控制文、修改数据库结构，利用RMAN备份时都需要使用。它由数据库名和域名构成的，类似网络中的域名，使数据库的命名在整个网络环境中唯一。

3.数据库实例

每个启动的数据库都对应一个数据库实例，由这个实例来访问数据库中数据。如果把数据库简单的理解为磁盘上的文件，具有永久性，则数据库实例就是通过内存共享运行状态的一组服务器后台进程。

4.表空间

每个Oracle数据库都是由若干个表空间构成的，用户在数据库中建立的所有内容都被存储到表空间。一个表空间可以由多个数据文件组成，但一个数据文件只能属于一个空间。与数据文件这种物理结构相比，表空间属于数据库的逻辑结构。

5.数据文件

数据文件的扩展名是：dbf，是用于存储数据库数据的文件

一个数据文件中可能存储很多个表的数据，而一个表的数据也可能存放在多个数据文件中，即数据库名和数据文件不存在一对一的关系。

6.控制文件

控制文件的扩展名是：ctl，是一个二进制文件。控制文件中存储的信息很多，其中包括数据文件和日志文件的名称和位置。控制文件是数据库启动及运行所必需的文件。当Oracle读写数据时，要根据控制文件的信息查找数据文件。

7.日志文件

日志文件的扩展名是.log，它记录了数据的所有更改信息，并提供了一种数据恢复机制，确保在系统崩溃或其他意外出现意外出现后重新恢复数据库。

在Oracle数据库中，日志文件是成组使用的，每个日志文件组成可以有一个或多个日志文件。在工作过程中，多个日志文件组之间循环使用，当一个日志文件组写满后，会转向下一个日志文件组。

8.模式和模式对象

模式是数据库对象（如表、索引等，也称模式对象）的集合。Oracle会为每个数据库用户创建一个模式，此模式为当前用户所拥有，和用户具有相同的名称。

3.数据库服务简介

共有7个服务，这七个服务的含义分别为：

Oracle ORCL VSS Writer Service： Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service）能够让存储基础设备（比如磁盘，阵列等）创建高保真的时间点映像，即映射拷贝（shadow copy）。它可以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。（非必须启动）

OracleDBConsoleorcl：Oracle数据库控制台服务，orcl是Oracle的实例标识，默认的实例为orcl。在运行Enterprise Manager（企业管理器OEM）的时候，需要启动这个服务。（非必须启动）

OracleJobSchedulerORCL： Oracle作业调度（定时器）服务，ORCL是Oracle实例标识。（非必须启动）

OracleMTSRecoveryService：服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。（非必须启动）

OracleOraDb11g_home1ClrAgent： Oracle数据库 .NET扩展服务的一部分。（非必须启动）

OracleOraDb11g_home1TNSListener：监听器服务，服务只有在数据库需要远程访问的时候才需要。（非必须启动，下面会有详细详解）。

OracleServiceORCL：数据库服务(数据库实例)，是Oracle核心服务该服务，是数据库启动的基础，只有该服务启动，Oracle数据库才能正常启动。(必须启动)

4.连接数据库

1.SQL*Plus工具

Oracle的SQLPlus是与Oracle数据库进行交互的客户端工具。在SQL*Plus中，可以运行SQLPlus命令与SQL语句

SQLPlus命令(win+R运行 sqlplus /nolog)

#登录系统管理员
conn / as sysdba;

#开始记录（结束才能保存）
spool d:\XX.txt

#记录结束
spool off;

#清屏命令
host cls 
clear screen
clear SCR

#显示行宽
show linesize

#设置行宽
set linesize 120

#设置列宽（a代表一个字符,8代表有8个a,9代表一个数字）
col 字段名 for a8
col 字段名 for 9999

#ed写命令执行文件   /执行命令文件
ed
/

#错误修改
SQL> select empno,ename,sal
  2  form emp;
form emp
     *
第 2 行出现错误: 
ORA-00923: 未找到要求的 FROM 关键字 

SQL> 2
  2* form emp
SQL> c/form/from
  2* from emp
SQL> /

2.PL/SQL Developer工具

连接：

Normal：普通用户

sysOper：数据库操作员--主要包括打开、关闭、备份、恢复数据库服务器等

sysDBA：数据库管理员--主要包括打开数据库服务器、关闭数据库服务器、备份数据库服务器、恢复数据库服务器、日志归档、管理功能、创建数据库等。

二.Oracle数据类型

1.字符数据类型

字符类型主要包括VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2等。

（1）VARCHAR2类型：用于存储可变长度的字符串，其语法格式VARCHAR2(maxlength)，参数maxlength的最大值可以是32767字节。

注意：数据库类型的VARCHAR2的最大长度是4000字节，所以一个长度大于4000字节的PL/SQL类型VARCHAR2变量不可以赋值给数据库中的一个VARCHAR2变量，而只能赋值给LONG类型的数据库变量。

（2）CHAR类型：CHAR类型表示指定长度的字符串，其语法格式CHAR(maxlength)。

（3）LONG类型：LONG类型表示一个可变的字符串，最大长度是32767字节，而数据库类型的LONG最大长度可达2GB，所以任何字符串变量都可以赋值给它（LONG类型不建议使用，而建议使用CLOB）。

（4）NCHAR数据类型：即国家字符集，使用方法和CHAR相同，如果开发的项目需要国际化，那么数据类型选择NCHAR数据类型。NCHAR和CHAR的区别在于NCHAR用来存储Unicode字符集类型，即双字符集类型。例如：我们定义CHAR(11)和NCHAR(11)类型的两个字段，字段长度为1字节和1个字符（2个字节），分别插入'a'和'a'是没有问题的，但是占用的字节数分别是1和。如果占用的字节数分别是1和2。如果分别插入‘的’和‘的’，则前者无法正常插入，而后者可以。

（5）NVARCHAR2与NCHAR类似，在使用上，NVARCHAR2存储需要国际化的可变长度字符串

2.数值数据类型

#p为精度，表示有效数字，在1~38之间有效数字
#s为范围，表示小数点右边数字的位数，它在-84~+127之间
#定位数
NUMBER(p)

#浮点数
NUMBER(p,s)
NUMBER(p=38,s=0)

3.日期时间数据类型

日期时间数据类型用于存储日期值和时间值

（1）DATE数据类型

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

（2）TIMESTAMP数据类型

TIMESTAMP数据类型用于存储日期的年、月、日，以及时间的小时、分和秒，其中秒值精确到小数点后6位，该数据类型同时包含时区信息。SYSTIMESTAMP函数的功能是返回当前日期，时间和时区。

4.LOB数据类型

（1）CLOB

CLOB（CHaracter LOB，字符LOB）能够存储大量字符类型。该数据类型可以存储单字节字符数据和多字节字符数据，主要用于存储非结构化的XML文档，如新闻、内容介绍等含大量文字内容的文档。

（2）BLOB

BLOB（BinaryLOB1，二进制LOB）可以存储较大的二进制对象，如图像、视频剪辑和声音剪辑等

（3）BFILE

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

（4）NCLOB

NCLOB数据类型用于存储大的NCHAR字符数据。NCLOB数据类型同时支持固定宽度字符和可变宽度字符（Unicode字符数据）。大字符对象的大小不大于4GB。NCLOB类型的使用方法与CLOB类型。

Oracle中的表可以有多个LOB列，每个LOB列可以是不同的LOB类型。

5.特殊数据类型

%TYPE类型

使用%TYPE关键字可以声明一个与指定列名称相同的数据类型，它通常紧跟在指定列名的后面。

RECORD类型

单词RECORD有“记录”之意，因此RECORD类型也称作“记录类型”，使用该类型的变量可以存储由多个列值组成的一行数据。在声明记录类型变量之前，首先需要定义记录类型，然后才可以声明记录类型的变量。记录类型是一种结构化的数据类型，它使用type语句进行定义，在记录类型的定义结构中包含成员变量及其数据类型，其语法格式如下：

type record_type is record ( var_member1 data_type [not null][:=default_value], … var_membern data_type [not null][:=default_value] ) record_type：表示要定义的记录类型名称。

var_member1：表示该记录类型的成员变量名称。

data_type：表示成员变量的数据类型。

3 .%ROWTYPE类型

%ROWTYPE类型的变量结合了%TYPE类型和RECORD类型变量的优点，它可以根据数据表中行的结构定义一种特殊的数据类型，用来存储从数据表中检索到的一行数据。它的语法形式很简单，如下所示：

rowVar_name table_name%rowtype; rowVar_name：表示可以存储一行数据的变量名。

table_name：指定的表明。

6.伪列

伪列就像Oracle中的一个表列，但实际上它并未存储在表中。伪列可以从表中查询，但是不能插入、更新和删除它们的值。这里主要讲解ROWID和ROWNUM。

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

ROWID伪列有以下重要的用途。
1.能以最快的方式访问表中的一行

2.能显示表的行是如何存储的

3.可以作为表中行的唯一标识

如：可以使用SELECT语句查询ROWID值
SQL>SELECT ROWID,eName
	FROM SCOTT.emp
	WHERE eName="SMITH"
2.ROWNUM

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

如：从emp表中提取10条记录并显示序号
SQL>SELECT emp.*,ROWNUM
	FROM SCOTT.emp
	WHERE ROWNUM<11
ROWNUM查询结果

查询条件结果

ROWNUM对于等于某值的查询条件等于1，可以查到第一条数据，但等于2则查不到

ROWNUM对于大于某值的查询条件查不到，原因是ROWNUM总是从第一个开始（>2)

ROWNUM对于大于某值的查询条件小于11，得到10条记录

查询条件	结果
ROWNUM对于等于某值的查询条件	等于1，可以查到第一条数据，但等于2则查不到
ROWNUM对于大于某值的查询条件	查不到，原因是ROWNUM总是从第一个开始（>2)
ROWNUM对于大于某值的查询条件	小于11，得到10条记录

伪表 dual

SQL>select 5+3 from dual； --作用：不存储主题数据，主要功能是用来做查询

三.SQL语言简介

1.数据定义语言DDL

CREATE（创建）、ALTER（更改）、TRUNCATE（截断）、DROP（删除）

（1）CREATE TABLE命令（省略）

（2）TRUNCATE TABLE命令
语法：
SQL>TRUNCATE TABLE <tablename>

##2.数据操纵语言DML

INSERT（插入）、SELECT（查询）、DELETE（删除）、UPDATE（更新）

STUNO	STUNAME	STUAGE	STUSEAT
1	张三	18	1
2	李四	20	2
3	王五	15	3
4	张三	18	4
5	张三	20	5

1.从语法的角度介绍DML语言操作

（1）选择无重复的行

要防止选择重复的行，可以在SELECT命令中包含DISTICT子句

（2）带条件和排序的SELECT命令

要从表中选择特定的行，可以在SELECT命令中包含WHERE子句。ORDER by ASC/DESC

（3）使用列别名

列别名是为列表达式提供的另一个名称，位于列表达式后面，并显示在列表达式标题中。列表达式不影响列的实际名称 as

（4）利用现有的表创建新表
SQL>CREATE TABLE <newtable>
	AS
	SELECT{* | column(s)}
	FROM <oldtable> [WHERE <condition>]
	
--只复制表结构时，可以加上WHERE 1=2
2.从使用技巧的角度介绍DML语言操作

（1）查看表中行数
SQL>SELECT COUNT(*) FROM stuinfo;  --效率低
SQL>SELECT COUNT(1) FROM stuinfo;  --效率高
（2）取出stuName，stuAge
SQL> SELECT stuName,stuAge 
          FROM stuInfo
          GROUP BY stuName,stuAge 
          HAVING(COUNT(stuName||stuAge) <2);
（3）删除stuName，stuName列重复的行（只保留一行）
SQL>DELETE 
          FROM stuInfo 
          --4.获得所有重复记录的ROWID
          WHERE ROWID NOT IN( 
              			--1.查找重复的记录，只保留一条
          		           SELECT MAX(ROWID) 
                   	     FROM stuInfo
                  	     GROUP BY stuName,stuAge 
                             HAVING(COUNT(stuAge||stuAge)>1)
              			--3.合并结果集，获得所有符合条件且不重复的记录
		                 UNION
              			--2.查找不重复的记录
		                 SELECT max(ROWID)
            		     FROM Stuinfo
		                 GROUP BY stuName,stuAge 
            		     HAVING(COUNT(stuAge||stuAge)=1)     

3.事务控制语言TCL

COMMIT（提交）、SAVEPOINT（保存点）、ROLLBACK（回滚）

组成

COMMIT ：提交事务，即把事务中对数据库的修改进行永久保存

ROLLBACK ：回滚事务，即取消对数据库所作的任何修改

SAVEPOINT ：在事务中创建存储点

ROLLBACK TO <SavePoint_Name>：将事务回滚到存储点
SQL>INSERT INTO dept VALUES(50,'a',null);
SQL>INSERT INTO dept VALUES(60,'b',null);
SQL>SAVEPOINT a;
SQL>INSERT INTO dept VALUES(70,'c',null);
SQL>ROLLBACK TO SAVEPOINT a;--回滚到a存储点
SQL>COMMIT;--执行commit，不包含第四行数据
SQL>SELECT * FROM dept;

4.数据控制语言DCL

GRANT（授权）、REVOKE（回收）

数据控制语言为用户提供权限控制命令

四.SQL操作符

##1.算术操作符

+、-、*、/

##2.比较运算符

=、！=、>、<、>=、<=、BETWEEN....AND、IN、LIKE、IS NULL

SQL>select * from emp where id is null;  ---null 永远！=null
SQL>select * from emp where id=null;--错误XXX

between :含有边界小值在前,大值在后

SQL>select * from emp where sal between 1000 and 2000;

in在集合中

SQL>select * from emp where deptno not in(10,20)

5.模糊查询方式

通配符 _任意一个字母

%:任意0-n个字母

#若使用模糊查询字段中值，值中带下划线，需要用 '\' escape '\'转义
SQL>select * from emp where ename like '%\_%' escape '\'

3.逻辑运算符

AND、OR、NOT

4.集合操作符

集合操作符将两个查询的结果组合成一个结果集。可以在SQL中使用下面的结合操作符来组合多个查询中的行

UNION（联合）：用来求两个集合的并集，并去掉重复值

UNION ALL（联合所有）：用来求两个集合的并集

INTERSECT（交集）：用来求两个集合的交集，并去掉重复值

MINUS（减集）：用来求在第一个集合中存在，而在第二个集合中不存在的记录，并去掉重复值

使用集合操作符连接起来的SELECT语句中列遵循以下规则：

通过集合操作符连接的各个查询具有相同的列数，而且对应列的数据类型必须兼容

这种查询不应含有LONG类型的列。

5.连接操作符

连接操作符（||）用于将两个或多个字符串合并成一个字符串，或者将一个字符串与一个数值合并在一起。

SQL>select a||'_'||b from emp;
SQL>select concat(EMPNO,ENAME) from emp;

优先级

运算符	级别
算术运算符(即‘+',‘-'，‘*',‘/')	1
连接运算符（即‘\|\|'）	2
比较运算符（即‘>'，‘>='，‘<'，‘<='，‘<>'）	3
Is [not] null,[not] like,[not] in	4
[not] between-and	5
not	6
and	7
or	8

五.SQL函数

Oracle SQL提供了用于执行特定操作的专用函数。（划分为3个）

单行函数：日期函数、数字函数、字符函数、转换函数、其他函数

聚合函数（分组函数）

分析函数

##1.单行函数:

（1）操作数据对象（2）接受参数返回一个结果（3）只对一行进行变换（4）每行返回一个结果（5）可以转换数据类型（6）可以嵌套（7）参数可以是一列或一个值

（1）转换函数

将值从一种数据类型转换为另一种数据类型。

常见转换函数

函数功能

TO_CHAR() 转换成字符串类型

TO_DATE() 转换成日期类型

TO_NUMBER 转换成数值类型

函数	功能
TO_CHAR()	转换成字符串类型
TO_DATE()	转换成日期类型
TO_NUMBER	转换成数值类型

#to_char()
SQL>select to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') from dual;--2018-10-15 07:10:00
SQL>select to_char(sal,'L9,999.9')from emp; --L9,999,9 规定写法 ￥12542

#to_date()
SQL>select to_date('2018-07-10','yyyy-mm-dd') from dual;

#to_number()
SQL>select sqrt(to_number('100')) from dual; --sqrt() 求平方根

（2）分析函数

分析函数是对一组查询结果进行运算，然后获得结果。

类似聚合函数，区别在于分析函数每个组返回多行，聚合函数每组返回一行

语法：

函数名([参数]) OVER ( [分区子句]

[ 排序语句])

在语法中：

函数名表示分析函数的名称

参数表示函数需要传入的参数

分区子句（PARTITION BY）表示将查询结果分为不同的组，功能类型GROUP By语句，是分析函数工具的基础，默认将每个分区进行排序。

RANK、DENSE_RANK、ROW_NUMBER函数用于为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。

1.ROW_NUMBER函数

表示返回一个唯一的值，当遇到相同数据时，排名按照记录集中记录的顺序依次递增。

2.DENSE_ANK函数

表示返回一个唯一的值，当遇到相同数据时，所有相同数据的排名都是一样的。

3.RANK函数

表示返回一个唯一的值，当遇到相同的数据时，所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

（3）字符函数

大小写控制函数：lower、upper、initcap

SQL>select lower('Hello world')"小写",
   >upper('Hello world')"大写", 
   >initcap('hello')"首字母大写"
   >from dual;

字符控制函数：concat、substr、length、lengthb、instr、lpad、rpad、trim、replace

#concat 字符拼接
SQL>select concat('a','b') as "字符拼接" from dual;

#substr 字符截取
SQL>select substr('hello world',5) from dual;
SQL>select substr('hello world',5,8) from dual;
SQL>select substr('hello world',8,5) from dual;--以8开始截取，5自动省略

#length 长度(以字符)  lengthb (以字节)
SQL>select length('hello') from dual;
SQL>select lengthb('hello') from dual;

#instr(a,b) 在a中查找b的位置
SQL>select instr('hello','e') from dual;

#lpad左填充，rpad右填充  填充到10位，不够以*代替
SQL>select lpad('aa',10,'*') from dual;
SQL>select rpad('aa',10,'*') from dual;

#trim 去除字符
SQL>select trim('a' from 'abac') from dual;--去除abac中第一个a

#replace() 替换字符
SQL>select replace('abac','a','r') from dual;--替换abac中所有a字符为r

（4）数字函数

round、trunc、mod

#round 四舍五入
SQL>select round(15.659,2) from dual; --15.66   2代表小数点位数
SQL>select round(15.655,-1) from dual;--20   保留百位及以上位，个位以0替换

#trunc 截断
SQL>select trunc(15.655,2) from dual;--15.65
SQL>select trunc(15.655,-1) from dual;--10

#mod(a,b) a/b的余数
SQL>select mod(45,2) from dual;--1

（5）日期函数

#sysdate 返回当前时间
SQL> select sysdate from dual;

#昨天 今天 明天
SQL>select (sysdate-1)"昨天",sysdate "今天",(sysdate+1)"明天" from dual;

#add_months 添加月数
SQL>select add_months(sysdate,1) from dual;--1代表月数

#last_day 月的最后一天
SQL>select last_day(sysdate) from dual;

#next_day 下个星期几的日期
SQL>select next_day(sysdate,'星期一') from dual; --星期一是规定，星期日也是

#months_between 两个时间相差的月数
SQL>select months_between('第一个日期','第二个日期') from dual;

#round 日期四舍五入
SQL>select round(sysdate) from dual; --2018/10/16

（6）其他函数

常用的其他函数

函数功能

NVL(exp1,exp2) 如果exp1的值为NULL，返回exp2的值，否则返回exp1的值

NVL2(exp1,exp2,exp3) 如果exp1的值为NULL，则返回exp3的值，否则返回exp2的值

DECODE(value,if1,then1,if2,then2) 如果value的值为if1，则返回then1；以此类推；否则返回else的值

NULLIF(expr1, expr2) 如果两个表达式不相等，NULLIF 返回第一个 expression1 的值。如果两个表达式相等，NULLIF 返回空值NULL。

COALESCE(expr1, expr2) COALESCE返回参数列表中第一个非空表达式。必须指定最少两个参数。如果所有的参数都是null，则返回null。

函数	功能
NVL(exp1,exp2)	如果exp1的值为NULL，返回exp2的值，否则返回exp1的值
NVL2(exp1,exp2,exp3)	如果exp1的值为NULL，则返回exp3的值，否则返回exp2的值
DECODE(value,if1,then1,if2,then2)	如果value的值为if1，则返回then1；以此类推；否则返回else的值
NULLIF(expr1, expr2)	如果两个表达式不相等，NULLIF 返回第一个 expression1 的值。如果两个表达式相等，NULLIF 返回空值NULL。
COALESCE(expr1, expr2)	COALESCE返回参数列表中第一个非空表达式。必须指定最少两个参数。如果所有的参数都是null，则返回null。

#nvl
SQL>select nvl(null,1) from dual; --1

#nvl2
SQL>select nvl2(null,null,3) from dual; --3

#decode
SQL>select decode(1,3,'aa',1,'bb') from dual;--bb

#nullif
SQL>select nullif('a','aa') from dual;--null

#coalesce
SQL>select coalesce(null,3+2) from dual;--5

六.表空间和用户权限

##1.表空间

Oracle数据库包含逻辑结构和物理结构。

物理结构是指构成数据库的一组操作系统文件。

逻辑结构是指描述数据组织方式的一组逻辑概念及它们之间的关系。

表空间是逻辑结构的一个重要组件。表空间可以存放各种应用对象，如表、索引。

而每个表空间由一个或多个数据文件组成。

（1）表空间分类

类别说明

永久性表空间一般保存表、视图、过程和索引等的数据。SYSTEM、SYSAUX、USERS、EXAMPLE表空间是默认安装的

临时性表空间只用于保存系统中短期活动的数据，如排序数据等

撤销表空间用来帮助回退未提交的事务，已提交了的数据在这里是不可以恢复的。一般不需要建临时和撤销表空间，除非把它们转移到其他磁盘中以提高性能

（2）表空间的目的

使用表空间的目的为：

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

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

（3）创建表空间
语法：
SQL>create tablespacename tablename datafile 'filename' [size integer[K|M]]
	[autoextend [off|on]]
tablespacename：创建的表名称

datafile：指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔

filename：数据文件的路径和名称

size：文件大小

autoextend：子句用来启动或禁用数据文件的自动扩展，设置为ON 则空间使用完毕会扩展。

例：
#创建表空间     system用户
SQL> create tablespace haha_test datafile 'd:\data\haha.dbf' size 60m;
（4）查询表空间
SQL> select * from dba_data_files;
（5）修改表空间大小
SQL> alter database datafile 'd:\data\haha.dbf' resize 80m;
（6）给表空间添加数据文件
SQL> alter tablespace haha_test add datafile 'd:\data\haha1.dbf' size 80m    		autoextend on;
（7）删除表空间
#删除表空间名称
SQL> drop tablespace haha_test;
#删除表空间并且物理文件
SQL> drop tablespace haha_test
     include contents;

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

2.自定义用户管理

Oracle中默认三个用户Sys、System、Scott，其中Sys和System是系统用户

1.Sys用户

Sys用户是超级用户，数据库中的数据字典和视图都存储在SYS用户中。

数据字典存储了用来管理数据对象的所有信息，是Oracle数据库中非常重要的系统信息。

Sys用户主要用来维护系统信息和管理实例。

Sys用户只能以SYSOPER或SYSDBA角色登录

2.System用户

这是Oracle中默认的系统管理员，拥有DBA权限。该用户拥有Oracle管理工具使用的内部表和视图。通常System用户管理Oracle数据库的用户、权限和存储等。

不建议在System用户下创建用户表。

System用户不能以SYSOPER或SYSDBA角色登录系统，只能默认

3.Scott用户

这是Oracle数据库时创建的示例用户

在Oracle 12c中，scott用户被取消，需要自己创建
--登录系统用户
SQL>conn / as sysdab;

--删除已有的用户(可选) 
SQL>DROP USER c##scott cascade;

--新建用户，授予权限，连接
--用户名前加c##是12c中的新特性 
SQL>CREATE USER c##scott identified by 123456 ;           
SQL>GRANT CREATE SEQUENCE to c##scott ;
SQL>GRANT UNLIMITED TABLESPACE to c##scott ;
SQL>GRANT CREATE SESSION to c##scott ;
SQL>GRANT CREATE SYNONYM to c##scott ;
SQL>GRANT ALTER SESSION to c##scott;
SQL>GRANT CREATE DATABASE LINK to c##scott;
SQL>GRANT CREATE VIEW to c##scott;
SQL>GRANT CREATE TABLE to c##scott;
SQL>ALTER USER c##scott DEFAULT TABLESPACE USERS;
SQL>ALTER USER c##scott TEMPORARY TABLESPACE TEMP;
SQL>CONN c##scott/123456;

--修改密码
SQL>alter user 用户名 identified by 新密码;

--删除表空间
SQL>drop user 用户名称 cascade;
--删除表空间
SQL>drop tablespace 表空间名称 including contents and datafiles cascade constraint;

（1）创建用户并给用户分配表空间

语法：
create USER user
identified by password
[default TABLESPACE tablespace]
[default TABLESPACE tablespace]

user:用户名，用户名必须是一个标识符，12c中创建用户需要在用户名前加c## password:用户密码，必须是一个标识符，且不区分大小写 default TABLESPACE或default TABLESPACE为用户确定默认表空间或临时表空间

例：
--创建用户，这个用户60m的表空间
SQL>create user haha identified by 123
   >default tablespace haha_test
   >temporary tablespace temp --临时表空间
   >quota unlimited on to haha --无底线
   >quota 10m on tp_bak --增长10m
   >password expire --设置口令;
   
--查询表空间
SQL>  select * from dba_users where username = 'HAHA'

--查看表空间限额
SQL>select * from dba_ts_quotas where username ='HAHA';

--更改表空间限额 60m
SQL>alter user haha quota 10m on haha_test;

（2）用户授权

System用户
--授权  connect:登录 resource：操作
SQL>grant connect,resource to haha;
--撤销授权
SQL>revoke connect,resource to haha;
--使用haha查看scott用户下的表
SQL>grant select on scott.emp to haha; --查询
SQL>grant update on scott.emp to haha; --修改
SQL>select * from scott.emp for update;

七.序列

序列是用来生成唯一、连续的整数的数据库对象。

序列通常用来自动生成主键或唯一键的值。

序列可以按升序、降序排列

1.创建序列

语法：
	create sequence sequence_name
		[start with integer]
		[increment by integer]
		[maxvalue integer|nomaxvalue]
		[minvalue integer|nominvalue]
		[cycle|nocycle]
		[cache integer|nocache]
解释：
1.start with：指定要生成的第一个序列，对于升序序列，默认值为序列的最小值，降序反之。
2.increment by：用于指定序列之间的间隔。默认1，升降序通过正负设置。
3.maxvalue：指定序列的最大值。
4.nomaxvalue：设置之后，升序的最大值为10的27次方，降序的最大值为-1.
5.minvalue：指定序列的最小值。
6.nominvalue：设置之后，升序的最小值为1，降序的最小值为-10的26次方。
7.cycle：指定序列在达到最大值或最小值后，将继续从头开始生成值。
8.nocycle：指定序列在达到最大值或最小值后，将停止生成。默认
9.cache：使用cache选项可以预先分配一组***，并将其保留在内存中，这样就可以更快访问。当用完缓存中的所有***时，Oracle将生成另一组数值，并将其保留在缓存中。
10.nocache：使用nocache：与上反之。

案例：

SQL> create table toy(
  2  toyid number not null,
  3  toyname varchar2(20)
  4  )；

#创建序列
SQL>create sequence seq
	start with 10
	increment by1
	maxvalue 2000
	nocycle
	cache 30;

2.访问序列

创建序列了之后，可以通过nextval和currval伪列来访问该序列。

1.nextval：创建序列后第一次使用nextval时，将返回该序列的初始值。以后再引用nextval时，将使用increment by子句来增加序列值，并返回这个新值。

2.currval：返回序列的当前值，即最后一次引用nextval时返回的值

案例：

#插入数据
SQL>insert into toys(toyid,toyname) values(seq.nextval,'AA');

#查看序列的当前值
SQL>select seq.currval from dual;

3.更改序列

alter sequence命令用于修改序列定义。

1.设置或删除minvalue或maxvalue

2.修改增量值

3.修改缓存中***的数目

语法：

SQL>alter sequence [schema.]sequence_name
	[increment by integer]
	[maxvalue integer|nomaxvalue]
	[minvalue integer|nominvalue]
	[cycle|nocycle]
	[cache integer|nocache]
注意：不能修改start with的值。

4.删除序列

drop sequence命令用于删除序列。还可以使用重新开始序列，方法是先删除序列，然后再重新创建该序列。

语法：

SQL>drop sequence [schema.]sequence_name;

案例：

SQL>drop sequence seq;

5.使用序列

可以使用序列设置Oracle的主关键字，所得的值为从定的起点开始的一系列整数值。

序列所生成的数字只能保证在单个实例里是唯一的，不适合将其作为并行或远程环境里的关键字。

还可以使用SYS_GUID函数生成32位的唯一编码作为主键。它源自不需要对数据库进行访问的时间戳和机器标识符，这会保证创建的标识符在每个数据库里都是唯一的。

案例:

SQL>select SYS_GUID from dual;

八.同义词

1.用途

1.简化SQL

2.隐藏对象的名称和所有者

3.为分布式数据库的远程访问提供了位置透明性

4.提供对象的公共访问

2.同义词分类

同义词分私有同义词和公有同义词

1.私有同义词

私有同义词只能被当前模式的用户访问，且私有同义词名称不可与当前对象的名称相同。

要在当前模式下创建私有同义词，用户必须拥有create synonym系统权限。

要在其他用户模式创建同义词，用户必须拥有create any synonym系统权限

语法：

SQL>create [or replace] synonym [schema.]synon_name
	for [schema.]object_name;

解释：
1.or replace：表示在同义词存在的情况下替换该同义词
2.synonym_name：表示要创建的同义词的名称
3.object_name：指定要为之创建同义词的对象的名称

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

--获得访问A_hr模式下的employ表的权限
SQL>grant select on A_hr.employee for A_oe

--创建同义词SY_EMP   A_oe用户
SQL>create synonym SY_EMP for A_hr.employee

--访问同义词
SQL>select * from SY_EMP;

--注意:在创建同义词的时候，如用户无创建同义词的权限，需要先赋予权限
SQL>grant create synonym to A_oe;

2.公有同义词

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

语法：

SQL>grant [or replace] public synonym for [schema.]object_name;

案例：

--在A_hr模式下创建公有同义词public_emp作为A_hr用户employee的表的别名
SQL>grant public synonym public_emp for employee;
--在A_oe模式下访问公有访问同义词
SQL>select * from public_emp;

3.删除同义词

语法：

SQL>drop [public] synonym [schema.]synonym_name;

案例:

--删除私有同义词
SQL>drop synonym A_oe.SY_EMP;

--删除公有同义词
SQL>drop public synonym A_hr.public_emp;

--在此之前，用户需要拥有grant drop [public] synonym系统权限
SQL>grant drop [public] synonym to 用户

4.两者区别

1.私有同义词只能在当前模式下访问，且不能与当前模式的对象同名。

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

九.索引

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

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

1.B树索引

B树索引通常也称为标准索引。索引的顶部为根，其中包含指向索引中下一级的项。下一级为分支块，分支块又指向索引中下一级的块。最低一级为叶节点。叶块为双向链接，有助于按关键字的升序和降序扫描索引。

语法：

SQL>create [unique] index index_name on table_name(column_list)
	[tablespace tablespace_name];
解释：
1.unique：指定唯一索引，默认非索引
2.index_name：指所创建索引的名称
3.table_name：指为之创建索引的表名。
4.column_list：在其上创建索引的列名的列表，可以基于多列创建索引，列之间用逗号分隔
5.tablespace_name：为索引指定表空间

2.唯一索引和非唯一索引

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

2.非唯一索引：单个关键字可以有多个与其关联的行

3.反向键索引

与常规B树索引相反，反向键索引在保持列顺序的同时反转索引列的字节。反向键索引通过反转索引键的数据值来实现。其优点是对于连续增长的索引列，反转索引列可以将索引数据分散在多个索引块之间，减少I/O瓶颈的发生。

案例:

#为员工编号empno列创建反向键索引
SQL>create index idnex_reverse_empno on employee(empno) reverse;

4.位图索引

其优点在于最适合低基数列（即该列的值是有限的，理论上不会是无穷大）。

优点：

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

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

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

位图索引不应当频繁发生insert、update、detele操作上。这些DML操作在性能方面的代价高。

位图索引最适合于数据仓库和决策支持系统

案例：

--在员工表中，为工种（job）列创建索引
SQL>create bitmap index index_bit_job on employee(job);

5.其他索引

1.组合索引：在表内多列上创建。索引中的列不必与表中的列顺序一致，也不必相互邻接。

2.基于函数的索引：若使用的函数或表达式涉及正在建立索引的表中的一列或多列，则创建基于函数的索引。可以将基于函数的索引创建为B树或位图索引。

案例:

#在员工（employee）表中，为员工名称（ename）列创建大写函数索引
SQL>create index index_name on employee(UPPER(ename));

6.删除索引

1.drop index 语句用于删除索引

--删除员工（employee）表中的index_bit_job位图索引
SQL>drop index index_bit_job

2.何时删除索引

1.应用程序不再需要索引

2.执行量加载前。为了提高性能，更加有效的使用索引空间

3.索引已损坏

7.重建索引

1.alter index...rebuild语句用于重建索引

#将反向键索引更改为正常的B树索引
SQL>alter index index_reverse_empno rebuild noreverse;

2.何时使用重建索引

1.用户表被移动到新的表空间后，表上的索引不是自动转移，此时需将索引移到指定表空间。
SQL>alter index index_name rebuild tablespace tablespace_name;
2.索引中包含很多已删除的项。对表进行频繁删除，造成索引空间浪费，可以重建索引。

3.需要将现有的正常索引转换成反向键索引

8.创建索引原则：

1.表中导入数据后再创建索引，否则每次表中插入数据时都必须更新索引

2.在适当的表和字段上创建索引

如果经常检索的数据少于表中的15%，需要创建索引

限制表中索引的数目

3.索引越多，在修改表时对索引做出修改的工作量越大

十.分区表

Oracle允许用户把一个表中的所有行分为几个部分，并将这些部分存储在不同的位置。被分区的表称为分区表，分成的每个部分称为一个分区。

对于大批量的表，分区很好用，其优点：

1.提高查询表的性能，只需查询表中的特定分区

2.表更容易管理，按分区查询和删除更容易

3.便于备份和恢复，可以独立备份和恢复文件

4.提高数据安全性，将不同的分区分布在不同的磁盘，可以减小所有分区的数据同时损坏的可能性

符合以下条件就可以建分区表

1.数据量大于2GB

2.已有的数据和新添的数据有明显的界限划分

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

1.分区表的分类

Oracle提供分区方法有以下几种，范围分区、列表分区、散列分区、复合分区、（间隔分区和虚拟列分区-->11g）等

(1)范围分区

以列的值的范围作为分区的划分条件，将记录存放到列值所在的range分区中

语法：

SQL>PARTITION BY RANGE (column_name)
	(
  		PARTITION part1 VALUE LESS THAN (range1),
 	 	PARTITION part2 VALUE LESS THAN (range2),
  		...
  		[PARTITION partN VALUE LESS THAN (MAXVALUE)]
	);

案例：

--创建表sales1
SQL>CREATE TABLE SALES1
	(
   		SALES_ID NUMBER,
   		PRODUCT_ID VARCHAR2(5),
   		SALES_DATE DATE NOT NULL
	)
	
--插入数据
SQL>insert into sales1 values(2000,'1',to_date('2013-05-16','yyyy-mm-dd'));
SQL>insert into sales1 values(2000,'2',to_date('201-11-16','yyyy-mm-dd'));

--进行分区
SQL>PARTITION BY RANGE (SALES_DATE)
	(
  	PARTITION P1 VALUES LESS THAN (to_date('2013-04-1', 'yyyy-mm-dd')),1900~2013-04-1
 	PARTITION P2 VALUES LESS THAN (to_date('2013-07-1', 'yyyy-mm-dd')),13-4-1~13-7-1
  	PARTITION P3 VALUES LESS THAN (to_date('2013-10-1', 'yyyy-mm-dd')),13-7-1~13-10-1
  	PARTITION P4 VALUES LESS THAN (to_date('2014-01-1', 'yyyy-mm-dd')),13-10-1~14-1-1
   	PARTITION P5 VALUES LESS THAN (maxvalue)
	);
--查询P2范围的数据
SQL>select * from sales1 partition(P2);   --结果为插入的第一条数据

--删除P2范围的数据
SQL>delete  from sales1 partition(P4);    --结果删除了插入的第二条数据

(2)间隔分区

间隔分区是11g版本新引入的分区方法，是范围分区的一种增强功能，可以实现范围分区的自动优化。

优点：在不需要创建表时就将所有分区划分清除。间隔分区随着数据的增加会划分更多的分区，并自动创建新的分区。

语法：

SQL>PARTITION BY RANGE(column_name)
		INTERVAL(NUMTOYMINTERVAL(n, 'interval_unit'))
		(PARTITION P1 VALUES LESS THAN (range1));
解释：
1.INTERVAL代表“间隔”，按照后面括号中的定义间隔添加分区
2.NUMTOYMINTERVAL(n, 'interval_unit') 函数
		将n转换成interval_unit所指定的值
		interval_unit可以为: YEAR, MONTH

1.创建间隔分区表：

--创建间隔分区表
CREATE TABLE SALES2
(
   SALES_ID NUMBER,
   PRODUCT_ID VARCHAR2(5),
   SALES_DATE DATE NOT NULL
)
		PARTITION BY RANGE(SALES_DATE)
		INTERVAL(NUMTOYMINTERVAL(3,'MONTH'))
		(PARTITION P1 VALUES LESS THAN (to_date('2013-04-1','yyyy/mm/dd')));
--插入数据
	INSERT INTO sales2 VALUES (1,'a',to_date('2013-08-1'),10,'1');
--获得分区情况
	SELECT table_name,partition_name 
   	  FROM user_tab_partitions 
	 WHERE table_name=UPPER('sales2');
--查询输出结果，系统自动根据输入数据情况创建新分区“SYS_P82”
   	TABLE_NAME	PARTITION_NAME
	----------------------------
	SALES2	     P1
	SALES2	     SYS_P82
--查询分区数据
	SELECT * FROM sales2 PARTITION(sys_P82);

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

/*准备工作*/
--1.创建普通SALES表
CREATE TABLE SALES
(
   SALES_ID NUMBER,
   PRODUCT_ID VARCHAR2(5),
   SALES_DATE DATE NOT NULL
);

--2.自行向SALES表插入数据

/*实施步骤*/
--1.创建间隔分区表SALES3
CREATE TABLE SALES3
		PARTITION BY RANGE(SALES_DATE)
		INTERVAL(NUMTOYMINTERVAL(3,'MONTH'))
		(PARTITION P1 VALUES LESS THAN (to_date('2013-04-1','yyyy/mm/dd')))
        AS SELECT * FROM SALES; --SALES表为已经创建的表

--2.查询分区情况
	SELECT table_name,partition_name 
   	  FROM user_tab_partitions 
	 WHERE table_name=UPPER('sales3');
--3.查询某一分区数据
--4.自行向SALES3表插入数据
--5.再次查询某一分区数据

注意：

1.字符串大小敏感

Oracle中存在大小写敏感，mysql不存在

2.日期格式敏感

---修改日期格式

sql>alter session set NLS_DATE_FORMAT ='yyyy-mm-dd';

一.Oracle基础知识

1.Oracle简介

Oracle是一个数据库管理系统，是Oracle公司的核心产品。

Oracle数据库的主要特点如下：

    >支持多用户，大事务量的事务处理
    >
    >在保持数据安全性和完整性方面性能优越
    >
    >支持分布式数据处理
    >
    >具有可移植性。

12c增加了big data、data optimization、manageability、high availability等一些新功能。

2.Oracle基本概念

1.数据库

>这里数据库不是通常情况下我们所说的数据库，而是Oracle的一个企业名词。它是磁盘上存储的数据的集合，在物理上表现为数据文件、日志文件和控制文件等，在逻辑上以表空间形式存在。使用时，必须首先创建数据库，然后使用。

2.全局数据库名

全局数据库名是用于区分一个数据库的标识，在安装数据库、创建数据库、创建控制文、修改数据库结构，利用RMAN备份时都需要使用。它由数据库名和域名构成的，类似网络中的域名，使数据库的命名在整个网络环境中唯一。

3.数据库实例

每个启动的数据库都对应一个数据库实例，由这个实例来访问数据库中数据。如果把数据库简单的理解为磁盘上的文件，具有永久性，则数据库实例就是通过内存共享运行状态的一组服务器后台进程。

4.表空间

每个Oracle数据库都是由若干个表空间构成的，用户在数据库中建立的所有内容都被存储到表空间。一个表空间可以由多个数据文件组成，但一个数据文件只能属于一个空间。与数据文件这种物理结构相比，表空间属于数据库的逻辑结构。

5.数据文件

数据文件的扩展名是：dbf，是用于存储数据库数据的文件

一个数据文件中可能存储很多个表的数据，而一个表的数据也可能存放在多个数据文件中，即数据库名和数据文件不存在一对一的关系。

6.控制文件

控制文件的扩展名是：ctl，是一个二进制文件。控制文件中存储的信息很多，其中包括数据文件和日志文件的名称和位置。控制文件是数据库启动及运行所必需的文件。当Oracle读写数据时，要根据控制文件的信息查找数据文件。

7.日志文件

日志文件的扩展名是.log，它记录了数据的所有更改信息，并提供了一种数据恢复机制，确保在系统崩溃或其他意外出现意外出现后重新恢复数据库。

在Oracle数据库中，日志文件是成组使用的，每个日志文件组成可以有一个或多个日志文件。在工作过程中，多个日志文件组之间循环使用，当一个日志文件组写满后，会转向下一个日志文件组。

8.模式和模式对象

模式是数据库对象（如表、索引等，也称模式对象）的集合。Oracle会为每个数据库用户创建一个模式，此模式为当前用户所拥有，和用户具有相同的名称。

3.数据库服务简介

共有7个服务，这七个服务的含义分别为：

Oracle ORCL VSS Writer Service： Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service）能够让存储基础设备（比如磁盘，阵列等）创建高保真的时间点映像，即映射拷贝（shadow copy）。它可以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。（非必须启动）

OracleDBConsoleorcl：Oracle数据库控制台服务，orcl是Oracle的实例标识，默认的实例为orcl。在运行Enterprise Manager（企业管理器OEM）的时候，需要启动这个服务。（非必须启动）

OracleJobSchedulerORCL： Oracle作业调度（定时器）服务，ORCL是Oracle实例标识。（非必须启动）

OracleMTSRecoveryService：服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。（非必须启动）

OracleOraDb11g_home1ClrAgent： Oracle数据库 .NET扩展服务的一部分。（非必须启动）

OracleOraDb11g_home1TNSListener：监听器服务，服务只有在数据库需要远程访问的时候才需要。（非必须启动，下面会有详细详解）。

OracleServiceORCL：数据库服务(数据库实例)，是Oracle核心服务该服务，是数据库启动的基础，只有该服务启动，Oracle数据库才能正常启动。(必须启动)

4.连接数据库

1.SQL*Plus工具

Oracle的SQLPlus是与Oracle数据库进行交互的客户端工具。在SQL*Plus中，可以运行SQLPlus命令与SQL语句

SQLPlus命令(win+R运行 sqlplus /nolog)

#登录系统管理员
conn / as sysdba;

#开始记录（结束才能保存）
spool d:\XX.txt

#记录结束
spool off;

#清屏命令
host cls 
clear screen
clear SCR

#显示行宽
show linesize

#设置行宽
set linesize 120

#设置列宽（a代表一个字符,8代表有8个a,9代表一个数字）
col 字段名 for a8
col 字段名 for 9999

#ed写命令执行文件   /执行命令文件
ed
/

#错误修改
SQL> select empno,ename,sal
  2  form emp;
form emp
     *
第 2 行出现错误: 
ORA-00923: 未找到要求的 FROM 关键字 

SQL> 2
  2* form emp
SQL> c/form/from
  2* from emp
SQL> /

2.PL/SQL Developer工具

连接：

Normal：普通用户

sysOper：数据库操作员--主要包括打开、关闭、备份、恢复数据库服务器等

sysDBA：数据库管理员--主要包括打开数据库服务器、关闭数据库服务器、备份数据库服务器、恢复数据库服务器、日志归档、管理功能、创建数据库等。

二.Oracle数据类型

1.字符数据类型

字符类型主要包括VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2等。

（1）VARCHAR2类型：用于存储可变长度的字符串，其语法格式VARCHAR2(maxlength)，参数maxlength的最大值可以是32767字节。

注意：数据库类型的VARCHAR2的最大长度是4000字节，所以一个长度大于4000字节的PL/SQL类型VARCHAR2变量不可以赋值给数据库中的一个VARCHAR2变量，而只能赋值给LONG类型的数据库变量。

（2）CHAR类型：CHAR类型表示指定长度的字符串，其语法格式CHAR(maxlength)。

（3）LONG类型：LONG类型表示一个可变的字符串，最大长度是32767字节，而数据库类型的LONG最大长度可达2GB，所以任何字符串变量都可以赋值给它（LONG类型不建议使用，而建议使用CLOB）。

（4）NCHAR数据类型：即国家字符集，使用方法和CHAR相同，如果开发的项目需要国际化，那么数据类型选择NCHAR数据类型。NCHAR和CHAR的区别在于NCHAR用来存储Unicode字符集类型，即双字符集类型。例如：我们定义CHAR(11)和NCHAR(11)类型的两个字段，字段长度为1字节和1个字符（2个字节），分别插入'a'和'a'是没有问题的，但是占用的字节数分别是1和。如果占用的字节数分别是1和2。如果分别插入‘的’和‘的’，则前者无法正常插入，而后者可以。

（5）NVARCHAR2与NCHAR类似，在使用上，NVARCHAR2存储需要国际化的可变长度字符串

2.数值数据类型

#p为精度，表示有效数字，在1~38之间有效数字
#s为范围，表示小数点右边数字的位数，它在-84~+127之间
#定位数
NUMBER(p)

#浮点数
NUMBER(p,s)
NUMBER(p=38,s=0)

3.日期时间数据类型

日期时间数据类型用于存储日期值和时间值

（1）DATE数据类型

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

（2）TIMESTAMP数据类型

TIMESTAMP数据类型用于存储日期的年、月、日，以及时间的小时、分和秒，其中秒值精确到小数点后6位，该数据类型同时包含时区信息。SYSTIMESTAMP函数的功能是返回当前日期，时间和时区。

4.LOB数据类型

（1）CLOB

CLOB（CHaracter LOB，字符LOB）能够存储大量字符类型。该数据类型可以存储单字节字符数据和多字节字符数据，主要用于存储非结构化的XML文档，如新闻、内容介绍等含大量文字内容的文档。

（2）BLOB

BLOB（BinaryLOB1，二进制LOB）可以存储较大的二进制对象，如图像、视频剪辑和声音剪辑等

（3）BFILE

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

（4）NCLOB

NCLOB数据类型用于存储大的NCHAR字符数据。NCLOB数据类型同时支持固定宽度字符和可变宽度字符（Unicode字符数据）。大字符对象的大小不大于4GB。NCLOB类型的使用方法与CLOB类型。

Oracle中的表可以有多个LOB列，每个LOB列可以是不同的LOB类型。

5.特殊数据类型

%TYPE类型

使用%TYPE关键字可以声明一个与指定列名称相同的数据类型，它通常紧跟在指定列名的后面。

RECORD类型

单词RECORD有“记录”之意，因此RECORD类型也称作“记录类型”，使用该类型的变量可以存储由多个列值组成的一行数据。在声明记录类型变量之前，首先需要定义记录类型，然后才可以声明记录类型的变量。记录类型是一种结构化的数据类型，它使用type语句进行定义，在记录类型的定义结构中包含成员变量及其数据类型，其语法格式如下：

type record_type is record ( var_member1 data_type not null, … var_membern data_type not null ) record_type：表示要定义的记录类型名称。

var_member1：表示该记录类型的成员变量名称。

data_type：表示成员变量的数据类型。

3 .%ROWTYPE类型

%ROWTYPE类型的变量结合了%TYPE类型和RECORD类型变量的优点，它可以根据数据表中行的结构定义一种特殊的数据类型，用来存储从数据表中检索到的一行数据。它的语法形式很简单，如下所示：

rowVar_name table_name%rowtype; rowVar_name：表示可以存储一行数据的变量名。

table_name：指定的表明。

6.伪列

伪列就像Oracle中的一个表列，但实际上它并未存储在表中。伪列可以从表中查询，但是不能插入、更新和删除它们的值。这里主要讲解ROWID和ROWNUM。

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

ROWID伪列有以下重要的用途。
1.能以最快的方式访问表中的一行

2.能显示表的行是如何存储的

3.可以作为表中行的唯一标识

如：可以使用SELECT语句查询ROWID值
SQL>SELECT ROWID,eName
    FROM SCOTT.emp
    WHERE eName="SMITH"
2.ROWNUM

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

如：从emp表中提取10条记录并显示序号
SQL>SELECT emp.*,ROWNUM
    FROM SCOTT.emp
    WHERE ROWNUM<11
ROWNUM查询结果
一.Oracle基础知识

1.Oracle简介

Oracle是一个数据库管理系统，是Oracle公司的核心产品。

Oracle数据库的主要特点如下：
>支持多用户，大事务量的事务处理
	>
	>在保持数据安全性和完整性方面性能优越
	>
	>支持分布式数据处理
	>
	>具有可移植性。
12c增加了big data、data optimization、manageability、high availability等一些新功能。

2.Oracle基本概念

图片

1.数据库
>这里数据库不是通常情况下我们所说的数据库，而是Oracle的一个企业名词。它是磁盘上存储的数据的集合，在物理上表现为数据文件、日志文件和控制文件等，在逻辑上以表空间形式存在。使用时，必须首先创建数据库，然后使用。
2.全局数据库名

全局数据库名是用于区分一个数据库的标识，在安装数据库、创建数据库、创建控制文、修改数据库结构，利用RMAN备份时都需要使用。它由数据库名和域名构成的，类似网络中的域名，使数据库的命名在整个网络环境中唯一。

3.数据库实例

每个启动的数据库都对应一个数据库实例，由这个实例来访问数据库中数据。如果把数据库简单的理解为磁盘上的文件，具有永久性，则数据库实例就是通过内存共享运行状态的一组服务器后台进程。

4.表空间

每个Oracle数据库都是由若干个表空间构成的，用户在数据库中建立的所有内容都被存储到表空间。一个表空间可以由多个数据文件组成，但一个数据文件只能属于一个空间。与数据文件这种物理结构相比，表空间属于数据库的逻辑结构。

图片

5.数据文件

数据文件的扩展名是：dbf，是用于存储数据库数据的文件

一个数据文件中可能存储很多个表的数据，而一个表的数据也可能存放在多个数据文件中，即数据库名和数据文件不存在一对一的关系。

6.控制文件

控制文件的扩展名是：ctl，是一个二进制文件。控制文件中存储的信息很多，其中包括数据文件和日志文件的名称和位置。控制文件是数据库启动及运行所必需的文件。当Oracle读写数据时，要根据控制文件的信息查找数据文件。

7.日志文件

日志文件的扩展名是.log，它记录了数据的所有更改信息，并提供了一种数据恢复机制，确保在系统崩溃或其他意外出现意外出现后重新恢复数据库。

在Oracle数据库中，日志文件是成组使用的，每个日志文件组成可以有一个或多个日志文件。在工作过程中，多个日志文件组之间循环使用，当一个日志文件组写满后，会转向下一个日志文件组。

8.模式和模式对象

模式是数据库对象（如表、索引等，也称模式对象）的集合。Oracle会为每个数据库用户创建一个模式，此模式为当前用户所拥有，和用户具有相同的名称。

3.数据库服务简介

共有7个服务，这七个服务的含义分别为：

Oracle ORCL VSS Writer Service： Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service）能够让存储基础设备（比如磁盘，阵列等）创建高保真的时间点映像，即映射拷贝（shadow copy）。它可以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。（非必须启动）

OracleDBConsoleorcl：Oracle数据库控制台服务，orcl是Oracle的实例标识，默认的实例为orcl。在运行Enterprise Manager（企业管理器OEM）的时候，需要启动这个服务。（非必须启动）

OracleJobSchedulerORCL： Oracle作业调度（定时器）服务，ORCL是Oracle实例标识。（非必须启动）

OracleMTSRecoveryService：服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。（非必须启动）

OracleOraDb11g_home1ClrAgent： Oracle数据库 .NET扩展服务的一部分。（非必须启动）

OracleOraDb11g_home1TNSListener：监听器服务，服务只有在数据库需要远程访问的时候才需要。（非必须启动，下面会有详细详解）。

OracleServiceORCL：数据库服务(数据库实例)，是Oracle核心服务该服务，是数据库启动的基础，只有该服务启动，Oracle数据库才能正常启动。(必须启动)

4.连接数据库

1.SQL*Plus工具
Oracle的SQLPlus是与Oracle数据库进行交互的客户端工具。在SQL*Plus中，可以运行SQLPlus命令与SQL语句

SQLPlus命令(win+R运行 sqlplus /nolog)
#登录系统管理员
conn / as sysdba;

#开始记录（结束才能保存）
spool d:\XX.txt

#记录结束
spool off;

#清屏命令
host cls 
clear screen
clear SCR

#显示行宽
show linesize

#设置行宽
set linesize 120

#设置列宽（a代表一个字符,8代表有8个a,9代表一个数字）
col 字段名 for a8
col 字段名 for 9999

#ed写命令执行文件   /执行命令文件
ed
/

#错误修改
SQL> select empno,ename,sal
  2  form emp;
form emp
     *
第 2 行出现错误: 
ORA-00923: 未找到要求的 FROM 关键字 

SQL> 2
  2* form emp
SQL> c/form/from
  2* from emp
SQL> /
2.PL/SQL Developer工具

连接：

Normal：普通用户

sysOper：数据库操作员--主要包括打开、关闭、备份、恢复数据库服务器等

sysDBA：数据库管理员--主要包括打开数据库服务器、关闭数据库服务器、备份数据库服务器、恢复数据库服务器、日志归档、管理功能、创建数据库等。

二.Oracle数据类型

1.字符数据类型

字符类型主要包括VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2等。

（1）VARCHAR2类型：用于存储可变长度的字符串，其语法格式VARCHAR2(maxlength)，参数maxlength的最大值可以是32767字节。

注意：数据库类型的VARCHAR2的最大长度是4000字节，所以一个长度大于4000字节的PL/SQL类型VARCHAR2变量不可以赋值给数据库中的一个VARCHAR2变量，而只能赋值给LONG类型的数据库变量。

（2）CHAR类型：CHAR类型表示指定长度的字符串，其语法格式CHAR(maxlength)。

（3）LONG类型：LONG类型表示一个可变的字符串，最大长度是32767字节，而数据库类型的LONG最大长度可达2GB，所以任何字符串变量都可以赋值给它（LONG类型不建议使用，而建议使用CLOB）。

（4）NCHAR数据类型：即国家字符集，使用方法和CHAR相同，如果开发的项目需要国际化，那么数据类型选择NCHAR数据类型。NCHAR和CHAR的区别在于NCHAR用来存储Unicode字符集类型，即双字符集类型。例如：我们定义CHAR(11)和NCHAR(11)类型的两个字段，字段长度为1字节和1个字符（2个字节），分别插入'a'和'a'是没有问题的，但是占用的字节数分别是1和。如果占用的字节数分别是1和2。如果分别插入‘的’和‘的’，则前者无法正常插入，而后者可以。

（5）NVARCHAR2与NCHAR类似，在使用上，NVARCHAR2存储需要国际化的可变长度字符串

2.数值数据类型

数值类型主要包括NUMBER、PLS_INTEGER、BINARY_INTEGER这3中基本类型。其中，NUMBER类型的变量可以存储正数、负数、零、定点数和精度为38位的浮点数；而PLS_INTEGER、BINARY_INTEGER类型的变量只存储整数。
#p为精度，表示有效数字，在1~38之间有效数字
#s为范围，表示小数点右边数字的位数，它在-84~+127之间
#定位数
NUMBER(p)

#浮点数
NUMBER(p,s)
NUMBER(p=38,s=0)
3.日期时间数据类型

日期时间数据类型用于存储日期值和时间值

（1）DATE数据类型

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

（2）TIMESTAMP数据类型

TIMESTAMP数据类型用于存储日期的年、月、日，以及时间的小时、分和秒，其中秒值精确到小数点后6位，该数据类型同时包含时区信息。SYSTIMESTAMP函数的功能是返回当前日期，时间和时区。

4.LOB数据类型

LOB又称“大对象”数据类型。该数据类型可以存储多达4GB的废结构化信息，如声音和视频剪辑等。LOB允许对数据进行高效、随机、分段的访问。LOB可以是外部的，也可以是内部的，这取决于它相对于数据库的位置。LOB数据类型有CLOB、BFILE、BLOB和NCLOB。

（1）CLOB

CLOB（CHaracter LOB，字符LOB）能够存储大量字符类型。该数据类型可以存储单字节字符数据和多字节字符数据，主要用于存储非结构化的XML文档，如新闻、内容介绍等含大量文字内容的文档。

（2）BLOB

BLOB（BinaryLOB1，二进制LOB）可以存储较大的二进制对象，如图像、视频剪辑和声音剪辑等

（3）BFILE

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

（4）NCLOB

NCLOB数据类型用于存储大的NCHAR字符数据。NCLOB数据类型同时支持固定宽度字符和可变宽度字符（Unicode字符数据）。大字符对象的大小不大于4GB。NCLOB类型的使用方法与CLOB类型。

Oracle中的表可以有多个LOB列，每个LOB列可以是不同的LOB类型。

5.特殊数据类型

为了提高用户的编程效率和解决复杂的业务逻辑需求，PL/SQL语言除了可以使用Oracle规定的基础数据类型外，还提供了3种特殊的数据类型，但这3种类型仍然是建立在基本数据类型基础之上的。

%TYPE类型

使用%TYPE关键字可以声明一个与指定列名称相同的数据类型，它通常紧跟在指定列名的后面。

RECORD类型

单词RECORD有“记录”之意，因此RECORD类型也称作“记录类型”，使用该类型的变量可以存储由多个列值组成的一行数据。在声明记录类型变量之前，首先需要定义记录类型，然后才可以声明记录类型的变量。记录类型是一种结构化的数据类型，它使用type语句进行定义，在记录类型的定义结构中包含成员变量及其数据类型，其语法格式如下：

type record_type is record ( var_member1 data_type [not null][:=default_value], … var_membern data_type [not null][:=default_value] ) record_type：表示要定义的记录类型名称。

var_member1：表示该记录类型的成员变量名称。

data_type：表示成员变量的数据类型。

3 .%ROWTYPE类型

%ROWTYPE类型的变量结合了%TYPE类型和RECORD类型变量的优点，它可以根据数据表中行的结构定义一种特殊的数据类型，用来存储从数据表中检索到的一行数据。它的语法形式很简单，如下所示：

rowVar_name table_name%rowtype; rowVar_name：表示可以存储一行数据的变量名。

table_name：指定的表明。

6.伪列

伪列就像Oracle中的一个表列，但实际上它并未存储在表中。伪列可以从表中查询，但是不能插入、更新和删除它们的值。这里主要讲解ROWID和ROWNUM。
1.ROWID 数据库中每行都有一行地址，ROWID伪列返回该行地址。可以使用ROWID值来定位表中的一行。通常情况下，ROWID值可以唯一地标识数据库中的一行。

ROWID伪列有以下重要的用途。
1.能以最快的方式访问表中的一行

2.能显示表的行是如何存储的

3.可以作为表中行的唯一标识

如：可以使用SELECT语句查询ROWID值
SQL>SELECT ROWID,eName
	FROM SCOTT.emp
	WHERE eName="SMITH"
2.ROWNUM

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

如：从emp表中提取10条记录并显示序号
SQL>SELECT emp.*,ROWNUM
	FROM SCOTT.emp
	WHERE ROWNUM<11
ROWNUM查询结果

查询条件结果

ROWNUM对于等于某值的查询条件等于1，可以查到第一条数据，但等于2则查不到

ROWNUM对于大于某值的查询条件查不到，原因是ROWNUM总是从第一个开始（>2)

ROWNUM对于大于某值的查询条件小于11，得到10条记录
伪表 dual
SQL>select 5+3 from dual； --作用：不存储主题数据，主要功能是用来做查询
三.SQL语言简介

1.数据定义语言DDL

CREATE（创建）、ALTER（更改）、TRUNCATE（截断）、DROP（删除）
（1）CREATE TABLE命令（省略）

（2）TRUNCATE TABLE命令
语法：
SQL>TRUNCATE TABLE <tablename>
##2.数据操纵语言DML

INSERT（插入）、SELECT（查询）、DELETE（删除）、UPDATE（更新）
STUNO STUNAME STUAGE STUID STUSEAT

1 张三 18 1

2 李四 20 2

3 王五 15 3

4 张三 18 4

5 张三 20 5
1.从语法的角度介绍DML语言操作

（1）选择无重复的行

要防止选择重复的行，可以在SELECT命令中包含DISTICT子句

（2）带条件和排序的SELECT命令

要从表中选择特定的行，可以在SELECT命令中包含WHERE子句。ORDER by ASC/DESC

（3）使用列别名

列别名是为列表达式提供的另一个名称，位于列表达式后面，并显示在列表达式标题中。列表达式不影响列的实际名称 as

（4）利用现有的表创建新表
SQL>CREATE TABLE <newtable>
	AS
	SELECT{* | column(s)}
	FROM <oldtable> [WHERE <condition>]
	
--只复制表结构时，可以加上WHERE 1=2
2.从使用技巧的角度介绍DML语言操作

（1）查看表中行数
SQL>SELECT COUNT(*) FROM stuinfo;  --效率低
SQL>SELECT COUNT(1) FROM stuinfo;  --效率高
（2）取出stuName，stuAge
SQL> SELECT stuName,stuAge 
          FROM stuInfo
          GROUP BY stuName,stuAge 
          HAVING(COUNT(stuName||stuAge) <2);
（3）删除stuName，stuName列重复的行（只保留一行）
SQL>DELETE 
          FROM stuInfo 
          --4.获得所有重复记录的ROWID
          WHERE ROWID NOT IN( 
              			--1.查找重复的记录，只保留一条
          		           SELECT MAX(ROWID) 
                   	     FROM stuInfo
                  	     GROUP BY stuName,stuAge 
                             HAVING(COUNT(stuAge||stuAge)>1)
              			--3.合并结果集，获得所有符合条件且不重复的记录
		                 UNION
              			--2.查找不重复的记录
		                 SELECT max(ROWID)
            		     FROM Stuinfo
		                 GROUP BY stuName,stuAge 
            		     HAVING(COUNT(stuAge||stuAge)=1)     
3.事务控制语言TCL

COMMIT（提交）、SAVEPOINT（保存点）、ROLLBACK（回滚）
组成

COMMIT ：提交事务，即把事务中对数据库的修改进行永久保存

ROLLBACK ：回滚事务，即取消对数据库所作的任何修改

SAVEPOINT ：在事务中创建存储点

ROLLBACK TO <SavePoint_Name>：将事务回滚到存储点
SQL>INSERT INTO dept VALUES(50,'a',null);
SQL>INSERT INTO dept VALUES(60,'b',null);
SQL>SAVEPOINT a;
SQL>INSERT INTO dept VALUES(70,'c',null);
SQL>ROLLBACK TO SAVEPOINT a;--回滚到a存储点
SQL>COMMIT;--执行commit，不包含第四行数据
SQL>SELECT * FROM dept;
4.数据控制语言DCL

GRANT（授权）、REVOKE（回收）

数据控制语言为用户提供权限控制命令

四.SQL操作符

##1.算术操作符

+、-、*、/

##2.比较运算符

=、！=、>、<、>=、<=、BETWEEN....AND、IN、LIKE、IS NULL
SQL>select * from emp where id is null;  ---null 永远！=null
SQL>select * from emp where id=null;--错误XXX
between :含有边界小值在前,大值在后
SQL>select * from emp where sal between 1000 and 2000;
in在集合中
SQL>select * from emp where deptno not in(10,20)
5.模糊查询方式
通配符 _任意一个字母

%:任意0-n个字母
#若使用模糊查询字段中值，值中带下划线，需要用 '\' escape '\'转义
SQL>select * from emp where ename like '%\_%' escape '\'
3.逻辑运算符

AND、OR、NOT

4.集合操作符

集合操作符将两个查询的结果组合成一个结果集。可以在SQL中使用下面的结合操作符来组合多个查询中的行

UNION（联合）：用来求两个集合的并集，并去掉重复值

UNION ALL（联合所有）：用来求两个集合的并集

INTERSECT（交集）：用来求两个集合的交集，并去掉重复值

MINUS（减集）：用来求在第一个集合中存在，而在第二个集合中不存在的记录，并去掉重复值

使用集合操作符连接起来的SELECT语句中列遵循以下规则：

通过集合操作符连接的各个查询具有相同的列数，而且对应列的数据类型必须兼容

这种查询不应含有LONG类型的列。

5.连接操作符

连接操作符（||）用于将两个或多个字符串合并成一个字符串，或者将一个字符串与一个数值合并在一起。
SQL>select a||'_'||b from emp;
SQL>select concat(EMPNO,ENAME) from emp;
优先级

运算符级别

算术运算符(即‘+',‘-'，‘*',‘/') 1

连接运算符（即‘||'） 2

比较运算符（即‘>'，‘>='，‘<'，‘<='，‘<>'） 3

Is [not] null,[not] like,[not] in 4

[not] between-and 5

not 6

and 7

or 8

五.SQL函数

Oracle SQL提供了用于执行特定操作的专用函数。（划分为3个）

单行函数：日期函数、数字函数、字符函数、转换函数、其他函数

聚合函数（分组函数）

分析函数

##1.单行函数:

（1）操作数据对象（2）接受参数返回一个结果（3）只对一行进行变换（4）每行返回一个结果（5）可以转换数据类型（6）可以嵌套（7）参数可以是一列或一个值

（1）转换函数

将值从一种数据类型转换为另一种数据类型。

常见转换函数

函数功能

TO_CHAR() 转换成字符串类型

TO_DATE() 转换成日期类型

TO_NUMBER 转换成数值类型
#to_char()
SQL>select to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') from dual;--2018-10-15 07:10:00
SQL>select to_char(sal,'L9,999.9')from emp; --L9,999,9 规定写法 ￥12542

#to_date()
SQL>select to_date('2018-07-10','yyyy-mm-dd') from dual;

#to_number()
SQL>select sqrt(to_number('100')) from dual; --sqrt() 求平方根
（2）分析函数

分析函数是对一组查询结果进行运算，然后获得结果。

类似聚合函数，区别在于分析函数每个组返回多行，聚合函数每组返回一行

语法：

函数名([参数]) OVER ( [分区子句]

[ 排序语句])

在语法中：

函数名表示分析函数的名称

参数表示函数需要传入的参数

分区子句（PARTITION BY）表示将查询结果分为不同的组，功能类型GROUP By语句，是分析函数工具的基础，默认将每个分区进行排序。

RANK、DENSE_RANK、ROW_NUMBER函数用于为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。

1.ROW_NUMBER函数

表示返回一个唯一的值，当遇到相同数据时，排名按照记录集中记录的顺序依次递增。

2.DENSE_ANK函数

表示返回一个唯一的值，当遇到相同数据时，所有相同数据的排名都是一样的。

3.RANK函数

表示返回一个唯一的值，当遇到相同的数据时，所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

（3）字符函数

大小写控制函数：lower、upper、initcap
SQL>select lower('Hello world')"小写",
   >upper('Hello world')"大写", 
   >initcap('hello')"首字母大写"
   >from dual;
字符控制函数：concat、substr、length、lengthb、instr、lpad、rpad、trim、replace
#concat 字符拼接
SQL>select concat('a','b') as "字符拼接" from dual;

#substr 字符截取
SQL>select substr('hello world',5) from dual;
SQL>select substr('hello world',5,8) from dual;
SQL>select substr('hello world',8,5) from dual;--以8开始截取，5自动省略

#length 长度(以字符)  lengthb (以字节)
SQL>select length('hello') from dual;
SQL>select lengthb('hello') from dual;

#instr(a,b) 在a中查找b的位置
SQL>select instr('hello','e') from dual;

#lpad左填充，rpad右填充  填充到10位，不够以*代替
SQL>select lpad('aa',10,'*') from dual;
SQL>select rpad('aa',10,'*') from dual;

#trim 去除字符
SQL>select trim('a' from 'abac') from dual;--去除abac中第一个a

#replace() 替换字符
SQL>select replace('abac','a','r') from dual;--替换abac中所有a字符为r
（4）数字函数

round、trunc、mod
#round 四舍五入
SQL>select round(15.659,2) from dual; --15.66   2代表小数点位数
SQL>select round(15.655,-1) from dual;--20   保留百位及以上位，个位以0替换

#trunc 截断
SQL>select trunc(15.655,2) from dual;--15.65
SQL>select trunc(15.655,-1) from dual;--10

#mod(a,b) a/b的余数
SQL>select mod(45,2) from dual;--1
（5）日期函数
#sysdate 返回当前时间
SQL> select sysdate from dual;

#昨天 今天 明天
SQL>select (sysdate-1)"昨天",sysdate "今天",(sysdate+1)"明天" from dual;

#add_months 添加月数
SQL>select add_months(sysdate,1) from dual;--1代表月数

#last_day 月的最后一天
SQL>select last_day(sysdate) from dual;

#next_day 下个星期几的日期
SQL>select next_day(sysdate,'星期一') from dual; --星期一是规定，星期日也是

#months_between 两个时间相差的月数
SQL>select months_between('第一个日期','第二个日期') from dual;

#round 日期四舍五入
SQL>select round(sysdate) from dual; --2018/10/16
（6）其他函数

常用的其他函数

函数功能

NVL(exp1,exp2) 如果exp1的值为NULL，返回exp2的值，否则返回exp1的值

NVL2(exp1,exp2,exp3) 如果exp1的值为NULL，则返回exp3的值，否则返回exp2的值

DECODE(value,if1,then1,if2,then2) 如果value的值为if1，则返回then1；以此类推；否则返回else的值

NULLIF(expr1, expr2) 如果两个表达式不相等，NULLIF 返回第一个 expression1 的值。如果两个表达式相等，NULLIF 返回空值NULL。

COALESCE(expr1, expr2) COALESCE返回参数列表中第一个非空表达式。必须指定最少两个参数。如果所有的参数都是null，则返回null。
#nvl
SQL>select nvl(null,1) from dual; --1

#nvl2
SQL>select nvl2(null,null,3) from dual; --3

#decode
SQL>select decode(1,3,'aa',1,'bb') from dual;--bb

#nullif
SQL>select nullif('a','aa') from dual;--null

#coalesce
SQL>select coalesce(null,3+2) from dual;--5
六.表空间和用户权限

##1.表空间

Oracle数据库包含逻辑结构和物理结构。

物理结构是指构成数据库的一组操作系统文件。

逻辑结构是指描述数据组织方式的一组逻辑概念及它们之间的关系。

表空间是逻辑结构的一个重要组件。表空间可以存放各种应用对象，如表、索引。

而每个表空间由一个或多个数据文件组成。
（1）表空间分类

类别说明

永久性表空间一般保存表、视图、过程和索引等的数据。SYSTEM、SYSAUX、USERS、EXAMPLE表空间是默认安装的

临时性表空间只用于保存系统中短期活动的数据，如排序数据等

撤销表空间用来帮助回退未提交的事务，已提交了的数据在这里是不可以恢复的。一般不需要建临时和撤销表空间，除非把它们转移到其他磁盘中以提高性能

（2）表空间的目的

使用表空间的目的为：

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

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

（3）创建表空间
语法：
SQL>create tablespacename tablename datafile 'filename' [size integer[K|M]]
	[autoextend [off|on]]
tablespacename：创建的表名称

datafile：指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔

filename：数据文件的路径和名称

size：文件大小

autoextend：子句用来启动或禁用数据文件的自动扩展，设置为ON 则空间使用完毕会扩展。

例：
#创建表空间     system用户
SQL> create tablespace haha_test datafile 'd:\data\haha.dbf' size 60m;
（4）查询表空间
SQL> select * from dba_data_files;
（5）修改表空间大小
SQL> alter database datafile 'd:\data\haha.dbf' resize 80m;
（6）给表空间添加数据文件
SQL> alter tablespace haha_test add datafile 'd:\data\haha1.dbf' size 80m    		autoextend on;
（7）删除表空间
#删除表空间名称
SQL> drop tablespace haha_test;
#删除表空间并且物理文件
SQL> drop tablespace haha_test
     include contents;
2.自定义用户管理

Oracle中默认三个用户Sys、System、Scott，其中Sys和System是系统用户
1.Sys用户

Sys用户是超级用户，数据库中的数据字典和视图都存储在SYS用户中。

数据字典存储了用来管理数据对象的所有信息，是Oracle数据库中非常重要的系统信息。

Sys用户主要用来维护系统信息和管理实例。

Sys用户只能以SYSOPER或SYSDBA角色登录

2.System用户

这是Oracle中默认的系统管理员，拥有DBA权限。该用户拥有Oracle管理工具使用的内部表和视图。通常System用户管理Oracle数据库的用户、权限和存储等。

不建议在System用户下创建用户表。

System用户不能以SYSOPER或SYSDBA角色登录系统，只能默认

3.Scott用户

这是Oracle数据库时创建的示例用户

在Oracle 12c中，scott用户被取消，需要自己创建
--登录系统用户
SQL>conn / as sysdab;

--删除已有的用户(可选) 
SQL>DROP USER c##scott cascade;

--新建用户，授予权限，连接
--用户名前加c##是12c中的新特性 
SQL>CREATE USER c##scott identified by 123456 ;           
SQL>GRANT CREATE SEQUENCE to c##scott ;
SQL>GRANT UNLIMITED TABLESPACE to c##scott ;
SQL>GRANT CREATE SESSION to c##scott ;
SQL>GRANT CREATE SYNONYM to c##scott ;
SQL>GRANT ALTER SESSION to c##scott;
SQL>GRANT CREATE DATABASE LINK to c##scott;
SQL>GRANT CREATE VIEW to c##scott;
SQL>GRANT CREATE TABLE to c##scott;
SQL>ALTER USER c##scott DEFAULT TABLESPACE USERS;
SQL>ALTER USER c##scott TEMPORARY TABLESPACE TEMP;
SQL>CONN c##scott/123456;

--修改密码
SQL>alter user 用户名 identified by 新密码;

--删除用户
SQL>drop user 用户名称 cascade;
--删除表空间
SQL>drop tablespace 表空间名称 including contents and datafiles cascade constraint;
（1）创建用户并给用户分配表空间
语法：
create USER user
identified by password
[default TABLESPACE tablespace]
[default TABLESPACE tablespace]
user:用户名，用户名必须是一个标识符，12c中创建用户需要在用户名前加c## password:用户密码，必须是一个标识符，且不区分大小写 default TABLESPACE或default TABLESPACE为用户确定默认表空间或临时表空间

例：
--创建用户，这个用户60m的表空间
SQL>create user haha identified by 123
   >default tablespace haha_test
   >temporary tablespace temp --临时表空间
   >quota unlimited on to haha --无底线
   >quota 10m on tp_bak --增长10m
   >password expire --设置口令;
   
--查询表空间
SQL>  select * from dba_users where username = 'HAHA'

--查看表空间限额
SQL>select * from dba_ts_quotas where username ='HAHA';

--更改表空间限额 60m
SQL>alter user haha quota 10m on haha_test;
（2）用户授权
System用户
--授权  connect:登录 resource：操作
SQL>grant connect,resource to haha;
--撤销授权
SQL>revoke connect,resource to haha;
--使用haha查看scott用户下的表
SQL>grant select on scott.emp to haha; --查询
SQL>grant update on scott.emp to haha; --修改
SQL>select * from scott.emp for update;
注意：

1.字符串大小敏感

Oracle中存在大小写敏感，mysql不存在

2.日期格式敏感
---修改日期格式
sql>alter session set NLS_DATE_FORMAT ='yyyy-mm-dd';

函数	功能
TO_CHAR()	转换成字符串类型
TO_DATE()	转换成日期类型
TO_NUMBER	转换成数值类型

函数	功能
NVL(exp1,exp2)	如果exp1的值为NULL，返回exp2的值，否则返回exp1的值
NVL2(exp1,exp2,exp3)	如果exp1的值为NULL，则返回exp3的值，否则返回exp2的值
DECODE(value,if1,then1,if2,then2)	如果value的值为if1，则返回then1；以此类推；否则返回else的值
NULLIF(expr1, expr2)	如果两个表达式不相等，NULLIF 返回第一个 expression1 的值。如果两个表达式相等，NULLIF 返回空值NULL。
COALESCE(expr1, expr2)	COALESCE返回参数列表中第一个非空表达式。必须指定最少两个参数。如果所有的参数都是null，则返回null。

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

伪表 dual

SQL>select 5+3 from dual； --作用：不存储主题数据，主要功能是用来做查询

三.SQL语言简介

1.数据定义语言DDL

CREATE（创建）、ALTER（更改）、TRUNCATE（截断）、DROP（删除）

（1）CREATE TABLE命令（省略）

（2）TRUNCATE TABLE命令
语法：
SQL>TRUNCATE TABLE <tablename>

2.数据操纵语言DML

INSERT（插入）、SELECT（查询）、DELETE（删除）、UPDATE（更新）

STUNO	STUNAME	STUAGE	STUSEAT
1	张三	18	1
2	李四	20	2
3	王五	15	3
4	张三	18	4
5	张三	20	5

1.从语法的角度介绍DML语言操作

（1）选择无重复的行

要防止选择重复的行，可以在SELECT命令中包含DISTICT子句

（2）带条件和排序的SELECT命令

要从表中选择特定的行，可以在SELECT命令中包含WHERE子句。ORDER by ASC/DESC

（3）使用列别名

列别名是为列表达式提供的另一个名称，位于列表达式后面，并显示在列表达式标题中。列表达式不影响列的实际名称 as

（4）利用现有的表创建新表
SQL>CREATE TABLE <newtable>
    AS
    SELECT{* | column(s)}
    FROM <oldtable> [WHERE <condition>]
    
--只复制表结构时，可以加上WHERE 1=2
2.从使用技巧的角度介绍DML语言操作

（1）查看表中行数
SQL>SELECT COUNT(*) FROM stuinfo;  --效率低
SQL>SELECT COUNT(1) FROM stuinfo;  --效率高
（2）取出stuName，stuAge
SQL> SELECT stuName,stuAge 
          FROM stuInfo
          GROUP BY stuName,stuAge 
          HAVING(COUNT(stuName||stuAge) <2);
（3）删除stuName，stuName列重复的行（只保留一行）
SQL>DELETE 
          FROM stuInfo 
          --4.获得所有重复记录的ROWID
          WHERE ROWID NOT IN( 
                        --1.查找重复的记录，只保留一条
                           SELECT MAX(ROWID) 
                         FROM stuInfo
                         GROUP BY stuName,stuAge 
                             HAVING(COUNT(stuAge||stuAge)>1)
                        --3.合并结果集，获得所有符合条件且不重复的记录
                         UNION
                        --2.查找不重复的记录
                         SELECT max(ROWID)
                         FROM Stuinfo
                         GROUP BY stuName,stuAge 
                         HAVING(COUNT(stuAge||stuAge)=1)     

3.事务控制语言TCL

COMMIT（提交）、SAVEPOINT（保存点）、ROLLBACK（回滚）

组成

COMMIT ：提交事务，即把事务中对数据库的修改进行永久保存

ROLLBACK ：回滚事务，即取消对数据库所作的任何修改

SAVEPOINT ：在事务中创建存储点

ROLLBACK TO <SavePoint_Name>：将事务回滚到存储点
SQL>INSERT INTO dept VALUES(50,'a',null);
SQL>INSERT INTO dept VALUES(60,'b',null);
SQL>SAVEPOINT a;
SQL>INSERT INTO dept VALUES(70,'c',null);
SQL>ROLLBACK TO SAVEPOINT a;--回滚到a存储点
SQL>COMMIT;--执行commit，不包含第四行数据
SQL>SELECT * FROM dept;

4.数据控制语言DCL

GRANT（授权）、REVOKE（回收）

数据控制语言为用户提供权限控制命令

在Oracle 12c中，scott用户被取消，需要自己创建

--登录系统用户
SQL>conn / as sysdab;

--删除已有的用户(可选) 
SQL>DROP USER c##scott cascade;

--新建用户，授予权限，连接
--用户名前加c##是12c中的新特性 
SQL>CREATE USER c##scott identified by 123456 ;           
SQL>GRANT CREATE SEQUENCE to c##scott ;
SQL>GRANT UNLIMITED TABLESPACE to c##scott ;
SQL>GRANT CREATE SESSION to c##scott ;
SQL>GRANT CREATE SYNONYM to c##scott ;
SQL>GRANT ALTER SESSION to c##scott;
SQL>GRANT CREATE DATABASE LINK to c##scott;
SQL>GRANT CREATE VIEW to c##scott;
SQL>GRANT CREATE TABLE to c##scott;
SQL>ALTER USER c##scott DEFAULT TABLESPACE USERS;
SQL>ALTER USER c##scott TEMPORARY TABLESPACE TEMP;
SQL>CONN c##scott/123456;

--修改密码
SQL>alter user 用户名 identified by 新密码;

--删除用户
SQL>drop user 用户名称 cascade;
--删除表空间
SQL>drop tablespace 表空间名称 including contents and datafiles cascade constraint;

四.SQL操作符

1.算术操作符

+、-、*、/

2.比较运算符

=、！=、>、<、>=、<=、BETWEEN....AND、IN、LIKE、IS NULL

SQL>select * from emp where id is null;  ---null 永远！=null
SQL>select * from emp where id=null;--错误XXX

between :含有边界小值在前,大值在后

SQL>select * from emp where sal between 1000 and 2000;

in在集合中

SQL>select * from emp where deptno not in(10,20)

5.模糊查询方式

通配符 _任意一个字母

%:任意0-n个字母

#若使用模糊查询字段中值，值中带下划线，需要用 '\' escape '\'转义
SQL>select * from emp where ename like '%\_%' escape '\'

3.逻辑运算符

AND、OR、NOT

4.集合操作符

集合操作符将两个查询的结果组合成一个结果集。可以在SQL中使用下面的结合操作符来组合多个查询中的行

UNION（联合）：用来求两个集合的并集，并去掉重复值

UNION ALL（联合所有）：用来求两个集合的并集

INTERSECT（交集）：用来求两个集合的交集，并去掉重复值

MINUS（减集）：用来求在第一个集合中存在，而在第二个集合中不存在的记录，并去掉重复值

使用集合操作符连接起来的SELECT语句中列遵循以下规则：

通过集合操作符连接的各个查询具有相同的列数，而且对应列的数据类型必须兼容

这种查询不应含有LONG类型的列。

5.连接操作符

连接操作符（||）用于将两个或多个字符串合并成一个字符串，或者将一个字符串与一个数值合并在一起。

SQL>select a||'_'||b from emp;
SQL>select concat(EMPNO,ENAME) from emp;

优先级

运算符	级别
算术运算符(即‘+',‘-'，‘*',‘/')	1
连接运算符（即‘\|\|'）	2
比较运算符（即‘>'，‘>='，‘<'，‘<='，‘<>'）	3
Is [not] null,[not] like,[not] in	4
[not] between-and	5
not	6
and	7
or	8

五.SQL函数

Oracle SQL提供了用于执行特定操作的专用函数。（划分为3个）

单行函数：日期函数、数字函数、字符函数、转换函数、其他函数

聚合函数（分组函数）

分析函数

1.单行函数:

（1）操作数据对象（2）接受参数返回一个结果（3）只对一行进行变换（4）每行返回一个结果（5）可以转换数据类型（6）可以嵌套（7）参数可以是一列或一个值

（1）转换函数

将值从一种数据类型转换为另一种数据类型。

常见转换函数

函数功能

TO_CHAR() 转换成字符串类型

TO_DATE() 转换成日期类型

TO_NUMBER 转换成数值类型

函数	功能
TO_CHAR()	转换成字符串类型
TO_DATE()	转换成日期类型
TO_NUMBER	转换成数值类型

#to_char()
SQL>select to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') from dual;--2018-10-15 07:10:00
SQL>select to_char(sal,'L9,999.9')from emp; --L9,999,9 规定写法 ￥12542

#to_date()
SQL>select to_date('2018-07-10','yyyy-mm-dd') from dual;

#to_number()
SQL>select sqrt(to_number('100')) from dual; --sqrt() 求平方根

（2）分析函数

分析函数是对一组查询结果进行运算，然后获得结果。

类似聚合函数，区别在于分析函数每个组返回多行，聚合函数每组返回一行

语法：

函数名([参数]) OVER ( [分区子句]

[ 排序语句])

在语法中：

函数名表示分析函数的名称

参数表示函数需要传入的参数

分区子句（PARTITION BY）表示将查询结果分为不同的组，功能类型GROUP By语句，是分析函数工具的基础，默认将每个分区进行排序。

RANK、DENSE_RANK、ROW_NUMBER函数用于为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。

1.ROW_NUMBER函数

表示返回一个唯一的值，当遇到相同数据时，排名按照记录集中记录的顺序依次递增。

2.DENSE_ANK函数

表示返回一个唯一的值，当遇到相同数据时，所有相同数据的排名都是一样的。

3.RANK函数

表示返回一个唯一的值，当遇到相同的数据时，所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

（3）字符函数

大小写控制函数：lower、upper、initcap

SQL>select lower('Hello world')"小写",
   >upper('Hello world')"大写", 
   >initcap('hello')"首字母大写"
   >from dual;

字符控制函数：concat、substr、length、lengthb、instr、lpad、rpad、trim、replace

#concat 字符拼接
SQL>select concat('a','b') as "字符拼接" from dual;

#substr 字符截取
SQL>select substr('hello world',5) from dual;
SQL>select substr('hello world',5,8) from dual;
SQL>select substr('hello world',8,5) from dual;--以8开始截取，5自动省略

#length 长度(以字符)  lengthb (以字节)
SQL>select length('hello') from dual;
SQL>select lengthb('hello') from dual;

#instr(a,b) 在a中查找b的位置
SQL>select instr('hello','e') from dual;

#lpad左填充，rpad右填充  填充到10位，不够以*代替
SQL>select lpad('aa',10,'*') from dual;
SQL>select rpad('aa',10,'*') from dual;

#trim 去除字符
SQL>select trim('a' from 'abac') from dual;--去除abac中第一个a

#replace() 替换字符
SQL>select replace('abac','a','r') from dual;--替换abac中所有a字符为r

（4）数字函数

round、trunc、mod

#round 四舍五入
SQL>select round(15.659,2) from dual; --15.66   2代表小数点位数
SQL>select round(15.655,-1) from dual;--20   保留百位及以上位，个位以0替换

#trunc 截断
SQL>select trunc(15.655,2) from dual;--15.65
SQL>select trunc(15.655,-1) from dual;--10

#mod(a,b) a/b的余数
SQL>select mod(45,2) from dual;--1

（5）日期函数

#sysdate 返回当前时间
SQL> select sysdate from dual;

#昨天 今天 明天
SQL>select (sysdate-1)"昨天",sysdate "今天",(sysdate+1)"明天" from dual;

#add_months 添加月数
SQL>select add_months(sysdate,1) from dual;--1代表月数

#last_day 月的最后一天
SQL>select last_day(sysdate) from dual;

#next_day 下个星期几的日期
SQL>select next_day(sysdate,'星期一') from dual; --星期一是规定，星期日也是

#months_between 两个时间相差的月数
SQL>select months_between('第一个日期','第二个日期') from dual;

#round 日期四舍五入
SQL>select round(sysdate) from dual; --2018/10/16

（6）其他函数

常用的其他函数

函数功能

NVL(exp1,exp2) 如果exp1的值为NULL，返回exp2的值，否则返回exp1的值

NVL2(exp1,exp2,exp3) 如果exp1的值为NULL，则返回exp3的值，否则返回exp2的值

DECODE(value,if1,then1,if2,then2) 如果value的值为if1，则返回then1；以此类推；否则返回else的值

NULLIF(expr1, expr2) 如果两个表达式不相等，NULLIF 返回第一个 expression1 的值。如果两个表达式相等，NULLIF 返回空值NULL。

COALESCE(expr1, expr2) COALESCE返回参数列表中第一个非空表达式。必须指定最少两个参数。如果所有的参数都是null，则返回null。

函数	功能
NVL(exp1,exp2)	如果exp1的值为NULL，返回exp2的值，否则返回exp1的值
NVL2(exp1,exp2,exp3)	如果exp1的值为NULL，则返回exp3的值，否则返回exp2的值
DECODE(value,if1,then1,if2,then2)	如果value的值为if1，则返回then1；以此类推；否则返回else的值
NULLIF(expr1, expr2)	如果两个表达式不相等，NULLIF 返回第一个 expression1 的值。如果两个表达式相等，NULLIF 返回空值NULL。
COALESCE(expr1, expr2)	COALESCE返回参数列表中第一个非空表达式。必须指定最少两个参数。如果所有的参数都是null，则返回null。

#nvl
SQL>select nvl(null,1) from dual; --1

#nvl2
SQL>select nvl2(null,null,3) from dual; --3

#decode
SQL>select decode(1,3,'aa',1,'bb') from dual;--bb

#nullif
SQL>select nullif('a','aa') from dual;--null

#coalesce
SQL>select coalesce(null,3+2) from dual;--5

六.表空间和用户权限

1.表空间

Oracle数据库包含逻辑结构和物理结构。

物理结构是指构成数据库的一组操作系统文件。

逻辑结构是指描述数据组织方式的一组逻辑概念及它们之间的关系。

表空间是逻辑结构的一个重要组件。表空间可以存放各种应用对象，如表、索引。

而每个表空间由一个或多个数据文件组成。

（1）表空间分类

类别说明

永久性表空间一般保存表、视图、过程和索引等的数据。SYSTEM、SYSAUX、USERS、EXAMPLE表空间是默认安装的

临时性表空间只用于保存系统中短期活动的数据，如排序数据等

撤销表空间用来帮助回退未提交的事务，已提交了的数据在这里是不可以恢复的。一般不需要建临时和撤销表空间，除非把它们转移到其他磁盘中以提高性能

（2）表空间的目的

使用表空间的目的为：

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

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

（3）创建表空间
语法：
SQL>create tablespacename tablename datafile 'filename' [size integer[K|M]]
    [autoextend [off|on]]
tablespacename：创建的表名称

datafile：指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔

filename：数据文件的路径和名称

size：文件大小

autoextend：子句用来启动或禁用数据文件的自动扩展，设置为ON 则空间使用完毕会扩展。

例：
#创建表空间     system用户
SQL> create tablespace haha_test datafile 'd:\data\haha.dbf' size 60m;
（4）查询表空间
SQL> select * from dba_data_files;
（5）修改表空间大小
SQL> alter database datafile 'd:\data\haha.dbf' resize 80m;
（6）给表空间添加数据文件
SQL> alter tablespace haha_test add datafile 'd:\data\haha1.dbf' size 80m           autoextend on;
（7）删除表空间
#删除表空间名称
SQL> drop tablespace haha_test;
#删除表空间并且物理文件
SQL> drop tablespace haha_test
     include contents;

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

2.数据库权限管理

注意：

1.字符串大小敏感

Oracle中存在大小写敏感，mysql不存在

2.日期格式敏感

---修改日期格式

sql>alter session set NLS_DATE_FORMAT ='yyyy-mm-dd';