java李兴华Oracle笔记

oracle的数据库学习笔记，李兴华老师视频的

分类： Oracle 转

1、   多表查询：同时从多张表中取数据的形式，但是多表查询会存在笛卡尔积的问题，而消除此问题可以使用连接的方式完成（内连接、外连接（左外连接、右外连接、全外连接）），虽然连接的方式可以消除笛卡尔积，但是这个积依然存在，所以多表查询从性能上来讲是很差的；

2、   统计函数：COUNT()、AVG()、MAX()、MIN()、SUM()；

3、   分组使用GROUP BY子句完成，在GROUP BY子句之中允许指定多个分组字典，而SELECT子句里面只能出现分组字段以及统计函数，其他字段不允许出现。分组函数本身允许嵌套，嵌套之后的查询里面不能再出现任何的其他字段，包括分组字段。如果要对分组后的数据再次进行过滤，则使用HAVING子句完成；

4、   子查询：在一个查询之中嵌入了若干个小的查询称为子查询，子查询一般出现在以下子句中较多：

         · WHERE子句：返回的数据为单行单列，或者是单行多列，或者是多行单列（IN、ANY、ALL）；

         · HAVING子句：返回的数据为单行单列，而且是在有分组的情况下才去使用；

         · FROM子句：返回的结果是多行多列的数据，按照一张临时表的方式进行操作。如果在一个查询之中需要使用统计数据，但是又不能直接使用统计函数的情况下，可以考虑在FROM子句之后编写子查询。

2.2、本次预计讲解的知识点

1、   数据的更新操作及事务处理操作；

2、   数据表的定义与管理；

3、   约束的设置；

4、   数据伪列的使用；

5、   SQL Developer工具的使用；

3、具体内容

3.1、数据更新操作（重点）

         DML主要包含两个功能：查询、更新，对于查询操作在之前已经讲解完成，而更新操作也是一个重点的内容，但是考虑到以后scott中的数据表的内容还要使用，所以在讲解之前首先将emp表复制为一张myemp表。

范例：复制emp表

CREATE TABLE myemp AS SELECT * FROM emp ;

         而这种复制表的结构只有Oracle才有，其他数据库的语法是不一样的。

面试题：请问在SQL Server之中如何进行数据表的复制？

         答案：略。。。

         而数据的更新操作分为三种：增加、修改、删除。

3.1.1、数据的增加操作

         如果要进行数据的增加操作，可以使用如下的语法完成：

INSERT INTO 表名称 [(列1，列2，....)] VALUES (值1，值2，...) ;

         但是有一点需要注意的是，在使用INSERT语句增加数据的时候，它所设置的数值有如下要求：

                  ·数字：直接编写，例如：30；

                  ·字符串：加上一个“'”声明，例如：'Hello'；

                  ·日期：按照已有的日期格式编写字符串（'28-9月 -81'）、使用TO_DATE()函数转换、SYSDATE；

范例：为myemp数据表增加一条新的记录

         ·完整语法格式：要明确的写出数据增加时所需要的数据列

INSERT INTO myemp (empno,job,ename,hiredate,sal,comm,mgr,deptno)

         VALUES (8888,'清洁工','张三',TO_DATE('1989-09-12','yyyy-mm-dd'),5000,2000,7369,40) ;

         ·简化语法格式：不明确的写出列的名称，要求与表中的数据列的顺序一致

INSERT INTO myemp

         VALUES (9999,'李四','清洁工',7369,TO_DATE('1989-09-12','yyyy-mm-dd'),8000,2000,40) ;

         从开发的角度而言，永远都要使用完整格式编写，这样可以方便进行错误的调试。

3.1.2、数据的修改操作

         如果现在要修改数据表中的数据记录，则可以使用如下语法完成：

UPDATE 表名称 SET 列1=值1，列2=值2，... [WHERE 更新条件(s)] ;

范例：要求将所有雇员雇佣日期修改为今天，取消所有雇员的奖金

UPDATE myemp SET hiredate=SYSDATE,comm=null ;

范例：要求将所有雇员的工资增长10%

UPDATE myemp SET sal=sal*1.1 ;

         但是这种更新操作属于更新一张完整数据表的操作，所有记录都要修改，那么考虑这样一个问题：假设现在每修改一条记录，所花费的时间是0.001秒，那么如果说现在一张数据表中有500W条记录，这种操作所耗费的时间是：84分，按照数据库的机制在这84分钟之内，所有的用户都无法在进行数据的更新操作，所以记住了，如果对于数据的更新操作，没有一个正常人说要更新表中的正常记录；

范例：要求现在将雇员编号是7369的雇员的工资修改为9000，奖金修改为500，而且职位修改为MANAGER；

UPDATE myemp SET sal=9000,comm=500,job='MANAGER' WHERE empno=7369 ;

范例：要求将所有低于公司平均工资的雇员工资每个雇员增长1000

UPDATE myemp SET sal=sal+1000

WHERE sal<(SELECT AVG(sal) FROM myemp) ;

3.1.3数据的删除操作

         如果现在要删除数据表中的数据记录，则可以使用如下语法完成：

DELETE FROM 表名称 [WHERE 条件(s)] ;

范例：删除职位是清洁工的雇员

DELETE FROM myemp WHERE job='清洁工' ;

范例：要求删除雇员编号是7566，7903，7885雇员信息

DELETE FROM myemp WHERE empno IN (7566,7788,7885) ;

         在进行更新操作的时候，都会返回数据的更新行数，如果删除的时候删除条件不能满足，则没有任何的数据被删除。

         如果说现在在删除的时候不指定删除条件，则表示删除全部。

DELETE FROM myemp ;

         但是这种操作在开发之中也少使用。

3.2、事务处理（重点）

         数据的更新操作由于要对数据本身进行更改，那么就与查询相比存在了许多不安全的因素，例如，现在有如下一个业务操作：

                  · A、从赵XX的帐户上转出5000W给王飞；

                  · B、在王飞的帐户上增加5000W；

                  · C、赵XX要负责支付本次操作的服务费，同时上缴个人失去税；

         那么如果说现在赵XX的帐户上没有5000W，那么王飞的帐户上不能增加。如果说现在王飞的帐户上由于问题，没有收到着5000W，那么赵XX的帐户上肯定不应该减少；如果最后赵XX坚决不支付所有的额外费用，那么之前的操作也应该失败。

         所以为了能够保证以上一系列的操作都能同时成功或同时失败，则在数据库之中就引入了事务的概念。

         在讲解事务之前首先必须先强调一下SESSION（会话）的概念，对于Oracle而言，把每一个通过客户端连接到服务器上的用户都称为一个SESSION，每一个SESSION有每一个SESSION自己独立的事务，例如SESSION A更新的时候默认情况下都不会直接向数据表中发出指令，而只是在一个缓冲区之内完成的更新，如果现在发现有错误则可以进行及时的纠正，而这就属于事务处理的范畴，即：每一个SESSION拥有自己的事务处理能力，在Oracle中事务处理主要有两个命令：提交事务（COMMIT）、事务回滚（ROLLBACK）。

         如果以后要编写数据更新操作的话，最后一定要加上COMMIT；

         但是这个时候又有一个问题出现了，如果现在不同的SESSION更新的是同一条记录呢？

         这个时候会出现锁的问题，但是锁又被分为很多种：同步锁、排它锁、死锁、活锁等。

3.3、数据伪列（重点）

         所谓的数据伪列指的是不是明确定义的数据列，但是又确实存在的数据列，在Oracle之中强调的数据伪列：ROWNUM、ROWID。

3.3.1、行号：ROWNUM（核心）

         在进行数据查询的时候，如果现在希望可以为其进行行号的命名，则可以使用ROWNUM定义：

SELECT ROWNUM,empno,ename,job,hiredate,sal FROM emp ;

         但是需要强调的是，这个序号本身并不是固定的，而是根据查询动态生成的。

SELECT ROWNUM,empno,ename,job,hiredate,sal FROM emp WHERE deptno=10 ;

         但是另外一点需要注意的是，虽然ROWNUM可以给出行号，可是ROWNUM在实际的开发之中只能实现两个功能：

                  ·取出第一行数据记录；

                  ·取出前N行数据记录；

范例：要求查询出工资最高的雇员信息

SELECT *

FROM (

         SELECT *

         FROM emp

         ORDER BY sal DESC)

WHERE ROWNUM=1 ;

范例：取出数据表的前5条记录

SELECT * FROM emp WHERE ROWNUM<=5 ;

         但是从实际的开发来讲，ROWNUM可以完成数据的分页显示功能（数据的部分显示），下面对此问题分析（重点）。

范例：现在假设每页显示的记录是5条记录，当前在第1页

         · lineSize = 5；每页显示5条；

         · currentPage = 1：当前在第一页；

SELECT * FROM (

         SELECT empno,ename,job,hiredate,sal,ROWNUM rn FROM emp WHERE ROWNUM<=1*5) temp

WHERE temp.rn>(1-1)*5 ;

范例：现在假设每页显示的记录是5条记录，当前在第2页

         · lineSize = 5；每页显示5条；

         · currentPage = 2：当前在第2页；

SELECT * FROM (

         SELECT empno,ename,job,hiredate,sal,ROWNUM rn FROM emp WHERE ROWNUM<=2*5) temp

WHERE temp.rn>(2-1)*5 ;

范例：现在假设每页显示的记录是5条记录，当前在第3页

         · lineSize = 5；每页显示5条；

         · currentPage = 3：当前在第3页；

SELECT * FROM (

         SELECT empno,ename,job,hiredate,sal,ROWNUM rn FROM emp WHERE ROWNUM<=3*5) temp

WHERE temp.rn>(3-1)*5 ;

         以上的三个程序，就是日后学习分页实现功能的数据库部分。

3.3.2、行ID：ROWID（理解）

         如果说现在一张数据表之中有两条记录完全一样的话，那么可以唯一区分这两条记录的不同，那么就只能依靠ROWID完成了，所谓的ROWID就相当于是一个可以唯一声明一个数据保存地址的编号，此编号通过数据库自动生成，例如，现在观察以下的程序操作：

SELECT ROWID,deptno,dname,loc FROM mydept ;

         而对于一个ROWID的组成实际上就包含了：数据号、文件号、数据位号；

DELETE FROM mydept WHERE ROWID='AAAMTvAAEAAAAFvAAA' ;

面试题：请删除表中的重复记录，重复的内容只保留一个

         现在假设mydept数据表之中存在如下的数据记录，可以发现数据存在了重复。

 

INSERT INTO mydept(deptno,dname,loc) VALUES (10,'ACCOUNTING','NEW YORK') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (10,'ACCOUNTING','NEW YORK') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (20,'RESEARCH','DALLAS') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (20,'RESEARCH','DALLAS') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (20,'RESEARCH','DALLAS') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (40,'OPERATIONS','BOSTON') ;

         要求将重复的内容删除掉，而且只保留一个（最早增加的数据）。

 

         那么首先换一个角度来思考问题，如果说现在让你来确定有多少条记录是重复，那么唯一的检测方式就是统计个数，个数大于1就是重复的，既然要统计个数，那么肯定要进行分组，那么现在数据都是重复的，所以直接多字段分组。

SELECT deptno,dname,loc,COUNT(*),MIN(ROWID)

FROM mydept

GROUP BY deptno,dname,loc

HAVING COUNT(*)>1 ;

 

DELETE FROM mydept

WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM mydept GROUP BY deptno,dname,loc) ;

         但是除了本题目之外，ROWID在以后讲解的过程中概念上还是有一些用处的，其他的地方意义不大。

3.4、表的创建和管理（重点）

         在之前使用的主要是由系统提供的数据表，但是在实际的开发之中，肯定要根据用户的需求建立相对应的数据表，那么如果要进行数据表的维护，则可以使用三个命令完成：CREATE、DROP、ALTER；

         但是需要强调的是，对于Oracle数据库而言，每一张数据表严格来讲都属于Oracle的一个对象，例如：一个用户是一个对象，一个数据表也是一个对象，只要是对象的操作就是三个指令。

3.4.1、支持的数据类型

         如果要创建数据表，那么必须首先了解数据库操作之中主要的数据类型，从实际的应用来讲，在数据库之中主要使用以下几种数据类型：

                  · VARCHAR2(n)：表示定义的是字符串，一般只要是在200个文字之内的都可以定义成此类型；

                  · NUMBER(n)：表示定义整数，其中n表示最多的数据位数，也可以使用INT代替；

                  · NUMBER(n, m)：表示定义小数，其中有m位小数，有n-m位整数，也可以使用FLOAT代替；

                  · DATE：包含了日期时间数据的类型；

                  · CLOB：表示大文本字段、可以保存最多4G的文字信息；

                  · BLOB：表示二进制数据，可以存放文本、音乐、图片、电影等等，最多可以保存4G；

3.4.2、创建数据表

         创建数据表属于创建对象的过程，创建的语法如下：

CREATE TABLE 表名称 (

         字段          数据类型   [DEFAULT 默认值] ,

         字段          数据类型   [DEFAULT 默认值] ,

                  ...

         字段          数据类型   [DEFAULT 默认值]

) ;

范例：创建一张数据表—— member

CREATE TABLE member (

         mid            NUMBER          ,

         name          VARCHAR2(50)          DEFAULT '无名氏' ,

         birthday     DATE                 DEFAULT  SYSDATE ,

         note           CLOB

) ;

范例：增加数据

INSERT INTO member(mid,name,birthday,note) VALUES (1,'张三',TO_DATE('1990-09-01','yyyy-mm-dd'),'是一个人。') ;

INSERT INTO member(mid,note) VALUES (3,'是一个人。') ;

问题：按照什么规律创建数据表？

         虽然现在已经清楚了数据表的创建语法，但是数据表是依靠什么来创建的？

         如果要解释此问题，那么先来解释一下程序开发的流程；

                  1、   获取需求过程；                  è     软件销售；

                  2、   需求分析确定；                  è     根据客户的要求进行功能的描述；

                  3、   详细设计：数据表、接口、操作规范；       è     业务分析；

                  4、   程序的开发过程；              è     技术人员根据文档的要求实现功能；

                  5、   程序的测试过程；              è     软件测试人员；

                  6、   程序的运行及维护过程；   è     后期有一些小的功能进行免费升级，如果是大的改动再额外支付；

3.4.3、复制表（理解）

         表的复制在之前已经实现了完整的操作，但是下面给出表复制的完整语法。

CREATE TABLE 复制的表名称 AS 子查询；

范例：将emp表中10部门的全部雇员信息复制出来

CREATE TABLE emp10 AS SELECT * FROM emp WHERE deptno=10 ;

范例：要求复制一张表为empnull，但是只要求复制表结构，不要求复制表中的数据

CREATE TABLE empnull AS SELECT * FROM emp WHERE 1=2 ;

         本操作只有Oracle数据库才支持，其他数据库不要使用，而且也没多大的用处。

3.4.4、截断表（了解）

         在之前使用过了DELETE语句删除过一张表的数据，但是这种删除数据的方式有一个特点：删除之后表中所占用的资源并不会被立刻释放掉（例如：数据受到事务的控制，或者是表中的其他内容，例如：约束、索引等等），而现在希望表可以在删除全部数据时自动的释放掉全部所占用的资源，为此，在Oracle中专门提供了一个截断表的功能，语法：

TRUNCATE TABLE 表名称；

范例：阶段emp10表

TRUNCATE TABLE emp10 ;

         表被截断之后，所占用的全部资源就被彻底的清空了。但是此功能只有oracle才支持，清楚就行了。

3.4.5、修改表名称（了解）

         在讲解此操作之前，那么首先需要先来解释一下数据字典的概念，在Oracle之中，曾经使用过如下的一种语法：

SELECT * FROM tab ;

         本操作可以列出一个用户下的全部对象（表就是一个对象）的信息，实际上这就是一个数据字典的应用，所谓的数据字典指的是记录数据库中所有对象的一个信息的数据表，但是这张数据表是由oracle自己维护的。

         例如，现在使用CREATE TABLE创建了一个新的数据库对象，那么就自动在一个*_tables数据字典里面进行了一个注册，而随后查询全部数据表的功能，就是查询数据字典的功能，而数据字典主要分为三种：all_*、dba_*、user_*，如果现在要想查看一个用户全部的数据表，那么使用user_tables数据字典完成。

SELECT * FROM user_tables ;

         但是这种数据字典返回的数据属于大而全的，内容比较多一些，那么所以使用tab会比较多。实际上修改表名称的操作就属于修改数据字典之中内容的操作，但是语法不直接使用UPDATE，而是使用：

RENAME 旧的表名称 TO 新的表名称；

范例：将mydept表修改为temp

RENAME mydept TO temp ;

         同时需要说明的是，在用户创建新的数据库对象时或者是修改数据表名称时，所有的事务都会自动提交，但是事务只是对数据而言的，对数据表不会有任何的影响，DDL不受事务的控制。

3.4.6、删除数据表

         数据表本身属于数据库对象，所以，如果要想进行数据表的删除，那么就表示删除的是数据库对象，删除的语法：

DROP TABLE 数据表名称；

范例：删除temp表

DROP TABLE temp ;

3.4.7、Oracle 10g的新功能：FlashBack，闪回（理解）

         可以发现，在以上执行了DROP语法删除数据表的时候，每次删除都会留下一些记号：

                  · BIN$X9iwigZXSSOKuGBZXoRRHg==$0

         实际上这个就是在Oracle 10g之中为了保护误删表可以尽快恢复所支持的一个操作，非常类似于Windows的回收站功能，即：所有的数据表现在如果直接执行了DROP指令，并不会立刻删除，而是先将其保存在回收站之中。

范例：查看回收站

SHOW RECYCLEBIN ;

范例：从回收站之中恢复temp表

FLASHBACK TABLE temp TO BEFORE DROP ;

         当然，如果觉得数据表放回到回收站之中不是必须的，那么可以直接使用强制删除表的操作，只需要在DROP语句之中增加一个“PURGE”的选项即可。

DROP TABLE temp PURGE ;

范例：删除回收站之中的一张数据表

PURGE TABLE emp10 ;

范例：清空回收站

PURGE RECYCLEBIN ;

         这一操作从Oracle 10g之后开始存在，oracle 11g也是有的，不过由于其不具备通用性，所以只有在使用Oracle的时候才需要考虑。

3.4.8、修改数据表结构（了解）

         现在，为了解释问题，首先创建一张数据表：

CREATE TABLE member (

         mid            NUMBER          ,

         name          VARCHAR2(50)          DEFAULT '无名氏'

) ;

INSERT INTO member(mid,name) VALUES (1,'张三') ;

INSERT INTO member(mid,name) VALUES (2,'李四') ;

         在SQL语法之中，对于表结构的修改也有了其定义的语法，使用ALTER指令完成。

1、   为数据表增加新的数据列

         如果现在要为一张数据表里面增加新的列，则可以使用如下语法完成：

ALTER TABLE 表名称 ADD (字段数据类型 [DEFAULT 默认值] [,字段数据类型 [DEFAULT 默认值],..])

范例：为表中增加两个字段，一个有默认值，另外一个没有默认值

ALTER TABLE member ADD (sex VARCHAR2(20) DEFAULT '男') ;

ALTER TABLE member ADD (note CLOB) ;

         如果现在增加的新的字段存在了默认值，则会更新已有的全部数据记录。

2、   也可以修改已有的数据列

ALTER TABLE 表名称 MODIFY (字段数据类型 [DEFAULT 默认值] [,字段数据类型 [DEFAULT 默认值],..])

范例：修改已有的数据列

ALTER TABLE member MODIFY(name VARCHAR2(20) DEFAULT '匿名') ;

         但是从今天开始忘记以上语法，别写、别使。之所以这样强调，主要是从IBM DB2倡导的理念，此数据库最大的特点是数据表的表结构不允许修改。

         在以后进行项目设计时，尽可能考虑到有可能发生的改变，而这一点达到就是看你对于整个行业业务流程的把握了。而且虽然允许修改表结构，但是也不能随便由你一个人去决定修改。

3.4.9、思考题（面试题）

         现在要求建立一张nation表，表中只有一个name字段，保存的信息有：中国、美国、巴西、荷兰四个国家的信息，现在要求通过查询实现对战的操作，最终的显示效果如下：

                  •中国       美国；

                  •中国       巴西；

                  •中国      荷兰；

                  •美国       中国；

                  •美国       巴西；

                  •美国       荷兰；

                           …依次类推。。。

         既然现在表不存在，那么首先要创建的也肯定是数据表，而现在就可以给出数据库创建脚本（可以使用这些代码直接从无到有的生成数据表或者是重置数据表）的操作规范。

                  ·数据库创建脚本的文件后缀名称必须是*.sql；

                  ·删除已有的数据表，编写DROP指令；

                  ·编写创建表的语句；

                  ·为数据表增加测试数据；

                  ·提交事务；

-- 删除数据表

DROP TABLE nation PURGE ;

-- 创建数据表

CREATE TABLE nation (

         name          VARCHAR2(50)

) ;

-- 测试数据

INSERT INTO nation (name) VALUES('中国') ;

INSERT INTO nation (name) VALUES('美国') ;

INSERT INTO nation (name) VALUES('巴西') ;

INSERT INTO nation (name) VALUES('荷兰') ;

-- 提交事务

COMMIT ;

         要想实现此功能依靠笛卡尔积完成，但是自己不能跟自己对战。

SELECT n1.name,n2.name

FROM nation n1,nation n2

WHERE n1.name<>n2.name ;

         以后进行完数据库分析之后，就要编写数据库创建脚本。

3.5、约束（核心）

         数据表现在已经可以进行创建了，但是这个时候的数据表本身并不具备数据合法性的检查功能，例如：在表示人员年龄的时候其范围只能是：0~250、表示性别的时候只能是：男、女、不男不女、表示注册邮箱的时候不能够重复，但是这些操作在之前的数据表里面并不存在，要想对数据进行限制，那么就只能使用约束完成，而约束一共分为五种：非空约束、唯一约束、主键约束、外键约束（难）。

3.5.1、非空约束：NOT NULL，NK

         非空约束的概念主要是指一个字段上的内容不允许设置null，如果要指定非空约束，则直接在创建表字段的时候设置。

DROP TABLE memeber PURGE ;

CREATE TABLE member (

         mid            NUMBER ,

         name          VARCHAR2(50)          NOT NULL

) ;

         这个时候name字段上定义时存在了NOT NULL约束，所以下面观察数据的增加。

范例：增加正确的数据

INSERT INTO member(mid,name) VALUES (1,'张三') ;

范例：增加错误的数据

INSERT INTO member(mid,name) VALUES (2,null) ;

INSERT INTO member(mid) VALUES (3) ;

         错误提示：“ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."NAME")”；

         这个时候的错误提示直接会告诉用户那一个字段上违反了约束。

3.5.2、唯一约束：UNIQUE，UK

         所谓的唯一约束指的是在数据表中一个列上的数据内容不允许存在重复的记录，例如，现在假设每一个成员都应该拥有属于自己的邮箱，所以邮箱现在不应该重复。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER ,

         name          VARCHAR2(50)          NOT NULL ,

         email          VARCHAR2(200)        UNIQUE

) ;

范例：增加错误的数据（执行两遍）

INSERT INTO member(mid,name,email) VALUES (1,'张三','aa@11.com') ;

         错误提示：“ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005300)”

         发现这个时候出现的错误提示的名字或者说信息根本就不知道那个字段上违反了约束，对于Oracle而言，每一个约束实际上也属于一个数据库的对象，那么如果在设置约束的时候没有设置名字，则表示会由Oracle自动设置一个名称，但是这个名称往往不方便维护，所以可以使用CONSTRAINT关键字手工指定一个名称，而约束的名称建议的形式：约束简写_字段的形式，例如：在email字段上设置唯一约束：uk_email。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER ,

         name          VARCHAR2(50)          NOT NULL ,

         email          VARCHAR2(200)        ,

         CONSTRAINT uk_email UNIQUE(email)

) ;

         错误信息：“ORA-00001: 违反唯一约束条件 (SCOTT.UK_EMAIL)”；唯一约束可以设置为null。

3.5.3、主键约束：PRIMARY KEY，PK

         主键约束 = 唯一约束 + 非空约束，在创建数据表的时候可以通过PRIMARY KEY进行设置，例如：每一个成员都有一个属于自己的编号，那么这个唯一的编号就可以作为主键存在。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          PRIMARY KEY ,

         name          VARCHAR2(50)          NOT NULL

) ;

         这个时候增加两种数据：

INSERT INTO member(mid,name) VALUES (1,'张三') ;  à重复执行会违反唯一约束

INSERT INTO member(mid,name) VALUES (null,'张三') ; à出现非空约束

         而如果说现在希望在其违反唯一约束时可以明确的显示出错误信息，则只能利用CONSTRAINT设置主键约束名称。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          ,

         name          VARCHAR2(50)          NOT NULL ,

         CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;

         可是有另外一点需要注意的是，在一张数据表之中往往只会设置一个主键约束，当然，也可以同时为一张表中的多个字段同时设置为主键约束，这种操作的形式就被称为复合主键。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          ,

         name          VARCHAR2(50)          ,

         CONSTRAINT pk_mid PRIMARY KEY(mid,name)

) ;

         此时，mid和name两个字段上的内容都定义出了主键约束，所以此操作称为复合主键，复合主键的特点就是在：如果内容全部相同的时候，才表示不同的内容。

INSERT INTO member(mid,name) VALUES (1,'张三') ;

INSERT INTO member(mid,name) VALUES (1,'李四') ;

INSERT INTO member(mid,name) VALUES (2,'李四') ;

         但是需要说明的是，这种复合主键正常人不使。

3.5.4、检查约束：CHECK，CK

         检查约束指的就是要设置一系列的操作条件，例如：在设置年龄的时候需要有一个范围的限制，在设置性别的时候也要存在此限制等，这些都是依靠检查约束完成的。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          ,

         name          VARCHAR2(50)          ,

         age             NUMBER(3)      CHECK(age BETWEEN 0 AND 250),

         sex             VARCHAR2(20) ,

         CONSTRAINT pk_mid PRIMARY KEY(mid) ,

         CONSTRAINT ck_sex CHECK (sex IN ('男','女'))

) ;

         以后只有数据正确的操作，才可以正常的保存在数据表之中。

3.5.5、主-外键约束（难点）

         如果说现在希望设置一种数据表的关系：一个成员可以有多本书，现在也肯定要设置两张表，肯定现在在书的表中要存在一个成员的编号，这样就可以表示出一本属于一个人了，则，下面不使用外键的方式来建立表。

DROP TABLE member PURGE ;

DROP TABLE book PURGE ;

CREATE TABLE member (

         mid            NUMBER          PRIMARY KEY ,

         name          VARCHAR2(50)          NOT NULL

) ;

CREATE TABLE book (

         bid             NUMBER          PRIMARY KEY ,

         title            VARCHAR2(50)          NOT NULL ,

         mid            NUMBER

) ;

         现在的表之中只是设置了两个约束：主键约束、非空约束。在这个时候下面增加一些数据：

INSERT INTO member(mid,name) VALUES (1,'张三') ;

INSERT INTO member(mid,name) VALUES (2,'李四') ;

INSERT INTO book(bid,title,mid) VALUES (101,'Java SE开发',1) ;

INSERT INTO book(bid,title,mid) VALUES (102,'HTML开发',1) ;

INSERT INTO book(bid,title,mid) VALUES (103,'Oracle',2) ;

INSERT INTO book(bid,title,mid) VALUES (109,'DB2',2) ;

INSERT INTO book(bid,title,mid) VALUES (119,'JSP',2) ;

         以上的数据是是合法的，因为可以通过检测得出结果。

范例：要求查询出每一个成员所拥有的书的数量

SELECT m.name,COUNT(b.bid)

FROM member m,book b

WHERE m.mid=b.mid

GROUP BY m.name ;

范例：列出每一个成员和他所拥有的图书的名称

SELECT m.name,b.title

FROM member m , book b

WHERE m.mid=b.mid ;

         但是，由于这个时候没有设置任何的关联约束问题（mid）所以以下的数据也可以增加。

INSERT INTO book(bid,title,mid) VALUES (999,'晕',30) ;

         现在根本就没有一个成员的编号是30，但是这样的数据也可以保存，所以注定是一个错误的数据。

         即：现在book.mid字段的取值应该由member.mid所决定，而此时也就可以得出这样的关系，子表（book）中的某个字段的取值范围必须有主表（member）中的指定字段所设置，而这一功能的完成就需要通过外键约束。

         但是需要说明的是，被设置为外键的字段必须具备唯一性，即：在主表之中应该是主键或者是唯一约束。

DROP TABLE member PURGE ;

DROP TABLE book PURGE ;

CREATE TABLE member (

         mid            NUMBER          PRIMARY KEY ,

         name          VARCHAR2(50)          NOT NULL

) ;

CREATE TABLE book (

         bid             NUMBER          PRIMARY KEY ,

         title            VARCHAR2(50)          NOT NULL ,

         mid            NUMBER ,

         CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)

) ;

         这句话的含义就是指book.mid字段设置为了外键，其内容要参考member表的mid字段的内容。

         清楚了外键的操作之后，那么下面就要解决一些新的问题了。

1、   问题一：在删除表时，必须先删除子表后再删除父表

         就拿本程序来讲，应该先删除book表，而后再删除member表；

DROP TABLE book PURGE ;

DROP TABLE member PURGE ;

         虽然说这种删除的方式可以实现表的删除操作，但是这种操作一个最大的问题在于，所有的表在删除之前要先分清楚先后的关系，这本来就是一件很耗费时间的事，为了解决这样的问题，在SQL之中提供了强制删除数据表的操作。

DROP TABLE member CASCADE CONSTRAINT ;

         但是这种操作只在极其特殊的环境下使用，理论上，数据库创建脚本应该考虑到这些删除的先后关系问题。

2、   问题二：如果在删除父表数据时，还存在着其对应的子表数据，则父表数据无法删除

         但是这样的操作本身并不方便，例如：一个学生被学校开除了，那么这个学生的所有成绩就没有用处了，而要想实现这些级联的数据操作功能，那么在SQL语句之中可以使用两种选项：ON DELETE CASCADE、ON DELETE SET NULL。

（1）、级联删除：ON DELETE CASCADE

         当主表中的数据被删除之后，对应的子表中的数据也同时被删除掉。

DROP TABLE book PURGE ;

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          PRIMARY KEY ,

         name          VARCHAR2(50)          NOT NULL

) ;

CREATE TABLE book (

         bid             NUMBER          PRIMARY KEY ,

         title            VARCHAR2(50)          NOT NULL ,

         mid            NUMBER          REFERENCES member(mid) ON DELETE CASCADE

) ;

（2）、级联设置null：ON DELETE SET NULL

         当主表中数据被删除时，对应的子表中相应字段的内容自动的更新为null

DROP TABLE book PURGE ;

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          PRIMARY KEY ,

         name          VARCHAR2(50)          NOT NULL

) ;

CREATE TABLE book (

         bid             NUMBER          PRIMARY KEY ,

         title            VARCHAR2(50)          NOT NULL ,

         mid            NUMBER          REFERENCES member(mid) ON DELETE SET NULL

) ;

         至于在开发之中到底什么时候设置级联，什么时候不设置，这些完全由项目的业务功能所决定。

3.5.6、修改约束（了解）

         如果说现在要进行约束的修改，那么首先必须有一个前提要保证，要维护一个数据库对象，那么必须知道约束的名字，而在Oracle之中，为了方便用户查找约束的名称专门提供了两个数据字典：user_constraints、user_cons_columns。

范例：查询user_constraints

SELECT owner,constraint_name,table_name FROM user_constraints ;

         但遗憾的是，在此数据字典里面无法确定那个数据列上存在约束，所以要使用另外一张数据字典。

范例：查询user_cons_columns

COL owner FOR A15 ;

COL constraint_name FOR A15 ;

COL table_name FOR A15 ;

COL column_name FOR A15 ;

SELECT owner,constraint_name,table_name,column_name FROM user_cons_columns ;

         所以这两张数据表就为了修改约束提供了支持，但是为了方便起见，如果日后需要维护的话，还是要起一个名字。

         修改约束依然属于数据库对象的修改过程，既然是修改数据库对象就使用ALTER语法完成，修改约束主要有两种操作：增加约束、删除约束。

1、   增加约束

         如果一张数据表在建立的时候没有指定约束，则可以使用如下语法为其增加一个新的约束：

ALTER TABLE 表名称 ADD CONSTRAINT 约束类型(约束字段) [其他选项] ;

         但是需要注意的是，虽然有五种约束，但是非空约束可不是采用这种方法设置的，只能通过之前讲解过的MODIFY操作完成（修改一个列）。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          ,

         name          VARCHAR2(50)          NOT NULL

) ;

范例：为表中增加主键约束

ALTER TABLE member ADD CONSTRAINT pk_mid PRIMARY KEY(mid) ;

         如果现在数据表之中没有违反此约束的数据存在的话，则可以正常的增加，反之则无法增加。

2、   删除约束

         如果要删除约束，那么一定要为约束设置名称，否则无法删除，删除约束的语法如下：

ALTER TABLE 表名称 DROP CONSTRAINT 约束名称 ;

范例：删除表中的主键约束

ALTER TABLE member DROP CONSTRAINT pk_mid ;

         但是这种修改约束的操作和修改数据表的操作一样，不建议使用，而且只要是约束，一定要在建立表的同时就为其增加完整。

3.6、建表、更新、查询综合练习（重点）

1、   现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：

·商品product（商品号productid，商品名productname，单价unitprice，商品类别category，供应商provider）；

·顾客customer（顾客号customerid，姓名name，住址location）；

·购买purcase（顾客号customerid，商品号productid，购买数量quantity）；

根据题目要求建立约束；

2、   插入以下的测试数据：

商品（

         M01，佳洁士，8.00，牙膏，宝洁；

         M02，高露洁，6.50，牙膏，高露洁；

         M03，洁诺，5.00，牙膏，联合利华；

         M04，舒肤佳，3.00，香皂，宝洁；

         M05，夏士莲，5.00，香皂，联合利华；

         M06，雕牌，2.50，洗衣粉，纳爱斯

         M07，中华，3.50，牙膏，联合利华；

         M08，汰渍，3.00，洗衣粉，宝洁；

         M09，碧浪，4.00，洗衣粉，宝洁；）

顾客（

         C01，Dennis，海淀；

         C02，John，朝阳；

         C03，Tom，东城；

         C04，Jenny，东城；

         C05，Rick，西城；）

购买(

         C01，M01，3；

         C01，M05，2；

         C01，M08，2；

         C02，M02，5；

         C02，M06，4；

         C03，M01，1；

         C03，M05，1；

         C03，M06，3；

         C03，M08，1；

         C04，M03，7；

         C04，M04，3；

         C05，M06，2；

         C05，M07，8；）

                  3、   完成如下的数据操作要求：

A、  求购买了供应商"宝洁"产品的所有顾客；

B、   求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客（姓名）；

C、   求牙膏卖出数量最多的供应商；

D、  将所有的牙膏商品单价增加10%；

E、   删除从未被购买的商品记录。

3.3.1、建表

DROP TABLE purcase PURGE ;

DROP TABLE product PURGE ;

DROP TABLE customer PURGE ;

CREATE TABLE product(

         productid   VARCHAR2(10)          PRIMARY KEY ,

         productname       VARCHAR2(50)          NOT NULL ,

         unitprice    NUMBER          NOT NULL ,

         category     VARCHAR2(50)          NOT NULL ,

         provider     VARCHAR2(50)          NOT NULL

) ;

CREATE TABLE customer (

         customerid VARCHAR2(10)          PRIMARY KEY ,

         name          VARCHAR2(50)          NOT NULL ,

         location      VARCHAR2(200)        NOT NULL

) ;

CREATE TABLE purcase (

         customerid VARCHAR2(10)          REFERENCES customer(customerid) ON DELETE CASCADE ,

         productid   VARCHAR2(10)          REFERENCES product(productid) ON DELETE CASCADE ,

         quantity     NUMBER

) ;

3.3.2、增加数据

1、   增加商品记录

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M01','佳洁士',8.00,'牙膏','宝洁') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M02','高露洁',6.50,'牙膏','高露洁') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M03','洁诺',5.00,'牙膏','联合利华') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M04','舒肤佳',3.00,'香皂','宝洁') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M05','夏士莲',5.00,'香皂','联合利华') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M06','雕牌',2.50,'洗衣粉','纳爱斯') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M07','中华',3.50,'牙膏','联合利华') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M08','汰渍',3.00,'洗衣粉','宝洁') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M09','碧浪',4.00,'洗衣粉','宝洁') ;

2、   增加顾客记录

INSERT INTO customer(customerid,name,location) VALUES ('C01','Dennis','海淀') ;

INSERT INTO customer(customerid,name,location) VALUES ('C02','John','朝阳') ;

INSERT INTO customer(customerid,name,location) VALUES ('C03','Tom','东城') ;

INSERT INTO customer(customerid,name,location) VALUES ('C04','Jenny','东城') ;

INSERT INTO customer(customerid,name,location) VALUES ('C05','Rick','西城') ;

3、   购买记录

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C01','M01',3) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C01','M05',2) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C01','M08',2) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C02','M02',5) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C02','M06',4) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C03','M01',1) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C03','M05',1) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C03','M06',3) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C03','M08',1) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C04','M03',7) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C04','M04',3) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C05','M06',2) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C05','M07',8) ;

COMMIT ;

         此时程序的关系属于多对多的表关系。一个客户可以购买多种商品，每一个商品可以被多个用户所购买。

3.3.3、数据操作

1、   求购买了供应商"宝洁"产品的所有顾客；

         ·确定所需要的数据表：

                  |- customer：找到顾客的完整信息；

                  |- purcase：购买记录可以找到一个商品购买的顾客信息；

                  |- product：需要通过这张表找到“宝洁”产品的全部产品ID，此时返回的是多行单列；

         ·确定已知的关联字段：

                  |- 顾客和购买记录：customer.customerid=purcase.customerid；

                  |- 商品和购买记录：product.productid=purcase.productid；

第一步：要找到“宝洁”的所有商品的编号

SELECT productid

FROM product p

WHERE p.provider='宝洁' ;

第二步：以上的是多行单列的记录，可以在WHERE子句之中出现

SELECT DISTINCT p.customerid

FROM purcase p

WHERE p.productid IN (

         SELECT productid

         FROM product p

         WHERE p.provider='宝洁') ;

第三步：已经知道了顾客编号，就可以查询出顾客的完整信息

SELECT *

FROM customer c

WHERE c.customerid IN (

         SELECT DISTINCT p.customerid

         FROM purcase p

         WHERE p.productid IN (

                  SELECT productid

                  FROM product p

                  WHERE p.provider='宝洁')) ;

2、   求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客（姓名）；

         ·确定所需要的数据表：

                  |- customer：顾客姓名；

                  |- purcase：找到一个顾客所购买的全部商品的编号；

         ·确定已知的关联字段：

                  |- 顾客和购买记录：customer.customerid=purcase.customerid；

第一步：找到Dennis的顾客编号，并且依据此编号找到对应的商品编号

SELECT p.productid

FROM customer c,purcase p

WHERE c.customerid=p.customerid

         AND c.name='Dennis';

第二步：根据已经查询出来的商品编号，找到对应的顾客编号

SELECT DISTINCT p.customerid

FROM purcase p

WHERE p.productid IN (

         SELECT p.productid

         FROM customer c,purcase p

         WHERE c.customerid=p.customerid

                  AND c.name='Dennis');

第三步：确定顾客的姓名

SELECT *

FROM customer

WHERE customerid IN (

         SELECT DISTINCT p.customerid

         FROM purcase p

         WHERE p.productid IN (

                  SELECT p.productid//in关键字后面的字段只能是一个

                  FROM customer c,purcase p

                  WHERE c.customerid=p.customerid

                           AND c.name='Dennis'))

         AND name<>'Dennis';

3、   求牙膏卖出数量最多的供应商；

         ·确定所需要的数据表：

                  |- product：查找出供应商的名字；

                  |- purcase：销售数量统计；

         ·确定已知的关联字段：product.productid=purcase.productid；

第一步：暂时不考虑最多的问题，只是统计出每个供应商卖牙膏的销售量

SELECT p.provider,COUNT(pc.quantity)

FROM product p,purcase pc

WHERE p.productid=pc.productid

         AND p.category='牙膏'

GROUP BY p.provider ;

第二步：计算出最高销售量，应该对COUNT()函数的结果进行求出最大值的操作，属于统计函数嵌套，而统计函数嵌套时不能出现任何的字段，包括分组字段。

SELECT MAX(COUNT(pc.quantity))

FROM product p,purcase pc

WHERE p.productid=pc.productid

         AND p.category='牙膏'

GROUP BY p.provider ;

第三步：将第一步增加一个HAVING子句，使用第二步计算的结果判断

SELECT p.provider,COUNT(pc.quantity)

FROM product p,purcase pc

WHERE p.productid=pc.productid

         AND p.category='牙膏'

GROUP BY p.provider

HAVING COUNT(pc.quantity)=

         (SELECT MAX(COUNT(pc.quantity))

         FROM product p,purcase pc

         WHERE p.productid=pc.productid

                  AND p.category='牙膏'

         GROUP BY p.provider) ;

4、   将所有的牙膏商品单价增加10%；

UPDATE product SET unitprice=unitprice*1.1 WHERE category='牙膏' ;

5、   删除从未被购买的商品记录。

DELETE FROM product

WHERE productid IN (

         SELECT productid

         FROM purcase

         GROUP BY productid

         HAVING COUNT(productid)=0) ;

         像本类的这种建表、约束、更新、查询操作的题目要求能够熟练编写。

3.7、前台工具（了解）

         Oracle本身实际上所提供的只是一个数据库的服务，但是在这个服务之中，有一些问题出现，主要是针对于前台界面：

                  · Oracle 9i：OEM、sqlplusw、sqlplus；

                  · Oracle 10g：sqlplusw、sqlplus；

                  · Oracle 11g：sqlplus，SQL Developer；

         可是在一般的开发之中，不可能说每一个开发者电脑上都安装一个数据库，所以往往会使用一个专门的数据库服务器提供数据库的支持，而每个开发者电脑上都会使用一个客户端进行连接，而这个客户端（需要单独配置Oracle开发环境）往往会使用PL SQL Developer工具完成。

         此工具据说是$180一套，好像买4套是$600，不过国内一分不花。

         但是此工具要想使用，必须有监听服务支持。

 

扩展：关于Oracle监听服务问题的解决。开发的第一大头疼问题。

         Oracle数据库直接与网络的依附有关，所以一旦网络环境发生改变，那么Oracle 11g以下的版本无法自动的匹配，则需要用户手工控制，修改两个文件（D:oracleproduct10.1.0db_1NETWORKADMIN）：listener.ora、tnsnames.ora。

         如果自动的服务不好使用，则可以通过手工控制完成，控制命令：D:oracleproduct10.1.0db_1BINLSNRCTL.EXE

如果此时依然无法连接，则可以通过网络配置，注册服务ID（SID），SID就是数据库的名称，那么有可能是因为网络环境发生改变之后，所导致服务ID消失了，这个时候可以通过网络管理重新增加回来。

4、总结

1、   表的创建和管理：主要数据类型、创建和删除表、清空回收站；

2、   约束：只要是表就一定要定义约束，五种约束的定义及特点；

3、   查询：复杂查询的操作一定要熟练使用；

4、   数据更新：INSERT、UPDATE、DELETE；

5、   分页显示操作：ROWNUM；

5、预习任务

         视图、同义词、序列、索引、用户管理、数据库备份。

6、作业

         1、   熟练掌握DML、DDL；

         2、   开始预习Java基础；

EditPlus我所知道的快捷键：

         · CTRL + Z：撤消；

         · CTRL + Y：恢复；

         · CTRL + S：保存；

         · CTRL + N：新建；

         · CTRL + J：复制当前行；

         · TAB：制表符；

         · SHIFT + TAB：取消一个制表符；

 

1、   多表查询：同时从多张表中取数据的形式，但是多表查询会存在笛卡尔积的问题，而消除此问题可以使用连接的方式完成（内连接、外连接（左外连接、右外连接、全外连接）），虽然连接的方式可以消除笛卡尔积，但是这个积依然存在，所以多表查询从性能上来讲是很差的；

2、   统计函数：COUNT()、AVG()、MAX()、MIN()、SUM()；

3、   分组使用GROUP BY子句完成，在GROUP BY子句之中允许指定多个分组字典，而SELECT子句里面只能出现分组字段以及统计函数，其他字段不允许出现。分组函数本身允许嵌套，嵌套之后的查询里面不能再出现任何的其他字段，包括分组字段。如果要对分组后的数据再次进行过滤，则使用HAVING子句完成；

4、   子查询：在一个查询之中嵌入了若干个小的查询称为子查询，子查询一般出现在以下子句中较多：

         · WHERE子句：返回的数据为单行单列，或者是单行多列，或者是多行单列（IN、ANY、ALL）；

         · HAVING子句：返回的数据为单行单列，而且是在有分组的情况下才去使用；

         · FROM子句：返回的结果是多行多列的数据，按照一张临时表的方式进行操作。如果在一个查询之中需要使用统计数据，但是又不能直接使用统计函数的情况下，可以考虑在FROM子句之后编写子查询。

2.2、本次预计讲解的知识点

1、   数据的更新操作及事务处理操作；

2、   数据表的定义与管理；

3、   约束的设置；

4、   数据伪列的使用；

5、   SQL Developer工具的使用；

3、具体内容

3.1、数据更新操作（重点）

         DML主要包含两个功能：查询、更新，对于查询操作在之前已经讲解完成，而更新操作也是一个重点的内容，但是考虑到以后scott中的数据表的内容还要使用，所以在讲解之前首先将emp表复制为一张myemp表。

范例：复制emp表

CREATE TABLE myemp AS SELECT * FROM emp ;

 

         而这种复制表的结构只有Oracle才有，其他数据库的语法是不一样的。

面试题：请问在SQL Server之中如何进行数据表的复制？

         答案：略。。。

         而数据的更新操作分为三种：增加、修改、删除。

3.1.1、数据的增加操作

         如果要进行数据的增加操作，可以使用如下的语法完成：

INSERT INTO 表名称 [(列1，列2，....)] VALUES (值1，值2，...) ;

 

         但是有一点需要注意的是，在使用INSERT语句增加数据的时候，它所设置的数值有如下要求：

                  ·数字：直接编写，例如：30；

                  ·字符串：加上一个“'”声明，例如：'Hello'；

                  ·日期：按照已有的日期格式编写字符串（'28-9月 -81'）、使用TO_DATE()函数转换、SYSDATE；

范例：为myemp数据表增加一条新的记录

         ·完整语法格式：要明确的写出数据增加时所需要的数据列

INSERT INTO myemp (empno,job,ename,hiredate,sal,comm,mgr,deptno)

         VALUES (8888,'清洁工','张三',TO_DATE('1989-09-12','yyyy-mm-dd'),5000,2000,7369,40) ;

 

         ·简化语法格式：不明确的写出列的名称，要求与表中的数据列的顺序一致

INSERT INTO myemp

         VALUES (9999,'李四','清洁工',7369,TO_DATE('1989-09-12','yyyy-mm-dd'),8000,2000,40) ;

 

         从开发的角度而言，永远都要使用完整格式编写，这样可以方便进行错误的调试。

3.1.2、数据的修改操作

         如果现在要修改数据表中的数据记录，则可以使用如下语法完成：

UPDATE 表名称 SET 列1=值1，列2=值2，... [WHERE 更新条件(s)] ;

 

范例：要求将所有雇员雇佣日期修改为今天，取消所有雇员的奖金

UPDATE myemp SET hiredate=SYSDATE,comm=null ;

 

范例：要求将所有雇员的工资增长10%

UPDATE myemp SET sal=sal*1.1 ;

 

         但是这种更新操作属于更新一张完整数据表的操作，所有记录都要修改，那么考虑这样一个问题：假设现在每修改一条记录，所花费的时间是0.001秒，那么如果说现在一张数据表中有500W条记录，这种操作所耗费的时间是：84分，按照数据库的机制在这84分钟之内，所有的用户都无法在进行数据的更新操作，所以记住了，如果对于数据的更新操作，没有一个正常人说要更新表中的正常记录；

范例：要求现在将雇员编号是7369的雇员的工资修改为9000，奖金修改为500，而且职位修改为MANAGER；

UPDATE myemp SET sal=9000,comm=500,job='MANAGER' WHERE empno=7369 ;

 

范例：要求将所有低于公司平均工资的雇员工资每个雇员增长1000

UPDATE myemp SET sal=sal+1000

WHERE sal<(SELECT AVG(sal) FROM myemp) ;

 

3.1.3数据的删除操作

         如果现在要删除数据表中的数据记录，则可以使用如下语法完成：

DELETE FROM 表名称 [WHERE 条件(s)] ;

 

范例：删除职位是清洁工的雇员

DELETE FROM myemp WHERE job='清洁工' ;

 

范例：要求删除雇员编号是7566，7903，7885雇员信息

DELETE FROM myemp WHERE empno IN (7566,7788,7885) ;

 

         在进行更新操作的时候，都会返回数据的更新行数，如果删除的时候删除条件不能满足，则没有任何的数据被删除。

         如果说现在在删除的时候不指定删除条件，则表示删除全部。

DELETE FROM myemp ;

 

         但是这种操作在开发之中也少使用。

3.2、事务处理（重点）

         数据的更新操作由于要对数据本身进行更改，那么就与查询相比存在了许多不安全的因素，例如，现在有如下一个业务操作：

                  · A、从赵XX的帐户上转出5000W给王飞；

                  · B、在王飞的帐户上增加5000W；

                  · C、赵XX要负责支付本次操作的服务费，同时上缴个人失去税；

         那么如果说现在赵XX的帐户上没有5000W，那么王飞的帐户上不能增加。如果说现在王飞的帐户上由于问题，没有收到着5000W，那么赵XX的帐户上肯定不应该减少；如果最后赵XX坚决不支付所有的额外费用，那么之前的操作也应该失败。

         所以为了能够保证以上一系列的操作都能同时成功或同时失败，则在数据库之中就引入了事务的概念。

         在讲解事务之前首先必须先强调一下SESSION（会话）的概念，对于Oracle而言，把每一个通过客户端连接到服务器上的用户都称为一个SESSION，每一个SESSION有每一个SESSION自己独立的事务，例如SESSION A更新的时候默认情况下都不会直接向数据表中发出指令，而只是在一个缓冲区之内完成的更新，如果现在发现有错误则可以进行及时的纠正，而这就属于事务处理的范畴，即：每一个SESSION拥有自己的事务处理能力，在Oracle中事务处理主要有两个命令：提交事务（COMMIT）、事务回滚（ROLLBACK）。

         如果以后要编写数据更新操作的话，最后一定要加上COMMIT；

         但是这个时候又有一个问题出现了，如果现在不同的SESSION更新的是同一条记录呢？

         这个时候会出现锁的问题，但是锁又被分为很多种：同步锁、排它锁、死锁、活锁等。

3.3、数据伪列（重点）

         所谓的数据伪列指的是不是明确定义的数据列，但是又确实存在的数据列，在Oracle之中强调的数据伪列：ROWNUM、ROWID。

3.3.1、行号：ROWNUM（核心）

         在进行数据查询的时候，如果现在希望可以为其进行行号的命名，则可以使用ROWNUM定义：

SELECT ROWNUM,empno,ename,job,hiredate,sal FROM emp ;

 

         但是需要强调的是，这个序号本身并不是固定的，而是根据查询动态生成的。

SELECT ROWNUM,empno,ename,job,hiredate,sal FROM emp WHERE deptno=10 ;

 

         但是另外一点需要注意的是，虽然ROWNUM可以给出行号，可是ROWNUM在实际的开发之中只能实现两个功能：

                  ·取出第一行数据记录；

                  ·取出前N行数据记录；

范例：要求查询出工资最高的雇员信息

SELECT *

FROM (

         SELECT *

         FROM emp

         ORDER BY sal DESC)

WHERE ROWNUM=1 ;

 

范例：取出数据表的前5条记录

SELECT * FROM emp WHERE ROWNUM<=5 ;

 

         但是从实际的开发来讲，ROWNUM可以完成数据的分页显示功能（数据的部分显示），下面对此问题分析（重点）。

范例：现在假设每页显示的记录是5条记录，当前在第1页

         · lineSize = 5；每页显示5条；

         · currentPage = 1：当前在第一页；

SELECT * FROM (

         SELECT empno,ename,job,hiredate,sal,ROWNUM rn FROM emp WHERE ROWNUM<=1*5) temp

WHERE temp.rn>(1-1)*5 ;

 

范例：现在假设每页显示的记录是5条记录，当前在第2页

         · lineSize = 5；每页显示5条；

         · currentPage = 2：当前在第2页；

SELECT * FROM (

         SELECT empno,ename,job,hiredate,sal,ROWNUM rn FROM emp WHERE ROWNUM<=2*5) temp

WHERE temp.rn>(2-1)*5 ;

 

范例：现在假设每页显示的记录是5条记录，当前在第3页

         · lineSize = 5；每页显示5条；

         · currentPage = 3：当前在第3页；

SELECT * FROM (

         SELECT empno,ename,job,hiredate,sal,ROWNUM rn FROM emp WHERE ROWNUM<=3*5) temp

WHERE temp.rn>(3-1)*5 ;

 

         以上的三个程序，就是日后学习分页实现功能的数据库部分。

3.3.2、行ID：ROWID（理解）

         如果说现在一张数据表之中有两条记录完全一样的话，那么可以唯一区分这两条记录的不同，那么就只能依靠ROWID完成了，所谓的ROWID就相当于是一个可以唯一声明一个数据保存地址的编号，此编号通过数据库自动生成，例如，现在观察以下的程序操作：

SELECT ROWID,deptno,dname,loc FROM mydept ;

 

         而对于一个ROWID的组成实际上就包含了：数据号、文件号、数据位号；

DELETE FROM mydept WHERE ROWID='AAAMTvAAEAAAAFvAAA' ;

 

面试题：请删除表中的重复记录，重复的内容只保留一个

         现在假设mydept数据表之中存在如下的数据记录，可以发现数据存在了重复。

 

INSERT INTO mydept(deptno,dname,loc) VALUES (10,'ACCOUNTING','NEW YORK') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (10,'ACCOUNTING','NEW YORK') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (20,'RESEARCH','DALLAS') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (20,'RESEARCH','DALLAS') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (20,'RESEARCH','DALLAS') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (40,'OPERATIONS','BOSTON') ;

 

         要求将重复的内容删除掉，而且只保留一个（最早增加的数据）。

 

         那么首先换一个角度来思考问题，如果说现在让你来确定有多少条记录是重复，那么唯一的检测方式就是统计个数，个数大于1就是重复的，既然要统计个数，那么肯定要进行分组，那么现在数据都是重复的，所以直接多字段分组。

SELECT deptno,dname,loc,COUNT(*),MIN(ROWID)

FROM mydept

GROUP BY deptno,dname,loc

HAVING COUNT(*)>1 ;

 

 

DELETE FROM mydept

WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM mydept GROUP BY deptno,dname,loc) ;

 

         但是除了本题目之外，ROWID在以后讲解的过程中概念上还是有一些用处的，其他的地方意义不大。

3.4、表的创建和管理（重点）

         在之前使用的主要是由系统提供的数据表，但是在实际的开发之中，肯定要根据用户的需求建立相对应的数据表，那么如果要进行数据表的维护，则可以使用三个命令完成：CREATE、DROP、ALTER；

         但是需要强调的是，对于Oracle数据库而言，每一张数据表严格来讲都属于Oracle的一个对象，例如：一个用户是一个对象，一个数据表也是一个对象，只要是对象的操作就是三个指令。

3.4.1、支持的数据类型

         如果要创建数据表，那么必须首先了解数据库操作之中主要的数据类型，从实际的应用来讲，在数据库之中主要使用以下几种数据类型：

                  · VARCHAR2(n)：表示定义的是字符串，一般只要是在200个文字之内的都可以定义成此类型；

                  · NUMBER(n)：表示定义整数，其中n表示最多的数据位数，也可以使用INT代替；

                  · NUMBER(n, m)：表示定义小数，其中有m位小数，有n-m位整数，也可以使用FLOAT代替；

                  · DATE：包含了日期时间数据的类型；

                  · CLOB：表示大文本字段、可以保存最多4G的文字信息；

                  · BLOB：表示二进制数据，可以存放文本、音乐、图片、电影等等，最多可以保存4G；

3.4.2、创建数据表

         创建数据表属于创建对象的过程，创建的语法如下：

CREATE TABLE 表名称 (

         字段          数据类型   [DEFAULT 默认值] ,

         字段          数据类型   [DEFAULT 默认值] ,

                  ...

         字段          数据类型   [DEFAULT 默认值]

) ;

 

范例：创建一张数据表—— member

CREATE TABLE member (

         mid            NUMBER          ,

         name          VARCHAR2(50)          DEFAULT '无名氏' ,

         birthday     DATE                 DEFAULT  SYSDATE ,

         note           CLOB

) ;

 

范例：增加数据

INSERT INTO member(mid,name,birthday,note) VALUES (1,'张三',TO_DATE('1990-09-01','yyyy-mm-dd'),'是一个人。') ;

INSERT INTO member(mid,note) VALUES (3,'是一个人。') ;

 

问题：按照什么规律创建数据表？

         虽然现在已经清楚了数据表的创建语法，但是数据表是依靠什么来创建的？

         如果要解释此问题，那么先来解释一下程序开发的流程；

                  1、   获取需求过程；                  è     软件销售；

                  2、   需求分析确定；                  è     根据客户的要求进行功能的描述；

                  3、   详细设计：数据表、接口、操作规范；       è     业务分析；

                  4、   程序的开发过程；              è     技术人员根据文档的要求实现功能；

                  5、   程序的测试过程；              è     软件测试人员；

                  6、   程序的运行及维护过程；   è     后期有一些小的功能进行免费升级，如果是大的改动再额外支付；

3.4.3、复制表（理解）

         表的复制在之前已经实现了完整的操作，但是下面给出表复制的完整语法。

CREATE TABLE 复制的表名称 AS 子查询；

 

范例：将emp表中10部门的全部雇员信息复制出来

CREATE TABLE emp10 AS SELECT * FROM emp WHERE deptno=10 ;

 

范例：要求复制一张表为empnull，但是只要求复制表结构，不要求复制表中的数据

CREATE TABLE empnull AS SELECT * FROM emp WHERE 1=2 ;

 

         本操作只有Oracle数据库才支持，其他数据库不要使用，而且也没多大的用处。

3.4.4、截断表（了解）

         在之前使用过了DELETE语句删除过一张表的数据，但是这种删除数据的方式有一个特点：删除之后表中所占用的资源并不会被立刻释放掉（例如：数据受到事务的控制，或者是表中的其他内容，例如：约束、索引等等），而现在希望表可以在删除全部数据时自动的释放掉全部所占用的资源，为此，在Oracle中专门提供了一个截断表的功能，语法：

TRUNCATE TABLE 表名称；

 

范例：阶段emp10表

TRUNCATE TABLE emp10 ;

 

         表被截断之后，所占用的全部资源就被彻底的清空了。但是此功能只有oracle才支持，清楚就行了。

3.4.5、修改表名称（了解）

         在讲解此操作之前，那么首先需要先来解释一下数据字典的概念，在Oracle之中，曾经使用过如下的一种语法：

SELECT * FROM tab ;

 

         本操作可以列出一个用户下的全部对象（表就是一个对象）的信息，实际上这就是一个数据字典的应用，所谓的数据字典指的是记录数据库中所有对象的一个信息的数据表，但是这张数据表是由oracle自己维护的。

         例如，现在使用CREATE TABLE创建了一个新的数据库对象，那么就自动在一个*_tables数据字典里面进行了一个注册，而随后查询全部数据表的功能，就是查询数据字典的功能，而数据字典主要分为三种：all_*、dba_*、user_*，如果现在要想查看一个用户全部的数据表，那么使用user_tables数据字典完成。

SELECT * FROM user_tables ;

 

         但是这种数据字典返回的数据属于大而全的，内容比较多一些，那么所以使用tab会比较多。实际上修改表名称的操作就属于修改数据字典之中内容的操作，但是语法不直接使用UPDATE，而是使用：

RENAME 旧的表名称 TO 新的表名称；

 

范例：将mydept表修改为temp

RENAME mydept TO temp ;

 

         同时需要说明的是，在用户创建新的数据库对象时或者是修改数据表名称时，所有的事务都会自动提交，但是事务只是对数据而言的，对数据表不会有任何的影响，DDL不受事务的控制。

3.4.6、删除数据表

         数据表本身属于数据库对象，所以，如果要想进行数据表的删除，那么就表示删除的是数据库对象，删除的语法：

DROP TABLE 数据表名称；

 

范例：删除temp表

DROP TABLE temp ;

 

3.4.7、Oracle 10g的新功能：FlashBack，闪回（理解）

         可以发现，在以上执行了DROP语法删除数据表的时候，每次删除都会留下一些记号：

                  · BIN$X9iwigZXSSOKuGBZXoRRHg==$0

         实际上这个就是在Oracle 10g之中为了保护误删表可以尽快恢复所支持的一个操作，非常类似于Windows的回收站功能，即：所有的数据表现在如果直接执行了DROP指令，并不会立刻删除，而是先将其保存在回收站之中。

范例：查看回收站

SHOW RECYCLEBIN ;

 

范例：从回收站之中恢复temp表

FLASHBACK TABLE temp TO BEFORE DROP ;

 

         当然，如果觉得数据表放回到回收站之中不是必须的，那么可以直接使用强制删除表的操作，只需要在DROP语句之中增加一个“PURGE”的选项即可。

DROP TABLE temp PURGE ;

 

范例：删除回收站之中的一张数据表

PURGE TABLE emp10 ;

 

范例：清空回收站

PURGE RECYCLEBIN ;

 

         这一操作从Oracle 10g之后开始存在，oracle 11g也是有的，不过由于其不具备通用性，所以只有在使用Oracle的时候才需要考虑。

3.4.8、修改数据表结构（了解）

         现在，为了解释问题，首先创建一张数据表：

CREATE TABLE member (

         mid            NUMBER          ,

         name          VARCHAR2(50)          DEFAULT '无名氏'

) ;

INSERT INTO member(mid,name) VALUES (1,'张三') ;

INSERT INTO member(mid,name) VALUES (2,'李四') ;

 

         在SQL语法之中，对于表结构的修改也有了其定义的语法，使用ALTER指令完成。

1、   为数据表增加新的数据列

         如果现在要为一张数据表里面增加新的列，则可以使用如下语法完成：

ALTER TABLE 表名称 ADD (字段数据类型 [DEFAULT 默认值] [,字段数据类型 [DEFAULT 默认值],..])

 

范例：为表中增加两个字段，一个有默认值，另外一个没有默认值

ALTER TABLE member ADD (sex VARCHAR2(20) DEFAULT '男') ;

ALTER TABLE member ADD (note CLOB) ;

 

         如果现在增加的新的字段存在了默认值，则会更新已有的全部数据记录。

2、   也可以修改已有的数据列

ALTER TABLE 表名称 MODIFY (字段数据类型 [DEFAULT 默认值] [,字段数据类型 [DEFAULT 默认值],..])

 

范例：修改已有的数据列

ALTER TABLE member MODIFY(name VARCHAR2(20) DEFAULT '匿名') ;

 

         但是从今天开始忘记以上语法，别写、别使。之所以这样强调，主要是从IBM DB2倡导的理念，此数据库最大的特点是数据表的表结构不允许修改。

         在以后进行项目设计时，尽可能考虑到有可能发生的改变，而这一点达到就是看你对于整个行业业务流程的把握了。而且虽然允许修改表结构，但是也不能随便由你一个人去决定修改。

3.4.9、思考题（面试题）

         现在要求建立一张nation表，表中只有一个name字段，保存的信息有：中国、美国、巴西、荷兰四个国家的信息，现在要求通过查询实现对战的操作，最终的显示效果如下：

                  •中国       美国；

                  •中国       巴西；

                  •中国      荷兰；

                  •美国       中国；

                  •美国       巴西；

                  •美国       荷兰；

                           …依次类推。。。

         既然现在表不存在，那么首先要创建的也肯定是数据表，而现在就可以给出数据库创建脚本（可以使用这些代码直接从无到有的生成数据表或者是重置数据表）的操作规范。

                  ·数据库创建脚本的文件后缀名称必须是*.sql；

                  ·删除已有的数据表，编写DROP指令；

                  ·编写创建表的语句；

                  ·为数据表增加测试数据；

                  ·提交事务；

-- 删除数据表

DROP TABLE nation PURGE ;

-- 创建数据表

CREATE TABLE nation (

         name          VARCHAR2(50)

) ;

-- 测试数据

INSERT INTO nation (name) VALUES('中国') ;

INSERT INTO nation (name) VALUES('美国') ;

INSERT INTO nation (name) VALUES('巴西') ;

INSERT INTO nation (name) VALUES('荷兰') ;

-- 提交事务

COMMIT ;

 

         要想实现此功能依靠笛卡尔积完成，但是自己不能跟自己对战。

SELECT n1.name,n2.name

FROM nation n1,nation n2

WHERE n1.name<>n2.name ;

 

         以后进行完数据库分析之后，就要编写数据库创建脚本。

3.5、约束（核心）

         数据表现在已经可以进行创建了，但是这个时候的数据表本身并不具备数据合法性的检查功能，例如：在表示人员年龄的时候其范围只能是：0~250、表示性别的时候只能是：男、女、不男不女、表示注册邮箱的时候不能够重复，但是这些操作在之前的数据表里面并不存在，要想对数据进行限制，那么就只能使用约束完成，而约束一共分为五种：非空约束、唯一约束、主键约束、外键约束（难）。

3.5.1、非空约束：NOT NULL，NK

         非空约束的概念主要是指一个字段上的内容不允许设置null，如果要指定非空约束，则直接在创建表字段的时候设置。

DROP TABLE memeber PURGE ;

CREATE TABLE member (

         mid            NUMBER ,

         name          VARCHAR2(50)          NOT NULL

) ;

 

         这个时候name字段上定义时存在了NOT NULL约束，所以下面观察数据的增加。

范例：增加正确的数据

INSERT INTO member(mid,name) VALUES (1,'张三') ;

 

范例：增加错误的数据

INSERT INTO member(mid,name) VALUES (2,null) ;

INSERT INTO member(mid) VALUES (3) ;

 

         错误提示：“ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."NAME")”；

         这个时候的错误提示直接会告诉用户那一个字段上违反了约束。

3.5.2、唯一约束：UNIQUE，UK

         所谓的唯一约束指的是在数据表中一个列上的数据内容不允许存在重复的记录，例如，现在假设每一个成员都应该拥有属于自己的邮箱，所以邮箱现在不应该重复。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER ,

         name          VARCHAR2(50)          NOT NULL ,

         email          VARCHAR2(200)        UNIQUE

) ;

 

范例：增加错误的数据（执行两遍）

INSERT INTO member(mid,name,email) VALUES (1,'张三','aa@11.com') ;

 

         错误提示：“ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005300)”

         发现这个时候出现的错误提示的名字或者说信息根本就不知道那个字段上违反了约束，对于Oracle而言，每一个约束实际上也属于一个数据库的对象，那么如果在设置约束的时候没有设置名字，则表示会由Oracle自动设置一个名称，但是这个名称往往不方便维护，所以可以使用CONSTRAINT关键字手工指定一个名称，而约束的名称建议的形式：约束简写_字段的形式，例如：在email字段上设置唯一约束：uk_email。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER ,

         name          VARCHAR2(50)          NOT NULL ,

         email          VARCHAR2(200)        ,

         CONSTRAINT uk_email UNIQUE(email)

) ;

 

         错误信息：“ORA-00001: 违反唯一约束条件 (SCOTT.UK_EMAIL)”；唯一约束可以设置为null。

3.5.3、主键约束：PRIMARY KEY，PK

         主键约束 = 唯一约束 + 非空约束，在创建数据表的时候可以通过PRIMARY KEY进行设置，例如：每一个成员都有一个属于自己的编号，那么这个唯一的编号就可以作为主键存在。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          PRIMARY KEY ,

         name          VARCHAR2(50)          NOT NULL

) ;

 

         这个时候增加两种数据：

INSERT INTO member(mid,name) VALUES (1,'张三') ;  à重复执行会违反唯一约束

INSERT INTO member(mid,name) VALUES (null,'张三') ; à出现非空约束

 

         而如果说现在希望在其违反唯一约束时可以明确的显示出错误信息，则只能利用CONSTRAINT设置主键约束名称。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          ,

         name          VARCHAR2(50)          NOT NULL ,

         CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;

 

         可是有另外一点需要注意的是，在一张数据表之中往往只会设置一个主键约束，当然，也可以同时为一张表中的多个字段同时设置为主键约束，这种操作的形式就被称为复合主键。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          ,

         name          VARCHAR2(50)          ,

         CONSTRAINT pk_mid PRIMARY KEY(mid,name)

) ;

 

         此时，mid和name两个字段上的内容都定义出了主键约束，所以此操作称为复合主键，复合主键的特点就是在：如果内容全部相同的时候，才表示不同的内容。

INSERT INTO member(mid,name) VALUES (1,'张三') ;

INSERT INTO member(mid,name) VALUES (1,'李四') ;

INSERT INTO member(mid,name) VALUES (2,'李四') ;

 

         但是需要说明的是，这种复合主键正常人不使。

3.5.4、检查约束：CHECK，CK

         检查约束指的就是要设置一系列的操作条件，例如：在设置年龄的时候需要有一个范围的限制，在设置性别的时候也要存在此限制等，这些都是依靠检查约束完成的。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          ,

         name          VARCHAR2(50)          ,

         age             NUMBER(3)      CHECK(age BETWEEN 0 AND 250),

         sex             VARCHAR2(20) ,

         CONSTRAINT pk_mid PRIMARY KEY(mid) ,

         CONSTRAINT ck_sex CHECK (sex IN ('男','女'))

) ;

 

         以后只有数据正确的操作，才可以正常的保存在数据表之中。

3.5.5、主-外键约束（难点）

         如果说现在希望设置一种数据表的关系：一个成员可以有多本书，现在也肯定要设置两张表，肯定现在在书的表中要存在一个成员的编号，这样就可以表示出一本属于一个人了，则，下面不使用外键的方式来建立表。

DROP TABLE member PURGE ;

DROP TABLE book PURGE ;

CREATE TABLE member (

         mid            NUMBER          PRIMARY KEY ,

         name          VARCHAR2(50)          NOT NULL

) ;

CREATE TABLE book (

         bid             NUMBER          PRIMARY KEY ,

         title            VARCHAR2(50)          NOT NULL ,

         mid            NUMBER

) ;

 

         现在的表之中只是设置了两个约束：主键约束、非空约束。在这个时候下面增加一些数据：

INSERT INTO member(mid,name) VALUES (1,'张三') ;

INSERT INTO member(mid,name) VALUES (2,'李四') ;

INSERT INTO book(bid,title,mid) VALUES (101,'Java SE开发',1) ;

INSERT INTO book(bid,title,mid) VALUES (102,'HTML开发',1) ;

INSERT INTO book(bid,title,mid) VALUES (103,'Oracle',2) ;

INSERT INTO book(bid,title,mid) VALUES (109,'DB2',2) ;

INSERT INTO book(bid,title,mid) VALUES (119,'JSP',2) ;

 

         以上的数据是是合法的，因为可以通过检测得出结果。

范例：要求查询出每一个成员所拥有的书的数量

SELECT m.name,COUNT(b.bid)

FROM member m,book b

WHERE m.mid=b.mid

GROUP BY m.name ;

 

范例：列出每一个成员和他所拥有的图书的名称

SELECT m.name,b.title

FROM member m , book b

WHERE m.mid=b.mid ;

 

         但是，由于这个时候没有设置任何的关联约束问题（mid）所以以下的数据也可以增加。

INSERT INTO book(bid,title,mid) VALUES (999,'晕',30) ;

 

         现在根本就没有一个成员的编号是30，但是这样的数据也可以保存，所以注定是一个错误的数据。

         即：现在book.mid字段的取值应该由member.mid所决定，而此时也就可以得出这样的关系，子表（book）中的某个字段的取值范围必须有主表（member）中的指定字段所设置，而这一功能的完成就需要通过外键约束。

         但是需要说明的是，被设置为外键的字段必须具备唯一性，即：在主表之中应该是主键或者是唯一约束。

DROP TABLE member PURGE ;

DROP TABLE book PURGE ;

CREATE TABLE member (

         mid            NUMBER          PRIMARY KEY ,

         name          VARCHAR2(50)          NOT NULL

) ;

CREATE TABLE book (

         bid             NUMBER          PRIMARY KEY ,

         title            VARCHAR2(50)          NOT NULL ,

         mid            NUMBER ,

         CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)

) ;

 

         这句话的含义就是指book.mid字段设置为了外键，其内容要参考member表的mid字段的内容。

         清楚了外键的操作之后，那么下面就要解决一些新的问题了。

1、   问题一：在删除表时，必须先删除子表后再删除父表

         就拿本程序来讲，应该先删除book表，而后再删除member表；

DROP TABLE book PURGE ;

DROP TABLE member PURGE ;

 

         虽然说这种删除的方式可以实现表的删除操作，但是这种操作一个最大的问题在于，所有的表在删除之前要先分清楚先后的关系，这本来就是一件很耗费时间的事，为了解决这样的问题，在SQL之中提供了强制删除数据表的操作。

DROP TABLE member CASCADE CONSTRAINT ;

 

         但是这种操作只在极其特殊的环境下使用，理论上，数据库创建脚本应该考虑到这些删除的先后关系问题。

2、   问题二：如果在删除父表数据时，还存在着其对应的子表数据，则父表数据无法删除

         但是这样的操作本身并不方便，例如：一个学生被学校开除了，那么这个学生的所有成绩就没有用处了，而要想实现这些级联的数据操作功能，那么在SQL语句之中可以使用两种选项：ON DELETE CASCADE、ON DELETE SET NULL。

（1）、级联删除：ON DELETE CASCADE

         当主表中的数据被删除之后，对应的子表中的数据也同时被删除掉。

DROP TABLE book PURGE ;

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          PRIMARY KEY ,

         name          VARCHAR2(50)          NOT NULL

) ;

CREATE TABLE book (

         bid             NUMBER          PRIMARY KEY ,

         title            VARCHAR2(50)          NOT NULL ,

         mid            NUMBER          REFERENCES member(mid) ON DELETE CASCADE

) ;

 

（2）、级联设置null：ON DELETE SET NULL

         当主表中数据被删除时，对应的子表中相应字段的内容自动的更新为null

DROP TABLE book PURGE ;

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          PRIMARY KEY ,

         name          VARCHAR2(50)          NOT NULL

) ;

CREATE TABLE book (

         bid             NUMBER          PRIMARY KEY ,

         title            VARCHAR2(50)          NOT NULL ,

         mid            NUMBER          REFERENCES member(mid) ON DELETE SET NULL

) ;

 

         至于在开发之中到底什么时候设置级联，什么时候不设置，这些完全由项目的业务功能所决定。

3.5.6、修改约束（了解）

         如果说现在要进行约束的修改，那么首先必须有一个前提要保证，要维护一个数据库对象，那么必须知道约束的名字，而在Oracle之中，为了方便用户查找约束的名称专门提供了两个数据字典：user_constraints、user_cons_columns。

范例：查询user_constraints

SELECT owner,constraint_name,table_name FROM user_constraints ;

 

         但遗憾的是，在此数据字典里面无法确定那个数据列上存在约束，所以要使用另外一张数据字典。

范例：查询user_cons_columns

COL owner FOR A15 ;

COL constraint_name FOR A15 ;

COL table_name FOR A15 ;

COL column_name FOR A15 ;

SELECT owner,constraint_name,table_name,column_name FROM user_cons_columns ;

 

         所以这两张数据表就为了修改约束提供了支持，但是为了方便起见，如果日后需要维护的话，还是要起一个名字。

         修改约束依然属于数据库对象的修改过程，既然是修改数据库对象就使用ALTER语法完成，修改约束主要有两种操作：增加约束、删除约束。

1、   增加约束

         如果一张数据表在建立的时候没有指定约束，则可以使用如下语法为其增加一个新的约束：

ALTER TABLE 表名称 ADD CONSTRAINT 约束类型(约束字段) [其他选项] ;

 

         但是需要注意的是，虽然有五种约束，但是非空约束可不是采用这种方法设置的，只能通过之前讲解过的MODIFY操作完成（修改一个列）。

DROP TABLE member PURGE ;

CREATE TABLE member (

         mid            NUMBER          ,

         name          VARCHAR2(50)          NOT NULL

) ;

 

范例：为表中增加主键约束

ALTER TABLE member ADD CONSTRAINT pk_mid PRIMARY KEY(mid) ;

 

         如果现在数据表之中没有违反此约束的数据存在的话，则可以正常的增加，反之则无法增加。

2、   删除约束

         如果要删除约束，那么一定要为约束设置名称，否则无法删除，删除约束的语法如下：

ALTER TABLE 表名称 DROP CONSTRAINT 约束名称 ;

 

范例：删除表中的主键约束

ALTER TABLE member DROP CONSTRAINT pk_mid ;

 

         但是这种修改约束的操作和修改数据表的操作一样，不建议使用，而且只要是约束，一定要在建立表的同时就为其增加完整。

3.6、建表、更新、查询综合练习（重点）

1、   现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：

·商品product（商品号productid，商品名productname，单价unitprice，商品类别category，供应商provider）；

·顾客customer（顾客号customerid，姓名name，住址location）；

·购买purcase（顾客号customerid，商品号productid，购买数量quantity）；

根据题目要求建立约束；

2、   插入以下的测试数据：

商品（

         M01，佳洁士，8.00，牙膏，宝洁；

         M02，高露洁，6.50，牙膏，高露洁；

         M03，洁诺，5.00，牙膏，联合利华；

         M04，舒肤佳，3.00，香皂，宝洁；

         M05，夏士莲，5.00，香皂，联合利华；

         M06，雕牌，2.50，洗衣粉，纳爱斯

         M07，中华，3.50，牙膏，联合利华；

         M08，汰渍，3.00，洗衣粉，宝洁；

         M09，碧浪，4.00，洗衣粉，宝洁；）

顾客（

         C01，Dennis，海淀；

         C02，John，朝阳；

         C03，Tom，东城；

         C04，Jenny，东城；

         C05，Rick，西城；）

购买(

         C01，M01，3；

         C01，M05，2；

         C01，M08，2；

         C02，M02，5；

         C02，M06，4；

         C03，M01，1；

         C03，M05，1；

         C03，M06，3；

         C03，M08，1；

         C04，M03，7；

         C04，M04，3；

         C05，M06，2；

         C05，M07，8；）

 

                  3、   完成如下的数据操作要求：

A、  求购买了供应商"宝洁"产品的所有顾客；

B、   求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客（姓名）；

C、   求牙膏卖出数量最多的供应商；

D、  将所有的牙膏商品单价增加10%；

E、   删除从未被购买的商品记录。

3.3.1、建表

DROP TABLE purcase PURGE ;

DROP TABLE product PURGE ;

DROP TABLE customer PURGE ;

CREATE TABLE product(

         productid   VARCHAR2(10)          PRIMARY KEY ,

         productname       VARCHAR2(50)          NOT NULL ,

         unitprice    NUMBER          NOT NULL ,

         category     VARCHAR2(50)          NOT NULL ,

         provider     VARCHAR2(50)          NOT NULL

) ;

CREATE TABLE customer (

         customerid VARCHAR2(10)          PRIMARY KEY ,

         name          VARCHAR2(50)          NOT NULL ,

         location      VARCHAR2(200)        NOT NULL

) ;

CREATE TABLE purcase (

         customerid VARCHAR2(10)          REFERENCES customer(customerid) ON DELETE CASCADE ,

         productid   VARCHAR2(10)          REFERENCES product(productid) ON DELETE CASCADE ,

         quantity     NUMBER

) ;

 

3.3.2、增加数据

1、   增加商品记录

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M01','佳洁士',8.00,'牙膏','宝洁') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M02','高露洁',6.50,'牙膏','高露洁') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M03','洁诺',5.00,'牙膏','联合利华') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M04','舒肤佳',3.00,'香皂','宝洁') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M05','夏士莲',5.00,'香皂','联合利华') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M06','雕牌',2.50,'洗衣粉','纳爱斯') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M07','中华',3.50,'牙膏','联合利华') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M08','汰渍',3.00,'洗衣粉','宝洁') ;

INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M09','碧浪',4.00,'洗衣粉','宝洁') ;

 

2、   增加顾客记录

INSERT INTO customer(customerid,name,location) VALUES ('C01','Dennis','海淀') ;

INSERT INTO customer(customerid,name,location) VALUES ('C02','John','朝阳') ;

INSERT INTO customer(customerid,name,location) VALUES ('C03','Tom','东城') ;

INSERT INTO customer(customerid,name,location) VALUES ('C04','Jenny','东城') ;

INSERT INTO customer(customerid,name,location) VALUES ('C05','Rick','西城') ;

 

3、   购买记录

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C01','M01',3) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C01','M05',2) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C01','M08',2) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C02','M02',5) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C02','M06',4) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C03','M01',1) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C03','M05',1) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C03','M06',3) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C03','M08',1) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C04','M03',7) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C04','M04',3) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C05','M06',2) ;

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C05','M07',8) ;

COMMIT ;

 

         此时程序的关系属于多对多的表关系。一个客户可以购买多种商品，每一个商品可以被多个用户所购买。

3.3.3、数据操作

1、   求购买了供应商"宝洁"产品的所有顾客；

         ·确定所需要的数据表：

                  |- customer：找到顾客的完整信息；

                  |- purcase：购买记录可以找到一个商品购买的顾客信息；

                  |- product：需要通过这张表找到“宝洁”产品的全部产品ID，此时返回的是多行单列；

         ·确定已知的关联字段：

                  |- 顾客和购买记录：customer.customerid=purcase.customerid；

                  |- 商品和购买记录：product.productid=purcase.productid；

第一步：要找到“宝洁”的所有商品的编号

SELECT productid

FROM product p

WHERE p.provider='宝洁' ;

 

第二步：以上的是多行单列的记录，可以在WHERE子句之中出现

SELECT DISTINCT p.customerid

FROM purcase p

WHERE p.productid IN (

         SELECT productid

         FROM product p

         WHERE p.provider='宝洁') ;

 

第三步：已经知道了顾客编号，就可以查询出顾客的完整信息

SELECT *

FROM customer c

WHERE c.customerid IN (

         SELECT DISTINCT p.customerid

         FROM purcase p

         WHERE p.productid IN (

                  SELECT productid

                  FROM product p

                  WHERE p.provider='宝洁')) ;

 

2、   求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客（姓名）；

         ·确定所需要的数据表：

                  |- customer：顾客姓名；

                  |- purcase：找到一个顾客所购买的全部商品的编号；

         ·确定已知的关联字段：

                  |- 顾客和购买记录：customer.customerid=purcase.customerid；

第一步：找到Dennis的顾客编号，并且依据此编号找到对应的商品编号

SELECT p.productid

FROM customer c,purcase p

WHERE c.customerid=p.customerid

         AND c.name='Dennis';

 

第二步：根据已经查询出来的商品编号，找到对应的顾客编号

SELECT DISTINCT p.customerid

FROM purcase p

WHERE p.productid IN (

         SELECT p.productid

         FROM customer c,purcase p

         WHERE c.customerid=p.customerid

                  AND c.name='Dennis');

 

第三步：确定顾客的姓名

SELECT *

FROM customer

WHERE customerid IN (

         SELECT DISTINCT p.customerid

         FROM purcase p

         WHERE p.productid IN (

                  SELECT p.productid//in关键字后面的字段只能是一个

                  FROM customer c,purcase p

                  WHERE c.customerid=p.customerid

                           AND c.name='Dennis'))

         AND name<>'Dennis';

 

3、   求牙膏卖出数量最多的供应商；

         ·确定所需要的数据表：

                  |- product：查找出供应商的名字；

                  |- purcase：销售数量统计；

         ·确定已知的关联字段：product.productid=purcase.productid；

第一步：暂时不考虑最多的问题，只是统计出每个供应商卖牙膏的销售量

SELECT p.provider,COUNT(pc.quantity)

FROM product p,purcase pc

WHERE p.productid=pc.productid

         AND p.category='牙膏'

GROUP BY p.provider ;

 

第二步：计算出最高销售量，应该对COUNT()函数的结果进行求出最大值的操作，属于统计函数嵌套，而统计函数嵌套时不能出现任何的字段，包括分组字段。

SELECT MAX(COUNT(pc.quantity))

FROM product p,purcase pc

WHERE p.productid=pc.productid

         AND p.category='牙膏'

GROUP BY p.provider ;

 

第三步：将第一步增加一个HAVING子句，使用第二步计算的结果判断

SELECT p.provider,COUNT(pc.quantity)

FROM product p,purcase pc

WHERE p.productid=pc.productid

         AND p.category='牙膏'

GROUP BY p.provider

HAVING COUNT(pc.quantity)=

         (SELECT MAX(COUNT(pc.quantity))

         FROM product p,purcase pc

         WHERE p.productid=pc.productid

                  AND p.category='牙膏'

         GROUP BY p.provider) ;

 

4、   将所有的牙膏商品单价增加10%；

UPDATE product SET unitprice=unitprice*1.1 WHERE category='牙膏' ;

 

5、   删除从未被购买的商品记录。

DELETE FROM product

WHERE productid IN (

         SELECT productid

         FROM purcase

         GROUP BY productid

         HAVING COUNT(productid)=0) ;

 

         像本类的这种建表、约束、更新、查询操作的题目要求能够熟练编写。

3.7、前台工具（了解）

         Oracle本身实际上所提供的只是一个数据库的服务，但是在这个服务之中，有一些问题出现，主要是针对于前台界面：

                  · Oracle 9i：OEM、sqlplusw、sqlplus；

                  · Oracle 10g：sqlplusw、sqlplus；

                  · Oracle 11g：sqlplus，SQL Developer；

         可是在一般的开发之中，不可能说每一个开发者电脑上都安装一个数据库，所以往往会使用一个专门的数据库服务器提供数据库的支持，而每个开发者电脑上都会使用一个客户端进行连接，而这个客户端（需要单独配置Oracle开发环境）往往会使用PL SQL Developer工具完成。

         此工具据说是$180一套，好像买4套是$600，不过国内一分不花。

         但是此工具要想使用，必须有监听服务支持。

 

扩展：关于Oracle监听服务问题的解决。开发的第一大头疼问题。

         Oracle数据库直接与网络的依附有关，所以一旦网络环境发生改变，那么Oracle 11g以下的版本无法自动的匹配，则需要用户手工控制，修改两个文件（D:oracleproduct10.1.0db_1NETWORKADMIN）：listener.ora、tnsnames.ora。

         如果自动的服务不好使用，则可以通过手工控制完成，控制命令：D:oracleproduct10.1.0db_1BINLSNRCTL.EXE

如果此时依然无法连接，则可以通过网络配置，注册服务ID（SID），SID就是数据库的名称，那么有可能是因为网络环境发生改变之后，所导致服务ID消失了，这个时候可以通过网络管理重新增加回来。

4、总结

1、   表的创建和管理：主要数据类型、创建和删除表、清空回收站；

2、   约束：只要是表就一定要定义约束，五种约束的定义及特点；

3、   查询：复杂查询的操作一定要熟练使用；

4、   数据更新：INSERT、UPDATE、DELETE；

5、   分页显示操作：ROWNUM；

5、预习任务

         视图、同义词、序列、索引、用户管理、数据库备份。

6、作业

         1、   熟练掌握DML、DDL；

         2、   开始预习Java基础；

EditPlus我所知道的快捷键：

         · CTRL + Z：撤消；

         · CTRL + Y：恢复；

         · CTRL + S：保存；

         · CTRL + N：新建；

         · CTRL + J：复制当前行；

         · TAB：制表符；

         · SHIFT + TAB：取消一个制表符；