数据库系统概论 An Introduction to Database Systems

1. 数据库系统概论An Introduction to Database Systems 黔 南 民 族 师 范 学 院 计 算 机 科 学系 主讲：钟志宏 E-mail：zzhstudio@126.com An Introduction to Database Systems

2. 黔南民族师范学院计算机科学系 数据库系统概论 An Introduction to Database System 第三章 关系数据库标准语言SQL An Introduction to Database Systems

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

4. 3.1 SQL概述 SQL（Structured Query Language） 结构化查询语言，是关系数据库的标准语言 • SQL的特点： • 综合统一 • 高度非过程化 • 面向集合的操作方式 • 以同一种语法结构提供多种使用方式 • 语言简洁，易学易用 An Introduction to Database Systems

5. 3.1.2 SQL的特点 • 1.综合统一 • 集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。 • 可以独立完成数据库生命周期中的全部活动： • 定义关系模式，插入数据，建立数据库； • 对数据库中的数据进行查询和更新； • 数据库重构和维护 • 数据库安全性、完整性控制等 • 2.高度非过程化 • SQL只要提出“做什么”，无须了解存取路径。 存取路径的选择以及SQL的操作过程由系统自动完成。 An Introduction to Database Systems

6. 3.1.2 SQL的特点 • 3.面向集合的操作方式（以关系代数为基础） • 操作对象、查找结果是元组的集合 • 一次插入、删除、更新操作的对象也是元组的集合 • 4.以同一种语法结构提供多种使用方式 • SQL的独立性：能够独立地用于联机交互的使用方式 • SQL的嵌入性：SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用 An Introduction to Database Systems

7. 3.1.2 SQL的特点 • 5.语言简洁，易学易用 • SQL功能极强，完成核心功能只用了9个动词。 An Introduction to Database Systems

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

9. 3.2 学生-课程 数据库 • 学生-课程模式 S-T : 学生表：Student(Sno,Sname,Ssex,Sbirthday,Sage,Sdept) 课程表：Course(Cno,Cname,Cpno,Ccredit) 学生选课表：SC(Sno,Cno,Grade) • 模式上的完整性（实体、参照、用户定义）: student：主键sno，性别只能取‘男’、‘女’，出生日期与年龄之间存在一致关系 course：主键cno sc：主键(sno,cno)，同时sno、cno作为外键，成绩在0~40之间 An Introduction to Database Systems

10. Student表 An Introduction to Database Systems

11. Course表 An Introduction to Database Systems

12. SC表 An Introduction to Database Systems

13. 3.3 数据定义 SQL的数据定义: 模式定义、表定义、视图和索引的定义 An Introduction to Database Systems

14. 3.3 数据定义 • 3.3.1 模式的定义与删除 • 3.3.2 基本表的定义、删除与修改 • 3.3.3 索引的建立与删除 An Introduction to Database Systems

15. 一、定义模式 • 模式定义基本语法： CREATE SCHEMA<模式名>AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>] • 如果没有指定<模式名>，那么<模式名>隐含为<用户名> • 定义模式时，可以定义模式中所包括的表、视图、授权等 • 定义模式实际上定义了一个命名空间，空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。 An Introduction to Database Systems

16. 一、定义模式 [例] CREATE SCHEMA TESTAUTHORIZATIONZHANG CREATE TABLE TAB1(COL1 SMALLINT， COL2 INT， COL3 CHAR(20)， COL4 NUMERIC(10，3)， COL5 DECIMAL(5，2) )； 为用户ZHANG创建了一个模式TEST，并在其中定义了一个表TAB1。 An Introduction to Database Systems

17. 二、删除模式 • 删除模式基本语法： DROP SCHEMA <模式名> <CASCADE|RESTRICT> CASCADE(级联) 删除模式的同时把该模式中所有的数据库对象全部删除 RESTRICT(限制) 如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行，当该模式中没有任何下属的对象时才能执行。 [例] DROP SCHEMA ZHANG CASCADE； 删除模式ZHANG，同时该模式中定义的表TAB1也被删除 An Introduction to Database Systems

18. SQL2005中模式的说明 模式的定义在不同的数据库系统产品中有着不同的支持和意义。 • SQL2005同时支持模式和数据库的定义，但用户主要以定义数据库语法为主（CREATE DATABASE ） • ORACLE和DB2同样同时支持模式和数据库的定义。 • MYSQL则将模式和数据库的定义合而为一，仅支持CREATE DATABASE 语句。 An Introduction to Database Systems

19. 数据库定义补充 • SQL2005数据库定义语法： CREATE DATABASE database_name • SQL2005有三种类型的文件用来存储数据库： • 主文件(.mdf)包含数据库的启动信息，还可以用来存储数据。每个数据库都包含一个主文件。 • 次要文件(.ndf)保存所有主要数据文件中容纳不下的数据。如果主文件大到足以容纳数据库中的所有数据，就不需要有次要数据文件。而另一些数据库可能非常大，需要多个次要数据文件，也可能使用多个独立磁盘驱动器上的次要文件，以将数据分布在多个磁盘上。 • 事务日志文件(.ldf)保存用来恢复数据库的日志信息。每个数据库必须至少有一个事务日志文件（尽管可以有多个）。事务日志文件最小为 512 KB。 • 每个数据库至少有两个文件，一个主文件和一个事务日志文件。 An Introduction to Database Systems

20. 数据库定义示例1 CREATE DATABASE st ON PRIMARY ( NAME = st1, FILENAME = ‘C:\stdat1.mdf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = st2, FILENAME = ‘D:\stdat2.ndf', SIZE = 100MB, FILEGROWTH = 20), ( NAME = st3, FILENAME = ‘E:\stdat3.ndf', SIZE = 100MB, FILEGROWTH = 20) LOG ON ( NAME = stlog1, FILENAME = ‘F:\stlog1.ldf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = stlog2, FILENAME = ‘G:\stlog2.ldf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) 说明：示例使用三个 数据文件和两个 100 事务日志文件创建了名为 st 的数据库。 主文件是列表中的第一个文件，并使用 PRIMARY 关键字显式指定。 日志文件在 LOG ON 关键字后指定。 An Introduction to Database Systems

21. 数据库定义示例2 不指定文件创建数据库，示例创建名为 mytest 的数据库，并创建相应的主文件和事务日志文件。因为没有指定 MAXSIZE，文件可以增长到填满所有可用的磁盘空间为止。 CREATE DATABASE mytest An Introduction to Database Systems

22. 数据库的删除 • 语法：DROP DATABASE database_name • 除去单个数据库 DROP DATABASE st • 除去多个数据库 DROP DATABASE st, mytest • 注意 • 使用DROP DATABASE，连接数据库上下文必须在 master 数据库 • 只有通过还原备份才能重新创建已除去的数据库。任何时候除去数据库，都应备份 master 数据库。 • 无法除去系统数据库（msdb、model、master、tempdb）。 An Introduction to Database Systems

23. 3.3.2 基本表的定义、删除与修改 • 表创建的基本步骤和内容： • 表名 • 字段名 • 字段数据类型 • 完整性定义 • 实体完整性（主键） • 参照完整性（外键） • 用户自定义完整性（CHECK） • 创建索引 An Introduction to Database Systems

24. 数据类型 • SQL中域的概念用数据类型来实现 • 不同的DBMS其数据类型也不完全相同 • 选用哪种数据类型 • 取值范围 • 要做哪些运算 总体要求：存储空间越小越好、查询效率越高越好 An Introduction to Database Systems

25. SQL2005中的数据类型 整型： Bigint 从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型。 Int 从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字）。 Smallint 从 -2^15 (-32,768) 到 2^15 - 1 (32,767) 的整数数据。 Tinyint 从 0 到 255 的整数数据。 bit型： Bit1 或 0 的整数数据。 decimal 和 numeric两者相同： Decimal从 -10^38 +1 到 10^38 –1 的固定精度和小数位的数字数据。 An Introduction to Database Systems

26. SQL2005中的数据类型 近似数字： Float从 -1.79E + 308 到 1.79E + 308 的浮点精度数字。 Real从 -3.40E + 38 到 3.40E + 38 的浮点精度数字。 时间日期： Datetime从 1753 年 1 月 1 日到 9999 年 12 月 31 日，精确到秒 Smalldatetime从 1900 年 1 月 1 日到 2079 年 6 月 6 日的，精确到分钟 二进制字符串： Binary固定长度的二进制数据，其最大长度为 8,000 个字节。 Varbinary可变长度的二进制数据，其最大长度为 8,000 个字节。 Image可变长度的二进制数据，其最大长度为 2^31 - 1 (2,147,483,647) 个字节。 An Introduction to Database Systems

27. SQL2005中的数据类型 字符串： Char 固定长度的非 Unicode 字符数据，最大长度为 8,000 个字符。 Varchar 可变长度的非 Unicode 数据，最长为 8,000 个字符。 Text 可变长度的非 Unicode 数据，最大长度为 2^31 - 1 (2,147,483,647) 个字符。 Unicode 字符串： Nchar 固定长度的 Unicode 数据，最大长度为 4,000 个字符。 Nvarchar可变长度 Unicode 数据，其最大长度为 4,000 字符。 Ntext 可变长度 Unicode 数据，其最大长度为 2^30 - 1 (1,073,741,823) 个字符。 An Introduction to Database Systems

28. 3.3.2 基本表的定义、删除与修改 一、定义基本表 CREATE TABLE <表名> （<列名> <数据类型>[ <列级完整性约束条件> ] [，<列名> <数据类型>[ <列级完整性约束条件>] ] … [，<表级完整性约束条件> ] ）； 如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。 An Introduction to Database Systems

29. 学生-课程 数据库 • 学生-课程模式 S-T : 学生表：Student(Sno,Sname,Ssex,Sbirthday,Sage,Sdept) 课程表：Course(Cno,Cname,Cpno,Ccredit) 学生选课表：SC(Sno,Cno,Grade) • 模式上的完整性（实体、参照、用户定义）: student：主键sno，性别只能取‘男’、‘女’，出生日期与年龄之间存在一致关系 course：主键cno sc：主键(sno,cno)，同时sno、cno作为外键，成绩在0~100之间 An Introduction to Database Systems

30. 学生表Student [例] 建立“学生”表Student。 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(20) UNIQUE， Ssex CHAR(2) CHECK (Ssex IN (‘男’，‘女’) ) default(‘男’)， Sbirthday DATETIME, Sage SMALLINT NOT NULL， Sdept CHAR(20), CONSTRAINT C1 CHECK (year(getdate())-year(sbirthday)=Sage) )； An Introduction to Database Systems

31. 完整性约束命名子句 • CONSTRAINT 约束子句 CONSTRAINT <完整性约束条件名> [PRIMARY KEY短语 |FOREIGN KEY短语 |CHECK短语] 通过对完整性进行命名，就可以通过名字对完整性进行操作，可以独自对表的完整性进行添加、删除和修改。 An Introduction to Database Systems

32. 课程表Course [例] 建立一个“课程”表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 An Introduction to Database Systems

33. 学生选课表SC [例] 建立一个“学生选课”表SC CREATE TABLE SC ( Sno CHAR(9)， Cno CHAR(4)， Grade SMALLINT check(grade BETWEEN 0 AND 100)， PRIMARY KEY (Sno，Cno)， CONSTRAINT sc_pk PRIMARY KEY (Sno，Cno)， FOREIGN KEY (Sno) REFERENCES Student(Sno) ON DELETE CASCADE ON UPDATE CASCADE， FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE CASCADE ON UPDATE CASCADE， ); An Introduction to Database Systems

34. 二、表的修改 • 基本语法 ALTER TABLE <表名> [ ADD <新列名> <数据类型> [ 完整性约束 ] ] [ DROP <列名> <完整性约束名> ] [ ALTER COLUMN<列名> <数据类型> ]； An Introduction to Database Systems

35. 修改基本表 [例1]向Student表增加列：入学时间（日期）、身份证号（字符串）。 ALTER TABLE Student ADD Sentrance DATE, SID char(18)； [例2]从Student表删除列：入学时间、身份证号。 ALTER TABLE Student DROP Sentrance, SID ； [例3]修改字段数据类型：将年龄的数据类型改为整数。 ALTER TABLE Student ALTER COLUMN Sage INT； [例4]添加约束条件：要求本科入学学生年龄小于40岁。 ALTER TABLE Student ADD CONSTRAINT C3 CHECK (Sage < 40)； [例5]删除约束条件：删除对本科入学学生年龄的约束。 ALTER TABLE Student DROP CONSTRAINT C3； An Introduction to Database Systems

36. 域中的完整性限制 SQL支持域的概念，并可以用CREATE DOMAIN语句建立一个域以及该域应该满足的完整性约束条件。 [例]建立一个性别域，并声明性别域的取值范围 CREATE DOMAIN GenderDomain CHAR(2) CONSTRAINT GD CHECK ( VALUE IN ('男'，'女') ); 则前例中对Ssex的说明可以改写为 Ssex GenderDomain 说明：这一功能在SQL2005中通过规则rule来实现 An Introduction to Database Systems

37. 删除基本表 • 基本语法： DROP TABLE <表名>［RESTRICT| CASCADE］； • RESTRICT：删除表是有限制的。 • 欲删除的基本表不能被其他表的约束所引用，如果存在依赖该表的对象（索引、视图、触发器），则此表不能被删除 • CASCADE：删除该表没有限制。 • 在删除基本表的同时，相关的依赖对象一起删除 [例] 删除Student表 DROP TABLE Student CASCADE ; An Introduction to Database Systems

38. 删除基本表 DROP TABLE时，SQL99 与 3个RDBMS的处理策略比较 R表示RESTRICT , C表示CASCADE '×'表示不能删除基本表，'√'表示能删除基本表，‘保留’表示删除基本表后，还保留依赖对象 An Introduction to Database Systems

39. 3.3.3 索引的建立与删除 • 建立索引的目的：加快查询速度 • 谁可以建立索引 • DBA 或 表的属主（即建立表的人） • DBMS一般会自动建立以下列上的索引 PRIMARY KEY UNIQUE • 维护索引——DBMS自动完成 • 使用索引 DBMS自动选择是否使用索引以及使用哪些索引 An Introduction to Database Systems

40. 索 引 • RDBMS中索引一般采用B+树、HASH索引来实现 • B+树索引具有动态平衡的优点 • HASH索引具有查找速度快的特点 • 采用B+树，还是HASH索引 则由具体的RDBMS来决定 • 索引是关系数据库的内部实现技术，属于内模式的范畴 An Introduction to Database Systems

41. 一、建立索引 • 语句格式 CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)； • 唯一索引：UNIQUE • 聚簇索引：CLUSTER • 次序：ASC（升序），DESC（降序） An Introduction to Database Systems

42. 建立索引 [例1] CREATE CLUSTER INDEX Stusname ON Student(Sname)； • 在最经常查询的列上建立聚簇索引以提高查询效率 • 一个基本表上最多只能建立一个聚簇索引 • 经常更新的列不宜建立聚簇索引 [例2]为学生-课程数据库中的Student，Course，SC三个表建立索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno)； CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC) An Introduction to Database Systems

43. 二、删除索引 • 基本语法：DROP INDEX <索引名> 删除索引时，系统会从数据字典中删去有关该索引的描述。 [例3] 删除Student表的Stusname索引 DROP INDEX Stusname An Introduction to Database Systems

44. 3.4 数 据 更 新 3.4.1 插入数据 3.4.2 修改数据 3.4.3 删除数据 An Introduction to Database Systems

45. 3.5.1 插入数据 • 两种插入数据方式 1. 插入元组 2. 插入子查询结果：可以一次插入多个元组 An Introduction to Database Systems

46. 一、插入元组 • 语句格式 INSERT INTO <表名> [(<属性列1>[，<属性列2 >…)] VALUES (<常量1> [，<常量2>] … ) • INTO子句 • 属性列的顺序可与表定义中的顺序不一致 • 没有指定属性列 • 指定部分属性列 • VALUES子句 • 提供的值必须与INTO子句匹配（值的个数、值的类型） An Introduction to Database Systems

47. 插入元组 [例1] 将一个新学生元组插入到Student表中。 学生表关系模式：Student(Sno,Sname,Ssex,Sage,Sdept) INSERT INTO Student (Sno，Sname，Ssex，Sdept，Sage) VALUES ('200215128'，'陈冬'，'男'，'IS'，18) 也可如下插入该元组： INSERT INTO Student VALUES ('200215128'，'陈冬'，'男'，18 ，'IS') 即如果插入值的顺序与关系模式中定义一致，可以不输入属性列表。 An Introduction to Database Systems

48. 插入元组 [例2]插入一条选课记录( ‘200215128’，‘1 ’)。 学生选课表关系模式：SC(Sno,Cno,Grade) INSERT INTO SC(Sno，Cno) VALUES (‘ 200215128 ’，‘ 1 ’) RDBMS将在新插入记录的Grade列上自动地赋空值。 或者： INSERT INTO SC VALUES (' 200215128 '，' 1 '，NULL) An Introduction to Database Systems

49. 二、插入子查询结果 将子查询结果插入指定表中，即将多条记录插入数据表中 • 语句格式 INSERT INTO <表名> [(<属性列1> [，<属性列2>… )] 子查询； • 子查询 • SELECT子句目标列必须与INTO子句匹配(值的个数及类型) An Introduction to Database Systems

50. 插入子查询结果 [例]对每一个系，求学生的平均年龄，并把结果存入数据。 第一步：建表 CREATE TABLE Dept_age (Sdept CHAR(15) /* 系名*/ Avg_age SMALLINT) /*学生平均年龄*/ 第二步：插入数据 INSERT INTO Dept_age(Sdept，Avg_age) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept An Introduction to Database Systems