Oracle 11g 数据库基础笔记

1 Oracle 11g简介

1.1 数据库基本概念

数据库 DB（database)
数据库管理系统 DBMS(DataBase Management System)
数据库管理员 DBA(Database Administrator)

Oracle数据库：相关操作系统文件（即存储在计算机硬盘上的文件）集合，这些文件组织在一起，成为一个逻辑整体，即为Oracle数据库。

Oracle实例：位于物理内存里的数据结构，它由操作系统的多个后台进程和一个共享的内存池所组成，共享的内存池可以被所有进程访问。Oracle 实例 = 进程+进程所使用的内存。

1.2 Oracle的7种服务

该节转自：详解Oracle的7种服务
通常安装Oracle后会在计算机服务里看到7个相关服务：

1. Oracle ORCL VSS Writer Service：
   Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service）能够让存储基础设备（比如磁盘，阵列等）创建高保真的时间点映像，即映射拷贝（shadow copy）。它可以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。（非必须启动）

2. OracleDBConsoleorcl：
   Oracle数据库控制台服务，orcl是Oracle的实例标识，默认的实例为orcl。在运行Enterprise Manager（企业管理器OEM）的时候，需要启动这个服务。（非必须启动）
   这个就是在网页中输入 https：//计算机全名：端口号（1158）/em 的时候，必须开启这个服务，才能够成功进入。

3. OracleJobSchedulerORCL：
   Oracle作业调度（定时器）服务，ORCL是Oracle实例标识。（非必须启动）

4. OracleMTSRecoveryService：
   服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。（非必须启动）

5. OracleOraDb11g_home1ClrAgent：
   Oracle数据库 .NET扩展服务的一部分。 （非必须启动）

6. OracleOraDb11g_home1TNSListener：
   监听器服务，服务只有在数据库需要远程访问的时候才需要。（可为必须启动，也可为非必须启动，详解见3）

7. OracleServiceORCL：
   数据库服务(数据库实例)，是Oracle核心服务该服务，是数据库启动的基础， 只有该服务启动，Oracle数据库才能正常启动。(必须启动)
   对新手来说，要是只用Oracle自带的sql*plus的话，只要启动OracleServiceORCL即可，要是使用PL/SQL Developer等第三方工具的话，OracleOraDb11g_home1TNSListener服务也要开启。OracleDBConsoleorcl是进入基于web的EM必须开启的，其余服务很少用。
   注：ORCL是数据库实例名，默认的数据库是ORCL，你可以创建其他的，即OracleService+数据库名。

1.3 Oracle操作工具

常用的Oracle操作工具有4种：第一种是Oracle自带的sqlplus命令行方式，第二种是Oracle自带的基于浏览器方式，第三种是Sql Developer工具，第四种是PLSQL Developer工具。

1.4 常用的sqlplus命令

connect：切换连接用户
show user：显示当前登录用户
host <dos命令>：执行操作系统命令
spool：导出记录到文本
clean screen：清屏
start d:/test.sql或@ d:/test.sql ：执行文件系统中的sql语句
desc：显示表结构
show error：显示错误信息
exit：退出

2 Oracle的用户、权限、角色及表空间管理

2.1 Oracle的默认用户

Oracle数据库安装好后有三个默认的用户：

• 用户：sys 密码：install时自设
• 用户：system 密码：manager
• 用户：scott 密码：tiger
• sysman
• dbsnmp
  相关介绍参看：Oracle数据库用户sys、system、sysman、scott、dbsnmp及权限分类说明

2.2 sqlplus用户登录

管理员登陆：sqlplus sys/oracle as sysdba
一般用户登录：sqlplus scott/tiger
切换的时候使用conn：conn sqlplus scott/tiger 或 conn sys/oracle as sysdba

2.3 表空间

数据库与表空间：表空间实际上是数据上的逻辑存储结构，可以把表空间理解为在数据库中开辟的一个空间，用于存放我们的数据库对象，一个数据库可以由多个表空间构成。
表空间与数据文件：表空间实际是由一个或多个数据文件构成的，数据文件的位置和大小可以由我们用户自己来定义。我们所创建的一些表啊，一些其他的数据对象都是存放在数据文件里的。那么数据文件是物理存储结构，真正可以看到的，而表空间是逻辑存储结构。

表空间一般分为3类：永久表空间，临时表空间，UNDO表空间
创建永久表空间：create tablespace test1_tablespace datafile 'test1file.dbf' size 10m;
创建临时表空间：create temporary tablespace temptest1_tablespace tempfile 'tempfile1.dbf' size 10m;

2.4 Oracle的用户管理

创建用户语法：

CREATE USER [user_name]
IDENTIFIED BY [password]
DEFAULT TABLESPACE [default tablespace]
TEMPORARY TABLESAPCE [temporary tablespace]

查看创建用户：

SELECT username FROM dba_users;

给创建的用户授权：

GRANT [权限] TO [username];

连接（切换）用户：

CONNECT [username/password];

更改密码：

ALTER USER [username] IDENTIFIED BY [newpassword];

锁定用户：

ALTER USER [username] ACCOUNT LOCK;

删除用户：

DROP USER [username] CASCADE;

加cascade表示连同该用户创建的东西一起删除。

2.5 角色

Oracle角色就是一组权限（或者说权限的集合）。用户可以创建角色，并给角色赋予指定的权限，并将角色赋给相应用户，该用户就拥有了它所拥有的角色的权限。使用角色主要是为了更方便管理和使用权限。

常见的三种角色：

• CONNECT（连接角色）：只可以登录数据库。
• RESOURCE（资源角色）：只可以登录和创建实体。
• DBA（数据库管理员角色）：拥有全部权限，可以创建数据库结构。

创建角色：

CREATE ROLE [角色名];

为角色赋予权限：

GRANT [权限] TO [角色名];

将角色赋予用户：

GRANT [角色名] TO [用户名];

收回用户的角色权限：

REVOKE [角色名] FROM [用户名];

删除创建角色：

DROP ROLE [角色名];

2.6 Oracle用户的权限管理

系统权限：允许用户执行特定的数据库动作，如创建表、创建索引、连接实例等。
对象（实体）权限：允许用户操作一些特定的对象，如读取视图，可更新某些列、执行存储过程等。
查询Oracle所有系统权限：select * from SYSTEM_PRIVILEGE_MAP;

2.6.1 系统权限

常用的系统权限：
   create session – 创建会话
   create sequence – 创建序列
   create table – 创建表
   create user – 创建用户
   alter user – 更改用户
   drop user – 删除用户
   create view – 创建视图

授予用户角色权限：

GRANT {privilege1[, privilege2，...]} TO {user1|role1[, user2|role2, PUBLIC, ...]}

回收用户角色权限：

REVOKE {privilege|role} FROM {username | rolename | PUBLIC}

2.6.2 对象权限

常用的对象权限有：select, update, insert, delete, all （all表示所有权限）

授予对象权限：

GRANT object_priv | ALL[(columns)] ON object TO {user | role | PUBLIC}

-- example
grant select, update, insert on scott.emp to manager2;

回收对象权限：

REVOKE {privilege1, [privilege2, ...] | ALL} ON object FROM {user1[, user2, ...] | role | PUBLIC}

2.7 Oracle用户的表空间管理

与表空间相关数据字典：解释后续补充
    dba_tablespaces
    user_tablespaces
    dba_users
    user_users

设置用户的默认或临时表空间：

ALTER USER username DEFAULT|TEMPORARY TABLESPACE tablespace_name;

修改用户表空间状态：

ALTER TABLESPACE tablespace_name ONLINE|OFFLINE;
-- online 表示联机状态
-- offline 表示脱机状态，即该表空间暂时不让访问

增加数据文件：

ALTER TABLESPACE tablespace_name ADD DATAFILE 'filename.dbf' SIZE xxm;

删除数据文件：

ALTER TABLESPACE tablespace_name DROP DATAFILE 'filename.dbf';

删除表空间：

DROP TABLESPACE tablespace_name[INCLUDING CONTENTS];
-- 添加 INCLUDING CONTENTS 表示连同表空间下的数据文件一并删除

3 Oracle数据库表的管理

3.1 SQL语句类型

DDL(Data Define Language)数据定义语言

create table -- 创建数据库表
create index -- 创建索引
drop table -- 删除数据库表
drop index -- 删除索引
truncate -- 删除表中的所有行
alter table -- 更改表结构，增加、修改、删除列
alter table add constraint -- 在已有的表上增加约束

DML(Data Manipulation Language)数据操作语言

insert -- 添加
update -- 更新
delete -- 删除
select -- 查询（选择）

DCL(Data Control Language)数据控制语言

grant -- 授予权限或角色
revoke -- 回收权限或角色
lock -- 对数据库特定部分进行锁定

TCL(Transaction Control Language)事务控制语言

commit -- 提交
rollback -- 事务处理回退
savepoint -- 设置保存点

3.2 表的创建及约束

3.2.1 创建表

创建表

create table student(
sid number(8,0),
name varchar2(20),
sex char(2),
birthday date,
address varchar2(50)
);

3.2.2 表的约束

常见表的约束有：主键约束（primary key constraint）、唯一性约束（unique constraint）、默认约束（default constraint）、非空约束（not null constraint）、检查约束（check constraint）、外部键约束（foreign key constraint）

主键约束（primary key）：一张表只能有一个主键，主键唯一，非空，可以由多列构成，从而保证数据的完整性。

-- 创建表时添加主键约束
create table student(
sid number(8,0),
name varchar2(20),
sex char(2),
birthday date,
address varchar2(50),
constraint sid_pk primary key(sid)
);

alter table student add constraint sid_pk primary key(sid); -- 修改表时创建主键约束

非空约束（not null）：非空约束表示该字段不能为空，非空约束是列级约束。

create table student(
sid number(8,0),
name varchar2(20) not null, -- 列级约束
sex char(2) constraint nn_sex not null, -- 自命名约束名称
birthday date,
address varchar2(50)
);

唯一性约束（unique）：用于指定一个或多个列的组合值具有唯一性，以在列中输入重复的值。允许有一个空值，一个表中可以有多个唯一性约束，可以把唯一性约束定义在多个列上。

create table student(
sid number(8,0),
name varchar2(20),
sex char(2),
birthday date,
address varchar2(50),
email varchar2(50) unique, -- 列级约束，系统自动生成约束名称
cardid varchar2(18),
constraint uk_cardid unique(cardid) -- 表级约束，自己命名约束名称
);

alter table student add constraint uk_student_cardid unique(cardid); -- 修改表时创建
alter table student disable constraint uk_student_cardid; -- 禁用约束
alter table student drop constraint uk_student_cardid; -- 彻底删除约束

检查约束（check）：检查约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据库数据的完整性。

create table student(
sid number(8,0),
name varchar2(20),
sex char(2) check(sex='男' or sex='女'), -- 列级约束，限制取值
birthday date,
address varchar2(50)
);

create table student(
sid number(8,0),
name varchar2(20),
sex char(2),
birthday date,
address varchar2(50),
constraint ck_sex check(sex='男' or sex='女') -- 表级约束
);

alter table student add constraint ck_sex check(sex='男' or sex='女') -- 在修改表时添加检查约束
alter table student disable constraint ck_sex; -- 禁用删除
alter table student drop constraint ck_sex; -- 彻底删除

外键约束（foreign key）：是用于建立和加强两个表数据之间的链接的一列或多列。外键约束是唯一涉及两个表关系的约束。

-- 列级约束
create table 从表 (
column_name datatype references 主表(column_name) [ON DELETE CASCADE],...
);

-- 表级约束
constraint constraint_name foreign key (column_name) references 主表(column_name) [ON DELETE CASCADE]

-- 实例

-- 主表
create table department( 
depid varchar2(10) primary key,
depname varchar2(30)
);
-- 从表
create table student( 
sid number(8,0),
name varchar2(20),
sex char(2),
birthday date,
address varchar2(50),
depid varchar2(10) references department(depid)
);
-- 或者
create table student( 
sid number(8,0),
name varchar2(20),
sex char(2),
birthday date,
address varchar2(50),
depid varchar2(10),
-- 定义外键，级联删除
constraint fk_depid foreign key(depid) references department(depid) on delete cascade
);

-- 修改表时添加外键约束
alter table student add constraint fk_depid foreign key(depid) references department(depid) on delete cascade;
-- 禁用约束
alter table student disable constraint fk_depid;
-- 彻底删除
alter table student drop constraint fk_depid;

3.3 修改或删除表

添加列

alter table 表名 add 新增列名 数据类型;

修改列

alter table 表名 modify 列名 新数据类型;

删除列

alter table 表名 drop column 列名;

修改列名

alter table 表名 rename column 列名 to 新列名;

修改表名

rename 表名 to 新表名;

删除表中的所有数据

truncate table 表名;

删除表结构

drop table 表名;

3.4 增删改查

-- 增加数据
insert into 表名[(列1, 列2, ..., 列n)] values (值1, 值2, ..., 值N); 
-- 查询数据
select * column1[, column2, ...] from 表名;
-- 修改数据
update 表名 set column1 = value1[, column2=value2, ...] [WHERE condition];
-- 删除数据
delete from 表名 [WHERE condition];

3.5 事务

什么是事务：事务可以看作是由对数据库的若干操作组成的一个单元，这些操作要么都完成，要么都取消，从而保证数据满足一致性的要求。
为什么使用事务：当执行事务（DML语句）操作时，Oracle会在被作用表上加上表锁，以防止其他用户改变表结构；同时会在被作用行上加行锁，以防止其他事务在相应行上执行DML操作。

在没有执行commit 命令前DML语句并不会真正改变数据库中的数据

delete from emp_bak where empno=7369; -- 执行完并未删除
commit; -- 提交后才真正执行了删除

update emp_bak set sal=7777 where empno=7499;
savepoint a;  -- 设置保存点
update emp_bak set sal=8888 where empno=7499;
savepoint b; -- 设置保存点

select * from emp_bak where empno=7499;

rollback to a; -- 回滚到保存点a

rollback; -- 全部回滚

3.6 Oracle的数据字典

什么是数据字典
数据字典是Oracle存放有关数据库信息的地方，其用途是用来描述数据的。数据库数据字典是一组表和视图结构。数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。
数据字典的作用
通过数据字典，使我们了解数据库内部的信息。当用户在对数据库中的数据进行操作时遇到困难就可以访问数据字典来查看详细的信息。
*Oracle中三类数据字典user_、all_、dba_的区别
user_*数据字典：该视图存储了关于当前用户所拥有的对象信息。（即所有在该用户模式下的对象）
all_*数据字典：该视图存储了当前用户能够访问的对象信息。（与user_*相比，all_*并不需要拥有该对象，只需要具有访问该对象的权限即可）
dba_*数据字典：该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限）

使用数据字典查看相应数据库信息

select * from user_users; -- 查看当前用户下的用户信息
select * from all_users; -- 当前用户有权访问的所有用户的基本信息
select * from dba_users; -- 数据库所有用户的用户信息