楠槡

Oracle 学习笔记

2017-09-16

前言

本贴内容纪录Oracle课程中的学习笔记,和Oracle的课后作业,以及数据库相关课程的学习笔记,笔记部分使用实例代码记录,不记详细语法。

用户管理

1.如果新安装数据库后,忘了oracle用户的密码,可以用哪些方法重新设置密码?(从系统用户sys和普通用户scoot两个方面去考虑),针对两种不同类型的用户,给出解决问题的具体思路和代码。

分两种情况考虑:

一种是忘记普通用户(如scoot)

用SYS (或SYSTEM)用户登录: CONN SYS/PASS_WORD AS SYSDBA;
使用修改语句:ALTER USER user_name IDENTIFIED BY newpass;
注意:密码不能纯数字或以数字开头,否则会出现ORA-00988: 口令缺失或无效

另一种是管理员用户(SYS或System)

可以使用ORAPWD.EXE 工具修改密码。

开始菜单->运行->输入CMD,打开命令提示符窗口,输入如下命令:

1
orapwd file=D:\oracle\product\10.2.0\db_1\database\pwdctcsys.ora password=Wut1234

或者使用系统模式登陆
cmd中输入 sqlplus /nolog
使用系统登入
conn / as sysdba;
输入
alter user sys identified by Wut1234;


用户以及权限管理

创建用户

1
CREATE USER user1 IDENTIFIED BY user1;

授权 WITH ADMIN OPTION 传递

1
GRANT CREATE SESSION,CREATE TABLE TO user1 WITH ADMIN OPTION;

收回授权

1
REVOKE CREATE SESSION,CREATE TABLE FROM user1;

将scott模式下的emp表的SELECT,UPDATE,INSERT权限授予user1用户(带 WITH GRANT OPTION 参数)。

1
2
CONN scott/tiger
GRANT SELECT,INSERT,DELETE ON emp TO user1 WITH GRANT OPTION;

回收用户user1授予的scott模式下的emp表的SELECT,UPDATE,INSERT权限。

1
2
CONN scott/tiger@orcl
REVOKE SELECT,UPDATE,INSERT ON emp FROM user1;

表空间

建表空间

1
2
3
4
CREATE TABLESPACE datastudent
DATAFILE '%ORACLE_HOME%\database\datastudent.dbf'
SIZE 100M REUSE
UNIFORM SIZE 128K;

建临时表空间

1
2
3
4
CREATE TEMPORARY TABLESPACE tempstudent
TEMPFILE '%ORACLE_HOME%\database\tempstudent.dbf'
SIZE 120M REUSE
UNIFORM SIZE 128K;

建用户并指定表空间

1
CREATE USER class_mgr IDENTIFIED BY manager DEFAULT TABLESPACE datastudent TEMPORARY TABLESPACE tempstudent;

授权用户指定权限

1
2
GRANT SELECT ON scott.emp TO class_mgr;
GRANT UPDATE(deptno,dname) ON scott.dept to class_mgr;

手动修改表空间大小

1
2
3
ALTER TABLESPACE data_ts1
ADD DATAFILE
'%ORACLE_HOME%\database\data_ts2.dbf' SIZE|RESIZE 10M; //增加/减少

开启/关闭自动扩容

1
2
3
ALTER DATABASE DATAFILE
'%ORACLE_HOME%\database\data_ts2.dbf'
AUTOEXTEND ON|OFF NEXT 5M MAXSIZE 100M; //开关

删除表空间-删除临时

1
2
3
ALTER TABLESPACE temp_ts1
DROP TEMPFILE
'%ORACLE_HOME%\database\temp_ts2.dbf';

删除表空间-删除数据文件及约束

1
2
3
DROP TABLESPACE temp_ts1
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;

表空间离线/上线

1
ALTER TABLESPACE tablespace_name ONLINE|OFFLINE

离线状态的表空间是不能进行数据访问的,所对应的所有数据文件也都处于脱机状态。

表空间读写状态

1
2
ALTER TABLESPACE tbs_name
READ ONLY|READ WRITE //可读 读写

Oracle数据类型

常用数据类型:

  • VARCHAR2:可变长字符串类型,≤4KB
    DATE :固定长度(7B)的日期型。注意存储与使用格式
  • TIMESTAMP:时间戳类型,可更精确的存储与表示时间
  • NUMBER:数字型,可存放实型和整型
  • FLOAT:浮点型,可存放实型

不常用数据类型:

  • BLOB 大二进制对象类型,≤4GB
    CLOB 大字符串对象类型,≤4GB
  • NCLOB:多字节字符集的CLOB,≤4GB
  • BFILE:外部二进制文件类型,只读,大小与OS有关
  • ROWID:存储记录行的物理地址(2进制表示),固定为10个字节

特殊类型Date(注意)

用来存放日期时间类型数据,用7个字节分别描述年、月、日、时、分、秒。
语法格式:

date_field DATE;

缺省格式为DD-MON-YY,分别对应日、月、年。

例17-JUN-02。(月份的表达要用英文单词的缩写格式)。

日期的格式可以设置为中文格式,例如17-六月-2002。

1
2
3
to_date('2017-03-02','yyyy-mm-dd')
SQL> select to_date('2017-03-02','yyyy-mm-dd') from dual;

Oracle衍生Date类型-TIMESTAMP 不展开赘述

表管理

表是真正存储各种各样数据的对象,由行和列组成。行有时也称为记录,列有时也称为字段或域。设计数据库时,要决定它包括哪些表,每个表中包含哪些列,每列的数据类型等。

在表中创建列时,必须为其指定数据类型,列的数据类型决定了数据的取值、范围和存储格式。

建表

1
2
3
4
5
6
7
8
9
Create Table XS
(XH VARCHAR2 (10) NOT NULL,
XM VARCHAR2 (20) NOT NULL,
ZYM VARCHAR2 (20),
XB VARCHAR2(2) NOT NULL,
CSSJ Date NOT NULL,
ZXF Number(2) DEFAULT 01,
BZ Varchar(200)
);

使用查询建表

为了保存原始数据以便于恢复或是得到一个与源表一样结构的表,可通过子查询创建表。

1
2
CREATE TABLE XS_JSJ
AS SELECT * FROM XS WHERE ZYM='计算机' ;

例为计算机专业的学生创建一个备份表XS_JSJ。

表管理

主要为对列及其属性的修改

增加列

1
2
3
4
ALTER TABLE XS ADD
( JXJ NUMBER(1),
DJSM VARCHAR2(40) DEFAULT '奖金1000元'
);

修改列

1
2
ALTER TABLE XS
MODIFY ( DJSM DEFAULT '奖金800元' );

删除列

1
2
ALTER TABLE XS
DROP COLUMN DJSM;

修改表名

1
Alter Table XS Rename To XSCopy;


1
2
Alter Table XS
RENAME XS TO XSCopy;

修改字段名

1
2
Alter Table xscopy
Rename Column BZ To BZSM;

删除表

1
DROP TABLE XS;

表的删除闪回

1
FLASHBACK TABLE XS TO BEFORE DROP;

注意:管理员sys是没有回收站,请尽量不要在sys用户下创建表

语法:FLASHBACK TABLE 表名TO BEFORE DROP;

此命令可用于恢复误删除的表、视图和索引。

彻底删除表

无法闪回

1
DROP TABLE XS PURGE;

临时表

临时表的特点:

  • 多用户操作的独立性:对于使用同一张临时表的不同用户,ORACLE都会分配一个独立的临时表,这样就避免了多个用户在对同一张临时表操作时发生交叉,从而保证了多个用户操作的并发性和独立性;
  • 数据的临时性:既然是临时表,顾名思义,存放在该表中的数据是临时性的。ORACLE根据你创建临时表时指定的参数(On Commit Delete Rows / On Commit Preserve Rows),自动将数据TRUNCATE掉。

会话级临时表

会话级的临时表因为这这个临时表中的数据和你的当前会话有关系,当你当前SESSION不退出的情况下,临时表中的数据就还存在,而当你退出当前SESSION的时候,临时表中的数据就全部没有了,当然这个时候你如果以另外一个SESSION登陆的时候是看不到另外一个SESSION中插入到临时表中的数据的。即两个不同的SESSION所插入的数据是互不相干的。当某一个SESSION退出之后临时表中的数据就被截断(truncate table,即数据清空)了。会话级的临时表创建方法:

1
2
Create Global Temporary Table Table_Name
(Col1 Type1,Col2 Type2...) On Commit Preserve Rows;

事务级临时表

事务级临时表是指该临时表与事务相关,当进行事务提交或者事务回滚的时候,临时表中的数据将自行被截断,其他的内容和会话级的临时表的一致(包括退出SESSION的时候,事务级的临时表也会被自动截断)。事务级临时表的创建方法:

1
2
Create Global Temporary Table Table_Name
(Col1 Type1,Col2 Type2...) On Commit Delete Rows;

两中类型临时表的区别

会话级临时表采用on commit preserve rows;而事务级则采用on commit delete rows;用法上,会话级别只有当会话结束临时表中的数据才会被截断,而且事务级临时表则不管是commit、rollback或者是会话结束,临时表中的数据都将被截断

约束

主键约束,外键约束,特殊约束等

手机号约束

1
2
ALTER TABLE EMPLOYEES
ADD ( CONSTRAINT "CH_PHONE_CHECK" CHECK (phonenumber between '000000000000' and '999999999999') ) ;

索引

在Oracle 中,索引是为了加速对表中元组的检索而创建的一种分散存储结构;
索引是对表而建立的,由除存放表的数据页面以外的索引页面组成,独立于被索引的表。

注意:索引增加了查询的速度,但是会降低插入,修改,删除的速度,故县插入数据,后创建索引。

  • 索引中存储了记录的关键字和ROWID
  • 索引在逻辑上和物理上都与相关表的数据无关
  • 索引需要单独的存储空间。否则使用表所在表空间
  • 创建或删除一个索引不会影响基于数据表的数据库应用或其他索引
  • 创建索引后一般会增加数据更新操作的执行时间

B-树索引(B-tree index)

B*树索引存储结构类似书的索引结构,有分支和叶两种类型的存储数据块(叶子块数据是排序的,从左向右递增; 在分支块和根块中放的是索引的范围 ),分支块相当于书的大目录,叶块相当于索引到的具体的书页。

对于取值范围很大的列应当创建B树索引。

位图索引

位图索引储存主要用来节省空间,减少ORACLE对数据块的访问,它采用位图偏移方式来与表的行ID号对应。( 每一个BIT(值是1或0)对应着一个ROWID。是1表示着BIT对应的ROWID有值;)

对于取值范围很小的列应当创建位图索引。

条件

  1. 索引的表或簇必须在自己的模式中;
  2. 必须在要索引的表上具有INDEX权限;
  3. 必须具有CREATE ANY INDEX权限。

创建索引

1
2
3
4
CONN scott/tiger@orcl
CREATE INDEX kc_kcm_index
ON kc(kcm DESC)
TABLESPACE my_index;

默认情况下,索引中数据按升序(ASC)排列

视图

视图(view),也称虚表, 不占用物理空间,这个也是相对概念,因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候, 只是重新执行SQL.

视图是从一个或多个实际表中获得的,这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。

视图的定义存在数据库中,与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。

视图看上去非常象数据库的物理表,对它的操作同任何其它的表一样。当通过视图修改数据时,实际上是在改变基表中的数据;相反地,基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因,有些Oracle视图可以修改对应的基表,有些则不能(仅仅能查询)。

需要给予视图权限

1
2
3
-- 给予创建视图权限
conn system
GRANT create view TO McswAdmin;

创建视图

1
2
3
4
5
create or replace view vw_Users_Buy
as
select "Users_Id","Goods_Id","Tr_Num"
from Transaction_Log
order by "Tr_Id";

PL/SQL程序块

PL/SQL程序块是PL/SQL程序的基本单元,按照指定的方式,进行定义的一段程序。

概念等内容参考 PL/Sql语句块

示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--序列
create sequence Users_sequence
increment by 1
start with 10000020
nomaxvalue
nocycle
nocache
--触发器
CREATE TRIGGER Users_idadd
BEFORE INSERT ON Users
FOR EACH ROW
WHEN(NEW."Users_Id" is null) --当输入ID为空时启动触发器
BEGIN
select Users_sequence.nextval into :new."Users_Id" from sys.dual;
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--创建good_check函数,检查商品的对应游戏物品ID
CREATE OR REPLACE FUNCTION good_check
(f_goodsid Goods."Goods_Id"%type
)
RETURN Goods."Goods_Game_id"%TYPE
AS
v_g_rname Goods."Goods_Game_id"%TYPE;
BEGIN
select "Goods_Game_id" into v_g_rname FROM Goods
where "Goods_Id"=f_goodsid;
RETURN v_g_rname ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到对应游戏物品!');
END good_check;
--调用good_check输入Goods_Id和Goods_Game_id检查商品的对应游戏物品ID
select good_check('ces01') from dual;
Tags: Other
使用支付宝打赏
使用微信打赏

若你觉得我的文章对您有帮助,点击上方按钮请我喝杯咖啡☕

若文章中存在问题,或您有任何意见和疑问,均可与我联系

扫描二维码,分享此文章 📱