rhb_blog

rao的博客

0%

oracle数据库

发表于 更新于 分类于 ，

安装教程

参考文章

参考文章

概念

Oracle数据库服务器由两部分组成：

  • 实例：理解为对象,看不见的
  • 数据库：理解为类，看得见的

Oracle数据库把表、视图等都看成是对象。

null值

Oracle中如果存在字段是null值的话，那么在sqlplus中它是不会显示出来的….如果我们使用null值的数据与其他数据进行运算…那么最终得出的结果都是null值

因此，Oracle提供了NVL(表达式1，表达式2)函数供我们使用，如果表达式1的值为null值，那么就取表达式2的值…当然了，如果表达式1不是null，取的就是表达式1的值

还有值得注意的是：null值不能参数=号运算，null能参数number/date/varchar2类型运算

Oracle提供了 is null关键字来代替=号运算的问题

别名

Mysql中如果要用别名的话，需要使用as关键字 ，后面跟着别名就行了….Oracle可以省略as关键字…

并且，一般地，我们使用别名都是用双引号””把别名括起来，Oracle也支持我们直接写别名，但是呢，如果我们不写双引号，那么我们的别名是不能有空格的

还有一点的是：Oracle的别名是不能使用单引号来括起来的，Oracle默认认为单引号是字符串类型和日期类型的。

操作表细节

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
进入回收站
drop table users;

查询回收站中的对象
show recyclebin;

闪回，即将回收站还原
flashback table 表名 to before drop;
flashback table 表名 to before drop rename to  新表名;

彻底删除users表
drop table users purge;

清空回收站
purge recyclebin;

为emp表增加image列，alter table 表名 add 列名 类型(宽度) 
alter table emp
add image blob;

修改ename列的长度为20个字节，alter table 表名 modify 列名 类型(宽度) 
alter table emp
modify ename varchar2(20);

删除image列，alter table 表名 drop column 列名
alter table emp
drop column image;

重名列名ename为username，alter table 表名 rename column 原列名 to 新列名
alter table emp
rename column ename to username;

将emp表重命名emps，rename 原表名 to 新表名
rename emp to emps;

值得注意的是：修改表的时候，是不能回滚的！

Oracle中的级联操作：

  • 【on delete cascade】级联删除
  • 【on delete set null】将外键一方设置为null

分页

Oracle中的分页是依靠着rownum这个伪列来实现的，由于rownum只能使用的是<=或者<来获取数据。。。因为rownum的值可能会经常变【加入一条数据，那么rownum就+1，讲道理rownum可以是无穷大的，因此不能使用>来进行操作】….

那么Oracle分页的思路是这样子的：

  • 先在子查询中获取前n条记录
  • 由于返回的是多行多列，因此我们可以看做成一张表
  • 那么将查询出来的数据放在from字句的后边
  • 外套的查询可以通过where字句来对子查询出来的数据进行过滤
  • 那么我们就可以查询出想要的数据了…

公式：

  • Mysql从(currentPage-1)*lineSize开始取数据，取lineSize条数据
  • Oracle先获取currentPage*lineSize条数据，从(currentPage-1)*lineSize开始取数据

语法

批量操作

我们查询出来的数据可看成是一张表，那么我们在插入数据的时候，可以根据查询出来的数据进行插入…这就可以看成是批量操作…

值得注意的是，如果没有指定插入哪些字段的话，那么查询出来的全部字段均会插入表中..

1
2
3
4
5
将xxx_emp表中所有20号部门的员工，复制到emp表中，批量插入，insert into 表名 select ...语法
insert into emp
select * 
from xxx_emp
where deptno=20;

删除操作对比

我们的删除语法有三种：

  • delete from
  • truncate from
  • drop from

我们来对比一下他们的区别：

drop table

  • 1)属于DDL
  • 2)不可回滚
  • 3)不可带where
  • 4)表内容和结构删除
  • 5)删除速度快

truncate table

  • 1)属于DDL
  • 2)不可回滚
  • 3)不可带where
  • 4)表内容删除
  • 5)删除速度快

delete from

  • 1)属于DML
  • 2)可回滚
  • 3)可带where
  • 4)表结构在，表内容要看where执行的情况
  • 5)删除速度慢,需要逐行删除

访问其他用户的对象

我们只要在访问表的时候，指定具体的用户.数据库表就行了，但是呢，还要看看该用户有没有权限查询别的用户的数据表，于是就需要赋予权限了…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
声明：scott或hr叫用户名/方案名/空间名
      scott--tiger
      hr-----lion
      
查询当前用户是谁
show user;

查询scott自己表空间下的所有对象时，可加，或不加用户名select * from emp;
select * from emp;
或
select * from scott.emp;

以sysdba身份解锁hr普通帐户
alter user hr account unlock;

以sysdba身份设置hr普通帐户的密码
alter user hr identified by lion;

当scott查询hr表空间下的所有表时，必须得加用户名
select * from hr.jobs;

在默认情况下，每个用户只能查询自已空间下的对象的权限，不能查询其它用户空间下的对象

以sysdba身份角色，授予scott用户查询所有用户空间下的对象权限
grant select any table to scott;

以sysdba身份，撤销scott用户查询所有用户空间下的对象权限
revoke select any table from scott;

scott自已查看自己所拥有的权限
select * from user_sys_privs;

从scott用户空间导航到sysdba用户空间
conn / as sysdba;

从sysdba用户空间导航到scott用户空间
conn scott/tiger;

从scott用户空间导航到hr用户空间
conn hr/lion;

查询hr用户空间中的所有对象
select * from tab;

从hr用户空间导航到scott用户空间
conn scott/tiger;

在scott用户空间下，查询hr用户空间下的jobs表，必须加上hr用户空间名
select * from hr.jobs;

视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
基于emp表所有列，创建视图emp_view_1，create view 视图名 as select对一张或多张基表的查询
create view emp_view_1
as
select * from emp;

默认情况下，普通用户无权创建视图，得让sysdba为你分配creare view的权限 

以sysdba身份，授权scott用户create view权限
grant create view to scott;

以sysdba身份，撤销scott用户create view权限
revoke create view from scott;

基于emp表指定列，创建视图emp_view_2，该视图包含编号/姓名/工资/年薪/年收入（查询中使用列别名）
create view emp_view_2
as
select empno "编号",ename "姓名",sal "工资",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;

基于emp表指定列，创建视图emp_view_3(a,b,c,d,e)，包含编号/姓名/工资/年薪/年收入（视图中使用列名）
create view emp_view_3(a,b,c,d,e)
as
select empno "编号",ename "姓名",sal "工资",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;

查询emp_view_3创建视图的结构
desc emp_view_3;

修改emp_view_3(id,name,salary,annual,income)视图，create or replace view 视图名 as 子查询
create or replace view emp_view_3(id,name,salary,annual,income)
as
select empno "编号",ename "姓名",sal "工资",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;

查询emp表，求出各部门的最低工资，最高工资，平均工资
select min(sal),max(sal),round(avg(sal),0),deptno
from emp
group by deptno;

创建视图emp_view_4，视图中包含各部门的最低工资，最高工资，平均工资
create or replace view emp_view_4
as
select deptno "部门号",min(sal) "最低工资",max(sal) "最高工资",round(avg(sal),0) "平均工资"
from emp
group by deptno;

创建视图emp_view_5，视图中包含员工编号，姓名，工资，部门名，工资等级
create or replace view emp_view_5
as
select e.empno "编号",e.ename "姓名",e.sal "工资",d.dname "部门名",s.grade "工资等级"
from emp e,dept d,salgrade s
where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal);

删除视图emp_view_1中的7788号员工的记录，使用delete操作，会影响基表吗
delete from emp_view_1 where empno=7788;写法正确，会影响基表

修改emp_view_1为只读视图【with read only】，再执行上述delete操作，还行吗？
create or replace view emp_view_1
as
select * from emp
with read only;
不能进行delete操作了

删除视图中的【某条】记录会影响基表吗？
会影响基表

将【整个】视图删除，会影响表吗？
不会影响基表

删除视图，会进入回收站吗？
不会进入回收站

删除基表会影响视图吗？
会影响视图

闪回基表后,视图有影响吗？
视图又可以正常工作了

序列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

为emp表的empno字段，创建序列emp_empno_seq，create sequence 序列名
create sequence emp_empno_seq;

删除序列emp_empno_seq，drop sequence 序列名
drop sequence emp_empno_seq;

查询emp_empno_seq序列的当前值currval和下一个值nextval，第一次使用序列时，必须选用：序列名.nextval
select emp_empno_seq.nextval from dual;
select emp_empno_seq.currval from dual;

使用序列，向emp表插入记录，empno字段使用序列值
insert into emp(empno) values(emp_empno_seq.nextval);
insert into emp(empno) values(emp_empno_seq.nextval);
insert into emp(empno) values(emp_empno_seq.nextval);

修改emp_empno_seq序列的increment by属性为20，默认start with是1，alter sequence 序列名
alter sequence emp_empno_seq
increment by 20;

修改修改emp_empno_seq序列的的increment by属性为5
alter sequence emp_empno_seq
increment by 5;

修改emp_empno_seq序列的start with属性，行吗
alter sequence emp_empno_seq
start with 100;

有了序列后，还能为主健手工设置值吗？
insert into emp(empno) values(9999);
insert into emp(empno) values(7900);

删除表，会影响序列吗？
你无法做insert操作,表真正亡，序列亡

删除序列，会影响表吗？

不会


在hibernate中，如果是访问oracle数据库服务器，那么User.hbm.xml映射文件中关于<id>标签如何配置呢？
<id name="id" column="id">
   <generator class="increment/identity/uuid/【sequence】/【native】"/>
</id>

索引

（1）是一种快速查询表中内容的机制，类似于新华字典的目录
（2）运用在表中某个/些字段上，但存储时，独立于表之外

为什么要用索引

  • （1）通过指针加速Oracle服务器的查询速度
  • （2）通过rowid快速定位数据的方法，减少磁盘I/O
    • rowid是oracle中唯一确定每张表不同记录的唯一身份证

rowid特点

  1. 位于每个表中，但表面上看不见，例如：desc emp是看不见的
  2. 只有在select中，显示写出rowid，方可看见
  3. 它与每个表绑定在一起，表亡，该表的rowid亡，二张表rownum可以相同，但rowid必须是唯一的
  4. rowid是18位大小写加数字混杂体，唯一表代该条记录在DBF文件中的位置
  5. rowid可以参与=/like比较时，用’’单引号将rowid的值包起来，且区分大小写
  6. rowid是联系表与DBF文件的桥梁

索引特点

  1. 索引一旦建立, Oracle管理系统会对其进行自动维护, 而且由Oracle管理系统决定何时使用索引
  2. 用户不用在查询语句中指定使用哪个索引
  3. 在定义primary key或unique约束后系统自动在相应的列上创建索引
  4. 用户也能按自己的需求，对指定单个字段或多个字段，添加索引

需要注意的是：Oracle是自动帮我们管理索引的，并且如果我们指定了primary key或者unique约束，系统会自动在对应的列上创建索引..

什么时候【要】创建索引

（1）表经常进行 SELECT 操作

（2）表很大(记录超多)，记录内容分布范围很广

（3）列名经常在 WHERE 子句或连接条件中出现

什么时候【不要】创建索引

（1）表经常进行 INSERT/UPDATE/DELETE 操作

（2）表很小(记录超少)

（3）列名不经常作为连接条件或出现在 WHERE 子句中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
为emp表的empno单个字段，创建索引emp_empno_idx，叫单列索引，create index 索引名 on 表名(字段,...)
create index emp_empno_idx
on emp(empno);

为emp表的ename,job多个字段，创建索引emp_ename_job_idx，多列索引/联合索引
create index emp_ename_job_idx 
on emp(ename,job);
如果在where中只出现job不使用索引
如果在where中只出现ename使用索引
我们提倡同时出现ename和job

注意：索引创建后，只有查询表有关，和其它（insert/update/delete）无关,解决速度问题

删除emp_empno_idx和emp_ename_job_idx索引，drop index 索引名
drop index emp_empno_idx;
drop index emp_ename_job_idx;

权限和用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
一）用户
Oracle中的用户分为二大类
1）Oracle数据库服务器创建时，由系统自动创建的用户，叫系统用户，如sys。
2）利用系统用户创建的用户，叫普通用户，如scott,hr，c##tiger,zhaojun,...

》用sys登录，查询当前Oracle数据库服务器中已有用户的名字和状态
  username表示登录名
  expired&locked表示帐号过期和锁定
  open表示帐号现在可用
  sqlplus / as sysdba;
  col username for a30;
  col account_status for a30;
  set pagesize 100;
  select username,account_status from dba_users;
  
  查询Oracle中有哪些用户
  select * from all_users;



二）创建与删除普通用户
可以在Oracle中创建新的普通用户，创建普通用户命令是：create user，在创建普通用户的同时，应该为其分配一个具体的表空间，通常叫users。

》用sys登录，查询Oracle中有哪些可用存储空间，所有普通用户默认为users存储空间
  select * from v$tablespace;

》用sys登录，创建普通用户c##tiger，密码为abc，默认使用users存储空间，即对应硬盘上的一个DBF二进制文件
  sqlplus / as sysdba;
  create user c##tiger identified by abc default tablespace users;

》用sys登录，为c##tiger分配users空间无限制使用，即数据库中DBF文件可以无限增加，一个DBF文件不够，会创建第二个DBF文件
  sqlplus / as sysdba;
  alter user c##tiger quota unlimited on users;

》用c##tiger登录，能进orcl数据库吗？
  sqlplus c##tiger/abc
  进不去orcl数据库

》用sys登录，删除普通用户c##tiger
  sqlplus / as sysdba;
  drop user c##tiger cascade;



三）了解系统用户
sys是Oracle中一个重要的系统用户，sys是Oracle中最高权限用户，其角色为SYSDBA（系统管理员）
sqlplus / as sysdba



四）权限
权限的最终作用于用户。即所有用户在数据库内的操作对象和可执行的动作都是受到限制的。
Oracle中权限分为二大类：
1）系统权限
2）对象权限



五）系统权限
针对数据库中特定操作的许可，例如：让c##tiger能登录到orcl数据库，能在orcl数据库中创建表

》用sys登录，获取系统权限的相关信息，例如：select any table表示针对所有表的select权限
  sqlplus / as sysdba;
  select distinct privilege from dba_sys_privs;

》用sys登录，为c##tiger分配create session与数据库建立会话的权限，即允许该用户登录
  sqlplus / as sysdba;
  grant create session to c##tiger;

》用c##tiger登录，能进orcl数据库吗？
  sqlplus c##tiger/abc
  能进去orcl数据库

》用c##tiger登录，创建一张tiger的表，能创建吗？
  sqlplus c##tiger/abc
  create table tiger(
    name varchar2(20)
  );
  这时c##tiger没有权限创建表

》用sys登录，为c##tiger分配create table权限，即允许创建表
  sqlplus / as sysdba;
  grant create table to c##tiger;

》用c##tiger登录，创建一张tiger的表，能创建吗？
  sqlplus c##tiger/abc
  create table tiger(
    name varchar2(20)
  );
  可以创建c##tiger表

》用sys登录，查询c##tiger所拥有的系统权限
  sqlplus / as sysdba;
  select grantee,privilege from dba_sys_privs where lower(grantee) = 'c##tiger';
  grantee表示普通用户名
  privilege权限名  

》用sys登录，撤销c##tiger的create table权限
  sqlplus / as sysdba;
  revoke create table from c##tiger;



六）对象权限
用户对已有对象的操作权限，包括：
1）select可用于表，视图和序列
2）insert向表或视图中插入新的记录
3）update更新表中数据
4）delete删除表中数据
5）execute函数，过程的执行
6）index为表创建索引
7）references为表创建外健
8）alter修改表或者序列的属性

》用sys登录，查询c##tiger所拥有的对象权限
  sqlplus / as sysdba;
  col grantee for a10;
  col table_name for a10;
  col privilege for a20;
  select grantee,table_name,privilege from dba_tab_privs where lower(grantee) = 'c##tiger';

》用sys登录，为c##tiger分配对tiger表的所有权限，即增删改查操作
  sqlplus / as sysdba;
  grant all on c##tiger.tiger to c##tiger;
  注意：c##tiger表示空间名
        tiger表示该空间下的表名
  C##TIGER   TIGER      FLASHBACK
  C##TIGER   TIGER      DEBUG
  C##TIGER   TIGER      QUERY REWRITE
  C##TIGER   TIGER      ON COMMIT REFRESH
  C##TIGER   TIGER      REFERENCES
  C##TIGER   TIGER      UPDATE
  C##TIGER   TIGER      SELECT
  C##TIGER   TIGER      INSERT
  C##TIGER   TIGER      INDEX
  C##TIGER   TIGER      DELETE
  C##TIGER   TIGER      ALTER

》用c##tiger登录，对tiger表进行增删改查操作
  sqlplus c##tiger/abc;
  insert into tiger(name) values('AA');
  update tiger set name = 'BB';
  delete from tiger where rownum = 1;
  select * from tiger;


PLSQL学习

PLSQL是Oracle对SQL99的一种扩展

  • 是专用于Oracle服务器，在SQL基础之上，添加了一些过程化控制语句，叫PLSQL
  • 过程化包括有：类型定义，判断，循环，游标，异常或例外处理。。。
  • PLSQL强调过程

为什么要用PLSQL

  • 因为SQL是第四代命令式语言，无法显示处理过程化的业务，所以得用一个过程化程序设计语言来弥补SQL的不足之处，
  • SQL和PLSQL不是替代关系，是弥补关系

语法

参考文章

面试题

参考文章

有关mysql命令

面试前必须知道的MySQL命令【explain】 - SegmentFault 思否