Oracle基本知识点

Oracle数据库在运行时，可以选择是否开启归档日志，在非归档日志的情况下，Oracle所有的操作日志都写在重做日志当中，当所有重做日志文件写满时（Oracle重做日志是分组的，默认是分为三组），那么就把前面的日志文件覆盖继续写入。而在开启归档日志模式情况下，当重做日志都写满时，继续要写入日志时，会把要覆盖的日志文件写入归档日志当中，然后再对重做日志进行覆盖，因此使用归档日志利于后期进行数据恢复。

查看数据库是否开启归档日志，使用以下命令查询：

重做日志文件的位置和属性信息查询：

创建日志

结构：

1、group n：表示创建日志文件组的组号，在Oracle当中日志文件组的组号是唯一的。

2、filename：表示日志文件组存储的位置。

3、size m：表示日志文件组的大小，默认是50M大小。

添加日志文件进文件组

Oracle数据库中一个日志文件组可以包含多个日志文件，但是必须包含一个文件。因此，我们可以对建好的日志文件组进行扩充

结构：

1、filename：表示日志文件存储的位置。

2、group n：表示添加到那个日志文件组。

删除日志文件

结构：

1、filename：日志文件存储的位置。

2、当日志文件组只有一个日志文件时，是不能进行删除日志文件的，必须把整个日志文件组进行删除。

删除日志文件组

结构：

1、group n：表示要删除的文件组组号。

2、删除文件组，会对应的里面的日志文件一并全部删除。

表空间

Oracle表空间是Oracle数据对象和数据存储的容器，Oracle表空间经常和数据文件成对出现，一个表空间可以对应多个数据文件，而一个数据文件只能在一个表空间当中。我们在创建表空间时，就会默认创建一个数据文件，同理，我们创建数据文件时，必须指定一个表空间。Oracle数据库存储数据是有一个个表空间组成的，一个表空间当中存储着多个数据文件，Oracle的数据（表、索引等数据）存储在数据文件当中，在表空间当中的逻辑单位是段（segment）。

默认表空间

查看表空间：

select * from 表空间名；

创建表空间

结构：

create tablespace：创建表空间的关键字。

tab_name：创建后表空间的名字。

datafile：指定数据文件的路径为filename。

size n：指定数据文件的大小。

[autoextend on next n1 maxsize m /of ]：表示表空间是否是自动扩展的，on 为自动扩展，of为不扩展，当自动扩展时，next n1表示自动扩展的大小,max size m 表示数据文件最大扩展到m大小。

[permanent] ：表示创建的表空间的类型，permanent表示永久表空间，不填都是默认永久表空间。

[extent management local/dictionary]：表示表空间管理的方式，local表示本地的管理模式，dictionary表示数据字典管理模式，默认都是本地管理方式。

eg：

创建一个stu表空间，指定了数据文件为 ' F：\tbplace\stu.dbf '，表空间是自动扩展的，每次自动扩展大小为10M，最大扩展到500M，创建的是永久表空间，用来存储stu用户的数据库对象和数据，管理模式为本地管理。

删除表空间

删除表空间可以通过OEM企业管理、SQL命令两种方式进行直接删除。Oracle删除表空间的时候不需要先删除数据文件，再删除表空间，可以选择删除表空间时，把数据文件一并删除。

结构：

drop tablespace：删除表空间的关键字，tab_name表示表空间名字。

[including contents]：表示在删除表空间的时候把表空间中的数据文件一并删除。

[cascade constraints]：表示在删除表空间的时候把表空间的完整性也一并删除。比如表的外键，和触发器等就是表的完整性约束。

eg：删除stu表空间，并删除表空间的数据文件和完整性。

创建临时表空间

当操作完成后，临时表空间就会自动清空释放

1、create temporary tablespace：表示创建临时表空间，tempname表示创建临时表空间的名字。

2、filename：指定临时表空间数据文件的位置。

3、size m：表示临时表空间的大小。

SQL语句

Oracle字段数据类型

运算符

算术运算符：+ 、- 、* 、/ （/获得的结果是浮点数）

关系运算符：= 、> 、< 、>=、<=、<>或！=

逻辑运算符：AND 、OR 、NOT

Oracle集合运算

Oracle集合运算就是把多个查询结果组合成一个查询结果，Oracle的集合运算包括：INTERSECT（交集）、UINION ALL（交集重复）、UINION（交集不重复）、MINUS（补集）

1.intersect（交集），返回两个查询共有的记录

2.union all（并集重复），返回各个查询的所有记录，包括重复记录。

3.union（并集不重复），返回各个查询的所有记录，不包括重复记录（重复的记录只取一条）

4.minus（补集），返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

注意：当使用Oracle集合运算时，要注意每个独立查询的字段名的列名尽量一致（列名不同时，取第一个查询的列名），列的数据类型，列的个数要一致，不然报错。

创建表并插入数据

查询：

①select * from 表名；--查询所有信息

②select 某字段 from 表名； -- 查询表中某个字段的信息

select 某个字段，某个字段 from表名；--查询表中多个字段的数据

③select distinct 字段名 from 表名；--查询表中某个字段但无重复的数据

select distinct 字段号，字段号 from表名； --查询某几个字段但无重复的数据

起别名：

①直接在字段后面接别名的名称 --eg：select name 姓名，sex 性别 from 表名；

②使用关键字as --eg：select name as 姓名，sex as 数学成绩 from 表名；

条件查询： select * from 表名 where 字段名条件

模糊查询：like

%表示占位符，可以占多个位置 eg：select * from 表名 where字段名 like’%小%‘；--查询字段带小字的数据

_表示占位符，只能占一位 eg：select * from 表名 where 字段名 like’负_‘;查询字段中负开头且后面只有一个字符的数据； like‘_负’；

（要查询数据中带%或_,用escape函数名，escape‘\’，常用 ' \ ' 也可以用其他符号）

eg:select 字段 from 表名 where 字段 like ’\%%\_‘ escape ' \ ';

常用运算符：= 、> 、< 、>=、<=、<>、！=、between、and、or、in 、not(取反)

插入:insert into

结构：insert into 表名（列名1，列名2，列名3......) values(值1，值2，值3...)

结构解析：

1.列名可以省略，当列名不填时，默认的是表中所有的列，列的顺序是按照建表的顺序进行排名的。

2.列名的数量和值的数量要一致，并且值的类型和列的类型一一对应。

3.当表当中某些字段设置了某些约束的情况下，必须按照字段的约束来进行该值的插入，例如：学生信息表（stuinfo）当中设置有主键（主键字段是stuID），因此该字段必须具有唯一性，不能和原有的数据重复，age，stuname，classno等字段是必填字段，因此是必须有值的。

eg：向stuinfo插入一条数据

insert into student.stuinfo（stuID，stuname，age） values（‘A0001’，‘张三’，‘18’）；

eg：insert插入一个select的结果集 insert into 表名 select 子句；

insert into student.stuinfo select * from student.stuinfo1 --把表student.stuinfo1的数据一次插入表stuinfo中。

更新：update

结构：update 表名 set 列名1=值1，列名2=值2，列名3=值3.....where 条件

删除：delete

delete

结构：delete from 表名 where 条件（当无条件时则是把表中数据全部删除）

truncate命令：也是数据删除命令，是直接把Oracle表数据一次删除的命令，truncate命令是一个DDL命令，不同于delete是DML命令。

结构：truncate table 表名；

delete与truncate的区别

1.truncate是DDL命令，命令执行完就提交，删除的数据不能恢复；delete命令是DML命令，命令执行完需提交后才能生效，删除后的数据可以通过日志文件恢复。

2.如果表中的数据量较大，truncate速度比delete速度快。

3.truncate删除将重新设置表索引的初始大小，而delete不能。

4.delete能触发表上相关的delete触发器，而truncate则不会触发。

5.delete删除的原理是一次一次从表中删除数据，并将删除操作当做事物记录在数据库的日志当中，以便进行数据回滚。而truncate是一次性进行数据页的删除，因此执行速度快，但不能回滚。

总之，truncate命令是属于DDL命令，一次性删除表中所有数据，并且数据不能恢复，应当慎用truncate命令。

分组：group by

结构：select...from...where...group by...

eg:select sex, avg(math) from student where age >=18 group by sex; --求表中年龄大于18岁的男女生的数学平均成绩并按照sex进行分组

null运算nvl()

判断一个数据是否为空时，有is null ，is not null，not...is null

is null是查询值为null的记录，is not null查询值不是null的记录，not is null对isnull取反的记录，not开头是可以查询到0值

当某个值为空时，还能用nvl函数给一个特定的值，eg：nvl（math，res），如果math成绩不是空，就返回math的原本值，如果为null值，就返回res这个值，一般res设置为0.

排序：order by查询结果按某个字段进行排序，默认为升序

①升序排：select * from 表名 order by 字段 asc；

②降序排：select * from 表名 order by 字段 desc；

③多要求排： select * from 表名 order by 字段名1 asc，字段名2 desc；--以字段1为排序标准进行升序排序，if字段1相同时，以字段2进行降序排序。

伪列与表达式

查询不存在的列就是伪列，当需要的结果不能直接从表中得到，需要经过计算来展示则可以使用伪列+表达式来实现

“创建”伪列：select 字段1，字段2，字段3（不存在表中） from 表名；--字段3不存在与表中即“创 --建”了一个伪列；

伪列添入内容： select 字段1，字段2，（分数1+分数2）/2 平均分 from 表名；--增加了一行位 --列，并且给伪列起了别名叫做平均分

字符串拼接：数字或者字符串，拼接符号：||

语法：select 字段1，字段2 || ’A‘ 别名 from 表名；

eg：select 字段1 || ‘1’ 加一，字段2 || ’A‘ 加 A，字段3|| ’A‘，’1‘ from 表名；

虚表：

Oracle的一个叫dual的虚表，是用来构成select的语法规则，Oracle保证dual里面永远只有一条记录。该表只有一行一列，它可以进行增删改查等操作，但不能随便drop表，可能导致数据库系统出现问题，但我们可以利用虚表来计算一些数值，eg12*12：使用 select 12*12 from dual；（以后查询恢复默认）

Oracle连接查询

Oracle连接查询，包含内关联（inner join）和外关联（outer join），其中外关联又分为左外关联（left outer join），右外关联（right outer join）和全外关联（full outer join）其中外关联可以使用（+）来表示。

内连接（自然连接）

Oracle内连接：两张表通过某个字段进行内关联，查询结果是通过该字段按关系运算符匹配出的数据行。其中可以包括：

1.等值连接：在连接条件中使用等于号（=）运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列。

2.不等连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值，这些关系运算符包括：>、 >= 、<= 、!> 、!< 、<>

外连接

外连接，返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接或左连接))、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。

1、left join(左连接)等价于（left outer join）返回包括左表中的所有记录和右表中联结字段相等的记录。

2、right join(右连接)等价于（right outer join）返回包括右表中的所有记录和左表中联结字段相等的记录。

3.、full join （全连接）等价于（full outer join）查询结果等于左外连接和右外连接的和。

Oracle视图

Oracle视图可以理解为数据库中一张虚拟的表，它是通过一张或者多张基表进行关联查询后组成一个虚拟的逻辑表。查询视图，本质上是对表进行关联查询。

视图的本身是不包含任何数据，只是一个查询结果，当基表的数据发生变化时，视图里面的数据也会跟着发生变化。在实际开发过程中遇到的视图大概可以分为三种：单表视图，多表关联视图，视图中含有子视图。

视图的作用：

1.使数据简单化：可以将复杂的查询创建成视图，提供给他人使用，他人就不需要去理解其中复杂性的业务关系或逻辑关系。

2.表结构设计的补充：系统在开始设计时，大部分程序是直接访问表结构的数据的，但随业务的变化、系统的更新等，造成了某些表结构的不适用，这时候去修改表结构对系统的影响太大，开发成本较高，使用创建视图来对表结构的设计进行补充，降低开发成本。程序可以直接通过查询视图得到想要的数据。

3.增加安全性：视图可以把表中指定的字段展示给用户，而不必把表中所有字段都展示给用户。

创建视图

结构：create [or replace] view 视图名 --or replace：如果视图已经存在，则替换旧视图

select

[with read only constraint] --with read only：默认不填的，用户是可以通过视图对基表执行增删改操作，但是有很多在基表上的限制（比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行 insert 操作，或者基表设置了某些约束，这时候插入视图或者修改视图的值，有可能会报错）， WITH READ ONLY 说明视图是只读视图，不能通过该视图进行增删改操作。但是在现实开发中，基本上不通过视图对表中的数据进行增删改操作。