oracle教案.doc

1. oracle安装——————及基本命令

1.1 oracle简介
Oracle是一个生产中间件和数据库的较大的生产商,oracle原本的含义：“神谕”,指的是神说的话,中国在商朝时代，把一些刻在龟壳上的文字当成是上天的指示,所以在中国将oracle翻译成”甲骨文”.
Oracel公司的发展实际上还是靠IBM

1.2 oracle安装
1.2.1 常见版本
现在使用的oracle是10G版本,oracle中的主要版本分为以下几个:
Oracle 8
Oralce 8i 表示oracle公司正式进军互联网
Oracel 9i: 与8i极为相似
Oracle 10g: g,表示网格计算

1.2.2 安装:
选择oracle 10G下instal目录下的setup.exe就可以执行oracle 10G的安装指令
接着oracle就进入系统环境检查环节（最好关闭防火墙软件）
选择安装的目录
选择企业版
输入数据库实例名/选中创建带样本的数据库
所有账户用同样的口令
安装完成后，任何按钮都不要点,如果做其它的操作，可能导致安装失败
选择口令管理,出现口令管理对话框,有∨的地方是被锁定的用户:
普通用户:scott用户的密码tiger
普通管理员:system用户密码manager
超级管理员sys用户密码admin
设置好密码后，点击确定
完成后，即可退出
1.2.3 oracle必须启动的服务
Oracle安装完成之后要在系统中注册服务,其有两个服务是必须启动oracle才能正常启动

OracleOraDb10g_home1TNSListener:表示监听服务，用户客户端要想连接到数据库,此服务必须打开，在以后的程序开发中此服务作用很大.
OracleServiceGYBOSS,表示数据库的主服务,命名规则OracleService+数据库名称,此服务必须启动,否则oracle根本无法使用
之后直接通过命令行方式就可以进入SQLPlus的控制中心,进行命令输入.

安装完毕后可能通过三种方式访问数据库:
1) 开始/运行/sqlplus
2) 开始/程序/Oracle - OraDb10g_home1/SQL Plus
3) 打开IE,输入本机IP:5560/isqlplus 或者 localhost:5560/isqlplus
登录界面如下:

1.3 oracle常见的第三方工具
1) PL/SQL Developer
2) Toad

在采用第三方工具连接数据库前，必须保证数据库的监听程序配置（服务器名，IP，端口号）正确并处于于监听状态,主要设置这两个文件
listener.ora

# listener.ora Network Configuration File: e:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = e:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)

(SID_DESC =
(GLOBAL_DBNAME = gyboss)
(ORACLE_HOME = e:\oracle\product\10.2.0\db_1)
(SID_NAME = gyboss)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.104)(PORT = 1521))
)
)

# tnsnames.ora Network Configuration File: e:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

GYBOSS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.104)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gyboss)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
2. SQL/PLUS
2.1 命令行方式
Sqlp是oracle提供的一个命令行执行的工具软件,安装oracle后会自动在系统中注册.
在开始/运行/sqlplus

3.3.2窗口方式sqlplusw

采用这种方式连接数据库时会提示主机的字符串,如果一台电脑上有多个数据库，则需要在此处输入数据的名称，若不填，则系统连接默认数据库

更改用户设置
        1. sqlplus sys/root as sysdba
        2. alter user scott account unlock;(解锁)
用系统管理员账号登陆数据库：conn sys/sys as sysdba
conn scott/tiger 或者connect user/pwd 切换用户
ed或者edit//打开一个缓冲文件
操作回滚:rollback，一旦提交(commit)则不可回滚;

最常用的就是sqlplusw的运行方式完成对oracle的操作
Select * from emp;
此语句表示向数据库发出查询命令,查询数据中emp表中的全部信息，结果如果:
使用sqlplusw之前首先必须进行环境的设置
(1) 设置每行显示的长度
Set linesize 300;

以上命令查询结果有一个问题，标题行重复了,这是因为oracle中的数据是一页一页地输出的.
(2) 修改每页显示的记录行数
Set pagesize 30;
设置每页输出30行,结果为

(3) 修改每列显示的显示宽度(每次都要执行一次)
column field_name format a40
col field_name for a40
例如:
SQL> select * from dual;
D
-
X

顺序执行了下面的语句后
SQL> col dummy for a5;
SQL> select * from dual;
DUMMY
-----
X
整个DUMMY的列名就完整地显示出来了

(4) 清除屏幕的命令

在在SQL*Plus中执行一段时间后，屏幕上会存大大量的输入和输出信息，要想清除屏幕
方法一：同时按SHIFT和DELETE键然后点OK就可以了 。

方法二：如果在window窗口下sqlplus 中清屏命令：host cls 或是clear screen 或只是4位 clea scre。

方法三：如果是在dos的窗口下进入sql/plus就要用clear SCR。
2.2 动手实践

在sqlplusw中存大着大量可用的命令
在sqlplusw中因为无法修改已经输入的内容，所以一般使用本机的记事本程序进行编辑，直接在命令窗口中输入”ed 文件名称”即可.
输入ed后会提示用户是否要创建此文件,如下图所示

除了可以在sqlplusw中建立这个文件之外，也可以通过@找到磁盘上的文件,
3. SQL语句
本课内容提要：
1) 查询语句的基本格式
2) 使用distinct关键字去掉重复的查询
3) 使用限定查询
4) 对查询结果进行排序
5) 掌握oracle中提供的各种主要单行函数

3.1 所需要用到的表
在所有后面的课程中，我们所使用到的表都是scott用户下的表,所以我们必须了解scott用户下有哪几张表，以及这些的结构和表与表之间的关系.
如果scott用户下的某个对表对象出了问题，如dept表被误删除了，或emp表的内的数据被修改了，现在要想恢复成原来的样子，应按如下步骤进行
以管理员角色连接数据库,执行
@\ E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\scott.sql
执行以上命令后所有的scott的对象将被重建，因而scott的密码被初始化为系统默认的TIGER,因此用户执行以上命令后应当重新设置用户口令
常用的关于用户的命令:
Show user --显示当前用户名
select table_name from user_tables ------查询当前用户下的所有表

查询TEST用户下的所有表，及其创建时间
SELECT OBJECT_NAME,CREATED FROM ALL_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER='TEST' ORDER BY CREATED DESC;

查询当前用户下的所有表
SELECT * FROM USER_TABLES

查询TEST用户下的所有表
SELECT * FROM ALL_TABLES WHERE OWNER='TEST'

3.1.1 雇员表(emp)
记录了每个雇员的基本信息

NO 字段 类型 描述
1 empno Number（4） 雇员编号
2 ename Varchar2(10) 雇员姓名
3 job Varchar2(9) 工作职位
4 mgr Number(4) 雇员的领导(经理)编号
5 hierdate date 入职日期
6 sal Number(7,2) 月薪/工资
7 comm Number(7,2) 奖金
8 deptno Number(2) 雇员所属部门的编号
3.1.2 部门表(emp)
记录了每个部门的基本信息

NO 字段 类型 描述
1 deptno Number(2) 部门编号(唯一)
2 dname Varchar2(14) 部门名称
3 loc Varchar2(13) 地址

3.1.3 工资等级表 (salgrade)
一个公司的工资等级制，用来表示同一等级的工资下限及上限

NO 字段 类型 描述
1 Grade number 等级名称
2 Losal number 此等级的最低工资
3 hisal number 此等级的最高工资
3.1.4 奖金表 (bonus)
一个公司中雇员获得奖金的情况

NO 字段 类型 描述
1 ename Varchar2(10) 雇员姓名
2 Job Varchar2(9) 雇员工作
3 Sal Number 工资
4 comm Number 奖金

3.2 SQL简介
对于各个不同的数据库来说，重点是掌握SQL语句，因为现在的数据库都是以SQL语句为操作标准，在实际应用中，各个数据库提供的函数各不同。不同的数据库的SQL语句格式有90%相同，10%的差异

3.2.1 SQL简介
SQL(structured Query Language 结构化查询语言) 是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。。SQL通常用于与数据库的通信，ANSI（美国国家标准学会）声称，SQL是关系数据库管理系统的标准语言.
按代数论，SQL应当属于第4代程序设计语言
第1代
SQL
第2代
C，C++，JAVA
第3代
汇编语言
第4代
机器语言

SQL语言包含4个部分：
　　※ 数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句。
　　※ 数据操作语言(DML)，例如：INSERT、UPDATE、DELETE语句。
　　※ 数据查询语言(DQL)，例如：SELECT语句。
※ 数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。

平常程序员交流的时候通常不会说“我今天写了几个DDL/DML/DQL/DCL语句”,而是说“我今天写了几个CRATE语句或几个SELECT语句”诸如此类的描述
3.2.2 简单查询语句格式
简单的SQL查询语句的语法格式是:
Select *|字段列表 别名
From 表名称;

(1) 查询全部列
Select * from emp;

(2) 如果现在只要查询出雇员的编号、姓名、工作，则需要指定查询的列
Select empno,ename,job from emp;

(3) 可以为查询的结果列指定一个名称，这个名称被为该列的”别名”,一般地，别名最好不用中文命名

(4) 查询所有的工作（去掉重复的工作） distinct
Select job from emp;

结果中存在大量重复的工作--------即job存在重复值,要消除重复值，在oracle中用distinct这个短语来消除查询结果中重复的值

Select [distinct]*|具体的列名 别名
From 表名称
但是，在消除重复列的时候，有一点必须说明，如果同时查询多列,则必须保证所有列都重复才能被消除
例如：查询所有职工的雇员号和工作

因为雇员号不重复，所以不存在重复的列，因页没有哪一列被消除
(5) 将各列查询结果相连 ||
例.要求查询出雇员的编号，姓名，工作，显示格式为:
编号是7369 的雇员，姓名是:SMITH，工作是:CLERK
要实现此功能：则可能使用oracle中提供的字符串连接操作符”||”来表示，如果还要加入一些固定显示的内容，则用单引号’ ’括起来

(6) SQL中的简单计算功能 + - * /
在查询中也可以使用四则混合运算（+ — * /）功能。例如：求出每个雇员的姓名及年薪
select ename,sal*12 income from emp

ENAME INCOME
---------- ----------
SMITH 9600
ALLEN 19200
WARD 15000
JONES 35700
MARTIN 15000
BLAKE 34200
CLARK 29400
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
JAMES 11400
FORD 36000
MILLER 15600

已选择14行。

在程序中sal*12意义不明确，为了明确这个表达式计算结果的意义,一般给这一列取一个有意义的别名，别名尽量避免用中文表示

3.2.3 限定查询（where 子句）
之前的查询是将一张表的全部记录都查出来，更多的时候我希望可以根据指的条件来查询，这样我们就必须指定查询的限定条件，限定查询的语法
Select {distinct}*|具体的列名 别名;
From 表名称
{where 条件语句}
(1) 例：查询出工资大于1500的所有雇员
SQL> select * from emp where sal>1500;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20

已选择7行。
(2) 例:查询每月可以得到奖金的雇员信息
奖金是comm列
只要列中存在内容，则表示此内容不为空(null),如果列中有内容，则会显示具体的值
列的内容不为空的表示,字段 IS NOT NULL
SQL> SELECT * FROM emp WHERE comm IS NOT NULL;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
(3) 例:查询没有奖金的职工的信息
SQL> SELECT * FROM emp WHERE comm IS NULL;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
(4) 例:查询出基本工资大于1500,并且可以领取奖金的职工的信息
分析:两个条件要同时满足 sal>1500 并且comm 不为空值
表示为: sal>1500 and comm IS NOT NULL
SQL> SELECT * FROM emp WHERE sal>1500 AND comm IS NOT NULL;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

(5) 例:查询出基本工资大于1500,或者可以领取奖金的职工的信息
如果要表示“或者”这个概念使用 OR对两个条件进行连接，表示只要其中一个条件成立就出现在查询结果中
SQL> SELECT * FROM emp WHERE sal>1500 OR comm IS NOT NULL;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7902 FORD ANALYST 7566 03-12月-81 3000 20

(6) NOT 可以对条件取反，把条件真变为假，把假变为真
例:查询出基本工资不大于1500，同时不可以领取奖金的职工的信息
SELECT * FROM emp WHERE not (sal>1500 or comm IS NOT NULL)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- -
7369 SMITH CLERK 7902 17-12月-80 800 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10

通过括号可表示一组条件

(7) 例:查询基本工资大于等于1500，但是小于等于3000的职工的全部信息
分析:条件满足 sal >= 1500 and sal < 300
在 SQL语法中,提供了一个专门的指定范围的过滤语句 BETWEEN … AND …
格式为:BETWEEN 最小值 AND 最大值
上一个例题则可写成：
SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7902 FORD ANALYST 7566 03-12月-81 3000 20

注意：BETWEEN … AND… 包含边界条件

(8) 例:查询出在1981年入职的全部雇员的信息
1981年入职等价于 1981年1月1日~1981年12月31日
SELECT * FROM emp WHERE hiredate BETWEEN '1-1月 -81' and '31-12月 -81'

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20

从个例子可以看出，BETWEEN … AND …即可支持对数据的查询，支持对日期的查询.
实际上日期可以转化为数字的形式表示出来，在后面的课程中将会详细介绍.

(9) 例:查询出姓名是SMITH的雇员信息
SELECT * FROM emp WHERE ename = 'SMITH'

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