Oracle学习总结

一、引入数据库

1.文件系统的不足

1.1缺乏对数据检索的支持（文件系统不会对数据的有效性和完整性作出检查）
1.2文件的大小有限制（文件系统不适合存储数据量比较大的信息）
1.3文件没有必须的数据类型（文本文件中存储的数据都是字符串类型）
1.4文件缺乏并发访问的支持
1.5没有任何安全保护

二、数据库的分类

1.关系型数据库（RDB Relationship DataBase）

关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织
优点：
1、易于维护：都是使用表结构，格式一致；
2、使用方便：SQL语言通用，可用于复杂查询；
3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。
缺点：
1、读写性能比较差，尤其是海量数据的高效率读写；
2、固定的表结构，灵活度稍欠；
3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

2.非关系型数据库

非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。
对象型数据库（Object DataBase)
NOSQL数据库（Not Only SQL）

优点：
1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
2、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
3、高扩展性；
4、成本低：nosql数据库部署简单，基本都是开源软件。
缺点：
1、不提供sql支持，学习和使用成本较高；
2、无事务处理；
3、数据结构相对复杂，复杂查询方面稍欠。
非关系型数据库的分类和比较：
1、文档型
2、key-value型
3、列式数据库
4、图形数据库

三、数据库的工作模型

1.关系型数据是基于客户端服务器模式工作的

C/S结构，即Client/Server(客户机/服务器)结构
改进C/S结构后的B/S结构，即Browser/Server(浏览器/服务器)结构
其中服务器就是数据库

2.数据库通过SQL语言 (Structured Query Language)对数据进行管理

SQL 是一门== ANSI== 的标准计算机语言，用来访问和操作数据库系统。SQL 语句用于取回和更新数据库中的数据。所有的数据库厂商都会遵从SQL规范来设计自己的产品，这就是说，在不同的数据库之间标准SQL语句是可以通用的。当然，各个厂商也会结合自身的特点对标准SQL进行扩展。对于程序员而言，标准SQL的学习占到了学习总量的80%以上。

四、数据库的安装

按照提示不停的点击next就可以，注意记录输入的用户口令。
注意：oracle安装路径需要不能含有中文或者空格。
验证是否安装成功：

1.查看服务

a)OracleServiceXE
b)OracleXETNSListener

2.打开浏览器，输入网址http://localhost:8080/apex 查看是否可以访问

3.OracleXE数据库的卸载

重新执行安装程序，选择卸载项。

五、Oracle数据库的访问方式

1.CMD命令行方式访问 sqlplus 访问

2.Web页面访问 isqlplus [oracle11g 开始不再支持]

3.第三方工具：PL/SQL developer 工具

六、数据库的相关名词与概念

1. 表（Table）组织管理数据的基本单位
2. 行（Row）代表一个数据
3. 列（Column）描述数据的属性（特征）
4. 特殊的列：
a)主键（primary key）用来在表中唯一的标识一行数据的
b)外键（foreign key）用来记录表和表之间的关系
5. 用户（Account）
a)用来完成身份认证，连接数据库的
b)数据库通过用户来管理表

七、SQL结构化查询语言

一、单表查询（以Employee表为例）

1.查询所有

select * from 表名;

注意：*进行所有列的查询，运行效率偏低。

2.查询的定列的内容

      select 列名1,列名2,……
      from 表名;

3.对列的内容进行运算(±*/)

    select last_name,first_name,salary*12
    from employees;

注意：%不代表取余。

4.列起别名

    select 列名
    [as] 别名，列名 [as] 别名……
    from employees 别名;

注意：列名起别名的as可以省略，表起别名时一定要省略as

5.多列内容的连接（字符串拼接）

||运算符可以完成2个列或者多个列的内容拼接

    select first_name||' '||last_name as name,salary
    from employees;

6.数据去重distinct

    select distinct salary from Employees;

注意：多列查询时，dicstinct必须在开头，且后面所有列都去重。

7.查询当前日期和时间sysdate

    Select sysdate from 表名;

8.case when

    select salary,case
    when salary >= 12000 then '优秀'
    when salary >= 8000 then '还行'
    else '垃圾'
    end 薪资水平 from employees;

注意：then后面的标识符加单引号，end后面不加单引号。

9.条件查询（where）

9.1比较查询

>,>=,=,<=,<,!=

    select * from employees where salary = 10000;
    select * from employees where first_name = 'Peter';

9.2多条件查询

and相当于与运算符&&，or相当于或运算||

    select * from employees where first_name = 'Peter' and salary = 10000;
    select * from employees where salary >= 8000 or first_name = 'Peter';

9.3区间查询

特殊谓词：between，简化and和or连接的语句（连续的区间）

    select * from employees where salary >= 8000 and salary <= 12000;
    select * from employees where salary between 8000 and 12000;
    select * from employees where salary <= 8000 or salary >= 12000;
    select * from employees where salary not between 8000 and 12000;

9.4枚举查询

特殊谓词：in，简化and和or连接的语句（离散的点）

    select * from employees where salary != 8000 and salary != 10000 and salary != 12000;
    select * from employees where salary not in (8000,10000,12000);
    select * from employees where salary = 8000 or salary = 10000 or salary = 12000;
    select * from employees where salary in (8000,10000,12000);

9.5 null查询

    select * from employees where manager_id is null;
    select * from employees where manager_id is not null;

9.6模糊查询

%代表任意个字符
_代表一个字符

    select * from employees where first_name like 'K%';（K开头）
    select * from employees where first_name like '%o';（结尾为o）
    select * from employees where first_name like '%i%';（含有i）
    select * from employees where first_name like 'K____';（K开头，后面还有四个字符）
    select * from employees where first_name like '_a%';（第二个字符为a）

9.7查询一个用户下所有的表

    select table_name from all_tables where owner = 'HR';
    select * from user_tables;

注意：前面格式固定，后面owner的值必须全大写。

9.8描述一个表的信息（describe）

    describe 表名;
    desc 表名;

注意：describe和desc语句只能在命令窗口下才能用。

10.排序（order by，asc升序，desc降序）

    select * from employees order by 排序依据列1 asc/desc,排序依据列2 asc/desc;

注意：
asc可以省略不写，因为默认的为升序。
order by一旦出现就必须在SQL语句末尾。

11.函数

11.1.单行函数（内置函数）

--oracle中的虚表，哑表dual
--mod(m,n)，相当于Java中m%n

    select mod(5,2) from dual
    select mod(salary,2000) from employees;

--length(字符串)，获取字符串的长度

    select length('aaaa') from dual;
    select length(first_name) from employees;

--sysdate获取当前系统时间

    select sysdate from dual;

--to_char(日期数据,'日期格式')将日期转换成字符串

    select to_char(sysdate,'yyyy-mm-dd-hh-mi-ss-day') from dual;
    select to_char(hire_date,'yyyy') from employees;

--to_date('字符串日期','日期格式')将字符串转换成日期

    select to_date('2019-5-23-8-8-8-星期四','yyyy-mm-dd-hh-mi-ss-day') from dual;**

11.2.组函数

    --取最大值：max()
    select max(salary) from employees;
    --取最小值：min()
    select min(salary) from employees;
    --取平均值：avg()
    select avg(salary) from employees;
    --取数据的和：sum()
    select sum(salary) from employees;
    --取条数：count()
    select count(*) from employees;

注意：通常可以使用count(*)统计表中的行数。

12.分组（group by，having）

注意：分组之后select后只能跟分组依据或者组函数。

    select department_id,count(*) from employees group by
    department_id;

having子句：

    select salary 
    from employees 
    where salary >= 8000 
    group by salary 
    having salary >= 9000 
    order by salary asc;

注意：
having语句必须在group by语句之后，作用：在分组之后进行判断。
何时用where，何时用having？
没有分组只能用where；
涉及到应用组函数的条件判断，必须使用having；
当都能使用时，where的效率比having高。

定义顺序	执行顺序
select	5
from	1
where	2
group by	3
having	4
order by	6

13.伪列（Oracle独有）

不需要程序员手动创建，由Oracle自动创建。
伪列的值若不主动查询，不会显示。

13.1.rowid

行数据：数据在磁盘中物理地址的十六进制形式，在数据库中的唯一标示，代表行数据所对应的物理存储空间。

    select e.*,rowid from employees e;

13.2.rownum

对本次查询结果，符合要求的数据，从1开始进行标号

    select e.*,rownum from employees e;

rownum的赋值时机，在条件判断过程中尝试为当前行数据编号，满足条件赋值成功，失败则为下一行继续连续赋值。因此rownum参与分页操作时，不能直接用 >，>=，=进行条件判断否则会有错误。

分页操作：

    select * from (select e.*,rownum r from employees e) where r >= 10 and r<=20;

二、多表查询

1.子查询

当一个查询SQL需要使用另外一个查询SQL的结果时，可以在一个SQL中嵌套另外一个SQL。

1.1.where单值子查询：被嵌套的SQL返回的结果是一行一列的数据

查询employee_id=100的员工所在部门的信息：

    select * from departments where department_id = (select
    department_id from employees where employee_id = 100);

注意：可以基于rowid进行查询，查询效率最快。但要事先知道十六进制地址，所以没用。

1.2.where多值子查询：被嵌套的SQL返回的结果是多行一列的数据

查询last_name为King所在部门的部门信息：

    select * from departments where department_id in (select
    department_id from employees where last_name = 'King');

1.3.from子查询：被嵌套的SQL返回的结果是多行多列的数据，此时，需要将被嵌套的SQL返回的结果当做一个新表对待。

查询员工信息，先按照薪资降序排列，后编号：

    select e.*,rownum from (select * from employees order by
    salary desc)e;

2.表连接

当需要从多张表中获取数据时，需要将多张表连接起来。

2.1.内连接

    语法：
    select 列
    from 表1 inner join 表2
    on 连接条件;
    select e.*,d.*
    from employees e inner join departments d
    on e.department_id = d.department_id;

缺点：inner join只会连接两张表可以连接的数据，不能连接的数据就查询丢了。

2.2.左外连接

    语法：
    select 列
    from 左表 left outer join 右表 
    on 连接条件;
    select e.*,d.*
    from employees e left outer join departments d
    on e.department_id = d.department_id;

特点：左边的主表全部查询出来，右边的辅表不能连接的补null。

2.3.右外连接

    语法：
    select 列
    from 左表 right outer join 右表
    on 连接条件;
    select e.*,d.*
    from employees e right outer join departments d
    on e.department_id = d.department_id;

特点：右边的辅表全部查询出来，左边的主表不能连接的补null。

2.4.全外连接

    语法：
    select 列
    from 表1  full outer join 表2
    on 连接条件;
    select e.*,d.*
    from employees e full outer join departments d
    on e.department_id = d.department_id;

2.5.多表连接

    语法：
    select 列
    from 表1 left outer join 表2
    on 连接条件
    left outer join 表3
    on 连接条件
    ……
    select e.*,d.*,l.*
    from employees e left outer join departments d
    on e.department_id = d.department_id
    left outer join locations l
    on d.location_id = l.location_id;

2.6.自连接
自己连接自己，特殊多表连接。

    select e.*,m.*
    from employees e left outer join employees m
    on e.manager_id = m.manager_id;

三、Oracle中的其它对象

1.序列（Sequence）（Oracle独有）

Oracle提供的一个工具，用于生成自增不重复的值。

1.1.创建序列

    create sequence 序列名 [start with 起始值 increment by 步长];

1.2.使用序列

    序列名.currval    --当前序列值
    序列名.nextval    --序列自增

1.3.删除序列

    drop sequence 序列名;

2.视图（View）

给一个复杂的查询SQL起一个别名，把查询的sql语句，命名，存储在数据库中便于后续的重复使用。

2.1.创建视图

    Create view 视图名 as select语句;

注意：创建视图需要数据库授予DBA权限

grant dba to user名;
--补充其它的权限名有
grant connect,resource to user名;
--回收权限
revoke 权限名 from user名;

2.2.使用视图

将视图看作一张表使用

2.3.删除视图

    drop view 视图名;

视图的特点：
1.视图代表的是一个查询SQL。隐藏重要字段名，保护真实表，保护数据。
2.视图使用时像表，但不是表，不保存数据，根据视图查询时，最终解析为根据其代表的查询SQL再次进行查询。
3.不会提高查询效率，只是简化查询，不建议使用。

3.索引（Index）

作用：提高查询的效率

3.1.创建索引

    create index 索引名 on 表名(列名); 

3.2.使用索引

根据建立索引的列查询时会自动使用索引

3.3.删除索引

    drop index 索引名;

注意：索引不是越多越好：
A.索引本身会占据空间
B.索引会降低增删改的效率
C.使用like或者null查询时不走索引
D.主键和唯一列自动添加索引

四、SQL语句体系

1. DDL（Data Definition Language）数据定义语言

用于描述数据库中要存储的现实世界实体的语言。

数据定义语言DDL用来创建数据库中的各种对象：表、视图、索引、同义词、聚簇等。

DDL操作是隐性提交的！不能rollback。

1.1.创建表：

    create table 表名(
          列名1   数据类型   约束，
          列名2   数据类型   约束，
          列名3   数据类型   约束
    );

约束：
primary key（主键）一设置就默认非空和唯一
not null（不能为空）
unique（唯一）
check(列值的约束条件，类比where语句)（检查约束）
references 表名(列名)（外键约束，引用关联表的列，可以为空，可以重复，而且该列必须是另外一个表的主键）

1.2.删除表：

   drop table 表名;

注意：
如果删除某一张表时，有别的表与其建立了外键关系，则应该先删除子表，再删除父表。
可以进行级联删除 在删除表的同时，把约束（外键）同时删除：

    drop table 表名
    cascade constraints;

1.3.修改表：

修改列的类型：

    alter table 表名 modify 列名 类型;

前提条件：列没有存储数据。

修改列的名字：

    alter table 表名 rename column 老列名 to 新列名;

追加新列：

    alter table 表名 add 列名 类型;

2.DQL(Data Query Language) 数据查询语言

见上面

3. DML(Data Modify Language) 数据的修改语言

注意：DML语句必须执行commit操作才可以同步数据库。

插入数据：

    insert into 表名 (字段1,字段2,字段3) values (值1,值2,值3);

修改（更新）数据：

    update表名set 字段1 = 值1,字段2= 值2, …… where 语句;

注意：主键不能修改。

删除数据：

    delete from 表名;（删除全表）
    delete from 表名 where 条件;（选择删除）

4. DCL（Data Control Language）数据控制语言

用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果。

grant：授权；
revoke：取消权限；

5. TCL(Transaction Control Language) 事务控制语言

事务的控制：

数据库提供的一种机制，用于保障业务操作的完整性。

事务：保证业务操作完整性的一种数据库机制，保障一个业务操作包含的多条SQL要么同时成功，要么同时失败。

commit：保存已完成事务动作结果
rollback：回滚，恢复事务相关数据至上一次commit操作之后

事务的特点：
1.原子性（Atomic）：事务包含的多条SQL语句，要么同时成功，要么同时失败。
2.一致性（Consistency）：事务结束前后，数据库中数据状态要正确。
3.隔离性（Isolation）：保证多用户并发访问的数据安全。
4.持久性（Durability）：事务操作的数据，持久化到数据库中。