关于最近一周学习的oracle

创建用户

orcale中，一般不会轻易在一个服务器上创建多个数据库，在一个数据库中，不同的项目由不同的用户访问，每个用户拥有自身创建的数据库对象，因此，用户的概念在orcale至关重要。
创建用户的语句：

CREATE USER 用户名 IDENTIFIED BY 口令 [ACCOUNT LOCK|UNLOCK] 

LOCK|UNLOCK 创建用户时是否锁定，默认为锁定状态。锁定的用户无法正常的登录进行数据库操作。如果你所使用的用户被锁定，可以使用以下语句来解锁（system用户下解锁所要解锁的用户）：

alert user scott account unlock/lock

修改密码：

alter user system(用户名) identified by 密码

授予权限

尽管用户成功创建，但是还不能正常的登录 Oracle 数据库系统，因为该用户还没有任 何权限。如果用户能够正常登录，至少需要 CREATE SESSION 系统权限。

Oracle 用户对数据库管理或对象操作的权利，分为系统权限和数据库对象权限。系统权限比如：CREATE SESSION，CREATE TABLE 等，拥有系统权限的用户，允许拥有相应的系统操作。数据库对象权限，比如对表中的数据进行增删改操作等，拥有数据库对象权限的用户可以对所拥有的对象进行对应的操作。
还有一个概念就是数据库角色，数据库角色就是若干个系统权限的集合。下面介绍几个常用角色：

• CONNECT 角色，主要应用在临时用户，特别是那些不需要建表的用户，通常只赋予他们CONNECT ROLE。CONNECT 是使用 Oracle 的简单权限，拥有 CONNECT 角色的用户，可以与服务器建立连接会话（session，客户端对服务器连接，称为会话） 。
• RESOURCE 角色，更可靠和正式的数据库用户可以授予 RESOURCE ROLE。RESOURCE 提供给用户另外的权限以创建他们自己的表、序列、过程（procedure）、触发器 （trigger）、索引（index）等。
• DBA 角色，DBA ROLE拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。用户 SYSTEM 拥有 DBA 角色。

一般情况下，一个普通的用户（如 SCOTT），拥有 CONNECT 和 RESOURCE 两个角色即可 进行常规的数据库开发工作。可以把某个权限授予某个角色，可以把权限、角色授予某个用户。系统权限只能由 DBA 用户授权，对象权限由拥有该对象的用户授权，授权语法是：

GRANT角色|权限 TO 用户（角色） 

可以授权当然也可以回收权限：

REVOKE 角色|权限 FROM 用户（角色） 

创建表空间

在数据库系统中，存储空间是较为重要的资源，合理利用空间，不但能节省空间，还可 以提高系统的效率和工作性能。Oracle 可以存放海量数据，所有数据都在数据文件中存储。 而数据文件大小受操作系统限制，并且过大的数据文件对数据的存取性能影响非常大。同时 Oracle 是跨平台的数据库，Oracle 数据可以轻松的在不同平台上移植，那么如何才能提供统 一存取格式的大容量呢？Oracle 采用表空间来解决。
Oracle 中所有的数据（包括系统数据），全部保存在表空间中，常见的表空间有：

• 系统表空间：存放系统数据，系统表空间在数据库创建时创建。表空间名称为 SYSTEM。存放数据字典和视图以及数据库结构等重要系统数据信息，在运行时如 果 SYSTEM 空间不足，对数据库影响会比较大，虽然在系统运行过程中可以通过命 令扩充空间，但还是会影响数据库的性能，因此有必要在创建数据库时适当的把数 据文件设置大一些。
• TMEP 表空间：临时表空间，安装数据库时创建，可以在运行时通过命令增大临时 表空间。临时表空间的重要作用是数据排序。比如当用户执行了诸如 Order by 等 命令后，服务器需要对所选取数据进行排序，如果数据很大，内存的排序区可能装 不下太大数据，就需要把一些中间的排序结果写在硬盘的临时表空间中。
• 用户表自定义空间：用户可以通过 CREATE TABLESPACE 命令创建表空间。

关于表空间的语法：

CREATE TABLESPACE 空间名称
DATAFILE
 '文件名1' SIZE 数字M  ，
，'文件名2' SIZE 数字M 
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 数字M 

文件名包括完整路径和文件名，每个数据文件定义了文件的初始大小，初始大小一 般以“M”为单位。一个表空间中可以有多个数据文件。 2. EXTENT MANAGEMENT LOCAL 指明表空间类型是：本地管理表空间。本地管理表空 间要求 Oracle 中的数据分区（Extent）大小统一。 3. UNIFORM SIZE：指定每个分区的统一大小。

当表空间的空间不足时可以使用 ALTER TABLESPACE 命令向表空间中追加数据文件扩充表空间。 扩充表空间：

ALTER TABLESPACE MYSPACE 
ADD DATAFILE 'D:/C.ORA' SIZE 10M    
/  

表空间可以在不使用时删除，使用 DROP TABLESPACE 命令
数据库的所有数据全部在某一表空间中存放，在创建用户时，可以为用户指定某一表空 间，那么该用户下的所有数据库对象（比如表）默认都存储在该空间中。

- 为用户指定表空间

 CREATE USER ACONG IDENTIFIED BY ACONG 
 DEFAULT TABLESPACE MYSPACE 
 /

- 为表指定表空间

 CREATE TABLE SCORES 
 (
  		ID NUMBER , 
   		TERM VARCHAR2(2), 
   		STUID VARCHAR2(7) NOT NULL, 
   		 EXAMNO VARCHAR2(7) NOT NULL, 
   		 WRITTENSCORE NUMBER(4,1) NOT NULL, 
   		 LABSCORE NUMBER(4,1) NOT NULL 
 ）
 TABLESPACE MYSPACE 

SQL数据类型

简介

SQL 是结构化查询语言（Structured Query Language），专门用于数据存取、数据更新及数据库管理等操作。并且已经学习了用 SQL 语 句对数据库的表进行增删改查的操作。
在 Oracle 开发中，客户端把 SQL 语句发送给服务器，服务器对 SQL 语句进行编译、执 行，把执行的结果返回给客户端。Oracle SQL 语句由如下命令组成：

1. 数据定义语言（DDL），包括 CREATE（创建）命令、ALTER（修改）命令、DROP（删 除）命令等。
2. 数据操纵语言（DML），包括 INSERT（插入）命令、UPDATE（更新）命令、DELETE （删除）命令、SELECT … FOR UPDATE（查询）等。
3. 数据查询语言（DQL），包括基本查询语句、Order By 子句、Group By 子句等。
4. 事务控制语言（TCL），包括 COMMIT（提交）命令、SAVEPOINT（保存点）命令、 ROLLBACK（回滚）命令。
5. 数据控制语言（DCL）， GRANT（授权）命令、REVOKE（撤销）命令。

Oracle数据类型

Oracle 数据库的核心是表，表中的列使用到的常见数据类型如下：

1. CHAR(length) ：存储固定长度的字符串。参数 length 指定了长度，如果存储的字符串长
   度小于 length，用空格填充。默认长度是 1，最长不超过 2000 字节
2. VARCHAR2(length) ： 存储可变长度的字符串。length 指定了该字符串的最大长度。默认长度
   是 1，最长不超过 4000 字符。
3. NUMBER(p，s) ：既可以存储浮点数，也可以存储整数，p 表示数字的最大位数（如果是
   小数包括整数部分和小数部分和小数点，p 默认是 38 为），s 是指小数位数。
4. DATE ： 存储日期和时间，存储纪元、4 位年、月、日、时、分、秒，存储时间从公元前 4712 年 1 月 1 日到公元后 4712 年 12 月 31 日。
5. TIMESTAMP ： 不但存储日期的年月日，时分秒，以及秒后 6 位，同时包含时区。
6. CLOB ： 存储大的文本，比如存储非结构化的 XML 文档 。
7. BLOB ： 存储二进制对象，如图形、视频、声音等。

创建表和约束

Oracle 创建表同 SQL Server和mysql 一样，使用 CREATE TABLE 命令来完成。
当然也可以根据结果集创建表

CREATE TABLE 表名 AS SELECT 语句 

使用上面命令创建的新表中，不存在任何约束，并且把查询的数据一起插入到新表中。 如果只复制表结构，只需使查询的条件不成立（比如 where 1=2），就不会查询从出任何数据，从而复制一个表结构

创建约束则使用如下命令：

 ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容

约束共分为*主键约束（PRIMARY KEY），检查约束（ CHECK ） ，外键约束 （ FOREIGN KEY ）， 非空约束（ NOT NULL ）， 唯一约束（ UNIQUE ） 五种。

数据操纵语言（DML） （其实和别的数据库基本一样）

• 简单查询
  SELECT *|列名|表达式 FROM 表名 WHERE 条件 ORDER BY 列名
• 数据插入
  INSERT INTO 表名(列名 1，列名 2……) VALUES (值 1，值 2……)
• 更新数据
  UPDATE 表名 SET 列名 1=值，列名 2=值…… WHERE 条件
• 删除数据
  DELETE FROM 表名 WHERE 条件
• TRUNCAT
  TRUNCATE TABLE 表名       一次性删除所有表数据

TRUNCAT和DELETE区别：

• TRUNCATE 是 DDL 命令，删除的数据不能恢复；DELETE 命令是 DML 命令，删除后 的数据可以通过日志文件恢复。
• 如果一个表中数据记录很多，TRUNCATE 相对 DELETE 速度快。
• 由于 TRUNCATE 命令比较危险，因此在实际开发中，TRUNCATE 命令慎用

操作符

• 算术运算
  只有+、-、*、/四个，其中除号 (/)的结果是浮点数。求余运算只能借助函数：MOD(x,y)：返回 x 除以 y 的余数。
• 关系运算和逻辑运算
  Oracle 中 Where 子句经中经常见到关系运算和逻辑运算，常见的关系运算有： =，<>或者!= ，<
  逻辑运算符有三个：AND、OR、NOT （和别的数据库一样的。。。）
• 字符串连接操作符（||）
  在 Oracle 中，字符串的连接用双竖线（||）表示，字符串可以用单引号，也可以用双引号，在别名中存在空格时，必须用双 引号。在表名、列名时用双引号。
• 消除重复行
  在 Oracle 查询中结果中，可能出现若干行相同的情况，那么可以使用 DISTINCT 消除重复行。
• NULL操作
  在查询条件中 NULL 值用 IS NULL 作条件，非 NULL 值用 IS NOT NULL 做条件。
• IN 操作
  在 Where 子句中可以使用 IN 操作符来查询其列值在指定的列表中的行，对应 IN 操作的还有 NOT IN，用法一样，结果相反。
• BETWEEN…AND…
  在 WHERE 子句中，可以使用 BETWEEN 操作符来查询列值包含在指定区间内的行。
• LIKE模糊查询
  模糊查询使用 LIKE 关键字通过字符匹配检索出所 需要的数据行。字符匹配操作可以使用通配符“%”和“_”:
  %：表示零个或者多个任意字符。
  _：代表一个任意字符。

集合运算

• INTERSECT(交集)，返回两个查询共有的记录。
• UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
• UNION(并集)，返回各个查询的所有记录，不包括重复记录。
• MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩 余的记录。

当使用集合操作的时候，要注意：查询所返回的列数以及列的类型必须匹配，列名可以不同。

连接查询

内连接(inner join)、外连接(outer join)，外连接又分为左外连接(left outer join)和右外连接(right outer join)。Oracle 中对两个表或者若干表之间的外连接用(+)表示。

内连接：根据指定的连接条件进行连接查询，因此满足连接条件的数据才会出现在结果集。

两个表进行内连接查询时，先找到表1的第一条记录，然后从头到尾扫描表2，将符合连接条件的记录与表1第一条记录连接成结果；
当表2扫描一遍后，再从表1的第二条记录开始，从头到尾扫描表2，将符合连接条件的与表1第二条记录连接成结果…
:
直至表1中所有记录处理完毕为止

外连接
在内连接的基础上，将某个连接表不符合连接条件的记录加入结果集。

• 左外连接：在内连接基础上，将连接操作符左侧表不符合连接条件的记录加入结果集中，右侧表则用null填充。（个人觉得这更适合解释标准SQL语句的连接方式）。
• 右外连接：在内连接基础上，将连接操作符右侧表不符合连接条件的记录加入结果集中，左侧表则用null填充。（个人觉得这更适合解释标准SQL语句的连接方式）。

子查询
在 SELECT、UPDATE、DELETE 语句内部可以出现 SELECT 语句。内部的SELECT 语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表。子查询的类型有：

1. 单行子查询：不向外部返回结果，或者只返回一行结果 （ANY，ALL ）。
2. 多行子查询：向外部返回零行、一行或者多行结果 （ANY，ALL ）。

大于ALL：比子查询结果中所有值还要大，也就是说，比子查询结果中最大值还要大。
小于ALL : 表示比最小值还要小。
小于any:比子查询结果中任意的值都小，也就是说，比子查询结果中最小值还小。
大于any 表示比子查询结果中最大的还大。

伪列

在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中 的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。接下来学习两个伪 列：ROWID 和 ROWNUM。

1. ROWID
   表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的物理地 址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的标识表中的一行。由 于 ROWID 返回的是该行的物理地址，因此使用 ROWID 可以显示行是如何存储的。

2. ROWNUM
   在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回 1，第二行返回 2，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数。如果条件查询中ROWNUM大于某一正整数，则不返回任何结果，下面是查询员工表5-10的记录的sql语句

select * from (select rownum r,empno,ename from emp where rownum <= 10 )s 
where empno not in (select empno from emp where rownum <= 5);

可以查询小于10的记录，再减去小于5的记录，还有另一种做法：

select * from (select rownum r,empno,ename from emp)s where r between 6 and 10;

可以在外层再包装一层，在外层看来，内层查询的 ROWNUM 是正常的一 列。

函数

Oracle SQL提供了用于执行特定操作的专用函数。这些函数大大增强了 SQL 语言的功能。 函数可以接受零个或者多个输入参数，并返回一个输出结果。Oracle 数据库中主要使用两种类型的函数：

1. 单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果， 比如：MOD(x,y)返回 x 除以 y 的余数（x 和 y 可以是两个整数，也可以是表中的整 数列）。常用的单行函数有： 字符函数，数字函数，转换函数，日期函数。
2. 聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如 SUM(x) 返回结果集中 x 列的总合。

• 字符函数
  字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。下表 列出了常用的字符函数。
  ASCII(x)：返回字符 x 的 ASCII 码。
  CONCAT(x,y) ： 连接字符串 x 和 y。
  INSTR(x, str [,start] [,n) ：在 x 中查找 str，可以指定从 start 开始，也可以指定从 第 n 次开始。
  LENGTH(x) : 返回 x 的长度。
  LOWER(x) ： x 转换为小写。
  UPPER(x) ： x 转换为大写。
  LTRIM(x[,trim_str]) ：把 x 的左边截去 trim_str 字符串，缺省截去空格。
  LTRIM(x[,trim_str]) ： 把 x 的左边截去 trim_str 字符串，缺省截去空格。
  RTRIM(x[,trim_str]) ：把 x 的右边截去 trim_str 字符串，缺省截去空格。
  TRIM([trim_str FROM] x) ： 把 x 的两边截去 trim_str 字符串，缺省截去空格。
  REPLACE(x,old,new) ： 在 x 中查找 old，并替换为 new。
  SUBSTR(x,start[,length]) ： 返回 x 的字串，从 staart 处开始，截取 length 个字符， 缺省 length，默认到结尾。

• 数字函数
  数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。
  ABS(x) ： x 绝对值 。
  ACOS(x) ：x 的反余弦 。
  COS(x) ： 余弦。
  CEIL(x) ： 大于或等于 x 的最小值 。
  FLOOR(x) ： 小于或等于 x 的最大值 。
  LOG(x,y) ： x 为底 y 的对数 。
  MOD(x,y) ： x 除以 y 的余数 。
  POWER(x,y) ： x 的 y 次幂 。
  ROUND(x[,y]) ： x 在第 y 位四舍五入 。
  SQRT(x) ： x 的平方根 。
  TRUNC(x[,y]) ：x 在第 y 位截断 。

• 日期函数
  日期函数对日期进行运算

1. ADD_MONTHS(d,n)，在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。 d 表示日期，n 表示要加的月数。
2. LAST_DAY(d)，返回指定日期当月的最后一天。
3. ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值，d 是日期，fmt 是格式 模型。默认 fmt 为 DDD，即月中的某一天。
4. EXTRACT(fmt FROM d)，提取日期中的特定部分。

• 转换函数
  TO_CHAR(d|n[,fmt]) ： 把日期和数字转换为制定格式的字符串
  TO_NUM函数 ： 求和
  TO_DATE(x [,fmt]) ： 把一个字符串以 fmt 格式转换为一个日期类型
  TO_NUMBER(x[,fmt]) ： 把一个字符串以 fmt 格式转换为一个数字

• 其他单行函数
  NVL(x,value) ： 如果 x 为空，返回 value，否则返回 x。
  NVL2(x,value1,value2) ： 如果 x 非空，返回 value1，否则返回 value2。

• 聚合函数
  聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。
  SUM函数 ：求和
  AVG函数 ：平均值

序列

序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列， 序列中的可以升序生成，也可以降序生成。创建序列的语法是：

CREATE SEQUENCE sequence_name 
[START WITH num] 
[INCREMENT BY increment]
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
[CYCLE|NOCYCLE] 
[CACHE num|NOCACHE] 

START WITH：从某一个整数开始.
INCREMENT BY：增长数
MAXVALUE：指最大值。
NOMAXVALUE：这是最大值的默认选项，升序的最大值是：1027，降序默认值是-1。
MINVALUE：指最小值。
NOMINVALUE：这是默认值选项，升序默认值是 1，降序默认值是-1026。
CYCLE：表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最 小值后，从最大值重新开始。

NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默认NOCYCLE

CACHE：使用 CACHE 选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统 再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。Oracle 默认会生产 20 个序列号。
NOCACHE：不预先在内存中生成序列号。

序列创建之后，可以通过序列对象的 CURRVAL 和 NEXTVAL 两个“伪列”分别访问该序 列的当前值和下一个值。

	NEXTVAL 
 SELECT MYSEQ.NEXTVAL FROM DUAL; 
 	CURRVAL 
 SELECT MYSEQ.CURRVAL FROM DUAL; 

使用 ALTER SEQUENCE 可以修改序列，在修改序列时有如下限制： 1. 不能修改序列的初始值。 2. 最小值不能大于当前值。 3. 最大值不能小于当前值。

使用 DROP SEQUENCE 命令可以删除一个序列对象。

ALTER SEQUENCE MYSEQ   
MAXVALUE 10000
MINVALUE -300   
 / 

SEQUENCE ALTERED 
DROP SEQUENCE MYSEQ; 
SEQUENCE DROPPED 

视图

视图（View）实际上是一张或者多张表上的预定义查询，这些表称为基表。从视图中 查询信息与从表中查询信息的方法完全相同。只需要简单的 SELECT…FROM 即可。
视图具有以下优点：

1. 可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基 表。
2. 可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。
3. 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。
4. 从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。

语法
OR REPLACE：如果视图已经存在，则替换旧视图
FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表 创建成功后，视图才能正常使用
NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。
WITH READ ONLY：默认可以通过视图对基表执行增删改操作。

创建视图：

CREATE [OR REPLACE] 
[{FORCE|NOFORCE}]
 VIEW view_name
AS 
SELECT查询 [WITH READ ONLY CONSTRAINT]

索引

数据库中索引（Index）的概念与目录的概念非常类似。如果某列出现在查询的条件中， 而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特
定列中的数据排序，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中， Oracle 会自动的引用该索引，先从索引表中查询出符合条件记录的 ROWID，由于 ROWID 是 记录的物理地址，因此可以根据 ROWID 快速的定位到具体的记录，表中的数据非常多时， 引用索引带来的查询效率非常可观。
Oracle 数据库会为表的主键和包含唯一约束的列自动创建索引。索引可以提高查询的效 率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。

创建索引：

CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…]) 

UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引
index_name：指定索引名
tabl_name：指定要为哪个表创建索引
column_name：指定要对哪个列创建索引。我们也可以对多列创建索引；这种索引 称为组合索引

在 select 语句查询时，Oracle 系统会自动为查询条件上的列应用索引。索引就是对某一 列进行排序，因此在索引列上，重复值越少，索引的效果越明显。
Oracle 可以为一些列值重复非常多且值有限的列（比如性别列）上创建位图索引。

有错误请指出，谢谢！