oracle 学习

时间：2022-03-14 02:53

一、数据库语言部分
1. SQL语言：关系数据库的标准语言
2. PL/SQL：过程化语言Procedural Language
3. SQL*Plus：简单的报表，操作系统接口

4. Oracle 8.01后出现:
(1) 数据分区技术：只适用8.01后的版本，数据分散存放，不要放在一个硬盘上，I/O性能好，安全性能好。
(2) 对象技术：存储过程、函数、包、数据库触发器、动态SQL编程
(3) 数据库权限管理
(4) 数据完整性约束(Data Integrity Constraints)

二、Oracle 数据库核心，数据库管理员DBA
数据库的管理与日常维护
数据库总体设计
数据库存储结构设计：物理结构、逻辑结构

/**************************************************************************************************

可以运行Oracle的操作系统：
UNIX：Sun Solaris, HP-UX, AIX, Compaq-Tru64, SCO-UNIX和Linux（运行在PC机上）

Windows NT/2000

P4机器上不能安装Oracle，要想安装需要对安装文件进行修改或者下载补丁程序
在P4机器上安装Oracle的方法(只限于Intel P4机器)：
将Oracle的安装光盘拷贝到硬盘上，然后将/stage/components/oracle.swp.jre/win32/bin/symcjit.dll 文件改名为symcjit.org，然后再开始安装。

***************************************************************************************************/

数据库的备份与恢复
优化与性能调整

三、应用系统开发

四、应用服务器OAS

五、在Windows NT/2000下清除Oracle8i运行环境（重新安装前的准备工作）：

1. 删除Oracle8i注册表：
regedit.exe => HKLM => Software => ORACLE

2. 删除Oracle8i服务：
regedit.exe => HKLM => System => CurrentControlset => Services => 以Oracle开头的服务

3. 删除Oracle8i事件日志：
regedit.exe => HKLM => System => CurrentControlset => Services => Eventlog => Application => 以Oracle开始的事件

4. 删除Windows NT/2000安装磁盘/Program Files/Oracle目录。

5. 删除Oracle8i环境变量
控制面板 => 系统 => 高级 => 环境变量
(1) 删除CLASSPATH
(2) 编辑PATH，将其中与Oracle有关系的路径删除。

6. 删除Oracle8i菜单

7. 重新启动Windows NT/2000，停止服务。

8. 删除Oracle8i主目录。

/*
IP: 75.64.16.X
Mask: 255.255.248.0
GateWay: 75.64.16.3
DNS: 75.64.16.3
*/

[七、Oracle 网络配置]

/*
(1) 查询数据库名：
SQL> select name from v$database;

(2) 查询数据库实例名：
SQL> select instance_name from v$instance;

(3) 查询数据库服务名：
SQL> select value from v$parameter where name=‘service_names‘; // (小写)

(4) 查询全局数据库名(sys用户)：
SQL> select value$ from props$ where name=‘GLOBAL_DB_NAME‘; //　字符串区分大小写
*/

1. Oracle网络驱动使用SQL*Net v2.0 连接

Oracle for Win98 -> SQL*Net Easy Configuration

2. Oracle 网络驱动使用Net8 连接

3. Oracle 网络驱动使用Net8i 连接

Oracle程序组-> Network Administration -> Net8 Assistant

[创建TNS连接过程]:
(1) 启动Net8 Assistant
(2) 本地->服务命名
(3) 编辑->创建...
(4) 第一页：网络服务名：给要使用的网络数据库在本机指定一个Host String，自己定义。
(5) 第二页：协议：TCP/IP(Internet协议)
(6) 第三页：
　　主机名：输入对方机器的IP地址或域名。
　　端口号：默认是1521，一般不需要修改。
(7) 第四页：（Oracle8i)服务名：输入网络数据库的服务名
(8) 第五页：完成。
(9) 菜单：文件－＞保存网络配置。

监视用户会话：
SQL> select username, sid, serial#, machine from v$session;
删除用户会话：
SQL> alter system kill session ‘sid,serail#‘;

/* 练习
sqlplus stud01/stud01
SQL> show user

SQL> select * from emp;

SQL> select * from dept;

*/
[ // 使用下面的方法可以重复执行上一条SQL语句（在SQL*Plus中）
SQL> l //小写字母L，显示上一条SQL语句
1* select username, sid, serial#, machine from v$session
SQL> / //　正斜杠：重复执行上一条SQL语句

SQL> set linesize 1000 //将SQL*Plus中显示行宽设成1000个字符。

SQL> connect // 在SQL*Plus中直接连接到另一台机器上的数据库，@字符后是另一台机器的tnsname

]

4. 手工配置Oracle网络连接:主要是配置"tnsnames.ora"文件。
A. 手工配置Oracle网络连接配置文件：tnsnames.ora

(1) 在UNIX中：/u01/app/oracle/product/8.1.6/network/admin/tnsnames.ora

(2) 在Windows 98/NT/2000中：d:/oracle/ora81/network/admin/tnsnames.ora

B. 手工配置Oracle监听进程配置文件：listener.ora

(1) 在UNIX中：/u01/app/oracle/product/8.1.6/network/admin/listener.ora

UNIX下启动进程命令：
$ lsnrctl start // 启动监听进程
$ lsnrctl status // 显示监听进程状态
$ lsnrctl stop // 停止监听进程

(2) 在Windows 98/NT/2000中：d:/oracle/ora81/network/admin/listener.ora

启动进程的命令与UNIX相同。

# LISTENER.ORA Network Configuration File: d:/Oracle/Ora81/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ORA54)(PORT = 1521))
        //注意：上面一行的HOST必须是你的主机名，否则监听会出问题，也可以使用你的机器的IP地址
      )
    )
    (DESCRIPTION =
      (PROTOCOL_STACK =
        (preSENTATION = GIOP)
        (SESSION = RAW)
      )
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORA54)(PORT = 2481))
    )
)

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = d:/Oracle/Ora81)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ora54)
      (ORACLE_HOME = d:/Oracle/Ora81)
      (SID_NAME = ora54)   // 注意：ora54为数据库的SID名称，不能更改，否则监听出问题
    )
)

5. 将主机字符串(Host String)写入注册表(简化SQL*Plus的登录)
regedit.exe -> HKLM -> Software -> Oracle -> home0
增加关键字: local(字符串)，键值：主机字符串名。
/* 将下面的内容复制到一个.reg文件中，在Windows 2000中双击执行，即可实现增加或者修改local键值的作用
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOME0]
"local"="ora54"
*/

[SVRMGRL: Server Manager的使用方法]
D:/>svrmgrl

Oracle Server Manager Release 3.1.6.0.0 - Production

Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SVRMGR> connect internal //使用svrmgrl，进入后使用的第一个命令必须是这个命令。
口令:   // 如果要求输入口令，请输入oracle
连接成功。
SVRMGR> select name from v$database; // 检查当前使用的数据库名
NAME
---------
ORA44
已选择 1 行。
SVRMGR> shutdown immediate //关闭当前使用的数据库
已关闭数据库。
已卸下数据库。
已关闭 ORACLE 实例。
SVRMGR> startup    //启动当前使用的数据库，如果无效，请使用startup force
已启动 ORACLE 实例。
系统全局区域合计有                         24433932个字节
Fixed Size                                          70924个字节
Variable Size                                     7507968个字节
Database Buffers                                 16777216个字节
Redo Buffers                                        77824个字节
ORA-00205: ?????????????????????
SVRMGR>

[另一个启动oracle数据库的例子]
d:/> sqlplus internal/oracle
SQL> startup force //强行重新启动数据库。

[修改口令字]
SQL> grant connect to system identified by NewPassword; //如果用数字作口令，需要使用双引号括起来
SQL> grant connect to sys identified by NewPassword;
SQL> alter user system identified by NewPassword;
SQL> alter user sys identified by NewPassword;
SQL>password //需要输入原口令

// 注：以上修改口令的方法等价；sys与system用户可以互相修改口令；如果sys与system用户的口令都忘记了，使用如下方法：

D:/>svrmgrl

Oracle Server Manager Release 3.1.6.0.0 - Production

Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SVRMGR> connect internal/oracle
连接成功。
SVRMGR> grant connect to system identified by manager;
语句已处理。
SVRMGR> exit
服务器管理程序结束。

[2002.04.18]
//=========================================================================================================

八、Oracle 产品组成

查询数据库选件产品：
SQL> select * from v$option;
一般都是True, 如果是False, 可以双击激活。
/* cartridges (小产品的)插件，(大产品的)选件(options)*/

[SYS用户是Oracle数据库中权限最大的用户。]

[SQL*Plus登录方法]
1. c:/> sqlplus "/ as sysdba"
2. c:/> sqlplus internal
// 注意：以上两种方法可以类似的使用于SQL*Plus的图形登录界面中
// 用这种登录方法登录进去，所使用的用户均为SYS。
[之所以会出现这种登录方法，是因为在NT的用户组中存在一个ORA_DBA的本地组，凡在此组中的用户使用操作系统认证，即在此组中的用户登录数据库时不需要密码。]

[orapwd命令:修改internal用户的口令字]
/* orapwd的命令行参数
D:/>orapwd
Usage: orapwd file=<fname> password=<password> entries=<users>

where
    file - name of password file (mand),
    password - password for SYS and INTERNAL (mand),
    entries - maximum number of distinct DBA and OPERs (opt),
There are no spaces around the equal-to (=) character. */

修改internal口令字(internal默认口令为oracle)认证方法(A B两个步骤)：

A. 修改Oracle登录认证方法：
修改文件SQLNET.ORA文件。
SQLNET.AUTHENTICATION_SERVICES=(NTS) //将这一行前面加上#号注释掉，即可将Oracle的认证方法由操作系统认证改为Oracle认证

SQLNET.ORA文件的位置：
UNIX:/u01/app/oracle/product/8.1.6/network/admin/sqlnet.ora
Windows NT/2000: d:/oracle/ora81/network/admin/sqlnet.ora

B. 修改Internal口令字：
Windows NT/2000:
C:/>ORAPWD file=d:/oracle/ora81/database/pwdora8i.ora
password=YourPassword // YourPassword为你要设置的密码
entries=30
/* ORAPWD file=d:/oracle/ora81/database/pwdora54.ora password=qev entries=30 */

UNIX:
$ orapwd file=$ORACLE_HOME/dbs/orapwSID
password=YourPassword
entries=30
然后重新启动Oracle服务(服务－＞OracleServiceHOSTNAME)。

/* 在执行上述命令之前，需要先将相应目录的pwdora8i.ora(或orapwSID)文件删除或者改名，因为口令字文件不能重名。*/

[tkprof 跟踪文件整理工具]
$ tkprof x.trc x.txt

Windows 2000/NT:
d:/oracle/admin/db_name/udump/*.trc

UNIX:/u01/app/oracle/amdin/db_name/udump/*.trc // */

九、Oracle数据分区技术：8.0以后开始使用

(一)LOB(Large Object)大对象类型数据：
1. BLOB:存储二进制数据，如图象、视频、声音等，用于代替Long raw类型(Oracle7.0以前的数据类型，今后不再支持)
2. CLOB:存储大字符，如：个人简历，用于代替long字段。
3. NCLOB:其它民族语言的支持
(1) 数据库字符集NLS:
Server端：NLS_CHARACTERSET: (如果为以下的字符集，Oracle的数据库可以用来存储汉字)
ZHS16GBK(Oraclei8, Oracle8)
ZHS16CGB231280(Oracle7.3, 8, 8i)

此参数位置在数据字典中，查询核心字符集(语言、日期、货币等)：
SQL>select * from nls_database_parameters;

Client端：NLS_LANG=Simplified Chinese_CHINA.ZHS16GBK (如果没有设置，默认是英文)
regedit.exe

UNIX Client中：
$ NLS_LANG="simplified chinese"_china.zhs16gbk
$ export NLS_LANG

//一般将上述环境变量放入.profile文件中。

/***********************************************插入内容********************************************************

(1) sys是一种用户，internal是一种方式，用来启动关闭数据库，9.0以后不再使用internal，全部是sys。

(2) SQL>set com v7 //将8i版本暂时退回到7版

(3) SQL> alter system suspend; // 使用internal用户执行，冻结数据
SQL> alter system resume; // 取消冻结，恢复正常

****************************************************************************************************************/

(2)Oracle数据库核心字符集修改方法：

[*] 修改数据字典(使用SYS用户)：
SQL>update props$ set value$=‘ZHS16GBK‘ where name=‘NLS_CHARACTERSET‘;
SQL>commit;
然后重新启动数据库。

4. BFILE:外部文件存储，将数据存储在服务器硬盘

十、Oracle 8i 数据库改变：
1. 网络计算数据库
2. 支持IFS(Internet File System)
3. 集成Java虚拟机

[数据库语言]
第一章　　SQL语言基础

一、什么是SQL语言：
Structured Query Language ----　结构化查询语言

SQL*Plus支持的SQL语言：
1. SQL语言（标准的SQL语言）
2. SQL*Plus语言（非标准）：报表或接口
3. PL/SQL 语言（非标准）：程序开发（例如存储过程）

二、SQL语言特点：
1. 非过程化语言
2. 功能强
3. 提供视图功能
4. 两种使用方式
(1)交互式
(2)程序式: SQL> @filename.sql

5. 提供数据控制

三、SQL语言分类：
1. 数据查询语言(QL): Query Language
2. 数据操纵语言(DML): 包括数据的插入、更新和删除，Data Manipulation Language
3. 数据定义语言(DDL Data Definition Language): 建表、建视图、建存储过程等
4. 数据控制语言(DCL Data Control Language): 事务控制、包括权限等

四、SQL关键字（命令）
SQL关键字（以下命令为SQL命令，以分号结束）
1. Alter：修改表结构
2. Audit: 审计，　NoAudit(取消审计)
3. Commit:数据提交，相反的命令：Rollback(回退)
4. Comment:将oracle的注释写入数据字典
5. Create: 建表、数组、索引、视图等，　相反的命令：Drop
6. delete: 删除表中的数据，　Drop是把表中的数据包括结构全部删除
7. Grant:授权，　Revoke:收权(权限回收)
8. Insert:在表中插入新行
9. Lock: 将表强行锁住
10. Rename:修改表名
11. Select: 数据查询
12. Update:数据更新，修改某一个列
13. Validate:校验，对数据进行校验。

五、SQL命令的输入：
在SQL>提示符后输入命令，可以输入多行，以分号结束

六、SQL*Plus 关键字：以回车结束
1. @: 执行外部命令，格式：@路径/文件名
2. #: 注释
3. /: 执行上一条命令
4. Accept:接收键盘输入的命令
5. Append（简化为a): 在行尾增加字符串
/* 例
SQL> select * from dep;
select * from dep
*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

SQL> a t
1* select * from dept
SQL> /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 COMPUTER       BEIJING
*/

6. Break: 分组，　Syntax: break on 分组列 skip n　//n为每两组之间间隔的空行
/* 例：
SQL> break on job
SQL> select * from emp order by job; // 此处务必按分组列排序

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 13-6月 -87       3000                    20
      7902 FORD                       7566 03-12月-81       3000                    20
      7369 SMITH      CLERK           7902 17-12月-80        800                    20
      7876 ADAMS                      7788 13-6月 -87       1100                    20
      7934 MILLER                     7782 23-1月 -82       1300                    10
      7900 JAMES                      7698 03-12月-81        950                    30
      7566 JONES      MANAGER         7839 02-4月 -81       2975                    20
      7782 CLARK                      7839 09-6月 -81       2450                    10
      7698 BLAKE                      7839 01-5月 -81       2850                    30
      7839 KING       preSIDENT            17-11月-81       5000                    10
      7499 ALLEN      SALESMAN        7698 20-2月 -81       1600        300         30

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 28-9月 -81       1250       1400         30
      7844 TURNER                     7698 08-9月 -81       1500          0         30
      7521 WARD                       7698 22-2月 -81       1250        500         30

已选择14行。

SQL> break on job skip 1
SQL> /

      7369 SMITH      CLERK           7902 17-12月-80        800                    20
      7876 ADAMS                      7788 13-6月 -87       1100                    20
      7934 MILLER                     7782 23-1月 -82       1300                    10
      7900 JAMES                      7698 03-12月-81        950                    30

      7566 JONES      MANAGER         7839 02-4月 -81       2975                    20
      7782 CLARK                      7839 09-6月 -81       2450                    10
      7698 BLAKE                      7839 01-5月 -81       2850                    30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------

7839 KING preSIDENT 17-11月-81 5000 10

      7499 ALLEN      SALESMAN        7698 20-2月 -81       1600        300         30
      7654 MARTIN                     7698 28-9月 -81       1250       1400         30
      7844 TURNER                     7698 08-9月 -81       1500          0         30
      7521 WARD                       7698 22-2月 -81       1250        500         30

已选择14行。
*/
7. Btitle, Ttitle:设置表尾、表头
SQL> Ttitle ‘表头‘
SQL> Btitle ‘表尾‘
SQL> Ttitle off //失效

8. Change: 写错了改正, Syntax: C/old/new
/* 例：
SQL> select * from detp;
select * from detp
*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

SQL> c/tp/pt
1* select * from dept
SQL> /

9. Clear: 清除
SQL> clear break // 清除前面的break设置
SQL> clear buff // 清除SQL*Plus缓冲区中的SQL语句

10. Column: 列定义，用于定义列的显示格式
语法：SQL> column 列名 format 格式
例：SQL> Column sal format $99.999999

11. computer: 统计计算

12. connect: 从一个用户退出，直接进入另一个用户

13. disconnect: 中断当前用户的连接

14. copy: 远程复制（用的较少，一般数据链路)

15. define:定义

16. undefine:取消定义

17. del: 删除当前行

18. describe（简写desc): 显示表结构
例：SQL> desc emp

19. document: 文档注释

20. edit(简化ed):调入外部编辑器编辑缓冲区中的SQL语句，保存后，可以直接通过“/”运行编辑过的SQL语句

21. get: 将外部文件调入不执行，只供显示
例：SQL> get c:/sqlplus.ora

22. host: 执行操作系统命令
例：SQL> host dir

23. input(简化为I): 行插入，在当前行的后面插入。

24. List(简化为L): 列示

25. Pause: 设置屏幕暂停
SQL> set pause on //暂停
SQL> set pause off //不暂停
SQL> set ‘more‘ pause on [???]

26. Quit: 退出

27. Remark: 注释

28. Run(R): 类似“/”

29. Save: 将已经运行过的SQL命令保存到磁盘
例：SQL> save c:/run.sql replace //覆盖保存
append //追加

30. set: 设置

31. show: 显示环境变量的值

32. Spool: 显示跟踪
例：SQL> spool c:/文件名
SQL> spool off //停止记录

33. Start: 类似@，执行外部的SQL文件

33. Timing: 服务器端某一命令的执行时间跟踪

七、数据字典：描述系统信息的表、视图、同义词等，由系统自动维护。
例：SQL>select * from tabs;

1. 显示当前所登录的用户：select * from all_users;

2. user_xxx:描述用户创建的对象，如：user_tables，user_views，user_indexes

3. dba_XXX:数据库管理员(sys, system)专用数据字典

4. all_XXX: 描述用户创建的对象，其他用户授权可以存取的对象。
all_objects: 全部的数据字典

[第二章数据库查询语言(QL)]

一、查询语句基本语法：
1. 查询全表的数据：
SQL> select * from emp;

2. 查询某（几）个列：SQL> select ename, sal from emp;

3. DISTINCT 标识：只显示不相同的列
例：
SQL> select job from emp; // 显示结果中有相同的记录
SQL> select distinct job from emp; // 只显示不相同的职业

4. 使用order by将显示结果排序：ASC升序（默认），DESC降序

5. 使用where指出查询条件：
SQL> select * from emp where sal > 2000;

[字符型或日期型用单引号括起来，并区分大小写]

6. 设置日期显示格式：
(1) 确定日历格式：设置参数nls_calendar。

SQL>alter session set nls_calendar=‘Japanese Imperial‘; //设置为日本日历
SQL>alter session set nls_calendar=‘ROC official‘; //设置为台湾日历
SQL>alter session set nls_calendar=‘Gregorian‘; //设置为格林尼治日历，这是我们要设置成的日历。

SQL> select sysdate from dual; //查询系统时间

(2) 确定日期格式：参数nls_date_format
常用的日期格式：
yyyy.mm.dd
yyyy-mm-dd
yyyy/mm/dd
yyyy"年"mm"月"dd"日" //如果要显示汉字，请用双引号括起来
yyyy"年"mm"月"dd"日"dy // dy表示星期

命令格式：
SQL>alter session set nls_date_format=‘格式‘;
[此命令只修改前端的显示，退出后即失效，要想永久有效，要修改注册表，在注册表中HKLM->Software->Oracle->Home0增加字符串关键字nls_date_format，键值为日期格式，此处的格式不需要用单引号括起来。]

例：SQL>alter session set nls_date_format=‘yyyy"年"mm"月"dd"日"dy‘;

7. Oracle登录自动执行文件glogin.sql:每次用户登录时自动执行该文件，可以将一些环境变量（如linesize）的设置命令或者其它命令放入该文件中。

glogin.sql文件路径：

Windows下：d:/oracle/ora81/sqlplus/admin/glogin.sql

UNIX: /u01/app/oracle/product/8.1.6/sqlplus/admin/glogin.sql

二、运算符与谓词
1、算术运算符：+, -, *, /

SQL> select sal, sal*12 from emp; //计算工资及年薪。
SQL> select sal, sal+comm from emp; //错误语句，因为comm中存在空值，会使运算结果也会出现空值。
SQL> select sal, sal + NVL(comm,0) from emp; // 正确语句，NVL为空值运算函数，当comm为空值时，该函数返回第二个值0。

2、逻辑运算符：NOT, AND, OR，三个运算符的优先级别依次降低。

3、比较运算符：>, <, =, >=, <=, !=

4、谓词：
(1) IN(或NOT IN):等于（不等于）列表中的任意值。

SQL> select * from emp where job in (‘MANAGER‘, ‘CLERK‘)

(2) (NOT) BETWEEN AND : 表示从小到大的一个范围。[必须是从小到大]
SQL> select * from emp where sal not between 2000 and 3000;

(3) LIKE:模式匹配
SQL> select * from emp where ename like ‘S%‘; //寻找ename为S打头的记录
%: 表示任意字符串
_(下划线): 表示一个任意字符

SQL> select * from emp where ename like ‘李%‘; //可以使用中文
SQL> select * from emp where ename like ‘S_I%‘; //寻找ename为S打头，第三个字母为I的记录
SQL> select object_name from all_objects where object_name like ‘DBA%‘; //搜索数据字典中与dba有关系的对象

(4) <IS> (NOT) NULL(空值):

SQL> select * from emp where comm is NULL; //查询哪些人没有资金。

5. 伪列：

(1) rowid: 唯一行标识，行被删除之后，rowid不变。
(2) rownum: 行号，一行被删除之后，后面的行号会随之改变

SQL> select empno, ename, rowid, rownum from emp;

[查询SQL语句的相关数据字典]

（1）SQL> select sql_text from v$sqlarea; //查询以前使用过的SQL语句

（2）查询当前连接用户的SQL语句：
SQL> select user_name,sql_text from v$open_cursor; //需要用sys或system用户执行

三、列名别名
SQL> select ename, sal as Salary from emp;
SQL> select ename 职工姓名, sal 工资 from emp;

* # / / select 都不允许做别名，如果一定要用，用双引号括起来。

[第三章　数据操纵语言(DML)]

一、数据插入：

1. 对于表中全部列插入
语法：SQL> insert into　表名 values(值表达式);

例：SQL> Insert into dept values(51, ‘软件开发部‘, ‘北京‘);

[*] 用desc显示表结构、数据类型、顺序 SQL> desc dept //注意，不要将linesize设置的太大，否则看不到表结构，设置成100即可。
[*] 所插数据必须与目标列一致。
[*] 字符与日期数据使用单引号

2. 对于表中部分列插入
语法：SQL> insert into　表名（列名1，列名2...) values(值表达式);
[*]对于表中的非空列必须插入数据

SQL> insert into emp(empno, ename, job, hiredate,deptno) values(1234, ‘李大力‘, ‘工程师‘, sysdate - 30, 10);

/* sys 或 system 用户访问其它用户的表的方法

SQL> connect
已连接。
SQL> select * from user01.emp */

/******************** commit　及 Rollback用法:下例是Rollback的一个例子。
SQL> delete dept where deptno = 51;

已删除 1 行。

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 COMPUTER       BEIJING

SQL> rollback;

重算已完成。

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 COMPUTER       BEIJING
        51 软件开发部     北京

已选择6行。
*************************************************/

3. 使用参数(变量)，临时输入值

SQL> insert into 表名(列名1,列名2, ...) values(&x1, &x2, ...);

SQL> insert dept(deptno, dname, loc) values(&x1, ‘&x2‘, ‘&x3‘)

/* 例：
SQL> insert into dept values(&x1, &x2, &x3)   //&x2, &x3没有用单引号括起来
输入 x1 的值: 52
输入 x2 的值: ‘bb‘        // 字符型数据输入时需要输入单引号
输入 x3 的值: ‘cc‘        // 同上
原值    1: insert into dept values(&x1, &x2, &x3)
新值    1: insert into dept values(52, ‘bb‘, ‘cc‘)

已创建 1 行。

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 COMPUTER       BEIJING
        51 软件开发部     北京
        52 bb             cc

已选择7行。

SQL> insert into dept values(&x1, ‘&x2‘, ‘&x3‘); // 在命令行中给字符或日期型参数加上单引号，则下面输入时不再需要输入单引号

输入 x1 的值: 53
输入 x2 的值: dd
输入 x3 的值: ee
原值 1: insert into dept values(&x1, ‘&x2‘, ‘&x3‘)
新值 1: insert into dept values(53, ‘dd‘, ‘ee‘)

已创建 1 行。
********************************************************************/

// define x = 7788
// 也可以这样用：select &x1, &x2 from emp;

4. 使用子查询从另一个表中复制数据
语法：Insert into 表名 select 子句;

SQL> insert to emp(empno, hiredate, deptno)
select deptno+7300, sysdate, deptno from dept;

SQL> insert to emp(empno, hiredate, deptno)
select deptno+7300, sysdate, deptno from dept@数据库链路名; //表示dept这个表在网络上的其它服务器中，注意：@之后不是连接串。

SQL> create table pay as select ename, sal from emp; // 用复制的方法创建表
SQL> insert into pay select * from pay; //自己复制自己，可以使一个表飞速扩大。

二、数据更新
语法：SQL> Update 表名 set 列名=值表达式 where 条件;

例：
SQL> update emp set sal = sal + 100 where sal < 2000;
SQL>update emp set sal = 5000, job=‘MANAGER‘ where ename = ‘SMITH‘;
SQL> update emp set comm = 100 where comm is null;

三、数据删除
语法： SQL> Delete from 表名 where 条件;       //删除数据保留结构，可以回退
SQL>Truncate table 表名;    //删除数据保留结构，不可以回退，效率高
SQL>Drop table 表名;     //删除数据及结构，不可以回退

[第四章　数据控制语言(DCL)]

一、事务提交：对于数据的插入、更新、删除，只有提交后，数据才真正改变，在提交之前，只有修改了数据的用户才可以看到数据的改变，而其他用户看不到数据的改变。

1. 显式数据提交语法：SQL> commit;

/* 某一个用户对表进行更新等写操作之后，如果没有commit，那么其它用户不能对表进行更新操作，否则就会死掉。
查锁方法：
SQL> select username, sid, serial# from v$session;

解锁方法：
SQL> alter system kill session ‘sid, serial#‘;

*****************************************************************************************************/

2. 隐式数据提交：
下列命令是隐式提交命令：
Create, Alter, Drop, Connect, Disconnect, Grant, Revoke, Rename, Exit, Quit, Audit, NoAudit

3. 自动数据提交：
SQL> set autocommit on //打开自动数据提交开关
SQL> set autocommit off //关闭自动数据提交开关（默认）

二、事务回退
语法：SQL>Rollback; //使数据库回退到最近一次提交后状态，如果一次也没有提交过，回到最原始状态
SQL>Rollbac;
SQL>Rollba;
SQL>Rollb;
SQL>Roll;
//以上的用法都是正确的。

三、设置保存点：
SQL> SavePoint a;

回退到保存点：
SQL> Rollback to a; //此处的Rollback不能再简写。

[第五章　数据定义语言(DDL)]

包括：创建基表、视图、同义词、索引、数据库链路、序列等

一、创建基表
语法：
SQL> Create Table 表名(
列名1　数据类型,
列名2　数据类型,
...); //最多可以到1000个字段

例：SQL> Create Table product(
p_name, varchar2(20),
p_id_number number(7),
p_date date);

1. 数据类型
(1)字符型
char(n): n<=2000，固定长度，如果实际长度不够，前面用空格补齐。
varchar2(n): n<=4000，可变长度，不用空格补齐。
(2) 数字型　number(n):整数，　number(n,d):小数
(3) 日期型
(4) 二进制raw(8i以前的，现在不支持了）
(5) 大字符long：建议不要使用，现在不支持了
(6) blob:存储二进制
(7) CLOB:存储大字符

SQL> Create table employee(
name varchar2(30),
salary number(7,2),
b_date date,
phote blob,
resume clob);

[*]修改数据库兼容性参数（如果在执行上面的SQL语句时，提示clob字段“默认字符集具有不同的宽度”，则需要修改数据库兼容性。）:
D:/oracle/amdin/db_name/pfile/init.ora
compatible=8.0.5 =改为=> 8.1.0或8.1.5
改完后重新启动数据库使修改生效。
/**********************************************
修改前：
SQL> Create table employee(
2   name varchar2(30),
3   salary number(7,2),
4   b_date date,
5   phote blob,
6   resume clob);
resume clob)
*
ERROR 位于第 6 行:
ORA-22866: 默认字符集具有不同的宽度

修改后：
SQL> Create table employee(
2   name varchar2(30),
3   salary number(7,2),
4   b_date date,
5   phote blob,
6   resume clob);

表已创建。

************************************************/

2. 约束条件：数据完整性约束条件(Data Integrity Constaints)

Oracle 常用约束条件：
(1) 非空约束：NOT NULL
(2) 唯一性约束：UNIQUE
(3) 主键：PRIMARY KEY，主键同时具有上面两个约束条件，一个表中只允许有一个主键。
(4) 外键：FOREIGN KEY，这一列值从其它表中取出，允许重复，但不允许修改。
(5) 检查：CHECK (e.g. check (sal > 200)
(6) 引用（参考）：REFERENCES，只能用其它表或者本表的某一列列值，不能随便修改。
(7) 缺省值：DEFAULT，如果没有输入，自动使用DEFAULT值。

3. 约束条件的定义方法：
可以定义为列的一部分，也可以定义为表的一部分。
(1)定义为列的一部分：
SQL> Create Table product(
p_name varchar2(20) unique,
p_id number(7) primary key,
p_date date not null);

[*] Check、Default约束条件：
SQL> Create table employee(
name varchar2(20),
id number(7) primary key,
sal number(11) check(sal > 200 and sal < 2000),
h_date date default sysdate);

一个列具有多个约束条件的写法：
SQL> Create Table product(
p_name varchar2(20) unique,
p_id number(7) primary key,
check(p_id >= 111 and p_id <= 999),
p_date date not null);

例：
SQL> insert into employee(name, id, sal) values(‘Smith‘, 2, 201)；
SQL> select * from employee;

NAME                         ID        SAL H_DATE
-------------------- ---------- ---------- --------------
ChenZheng                     1        500 2002年04月14日
Smith                         2        201 2002年04月19日

(2)定义为表的一部分(不能用在Default 和 Not NUll的定义上)：
SQL> Create Table Product(
p_name varchar2(20),
p_id number(7),
p_date date not null,
constraint p_id_pk primary key(p_id),
constraint p_name_uk unique(p_name));

[*] 查询约束条件：
SQL> select * from user_constraints where table_name = ‘PRODUCT‘;

(3) 另一种约束定义方法：
SQL> Create Table Product(
p_name varchar2(20),
p_id number(7) constraint pk_p_id primary key,
p_date date constraint fk_p_date not null);

4. 使用外键创建主从基表
(1) 创建主表(定义主表):
SQL> Create Table product(
p_name varchar2(20),
p_id number(7) primary key,
p_date date not null);

(2)创建子表，定义外键
SQL> create table sales_list(
sales_name varchar2(20),
sales_id number(7) primary key,
p_id number(7),
constraint p_id_fk foreign key (p_id) references product(p_id));

5. 数据完整性约束条件的修改
(1) 删除约束条件：
A. 删除主键约束：
SQL> Alter table product drop primary key;
SQL> Alter table product drop constraint p_id_pk;

// 以上两种方法等价

B. 删除唯一性约束：
SQL> Alter table product drop unique(p_name);
SQL> Alter table product drop constraint p_id_uk;

C. 删除非空约束
SQL> Alter table product modify(p_date NULL);

D. 删除缺省值：
SQL> Alter table product modify(p_date default null);

(2) 增加约束条件
A. 增加主键约束：
SQL> Alter table product add primary key(p_id);
SQL> Alter table product add constraint p_id_pk primary key (p_id);

B. 增加非空约束
SQL> alter table product modify ( p_date not null);

C. 增加缺省值
SQL> Alter table product modify (p_date default sysdate-1);

[*]查询缺省值：
SQL> select table_name, column_name, data_default from user_tab_columns;

二、修改表结构
1. 在表中增加新列：
SQL> Alter table product add (p_list number(7), p_loc varchar2(20));

2. 删除一个列(只适用于Oracle8i以后的版本）：

SQL> Alter table product drop(p_list, p_loc); //删除多个列
SQL> Alter table product drop column p_list; //删除一个列

3. 修改列宽：
SQL> Alter table product modify (p_name varchar2(40));
//增加列宽没有约束，但减小列宽要求列中数据为空。使用此命令也可以修改列的数据类型。

三、视图(View):视图是虚表。
[*]视图不存储数据
[*]数据来源于基表
[*]不是数据的复制
[*]在同一个表上可以创建多个视图

1. 创建视图的语法：
SQL> Create or Replace view 视图名
as select 语句;

例：SQL> create view manager
as
select * from emp where job=‘MANAGER‘; // select * from tab; 检查

SQL> select * from manager; // 查询视图如果查询表

// 第二次创建视图，可以使用or replace参数，不需要再删除而直接覆盖同名视图。
SQL> create or replace view manager
    as
    select * from emp where job=‘MANAGER‘;

[几点说明:]
(1) 在创建视图时，不得使用order by排序。
(2) 在视图中插入数据，则数据被插入到基表中，所以，如果要向视图插入数据，则创建视图时，必须包含表中全部非空列。
(3) 用户视图数据字典：
SQL> select view_name,text from user_views;

2. 视图列别名：

错误语句：
SQL> create view payment as
select sal, sal*12, nul(comm,0)/sal from emp; // 错误原因：视图可以视同为表，所以列名也要符合规定，而sal*12则是不符合规矩的列名。

正确语句：
SQL> create view payment(c1, c2, c3) as // c1, c2, c3即为视图列别名
select sal, sal*12, nvl(comm,0)/sal from emp;

3. 创建视图时增加约束条件：WITH CHECK OPTION

SQL> Create or replace view deptno20 as
select empno, ename, deptno from emp where deptno=20;

SQL> Insert into deptno20 values(1236, ‘李力‘, 30);

SQL> select * from deptno20;

// 上面的语句会出现能够通过视图入基表中插入数据，但却不能通过视图看到插入的数据的问题，解决办法：
SQL> Create or replace view deptno20 as
select empno, ename, deptno from emp where deptno=20
WITH CHECK OPTION;

4. 创建Oracle8i的实体化视图(Materialized View):视图不依赖于基表，基表被删除后，视图仍然正常。一般用于两个远程数据库之间的访问，通过数据链路来实现。

(1) 以DBA用户登录，为用户授予创建实体化视图的权限：
SQL> Grant Create Materialized View to 用户名;

(2)以获权用户登录，创建实体化视图：
SQL> Create Materialized View manager as
select * from emp where job = ‘MANAGER‘;

(3) 删除实体化视图：
SQL> Drop Materialized View manager;

删除基表：
SQL>drop table 表名;

删除视图：
SQL> drop view 视图名;

三、创建数据库链路(Database link):
数据库链路：用于数据库之间的远程数据复制。

DB1(UNIX) <------- DB2(NT)

若要将DB2中的数据复制到DB1，则需要在DB1中创建数据库链路指向DB2数据库。

创建数据库链路的步骤：
(1) 创建好连接串。
(2) 创建数据库链路。

1. 创建数据库链路的语法：
SQL> Create database link 数据库链路名
connect to 用户名 identified by 口令
using ‘主机字符串‘;

[*]数据库链路名必须与远程数据库的全局数据库名(数据库名.域名，若没有数据库名，就是数据库名)相同
[*]用户名及口令为远程数据库的用户名及口令
[*]主机字符串为本机tnsnames.ora中网络连接串。

SQL> Create database link ora31 connect to user30 identified by user30 using ‘ora31‘;

2. 使用数据库链路：
SQL> select * from ;
SQL> insert into values(...);
SQL> Create table product as select * from ;

3. 删除数据库链路：
SQL> Drop database link ora31;

(一）创建数据库触发器实现两个数据库之间实时数据传输。
DB1(UNIX) <------- DB2(NT)
若要将DB2中的数据复制到DB1，则需要在DB1中创建数据库链路指向DB2数据库。

[*]在对方的数据库(DB2)上建立触发器，即数据发送方的数据库上建立触发器。
[*]在数据发送方建立指向DB1的数据库链路。

[操作步骤（假设db1为ora31, db2为ora8i)：]
(1) 在DB1上建立用来复制远程数据的表：
SQL> Create table product
as select * from ;

(2) 在DB2上建立到DB1的数据库链路：
SQL> Create database link ora31 connect to user30 identified by user30 using ‘ora31‘;

(3) 在DB2上建立触发器：
SQL> Create or Replace trigger insert_product before insert on product
for each row
Begin
Insert into
values(:new.p_id, :new.p_name);
End insert_product;

// 上述创建数据库触发器的语句，请以.号结束，然后以/执行。

/* 查看SQL语句执行的错误信息：
SQL> show errors
*/

(4) 在DB2上测试数据的自动复制是否成功：
SQL> Insert into product values(1005, ‘测试商品‘); // 在db2的表中插入一条数据
SQL> select * from product; // 检查数据是否正确插入本地表
SQL> select * from ; // 检查数据是否复制到了db1的表中

查询数据库链路信息：
SQL> select username, password from user_db_links;

数据更新：
SQL> Create or Replace trigger update_product
before update on product
for each row
Begin
update
set p_id = :new.p_id, p_name = new.p_name where p_id = :old.p_id
End update_product;

数据删除：
SQL> Create or Replace trigger delete_product
before delete on product
for each row
Begin
delete from where p_id = :old.pid
End delete_product

（二）. 创建快照（实体化视图）实现两个数据库之间定时数据库传输：
快照：要求主副站点数据库的用户名相同

(1) 在主节点创建快照日志
语法：SQL> Create snapshot log on 主节点表名; //主节点基表必须含有主键

(2) 在副节点创建快照
语法：
SQL> Create snapshot 快照名
refresh 刷新方式
next 时间间隔
with primary key
for update
as select * from 主节点表名@数据库链路名;

[*]刷新方式：
Compelete:完全刷新
Force:强制刷新(建议使用，强制刷新自动先fast刷新，然后再force刷新)
Fast:快速刷新
[*] 时间间隔：以天为单位。
sysdate + 1/4 // 六个小时刷新一次
sysdate + 1/1440 // 一分钟刷新一次

/******************************* 教师的部分操作语句 *****************************************************

以下SQL语句为在副节点上的操作语句，主节点对应的用户为user30，例中的photo为要创建快照的表名
create user user30 identified by user30;

grant connect, resource to user30;

grant create snapshot to user30;

connect user30/user30

create database link ora31 connect to user30 identified user30 using ‘ora31‘;

select * from ;

create snapshot photo
refresh force
next sysdate + 1/1440
with primary key
for update
as select * from ;

********************************************************************************************************/

/**************************　我的操作步骤 ******************************
[1] 主节点：ora8i
SQL> show user
USER 为"STUD29"
SQL> create snapshot log on dept;

实体化视图日志已创建。

SQL> show user
USER 为"STUD29"
SQL> select * from dept;

SQL> insert into dept values(60, ‘test snap‘, ‘snapshot‘);

已创建 1 行。

提交完成。

[2]副节点

SQL> connect system/ab
已连接。
SQL> create user stud29 identified by stud29;

用户已创建

SQL> grant connect, resource to stud29;

授权成功。

SQL> grant create snapshot to stud29;

授权成功。