一条咸鱼的java学习笔记第7天之Oracle数据库

1.当前数据库的分类？什么是关系型数据库？什么是非关系型数据库？

数据库根据其数据的存储方式可以分为关系型数据库和非关系型数据库。常见的关系型数据库有Oracle、MySQL等。
关系型数据库：指采用了关系模型来组织数据的数据库。
关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。
非关系型数据库：指非关系型的，分布式的，且一般不保证遵循ACID原则的数据存储系统。
非关系型数据库以键值对存储，且结构不固定，每一个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对，不局限于固定的结构，可以减少一些时间和空间的开销。

2.Orale数据库中单引号和双引号的区别？

在双引号" "中，一般在如下场合使用
1、表示其内部的字符串严格区分大小写 （比如用作字段别名时区分大小写）
2、用于特殊字符或关键字 （比如包含空格，#或&时）
3.不受标识符规则限制
4.会被当成一个列来处理
5.当出现在to_char的格式字符串中时，双引号有特殊的作用，就是将非法的格式符包装起

而在单引号’ ‘中,一般在如下场合使用
1、 表示字符串常量 （比如用于条件限定时where=‘aa’，单引号用于条件限定时对大小写敏感）
2、字符串中的双引号仅仅当作一个字符串"处理，可以在单引号’ ‘中使用双引号"
3、 如果字符串常量中包含了单引号’ '，那么需要使用两个单引号 ‘’ 表示一个单引号常量

3.查询语句中对于null的处理？运算表达式中null值的处理? mysql和oracle不太一样

oracle: nvl(字段,0) 作用：判断某个值是否为空值，若不为空值则输出，若为空值，返回指定值。
mysql: ifnull(字段,0);

可以利用NULL值函数实现。

4.distinct关键字的用法

distinct这个关键字来过滤掉多余的重复记录只保留一条

5.查询结果集如何排序？ asc/desc 多字段排序

6.mysql和oracle查询结果中字段拼接的方式？

mysql使用concat函数进行拼接；Oracle 使用 || 进行拼接；

7.数据库常用的函数：字符串函数、数值函数、日期函数；分别列举Mysql和Oracle的三者之间的转换函数？

转换函数主要是对字符串、数值、日期之间的相互转换；
to_char 将日期、数值转换为指定格式的字符串；
to_number 将字符串转换为数值类型；
to_date 将指定格式的字符串转换为日期类型；

8、Oracle的通用函数：nvl（重点掌握）、nvl2、case-when、decode函数；

详解

9、什么是外连接？有几种？【提示：左外连接、右外连接、全外连接】 笛卡尔连接也是外连接（不常用）

内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。

外连接：连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。

左外连接：左边表数据行全部保留，右边表保留符合连接条件的行。

右外连接：右边表数据行全部保留，左边表保留符合连接条件的行。
全外连接 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

10、什么是等值连接？常用的等值连接有哪几种？ 【where等值连接、join-on等值连接、自然等值连接、using等值连接】

（1）等值连接：在连接中使用等号（=）操作就是等值连接。

比如查询部门编号为30的员工编号、姓名、部门名称。

（2）不等值连接：主要用除了等号之外的操作符，比如：<>、>、<、>=、<=、LIKE、IN、BETWEEN…AND。

比如查询工资为1500以上的员工所属部门和所在的具体地点。

自然连接(Natural join)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。 [1]

11.什么是分组查询？分组函数【提示：count、sum、avg、max、min，注意：分组查询函数会自动过滤null值，可以使用nvl函数进行预处理】

group by 是分组，是分组，是分组，分组并不是去重，而是分组

将查询结果按一个或多个进行分组，字段值相同的为一组

12.如何进行分组查询？group by如何使用？什么是having语句，如何使用？

当分组函数（聚合函数）作为查询条件时 需要使用having语句

GROUP BY语句用来与聚合函数(aggregate functions such as COUNT, SUM, AVG, MIN, or MAX.)联合使用来得到一个或多个列的结果集。

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

13.分组查询需要注意的语法点：分组查询中select部分显示的字段，要么是分组函数要么是出现group by之后的字段；其他的字段不可以；

14.什么是子查询？exists关键字如何使用？

当一个查询结果是另一个查询的条件的时候，那么就称为子查询，子查询是在SQL语句内的另外一条SELECT语句。
exists用于检查一个子查询是否至少会返回一行数据(即检测行的存在)，返回值为true或false。
语法： exists subquery
参数： subquery是一个受限的select语句(不允许有compute子句和into关键字)，该语句返回一个结果集。
结果类型： boolean类型——如果子查询包含行，则返回true，否则返回false，即言：exists根据subquery的结果集是否为空来返回一个布尔值——如果不为空则返回true，否则返回false。

15.什么是多行子查询和单行子查询？【提示：单行子查询使用exists关键字；多行子查询使用：in、any、all、>、=、<】

单行子查询 返回一行数据的子查询语句
多行子查询 返回多行数据的子查询

16.rowid和rownum伪列对象的异同点？

做过Oracle分页的人都知道由于Oracle中没有像MySql中limit函数以及SQLServer中的top关键字等，所以只能通过伪列的方式去满足分页功能，
rownum和rowid都是伪列，但是两者的根本是不同的，rownum是根据sql查询出的结果给每行分配一个逻辑编号，所以你的sql不同也就会导致最终rownum不同，但是rowid是物理结构上的，在每条记录insert到数据库中时，都会有一个唯一的物理记录
一、ROWNUM伪列

ROWNUM伪列是Oracle首先进行查询获取到结果集之后在加上去的一个伪列，这个伪列对符合条件的结果添加一个从1开始的序列号先看一个例子：

SELECT ROWNUM,empno,ename,job FROM emp WHERE deptno = 30;结果如下

rowid的用法 可以去重复数据

同rownum伪列不同的是，他是物理存在，rowid是一种数据类型，基于64位编码的18个字符来唯一标志一条记录物理位置的一个id,rowid可以在表中进行查询，但是其值并未存储在表中，所以不支持增删改操作。由于ROWID用来唯一标识表中数据的唯一性，所以可以利用这个特性去除重复

17.Oracle和Mysql分页查询如何实现？ 【Oracle：集合操作；rowid伪对象+子查询；Mysql：limit关键字】

1.oracle使用rownum实现从第几行到第几行:
select * from(select a.*,rownum rn from (select * from TABLENAME) a where rownum <=end ) where rn >= begin;
2.mysql是从第几行开始取多少行:
select * from TABLENAME limit start , end;

18.delete与truncate的异同点？

作用都是用来删除数据的，truncate和 delete（不带条件，全表删除数据）只删除数据不删除表的结构，不同点如下。

1、truncate删除数据，但是不备份，删除后无法回滚；delete会备份，可以回滚（回滚（Rollback）指的是程序或数据处理错误，将程序或数据恢复到上一次正确状态的行为。回滚包括程序回滚和数据回滚等类型。）

2、truncate删除非常快，删除之后，自增id从0开始统计；而delete删除慢，自增id继续累加

19.DML操作是受事务管理的；

DML : 数据操作语言，对表格中的数据进行操作,insert、update、delete。
事务管理是对于一系列数据库操作进行管理，一个事务包含一个或多个SQL语句，是逻辑管理的工作单元。

20.什么是事务？事务的特点？cadi

事务：是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合（工作逻辑单元）；
事务的四大特性：

1 、原子性
事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
2 、一致性
事 务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。
3 、隔离性
一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
4 、持续性
也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

21、事务的隔离级别？常用的有4个隔离级别，每个隔离级别可能产生的问题？ 顺序读是安全的。

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted(未授权读取、读未提交)、Read committed（授权读取、读提交）、Repeatable read（可重复读取）、Serializable（序列化），这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

22、常用的约束有哪些？非空约束（null）、唯一性约束、主键约束、外键约束、自定义check约束；

23.了解权限、角色、如何创建Oracle用户？如何赋予权限和剥夺权限？权限的传递性？

一、创建用户

语法[创建用户]： create user 用户名 identified by 口令[即密码]；

例子： create user test identified by test;

1.对表增删改查授权

对用户授权，允许其具有对数据表user的更新和删除的操作权限：
GRANT UPDATE,DELETE ON user
TO member WITH GRANT OPTION –WITH GRANT OPTION表示该用户可以向其他用户授予他所拥有的权限；
2.对存储过程授权

对用户menber对存储过程user_income的授权
GRANT EXECUTE ON user_income TO member存储过程
2.禁止对表的授权

—–禁止member用户对数据表user的更新权限：
DENY UPDATE ON user
TO member CASCADE;
3.收回用户对表的权限

—–收回用户member对user表的删除权限：
REVOKE DELETE ON user
FROM member

24.什么视图？如何创建视图？视图和物理表之间有什么差异性？

1.1. 视图
1.1.1. 什么是视图
视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。
视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

创建视图的语法：
01.CREATE [OR REPLACE] VIEW view_name[(alias[, alias…])]
02.AS subquery ;

区别：1、视图是已经编译好的sql语句。而表不是
2、视图没有实际的物理记录。而表有。
3、表是内容，视图是窗口
4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
5、表是内模式，视图是外模式
6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
8、视图的建立和删除只影响视图本身，不影响对应的基本表。

联系：视图（view）是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

25、什么序列？

序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。不占用磁盘空间，占用内存。

其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。

26、什么是索引？索引的作用？索引在什么情况下可以使用？什么情况下不建议使用？

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，相 当于图书上的目录，可以加快查询速度，但会使修改、插入、删除操作变慢，同时还会 占用较大的磁盘空间 。

可以通过explain语句对SQL语句进行分析，来决定哪些字段需要创建索引，一般情 况下，可得出以下结论：
必须创建索引的情况：
1．where子句、group子句中出现的字段，要创建索引
2．order by子句的字段，要创建索引
3．统计聚合函数中的字段，要创建索引。比如count(字段) 、max(字段)

不需要创建索引的情况：
1．如果需要取到表中所有记录，则没必要创建索引
2．对非唯一有大量重复值的字段，没必要创建索引，如性别
3．经常进行修改、删除等操作的字段，没必要创建索引
4．记录比较少的表，没必要创建索引

27、什么是存储过程？什么是函数？有什么异同点？ 参数的类型有哪些？【提示：out、in、inout三种类型】【视能力掌握】

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合，调用存储过程函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数区别：
存储过程没有返回值；
函数可以有返回值；

传递的参数有三种类型：
in类型：默认的；
只能从外往里传值；
out类型：
只能从里往外传值；
in out类型：
可以从里往外传值也可以从外往里传值；

28、什么是触发器？什么是游标？如何使用？【视能力掌握】

触发器就是按照条件在一定的时机下自动执行的某种动作处理；
触发器就是定时按条件触发的动作行为；
游标：是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。
如何使用？
1、定义游标
CURSOR emp_cursor IS
SELECT first_name,salary
FROM employees WHERE department_id=60;
– 定义变量
emp_record emp_cursor%rowtype;

— 主体
BEGIN
– 2、打开游标
OPEN emp_cursor;
– 3、提取游标
LOOP
– fetch 游标名 into 变量列表 表示将游标中的一行数据 对应赋值给 变量列表中的变量
— 注意 into 后面的变量列表需要和游标中的子查询中的显示的字段列表一一对应
FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_cursor%NOTFOUND;
– 输出
DBMS_OUTPUT.PUT_LINE(emp_record.first_name||‘的薪水是’||emp_record.salary);
END LOOP;
– 4、关闭游标
CLOSE emp_cursor;
END;

29、Oracle和Mysql数据库如何使用主键字段值自增？【Oracle：序列+触发器（还有其他方式）；Mysql：自增属性】在mysql中，如果把表的主键设为auto_increment类型，数据库就会自动为主键赋值。

Oracle
--------------使用序列和触发器实现主键字段自增（要求必须掌握）--------------------
— 使用序列和触发器实现主键字段自增（要求必须掌握）
— 创建表
create table dept100 as select * from dept where 1=2;
– 查询表
select d.dept_id,
d.dept_name,
d.manager_id,
d.location_id from dept100 d;

— 创建自增主键字段的序列
create sequence seq_dept100_deptId
–start with 1 — 起始值 默认为 1
–increment by 1 — 自增步长 默认步长为1
–nomaxvalue — 没有最大值 默认为nomaxvalue
–nocycle — 不循环 默认为 nocycle
–nocache — 不使用缓存 默认为 nocache

— 创建自增主键字段的触发器
create or replace trigger trigger_dept100_deptId
before insert on dept100
for each row
begin
– 使用new伪对象，在插入之前将序列提供的值 赋值给表 dept100的主键字段
select seq_dept100_deptId.nextval into :new.dept_id from dual;
end;

— 测试数据
insert into dept100(dept_name,manager_id,location_id) values(‘IT’,100,10);
commit;

insert into dept100(dept_name,manager_id,location_id) values(‘SALE’,101,11);
commit;

– 删除一条记录
delete from dept100 where dept_id = 2;
commit;

– 再次插入
insert into dept100(dept_name,manager_id,location_id) values(‘SALE’,101,11);
commit;

--------------创建用户并赋予权限-------------------------
– 创建用户
create user yuw2019 identified by oracle;
– 赋予用户对应的权限（角色）
grant connect,resource to yuw2019;

---

----- ★★★★★★★★★★ ---------------

30、备份与还原？

Oracle数据库备份
逻辑备份：
使用imp/exp命令进行备份；
需要在dos命令窗口下执行；

使用plsql客户端工具进行备份；

注意：
使用哪种方式进行导出备份的，需要使用与之对应的方式进行导入恢复；

31、你在项目中有没有做过数据库可以做哪些优化？【Oracle和Mysql分别说明】【一定要提前准备整理】★★★

1.SQL语句尽量用大写的

因为oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。

2.使用表的别名

当在SQL语句中连接多个表时, 尽量使用表的别名并把别名前缀于每个列上。这样一来,就可以减少解析的时间并减少那些由列歧义引起的语法错误。

3.选择最有效率的表名顺序(只在基于规则的优化器中有效)

ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表也称为驱动表,driving table)将被最先处理，在FROM子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。
　4.Where子句后面的条件过滤有讲究

ORACLE对where子句后面的条件过滤是自下向上，从右向左扫描的，所以和From子句一样一样的，把过滤条件排个序，按过滤数据的大小，自然就是可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾最下面，最右边，依次类推。
　　5.在select的时候少用*

多敲敲键盘，写上字段名吧，因为ORACLE的查询器会把*转换为表的全部列名，这个会浪费时间的，所以在大表中少用。

6.使用rowid
　　　7.减少对数据库表的查询

ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等，所以少一次访问就能提高更高的效率。

8.使用DECODE函数来减少处理时间

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

使用方法：https://www.cnblogs.com/ghzjm/p/9517127.html

9.整合简单,无关联的数据库访问

如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

10.删除重复记录

最高效的删除重复记录方法 。。。。
　　摘抄地址，内容太多了

32、什么是范式？经常用的有哪些范式？

范式的含义：
符合某种级别的关系模式的集合。表示一个关系内部的各属性之间联系的合理化程度。可以理解为：数据表的表结构所符合的某种设计标准的级别。
第一范式：是对关系模式的基本要求。不满足第一范式的关系，不能称为关系型数据库。符合第一范式的关系，每个属性都不可以再分割。
但是如果仅仅满足第一范式：仍然存在数据冗余过大、插入异常、删除异常、修改异常等的问题。
第二范式：建立在第一范式的基础上，首先满足第一范式。
消除了非主属性对码的部分函数依赖。
概念解析：
函数依赖，在一张表中，属性x(可能为一个属性也可能为一个属性组)确定的情况下，Y的取值必定可以确定。我们就说Y依赖于X。跟y=f(x)的关系很相似。
衍生三个函数依赖 的概念：
完全函数依赖，部分函数依赖，传递函数依赖。
属性Y依赖于属性X。X可能为一个属性或者一个属性组。
摘抄地址

33、数据库死锁？加锁粒度？

数据库中常见的死锁原因与解决方案有：

1. 事务之间对资源访问顺序的交替
2. 并发修改同一记录
3. 索引不当导致的死锁
   摘抄地址