前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Oracle 学习笔记

Oracle 学习笔记

作者头像
李郑
发布2018-03-01 16:45:19
1.3K0
发布2018-03-01 16:45:19
举报
文章被收录于专栏:漫漫全栈路

前言

本贴内容纪录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,打开命令提示符窗口,输入如下命令:

代码语言:javascript
复制
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;


用户以及权限管理

创建用户

代码语言:javascript
复制
CREATE USER user1 IDENTIFIED BY user1;

授权 WITH ADMIN OPTION 传递

代码语言:javascript
复制
GRANT CREATE SESSION,CREATE TABLE TO user1 WITH ADMIN OPTION;

收回授权

代码语言:javascript
复制
REVOKE CREATE SESSION,CREATE TABLE FROM user1;

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

代码语言:javascript
复制
CONN scott/tiger
GRANT SELECT,INSERT,DELETE ON emp TO user1 WITH GRANT OPTION;

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

代码语言:javascript
复制
CONN scott/tiger@orcl
REVOKE SELECT,UPDATE,INSERT ON emp FROM user1;

表空间

建表空间

代码语言:javascript
复制
CREATE TABLESPACE datastudent
  DATAFILE '%ORACLE_HOME%\database\datastudent.dbf'
    SIZE 100M REUSE
  UNIFORM SIZE 128K;

建临时表空间

代码语言:javascript
复制
CREATE TEMPORARY TABLESPACE tempstudent
  TEMPFILE '%ORACLE_HOME%\database\tempstudent.dbf' 
  SIZE 120M REUSE
  UNIFORM SIZE 128K;

建用户并指定表空间

代码语言:javascript
复制
CREATE USER class_mgr IDENTIFIED BY manager DEFAULT TABLESPACE datastudent TEMPORARY TABLESPACE tempstudent;

授权用户指定权限

代码语言:javascript
复制
GRANT SELECT ON scott.emp TO class_mgr;
GRANT UPDATE(deptno,dname) ON scott.dept to class_mgr;

手动修改表空间大小

代码语言:javascript
复制
ALTER TABLESPACE data_ts1 
ADD DATAFILE
'%ORACLE_HOME%\database\data_ts2.dbf' SIZE|RESIZE 10M; //增加/减少

开启/关闭自动扩容

代码语言:javascript
复制
ALTER DATABASE DATAFILE        
'%ORACLE_HOME%\database\data_ts2.dbf' 
AUTOEXTEND ON|OFF NEXT 5M MAXSIZE 100M;  //开关

删除表空间-删除临时

代码语言:javascript
复制
ALTER TABLESPACE temp_ts1 
DROP TEMPFILE 
'%ORACLE_HOME%\database\temp_ts2.dbf';

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

代码语言:javascript
复制
DROP TABLESPACE temp_ts1 
  INCLUDING CONTENTS AND DATAFILES
  CASCADE CONSTRAINTS;

表空间离线/上线

代码语言:javascript
复制
ALTER TABLESPACE tablespace_name ONLINE|OFFLINE

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

表空间读写状态

代码语言:javascript
复制
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。

代码语言:javascript
复制
to_date('2017-03-02','yyyy-mm-dd')

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

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

表管理

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

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

建表

代码语言:javascript
复制
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)
);

使用查询建表

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

代码语言:javascript
复制
CREATE TABLE XS_JSJ
  AS SELECT * FROM XS WHERE ZYM='计算机' ;

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

表管理

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

增加列
代码语言:javascript
复制
ALTER TABLE XS  ADD
( JXJ NUMBER(1),
DJSM VARCHAR2(40) DEFAULT '奖金1000元'
);
修改列
代码语言:javascript
复制
ALTER TABLE XS
	MODIFY ( DJSM DEFAULT '奖金800元' );
删除列
代码语言:javascript
复制
ALTER TABLE XS
 DROP COLUMN DJSM;
修改表名
代码语言:javascript
复制
Alter Table XS   Rename To XSCopy;

代码语言:javascript
复制
Alter Table XS 
RENAME XS TO XSCopy;
修改字段名
代码语言:javascript
复制
Alter Table xscopy
Rename Column BZ To BZSM;
删除表
代码语言:javascript
复制
DROP TABLE XS;
表的删除闪回
代码语言:javascript
复制
FLASHBACK TABLE XS TO BEFORE DROP;

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

语法:FLASHBACK TABLE 表名TO BEFORE DROP;

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

彻底删除表

无法闪回

代码语言:javascript
复制
DROP TABLE XS PURGE;

临时表

临时表的特点:

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

会话级临时表

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

代码语言:javascript
复制
Create Global Temporary Table Table_Name
(Col1 Type1,Col2 Type2...) On Commit Preserve Rows;

事务级临时表

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

代码语言:javascript
复制
Create Global Temporary Table Table_Name
(Col1 Type1,Col2 Type2...) On Commit Delete Rows;

两中类型临时表的区别

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

约束

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

手机号约束

代码语言:javascript
复制
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权限。

创建索引

代码语言:javascript
复制
CONN scott/tiger@orcl
CREATE INDEX kc_kcm_index
    ON kc(kcm DESC)
    TABLESPACE my_index;

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

视图

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

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

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

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

需要给予视图权限

代码语言:javascript
复制
-- 给予创建视图权限
conn system
GRANT create view TO McswAdmin;

创建视图

代码语言:javascript
复制
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语句块

示例:

代码语言:javascript
复制

--序列
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;
代码语言:javascript
复制
--创建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;
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2017-09-16,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 前言
  • 用户管理
    • 一种是忘记普通用户(如scoot)
      • 另一种是管理员用户(SYS或System)
      • 用户以及权限管理
        • 创建用户
          • 授权 WITH ADMIN OPTION 传递
            • 收回授权
              • 将scott模式下的emp表的SELECT,UPDATE,INSERT权限授予user1用户(带 WITH GRANT OPTION 参数)。
              • 回收用户user1授予的scott模式下的emp表的SELECT,UPDATE,INSERT权限。
              • 表空间
                • 建表空间
                  • 建临时表空间
                    • 建用户并指定表空间
                      • 授权用户指定权限
                        • 手动修改表空间大小
                          • 开启/关闭自动扩容
                            • 删除表空间-删除临时
                              • 删除表空间-删除数据文件及约束
                                • 表空间离线/上线
                                  • 表空间读写状态
                                  • Oracle数据类型
                                    • 特殊类型Date(注意)
                                    • 表管理
                                      • 建表
                                        • 使用查询建表
                                          • 表管理
                                            • 增加列
                                            • 修改列
                                            • 删除列
                                            • 修改表名
                                            • 修改字段名
                                            • 删除表
                                            • 表的删除闪回
                                            • 彻底删除表
                                        • 临时表
                                          • 会话级临时表
                                            • 事务级临时表
                                              • 两中类型临时表的区别
                                              • 约束
                                                • 手机号约束
                                                • 索引
                                                  • B-树索引(B-tree index)
                                                    • 位图索引
                                                      • 条件
                                                        • 创建索引
                                                        • 视图
                                                          • 需要给予视图权限
                                                            • 创建视图
                                                            • PL/SQL程序块
                                                            相关产品与服务
                                                            数据库
                                                            云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
                                                            领券
                                                            问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档