Oracle数据库快速入门

前言：

我想现在很多人的入门数据库都是mysql，但是由于工作中会接触到Oracle数据库，如果你有MySQL的基础的话，这篇文章能让你很快掌握Oracle。

目录

1.体系结构

2.创建用户和表空间

2.1.创建表空间

2.2.创建用户

3.数据类型

4.表操作

4.1.创建表

4.2.修改表结构

5.数据操作

5.1.数据增删改查

5.2.分组查询

6.创建索引

---

1.体系结构

我相信在还没有接触到Oracle数据库之前，会听到很多五花八门的关于Oracle特点的介绍。其实在架构上来说Oracle最大的特点就是围绕用户，以用户为单位，做了严格的资源权限的控制和隔离。以下是架构图，如果很复杂不要担心，简单理解为用户到表空间到表的一对多关系即可。

注意：Oracle中其实没有库这种概念，而是用户对应表空间，表空间中存放表。

用户：

在mysql中我们说一个库里有多少张表，是因为MySQL体系中库是管理表的单位。

而在Oracle里，我们说一个用户下有多少张表，是因为一个库里面还用用户做了隔离，不同的用户对不同的表拥有访问权限。

表空间：

可以理解为拿来装某一个库里面的所有内容的存储区域。在 Oracle 数据库中，表空间是一种逻辑存储结构，它是由一个或多个数据文件组成的，用于存储数据库对象（如表、索引、分区等）。Oracle 数据库中的每个对象都存储在表空间中，而且每个表空间只属于一个数据库。

一个表空间可以包含多个数据文件，这些文件可以位于同一个磁盘上，也可以位于不同的磁盘上，甚至可以位于不同的服务器上。因此，通过使用表空间，Oracle 数据库可以将数据分散在多个磁盘上，提高了存储的灵活性和可靠性。

数据文件：

数据文件位于表空间中，里面存储的是数据，一个表空间可以由一个或者多个数据文件组成，但是一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件了，如果要删除只能先删除其所属的表空间。

2.创建用户和表空间

2.1.创建表空间

创建表空间必须使用有超级管理员权限的用户来操作。

create tablespace <space_name>
--指定表空间文件的存放路径和名称
datafile 'C:\<spacen_name>.dbf'
//指定表空间文件的大小
size 100m
--指定超出空间后每次扩展的大小
autoextend on
next 10m;

2.2.创建用户

create user <username>
identified by <password>
default tablespace <space_name>

创建出来的用户需要授权，否则不能登录，因为根本就不知道你能访问哪些表。

常用角色有如下：

• connect，连接角色，最基本的角色

• resource，开发者角色

• dba，超级管理员角色

给用户授权：

grant dba to <username>

3.数据类型

Oracle数据类型分为四类：字符串、数字、日期、大对象

数据类型	描述
varchar、varchar2	表示可变长度的符串，常用的是varchar2，因为varchar会自动填充，而varchar2不会。
number	number(n)表示长度为n的整数。number(m,n)表示总长度为m，小数部分长度是n
data	日期类型
clob	大对象，大文本数据类型，单字节可达4G
blob	大对象，二进制数据，单字节可达4G

4.表操作

4.1.创建表

CREATE TABLE person (id NUMBER(10) PRIMARY KEY,name VARCHAR2(50),age NUMBER(3),gender VARCHAR2(10)
);

4.2.修改表结构

--添加一个字段
alter table 表名 add 字段名 字段类型;
--修改字段类型
alter table 表名 modify 字段名 字段类型;
--修改字段名称
alter table 表名 rename column 原字段名 to 新字段名;
--删除字段

5.数据操作

5.1.数据增删改查

基础增删改查

INSERT INTO employees (employee_id, first_name, last_name, hire_date, department_id)
VALUES (101, 'John', 'Doe', TO_DATE('2023-08-21', 'YYYY-MM-DD'), 10);

UPDATE employees
SET first_name = 'Jane'
WHERE employee_id = 101;
 

DELETE FROM employees
WHERE employee_id = 101;
 

SELECT first_name, last_name, job_title
FROM employees;
 

连表查询

SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id = 1700;

5.2.分组查询

在group by的时候只有group by的字段才能被单独展示，没有被group by的字段只能用上诸如求和、求平均值等聚合函数才会显示。

举个例，假设有一个名为sales的表，其中包含销售数据，想按照产品类别进行分组并计算每个类别的销售总额：

SELECT product_category, SUM(sales_amount) AS total_sales FROM sales GROUP BY product_category;

Oracle的分组查询里值得注意的是开窗函数。

开窗函数顾名思义就是再开一个窗子，即再开一列，再开的这一列的数据来自于各个分组内部统计得出。

如计算每个部门总的薪水：

create  table t(id number,deptno number,name varchar2(20),sal number);
insert into t values(1,1,'1aa',120);
insert into t values(2,1,'2aa',300);
insert into t values(3,1,'3aa',100);
insert into t values(4,1,'4aa',99);
insert into t values(5,1,'5aa',90);
insert into t values(6,2,'6aa',87);
insert into t values(7,2,'7aa',500);
insert into t values(8,2,'8aa',200);
insert into t values(9,2,'9aa',20);
insert into t values(10,2,'10aa',30);

select t.*,sum(sal)over(partition by deptno order by sal desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_value 
from t t;

再如计算每个用户最早和最晚的订单：

SELECT customer_id,first_value(order_date) OVER (PARTITION BY customer_id ORDER BY order_date ASC) as first_order_date,first_value(amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC) as first_order_amount,last_value(order_date) OVER (PARTITION BY customer_id ORDER BY order_date ASC) as last_order_date,last_value(amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC) as last_order_amount
FROM order_details;

常用的开窗函数有：

1. ROW_NUMBER: 为每一行分配一个唯一的整数编号。

2. RANK, DENSE_RANK: 根据指定的排序规则计算每个行的排名。其中，RANK函数在相同的值存在时可能会跳过某些排名，而DENSE_RANK函数不会跳过任何排名。

3. NTILE(n): 将查询结果集分为n个等分，并为每个行分配一个对应的数字。

4. LAG, LEAD: 分别返回指定列在当前行之前或之后的值。可以使用LAG或LEAD函数来计算时间序列数据的移动平均数或计算相邻行之间的差异等等。

5. FIRST_VALUE, LAST_VALUE: 分别返回指定列的第一个值和最后一个值。

6. SUM, AVG, COUNT等聚合函数: 可以和 OVER 子句一起使用来计算每个行的聚合值，如行总数、行平均数等等。

6.创建索引

创建单列索引

CREATE INDEX idx_employee_last_name ON employees(last_name);

创建唯一索引

CREATE UNIQUE INDEX idx_employee_email ON employees(email);

创建组合索引

CREATE INDEX idx_employee_name_dept ON employees(last_name, department_id);