第4章-关系数据库标准语言SQL1.ppt

1、 数据库技术及应用数据库技术及应用DB1第第 4 章 关系数据库标准语言章 关系数据库标准语言 SQL本章主要内容本章主要内容：认识认识 SQL Server 及其基本使用；及其基本使用；数据库标准语言数据库标准语言 SQL 及其使用及其使用（建立数据库、查询数据、更新数据）。（建立数据库、查询数据、更新数据）。24.1.1 SQL Server2005 概述概述 Microsoft SQL Server 2005Microsoft SQL Server 2005（简称（简称 SQL ServeSQL Server r）是）是 MicrosoftMicrosoft 公司在公司在 Windows

2、Windows 平台上开发的一平台上开发的一个关系数据库管理系统个关系数据库管理系统,它支持关系数据库的创它支持关系数据库的创建建,设计和管理。设计和管理。4.1 SQL Server 简介简介2.SQL Server 主要组件及功能 SQL Server 提供了一整套数据库系统管理工具和实用提供了一整套数据库系统管理工具和实用程序，打开程序，打开“开始开始/程序程序 Microsoft SQL Server2005Microsoft SQL Server2005”菜单即可看到如下所示的菜单即可看到如下所示的 SQL ServerSQL Server 程序组程序组：1 SQL Server M

3、anagement Studio 对象资源管理器对象资源管理器 已注册的服务器已注册的服务器 查询编辑器查询编辑器 解决方案资源管理器解决方案资源管理器 模板资源管理器模板资源管理器2 SQL Server Configuration manager服务有服务有 3 种状态：种状态：停止（红色）：该服务没有启动，所有与SQL Server 连接的客户端全部断开。启动（绿色）：服务正常运行，相关管理和服务功能可用。暂停（黄色）：暂停状态时，已连接的客户端任务可以完成，不允许新的连接。表 4-1 SQL Server 提供的主要服务服务名称主要作用支持主要服务和管理SQL Server（SQL S

4、erver数据库引擎服务）是SQL Server的核心服务组件，是实际的数据库服务器，该服务启动后，客户端才可以连接服务器上。一般的数据库功能都由它提供数据管理（创建、维护数据库等）、T-SQL处理、并发控制、数据完整性控制等SQL Server Agent（SQL Server代理服务）作业调度和管理，可支持数据库定时、自动备份、维护，监视数据库、异常告警等功能，必需和SQL Server服务一起使用作业、报警、操作员等数据质量管理和数据清洗数据转换服务 提供了一个统一和集成的商业提供了一个统一和集成的商业数据库视图，可用作所有传统报表、数据库视图，可用作所有传统报表、OLAP 分分析和数据

5、挖掘的基础。析和数据挖掘的基础。3 Analysis Services4 SQL Server 联机丛书 SQL Server Profiler（事件探查（事件探查器）可以跟踪器）可以跟踪 SQL Server 服务器上的服务器上的每个操作，包括用户名、所使用程序名每个操作，包括用户名、所使用程序名、操作命令和持续时间等，可以实时显、操作命令和持续时间等，可以实时显示这些操作或保存到一个跟踪文件中，示这些操作或保存到一个跟踪文件中，用于分析数据库服务器的运行状态。用于分析数据库服务器的运行状态。5 性能工具 SQL Server 是一个完整的商务智能（是一个完整的商务智能（BI）平台，其中为用

6、户提供了可用于构件分析）平台，其中为用户提供了可用于构件分析应用程序所需的各种特性、工具和功能。应用程序所需的各种特性、工具和功能。6 SQL Server Business Intelligence StudioSQL Server 数据库创建和维护数据库创建和维护 1.数据库分类2.数据库组成3.数据库的存储结构 SQL Server 数据库基础 SQL Server 有两类数据库：(1)系统数据库:存放 SQL Server 工作时所需要的的系统级信息。数据库主要作用master从整体上控制 SQL Server 系统和用户数据库的运行。保存登录标识、系统配置、用户数据库基本信息等。该库

7、非常重要，应设置权限禁止一般用户访问，另外要及时备份model是新建数据库的模板，包含了每个用户数据库都需要的一些系统表，SQL Server 以它为基础创建新的数据库msdb支持 SQL Server Agent 服务，它记录有关作业、警报、操作员等信息tempdb是一个临时数据库，它为 SQL Server 的查询、存储过程等的执行提供临时存储14Master 数据库数据库 Master 数据库记录了数据库记录了 SQL Server 系统的系统的所有信息。这些信息包括所有的登录信息所有信息。这些信息包括所有的登录信息、系统设置信息、系统设置信息、SQL Server 的初始化的初始化信息

8、和其它系统数据库及用户数据库的相信息和其它系统数据库及用户数据库的相关信息。关信息。Master 数据库对应的主数据文件是数据库对应的主数据文件是Master.mdf，日志文件是，日志文件是 Mastlog.ldf。15Model 数据库数据库 Model 数据库是所有用户数据库和数据库是所有用户数据库和 Tempdb数据库的模板数据库。数据库的模板数据库。当用户使用当用户使用 CREATE DATABASE 命令建立新的命令建立新的数据库时，新数据库的第一部分总是通过复制数据库时，新数据库的第一部分总是通过复制Model 数据库中的内容创建，剩余部分由空页填数据库中的内容创建，剩余部分由空页

9、填充。充。Model 数据库对应的主数据文件是数据库对应的主数据文件是 Model.mdf，日志文件是日志文件是 Modellog.ldf。16Msdb 数据库数据库 Msdb 数据库是数据库是代理数据库代理数据库，主要用于，主要用于SQL Server Agent,为其警报、任务调度和为其警报、任务调度和记录操作员的操作提供存储空间。记录操作员的操作提供存储空间。Msdb 数据库对应的主数据文件是数据库对应的主数据文件是Msdb.mdf，日志文件是，日志文件是 Msdblog.ldf。17Tempdb 数据库数据库 是一个临时数据库。是一个临时数据库。Tempdb 数据库由整个系统的所有数据

10、库使用。数据库由整个系统的所有数据库使用。它为所有的临时表、临时存储过程及其它临时操它为所有的临时表、临时存储过程及其它临时操作提供存储空间。作提供存储空间。SQL Server 每次启动时，每次启动时，Tempdb 数据库被重新建立。当用户与数据库被重新建立。当用户与 SQL Server 断开联系时，其临时表和存储过程被自动断开联系时，其临时表和存储过程被自动删除。删除。对应的主数据文件是对应的主数据文件是 Tempdb.mdf，日志文件是，日志文件是Templog.ldf。18(2)(2)用户自定义数据库用户自定义数据库:用户根据数据管理的用户根据数据管理的需要建立的数据库，由用户自行创

11、建和维护。需要建立的数据库，由用户自行创建和维护。SQL Server 提供提供两个样本数据库两个样本数据库：pubs（图书出版的数据库）图书出版的数据库）Northwind（有关贸易公司的数据库）有关贸易公司的数据库）.数据库组成 从逻辑上看，SQL Server 数据库是由一组存储、管理和使用数据的对象构成。(1)文件：数据库文件可分为三类：数据库的物理存储结构 指数据库文件在磁盘上的存储方式。主数据文件主数据文件：用来存储数据以及数据库的启动信息。每个数据库都包含一个且只能包含一个主数据文件。主数据文件使用.MDF 为扩展名。次数据文件次数据文件：用来存储不能置于主数据文件中的其他数据。

12、次数据文件使用.NDF 为扩展名。事务日志文件事务日志文件：记录 SQL Server 执行的所有事务以及由这些事务操作引起的数据库的变化。事务日志文件使用.LDF 为扩展名。每个数据库至少包含两个文件：主数据文件主数据文件和事务日志文件事务日志文件。(2)文件组 文件组是用户对数据库文件（不包括事务日志文件）进行分组而形成的。例如：将存储在两个不同驱动器上的数据库文件组成一个组对它们整体进行管理。SQL Server 提供了两种文件组类型：用户自定义文件组：指在创建或更改数据库时，用户明确创建的任何文件组 主文件组：包含主数据文件和任何其它未放入其他文件组中的文件。所有系统表都包含在主文件组

13、中。(1)每个数据库中都有一个文件组作为默认文件组运行。可以指定默认文件组，如果没有指定默认文件组，则主文件组是默认文件组。说明：(2)若不指定用户定义文件组，则所有数据文件都包含在主文件组中。(3)一个文件只能是一个文件组的成员。234.1.3 创建数据库和基本表创建数据库和基本表1.创建数据库创建数据库要创建数据库必须拥有创建数据库的权限。要创建数据库必须拥有创建数据库的权限。在初始情况下，只有系统管理员可以创建数在初始情况下，只有系统管理员可以创建数据库。在数据库创建完成后，它的创建者自据库。在数据库创建完成后，它的创建者自动成为其所有者。动成为其所有者。244.1.3 创建数据库和基本

14、表创建数据库和基本表创建数据库的工作主要是创建数据库的工作主要是:确定数据库名称；确定数据库名称；确定数据文件的名称、存放位置和增长信确定数据文件的名称、存放位置和增长信息等；息等；确定事务日志文件名称、存放位置和增长确定事务日志文件名称、存放位置和增长信息等。信息等。25创建数据库方法有多种创建数据库方法有多种：使用使用SQL Server Management Studio可视化实现可视化实现；使用使用 Transact-SQL 语言创建语言创建;利用已有数据库文件或数据库备份文件创建利用已有数据库文件或数据库备份文件创建；从其他数据库复制创建；从其他数据库复制创建；导入数据到数据库中直接

15、创建。导入数据到数据库中直接创建。4.1.3 创建数据库和基本表创建数据库和基本表本节介绍使用本节介绍使用SQL Server Management Studio创建数据库。创建数据库。数据库创建与维护 1 通过通过SQL Server Management Studio创建数据库创建数据库【例例 4-14-1】创建数据库 School 它包含主数据文件 School_Data.MDF，其初始大小为 5MB，事务日志文件 School_Log.LDF，并将这两个文件建立在 D:DBSchool 文件夹下（如果该文件夹不存在，需要先在我的电脑中创建，否则会报错），其他按照默认设置不变。2 修改数

16、据库修改数据库 数据库创建后，数据文件和日志文件名就不能改变了。对已存在的数据库可以进行如下的修改：增加或删除数据文件；改变数据文件的大小和增长方式；改变日志文件的大小和增长方式；增加或删除日志文件；增加或删除文件组。操作步骤如下：操作步骤如下：右单击需要修改的数据库，在快捷菜单中选择“属性”命令，弹出“数据库属性”界面。(1)通过“文件”选项卡：可以修改已有数据文件的已分配空间、增长方式、最大大小等属性。增加数据文件（增加的文件是次数据文件，扩展名是.NDF）删除数据文件。(2)通过“文件组”选项卡：增加文件组 删除文件组注意：不能删除主文件组(PRIMARY)。可以删除用户定义的文件组，但

17、必须首先删除该文件组中的所有文件。删除数据库删除数据库(1)右单击要删除的数据库，在弹出的快捷菜单上选择“删除”命令。(2)在弹出的对话框中单击【确定】按钮，即可删除选定的数据库。操作步骤如下：操作步骤如下：32 每个数据库最多可创建每个数据库最多可创建 200 万个基本表万个基本表，用户创建基本表时，最多可以定义，用户创建基本表时，最多可以定义 1024个字段。个字段。有两种方法创建基本表：有两种方法创建基本表：a.使用使用 Transact-SQL 创建基本表；创建基本表；b.使用使用SQL Server Management Studio创建基本创建基本表。表。2.创建基本表创建基本表

18、本节介绍使用本节介绍使用SQL Server Management Studio创建基本表。创建基本表。4.1.3 创建数据库和基本表创建数据库和基本表33 创建基本表的主要工作：创建基本表的主要工作：确定基本表名；确定基本表名；确定每个字段的名称、数据类型、字段确定每个字段的名称、数据类型、字段宽度、是否可为空等；宽度、是否可为空等；确定每个表的主键。确定每个表的主键。(1)数据类型数据类型常用数据类型见下表所示。常用数据类型见下表所示。4.1.3 创建数据库和基本表创建数据库和基本表1 SQL Server 数据类型 整型：整型数据类型用于存储整数，有 4 种整型分别用于存储不同范围的整数

19、 数据类型数据范围占用存储空间bigint-263 263-18 个字节int-231 231-14 个字节smallint-215 215-12 个字节tinyint0 2551 个字节float 和 real 是近似数值型，采用科学计数法表示数据。decimal 和 numeric 是精确数值类型。两者的区别在于decimal 不能应用于带有 Identity 的列。声明精确数值型数据的格式如下：decimal|numeric(p,s)，其中：p 为精度，s 为小数位数，s的默认值为 0。例如，表达 345.65 所对应的数据精度的声明为 decimal(5,2)或 numeric(5,2

20、)浮点型:用于存储含小数的十进制数。数据类型数据范围精确度占用存储空间float-1.79E+3081.79E+308 可精确到第 15 位小数8 个字节real-3.40E+383.40E+38可精确到第 7 位小数4 个字节decimal-1038+11038-1完整精度217 个字节numeric-1038+11038-1完整精度217 个字节 字符型和文本型 字符型：字符数据类型用于存储由字母、符号和数字组成的字符串。字符串常量要用单引号为起止界限，例如 9aC。数据类型定义格式长度说明charchar(n)，n 表示字符串的长度，1=n=8000定长，最大 8KBvarcharvar

21、char(n)，n 表示字符串可达到的最大长度1=n=8000变长，最大 8KBtexttext变长，大于 8KBchar(n)是定长字符类型，字符串长度超出 n 的部分被截取；如不足 n 时，则在串的尾部添加空格以达到长度 n。varchar(n)是变长字符数据类型，长度为字符串的实际字符个数。如 varchar(50)，当输入“Zhonghua”字符串时，其存储长度为 8 个字节。通常，当某一字段的字符串长度固定或接近一固定值时使用 char 类型；当字符串长度明显不一致时使用 varchar 类型。Text 是文本数据类型，用来存储可变长度的大量字符数据，其存储长度最多可达 20 亿个

22、ASCII 字符。日期时间数据类型 用来存储日期和时间数据，默认数据格式为：”YYYY-MM-DD HH:MM:SS”,例如：“2008-12-08 12:35:29”，在使用时也可只存储日期或时间。位类型 bit 是位数据类型，用来作为逻辑变量使用，其数值有两种取值：0 和 1，分别代表 TRUE 和 FALSE 或 YES 和 NO。例如在 T_Student 表中，字段 LiveInDorm 存储“是否住校”信息使用 bit。数据类型数据范围占用存储空间datetime1753 年 1 月 1 日 9999 年 12 月 31 日之间的日期时间8 个字节smalldatetime1900

23、 年 1 月 1 日 2079 年 6 月 6 日之间的日期时间4 个字节 图像数据类型 image 用于存储照片、图片等，实际存储的是可变长度的二进制数据，其长度最大可达 231-1 个字节。货币型用十进制数表示货币值。实际上是一种小数，但小数点后只有 4 位，且自动四舍五入。uniqueidentifie:唯一标识符类型 数据库管理系统会随着记录的加入自动为该类型的字段产生唯一标识符。它是 16 字节长的二进制数据。数据类型数据范围占用存储空间money-922,337,203,685,477.5808 +922,337,203,685,477.5807 精度为 4 位小数8 个字节sma

24、llmoney-214,748.3648 214,748.3647，精度为 4 位小数4 个字节2 数据表的创建和维护(1)创建数据表【例例 4-34-3】在数据库 School 中建立学生表 T_Student，完成各字段的定义。1）展开 School 数据库。右击数据库文件夹下的“表”项，从弹出的快捷菜单中选择“新建表”命令，弹出“表设计”界面。2）在表设计界面，根据表的设计结构依次完成每个字段的名称和数据类型设置。3）在输入完各表字段后，单击 按钮或关闭表设计窗口，出现“选择表名”对话框,输入表名“T_Student”，单击【确定】,T_Student 表就建立起来了。(2)修改数据表结

25、构增加字段、删除字段、修改字段属性等。操作步骤：1)选择“表”对象，在右边窗格右击需要修改的表，在弹出的菜单上选择“设计表”命令，出现“表设计”界面。2)在数据表设计界面上，选中需要修改的一行，然后右击鼠标，弹出快捷菜单，可以对表设置主键、插入字段、删除字段。3)对表的结构修改完毕后，单击关闭窗口按钮，出现保存表结构修改对话框，单击“是”即可保存修改后的表。（3）更改表名 操作步骤：1)展开表，右击要更改表名的表，在快捷菜单上选择“重命名”命令。2)在表名位置输入新的表名。3 数据表的记录插入和维护 操作步骤：展开需操作的表所在的数据库，在右窗格中右击需操作的数据表，在弹出的菜单中选择“打开表

26、”，出现所选择表的数据窗口。在该数据窗口中，可插入记录、修改记录和删除记录。(1)插入记录1)将光标定位在表尾的下一行，逐字段输入值，每输入一个字段值，按回车键。2)当一个记录最后一个字段输入完毕按回车键后，光标将自动转到下一行的第一个字段处，便可插入下一个记录。3)输完数据，单击“表数据”窗口的【关闭】按钮，添加的记录就保存到数据表中。(2)修改记录定位被修改的记录字段，然后对该字段值进行编辑修改。(3)删除记录1)在表数据窗口中定位需被删除的记录行，单击鼠标右键，在弹出的快捷菜单上选择“删除”命令。2）弹出确认对话框，单击【是】按钮即删除选择的记录。4 删除数据表(1)在企业管理器中展开表

27、，右单击要删除的表，在快捷菜单上选择“删除”命令。(2)在弹出的“除去对象”对话框中，选择【除去】按钮，即可删除选择的表。数据表约束和数据完整性控制 数据的完整性控制指保护数据库中的数据的正确性、有效性和相容性，防止数据库中存在不符合语义的数据，防止错误信息进入数据库，并且使数据符合所描述的业务规则。SQL Server 使用表约束实现数据库完整性控制的方法：通过限制表的字段、记录以及表之间的数据来保证数据完整性。表 4-10 SQL Server 数据常用完整性约束机制完整性约束作用实体完整性主关键字约束（Primary Key）保证表中记录行的唯一性唯一性约束（Unique）保证在非关键字

28、字段不出现重复值 域完整性默认值约束（Default）对没有插入值的列自动添加表定义时对该列设置的默认值非空值约束（Not Null）限定某一列必须有值，即不允许空值检查约束（Check）限定某一列中可接受的值或数据格式参照完整性外关键字（Foreign Key）通过表间关系约束字段值的有效性【例例 4-44-4】在数据库 School 中定义学生表T_Student 的主键为“StudentCode”。【例例 4-54-5】在数据库 School 中的学生表T_Student 不允许出现姓名且生日相同的记录，并按姓名升序和生日降序排列,即对“StudentName”和”Birthday”字段

29、组合定义唯一性约束。实体完整性 主关键字约束（Primary Key）唯一性约束（Unique）主关键字（简称主键）是表中能保证表中记录唯一性的一个或多个字段的组合。主键的值不能重复，且不能为空。一个表只能有一个主键。唯一性约束可保证非主键列不输入重复值。对非主键字段或字段的组合强制唯一性。对允许空值的字段强制唯一性。实体完整性主要体现在表中记录的唯一性。域完整性 默认值约束（Default）非空值约束（Not Null）检查约束（Check）默认值约束使用户能够定义一个值，每当用户没有在某一列中输入值时，则系统将所定义的值提供给这一列。记录中的每一列均必须有值，即使它是空（NULL）。CHE

30、CK 约束限制某一列中可接受的值或格式。域完整性主要体现在表中字段（或称列或域）的值的有效性。【例例 4-64-6】在数据库 School 的学生表 T_Student 中，设置性别 Sex 字段的默认值为“男”。【例例 4-74-7】在数据库 School 的学生表 T_Student 中，设置StudentCode、StudentName、ClassCode、Sex 字段为非空值，其他字段允许为空。【例例 4-84-8】在数据库 School 的学生表 T_Student 中，设置Sex 列的值只允许是“男”或“女”。参照完整性：外关键字（Foreign Key）参照完整性主要体现在通过表

31、间关系约束字段（或称列或域）的值的有效性。两个建立关联关系的表中，在主表中相关字段定义了主键或唯一性约束，在从表中将相关字段定义为外关键字（简称外键），这样，外键的取值必须是主键中存在的值或空值。【例例 4-94-9】在数据库 School 的学生表 T_Student 和 T_Class表间建立关系，实施参照完整性约束。在该关系中，将T_Class 的 ClassCode 字段设置为主键，将 T_Student 的ClassCode 设置为外键，并实施参照完整性。【例例 4-10】利用例利用例 4-9 建立的建立的FK_STUDENT_CLASS 关系检验参照完关系检验参照完整性约束设置整性

32、约束设置,体验”强制外键约束”对体验”强制外键约束”对在外表插入记录的影响在外表插入记录的影响.向向 Student 表增加一条记录表增加一条记录(07128801,陶哲陶哲,071288,男男)【例例 4-11】利用例利用例 4-9 建立的建立的FK_STUDENT_CLASS 关系检验参照完关系检验参照完整性约束设置整性约束设置,体体验”验”INSERT”和”和”UPDATE”规范更规范更新规则和删除规则的应用新规则和删除规则的应用.4.2.5 索引的创建与维护 索引提供数据表查询时快速查找支持。一般来说，需要在下面这些地方建立索引：在主键和外键上创建索引。在检索频繁的字段上建立索引。在经

33、常需要排序的字段上建立索引。SQL Server 自动为主键字段建立聚集索引，又称为主索引，其他索引用户根据需要建立等。可在数据表上建立一个或多个索引。索引的创建【例例 4-124-12】School 数据库的 T_Student 表建立索引，按学号（StudentCode）建立主键索引；按姓名（ClassCode）建立升序非唯一索引，索引的组织方式为非聚集索引。例例 4.7 查询所有房产的房产编号查询所有房产的房产编号,地址地址,房型和租金房型和租金.select 房产编号房产编号,地址地址,房型房型,租金租金from 房产表房产表 例例 4.8 从房产表中查询所在的城市从房产表中查询所在的城市,查询查询结果中不出现重复的记录结果中不出现重复的记录.select distinct 城市城市from 房产表房产表 例例 4.9 使用合计函数使用合计函数,查询房产数查询房产数,最低最低租金租