Oracle学习笔记简版

一、Oracle简介

二、Oracle安装与配置

OracleOraDb11g_home1TNSListener：监听服务，如果要通过程序或者是不同的客户端连接Oracle数据库的时候，此服务必须启动，否则无法连接。
OracleServiceMLDN：数据库的实例服务。

三、SQLPlus命令

四、SQL简介与数据表分析

1、SQL简介

SQL是结构化查询语言。
DML：数据操作语言。DDL：数据定义语言。 DCL：数据库控制语言。

2、Scott用户表结构

查询一个用户下的所有数据表：SELECT *FROM tab;
查询一个表的结构：DESC dept；

五、数据查询

1、SQL简单查询

简单查询指的是查询一张数据表中所有数据行的内容。

②SELECT [DISTINCT] * 列名称[别名]，列名称[别名]，...
①FROM 表名称 [别名];

首先执行的是FROM子句，因为必须通过FROM子句确定数据的来源，而后要针对数据进行筛选，那么就通过SELECT子句完成。

(1)简单查询不能控制数据行，只能够在SELECT子句里面控制列。
SELECT empno,ename FORM emp;

(2)去除掉所有的重复信息，使用“DISTINCT”完成。
SELECT DISTINCT job FROM emp;

(3)SELECT中可以进行四则运算。

(4)SELECT中可以直接输出常量内容，对于字符串使用“’”、数字直接编写、日期按照字符串格式。

(5)“||”负责进行输出的内容连接，此类操作一般很少直接在查询中出现(存储过程中会用到）。

2、SQL限定查询(部分数据行)

③SELECT [DISTINCT] * 列名称[别名]，列名称[别名]，...
①FROM 表名称 [别名]
②[WHERE 过滤条件]；

关系运算符：>、<、>=、<=、<>(!=)；
逻辑运算符：AND、OR、NOT；
范围运算符：BETWEEN…AND
谓词范围：IN、NOT IN;
空判断：IS NULL、IS NOT NULL;
模糊查询：LIKE

(1)关系运算符
关系运算符主要是进行大小关系比较操作使用的。
范例：查询出所有基本工资高于1500的雇员信息。

SELECT * FORM emp WHERE sal>1500;

(2)逻辑运算符
多个条件要进行连接，需要判断是与连接还是外连接。
与连接：所有的判断条件都要满足；
或连接：若干个条件有一个满足即可。
范例：查询出工资在1500~3000之间的雇员信息

SELECT * FROM emp WHERE sal>1500 AND sal <3000;

(3)范围查询
在进行查询条件过滤的时候针对某一个范围进行过滤。
范例：查询工资在1500~2000之间的雇员

SELECT * FROM emp WHERE sal>=1500 AND sal<=2000;
SELECT * FROM emp WHERE sal BETWEEN 1500 AND 2000;

(4)空判断
空在数据库上解释为不确定的内容。但是要注意的是在数字列上使用null那么绝对不表示0.对于null的判断不能够使用关系运算符完成。空的操作只能够使用IS NULL或者是IS NOT NULL(NOT IS NULL)表示。
范例：查询所有领取佣金的雇员信息(佣金存在，不为空)

SELECT * FROM emp WHERE comm IS NOT NULL;
SELECT * FROM emp WHERE NOT comm IS NULL;

(5)IN操作符
IN给出的是一个指定的可选范围。
范例：查询出雇员编号是7369、2239、3894的雇员信息。

SELECT * FROM emp WHERE empno IN(7369,2239,3894);

(6)模糊查询：LIKE
在使用LIKE的时候可以使用两个通配符：
“_”：匹配任意的一位字符；
“%”：匹配任意的零位、一位或多位字符。
范例：查询姓名是以字母A开头的雇员信息。

SELECT * FROM emp WHERE ename LIKE ‘A%’;

注：使用LIKE实现模糊查询的时候，如果不设置关键字’%%’表示查询全部。
使用NOT IN查询的时候，查询的时候里面不允许出现null,否则不会有任何查询结果返回。

3、查询排序

③SELECT [DISTINCT] * 列名称[别名]，列名称[别名]，...
①FROM 表名称 [别名]
②[WHERE 过滤条件]
④[ORDER BY 字段 [ASC|DESC],字段 [ASC|DESC],...]；

对于排序的方式有两种：ASC(默认升序)，DESC(降序)
范例：查询所有的雇员信息，要求按照工资由高到低排序

SELECT * FORM ORDER BY sal DESC;

在整个SQL查询中，只有ORDER BY子句可以调用SELECT子句里面定义的别名。

4、单行函数

5、多表查询(性能差，开发中尽可能回避，大数据中不要使用)

③SELECT [DISTINCT] * 列名称[别名]，列名称[别名]，...
①FROM 表名称 [别名]，表名称 [别名]
②[WHERE 过滤条件]
④[ORDER BY 字段 [ASC|DESC],字段 [ASC|DESC],...]；

两个集合发生了积的关系，想要消除笛卡尔积的问题，就必须想办法为两张数据表设置关系。
范例：消除掉积的影响

SELECT * FROM dept,emp WHERE emp.deptno=dept.deptno

以上代码实际上只是消除了显示的笛卡尔积，而在数据库的多表查询之中，笛卡尔积会一直存在，只要是存在有数据表，那么一定会存在有笛卡尔积。
在任何情况下，如果要想实现多表查询操作，永远只有一个前提：要关联的数据表一定要存在有关联字段或者是关联条件，不存在这些要求的，一定不能够使用多表查询。

6、表的连接

内连接(等值连接)：所有满足条件的数据都会被显示出来；
外连接(左外、右外、全外)：控制左表与右表的数据是否完全显示。
(1)内连接
之前使用的都是内连接。

(2)外连接
左外连接：字段=字段(+);
右外连接：字段(+)=字段;

范例：观察左外连接

SELECT e.empdno,e.ename,e.job,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno(+)

此时会把左表完全显示，而部分右表内容没有对应数据，则部门数据即为空。

7、SQL：1999语法支持

8、数据集合操作

9、分组统计查询

(1)五个常用的统计函数：

统计个数：COUNT(*|[DISTINCT]字段)；
MAX(字段)、MIN(字段)；
SUM(数字字段)、AVG(数字字段)

范例：查询所有雇员之中最高和最低工资

SELECT MAX(sal),MIN(sal) FROM emp;

解释COUNT(*)、COUNT(字段)、COUNT(DISTINCT 字段)的区别？(★★★★★)
◆COUNT(*)：明确的返回表中的数据个数，是最准确的。
◆COUNT(字段)：不统计为null的数据个数，如果某一列的数据不可能为空，那么结果与COUNT(*)相同。
◆COUNT(DISTINCT 字段)：统计消除掉重复数据后的数据个数。

(2)能够分组的时候往往指的是部分数据具备某些共性。

④SELECT [DISTINCT] 分组字段 [别名]，... |统计函数
①FROM 表名称 [别名]，表名称 [别名]
②[WHERE 过滤条件]
③GROUP BY 分组字段，分组字段，...]
⑤[ORDER BY 字段 [ASC|DESC],字段 [ASC|DESC],...]；

范例：按照职位分组，统计出每个职位的名称、人数、平均工资

SELECT job,COUNT(empno),AVG(sal)  FROM emp GROUP BY job;

◆限制一：在没有编写GROUP BY子句的时候，那么SELECT子句之中只允许出现统计函数。
◆限制二：在使用GROUP BY 子句分组的时候，SELECT子句之中只允许出现分组字段与统计函数，其他字段不允许出现。
◆限制三：统计函数允许嵌套查询，但是嵌套后的统计查询中，SELECT子句里面不允许再出现任何的字段，包括分组字段，只能够使用嵌套的统计函数。

⑤SELECT [DISTINCT] 分组字段 [别名]，... |统计函数
①FROM 表名称 [别名]，表名称 [别名]
②[WHERE 过滤条件]
③GROUP BY 分组字段，分组字段，...]
④HAVING 分组后的过滤条件]
⑥[ORDER BY 字段 [ASC|DESC],字段 [ASC|DESC],...]；

关于HAVING子句与WHERE的区别？(★★★★★)
◆WHERE发生在GROUP BY操作之前，属于分组前的数据筛选，即：从所有的数据之后筛选出可以分组的数据，WHERE子句不允许使用统计函数。
◆HAVING发生在GROUP BY操作之后，是针对于分组后的数据进行筛选，HAVING子句可以使用统计函数。

注：多表查询与分组统计的时候，查询结果相当于是一张临时表，所有的分组是在临时表里面完成的。

10、子查询(★★★★★)

子查询指的是在一个查询里面继续嵌套其他的查询语句。

SELECT [DISTINCT] 分组字段 [别名]，... |统计函数 (
    SELECT [DISTINCT] 分组字段 [别名]，... |统计函数
    FROM 表名称 [别名]，表名称 [别名]
    [WHERE 过滤条件]
    GROUP BY 分组字段，分组字段，...]
    HAVING 分组后的过滤条件]
    [ORDER BY 字段 [ASC|DESC],字段 [ASC|DESC],...]；)
FROM 表名称 [别名]，表名称 [别名] (
    SELECT [DISTINCT] 分组字段 [别名]，... |统计函数
    FROM 表名称 [别名]，表名称 [别名]
    [WHERE 过滤条件]
    GROUP BY 分组字段，分组字段，...]
    HAVING 分组后的过滤条件]
    [ORDER BY 字段 [ASC|DESC],字段 [ASC|DESC],...]； )
[WHERE 过滤条件](
    SELECT [DISTINCT] 分组字段 [别名]，... |统计函数
    FROM 表名称 [别名]，表名称 [别名]
    [WHERE 过滤条件]
    GROUP BY 分组字段，分组字段，...]
    HAVING 分组后的过滤条件]
    [ORDER BY 字段 [ASC|DESC],字段 [ASC|DESC],...]；)
GROUP BY 分组字段，分组字段，...]
HAVING 分组后的过滤条件](
    SELECT [DISTINCT] 分组字段 [别名]，... |统计函数
    FROM 表名称 [别名]，表名称 [别名]
    [WHERE 过滤条件]
    GROUP BY 分组字段，分组字段，...]
    HAVING 分组后的过滤条件]
    [ORDER BY 字段 [ASC|DESC],字段 [ASC|DESC],...]；)
[ORDER BY 字段 [ASC|DESC],字段 [ASC|DESC],...]；

◆WHERE子句：子查询一般会返回单行单列、单行多列、多行单列数据；
◆HAVING子句：子查询会返回单行单列，同时表示要使用统计函数；
◆FROM子句：子查询会返回多行多列数据(表结构)；
◆SELECT子句：返回单行单列，不过一般不使用。

如果是子查询，首先考虑的一定是WHERE或FROM子句里面出现子查询的操作，像SELECT子句几乎是可以忽略掉的，而HAVING子句出现在子查询只有在使用统计函数的时候才会使用。
子查询最大的作用是解决了多表查询所带来的笛卡尔积影响的性能问题。

复杂查询=简单查询+限定查询+多表查询+分组统计查询+子查询

六、数据更新、修改、删除

1、数据增加

INSERT INTO myemp(empno,sal,job,comm,ename) VALUES(777,9000.0,’清洁工’，10.0，‘张三’);

2、数据修改

UPDATE myemp SET sal=8000,comm=9000 WHERE ename=’SMITH’;

总结：以后只要是更新操作，不可能不写WHERE子句，如果不写就要更新全部。

3、数据删除

DELETE FROM myemp WHERE empno=7566;

七、事务

1、事务处理

同一操作业务，要保证多个更新操作要么同时完成，要么同时失败，就会有事务。
事务是针对于数据更新使用的，只有DML的更新操作才存在有事务的支持。
◆commit：事务提交，即：如果已经执行了多条更新操作，那么只有执行了commit之后更新才会真正发出，在没有commit之前，所有的更新操作都会保存在缓冲区中。
◆Rollback：事务回滚操作，即：如果发现更新的操作有问题，则恢复所有的更新操作，以保证原始数据不被破坏。

2、死锁

SessionA与SessionB更新了同一个数据。一直等待中。

八、数据伪列

1、ROWNUM

利用ROWNUM针对于显示的数据进行一个自动的行号编号。

2、ROWID

所有表中的数据行都会有一个唯一的一块物理地址编号，这个编号可以通过ROWID查找到。
任何情况下，都可以通过ROWID确定唯一的一行记录。

九、表的内容

1、常见的数据类型

VARCHAR2(n);NUMBER(n，m);DATE;CLOB;BLOB

2、创建数据表

3、表的重命名

4、截断表

5、复制表

6、表的删除

DROP TABLE 表名称；

7、闪回技术

当执行数据表删除之后并不会真正立刻发出删除操作，而是先将数据表保存在回收站之中，并且标记处回收站的内容不是空的，就在表的数据字典上提供有了”BIN*”。
如果希望表可以直接删除，就在删除表的时候加上“PURGE”。

8、修改表结构

十、约束(★★★★★)

约束是为了保证表中的数据尽可能是合法有效的数据。
约束有六种：数据类型、唯一约束、非空约束、主键约束、外键约束、检查约束

DROP TABLE member PURGE;
CREATE TABLE member(
  mid NUMBER,
  name VARCHAR2(20) NOT NULL
  email VARCHAR2(30) 
  CONSTARINT uk_email UNIQUE(email)   唯一约束
  CONSTARINT pk_mid PRIMARY KEY(mid)  主键约束
  CONSTARINT pk_mid_name PRIMARY KEY(mid,name) 复合主键
  CONSTARINT ck_age CHECK (age BETWEEN 0 AND 250) 检查约束
  CONSTARINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)  外键约束
);

数据的级联删除：

 CONSTARINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE

级联更新：

 CONSTARINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL

十一、序列(★★★★★)

在很多数据库中都存在有一种提供自动增长列的数据类型，几乎所有的关系型数据库都支持自动增长列的操作，但是只有Oracle特殊，它只有在Oracle 12C版本之后才提供有自动增长列，在这之前没有。此时只能通过序列来完成。

创建序列

CREATE SEQUENCE myseq;

查询序列

SELECT * FROM user_sequences;

大部分情况下，使用序列的时候都只会创建默认序列，默认序列就是指最小值为1，并且没有最大值，开始值为0，并且设置有20个缓存数据。

对于序列的使用可以采用两个伪列操作：
◆序列对象.nextval:表示进行序列的增长，每调用一次，序列加上指定的步长。
◆序列对象.currcal:表示取得当前的序列内容，不管如何调用，序列内容不发生改变。
当序列对象创建完成之后，严格来讲序列是无法直接执行currcal的，必须先执行nextval之后才可以使用currcal。

十二、同义词

十三、视图(★★★★★)

所谓视图就是包装了复杂查询的SQL语句对象。

CREATE [OR REPLACE] VIEW 视图名称 AS 子查询；

严格来讲视图的数据是不能够修改的，因为视图中保存不是真实而只是查询结果的数据，并且在以上的复杂查询里面这样的操作更是不可能修改。

总结：
如果是标准的开发，强烈建议使用视图，这样可以很好的实现分工。
定义的视图都建议使用只读视图(WITH READ ONLY)，因为视图中的数据都不是真实数据。

十四、索引(★★★★★)

以前所有的都采用了全表扫描。

CREATE INDEX  emp_sal_ind ON scott.emp(sal)；

一旦加入了索引机制，那么不再执行全表扫描的操作应用了，而是使用索引进行了查询，但是这个索引也只是针对于sal字段上起作用。

数据库查询之中可以使用索引来提升查询性能，但是不适合应用在数据更新频繁的操作上。

十五、用户管理

十六、数据库备份

十七、数据库设计(★★★★★)

在初期结构之中可以根据范式来进行设计，但是在最终你会发现所有的范式都会被打破，so只有一个原则：根据业务的需要尽可能的减少多表查询或复杂查询。

第一范式：数据表中的每一个字段都不可再分。  单表
第二范式：数据表之中不存在非关键字对任意一候选关键字段的部分函数依赖。 多对多
        简而言之就是数据表之中的两个列之间不要存在有数学关系。
第三范式：数据表之中不存在非关键字段对任意一候选关键字段的传递函数依赖。                                            一对多

在实际开发中第三范式一定最优先考虑的。