课程名称：数据库系统概论

Transcription

1 数据库系统概论 第三章关系数据库标准语言 SQL(I) 兴义民族师范学院

2 第三章关系数据库标准语言 SQL 3.1 SQL 概述 3.2 学生 - 课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 视图 3.7 小结

3 3.1 SQL 概述 SQL(Structured Query Language) 结构化查询语言, 是关系数据库的标准语言 SQL 是一个通用的 功能极强的关系数据库语言 SQL 标准的进展过程 标准大致页数发布日期 SQL/ SQL/89(FIPS 127-1) 120 页 1989 年 SQL/ 页 1992 年 SQL 页 1999 年 SQL 年

4 3.1.2 SQL 的特点 1. 综合统一 集数据定义语言 (DDL), 数据操纵语言 (DML), 数据控制语言 (DCL) 功能于一体 可以独立完成数据库生命周期中的全部活动 : 定义关系模式, 插入数据, 建立数据库 ; 对数据库中的数据进行查询和更新 ; 数据库重构和维护 数据库安全性 完整性控制等 用户数据库投入运行后, 可根据需要随时逐步修改模式, 不影响数据的运行 数据操作符统一

5 2. 高度非过程化 非关系数据模型的数据操纵语言 面向过程, 必须制定存取路径 SQL 只要提出 做什么, 无须了解存取路径 存取路径的选择以及 SQL 的操作过程由系统自动完成 3. 面向集合的操作方式 非关系数据模型采用面向记录的操作方式, 操作对象是一条记录 SQL 采用集合操作方式 操作对象 查找结果可以是元组的集合 一次插入 删除 更新操作的对象可以是元组的集合

6 4. 以同一种语法结构提供多种使用方式 SQL 是独立的语言, 能够独立地用于联机交互的使用方式 SQL 又是嵌入式语言, 能够嵌入到高级语言 ( 例如 C,C++,Java) 程序中, 供程序员设计程序时使用 5. 语言简洁, 易学易用 SQL 功能极强, 完成核心功能只用了 9 个动词 SQL 功能 数据查询 数据定义 数据操纵 数据控制 表 3.1 SQL 语言的动词 动词 SELECT CREATE,DROP,ALTER INSERT,UPDATE DELETE GRANT,REVOKE

7 3.1.3 SQL 的基本概念 SQL 支持关系数据库三级模式结构 SQL 视图 1 视图 2 外模式 基本表 1 基本表 2 基本表 3 基本表 4 模式 存储文件 1 存储文件 2 内模式

8 SQL 的基本概念 ( 续 ) 基本表 本身独立存在的表 SQL 中一个关系就对应一个基本表 一个 ( 或多个 ) 基本表对应一个存储文件 一个表可以带若干索引 存储文件 逻辑结构组成了关系数据库的内模式 物理结构是任意的, 对用户透明 视图 从一个或几个基本表导出的表 数据库中只存放视图的定义而不存放视图对应的数据 视图是一个虚表 用户可以在视图上再定义视图

9 3.2 学生 - 课程数据库 学生 - 课程模式 S-T : 学生表 :Student(Sno,Sname,Ssex,Sage,Sdept) 课程表 :Course(Cno,Cname,Cpno,Ccredit) 学生选课表 :SC(Sno,Cno,Grade) Student 表 学号 Sno 姓名 Sname 性别 Ssex 年龄 Sage 所在系 Sdept 李勇刘晨王敏张立 男女女男 CS CS MA IS

10 Course 表 课程号 Cno 课程名 Cname 先行课 Cpno 学分 Ccredit 1 数据库 数学 2 3 信息系统 操作系统 数据结构 数据处理 PASCAL 语言 SC 表 学号 Sno 课程号 Cno 成绩 Grade

11 3.3 数据定义 SQL 的数据定义功能 : 模式定义 表定义 视图和索引的定义 表 3.2 SQL 的数据定义语句 操作方式操作对象创建删除修改模式 CREATE SCHEMA DROP SCHEMA 表 CREATE TABLE DROP TABLE ALTER TABLE 视图 CREATE VIEW DROP VIEW 索引 CREATE INDEX DROP INDEX

12 3.3.1 模式的定义与删除 一 定义模式 [ 例 1] 定义一个学生 - 课程模式 S-T CREATE SCHEMA S-T AUTHORIZATION WANG; 为用户 WANG 定义了一个模式 S-T [ 例 2] CREATE SCHEMA AUTHORIZATION WANG; < 模式名 > 隐含为用户名 WANG 如果没有指定 < 模式名 >, 那么 < 模式名 > 隐含为 < 用户名 > 定义模式实际上定义了一个命名空间 在这个空间中可以定义该模式包含的数据库对象, 例如基本表 视图 索引等

13 定义模式 ( 续 ) [ 例 3] 为用户 ZHANG 创建了一个模式 TEST, 并在其中定义了一个表 TAB1 CREATE SCHEMA TEST AUTHORIZATION ZHANG CREATE TABLE TAB1(COL1 SMALLINT, COL2 INT, COL3 CHAR(20), COL4 NUMERIC(10,3), COL5 DECIMAL(5,2) );

14 二 删除模式 DROP SCHEMA < 模式名 > <CASCADE RESTRICT> CASCADE( 级联 ) 删除模式的同时把该模式中所有的数据库对象全部删除 RESTRICT( 限制 ) 如果该模式中定义了下属的数据库对象 ( 如表 视图等 ), 则拒绝该删 除语句的执行 当该模式中没有任何下属的对象时才能执行 [ 例 4] DROP SCHEMA ZHANG CASCADE; 删除模式 ZHANG 同时该模式中定义的表 TAB1 也被删除

15 3.3.2 基本表的定义 删除与修改 一 定义基本表 CREATE TABLE < 表名 > (< 列名 > < 数据类型 >[ < 列级完整性约束条件 > ] [,< 列名 > < 数据类型 >[ < 列级完整性约束条件 >] ] [,< 表级完整性约束条件 > ] ); 如果完整性约束条件涉及到该表的多个属性列, 则必须定义在表级上, 否则既可以定义在列级也可以定义在表级

16 学生表 Student [ 例 5] 建立 学生 表 Student, 学号是主码, 姓名取值唯一 主码 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件 */ Sname CHAR(20) UNIQUE, /* Sname 取唯一值 */ Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) );

17 课程表 Course [ 例 6] 建立一个 课程 表 Course CREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT, 先修课 FOREIGN KEY (Cpno) REFERENCES Course(Cno) ); Cpno 是外码被参照表是 Course 被参照列是 Cno

18 学生选课表 SC [ 例 7] 建立一个 学生选课 表 SC CREATE TABLE SC (Sno CHAR(9), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY (Sno,Cno), /* 主码由两个属性构成, 必须作为表级完整性进行定义 */ FOREIGN KEY (Sno) REFERENCES Student(Sno), /* 表级完整性约束条件,Sno 是外码, 被参照表是 Student */ FOREIGN KEY (Cno) REFERENCES Course(Cno) /* 表级完整性约束条件, Cno 是外码, 被参照表是 Course*/ );

19 二 数据类型 p86 SQL 域的概念用数据类型来实现, 需要指明数据类型及长度 CHAR(n) 数据类型 VARCHAR(n) 长度为 n 的定长字符串 最大长度为 n 的变长字符串 含义 INT SMALLINT NUMERIC(p,d) REAL Double Precision FLOAT(n) DATE TIME 长整数 ( 也可以写作 INTEGER) 短整数 定点数, 由 p 位数字 ( 不包括符号 小数点 ) 组成, 小数后面有 d 位数字 取决于机器精度的浮点数 取决于机器精度的双精度浮点数 浮点数, 精度至少为 n 位数字 日期, 包含年 月 日, 格式为 YYYY-MM-DD 时间, 包含一日的时 分 秒, 格式为 HH:MM:SS

20 三 模式与表 每一个基本表都属于某一个模式 一个模式包含多个基本表 定义基本表所属模式 方法一 : 在表名中明显地给出模式名 Create table S-T.Student(...); /* 模式名为 S-T*/ Create table S-T.Cource(...); Create table S-T.SC(...); 方法二 : 在创建模式语句中同时创建表 方法三 : 设置所属的模式

21 模式与表 ( 续 ) 创建基本表 ( 其他数据库对象也一样 ) 时, 若没有指定模式, 系统根 据搜索路径来确定该对象所属的模式 RDBMS 会使用模式列表中第一个存在的模式作为数据库对象的模式名 若搜索路径中的模式名都不存在, 系统将给出错误 显示当前的搜索路径 : SHOW search_path; 搜索路径的当前默认值是 :$user, PUBLIC DBA 用户可以设置搜索路径, 然后定义基本表 SET search_path TO S-T,PUBLIC; Create table Student(...); 结果建立了 S-T.Student 基本表

22 四 修改基本表 ALTER TABLE < 表名 > [ ADD < 新列名 > < 数据类型 > [ 完整性约束 ] ] [ DROP < 完整性约束名 > ] [ ALTER COLUMN< 列名 > < 数据类型 > ]; [ 例 8] 向 Student 表增加 入学时间 列, 其数据类型为日期型 ALTER TABLE Student ADD S_entrance DATE; 不论基本表中原来是否已有数据, 新增加的列一律为空值 [ 例 9] 将年龄的数据类型由字符型 ( 假设原来的数据类型是字符型 ) 改为 整数 ALTER TABLE Student ALTER COLUMN Sage INT; [ 例 10] 增加课程名称必须取唯一值的约束条件 ALTER TABLE Course ADD UNIQUE(Cname);

23 五 删除基本表 DROP TABLE < 表名 >[RESTRICT CASCADE]; RESTRICT: 删除表是有限制的 欲删除的基本表不能被其他表的约束所引用 如果存在依赖该表的对象, 则此表不能被删除 CASCADE: 删除该表没有限制 在删除基本表的同时, 相关的依赖对象一起删除 [ 例 11] 删除 Student 表 DROP TABLE Student CASCADE ; 基本表定义被删除, 数据被删除 表上建立的索引 视图 触发器等一般也将被删除

24 [ 例 12] 若表上建有视图, 选择 RESTRICT 时表不能删除 CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept='IS'; DROP TABLE Student RESTRICT; --ERROR: cannot drop table Student because other objects depend on it 如果选择 CASCADE 时可以删除表, 视图也自动被删除 DROP TABLE Student CASCADE; --NOTICE: drop cascades to view IS_Student SELECT * FROM IS_Student; --ERROR: relation " IS_Student " does not exist

25 序 号 删除基本表 ( 续 ) DROP TABLE 时,SQL99 与 3 个 RDBMS 的处理策略比较 标准及主流数据库的处理方式 依赖基本表的对象 SQL99 Kingbase ES ORACLE 9i MS SQL SERVER 2000 R C R C C 1. 索引无规定 2. 视图 保留 3. DEFAULT,PRIMARY KEY,CHECK ( 只含该表的列 )NOT NULL 等约束 保留 保留 4. Foreign Key 5. TRIGGER 6. 函数或存储过程 保留 保留 保留 保留 R 表示 RESTRICT, C 表示 CASCADE ' ' 表示不能删除基本表,' ' 表示能删除基本表, 保留 表示删除基本表后, 还保留依赖对象 保留

26 3.3.3 索引的建立与删除 建立索引的目的 : 加快查询速度 建立索引是由 DBA 或表的属主 ( 即建立表的人 ) RDBMS 中索引一般采用 B+ 树 HASH 索引来实现 索引是关系数据库的内部实现技术, 属于内模式的范畴 CREATE INDEX 语句定义索引时, 可以定义索引是唯一索引 非 唯一索引或聚簇索引 语句格式 CREATE [UNIQUE] [CLUSTER] INDEX < 索引名 > ON < 表名 >(< 列名 >[< 次序 >][,< 列名 >[< 次序 >] ] );

27 [ 例 13] CREATE CLUSTER INDEX Stusname ON Student(Sname); 在 Student 表的 Sname( 姓名 ) 列上建立一个聚簇索引 在最经常查询的列上建立聚簇索引以提高查询效率 一个基本表上最多只能建立一个聚簇索引 经常更新的列不宜建立聚簇索引 [ 例 14] 为学生 - 课程数据库中的 Student,Course,SC 三个表建立索引 CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Coucno ON Course(Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC); Student 表按学号升序建唯一索引 Course 表按课程号升序建唯一索引 SC 表按学号升序和课程号降序建唯一索引

28 二 删除索引 DROP INDEX < 索引名 >; 删除索引时, 系统会从数据字典中删去有关该索引的描述 [ 例 15] 删除 Student 表的 Stusname 索引 DROP INDEX Stusname;

29 第三章关系数据库标准语言 SQL 3.1 SQL 概述 3.2 学生 - 课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 视图 3.7 小结 上机实践 自学讨论

30 学习方法与步骤 1 创建数据库表 3.3 p85 例 插入更新数据 3.5 p115 例 数据查询 3.4 p91 例 虚表视图 3.6 p118 例 1-52 阅读教材第 3 章 准备课堂讨论 反复上机实践每个语句, 制作 sql 文件 选择 2-3 个专题撰写实验报告

31 理论 ( 讨论 )+) 上机实验 (2+2 节每周, 共 3 周 )