Oracle学习总结大纲

Oracle学习总结

---

基本架构

概述

一个Oracle数据库

1. 是一个数据管理系统(RDBMS)
2. 由一个Oracle数据库和一个或多个Oracle实例提供

这里的Oracle数据库是物理上的概念，指的是实际的数据库文件，当Oracle实例为多个时该数据库为Oracle集群数据库

两阶段提交

Oracle会在用户(PGA)操作后将数据提交至SGA，再通过SGA将多个用户的操作一次性提交至数据库。

PGA(Process Global Area)

PGA为每个来访用户创建进程并保留了一定的内存空间。

SGA(System Global Area是Oracle Instance)

SGA是Oracle实例的基本组成部分主要由共享池、数据缓冲区、日志缓冲区构成。

SQL

SQL简介

DML(data manipulation language)

数据库操纵语言，关键字包括UPDATE、DELETE、INSERT分别用于对表中的数据更新、删除和插入数据。

DQL(Data Query Language)

数据库查询语言，关键字仅有SELECT，用于查询表中的数据。

DDL(data definition language)

数据库模式定义语言，关键字包括CREATE，TRUNCATE ，DROP，ALTER，用于对数据库进行操作。

DCL(Data Control Language)

数据库控制语言，关键字为GRANT,REVOKE用来设置或更改数据库用户或角色权限的语句。

TCL(Transaction Control Language)

事务控制语言关键字为COMMIT、ROLLBACK等，分别用于提交和回滚事务。

SELECT

单表查询

仅涉及一张表的查询为单表查询，其中可以配合多种条件或者函数进行查询

查询EMP表的所有员工信息

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

已选择14行。

查询每个部门由多少个员工,并按照部门号排序

SQL> select deptno,count(*) from emp group by deptno order by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        20          5
        30          6

查询名字中含有字母’A’的员工

SQL> select * from emp where ename like '%A%';

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30

已选择7行。

case表达式

SQL CASE 表达式是一种通用的条件表达式，类似于其它语言中的 if/else 语句。

给员工薪资划分等级

SQL> select empno,ename,sal,case when sal <=1000 then 'p1'
  2                                  when sal >1000 and sal <2000 then 'p2'
  3                                  when sal >=2000 then 'p3'
  4                                  else 'p0' end
  5  from emp;

     EMPNO ENAME             SAL CA
---------- ---------- ---------- --
      7369 SMITH             800 p1
      7499 ALLEN            1600 p2
      7521 WARD             1250 p2
      7566 JONES            2975 p3
      7654 MARTIN           1250 p2
      7698 BLAKE            2850 p3
      7782 CLARK            2450 p3
      7788 SCOTT            3000 p3
      7839 KING             5000 p3
      7844 TURNER           1500 p2
      7876 ADAMS            1100 p2
      7900 JAMES             950 p1
      7902 FORD             3000 p3
      7934 MILLER           1300 p2

已选择14行。

Oracle分页查询

可以使用伪劣(rownum)进行分页的查询，每一张表都会有一个属于该表的rownum如果想进行排序分页查询，可以对排序后的表在进行一次嵌套查询，并且如过使用rownum关键字判断的话不能对rownum使用>(大于符号)

查询按薪资降序排序后的5-10号员工信息

首先可以看到rownum已经被打乱了，因为这里的rownum实际上是属于emp表的而不是查询出来的表的
SQL> select rownum,empno,ename,sal from emp order by sal desc;

    ROWNUM      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
         9       7839 KING             5000
        13       7902 FORD             3000
         8       7788 SCOTT            3000
         4       7566 JONES            2975
         6       7698 BLAKE            2850
         7       7782 CLARK            2450
         2       7499 ALLEN            1600
        10       7844 TURNER           1