Oracle数据库习题整理

1.数据库核心

数据库的核心是数据库管理系统，数据库的核心任务是数据管理。

2.关系型数据库

主流的关系型数据库有：Oracle、SQL Server、MySql、DB2、Sybase、MicroSoft Access、Postgresql、SQLite
主流的非关系型数据库有：、Redis、Memcaced
列存储数据库：Hbase
面向文档数据库：Mongodb
图形数据库：Neo4J、InfoGrid
搜索引擎数据库：Solr、Elasticsearch

oracle 11g也是Oracle，关系型

3.权限

用户：
sys是Oracle最高级管理员，system是Oracle普通管理员。scott是普通用户。
权限：
系统权限：
DBA：全部权限。只有DBA才可以创建数据库结构
RESOURCE：可以创建实体。
CONNECT：只能连接。
对于普通用户授予后两个，对于dba用户授予三个。
系统用户只能由sys、system（拥有dba权限）用户授出。
对象权限：用户对于表、视图、序列、过程的操作权限。

对象权限：用户对于表、视图、序列、过程的操作权限。

角色：一些权限的集合
标准角色：CONNECT, RESOURCE, DBA

dba是一种角色，也是权限，但sysdba不是角色，只是一种身份。

4.逻辑结构

Oracle逻辑结构：表空间、段、区、数据块
表空间：数据库的逻辑划分。多个表的逻辑存储空间。表空间在物理上由一个或多个磁盘文件组成。每个数据库至少有一个表空间。
表空间分为：系统表空间（每个Oracle数据库必须具备的）、SYSAUX表空间、临时表空间、撤销表空间（回滚表空间）、用户表空间
段（Segment）：多个数据区构成。段内的数据区可以不连续，可以跨越多个文件。段是为了保存特定对象（表段、索引段、回滚段、临时段）分配的一系列数据区。
区（Extent）：是一组连续的数据块。区不能跨越多个数据文件。一个Oracle对象至少包括一个数据区。
数据块（Data Blocks）：数据块是Oracle最小的存储单位，数据存放在块中，每次存取也是以块为单位。Oracle块一般是操作系统块的整数倍。
块由块头、表目录、行目录、剩余空间、行数据组成
块的标准大小由初始化参数DB_BLOCK_SIZE指定。大小一样的叫标准块，不一样的叫非标准块。DB_BLOCK_SIZE有4K,8K,16K…等等。

模式：模式是一个数据库用户拥有的。用户下所有数据库对象都会归属到数据库模式。一般与用户同名。
用户：用于认证登录，连接和访问数据库。

5.实例

测试用户：Scott tiger
Oracle默认端口号：1521
数据库：oracle只有一个大数据库。是数据的物理存储，包括数据文件、控制文件、日志文件等
实例：一个Oracle实例包括一系列的后台进程和内存结构。一个数据库可以由多个实例构成。内存又分为SGA（system global area）和PGA（Process global area），SGA是实例的基本组成部分，会在实例启动时分配。SGA由共享池、数据缓冲池、日志缓冲池组成。
用户：用户是在实例下建成的。不同实例可以有同名用户。
表空间：管理数据的逻辑概念，表空间只和数据文件产生关系。一个表空间可以有多个数据文件，但是一个数据文件只能隶属于一个表空间。删除一个数据文件，只能删除它所在的表空间。
实例名：instance_name，也称为system identifier(sid)。
默认sid是orcl。
区分： oracle_sid就是一个环境变量。
进程：常见Oracle进程有：用户进程、服务进程、后台进程、其他可选进程
用户进程：在用户连接数据库时产生，请求Oracle服务器连接。必须要先建立一个连接，不会直接和服务器连接。
服务进程：在连接实例并建立用户会话时产生。当客户端处理请求缓冲时，服务器进程会搜索缓冲缓存以获得缓冲。如果数据库在内存中发现缓冲器，则会出现缓存命中。数据读写由服务进程完成。
后台进程：维持物理与内存之间的联系，维护数据库读写。用户进程和服务进程通过后台进程进行数据交换。当Oracle实例被启动时，启动相关的后台进程。

服务：OracleOraHome81TNSListener、OracleServiceORCL需要开启。


SYS和SYSTEM 是每个ORACLE 数据库系统缺省安装的两个帐户。SYS 是所有内部数据库表、结构、过程包、等拥有者，此外它还拥有 V$ 和数据字典视图，并创建所有封装的数据库角色(DBA，CONNECT，RESOURCE)。

sys是一个唯一能访问特定内部数据字典的用户。 System 也是在安装ORACLE 时创建的用户，用于 DBA 任务的管理。
sys模式存储数据基表和视图。

TNS是Oracle net的一部分，全称是Transparent Network Substrate。就是监听的服务OracleOraDb11g_home1TNSListener。默认使用1521端口号。
TNS的配置文件是tnsnames.ora，可以修改主机号、端口号等。

使用Oracle连接JDBC，有三种方式：SERVICE_NAME、SID和TNSName。
SERVICE_NAME方式：jdbc:oracle:thin:@//:/<SERVICE_NAME>
SID连接方式：jdbc:oracle:thin:@::
或：jdbc:oracle:thin:@:/
TNSName连接方式：jdbc:oracle:thin:@

6.表空间

Oracle10之前是给未指定default tablespace的用户分配system，10开始是分配users。

7.解锁/加锁语法

解锁账户：alter user 用户名称 account unlock;
加锁账户：alter user 用户名称 account lock;

8.日志文件 控制文件

日志文件作用：数据在块在数据缓冲区被修改，单不会立即写入到物理文件中，Oracle先将数据块的变动情况写入日志文件中去，再写入表的物理文件。
日志文件分为重做日志文件和归档日志文件两种。重做日志文件是oracle正常运行不可缺少的一部分，主要记录了数据库操作过程，也就是数据的所有更新操作。

控制文件作用：控制文件是数据库的一个二进制文件，它主要记录数据库的名称、数据库的数据文件存放位置等信息。一个控制文件只能属于一个数据库。如果控制文件丢失，这数据库就无法操作。

9.查看表结构

DESC：用于查看表结构、列名和数据类型
show tables/show databases都是mysql中的语法。
show user是SQLPlus的写法，oracle中是select user from dual

10.配置文件注释#

参数文件中#表示注释。

11.数据库参数文件

init.ora参数文件的作用：得到数据库名和控制文件位置

手工创建新的数据库时，需要：
1.创建相关目录
2.创建一个新的初始化文件（拷贝init.ora）
3.配置环境变量oracle_sid
4.创建实例oradim –new –sid book
5.创建口令文件
C:>orapwd file=D:\oracle\product\10.1.0\db_1\database\pwdbook.ora password=bookstore entries=2
6.启动数据库到nomount(实例)状态
7.执行建库脚本
8.执行catalog脚步本创建数据字典
9. 执行catproc创建package包
10. 执行pupbld
11. 由初始化参数文件创建spfile文件
12. 执行scott脚本创建scott模式
13. SQL>alter database open

数据库启动分三步：
1 startup nomount
2 alter database mount;
3 alter database open;
startup nomount选项：（读初始化参数文件，启动实例）
startup mount 选项：（打开控制文件）
startup open 选项：（打开数据文件，日志文件）

数据库从init.ora中得知控制文件的位置。一个数据库至少一个控制文件

创建控制文件的时候，一定要处于no mount状态。

12.sqlplus登录

cmd命令：SQLPLUS username [/password] [@connect_identifier] [AS SYSOPER|SYSDBA]

登陆时可以使用IP+端口+服务名或主机名+端口+服务名
SQL>conn bys/bys@192.168.0.181:1521/orcl

13.单引号 双引号 不加引号

双引号：对字段、对象名的引用
单引号：常量值
不加引号：会报没有逗号的错误

单引号的转义：’’’’ 第一个和第四个单引号表示引用常量，第二个是对第三个单引号的转义

initcap(“”)会将一串由字母、空格、数字、下划线的字符串，单词首字母大写，其他字母小写返回。

14.sql语法

执行顺序：from where group by having select order by

右外和左外互换：左右表也换

行：表中的一行
组：group by 后的一行
where只能筛选分组前，所以是限制行。having只能筛选分组后，所以是限制组。

单行子查询：查询结果只返回一行
多行子查询：查询结果返回多行
单行子查询可以使用单行比较运算符（大于小于等于不等于…），多行子查询可以使用多行比较运算符Ø+（in any all）

count(*)和count(1)都不忽略null，但是count(colname)会忽略空值。

移动平均数是分析时序数据的值。用分组函数实现。

排它锁：写锁
共享锁：读锁

行级排他锁？

针对行数据修改，事务占有的排他锁，称作行级排它锁（或排他的行锁，或粒度为行的排它锁
行数据修改的修改不是指数据内容的修改，它是指新增行，删除行，修改行内容。
for update会在一行或多行放置排它锁

oracle 集合运算符：intersect A交B union A并B minus A-B

直接用逗号连接两个表，是笛卡尔积运算。

DDL：create drop alter truncate comment rename
DML：update insert delete merge call (select 广义上也是DML) lock table explain plan
DQL：select
DCL：grant revoke
TCL：commit rollback savepoint set transaction

可以join自己，所以是一张表

union在合并后会排序去重，union all不去重。所以sql优化应该选择union all，不去重效率高。

count(*)不可能，因为空表返回0。
max(col1)空表返回0，concat()只要参数有一个是null就会返回null。

需要返回全部学生，不需要所有宿舍，所以是左外连接。

分组函数如果求一列也不需要去重，可以不加group by。
delete from 表名 ，没有 *。


因为使用了单行比较运算符>，所以是单行子查询。

一个为null，全部为null

15.oracle函数

round(数字,3)：千位四舍五入
round(数字,2)：百位四舍五入
round(数字,1)：十位四舍五入
round(数字,0)：个位四舍五入
round(数字,-1)：小数第一位四舍五入
round(数字,-2)：小数第二位位四舍五入
round(数字,-3)：小数第三位位四舍五入

trunc(数字,3)：个位、十位、百位为0，其他整数位不变
trunc(数字,2)：个位、十位为0，其他整数位不变
trunc(数字,1)：个位为0，其他整数位不变
trunc(数字,0)：保留到整数
trunc(数字,-1)：保留到小数点后一位
trunc(数字,-2)：保留到小数点后两位
trunc(数字,-3)：保留到小数点后三位

SELECT ADD_MONTHS(MAX(hire_Date), 6)
FROM EMP111;--2022/2/16 19:29:23

SELECT ROUND(hire_date)
FROM EMP111;--2021/8/16

SELECT sysdate-hire_date
FROM EMP111;--1.00097222222222

SELECT TO_NUMBER(hire_date +7)
FROM EMP111;--报错 无效数字

add_months 有两个参数，第一个参数是日期，第二个参数是对日期进行加减的数字(以月为单位的)
A选项是将最大的日期，加了6个月
round()日期函数，如果不指定，默认为四舍五入到天。可以指定第二个参数为’year’,‘month’,‘day’
日期相减，返回小数
to_number()函数不能操作日期

oracle没有create or replace语法，创建表如果已经存在只能先drop 后 create

drop user 用户名; drop user 用户名 cascade;

sysdate函数返回系统日期+时间
D选项，substr也有两种格式，可以指定长度或者不指定

floor()向负数方向取整，-98

month_between()表示两个日期之间相差的月份，前-后，结果可能为小数

round()和trunc()函数并不会占位，所以无法保留没有的小数位

oracle字符串：char（定长），varchar2（变长）
CHAR的效率比VARCHAR2的效率稍高，varchar2更节省空间
VARCHAR2字段类型，最大值为4000字节。varchar是varchar2的同义词
char最大长度为2000字节

LOB对象中，BLOB是字节流对象，CLOB是字符流对象。所以大文本是CLOB。


number()默认精度为38，默认scale为0

由于truncate不能回滚，不使用UNDO表空间，所以在大表删除的时候truncate效率会更高

创建临时表格式：
create global temporary table xx () on commit preserve(delete) rows;
临时表会在会话结束时消失，不同session之间看不到。delete会在回滚时删除行。

根据已有表创建表：
create table tab_new as select col1,col2… from tab_old

to_char()和extract()都可以
extract(month|day|year|hour from date);

日期默认格式是数字，存储的是毫秒整数。

16.索引index

索引占用存储空间。

普通索引:没有任何限制
唯一索引:不允许建立索引的列有重复的值，但可以有空值
主索引:特殊的唯一索引，不允许有空值
候选索引:也要求唯一性，一个表中可以有多个候选索引
B树索引：不能有null；模糊查询词首不能是通配符；不适合键值较少（重复值较多）的列
Hash索引：不适合模糊查询、不适合排序、不适合复合查询、也不适合键值较少（重复值较多）的列 优点是精确查找（in <> = ）非常快，比b树还快
位图索引：不适合键值很多（重复值很少）的列、不适合增删改频繁的列，代价很高 优点：适合决策支持系统、select count(val1)可以直接根据一个位图得出结果、and\or时可以使用位图运算


表达式索引：对字段进行某种运算之后的结果创建索引，可以使用一些单行函数

唯一约束和主键约束都会自动建立索引。

外模式：视图
模式：基本表
内模式：存储文件

数据并发会丢失更新

优化SQL查询速度
1.避免使用*
2.减少表之间的关联
3.表的关联使用别名
4.连接查询关联条件写在where左边
5.建立索引
6.避免使用模糊查询，词首为通配符
7.优化SQL
8.使用exists代替in

表连接有三种算法，有hash，nested loop，merge。
对于nested loop，使用索引可以在第二个表中进行索引查询，加速表与表之间的连接。
利用索引的唯一性来控制记录的唯一性

rowid不需要查询，最快，主键和唯一索引都有索引，比全表扫描快

索引可以建在表、视图、聚簇上。

索引对用户是透明的

BCD

主键和外键建立索引是因为相对的这两个值比较能确定一些数据，所以比较适合建立索引；
where条件中的字段适合建立索引是因为要在查询过程中减少数据检索，需要使用索引；
而select中的字段是否建立索引本质是没有意义的，没什么必要；

创建索引的语法 create index index_name on table_name(colname1,…)

索引占有物理空间，提高查询速度，降低DML速度
索引不是尽量少建立

17.视图view

ACD都是视图的优点

视图和基本表都可以构建视图

视图不允许更新的限制：
1.视图是由两个及以上的表导出的
2.若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作
3.若视图的字段来自聚集函数，则此视图不允许更新。
4. 若视图定义中含有GROUP BY子句/distinct关键字，则此视图不允许更新。
5. 嵌套查询，且子表也是导出视图的基本表
6. 不允许更新的视图导出的视图

视图不支持order by
所以可以建立但功能不符合

视图是虚表，不占有物理空间，可以隐藏原始表的一些列

违反了视图的检查约束，提示违反检查错误。

18.序列sequence

建立序列
create sequence seq_name increment by n
start with n
maxvalue/ minvalue n | nomaxvalue
cycle/nocycle
cache/nocache

修改序列：
1.必须是序列的拥有者或对序列有 ALTER any sequence权限
2.只有将来的序列值会被改变
3.改变序列的初始值只能通过删除序列之后重建序列的方法实现
alter sequence seq_name 值名

序列是属于特定模式，为该模式下的所有用户共享
序列并不能自动链接到哪个表，需要手动链接
序列不能提供给所有用户，只能提供给有权限的用户
dba可以查询所有序列，但是有权限的用户就可以使用序列
insert和update需要使用序列.nextval才能使用序列

19.约束 完整性约束

会自动删除子表中所有相关记录

在父表连接字段上建立主索引，在子表连接字段上建立普通索引

外键是参照完整性

语句执行，约束生效。

数据字典视图有三种：
user_:有关用户所拥有的对象的信息，即用户自己创建的对象的信息
all_:有关用户可以访问的对象的信息，即用户自己创建的对象信息加上用户可以有权限访问的对象的信息
dba_*:有关整个数据库对象的信息

*可以是tables,index,sequence等等

20.存储过程 pl/sql块

注意rollback;所有操作都回滚了

varhchar2在pl/sql中是32767字节 在sql中是4000字节
oracle中没有boolean类型，pl/sql中有
%type取得是某一字段类型

游标有%found，%notfound，%rowcount，%isopen四个属性。

更新和删除操作需要在游标定义后面加上for update子句

执行部分

stats_exist_exception是未定义的变量，需要定义为exception类型

21.触发器

触发器可以用于表和视图
触发器分为4种：DML、DDL、替代触发器（instance of）、DB触发器
触发器禁用/可用：
alter trigger trigger_name disable;
alter trigger trigger_name enable;
触发器是一种特殊的存储过程

DML触发器

语句级触发器，会生效1次

？

全部正确