一、数据库语言部分
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 system/manager@orasjz // 在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
版权所有 (c) 1997,1999,Oracle Corporation。保留所有权利。
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
版权所有 (c) 1997,1999,Oracle Corporation。保留所有权利。
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> /
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
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> /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 COMPUTER BEIJING
*/
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 system/ab@ora44
已连接。
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) 引用(参考)