第9章数据库编程

Java程序设计张家羽 整理Java程序开发 从这里开始注：java中的对象（引用变量），是一个包含数据（存储在JVM的堆空间中）地址的指针。在Java、C+、C#等编程语言中，所有的引用变量就是一个地址值即C语言中称的“指针”，虽说高级语言（面向对象）不能操作指针，但处处都在操作指针对象存储的不是对象数据本身，而是数据的地址。从C语言编程 升级上来的同学们可以这里理解：面向对象编程中的一般数组 相当于 C语言中的指针数组（存储的是地址）；面向对象编程中的类 类似于 C语言中的结构体（不等于）；面向对象编程中的接口 类似于 C语言中的指针类型的结构体；目 录目 录 .2第 1 章 数据库编程 .41.1 关系数据库 41.1.1 主流关系型数据库 .41.1.2 数据库基础 .51.2 Oracle 数据库访问 .51.3 SQL 语言 71.3.1 SQL 组成 .71.3.2 Oracle 数据类型 81.3.3 SQL（DDL、DML） .91.4 数据格式操作 111.4.1 字符串操作 .111.4.2 数值操作 .111.4.3 日期操作 .121.4.4 空值操作 .131.5 数据查询 DQL 语句 .131.5.1 基础查询 .131.5.2 关联查询 .161.5.3 高级查询 .171.6 视图 191.6.1 创建视图 .201.6.2 删除视图 .211.7 序列 211.7.1 创建序列 .211.7.2 删除序列 .211.8 索引 221.8.1 创建索引 .221.8.2 修改和删除索引 .231.8.3 创建和使用索引的原则 .231.9 约束 231.9.1 约束的类型 .231.10 数据库设计 241.11 JDBC 编程 .251.11.1 JDBC 基础 251.11.2 JDBC 基础编程 291.11.3 连接池技术 .291.11.4 SQL 异常处理 .311.11.5 JDBC 高级编程 .311.12 DAO（数据访问对象） 341.12.1 对象关系映射 .341.12.2 编写 DAO .341.13 附 35第 1 章 数据库编程DB、DBMS数据库（Database，简称DB ）：按照数据结构来组织、存储和管理数据的仓库，是存放数据、访问数据、操作数据的存储仓库。数据库管理系统(Database Management System,简称DBMS)：管理数据库的软件。数据库建立了数据之间的联系，使用结构化的方式组织和处理数据，能够统一、集中及独立的管理数据，使数据的存取独立于使用数据的程序，实现了数据共享。数据库的典型特征包括：数据的结构化，数据间的共享，减少数据的冗余度，以及数据的独立性。1.1 关系数据库关系数据库：采用关系模型作为数据组织方式的数据库。主流产品有：Access、SQL Server（MS）、 Oracle（Oracle）、DB2（IBM）、MySQL（Oracle）1.1.1 主流关系型数据库Oracle数据库Oracle是Oracle(甲骨文) 公司的数据库产品，是世界上第一个商品化的关系型数据库管理系统，也是第一个推出和数据库结合的第四代语言开发工具的数据库产品。Oracle采用标准的SQL 结构化查询语言，支持多种数据类型，提供面向对象的数据支持，具有第四代语言开发工具，支持UNIX、 WINDOWS、OS/2 等多种平台。DB2数据库DB2是IBM的关系型数据库管理系统，可以运行在从掌上产品到大型机不同的终端机器上。DB2 Universal Database Personal Edition和DB2 Universal Database Workgroup Edition分别是为OS/2和Windows系统的单用户和多用户提供的数据库管理系统。DB2在高端数据库的主要竞争对手是Oracle。SQL Server数据库Microsoft SQL Server是运行在Windows NT服务器上，支持C/S结构的数据库管理系统。采用标准SQL语言，微软公司对它进行了部分扩充而成为事务SQL（Transact-SQL）。SQL Server最早是微软为了要和IBM竞争时，与Sybase合作所产生的，其最早的发展者是Sybase，和Sybase数据库完全兼容。在与 Sybase终止合作关系后，微软自主开发出 SQL Server 6.0版，往后的SQL Server即均由微软自行研发。Microsoft SQL Server几个初始版本适用于中小企业的数据库管理，但是后来它的应用范围有所扩展，已经触及到大型、跨国企业的数据库管理。MySQL数据库MySQL是一个开放源码的小型关系型数据库管理系统，开发者为瑞典MySQL AB公司。目前MySQL被广泛地应用在Internet 上的中小型网站中。与其它的大型数据库例如Oracle、IBM DB2等相比，MySQL自有它的不足之处，如规模小、功能有限等，但对于一般个人使用者和中小型企业来说，MySQL提供的功能已经绰绰有余，而且由于MySQL是开放源码软件，因此可以大大降低总体拥有成本，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。2008年1月16日，Sun正式收购MySQL。2009年4月20日，SUN被Oracle公司收购。1.1.2 数据库基础基本概念关系（relation）：一张规范化的二维表。元组（tuple ）：关系中除表头以外的一非空行，也称为记录（Record ）。属性（attribute）：关系中的每一列，也称为字段（Field）。属性值（attribute value）：元组中一个属性所取的具体值。域：属性的取值范围。关系模式：对应于二维表的表头，是对关系的结构的描述，一般表示为关系名(属性1，属性2属性n) 。候选键：二维表中可以唯一确定一个或一组属性的集合。一个关系中可以有若干个候选键。主键：即主关键字，选择用作参照的候选键。一个表有且仅有一个主键。外键：除主键外的候选键，且是另一个关系中的主键。关系模型的完整性约束：实体完整性（entity integrity ）：关系中每个一元组的主键属性值不能为空值。（必须）参照完整性（reference integrity）：描述实体之间的引用关系。若属性组A 是关系R 的外键同时又是关系S的主键，则对于R 中的每一个元组，属性A的值或空（表示没有在S出现），或必须等于S中的某一个元组的主键值。（必须）用户自定义完整性（user defined integrity）：指明关系中属性的取值范围，也称为域完整性。关系运算：包括选择、投影、连接操作，运算的对象是关系，运算的结果也为关系。选择操作：在关系中选择满足某些条件的元组。投影操作：在关系中选择若干属性列组成新的关系。连接操作：将不同的两个关系连接成为一个关系。连接的结果是一个包含原关系所有属性的新关系。1.2 Oracle 数据库访问远程登录数据库服务器Oracle数据库的开发和管理工具主要有两种：命令行工具（Oracle自带的SQL*PLUS）和图形界面工具（Oracle提供的 SQL Developer）。这些工具可以和数据库在同一台服务器上，也可以安装在另一台机器中。使用本机的S Q L D e v e l o p e rT e l n e t 到远程机使用远程机的 S Q L * P L U S使用本机的S Q L * P L U SO r a c l e 服务器S Q L * P L U S图 1.1 操作数据库的方式SQL*Plus是Oracle数据库提供的，一个基于传统C/S 结构的和数据库进行交互的开发工具。可以运行在任何Oracle运行的平台上，默认和数据库一起安装；客户端和服务器端可以在同一台主机或不同主机上。使用远程机的 SQL*PLUSTelnet（远程）登录到数据库所在的机器上(需要远程机开启 telnet服务，并还可能需要防火墙设置23端口例外) ，输入远程机器操作系统的帐号和密码，然后在% 提示符下，输入SQLPlus 命令，并输入数据库的帐号和密码，如果出现SQL提示符（Connected to： ），则表示登录成功。退出时，在SQL后输入exit退出数据库；再次输入exit，即退出远程服务器。使用 Oracle SQL DeveloperOracle SQL Developer是Oracle官方出品的免费图形化开发工具，相对 SQL*Plus来说，图形化的界面便于操作，不必记忆大量的命令，输出结果美观。基本功能包括结果的格式化输出，编辑器自动提示，代码美化，显示SQL的执行计划，监控会话，编写以及调试存储过程等。第一次进入SQL Developer，需要设置连接参数。首先在窗口左边的 Connection（连接）上按右键，新建一个连接，输入远程数据库的参数，界面如所示。图 1.2 设置连接用户信息成功建立了连接之后，即可打开对应用户方案中的数据库对象了。如，在这个界面中，即可操作SQL语句了。1.3 SQL 语言SQL(Structured Query Language)：即结构化查询语言，是关系数据库执行数据操作、检索及维护而使用的标准语言。SQL可以用来查询数据，操纵数据，定义数据，控制数据。执行SQL语句时，用户只需要知道其逻辑含义，而不需要知道SQL语句的具体执行步骤。1.3.1 SQL 组成1234（ Definition definin：定义 ） ：数据定义语言（ Manipulation mnpjlen：操作 ） ：数据操作语言（ Transactiontrnzkn：事务 ） ：事务控制语言（ Query kwiri：查询 ）：数据查询语言（ ）：数据控制语言 数据定义语言（DDL）用于建立、修改、删除数据库对象，包括创建语句(CREATE)、修改语句(ALTER) 、删除语句(DROP) ，比如使用CREATE TABLE创建表，使用ALTER TABLE修改表，使用DROP TABLE删除表等动作。这类语言不需要事务的参与，自动提交。数据操作语言（DML）用于改变数据库数据，包括INSERT、UPDATE、DELETE三条语句。其中，INSERT语句用于将数据插入到数据库中，UPDATE语句用于更新数据库中已存在的数据， DELETE用于删除数据库中已存在的数1 Definition definin：定义2 Manipulation mnpjlen：操作3 Transactiontrnzkn：事务4 Query kwiri：查询据。DML语言和事务是相关的，执行完 DML操作后必须经过事务控制语句提交后才真正的将改变应用到数据库中。事务控制语言（TCL）用来维护数据一致性的语句，包括提交(COMMIT)、回滚(ROLLBACK)、保存点(SAVEPOINT)三条语句。其中COMMIT用来确认已经进行的数据库改变，ROLLBACK语句用来取消已经进行的数据库改变。当执行DML操作后(也就是上面说的增加、修改、删除等动作 )，可以使用COMMIT语句来确认这种改变，或者使用ROLLBACK取消这种改变。SAVEPOINT语句用来设置保存点，使当前的事务可以回退到指定的保存点，便于取消部分改变。数据查询语言（DQL）用来查询所需要的数据。使用最广泛，语法灵活复杂。数据控制语言（DCL）用于执行权限的授予和收回操作、创建用户等，包括授予(GRANT)语句，收回(REVOKE)语句，CREATE USER语句。其中 GRANT用于给用户或角色授予权限，REVOKE用于收回用户或角色已有的权限。DCL语句也不需要事务的参与，是自动提交的。1.3.2 Oracle 数据类型类型 说明 定义格式number 数字类型number(precision5, scale6)，当scale=0 时 number(p)。precision表示数字的总位数(不含小数点)，scale表示小数位数。若没有指定precision，则默认precision=38；若没有指定scale，则默认scale =0。number变种 数字类型numeric(p,s) number(p,s) decimal(p,s)或dec(p,s) number(p,s) integer/int number(38) smallint number(38) float(b)：映射至number 类型double precision：映射至number类型real：映射至number 类型char 定长字符串类型 char(n)n表示占用的字节数，最大取值为2000字节.varchar2 变长字符串类型 varchar2(n)n表示最多可占用的字节数，最大长度为4000字节。long 扩展变长字符串类型longvarchar2的加长版，最大长度为2GB，但有以下限制：每个表只能有一个long类型列，不能作为主键，不能建立索引，不能出现在查询条件中。5 precision prisin：精度6 scale skeil：标度clob 扩展变长字符串类型 clobvarchar2的加长版，最大长度为4GB。date 定义日期时间的数据 长度是7个字节datedate范围为：公元前4712年1月1日公元9999年12月31日。date类型在数据库中的实际存储(7个字节)固定格式为：世纪+100 年 月 日 时+1 分+1 秒+1关于date的其它信息见107页。timestamp7 时间戳timestamp(n)n为时间“秒”的精度位数，范围为09，默认为6.数据库内存用7或11字节存储，第17字节与date相同。精度为0时用7个字节存储，精度大于0时用11个字节存储，第811字节存储纳秒数(整数)。null 空值 表示某些属性的数据未知或暂时不存在。详细说明：char和varchar2类型都用于表示字符串数据类型。char：定长方式存放字符串，若数据实际长度不足，则用空格补齐；varchar2：变长方式存放字符串，占用空间数量为数据的实际长度。char和varchar2的存储编码：字符串在数据库中存储的默认单位是字节，也可显式地指定为字符。如：常规声明 显示声明char(10) char(10 byte) char(10 char)，20个字节varchar2(10) varchar2(10 byte) varchar2(10 char)，20个字节每个英文字符占用一个字节，每个中文字符按编码不同，占用2-4个字节：zhs16gbk 为2个字节；utf-8为2-4个字节。另外，char如果不指定长度，默认为1个字节，varchar2必须指定长度。1.3.3 SQL（DDL、DML）说明：下文中的“ ”的内容表示可根据实际情况省略内容。显示当前账户名：show user;DDL 语句1. 创建表create语句语法格式：create table schema8. 表名(属性(字段)名 数据类型默认值表达式, 其它设置项, 完整性约束);7 timestamptaimtmp：时间戳8 schema ski:m：模式，概要。这里指该表所属的用户其中，schema表示该表所属的用户，省略时为当前连接的用户。此处的完整性约束主要有：主键子句（Primary Key）、外键子句（ Foreign Key）和检查子句（Check ）。Primary Key的用法格式为： Primary Key(属性名) ，表示该属性列必须满足主键约束的要求（非空、不重复）。(1) 指定默认值default语句在创建表的过程中，在数据类型之后设置默认值，语法格式为： 数据类型 default 默认值(2) 非空约束not null非空(not null)是一种约束条件，用于设置某个属性值不能为空。默认情况下，主键之外的任何属性都允许有空值。执行更新操作时，不能设置这个属性的值为null 。语法格式为： 数据类型 not null2. 删除表drop语法格式：drop table 表名;3. 查看表的数据结构desc语句语法格式：desc 表名;4. 修改表语法格式：alter table 表名 其它指令；（不适用于下面的rename ）(1) 修改表名rename语句语法格式：rename 原表名 to 新表名;(2) 增加属性add语法格式：alter table 表名 add(属性名 数据类型 其它设置项)注：列只能增加在最后，不能插入到现有的列中。(3) 修改属性modify 9此语句用于修改表中属性的数据类型、长度和默认值等注：修改操作仅对以后插入的数据有效，若表中已有数据，把数据长度改小，则修改操作不会被执行。语法如下：alter table 表名 modify (属性名 数据类型 其它设置项 );修改属性名：alter table 表名 rename column 原属性名 to 新属性名;(4) 删除属性drop 10语法格式：alter table 表名 drop (属性名);DML 语句1. 添加元组(记录)insert语句语法格式：insert into 表名(属性名1,属性名2) values(值1,值2);注：所有的dml操作，在完成后，都需要执行事务提交语句 commit，才能执行此操作更新的内容。属性名与值一一对应，省略时，将按表中属性的顺序进行赋值，没有对应值的属性将赋空值null 。9 modifymdifai：修改10 dropdrp：除去，撤消oracle中的日期数据比较特殊，如果插入的数据有日期属性，则需要考虑日期的格式。oracle 默认的日期格式为dd-mon-yy（即日- 月- 年），可以使用自定义日期格式（用to_date()函数转换为自定义格式。to_date()使用格式为： to_date(日期数据, 格式)，格式设置形如：yyyy-mm-dd.2. 更新(修改)元组update 语句语法格式：update 表名 set 属性名1 = 值1 , 表名2= 值2 where condition11;其中，where子句用于条件筛选，是可选项，若没有where子句，则全表的数据都会被更新。注：当判断属性值是否为空(null)时应使用“is ”而不是“=”.3. 删除元组delete语句语法格式：delete from 表名 where condition;其中，where子句用于条件筛选，是可选项，若没有where子句，则全表的数据都会被删除。DQL 语句略，关于DQL 见 110页。1.4 数据格式操作1.4.1 字符串操作表 1.1 9.4 字符串操作操作符或函数 类别 说明concat() 字符串连接 返回两个字符串连接后的结果，一般用于查询语句的选择分部。| 字符串连接符 用于两个或多字符串的连接。等价于concat()。当某个字符串为null时，则此字符串位置连接的将是一个空格。length(char) 长度运算 返回字符串的长度。若字符类型是varchar2，返回字符串的实际长度；若字符串类型是char，则长度包括后补空格。upper(char) 转换为大写 将字符串中的每个字母转为大写，若字符串为null，则返回null。lower(char) 转换为小写 将字符串中的每个字母转为小写，若字符串为null，则返回null。initcap(char) 首字母大写 将字符串中每个单词的首字母转为大写，其它字母为小写，若字符串为null，则返回null。trim(c2 from c1) 字符串去除 从c1的前、后截去c2 。ltrim(c1, c2) 字符串去除 从c1的左边（left）截去c2 。若没有c2 ，则去除空格。rtrim(c1, c2) 字符串去除 从c1的右边（right）截去c2。若没有c2，则去除空格。lpad(char1, n, char2) 左补位函数12将char2补到char1的左边，总字符数为n，char2可重复多次。rpad(char1, n, 右补位函数 将char2补到char1的右边，总字符数为n，char2可重复多次。11conditionkndin：条件12 pad pd：插入char2)substr(char,m, n) 截取子串返回char中从m位开始的n个字符的子串，字符串的首位计数从1开始。若m=0 ，则从首字符开始；若 m取负数，则从尾部开始；若没有设置n，或者n的长度超过了char的长度，则取到剩下的所有字符。instr(char1, char2,n, m)返回子串位置返回子串char2在源字符串char1中的位置。从n的位置开始搜索，若没有指定n，则从第1个字符开始搜索。m用于指定子串的第m次出现次数，若不指定，则取m=1。若在char1中没有找到char2，则返回0。1.4.2 数值操作表 1.2 数值操作操作符或函数 说明round(n, m)将n按照指定的要求m进行四舍五入。 (m)m0，表示四舍五入到小数点后m 位；m=0，表示四舍五入到整数位；缺省时，m=0. ；m=n 的最小整数。floor14(n) 返回 小于= 大于等于=. 如：A any B，表示A的结果大于B 中最小的结果即可；all用于“A 比较运算符 all (B)”中，要求A 的结果满足与B中的结果进行比较运算后的全部结果为true。可用的比较运算符有：、=.如：A all B，表示A的结果大于B中最大的结果才可；在查询条件中还可以进行多种运算和调用函数。其它查询子句其它查询子句是对 select 查询的进一步约束，其语法格式为： where 分组 排序1去重，见查询项目的结果筛选部分。2排序，语法格式为： order by 属性名 排序依据 排序方式;注：order by子句必须写在select语句中的最后部分。排序依据：指依据查询项目列表中的哪几项进行排序，可以直接使用属性名或别名，也可以使用列表序号（1，2，）对应于查询项目列表的项。排序方式有：asc 15（升序，缺省）和 desc16(降序) 。注意，null 值被视作最大。关于排序的其它方法，见115页。3多列排序以多列作为排序标准时，首先按照第一列进行排序，如果第一列数据相同，则相同数据的元组以第二列排序，以此类推。注意：多列排序时，不管正序还是倒序，每个列需要单独设置排序方式。3分组分组部分的语法格式为：group by 分组依据 having17 分组条件。其中，分组依据可为某个属性名，即按哪个属性分组；分组条件为逻辑表达式。15 ascendingsendi16 descendingdisendi17 havinghvi：满足条件查询执行顺序表 1.7 查询子句的执行顺序子句 说明from 从右到左，数据量小的表尽量放在后面where 从右到左，将过滤记录数量大的条件写在WHERE子句的最右边group by 从左往右，建议使用WHERE将不需要的记录过滤掉。having 在检索出所有记录之后才对结果集进行过滤，需要排序等操作，尽量避免使用。select 少用*号，尽量取字段名称。使用“*”时会在内部解析为所有属性名，耗时。order by 从左到右，比较消耗资源。聚合函数聚合函数：即分组函数，对一组数据进行运算并返回单一结果的函数。注意：聚合函数忽略含 null值的记录，即在聚合函数中，含 null 值的记录不参与运算。表 1.8 聚合函数函数名称 说明max 计算属性或表达式的最大，可以作用于数字、字符和日期等数据类型数据。min 计算属性或表达式的最小值，可以作用于数字、字符和日期等数据类型数据。avg 统计属性或表达式的平均值，仅作用于数字类型数据。sum 统计属性或表达式的和值，仅作用于数字类型数据（忽略含null 值的记录）。count 计算表中的记录条数（忽略含null值的记录）。count(*)表示统计所有记录。1.5.2 关联查询关联查询：查询两个或两个以上数据表或视图的查询，也称为连接查询。连接查询通常建立在存在相互关系的父子表之间。笛卡尔积：若集合A的元素数量为 x，集合B的元素数量为y，则集合的A、B的笛卡尔积为x*y，表示为：AB=(x,y)x Ay B此处，A、B各表示一张表，x、y为它们的属性，对两个表进行关联查询时，若没有where子句，得到的总记录数将为x*y。如 图 9.3所示，注：源记录集指查询数据源中的记录集。 , A . a , , B . b 1 , , B . b 2 , , A . a , B . b 1 , , A . a , B . b 2 , A 表 记 录B 表 记 录 关 联 后 的 记 录 图 1.3 表关联前后源记录集的变化（没有限制条件时）关联方式有：等值连接、内连接、外连接、全连接、自连接。关联查询的语法为：select 查询项目 from A 连接方式 join B on(A.a = B.b);其中，连接方式有：inner、left outer、right outer、full，分别表示内连接、外连接、全连接，缺省为内连接。等值连接：一般在有主、外键关联关系的表间建立，并将连接条件设定为有关系的列，使用等号”=”连接相关的表。语法格式为： from A join B on(A.a = B.b); from A,B where A.a = B.b; 内连接：返回源记录集中所有满足条件的记录（这些记录作为数据源供from使用），缺省的连接方式。语法格式为： from A inner join B on(A.a = B.b);外连接：分为左外连接和右外连接。左外连接：以这样的方式建立源记录集：以A 表作为驱动表（参照表），若B中有记录满足指定的条件，则B 中满足条件的记录与 A中对应记录组合成源记录集的记录；若B中所有记录都不满足指定的条件，则新建一条包含B中所有属性且这些属性的值都为空的记录，再与A中对应记录组合成一条源记录集的记录； , A . a , , B . b 1 , , A . a , B . b 1 , A 表 记 录 B 表 记 录 A . a = B . b 1A . a ! = , B . b 1 , B . b 2 , , A . a , , B . b 1 = n u l l , B . b 2 = n u l l 源 记 录 集源 记 录 集源 记 录图 1.4 左外连接示意图右外连接：与左外连接类似，不同的是，右外连接以B表作为参照表。全连接：左外连接和右外连接的源记录集的总和。自连接：关联关系来自于单表中的多个属性的连接。在自连接中，应注意以下问题：1select e.姓名,e.id,d.姓名,d.id from emp e,emp d where e.管理者=d.id;表示查找对象是e.姓名，那他的管理者是谁呢？是d.id这个人。2select e.姓名,e.id,d.姓名,d.id from emp e,emp d where e.id=d.管理者;表示查找对象是e.姓名，这个人是谁？是d.id的管理者。1.5.3 高级查询子查询：为了给上级查询提供数据源或条件数据而进行的查询。父查询：嵌入子查询的查询语句，也称作主查询。嵌套查询嵌套格查询式如：select 查询项目, 子查询1 from 子查询2 where 子查询3 子 查询类 型 单 行子 查询多行子 查询多列子 查询 group by 表达式 having 子查询4 排序;在子查询中常用到in、any 、all 等运算符（后两者须与比较运算符配合使用）。exist关键字：用于在子查询中引用主查询的某些数据。使用方法如下：where exist(子查询 X);注：exist后的括号中的子查询引用主查询中的数据是通过主查询中的表达式别名实现的。若子查询X至少返回一行数据，则exist( ) 返回true ；若子查询X没有返回数据，则exist( )返回false。分页查询rownum，称为伪列，用于标识结果集的行（从 1开始），一般用于子查询中。注意，在select 语句中，应考虑各子句执行的先后顺序，当引用rownum(通过别名 )时应rownum存在后才能进行。分页方法：指定页的记录数范围为(n - 1) * pageSize + 1 n * pageSize其中，n为表示第n页，pageSize表示一页中的最大记录数。如：select * from (select rownum rn, 结果列 from ) where rn between M and N;其中，M=(n - 1) * pageSize + 1， = n * pageSize.关于JDBC 中的分页查询见 46页。Decode 函数语法格式为：decode (expr, search1, result1 , search2, result2 , default)变换为右图所示，即与swich-case-break语句类似：用参数expr 的值去匹配search条件，返回匹配的search 对应的result结果。若没有匹配的search ，则返回 default的值（若没有提供default参数值，返回null）。case 语句case语句也类似swich-case-break，语法格式如下，case 表达式when 表达式1 then result1when 表达式2 then result2when 表达式n then resultnelse resultn+1end排序1按字面顺序排序：order by decode (expr, search1, result1 , search2, result2)顺序即为result1、result2 的先后顺序。2排序函数row_number()，语法如下：decode (expr,search1, result1 ,search2, result2 ,default)row_number() over (partition18 by col1 order by col2)根据col1分组，各分组内部根据col2排序。返回值为各组内部排序后的顺序编号（各组编号均始于1），顺序编号连续且唯一。rank()，语法如下：rank() over(partition by col1 order by col2)根据col1分组，各分组内部根据col2排序。返回值为各组内部排序后的顺序编号（各组编号均始于1），若col2相同，则顺序编号相同，但下一不同的col2对应的顺序编号将跳过相同顺序编号本应占据的编号，即若出现顺序编号相同，则顺序编号会不连续。dense_rank()，语法如下：dense_rank() over(partition by col1 order by col2)根据col1分组，各分组内部根据col2排序。返回值为各组内部排序后的顺序编号（各组编号均始于1），若col2相同，则顺序编号相同，各顺序编号连续。高级分组函数rollup、cube和grouping sets运算符是group by子句的扩展，用于简化和高效地实现统计查询。可以生成与用union all 组合单个分组查询时相同的结果集。语法如下：group by rollup19(a, b,)用于分组统计。以从右至左一次少一列（参数表 a, b,）的方式进行分组统计，直到所有列都去掉也就是对全表分组。对于 n 个参数的 rollup，有 n+1 次分组统计。如 rollup (a, b)，第一次，group by a,b（对每一个 b 分组的指定属性进行统计）；第二次，group by a （对每一个 a 分组的指定属性进行统计）；第三次，对全表(的指定属性进行统计 )。对以上统计的结果存放在结果集中的对应分组末尾（行）。group by cube20(a, b, c)对于cube的每个参数，都可以理解为取值为参与分组和不参与分组两个值的一个维度，所有维度取值组合的集合就是分组后的集合。也可以表述为：对于cube 的每个参数，都有参与和不参与组合两种情况，所有参数参与情况组合的集合就是分组后的集合。对于n个参数的cube ，有2n 次分组。group by grouping sets (A), (B),(null)分别按照A、B、进行分组统计，其中，A、B 、各项均表示a,b形式的表达式。每一部分的分组均是独立的，分组效果均等价于：group by 分组依据。最后一项“(null) ”可写为“()”，表示对指定属性作全统计。集合操作使用集合操作符，可以实现集合的并、交、差。集合操作符包括union、union all、intersect 和minus 。语法如下：select语句 1 union | union all | intersect | minus select语句2;注：select语句1和select语句2的列的个数和数据类型必须匹配。union和union all返回两个结果集的并集。union 返回结果集中不包括重复记录； union all返回结果集中包括重复记录。union操作符对查询结果排序， union all不排序intersect21返回两个结果集的交集。结果集以第一列的数据作升序排列。18 partition p:tin：划分19 rollup：汇总20 cube：立方体minus22返回两个结果集的差集即结果集一减去结果集二的结果。1.6 视图视图(VIEW)：也称为虚表，是一组数据的逻辑表示，其本身不包含任何数据，仅包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。视图的本质是一条SELECT语句，结果集被赋予一个名字视图名字。视图分为：简单视图：SELECT语句基于单表建立，且不包含任何函数运算、表达式或分组函数的视图。复杂视图：SELECT语句基于单表建立，但包含了单行函数、表达式、group by子句（或分组函数）的视图。连接视图：SELECT语句基于多个表建立的视图。视图的作用：1简化复杂查询：如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可；2安全和保密：限制数据访问只能访问到视图对应的SELECT语句中涉及到的列（基表中的）。1.6.1 创建视图语法格式：create or replace view 视图名称(alias 23, alias) as 子查询;视图创建后，可以像操作表一样操作视图，主要是查询操作。子查询对应的表被称作基表。授权创建视图用户必须有create view系统权限，才能创建视图。管理员授予用户创建视图权限的语法格式：grant24 create view to 用户名;简单视图能够执行DML操作，下列情况除外：1. 若基表中定义了非空列，但简单视图对应的select 语句并没有包含此非空列 即此非空列对视图不可见，这时无法对视图执行insert操作；2. 若视图定义中包含了函数、表达式、分组语句、distinct 关键字或rownum伪列，不允许执行DML操作；3. DML操作不能违反基表的约束条件。创建具有 check option 约束的视图语法如下：视图创建语句 with check option;其中，with check option短语表示，视图创建后，通过视图所做的修改，必须在视图的可见范围内：1. 若为insert，新增的记录在视图范围内仍可查看；21 intersectntsekt：相交22 minus mains：减去，求差23 alias eli:s, eljs：别名24 grant r:nt：授予2. 若为update，修改后的结果必须能通过视图查看到；3. 若为delete，只能删除现有视图里能查到的记录。创建具有 read only 约束的视图对简单视图进行DML操作，合法但不安全。若没有必要在视图上执行DML 操作，则在建立视图时应声明为只读视图，以保证视图对应的基表数据不会被非法修改。视图加入read only约束的语法如下：视图创建语句with read only;通过查询 user_views 获取相关信息和视图相关的数据字典有：user_objects、user_views、user_update_columns在数据字典user_objects中查询所有视图名称：select object_name from user_objects where object_type = view;在数据字典user_views 中查询指定视图：select text from user_views where view_name = 指定视图名称;在数据字典user_update_columns中查询视图：select 列名, insertable, updatable, deletable from user_updatable_columns where table_name =指定视图名称;创建复杂视图(多表关联)复杂视图指在子查询中包含了表达式、单行函数或分组函数的视图。复杂视图的列名必须为子查询中表达式或函数定义的别名。复杂视图不允许DML 操作，否则会报错。1.6.2 删除视图语法格式为：drop view 视图名称;视图是存放在数据字典中的独立对象，仅仅是基于表的一个查询定义，对视图的删除操作不会导致基表数据的丢失。1.7 序列序列(sequence)：用于生成唯一数字值的数据库对象，一般用来自动产生表的主键值，是一种高效率获得唯一键值的途径。序列是独立的数据库对象，并不依附于表。序列的值由oracle程序按递增或递减顺序自动生成。一般地，一个序列为一个表提供主键值，也可以为多个表提供主键值。1.7.1 创建序列创建序列的语法格式：create sequence schema.序列名 start with i increment by j maxvalue m | nomaxvalue minvalue n | nominvalue cycle | nocycle cache p | nocache 其中，i为起始值（默认为1）， j为步进值（j0为递增，j0为递减，默认为1）；m为最大值，n为最小值；cycle 25表示在递增至最大值或递减至最小值之后是否继续生成序列号（重新从起始值开始），默认是nocycle；cache指定先预取 p个数据存放在缓存中，以提高序列值的生成效率，默认 p=20.序列中有两个伪列：1. nextval：获取序列的下一个值；2. currval：获取序列的当前值。同一会话中，必须先执行一次nextval后才能使用currval。获取序列号的方法（下一个值）：select 序列名.nextval from dual;在序列的使用过程中，若执行了语句：select 序列名.nextval from dual，则浪费了一个序列值，会导致表的主键值不连续。currval的使用不会导致序列值的递进。1.7.2 删除序列删除序列的语法为：drop sequence 序列名;1.8 索引索引：为了提高查询效率而引入的，允许直接访问数据表中某一数据行的树型结构，是独立于表的对象。索引可以存放在与表不同的表空间（tablespace ）中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的i/o 操作比对表进行的少。索引一旦被建立，就会被oracle系统自动维护。查询语句中不用指定使用哪个索引，是一种提高查询效率的机制。B-tree索引结构，也即B-树索引结构，是一个典型的树结构，始终是平衡的，也就是说从Root 节点到Leaf节点的任何一个路径都是等距离的。其包含的组件主要有：叶子节点（Leaf node ）：包含的条目直接指向表里的数据行。分支节点（Branch 26 node）：包含的条目指向索引里其他的分支节点或者是叶子节点。根节点（Root node）：一个B树索引只有一个根节点，是位于树的最顶端的分支节点。rowid：伪列，唯一标识一条数据记录，指向表的行的地址。25sycle saikl：循环，周期26 branchbr:nt：分支0 B 15 0 0 B 21 0 0 0 B 3 0 L 12 0 0 L 24 0 0 L 30 r 12 9 r 21 9 0 r 32 0 0 r 13 0 0 r 23 9 0 r 34 0 0 r 14 5 0 r 24 8 0 r 3 根结点块( R o o t )分支结点块( B r a n c h )叶子结点块( l e a f )L 1 L 2 L 3 L xB 1 B 2 B 3图 1.5 B-tree 索引结构表的数据行r o w i d k e y L e a f 块结点图 1.6 叶子结点与表的对应关系1.8.1 创建索引创建索引的语法：create unique index 索引名 on table(column, column);其中，unique表示唯一索引，table为表名，column为列名，可以建立单列索引或复合索引。“column, column”可以为某表达式E，当查询语句中出现与E相同的表达式时会自动应用相关的索引。复合索引：基于多个列的索引，也叫多列索引。1.8.2 修改和删除索引若经常在索引列上执行DML 操作，需要定期重建索引，提高索引的空间利用率。重建索引的语法为：alter inde