Oracle 学习笔记

前言

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

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;


用户以及权限管理

创建用户

CREATE USER user1 IDENTIFIED BY user1;

授权 WITH ADMIN OPTION 传递

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

收回授权

REVOKE CREATE SESSION,CREATE TABLE FROM user1;

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

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

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

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

表空间

建表空间

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

建临时表空间

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

建用户并指定表空间

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

授权用户指定权限

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

手动修改表空间大小

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

开启/关闭自动扩容

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

删除表空间-删除临时

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

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

DROP TABLESPACE temp_ts1 
  INCLUDING CONTENTS AND DATAFILES
  CASCADE CONSTRAINTS;

表空间离线/上线

ALTER TABLESPACE tablespace_name ONLINE|OFFLINE

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

表空间读写状态

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。

to_date('2017-03-02','yyyy-mm-dd')

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

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

表管理

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

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

建表

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)
);

使用查询建表

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

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

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

表管理

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

增加列

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

修改列

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

删除列

ALTER TABLE XS
 DROP COLUMN DJSM;

修改表名

Alter Table XS   Rename To XSCopy;

Alter Table XS 
RENAME XS TO XSCopy;

修改字段名

Alter Table xscopy
Rename Column BZ To BZSM;

删除表

DROP TABLE XS;

表的删除闪回

FLASHBACK TABLE XS TO BEFORE DROP;

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

语法:FLASHBACK TABLE 表名TO BEFORE DROP;

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

彻底删除表

无法闪回

DROP TABLE XS PURGE;

临时表

临时表的特点:

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

会话级临时表

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

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

事务级临时表

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

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

两中类型临时表的区别

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

约束

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

手机号约束

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权限。

创建索引

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

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

视图

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

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

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

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

需要给予视图权限

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

创建视图

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语句块

示例:

--序列
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;
--创建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;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏吴伟祥

百万级数据库优化方案 转

1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

1092
来自专栏数据库

oracle常用命令大全

一、Oracle数据库实例、用户、目录及session会话查看: 1、ORACLE SID查看设置 查看SID、用户名 $ env|grep SID 、sele...

4847
来自专栏乐沙弥的世界

Oracle AWR 阙值影响历史执行计划

      最近有网友提到为什么在dba_hist_sql_plan中无法查看到sql语句的历史执行计划,对于这个问题是由于缺省情况下,Oracle 设定的阙值...

792
来自专栏杨建荣的学习笔记

手工创建/删除数据库的步骤

今天和大家分享下数据库的创建和删除的步骤,里面有很多细节需要大家考虑。创建数据库不只是一个create database语句。删除数据库 drop databa...

3236
来自专栏程序猿

Mssql高级注入笔记

暴库特殊技巧::%5c='\' 或者把/和\ 修改%5提交 and 0<>(select count(*) from master.dbo.sysdatabas...

3847
来自专栏琯琯博客

MySQL 查询优化

24711
来自专栏杨建荣的学习笔记

MySQL Profile在5.7的简单测试(r10笔记第50天)

MySQL Profile对于分析执行计划的开销来说,还是有一定的帮助,至少在分析一些性能问题的时候有很多的参考依据。 我在5.6, 5.7版本中进行了测试,没...

3275
来自专栏沈唁志

谈谈在SQL语句中的优化技巧

1884
来自专栏谢庆玲的专栏

MySQL 查询分析

本文主要由一个案例引发对 MySQL 性能问题的思考,主要讲述 MySQL 慢查询和 explain 工具这两个定位 MySQL 性能瓶颈的方法。

1.3K1
来自专栏机器学习算法与Python学习

SQL Server常用命令(平时不用别忘了)

SQL Server 2008 在Microsoft的数据平台上发布,可以组织管理任何数据。可以将结构化、半结构化和非结构化文档的数据直接存储到数据库中。可以对...

3037

扫码关注云+社区

领取腾讯云代金券