前言: 关系数据库的简单介绍
1970.Dr.E.F.codded 创建了关系数据库的模型（类似现在常用的二维表格）
关系数据库由许多数据对象组成, 被关系操作SQL命令管理着, 数据之间有完整性的约束条件.
RDBMS ( relational database management system )
ORDBMS ( object relational database management system )
Oracle RDBMS常称为ORACLE SERVER
它的内核是用C语言写的.
ORACLE的简单介绍
ORACLE公司是一家提供综合技术产品、方案、服务的数据库公司
ORACLE数据库设计成可大量存储数据，快速查询数据，保证数据的安全和一致性，
跨网络的分布式管理及客户-服务器的配置等。
ORACLE SQL语法符合ANSI 1986标准
SQL 和数据库交流的命令式语言
SQL*PLUS ORACLE的一种工具, 用来运行SQL和PL/SQL语句
PL/SQL ORACLE的过程化编程语言
一、选择行
1. 简单的SELECT 语句
SELECT [DISTINCT] {*,COLUMN [ALIAS], ……} FROM table_name;
字段的非唯一的结果集
DISTINCT 字段名1 [ ,字段名2]
数字类型字段名之间可以进行算术运算
+ - * / 等
例如: (字段名1*字段名2)/3
合并字段内容的连接字符 ||
字段名1 || 字段名2
字段可以有别名
SELECT 字段名1 [AS] '字段名1 解释' FROM table;
2. 处理NULL
NULL 未定义，不可操作，什么都不是
NULL != 0 NULL!='空格'
和NULL的任何运算都返回NULL
ORACLE里未定义的变量值都为NULL
NVL函数可把NULL转换成其它类型的符号
NVL(EXPR1, EXPR2)函数
解释: IF EXPR1=NULL RETURN EXPR2
ELSE RETURN EXPR1
编程技巧: NVL函数在多条件模糊查询的时候比较有用
nvl('+input_value+','0')='0' or filed_name like '%+input_value+%'
NVL函数可返回多种数据类型:
返回日期 NVL(start_date,'2002-02-01')
返回字符串 NVL(title,'no title')
返回数字 NVL(salary,1000)
3. 使用SQL*PLUS（1）
登录SQL*PLUS的方法
用户名，密码，数据库连接字符串
数据库连接字符串可以用net8 easy configuration建立本地网络服务名配置，
也可以手工编辑$ORACLE_HOME/network/admin/tnsnames.ora文件。
在UNIX系统下不要用
$sqlplus username/password@dbname
这样别的用户用$ps命令能看出用户的密码
SQL> desc table; 显示表结构
SQL> select * from tab; 查看用户下所有的表
SQL> set pause on; 可以使大量结果集在用户按“Enter”(回车)后翻页
SQL> set pagesize 100; 设定SQL语句返回结果集一页的行数100, 默认值是14
SQL> set linesize 100; 设定SQL语句返回结果集一行的宽度100, 默认值是80
3. 使用SQL*PLUS（2）SQL*PLUS里的编辑命令
最近一条SQL命令语句存在ORACLE内存（sql buffer）里,但SQL*PLUS命令却不会存进去。
SQL*PLUS里的编辑命令:
A[PPEND] text 把text增加到当前行后面
C[HANGE] /old/new/ 把当前行old符号替换成new符号,new为空，删除old符号
CL[EAR] BUFF[ER] 从sql buffer里删除所有的行
DEL n 删除sql buffer里第n行
I[NPUT] text 在sql buffer后面插入text
L[IST] n 显示sql buffer里第n行
n 使第n行为当前行
n text 第n行替换成text
0 text 在第一行前面插入一行
3. 使用SQL*PLUS（3） SQL*PLUS里的文件命令:
SAV[E] filename [REP[LACE] | APP[END] ]
把sql buffer里的SQL命令存到OS下一个文件filename,默认的文件名后缀为.sql.
REP[LACE]替换filename里的SQL命令
APP[END] 把sql buffer里的SQL命令添加到filename后
GET filename 从filename里把SQL命令读到sql buffer
STA[RT] filename 运行上次保存到文件filename里的SQL命令
@filename 运行上次保存到文件filename里的SQL命令
EDIT 打开编辑窗口，编辑sql buffer里的SQL命令
EDIT filename 打开编辑窗口，编辑文件filename里的SQL命令
SPO[OL] filename [OFF | OUT] 把SQL命令结果输出到OS下一个文件filename OFF结束输出到文件,
OUT结束输出到文件并打印文件
4. SQL*PLUS里规定字段的显示格式
规定数字的显示格式
SQL>column 字段名 format 99999999999;
SQL>column 字段名 format 999,999,999,999;
规定字符串的显示宽度
SQL>column 字段名 format a数字 [word_wrapped];
说明: 一行只显示数字位的长度, 超过长度折行,加word_wrapped后, 单词不会折行
规定long字符的显示宽度
SQL>set long 200;
规定字段名的显示内容
SQL> column 字段名 heading '字段名显示内容';
SQL> set heading off; 查询时不显示字段名称
规定字段的对齐方向
SQL> column 字段名 justify [left | right | center];
清除字段的格式
SQL> column 字段名 clear;
5. SQL*PLUS里规定字段的显示格式例子
SQL> column last_name heading 'Employee|Name' format a15;
SQL> column salary justify right format $99,999.99;
SQL> column start_date format a10 null 'Not Hired';
说明：如果start_date为null, 显示字符串'Not Hired'
6. 判断题(T/F)
(1). SQL command are always held in sql buffer. [T]
(2). SQL*PLUS command assit with query data. [T]
SQL*PLUS命令只控制SELECT结果集的显示格式及控制文件.只有SQL命令能访问数据库.
二、限制选择行
1. 按指定的规则排序
SELECT expr FROM table [ORDER BY {column, expr} [ASC | DESC] ];
默认的排序是ASC升序(由小到大)
还可以ORDER BY 字段名的位置[1]| [2] ASC| DESC;
当字段名很复杂或者是算术表达式时用字段名显示的位置排序很方便.
2. 用WHERE限制选择行(1)
比较操作符 = > < >= >= to_date('2001-12-12','YYYY-MM-DD') and日期字段delete from table_name t1
where t1.rowid >
(select min(rowid) from table_name t2
where t1.email = t2.email
group by email
having count(email) > 1);
找到手机用户的服务区域:
SQL> select a.handphoneno,nvl(c.name,'null'),a.totalscore
from topscore a,chargeoperator cc,chargeoperatorinfo c
where substr(a.handphoneno,1,7)=cc.hpnohead(+)
and cc.chargetype=c.chargetype(+)
order by a.totalscore desc;
3. 数据表间的连接技巧
连接N个表, 需要N-1个连接操作
被连接的表最好建一个单字符的别名, 字段名前加上这个单字符的别名
BETWEEN .. AND.. 比用 >= AND grant plustrace to &username;
一般用户下运行@/ORACLE_HOME/rdbms/admin/utlxplan.sql
产生plan_table
SQL> set time on; 说明：打开时间显示
SQL> set autotrace on; 说明：打开自动分析统计，并显示SQL语句的运行结果
SQL> set autotrace traceonly; 说明：打开自动分析统计，不显示SQL语句的运行结果
接下来你就运行测试SQL语句，看到其分析统计结果了。
一般来讲，我们的SQL语句应该避免大表的全表扫描。
SQL> set autotrace off; 说明：关闭自动分析统计
五、集合函数 经常和group by一起使用
1. 集合函数列表
AVG (DISTINCT | ALL | N) 取平均值
COUNT (DISTINCT | ALL | N | expr | * ) 统计数量
MAX (DISTINCT | ALL | N) 取最大值
MIN (DISTINCT | ALL | N) 取最小值
SUM (DISTINCT | ALL | N) 取合计值
STDDEV (DISTINCT | ALL | N) 取偏差值,如果组里选择的内容都相同,结果为0
VARIANCE (DISTINCT | ALL | N) 取平方偏差值
2. 使用集合函数的语法
SELECT column, group_function FROM table
WHERE condition GROUP BY group_by_expression
HAVING group_condition ORDER BY column;
3. 使用count时的注意事项
SELECT COUNT(*) FROM table;
SELECT COUNT(常量) FROM table;
都是统计表中记录数量,如果没有PK后者要好一些
SELECT COUNT(all 字段名) FROM table;
SELECT COUNT(字段名) FROM table;
不会统计为NULL的字段的数量
SUM,AVG时都会忽略为NULL的字段
4. 用group by时的限制条件
SELECT字段名不能随意, 要包含在GROUP BY的字段里
GROUP BY后ORDER BY时不能用位置符号和别名
限制GROUP BY的显示结果, 用HAVING条件
5. 例子
SQL> select title,sum(salary) payroll from s_emp
where title like 'VP%' group by title
having sum(salary)>5000 order by sum(salary) desc;
找出某表里字段重复的记录数, 并显示
SQL> select (duplicate field names) from table_name
group by (list out fields) having count(*)>1;
6. 判断题(T/F)
(1) Group functions include nulls in calculations [F]
(2) Using the having clause to exclude rows from a group calculation [F]
解释:
Group function 都是忽略NULL值的 如果您要计算NULL值, 用NVL函数
Where语句在Group By前把结果集排除在外Having语句在Group By后把结果集排除在外
7. 在SQL*PLUS里可使用的其它命令:
Ctrl^C 终止正在运行的SQL语句
remark /*...*/ -- 注释符号
HOST 可执行的操作系统下的命令 有些unix可以用 !
BREAK ON column_name SKIP n [ ON column_name SKIP n ]
按字段的名称column_name分隔显示,更清晰,SKIP n 是在分隔处空行的数量n
BREAK ON ROW SKIP n 每一行间隔都放n个空行
COMPUTE 集合运算符 OF 字段1 ON 字段2 按字段2对字段1进行集合运算
COMPUTE后面可以跟的集合运算符:
SUM MINIMUM MAXIMUM AVG STD VARIANCE COUNT NUMBER
8.在SQL*PLUS里可使用的其它命令举例:(scott用户)
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF SAL ON REPORT 在全部结果集后面算合计
select ename,sal from emp where job='SALESMAN';
COMPUTE AVG LABEL avg OF SAL ON REPORT 在全部结果集后面算平均值
/ 再次执行上次的sql语句
break on DEPTNO skip 2 on JOB skip 1 在BREAK字段结果集后面算合计
COMPUTE SUM OF SAL ON DEPTNO
SELECT DEPTNO,JOB,ENAME,SAL FROM EMP ORDER BY DEPTNO,JOB;
SQL> CLEAR BREAKS; 清除设置的BREAK条件
SQL> CLEAR COMPUTES; 清除设置的COMPUTE条件
六、子查询
1. 查询语句可以嵌套
例如: SELECT …… FROM (SELECT …… FROM表名1, [表名2, ……] WHERE 条件) WHERE 条件2;
2. 何处可用子查询?
当查询条件是不确定的条件时
DML(insert, update,delete)语句里也可用子查询
HAVING里也可用子查询
3. 两个查询语句的结果可以做集合操作
例如:
并集UNION(去掉重复记录)
并集UNION ALL(不去掉重复记录)
差集MINUS,
交集INTERSECT
4. 子查询的注意事项
先执行括号里面的SQL语句，一层层到外面
内部查询只执行一次
如果里层的结果集返回多个，不能用= > < >= select title,avg(salary) from s_emp
group by title Having avg(salary) =
(select min(avg(salary)) from s_emp
group by title);
找到最低平均工资的职位名称和工资
5. 子查询的例子(2)
子查询可以用父查询里的表名
这条SQL语句是对的:
SQL>select cty_name from city where st_code in
(select st_code from state where st_name='TENNESSEE' and
city.cnt_code=state.cnt_code);
说明：父查询调用子查询只执行一次．
6.取出结果集的80 到100的SQL语句
ORACLE处理每个结果集只有一个ROWNUM字段标明它的逻辑位置,
并且只能 用ROWNUM80。
以下是经过分析后较好的两种ORACLE取得结果集80到100间的SQL语句( ID是唯一关键字的字段名 )：
语句写法：
SQL>select * from (
( select rownum as numrow, c.* from (
select [field_name,...] from table_name where 条件1 order by 条件2) c)
where numrow > 80 and numrow select id, last_name, salary from s_emp where dept_id = &department_number;
Enter value for department_number: 10
old 1: select id, last_name, salary from s_emp where dept_id=&department_number;
new 1: select id, last_name, salary from s_emp where dept_id= 10
SQL> SET VERIFY OFF | ON;可以关闭和打开提示确认信息old 1和new 1的显示.
3. 绑定变量SQL语句的例子(2)
SQL> select id, last_name, salary
from s_emp
where title = '&job_title';
Enter value for job_title: Stock Clerk
SQL> select id, last_name, salary
from s_emp
where hiredate >to_date( '&start_hire_date','YYYY-MM-DD');
Enter value for start_hire_date : 2001-01-01
把绑定字符串和日期类型变量时，变量外面要加单引号
也可绑定变量来查询不同的字段名
输入变量值的时候不要加;等其它符号
4. ACCEPT的语法和例子
SQL> ACCEPT variable [datatype] [FORMAT] [PROMPT text] [HIDE]
说明： variable 指变量名 datatype 指变量类型，如number,char等 format 指变量显示格
式 prompt text 可自定义弹出提示符的内容text hide 隐藏用户的输入符号
使用ACCEPT的例子：
ACCEPT p_dname PROMPT 'Provide the department name: '
ACCEPT p_salary NUMBER PROMPT 'Salary amount: '
ACCEPT pswd CHAR PROMPT 'Password: ' HIDE
ACCEPT low_date date format 'YYYY-MM-DD' PROMPT“Enter the low date range('YYYY-MM-DD'):”
4. DEFINE的语法和例子
SQL> DEFINE variable = value
说明： variable 指变量名 value 指变量值
定义好了变量值后, 执行绑定变量的SQL语句时不再提示输入变量
使用DEFINE的例子：
SQL> DEFINE dname = sales
SQL> DEFINE dname
DEFINE dname = “sales” (CHAR)
SQL> select name from dept where lower(name)='&dname';
NAME
-------------------------
sales
sales
SQL> UNDEFINE dname
SQL> DEFINE dname
Symbol dname is UNDEFINED
5. SQL*PLUS里传递参数到保存好的*.sql文件里
SQL> @ /路径名/文件名 参数名1[,参数名2, ….]
SQL> start /路径名/文件名 参数名1[,参数名2, ….]
注意事项:
一次最多只能获取9个&变量, 变量名称只能是从&1,&2到&9
变量名后不要加特殊的结束符号
如果在SQL*PLUS里要把&符号保存在ORACLE数据库里,要修改sql*plus环境变量define
SQL> set define off;
八、概述数据模型和数据库设计
1. 系统开发的阶段:
Strategy and Analysis
Design
Build and Document
Transition
Production
2. 数据模型
Model of system in client's mind
Entity model of client's model
Table model of entity model
Tables on disk
3. 实体关系模型 (ERM)概念
ERM ( entity relationship modeling)
实体 存有特定信息的目标和事件 例如: 客户,订单等
属性 描述实体的属性 例如: 姓名,电话号码等
关系 两个实体间的关系 例如:订单和产品等
实体关系模型图表里的约定
Dashed line (虚线) 可选参数 “may be”
Solid line (实线) 必选参数 “must be”
Crow's foot (多线) 程度参数 “one or more”
Single line (单线) 程度参数 “one and only one”
4. 实体关系模型例子
每个订单都必须有一个或几个客户
每个客户可能是一个或几个订单的申请者
5. 实体关系的类型
1:1 一对一 例如: 的士和司机
M:1 多对一 例如: 乘客和飞机
1:M 一对多 例如: 员工和技能
6. 校正实体关系的原则
属性是单一值的, 不会有重复
属性必须依存于实体, 要有唯一标记
没有非唯一属性依赖于另一个非唯一的属性
7. 定义结构时的注意事项
减少数据冗余
减少完整性约束产生的问题
确认省略的实体,关系和属性
8. 完整性约束的要求
Primary key 主关键字 唯一非NULL
Foreign key 外键 依赖于另一个Primary key,可能为NULL
Column 字段名 符合定义的类型和长度
Constraint 约束条件 用户自定义的约束条件,要符合工作流要求
例如: 一个销售人员的提成不能超过它的基本工资
Candidate key 候选主关键字 多个字段名可组成候选主关键字, 其组合是唯一和非NULL的
9. 把实体关系图映射到关系数据库对象的方法
把简单实体映射到数据库里的表
把属性映射到数据库里的表的字段, 标明类型和注释
把唯一标记映射到数据库里的唯一关键字
把实体间的关系映射到数据库里的外键
其它的考虑:
设计索引,使查询更快
建立视图,使信息有不同的呈现面, 减少复杂的SQL语句
计划存储空间的分配
重新定义完整性约束条件
10. 实体关系图里符号的含义
PK 唯一关键字的字段
FK 外键的字段
FK1,FK2 同一个表的两个不同的外键
FK1,FK1 两个字段共同组成一个外键
NN 非null字段
U 唯一字段
U1,U1 两个字段共同组成一个唯一字段
11. 实体关系图的例子
ORACLE SQL and SQL*PLUS
九、创建表
1. ORACLE常用的字段类型
ORACLE常用的字段类型有
VARCHAR2 (size) 可变长度的字符串, 必须规定长度
CHAR(size) 固定长度的字符串, 不规定长度默认值为１
NUMBER(p,s) 数字型p是位数总长度, s是小数的长度, 可存负数
最长38位. 不够位时会四舍五入.
DATE 日期和时间类型
LOB 超长字符, 最大可达4G
CLOB 超长文本字符串
BLOB 超长二进制字符串
BFILE 超长二进制字符串, 保存在数据库外的文件里是只读的.
数字字段类型位数及其四舍五入的结果
原始数值1234567.89
数字字段类型位数 存储的值
Number 1234567.89
Number(8) 12345678
Number(6) 错
Number(9,1) 1234567.9
Number(9,3) 错
Number(7,2) 错
Number(5,-2) 1234600
Number(5,-4) 1230000
Number(*,1) 1234567.9
2. 创建表时给字段加默认值 和约束条件
创建表时可以给字段加上默认值
例如 : 日期字段 DEFAULT SYSDATE
这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间
创建表时可以给字段加上约束条件
例如: 非空 NOT NULL
不允许重复 UNIQUE
关键字 PRIMARY KEY
按条件检��� CHECK (条件)
外键 REFERENCES 表名(字段名)
3. 创建表的例子
CREATE TABLE DEPT(
EPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)) ;
CREATE TABLE region(
ID number(2) NOT NULL PRIMARY KEY,
postcode number(6) default '0' NOT NULL,
areaname varchar2(30) default ' ' NOT NULL);
4. 创建表时的命名规则和注意事项
表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,#
大小写不区分
不用SQL里的保留字, 一定要用时可用双引号把字符串括起来．
用和实体或属性相关的英文符号长度有一定的限制
注意事项:
建表时可以用中文的字段名, 但最好还是用英文的字段名
创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面
建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引
一个表的最多字段个数也是有限制的，254个.
5. 约束名的命名规则和语法
约束名的命名规则约束名如果在建表的时候没有指明，系统命名规则是SYS_Cn(n是数字)
约束名字符串的命名规则同于表和字段名的命名规则
6. 使用约束时的注意事项
约束里不能用系统函数,如SYSDATE和别的表的字段比较
可以用本表内字段的比较
想在事务处理后, 做约束的检查
SQL> alter session set constraints deferred.
7. 由实体关系图到创建表的例子 s_dept
前提条件:已有region表且含唯一关键字的字段id
SQL> CREATE TABLE s_dept
(id NUMBER(7)
CONSTRAINT s_dept_id_pk PRIMARY KEY,
name VARCHAR2(25)
CONSTRAINT s_dept_name_nn NOT NULL,
region_id NUMBER(7)
CONSTRAINT s_dept_region_id_fk REFERENCES region (id),
CONSTRAINT s_dept_name_region_id_uk UNIQUE(name, region_id));
8. 较复杂的创建表例子
SQL> CREATE TABLE s_emp
(id NUMBER(7)
CONSTRAINT s_emp_id_pk PRIMARY KEY,
last_name VARCHAR2(25)
CONSTRAINT s_emp_last_name_nn NOT NULL,
first_name VARCHAR2(25),
userid VARCHAR2(8)
CONSTRAINT s_emp_userid_nn NOT NULL
CONSTRAINT s_emp_userid_uk UNIQUE,
start_date DATE DEFAULT SYSDATE,
comments VARCHAR2(25),
manager_id NUMBER(7),
title VARCHAR2(25),
dept_id NUMBER(7)
CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id),
salary NUMBER(11,2),
commission_pct NUMBER(4,2)
CONSTRAINT s_emp_commission_pct_ck CHECK
(commission_pct IN(10,12.5,15,17.5,20)));
8. 通过子查询建表
通过子查询建表的例子
SQL>CREATE TABLE emp_41 AS SELECT id, last_name, userid, start_date
FROM s_emp WHERE dept_id = 41;
SQL> CREATE TABLE A as select * from B where 1=2;
只要表的结构.
10. 用子查询建表的注意事项
可以关连多个表及用集合函数生成新表,注意选择出来的字段必须有合法的字段名称,且不能重复。
用子查询方式建立的表，只有非空NOT NULL的约束条件能继承过来, 其它的约束条件和默认值都没有继承过来.
根据需要，可以用alter table add constraint ……再建立其它的约束条件，如primary key等.
10. Foreign Key的可选参数ON DELETE CASCADE
在创建Foreign Key时可以加可选参数:
ON DELETE CASCADE它的含义是如果删除外键主表里的内容，子表里相关的内容将一起被删除.
如果没有ON DELETE CASCADE参数，子表里有内容，父表里的主关键字记录不能被删除掉.
12. 如果数据库表里有不满足的记录存在，建立约束条件将不会成功.
13. 给表创建和删除同义词的例子
SQL> CREATE SYNONYM d_sum
2 FOR dept_sum_vu;
SQL> CREATE PUBLIC SYNONYM s_dept
2 FOR alice.s_dept;
SQL> DROP SYNONYM s_dept;