山东理工大学计算机学院

1. 山东理工大学计算机学院 数据库系统概论 An Introduction to Database System 第三章 关系数据库标准语言SQL An Introduction To Database System

2. 课前思考 • 关系数据库的组织结构 • 关系数据模型和关系数据库的术语 • 关系数据库管理系统的基本功能 • 关系数据模型的完整性约束 An Introduction To Database System

3. 学习目标 • 掌握SQL语言的基本功能 • 使用SQL语言产生和修改数据库表 • 使用SQL语言操纵数据库数据 • 掌握嵌入SQL语言的使用 • 掌握触发器和存储过程的使用 An Introduction To Database System

4. 学习指南 • 通过SQL Server或VFP，练习SQL各种语句的操作 • 通过创建自己的数据库和表练习数据库和表的操作 • 利用SQL Server提供的查询分析器或VFP实现所有的SQL语句操作，并把操作语句保留在一个文件中，作为一次实验报告提交 An Introduction To Database System

5. 难点重点 • 掌握SQL语句的各种基本操作语句的格式和使用方法，包括带有各种集合函数、分组排序的查询操作 • 嵌入式SQL中游标的使用方法 • 视图、触发器、存储过程的功能和使用方法 • 给定关系表和查询要求，会给出正确的SQL语句和关系代数表达式 An Introduction To Database System

6. 第三章关系数据库标准语言SQL 3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL 3.8 小结 An Introduction To Database System

7. 3.1 SQL概述—发展 • SQL是结构化查询语言(Structured Query Language)的缩写,是关系数据库的标准语言 • SQL是1975－1979年在IBM的关系数据库SYSTEM R上实现的语言。它提供给用户一种表示方法说明要查询的结果特性，至于如何查询、以及查询结果的形式都由DBMS来完成 • SQL名为结构化查询语言, 实际功能包括数据定义、数据查询、数据操纵和数据控制。目前新的SQL标准是1992年制定的SQL-92，简称SQL2 • SQL标准的制定使得几乎所有的数据库厂家都采用SQL语言作为其数据库语言。但各家又在SQL标准的基础上进行扩充，形成自己的语言。SQL Server扩充SQL标准称为Transact-SQL，简称T-SQL。 An Introduction To Database System

8. SQL语言特点 • SQL的特点 作为关系数据库的标准语言，该语言具有以下特点： • 1. 语言功能的一体化 • 2.模式结构的一体化 • 3. 高度非过程化的语言 • 4. 面向集合的操作方式 • 5.两种使用方式、统一的语法结构 • 6. 语言简洁，易学易用 An Introduction To Database System

9. 5. 语言简捷，易学易用 An Introduction To Database System

10. 第三章关系数据库标准语言SQL 3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL 3.8 小结 An Introduction To Database System

11. SQL语言的数据类型 • Char(n):长度为n的定长字符串型 • Varchar(n):最大长度为n的变长字符串型 • Int：4字节的整型数 • Real：4字节的浮点数 • Date：日期型 An Introduction To Database System

12. SQL Server的数据库文件 • 数据库是SQL Server用于存储对象（如表、索引、触发器、视图、存储过程等）的逻辑实体。SQL Server的数据库由一套操作系统文件组成。数据库中的所有对象都存储在这些操作系统文件中。这些文件可以组成一个文件组，称为数据库文件组。 • 使用的操作系统文件包括主数据文件、辅助数据文件、日志文件三大类 An Introduction To Database System

13. 主数据文件 • 存储指向其他数据库文件的指针和启动信息。每个数据库必须包含一个也只能包含一个主数据文件。主数据文件的扩展名是.mdf An Introduction To Database System

14. 辅助数据文件 • 所有不适合存储在主数据文件中的数据都存储在辅数据文件中。如果主数据文件可以包含所有的数据，可不用辅助数据文件。大规模的数据库可以有分布在多个磁盘驱动器上的辅助数据文件。辅助文件的扩展名为.ndf An Introduction To Database System

15. 日志文件 • 存储数据库的事务日志信息。必要时利用日志恢复数据库。每个数据库至少需要一个日志文件，日志文件的扩展名为.ldf。SQL Server通常是先写日志再进行数据库修改。因此如果数据库系统崩溃，只要保留有完整的日志文件，仍然可以完成对数据库的修复和重建 An Introduction To Database System

16. 注意问题 • 由于数据文件是实际存放数据、索引等数据库对象的地方，因此建立数据库时要认真考虑实际使用中对数据量的需求，以便为将来增加数据库对象存放提供更大的空间。当然，数据文件也不宜设置过大，巨大的数据文件会让SQL Server花费大量空间管理，从而降低使用效率。 An Introduction To Database System

17. 数据存储单位 • SQL Server 数据库文件按照系统定义的存储单位调整文件的大小，SQL Server 数据库使用的数据存储单位有：(1) 存储页（page）：是SQL Server的基本数据存储单位，每页8KB(2) 范围（extent）：一个范围是8个连续的存储页，是存储表或索引的基本单位。(3) SQL Server可以自动调整数据库和日志文件的大小。(4) 划分文件组：SQL Server把数据库文件在逻辑上分成组，把相关文件放入特定文件组中。文件组的类型分为：· 缺省组：存放主文件和其他文件。· 用户定义的组：由Create命令中的File Group命令定义的数据文件 An Introduction To Database System

18. 创建数据库 • 在SQL Server中创建数据库有三种途径：　　①用企业管理器创建数据库；　　②用创建数据库向导创建数据库；　　③用Transact_SQL语句创建数据库 An Introduction To Database System

19. 用T-SQL语句创建 • CREATE DATABASE <数据库名称>[ ON [PRIMARY][ <filespec>[,…n] ][, <filegroup>[,…n] ] ] • [ LOG ON { <filespec> [,…n] } ]<filespec>:: = ( [ NAME = logical_file_name,]FILENAME = 'os_file_name'[,SIZE = size][,MAXSIZE = {max_size|UNLIMITED}][,FILEGROWTH = growth_increment]) [,…n] • <filegroup>:: = FILEGROUP filegroup_name <filespec>[,…n] An Introduction To Database System

20. 删除数据库 • （1）用企业管理器删除数据库：展开数据库服务器上的数据库节点，选择要删除的数据库，从快捷菜单中选择“删除”命令，并确认即可。 • （2）使用SQL语句删除数据库：　删除数据库的语法格式：DROP DATABASE database_name[1,….n] An Introduction To Database System

21. 注意问题 • 如果要删除数据库，该数据库必须存在于数据库服务器中。 • 无法除去系统数据库（msdb、model、master、tempdb）。 • 在删除某数据库之前，确保该数据库的"ReadOnly"选项被置为"False"。 • 只有数据库拥有者和sysadmin服务器角色的成员可以执行删除数据库的操作。 • 在删除了数据库之后，其中的所有数据库对象和数据都会被删除，所有日志文件和数据文件也会被删除，释放空间给操作系统。 An Introduction To Database System

22. 修改数据库 1.数据库改名 使用系统存储过程sp_renamedb可以修改数据库的名称，语句格式如下： Sp_renamedb <旧名字>，<新名字> 注意： （1）只有属于sysadmin服务器角色的成员可以给数据库更名 （2）在给数据库更名前，必须在企业管理器中设置该数据库为单用户状态。在单用户状态下，只能有一个用户使用该数据库，因此，这时不能同时打开企业管理器和查询分析器 An Introduction To Database System

23. 3.2 数 据 定 义 An Introduction To Database System

24. 3.2.1 定义语句格式 CREATE TABLE <表名> （<列名> <数据类型>[ <列级完整性约束条件> ] [，<列名> <数据类型>[ <列级完整性约束条件>] ] … [，<表级完整性约束条件> ] ）； • <表名>：所要定义的基本表的名字 • <列名>：组成该表的各个属性（列） • <列级完整性约束条件>：涉及相应属性列的完整性约束条件 • <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件 An Introduction To Database System

25. CREATE TABLE 表名　　　（　　　列名 数据类型[缺省值][NOT NULL][,列名 数据类型[缺省值][NOT NULL]]….[，UNIQUE（列名[，列名]….）][，PRIMARY KEY（列名[，列名]…）][，FOREIGN KEY （列名[，列名]…）REFERENCES 表名（列名[，列名]…）][,CKECK (条件)]) An Introduction To Database System

26. 参数说明 • 参数说明： 缺省值(DEFAULT):指定列的缺省值； NOT NULL：列值不能为空值； UNIQUE：取值唯一的列名； PRIMARY KEY：主关键字（主码）列名；　 FOREIGN KEY：外部码列名； REFERENCES：引用的外部码的表名和列名 CKECK ：指定表级约束条件 An Introduction To Database System

27. 例题 • ［例1］定义一个表DEPT，包括4列。其中deptno为主键。CREATE TABLE DEPT( deptno CHAR(8) NOT NULL, Deptname CHAR(20) NOT NULL, Deptphno CHAR（12）,Deptadd CHAR（40）PRINARY KEY (deptno)) An Introduction To Database System

28. 例题 [例2] 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。 CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE， Sname CHAR(20) UNIQUE， Ssex CHAR(1) ， Sage INT， Sdept CHAR(15))； An Introduction To Database System

29. 例题 • ［例3］产生表student，包括5列，其中sno为主键，sclass为外键码，sclass为表classname的主键，需要通过check约束学生年龄大于等于14岁， • 如下定义： CREATE TABLE student( sno CHAR(8) NOT NULL,Sname CHAR(20) NOT NULL,Ssex CHAR(1),Sage INT CHECK (sage >= 14),Sclass CHAR(8)PRIMARY KEY (sno)FOREIGN KEY(sclass) REFERENCES class(classname)) An Introduction To Database System

30. 定义基本表（续） • 常用完整性约束 • 主码约束： PRIMARY KEY • 唯一性约束：UNIQUE • 非空值约束：NOT NULL • 参照完整性约束 PRIMARY KEY与UNIQUE的区别？ 1:表中一列或多列的组合,其值唯一确定表的一行. 2:主码不能重复3:作为主码各列都不能为空 An Introduction To Database System

31. 例题 （续） • [例4] 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主码。 • CREATE TABLE SC( • Sno CHAR(5) , • Cno CHAR(3) , • Grade int, • Primary key (Sno, Cno)); An Introduction To Database System

32. 例题 （续） An Introduction To Database System

33. 三、删除基本表 DROP TABLE <表名>; 基本表删除 数据、表上的索引都删除 表上的视图往往仍然保留，但 无法引用 删除基本表时，系统会从数据字典中删去有关该 基本表及其索引的描述 An Introduction To Database System

34. 例题 [例5] 删除Student表 DROP TABLEStudent ; An Introduction To Database System

35. 删除基本表中的数据 • Truncate table <表名> • 例： truncate table student An Introduction To Database System

36. 二、修改基本表 ALTER TABLE <表名> [ ADD <新列名> <数据类型> [ 完整性约束 ] ] [ DROP <完整性约束名> ] [ ALTER COLUMN子句 ] [ ADD CONSTRAINT子句 ] [ DROP CONSTRAINT子句 ] ； • <表名>：要修改的基本表 • ADD子句：增加新列和新的完整性约束条件 • DROP子句：删除指定的完整性约束条件 • ALTER COLUMN：用于修改列名和数据类型 • ADD Foreign Key（cdept）References <表名>（字段） An Introduction To Database System

37. 例题 [例2] 向Student表增加“入学时间”列，其数据类型为日期型。 ALTER TABLE Student ADD Scome DATE； • 不论基本表中原来是否已有数据，新增加的列一律为空值。 An Introduction To Database System

38. 语句格式（续） • 删除属性列 直接/间接删除 • 把表中要保留的列及其内容复制到一个新表中 • 删除原表 • 再将新表重命名为原表名 直接删除属性列:(新) 例：ALTER TABLE Student Drop column Scome； An Introduction To Database System

39. [例3] 将年龄的数据类型改为半字长整数。 ALTER TABLE Student alter column Sage SMALLINT； • 注：修改原有的列定义有可能会破坏已有数据 An Introduction To Database System

40. 例题 [例4] 删除学生姓名必须取唯一值的约束。 ALTER TABLE Student DROP UNIQUE(Sname)； An Introduction To Database System

41. 3.2.2 建立与删除索引 • 建立索引是加快查询速度的有效手段 • 建立索引 • DBA或表的属主（即建立表的人）根据需要建立 • 有些DBMS自动建立以下列上的索引 • PRIMARY KEY • UNIQUE • 维护索引 • DBMS自动完成 • 使用索引 • DBMS自动选择是否使用索引以及使用哪些索引 An Introduction To Database System

42. 一、建立索引 • 语句格式 CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)； • 用<表名>指定要建索引的基本表名字 • 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔 • 用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC • UNIQUE表明此索引的每一个索引值只对应唯一的数据记录 • CLUSTER表示要建立的索引是聚簇索引 An Introduction To Database System

43. 例题 [例6] 为学生-课程数据库中的Student，Course，SC三个表建立索引。其中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)； An Introduction To Database System

44. 建立索引 （续） • 唯一值索引 • 对于已含重复值的属性列不能建UNIQUE索引 • 对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束 An Introduction To Database System

45. 建立索引 （续） • 聚簇索引 • 建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致 例： CREATE CLUSTER INDEX Stusname ON Student(Sname)； 在Student表的Sname（姓名）列上建立一个聚簇索引，而 且Student表中的记录将按照Sname值的升序存放 An Introduction To Database System

46. 建立索引 （续） • 在一个基本表上最多只能建立一个聚簇索引 • 聚簇索引的用途：对于某些类型的查询，可以提高查询效率 • 聚簇索引的适用范围 • 很少对基表进行增删操作 • 很少对其中的变长列进行修改操作 An Introduction To Database System

47. 二、删除索引 DROP INDEX <索引名>； • 删除索引时，系统会从数据字典中删去有关该索引的描述。 [例7] 删除Student表的Stusname索引。 DROP INDEX Stusname； An Introduction To Database System

48. 3.3 查 询 3.3.1 概述 3.3.2 单表查询 3.3.3 连接查询 3.3.4 嵌套查询 3.3.5 集合查询 3.3.6 小结 An Introduction To Database System

49. 3.3.1 概述 • 语句格式 SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] … FROM <表名或视图名>[， <表名或视图名> ] … [ WHERE <条件表达式> ] [ GROUP BY <列名1> [ HAVING <条件表达式> ] ] [ ORDER BY <列名2> [ ASC|DESC ] ]； An Introduction To Database System

50. 语句格式 • SELECT子句：指定要显示的属性列 • FROM子句：指定查询对象(基本表或视图) • WHERE子句：指定查询条件 • GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。 • HAVING短语：筛选出只有满足指定条件的组 • ORDER BY子句：对查询结果表按指定列值的升序或降序排序 An Introduction To Database System