CSS201.mps

Transcription

1 第 章 SQL 基础 学习目标 瞯掌握 SQL 的概念, 了解其发展历程和特点 瞯掌握 SQL 中的数据定义 数据查询和数据更新功能及基本命令格式 内容框架 SQL 是数据库应用系统开发过程中必然会用到的技术, 所以, 在开发数据库应用之前必须掌 握 SQL 的具体命令和用法 本章将对 SQL 的各种命令进行详细介绍 3.1 SQL 概述 SQL( Structured Query Language, 结构化查询语言 ) 是关系数据库的标准语言, 是一种通用的 功能强大的关系数据库语言 当前几乎所有的关系数据库管理系统都支持 SQL, 许多软件厂商还对 SQL 基本命令集进行了不同程度的修改和扩充 SQL 的产生和发展 SQL 是由 Boyce 和 Chamberlin 于 1974 年提出的,1975 ~1979 年,IBM 公司 San Jose 研究实验室所研制的著名的关系数据库管理系统原型 System R 中实现了这种语言 由于 SQL 功能丰

2 3.1 SQL 概述 45 富 语法简洁, 因而深受计算机业界的欢迎 经过各公司的不断修改 扩充和完善,SQL 最终发展成为关系数据库的标准语言 1986 年 10 月, 美国国家标准学会 ( American National Standards Institute,ANSI) 确定其为美国关系数据库系统的工业标准 1987 年, 国际标准化组织 ( Interna- tional Organization for Standardization,ISO) 也通过了这一标准 SQL 正式成为数据库领域的主流语言之一 自 1986 年公布以来,SQL 随着数据库技术的发展而不断发展 丰富, SQL 标准的进程如表 3.1 所示 表 3.1 SQL 标准的进程标准原文大致页数发布期 SQL / 年 10 月 SQL / 年 SQL / 年 SQL / 年 SQL / 年 SQL 的特点 SQL 之所以能够为用户和业界所普遍接受并成为国际标准, 是因为它是一种综合的 功能极强同时又简单 易学的语言,SQL 集数据查询 ( data query) 数据操纵( data manipulation) 数据定义 (data definition) 数据控制( data control) 功能于一体, 其主要特点如下 (1) 综合统一因为 SQL 集 DQL( 数据查询语言 ) DDL( 数据定义语言 ) DML( 数据操纵语言 ) DCL( 数据控制语言 ) 于一体, 所以 SQL 风格统一, 可以独立地完成数据库生命周期中的全部活动 由于关系模型的数据结构单一, 关系模型中的实体及实体间的联系均用关系表示, 所以 SQL 中的所有修改操作只需一种操作符 (2) 高度非过程化在使用 SQL 时, 存取路径的选择以及 SQL 的操作过程由系统自动完成, 这不但大大降低了用户干预的程度, 减轻了用户的负担, 而且有利于提高数据的独立性 (3) 面向集合的操作方式 SQL 采用集合操作方式, 不仅操作对象 查询结果可以是记录的集合, 而且插入 删除 更新操作的对象也可以是记录的集合 (4) 以同一种语法结构提供两种使用方式 SQL 既是独立的语言, 又是嵌入式语言 作为独立的语言,SQL 能够独立地用于联机交互方式, 用户可以在终端键盘上直接输入 SQL 命令对数据库进行操作 ; 作为嵌入式语言,SQL 能够嵌入高级语言 ( 例如 C Java C ++) 程序中, 供程序员设计程序时使用 在上述两种不同的使用方式下,SQL 语法结构基本上是一致的, 从而提供其使用的灵活性和

3 46 第 3 章 SQL 基础 方便性 (5) 语言简洁, 易学易用 SQL 所实现的功能极强, 但由于其设计巧妙, 语言结构简洁, 完成核心功能只用到 9 个动词 ( 又称命令 ), 如表 3.2 所示 表 3.2 SQL 的动词 功能 SQL 动词 数据查询数据定义数据更新数据控制 SELECT CREATE ALTER DROP INSERT UPDATE DELETE GRANT REVOKE 本章只介绍 SQL 的数据定义 数据查询 数据更新命令, 关于数据控制命令的内容将在第 9 章介绍 由第 2 章可知学生选课系统的关系模式与相应的数据表是一一对应的, 所以, 学生选课系统中的关系模式一经确定, 就可以考虑在数据库系统中建立其关系模式了, 即建立数据表 如果数据表的结构有误, 则应对其加以修改 ; 如果不再需要某数据表, 则应删除之 数据表建好之后, 应向表中输入数据 如果所输入的数据有误, 则应修改之 数据一旦正确输入, 就可以进行查询 统计等操作了 为了提高数据查询速度, 应在数据表的相应字段上建立索引 为了增强数据查询操作的安全性, 还应考虑建立视图机制 3.2 数据定义 SQL 的数据定义包括针对各种数据库对象的定义, 本章只介绍定义表 索引和视图的方法, 基本定义语句 ( 命令 ) 如表 3.3 所示 表 3.3 SQL 的数据定义语句 操作对象 操作方式 定义删除修改 表 CREATE TABLE DROP TABLE ALTER TABLE 索引 CREATE INDEX DROP INDEX 视图 CREATE VIEW DROP VIEW 表的定义 查看 修改与删除表是数据库最基本的操作对象, 是实际存放数据的地方 不同的 DBMS( 数据库管理系统 ) 中定义数据表的方法稍有差别, 在开发实际应用系统时, 应针对所使用的 DBMS 查找相关资料以确定 本书中的所有内容均针对 Oracle 数据库系统 Oracle 数据库中的数据存储在一张二维表中, 通过表中的行和列来组织数据 通常表中的

4 3.2 数据定义 47 一行称为记录, 表中的一列称为字段, 一个表一般具有多个字段 Oracle 数据库中常用的基本数据类型如表 3.4 所示 表 3.4 Oracle 数据库中常用的基本数据类型 数据类型 说明 CHAR(n) VARCHAR2(n) NUMBER(p,s) INT SMALLINT FLOAT(n) DATE TIME 用于存储长度为 n 的定长字符串用于存储长度为 n 的变长字符串用于存储带符号的整型数或浮点型数,p 指有效数字位数,s 指小数位数用于存储长整型数, 也可将其写为 INTEGER 用于存储短整型数用于存储精度至少为 n 位的浮点型数用于存储日期, 包含年 月 日, 其格式为 YYYY -MM -DD 用于存储时间, 包含时 分 秒, 其格式为 HH MM SS 1. 定义表定义表的命令的一般格式如下 : CREATE TABLE < 表名 > ( < 字段名 > < 数据类型 ( 长度 ) >[ < 字段级约束 >] [, < 字段名 > < 数据类型 ( 长度 ) > [ < 字段级约束 >] ] [,CONSTRAINT < 约束名 > < 约束类型 >( 字段 [, 字段 ])] [ [,CONSTRAINT < 约束名 > < 约束类型 >( 字段 [, 字段 ]) ] ] ); 其中 : (1) CREATE 为定义表的关键字,TABLE 为表关键字 (2) 表名的命名规则如下 : 1 表名要尽量短, 且能够说明表的主要特征 ; 2 将主表与从表的名称联系起来, 引用列与被引用列最好能够使用同一个名称 ; 3 与表相关的对象的命名要与表名联系起来 ; 4 避免使用引号 关键字 非字符或非数字的字符, 除非 $ _ # 这 3 种符号 ; 5 尽量不使用特殊字符, 如中欧字符集 亚洲字符集 ( Oracle8i 版本以上可用中文字符来命名表和列, 但即便这样也尽量不要用, 除非存在特殊需求 ); 6 Oracle 系统保留字和 dual 不能用, 开发工具或软件中的保留字不能用 ; 7 表中最多可以有 个字段 ; 8 表名不区分字母大小写 注意 : 所谓主表是指参照完整性中的被引用表, 从表是指参照完整性中引用其他表中的字段值的表 例如, 课程选修 表中的 学号 字段值参照 学生 表中的 学号 字段值, 学生 表即为主表, 课程选修 表即为从表 (3) 约束可分为表级约束和字段级约束两种 如果某个约束只作用于单个字段, 则在此字

5 48 第 3 章 SQL 基础 段定义后面写出字段级约束 ; 如果某个约束作用于多个字段, 则必须在所有字段定义完成之后使 用 CONSTRAINT 子句来定义表级约束 字段级约束可被定义成表级约束, 但表级约束不能被定 义成字段级约束 Oracle 数据库中的约束类型分为主键 ( PRIMARY KEY ) 外键 ( FOREIGN KEY ) 检查 (CHECK) 唯一性 ( UNIQUE) 非空 (NOT NULL) 默认值 ( DEFAULT)6 种, 各种约束类型及其作 用如表 3.5 所示 表 3.5 Oracle 数据库的约束类型及其作用 约束类型 作用 主键约束外键约束唯一性约束检查约束非空约束默认值 保证主键列值的唯一性和非空性用来限定主表和从表中相应字段取值的一致性用来限定字段取值的非重复性, 一般用于非主属性用来限定字段的取值范围用来限定字段值必须是一个确切的值用来设定当向表中插入一条记录却未给指定字段赋值时, 用此字段的默认值填充字段 下面分别介绍表 3.5 中各种约束的定义方法 1 定义 PRIMARY 类型的约束 ( a) 定义 PRIMARY 类型的字段级约束时, 在相应字段后面标明 PRIMARY KEY 即可 例如, 在某个表中要求字段 s_no 单独作主键, 可将 s_no 定义为字段级约束, 其格式为 s_no CHAR(6) PRIMARY KEY ( b) 定义 PRIMARY 类型的表级约束时, 其格式为 CONSTRAINT < 约束名 > PRIMARY KEY( 主键字段列表 ) 字段列表中的各字段之间用符号, 分隔 例如, 在某个表中要求字段 s_no c_no 联合作主键, 由于此时主键约束作用于 2 个字段上, 因而只能将主键约束定义成表级约束, 其格式为 CONSTRAINT sc_pri PRIMARY KEY ( s_no,c_no) 上面的命令中将字段 s_no c_no 定义为表级主键约束, 约束名为 sc_pri 由于一个表的主键约束是唯一的, 所以可以省略主键约束名, 直接将主键约束写成 PRIMARY KEY ( s_no,c_no) 注意 : 一个表的主键约束是唯一的, 这并不表明一个表中的主键列是唯一的 一个表的主键可以由一列构成, 也可以由多列联合作主键 例如, 学生 表的主键是 学号, 课程 表的主键是 课程编号, 但 课程选修 表的主键则是 学号 和 课程编号 两个字段 2 定义 FOREIGN 类型的约束 ( a) 定义 FOREIGN 类型的字段级约束时, 在相应字段后面定义 REFERENCES 子句, 其格式为 REFERENCES < 引用表 >( < 引用字段 >) [ ON DELETE CASCADE DELETE SET NULL DELETE NO ACTION] 其中关键字 ON 指定引用行为, 即当主表中的一条记录被删除时, 外键所关联的从表中的所有

6 3.2 数据定义 49 相关记录的处理方法的含义如表 3.6 所示 表 3.6 外键引用行为的含义 项 DELETE CASCADE DELETE SET NULL DELETE NO ACTION 说明主表中的一条记录被删除时, 从表中的所有相关记录均被删除主表中的一条记录被删除时, 从表中的所有相关记录中的相关字段值被设置为 NULL 主表中的一条记录被删除时, 从表中的所有相关记录不执行任何操作 例如, 某个表要求字段 s_no 与 学生 表 ( student) 中的主键 s_no 关联, 并且当删除 学生 表中某个 s_no 值所在的记录时一并删除该表中的所有相关记录 该表的 s_no 字段属于外键, 可以定义为 FOREIGN 字段级约束, 具体的定义格式如下 : s_no CHAR(6) REFERENCES student( s_no) ON DELETE CASCADE ( b) 定义 FOREIGN 类型的表级约束时, 其格式为 CONSTRAINT < 约束名 >FOREIGN KEY( 字段 ) REFERENCES < 引用表 >( < 引用字段 >) [ ON DELETE CASCADE DELETE SET NULL DELETE NO ACTION] 同样, 可以将上面的例子用表级约束的方式来定义, 其格式为 CONSTRAINT stu_fig FOREIGN KEY ( s_no) REFERENCES student( s_no) ON DELETE CAS- CADE 上面的命令中定义字段 s_no 为表级外键约束, 约束名为 stu_fig 在一般情况下,FOREIGN 类型的约束均定义为字段级约束 注意 : 由于一个表的外键约束不唯一, 所以必须为每个外键约束标明不同的名称 3 定义 CHECK 类型的约束 ( a) 定义 CHECK 类型的字段级约束时, 需要在字段后面标明检查约束 检查约束的定义格式为 CHECK( < 约束表达式 >) 例如, 某个表要求字段 s_no 的取值范围为 ~ 00100, 可以在字段 s_no 上定义 CHECK 类型的字段级约束 字段级检查约束的定义格式如下 : s_no CHAR(6) CHECK( s_no BETWEEN AND ) 例如, 某个表要求字段 s_score 的值必须大于 500, 其字段级检查约束的定义格式如下 : s_score NUMBER(5,2) CHECK( s_score >500) ( b) 定义 CHECK 类型的表级约束时, 其格式为 CONSTRAINT < 约束名 > CHECK < 约束表达式 >) 例如, 某个表要求 s_score 和 s_addf 字段值之和必须大于 600, 由于检查约束作用于 2 个字段上, 因而只能将检查约束定义成表级约束, 其格式为 CONSTRAINT ck_1 CHECK( s_score +s_addf >600) 上面的命令定义表级检查约束, 其约束名为 ck_1 注意 : 由于一个表的检查约束不唯一, 所以必须为每个检查约束标明不同的名称 4 定义 UNIQUE 类型的约束

7 50 第 3 章 SQL 基础 ( a) 定义 UNIQUE 类型的字段级约束时, 在相应字段后面标明 UNIQUE 即可 例如, 某个表要求字段 s_name 的值不能重复, 则将 s_name 定义为字段级约束, 其格式为 s_name CHAR(6) UNIQUE ( b) 定义 UNIQUE 类型的表级约束时, 其格式为 CONSTRAINT < 约束名 > UNIQUE ( < 字段列表 >) 字段列表中的各字段之间用符号, 分隔 例如, 某个表要求字段 s_name s_sex 的值不能重复, 由于唯一性约束作用于 2 个字段上, 因 而只能将唯一性约束定义成表级约束, 其格式为 CONSTRAINT ut UNIQUE( s_name,s_sex) 上面的命令定义表级唯一性约束, 约束名为 ut 注意 : 由于一个表的唯一性约束不唯一, 所以必须为每个唯一性约束标明不同的名称 5 非空约束一般定义成字段级约束 定义字段级非空约束时, 在字段后面直接标明 NOT NULL 即可 例如, 某个表要求字段 s_name 必须给定一个确切的值, 不能为空值, 那么定义格式如下 : s_name CHAR(8) NOT NULL 6 默认值一般定义成字段级约束 定义字段的默认值时在字段后面标明 DEFAULT < 默认 值 > 即可 例如, 某个表要求设置字段 s_score 的默认值为 50.00, 定义格式如下 : s_score NUMBER (5,2) DEFAULT 此时所给出的默认值应与此字段的数据类型一致 下面定义学生选课系统中的 学生 表 (student) 课程 表 (course) 和 课程选修 表 (sc) 例 3.1 在数据库中定义学生选课系统中的 学生 表 ( student), 写出其 SQL 命令, 基本 要求如表 3.7 所示 表 3.7 学生选课系统中 学生 表基本要求 属性名属性代码数据类型长度小数位数约束类型 学号 s_no CHAR 6 主键约束 姓名 s_name CHAR 8 非空约束 性别 s_sex CHAR 2 取值仅限于 男 女 出生日期 s_birthday DATE 入学成绩 s_score NUMBER 5 2 默认值为 附加分 s_addf NUMBER 3 1 班级编号 class_no CHAR 4 CREATE TABLE student ( s_no CHAR(6) PRIMARY KEY, s_name CHAR(8) NOT NULL, s_sex CHAR(2) CHECK(s_sex IN( 男, 女 )),

8 3.2 数据定义 51 s_birthday DATE, s_score NUMBER(5, 2) DEFAULT , s_addf NUMBER(3,1), class_no CHAR(4) ); 本例中所使用的约束都是字段级约束, 这是因为主键约束只作用于 学号 一个字段上, 检查约束只作用于 性别 一个字段上, 默认值只是 入学成绩 单个字段的默认值 也可以将字段级约束定义成表级约束, 相应的命令如下 : CREATE TABLE student ( s_no CHAR(6), s_name CHAR(8) NOT NULL, s_sex CHAR(2), s_birthday DATE, s_score NUMBER(5,2) DEFAULT , s_addf NUMBER(3,1), class_no CHAR(4), PRIMARY KEY( s_no), CONSTRAINT ck_1 CHECK(s_sex IN( 男, 女 )) ); 此命令执行后, 在数据库中创建一个名为 student 的数据表, 其字段信息 约束信息存储于 Oracle 系统表中 例 3.2 在数据库中定义学生选课系统中的 课程 表 (course), 写出其 SQL 命令, 基本要求如表 3.8 所示 表 3.8 学生选课系统中 课程 表基本要求属性名属性代码数据类型长度小数位数约束类型课程编号 c_no CHAR 4 主键约束课程名称 c_name CHAR 20 非空约束学分 c_fen NUMBER 3 1 CREATE TABLE course ( c_no CHAR(4), c_name CHAR(20) NOT NULL, c_fen NUMBER(3,1), PRIMARY KEY(c_no)) ; 同理, 也可以将表级主键约束定义成字段级约束, 相应的命令如下 : CREATE TABLE course ( c_no CHAR(4) PRIMARY KEY, c_name CHAR(20) NOT NULL, c_fen NUMBER(3,1) ); 同样, 此命令执行后, 在数据库中创建一个名为 course 的数据表, 其字段信息 约束信息存储

9 52 第 3 章 SQL 基础 于 Oracle 系统表中 例 3.3 在数据库中定义学生选课系统中的 课程选修 表 ( sc), 写出其 SQL 命令, 基本要求如表 3.9 所示 表 3.9 学生选课系统中 课程选修 表基本要求属性名属性代码数据类型长度小数位数约束类型 课程编号 c_no CHAR 4 学号 s_no CHAR 6 与 学号 字段联合作主键, 与 课程 表的 课程编号 外键关联与 课程编号 字段联合作主键, 与 学生 表的 学号 外键关联, 且级联删除 成绩 score NUMBER 3 1 CREATE TABLE sc ( s_no CHAR(6) REFERENCES student( s_no) ON DELETE CASCADE, c_no CHAR(4) REFERENCES course( c_no), score NUMBER(3,1), PRIMARY KEY( s_no,c_no)) ; 由于主键约束作用于 学号 课程编号 两个字段上, 因此, 主键约束只能定义成表级约束而不能定义成字段级约束 由于两个外键约束作用于单个字段上, 因此外键约束也可以定义成表级约束, 相应的命令如下 : CREATE TABLE sc ( s_no CHAR(6), c_no CHAR(4), score NUMBER(3,1), PRIMARY KEY( s_no,c_no), CONSTRAINT fk_1 FOREIGN KEY( s_no) REFERENCES student( s_no) ON DELETE CASCADE, CONSTRAINT fk_2 FOREIGN KEY( c_no) REFERENCES course( c_no)); 同样, 此命令执行后, 在数据库中创建一个名为 sc 的数据表, 其字段信息 约束信息存储于 Oracle 系统表中 注意 : 在定义表时通常先定义无外键关联的独立的表, 如果某个表存在外键关系, 则必须先定义主表, 再定义从表 在学生选课系统中, 必须先定义 student 表和 course 表, 再定义 sc 表, 因为 sc 表外键关联 student 表和 course 表 上面的例子中定义了学生选课系统中的 学生 表 课程 表和 课程选修 表, 涉及 Oracle 系统表的主键约束 外键约束 检查约束 唯一性约束 非空约束和表的字段级约束 表级约束的定义方法 学生选课系统中的其他表的定义方法与此类似, 请读者自行练习 2. 查看表在学生选课系统的开发过程中, 特别是在开发和调试程序时, 经常需要查看数据表的结构信

10 3.2 数据定义 53 息 在 Oracle 数据库中, 一旦表的定义成功执行, 表的相关信息就被存储在 Oracle 数据库的多 个系统表中, 具体的系统表如表 3.10 所示 表 3.10 表信息所存储的系统表 系统表名 DBA_TABLES DBA_TAB_COLUMNS DBA_CONSTRAINTS DBA_CONS_COLUMNS 存储参数存储表的表空间 存储参数 块空间管理参数 事务处理参数等信息存储表的字段信息存储表的约束信息存储字段的约束信息 使用 DESC 命令可以查看系统表的结构 例 3.4 用 DESC 命令查看系统表 DBA_TAB_COLUMNS 的结构 DESC DBA_TAB_COLUMNS; 此命令的 名称 是否为空 数据类型 OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) DATA_TYPE DATA_TYPE_MOD DATA_TYPE_OWNER VARCHAR2(106) VARCHAR2(3) VARCHAR2(30) DATA_LENGTH NOT NULL NUMBER DATA_PRECISION DATA_SCALE NUMBER NUMBER 由于字段太多, 在此不逐一罗列 从本例可以看出,DBA_TAB_COLUMNS 系统表中存储着表名 ( TABLE_NAME) 字段名( COLUMN_NAME) 数据类型(DATA_TYPE) 等信息 可以用 SELECT 命令从表 3.10 所示的各个系统表中查询所需要的信息,SELECT 命令的具体使用方法将在 3.4 节介绍 例 3.5 查看学生选课系统中 学生 表 ( student) 的字段名 数据类型 字段长度等信息, 写出相应的 SQL 命令 要实现题目要求的功能, 可以利用 DBA_TAB_COLUMNS 系统表查看 student 表 其 SQL 命令如下 : SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = STUDENT ; 此命令的

11 54 第 3 章 SQL 基础 COLUMN_NAME DATA_TYPE DATA_LENGTH S_NO CHAR 6 S_NAME CHAR 8 S_SEX CHAR 2 S_BIRTHDAY DATE 7 S_SCORE NUMBER 22 S_ADDF NUMBER 22 CLASS_NO CHAR 4 注意 : 在 Oracle 数据库中查看系统表时, 所有方案下的 student 表都显示出来, 此时可以在 WHERE 条件中增加 AND OWNER = 用户名 来限定只显示某用户下的 student 表结构 也可以用 DESC 命令直接调用表名以查看表的字段信息, 此命令的一般格式如下 : DESC < 表名 >; 例如, 用 DESC 命令查看 student 表的字段信息, 相应的命令如下 : DESC student; 执行结果为 名称 是否为空 数据类型 S_NO NOT NULL CHAR(6) S_NAME NOT NULL CHAR(8) S_SEX S_BIRTHDAY S_SCORE S_ADDF CLASS_NO CHAR(2) DATE NUMBER(5,2) NUMBER(3,1) CHAR(4) 此执行结果与利用 SELECT 的结果大体相同, 只不过 DBA_TAB_COLUMNS 系统表中的信息比利用 DESC < 表名 > 命令所得到的信息更多 3. 修改表当发现表名 字段名 字段数据类型 长度或约束条件的定义错误时, 需要修改表中的相关内容 修改表的相关操作包括修改表名和表结构两部分 (1) 修改表名在学生选课系统中的表的定义过程中, 如果表名的定义不合适, 可以修改表名 修改表名的命令的一般格式为

12 3.2 数据定义 55 RENAME < 旧表名 > TO < 新表名 >; 其中 RENAME 是表名重命名关键字 例 3.6 将表 student 重命名为 student_sql, 写出其 SQL 命令 RENAME student TO student_sql; 此命令执行后,Oracle 数据库中将不再有 student 表, 而只有 student_sql 表了 (2) 修改表结构在学生选课系统中的表的定义过程中, 如果字段数据类型及约束条件的定义不妥, 则需要修改表结构 修改表结构的命令的一般格式为 ALTER TABLE < 表名 > [ ADD ( < 字段名 > < 数据类型 ( 长度 ) >[ < 字段级约束 >] [, < 字段名 > < 数据类型 ( 长度 ) > [ < 字段级约束 >] ] )] [ DROP [COLUMN < 字段名 >] ( < 字段名 >[, < 字段名 > ]) ] [ MODIFY ( < 字段名 >< 字段数据类型 > [DEFAULT < 值 > NOT NULL NULL] [, < 字段名 >< 字段数据类型 > [DEFAULT < 值 > NOT NULL NULL]]) ]; 其中,ALTER 是修改操作的关键字,ADD 子句向表中添加字段,DROP 子句从表中删除字段, MODIFY 子句修改表中已有字段 下面以学生选课系统中的 学生 表 (student) 为例介绍表中的字段及约束条件的修改方法 例 3.7 向表 student 添加 age INT salary NUMBER(5,2) salary_add NUMBER(3,1) 这 3 个字段, 写出其 SQL 命令 题目要求向表 student 添加字段, 可以使用 ALTER TABLE 命令中的 ADD 子句加以实现 ALTER TABLE student ADD ( age INT,salary NUMBER(5,2),salary_add NUMBER(3,1)) ; 此命令执行后, 使用 DESC 命令查看 student 表的结构, 名称是否为空数据类型 S_NO NOT NULL CHAR(6) S_NAME NOT NULL CHAR(8) S_SEX S_BIRTHDAY S_SCORE S_ADDF CLASS_NO AGE SALARY SALARY_ADD CHAR(2) DATE NUMBER(5,2) NUMBER(3,1) CHAR(4) INT NUMBER(5,2) NUMBER(3,1) 从执行结果可以看出, 表 student 增加了 age salary salary_add 这 3 个字段

13 56 第 3 章 SQL 基础 实现 例 3.8 从表 student 中删除字段 age, 写出其 SQL 命令 题目要求从表 student 中删除一个字段, 可以使用 ALTER TABLE 命令中的 DROP 子句加以 ALTER TABLE student DROP COLUMN age; 此命令执行后, 使用 DESC 命令查看 student 表的结构, 名称是否为空数据类型 S_NO NOT NULL CHAR(6) S_NAME NOT NULL CHAR(8) S_SEX S_BIRTHDAY S_SCORE S_ADDF CLASS_NO SALARY SALARY_ADD CHAR(2) DATE NUMBER(5,2) NUMBER(3,1) CHAR(4) NUMBER(5,2) NUMBER(3,1) 从执行结果中可以看出,student 表中删除了一个字段 age 例 3.9 从表 student 中删除两个字段 salary salary_add, 写出其 SQL 命令 ALTER TABLE student DROP( salary,salary_add); 此命令执行后, 使用 DESC 命令查看 student 表的结构, 名称 是否为空 数据类型 S_NO NOT NULL CHAR(6) S_NAME NOT NULL CHAR(8) S_SEX S_BIRTHDAY S_SCORE S_ADDF CLASS_NO CHAR(2) DATE NUMBER(5,2) NUMBER(3,1) CHAR(4) 从执行结果可以看出, 表 student 中删除了 salary salary_add 两个字段 注意 : 删除一个字段和删除多个字段的命令格式是不同的 在一般情况下, 非空属性字段是不能删除的 ; 如果某字段的所有记录值均为空, 则可删除此字段 例 3.10 将 student 表的 s_name 字段的长度改为 10, 且为 s_addf 字段增加默认值为 50.0 的约束条件, 写出其 SQL 命令

14 3.2 数据定义 57 题目要求修改表 student 中的字段长度, 可以使用 ALTER TABLE 命令中的 MODIFY 子句加 以实现 ALTER TABLE student MODIFY(s_name CHAR(10), s_addf DEFAULT 50.0) ; 此命令执行后, 使用 DESC 命令查看 student 表的结构, 名称 是否为空 数据类型 S_NO NOT NULL CHAR(6) S_NAME NOT NULL CHAR(10) S_SEX S_BIRTHDAY S_SCORE S_ADDF CLASS_NO CHAR(2) DATE NUMBER(5,2) NUMBER(3,1) CHAR(4) 从执行结果可以看出, 修改后的 student 表中的 s_name 字段长度为 10, 如果查看 DBA_CONS _COLUMNS 系统表, 可以看到为 s_addf 字段增加了默认值为 50.0 的约束条件 注意 : 要改变表中字段的数据类型或缩短字段长度, 此字段的所有记录值必须为空 如果此字段存在某些记录值, 则字段长度只能加长, 不能缩短 4. 删除表一旦在数据库中成功定义表之后, 表会一直存储在数据库中 但对于某些表, 有时会不再需要, 此时应删除此表, 以释放其所占用的存储空间 删除表的命令的一般格式如下 : DROP TABLE < 表名 >; 其中 DROP 为删除操作的关键字 假设学生选课系统中不再需要 课程选修 表 (sc), 此时就应该将其删除 例 3.11 删除表 sc, 写出其 SQL 命令 DROP TABLE sc; 此命令执行后, 数据库中将不再有 sc 表了 注意 : 表成功定义后, 删除存在外键关联的表的顺序与定义表的顺序正好相反, 应先删除从表, 再删除主表, 否则会出现 表中的唯一 / 主键被外部关键字引用 错误, 所以在学生选课系统中应先删除 sc 表, 再删除 student course 表 索引的定义 查看与删除在学生选课系统中, 当 student course sc 表中的数据量达到一定的值之后 ( 比如, 达到几万

15 58 第 3 章 SQL 基础 条记录 ), 数据查询的速度会大大降低, 直接影响应用系统的性能 为了解决查询速度问题, 需 要在针对数据表的查询字段上定义索引 索引提供了一种直接 快速访问记录的方式, 可以大大 提高数据查询速度 一个表可以拥有任意多个索引, 可以索引一列, 也可以索引多列, 在一个索引中最多可包含 16 列 索引必须先定义, 然后由数据库管理系统在执行数据查询时自动调用 1. 定义索引 定义索引的命令的一般格式如下 : CREATE [UNIQUE] INDEX < 索引名 > ON < 表名 >( < 字段 >< 次序 >[, < 字段 >< 次序 >] ) ; 其中 CREATE 是用于定义的关键字,INDEX 是索引关键字,UNIQUE 表示索引类型是唯一的, 即索 引列的值不重复, 次序 指索引值的排列顺序,ASC 表示升序,DESC 表示降序, 其默认值为升序 在学生选课系统中, 经常需要按照学生姓名来查询学生信息, 为了提高查询速度, 应该在 student 表的 姓名 ( s_name) 字段上定义索引 例 3.12 在 学生 表的 姓名 (s_name) 字段上定义升序索引, 写出其 SQL 命令 CREATE INDEX student_sname_index ON student( s_name); 此命令执行后, 在数据库中创建了一个按照学生姓名升序排列 名为 student_sname_index 的 索引, 此索引信息存储在 Oracle 系统表中 在学生选课系统中, 经常需要按照课程名称来查询课程信息, 为了提高查询速度, 也应该在 course 表的 课程名称 ( c_name) 字段上定义索引 命令 例 3.13 在 课程 表的 课程名称 ( c_name) 字段上定义唯一性降序索引, 写出其 SQL CREATE UNIQUE INDEX course_cname_index ON course( c_name DESC); 本例利用关键字 UNIQUE 定义唯一性索引, 利用 DESC 定义降序索引 同样, 此命令执行 后, 在数据库中创建一个名为 course_cname_index 的索引, 此索引具有降序唯一性的特点, 其信 息存储在 Oracle 系统表中 注意 : Oracle 自动为表的主键定义升序索引 如果删除表的主键约束, 则定义在此主键上 的索引会自动被删除 2. 查看索引 索引成功定义之后, 索引信息存储于系统表 DBA_INDEXES 中 例 3.14 用 DESC 命令查看系统表 DBA_INDEXES 的结构 DESC DBA_INDEXES; 名称是否为空数据类型 OWNER NOT NULL VARCHAR2(30)

16 3.2 数据定义 59 INDEX_NAME NOT NULL VARCHAR2(30) INDEX_TYPE VARCHAR2(27) TABLE_OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) TABLE_TYPE VARCHAR2(11) 由于字段太多, 在此不逐一罗列 从本例可以看出,DBA_INDEXES 系统表中存储着索引的名称 ( INDEX_NAME) 索引的类型( INDEX_TYPE) 索引所依附的表( TABLE_NAME) 等信息 可以用 SELECT 命令查看 DBA_INDEXES 中所存储的索引信息 例 3.15 查看学生选课系统中 课程 ( course) 表的索引信息, 写出其 SQL 命令 SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME FROM DBA_INDEXES WHERE TABLE_NAME = COURSE ; INDEX_NAME INDEX_TYPE TABLE_NAME SYS_C NORMAL COURSE COURSE_CNAME_INDEX NORMAL COURSE 由执行结果可以看出,course 表中存在两个索引, 一个是例 3.13 中所定义的索引, 二是定义 course 表时 Oracle 系统自动为主键所创建的索引 3. 删除索引当应用系统中的查询条件发生变化时, 查询不再涉及某字段, 因此在此字段上所定义的索引也就不再需要了, 此时应该删除索引, 以释放其所占用的存储空间 删除索引的命令的一般格式如下 : DROP INDEX < 索引名 >; 假设在学生选课系统中, 查询条件不再涉及学生的姓名, 此时, 可以删除基于 姓名 字段所建立的索引 例 3.16 删除 student_sname_index 索引, 写出其 SQL 命令 DROP INDEX student_sname_index; 此命令执行后, 数据库中就不再存在 student_sname_index 索引了 注意 : 主键索引是不能被删除的 如果删除表的主键约束, 主键索引会自动被删除

17 60 第 3 章 SQL 基础 3.3 数据更新 学生选课系统中的数据表定义完成后, 就需要向表中插入数据 如果数据输入有误, 则需要修改数据 如果不再需要某些数据, 则需要删除数据 数据的插入 修改 删除统称为数据更新 插入数据一般地,SQL 提供 3 种插入数据方法 (1) 单行 :INSERT 命令向表中插入一条新记录 这种方法在日常应用中最为常用 (2) 多行 :INSERT 命令从数据库的其他对象中选取多行数据并将其添加至表中 (3) 表间数据复制 : 从一个数据表中选择所需要的数据并将其插入新表中 这种方法经常用于初始装载数据库或者插入由其他计算机系统下载而来的或从某些结点收集来的数据 1. 单行插入命令 INSERT 命令可以向数据库表中插入一行数据 单行 INSERT 命令的格式如下 : INSERT INTO < 表名 >[( < 字段列表 >)] VALUES( < 数值列表 >); 其中,INSERT 是用于插入的关键字,INTO 子句指定接收新数据的表和字段,VALUES 子句指定其数值 字段列表 和 数值列表 指定哪些数据进入哪些字段, 且数值列表应与字段列表一一对应, 列表各项之间用符号, 分隔 如果是向表中的所有字段插入数据, 那么字段列表可以省略 如果是向表中的部分字段插入数据, 那么字段列表就不可省略 如果某字段的值未知, 可以使用关键字 NULL 将其设置为空值 但是, 如果在表定义中将此字段设置为 NOT NULL, 则不能使用空值插入 对于数值型字段, 可以直接输入值 ; 字符型字段值要添加西文单引号 ; 日期型字段值要添加西文单引号, 其输入顺序为 日 - 月 - 年, 如 月 表示 2003 年 12 月 20 日 例 3.17 向 student 表中插入一条新记录, 学号为 , 姓名为孙明, 性别为男, 出生日期为 1980 年 6 月 7 日, 入学成绩为 560 分, 附加分为 40 分, 班级编号为 0102, 写出其 SQL 命令 题目要求向 student 表中插入一条记录, 使用 INSERT 命令如下 : INSERT INTO student( s _ no, s _ name, s _ sex, s _ birthday, s _ score, s _ addf, class_no) VALUES( , 孙明, 男, 7-6 月 -1980,560,40, 0102 ) ; 此命令执行后, 将向 student 表中插入一条记录, 且为所有的字段赋值 由于本例需向 student 表中的所有字段插入值, 所以执行此操作的命令也可写成 : INSERT INTO student VALUES( , 孙明, 男, 7-6 月 -1980,560,40, 0102 ); 例 3.18 向 student 表中插入一条新记录, 学号为 , 姓名为张仪, 性别为男, 班级编号为 0102, 其他字段信息不确定, 写出其 SQL 命令 题目要求向 student 表的部分字段插入数据, 所以表名后面的字段列表不能省略, 命令如下 :

18 3.3 数据更新 61 INSERT INTO student( s_no,s_name,s_sex,class_no) VALUES( , 张仪, 男, 0102 ); 此命令执行后, 将向 student 表中插入一条记录, 且只为 学号 姓名 性别 和 班级编号 字段赋值 注意 : 在向表中插入数据时, 所插入的数据应满足定义表时的约束条件 如果再次向 student 表中插入学号为 的学生记录的话, 系统就会给出错误提示信息, 违反了主键约束 另外, 存在外键关联的主从表之间的数据插入顺序是, 先插入主表数据, 再插入从表数据 例如, 在学生选课系统中, 先在 student 表中插入某学号学生的记录, 然后才能在 sc 表中插入这名学生的课程选修记录 2. 多行插入命令如果某表中已有数据, 需要将这些数据插入另外一个表中, 此时可以使用多行插入命令 多行插入命令的格式如下 : INSERT INTO < 表名 >[( < 字段列表 >)] SELECT 子句 ; 此命令将多行数据插入目标表中 在这种形式的 INSERT 命令中, 未明确地在命令中指定新行的数据, 新行的数据源是 SELECT 子句的查询结果 这是从一个表向另一个表复制多行记录的典型方法 例 3.19 将 student_bak 表中的数据插入 student 表中 student_bak 表的定义与 student 表的定义一样, 此表中装入部分学生的记录, 而且这些学生的信息在 student 表中尚未插入, 写出其 SQL 命令 题目要求将 student_bak 表中的数据插入 student 表中 student_bak 表中可能有多条记录, 采用多行插入命令如下 : INSERT INTO student SELECT 倡 FROM student_bak; 此命令执行后,student_bak 表中的数据就插入 student 表中 如果两个表的记录数据中有重复的学号, 则在数据插入过程中会出现错误, 因为主键的值必须是唯一的 关于 SELECT 命令将在 3.4 节详细介绍 3. 表间数据复制被插入数据库的数据通常是从其他计算机系统中下载, 或是从其他站点搜集, 或是存储于数据文件中的数据 为了将数据装载到表中, 可以设计程序采用循环方式读出数据文件中的每条记录, 然后用单行 INSERT 命令将其插入表中, 但是这样做会很费时 为此, 要想快速地从一个表向一个新表复制数据, 可以使用 CREATE TABLE 命令来定义此表, 并将 SELECT 命令的执行结果复制到新表中 其格式如下 : CREATE TABLE < 表名 >AS SELECT 子句 ; 例 3.20 定义一个新表 student_new, 同时将 student 表中的 学号 姓名 性别 这 3 个字段的值复制到 student_new 表中, 写出其 SQL 命令 题目要求在定义新表的基础上将已有表中的数据插入新表中, 其间会用到表间数据复制命

19 62 第 3 章 SQL 基础 令如下 : CREATE TABLE student_new AS SELECT s_no,s_name,s_sex FROM student; 此命令执行后, 将定义新表 student_new, 同时将 student 表中所指定字段的所有记录复制到 新表 student_new 中 可以用 DESC 命令查看 student_new 表的结构, 用 SELECT 命令查看其数 据 关于 SELECT 命令将在 3.4 节详细介绍 修改数据 如果表中的数据出现错误, 可以利用 UPDATE 命令进行修改 一般地,SQL 提供两种修改 数据的方法 (1) 直接赋值修改 :UPDATE 命令直接将表中的数据修改为确定值 这种方法在日常应用 中最为常用 (2) 嵌套修改 :UPDATE 命令将表中数据修改为从数据库的其他对象中所选取的数据 1. 直接赋值修改 UPDATE 命令可以修改单个表所选行的一个或多个字段的值 UPDATE 命令的格式如下 : UPDATE < 表名 >SET < 字段名 >=< 表达式 > [, < 字段名 >=< 表达式 > ] [ WHERE < 条件 >] ; 其中,UPDATE 是用于修改的关键字, 表名是被修改的目标表,WHERE 子句指定被修改表中的 记录,SET 子句指定所修改的字段并对其赋予新值, 赋值表达式之间用符号, 分隔 例 3.21 将 student 表中王蕾同学的学号改为 , 将班级编号改为 0102, 写出其 SQL 命令 命令 题目要求修改指定学生的数据, 使用 UPDATE 命令如下 : UPDATE student SET s_no =010204, class_no =0102 WHERE s_name = 王蕾 ; 此命令执行后, 王蕾同学的学号改为 , 其班级编号改为 嵌套修改 与 INSERT 命令一样,UPDATE 命令也可以使用 SELECT 子句的查询结果进行修改 例 3.22 将 student 表中王蕾同学的班级编号改为马力同学的班级编号, 写出其 SQL 题目要求将 student 表中王蕾同学的班级编号改为马力同学的班级编号, 应首先查询马力同 学的班级编号, 再将王蕾同学的班级编号修改为此值 需要用到嵌套查询, 命令如下 : UPDATE student SET class_no = (SELECT class_no FROM student WHERE s_name = 马力 ) WHERE s_name = 王蕾 ;

20 3.3 数据更新 63 此命令执行后, 王蕾同学的班级编号修改为马力同学的班级编号 0202 关于 SELECT 命令将在 3.4 节详细介绍 注意 : 在修改表中的数据时, 修改后的数据应满足定义表时设定的约束条件, 否则系统就会给出错误提示信息 例如, 如果将某学生的姓名修改为空值, 就违反了表定义时对于 姓名 字段的非空约束 删除数据如果不再需要学生选课系统中的某些数据, 此时应删除此数据, 以释放其所占用的存储空间 一般地,SQL 提供两种删除数据的方法 (1) 删除所选行数据 :DELETE 命令可以从数据表中删除所选行的数据 这种方法在日常应用中最为常用 (2) 整表数据删除 :TRUNCATE 命令可以删除整个数据表中的数据 整表数据删除操作只删除数据, 表定义仍然存在 1. 删除所选行数据 DELETE 命令可以从数据表中删除所选行的数据 DELETE 命令的格式如下 : DELETE FROM < 表名 >[WHERE < 条件 >]; 其中,DELETE 是用于删除的关键字,FROM 子句指定目标表,WHERE 子句指定被删除的行 如果未指定条件, 则将删除表中的所有数据 例 3.23 将 student 表中姓名为王蕾的学生的记录删除, 写出其 SQL 命令 DELETE FROM student WHERE s_name = 王蕾 ; 此命令执行后, 王蕾同学的记录将从 student 表中删除 注意 : 在删除表中的数据时, 应满足定义表时设定的约束条件, 否则系统会给出错误提示信息 例如, 如果 sc 表中仍存在某门课程的选修信息, 此时如果想在 课程 表中删除这一门课程就会出现错误, 因为外键关联的表的数据删除顺序是先删除从表中的数据, 再删除主表中的数据 2. 整表数据删除使用 DELETE 命令删除一个大型表中的所有记录需要很长的时间, 因为需要把这些数据存储在系统回滚段中, 以备数据恢复时使用 Oracle 数据库提供快速地删除一个表中全部记录的命令 TRUNCATE 这个命令所做的修改不能回滚, 对于已经删除的表数据不能恢复, 即所做的删除是永久删除 其格式为 TRUNCATE TABLE < 表名 >; 其中,TRUNCATE 是用于永久删除数据的关键字 假设要删除例 3.20 中所定义的表 student_new 中的所有数据, 而且不再恢复, 需要利用整表数据删除的方法 例 3.24 永久删除 student_new 表中的全部记录, 写出其 SQL 命令 TRUNCATE TABLE student_new;

21 64 第 3 章 SQL 基础 此命令执行后,student_new 表中的记录为空, 但 student_new 表仍然存在 注意 : 在对数据库表中的数据执行操作后使用 COMMIT 命令, 此命令将对数据库所做的修 改都变成永久性的 如果想撤销对数据库所做的修改, 则使用 ROLLBACK 命令 3.4 数据查询 当学生选课系统中的数据表在数据库中已建立并向其输入数据之后, 接下来就要考虑如何查询数据表中的数据了 查询数据的方法主要涉及 SQL 中的 SELECT 命令 SELECT 命令可以从数据库中查询数据, 并将查询结果进行排序 分组 统计等 SELECT 命令的一般格式如下 : SELECT [ALL DISTINCT] < 显示列表项 > 倡 FROM < 数据来源项 > [ WHERE < 条件表达式 >] [ GROUP BY < 分组选项 >[HAVING < 组条件表达式 >]] [ ORDER BY < 排序选项 >[ASC DESC] ]; 其中 : (1) SELECT 是用于查询的关键字 (2) ALL DISTINCT 表示 ALL 和 DISTINCT 可以任选其一 ALL 表示筛选出数据库表中满足条件的所有记录, 一般情况下省略不写 DISTINCT 表示输出结果中无重复记录 (3) 显示列表项 指定查询结果中所显示的项, 既可以是数据库表中的字段 字段表达式, 也可以是 SQL 常量, 各项之间用逗号分隔 字段表达式可以是 SQL 函数表达式, 也可以是 SQL 操作符所连接的表达式 如果 显示列表项 包含表中的所有字段, 可以用符号 倡 代替 (4) 数据来源项 指定显示列表中显示项的来源, 可以是数据库中的一个或多个表 视图, 各项之间用逗号分隔 (5) WHERE < 条件表达式 > 指定查询条件 查询条件中会涉及 SQL 函数和 SQL 操作符 (6) GROUP BY < 分组选项 > 表示在查询时, 可以按照某个或某些字段分组汇总, 各分组选项之间用逗号分隔 HAVING < 组条件表达式 > 表示在分组汇总时, 可以根据组条件表达式筛选出满足条件的组记录 (7) ORDER BY < 排序选项 > 表示在显示结果时, 可以按照指定字段进行排序, 各选项之间用逗号分隔 ASC 表示升序,DESC 表示降序, 系统默认为升序 SELECT 命令的含义是, 根据 WHERE 子句的条件表达式, 从 FROM 子句所指定的表或视图中查找满足条件的记录, 再将显示列表项中的显示项的值列举出来 在这种固定模式中, 可以不要 WHERE 子句, 但是必须有关键字 SELECT 和 FROM 注意 : 将 SELECT 命令与第 2 章关系代数中所介绍的选择 投影 连接操作做对比学习, 可以加快对 SELECT 命令的理解与掌握

22 3.4 数据查询 单表查询单表查询是指所有的显示列表项来源于同一个表, 此时,SELECT 命令中的 FROM 子句只涉及一个表 在学生选课系统中, 学生 表 ( student) 中的字段数据类型较丰富, 下面以 学生 表为例介绍单表查询方法 例 3.25 查询 student 表中所有学生的全部信息, 写出其 SQL 命令 题目要求查询所有学生的全部信息, 这一查询所涉及的表很容易找到, 就是 student 表 查询所有学生的全部信息, 意味着不带任何查询条件, 命令如下 : SELECT s_ no, s _ name, s _ sex, s _ birthday, s _ score, s _ addf, class _ no FROM student; S_NO S_NAME S_ S_BIRTHDAY S_SCORE S_ADDF CLAS 赵明 男 月 赵以 男 24-8 月 马水 男 06-3 月 杨仪 女 24-4 月 王蕾 女 月 牛可 男 06-6 月 马力 女 07-7 月 查询所有学生的全部信息, 意味着显示列表项中包含 student 表的所有字段, 可以逐一写出 字段名, 也可以用 倡 代替, 命令可以写成 : SELECT 倡 FROM student; 注意 : 命令执行结果中的表头就是显示项字段名的字符, 表头的显示长度是根据字段数据 长度来确定的 例如, 性别 的表头本来应该为 S_SEX, 但是由于 性别 字段的长度为 2 位, 所 以 性别 的表头仅显示 S_ ; 班级编号 的表头本来应该为 CLASS_NO, 但是由于 班级编号 字段的长度只有 4 位, 所以 班级编号 的表头仅显示 CLAS 另外, 如果使用 SELECT 命令未查找到满足条件的数据, 结果将显示信息 未选定行 例 3.26 查询 student 表中所有学生的姓名 出生日期 入学成绩信息, 写出其 SQL 命令, 注意将其与例 2.7 进行比较 题目要求查询 student 表中所有学生的姓名 出生日期 入学成绩信息, 姓名 出生日 期 入学成绩 字段均来源于 student 表, 查询数据库表的部分字段信息时, 显示列表项 中必 须逐一写出字段名, 命令如下 : SELECT s_name,s_birthday,s_score FROM student;

23 66 第 3 章 SQL 基础 S_NAME S_BIRTHDAY S_SCORE 赵明 月 赵以 24-8 月 马水 06-3 月 杨仪 24-4 月 王蕾 月 牛可 06-6 月 马力 07-7 月 例 3.27 查询 student 表中学生所属班级的信息, 写出其 SQL 命令 题目的要求很简单, 班级信息来源于 student 表, 命令如下 : SELECT class_no FROM student; 执行结果如左下表所示 CLAS CLAS 从执行结果可以看出, 输出结果中存在重复的字段值, 这是因为一个班级中可以有多名学生, 一个班级中含有多少名学生, 班级编号在查询结果中就出现几次 如果要使得结果中无重复记录, 可将命令修改为 SELECT DISTINCT class_no FROM student; 执行结果如右上表所示 对比上述两个命令的执行结果, DISTINCT 确保查询结果中无重复记录 例 3.28 查询 student 表中入学成绩大于 520 分的学生的信息, 写出其 SQL 命令, 注意将其与例 2.5 进行比较 题目要求查询入学成绩大于 520 分的学生的信息, 需要设定一个查询条件 执行带条件的查询时须用 WHERE 子句, 命令如下 : SELECT 倡 FROM student WHERE s_score >520;

24 3.4 数据查询 67 S_NO S_NAME S_ S_BIRTHDAY S_SCORE S_ADDF CLAS 赵明 男 月 杨仪 女 24-4 月 王蕾 女 月 牛可 男 06-6 月 例 3.29 查询 student 表中入学成绩大于 520 分的女生的姓名 性别 出生日期和入学成 绩, 写出其 SQL 命令 题目要求查询入学成绩大于 520 分的女生的相关字段信息, 需要设定两个查询条件 执行 组合条件查询须用 WHERE 子句, 且由于查询条件是 既为女生且入学成绩大于 520 分, 所以 WHERE 子句中有逻辑运算操作符 AND, 命令如下 : SELECT s_name,s_sex,s_birthday,s_score FROM student WHERE s_score > 520 AND s_sex = 女 ; S_NAME S_ S_BIRTHDAY S_SCORE 杨仪 女 24-4 月 王蕾 女 月 例 3.30 查询 student 表中学号前 4 位为 0201 的女生的全部信息, 写出其 SQL 命令 题目要求查询 student 表中学号前 4 位为 0201 的女生的全部信息, 这就需要得到学号的前 4 位信息, 要用到 SQL 函数 利用函数 SUBSTR(s_no,1,4) 可以得到学号的前 4 位, 命令如下 : SELECT 倡 FROM student WHERE SUBSTR(s_no,1,4) = 0201 AND s_sex = 女 ; S_NO S_NAME S_ S_BIRTHDAY S_SCORE S_ADDF CLAS 杨仪女 24-4 月 王蕾女 月 注意 : 关于 SQL 函数的内容请参见附录 D 例 3.31 查询 student 表中所有学生的学号 姓名 出生日期信息, 且将出生日期以 yyyy / mm /dd 格式输出, 写出其 SQL 命令 题目要求改变出生日期的输出格式, 这就要用到 SQL 函数 利用函数 TO_CHAR( s_birth- day, yyyy /mm /dd ) 可以控制出生日期的输出格式, 命令如下 : SELECT s_no,s_name,to_char( s_birthday, yyyy /mm /dd ) FROM student;

25 68 第 3 章 SQL 基础 S_NO S_NAME TO_CHAR(S_ 赵明 1980 /11 / 赵以 1978 /08 / 马水 1979 /03 / 杨仪 1980 /04 / 王蕾 1980 /11 / 牛可 1981 /06 / 马力 1981 /07 /07 从上面两例可以看出,SQL 函数既可以用在显示列表项中, 也可以用在查询条件中 例 3.32 查询 student 表中所有学生的学号 姓名 入学总分信息, 其中入学总分为入学 成绩和附加分之和, 写出其 SQL 命令 题目要求查询学生的学号 姓名 入学总分, 利用 s_score +s_addf 可以得到学生的入学总分, 命令如下 : SELECT s_no,s_name,s_score +s_addf FROM student; S_NO S_NAME S_SCORE +S_ADDF 赵明 赵以 马水 杨仪 王蕾 牛可 马力 530 注意 : 关于 SQL 操作符的内容请参见附录 D 例 3.33 查询 student 表中入学总分大于 550 分的学生的学号 姓名 入学总分信息, 写 出其 SQL 命令 SELECT s_no,s_name,s_score +s_addf FROM student WHERE ( s_score +s_addf) >550; S_NO S_NAME S_SCORE +S_ADDF 赵明 杨仪 王蕾 牛可 630 从上面两例可以看出,SQL 操作符既可以用在显示列表项中, 也可以用在查询条件中 例 3.34 查询 student 表中入学总分在 520 ~550 分之间的学生的学号 姓名 入学总分

26 3.4 数据查询 69 信息, 写出其 SQL 命令 题目要求查询入学总分在 520 ~550 分之间的学生信息, 可以利用谓词 BETWEEN AND 定 义一个集合作为查询条件, 命令如下 : SELECT s_no,s_name,s_score +s_addf FROM student WHERE ( s_score +s_addf) BETWEEN 520 AND 550; S_NO S_NAME S_SCORE +S_ADDF 赵以 马水 马力 530 注意 : 关于谓词的内容请参见附录 D 例 3.35 查询 student 表中所有赵姓学生的学号 姓名 入学总分信息, 写出其 SQL 命令 题目要求查询赵姓学生的学号 姓名 入学总分信息, 可以利用谓词 LIKE 定义一个集合作 为查询条件 s_name LIKE 赵 % 表示学生 姓名 字段 s_name 中只要第 1 个字为 赵, 那么 这个表达式就成立, 即此表达式可以找出姓赵的所有学生, 命令如下 : SELECT s_no,s_name,s_score +s_addf FROM student WHERE s_name LIKE 赵 % ; S_NO S_NAME S_SCORE +S_ADDF 赵明 赵以 540 例 3.36 查询 student 表中 班级编号 字段值为 0101 和 0201 的学生的全部信息, 写 出其 SQL 命令 题目要求查询 班级编号 字段值为 0101 和 0201 的学生的全部信息, 可以利用谓词 IN 定义一个集合作为查询条件, 命令如下 : SELECT 倡 FROM student WHERE class_no IN( 0101, 0201 ); S_NO S_NAME S_ S_BIRTHDAY S_SCORE S_ADDF CLAS 赵明 男 月 马水 男 06-3 月 杨仪 女 24-4 月 王蕾 女 月 例 3.37 查询 student 表中所有学生的全部信息, 并将显示结果按照学生入学成绩的降 序排列, 写出其 SQL 命令

27 70 第 3 章 SQL 基础 题目要求将显示结果按照学生入学成绩的降序排列, 利用 ORDER BY 子句可以对查询结果 进行排序, 命令如下 : SELECT 倡 FROM student ORDER BY s_score DESC; S_NO S_NAME S_ S_BIRTHDAY S_SCORE S_ADDF CLAS 牛可男 06-6 月 赵明男 月 王蕾女 月 杨仪女 24-4 月 马水男 06-3 月 马力女 07-7 月 赵以男 24-8 月 从本例可以看出, 利用 ORDER BY 子句可以对查询结果进行排序, 使结果的显示遵循一定 的规律, 便于查找数据 例 3.38 查询 student 表中所有学生的全部信息, 并将显示结果按照入学成绩降序 班级 编号降序排列, 写出其 SQL 命令 如下 : 题目要求将显示结果按照入学成绩降序 班级编号降序排列, 需要用到两个排序条件, 命令 SELECT 倡 FROM student ORDER BY s_score DESC,class_no DESC; S_NO S_NAME S_ S_BIRTHDAY S_SCORE S_ADDF CLAS 牛可男 06-6 月 王蕾女 月 赵明男 月 杨仪女 24-4 月 马水男 06-3 月 马力女 07-7 月 赵以男 24-8 月 从本例可以看出, 对多个字段进行排序时, 依次按照指定字段的指定排序方式进行排序 本 例首先按第 1 个指定字段 入学成绩 的降序排序, 入学成绩相同的则按第 2 个指定字段 班级 编号 的降序排序 例 3.39 按 班级编号 分组统计 student 表中各班的学生人数和入学总分的平均分, 写 出其 SQL 命令 题目要求按班级分别统计学生信息, 一个班级为一组, 为分组查询, 可以利用 GROUP BY 子 句对查询结果进行分组, 命令如下 : SELECT class_no,count( s_no),avg( s_score +s_addf) FROM student GROUP

28 3.4 数据查询 71 BY class_no; CLAS COUNT(S_NO) AVG(S_SCORE +S_ADDF) 从本例可以看出, 利用 GROUP BY 子句可对查询结果进行分组统计 从命令执行结果可以 看出, 结果是按照 班级编号 字段值进行分组的, 每个分组利用 COUNT( ) 函数对各班的学号计 数 ( 统计本班学生人数 ), 利用 AVG( ) 函数求各班学生的入学总分平均分 COUNT( ) 函数和 AVG() 函数均为聚集函数 注意 : (1) 关于聚集函数的内容请参见附录 D (2) 分组查询的显示列表项中只能出现分组字段和利用聚集函数所得到的统计结果, 如果 上例中的 SQL 命令如下 : SELECT class_no,s_no, COUNT( s_no) FROM student GROUP BY class_no; 则会出现错误, 提示非分组信息即 s_no 不是 GROUP BY 表达式 例 3.40 按 班级编号 分组统计 student 表中各班入学总分平均分高于 550 分的班级学 生人数, 写出其 SQL 命令 题目要求按班级进行统计, 为分组查询, 且要求只输出各班入学总分平均分高于 550 分的分 组, 所以应利用 HAVING 子句指定分组查询条件, 命令如下 : SELECT class_no,count( s_no) FROM student GROUP BY class_no HAVING AVG ( s_score +s_addf) >550; CLAS COUNT(S_NO) AVG(S_SCORE +S_ADDF) 从本例可以看出, 利用 HAVING 子句可以指定分组查询条件, 此时, 只有满足条件的分组记 录才会显示出来 例 3.41 查询 student 表中的 s_no 和 s_name 字段, 要求输出结果的表头为学号 姓名, 写 出其 SQL 命令 题目要求输出结果的表头为学生学号 姓名, 利用字段名后面加空格再加别名的方式可以改 变输出表头的显示方式, 即为指定字段定义别名, 命令如下 : SELECT s_no 学号,s_name 姓名 FROM student;

29 72 第 3 章 SQL 基础 学号 姓名 赵明 赵以 马水 杨仪 王蕾 牛可 马力 从本例可以看出, 利用字段名后面加空格再加别名的方式可以改变输出表头的显示方式, 即 为指定字段定义别名 同样, 可以利用字段名后面加 AS 或双引号的方式为指定字段定义别 名, 命令如下 : SELECT s_no AS 学号,s_name AS 姓名 FROM student; 或 SELECT s_no " 学号 ",s_name " 姓名 "FROM student; 例 3.42 查询 student 表中的 s_no 和 s_name 字段, 要求输出结果的表头为学号 姓名, 而 且在学号和姓名之间添加空格和 学生的 3 个汉字, 写出其 SQL 命令 题目特别要求在学号和姓名之间添加空格和 学生的 3 个汉字, 这是字符串常量, 可以直接 将其写在显示列表项中, 命令如下 : SELECT s_no 学号,, 学生的,s_name 姓名 FROM student; 学号 学生的 姓名 学生的 赵明 学生的 赵以 学生的 马水 学生的 杨仪 学生的 王蕾 学生的 牛可 学生的 马力 从本例可以看出, 在显示列表项中可以含有空格或字符串等常量, 这些常量将直接输出到结 果中 例 3.43 查询选修成绩为 NULL 的学生学号 课程编号 成绩, 写出其 SQL 命令 所谓成 绩为 NULL, 即选修某门课程但尚未参加考试, 或考试未能通过而没有得到此门课程的成绩 题目要求查询选修成绩为 NULL 的学生相关信息, 字段值是否为空使用 IS NULL 来判断, 命令如下 : SELECT s_no,c_no,score FROM sc WHERE score IS NULL;

30 3.4 数据查询 73 S_NO C_NO SCORE 多表查询相对于单表查询, 多表查询是指 SELECT 命令中的显示列表项来源于多个数据表 在日常的查询中, 单表查询是很少的, 多数查询涉及多个表的信息 例如, 在学生选课系统中要查询某学生的成绩, 结果中应至少包含学生姓名和成绩, 但学生姓名存储于 学生 表 成绩存储于 课程选修 表, 此查询将涉及两个表 多表查询 SELECT 命令中的 FROM 子句包含多个表, 表间用符号, 分隔, 而且在显示列表项中, 如果某个字段名在多个表中重复出现, 此字段前面必须加 表名. 作为前缀 进行多表查询时,WHERE 子句中必须带有表间连接条件, 最常用的是自然连接 在单表查询中应用的所有 SQL 函数 运算操作等均能用于多表查询 SELECT 中最难的是确定数据来源, 也就是通常所说的查询中涉及的多个表 下面介绍确定全部表的基本步骤 第一步, 将显示列表项中所涉及的字段罗列出来 如果显示列表项是字段表达式或函数表达式, 则将此表达式所涉及的字段也罗列出来 根据列举的所有字段确定显示列表项所涉及的所有表 ; 第二步, 将 WHERE 或 HAVING 子句所涉及的字段罗列出来, 如果设定条件是字段表达式或函数表达式, 则将此表达式中涉及的字段也罗列出来, 根据列举的所有字段确定条件所涉及的所有表 ; 第三步, 查看前两步确定的表是否能通过某些字段建立自然连接, 通常是通过表间的外键关联 如果可以, 则确定了 SELECT 语句中的数据来源 ; 如果不可以, 则应新增将前两步所确定的表连接起来的中间表, 将这些表加入数据来源中 经过上述 3 个步骤, 一个 SELECT 语句所涉及的数据来源就确定下来了 下面以学生选课系统中的 学生 表 课程 表和 课程选修 表为例介绍多表查询方法 例 3.44 查询选修课程的学生学号 姓名 课程编号 课程名称 成绩, 写出其 SQL 命令 题目要求查询选修课程的学生学号 姓名 课程编号 课程名称 成绩, 由于 学号 姓名 字段在 student 表中, 课程编号 课程名称 字段在 course 表中, 成绩 字段在 sc 表中, 属于多表查询, 而且 student 表通过 s_no 与 sc 表连接,course 表通过 c_no 与 sc 表连接, 这 3 个表无需其他表就已经连接起来了, 所以多表查询中的 FROM 子句会涉及 student 表 course 表 sc 表 由于此查询涉及 student 表 course 表 sc 表 3 个表, 而且 s_no 字段在 student 和 sc 两个表中均存在, 所以在显示列表项的字段名 s_no 前面必须加表名 同理,c_no 字段在 course 和 sc 两个表中均存在, 所以在显示列表项的字段名 c_no 前面必须加表名

31 74 第 3 章 SQL 基础 由于是多表查询, 为了保证查询结果的正确性, 需要添加表间自然连接条件, 相应的命令如下 : SELECT student.s _ no, s _ name, course.c _ no, c _ name, score FROM student, course,sc WHERE student.s_no =sc.s_no AND course.c_no =sc.c_no; S_NO S_NAME C_NO C_NAME SCORE 赵明 0001 C 语言 赵明 0002 Java 语言 赵明 0003 C ++ 语言 赵明 0004 VB 语言 马水 0001 C 语言 赵以 0002 Java 语言 赵以 0001 C 语言 马力 0001 C 语言 马力 0002 Java 语言 本例的执行过程为 : 首先进行 student 表 course 表 sc 表的自然连接运算, 连接条件即为 SE- LECT 语句的 WHERE 子句中的条件, 然后进行投影运算, 仅保留显示列表项中所需要的字段 以写成 : 由于 student 表和 sc 表中都有 s_no 字段,course 表和 sc 表中都有 c_no 字段, 所以命令也可 SELECT sc.s_no,s_name,sc.c_no,c_name,score FROM student,course,sc WHERE student.s_no =sc.s_no AND course.c_no =sc.c_no; 例 3.45 查询选修课程的学生姓名 课程名称, 写出其 SQL 命令 题目要求查询选修课程的学生姓名 课程名称, 由于学生 姓名 字段在 student 表中, 课程 名称 字段在 course 表中, 属于多表查询, 但 student 表与 course 表之间不存在直接的关联, 必须 通过 sc 表实现, 因此 sc 表将作为连接表, 所以多表查询的 FROM 子句会涉及 student 表 course 表 sc 表这 3 个表, 命令如下 : SELECT s_name, c_name FROM student,course,sc WHERE student.s_no =sc.s_no AND course.c_no =sc.c_no; S_NAME 赵明赵明赵明赵明马水赵以 C_NAME C 语言 Java 语言 C ++ 语言 VB 语言 C 语言 Java 语言

32 3.4 数据查询 75 赵以 C 语言 马力 C 语言 马力 Java 语言 例 3.46 查询选修课程成绩及格的学生学号 姓名 课程编号 课程名称 成绩, 写出其 SQL 命令 题目要求查询选修课程成绩及格的学生信息, 本例在例 3.44 的基础上增设成绩及格这一条 件, 所以应在 WHERE 子句中添加条件 score >=60, 命令如下 : SELECT student.s _ no, s _ name, course.c _ no, c _ name, score FROM student, course,sc WHERE student.s_no =sc.s_no AND course.c_no =sc.c_no AND score >=60; S_NO S_NAME C_NO C_NAME SCORE 赵明 0001 C 语言 赵明 0003 C ++ 语言 马水 0001 C 语言 赵以 0002 Java 语言 90 本例的执行过程为 : 首先进行 student 表 course 表 sc 表的自然连接运算, 连接条件即为 SE- LECT 语句的 WHERE 子句的前 2 个条件 ; 然后进行选择运算, 选择成绩及格的学生记录 ; 接着再 进行投影运算, 仅保留显示列表项中所需要的字段 例 3.47 查询选修 Java 语言的学生的学号 姓名 课程编号 课程名称 成绩, 写出其 SQL 命令, 注意与例 2.13 进行比较 题目要求查询选修 Java 语言的学生信息, 本例在例 3.44 的基础上增设课程名称为 Java 语 言这一条件, 所以应在 WHERE 子句中添加条件 c_name = Java 语言, 命令如下 : SELECT student.s _ no, s _ name, course.c _ no, c _ name, score FROM student, course,sc WHERE student.s_no =sc.s_no AND course.c_no =sc.c_no AND c_name = Java 语言 ; S_NO S_NAME C_NO C_NAME SCORE 赵明 0002 Java 语言 赵以 0002 Java 语言 马力 0002 Java 语言 例 3.48 查询所有学生选修课程的情况, 要求包含未选修任何课程的学生的信息, 显示 结果中包含学生学号 姓名 课程编号 成绩, 写出其 SQL 命令 题目要求查询所有学生选修课程的情况, 特别要求包含未选修任何课程的学生的信息, 这和

33 76 第 3 章 SQL 基础 例 3.44 的查询要求有所不同 首先观察例 3.44 的查询结果 S_NO S_NAME C_NO C_NAME SCORE 赵明 0001 C 语言 赵明 0002 Java 语言 赵明 0003 C ++ 语言 赵明 0004 VB 语言 马水 0001 C 语言 赵以 0002 Java 语言 赵以 0001 C 语言 马力 0001 C 语言 马力 0002 Java 语言 例 3.44 中的连接均为自然连接, 只有满足条件的记录才能在查询结果中出现, 而未选修任 何课程的学生在 sc 表中并无相关记录, 所以作自然连接时所得结果中就没有此学生的记录 要 想查询所有学生选修课程的记录, 就要用到外连接 所谓外连接, 是指在带有外连接一侧的表中添加一个万能行, 此行所有字段的值均为 NULL, 当此表中不存在满足条件的记录时, 利用这个万能行可以与其他表进行连接 在 Oracle 数据库中, 外连接用符号 + 表示, 外连接可分为左连接和右连接 + 位于连 接条件表达式的等号左边的为左连接, + 位于连接条件表达式的等号右边的为右连接 本例要求查询所有学生的选修信息, 特别包含未选修任何课程的学生的信息, 所以 sc 表中 需要添加一个所有字段的值均为 NULL 的万能行, 应在 sc 表一侧增设符号 +, 命令如下 : SELECT student.s_no,s_name,course.c_no,score FROM student,sc WHERE student.s_no =sc.s_no( +); S_NO S_NAME C_NO SCORE 赵明 赵明 赵明 赵明 赵以 赵以 马水 杨仪 王蕾 牛可 马力 马力 0002 将本例的执行结果与例 3.44 的执行结果进行比较之后可以看出, 进行表间的外连接时未选 修课程的学生数据也查询出来了 例如杨仪 王蕾和牛可同学均未选修课程, 在不建立外连接时

34 3.4 数据查询 77 就不能查询出来 嵌套查询存在这样的实际查询, 它以某一查询的执行结果作为其他查询的条件 例如, 在学生选课系统中查询与王蕾在同一班级学习的学生姓名, 执行此查询应首先查询王蕾所在班级的班级编号, 在此基础上再查询此班级编号对应的学生姓名, 这就需要使用嵌套查询 在 SELECT 查询语句中嵌入 SELECT 查询语句, 这种查询称为嵌套查询 被嵌入的 SELECT 查询语句称为子查询, 包含子查询的查询称为父查询 在嵌套查询中, 要对子查询添加括号, 子查询的格式与 SELECT 语句格式稍有不同, 子查询和 SELECT 语句之间的几点区别如下 (1) 子查询最为常见的用法是必须生成单字段数据作为其查询结果, 即必须是一个确定的项 如果查询结果是一个集合, 则需要使用附录 D 中介绍的谓词操作符 (2) ORDER BY 子句不能用于子查询, 子查询结果只是在主查询内部使用, 对用户而言是不可见的, 所以对其所做的任何排序操作都是没有意义的 嵌套查询常用的求解方法是由内向外进行处理, 即先执行子查询, 将子查询的结果作为父查询的查询条件 例 3.49 查询入学总分高于平均总分的学生的学号 姓名及总分信息, 并将显示结果按照班级编号 学号的升序排列, 写出其 SQL 命令 题目要求查询入学总分高于平均总分的学生信息, 首先需要得到学生入学总分的平均值, 然后才能查询高于此平均总分的学生的信息, 所以查询条件中要用到嵌套查询, 命令如下 : SELECT s_no,s_name,s_score +s_addf FROM student WHERE ( s_score +s_addf) >( SELECT AVG( s_score +s_addf) FROM student) ORDER BY class_no,s_no; S_NO S_NAME S_SCORE +S_ADDF 赵明 杨仪 王蕾 牛可 630 本例的执行过程为 : 先执行子查询 SELECT AVG( s_score +s_addf) FROM student 以获得学生的平均总分 578 分, 再执行父查询 SELECT s_no,s_name,s_score +s_addf FROM student WHERE (s_score +s_addf) >578 获得入学总分高于平均总分的学生的部分信息, 接着将查询结果按照 class_no s_no 的升序排列 例 3.50 查询选修 0001 号课程成绩高于这门课程平均分的学生的学号 姓名 成绩, 并将显示结果按照班级编号 学号的升序排列, 写出其 SQL 命令 题目要求查询选修 0001 号课程成绩高于这门课程平均分的学生信息, 需要先查询 0001 号课程平均分, 再查询高于此平均分的学生有关这门课程的选修信息, 同样要用到嵌套查询, 命

35 78 第 3 章 SQL 基础 令如下 : SELECT student.s_no,s_name,score FROM student,sc WHERE score >(SELECT AVG(score) FROM sc WHERE c_no =0001 ) AND c_no =0001 S_NO S_NAME SCORE 马水 95 AND student.s_no =sc.s_no ORDER BY class_no,s_no; 3.5 视图 在学生选课系统中经常需要查询学生的选修信息, 假设包含学号 姓名 班级编号 课程编号 课程名称 成绩等, 但不想将 student 表 course 表和 sc 表的结构信息向无关人员透露, 为了保证系统的安全性, 需要将所定义的基本表信息隐藏起来, 此时就可以采用视图技术 视图是为了确保数据表的安全性 提高数据的隐蔽性而从一个或多个表中或其他视图中使用 SELECT 语句所导出的虚表 数据库中仅存放视图的定义, 而不存放视图所对应的数据, 数据仍然存放在基础表中, 对视图中数据所做的更新实际上仍然是对组成视图的基础表的更新 换句话说, 视图相当于在基础表上增设一层保护膜, 通过使用视图, 基础表中的数据能够以各种不同的方式提供给用户, 以加强数据库的安全性 视图应先行定义, 再加以使用 定义视图定义视图命令的一般格式如下 : CREATE [OR REPLACE] VIEW < 视图名 >[ ( 列名列表 )] AS 子查询 [ WITH READ ONLY WITH CHECK OPTION] 其中 : VIEW 是视图关键字,OR REPLACE 选项表示将覆盖视图中原有的内容, 仅在修改时使用 ; WITH READ ONLY WITH CHECK OPTION 说明视图的性质, 前者表示视图的属性是只读的, 不能进行插入 修改 删除操作, 后者则表示当向视图中插入 修改 删除数据时, 数据必须满足视图定义中子查询的 WHERE 条件, 默认状态为只读 视图中的 列名列表 要么全部省略, 要么全部指定 当省略它时, 列名是子查询中的显示列表项 在以下 3 种情况下必须指出组成视图的所有列名 (1) 某个目标列不是单纯的属性名, 而是聚集函数或列表达式 ; (2) 进行多表连接时,SELECT 语句中有若干同名列作为视图的字段 ; (3) 需要在视图中为某个列启用新的更合适的名字 例 3.51 定义选修课程成绩及格的只读视图 sc_view, 要求视图中包含学号 姓名 班级

36 3.5 视图 79 编号 课程编号 课程名称 成绩, 写出其 SQL 命令 CREATE VIEW sc_view( s_no,s_name,class_no,c_no,c_name,score) AS SELECT student.s _ no,s _name, class _ no, course.c _ no, c _ name, score FROM student,course,sc WHERE student.s_no =sc.s_no AND course.c_no =sc.c_no AND score >=60 WITH READ ONLY; 本例中的 s_no 字段在 student 表和 sc 表中均存在,c_no 字段在 course 表和 sc 表中均存在, 所以视图列名必须明确指定 ; 由于是 只读 视图, 所以带有 WITH READ ONLY 参数 此命令执行后, 在数据库中创建一个名为 sc_view 的视图, 其信息存储于 Oracle 系统数据表 中 例 3.52 定义班级编号为 0101 的检查视图 student_view, 要求视图中包含学号 姓名 入学成绩, 班级编号, 写出其 SQL 命令 CREATE VIEW student_view AS SELECT s_no,s_name,s_score,class_no FROM student WHERE class_no =0101 WITH CHECK OPTION; 本例中的字段只来源于 student 表, 检查视图带有 WITH CHECK OPTION 子句 同样, 此命令执行后, 在数据库中创建一个名为 student_view 的视图, 其信息存储于 Oracle 系统数据表中 查询视图 视图定义成功之后, 视图信息存储于系统数据表 DBA_VIEWS 中, 通过使用查询命令 DESC 可以得到存储在 DBA_VIEWS 中的视图信息 例 3.53 用 DESC 命令查看 DBA_VIEWS 的结构 DESC DBA_VIEWS; 名称 是否为空 数据类型 OWNER NOT NULL VARCHAR2(30) VIEW_NAME NOT NULL VARCHAR2(30) TEXT_LENGTH NUMBER TEXT LONG TYPE_TEXT_LENGTH NUMBER TYPE_TEXT VARCHAR2(4000) OID_TEXT_LENGTH NUMBER

37 80 第 3 章 SQL 基础 OID_TEXT VARCHAR2(4000) VIEW_TYPE_OWNER VARCHAR2(30) VIEW_TYPE VARCHAR2(30) SUPERVIEW_NAME VARCHAR2(30) 从本例可以看出,DBA_VIEWS 系统数据表中存储着视图名称 ( VIEW_NAME) 视图的子查询语句 (TEXT) 及其长度 (TEXT_LENGTH) 等信息 可以使用 SELECT 命令查询所需的结果 例 3.54 利用 DBA_VIEWS 系统数据表查看视图 sc_view 的内容 长度信息, 写出其 SQL 命令 SELECT TEXT,TEXT_LENGTH FROM DBA_VIEWS WHERE VIEW_NAME = SC_VIEW ; TEXT TEXT_LENGTH SELECT student.s_no,s_name,class_no,course.c_no,c_name,score FROM 163 student,course 从本例可以看出,sc_view 子查询的长度为 163, 由于对查询结果的显示有所限制, 子查询语句没有全部显示出来 基于视图的数据查询与更新视图是从多表所导出的虚表, 具有表的基本特性, 可以基于视图作数据查询和更新 1. 基于视图的数据查询基于视图的数据查询与基于基本表的数据查询一样使用 SELECT 语句, 查询视图的基本方法与查询基本表一致, 只不过数据来源为视图 例 3.55 查询选修课程成绩及格的学生的学号 姓名 课程编号 课程名称和成绩, 写出其 SQL 命令 例 3.51 中所定义的视图 sc_view 就包含了本例所要查询的信息, 可以基于 sc_view 进行查询, 命令如下 : SELECT s_no,s_name,c_no,c_name,score FROM sc_view; 由于视图是建立于基本表基础之上的虚表, 所以基于视图的查询和更新最终都要转化为基于基本表的查询和更新, 这个过程称为视图消解过程 视图消解过程分三步进行, 第一步对视图进行有效性检查, 第二步将视图转换成等价的对基本表的查询语句, 第三步执行经转换后得到的查询语句 例 3.55 中基于视图 sc_view 的查询首先要转化成基于组成视图 sc_view 的基本表 student sc 和 course 的查询, 查询语句如下 : SELECT student.s _ no, s _ name, course.c _ no, c _ name, score FROM student,

38 3.5 视图 81 course,sc WHERE student.s_no =sc.s_no AND course.c_no =sc.c_no AND score >=60; 比较两种查询可知 : (1) 视图能够简化用户的操作, 可以将复杂的查询定义成视图, 然后基于视图执行查询操作, 这样能够简化用户的操作 (2) 可以基于相同的基本表定义不同的视图, 视图的组成属性可以不同, 使用户能够从多种角度分析同一数据 (3) 视图能够通过定义视图的 WHERE 条件来过滤数据, 对机密数据提供安全保护 (4) 视图隐藏了基本表的信息, 提供系统的安全性 例 3.56 查询选修 0001 号课程的成绩及格的学生的学号 姓名, 写出其 SQL 命令 SELECT s_no,s_name FROM sc_view WHERE c_no =0001 ; 2. 基于视图的数据更新在一般情况下, 只能基于行列子集视图进行数据更新 所谓行列子集视图即若一个视图是从单个基本表导出的, 并且只是去除基本表的某些行和列, 但保留主键, 则称这类视图为行列子集视图 注意 : 基于视图的更新实际上仍然是修改基本表中的数据 例 3.57 向 student_view 视图中插入一条记录, 学号为 , 姓名为 张三, 入学成绩为 560 分, 写出其 SQL 命令 INSERT INTO student_view VALUES( , 张三,560, 0101 ) ; 此命令执行后, 利用 SELECT 语句查询 student 表中的数据, 可以发现这一行数据已插入 student 表 可见, 基于视图的数据更新隐藏了数据库基本表的信息, 使得数据库更加安全 注意 : 基于视图作数据更新时, 一定要满足定义视图时查询子句中的 WHERE 条件, 本例中的班级编号应为 0101 如果 班级编号 字段值不是 0101, 则插入操作就会失败 删除视图一旦视图在数据库中定义成功, 会一直存储于数据库中, 但有时会发现不再需要某个视图, 此时应删除此视图, 以释放其所占用的存储空间 删除视图命令的一般格式如下 : DROP VIEW [ < 方案名 >.] < 视图名 >; 假设在学生选课系统中不再需要视图 sc_view, 此时, 可以将视图 sc_view 删除 例 3.58 删除视图 sc_view, 写出其 SQL 命令 DROP VIEW sc_view; 此命令执行后, 数据库中就不再有 sc_view 视图了

39 82 第 3 章 SQL 基础 3.6 小结 本章对 SQL 进行概述, 重点介绍了 SQL 的概念 特点及 SQL 中的数据定义 数据查询和数据操纵功能及基本命令格式 通过本章内容的学习, 读者应该了解 : (1) SQL 称为结构化查询语言, 在许多关系数据库管理系统中均可使用, 其功能并非仅局限于查询, 它集数据定义 数据查询 数据操纵 数据控制功能于一体 (2) 通过 SQL 定义对象的方法是使用 CREATE 命令, 通过 SQL 修改对象的方法是使用 AL- TER 命令, 通过 SQL 删除对象的方法是使用 DROP 命令 在数据定义命令中, 表的关键字为 TA- BLE, 索引的关键字为 INDEX, 视图的关键字为 VIEW (3) 通过 SQL 查询数据的方法是使用 SELECT 命令 (4) 通过 SQL 插入数据的方法是使用 INSERT 命令, 通过 SQL 修改数据的方法是使用 UP- DATE 命令, 通过 SQL 删除数据的方法是使用 DELETE 命令 思考题与习题 1. 简述 SQL 的发展历程 2. 什么是视图, 它与表有何不同? 3. 表级约束和列级约束有何不同? 4. 简述 Oracle 系统中表的约束类型及定义方法 5. 写出 SQL 的 9 个动词 6.UNIQUE 和 PRIMARY KEY 约束之间的区别是什么? 7. 写出视图定义中 WITH CHECK OPTION 约束的含义 8. 写出索引的作用 9. 基于哪种视图可以进行数据更新? 实 训 一 实训目标熟练掌握 SQL 的数据定义 数据查询和数据更新命令 二 实训学时建议实训参考学时为 6 或 8 学时 三 实训准备熟悉附录 A 中 SQL Plus 或 SQL Plus Worksheet 环境, 掌握常用命令的使用方法 四 实训内容针对学生选课系统, 执行下述操作, 记录执行命令和操作过程中所遇到的问题及相应的解决方法, 注意从原理上查找原因 1. 定义学生选课系统中的 学生 表 student 课程 表 course 课程选修 表 sc, 其中各表的表名设置为给

40 实训 83 定表名 + 班级名称缩写 + 学号 下面以 计算 号 学生为例,student 表的表名为 studentjs05102,course 表的表名为 coursejs05102,sc 表的表名为 scjs05102 student 表如表 3.7 所示,course 表如表 3.8 所示,sc 表如表 3.9 所示 2. 查看 学生 表的结构信息 3. 在 学生 表的 姓名 字段上定义降序索引 student_sname_index 4. 在 课程 表的 课程名称 字段上定义升序索引 course_cname_index 5. 在 课程选修 表的 成绩 字段上定义升序索引 sc_grade_index 6. 查看 学生 表的索引信息 7. 查看 课程 表的索引信息 8. 删除 课程选修 表 成绩 字段上的索引 9. 向学生选课系统的 3 个表中各插入 5 条记录, 记录插入数据过程中所遇到的问题及相应的解决方法, 注意体会约束对于数据所产生的影响 10. 定义 学生 表 2(studentjs051022), 向此表中插入 5 条不同的记录, 注意必须与 学生 表中的数据有所不同, 主要体现在主键 学号 上 执行下述操作 (1) 采用多行数据插入的方法, 将 学生 表 2 中的数据插入 学生 表, 观察执行此操作的结果 (2) 将命令再执行一次, 观察所得的结果, 分析产生错误的原因 11. 修改 学生 表 2(studentjs051022) 的结构, 添加 家庭地址 (f_address char(50)) 家庭收入 (f_salary number(5,1)) 和 家庭人数 (f_num number(2)) 这 3 个字段 12. 修改 学生 表 2(studentjs051022) 的结构, 删除 家庭地址 (f_address char(50)) 字段 13. 修改 学生 表 2(studentjs051022) 的结构, 删除 家庭收入 (f_salary number(5,1)) 和 家庭人数 ( f_ num number(2)) 这 2 个字段 14. 修改 学生 表 2(studentjs051022) 的结构, 将 姓名 (s_name) 字段的长度修改为 6, 记录修改结果 15. 修改 学生 表 2(studentjs051022) 的结构, 将 姓名 (s_name) 字段的长度修改为 10, 记录修改结果 16. 采用表间数据复制的方法定义 学生 表 3(studentjs051023), 将 student 表中全部字段的值复制到 学生 表 3, 查看 学生 表 3 的内容, 体会表间数据复制的含义 ; 采用表间数据复制的方法定义 学生 表 4(stu- dentjs051024), 将 student 表中部分字段的值复制到 学生 表 4, 查看 学生 表 4 的内容, 比较 学生 表 3 和 学生 表 4 中数据的不同, 进一步体会表间数据复制的含义 17. 修改 学生 表中某名学生的学号, 能否成功, 记录结果, 并解释成功或失败的原因 18. 修改 课程选修 表中某名学生的学号, 能否成功, 记录结果, 并解释成功或失败的原因 19. 删除 学生 表 3 中的所有数据, 然后删除 学生 表 3, 注意体会删除数据与删除表之间的不同 20. 永久删除 学生 表 2 中的数据, 然后删除 学生 表 定义 只读 视图 sc_view, 要求显示所有学生的学号 姓名 课程编号 课程名称和成绩 22. 查看视图 sc_view 的基本信息 23. 定义检查视图 student_view, 要求显示学生的学号 姓名 班级编号 入学成绩 24. 基于视图 student_view, 向 学生 表插入记录 修改记录 删除记录, 观察 学生 表数据的变化 25. 基于学生选课系统进行下述数据查询练习 (1) 基于视图 sc_view 查询选修课程编号为 0001 课程的学生的学号 姓名 成绩 (2) 查询所有学生的全部情况 (3) 查询男生的学号 姓名 出生日期 (4) 查询选修课程成绩在 60 分以上的学生的姓名 课程名称和成绩 (5) 查询赵以同学所学课程成绩低于 60 分的课程名称

41 84 第 3 章 SQL 基础 (6) 将 Java 语言 课程成绩低于 60 分的学生的成绩改为 60 分 (7) 查询全部赵姓学生的所有信息 (8) 统计学生数据库中选修课程成绩在 80 ~90 分之间的学生人数 (9) 计算 0101 班学生数据库选修课程的平均分 (10) 按班级分别统计学生入学总分在平均分之上的学生人数 (11) 按班级对学生的入学成绩从高到低进行排序 (12) 按班级统计各班学生的入学总分 (13) 分组统计选修各门课程的学生人数 (14) 基于视图 sc_view 查询所有选修课程成绩及格的学生的学号 姓名 课程名称 成绩 (15) 基于视图 sc_view 查询张三同学所学课程的课程编号 课程名称 成绩