02oracle之体系结构、plsql安装配置、数据库创建及增删改查

1、oracle体系结构

1.1、数据库

oracle数据库是数据的物理存储，这就包括（数据文件ORA、或者DBF、控制文件，联机日志、参数文件）。其实oracle数据库的概念和其他数据库不一样，这里的数据库是一个操作系统只有一个库，可以看做oracle就只有一个大数据库

1.2、实例

一个oracle实例由一系列的后台进程，和内存结构组成，一个数据库可以有n个实例

1.3、数据库文件dbf

数据库文件是数据库的物理存储单位，数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中；而一个表空间可以由一个或多个数据文件组成，一个数据文件只属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只有删除其所属的表空间才行。

1.4、表空间

表空间是oracle对物理数据库上相关数据文件（ORA、DBF）的逻辑映射，一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构，每个数据库至少有一个表空间（称之为system表空间）。每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据库文件（datafile），一个数据文件只能属于一个表空间
说明：表的数据，是由用户放入到某一个表空间的，而这个表空间会随机把这些表数据放到一个或多个数据文件中。由于oracle的数据库不是普通的概念，oracle是由用户和表空间对数据进行管理和存放的，但是表不是由表空间去查询的，而是由用户去查的，因为不同用户可以在同一个表空间建立同一个名字的表，这里区分就是用户了

1.5、用户

用户是在表空间下建立的，用户登录后只能看到和操作自己的表，oracle的用户与mysql的数据库类似，每建立一个应用需要创建一个用户

oracle的设计成表空间中有多个数据库文件，并且表随机存放在不同的数据库文件中，有利于将逻辑上是一个表空间，但实际不同的物理文件存放在不同的服务器上。
sqlplus登录数据库命令：
sqlplus 用户名/密码@主机名:端口号/数据库名@ip/orcl(全局数据库名)

2、PLSQL Developer 安装与配置

== 注意事项：安装目录不能有中文和空格，否则连接不上==

2.1、配置远程连接工具

sqlplus只支持连接本地数据库，要想实现连接远程数据库，需要一个集成了sqlplus的工具，该工具解压即可，支持远程连接数据库功能，为了不配置环境变量等复杂的操作，把该文件复制到数据库的product目录

2.2、配置plsql

在弹出的登陆窗口中，点取消，Tools->>Preferences ->>connect
将远程数据库配置文件F:\DevEnvironment\database\oracle11g\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora拷贝到远程连接工具的F:\DevEnvironment\database\oracle11g\product\instantclient_11_2目录中，并做好数据库信息配置，这样做的目的是不用配置环境变量

# tnsnames.ora Network Configuration File: F:\DevEnvironment\database\oracle11g\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

当然，tnsnames.ora文件可以不放在连接工具目录中，但是需要配置环境变量名称为TNS_ADMIN，值为tnsnames.ora所在的目录


至此，就可以使用plsql连接数据库了！

2.3、中文编码设置

查看服务器端编码 SQL:
select userenv(‘language’) from dual
我实际查到的结果为:SIMPLIFIEDCHINESE_CHINA.ZHS16GBK

计算机->属性->高级系统设置->环境变量->新建
设置变量名:NLS_LANG,变量值:第 1上一步步查到的值， 我的是 SIMPLIFIED CHINESE_CHINA.ZHS16GBK

3、数据库的创建

3.1、创建表空间

create tablespace waterboss 
datafile 'F:\DevEnvironment\database\tableSpace\waterboss.dbf' 
size 100m 
autoextend on 
next 10m

waterboss 为表空间名称 
datafile  用于设置物理文件名称 
size  用于设置表空间的初始大小 
autoextend on    用于设置自动增长，如果存储量超过初始大小，则开始自动扩容  
next  用于设置扩容的空间大小

3.2、为表空间创建用户

create user wateruser 
identified by root 
default tablespace waterboss 

wateruser 为创建的用户名 
identified by  用于设置用户的密码 
default tablesapce    用于指定默认表空间名称 

3、授权

grant dba to wateruser 
给用户 wateruser 赋予 DBA 权限后即可登陆 

退出system用户，使用wateruser用户登录

4、表的创建、修改与删除

4.1、创建表

4.1.1、语法

CREATE TABLE 表名称( 
  字段名 类型(长度) primary key, 
  字段名 类型(长度), 
  ....... 
);

4.1.2、数据类型

4.1.2.1、字符型

（1）CHAR : 固定长度的字符类型，最多存储 2000 个字节 (声明为10个字符，即使只有5个字符，后面用空格补齐，也会占用10字符的空间。)
（2）VARCHAR2 :可变长度的字符类型，最多存储 4000 个字节（类似于mysql的VARCHAR类型，不会采用空格补齐，长度为实际存储的大小）
（3）LONG : 大文本类型。最大可以存储 2 个 G（类似于mysql的text类型）
不固定长度时建议使用VARCHAR2

4.1.2.2、数值型

NUMBER : 数值类型
例如：NUMBER(5) 最大可以存的数为 99999
NUMBER(5,2) 最大可以存的数为 999.99

4.1.2.3、日期型

（1）DATE：日期时间型，精确到秒
（2）TIMESTAMP：精确到秒的小数点后 9 位

4.1.2.4、二进制型（大数据类型）

（1）CLOB : 存储字符,最大可以存 4 个 G
（2）BLOB：存储图像、声音、视频等二进制数据,最多可以存 4 个 G

4.1.3、示例

创建业主表

create table t_owners 
( 
id number primary key, 
name varchar2(30), 
addressid number, 
housenumber varchar2(30), 
watermeter varchar2(30), 
adddate date, 
ownertypeid number 
);

4.2、修改表

4.2.1、增加字段语法:

ALTER TABLE  表名称  ADD(列名 1  类型  [DEFAULT  默认值]，列名 1  类型 
[DEFAULT  默认值]...)

为业主表增加两个字段，语句：

--追加字段 
ALTER TABLE T_OWNERS ADD  
( 
  REMARK VARCHAR2(20), 
  OUTDATE DATE 
)

4.2.2、修改字段语法

ALTER TABLE 表名称  MODIFY(列名 1  类型  [DEFAULT  默认值]，列名 1  类型 
[DEFAULT  默认值]...) 

修改两个字段的类型，语句：

--修改字段 
ALTER TABLE T_OWNERS MODIFY  
( 
REMARK CHAR(20), 
OUTDATE TIMESTAMP 
)

4.2.3、修改字段名语法：

ALTER TABLE  表名称  RENAME COLUMN  原列名  TO  新列名 

修改业主表字段语句：

ALTER TABLE T_OWNERS RENAME COLUMN OUTDATE TO EXITDATE 

4.2.4、 删除字段名语法

--删除一个字段 
ALTER TABLE 表名称  DROP COLUMN  列名 
--删除多个字段 
ALTER TABLE 表名称  DROP (列名 1,列名 2...) 

删除业主表字段

--删除字段 
ALTER TABLE T_OWNERS DROP COLUMN REMARK

4.3、删除表

语法

DROP TABLE 表名称

5、数据增删改

5.1、插入数据

语法：

INSERT    INTO 表名[(列名 1，列名 2，...)]VALUES(值 1，值 2，...) 

执行 INSERT 后一定要再执行 commit 提交事务
向业主表插入数据：

insert into T_OWNERS VALUES (1,' 张 三 丰 
',1,'2-2','5678',sysdate,1); 

5.2、修改数据

语法

UPDATE  表名  SET  列名 1=值 1，列名 2=值 2，....WHERE  修改条件；

执行 UPDATE 后一定要再执行 commit 提交事务

需求：将 ID 为 1 的业主的登记日期更改为三天前的日期

update T_OWNERS set adddate=adddate-3 where id=1; 
commit;

5.3、删除数据

语法1

DELETE FROM  表名 WHERE 删除条件; 

执行 DELETE 后一定要再执行 commit 提交事务

需求：删除业主 ID 为 2 的业主信息

delete from T_OWNERS where id=2; 
commit; 

语法2

TRUNCATE    TABLE    表名称 

比较 truncat 与 delete 实现数据删除？

1. delete 删除的数据可以 rollback
2. delete 删除可能产生碎片，并且不释放空间
3. truncate 是先摧毁表结构，再重构表结构