第二章 关系数据库

Transcription

1 第三章关系数据库标准语言 SQL

2 本章内容 数据定义 数据查询 数据更新 数据库语言的编程

3 3.1 SQL 概述 SQL 的发展 1974 年, 由 Boyce 和 Chamberlin 提出 1975~1979,IBM San Jose Research Lab 的关系数据库管理系统原型 System R 实施了这种语言 SQL-86 是第一个 SQL 标准 SQL-89 SQL-92(SQL2) SQL-99(SQL3) SQL2003

4 SQL 的发展 ( 续 ) SQL-99(SQL3) 特点 关系特征 : 新的数据类型, 例如大对象类型, 集合类型等, 增加了递归查询等 面向对象特征 : 用户自定义的结构化类型, 实现了函数和方法, 有继承关系, 对象 ID 的实现 (REF 类型 )

5 SQL 的发展 ( 续 ) SQL2003 特点 支持新的数据类型和相应的操作, 例如 : MULTISET 支持数据仓库操作, 例如 MERGE, MERGE = UPDATE + INSERT 支持 XML?

6 SQL 概述 现状 大部分 DBMS 产品都支持 SQL, 成为操作数据库的标准语言 商业数据库软件对 SQL 的支持程度不同

7 SQL 概述 SQL 的功能 数据定义 (DDL) 定义 删除 修改关系模式 ( 基本表 ) 定义 删除视图 (View) 定义 删除索引 (Index) 数据操纵 (DML) 数据查询 数据增 删 改 数据控制功能

8 SQL 的特点 综合统一 ( 数据定义, 数据查询, 数据操纵和数据控制功能 ) 非过程化语言 ( 提出做什么, 而无须指明怎么做 ) 面向集合的操作 SQL 多种使用形式 ( 独立执行和嵌入在高级语言中 ) 简单, 易学

9 SQL 概述 SQL 的形式 交互式 SQL 一般 DBMS 都提供联机交互工具 用户可直接键入 SQL 命令对数据库进行操作 由 DBMS 来进行解释

10 SQL 的形式 交互式

11 SQL 的形式 交互式

12 SQL 概述 SQL 的形式 嵌入式 SQL 能将 SQL 语句嵌入到高级语言 ( 宿主语言 ) 使应用程序充分利用 SQL 访问数据库的能力 宿主语言的过程处理能力 一般需要预编译, 将嵌入的 SQL 语句转化为宿主语言编译器能处理的语句

13 SQL 的形式 嵌入式 Main(){... exec sql begin declare section; char co[10]; int id; exec sql end declare section; exec sql select company_name from customer where id = :id into co; }

14 SQL 概述 SQL 的形式 SQL/API(Application Programming Interface) 一组函数和程序 从宿主语言主程序中调用一个 SQL DBMS 库, 而 SQL 语句是这个调用的参数 目前更多的数据库编程在使用这种方法 ODBC JDBC SQL/CLI

15 SQL 的形式 SQL/API #include sqlcli.h SQLHSTMT hstmt; SQLPrepare(hstmt, Insert Into customer values( ) ); SQLExcute(hstmt);

16 SQL 概述 SQL 的动词 数据查询 SELECT 数据定义 CREATE DROP ALTER 数据操纵 INSERT UPDATE DELETE 数据控制 GRANT REVOKE

17 3.2 数据定义 数据定义语言 (Data Definition Language) 模式 基本表 视图 索引 创建 Create 修改 Alter( 基本表 ) 删除 Drop

18 数据定义 模式的定义与删除 定义模式 CREATE SCHEMA < 模式名 > AUTHORIZATION< 用户名 > 如没有指定模式名, 则隐含为用户名. 定义模式, 实际上是定义了一个命名空间, 在该空间中, 进一步定义基本表, 索引等. 删除模式 : DROP SCHEMA < 模式名 ><CASCADE I RESTRICT>

19 数据定义 创建基本表 定义一组关系 ( 基本表 ) 说明各关系的信息 各关系的模式 各属性的值域 完整性约束

20 数据定义 SQL 中的域类型 数值型 int (32) smallint (16) tinyint (8) numeric(p,d) real double float(n) 字符型 char(n) varchar(n) 日期 / 时间型 date time datetime

21 数据定义 SQL 的模式定义 Create Table r (A 1 D 1 C 1, A 2 D 2 C 2,, A n D n C n, < 表级完整性约束 1>, < 表级完整性约束 n>) 其中 : r 关系名 ( 表名 ) A i 关系 r 的一个属性名 D n 属性 A i 域值的域类型 C n 属性 A i 的完整性约束

22 数据定义 SQL 的模式定义 主键声明 : 单属性主键 :PRIMARY KEY 多属性主键 :primary key (A j1,a j2,,a jm ) 外键声明 : Foreign key (A f1,a f2,,a fp ) Reference S

23 数据定义 创建基本表 例 Create Table Student ( sno char(5) primary key, sname char(10), ssex char(1), sage int, sdept char(2))

24 数据定义 创建基本表 例 Create Table Course ( cno char(5) primary key, cname char(10), cpno char(5), credit int)

25 数据定义 创建基本表 例 Create Table SC ( sno cno grade char(5), char(5), int, primary key(sno,cno), foreign key (sno) references student, foreign key (cno) references course)

26 数据定义 创建基本表

27 数据定义 删除基本表 用 SQL 删除关系 ( 表 ) 将整个关系模式 ( 表结构 ) 彻底删除 Drop Table

28 数据定义 修改基本表结构 用 SQL 删除表中的某属性 去除属性及相应的数据 Alter Table r Drop A Alter Table student Drop sdept;

29 数据定义 修改基本表结构 用 SQL 增加表中的属性 向已经存在的表中添加属性 已有的元组中该属性的值被置为 Null Alter Table r Add A D Alter Table student Add address char(30)

30 数据定义 修改基本表结构 用 SQL 修改表中属性 修改表中属性的数据类型 可能破坏原有的数据 Alter Table r Modify A D Alter Table student Modify sname varchar(30)

31 数据定义 索引的建立与删除 建立索引 CREATE[UNIQUE][CLUSTER]INDEX< 索引名 >ON< 表名 >(< 列名 >[< 次序 >],< 列名 > ); UNIQUE: 每一个索引值只对应唯一的数据记录 CLUSTER: 聚簇索引, 索引项顺序与记录的物理顺序一致. 删除索引 DROP INDEX < 索引名 >

32 数据定义 数据添加 用 SQL 的插入语句, 向数据库表中添加数据 按关系模式的属性顺序 Insert Into Student Values ( 95001, 张三, M,27, CS ) 按指定的属性顺序, 也可以只添加部分属性 ( 非 Null 属性为必需 ) Insert Into Student ( sno, sname, sage) Values ( 95002, 李四,26 )

33 3.3 查询 数据查询是数据库的核心操作 Select

34 查询 基本结构 Select A 1, A 2,..., A n From r 1, r 2,..., r m Where P 等价于 : A 1, A 2,..., A n ( (r 1 x r 1 x... x r m )) Select Where From

35 查询 Select 语句的含义 对 From 子句中的各关系, 作笛卡儿积 对 Where 子句中的逻辑表达式进行选择 ( ) 运算, 找出符合条件的元组 根据 Select 子句中的属性列表, 对上述结果 作投影 ( ) 操作

36 查询 结果集 查询操作的对象是关系, 结果还是一个关系, 是一个结果集, 是一个动态数据集

37 查询 例 : 列出所有学生的学号及姓名 Select From sno,sname student;

38 查询 单表查询 仅涉及一个表的查询 从一个基本表中产生所需要的结果集 From 子句中仅有一个表名 无须进行笛卡儿积

39 单表查询 选择若干列 仅作投影 查询指定的列 Select < 目标列表达式 > 可按照需求排列属性的顺序 例 : 查询全体学生的学号与姓名 Select sno,sname From student;

40 单表查询 选择若干列 查询全部列 Select < 目标列表达式 > Select * 两者有何差异? 例 : 查询全体学生的详细信息 Select * From student;

41 单表查询 选择若干列 查询计算列 Select < 目标列表达式 ( 含有计算表达式 )> 例 : 查询学生的学号 姓名及出生年份 Select sno,sname, 2013-sage From student;

42 单表查询 选择若干列 改变结果集的列名 Select 列名 as 别名,... 例 : 查询学生的学号 姓名 Select sno as 学号,sname as 姓名 From student;

43 单表查询 选择若干列 函数的使用 在 Select 子句中, 可使用相应的 SQL 函数 ( 不同的 DBMS 可能有不同的函数集 ) 例 : 列出学生的学号 姓氏 Select sno as 学号,left(sname,1) as 姓氏 From student;

44 单表查询 选择若干元组 根据条件进行选择操作 集合 (Set) 与包 (Bag) Select 结果集 包 ( 缺省 ) 集合 ( 使用 Distinct 短语 ), 耗时 Select Distinct sno From SC;

45 单表查询 选择若干元组 查询满足条件的元组 :WHERE 子句 形式 : Select 列名, 列名, From 表名 Where 条件表达式 使条件表达式的结果为真的元组 结果为 真 或 假

46 单表查询 选择若干元组

47 单表查询 选择若干元组 比较大小 确定范围 确定集合 字符匹配 空值 Null 多重条件

48 单表查询 选择若干元组 确定集合 ( 组成员测试 ) where 字段 in (value1,value2, ) 等价与或运算 (OR) 例找出 CS 系或 MA 系或 IS 系的学生 Select * From student Where sdept IN ( CS, MA, IS )

49 单表查询 选择若干元组 字符匹配 (Like,%,_) 检查包含字符串数据字段的值是否与指定的样式匹配 使用 Like 运算符 % 表示任何顺序的 0 个或多个字符 _ 表示任何一个单个字符

50 单表查询 选择若干元组 找出所有数据库课程 Select * From course Where cname Like % 数据库 %

51 单表查询 选择若干元组 找出所有张姓单名的学生 Select * From student Where sname Like 张 _

52 单表查询 选择若干元组 空值 Null 表示信息短缺 不知道 未提供 Null 算术运算为 Null 逻辑运算 True(1) False(0) Unknownl(1/2) and( 取小 ) or( 取大 ) not(1-x) 判测 字段名 Is Null/Is Not Null

53 单表查询 选择若干元组 找出所有不需要先修课的课程 Select * From course Where cpno is null 找出所有提供了年龄的学生 Select * From student Where sage is not null

54 单表查询 结果集的排序 对查询结果进行排序 ORDER BY 子句 ASC/DESC 子句中指明列名 / 列号 需要临时表空间的支持, 耗时

55 单表查询 例 : 生成 CS 系学生的名册 ( 要求包含学号 姓名 年龄, 依次按照年龄 姓名排序 ) Select sno,sname,sage From student Where sdept = CS Order by sage,sname

56 单表查询 集函数 主要用于数据的统计计算 COUNT SUM AVG MAX MIN

57 单表查询 COUNT(*) 行数 COUNT(distinct 字段名 ) 指定字段中的不同取值的个数 NULL 被忽略 不计

58 单表查询 SUM/AVG 对数值型字段进行计算 NULL 不参与 不计入 MAX/MIN 可以是数值 字符串 日期 大小 字符码之大小 早晚 NULL 不参与

59 单表查询 结果分组 GROUP BY 子句 将结果表按一列或多列的值进行分组, 值相等的为一组 便于按分组的形式进行信息的统计 大部分 DBMS 要求 Group By 中的项, 必须出现在 Select 子句中

60 单表查询 例 : 计算各系的学生人数 Select sdept, count(*) From student Group by sdept; 例 : 计算各系女生的人数 Select sdept, count(*) From student Where ssex= f Group by sdept;

61 单表查询 分组的筛选 HAVING 子句 对分组后的结果表, 按各组的统计值进行筛选, 符合条件的组就是最后结果表中的元组 以集函数作为表达式的主体 Having COUNT(*) >= 50

62 单表查询 例 : 找出学生人数超过 50 人的系以及人数 Select sdept, count(*) From student Group by sdept Having count(*)>50

63 Where 与 Having 的区别 Where 作用于基本表, 从中选出符合条件的行 Having 作用于组, 从中选出符合条件的组

64 查询语句 小结 # Select From Where Group By Having Order By

65 课堂练习 建立第二章习题 5 中的 4 个表 求供应工程 J1 零件 P1 的供应商号码 SNO 求供应工程 J1 零件为红色的供应商号码 SNO

66 查询 多表查询 数据来自多表, 查询涉及两个或以上 的表, 必须将多个表进行连接 广义连接 将相关的表进行组合 所有行进行组合, 字段拼接, 行交叉组合 一般无意义

67 多表查询 条件连接 在广义连接的结果中, 施加条件, 加以选择, 留下符合要求的元组 条件连接 自然连接 自身连接 外 连接

68 多表查询 条件连接 例 : 列出每个学生及其选修课程的情况 学生情况 student 选修课程情况 SC Select student.*,sc.* From student,sc Where student.sno = SC.sno

69 多表查询 自身连接 例 : 列出每门课程的间接先修课程号 Select first.cno,second.cpno From course first,course second Where first.cpno = second.cno

70 多表查询 自身连接 例 : 列出年龄比 大的学生的学号 姓名 Select s1.sno, s1.sname From student s1, student s2 Where s2.sno= AND s1.sage>s2.sage;

71 多表查询 自然连接 参与连接的表, 在某些公共属性上具有相同值的元组 必须具有相同的属性列 在公共属性列上的等值连接

72 多表查询 自然连接 例 : 列出选修 C01 课程的学生的名册 Select sno,sname From student, SC Where student.sno = SC.sno and cno = C01

73 多表查询 外连接 不匹配连接, 含有 NULL 信息的处理 例 : 列出学生及其选课情况 主表, 完全 从表, 不完全

74 多表查询 外连接 例 : 列出学生及其选课情况 所有的学生情况以及他们的选课情况 ( 含未选课 ) Select student.*,sc.cno,sc.grade From student left outer join SC

75 多表查询 外连接

76 多表查询 外连接 主要用于主表 - 从表之间信息短缺的处理 注意 不同的数据库产品有不同的方言, 甚至不支持

77 嵌套查询 查询块 Select-From-Where 嵌套查询 (SubQuery) 查询块的 Where 或 Having 中含有另一个查询块

78 嵌套查询 一个简单例子 找出与 同岁的学生学号及姓名 的年龄? Select sage From student Where sno = 与上述年龄相等的学生? Select sno,sname From student Where sage = (Select sage From student Where sno = )

79 嵌套查询 带 IN 的子查询 子查询返回结果集 ( 多行 单列 ) 主查询与子查询之间由 IN 连接 几乎所有的 DBMS 均支持多行单列的 IN 操作 有些 DBMS 支持多行多列的 IN 操作 需要了解 DBMS 的性能

80 嵌套查询 带 IN 的子查询 例 : 找出选修数据库课程的学生的学号 数据库课程 course 表 cname Like % 数据库 % 获得相应课程的课程号 选修课程信息 SC 表 由 cno 查询出相应的选课学号

81 嵌套查询 带 IN 的子查询 Select sno From SC Where cno IN (Select cno From course Where cname Like % 数据库 % )

82 嵌套查询 带 IN 的子查询 例 : 找出选修数据库课程的学生的学号 姓名 数据库课程 course 表 cname Like % 数据库 % 获得相应课程的课程号 选修课程信息 SC 表 由 cno 查询出相应的选课学号 学生的姓名 STUDENT 表 由 sno 查得相应的姓名

83 嵌套查询 带 IN 的子查询 Select sno,sname From student Where sno IN ( Select sno From SC Where cno IN ( Select cno From course Where cname Like % 数据库 % ))

84 嵌套查询 带 IN 的子查询 表达查询最自然的方式 将复杂的查询切分成若干块, 逐个解决 Order By 只能作用于主查询

85 嵌套查询 带比较的子查询 子查询与主查询之间由比较运算符相连接 单值 ( 单行 单列 ) 直接使用比较运算符 > < = >= <=!= 等 多值 ( 多行 单列 ) 比较运算符和 ANY/ALL 连用

86 嵌套查询 带比较的子查询 例 : 找出年龄比 学生大的所有学生的学号 姓名 的年龄 年龄比他大的学生 Select sno,sname From student Where sage > (Select sage From student Where sno = )

87 嵌套查询 带 ANY/SOME/ALL 的子查询 当子查询返回多值结果集 ( 多行单列 ) 时, 比较操作须由比较运算符以及 ANY/ ALL ANY/SOME 子查询结果集中的某一个 ALL 子查询结果集中的所有

88 嵌套查询 带 ANY/SOME/ALL 的子查询 例 : 找出比最小年龄大的学生 学生年龄 Select sage From student 不是最小年龄 >some 学生年龄 Select * From student Where sage >some (Select sage From student)

89 嵌套查询 带 ANY/SOME/ALL 的子查询 例 : 找出年龄最小的学生 学生年龄 Select sage From student 最小年龄 <=All 学生年龄 Select * From student Where sage <=All (Select sage From student)

90 嵌套查询 带 Exists 的子查询 表示存在, 判断子查询的返回结果集是否为空集 子查询结果集为空集 :False 子查询结果集含有元组 :True 子查询只需使用 Select *

91 嵌套查询 带 Exists 的子查询 例 : 列出至少选修一门课程的学生的学号 姓名 Select sno,sname From student Where Exists ( Select * From SC Where student.sno = SC.sno )

92 嵌套查询 相关子查询 内层子查询的条件引用外层主查询的某些属性 在 Exists 运算中, 更多到涉及到相关子查询的使用 前例便是相关子查询的典型使用

93 嵌套查询 相关子查询 找出与 在同一个系的学生 Select * From student st1 Where Exists ( Select * From student st2 Where st2.sdept = st1.sdept and st2.sno = )

94 嵌套查询 相关子查询 查询没有选修 1 号课程的学生姓名 Select sname From student Where Not Exists ( Select * From SC Where student.sno = SC.sno and SC.cno = 1 )

95 嵌套查询 相关子查询 查询选修了所有课程的学生姓名 Select sname 不存在这样的一门课程, 这个学生没有选修 From student Where Not Exists ( Select * From Course Where Not Exists ( Select * From SC Where SC.sno = student.sno and SC.cno = course.cno))

96 集合查询 并 交 差集合操作 对多个查询的结果集实施集合操作 属性必须相容 ORDER BY 只能施加在整个结果集中 并不是所有的 DBMS 均支持三个操作

97 集合查询 查询计算机系以及数学系的学生 Select * From student Where sdept = CS Union Select * From student Where sdept = IS ;

98 集合查询 列出雇员和客户的姓名 可以来自不同的表 只要属性相容 ( 列数和数据类型相同 ) 即可 Select emp_name From employee Union Select cus_name From customer

99 3.4 数据更新 增 (Insert) 删 (Delete) 改 (Update)

100 数据更新 增 Insert 插入单行 按关系模式的属性顺序 Insert Into Student Values ( 95001, 张三, M,27, CS ) 按指定的属性顺序, 也可以只添加部分属性 ( 非 Null 属性为必需 ), 其余属性值为 NULL Insert Into Student ( sno, sname, sage) Values ( 95002, 李四,26 )

101 数据更新 增 Insert 插入多行 将子查询的结果插入数据库表中 Insert Into < 表名 > [ 属性列表 ] 子查询 Insert into stu_cs Select * From student Where sdept = CS

102 数据更新 删除 Delete 删除符合条件的元组 ( 使得表达式为真的元组 ) Delete From Student Where sage <=15 Delete From Student Where sage = (Select sage From student Where sno = )

103 数据更新 删除 Delete 删除所有元组 Delete From Student

104 数据更新 删除 Delete 不能同时对多表进行删除操作

105 数据更新 修改 Update 格式 update < 表名 > Set < 列名 >=< 表达式 >[, ] Where < 条件表达式 >

106 数据更新 修改 Update 修改符合条件的元组 对使得条件表达式成立的元组的相应属性进行修改 例 : 将所有学号前缀为 95 的学生转至 IS 系 Update student set sdept = IS Where sno Like 95%

107 数据更新 修改 Update 子查询在 UPDATE 中的使用 作为 Where 子句中的子查询 作为 Set 子句中的新值 例 : 将男生的年龄改为所有学生的平均年龄 Update student set sage = (Select AVG(sgae) From student) Where ssex = M 仅作举例

108 数据更新 修改 Update Update 不能对多表进行修改

109 数据更新 异常 数据更新, 可能引起参照完整性的违约 从表 在从表中插入外码值 在从表中修改外码值 主表 在主表中修改主码 ( 已被参照 ) 在主表中删除元组 ( 已被参照 )

110 数据更新 异常 级联操作 删除主表元组, 级联删除从表的参照元组 修改主表元组, 级联修改从表中的外码值 禁止操作 不得更改已具有参照关系的数据 Set Null 使得从表的外码被置为 Null Set Default 使得从表的外码被置为其缺省值

111 Creating Foreign Keys Example CREATE TABLE department (dept_id INTEGER NOT NULL, dept_name CHAR ( 20 ), dept_id_head INTEGER, FOREIGN KEY (dept_id_head) REFERENCES employee ON UPDATE CASCADE ON DELETE SET NULL);

112 Modifying and Deleting Foreign Keys Use ALTER TABLE to delete the original key and add the new definition ALTER TABLE department DELETE FOREIGN KEY id_fkey; ALTER TABLE department ADD FOREIGN KEY id_fkey (dept_id_head) REFERENCES employee (emp_id) ON UPDATE CASCADE ON DELETE CASCADE);

113 3.5 视图 (View) 用户模式 外模式 视图是一个虚表, 是从一个或几个基本表导出的表 DB 中存放的是视图的定义 ( 数据来源于基本表的查询 )

114 视图层 视图 (View)

115 视图 一个例子 Create View stu_cs as Select * From student Where sdept = CS ; Select sno,sname From stu_cs 将对视图的操作, 转换成基本表的操作 Select sno,sname From student Where sdept = CS

116 视图 一个例子 Create View stu_cs(no,name) as Select sno,sname From student Where sdept = CS ; Select * From stu_cs 将对视图的操作, 转换成基本表的操作 Select sno,sname From student Where sdept = CS

117 视图 格式 Create View < 视图名 > [(< 列名 >,...)] As < 子查询 >

118 视图 查询 在查询中, 视图基本可以作为基本表使用 大部分 DBMS 不支持聚集函数字段的视图

119 视图 更新 来自单表的基本属性可以修改 来自多表的视图不得更新 计算属性构成的视图, 不得修改 视图中的元组非自身元组 属性均不得改

120 视图 更新 ( 实例 ) INSERT INTO Stu_cs Values ( , 张三 ) 系统转换为 : INSERT INTO student (sno, sname, sdept) Values ( , 张三, CS )

121 视图 作用 安全 简便 数据独立性

122 本章小结 关系数据库中, 如何进行 数据定义 数据更新 数据查询

123 课堂练习 假设已创建数据库 : 供应商表 S(SNO,SNAME,STATUS,CITY) 零件表 P(PNO,PNAME,COLOR,WEIGHT) 项目表 J(JNO,JNAME,CITY) 三者关系表 SPJ (SNO,PNO,JNO,QTY) 查找 : 1. 供应工程 J1 零件为红色的供应商号码 SNO 2. 找出上海厂商供应的所有零件的代码 从供应商关系中删除 S2 记录, 并从供应关系中删除相应的记录

124 课堂练习 ( 难一些 ) 查询这样的工程项目号 : 供给该工程项目的零件 P1 的平均供应量大于供给工程项目 J1 的任何一种零件的最大供应量. 定义一个视图, 它由所有具有这种特点的工程项目 ( 项目号, 所在城市名称 ) 所组成 : 它们由供应商 S1 供货且使用零件 P1.

125 系统演示 使用 sqlite 开源软件, 命令行方式 创建一个学生数据库, 有学生表, 课程表和学生选修表 插入数据 查询操作 更新操作