Oracle的旅程

Oracle

1. 数据库

1.1 历史阶段

人工管理阶段
文件系统阶段
数据库系统阶段

1.2 特点

实现数据共享
数据共享包含所有用户可同时存取数据库中的数据，也包括用户可以用各种方式通过接口使用数据库，并提供数据共享。
减少数据的冗余度
同文件系统相比，由于数据库实现了数据共享，从而避免了用户各自建立应用文件。减少了大量重复数据，减少了数据冗余，维护了数据的一致性。
数据的独立性
数据的独立性包括数据库中数据库的逻辑结构和应用程序相互独立，也包括数据物理结构的变化不影响数据的逻辑结构。
数据实现集中控制
文件管理方式中，数据处于一种分散的状态，不同的用户或同一用户在不同处理中其文件之间毫无关系。利用数据库可对数据进行集中控制和管理，并通过数据模型表示各种数据的组织以及数据间的联系。
数据一致性和可维护性，以确保数据的安全性和可靠性
- 安全性控制：以防止数据丢失、错误更新和越权使用；
- 完整性控制：保证数据的正确性、有效性和相容性；
- 并发控制：使在同一时间周期内，允许对数据实现多路存取，又能防止用户之间的不正常交互作用；
- 故障的发现和恢复：由数据库管理系统提供一套方法，可及时发现故障和修复故障；
故障恢复
由数据库管理系统提供一套方法，可及时发现故障和修复故障，从而防止数据被破坏。数据库系统能尽快恢复数据库系统运行时出现的故障，可能是物理上或是逻辑上的错误。比如对系统的误操作造成的数据错误等。

1.3 数据库对象

数据库对象是数据库的组成部分，常常用 CREATE 命令进行创建，可以使用 ALTER 命令修改，用 DROP执行删除操作。常见的数据库对象有：

用户（user）

表（table）

视图（view）

索引（index）

触发器（trigger）

存储过程（procedure）

同义词（synonym）

序列（sequence)

1.4 sql

是什么？

SQL（Structured Query Language），结构化查询语言，是专门操作关系型数据库的一种语言

五种分类

DQL (Data Query Language)，数据查询语言
用于检索数据库中的数据，主要是 SELECT 语句
DML (Data Manipulation Language)，数据操纵语言
用于改变数据库中的数据，主要是 INSERT , UPDATE , DELETE 语句
DDL（Data Define Langage)，数据定义语言
用来建立、修改、删除数据库对象，主要是 CREATE 、 ALTER 、 DROP 、 TRUNCATE 语句
TCL (Transaction Control Language)，事务控制语言
用于维护数据的一致性，主要是 COMMIT , ROLLBACK , SAVEPOINT 语句
DCL（Data Control Language），数据控制功能
用于执行权限授予和权限收回操作，主要是 GRANT , REVOKE 语句

2. oracle数据库

2.1. 安装

正常下载安装包，进行安装
启动服务，也可能默认就会启动

这里面有俩个服务较为重要：
- OracleServiceXE服务，如果不启动的话，Oracle无法正常使用，也不能使用sqlplus登录到oracle数据库中
- OracleXETNSListener服务，如果不启动的话，就无法使用oracle自带管理系统登录到数据库中，之后也无法在代码中使用JDBC连接到数据库中
Oracle自带管理系统登录地址：http://127.0.0.1:8080/apex/ 界面操作可创建用户

2.2. 启动

连接用户数据库

sqlplus 用户名/密码
查看登陆的账号

show user
清屏

$cls

//Ubuntu

!clear
退出

exit
修改会话语言

alter session set nls_language=english;

alter session set nls_language='simplified chinese';

2.3. 关于用户命令

创建用户并赋予权限

//创建用户test1，设置密码test1
create user test1 identified by test1;
//把角色connect和resource授权给test1账号
grant connect,resource to test1;

//将当前用户下的表查询权限给另一个用户

grant select on s_emp to test1 with grant option;

//将该用户查询另一个用户下表的权限回收

revoke select on s_emp from test;

connect 角色，基本的连接
resource 角色，程序开发
DBA 角色，数据库管理
切换用户

conn 用户名/密码
删除用户

drop user test1 cascade

加了cascade就可以把用户连带的数据全部删掉。

2.4 关于表的命令

查看当前用户下的表

select table_name from user_tables;
导入表

@ 相对路径/绝对路径或者直接将文件拖进来
删除表

drop table 表名

2.5 sqlplus

sqlplus 相关的命令：

l 查看缓存中的sql语句
a 在[定位]的那一行后面追加新的内容
i 在[定位]的那一行下面插入新的一行
c 替换[定位]的那一行中的某些字符串，格式为：c/老的字符串/新的字符串
del 删除[定位]的那一行内容
n 后面加内容可以重写这一行
$ 后面跟一个终端命令，例如$cls清屏，linux中使用!
/ 执行缓存sql命令

2.6 哑表

dual被称之为哑表，它是一个单行单列的虚拟表，是Oracle内部自动创建的，这个表只有1列：DUMMY，数据类型为VERCHAR2(1)，dual表中只有一个数据'X'，Oracle有内部逻辑保证dual表中永远只有一条数据。

select 1+1 from dual;

2.7 伪列

Oracle中，有一个特殊的关键字rownum，被称为：伪列。
rownum只有Oracle数据中才有。

rownum 如果是相同的条件，那么伪列只能等于1
```
select last_name
from s_emp
where rownum=1
```
rownum 如果是大于的条件，那么伪列只能大于0
rownum 可以小于任何数

Oracle数据库中伪列rownum最核心的作用就是：完成分页查询。嵌套查询

3. 基础语法操作

执行FROM语句
执行ON过滤
添加外部行
执行WHERE过滤
执行GROUP BY分组
执行HAVING过滤
SELECT列表
执行DISTINCT子句
执行ORDER BY子句

3.1 DQL：select

select [distinct] *{col_name1,col_name2,..} from tb_name;

可以对查询的语句进行计算、起别名、拼接、nvl替换、distinct去重、format调整宽度

拼接：select id,first_name||' '||last_name||','||title as name

nvl替换：使用nvl函数可以将null进行替换 select nvl(col_name,change_value) from tb_name;

distinct去重: 放在select后select distinct col_name,col_name

3.2 DML：

INSERT、UPDATE、DELETE

3.3 DDL:建表、修改表

用来建立、修改、删除数据库对象，主要是 CREATE 、 ALTER 、 DROP 、 TRUNCATE 语句

3.3.1建表细节create

格式：

create table 表名(
	字段名 数据类型 [列约束类型],
	字段名 数据类型 [列约束类型],
	字段名 数据类型 [列约束类型],
	字段名 数据类型 [列约束类型],
	[表级约束],
	[表级约束]
);

命名格式

必须是字母开头
必须是1-30个字符之间的长度
表名中只能出现字母、数字、_、#
不能和数据库中己有对象的名字重复
不能是数据库中的关键字

数据的类型

约束

3.3.2修改表的关键字 alter 灵活使用

在表创建好的情况下，可以使用 alter 关键字，来修改表的信息，会自动当前事务

主要功能：

在表中添加新列
```
alter table t_user
add birthday date;
```

删除表中的列

alter table t_user
drop column birthday;

给表中添加约束

alter table t_user
add constraint user_name_un
unique(name);

删除表中的约束

alter table t_user
drop constraint user_name_un;

修改表名

rename t_user to mytest;
rename mytest to t_user;

修改列的数据类型

alter table t_user
modify (name varchar2(500));

设置约束失效

alter table t_user
disable constraint user_id_pk cascade;
//再次生效
alter table t_user
enable constraint user_id_pk;

3.4 DCL：（Data Control Language），数据控制功能

用于执行权限授予和权限收回操作，主要是 GRANT , REVOKE 语句

3.5 TCL：事务操作

COMMIT , ROLLBACK , SAVEPOINT

只有DML语句才会产生事务，其他语句不会产生事务

DML语句执行的时候，如果当前有事务，那么就使用这个事务。如果当前没有事务，则产生一个新事务

commit、rollback、DDL语句都可以把当前事务给结束掉

commit和DDL语句结束事务的方式是把这个事务给提交了，然后DML操作永久生效

rollback结束事务的方式是把这个事务给回滚了，默认回滚到事务开始的状态

3.5.1 事务特征（ACID）：

原子性：Atomicity
一个事务中所有的DML操作，同时成功或者同时失败
一致性：Consistency
事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态
隔离性：Isolation 、
事务操作应该相互独立
持久性：Durability
事务所做的影响，在事务结束之后应该能够是持久的

3.5.2 回滚：

insert into t_user values(1,'tom',1000);
savepoint A;
insert into t_user(id,name) values(2,'zs');
savepoint B;
delete from t_user;
rollback to B;

3.5.3 脏读、幻读、不可重复读

脏读：
主要针对update操作。一个事务A读到另一个事务B中修改过，但是还没有提交的数据
不可重复读：
主要针对update操作。一个事务A在第一次读数据和第二次读数据之间，有另一个事务B把这个数据更改
并提交了,所以就出现了事务A里面读一个数据俩次，但是读到的结果是不同的。
幻读：
主要针对的是insert/delete操作。事务A第一次用where条件筛选出了10条数据，事务A第二次用通样的
where条件筛选出的却是11条数据。因为事务B在事务A的第一次和第二次查询之间进行了插入操作，并
且插入的这个数据满足事务A的where筛选条件

在这种情况下，数据库中的事务隔离级别，就是来解决这些问题的：

read-uncommitted 不提交也能读
read-committed 提交之后才能读，解决了脏读
repeatable-read 解决了脏读和不可重复读
serializable 三个问题都解决了

注意，级别越高解决的问题越多但是效率越低
注意，并不是所有数据库都支持这四种事务隔离级别
例如，oracle就只支持第二种和第四种这俩种。而mysql四种全都支持
注意，具体的支持情况，不仅和数据库有关，也和数据库的版本有关。

oracle设置事务隔离级别的sql语句：

Set Transaction Isolation Level Read Uncommitted
Set Transaction Isolation Level Read Committed
Set Transaction Isolation Level Read Repeatable
Set Transaction Isolation Level Serializable

注意，oracle里面默认的事务隔离级别是第二种：read-committed

3.6 排序

order by col_name [asc|desc]

3.7 条件查询

where  比较操作表达式
=  >  <  >=  <=  !=

between and操作符，表示在俩个值之间

where salary between 700 and 1500;
in()表示值在一个指定的列表中

where id in (1,3,5,7,9);
like模糊查询，在值不精确的时候使用
- % ，通配0到多个字符
- _ ，通配一个字符，并且是一定要有一个字符
- \ ，转义字符，需要使用 escape 关键字指定，转义字符只能转义后面的一个字符
```
where last_name like '%\_%' escape '\';
```
is null is not null 判断值为null的时候使用，null值的判断不能使用等号
and or 逻辑操作符，当条件有多个的时候可以使用

4. 函数

oracle数据库中，内置了很多常用的函数，整体分为：

单行函数
- 字符函数
- 日期函数
- 数字函数
转换函数
聚合函数

函数可以进行嵌套

4.1 单行函数

也可以称为单值函数，每操作一行数据（某个字段值），都会返回一个结果

字符函数

数字函数

日期函数

sysdate ，是Oracle中用来表示当前时间的关键字，并且可以使用它来参与时间运算。

4.2 转换函数

可以将一个类型的数据转换为另一种类型的数据

tochar：数字和日期转字符

to_number:字符转数字

to_date:字符转日期

select to_date('10-12-2022','dd-mm-yyyy') as result
from dual;

4.3 聚合函数

也可以称为多行函数、分组函数、组函数，它可以操作多行数据，并返回一个结果，一般会结合着group分组来使用，当然也可以单独使用，那么默认全部数据就是一个小组。

聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用，所以也把其它称之为分组函数。

常用的聚合函数有：

avg ，求平均值
count ，计算有多少条数据
max ，求最大值
min ，求最小值
sum ，求和

在使用聚合函数的时候：

如果还使用了group by分组，那么就表示先分组，然后对每一个小组使用聚合函数
如果没有使用group by分组，那么就表示全部数据是一个默认小组，然后对这个全部数据使用聚合函数

select
	max(s1.salary) ,s1.dept_id,s2.last_name
from
	s_emp s1,s_emp s2
where
	s2.dept_id=s1.dept_id
group by
	s1.dept_id,s2.last_name,s2.salary
having
	max(s1.salary)=s2.salary
order by
	s1.dept_id

5.多表查询

5.1 等值连接

利用一张表中某列的值，和另一张表中某列的值相等的关系，把俩张表连接起来，满足条件的数据才会组合

select se.last_name,se.dept_id,sd.id,sd.name
from s_emp se,s_dept sd
where se.dept_id=sd.id;

5.2 不等值连接

select e.last_name, e.title, e.salray, s.gradeName
from s_emp e, salgrade s
where e.salray betweeb s.losal and s.hisal

5.3 外连接

左外连接：查询出来左边全数据

select last_name,dept_id,name
from s_emp left outer join s_dept
on s_emp.dept_id=s_dept.id;
//简写，Oracle独有
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+);

右外连接：查询出来右边全数据

select last_name,dept_id,name
from s_emp right outer join s_dept
on s_emp.dept_id=s_dept.id;
//简写 Oracle独有
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;

全连接：无简写

select last_name,dept_id,name
from s_emp full outer join s_dept
on s_emp.dept_id=s_dept.id;

自连接：自己连接自己

5.4 操作结果集

每一条sql语句，查询出的一个结果，都可以被称为结果集。

如果有俩条sql语句，它们分别查询出的结果集，都包含完全一致的字段名称和类型，那么我们可以使用下面的关键字对俩个结果集进行操作：

union ，取俩个结果集的并集
union all ，把俩个结果集合在一起显示出来
minus ，第一个结果集除去第二个结果集和它相同的部分
intersect ，求俩个结果集的交集

前提条件是，俩个结果集中查询的列要完全一致（名称和类型）

select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+)
union
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;

6. 子查询

子查询，也称嵌套查询，即一个select语句中嵌套了另外的一个或者多个select语句

子查询的思路就是，把第一个sql语句的查询结果，在第二个sql语句中使用，这时候第一条sql语句的结果，在第二条sql中就可以充当一个where条件中的一个值，或者充当一张虚拟的表。

select last_name,salary
from s_emp
where salary>(
	select salary
	from s_emp
	where last_name='Smith'
);

7. 分页查询:结合伪列

select t.id,t.last_name,t.dept_id
from (
	select rownum rn,id,last_name,dept_id
	from s_emp
	where rownum<=7
) t
where t.rn>=3;

8. 数据库设计问题

8.1主键

主键的作用，就是用来唯一标识一行数据的

特点：

能做主键的列必要满足非空唯一的特点
只要满足非空唯一的任何列都可以做主键
可以让表中一个有意义的列做主键
例如，学号，它既表示学生的学号，又作为表中的主键，因为这个列满足非空唯一的条件
可以找一个没有意义的列做主键
其作用就是标识一行数据，大部分情况下都是用没有意义的列去做主键，例如ID列
可以让多个列联合在一起做表中的主键
这个主键就是一个联合主键，要求这几个列的值联合在一起是非空唯一的

8.2外键

外键的作用，就是用来标识这个类中的数据，是引用另一种表的一个字段值

特点：

表中的某一个列声明为外键列，一般这个外键列都会引用另外一张表的主键列的值。
其实只要是具体唯一约束的列，就可以被另一种表的外键列所引用。
一张表的主键列中出现过的值，都可以在另一张表的外键列中使用。
外键列值也可以为空的，提前是这个外键列没有做主键或联合主键。
如果把B表中的联合主键，引用到A表中做外键，那么这个外键就是一个联合外键

8.3范式

目前关系数据库有六种范式：

一范式(1NF)
第二范式(2NF)
第三范式(3NF)
巴斯-科德范式(BCNF)
第四范式(4NF)
第五范式(5NF，又称完美范式)

一般使用到三范式：

第一范式：一个表中，每个列里面的值是不能再分割的。
第二范式：第二范式是在满足第一范式的基础上，表中的非主键列都必须依赖于主键列
第三范式：第三范式是在满足第二范式的基础上，表中的非主键列都必须直接依赖于主键列，而不能间接的依赖，

8.4关系规则

在常见的实体关系中，对应的数据库中表的设计规则如下：

一对一关系
假设是A表和B表，这种情况下，外键列设置在任意一张表中，都是可以的。
一对多关系
假设是A表和B表，这种情况下，外键列要设置在多的一方。
多对多关系
假设是A表和B表，这种情况下，需要设计第三张表（桥表），桥表中设置俩个外键，分别引用A表的主键和B表的主键。

9.序列、视图、索引

9.1序列

是什么？

排序的玩意，一种数据库对象

序列是oracle数据库所特有的对象，其他数据库中是没有的。

什么用？

它作用主要用来帮助表去创建自动增长的主键。

怎么用？

//创建

create sequence 序列名;

//查看及使用

select 序列名.nextval from dual;

//删除

drop sequence 序列名

9.2 视图

是什么？

视图（view），它也是一种数据库对象

视图其实就是提取一张表或者多张表的数据生成一个映射。
操作视图从而达到操作原表的效果，方便数据管理和安全操作。
视图的主要作用是隐藏表中的重要数据、代替比较长的sql语句。

简单视图：没有group by语句，没有组函数，查询的只有一张表

复杂视图：有group by语句，或者有组函数，或者查询的是多张表

区别：通过简单视图可以修改原来表中的数据，通过复杂视图是不能修改原来的数据的

使用：

create or replace view v_test
as
select id,last_name,salary
from t_user
where id < 10;

9.3 索引

是什么？

一列有许多数据，将不同的数据进行分类，像目录，方便查询

创建

自动创建索引
当在表中指定了primary Key或者unique约束时，会自动创建唯一值索引。
用户创建索引
用户可以创建非唯一值索引以提高在访问数据时的效率。
```
create index 索引名
on 表名(列名);
```

结构

B-tree：默认的索引
位图：数据基数比较少的时候，较适合建位图索引

create bitmap index
反序：B-tree索引的一个分支

create index emp_index_reverse
函数：经常对某个字段做查询的时候，并且是带函数操作的

create index func_index

posted @ 2021-07-08 09:05 橘生淮_南阅读(78) 评论(0) 收藏举报

刷新页面返回顶部

橘生淮_南

Oracle的旅程

Oracle

1. 数据库

1.1 历史阶段

1.2 特点

1.3 数据库对象

1.4 sql

2. oracle数据库

2.1. 安装

2.2. 启动

2.3. 关于用户命令

2.4 关于表的命令

2.5 sqlplus

2.6 哑表

2.7 伪列

3. 基础语法操作

3.1 DQL：select

3.2 DML：

3.3 DDL:建表、修改表

3.3.1建表细节create

3.3.2修改表的关键字 alter 灵活使用

3.4 DCL：（Data Control Language），数据控制功能

3.5 TCL：事务操作

3.5.1 事务特征（ACID）：

3.5.2 回滚：

3.5.3 脏读、幻读、不可重复读

3.6 排序

3.7 条件查询

4. 函数

4.1 单行函数

4.2 转换函数

4.3 聚合函数

5.多表查询

5.1 等值连接

5.2 不等值连接

5.3 外连接

5.4 操作结果集

6. 子查询

7. 分页查询:结合伪列

8. 数据库设计问题

8.1主键

8.2外键

8.3范式

8.4关系规则

9.序列、视图、索引

9.1序列

9.2 视图

9.3 索引

公告