Oracle数据库学习笔记

快来打我* 2021-12-04 00:13 388阅读 0赞

# Oracle数据库简介 #

Oracle数据库以分布式数据库为核心，多用于企业级B/S、C/S架构软件中，性能稳定，安全性强，处理大量数据能力强，是具有完整的数据管理功能的关系型数据库。

# Oracle安装 #

没有安装过Oracle，直接使用公司的数据库建立测试库。数据库可视化工具可以使用plSQL，也可以使用jetbrains出品的data grip。两个软件的安装过程这里忽略。

# Oracle体系结构 #

数据库：

数据库实例：通常情况下，Oracle只有一个实例ORCL。

表空间：由用户来管理。

数据文件（dbf）：实际存在的，用来存放表。

# Oracle和MySQL的比较 #

Oracle是多用户的，新建一个表空间由用户来管理负责创建表等；MySQL是多数据库的，创建一个数据库，再创建相应的表；

Oracle数据库的安全性大于MySQL，且适用大数据量数据库，价格昂贵。

1.  Oracle默认行级锁，每次UD都需要提交；MySQL不同引擎采用不同锁，InnoDB默认使用行级锁；
2.  Oracle的安全性大于MySQL，登录需多项验证；
3.  Oracle有表空间，角色管理，快照，同义词，包和自动管理；
4.  Oracle对数据库访问权限分类：
    
    ① DBA：拥有全部权限；②RESOURCE：可建实体，不可建库；③CONNECT：只能登陆不能操作。DBA可以通过grant指令下放权限。
5.  临时表区别：MySQL中临时表仅对当前用户会话可见的数据库对象，一旦会话结束，表自动删除；Oracle临时表中的数据仅对将数据插入表中的用户会话可见，并且数据可能在事务或用户会话期间持续存在。

# Oracle的虚表（伪表） #

SELECT 1+1；

上面这句SQL语句在MySQL中是可以执行到2的，但是在Oracle中执行却会报错，提示没有找到FROM表达式。

如何让上面的语句在Oracle中执行？ SELECT 1+1 FROM DUAL;

这里DUAL表是Oracle数据库提供的虚表，也叫伪表，用来补齐语法结构。

# Oracle语法（相对于MySQL语法） #

Oracle的连接方式独特，使用“(+)”来标注连接，根据“(+)”出现在等号的位置确定执行左连接还是右连接，符号在哪里，就是以哪表为基准进行连接。

-- 右连接的demo，查询出所有部门下的员工
    SELECT e.empno, e.ename, d.depton, d.dname FROM emp e, dept d WHERE e.depton(+) = d.depton;

也可以这样方便记忆：哪张表没有数据，“+”写在哪边。

## 关于模糊查询这里有些想说的 ##

Oracle的模糊查询和MySQL一样都是使用LIKE关键字，“\_”和“%”做通配符，但是Oracle对查询条件中含有通配符的查询语句该怎么写？

SELECT * FROM EMP WHERE ENAME LIKE '%\%%' ESCAPE '\';

式中ESCAPE关键字在这里对“\\”符号进行转义，意思“\\%”代表真正的“%”。

## 别名 ##

Oracle的别名同MySQL一样，使用可以省略的AS关键字定义。对于别名含有空格、特殊字符、关键字的情况需要加双引号。

SELECT ENAME "姓   名" ,JOB 岗位 FROM EMP;

## ![20190722190007885.png][] ##

## EXISTS在数据量大时的高效查询性能 ##

SELECT * FROM EMP WHERE EXISTS(SELECT * FROM EMP WHERE EMPNO=10086);
    -- 相当于
    SELECT * FROM EMP WHERE 1=2;-- 只有在条件满足时才会执行查询语句。这样的条件判断可大大减少子句查询的时间

## ROWNUM伪列 ##

系统自动生成的一列，用来表示行号。ROWNUM是一个Oracle关键字,用来表示行号，默认值是1，在查询出结果后加1。ROWNUM不能做大于号的比较，例如：ROWNUM>2这个条件是没有结果的，可以做小于号的判断。

SELECT ROWNUM,E1.* FROM EMP E1;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODkzMDcwNg_size_16_color_FFFFFF_t_70][]

-- 查询所有人里工资最高的三人
    SELECT E1.* FROM (SELECT E1.* FROM EMP E1 ORDER BY SAL DESC) E1 WHERE ROWNUM<=3;

### ROWNUM做分页查询 ###

ROWNUM不能做小于比较，所以提供的思路是将ROWNUM的查询结果取别名作为分页查询的子查询条件。下面给出查询第六条到第十条记录的查询语句：

SELECT * FROM (SELECT ROWNUM RN,EMP.* FROM EMP) TT WHERE TT.RN BETWEEN 6 AND 10;

![20190722205731265.png][]

## ROWID伪列 ##

代表每行记录所存放的真实物理地址。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODkzMDcwNg_size_16_color_FFFFFF_t_70 1][]

使用ROWID来将表中的重复数据删除：

第一一个P表中存放多组重复数据。

![20190722204801737.png][]

使用ROWID进行大小判断对表中重复数据进行去重：

DELETE FROM P P1 WHERE P1.ROWID> (SELECT MIN(ROWID) FROM P P2 WHERE P1.NAME = P2.NAME);

去重的结果：

![20190722204914637.png][]

## Oracle函数 ##

### 日期函数 ###

-- 查询当前系统时间
    SELECT SYSDATE FROM DUAL;
    -- 查询三个月后的今天
    SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;
    -- 查询三天后的此时
    SELECT SYSDATE + 3 FROM DUAL;
    -- 查询员工入职的天数
    SELECT CEIL(SYSDATE - HIREDATE) FROM EMP ;
    -- 查询员工入职的周数
    SELECT CEIL((SYSDATE - HIREDATE)/7) FROM EMP ;
    -- 查询员工入职的月数
    SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;
    -- 查询员工入职的年数
    SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE)/12 FROM EMP;

### 字符转换 ###

Oracle默认自动进行字符串和数字的转换。

-- 可以自动转换
    SELECT 100+'10' FROM DUAL;
    -- 手动转换
    SELECT 100+TO_NUMBER('10') FROM DUAL;
    -- 数值转字符
    SELECT TO_CHAR(SAL,'$9,999.99') FROM EMP;
    SELECT TO_CHAR(SAL,'L9,999.99') FROM EMP;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODkzMDcwNg_size_16_color_FFFFFF_t_70 2][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODkzMDcwNg_size_16_color_FFFFFF_t_70 3][]

### 日期转字符 ###

-- 日期转字符
    SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
    SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL; -- 一月中的第几天
    SELECT TO_CHAR(SYSDATE,'D') FROM DUAL; -- 一周中的第几天（从周日开始算1）
    SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL; -- 一年中的第几天（公历）
    SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL; -- 星期
    SELECT TO_CHAR(SYSDATE,'DY') FROM DUAL; -- 在英文环境下是星期的缩写

### 字符转日期 ###

-- 字符转日期
    SELECT TO_DATE('2019-7-22 17:24:30','YYYY-MM-DD HH24:MI:SS') FROM DUAL;

### 其他函数 ###

SELECT NVL(1,2) FROM DUAL; -- 如果1=null，返回2
    SELECT NVL2(1,2,3) FROM DUAL; -- 如果1=null，返回3，否则返回2
    SELECT NULLIF('1','2') FROM DUAL; -- 如果表达式1等于表达式2返回null，否则返回表达式1
    SELECT COALESCE(NULL,2,3) FROM DUAL; -- 返回第一个不为null的值

## 更多函数参考Oracle数据库API ##

# Oracle的特性 #

Oracle采用数据库->表空间->用户->表 的结构存储数据，一个数据库下可以创建多个**表空间**，一个表空间下可以创建多个**用户**，一个用户下可以创建多张**表**。表空间会被存储未dbf格式的文件，下面给出表空间的创建格式demo：

create tablespace mlgg001
    datafile 'd:\mlgg001.dbf'
    size 100m
    autoextend on
    next 10m;

表空间的创建权力在dba级别，我没法执行，下面对建表空间代码进行解释：

mlgg001是表空间名称

# Oracle游标 #

Oracle游标相当于jdbc中的resultMap，用来存放查询的结果集。

## 游标的语法 ##

cursor 游标名 is 查询结果集

## 游标的开发步骤： ##

1.声明游标

2.打开游标：  *open 游标名*

3.从游标中取数据： *fetch 游标名 into 变量 游标名%found：找到数据 游标名%notfound：没有找到数据*

4.关闭游标 *close 游标名*

# *多行子查询还是要注意null值问题。* #

[20190722190007885.png]: /images/20211203/3aed0fe6324c46fb8f8976bfb9e1b208.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODkzMDcwNg_size_16_color_FFFFFF_t_70]: /images/20211203/f86e4c36106d49fb9de73cc5875254e6.png
[20190722205731265.png]: /images/20211203/8653886be71a40d4bef61dcfe307b929.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODkzMDcwNg_size_16_color_FFFFFF_t_70 1]: /images/20211203/a61eb6cf7476454194cf075471ec60ce.png
[20190722204801737.png]: /images/20211203/74ea72f92d62469388caa502d0bf1ff4.png
[20190722204914637.png]: /images/20211203/8256331bd8334536a55447b404309b02.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODkzMDcwNg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190722170731808.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODkzMDcwNg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODkzMDcwNg_size_16_color_FFFFFF_t_70 3]: /images/20211203/d8d4b84a75824fa69bd885eaef4c645f.png