Oracle（一）

文章目录

• ​​1、Oracle简介​​

• ​​1.1、Oracle公司发展历史​​
• ​​1.2、Oracle几个重要的版本​​

• ​​2、Oracle安装和配置​​

• ​​2.1、安装Oracle数据库​​
• ​​2.2、卸载Oracle数据库​​
• ​​2.3、Oracle中主要系统服务的作用​​

• ​​3、Oracle基本概念​​

• ​​3.1、Oracle相关术语​​
• ​​3.2、Oracle基本使用​​
• ​​3.3、简单函数​​
• ​​3.4、SQL语言的特点​​
• ​​3.5、SQL语言的分类​​

• ​​4、数据库定义语言（DDL）​​

• ​​4.1、数据类型​​
• ​​4.2、创建表​​
• ​​4.3、复制表​​
• ​​4.4、删除表​​
• ​​4.5、修改表​​
• ​​4.6、查找表​​
• ​​4.7、还原表​​
• ​​4.8、维护表（字段的增、删、改）​​

• ​​5、约束​​

• ​​5.1、非空约束​​
• ​​5.2、主键约束​​
• ​​5.3、唯一索引​​
• ​​5.4、外键约束​​
• ​​5.5、检查约束​​
• ​​5.6、默认约束​​

• ​​6、数据库操纵语言（DML）​​
• ​​7、数据库查询语言（DQL）​​

• ​​7.1、普通查询​​
• ​​7.2、筛选查询​​
• ​​7.3、多表关联查询​​
• ​​7.4、子查询​​

1、Oracle简介

1.1、Oracle公司发展历史

Oracle公司是世界上最大的软件提供商之一，与之并列的还有Microsoft和Adobe。并且随着Oracle的发展，它已经成为了企业办公平台的最大软件提供商之一。

Oracle的中文含义叫做甲骨文，在古希腊神话之中被翻译为“神谕”、“上帝的宠儿”。实际上Oracle起源来自于最早的IBM一个分析人员的论文——论关系型数据库的发展。

于是这个时候Oracle的创始人开始开发自己的数据库软件，这个数据库就是后来的Oracle数据库。

左起为Ed Oates，Bruce Scott，Bob Miner，Larry Ellison（高个不戴眼镜）

Larry Ellison（拉里·埃里森）

后来Scott由于不满公司内部资源的分配，离开了Oracle公司，并开发了一套属于自己的数据库——PointBase，不过此数据库没有得到一个很好的发展。后来到了2000年之后，我们的PointBase开始做为了BEA公司的workshop开发策略一起进行了推广，不过后来BEA被Oracle收购了，以至于PointBase数据库也就基本消失了

在Oracle公司策略之中，所有的发展都是以微软为竞争对手展开的，意味着Oracle所有的产品都是有针对性地推出，例如：微软推出地办公平台Office，Oracle就开发了定制化的ERP、CRM系统。后来Oracle又收购了SUN公司，取得了Java，所以在开发语言的技术上就完全超过了微软的.net平台的开发人群。但是有遗憾的就是，在云时代的过程，Oracle落后了。以至于后来Oracle开始大力推广它自己的云服务后来推出的Oracle 12C的版本，也是支持所谓的云时代。

1.2、Oracle几个重要的版本

1、Oracle 8/Oracle 8i：只有一张CD的大小，大约670M。其中的 i 为internet，表示我们的Oracle开始进军网络。

2、Oracle 9i：大小为3张CD。属于Oracle 8i的稳定版本。

3、Oracle 10g：大小约为630M。使用了网格计算的方式。提升了数据库的分布式的访问性能。

4、Oracle 11g：大小约为1.7G。属于Oracle 10g的稳定版本。

5、Oracle 12C：指的是云服务的支持。

…

2、Oracle安装和配置

Oracle它在使用的时候是不收费的，但是如果我们在工作中进行了使用，一定要缴费。

2.1、安装Oracle数据库

详细过程请查看链接：

​​Oracle 11g及PLSQL可视化界面的安装与使用（内附软件）​​

2.2、卸载Oracle数据库

安装成功后的卸载：

1. 使用Oracle提供的卸载程序
2. 重新启动电脑，进入安全模式下
3. 找到系统上的对应的磁盘文件，并且删除
4. 运行注册表命令“regedit.exe”，查找所有与Oracle相关的内容，并且删除
5. 重新启动电脑后即可

安装失败后的卸载：

1. 重新启动电脑，进入安全模式下
2. 找到系统上的对应的磁盘文件，并且删除
3. 运行注册表命令“regedit.exe”，查找所有与Oracle相关的内容，并且删除
4. 重新启动电脑后即可

如果都不行：那就重装系统吧！！！没有什么是重装一次系统解决不了的，实在不行那就装两次。

2.3、Oracle中主要系统服务的作用

Oracle数据库安装完成后会出现一系列的服务。这个在我们安装教程中（服务端的安装中第12点）已经讲解过，这里只进行简单的讲解。

每次使用我们的Oracle数据库的时候，只需要启动两个服务即可（其他的都可以关闭）：

• OracleOraDb11g_home1TNSListener：是监听服务，如果要通过程序或者是不同的客户端来连接Oracle的时候，此服务必须启动，否则无法连接
• OracleServiceORCL：数据库的实例服务（保存所有的数据），它的命名标准：OracleServiceSID每当为系统增加一个数据库时候都会自动的出现一个类似的服务名称

• 在默认情况下，SID的名称和数据库的名称是完全一直的，在网络上使用的是SID的名字

3、Oracle基本概念

3.1、Oracle相关术语

数据库：

Oracle数据库是数据的物理存储。这就包括（数据文件ORA或者DBF、控制文件、联机文件、参数文件）。其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库

实例：

一个Oracle实例（Oracle Instance）有一系列的后台进程（Background Processes）和内存结构（Memory Structures）组成。一个数据库可以有n个实例

用户：

用户是在实例下建立的。不同实例可以建相同名字的用户。

表空间：

表空间是Oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表孔甲，每个表空间包含了在逻辑上相关联的一组结构。每个数据库之上有一个表空间（称之为sysytem空间）。每一表空间由同一个磁盘上的一个或多个文件组成，这些文件叫数据文件（datafile）。一个数据文件只能属于一个表空间。

模式：

是一个数据库对象地集合。模式为一个数据库对象所有，并且具有与该用户相同的名称，比如system模式，scott模式等。在一个模式内部不可以直接访问其他模式的数据库对象，及时在具有访问权限的情况下，也需要指定模式名称才可以访问其他模式的数据库对象。

--在权限满足的情况下，访问对应模式下的表
select * from scott.emp

用户模式：

在Oracle数据库中，为了方便管理用户所创建地数据库对象（表，索引，视图等），引入了模式概念，这样某个用户所创建地数据库对象就都属于用户模式。

模式对象：

是由用户创建的逻辑结构，用于存储或引用数据，比如（表、视图、索引等）

Oracle提供scott模式的目的，就是为了给用户提供一些实例表和数据来展示Oracle数据库的一些特性。scott模式拥有的模式对象都是数据表（emp、dept、salgrade、bouns）

3.2、Oracle基本使用

1）查看表结构

--查看我们dba_users表的表结构
desc dba_users

2）列的主要类型

• Number(n)：表示的数字，为数字的长度
• Number(5,1)：表示数字，小数点占1位，整数占4位，总共5位
• Varchar2(n)：表示可变字符串，n为字符串长度
• Date：表示日期

3）连接符：作用类似于MySQL的concat

||

--在我们查询结果中加上指定的中文修饰符
select '学号:'||num',姓名:'||name||',年龄:'||age  from student

结果：

学号:4115,姓名:默辨,年龄:21

4）去重

--查询出学生表的年龄字段，并且把重复的去除掉
select distinct age from student

5）别名

--给查询出来的字段设置别名，用于结果集时的显示，as可以省略
select num as 学号,name 姓名,age 年龄 from student

注意：

字段取别名以后，不能使用字段的别名来当作我们的条件组成部分；

但是表取别名以后，是可以用来当作我们的条件组成部分的。

示例：

select num as n from student where n = 4115  --这个是错误的

select num as n from student s where s.num = 4115  --这个是正确的

3.3、简单函数

AVG(列名)：求平均值
SUM(列名)：求和
MAX(列名)：求最大值
MIN(列名)：求最小值
COUNT(列名)：求查询结果中的记录数

3.4、SQL语言的特点

1、集合性

允许一条SQL语句的结果作为另一条SQL语句的输入

2、统一性

SQL为许多任务提供了同意的命令，方便用户学习和使用，主要体现在：

• 查询数据
• 在表中插入、修改、删除记录
• 建立、修改和删除数据对象
• 控制对数据和数据对象的读写
• 保证数据库一致性和完整性

3.5、SQL语言的分类

• 数据库查询语言（DQL）：主要是包括select。对表数据操作
• 数据库操纵语言（DML）：主要包括insert、update、delete。对表数据操作
• 数据库定义语言（DDL）：主要包括create、alter、drop。对表本身及结构操作
• 数据库控制语言（DCL）：用于执行权限授予和权限收回操作。grant、revoke。对表权限操作
• 事务控制语言（TCL）：用于维护数据的一致性。commit： 提交对数据库的更改；rollback：取消对数据库的更改

4、数据库定义语言（DDL）

数据表的逻辑结构：表是最常见的一种组织数据的方式，一张表一般都具有多个列（字段）。每个字段都具有特定的属性。包括字段名，数据类型，字段长度，约束，默认值等

4.1、数据类型

1）字符类型

• char：用于存储固定长度的字符串。范围是1-2000字节
• varchar2：用于存储边长，而非固定长度的字符串

2）数值类型

• number：用于存储整数或者浮点数，精度为38位
• number(p,s)：p表示精度（总长度），s表示小数位置且四舍五入
• int：用于存储整数

3）日期时间类型

• DATE：可以存储日期和时间组合数据。最常见的获取时间的方法是通过SYSDATE函数，可以获取当前系统的日期值

--dual 可以想象成一个伪表。受限于Oracle的语法，当我们仅需要返回某些数据的时候就可以使用dual表
select sysdate from dual

--to_char()：按照我们规定的格式来转化日期，并且日期变成了字符串
select to_char(sysdate,'yyyy-mm-dd-HH24-MI-SS') from dual

4）ROWID数据类型

• 创建表的时候自动创建。可以理解为ROW的ID，也被称为“伪列类型”，用于在Oracle内部存储表中每条记录的物理地址，在Oracle内部通过ROWID来定位所需记录的，该字段也有数据库本身自己管理。

可以使用ROWID来区分数据完全相同的两行数据

4.2、创建表

基本语法格式：create table 表名（列1 数据类型 约束，列2 数据类型 约束，…）

--创建一个学生表，包括学号、姓名、性别、年龄、系别编号、班级编号、建档日期
create table student(
    sno number(10) not null,
    sname varchar2(20),
    ssex char(1),
    sage number,
    departno number(10) not null, --系别编号
    classno number(10) not null, --班级编号
    regdate date --建档日期
    
);

补充：

1）虽然我们这里创建表时使用的表名为小写的student，但是正真存储到数据库的却是大写的表名。

2）我们在使用where条件过滤时，后面跟的表名一定要大写

3）由于PLSQL无法使用desc命令，所以我们使用SQL Plus查看对应的表结构，此处的表名我们可以使用student

4.3、复制表

create table 表名 as select * from 目标表名;

--创建student表的副本
create table student2 as select * from student;

4.4、删除表

--注意：如果该表存在约束，则必须使用"cascade constraints"这个可选的子句才能删除
drop table 表名 [cascade constraints];

--删除表student2
drop table student2

补充：cascade constraints的理解可以类比于MySQL的外键。我们如果一个表建立了外键依赖，那么我们是没有办法直接删除的，但是Oracle提供了一个在删除表时忽略外键的功能。即在删除表的时候，添加对应的可选项

4.5、修改表

alter table 原表名称 rename to 新表名称

--将表名由student修改为student2
alter table student rename to student2;

4.6、查找表

--查询该用户下的所有表
select * from tab;

4.7、还原表

当用户需要还原表时，可以使用flashback table 语句（这是一种闪回技术）来进行还原

--还原表对应的语句
flashback table 表名 to before drop
 
flashback table student to before drop;

--清空删除表的残留文件（清空回收站之后无法还原表）
purge recyclebin;

补充：

当我们drop一个表以后，并不是真正的删除它，而是将它放到了对应的文件目录下，如下图。这个文件夹可以类比于win系统中的回收站。只要数据文件还在，那么我们就可以还原。如果清空了删除表的残留文件，那就什么都没有了。

4.8、维护表（字段的增、删、改）

1）添加字段

alter table 表名 add(列明 数据类型); --一个字段可以省略括号

--给student表添加一个sid新字段
alter table student add(sid number(3), xx xxx, ......)

2）删除字段

alter table 表名 drop column 字段名;
alter table 表名 drop (字段名);

--删除student表的sid字段
alter table student drop column sid;

--同时删除两个字段。当字段是一个的时候，我们可以借助括号，省略column的使用
alter table student drop(sid, ssid);

3）修改字段

alter table 表名 modify 字段名 字段新类型
alter table 表名 rename aolumn 原列名 to 新列名

--修改字段的所有信息
alter table student modify ssex char(2);

--仅修改列名
alter table student rename column sid to id;

5、约束

定义约束的时期：创建表和修改表时

约束类型：

1. 非空约束 not null
2. 主键约束 primary key
3. 唯一约束 unique
4. 外键约束 foreign key
5. 检查约束 check
6. 默认约束 default

5.1、非空约束

• 非空约束是限制必须为某个列提供值
• 空值（null）是不存在的值，它既不是数字，也不是空字符串，而是不存在，未知的情况

创建表时的非空约束：

create table stu(
    sid number(10) not null, --建立非空约束
    sname varchar2(6),
);

修改表时的非空约束：

--为stu表中的sname字段设置非空约束
alter table stu modify sname not null;

--删除stu表中的sname字段的非空约束。not null --> null
alter table stu modify sname null;

--为及时查看我们约束是否设置生效，在SQL Plus中打印表结构
desc 表名;

5.2、主键约束

• 主键约束用于唯一的标识表种每一行记录
• 在一个表种，最多有一个主键约束
• 可以由两个或者两个以上列来组合（联合主键）
• 主键约束默认具有非空约束的特性

创建主键约束：

--创建表book，包含name、no、price、author字段，其中no为主键约束
create table book(
    --1、添加主键的方式
    no number(10) primary key,
    name varchar2(10),
    author varchar2(5),
    price number(8)
    --2、在表的末尾来指定
    --constraint PK_book_no primary key(no)
)

--3、直接使用sql命令来设置
alter table book add constraint PK_book_no primary key(no)

删除主键约束：

alter table 表名 drop constraint 约束的名字 删除约束

ALTER TABLE 表名 DROP PRIMARY KEY --删除主键索引

查看约束：

desc 表名 --我的PLSQL无法执行该语句

--user_constraints：内置的视图
select * from user_constraints where table_name = '表名' --表名要大写

--或者使用该方式，该方式能够看到被作用的字段
select * from user_cons_columns

补充：

--constraint type说明
C(check constraint on a table)：检查约束
P(primary key)：主键约束
U(unique key)：唯一约束
R(referential integrity)：引用外键
V(with check option, on a view)：表示视图
O(with read only, on a view)：表示只读

5.3、唯一索引

• 唯一性约束
• 强调所在的列不能有相同的值，可以有空值
• 格式：constraint 约束名 unique

创建一个唯一索引：

CREATE TABLE members
(
       memNo NUMBER(4) PRIMARY KEY,
       memName VARCHAR2(20) NOT NULL,
       phone VARCHAR2(20),
       email VARCHAR2(30),
       --1、直接添加
       qq VARCHAR2(20) CONSTRAINT qq_UK UNIQUE
       --2、在末尾添加
       constraiin qq_UK unique(qq)
    
)

--3、单独添加
alter table members add constraint qq_UK unique


--查看约束，是否创建成功
select * from user_constraints

5.4、外键约束

• 外键约束使用两个表进行关联
• 外键是指“当前表”引用“另外一个表”（即被引用表）的某个列
• 被引用的列必须具有主键约束或者唯一约束

创建一个外键约束：

CREATE TABLE mem
(
       no NUMBER(4) NOT NULL,
       qq VARCHAR2(20),
       --在members表创建的基础上，在mem表中创建外键约束
       CONSTRAINT FK_qq FOREIGN KEY (qq) REFERENCES members(qq) 
);

--单独使用语句创建外键
alter table mem add constraint FK_qq foreign key (qq) references members(qq)

5.5、检查约束

CREATE TABLE bookinfo 
(
       bookid NUMBER(5),
       bookname VARCHAR2(20),
       bookprice NUMBER(5),
       bookauthor VARCHAR2(10),
       --创建一个检查约束，当我们插入的数据不满足条件的时候就会报错
       CONSTRAINT CK_price CHECK (bookprice >= 10 AND bookprice <= 100)
)

--单独创建检车约束
alter table bookinfo add constraint CK_id check (bookid < 10000 and bookid > 999)

--删除检查约束
alter table bookinfo drop constraint CK_id

5.6、默认约束

CREATE TABLE person
(
       NAME VARCHAR2(10) NOT NULL,
       ID NUMBER(10),
       sex VARCHAR2(10),
       --设置默认约束
       age NUMBER(5) DEFAULT 18
)

--单独设置默认约束
alter table person modify sex default '男'

6、数据库操纵语言（DML）

以person表为例

1、插入数据（insert）

insert into 表名 （列名...） values （值...）

--注意插入时的数据类型，如果不匹配则会报错，且字符串只能使用单引号
INSERT INTO person  VALUES ('pan',1,1,22)
INSERT INTO person (NAME,ID,sex,age) VALUES ('默辨',1,1,22)

2、更新数据（update）

update 表名 set 列名 = 值 where 条件

UPDATE person SET ID = 12 WHERE person.name = '默辨'

3、删除数据（delete）

delete from 表名 [ where 列名 = 值 ...]

DELETE FROM person
DELETE FROM person WHERE person.NAME = 'pan'

--也可以使用该语句进行删除，该方式删除后无法还原
TRUNCATE TABLE person

7、数据库查询语言（DQL）

用户对表或视图的操作就是检索数据，使用select语句实现

select {[distinct] 列名...}
  from 表名
    [where 选择条件]
    [group by 列名]
    [having 选择条件]
    [order by 列名]

• 简单查询：只包含select子句和from子句的查询
• 数据将按照select子句后面指定的列名的顺序来显示
• 如果使用星号（*），则表示检索所有的列，数据按照表结构的自然顺序来显示

7.1、普通查询

1、检索所有的列

--查询person表中所有的数据
select * from person

--查询表person和student。使用该方式为两张表得笛卡尔积
select * from person,student

2、检索指定的列

select 列名1, 列名2... from 表名

select name,id,sex,age from person p

补充：ROWID行标识符，是Oracle数据库内部使用的隐藏列

3、带有表达式得select子句，算术运算符：+、-、*、/、（）

select age*(1+0.5) from person

4、为列指定别名

两种方式：

• as 关键字
• 直接指定列名

select 字段名 [AS] 别名 [,字段名 [AS] 别名] from 表名 

SELECT NAME 姓名,ID AS ID号,sex 性别 ,age 年龄  from person p

7.2、筛选查询

在select语句中，where子句位于from子句之后，其语法格式如下：

select 列名
from 表名
where 选择条件

1、条件查询

--在进行比较筛选的时候，字符串必须使用单引号标识
select 列名| * from 表名 where 选择条件

2、判空筛选

--判断内容是否为空
内容为空： is null
内容不为空： is not null

3、取反筛选

--取反
真变为假，假变为真：not(...)

4、范围筛选

--指定范围
指定查询范围：between...and...
指定特定字符或数字中： in (值1，值2...)
不再特定字符或数字中：not in (值1，值2...)

5、模糊筛选

--模糊查找
匹配任意长度的内容：like '%数据'
匹配一个长度的内容：like '_数据'
在like前面也可以添加not表示否定的判断

6、排序筛选

--使用Order By的子句进行排序查询
升序：ASC
降序：DESC 默认

7、分组筛选

--分组查询
--注意前面的列名一定要是group by后面的字段或者聚合函数
select 列名 from 表名 group by 列名

注意：如果查询出现多个条件，在where语句之后，条件与条件之间用and进行连接

7.3、多表关联查询

在实际的应用中会涉及到多个数据表来进行查询，每个表的信息不是独立存在的，而是若干个表之间的信息存在一定的关系

--表别名
在多表关联查询时，如果多个表之间存在同名的列，则必须使用表别名来限定列的引用
select s.id, p.id from person p, student s
          where p.name = s.name

1、多表之间进行内连接

--内连接 inner join on 字段 = 字段
检索两个表公共相同的部分

2、多表之间进行外连接

--外连接
左连接，以最开始的表为基表：left join 表名 on 字段 = 字段
右连接，以连接后的表为基表：right join 表名 on 字段 = 字段
完全连接，返回左右表中所有的行，没有对应的匹配行就单独返回：full join 表名 on 字段 = 字段

3、多表之间进行自然连接

--自然连接
自然连接时指在检索索格表的时候，Oracle会将第一个表中的列与第二个表中具有相同名称的列进行自动连接，在自然连接中，用户不需要明确指定进行连接的列，这个任务由Oracle系统自动完成，自然连接用 "natural join" 关键字
由于自然连接强制要求表之间具有相同的列名称，所以在设计表的时候会出现不可预知的错误，所以在实际中很少用到

4、多表之间进行交叉连接

--交叉连接
交叉连接实际上就是不需要任何连接条件的连接，它使用cross join关键字来实现
交叉连接的执行结果是一个笛卡尔积，这种查询结果是非常冗余

7.4、子查询

子查询是在SQL语句中包含另外一条select语句，也称为内查询

select * from person
--如果返回结果只有一条，叫单行子查询语句。如果返回的是多行，叫多行子查询语句
where id = (
select id from student where name = '默辨'
)

1、单行子查询

• 单行子查询是指返回一行数据得子查询语句
• 使用单行比较运算符 =、>、>=、<、<=、<>等

select * from person
where age > (select avg(age) from person)

2、多行子查询

• 多行子查询是指返回多行数据的子查询语句，当再where子句中使用多行子查询，必须使用多行运算符（in、all、any）

--使用in
select * from person
where age in ( select age from person where age <> 12)

--使用any
--any运算符必须和比较运算符结合使用，并返回行只要匹配子查询的任何一个结果即可
--当年龄大于person中任意一个性别为男性的学生的年龄，就输出 
select * from person
where age > any(select age from person where sex = 1)

--使用all
--all运算符，与所有值都比较为true才返回值
--只能查询出，年龄大于所有男性的年龄的时，才能输出
select * from person
where age > all(select age from person where sex = 1)

注意：MySQL和Oracle都支持all和any的使用，但是在MySQL中，all和any的后面只能使用子查询不能使用写包含常量的集合

3、关联子查询

• 对于外部查询返回的每一行数据，内部查询都要执行一次。在关联子查询中信息流是双向的。外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录。然后，外部查询根据返回的记录做出决策，这种查询方式被称为关联子查询

--普通的子查询语句，数据是先外层的查询，当遇到条件过滤的时候再去执行对应的过滤条件
select * from person
where age > (select avg(age) from person)

--关联子查询语句，外部的查询和内部的查询有关联
select * from person p1
where age > (select avg(age) from person p2 where p2.id = p1.id)
order by age