Oracle数据库基本增删改查、数据数值类型、SQL Plus

Oracle

• Oracle常用 - 数据类型 ， 健值类型 ， 约束条件 ， 事务 , DDL语句、DML语句

1 - 数据库对象

• 表 : 存储的基本单元，有行列组成
• 视图 :一个逻辑的数据集，数据来自一个或多个表
• 序列 : 产生主键值
• 索引 : 对数据库表中一个或多个列的值进行排序的结构，目的是提高查询的性能
• 同义词 : 给出对象的别名.

2 - Oracle - 数据类型

1)字符类型

字符类型有哪些：

• char类型
• varchar2/varchar类型
• LOB类型
• LONG类型

char类型

CHAR类型存储定长的字符串，当指定CHAR类型的时候，必须指定一个1-2000字节之间的值作为CHAR的宽度（以字节或字符为单位）。默认为1字节。Oracle保证：

• 当插入和更新一列的时候，CHAR类型的列具有定长的宽度。
• 当给定一个较小的值时，值会由空白字符填充至定长长度。
• 当一个值太大时，Oracle数据库会返回错误。

varchar2/varchar类型

• varchar2类型存储变长的字符串。当创建varchar2类型的列时，需要制定一个1-4000字节之间的值作为最大长度。如果一个值超过了最大长度，Oracle数据库就会返回错误。使用VARCHAR2和VARCHAR类型可以节省表的空间，但是当读表量多时会浪费系统性能

  varchar数据类型是VARCHAR2的同义词。虽然现在VARCHAR2和VARCHAR是同义词，但是将来可能会将VARCHAR更改为单独的数据类型。因此在使用时候最好只是用VARCHAR2。

LOB类型

大对象类型（Large Objects）包括BLOB、 CLOB、 NCLOB和BFILE。它们允许你存储和可操作大型非结构化的数据，例如图片、文本、视频、声音等等。

• BLOB类型 (二进制数据)

  BLOB类型在数据库中存储非结构化的二进制数据，最多可以存储128T的二进制数据。

• CLOB和NCLOB类型 (字符型数据)

  CLOB和NCLOB类型可以储存最多128T的字符数据。CLOB储存数据库字符集的数据，而NCLOB存储Unicode字符集数据。

• BFILE (二进制文件)

  BFILE类型存储在数据库之外的非结构化的二进制数据。BFILE数据存储了指向外部文件的文件定位符。可以存储的BFILE数量手操作系统的限制。另外，BFILE是只读的。

LONG类型

• LONG类型可以存储最多2的32次方-1字节的变长字符串。这个类型存在的意义仅仅是为了向后兼容，因此不要在自己的数据库中使用这个类型。如果有存储大字符串的需求，使用CLOB和NCLOB类型。

2)数值类型

• 数值类型可以保存正负定点数和浮点数、零、无穷大和未定义的操作结果（例如非数字NaN）

数值类型有哪些

• NUMBER类型

• 浮点数类型

NUMBER类型

NUMBER类型用于存储定点数和浮点数，可以保存几乎任意大小（38位精度）的数值并在不同版本的Oracle数据库中通用。

以下数值可以保存在NUMBER类型中：

• 从 1 x 10E-130 到 9.99…9 x 10E125 范围内带有38位有效数字的正数

• 从 -1 x 10E130 到 9.99…99 x 10E125 范围内38位有效数字的负数零

• 正负无穷大（仅当从Oracle数据库版本5中导入时生成）

你可以选择指定NUMBER类型精度和数值范围：

column_name NUMBER (precision, scale) 

定义格式NUMBER (precision,scale)

• precision表示数字中的有效位(从左边第一个不为0的数算起，小数点和负号不计入有效位数)，取值范围为【1-38】默认38。

• scale表示精确到多少位，取值范围为【-84-127】，默认值为0。大于零时，表示数字精确到小数点右边的位数；小于零时，将把该数字取舍到小数点左边的指定位数，所以，NUMBER整数部分允许的长度为（precision- scale），无论scale是正数还是负数，并且，如果precision小于scale，表示存储的是没有整数的小数。

关于number的精度§和刻度(s)遵循以下规则：

• 当一个数的整数部分的长度> p-s 时，Oracle就会报错

• 当一个数的小数部分的长度> s 时，Oracle就会舍入。

• 当s(scale)为负数时，Oracle就对小数点左边的s个数字进行舍入。

• 当s > p 时, p表示小数点后第s位向左最多可以有多少位数字，如果大于p则Oracle报错，小数点后s位向右的数字被舍入

  如果精度没有指定，那么数据库会按照给定的值保存数值。如果数值范围没有指定，数值范围就是0。Oracle保证精度小于等于38位的数值的可移植性。可以将精度指定为*，这种情况下精度是38位，数值范围仍然是给定的。如下：

column_name NUMBER (*, scale) 

下面的表格展示了数值范围是如何影响数据存储的：

输入	指定为	存储为
7,456,123.89	NUMBER	7456123.89
7,456,123.89	NUMBER(*,1)	7456123.9
7,456,123.89	NUMBER(9)	7456124
7,456,123.89	NUMBER(9,2)	7456123.89
7,456,123.89	NUMBER(9,1)	7456123.9
7,456,123.89	NUMBER(6)	(not accepted, exceeds precision)
7,456,123.89	NUMBER(7,-2)	7456100

浮点数类型

浮点数类型有哪些

• BINARY_FLOAT

• BINARY_FLOAT

• Oracle数据库专门为浮点数提供了两种数据类型，BINARY_FLOAT和BINARY_DOUBLE。它们支持所有NUMBER提供的基本功能。并且由于NUMBER使用十进制精度，BINARY_FLOAT和BINARY_DOUBLE使用二进制精度，这为它们提供了更快的数值计算速度并常常减少了存储需求。

• BINARY_FLOAT和BINARY_DOUBLE是近似的数值类型。它们储存了十进制数值的近似表示，而不是精确表示。例如，十进制数0.1不能精确的表示成BINARY_FLOAT或者BINARY_DOUBLE。它们经常用来进行科学计算，在行为上和Java以及XML Schema中的FLOAT和DOUBLE相似。

BINARY_FLOAT

• BINARY_FLOAT是32位单精度浮点数类型，需要占用5个字节的存储空间，包括一个长度字节。

BINARY_DOUBLE

• BINARY_DOUBLE是64位双精度浮点数类型，需要占用9个字节的存储空间，包括一个长度字节。

值	BINARY_FLOAT	BINARY_DOUBLE
最大正值	3.40282E+38F	1.79769313486231E+308
最小正值	1.17549E-38F	2.22507485850720E-308

3)日期和时间类型

- 日期时间类型包括
DATE  ， TIMESTAMP  ,  TIMESTAMP WITH TIME ZONE ,TIMESTAMP WITH LOCAL TIME ZONE。

- 时间间隔类型包括
INTERVAL YEAR TO MONTH    ，  INTERVAL DAY TO SECOND两种。

日期和时间类型有

• DATE类型

• TIMESTAMP类型

DATE类型

• DATE类型存储了日期和时间信息。对应每个DATE类型的值，Oracle存储了以下信息：

Century	世纪
Year	年
Month	月
Day	日
Hour	时
Minute	分
Second	秒

这些类型可以为DATE类型的值指定字面值，也可以使用TO_DATE函数将一个字符或者数值转换成DATE类型

TIMESTAMP类型

• TIMESTAMP数据类型是DATE类型的扩展。它存储了年、月、日，加上时、分、秒。这个数据类型在储存精确的时间值时很有用。

3 - Oracle语句分类

• DDL数据定义语ju
• DML数据操作语言
• DQL数据查询语言
• DCL数据库控制语言
• TCL事务控制语言

DDL语句，数据库定义语言（与结构有关）

create（创建）、drop（删除）、alter（修改）、truncate（清空） ......

DML语句，数据库操作语言（与表数据有关）

insert（添加）、update（修改）、delete（删除）.....

DQL语句，数据库查询语言

select（查询）......

DCL语句，数据库控制语言

grant（赋权）、revoke（撤销）

TCL语句，事务控制语言

commit（提交）、rollback（回滚）

DDL数据定义语言

• 创建表

1、创建表
create table emp(
ename number(4),
sal number(5,2),
job varchar2(20)
)

2、使用查询创建表
create table test select * from emp;

3、用子查询建表
create table test
as
slect ename,empno,sal*12 salary
from emp
where empno=10;

Create table dept80
as 
select employee_id,last_name,salary,hire_date from employees where department_id=80

• 修改表结构

1、增加新列：
alter table test
add (job varchar2(10));
会增加到最后一列。

2、更改现存的列：
alter table test
modify (ename varchar2(10) );
如果现存的列有数据，不能更改；没有数据，可以更改.

3、删除列
alter table test
drop column comm;

• drop

  删库删表

drop table test;
表中所有数据和结构都被删除。
只有所有未决的事务都被提交，才能drop成功。
此表上所有的索引全部被删除。
此操作不饿能回滚，但是可以从回收站回收回来。

补充：回收drop后的表
第一步：show recyclebin; 查看回收站的表
第二步：flashback table " BIN$oxq89J+9TQWUDMAD4bCpQw==$0" to before drop;

• truncate

truncate table test;

移除表中所有行的数据，释放表空间（delete不释放表空间）。
执行truncate后，不能回滚。

DML数据操作语言

• insert

  插入表记录

insert into emp values(6666,‘李四’,‘项目总监’,7839,to_date(‘2018-1-1’,‘yyyy-mm-dd’),2000,null ,20);

insert into test select * from emp;

• update

  修改表记录

1、更新指定行
update test set deptno=10 where empno=7369;

2、更新所有
update test set deptno=10

• delete

  删除表记录

1、删除指定行
delete from test where empno=6666;

2、删除所有行
delete from test;

TCL事务控制语言

• commit（提交）

• rollback（回滚）

• s如果回滚事务，一般情况下将回滚到事务的开始，即对数据库不做任何修改。
  在Oracle 中，允许部分回滚事务，即可以将事务有选择地回滚到中间的某个点。
  部分回滚是通过设置保存点（ SAVEPOINT ）来实现的。

保存点与回滚完整示例
1、创建保存点a
  savepoint a;
2、插入emp数据 it1
  insert into emp(empno,ename) values(1234,'it1');
3、创建保存点b
  savepoint b;
4、插入emp数据 it2
  insert into emp(empno,ename) values(1235,'it2');
5、查看emp表数据，存在it1、it2两条数据
  select ename from emp;
6、回滚到保存点b，即it2数据将消失
  rollback to b;
7、查看emp表数据，存在it1的数据，it2已不在
  select ename from emp;
8、提交数据
  commit;
9、查看emp表数据，存在it1的数据
  select ename from emp;
10、回滚到保存点a，将报错保存点不存在的错误信息
  rollback to a;

DQL数据查询语言

运算符

• 算术运算符

+、-、*、/

• 比较（关系）运算符

＝、!＝、<>、< 、 >、 <= 、 >= 、 between...and... 、in 、like 、is null

• 逻辑运算符

AND(逻辑与)，表示两个条件必须同时满足
OR(逻辑或)，表示两个条件中有一个条件满足即可
NOT(逻辑非)，返回与某条件相反的结果

• 连接运算符

union（并集无重复）
union all（并集有重复）
intersect（交集，共有部分）
minus（减集，第一个查询具有，第二个查询不具有的数据）

• 运算符优先级

1  算术运算符
2  连接符
3  比较符
4  IS[NOT]NULL, LIKE, [NOT]IN
5  [NOT] BETWEEN
6  NOT
7  AND
8  OR

注意：列数相关，对应列的数据类型兼容，不能含有Long类型的列，第一个select语句的列或别名作为结果标题

--union(并集将去重复)
select * from emp where deptno=10
union
select * from emp where deptno=20;
 
--intersect(交集) 查询工资即属于1000~2000区间和1500~2500区间的工资
select ename,sal from emp where sal between 1000 and 2000
intersect
select ename,sal from emp where sal between 1500 and 2500;
 
--minus(减集)
select ename,sal from emp where sal between 1000 and 2000
minus
select ename,sal from emp where sal between 1500 and 2500;

4 - Oracle事务

• 事务是对数据库操作的逻辑单位，在一个事务中可以包含一条或多条DML （数据操纵语言）、DDL （数据定义语言）和DCL （数据控制语言）语句，这些语句组成一个逻辑整体。

• 事务的执行只有两种结果：要么全部执行，把数据库带入一个新的状态，要么全部不执行，对数据库不做任何修改。
  对事务的操作有两个：提交（ COMMIT ）和回滚（ ROLLBACK ）。

• 提交事务时，对数据库所做的修改便永久写入数据库。
  回滚事务时，对数据库所做的修改全部撤销，数据库恢复到操作前的状态。
  事务可用于操作数据库的任何场合，包括应用程序、存储过程、触发器等。

• 事务具有四个属性，这四个属性的英文单词首字母合在一起就是ACID 。

  原子性（ Atomicity ）：事务要么全部执行，要么全部不执行，不允许部分执行。
  一致性（ Consistency ）：事务把数据库从一个一致状态带入另一个一致状态。
  独立性（ Isolation ）：一个事务的执行不受其他事务的影响。
  持续性（ Durability ）：一旦事务提交，就永久有效，不受关机等情况的影响。
  一个事务中可以包含多条DML语句，或者包含一条DDL语句，或者包含一条DCL语句。

• 事务开始于第一条SQL语句，在下列之一情况下结束：

  遇到COMMIT或ROLLBACK 命令。
  遇到一条DDL或者DCL命令。
  系统发生错误、退出或者崩溃。

总之，事务是一系列可以把系统带入一个新的状态的操作，如果事务被提交，则数据库进入一个新的状态，否则数据库恢复到事务以前的状态。
在数据库中使用事务的好处是首先可以确保数据的一致性，其次在对数据做永久修改之前可以预览以前的数据改变，还可以将逻辑上相关的操作进行分组。
控制事务的方式有两种，一种是隐式控制，数据库管理系统根据实际情况决定提交事务还是回滚事务；下载地址 springmvc整合mybatis框架源码
另一种方式是显式控制，在事务的最后放置一条COMMIT或ROLLBACK命令，将事务提交或回滚。
如果是隐式控制，那么事务在遇到一条DDL命令，如CREATE ，或者遇到一条DCL 命令，如GRANT ，或者从SQL Plus正常退出，即使没有发出COMMIT或ROLLBACK命令，这个事务将被自动提交。
如果从SQLPlus非正常退出或发生系统崩溃，那么系统将自动回滚事务。
如果是显式控制，那么在事务的最后就要通过COMMIT命令提交事务，或者通过一条ROLLBACK命令回滚事务。

• 如果事务被提交，那么对数据库所作的修改将写人数据库。

• 如果回滚事务，一般情况下将回滚到事务的开始，即对数据库不做任何修改。
  在Oracle 中，允许部分回滚事务，即可以将事务有选择地回滚到中间的某个点。
  部分回滚是通过设置保存点（ SAVEPOINT ）来实现的。

  事务中可以通过SAVEPOINT命令设置若干个保存点，这样可以将事务有选择地回滚到某一个保存点。

• 用户访问数据库时，数据库中的数据是放在缓冲区中的，当前用户可以通过查询操作，浏览对数据操作的结果。
  如果没有提交事务，其他用户是看不到事务的修改结果的。

• 如果在事务中设置了保存点，并且在事务的最后执行ROLLBACK命令回滚到某个保存点，那么在此保存点之后的DML语句所做的修改将被丢弃，但是在此保存点之前的DML语句所做的修改仍然没有写入数据库，还可以进行提交或回滚。

• 事务和锁

  当执行事务操作（DML语句）时，Oracle会在被作用表上加表锁，以防止其他用户改变表结构；同时会在被作用行上加行锁，以防止其他事务在相应行上执行DML操作。假定会话A更新EMP表行的数据，那么会在表EMP上加表锁,此时其他会话修改表结构，则会显示错误。只有在会话A提交了事务之后，其他会话才能查询到新工资。

  如果在事务的最后执行了COMMIT命令，则对数据的修改将被写入数据库，以前的数据将永久丢失，无法恢复，其他用户都可以浏览修改后的结果，在数据上加的锁被释放，其他用户可以对数据执行新的修改，在事务中设置的所有保存点将被删除

5-约束条件

• 创建一个约束:在创建表的同时,或者在创建表之后都可以定义约束.可以给约束起名字,但是约束名不能相同,必须是唯一的.如果没有为约束起名字,Oracle服务器将用默认格式SYS_Cn产生一个名字,这里n是一个唯一的整数,所有约束名是惟一的

• 数据库中的约束有哪些类型？

  • 非空约束(not null):指定列不能包含空值

  • 唯一性约束(unique):指定列的值或者列的组合的值对于表中所有的行必须是唯一的

  • 主键约束(primary key):表的每行的唯一性标识

  • 外键约束(foreign key):在列和引用表的一个列之间建立并且强制一个外键关系

  • 用户自定义约束(check):指定一个必须为真的条件

格式

create table student(
	stuno number(2),
	name varchar2(14),
	constraint stu_stuno_pk primary key(stuno),
	constraint emp_dept_fk foreign key(stuno) references dept2 (deptno),
	CONSTRAINT check_student_no CHECK (stuno BETWEEN 100 and 9999)
);

定义非空约束

创建表时定义 NOT NULL 约束
Create table tha (employees varchar2(23) not null );

修改表定义 NOT NULL 约束
Alter table tha modify employees varchar2(30) not null

定义唯一性约束

创建dept90表，包含如下列：id number具备唯一性约束、name varchar2(20)。
Create table dept2 (id number unique,name varchar2(20) unique );

定义主键约束

1、定义主键约束
create table dept50 (id number,name varchar2(20),constraint det_k primary key(id,name));

# 查询表的主键：(表名要大写，否则为空)
select cu.* from user_cons_columns cu, user_constraints au 
	where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' 
		and au.table_name = 'EMP';	
		
2、删除主键：
alter table emp drop constraint emp_no_pk;

3、加主键
alter table emp add constraint emp_no_pk primary key(empno);

定义外键约束

1、定义外键约束
创建dept40表，包含如下列：id number、d_id number并为d_id列分配键列约束参照DEPT60表的id列。
Create table dept40(id number,d_id number,constraint DD foreign key(id) references DEPT60(id) )

2、查询外键：（表名必须大写，否则为空）
select constraint_name "外键名",  table_name "表名",R_constraint_name "参考约束名" 
	from user_constraints c 
		where c.constraint_type= 'R' and c.table_name='EMP2';

3、删除外键：
alter table emp2 drop constraint emp_dept_fk;

4、增加外键：
#//父表的列必须是主键，否则会报异常ORA-02270: no matching unique or primary key for this column-list
  alter table emp2 add constraints emp_dept_fk foreign key(DEPTNO) references dept2(DEPTNO);
或者
  alter table emp2 add constraints emp_dept_fk foreign key(DEPTNO) references dept2(DEPTNO) on delete cascade；
#//注意：列名一定大写，否则操作的结果和你想的根本不一样。

5、
#//补充on delete cascade：
1、对于alter table emp2 add constraints emp_dept_fk foreign key(DEPTNO)
references dept2(DEPTNO)语句， 如果删除父表（部门表dept）数据，根本删除不了（会报错，因为有子表的外键牵连）。
delete from dept where depto=40; 会报错。
2、对于alter table emp2 add constraints emp_dept_fk foreign key(DEPTNO)
references dept2(DEPTNO) on delete cascade语句，
如果删除父表（dept表）数据，不但会删除dept表的数据，还会删除子表关联的数据。 delete from dept where
deptno=40; 会删除dept表中的部门为40的记录，也会删除emp表中与部门号为40相关的记录。

检查约束：

1、查询表有哪些约束：
select constraint_name "约束名",  table_name "表名",search_condition "约束条件"
	from user_constraints c 
		where c.constraint_type= 'C' and c.table_name='EMP2';

2、删除检查约束：
alter table emp2 drop constraint CK_EMP2_EMPNO;

3、增加检查约束：
建表时加check约束：
create table tb_supplier
(
  supplier_id       number,
  supplier_name     varchar2(50),
  contact_name      varchar2(60),
  /*定义CHECK约束，该约束在字段supplier_id被插入或者更新时验证，当条件不满足时触发。*/
  CONSTRAINT check_tb_supplier_id CHECK (supplier_id BETWEEN 100 and 9999)
);

6 - 视图

• 什么是视图？

  可以通过创建表的视图来表现数据的逻辑子集或数据的组合.视图是基于表或另一个视图的逻辑表,一个视图并不包含它自己的数据,它像一个窗口,通过该窗口可以查看或改变表中的数据.视图基于其上的表称为基表

• 视图的优越性是什么？

  视图限制数据的访问,因为视图能够选择性的显示表中的列

  视图限制数据的访问,因为视图能够选择性的显示表中的列

  视图对特别的用户和应用程序提供数据独立性,一个视图可以从几个表中取回数据

• 视图分为几种类型？

SQL Plus

SQL*Plus Oracle数据库工具之一

熟悉windows基本指令

• windows 上操作

• SQL Plus是Oracle数据库的一个字符界面工具，所有功能均以命令行的方式执行，需要涉及并使用部分常用的DOS命令，doc命令如下：

   - 命令提示符程序的启动和退出。
  菜单中输入cmd进入命令提示符程序
  退出cmd：可以直接输入exit命令，按回车键可退出命令提示符程序。
  
  

• 改变当前路径。
  1、在命令行状态下，如果行左侧不是C:>，表示当前工作路径不是根目录（如图1-21所示），可执行cd\命令回到根目录中。
  2、如果当前根目录不是C盘，可运行C:命令，将当前根目录切换到C盘，此时，运行md abc命令，可在C盘根目录下创建文件夹abc。
  3、在C盘根目录下，运行cd abc将路径c:\abc设为当前目录。
  4、在路径C:\abc中运行md aaa命令，在C:\abc中创建一个子文件夹，然后再运行cd aaa命令进入C:\abc\aaa文件夹中。
  5、在C:\abc\aaa文件夹中运行命令cd…，回到当前文件夹的父文件夹，即路径C:\abc中。
  6、在路径C:\abc中运行rd aaa命令删除空文件夹aaa，运行cd\命令回到根文件夹，然后运行命令d:，可将当前路径变换到D盘的根目录。

• 在DOS命令中，大小写是不敏感的，即命令cd\和CD\是相同的命令。
  cd\命令可以从任意路径返回到根目录；cd…命令可以从当前路径返回到上一级目录；cd表示从当前路径进入下一级子目录中，使下一级的某个子目录变成当前路径，该命令后面要跟上一个已存在的文件夹名；
  md命令表示在当前路径下创建一个文件夹；
  rd命令可删除当前路径下指定的空文件夹；
  d:是一个切换当前分区的命令，即从其它分区切换到D分区，如果要切换到E分区，可以使用e:命令，以此类推；DOS命令的大小写形式是相同的

1 在cmd中运行dir命令显示当前文件夹中的所有文件和文件夹（不包括隐藏文件和文件夹）。
运行dir/w/a命令显示文件和文件夹内容（包括隐藏文件和文件夹）。
注：/w选项表示以紧凑方式显示文件和文件夹；/a选项用于显示隐藏的文件和文件夹； 如果要显示其它文件夹中的内容，可以在命令后面加上路径（如dir/a/w d:\abc命令，可以显示路径d:\abc中的文件和文件夹），也可以使用cd命令先切换当前路径，然后再运行dir命令。

2、复制文件
在cmd复制文件：运行copy D:\aaa*.* C:\abc命令，将D:\aaa中的所有文件复制到C:\abc文件夹中。

3、删除文件。
在C盘根文件夹下，运行cd abc命令，使C:\abc成为当前路径，运行del *.*命令，删除该文件夹中的所有文件。

启动和退出SQL Plus工具

• 几个词的解释：
  table：表，tablespace:表空间 ，view:视图 ，customer:过程 ，roles:角色 ，object :对象 ，
  登录语句：

• 通过命令行启动SQL Plus时

1、启动
C:>sqlplus /nolog
SQL>conn sys/'password' as sysdba


注释：
/nolog子句表示启动SQL Plus，但不登录到Oracle数据库实例；
当使用管理员身份登录时，需要使用as sysdba子句，普通用户登录时不需要；
如果使用当前操作系统用户的身份登录，则使用sqlplus / as sysdba命令；
如果使用sqlplus /命令登录，则系统将提示你继续输入用户名和口令；
也可以在启动SQL Plus的同时，登录到Oracle数据库实例，如sqlplus sys/abcdefg as sysdba；
断开连接时，在SQL Plus中运行disconnect或disc命令即可。

2、退出
SQL>exit
或
SQL>quit

查看登录用户：
show user

SQL Plus的常用编辑命令

• 注意：Oracle命令需要在 SQL> 提示符下执行。
  在SQL Plus中，可以编写由多个语句或命令组成的语句块，这些语句块将暂存在缓存区中，由于SQL Plus无法使用鼠标工具编辑语句块，因此，只能通过执行命令的方式完成对缓冲区中语句块的修改；

1、在SQL Plus中执行下列语句。
SQL>select employee_id,first_name
2 from hr.employees
3 where job_id= ‘SA_MAN’;
说明：SQL语句以分号“;”结尾时，按“Enter”键将执行该SQL语句，否则将换到下一行，继续等待输入；已经输入的语句自动进行缓冲区暂存。

2、运行缓冲区中SQL语句。
SQL>run	– 执行缓冲区中的SQL语句
SQL>/	– 执行缓冲区中的SQL语句

3、重新显示缓冲区内容。
SQL>;
SQL>list
说明：在SQL语句块执行完后，可以用分号（;）或list命令，重新显示缓冲区中的内容。

4、当前行切换
SQL>2	– 将缓冲区的第2行设为当前行
说明：在SQL Plus中，只能对缓冲区中的当前行进行编辑，要想编辑某一行，首先要将其切换为当前行；在显示缓冲区时，当前行前面有一个星号（*）；–为注释语句的前导字符，与Java或C++语言中//的用法类似；每执行一个命令前，应该先显示一下缓冲区中的内容，以查看语句执行效果。

5、在第1行的行尾追加文本。
SQL>1
SQL>append ,last_name

6、替换第3行中的部分内容。
SQL>3
SQL>change /SA_MA/ST_MA

7、存储缓冲区中内容到文件。
SQL>save c:\a.tt

8、删除缓冲区中的第2行。
SQL>del 2

9、在第1行后插入一新行。
SQL>1
SQL>input from hr.employees

10、清除缓冲区中的内容。
SQL>clear buffer

11、装载文件中内容到缓冲区。
SQL>get c:\a.tt

数据库字典

• 数据库字典：
  是数据库提供的表，用来查询数据库的信息。
  dba_users 表就是用来查询 用户信息。

1、查询 用户信息
SQL>desc dba_users

2、查询当前所有用户名：
SQL>select username from dba_users;

describe命令

• desc命令不属于sql语句。
  列出指定表的列定义，视图或同义词，或指定函数或存储过程的详述。
  描述表、视图、同义词、函数、存储过程的命令。

启用用户:

SQL>alter user scott account unlock
用户已更改

SQL>connect scott/tiger
ERROR:
ORA-288001: the password has expired

更改 scott 的口令
新口令：
重新键入新口令：
口令亿更改
已连接。

表空间

是数据库的逻辑存储空间。

• 永久表空间

• 临时表空间

• UNDO表空间

  永久表空间存储表、视图、存储过程等。
  临时表空间存储数据库的中间执行过程。执行完就会被释放掉。
  UNDO表空间存储事务修改前的数据，可以对数据进行回滚。

• 用户表空间：
  不同用户登录后，对应不同的表空间。

• 表空间定义在 dba_tablespaces,user_tablespaces数据字典里。

SQL>desc dba_tablespaces
......
.....

SQL>select tablespaces_name from dba_tablespaces;
system表空间：存放sys管理员对应的表、视图、存储过程等数据库对象。系统表空间，永久表空间。
example表空间：oracle的事例用到的表空间。
sysaux表空间：辅助example表空间。
undotbs1表空间：存储一些撤销信息的表空间。属于undo表空间。
temp表空间：sql语句处理的表和索引等信息的表空间，是临时表空间。
users表空间：存放用户创建的表、视图、存储过程等数据库对象，是永久表空间。

SQL>select tablespaces_name from user_tablespaces

• dba_users 和 user_users数据字典里存放用户的默认表空间和临时表空间。

desc dba_users
....
DEFAULT_TABLESPACE
TEMPORARY_TABLESPACE
.....

• system用户的默认表空间是user,临时表空间是temp.

SQL> select default_tablespace,temporary_tablespace from dba_users where username="SYSTEM"

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

将用户的默认表空间修改为system表空间

格式：
SQL> alter user system  default|temporary  tablespace tablespace_name

SQL> alter user system 
  2 default tablespace system;
  
SQL> select default_tablespace,temporary_tablespace from dba_users where username="system"

• 创建表空间：

默认目录下创建一个该用户的永久表空间

SQL>create tablespace test1_tablespace 
  2 datafile 'test1file.dbf' size 10m

• dba_data_files数据字典：用来存储表空间信息。

SQL>desc dba_data_files

• 查询表空间的存储物理位置

select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

• 修改表空间：

表空间创建后，默认为联机状态。

SQL> alter tablespace test1_tablespace
  2 offline;

• 设置表空间的读写权限：

alter tablespace test1_tablespace 
  2 read only

• 向表空间增加数据文件：

alter tablespace test1_tablespace 
  2 add datafile 'test2_file.dbf' size 10m;

• 删除表空间的数据文件：

alter tablespace test1_tablespace
  2 drop datafile 'test2_file.dbf' ;

• 删除表空间：

drop tablespace test1_tablespace including contents;

PLSQL

…

…

…欲知PLSQL，请听下回分解

hezw 2020-3-11第一版Oracle数据库笔记