mid NUMBER,
name VARCHAR2(50) NOTNULL, CONSTRAINT pk_mid PRIMARYKEY(mid) );CREATETABLE book( bid NUMBER,
title VARCHAR2(50) NOTNULL, mid NUMBER,
CONSTRAINT pk_bid PRIMARYKEY(bid),
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ONDELETESETNULL );
INSERTINTO member(mid,name) VALUES(1,'张三'); INSERTINTO member(mid,name) VALUES(2,'李四');
INSERTINTO book(bid,title,mid) VALUES(101,'Java开发',1); INSERTINTO book(bid,title,mid) VALUES(102,'Java Web开发',2); INSERTINTO book(bid,title,mid) VALUES(103,'EJB开发',2); INSERTINTO book(bid,title,mid) VALUES(105,'Android开发',1); INSERTINTO book(bid,title,mid) VALUES(107,'AJAX开发',1);
3、 删除父表之前必须首先先删除对应的子表，否则无法删除
DROPTABLE book PURGE; DROPTABLE member PURGE;
但是这样做明显很麻烦，因为对于一个未知的数据库，如果要按照此类方式进行，则必须首先知道其父子关系，所以在Oracle之中专门提供了一个强制性删除表的操作，即：不再关心约束，在删除的时候写上一句―CASCADE CONSTRAINT‖。
DROPTABLE member CASCADECONSTRAINT PURGE; DROPTABLE book CASCADECONSTRAINT PURGE;
此时，不关心子表是否存在，直接强制性的删除父表。
合理做法：在以后进行数据表删除的时候，最好是先删除子表，之后再删除父表。
六、修改约束
约束本身也属于数据库对象，那么也肯定可以进行修改操作，而且只要是修改都使用ALTER指令，约束的修改主要指的是以下两种操作：
?
为表增加约束：
ALTERTABLE 表名称 ADDCONSTRAINT 约束名称 约束类型(字段);
46
? 删除表中的约束：
ALTERTABLE 表名称 DROPCONSTRAINT 约束名称;
可以发现，如果要维护约束，肯定需要一个正确的名字才可以，可是在这五种约束之中，非空约束作为一个特殊的约束无法操作，现在有如下一张数据表：
DROPTABLE member CASCADECONSTRAINT PURGE;CREATETABLE member( mid NUMBER,
name VARCHAR2(50) NOTNULL, age NUMBER(3) );
范例：为表中增加主键约束
ALTERTABLE member ADDCONSTRAINT pk_mid PRIMARYKEY(mid);
增加数据：
INSERTINTO member(mid,name,age) VALUES(1,'张三',30);INSERTINTO member(mid,name,age) VALUES(2,'李四',300);
现在在member表中已经存在了年龄上的非法数据，所以下面为member表增加检查约束：
ALTERTABLE member ADDCONSTRAINT ck_age CHECK(age BETWEEN0AND250);
这个时候在表中已经存在了违反约束的数据，所以肯定无法增加。 范例：删除member表中的mid上的主键约束
ALTERTABLE member DROPCONSTRAINT pk_mid;
可是，跟表结构一样，约束最好也不要修改，而且记住，表建立的同时一定要将约束定义好，以后的使用之中建议就不要去改变了。
七、查询约束
在Oracle之中所有的对象都会在数据字典之中保存，而约束也是一样的，所以如果要想知道有哪些约束，可以直接查询―user_constraints‖数据字典：
SELECT owner,constraint_name,table_name FROM user_constraints;
但是这个查询出来的约束只是告诉了你名字，而并没有告诉在哪个字段上有此约束，所以此时可以查看另外一张数据字典表―user_cons_columns‖；
COL owner FOR A15;
COL constraint_name FOR A15;
47
COL table_name FOR A15;
COL column_name FOR A15;SELECT owner,constraint_name,table_name,column_name FROM user_cons_columns;
这些维护工作大部分由专门的DBA负责。
上课实例：
--约束
--check检查约束 altertable userinfo2 addconstraints c_sex
check(sex ='男'or sex ='女');
--unique:唯一约束，表明此字段内的东西不能重复 altertable userinfo2 addconstraints u_id unique(id);
--primary key:主键约束，也是一个唯一性限定，通常作为记录的唯一标示。 altertable userinfo2 addconstraints p_id primarykey(id);
--not null:非空约束，标示本字段中不可以使用空值null。 altertable userinfo2 modifynamenotnull;
--foreign key:外键约束，表明此表中依赖外表的特性。 altertable da
addconstraints f_userid foreignkey(userid)
references userinfo2 (id);
第6章 视图、序列、索引
一、视图
在之前所学习过的所有的SQL语法之中，查询操作是最麻烦的，如果程序开发人员将大量的精力都浪费在查询的编写上，则肯定影响代码的工作进度，所以一个好的数据库设计人员，除了根据业务的操作设计出数据表之外，还需要为用户提供若干个视图，而每一个视图包装了一条条复杂的SQL语句，视图的创建语法如下：
48
CREATE[OR REPLACE]VIEW 视图名称 AS 子查询;
范例：创建一张视图
CREATEVIEW myview AS
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) count,AVG(e.sal) avg FROM emp e,dept d
WHERE e.deptno(+)=d.deptno GROUPBY d.deptno,d.dname,d.loc;
现在已经创建好了一张视图，名称为myview，所以现在查询myview：
SELECT*FROM myview;
此时通过一个简单的视图查询操作，就可以完成之前的复杂SQL语句的功能，所以视图就是包装了SQL查询操作。
范例：创建一张包含简单查询语句的视图
DROPVIEW myview;CREATEVIEW myview AS SELECT*FROM emp WHERE deptno=20;
可是以上的操作实际上是属于一个视图的替换操作，所以此时也可以使用另外一种语法：
CREATEORREPLACEVIEW myview AS SELECT*FROM emp WHERE deptno=20;
此时表示的是，如果视图存在则替换，不存在则创建一张新的视图，视图的概念虽然好理解，但是在创建视图的时候存在两个选项。
?
选项一：WITH CHECK OPTION
上面所创建的视图，是存在一个创建条件的―WHERE deptno=20‖，那么如果现在更新视图中的这个条件呢？
UPDATE myview SET deptno=30WHERE empno=7369;
此时更新的是一张视图，但是视图本身并不是一个具体的数据表，而且现在更新的操作又是视图的创建条件，很明显这样的做法不可取，所以此时为了解决这个问题，可以加入WITH CHECK OPTION;
CREATEORREPLACEVIEW myview AS SELECT*FROM emp WHERE deptno=20 WITHCHECKOPTION;
此时再次执行视图的更新操作，出现以下错误提示：
ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规
49
意味着现在根本就不能去更新视图的创建条件。
?
选项二：WITH READ ONLY
虽然使用WITH CHECK OPTION可以保证视图的创建条件不被更新，但是其他的字段却允许更新。
UPDATE myview SET sal=9000WHERE empno=7369;
与之前的问题一样，视图本身不是具体的真实数据，而是一些查询语句，所以这样的更新并不合理，那么在创建视图的时候建议将其设置为只读视图：
CREATEORREPLACEVIEW myview AS SELECT*FROM emp WHERE deptno=20 WITHREADONLY;
此时再次发出更新的操作，则直接提示如下错误：
ORA-01733: 此处不允许虚拟列
而且一定要注意的是，以上给出的是一个简单的操作语句视图，如果现在视图中的查询语句是统计操作，则根本就不可能更新。
CREATEORREPLACEVIEW myview AS SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) count,AVG(e.sal) avg
FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUPBY d.deptno,d.dname,d.loc;
现在的信息是统计而来的，根本就不可能更新。
在一个项目之中，视图的数量有可能超过表的数量，因为查询语句会很多的。
二、同义词
同义词就是意思相近的一组词语，对于同义词的操作之前一直在使用，例如，现在有如下一个查询语句：
SELECT SYSDATE FROM dual;
在之前说过―dual‖是一张虚拟表，但是虚拟表也肯定应该有它的用户，经过查询可以发现，这张表是属于SYS用户的，但是这个时候就出现一个问题，在之前讲解过，不同的用户要想访问其他用户的表，则需要写上―用户.表名称‖，那么为什么此时scott用户访问的时候直接使用dual即可，而不是使用―sys.dual‖呢，这个实际上就是同义词的应用，dual表示的是sys.dual的同义词，而同义词在Oracle之中称为SYNONYM，同义词的创建语法如下：
CREATE[PUBLIC] SYSNONYM 同义词的名称 FOR 用户名.表名称;
50
数据库Oracle笔记
白马非马
2016年8月21日
1
第1章 数据库Oracle __________________________________________________________ 4
一、主流数据库 ___________________________________________________________ 4 二、结构化查询语言 _______________________________________________________ 4 三、Oracle安装 ___________________________________________________________ 4 第2章 查询和排序 ____________________________________________________________ 4
一、数据查询 _____________________________________________________________ 4
1.基本语句 ___________________________________________________________ 4 2.字段别名 ___________________________________________________________ 4 3.连字运算符 || ______________________________________________________ 5 4.去重复行 DISTINCT ___________________________________________________ 5 5.限定行 WHERE ______________________________________________________ 5 6.优先规则 ___________________________________________________________ 5 上课实例： ___________________________________________________________ 5 二、排序 _________________________________________________________________ 6 第3-1章 单行函数 ____________________________________________________________ 7
一、字符函数 _________________________________________________________ 7 二、数字函数 _____________________________________________________________ 8 三、日期函数 _____________________________________________________________ 9 四、转换函数 ____________________________________________________________ 10 五、通用函数 ____________________________________________________________ 11 习题讲解 ________________________________________________________________ 12 第3-2章 多表查询 ___________________________________________________________ 15
一、多表查询 ____________________________________________________________ 15
上课实例： __________________________________________________________ 16 二、 内连接与外连接 _____________________________________________________ 16
A.内连接 ____________________________________________________________ 16 B.外连接 ____________________________________________________________ 17 上课实例： __________________________________________________________ 18 补充： ______________________________________________________________ 19
第4章 子查询和组函数 _______________________________________________________ 19
一、子查询 ______________________________________________________________ 19
1. 单行子查询 _______________________________________________________ 20 2.多行子查询 ________________________________________________________ 21 上课实例： __________________________________________________________ 22 二、组函数（统计函数） __________________________________________________ 23
1、统计函数 _________________________________________________________ 23 2、分组查询 _________________________________________________________ 23 注意点：WHERE和HAVING的区别______________________________________ 26 上课实例： __________________________________________________________ 26
第5-1章 操作数据 ___________________________________________________________ 27
一、数据增加 ____________________________________________________________ 28
上课实例： __________________________________________________________ 28 二、数据修改 ____________________________________________________________ 28
上课实例： __________________________________________________________ 29
2
三、数据删除 ____________________________________________________________ 29
上课实例： __________________________________________________________ 29 四、 事务处理 ___________________________________________________________ 30 五、数据伪列 ____________________________________________________________ 30 第5-2章 表的管理 ___________________________________________________________ 33
一、常用的数据字段 ______________________________________________________ 33 二、表的创建 ____________________________________________________________ 34 三、表的复制 ____________________________________________________________ 35 四、为表重命名 __________________________________________________________ 35 五、表的截断 ____________________________________________________________ 36 六、表的删除 ____________________________________________________________ 36 七、Oracle 10g的新特性：闪回技术 ________________________________________ 36 八、修改表结构 __________________________________________________________ 37 九、思考题 ______________________________________________________________ 38 上课实例： ______________________________________________________________ 39 第5-3章 约束 _______________________________________________________________ 39
一、非空约束（NOT NULL）：NK ___________________________________________ 39 二、唯一约束（UNIQUE）：UK _____________________________________________ 40 三、主键约束（Primary Key）：PK __________________________________________ 41 四、检查约束（Check）：CK _______________________________________________ 42 五、主-外键约束 _________________________________________________________ 43 六、修改约束 ____________________________________________________________ 46 七、查询约束 ____________________________________________________________ 47 上课实例： ______________________________________________________________ 48 第6章 视图、序列、索引 _____________________________________________________ 48
一、视图 ________________________________________________________________ 48 二、同义词 ______________________________________________________________ 50 三、索引 ________________________________________________________________ 51 四、集合 ________________________________________________________________ 52 五、序列 ________________________________________________________________ 53
上课实例： __________________________________________________________ 55
第7章 JDBC ________________________________________________________________ 55
一、通过名称登录 ________________________________________________________ 55 二、查询Oracle数据库 ___________________________________________________ 57 三、更新Oracle数据库数 _________________________________________________ 59
3
第1章 数据库Oracle
一、主流数据库
1.Oracle数据库 2.MySQL数据库 3.SQL Server 4.DB2
二、结构化查询语言
SQL包括四部分
数据定义语言（DDL）,用于定义SQL模式、表、视图、索引等数据库对象结构。 数据操作语言（DML），用于插入、删除和更改数据。
数据查询语言（DQL）,用于查询数据、通常将数据操作语言和数据查询语言系统统称为数据操作语言。
数据控制语言（DCL），用于对表、视图等的授权、完整性规则的而描述和事务控制等。
三、Oracle安装
略
第2章 查询和排序
一、数据查询
1.基本语句 1. 2.
* ： 表示选择表中的所有字段
字段名： 选择表中的字段名称，可以选择多个字段，各个字段间用逗号分隔。 表达式：由字段、函数等组成的表达式。 表名：指定包含字段的表。
WHERE条件：查询的条件，可以通过该条件进行选择。
ORDER BY 字段名：要求在查询的结果中进行排序，默认是升序ASC，降序DESC。
SELECT 字段列表 FROM 表名 WHERE 条件
ORDER BY 字段名1 [ASC|DESC][,字段名2 [ASC|DESC]...];
2.字段别名
在SELECT所选字段后面可以指定别名，字段名和别名之间用空格分开。在默认情况下，别名标题用大写字母显示，如果别名中包含空格或特殊字符（列如#或&），或者大小写敏感，需要在别名放在双引号中。最好加上AS关键字。
4
3.连字运算符 ||
使用连字运算符，可以进行字段与字段、字段与表达式、字段与常数之间的连接，来创建一个字符表达式，连字运算符两边的字段被合成一个单个的列输出。
4.去重复行 DISTINCT
Select DISTINCT...FROM ...
5.限定行 WHERE
简单比较条件：=，>,>+,<,<=,<>,!=,^=(后三个都是不等于) BETWEEN...AND...: 查询显示上下限之间的行。
IN：IN条件也称为成员条件，用以查询出所选字段中符合指定的一组值中的一个。
LIKE：LIKE条件也称为通配符，可以使用两个通配符来构造需要匹配的字符模板，其中“%”表示零个或多个字符，“_”表示一个字符，这里提到的字符可以说文字也可以是数字。 注意：需要匹配“%”和“_”时，可以使用ESCAPE选项，该选项指定换码符是什么。如： 想搜索emp表中job_id包含“AD_”的雇员职位编号、名字和薪水信息，可以用下面SQL语句：
SELECT job_id,name,sal FROM emp WHERE job_id LIKE’-$_%’ ESCAPE’$’;
此时ESCAPE 选项指定美元符“$”为换码符，即不将美元符号后面的”_”当作通配符看待。 Null :包括is null条件和is not null条件。判断空值时不能使用“=”，因为NUll不能等于或不等于任何值。
逻辑and or not: 与或非。
6.优先规则
默认的优先顺序： （1）算数运算 （2）连字运算 （3）比较运算
（4）Is [not] null、like、[not] in。 （5）[not] between。 （6）Not逻辑条件。 （7）And 逻辑条件。 （8）Or 逻辑条件。
上课实例：
--查询全部员工的全部信息 select*from EMP;
--查询全部员工的部分信息
select ename,job from emp;
--查询所有员工薪水在800~1500的员工
select*from emp where sal between800and1500; select*from emp where sal>=800and sal <=1500;
--查询所有员工工资大于1500的员工
5
DROPTABLE member PURGE;CREATETABLE member( mid NUMBER,
name VARCHAR2(50) NOTNULL, email VARCHAR2(50),
CONSTRAINT UK_email UNIQUE(email) );
以后再次增加错误数据时，提示信息如下：
ORA-00001: 违反唯一约束条件 (SCOTT.UK_EMAIL)
已经可以很明确的提示用户错误的位置。
三、主键约束（Primary Key）：PK
主键约束 = 非空约束 + 唯一约束，在之前设置唯一的约束的时候发现可以设置为null，而如果现在使用了主键约束之后则不能为空，而且主键一般作为数据的唯一的一个标记出现，例如：人员的ID。 范例：建立主键约束
DROPTABLE member PURGE;CREATETABLE member( mid NUMBER PRIMARY KEY, name VARCHAR2(50) NOTNULL );
范例：增加正确的数据
INSERTINTO member(mid,name) VALUES(1,'张三');
范例：错误的数据 —— 主键设置为null
INSERTINTO member(mid,name) VALUES(null,'张三');
错误信息，与之前的非空约束的错误信息提示是一样的；
ORA-01400: 无法将 NULL 插入 (\
范例：错误的数据 —— 主键重复
INSERTINTO member(mid,name) VALUES(1,'张三');
错误信息，这个错误信息就是唯一约束的错误信息，但是信息不明确，因为没起名字。
ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005276)
所以为了约束的使用方便，下面为主键约束起一个名字。
DROPTABLE member PURGE;CREATETABLE member(
41
mid NUMBER,
name VARCHAR2(50) NOTNULL, CONSTRAINT pk_mid PRIMARYKEY(mid) );
此时，重复插入数据，则错误信息如下：
ORA-00001: 违反唯一约束条件 (SCOTT.PK_MID)
从正常的开发角度而言，一张表一般都只设置一个主键，但是从SQL语法的规定而言，一张表却可以设置多个主键，而此种做法称为复合主键，例如：参考如下代码：
DROPTABLE member PURGE;CREATETABLE member( mid NUMBER,
name VARCHAR2(50) NOTNULL,
CONSTRAINT pk_mid PRIMARYKEY(mid,name) );
在复合主键的使用之中，只有两个字段的内容都一样的情况下，才被称为重复数据。 范例：插入正确的数据
INSERTINTO member(mid,name) VALUES(1,'张三');INSERTINTO member(mid,name) VALUES(1,'李四');INSERTINTO member(mid,name) VALUES(2,'李四');
范例：插入错误的数据
INSERTINTO member(mid,name) VALUES(1,'张三');
错误信息：
ORA-00001: 违反唯一约束条件 (SCOTT.PK_MID)
但是从开发的实际角度而言，一般都不使用复合主键，所以这个知识只是作为其相关的内容做一个介绍。只要是数据表，永远都只设置一个主键。
四、检查约束（Check）：CK
检查约束指的是为表中的数据增加一些过滤条件，例如：
? ?
设置年龄的时候范围是：0~200； 设置性别的时候应该是：男、女；
范例：设置检查约束
DROPTABLE member PURGE;CREATETABLE member( mid NUMBER,
42
name VARCHAR2(50) NOTNULL, sex VARCHAR2(10) NOTNULL, age NUMBER(3),
CONSTRAINT pk_mid PRIMARYKEY(mid),
CONSTRAINT ck_sex CHECK(sex IN('男','女')), CONSTRAINT ck_age CHECK(age BETWEEN0AND200) );
范例：增加正确的数据
INSERTINTO member(mid,name,sex,age) VALUES(1,'张三','男','26');
范例：增加错误的性别 —— ORA-02290: 违反检查约束条件 (SCOTT.CK_SEX)
INSERTINTO member(mid,name,sex,age) VALUES(2,'李四','非','26');
范例：增加错误的年龄 —— ORA-02290: 违反检查约束条件 (SCOTT.CK_AGE)
INSERTINTO member(mid,name,sex,age) VALUES(2,'李四','女','260');
检查的操作就是对输入的数据进行一个过滤。
五、主-外键约束
之前的四种约束都是在单张表中进行的，而主-外键约束是在两张表中进行的，这两张表是存在父子关系的，即：子表中某个字段的取值范围由父表所决定。
例如，现在要求表示出一种关系，每一个人有多本书，应该定义两张数据表：member（主）、book（子）；
DROPTABLE member PURGE;DROPTABLE book PURGE;CREATETABLE member( mid NUMBER,
name VARCHAR2(50) NOTNULL, CONSTRAINT pk_mid PRIMARYKEY(mid) );CREATETABLE book( bid NUMBER,
title VARCHAR2(50) NOTNULL, mid NUMBER,
CONSTRAINT pk_bid PRIMARYKEY(bid) );
此时只是根据要求建立了两张独立的数据表，那么下面插入几条数据：
INSERTINTO member(mid,name) VALUES(1,'张三');
43
INSERTINTO member(mid,name) VALUES(2,'李四');
INSERTINTO book(bid,title,mid) VALUES(101,'Java开发',1); INSERTINTO book(bid,title,mid) VALUES(102,'Java Web开发',2); INSERTINTO book(bid,title,mid) VALUES(103,'EJB开发',2); INSERTINTO book(bid,title,mid) VALUES(105,'Android开发',1); INSERTINTO book(bid,title,mid) VALUES(107,'AJAX开发',1);
要想验证这个数据是否有意义，最简单的做法，就是写两个查询。 范例：统计每个人员拥有书的数量
SELECT m.mid,m.name,COUNT(b.bid)FROM member m,book b WHERE m.mid=b.mid GROUPBY m.mid,m.name;
范例：查询出每个人员的编号，姓名，拥有书的名称
SELECT m.mid,m.name,b.title FROM member m,book b WHERE m.mid=b.mid;
即，现在的book.mid字段应该是与member.mid字段相关联的，但是由于本程序没有设置约束，所以，现在以下的数据也是可以增加的：
INSERTINTO book(bid,title,mid) VALUES(108,'PhotoShop使用手册',3); INSERTINTO book(bid,title,mid) VALUES(109,'FLEX开发手册',8);
现在增加了两条新的记录，而且记录可以保存在数据表之中，但是这两条记录没有意义，因为member.mid字段的内容没有3和8，而要想解决这个问题就必须依靠外键约束来解决。 让book.mid的字段的取值由member.mid所决定，如果member.mid的数据真实存在，则表示可以更新。
DROPTABLE member PURGE;DROPTABLE book PURGE;CREATETABLE member( mid NUMBER,
name VARCHAR2(50) NOTNULL, CONSTRAINT pk_mid PRIMARYKEY(mid) );CREATETABLE book( bid NUMBER,
title VARCHAR2(50) NOTNULL, mid NUMBER,
CONSTRAINT pk_bid PRIMARYKEY(bid),
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)
44
);
此时，只是增加了一个约束，这样一来如果输入的数据有错误，则会出现如下的提示：
ORA-02291: 违反完整约束条件 (SCOTT.FK_MID) - 未找到父项关键字
因为member.mid没有指定的数据，所以book.mid如果数据有错误，则无法执行更新操作。
使用外键的最大好处是控制了子表中某些数据的取值范围，但是同样带来了不少的问题； 1、 删除数据的时候，如果主表中的数据有对应的子表数据，则无法删除； 范例：删除member表中mid为1的数据
DELETEFROM member WHERE mid=1;
错误提示信息：―ORA-02292: 违反完整约束条件 (SCOTT.FK_MID) - 已找到子记录‖。 此时，只能先删除子表记录，之后再删除父表记录：
DELETEFROM book WHERE mid=1;DELETEFROM member WHERE mid=1;
但是这种操作明显不方便，如果说现在希望主表数据删除之后，子表中对应的数据也可以删除的话，则可以在建立外键约束的时候指定一个级联删除的功能，修改数据库创建脚本：
DROPTABLE member PURGE;DROPTABLE book PURGE;CREATETABLE member( mid NUMBER,
name VARCHAR2(50) NOTNULL, CONSTRAINT pk_mid PRIMARYKEY(mid) );CREATETABLE book( bid NUMBER,
title VARCHAR2(50) NOTNULL, mid NUMBER,
CONSTRAINT pk_bid PRIMARYKEY(bid),
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ONDELETECASCADE );
此时由于存在级联删除的操作，所以主表中的数据删除之后，对应的子表中的数据也都会被同时删除。
2、 删除数据的时候，让子表中对应的数据设置为null
当主表中的数据删除之后，对应的子表中的数据相关项也希望将其设置为null，而不是删除，此时，可以继续修改数据表的创建脚本：
DROPTABLE member PURGE;DROPTABLE book PURGE;CREATETABLE member(
45
1、 ROWNUM
ROWNUM从单词含义上讲应该表示的是行号，实际上ROWNUM为每一个显示的记录都会自动的随着查询生成行号，例如，下面通过两个代码观察：
SELECT ROWNUM,empno,ename,job,hiredate,sal FROM emp;
此时的ROWNUM行号并不是永久固定的；
SELECT ROWNUM,empno,ename,job,hiredate,sal FROM emp WHERE deptno=30;
是每次动态的重新生成的，那么既然有了ROWNUM之后，下面就可以实现数据的部分显示；
范例：查询前5条记录
SELECT ROWNUM,empno,ename,job,hiredate,sal FROM empWHERE ROWNUM<=5;
范例：查询6-10条记录
按照正常的思维肯定是直接进行BETWEEN…AND的判断；
SELECT ROWNUM,empno,ename,job,hiredate,sal FROM empWHERE ROWNUM BETWEEN6AND10;
这个时候并没有返回任何的数据，因为ROWNUM不是真实列，而要想真正的实现这种查询，思路是：先查询前10条记录，之后再显示后5条记录，要依靠子查询完成。
SELECT*FROM(
SELECT ROWNUM m,empno,ename,job,hiredate,sal FROM emp WHERE ROWNUM<=10) tempWHEREtemp.m>5;
如果现在按照这个思路，下面就可以给出日后程序中所需要分页的功能实现。 范例：显示前5条记录
当前所在页（currentPage）为1； 每页显示的记录长度（lineSize）为5； 第一页：
SELECT*FROM(
SELECT ROWNUM m,empno,ename,job,hiredate,sal FROM emp WHERE ROWNUM<=5) tempWHEREtemp.m>0;
范例：显示中间的5条记录 当前所在页（currentPage）为2； 每页显示的记录长度（lineSize）为5；
31
第二页：
SELECT*FROM(
SELECT ROWNUM m,empno,ename,job,hiredate,sal FROM emp WHERE ROWNUM<=10) tempWHEREtemp.m>5;
范例：显示第三页的内容，currentPage=3，lineSize=10；
SELECT*FROM(
SELECT ROWNUM m,empno,ename,job,hiredate,sal FROM emp WHERE ROWNUM<=15) tempWHEREtemp.m>10;
以上的程序就是分页显示操作的核心代码。 2、 ROWID
ROWID表示的是每一行数据保存的物理地址的编号，例如，观察如下的查询：
SELECT ROWID,deptno,dname,loc FROM dept;
每一条记录的ROWID都不会重复，所以即便表中所有列的数据内容都重复了，ROWID也是不会重复的，而且以一个ROWID为例，说明ROWID组成，例如：AAAL+XAAEAAAAANAAA
? ? ? ?
数据对象号：AAAL+X； 相对文件号：AAE； 数据块号：AAAAAN； 数据行号：AAA；
面试题：请删除表中的重复记录
现在项目之中由于管理不善，所以导致出现了许多重复的信息，为了说明问题，下面为表中增加若干条记录。
INSERTINTO dept(deptno,dname,loc) VALUES (21,'RESEARCH','DALLAS');INSERTINTO dept(deptno,dname,loc) VALUES (31,'SALES','CHICAGO');INSERTINTO dept(deptno,dname,loc) VALUES (32,'SALES','CHICAGO');INSERTINTO dept(deptno,dname,loc) VALUES (41,'OPERATIONS','BOSTON');INSERTINTO dept(deptno,dname,loc) VALUES (42,'OPERATIONS','BOSTON');INSERTINTO dept(deptno,dname,loc) VALUES (43,'OPERATIONS','BOSTON');COMMIT;
现在要求删除掉所有重复的记录，保留最早增加的记录内容。
先按照查询的方式做，首先找到所有重复的数据，重复的数据就比较容易了，按照部门名称及位置分组，之后统计个数，如果个数大于1，则表示重复。
SELECT dname,loc,MIN(ROWID)FROM deptGROUPBY dname,locHAVINGCOUNT(deptno)>1;
32
此时返回的三个ROWID，与最早的ROWID比较一下。
数据插入的早，ROWID就小，所以下一步首先必须确定的是不能删除的ROWID，去掉之前的限制（COUNT()>1），表示按照部门名称及位置分组，取出每一个最小的ROWID；
SELECTMIN(ROWID)FROM deptGROUPBY dname,loc;
以上的数据是删除的，所以下面编写DELETE语句：
DELETEFROM deptWHERE ROWID NOTIN( SELECTMIN(ROWID) FROM dept
GROUPBY dname,loc);COMMIT;
此时就是根据ROWID完成的，而这道题目也就是ROWID现在为止的唯一可以看的见的作用。
在所有的伪列之中，只有ROWNUM是以后开发之中最为重要的部分，一定要掌握，而对于ROWID知道就行了，不必做太深入的了解。
第5-2章 表的管理
对于数据库而言实际上每一张表都表示的是一个数据库的对象，而数据库对象指的就是DDL定义的所有操作，例如：表、视图、索引、序列、约束等等，都属于对象的操作，所以表的建立就是对象的建立，而对象的操作主要分为以下三类语法：
? ? ?
创建对象：CREATE 对象名称 …； 删除对象：DROP 对象名称 …； 修改对象：ALTER 对象名称 …；
一、常用的数据字段
每一张数据表实际上都是由若干个字段所组成，而每一个字段都会有其对应的数据类型，而在Oracle之中，常用的数据类型有如下几种： № 数据类型 关键字 描述 1 字符串 VARCHAR2其中n表示的是字符串所能保存的最大长度，(n) 基本上保存200个左右的内容 2 整数 NUMBER(n表示最多为n位的整数，有时候也可以使用 33
) INT代替 3 小数 NUMBER(n其中m为小数位，n-m为整数位，有时候也,m) 可以使用FLOAT代替 存放日期-时间 4 日期 DATE 5 大文本 CLOB 可以存储海量文字（4G），例如存储《三国演艺》、《红楼梦》 6 大对象 BLOB 存放二进制数据，例如：电影、MP3、图片、文字 一般在开发之中使用最多的：VARCHAR2()、NUMBER、DATE、CLOB，而对于BLOB字段一般使用较少，首先BLOB可以存放4G的二进制数据，但是存放进去之后，一是数据库过于庞大，二是读取不方便；
二、表的创建
如果现在要想进行表的创建，可以使用如下的操作语法：
CREATETABLE 表名称 (
字段1 数据类型 [DEFAULT默认值], 字段2 数据类型 [DEFAULT默认值], … …,
字段n 数据类型 [DEFAULT默认值] );
下面创建一张成员表（member），有如下保存的信息：姓名、年龄、生日、个人简介。
CREATETABLE member (
name VARCHAR2(50) DEFAULT'无名氏', age NUMBER(3),
birthday DATE DEFAULT SYSDATE, content CLOB );
表创建成功之后，下面开始向表中增加数据：
34
INSERTINTO member(name,age,birthday,content) VALUES('张三',20,TO_DATE('1990-08-12','yyyy-mm-dd'),'一个好人'); INSERTINTO member(age,content) VALUES(20,'一个好人');
一定要再次记住，表的创建是属于数据库对象的创建，所以使用的是CREATE语法。
三、表的复制
在之前学习过表的复制操作，下面给出其完整的操作语法：
CREATETABLE 复制表名称 AS 子查询;
范例：复制一张只包含20部门雇员信息的表
CREATETABLE emp20 ASSELECT*FROM emp WHERE deptno=20;
范例：现在要求将emp表的表结构复制出来，不要数据 —— 写一个永远满足不了的条件即可。
CREATETABLE empnull ASSELECT*FROM emp WHERE1=2;
但是以上的语法只是Oracle数据库所支持的操作，其他的数据库语法上会有一些区别。
四、为表重命名
在Oracle数据库之中，所有的数据实际上都是通过数据字典保存的，例如，在之前曾经使用过如下的一种查询：
SELECT*FROM tab;
以上就是一个数据字典，而在Oracle之中，提供了三种类型的数据字典，最常用的是：dba_、user_，所以下面查询一个user_tables数据字典：
SELECT*FROM user_tables;
也就是说Oracle中的所有数据都是按照文件保存的，那么所有的内容都会在数据字典中注册，既然这样，所谓的修改表名称实际上对于Oracle而言就相当于修改一条数据而已，而修改表名称的方法如下：
RENAME 旧的表名称 TO 新的表名称;
范例：将member表更名为person表
RENAME member TO person;
但是这种操作由于是Oracle数据库所独有的一种特性，所以了解即可，不用做深入的掌握。
35
五、表的截断
在之前曾经讲解过一个删除表数据的操作，使用的是DELETE操作，但是这种删除操作本身有一个特点，即：可以进行事务的回滚，也就是说删除之后并不会立刻释放数据的资源，如果现在希望彻底释放掉一张表所占用的全部资源（表空间、索引等等）就可以使用截断表的语法，此语法如下：
TRUNCATETABLE 表名称;
范例：截断person表
TRUNCATETABLE person;
但是这种语法本身只是Oracle所有的，所以只做了解即可。
六、表的删除
表的删除操作指的是数据库对象的删除，既然是删除则使用DROP语句，删除表的语法如下：
DROPTABLE 表名称;
范例：删除person表
DROPTABLE person;
七、Oracle 10g的新特性：闪回技术
在Oracle 10g之后，为了预防用户的误删除表操作，专门提供了回收站的功能，用户所删除的表默认情况下会在一个回收站之中保存，而用户也可以通过回收站，进行表的恢复，所以此技术称为闪回（FLASHBACK）； 范例：查看回收站
SHOW RECYCLEBIN;
这个时候可以发现所有已经删除的表都在回收站之中保存，那么下面就可以使用如下的语法恢复表：
FLASHBACK TABLE 表名称 TO BEFORE DROP;
范例：恢复myemp表
FLASHBACK TABLE myemp TO BEFORE DROP;
当然，现在也可以直接删除掉回收站中的一些数据表，语法如下：
PURGE TABLE 表名称;
36
范例：删除回收站中的person表
PURGE TABLE person;
范例：清空回收站
PURGE RECYCLEBIN;
如果现在希望删除一张表，而又不希望其进入到回收站之中，则可以在删除的时候增加PURGE。
DROPTABLE myemp PURGE;
这种技术是在Oracle 10g之后才有的，而Oracle 11g也是存在的。
问题：现在在回收站之中存在了一张tab表，而后又建立了一张tab表，那么如果从回收站中恢复的话，可以吗？ 答：无法恢复。
八、修改表结构
如果一张建立好的数据表，发现其初期的结构已经不满足于后期的使用要求，则可以进行表的修改操作，而表的修改操作实际上就是数据库对象的修改操作，使用ALTER指令完成，例如，现在有如下一张表：
CREATETABLE member ( mid NUMBER, name VARCHAR2(50) );
现在希望可以向表中增加字段，所以此时可以采用如下的语法完成：
ALTERTABLE 表名称 ADD(列名称 数据类型 [DEFAULT 默认值], 列名称 数据类型 [DEFAULT 默认值],…);
范例：为member表增加字段
ALTERTABLE member ADD(age NUMBER(3), birthday DATE DEFAULT SYSDATE);
如果增加的数据列没有默认值，则所有已有的数据的列的内容都是null，而如果增加的列指定了DEFAULT默认值的话，则所有已有的数据列都是设置的默认值。 现在也可以修改已有的表结构，此时的语法如下：
ALTERTABLE 表名称 MODIFY(列名称 数据类型 [DEFAULT 默认值], 列名称 数据类型 [DEFAULT 默认值],…);
37
范例：将name字段的默认值定义为无名氏
ALTERTABLE member MODIFY(name VARCHAR2(100) DEFAULT'无名氏');
虽然在SQL语法之中以及Oracle数据库之中，都给出了修改表结构的操作，但是这种操作能不使用就不使用，从大型数据库来讲，世界上性能最高的数据库是IBM DB2，但是IBM DB2本身有一个平台的限制问题，所以如果说是跨平台的数据库则是Oracle数据库的性能最高。
在IBM DB2数据库之中是不允许修改表结构的，即：表建立完成之后就不能修改了，所以以后在开发之中也尽可能的不要去修改表结构。
九、思考题
现在要求建立一张nation表，表中有一个name字段，里面保存四条记录：中国、美国、巴西、荷兰，要求通过查询实现如下的操作效果：
中国 美国 中国 巴西 中国 荷兰 美国 巴西 美国 中国 美国 荷兰
剩下的依次类推，现在要求建立新的表并完成此查询的操作。
本题目的主要目的并不是在于查询的编写上，而是在于规范化数据库创建脚本的格式，以后只要是碰到了类似的要求，首先必须编写一个数据库创建脚本，这个脚本的要求如下：
1、 本文件的文件名称后缀必须是―*.sql‖； 2、 先删除相应的数据表； 3、 编写创建表的语句； 4、 增加测试数据； 5、 提交事务；
--1、删除表DROPTABLE nation PURGE; --2、创建表CREATETABLE nation( name VARCHAR2(50) );
38
--3、测试数据INSERTINTO nation(name) VALUES('中国'); INSERTINTO nation(name) VALUES('美国'); INSERTINTO nation(name) VALUES('巴西'); INSERTINTO nation(name) VALUES('荷兰'); --4、事务提交COMMIT;
本程序如果要想完成依靠笛卡尔积完成，属于表的自身关联。
SELECT n1.name,n2.nameFROM nation n1,nation n2WHERE n1.name<>n2.name;
而且以后如果面试之中，出现了一些复杂的查询，建议把脚本写全了。
上课实例：
--修改表 alter table 表名称 操作 操作内容 --添加列
altertable userinfo2
add(age number,sex varchar2(2)); select*from userinfo2; --修改列
altertable userinfo2
modify(age number,sex varchar(2)); select*from userinfo2; --删除列
altertable userinfo2 drop(age);
select*from userinfo2;
第5-3章 约束
表虽然建立完成了，但是表中的数据是否合法并不能有所检查，而如果要想针对于表中的数据做一些过滤的话，则可以通过约束完成，约束的主要功能是保证表中的数据合法性，按照约束的分类，一共有五种约束：非空约束、唯一约束、主键约束、检查约束、外键约束。
一、非空约束（NOT NULL）：NK
当数据表中的某个字段上的内容不希望设置为null的话，则可以使用NOT NULL进行指定。 范例：定义一张数据表
DROPTABLE member PURGE;CREATETABLE member( mid NUMBER,
name VARCHAR2(50) NOTNULL
39
);
因为此时存在了―NOT NULL‖约束，所以下面插入两组数据。 范例：正确的数据
INSERTINTO member(mid,name) VALUES(1,'张三');INSERTINTO member(mid,name) VALUES(null,'李四');INSERTINTO member(name) VALUES('王五');
范例：插入错误的数据
INSERTINTO member(mid,name) VALUES(9,null);INSERTINTO member(mid) VALUES(10);
此时了出现的错误提示：
ORA-01400: 无法将 NULL 插入 (\
本程序之中，直接表示出了―用户‖.―表名称‖.―字段‖出现了错误。
二、唯一约束（UNIQUE）：UK
唯一约束指的是每一列上的数据是不允许重复的，例如：email地址每个用户肯定是不重复的，那么就使用唯一约束完成。
DROPTABLE member PURGE;CREATETABLE member( mid NUMBER,
name VARCHAR2(50) NOTNULL, email VARCHAR2(50) UNIQUE );
范例：插入正确的数据
INSERTINTO member(mid,name,email) VALUES(1,'张三
','mchina_tang@qq.com');INSERTINTO member(mid,name,email) VALUES(2,'李四',null);
范例：插入错误的数据 —— 重复数据
INSERTINTO member(mid,name,email) VALUES(3,'王五','mchina_tang@qq.com');
此时会出现如下的错误提示：
ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005272)
可是这个时候的错误提示与之前的非空约束相比并不完善，因为现在只是给出了一个代号而已，这是因为在定义约束的时候没有为约束指定一个名字，所以由系统默认分配了，而且约束的名字建议的格式―约束类型_字段‖，例如：―UK_email‖，指定约束名称使用CONSTRAINT完成。
40