oracle复习

最新推荐文章于 2024-11-13 23:26:54 发布

weixin_51767733

最新推荐文章于 2024-11-13 23:26:54 发布

阅读量845

点赞数 19

分类专栏： Oracle数据库文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_51767733/article/details/135609214

版权

Oracle数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

数据库系统（DBS）包含DBMS
SID是一个数据库的唯一标识符
Oracle ORACLE VSS Writer Service：映射拷贝
OracleDBConsoleoracle：Oracle数据库控制台服务
OracleJobSchedulerORACLE：作业调度服务
OracleMTSRecoveryService：服务端控制
OracleOraDb11g_home1ClrAgent：是.NET扩展服务的一部份
OracleOraDb11g_home1TNSListener：监听器服务
OracleServiceORACLE：Oracle核心服务
物理结构：
- 数据文件：用于存储数据库中的所以数据
- 控制文件：记录和描述数据库的物理存储结构信息
- 重做日志文件：记录外部程序（用户）对数据库的改变操作
- 归档文件：保持已经写满的重做日志文件
- 初始化参数文件：设置数据库启动时的参数初始值
- 跟踪文件：记录用户进程、数据库后台进程等的运行情况
- 口令文件：保存具有SYSDBA、SYSOPER权限的用户名和SYS用户口令
- 警告文件：用于记录数据库的重要活动以及发生的错误；
- 备份文件：存放数据库备份所产生的文件
逻辑结构：数据块(最小的组成单元) < 数据区间 < 数据段 < 表空间 < 数据库
SGA(系统全局区)包含的内存结构主要有：
- **数据高速缓冲区：**存储最近从数据库中读取出来的数据块
- 共享池：用于存放SQL语句、PL/SQL代码、数据字典、资源锁和其他控制信息
- 重做日志缓冲区：用于回滚事务
- 大型池
- java池
- 流池
- 其他结构
进程结构：

用户进程：在客户机内存上运行的程序，它向服务器进程发出数据处理请求

服务器进程：解析执行用户提交的SQL语句；搜索SGA区的数据缓存，决定是否读取数据文件，如果数据块不在SGA区的数据库缓存中，则将其从文件中读入；将查询或执行后形成的数据返回给用户。

后台进程：可以处理上百个并发请求，进行复杂的数据操作。

包括：
- DBWR 数据库写入进程 (负责把数据高速缓冲区中已经被修改过的数据成批写入数据文件中永久保存，同时使数据高速缓冲区有更多的空闲缓存块，保证服务器进程将所需要的数据从数据文件读取到数据高速缓冲区中，提高缓存命中率)
- LGWR 日志写入进程 (负责把重做日志缓冲区的数据写入重做日志文件中永久保存)
- GKPT 检查点进程 (记录下当前数据库的结构和状态，保证数据的同步)
- SMON 系统监控进程 (在实列启动时负责对数据库进行恢复；回收不在使用的临时空间；将各个表空间的空闲碎片合并)
- PMON 进程监控进程 (负责恢复失败的用户进程或服务器进程，并且释放进程所占用的资源；清除非正常中断的用户进程留下的孤儿会话，退回未提交的事务，释放会话所占用的锁、SGA、PGA等资源；监控调度进程和服务器进程的状态，如果它们失败，则尝试重新启动它们，并释放它们所占用的各种资源)
- ARCH 归档进程 (将写满的重做日志文件复制到归档目标中，防止写满的重做日志文件被覆盖)
- RECO 恢复进程 (在分布式数据库环境中自动解决分布式事务的故障)
- LCKn 封锁进程
- Dnnn 调度进程
- Snnn 共享服务器进程
数据定义语言(DDL)：用于建立(create)、修改(alter)、删除(drop)、数据库对象，如数据库、表、视图
数据库操作语言(DML)：主要用与数据库基本操作，增(insert)，删(delete)查改
数据控制语言(DCL)：用于设置用户对数据库对象的访问权限，主要包括GRANT、REVOKE、DENY等语句
事务控制语言(TCL)：主要用于事务操作，如COMMIT、SAVEPOINT、ROLLBACK等语句
基本查询语句：
- from：必需的语句，后面跟查询所选择的表或视图的名称
- where：查询条件的表达式，表达式可以是列名、函数、常数等组成的表达式，不需要可以省略
- group by：指定分组查询子句，后面跟需要的分组列名。要求在查询的结果中排序，默认是升序
- having：指定分组结果的筛选条件
- order by：指定对查询结果进行排序的条件。后面加ASC表示升序(默认)，DESC表示降序
消除重复行DISTINCT
```
SELECT DISTINCT job FROM emp;
```
比较运算符(where子句中常用的比较符运算)

比较运算符说明
= 等于
<>，!= 不等于
>= 大于等于
<= 小于等于
> 大于
< 小于
ANY 与一个列表中的任何值进行比较
ALL 与一个列表中的所有值进行比较
```
SELECT * FROM EMP WHERE SAL>1500;
```
LIKE子句(模糊查询)：%通配符，匹配任意多个字符；_表示匹配一个字符
```
#查询字母A开头的全部员工信息
SELECT * FROM emp WHERE name LIKE 'A%';
```

比较运算符	说明
=	等于
<>，!=	不等于
>=	大于等于
<=	小于等于
>	大于
<	小于
ANY	与一个列表中的任何值进行比较
ALL	与一个列表中的所有值进行比较

GROUP BY子句(将一个表中的行分为多个组) ；HAVING子句(与GROUP BY子句一起使用，对分组的结果进一步筛选)

#统计各个部门的员工人数
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
#用having子句进一步筛选
#查询emp表中员工人数大于3的部门
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno having COUNT(*)>3;

常用分组函数

名称	说明
Count	返回记录数（个数）
Min	返回一个数字列和计算列的最小值
Max	返回一个数字列或计算列的最大值
Sun	返回一个数字列或计算列的总和
Avg	返回一个数字列或计算列的平均值

内连接（join）
- 等值连接：连接条件中使用等于(=)运算符比较被连接的值，也就是通过相等的列值连接起来的查询
- 不等值连接（使用较少）
- 自然连接(NATURAL JOIN)：在两个表中寻找列名和数据类型都相同的字段，通过相同的字段将两个表连接在一起，并返回所有符合条件的结果
外连接：
- 左外连接(LEFT JOIN):左边的表不加限制
- 右外连接(RIGHT JOIN):右边的表不加限制
- 全外连接(FULL JOIN):左右两表都不加限制
子查询
- 单行操作符：=、>、>=、<、<=、<>、!=
- 多行操作符：
  
  ALL：将一个值与一个列表中的所有值进行比较
  
  ANY：将一个值与一个列表中的所有值进行比较，这个值只需要匹配列表中的一个值即可，然后将满足条件的数据返回
  
  IN：检查一个值列表中是否包含指定的值；NOT IN :检查在一个值列表中是否不包含指定的值
  
  EXISTS：当exist（）子句内的查询结果不存在时，返回false，不执行外层SQL语句。
  
  当exist（）子句内的查询结果存在时，返回true，执行外层
指定子查询时，需要注意以下几点
- 子查询需要使用括号()括起来
- 子查询要放在比较操作符的右边
- 当子查询的返回值是一个集合而不是一个值时，不能使用当行操作符，而必须根据需要使用ANY、IN、ALL或EXISTS等操作符
集合操作
- UNION求并集，公共部分只有包含一次
- UNION ALL求集并，公共部分包含两次 (只保留重复行中的一行)
- INTERSECT求交集，只有包含公共部分
- MINUS求差集，求集合A去除集合A和集合B的交集

INSERT语句

INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...)

UPDATE语句

UPDATE 表名 SET ...;
#在SET子句中使用表达式将sal的值增加200，然后重新赋值给sal列
UPDATE emp SET sal = sal+200

DELETE语句

#删除指定表中的所有行
DELETE FROM 表名;
#删除emp表中empno的值为7936的员工记录
DELETE FROM emp WHERE empno=7936;

事务的特性
- 原子性：每一个事务是一个工作单元
- 一致性：一个事务对数据库的更改必须保证数据库从一个一致的状态转换到另一个一致的状态
- 隔离性：事务之间也应该彼此独立
- 持久性：这个事务更改的数据将永久存在
提交事务
```
COMMIT语句
```
回滚事务
```
ROLLBACK TO 语句
```

创建表

CREATE TABLE 表名{
   字段1  字段类型(长度),
   字段2  字段类型(长度),
   ...
   字段N  字段类型(长度)
};
#如：
CREATE TABLE emp(
 id  number(8),
 name varchar(20)
);

复制表

CREATE TABLE 复制表名称 AS 子查询 ;
#如：复制一张只包含20部门雇员信息的表
CREATE TABLE emp AS SELECT * FROM emp WHERE deptno=20;

修改表结构

#向表中增添字段
ALTER TABLE 表名 ADD(列名称 数据类型 [DEFAULT 默认值],...);
#修改已有的表结构
ALTER TABLE 表名 MODIFY(列名称 数据类型 [DEFAULT 默认值],...);
#列子：将name字段的默认值定义为无名氏
ALTER TABLE emp MODIFY(name varchar2(40) DEFAULT '无名氏');

删除表

#删除表
DROP TABLE 表名;
#列子：删除emp表
DROP TABLE emp;

视图的创建
```
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 视图名[alias,...] 
AS subquery
[WITH CHECK OPTION][WITH READ ONLY]
#创建简单视图
CREATE VIWE vw_emp AS SELECT * FROM emp;
```
- OR REPLACE：若视图存在，自动重建该视图
- FORCE：不管基表是否存在都会自动创建该视图
- NOFORCE：只有基本存在才会创建该视图
- alias：为视图产生的列定义别名
- subquery：一条完整的SELECT语句，可以在该语句中定义别名
- WITH CHECK OPTION：插入或修改的数据行必须满足视图定义的约束
- WITH READ ONLY：创建的视图只能用于查询数据，不能进行任何DML操作
索引的优点
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
- 可以大大加快数据的检索速度(主要原因)
- 可以加快表和表之间的连接
- 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
索引的缺点
- 创建索引和维护索引药耗费时间，时间随着数据量的增加而增加
- 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
- 对表中的数据进行增删改的时候，索引也要动态的维护，就会降低数据的维护速度

创建于管理约束

主键(primary key)：能唯一标识一条记录的单个数据表列或联合的数据表列
```
CREATE TABLE s(
   sid number(4) primary key    ----主键约束
);
```

唯一约束(unique)：能够唯一标识一条记录的约束

CREATE TABLE c(
 cid number(3) primary key,
 cname varchar(40) unique    -----唯一约束
);

外键：指引用另一个表中一个列或几个列，或者本表中的一个列或几个列

CREATE TABLE SC(
   sid number(4),
   cid number(4),
   foreign key sid references S(sid) -----外键约束
);

检查约束：用来限制列值所允许的取值范围

CREATE TABLE S(
  sid number(4) primary key,
  name varchar2(20) not null,
  sex char(4) check(sex='男' or sex='女')  ---检查约束
);

非空约束：用于约束该字段的值不能为空

CREATE TABLE S(
  sid number(4) primary key,
  name varchar2(20) not null   ---非空约束
  );

默认值约束：创建表时，将该字段的所有数据全部定义为默认值

CREATE TABLE S(
  sid number(5) primary key,
  sex char(4) check(sex='男' or sex='女') default'女' ---检查约束和默认约束
);

PL/SQL：Procedure Language
PL/SQL优点：
- 支持SQL
- 支持面向对象编程
- 更好的性能
- 可移植
- 安全性
PL/SQL分类
- 无名块或匿名块
- 命名块
- 子程序
- 触发器
- 程序包

隐式游标的属性

隐式游标的属性	返回值类型	意义
ROWCOUNT	整型	代表DML语句成功执行的数据行数
FOUND	布尔型	值为TRUE代表插入、删除、更新或单行查询操作成功。注：与最近的sql语句发生交互，影响行数大于0条，则为true，否则为false。
NOTFOUND	布尔型	与SQL%FOUND属性返回值相反。注：与最近的一条sql语句发生交互，没有涉及任何行的时候，返回true，否则返回false。
ISOPEN	布尔型	DML执行过程中为真，结束后为假

声明游标：在DECLEAR部分按以下格式声明游标

CURSOR 游标名[参数1 数据类型,....] IS SELECT语句;

打开游标
```
OPEN 游标名[实际参数1,...];
```

提取数据

FETCH 游标名 INTO 变量名1,...;
 或
FETCH 游标名 INTO 记录变量;

关闭游标
```
CLOSE 游标名;
```

游标实列：

#用游标提取emp表中7788雇员的名称和职务(PL/SQL语句)
SET SERVEROUTPUT ON 
   DECLARE     --- 声明游标
     v_ename VARCHAR2(10),   --- 声明变量
     V_job VARCHAR2(10),
     CURSOR emp_cursor IS SELECT ename,job FROM emp WHERE empno=7788;
   BEGIN
     OPEN emp_cursor;   --- 打开游标
     FETCH emp_cursor INTO v_ename,v_job;   --- 提取数据
     dbms_output.put_lin(v_ename||','||v_job);   ---- 输出数据
     CLOSE emp_cursor;    ---- 关闭游标
   END;

触发器分类
- DML触发器：Oracle可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每行或语句操作上进行触发
- 替代触发：不能直接对由两个以上的表建立的视图进行操作。替代触发器是进行视图操作的一种处理方法。
- 系统触发器：第三种类型的触发器叫系统触发器。它可以在Oracle数据库系统的事件中进行触发，如Oracle系统的启动与关闭等。
触发器还可以分为语句级和行级触发器
- 语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次
- 行级(ROW)触发器：是指当某触发事件发生时，对受到操作影响的每一行数据，触发器都单独执行一次
触发器的注意事项：
- 触发器不接受参数
- 一个表上最多可有12个触发器，但同一时间、同一事件，同一类型的触发器只能有一个。各触发器之间不能有矛盾
- 一个表上触发器越多，对该表上的DML(增删查改)操作的性能影响就越大
- 触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用
- 在触发器的执行部分只能用DML语句(SELECT、INSERT、UPDATE、DELETE),不能使用DDL语句(CREATE、ALTER、DROP)
- 触发器中不能包含事务控制语句(COMMIT、ROLLBACK、SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、退回时，触发器也被提交、退回了。
禁用或启用触发器

有效状态：当触发事件发生时，处于有效状态的数据库触发器TRIGGER触发器

无效状态：如果在触发器内调用其他函数或过程，当这些函数或过程被删除或修改后，触发器的状态将被标识为无效。
SYSDBA和SYSOP0ER的区别在于：SYSDBA不仅具备SYSOPER的所有权限，而且还可以建立数据库，执行不完全恢复。
用户与模式是一一对应的关系，并且两者名称相同
在创建完用户之后，要注意以下几点：
- 如果建立用户时不指定DEFAULT TABLESPACE 子句，Oracle会将SYSTEM表空间作为用户默认表空间
- 如果建立用户时不能指定TEMPORARY TABLESPACE 子句，Oracle会将数据库默认临时表空间作为用户的临时表空间
- 初始建立的用户没有任何权限，所以为了是用户可以连接到数据库，必须授权其CREATE SESSION权限。
- 如果建立用户时没有为表空间指定QUOTA子句，那么用户在特定表空间上的配额为0，用户将不能在相应的表空间建立数据对象
- 初始建立的用户没有任何权限，不能执行任何数据库操作
用户管理权限
- 系统权限：
  
  DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
  
  RESOURCE：拥有RESOURCE权限的用户只可以创建实体，不可以创建数据库结构。
  
  CONNECT：拥有CONNECT权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构
- 对象权限：是指某一个用户对其他用户的表，视图，序列，存储过程，函数，包等的操作权限

系统授权操作

授权操作使用GRANT命令，格式如下：

GRANT sys_privi|role    ---sys_privi:表示Oracle系统权限，系统权限是一组约定的保留字。如创建表为“CREATE TABLE”  ---role:角色
TO user | role | public  --user:具体的用户名，或者是一些列的用户名。 --pulic:保留字，代表Oracle系统的所有用户
[WITH ADMIN OPTION]  --WITH ADMIN OPTION:表示被授权者可以再将权限授予另外的用户
#为用户east授予连接和开发系统权限
GRANT CONNECT(登录权限),RESOURCE(创建实体权限) TO east;

回收系统权限
- 系统权只能由DBA用户回收，格式如下：
```
REVOKE obj_privi|all
FROM user|role|public
#例子，收回用户dongfang的权限
REVOKE CREATE SESSION,CREATE TABLE FROM dongfang;
```
  注意：1，系统权限无级联，如果使用WITH ADMIN OPTION为某个用户授予系统权限，则取消该用户的系统权限并不会级联取消这些用户的相同权限。A授予B权限，B授予C权限，如果A收回B的权限，C的权限不受影响
  
  2，系统权限可以跨用户回收，即A可以直接收回C用户的权限

对象授权

GRANT obj_privi|all column ON schema.object  --obj_privi:表示对象的权限，可以是ALTER、EXECUTE、和DML语言 
TO user|role|public  -- role:角色名
[WITH GRANT OPTION]|[WITH HIERATCHY OPTION] --WITH GRANT OPTION：被授权者可以将系统权限授予其他用户;--WITH HIERATCHY OPTION:在对象的子对象(在视图上再建立视图)上授权给用户
#例子：授予xifang用户SELECT、INSERT、DELETE和UPDATE soctt.emp表的权限。
GRANT SELECT,INSERT,DELETE,UPDATE ON soctt.emp TO xifang;