当前位置：Gxlcms > 数据库问题 > oracle 数据库

oracle 数据库

时间：2021-07-01 10:21:17 帮助过：22人阅读

[用户名]/[密码][@数据库] [参数] sqlplus sys/orcl as sysdba -- 登录 sys 用户，必须指定 sysdba 或 sysoper 身份 sqlplus system/orcl -- 数据库管理员

1.2 创建一个自己的用户（比如 vip/vip）

  1. <span style="color: #0000ff">create</span> <span style="color: #ff00ff">user</span> vip identified <span style="color: #0000ff">by</span> vip;     <span style="color: #008080">--</span><span style="color: #008080"> 注意，新创建的用户，什么权限都没有，需要授权后才能使用</span>
  2. <span style="color: #0000ff">grant</span> <span style="color: #0000ff">create</span> session <span style="color: #0000ff">to</span> vip;           <span style="color: #008080">--</span><span style="color: #008080"> 授予登录的权限</span>
  3. <span style="color: #0000ff">grant</span> connect <span style="color: #0000ff">to</span> vip;                  <span style="color: #008080">--</span><span style="color: #008080"> 角色是很多权限的打包，connect 是一种角色，它包含了连接查看数据的一些基本权限</span>
  4. <span style="color: #0000ff">grant</span> dba <span style="color: #0000ff">to</span> vip;                      <span style="color: #008080">--</span><span style="color: #008080"> dba 是绝大多数权限的集合，它基本能做所有事情，所以很少单独授予用户。但在测试环境中，这样，很爽。</span>
  5. <span style="color: #008080">--</span><span style="color: #008080"> 上面的创建用户、授予权限两步操作，可以简化为下面一步：</span>
  6. <span style="color: #0000ff">grant</span> dba <span style="color: #0000ff">to</span> vip identified <span style="color: #0000ff">by</span> vip;    <span style="color: #008080">--</span><span style="color: #008080"> 注意，使用分号结尾</span>

1.3 切换到用户

  1. sqlplus vip<span style="color: #808080">/</span>vip  <span style="color: #008080">--</span><span style="color: #008080"> 在 cmd 下</span>
  2. conn vip<span style="color: #808080">/</span>vip     <span style="color: #008080">--</span><span style="color: #008080"> 在 sqlplus 中</span>

1.4 使用

  1. <span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span> aaa (id <span style="color: #0000ff">int</span>);

1.5 激活内置的测试账号，这里面有几张示例库，可以用它们练习下查询

  1. <span style="color: #0000ff">alter</span> <span style="color: #ff00ff">user</span><span style="color: #000000"> scott account unlock;
  2. conn scott</span><span style="color: #808080">/</span>tiger

1.6 修改密码

  1. <span style="color: #0000ff">alter</span> <span style="color: #ff00ff">user</span> scott identified <span style="color: #0000ff">by</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">newpassword</span><span style="color: #ff0000">]</span>;

2.题库

2.1 题库一

  • 在芝加哥工作的人中，谁的工资最高
  • 查询每个部门下有多少员工
  • 查询除去 salesman 所有平均工资超过 1500 的部门
  • 查询在 new york 工作的所有员工的姓名，部门名称和工资信息
  • 查询姓名为 King 的员工的编号，名称跟部门
  • 查询各种工作的最低工资
  • 查询工龄大于10年的所有员工信息
  • 查询每个部门员工数量，平均工资和平均工作年限
  • 统计各部门每个工种的人数，平均工资。
  • 查询从事同一种工作但不属于同一部门的员工信息。
  • 查询所有员工工资都大于1000的部门的信息及员工信息
  • 查询入职日期早于其直接上级的所有员工信息。
  • 列出雇员中（除去mgr为空的人)工资第二高的人。
  • 列出1981年来公司所有员工的总收入（包括sal和comm）

2.2 题库二

  • 查询工资为 2500 到 4000 的人的数量（用不同方式查询） 
    1. <span style="color: #0000ff">select</span> <span style="color: #ff00ff">count</span>(<span style="color: #808080">*</span>) <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">where</span> sal <span style="color: #808080">>=</span> <span style="color: #800000; font-weight: bold">2500</span> <span style="color: #808080">and</span> sal <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">4000</span><span style="color: #000000">;
    2. </span><span style="color: #008080">--</span><span style="color: #008080"> vs.</span>
    3. <span style="color: #0000ff">select</span> <span style="color: #ff00ff">count</span>(<span style="color: #808080">*</span>) <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">where</span> sal <span style="color: #808080">between</span> <span style="color: #800000; font-weight: bold">2500</span> <span style="color: #808080">and</span> <span style="color: #800000; font-weight: bold">4000</span>;
  • 查询部门编号为 10 和 30 的所有人（用不同方式查询）
  • 查询部门编号为 10 和 30 中名字中不含有 ‘C’ 的所有人
  • 查询部门编号为 10 和 30 中名字首字母之外不含有 ‘C’ 的所有人
  • 查询部门编号为 10 和 30 中所有的经理以及名字首字母之外不含有 ‘C’ 的所有人
  • 查询纽约和芝加哥地区所有的经理以及名字首字母之外不含有 ‘C’ 的所有人
  • 查询纽约和芝加哥地区所有的经理以及顶头上司名字的首字母之外不含有 ‘C’ 的所有人
  • 查询每个人的工资等级 
    1. <span style="color: #008080">--</span><span style="color: #008080"> 初始化数据</span>
    2. <span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span> salgrade <span style="color: #0000ff">as</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> scott.salgrade;
    3. </span><span style="color: #008080">--</span><span style="color: #008080"> 分别查看数据</span>
    4. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> emp;
    5. </span><span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> salgrade;
    6. </span><span style="color: #008080">--</span><span style="color: #008080"> 杂交</span>
    7. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> emp, salgrade;
    8. </span><span style="color: #008080">--</span><span style="color: #008080"> 过滤掉不合适的</span>
    9. <span style="color: #0000ff">select</span> ename, grade <span style="color: #0000ff">from</span> emp e, salgrade s <span style="color: #0000ff">where</span> e.sal <span style="color: #808080">between</span> s.losal <span style="color: #808080">and</span> s.hisal;
  • 查询每个部门的平均工资的等级 
    1. <span style="color: #008080">--</span><span style="color: #008080"> 分析题目</span><span style="color: #008080">
    2. --</span><span style="color: #008080"> 1. 需要先查询出每个部门的平均工资</span><span style="color: #008080">
    3. --</span><span style="color: #008080"> 2. 根据 salgrade 表中的数据，获取每个部门平均工资的等级</span>
    4. <span style="color: #008080">--</span><span style="color: #008080"> 这是所有的人</span>
    5. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> emp;
    6. </span><span style="color: #008080">--</span><span style="color: #008080"> 按照部门分组</span>
    7. <span style="color: #0000ff">select</span> deptno, <span style="color: #ff00ff">avg</span>(sal) <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span><span style="color: #000000"> deptno;
    8. </span><span style="color: #008080">--</span><span style="color: #008080"> 结果：</span><span style="color: #008080">
    9. --</span><span style="color: #008080"> | DEPTNO |   AVG(SAL)    |</span><span style="color: #008080">
    10. --</span><span style="color: #008080"> |     30  | 1566.66667  |</span><span style="color: #008080">
    11. --</span><span style="color: #008080"> |     20 |  2258.33333  |</span><span style="color: #008080">
    12. --</span><span style="color: #008080"> |     10 |  2916.66667  |</span>
    13. <span style="color: #008080">--</span><span style="color: #008080"> 跟 salgrade 表，杂交，总共 15 条结果</span>
    14. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000">
    15.   (</span><span style="color: #0000ff">select</span> deptno, <span style="color: #ff00ff">avg</span>(sal) sal <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span><span style="color: #000000"> deptno) t,
    16.   salgrade s;
    17. </span><span style="color: #008080">--</span><span style="color: #008080"> 过滤掉工资范围不合适的数据</span>
    18. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000">
    19.   (</span><span style="color: #0000ff">select</span> deptno, <span style="color: #ff00ff">avg</span>(sal) sal <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span><span style="color: #000000"> deptno) t,
    20.   salgrade s
    21. </span><span style="color: #0000ff">where</span><span style="color: #000000">
    22.   t.sal </span><span style="color: #808080">between</span> s.losal <span style="color: #808080">and</span><span style="color: #000000"> s.hisal;
    23. </span><span style="color: #008080">--</span><span style="color: #008080"> 结果就是这样，就对了</span><span style="color: #008080">
    24. --</span><span style="color: #008080"> | DEPTNO |  SAL        |  GRADE |  LOSAL |  HISAL  |</span><span style="color: #008080">
    25. --</span><span style="color: #008080"> |     10 |  2916.66667 |      4 |   2001 |   3000  |</span><span style="color: #008080">
    26. --</span><span style="color: #008080"> |     20 |  2258.33333 |      4 |   2001 |   3000  |</span><span style="color: #008080">
    27. --</span><span style="color: #008080"> |     30 |  1566.66667 |      3 |   1401 |   2000  |</span>
    28. <span style="color: #008080">--</span><span style="color: #008080"> 需要将部门名称显示出来，再杂交->过滤一次就可以了</span>
    29. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000">
    30.   (</span><span style="color: #0000ff">select</span> deptno, <span style="color: #ff00ff">avg</span>(sal) sal <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span><span style="color: #000000"> deptno) t,
    31.   salgrade s,
    32.   dept d
    33. </span><span style="color: #0000ff">where</span><span style="color: #000000">
    34.   t.deptno </span><span style="color: #808080">=</span> d.deptno <span style="color: #808080">and</span><span style="color: #000000">
    35.   t.sal </span><span style="color: #808080">between</span> s.losal <span style="color: #808080">and</span><span style="color: #000000"> s.hisal;
    36. </span><span style="color: #008080">--</span><span style="color: #008080"> 最后的最后，设置显示字段</span>
    37. <span style="color: #0000ff">select</span> dname, grade <span style="color: #0000ff">from</span><span style="color: #000000">
    38.   (</span><span style="color: #0000ff">select</span> deptno, <span style="color: #ff00ff">avg</span>(sal) sal <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span><span style="color: #000000"> deptno) t,
    39.   salgrade s,
    40.   dept d
    41. </span><span style="color: #0000ff">where</span><span style="color: #000000">
    42.   t.deptno </span><span style="color: #808080">=</span> d.deptno <span style="color: #808080">and</span><span style="color: #000000">
    43.   t.sal </span><span style="color: #808080">between</span> s.losal <span style="color: #808080">and</span><span style="color: #000000"> s.hisal;
    44. </span><span style="color: #008080">--</span><span style="color: #008080"> 最终结果，完美</span><span style="color: #008080">
    45. --</span><span style="color: #008080">| DNAME      |  GRADE  |</span><span style="color: #008080">
    46. --</span><span style="color: #008080">| ACCOUNTING |      4  |</span><span style="color: #008080">
    47. --</span><span style="color: #008080">| RESEARCH   |      4  |</span><span style="color: #008080">
    48. --</span><span style="color: #008080">| SALES      |      3  |</span>

2.3 题库三

  • 查询每个组最高工资的那些人
  • 有下面一个表，写一条 sql 语句计算男女之差
    gendernumber
    男 46
    女 10
  • 给 emp 中的人加工资，请写出相关语句:
    条件加多少
    1000元以下 50%
    2000元以下 30%
    3000元以下 20%
    其他 5%
  • 给 emp 中的人加工资，如上。但 1981/5/1 之后来的所有人，只加 2%, 请写出语句。
  • 计算你们从入学到现在过了多少个周末
  • 计算你们从现在到毕业还有多少天，还有多少个周末
  • 计算你们在学校的时间内，每天花费多少钱

查询各种工作中的最低工资

   看到最低、最大、平均之类的题目，首先想到的是分组函数的使用。

   也就是 group by, having。

  1. <span style="color: #0000ff">select</span> job, <span style="color: #ff00ff">min</span>(sal) <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> job;

在芝加哥工作的人中，谁的工资最高

[题目] 从 scott 用户的 emp/dept 表中，找到 “来自芝加哥最有钱的那个人” 。

首先，我们需要理清思路。

这里总共有两个条件：

  1. 这个人来自芝加哥
  2. 这个人是最有钱的，而且是芝加哥最有钱的

我们可以看出，第二个条件是依赖第一个条件的。

所以，分两步查询：

  1. 找出所有来自芝加哥的人
  2. 从这些人中，找到最有钱的那个。这一步，可以通过 max 函数或者 order by 方式实现。

下面是语句示例：

  1. <span style="color: #008080">--</span><span style="color: #008080">-- 第一步：找到来自芝加哥的所有人。下面两种写法等价：</span>
  2. <span style="color: #0000ff">select</span> e.<span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> emp e
  3.   </span><span style="color: #808080">join</span> dept d <span style="color: #0000ff">on</span> (e.deptno<span style="color: #808080">=</span><span style="color: #000000">d.deptno)
  4.   </span><span style="color: #0000ff">where</span> d.loc<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">CHICAGO</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
  5. </span><span style="color: #0000ff">select</span> e.<span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> emp e, dept d
  6.   </span><span style="color: #0000ff">where</span> d.deptno <span style="color: #808080">=</span><span style="color: #000000"> e.deptno
  7.         </span><span style="color: #808080">and</span> d.loc<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">CHICAGO</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
  8. </span><span style="color: #008080">--</span><span style="color: #008080">-- 第二步，基于上面结果，筛选出最有钱的那个</span>
  9. <span style="color: #008080">--</span><span style="color: #008080"> 可以通过 max 函数</span>
  10. <span style="color: #0000ff">select</span> e.<span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> emp e, dept d
  11.   </span><span style="color: #0000ff">where</span> e.deptno <span style="color: #808080">=</span><span style="color: #000000"> d.deptno
  12.         </span><span style="color: #808080">and</span> d.loc<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">CHICAGO</span><span style="color: #ff0000">‘</span>
  13.         <span style="color: #808080">and</span> sal <span style="color: #808080">=</span><span style="color: #000000"> 
  14.             (</span><span style="color: #0000ff">select</span> <span style="color: #ff00ff">max</span>(sal) <span style="color: #0000ff">from</span><span style="color: #000000"> emp e, dept d
  15.               </span><span style="color: #0000ff">where</span> e.deptno <span style="color: #808080">=</span><span style="color: #000000"> d.deptno
  16.                     </span><span style="color: #808080">and</span> d.loc<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">CHICAGO</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  17. </span><span style="color: #008080">--</span><span style="color: #008080"> 可以通过 order by 方式</span>
  18. <span style="color: #0000ff">select</span> ename <span style="color: #0000ff">from</span><span style="color: #000000">
  19.   (</span><span style="color: #0000ff">select</span> e.<span style="color: #808080">*</span>, d.<span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> emp e, dept d
  20.     </span><span style="color: #0000ff">where</span> e.deptno <span style="color: #808080">=</span><span style="color: #000000"> d.deptno
  21.           </span><span style="color: #808080">and</span> d.loc<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">CHICAGO</span><span style="color: #ff0000">‘</span>
  22.     <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sal <span style="color: #0000ff">desc</span><span style="color: #000000">)
  23. </span><span style="color: #0000ff">where</span> rownum <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span>;

注意，实现的方式，不止上面的那些。但总体 思路 是一样的。

所以，思路永远是最重要的。

查询所有员工工资都大于1000的部门的信息及员工信息

最核心的问题： 查询出符合条件的部门编号。

第一种思路

　1.查询出所有的部门编号

  1. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> emp;

　2.查询出所有工资少于 1000 的人， 我们要把它所在的部门，从上面的结果中排除掉。

  1. <span style="color: #0000ff">select</span> deptno <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">where</span> sal <span style="color: #808080"><</span> <span style="color: #800000; font-weight: bold">1000</span>;

　3.将上面查询出的不符合条件的部门排除掉

  1. <span style="color: #0000ff">select</span> <span style="color: #0000ff">distinct</span> deptno <span style="color: #0000ff">from</span><span style="color: #000000"> emp
  2.  </span><span style="color: #0000ff">where</span> deptno <span style="color: #808080">not</span> <span style="color: #808080">in</span><span style="color: #000000"> 
  3.   (</span><span style="color: #0000ff">select</span> deptno <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">where</span> sal <span style="color: #808080"><</span> <span style="color: #800000; font-weight: bold">1000</span>);

　4.修改上面语句，增加最终的条件，查询所有的其他信息

  1. <span style="color: #0000ff">select</span> d.dname, e.<span style="color: #808080">*</span> <span style="color: #0000ff">from</span> emp e <span style="color: #808080">join</span> dept d <span style="color: #0000ff">on</span>(e.deptno <span style="color: #808080">=</span><span style="color: #000000"> d.deptno)
  2.   </span><span style="color: #0000ff">where</span> e.deptno <span style="color: #808080">not</span> <span style="color: #808080">in</span><span style="color: #000000">
  3.     (</span><span style="color: #0000ff">select</span> deptno <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">where</span> sal <span style="color: #808080"><</span> <span style="color: #800000; font-weight: bold">1000</span>)

第二种思路

使用分组函数（group by / having）

分组函数主要用来统计分析。

一个完整的查询语句如下，其中 group by 和 having 是用来分组和筛选分组。

  1. <span style="color: #0000ff">select</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">字段</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">from</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">表名</span><span style="color: #ff0000">]</span>
  2.   <span style="color: #0000ff">where</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">条件</span><span style="color: #ff0000">]</span>
  3.   <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">分组字段</span><span style="color: #ff0000">]</span>
  4.   <span style="color: #0000ff">having</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">对分组结果进行筛选</span><span style="color: #ff0000">]</span>
  5.   <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">字段</span><span style="color: #ff0000">]</span>

示例：

  1. <span style="color: #0000ff">select</span> deptno,     <span style="color: #008080">--</span><span style="color: #008080"> 分组字段</span>
  2.        <span style="color: #ff00ff">count</span>(<span style="color: #808080">*</span>),   <span style="color: #008080">--</span><span style="color: #008080"> 人数</span>
  3.        <span style="color: #ff00ff">sum</span>(sal),   <span style="color: #008080">--</span><span style="color: #008080"> 工资总和</span>
  4.        <span style="color: #ff00ff">avg</span>(sal),   <span style="color: #008080">--</span><span style="color: #008080"> 平均工资</span>
  5.        <span style="color: #ff00ff">max</span>(sal),   <span style="color: #008080">--</span><span style="color: #008080"> 最高工资</span>
  6.        <span style="color: #ff00ff">min</span>(sal)    <span style="color: #008080">--</span><span style="color: #008080"> 最低工资</span>
  7.   <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> deptno         <span style="color: #008080">--</span><span style="color: #008080"> 按照部门分组，进行统计</span>
  8.            <span style="color: #0000ff">having</span> <span style="color: #ff00ff">avg</span>(sal) <span style="color: #808080">></span> <span style="color: #800000; font-weight: bold">2000</span>; <span style="color: #008080">--</span><span style="color: #008080"> 只显示平均工资大于 2000 的分组</span>

那我们的题目的解决思路就是：

1.按照部门分组

  1. <span style="color: #0000ff">select</span> deptno <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> deptno;

2.筛选，排除最低工资小于 1000 的部门。 即：得到符合条件的部门的编号。

  1. <span style="color: #0000ff">select</span> deptno <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> deptno <span style="color: #0000ff">having</span> <span style="color: #ff00ff">min</span>(sal) <span style="color: #808080">></span> <span style="color: #800000; font-weight: bold">1000</span>;

3.完善最终语句，得到最终结果。

  1. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> emp e, dept d
  2.   </span><span style="color: #0000ff">where</span> e.deptno <span style="color: #808080">=</span><span style="color: #000000"> d.deptno
  3.         </span><span style="color: #808080">and</span> e.deptno <span style="color: #808080">in</span> (<span style="color: #0000ff">select</span> deptno <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> deptno <span style="color: #0000ff">having</span> <span style="color: #ff00ff">min</span>(sal) <span style="color: #808080">></span> <span style="color: #800000; font-weight: bold">1000</span>);

查询当月总共有多少个周五

首先，第一步，得到本月所有日期的结果集，两种方式:

  1. 使用已有表的 rownum 构建
  2. 使用 oracle 的 connect by level 语句（结构化查询）

得到有 n 条记录的虚表：

  1. <span style="color: #0000ff">select</span> rownum <span style="color: #0000ff">from</span> dba_objects <span style="color: #0000ff">where</span> rownum <span style="color: #808080"><</span> <span style="color: #800000; font-weight: bold">32</span><span style="color: #000000">;
  2. </span><span style="color: #0000ff">select</span> <span style="color: #0000ff">level</span> <span style="color: #0000ff">from</span> dual connect <span style="color: #0000ff">by</span> <span style="color: #0000ff">level</span> <span style="color: #808080"><</span> <span style="color: #800000; font-weight: bold">32</span>;

将虚表转化为我们需要的日期表：

  1. <span style="color: #0000ff">select</span> trunc(sysdate, <span style="color: #ff0000">‘</span><span style="color: #ff0000">MON</span><span style="color: #ff0000">‘</span>) <span style="color: #808080">+</span> rownum <span style="color: #808080">-</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">from</span> dba_objects <span style="color: #0000ff">where</span> rownum <span style="color: #808080"><</span> <span style="color: #800000; font-weight: bold">32</span><span style="color: #000000">;
  2. </span><span style="color: #0000ff">select</span> trunc(sysdate, <span style="color: #ff0000">‘</span><span style="color: #ff0000">MON</span><span style="color: #ff0000">‘</span>) <span style="color: #808080">+</span> <span style="color: #0000ff">level</span> <span style="color: #808080">-</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">from</span> dual connect <span style="color: #0000ff">by</span> <span style="color: #0000ff">level</span> <span style="color: #808080"><</span> <span style="color: #800000; font-weight: bold">32</span>;

其次，在上面结果集的基础上进行筛选：

  1. <span style="color: #008080">--</span><span style="color: #008080"> 1.</span>
  2. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span>
  3.   <span style="color: #0000ff">from</span> (<span style="color: #0000ff">select</span> trunc(sysdate, <span style="color: #ff0000">‘</span><span style="color: #ff0000">MON</span><span style="color: #ff0000">‘</span>) <span style="color: #808080">+</span> rownum <span style="color: #808080">-</span> <span style="color: #800000; font-weight: bold">1</span><span style="color: #000000"> d
  4.           </span><span style="color: #0000ff">from</span><span style="color: #000000"> dba_objects
  5.          </span><span style="color: #0000ff">where</span> rownum <span style="color: #808080"><</span> <span style="color: #800000; font-weight: bold">32</span><span style="color: #000000">)
  6.  </span><span style="color: #0000ff">where</span> to_char(d, <span style="color: #ff0000">‘</span><span style="color: #ff0000">day</span><span style="color: #ff0000">‘</span>) <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">星期五</span><span style="color: #ff0000">‘</span>
  7.    <span style="color: #808080">and</span> d <span style="color: #808080"><=</span><span style="color: #000000"> last_day(sysdate);
  8. </span><span style="color: #008080">--</span><span style="color: #008080"> 2.</span>
  9. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span>
  10.   <span style="color: #0000ff">from</span> (<span style="color: #0000ff">select</span> trunc(sysdate, <span style="color: #ff0000">‘</span><span style="color: #ff0000">MON</span><span style="color: #ff0000">‘</span>) <span style="color: #808080">+</span> <span style="color: #0000ff">level</span> <span style="color: #808080">-</span> <span style="color: #800000; font-weight: bold">1</span><span style="color: #000000"> d
  11.           </span><span style="color: #0000ff">from</span><span style="color: #000000"> dual
  12.         connect </span><span style="color: #0000ff">by</span> <span style="color: #0000ff">level</span> <span style="color: #808080"><</span> <span style="color: #800000; font-weight: bold">32</span><span style="color: #000000">)
  13.  </span><span style="color: #0000ff">where</span> to_char(d, <span style="color: #ff0000">‘</span><span style="color: #ff0000">day</span><span style="color: #ff0000">‘</span>) <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">星期五</span><span style="color: #ff0000">‘</span>
  14.    <span style="color: #808080">and</span> d <span style="color: #808080"><=</span> last_day(sysdate);

当然，你也可以将 last_day 这一段放到里面：

  1. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span>
  2.   <span style="color: #0000ff">from</span> (<span style="color: #0000ff">select</span> trunc(sysdate, <span style="color: #ff0000">‘</span><span style="color: #ff0000">MON</span><span style="color: #ff0000">‘</span>) <span style="color: #808080">+</span> <span style="color: #0000ff">level</span> <span style="color: #808080">-</span> <span style="color: #800000; font-weight: bold">1</span><span style="color: #000000"> d
  3.           </span><span style="color: #0000ff">from</span><span style="color: #000000"> dual
  4.         connect </span><span style="color: #0000ff">by</span> <span style="color: #0000ff">level</span> <span style="color: #808080"><=</span> extract(<span style="color: #ff00ff">day</span> <span style="color: #0000ff">from</span><span style="color: #000000"> last_day(sysdate)))
  5.  </span><span style="color: #0000ff">where</span> to_char(d, <span style="color: #ff0000">‘</span><span style="color: #ff0000">d</span><span style="color: #ff0000">‘</span>) <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">6</span>;

3.管理系统

3.1 学生管理系统

根据我们学校的实际情况，请帮助设计一个学生管理系统。

  1. 比如, 学生，老师，班级，课程。按照你自己的设计，酌情增加。

基本步骤：

  1. 先用 e-r 图，将实体的关系表述出来。

    这样的图能帮助我们理清思路，并能帮助团队间的有效交流。

    一定在图画好之后再去着手数据库表的创建。要秉承先设计后实现的思路。

    你可以用一些知名的工具(如 visio)去画，也可以手动在纸上画。

  2. 根据设计好的图，写出相应的建表语句。
  3. 也可以进一步根据实体关系和表，创建相应的 Java 实体类。进一步可以 DAO，进一步可以 Service【可选】
  4. 最后，将图跟语句一起上交。这个过程着重思考下项目从设计到编码是怎么一个过程。 
    1. 需求分析 -> *概要设计* -> 详细实现。

3.2 博客管理系统

首先，设计一个博客表(blog), 至少有下面字段

  • id
  • author
  • title
  • content(要求是 clob 类型)
  • image(要求是 blob 类型)
  • create_time

用 Java 完成基本的 =CRUD=，并掌握使用 PL/SQL 操作 blob/clob 的技巧。

4.体系结构

Oracle 采取的是 Client/Server 架构。

技术分享图片

 客户端(client)操作数据库的请求发送后，服务端的监听器(TNSListener)接收到请求，并将其转发给相应的数据库实例(Instance)，再由实例(Instance)去操纵数据库(Database)。 返回操作结果，是一个相反的过程。下面是个简陋的图示:

技术分享图片

Client

Sqlplus

这是一个轻量级的功能强大的客户端, 是 dba 必须掌握的工具。

用 sqlplus 连接数据库的语法为：

  1. <span style="color: #000000"># Usage:
  2. #   sqlplus 用户名</span><span style="color: #808080">/</span>密码<span style="color: #008000">@主机名</span>:端口号<span style="color: #808080">/</span><span style="color: #000000">实例名
  3. # 参数个数不是固定的
  4. sqlplus                         # 会要求你输入用户名密码，默认连接本地 ORACLE_SID 变量指定的数据库
  5. sqlplus vip                     # 会要求你输入密码
  6. sqlplus vip</span><span style="color: #808080">/</span><span style="color: #000000">vip                 # 连接本地 ORACLE_SID 变量指定的数据库
  7. sqlplus sys</span><span style="color: #808080">/</span>hello <span style="color: #0000ff">as</span><span style="color: #000000"> sysdba     # sys 用户必须用 sysdba 或 sysoper 的身份登录
  8. sqlplus vip</span><span style="color: #008000">@192</span>.<span style="color: #800000; font-weight: bold">168.0</span>.<span style="color: #800000; font-weight: bold">111</span><span style="color: #808080">/</span>orcl  # 连接 <span style="color: #800000; font-weight: bold">192.168</span>.<span style="color: #800000; font-weight: bold">0.111</span><span style="color: #000000"> 机器上的 orcl 数据库，用户名为 vip
  9. sqlplus vip</span><span style="color: #008000">@192db</span>               # 连接 别名 为 192db 的数据库

我们可以配置 sqlplus 的一些行为，两个命令：

  1. show. 用来显示配置参数
  2. set. 用来设置配置参数

比如：

  1. show <span style="color: #808080">all</span>               <span style="color: #008080">--</span><span style="color: #008080"> 显示所有配置参数</span>
  2. show lines             <span style="color: #008080">--</span><span style="color: #008080"> 显示 lines 的配置信息</span>
  3. show errors            <span style="color: #008080">--</span><span style="color: #008080"> 显示错误</span>
  4. <span style="color: #0000ff">set</span> lines<span style="color: #ff0000">[</span><span style="color: #ff0000">ize</span><span style="color: #ff0000">]</span> <span style="color: #800000; font-weight: bold">333</span>     <span style="color: #008080">--</span><span style="color: #008080"> 将行宽设置为 333</span>
  5. <span style="color: #0000ff">set</span> pages<span style="color: #ff0000">[</span><span style="color: #ff0000">ize</span><span style="color: #ff0000">]</span> <span style="color: #800000; font-weight: bold">444</span>     <span style="color: #008080">--</span><span style="color: #008080"> 将每页的记录数设置为 444</span>
  6. <span style="color: #0000ff">set</span> echo <span style="color: #0000ff">off</span><span style="color: #808080">/</span><span style="color: #0000ff">on</span>        <span style="color: #008080">--</span><span style="color: #008080"> 导入外部文件，是否要显示原始 sql 语句</span>
  7. <span style="color: #0000ff">set</span> feedback <span style="color: #0000ff">on</span><span style="color: #808080">/</span><span style="color: #0000ff">off</span>    <span style="color: #008080">--</span><span style="color: #008080"> 是否显示“查询到xx数据”等信息</span>
  8. <span style="color: #0000ff">set</span> timing <span style="color: #0000ff">on</span><span style="color: #808080">/</span><span style="color: #0000ff">off</span>      <span style="color: #008080">--</span><span style="color: #008080"> 是否显示语句的执行时间</span>
  9. <span style="color: #0000ff">set</span> autocommit <span style="color: #0000ff">on</span><span style="color: #808080">/</span><span style="color: #0000ff">off</span>  <span style="color: #008080">--</span><span style="color: #008080"> 是否启用自动提交</span>
  10. <span style="color: #0000ff">set</span> autotrace <span style="color: #0000ff">on</span><span style="color: #808080">/</span><span style="color: #0000ff">off</span>   <span style="color: #008080">--</span><span style="color: #008080"> 是否输出执行计划</span>
  11. <span style="color: #0000ff">set</span> serveroutput <span style="color: #0000ff">on</span><span style="color: #808080">/</span><span style="color: #0000ff">off</span><span style="color: #008080">--</span><span style="color: #008080"> 是否显示来自服务端的信息</span>
  12. <span style="color: #0000ff">column</span> aaa format a22  <span style="color: #008080">--</span><span style="color: #008080"> 将列 ‘aaa‘ 的宽度限制为 22 个字幕‘a‘的大小。column 命令很强大，语句也复杂，此处不提。</span>

在 sqlplus 中有缓冲区的概念:

  1. 缓冲区是用来记录上一次执行的命令语句的空间。

我们可以通过一些列简单命令，对上一次输入的语句进行一些控制:

  • 增 append/insert
  • 删 delete
  • 改 change
  • 查 list
  • 执行修改后的语句 run 或者 /

例子：

  1. list         <span style="color: #008080">--</span><span style="color: #008080"> 显示完整的缓存区</span>
  2. list <span style="color: #800000; font-weight: bold">3</span>       <span style="color: #008080">--</span><span style="color: #008080"> 显示并定位到第三行</span>
  3. list <span style="color: #800000; font-weight: bold">3</span> <span style="color: #800000; font-weight: bold">5</span>     <span style="color: #008080">--</span><span style="color: #008080"> 显示第三行到第五行的内容</span>
  4. list last    <span style="color: #008080">--</span><span style="color: #008080"> 定位到最后一行</span>
  1. list <span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">
  2. del               </span><span style="color: #008080">--</span><span style="color: #008080"> 删除第三行</span>
  1. list <span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">
  2. append  </span><span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sal  <span style="color: #008080">--</span><span style="color: #008080"> 定位到第三行，然后追加 order by sal</span>
  3. <span style="color: #0000ff">insert</span> <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sal   <span style="color: #008080">--</span><span style="color: #008080"> 开启新的一行，插入 order by sal</span>

还有其他一些命令：

  1. get D:\aaa.sql        <span style="color: #008080">--</span><span style="color: #008080"> 将文件加载到缓冲区，但不执行</span>
  2. start D:\aaa.sql      <span style="color: #008080">--</span><span style="color: #008080"> 将文件加载到缓冲区，并且执行</span>
  3. <span style="color: #008000">@D</span>:\aaa.sql           <span style="color: #008080">--</span><span style="color: #008080"> 是上面一条语句的简写形式</span>
  4. <span style="color: #0000ff">save</span> D:\bbb.sql       <span style="color: #008080">--</span><span style="color: #008080"> 将缓冲区的内容保存到文件中</span>
  5. edit                  <span style="color: #008080">--</span><span style="color: #008080"> 调用外部编辑器，编辑缓冲区</span>
  6. clear screen          <span style="color: #008080">--</span><span style="color: #008080"> 清空缓冲区</span>
  1. show <span style="color: #ff00ff">user</span>             <span style="color: #008080">--</span><span style="color: #008080"> 显示当前用户</span>
  2. show parameters       <span style="color: #008080">--</span><span style="color: #008080"> 显示 oracle 的配置参数</span>
  3. show parameters nls   <span style="color: #008080">--</span><span style="color: #008080"> 显示 oracle 中所有跟语言配置相关的一些参数</span>
  4. describe emp          <span style="color: #008080">--</span><span style="color: #008080"> 显示 emp 表的结构信息</span>

JDBC

用 Java 连接数据库，需要用到 jdbc 驱动，它们可以在下面目录中找到：

  1. 主目录\product\12.1.0\dbhome_1\jdbc\lib\*.jar

比如 ojdbc7_g.jar, 7 表示适用于 JDK 版本 1.7, g 表示自带更多调试信息。

TNSListener

TNSListener，是用来监听来自客户端的请求，并将其转发给相对应的服务端实例的一种后台服务。

它是沟通客户端与服务端的一个桥梁。

比如，下面用 sqlplus 客户端将会连接 localhost 上的 orcl 数据库:

  1. sqlplus vip<span style="color: #808080">/</span>vip<span style="color: #008000">@localhost</span><span style="color: #808080">/</span>orcl

请求会发送到 localhost 主机的 1521 号端口， 作为监听的 TNSListener 收到这个请求后，再把请求转发给对应的 orcl 数据库实例。

所以必须开启监听服务，并且配置正确，才能连接操作数据库。

  1. 注：如果用 sqlplus vip/vip 的方式连接数据库，即没有指定连接的机器，那么默认连接的是本机数据库
  2. 这种连接是不需要监听服务的，因为为了增加连接速度，这样的本地连接 oracle 会使用一个专用的进程直接连接实例

我们可以使用 Oracle 提供的 lsnrctl 命令操纵监听服务的开启或关闭：

  1. <span style="color: #000000">lsnrctl status    # 查看状态
  2. lsnrctl stop      # 停止监听服务
  3. lsnrctl start     # 开启监听服务
  4. lsnrctl reload    # 重启监听服务
  5. lsnrctl services  # 查看监听的连接情况</span>

我们可以使用 Oracle 的 Net Manager 工具来配置自己的监听器。

实质上，用 Net Manager 配置跟直接修改下面文件的作用是一样的：

  1. 主目录\product\12.1.0\dbhome_1\network\admin\listener.ora

我们在 Net Manager 中对 listener 的配置对应的是这一段代码：

  1. LISTENER <span style="color: #808080">=</span><span style="color: #000000">
  2.   (DESCRIPTION_LIST </span><span style="color: #808080">=</span><span style="color: #000000">
  3.     (DESCRIPTION </span><span style="color: #808080">=</span><span style="color: #000000">
  4.       (ADDRESS </span><span style="color: #808080">=</span> (PROTOCOL <span style="color: #808080">=</span> TCP)(HOST <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">0.0</span>.<span style="color: #800000; font-weight: bold">0.0</span>)(PORT <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1521</span><span style="color: #000000">))
  5.     )
  6.     ...
  7.   )</span>

只要修改其中的 host/port 等，重启监听服务即可。

Server

Oracle 服务端分为两部分：

  1. Instance 实例
  2. Database 数据库

实例(Instance)

实例, 又称为数据库引擎，由 SGA(System Global Area, 系统全局区) 和 一系列后台进程 组成。 它需要启动才会生成，用来加载并管理一个数据库。

服务启动的大致过程：

  1. [读取] 读取系统的 ORACLE_SID 环境变量，确定要启动的实例名字，比如为 xxoo
  2. [加载] 从 $ORABASE/admin/xxoo 和 $ORA_HOME/database/SPFILEXXOO.ora 等位置加载相关配置文件。配置文件的名字是根据 sid 来定义的。
  3. [启动] 从配置文件中，读取相关信息，比如数据库名字、数据库控制文件位置、SGA 等信息，并根据这些，初始化数据库加载需要的 内存空间(SGA) 和 相关进程 。
  4. [装载] 根据配置文件中读取的数据库信息，找到各种数据文件位置，并装载数据库。
  5. [启动] 进行数据校验等，如果没有问题，启动数据库。

可以通过查看启动过程协助理解：

  1. <span style="color: #008080">--</span><span style="color: #008080"> 首先，登录 sys 用户，只有管理员才有完全操纵数据库的权力</span><span style="color: #008080">
  2. --</span><span style="color: #008080"> shutdown 用来关闭。如果不带参数，默认为 normal</span><span style="color: #008080">
  3. --</span><span style="color: #008080">-- immediate 表示立即关闭，如果有未处理完操作，回滚并断开</span><span style="color: #008080">
  4. --</span><span style="color: #008080">-- normal 表示等待所有连接断开才关闭数据库</span><span style="color: #008080">
  5. --</span><span style="color: #008080">-- 其他参数，略</span>
  6. <span style="color: #0000ff">shutdown</span><span style="color: #000000"> immediate;
  7. </span><span style="color: #008080">--</span><span style="color: #008080"> 启动数据库，分解为三个动作：</span><span style="color: #008080">
  8. --</span><span style="color: #008080">-- 启动实例</span><span style="color: #008080">
  9. --</span><span style="color: #008080">-- 利用启动的实例去挂载数据库</span><span style="color: #008080">
  10. --</span><span style="color: #008080">-- 校验并打开数据库</span><span style="color: #008080">
  11. --</span><span style="color: #008080"> 只有完全打开，才能进行完全的数据操作</span><span style="color: #008080">
  12. --</span><span style="color: #008080"> 也可以指定参数，启动到某个阶段。这是在维护数据库中使用的命令。</span>
  13. startup             <span style="color: #008080">--</span><span style="color: #008080"> 如果不加参数，</span>
  14. startup nomount     <span style="color: #008080">--</span><span style="color: #008080"> 启动到 nomount 阶段</span>
  15. startup mount       <span style="color: #008080">--</span><span style="color: #008080"> 启动到 mount 阶段</span>
  16. <span style="color: #008080">--</span><span style="color: #008080"> 当然，也可以这样分步启动：</span>
  17. <span style="color: #000000">startup nomount
  18. </span><span style="color: #0000ff">alter</span> <span style="color: #0000ff">database</span><span style="color: #000000"> mount
  19. </span><span style="color: #0000ff">alter</span> <span style="color: #0000ff">database</span> <span style="color: #0000ff">open</span>

数据库(Database)

数据库, 是保存在硬盘上的文件集合，它是数据的主要载体。

  1. $OracleBase\oradata\[数据库名字]

可以从不同的角度去认识数据库，比如物理/逻辑角度：

物理组件

数据库是保存在操作系统的一系列文件。

默认安装情况下，这些文件都在 $ORACLE_BASE/oradata 文件夹下：

  1. oradata<span style="color: #808080">/</span><span style="color: #000000">
  2. └── orcl </span><span style="color: #ff0000">[</span><span style="color: #ff0000">数据库的名字</span><span style="color: #ff0000">]</span><span style="color: #000000">
  3.     ├── CONTROL01.CTL
  4.     ├── CONTROL02.CTL
  5.     ├── EXAMPLE01.DBF
  6.     ├── REDO01.</span><span style="color: #ff00ff">LOG</span><span style="color: #000000">
  7.     ├── REDO02.</span><span style="color: #ff00ff">LOG</span><span style="color: #000000">
  8.     ├── REDO03.</span><span style="color: #ff00ff">LOG</span><span style="color: #000000">
  9.     ├── SYSAUX01.DBF
  10.     ├── SYSTEM01.DBF
  11.     ├── TEMP01.DBF
  12.     ├── UNDOTBS01.DBF
  13.     └── USERS01.DBF</span>

从文件角度分析，一个数据库包含下面几类（组件）：

  1. 控制文件(control file)。记录数据库的物理结构和其他信息，如数据库名称、各种文件位置等。多副本。 
    1. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> v$controlfile;
  2. 数据文件(data file)。用来存储数据的文件，会自动扩张。数据以块为单位进行保存。 
    1. <span style="color: #0000ff">select</span> name, status, enabled <span style="color: #0000ff">from</span> v$datafile;
  3. 重做日志文件(redo log)。用来记录用户的所有操作，为了备份恢复。 一个数据库至少有两个日志组，每个日志组至少有一个成员，成员之间是镜像关系。 用户的操作会记录到 redo log 中，当一个组记录满了，会自动切换到下一个组。轮流循环。 
    1. <span style="color: #008080">--</span><span style="color: #008080"> 需要理解 Oracle 日志的思路:</span><span style="color: #008080">
    2. --</span><span style="color: #008080"> 它采取了【多个分组，轮流循环写入；每组多成员，互为镜像；保存更多信息，使用归档模式】的方式，保证了记录安全性。</span><span style="color: #008080">
    3. --</span><span style="color: #008080"> 在生产环境中，需要日志调整到不同的磁盘中，这样，即使某个文件损坏，或某块磁盘损坏，都可以通过镜像的日志文件对数据进行恢复。</span>
    4. <span style="color: #008080">--</span><span style="color: #008080"> 查看 redo log 日志组</span>
    5. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> v$<span style="color: #ff00ff">log</span><span style="color: #000000">;
    6. </span><span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> v$logfile;
    7. </span><span style="color: #008080">--</span><span style="color: #008080"> 增加/删除 日志组</span>
    8. <span style="color: #0000ff">alter</span> <span style="color: #0000ff">database</span> <span style="color: #0000ff">add</span>  logfile <span style="color: #ff0000">‘</span><span style="color: #ff0000">d:/sss.rlog</span><span style="color: #ff0000">‘</span><span style="color: #000000"> size 100m;
    9. </span><span style="color: #0000ff">alter</span> <span style="color: #0000ff">database</span> <span style="color: #0000ff">drop</span> logfile <span style="color: #ff0000">‘</span><span style="color: #ff0000">d:/sss.rlog</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
    10. </span><span style="color: #008080">--</span><span style="color: #008080"> 清空日志组</span>
    11. <span style="color: #0000ff">alter</span> <span style="color: #0000ff">database</span> clear logfile <span style="color: #0000ff">group</span> <span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">;
    12. </span><span style="color: #0000ff">alter</span> <span style="color: #0000ff">database</span> clear unarchived logfile <span style="color: #0000ff">group</span> <span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">;
    13. </span><span style="color: #008080">--</span><span style="color: #008080"> 为日志组 增加/删除 成员</span>
    14. <span style="color: #0000ff">alter</span> <span style="color: #0000ff">database</span> <span style="color: #0000ff">add</span>  logfile member <span style="color: #ff0000">‘</span><span style="color: #ff0000">d:/ssss.log</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">to</span> <span style="color: #0000ff">group</span> <span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">;
    15. </span><span style="color: #0000ff">alter</span> <span style="color: #0000ff">database</span> <span style="color: #0000ff">drop</span> logfile member <span style="color: #ff0000">‘</span><span style="color: #ff0000">d:/ssss.log</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
    16.    
    17. </span><span style="color: #008080">--</span><span style="color: #008080"> 重命名文件</span><span style="color: #008080">
    18. --</span><span style="color: #008080"> 首先，在文件夹管理器里，将文件改名，比如，改为 ssss.redolog</span><span style="color: #008080">
    19. --</span><span style="color: #008080"> 其次，重启数据库到 mount 状态，然后执行重命名命令</span>
    20. <span style="color: #0000ff">alter</span> <span style="color: #0000ff">database</span> rename <span style="color: #0000ff">file</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">d:/ssss.log</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">to</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">d:/ssss.redolog</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
    21. </span><span style="color: #008080">--</span><span style="color: #008080"> 日志组一般是在写满的时候自动切换。</span><span style="color: #008080">
    22. --</span><span style="color: #008080"> 我们也可以手动切换</span>
    23. <span style="color: #0000ff">alter</span> system switch logfile;
  4. 归档日志文件。是重做日志的补充（redo log 记录的记录是有限的），可以把写满的 redo log 进行备份。 
    1. <span style="color: #008080">--</span><span style="color: #008080"> Oracle 的归档模式默认是关闭的</span><span style="color: #008080">
    2. --</span><span style="color: #008080"> 归档模式会占用大量空间</span><span style="color: #008080">
    3. --</span><span style="color: #008080"> 但他们用更多的空间，保存更多的历史记录，保障更大的安全性</span>
    4. <span style="color: #008080">--</span><span style="color: #008080"> 查看状态</span>
    5. archive <span style="color: #ff00ff">log</span><span style="color: #000000"> list;
    6. </span><span style="color: #008080">--</span><span style="color: #008080"> 切换数据库到归档模式</span>
    7. <span style="color: #0000ff">alter</span> <span style="color: #0000ff">database</span><span style="color: #000000"> archivelog;
    8. </span><span style="color: #008080">--</span><span style="color: #008080"> 启动</span>
    9. archive <span style="color: #ff00ff">log</span><span style="color: #000000"> start;
    10. </span><span style="color: #008080">--</span><span style="color: #008080"> 查看状态</span>
    11. archive <span style="color: #ff00ff">log</span> list;
  5. 其他文件

逻辑组件

https://docs.oracle.com/cd/B28359_01/server.111/b28318/physical.htm#CNCPT1082

从 Oracle 内部管理数据的角度，可以将 Oracle 分为4个组件：

  1. 表空间(tablespace)
    • 最基本的逻辑结构，是 Oracle 中进行数据恢复的最小单位，容纳着表、索引等对象
    • 数据库是由若干表空间组成的。一个表空间至少对应一个物理文件。
    • 实际开发中，不建议使用默认表空间。请为自己的业务创建自己的表空间。
    1. <span style="color: #008080">--</span><span style="color: #008080"> 内置的各种表空间</span><span style="color: #008080">
    2. --</span><span style="color: #008080">-- system/sysaux 系统表空间/系统辅助表空间，用来保存系统字典表和其他信息，数据库创建完会自动生成</span><span style="color: #008080">
    3. --</span><span style="color: #008080">-- users 用户表空间，创建新用户时，默认使用的表空间</span><span style="color: #008080">
    4. --</span><span style="color: #008080">-- temp 临时表空间</span><span style="color: #008080">
    5. --</span><span style="color: #008080">-- undo 回滚表空间</span>
    6. <span style="color: #008080">--</span><span style="color: #008080"> 查看表空间信息</span>
    7. <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> v$tablespace;
    8. </span><span style="color: #008080">--</span><span style="color: #008080"> 查看所有表空间跟文件对应关系</span>
    9. <span style="color: #0000ff">SELECT</span>  <span style="color: #ff00ff">FILE_NAME</span>, BLOCKS, TABLESPACE_NAME <span style="color: #0000ff">from</span><span style="color: #000000"> dba_data_files;
    10. </span><span style="color: #008080">--</span><span style="color: #008080"> 创建表空间</span>
    11. <span style="color: #0000ff">create</span><span style="color: #000000"> tablespace xxx
    12.   datafile   </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">D:/sss.dbf</span><span style="color: #ff0000">‘</span><span style="color: #000000">
    13.   size       50m
    14.   autoextend </span><span style="color: #0000ff">on</span>
    15.   <span style="color: #0000ff">next</span><span style="color: #000000">       50m
    16.   maxsize    1024m;
    17. </span><span style="color: #008080">--</span><span style="color: #008080"> 创建临时表空间</span>
    18. <span style="color: #0000ff">create</span> <span style="color: #0000ff">temporary</span><span style="color: #000000"> tablespace yyy
    19.   tempfile </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">D:/ANOTHER_TMP.dbf</span><span style="color: #ff0000">‘</span><span style="color: #000000">
    20.   size 5m;
    21. </span><span style="color: #008080">--</span><span style="color: #008080"> 删除表空间</span>
    22. <span style="color: #0000ff">drop</span> tablespace xxx;
  2. 段(Segment)
    • 段是对象在数据库中占用的空间
    • 包括索引段、数据段等
    • 表空间被划分为若干区域，每个区域负责存放不同类型数据，这些区域这就是段
  3. 区(Extend)
    • 由连续的数据块组成，由 Oracle 自动分配管理
    • 会自动扩展大小
  4. 块(Block)
    • 数据块是 Oracle 数据库最小的逻辑单元
    • 它代表在读写操作的时候，每次处理的数据大小是多少
    • 正常情况下，它是操作系统块的整数倍，默认是 8 KB
    • 可以通过参数 db_block_size 控制 
      1. show parameters block;

技术分享图片

用户权限

安装完 Oracle，缺省有两个用户：

  1. SYS 用户，又叫数据库系统管理员、特权用户，数据库中至高无上的存在。
    • 它是数据库的系统管理员，负责数据库的安装、维护、升级、备份、恢复、优化等操作。
    • 在它之下，保存着数据库所有的系统字典。
    • 不能用 normal 身份登录，必须用 SYSDBA/SYSOPER 身份登录。
  2. SYSTEM 用户，数据库管理员，它拥有 DBA 角色，主要负责对数据库中各种对象，各种资源的管理。
  3. SCOTT 用户，一个示例用户，默认是锁定的，需要解锁使用。

新创建的用户，是不能做任何事情的（甚至不能登录）。 必须要为用户授予权限，才能做相应的事情。 可以说，用户是权限的容器。

权限分为两种：

技术分享图片

用户(User)

用户是用于 资源管理 和 权限控制 的一个概念。

  1. <span style="color: #008080">--</span><span style="color: #008080"> 创建用户的语法</span>
  2. <span style="color: #0000ff">CREATE</span> <span style="color: #ff00ff">USER</span><span style="color: #000000"> name
  3.   IDENTIFIED </span><span style="color: #0000ff">BY</span> password          <span style="color: #008080">--</span><span style="color: #008080"> 一个用户，至少拥有用户名和密码</span>
  4.   <span style="color: #ff0000">[</span><span style="color: #ff0000">DEFAULT TABLESPACE users</span><span style="color: #ff0000">]</span>      <span style="color: #008080">--</span><span style="color: #008080"> 指定此用户下数据默认保存位置，如果不指定，默认为 Users 表空间</span>
  5.   <span style="color: #ff0000">[</span><span style="color: #ff0000">TEMPORARY TABLESPACE temp</span><span style="color: #ff0000">]</span>     <span style="color: #008080">--</span><span style="color: #008080"> 指定临时表空间，默认为 temp 表空间</span>
  6.   <span style="color: #ff0000">[</span><span style="color: #ff0000">QUOTA 100M/unlimited ON USERS</span><span style="color: #ff0000">]</span> <span style="color: #008080">--</span><span style="color: #008080"> 在表空间上有多少空间的使用权力，默认为 0, unlimited 表示无上限</span>
  7.   <span style="color: #ff0000">[</span><span style="color: #ff0000">ACCOUNT LOCK/UNLOCK</span><span style="color: #ff0000">]</span>           <span style="color: #008080">--</span><span style="color: #008080"> 账号的初始状态：锁定/未锁定，默认为未锁定</span>
  8.   <span style="color: #ff0000">[</span><span style="color: #ff0000">PASSWORD EXPIRE</span><span style="color: #ff0000">]</span>               <span style="color: #008080">--</span><span style="color: #008080"> 账号的初始状态：强制要求重设密码，默认不强制</span>
  9.   <span style="color: #ff0000">[</span><span style="color: #ff0000">PROFILE DEFAULT</span><span style="color: #ff0000">]</span>               <span style="color: #008080">--</span><span style="color: #008080"> 指定使用的资源文件，默认是 default</span>
  10. <span style="color: #000000">;
  11. </span><span style="color: #008080">--</span><span style="color: #008080"> 修改用户的语法</span>
  12. <span style="color: #0000ff">ALTER</span> <span style="color: #ff00ff">USER</span> name <span style="color: #ff0000">[</span><span style="color: #ff0000">跟创建用户的参数是一样的</span><span style="color: #ff0000">]</span><span style="color: #000000">;
  13. </span><span style="color: #008080">--</span><span style="color: #008080"> 删除用户的语法</span>
  14. <span style="color: #0000ff">DROP</span> <span style="color: #ff00ff">USER</span> name <span style="color: #ff0000">[</span><span style="color: #ff0000">cascade</span><span style="color: #ff0000">]</span>;

为了对用户行为有更精细的控制，需要使用 资源文件 (profile)。 资源文件是口令限制、资源限制的命名集合。

  1. <span style="color: #008080">--</span><span style="color: #008080"> 增加<                    </span>

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！