蓝桥学习数据库笔记

来源：网络收集时间：2018-10-27 下载这篇文档手机版

说明：文章内容仅供预览，部分内容可能不全，需要完整文档或者需要复制内容，请下载word后使用。下载word有问题请添加微信号:或QQ：处理（尽可能给您提供完整文档），感谢您的支持与谅解。

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 约束名称约束类型(字段);

? 删除表中的约束：

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;

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语句，视图的创建语法如下：

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 子句违规

意味着现在根本就不能去更新视图的创建条件。

选项二：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 用户名.表名称;

数据库Oracle笔记

白马非马

2016年8月21日

第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

三、数据删除 ____________________________________________________________ 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

第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关键字。

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的员工

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(

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,

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,'张三');

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

要想验证这个数据是否有意义，最简单的做法，就是写两个查询。范例：统计每个人员拥有书的数量

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)

);

此时，只是增加了一个约束，这样一来如果输入的数据有错误，则会出现如下的提示：

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(

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；

第二页：

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;

此时返回的三个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 );

表创建成功之后，下面开始向表中增加数据：

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数据库所独有的一种特性，所以了解即可，不用做深入的掌握。

五、表的截断

在之前曾经讲解过一个删除表数据的操作，使用的是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 表名称;

范例：删除回收站中的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 默认值],…);

范例：将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) );

--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

);

因为此时存在了―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完成。

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库蓝桥学习数据库笔记在线全文阅读。

蓝桥学习数据库笔记.doc 将本文的Word文档下载到电脑，方便复制、编辑、收藏和打印下载失败或者文档不完整，请联系客服人员解决！

下载这篇word文档

本文链接：https://www.77cn.com.cn/wenku/zonghe/236377.html（转载请注明文章来源）

上一篇：2018年高三语文一轮总复习(写作作文专项突破(六)结构)第02课典型
下一篇：无机化学实验(冯贻华)