时间:2021-07-01 10:21:17 帮助过:22人阅读
1.2 创建一个自己的用户(比如 vip/vip)
- <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>
- <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>
- <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>
- <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>
- <span style="color: #008080">--</span><span style="color: #008080"> 上面的创建用户、授予权限两步操作,可以简化为下面一步:</span>
- <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 切换到用户
- sqlplus vip<span style="color: #808080">/</span>vip <span style="color: #008080">--</span><span style="color: #008080"> 在 cmd 下</span>
- conn vip<span style="color: #808080">/</span>vip <span style="color: #008080">--</span><span style="color: #008080"> 在 sqlplus 中</span>
1.4 使用
- <span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span> aaa (id <span style="color: #0000ff">int</span>);
1.5 激活内置的测试账号,这里面有几张示例库,可以用它们练习下查询
- <span style="color: #0000ff">alter</span> <span style="color: #ff00ff">user</span><span style="color: #000000"> scott account unlock;
- conn scott</span><span style="color: #808080">/</span>tiger
1.6 修改密码
- <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>;
- <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">;
- </span><span style="color: #008080">--</span><span style="color: #008080"> vs.</span>
- <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>;
- <span style="color: #008080">--</span><span style="color: #008080"> 初始化数据</span>
- <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;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 分别查看数据</span>
- <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> emp;
- </span><span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> salgrade;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 杂交</span>
- <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> emp, salgrade;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 过滤掉不合适的</span>
- <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;
- <span style="color: #008080">--</span><span style="color: #008080"> 分析题目</span><span style="color: #008080">
- --</span><span style="color: #008080"> 1. 需要先查询出每个部门的平均工资</span><span style="color: #008080">
- --</span><span style="color: #008080"> 2. 根据 salgrade 表中的数据,获取每个部门平均工资的等级</span>
- <span style="color: #008080">--</span><span style="color: #008080"> 这是所有的人</span>
- <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> emp;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 按照部门分组</span>
- <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;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 结果:</span><span style="color: #008080">
- --</span><span style="color: #008080"> | DEPTNO | AVG(SAL) |</span><span style="color: #008080">
- --</span><span style="color: #008080"> | 30 | 1566.66667 |</span><span style="color: #008080">
- --</span><span style="color: #008080"> | 20 | 2258.33333 |</span><span style="color: #008080">
- --</span><span style="color: #008080"> | 10 | 2916.66667 |</span>
- <span style="color: #008080">--</span><span style="color: #008080"> 跟 salgrade 表,杂交,总共 15 条结果</span>
- <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000">
- (</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,
- salgrade s;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 过滤掉工资范围不合适的数据</span>
- <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000">
- (</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,
- salgrade s
- </span><span style="color: #0000ff">where</span><span style="color: #000000">
- t.sal </span><span style="color: #808080">between</span> s.losal <span style="color: #808080">and</span><span style="color: #000000"> s.hisal;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 结果就是这样,就对了</span><span style="color: #008080">
- --</span><span style="color: #008080"> | DEPTNO | SAL | GRADE | LOSAL | HISAL |</span><span style="color: #008080">
- --</span><span style="color: #008080"> | 10 | 2916.66667 | 4 | 2001 | 3000 |</span><span style="color: #008080">
- --</span><span style="color: #008080"> | 20 | 2258.33333 | 4 | 2001 | 3000 |</span><span style="color: #008080">
- --</span><span style="color: #008080"> | 30 | 1566.66667 | 3 | 1401 | 2000 |</span>
- <span style="color: #008080">--</span><span style="color: #008080"> 需要将部门名称显示出来,再杂交->过滤一次就可以了</span>
- <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000">
- (</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,
- salgrade s,
- dept d
- </span><span style="color: #0000ff">where</span><span style="color: #000000">
- t.deptno </span><span style="color: #808080">=</span> d.deptno <span style="color: #808080">and</span><span style="color: #000000">
- t.sal </span><span style="color: #808080">between</span> s.losal <span style="color: #808080">and</span><span style="color: #000000"> s.hisal;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 最后的最后,设置显示字段</span>
- <span style="color: #0000ff">select</span> dname, grade <span style="color: #0000ff">from</span><span style="color: #000000">
- (</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,
- salgrade s,
- dept d
- </span><span style="color: #0000ff">where</span><span style="color: #000000">
- t.deptno </span><span style="color: #808080">=</span> d.deptno <span style="color: #808080">and</span><span style="color: #000000">
- t.sal </span><span style="color: #808080">between</span> s.losal <span style="color: #808080">and</span><span style="color: #000000"> s.hisal;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 最终结果,完美</span><span style="color: #008080">
- --</span><span style="color: #008080">| DNAME | GRADE |</span><span style="color: #008080">
- --</span><span style="color: #008080">| ACCOUNTING | 4 |</span><span style="color: #008080">
- --</span><span style="color: #008080">| RESEARCH | 4 |</span><span style="color: #008080">
- --</span><span style="color: #008080">| SALES | 3 |</span>
gender | number |
---|---|
男 | 46 |
女 | 10 |
条件 | 加多少 |
---|---|
1000元以下 | 50% |
2000元以下 | 30% |
3000元以下 | 20% |
其他 | 5% |
1981/5/1
之后来的所有人,只加 2%
, 请写出语句。看到最低、最大、平均之类的题目,首先想到的是分组函数的使用。
也就是 group by, having。
- <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 表中,找到 “来自芝加哥最有钱的那个人” 。
首先,我们需要理清思路。
这里总共有两个条件:
我们可以看出,第二个条件是依赖第一个条件的。
所以,分两步查询:
- <span style="color: #008080">--</span><span style="color: #008080">-- 第一步:找到来自芝加哥的所有人。下面两种写法等价:</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
- </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)
- </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">;
- </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
- </span><span style="color: #0000ff">where</span> d.deptno <span style="color: #808080">=</span><span style="color: #000000"> e.deptno
- </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">;
- </span><span style="color: #008080">--</span><span style="color: #008080">-- 第二步,基于上面结果,筛选出最有钱的那个</span>
- <span style="color: #008080">--</span><span style="color: #008080"> 可以通过 max 函数</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
- </span><span style="color: #0000ff">where</span> e.deptno <span style="color: #808080">=</span><span style="color: #000000"> d.deptno
- </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: #808080">and</span> sal <span style="color: #808080">=</span><span style="color: #000000">
- (</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
- </span><span style="color: #0000ff">where</span> e.deptno <span style="color: #808080">=</span><span style="color: #000000"> d.deptno
- </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">);
- </span><span style="color: #008080">--</span><span style="color: #008080"> 可以通过 order by 方式</span>
- <span style="color: #0000ff">select</span> ename <span style="color: #0000ff">from</span><span style="color: #000000">
- (</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
- </span><span style="color: #0000ff">where</span> e.deptno <span style="color: #808080">=</span><span style="color: #000000"> d.deptno
- </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: #0000ff">order</span> <span style="color: #0000ff">by</span> sal <span style="color: #0000ff">desc</span><span style="color: #000000">)
- </span><span style="color: #0000ff">where</span> rownum <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span>;
注意,实现的方式,不止上面的那些。但总体 思路 是一样的。
所以,思路永远是最重要的。
最核心的问题: 查询出符合条件的部门编号。
1.查询出所有的部门编号
- <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> emp;
2.查询出所有工资少于 1000 的人, 我们要把它所在的部门,从上面的结果中排除掉。
- <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.将上面查询出的不符合条件的部门排除掉
- <span style="color: #0000ff">select</span> <span style="color: #0000ff">distinct</span> deptno <span style="color: #0000ff">from</span><span style="color: #000000"> emp
- </span><span style="color: #0000ff">where</span> deptno <span style="color: #808080">not</span> <span style="color: #808080">in</span><span style="color: #000000">
- (</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.修改上面语句,增加最终的条件,查询所有的其他信息
- <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)
- </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">
- (</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 是用来分组和筛选分组。
- <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>
- <span style="color: #0000ff">where</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">条件</span><span style="color: #ff0000">]</span>
- <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>
- <span style="color: #0000ff">having</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">对分组结果进行筛选</span><span style="color: #ff0000">]</span>
- <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>
示例:
- <span style="color: #0000ff">select</span> deptno, <span style="color: #008080">--</span><span style="color: #008080"> 分组字段</span>
- <span style="color: #ff00ff">count</span>(<span style="color: #808080">*</span>), <span style="color: #008080">--</span><span style="color: #008080"> 人数</span>
- <span style="color: #ff00ff">sum</span>(sal), <span style="color: #008080">--</span><span style="color: #008080"> 工资总和</span>
- <span style="color: #ff00ff">avg</span>(sal), <span style="color: #008080">--</span><span style="color: #008080"> 平均工资</span>
- <span style="color: #ff00ff">max</span>(sal), <span style="color: #008080">--</span><span style="color: #008080"> 最高工资</span>
- <span style="color: #ff00ff">min</span>(sal) <span style="color: #008080">--</span><span style="color: #008080"> 最低工资</span>
- <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>
- <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.按照部门分组
- <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 的部门。 即:得到符合条件的部门的编号。
- <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.完善最终语句,得到最终结果。
- <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
- </span><span style="color: #0000ff">where</span> e.deptno <span style="color: #808080">=</span><span style="color: #000000"> d.deptno
- </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>);
得到有 n 条记录的虚表:
- <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">;
- </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>;
将虚表转化为我们需要的日期表:
- <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">;
- </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>;
- <span style="color: #008080">--</span><span style="color: #008080"> 1.</span>
- <span style="color: #0000ff">select</span> <span style="color: #808080">*</span>
- <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
- </span><span style="color: #0000ff">from</span><span style="color: #000000"> dba_objects
- </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">)
- </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>
- <span style="color: #808080">and</span> d <span style="color: #808080"><=</span><span style="color: #000000"> last_day(sysdate);
- </span><span style="color: #008080">--</span><span style="color: #008080"> 2.</span>
- <span style="color: #0000ff">select</span> <span style="color: #808080">*</span>
- <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
- </span><span style="color: #0000ff">from</span><span style="color: #000000"> dual
- 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">)
- </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>
- <span style="color: #808080">and</span> d <span style="color: #808080"><=</span> last_day(sysdate);
当然,你也可以将 last_day 这一段放到里面:
- <span style="color: #0000ff">select</span> <span style="color: #808080">*</span>
- <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
- </span><span style="color: #0000ff">from</span><span style="color: #000000"> dual
- 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)))
- </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>;
根据我们学校的实际情况,请帮助设计一个学生管理系统。
- 比如, 学生,老师,班级,课程。按照你自己的设计,酌情增加。
基本步骤:
这样的图能帮助我们理清思路,并能帮助团队间的有效交流。
一定在图画好之后再去着手数据库表的创建。要秉承先设计后实现的思路。
你可以用一些知名的工具(如 visio)去画,也可以手动在纸上画。
- 需求分析 -> *概要设计* -> 详细实现。
首先,设计一个博客表(blog
), 至少有下面字段
用 Java 完成基本的 =CRUD=,并掌握使用 PL/SQL 操作 blob/clob 的技巧。
Oracle 采取的是 Client/Server 架构。
客户端(client)操作数据库的请求发送后,服务端的监听器(TNSListener)接收到请求,并将其转发给相应的数据库实例(Instance),再由实例(Instance)去操纵数据库(Database)。 返回操作结果,是一个相反的过程。下面是个简陋的图示:
这是一个轻量级的功能强大的客户端, 是 dba 必须掌握的工具。
用 sqlplus 连接数据库的语法为:
- <span style="color: #000000"># Usage:
- # sqlplus 用户名</span><span style="color: #808080">/</span>密码<span style="color: #008000">@主机名</span>:端口号<span style="color: #808080">/</span><span style="color: #000000">实例名
- # 参数个数不是固定的
- sqlplus # 会要求你输入用户名密码,默认连接本地 ORACLE_SID 变量指定的数据库
- sqlplus vip # 会要求你输入密码
- sqlplus vip</span><span style="color: #808080">/</span><span style="color: #000000">vip # 连接本地 ORACLE_SID 变量指定的数据库
- sqlplus sys</span><span style="color: #808080">/</span>hello <span style="color: #0000ff">as</span><span style="color: #000000"> sysdba # sys 用户必须用 sysdba 或 sysoper 的身份登录
- 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
- sqlplus vip</span><span style="color: #008000">@192db</span> # 连接 别名 为 192db 的数据库
我们可以配置 sqlplus 的一些行为,两个命令:
比如:
- show <span style="color: #808080">all</span> <span style="color: #008080">--</span><span style="color: #008080"> 显示所有配置参数</span>
- show lines <span style="color: #008080">--</span><span style="color: #008080"> 显示 lines 的配置信息</span>
- show errors <span style="color: #008080">--</span><span style="color: #008080"> 显示错误</span>
- <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>
- <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>
- <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>
- <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>
- <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>
- <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>
- <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>
- <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>
- <span style="color: #0000ff">column</span> aaa format a22 <span style="color: #008080">--</span><span style="color: #008080"> 将列 ‘aaa‘ 的宽度限制为 22 个字幕‘a‘的大小。column 命令很强大,语句也复杂,此处不提。</span>
在 sqlplus 中有缓冲区的概念:
- 缓冲区是用来记录上一次执行的命令语句的空间。
我们可以通过一些列简单命令,对上一次输入的语句进行一些控制:
append/insert
delete
change
list
run
或者 /
例子:
- list <span style="color: #008080">--</span><span style="color: #008080"> 显示完整的缓存区</span>
- list <span style="color: #800000; font-weight: bold">3</span> <span style="color: #008080">--</span><span style="color: #008080"> 显示并定位到第三行</span>
- 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>
- list last <span style="color: #008080">--</span><span style="color: #008080"> 定位到最后一行</span>
- list <span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">
- del </span><span style="color: #008080">--</span><span style="color: #008080"> 删除第三行</span>
- list <span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">
- 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>
- <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>
还有其他一些命令:
- get D:\aaa.sql <span style="color: #008080">--</span><span style="color: #008080"> 将文件加载到缓冲区,但不执行</span>
- start D:\aaa.sql <span style="color: #008080">--</span><span style="color: #008080"> 将文件加载到缓冲区,并且执行</span>
- <span style="color: #008000">@D</span>:\aaa.sql <span style="color: #008080">--</span><span style="color: #008080"> 是上面一条语句的简写形式</span>
- <span style="color: #0000ff">save</span> D:\bbb.sql <span style="color: #008080">--</span><span style="color: #008080"> 将缓冲区的内容保存到文件中</span>
- edit <span style="color: #008080">--</span><span style="color: #008080"> 调用外部编辑器,编辑缓冲区</span>
- clear screen <span style="color: #008080">--</span><span style="color: #008080"> 清空缓冲区</span>
- show <span style="color: #ff00ff">user</span> <span style="color: #008080">--</span><span style="color: #008080"> 显示当前用户</span>
- show parameters <span style="color: #008080">--</span><span style="color: #008080"> 显示 oracle 的配置参数</span>
- show parameters nls <span style="color: #008080">--</span><span style="color: #008080"> 显示 oracle 中所有跟语言配置相关的一些参数</span>
- describe emp <span style="color: #008080">--</span><span style="color: #008080"> 显示 emp 表的结构信息</span>
用 Java 连接数据库,需要用到 jdbc 驱动,它们可以在下面目录中找到:
- 主目录\product\12.1.0\dbhome_1\jdbc\lib\*.jar
比如 ojdbc7_g.jar
, 7
表示适用于 JDK 版本 1.7, g
表示自带更多调试信息。
TNSListener,是用来监听来自客户端的请求,并将其转发给相对应的服务端实例的一种后台服务。
它是沟通客户端与服务端的一个桥梁。
比如,下面用 sqlplus
客户端将会连接 localhost
上的 orcl
数据库:
- sqlplus vip<span style="color: #808080">/</span>vip<span style="color: #008000">@localhost</span><span style="color: #808080">/</span>orcl
请求会发送到 localhost 主机的 1521 号端口, 作为监听的 TNSListener 收到这个请求后,再把请求转发给对应的 orcl 数据库实例。
所以必须开启监听服务,并且配置正确,才能连接操作数据库。
- 注:如果用 sqlplus vip/vip 的方式连接数据库,即没有指定连接的机器,那么默认连接的是本机数据库
- 这种连接是不需要监听服务的,因为为了增加连接速度,这样的本地连接 oracle 会使用一个专用的进程直接连接实例
我们可以使用 Oracle 提供的 lsnrctl
命令操纵监听服务的开启或关闭:
- <span style="color: #000000">lsnrctl status # 查看状态
- lsnrctl stop # 停止监听服务
- lsnrctl start # 开启监听服务
- lsnrctl reload # 重启监听服务
- lsnrctl services # 查看监听的连接情况</span>
我们可以使用 Oracle 的 Net Manager 工具来配置自己的监听器。
实质上,用 Net Manager 配置跟直接修改下面文件的作用是一样的:
- 主目录\product\12.1.0\dbhome_1\network\admin\listener.ora
我们在 Net Manager 中对 listener 的配置对应的是这一段代码:
- LISTENER <span style="color: #808080">=</span><span style="color: #000000">
- (DESCRIPTION_LIST </span><span style="color: #808080">=</span><span style="color: #000000">
- (DESCRIPTION </span><span style="color: #808080">=</span><span style="color: #000000">
- (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">))
- )
- ...
- )</span>
只要修改其中的 host/port 等,重启监听服务即可。
Oracle 服务端分为两部分:
Instance
实例Database
数据库实例, 又称为数据库引擎,由 SGA(System Global Area, 系统全局区)
和 一系列后台进程
组成。 它需要启动才会生成,用来加载并管理一个数据库。
$ORABASE/admin/xxoo
和 $ORA_HOME/database/SPFILEXXOO.ora
等位置加载相关配置文件。配置文件的名字是根据 sid 来定义的。内存空间(SGA)
和 相关进程
。可以通过查看启动过程协助理解:
- <span style="color: #008080">--</span><span style="color: #008080"> 首先,登录 sys 用户,只有管理员才有完全操纵数据库的权力</span><span style="color: #008080">
- --</span><span style="color: #008080"> shutdown 用来关闭。如果不带参数,默认为 normal</span><span style="color: #008080">
- --</span><span style="color: #008080">-- immediate 表示立即关闭,如果有未处理完操作,回滚并断开</span><span style="color: #008080">
- --</span><span style="color: #008080">-- normal 表示等待所有连接断开才关闭数据库</span><span style="color: #008080">
- --</span><span style="color: #008080">-- 其他参数,略</span>
- <span style="color: #0000ff">shutdown</span><span style="color: #000000"> immediate;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 启动数据库,分解为三个动作:</span><span style="color: #008080">
- --</span><span style="color: #008080">-- 启动实例</span><span style="color: #008080">
- --</span><span style="color: #008080">-- 利用启动的实例去挂载数据库</span><span style="color: #008080">
- --</span><span style="color: #008080">-- 校验并打开数据库</span><span style="color: #008080">
- --</span><span style="color: #008080"> 只有完全打开,才能进行完全的数据操作</span><span style="color: #008080">
- --</span><span style="color: #008080"> 也可以指定参数,启动到某个阶段。这是在维护数据库中使用的命令。</span>
- startup <span style="color: #008080">--</span><span style="color: #008080"> 如果不加参数,</span>
- startup nomount <span style="color: #008080">--</span><span style="color: #008080"> 启动到 nomount 阶段</span>
- startup mount <span style="color: #008080">--</span><span style="color: #008080"> 启动到 mount 阶段</span>
- <span style="color: #008080">--</span><span style="color: #008080"> 当然,也可以这样分步启动:</span>
- <span style="color: #000000">startup nomount
- </span><span style="color: #0000ff">alter</span> <span style="color: #0000ff">database</span><span style="color: #000000"> mount
- </span><span style="color: #0000ff">alter</span> <span style="color: #0000ff">database</span> <span style="color: #0000ff">open</span>
数据库, 是保存在硬盘上的文件集合,它是数据的主要载体。
- $OracleBase\oradata\[数据库名字]
可以从不同的角度去认识数据库,比如物理/逻辑角度:
数据库是保存在操作系统的一系列文件。
默认安装情况下,这些文件都在 $ORACLE_BASE/oradata
文件夹下:
- oradata<span style="color: #808080">/</span><span style="color: #000000">
- └── orcl </span><span style="color: #ff0000">[</span><span style="color: #ff0000">数据库的名字</span><span style="color: #ff0000">]</span><span style="color: #000000">
- ├── CONTROL01.CTL
- ├── CONTROL02.CTL
- ├── EXAMPLE01.DBF
- ├── REDO01.</span><span style="color: #ff00ff">LOG</span><span style="color: #000000">
- ├── REDO02.</span><span style="color: #ff00ff">LOG</span><span style="color: #000000">
- ├── REDO03.</span><span style="color: #ff00ff">LOG</span><span style="color: #000000">
- ├── SYSAUX01.DBF
- ├── SYSTEM01.DBF
- ├── TEMP01.DBF
- ├── UNDOTBS01.DBF
- └── USERS01.DBF</span>
从文件角度分析,一个数据库包含下面几类(组件):
- <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> v$controlfile;
- <span style="color: #0000ff">select</span> name, status, enabled <span style="color: #0000ff">from</span> v$datafile;
- <span style="color: #008080">--</span><span style="color: #008080"> 需要理解 Oracle 日志的思路:</span><span style="color: #008080">
- --</span><span style="color: #008080"> 它采取了【多个分组,轮流循环写入;每组多成员,互为镜像;保存更多信息,使用归档模式】的方式,保证了记录安全性。</span><span style="color: #008080">
- --</span><span style="color: #008080"> 在生产环境中,需要日志调整到不同的磁盘中,这样,即使某个文件损坏,或某块磁盘损坏,都可以通过镜像的日志文件对数据进行恢复。</span>
- <span style="color: #008080">--</span><span style="color: #008080"> 查看 redo log 日志组</span>
- <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">;
- </span><span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> v$logfile;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 增加/删除 日志组</span>
- <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;
- </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">;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 清空日志组</span>
- <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">;
- </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">;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 为日志组 增加/删除 成员</span>
- <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">;
- </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">;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 重命名文件</span><span style="color: #008080">
- --</span><span style="color: #008080"> 首先,在文件夹管理器里,将文件改名,比如,改为 ssss.redolog</span><span style="color: #008080">
- --</span><span style="color: #008080"> 其次,重启数据库到 mount 状态,然后执行重命名命令</span>
- <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">;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 日志组一般是在写满的时候自动切换。</span><span style="color: #008080">
- --</span><span style="color: #008080"> 我们也可以手动切换</span>
- <span style="color: #0000ff">alter</span> system switch logfile;
- <span style="color: #008080">--</span><span style="color: #008080"> Oracle 的归档模式默认是关闭的</span><span style="color: #008080">
- --</span><span style="color: #008080"> 归档模式会占用大量空间</span><span style="color: #008080">
- --</span><span style="color: #008080"> 但他们用更多的空间,保存更多的历史记录,保障更大的安全性</span>
- <span style="color: #008080">--</span><span style="color: #008080"> 查看状态</span>
- archive <span style="color: #ff00ff">log</span><span style="color: #000000"> list;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 切换数据库到归档模式</span>
- <span style="color: #0000ff">alter</span> <span style="color: #0000ff">database</span><span style="color: #000000"> archivelog;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 启动</span>
- archive <span style="color: #ff00ff">log</span><span style="color: #000000"> start;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 查看状态</span>
- archive <span style="color: #ff00ff">log</span> list;
https://docs.oracle.com/cd/B28359_01/server.111/b28318/physical.htm#CNCPT1082
从 Oracle 内部管理数据的角度,可以将 Oracle 分为4个组件:
- <span style="color: #008080">--</span><span style="color: #008080"> 内置的各种表空间</span><span style="color: #008080">
- --</span><span style="color: #008080">-- system/sysaux 系统表空间/系统辅助表空间,用来保存系统字典表和其他信息,数据库创建完会自动生成</span><span style="color: #008080">
- --</span><span style="color: #008080">-- users 用户表空间,创建新用户时,默认使用的表空间</span><span style="color: #008080">
- --</span><span style="color: #008080">-- temp 临时表空间</span><span style="color: #008080">
- --</span><span style="color: #008080">-- undo 回滚表空间</span>
- <span style="color: #008080">--</span><span style="color: #008080"> 查看表空间信息</span>
- <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> v$tablespace;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 查看所有表空间跟文件对应关系</span>
- <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;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 创建表空间</span>
- <span style="color: #0000ff">create</span><span style="color: #000000"> tablespace xxx
- datafile </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">D:/sss.dbf</span><span style="color: #ff0000">‘</span><span style="color: #000000">
- size 50m
- autoextend </span><span style="color: #0000ff">on</span>
- <span style="color: #0000ff">next</span><span style="color: #000000"> 50m
- maxsize 1024m;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 创建临时表空间</span>
- <span style="color: #0000ff">create</span> <span style="color: #0000ff">temporary</span><span style="color: #000000"> tablespace yyy
- 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">
- size 5m;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 删除表空间</span>
- <span style="color: #0000ff">drop</span> tablespace xxx;
- show parameters block;
安装完 Oracle,缺省有两个用户:
SYS
用户,又叫数据库系统管理员、特权用户,数据库中至高无上的存在。
SYSTEM
用户,数据库管理员,它拥有 DBA 角色,主要负责对数据库中各种对象,各种资源的管理。SCOTT
用户,一个示例用户,默认是锁定的,需要解锁使用。新创建的用户,是不能做任何事情的(甚至不能登录)。 必须要为用户授予权限,才能做相应的事情。 可以说,用户是权限的容器。
权限分为两种:
用户是用于 资源管理 和 权限控制 的一个概念。
- <span style="color: #008080">--</span><span style="color: #008080"> 创建用户的语法</span>
- <span style="color: #0000ff">CREATE</span> <span style="color: #ff00ff">USER</span><span style="color: #000000"> name
- IDENTIFIED </span><span style="color: #0000ff">BY</span> password <span style="color: #008080">--</span><span style="color: #008080"> 一个用户,至少拥有用户名和密码</span>
- <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>
- <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>
- <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>
- <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>
- <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>
- <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>
- <span style="color: #000000">;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 修改用户的语法</span>
- <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">;
- </span><span style="color: #008080">--</span><span style="color: #008080"> 删除用户的语法</span>
- <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
)。 资源文件是口令限制、资源限制的命名集合。
- <span style="color: #008080">--</span><span style="color: #008080"> 增加< </span>