SQLServer数据库开发经典案例教程SQLServer数据库开发经典案例教程

《SQLServer数据库开发经典案例教程SQLServer数据库开发经典案例教程》由会员分享，可在线阅读，更多相关《SQLServer数据库开发经典案例教程SQLServer数据库开发经典案例教程（292页珍藏版）》请在金锄头文库上搜索。

1、0 00 00 021世纪高职高专创新精品规划教材世纪高职高专创新精品规划教材SQL Server 数据库开发经典案例教程 1 1第第1章章 数据库基础数据库基础2 22 22 2本章目标n n数据库存在的必要性和数据库的发展n nSQL Server2008数据库以及它所支持的操作系统平台n nSQL Server2008的安装方法n n服务器上的后台服务n nSQL Server2008服务端组件和客户端工具n n学会使用SSMS创建登录 3 33 33 3数据库存在的必要n n数据存在的必要性可以存储大量的数据，便于用户进行检索和管可以存储大量的数据，便于用户进行检索和管理理 可以保持数

2、据的一致性、完整性，降低数据冗可以保持数据的一致性、完整性，降低数据冗余余 实现应用程序的数据共享和安全实现应用程序的数据共享和安全 利用数据库可以智能的对数据进行分析和统计利用数据库可以智能的对数据进行分析和统计 n n数据库的发展史 第一代数据库第一代数据库层次模型和网状模型层次模型和网状模型 n n各种各种DBMS DBMS 软件都是基于某种数据模型的软件都是基于某种数据模型的 n nIBM IBM 公司在公司在1969 1969 年推出了世界上第一个层次数据年推出了世界上第一个层次数据库系统库系统IMS IMS 第二代数据库第二代数据库关系型数据库关系型数据库 n n关系数据库是建立在

3、关系模型之上的数据库关系数据库是建立在关系模型之上的数据库 第三代数据库第三代数据库面向对象数据库面向对象数据库 n n处于发展阶段，未来趋势处于发展阶段，未来趋势4 44 44 4数据库的基本概念n n数据库的基本概念数据和信息数据和信息n n数据中所包含的意义就是信息数据中所包含的意义就是信息 数据库数据库n n保存有组织的数据的容器保存有组织的数据的容器关系数据库关系数据库n n关系数据库是基于关系模型建立的，由一系列二维表关系数据库是基于关系模型建立的，由一系列二维表格组成，将数据分类存储在多个二维表中，用关系格组成，将数据分类存储在多个二维表中，用关系（外键）来体现各个表之间的关系。

4、（外键）来体现各个表之间的关系。 订单订单订单订单 客户客户客户客户 产品产品产品产品 产品数据库表数数数数 据据据据 表表表表存储过程存储过程存储过程存储过程视视视视 图图图图.5 55 55 5数据库的基本概念n n数据库的基本概念表、实体和记录表、实体和记录 n n实体是客观存在的事物，比如：桌子，椅子，电脑实体是客观存在的事物，比如：桌子，椅子，电脑实体是客观存在的事物，比如：桌子，椅子，电脑实体是客观存在的事物，比如：桌子，椅子，电脑行(记录)6 66 66 6数据库的基本概念n n数据库的基本概念数据冗余和数据完整性数据冗余和数据完整性 n n数据冗余即相同的数据存在了多个地方数据

5、冗余即相同的数据存在了多个地方 n n数据完整性是指数据库中数据的有效性和相容性数据完整性是指数据库中数据的有效性和相容性编号编号姓名姓名年龄年龄民族民族部门部门1 1王涛王涛3333汉族汉族人事管理部人事管理部2 2李梅李梅2727汉族汉族人事管理部人事管理部市市场营销场营销部部回族回族2727李梅李梅2 2财务财务管理部管理部汉汉族族3333王涛王涛1 1部部门门民族民族年年龄龄姓名姓名编编号号7 77 77 7SQLServer2008简介n nSQL Server2008SQL Server2008简介简介 SQLSQL ServerServer是微软公司开发的一款关系型数据库产品，是

6、微软公司开发的一款关系型数据库产品，具有成本低，易上手，工具全等优点。具有成本低，易上手，工具全等优点。 适用于大型或超大型数据库服务器端。微软的软件的适用于大型或超大型数据库服务器端。微软的软件的特点是版本分得细，可适合各种使用者不同的需要特点是版本分得细，可适合各种使用者不同的需要 n nSQL Server2008SQL Server2008的体系结构的体系结构 SQL Server2008SQL Server2008系统由系统由4 4个部分组成：个部分组成：n n数据库引擎数据库引擎n nAnalysis ServicesAnalysis Servicesn nReporting Se

7、rvicesReporting Servicesn nIntegration ServicesIntegration Servicesn n数据库引擎数据库引擎 数据库引擎是数据库引擎是Microsoft SQL Server2008Microsoft SQL Server2008系统的核系统的核心服务，是存储和处理关系（表格）类型的数据或心服务，是存储和处理关系（表格）类型的数据或XMLXML文档数据的服务，负责完成数据的存储、处理和文档数据的服务，负责完成数据的存储、处理和安全管理。安全管理。 例如：创建数据库、创建表、创建视图、查询数据和例如：创建数据库、创建表、创建视图、查询数据和访问

8、数据库等操作，都是由数据库引擎完成的访问数据库等操作，都是由数据库引擎完成的 8 88 88 8SQLServer2008的安装和版本n n安装：下载软件并参照书步骤进行安装n n版本企业版（企业版（SQL Server2008 Enterprise SQL Server2008 Enterprise EditionEdition） 标准版标准版 (SQL Server2008 Standard (SQL Server2008 Standard EditionEdition） 工作组版工作组版(SQL Server2008 Workgroup (SQL Server2008 Workgroup

9、 EditionEdition）开发版开发版(SQL Server2008 Developer (SQL Server2008 Developer Edition) Edition) 简易版（简易版（SQL Server2008 Express SQL Server2008 Express Edition) Edition) 9 99 99 9SQLServer2008后台服务后台服务n n服务器上的后台服务服务器上的后台服务 在控制面板上选择在控制面板上选择【管理工具管理工具】- - 【服务服务】选项。选项。n nSQL ServerSQL Server服务服务 n nSQL Server

10、AgentSQL Server Agent服务服务 n nSQL Server Browser SQL Server Browser n nSQL Server Integration ServicesSQL Server Integration Services服务服务 n nSQL Server Reporting ServicesSQL Server Reporting Services服务服务 n n客户端管理工具客户端管理工具 SQL Server Management StudioSQL Server Management Studio SQL Server Configurati

11、on ManagerSQL Server Configuration Manager Reporting ServicesReporting Services配置配置 SQL ServerSQL Server错误和使用情况报告错误和使用情况报告 SQL Server Profiler SQL Server Profiler 数据库引擎优化顾问数据库引擎优化顾问101010101010SQLServer2008的工作模式客户端应用程序客户端应用程序SQLServer服务器服务器批处理语句：批处理语句：语句语句1语句语句2GOn nSQL Server2008支持C/S工作模式，C/SC/S模式即

12、客户机模式即客户机/ /服务器服务器（Client/ServerClient/Server）模式的简称，在该种模）模式的简称，在该种模式下，服务器通常是在硬件配置和性能比式下，服务器通常是在硬件配置和性能比较高、安装了服务器软件的计算机，客户较高、安装了服务器软件的计算机，客户机就是安装了客户端软件的一般的计算机机就是安装了客户端软件的一般的计算机 111111111111连接与断开数据库服务器连接与断开数据库服务器n n连接与断开数据库服务器连接与断开数据库服务器身份验证：连接之前，需要选择身份验证模式，身份验证：连接之前，需要选择身份验证模式，身份验证：连接之前，需要选择身份验证模式，身份

13、验证：连接之前，需要选择身份验证模式，n n系统提供了两种身份验证模式：系统提供了两种身份验证模式：系统提供了两种身份验证模式：系统提供了两种身份验证模式： WindowsWindows身份验证：用当前登录操作系统的身份去登录身份验证：用当前登录操作系统的身份去登录身份验证：用当前登录操作系统的身份去登录身份验证：用当前登录操作系统的身份去登录. . SQL ServerSQL Server身份验证：用身份验证：用身份验证：用身份验证：用WindowsWindows加加加加SQL ServerSQL Server的混合模式进行验证，需要用合法的的混合模式进行验证，需要用合法的的混合模式进行验证

14、，需要用合法的的混合模式进行验证，需要用合法的SQL ServerSQL Server登录用登录用登录用登录用户去访问户去访问户去访问户去访问n n默认系统在安装以后会自动产生一个默认系统在安装以后会自动产生一个默认系统在安装以后会自动产生一个默认系统在安装以后会自动产生一个sasa的登录用户，的登录用户，的登录用户，的登录用户，具有最高权限。具有最高权限。具有最高权限。具有最高权限。断开数据库服务器断开数据库服务器断开数据库服务器断开数据库服务器 121212121212启动和停止SQLServer服务n n启动、停止、暂停和重新启动SQL Server服务 使用使用SQL ServerSQ

15、L Server配置管理器配置管理器用用SQL Server Management Studio SQL Server Management Studio (SSMS) (SSMS) 使用操作系统的服务管理功能使用操作系统的服务管理功能使用命令提示符使用命令提示符n n启动启动SQL ServerSQL Server默认实例：默认实例： net start net start mssqlservermssqlservern n停止停止SQL ServerSQL Server默认实例：默认实例： net stop net stop mssqlserver mssqlserver 13131313

16、1313SQLServerManagementStudio(SSMS)介绍n nSSMS简介 SQL ServerSQL Server是作为单独的是作为单独的WindowsWindows进程在基进程在基于于WindowsWindows的计算机上运行的，它可以运行在的计算机上运行的，它可以运行在独立的桌面计算机上，也可以运行在服务器或独立的桌面计算机上，也可以运行在服务器或者网络计算机上者网络计算机上 . .SQL ServerSQL Server作为服务运行，该服务由作为服务运行，该服务由WindowsWindows自身进行监控自身进行监控 SSMSSSMS中的一个重要的工具就是是查询编辑器中

17、的一个重要的工具就是是查询编辑器（query editor )query editor )n nSQL Server Management Studio(SSMS)的使用 141414141414创建数据库登录账户n n创建数据库登录账户在实际应用中，经常要根据不同的用户设置不在实际应用中，经常要根据不同的用户设置不同的访问权限来限制对数据库的操作同的访问权限来限制对数据库的操作 151515151515总结n n数据库存在的必要性和数据库的发展使用数据库可以安全，高效的实现数据的存储使用数据库可以安全，高效的实现数据的存储和管理和管理数据和信息的概念数据和信息的概念n nSQL Server

18、2008数据库只适合Windows平台n nSQL Server2008的安装方法n n服务器上的后台服务n nSQL Server2008服务端组件和客户端工具n n创建登录的方法161616161616n n使用数据库可以安全，高效的实现数据的存储和管理n n数据和信息n n数据库文件包括：主数据文件，次要数据文件和日志文件，每个主数据文件，次要数据文件和日志文件，每个数据库至少要包含一个数据文件和日志文件数据库至少要包含一个数据文件和日志文件n n文件组是逻辑上对数据文件进行管理的一种方式n n通过分离数据库可以将数据库分离成物理的文件n n附加数据库可以将物理文件恢复成数据库第2章 数

19、据库管理171718181818本章目标n n学会创建和维护数据库的方法n n掌握数据库的组成及数据文件的含义n n掌握数据库的分离和附加方法n n学会使用帮助文档181819191919数据库组成数据库组成1919n n数据文件（主要数据文件（主要数据文件（主要数据文件（主要/ /次要）和日志文件次要）和日志文件次要）和日志文件次要）和日志文件 数据文件包含数据和对象：如表、索引、视图等数据文件包含数据和对象：如表、索引、视图等数据文件包含数据和对象：如表、索引、视图等数据文件包含数据和对象：如表、索引、视图等 日志文件包含恢复数据库中的所有事务所需的信息日志文件包含恢复数据库中的所有事务所

20、需的信息日志文件包含恢复数据库中的所有事务所需的信息日志文件包含恢复数据库中的所有事务所需的信息数据库数据库主数据文件(.mdf)次要数据文件(.ndf)日志文件(.ldf)20202020数据库文件和文件组简介n n主数据文件主数据文件 包含数据库的启动信息，用户数据和对象存储在此文包含数据库的启动信息，用户数据和对象存储在此文件中，每个数据库只能有一个主数据文件，建议扩展件中，每个数据库只能有一个主数据文件，建议扩展名是名是 .mdf.mdfn n次要数据文件次要数据文件 次要数据文件是可选的，由用户定义并存储用户数据。次要数据文件是可选的，由用户定义并存储用户数据。建议扩展名建议扩展名.

21、ndf.ndfn n事务日志文件事务日志文件 保存用于恢复数据库的日志信息。每个数据库必须至保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件，建议扩展名少有一个日志文件，建议扩展名.ldf.ldf202021212121数据库数据库n n系统数据库系统数据库系统数据库系统数据库2121mastermastertempdbtempdbmsdbmsdbStudentsDBStudentsDBBookShopDBBookShopDB用户数据库用户数据库modelmodel4个系统数据库个系统数据库用户自己创建22222222系统数据库n n4个系统数据库MasterMastern n它

22、存储它存储 SQL Server SQL Server 系统的所有系统级信息系统的所有系统级信息 , ,它包它包括：括： 登录帐户、链接服务器和系统配置设置。登录帐户、链接服务器和系统配置设置。 所有其他数据库的存在、数据库文件的位置所有其他数据库的存在、数据库文件的位置 SQL ServerSQL Server的初始化信息的初始化信息 modelmodeln n在在 SQL Server SQL Server 实例上创建的所有数据库的模板实例上创建的所有数据库的模板 Msdb Msdb n n由由 SQL Server SQL Server 代理用来计划警报和作业代理用来计划警报和作业 te

23、mpdb tempdb n n是连接到是连接到 SQL Server SQL Server 实例的所有用户都可用的全实例的所有用户都可用的全局资源，它保存所有临时表和临时存储过程局资源，它保存所有临时表和临时存储过程 222223232323文件组文件组n nBookShopDBBookShopDB的物理文件结构的物理文件结构的物理文件结构的物理文件结构2323BookShopDBBookShopDB.ldf.ldfE:日志文件日志文件日志文件日志文件 BookInfoBookInfo PublisherPublisher UserInfoUserInfo 主文件组主文件组主文件组主文件组Bo

24、okShopDB.mdfBookShopDB.mdfC:用户定义文件组用户定义文件组用户定义文件组用户定义文件组Book1.ndfBook1.ndfBook2.ndfBook2.ndfD:syssys syssys sysuserssysusers sysobjectssysobjects 242424242424数据库的建立数据库的建立-可视化方式可视化方式25252525数据库的建立-使用代码创建n n代码创建数据库代码创建数据库252526262626数据库设置n n数据库的设置数据库右键数据库右键-属性属性 -选项选项-只读只读 -收缩收缩262627272727数据库的修改修改数据库

25、：可以在修改数据库：可以在【文件文件】选项页通过修改选项页通过修改数据文件修改数据库数据文件修改数据库 272728282828数据库删除n n可视化方式 n n代码方式Drop database testDrop database test282829292929分离数据库分离数据库n n分离数据库 分离数据库是指将数据分离数据库是指将数据库从库从 SQL Server SQL Server 实例中删除实例中删除292930303030附加数据库n n附加数据库附加是分离的反操作。附加是分离的反操作。就可以使用分离后的数据就可以使用分离后的数据文件和日志文件将数据库文件和日志文件将数据库附加

26、到任何附加到任何 SQL Server SQL Server 实例，实例，包括分离该数据库的服务器包括分离该数据库的服务器 303031313131总结n n数据库文件包括：主数据文件，次要数据文件和日志文件，每个主数据文件，次要数据文件和日志文件，每个数据库至少要包含一个数据文件和日志文件数据库至少要包含一个数据文件和日志文件n n文件组是逻辑上对数据文件进行管理的一种方式n n创建数据库可视化方式创建可视化方式创建代码方式创建代码方式创建n n维护数据库数据库的修改和删除数据库的修改和删除n n通过分离数据库可以将数据库分离成物理的文件n n附加数据库可以将物理文件恢复成数据库n n使用帮

27、助文档3131第3章-1 SQLServer表管理323233333333本章目标n n掌握数据表、数据完整性、主键和外键的基本概念掌握数据表、数据完整性、主键和外键的基本概念n n掌握掌握SQLServerSQLServer数据类型数据类型n n创建表的时候要设定字段名，选取合适的数据类型和约束创建表的时候要设定字段名，选取合适的数据类型和约束n n如果要保证某个列唯一并且自动增长，设定如果要保证某个列唯一并且自动增长，设定IDENTITYIDENTITY约束约束n n主键是保证实体完整性的约束，建议每个表有一个主键主键是保证实体完整性的约束，建议每个表有一个主键n n外键保证表之间的引用完

28、整性，从表中的数据依赖于主表外键保证表之间的引用完整性，从表中的数据依赖于主表n n在建表时设置在建表时设置 主键约束主键约束 外键约束外键约束 检查约束检查约束 默认约束默认约束 空值约束空值约束 标识列标识列 333334343434数据表基本概念n n表书数据库中最重要的数据对象，数据存储在表表书数据库中最重要的数据对象，数据存储在表里里n n数据表由行和列组成数据表由行和列组成343435353535数据完整性n n数据完整性实体完整性实体完整性n n将行定义为特定表的唯一实体。即表中的每一行数将行定义为特定表的唯一实体。即表中的每一行数据都代表一个不同的实体，不能存在相同的数据行据都

29、代表一个不同的实体，不能存在相同的数据行 域完整性域完整性n n域完整性指特定列项的有效性域完整性指特定列项的有效性 引用完整性引用完整性n n用来限制两个有关联关系的表之间的数据用来限制两个有关联关系的表之间的数据 353536363636主键和外键n n主键表通常具有包含唯一标识表中每一行值的一个表通常具有包含唯一标识表中每一行值的一个或一多个字段或一多个字段 特点特点n n一个表只能有一个主键一个表只能有一个主键n n主键中的列不能为空主键中的列不能为空n n如果一个表的主键由多个列组成，该主键也叫如果一个表的主键由多个列组成，该主键也叫“ “组组合主键合主键” ”n n外键外键约束保证

30、了数据库中两个表中数据的引用外键约束保证了数据库中两个表中数据的引用完整性完整性 将一个表的一列（或列组合）定义为引用其他将一个表的一列（或列组合）定义为引用其他表的主键，则引用表中的这个列（或列组合）表的主键，则引用表中的这个列（或列组合）就称为外键就称为外键 363637373737主键和外键3737主键主键外键外键38383838数据表n n建立数据表，建表时需考虑：字段的名称字段的名称数据类型数据类型是否允许为是否允许为NullNull是否自动增长等是否自动增长等 383839393939数据类型n nSqlserverSqlserver有以下数据有以下数据类型类型数据类型类别数据类型

31、类别数据类型类别数据类型类别 具体类型具体类型具体类型具体类型 精确数据精确数据精确数据精确数据 bigintbigint，decimaldecimal，intint，numericnumeric，smallintsmallint，moneymoney，tinyinttinyint，smallmoneysmallmoney，bitbit 近似数字近似数字近似数字近似数字 float ,realfloat ,real字符串字符串字符串字符串 char ,varchar, text char ,varchar, text UnicodeUnicode字符字符字符字符串串串串 nchar , nva

32、rchar, ntext nchar , nvarchar, ntext 二进制字符串二进制字符串二进制字符串二进制字符串 binary ,image, varbinary binary ,image, varbinary 日期类型日期类型日期类型日期类型 datetime ,smalldatetime datetime ,smalldatetime 其他类型其他类型其他类型其他类型 Uniqueidentifier, xml, Uniqueidentifier, xml, tabletable等等等等 393940404040创建表n n创建表的实质就是定义表结构以及约束等属性。n nSQL

33、 Server2008种提供了两种方式创建表。一种是通过一种是通过SQL Server Management SQL Server Management Studio(SSMS)Studio(SSMS)一种是通过一种是通过T-SQLT-SQL代码。代码。本书重点介绍使用T-SQL代码来创建表 404041414141T-SQL代码建表n n通过执行Transact-SQL的CREATE TABLE 语句可以创建表，建表的语法如下：例如: CREATE TABLE mytable ( col1 char(10) primary key, col2 int not null )4141CREATE

34、TABLE表名表名(字段字段1数据类型数据类型列的特征，列的特征，字段字段2数据类型数据类型列的特征，列的特征，。)42424242建表【示例3-1】 ：建立用户表：建立用户表UserInfo, UserInfo, 建表时设建表时设置主键并设置相应字段的置主键并设置相应字段的not nullnot null（非空）属性（非空）属性 4242USEBookShopDb-将当前数据库设置为BookShopDbGOCREATETABLEUserInfo-创建用户表(UserIDchar(3)PRIMARYKEY,-UerID为主键UserNamechar(10)notnull,Sexchar(2)n

35、otnull,Ageint,hiredatedatetime,telechar(15),Addrvarchar(100)GO43434343建表n n表的IDENTITY 属性标识列字段的数据类型只能是标识列字段的数据类型只能是decimaldecimal，intint，numericnumeric，smallintsmallint，bitintbitint，tinyinttinyint每个表最多有一列具有每个表最多有一列具有IDENTITYIDENTITY属性，且该属性，且该列不能为空、不允许有默认值列不能为空、不允许有默认值IDENTITYIDENTITY属性的语法属性的语法: : IDE

36、NTITY(seed,increment),seed IDENTITY(seed,increment),seed称为称为种子，表示装载到表中第一行使用的值；如种子，表示装载到表中第一行使用的值；如IDENTITY(1,1),IDENTITY(1,1),表示该列值从表示该列值从1 1开始，每条开始，每条记录增记录增1.1.IDENTITYIDENTITY属性与属性与CREATE TABLECREATE TABLE和和ALTER ALTER TABLETABLE一起使用一起使用 434344444444建表时使用标识列n n【示例3-2】建立图书类别表Category 4444USEBookSho

37、pDb-将当前数据库设置为BookShopDbGOCREATETABLEPublisher(PublisherIDintIDENTITY(1,1)PRIMARYKEY,-自动编号（标识列）PublisherNamevarchar(50)notnull-出版社名称，必须输入)GO45454545建表时使用约束4545USEBookShopDb-将当前数据库设置为BookShopDbGOCREATETABLEBookInfo/*创建BookInfo数据表*/(BookIDchar(15)PRIMARYKEY,-书号，主键BookNamevarchar(100)notnull,-书名Authorva

38、rchar(50)notnull,-作者ISBNchar(13),-ISBN,null可以省略PublishDatedatetimenotnull,-出版日期PublisherIDintnotnullREFERENCESPublisher(PublisherID),-外键CategoryIDintnotnullREFERENCESCategory(CategoryID),-外键Pricemoney,-价格Contenttext-介绍)GO46464646建表时使用约束4646USEBookShopDb-将当前数据库设置为BookShopDbGODroptableuserInfoCREATETA

39、BLEUserInfo-创建用户表(UserIDchar(3)PRIMARYKEYCHECK(UserIDLIKE0-90-90-9),UserNamechar(10)UNIQUE,-用户名，唯一约束Sexchar(2)CHECK(Sexin(男,女),-性别，Check约束Ageintnotnull,-年龄,非空约束hiredatedatetime,-入职日期telechar(15),-电话Addrvarchar(100)DEFAULT广东广州-地址，默认约束)GO【示例示例3-6】重建重建UserInfo表，演示在建表时如何设定约束47474747建表时使用约束4747USEBookSho

40、pDb-将当前数据库设置为BookShopDb,GOCREATETABLESalesMaster/*创建SalesMaster销售主表*/(SalesMasterIDintidentityprimarykey,-销售id,自动增长，主键SalesDatedatetimenotnull,-销售日期UserIDchar(3)notnull),CONSTRAINTfk_UserIDFOREIGNKEY(UserID)REFERENCESUserInfo(UserID)ONDELETECASCADE【示例示例3-7】建立建立SalesMaster销售主表，通过设定外键与UserInfo表之间建立联系4

41、8484848总结n n掌握数据表的概念和含义掌握数据表的概念和含义n n数据完整性、主键和外键的基本概念数据完整性、主键和外键的基本概念 主键是保证实体完整性的约束，建议每个表有一个主键主键是保证实体完整性的约束，建议每个表有一个主键 外键保证表之间的引用完整性，从表中的数据依赖于主外键保证表之间的引用完整性，从表中的数据依赖于主表表n n创建数据表创建数据表 创建表的时候要设定字段名，选取合适的数据类型和约创建表的时候要设定字段名，选取合适的数据类型和约束束 如果要保证某个列唯一并且自动增长，设定如果要保证某个列唯一并且自动增长，设定IDENTITYIDENTITY约束约束n n掌握掌握S

42、QLServerSQLServer数据类型，三大类：数据类型，三大类： 数字、字符和日期数字、字符和日期n n在建表时设置在建表时设置 主键约束主键约束 外键约束外键约束 检查约束检查约束 默认约束默认约束 空值约束空值约束 标识列标识列 4848第3章-2 SQLServer表管理494950505050本章目标n n约束的维护修改表添加约束修改表添加约束删除约束删除约束n n建立数据库关系图 以图形方式显示数据库表之间的关系以图形方式显示数据库表之间的关系n nSQL Server中的特殊表类型 已分区表已分区表临时表临时表系统表系统表n n修改和删除表修改表结构、删除表修改表结构、删除表

43、505051515151约束的维护n n添加约束语法5151ALTERTABLE表名表名ADDCONSTRAINT约束名约束类型具体的约束说明约束名约束类型具体的约束说明USEBookShopDb-选择数据库，以下示例从略选择数据库，以下示例从略GOALTERTABLEUserInfoADDCONSTRAINTpk_UserIDPRIMARYKEY(UserID)添加主键约束添加主键约束添加外键约束添加外键约束ALTERTABLESalesDetailsADDCONSTRAINTfk_BookIDFOREIGNKEY(BookID)REFERENCESBookInfo(BookID)52525

44、252约束的维护n n删除约束语法5252ALTERTABLE表名表名DROPCONSTRAINT约束名约束名ALTERTABLEUserInfoDROPCONSTRAINTpk_UserID53535353数据库关系图n n已建立了数据库中各个数据表之间的关系，可通过SQL Server提供的数据库关系图，即可以图表的形式显示各个数据表之间的关系n n如果表之间的关系没有建立，也可在通过数据库关系图建立数据表之间的关系 535354545454数据库关系图545455555555SQLServer中的特殊表类型n n分区表当表数据非常大时可以将一个表水平划分为多当表数据非常大时可以将一个表水

45、平划分为多个表单独存储，并且可以存储在多个不同的物个表单独存储，并且可以存储在多个不同的物理文件中。这样的表就是分区表理文件中。这样的表就是分区表 n n临时表在操作过程中系统需要维护一些临时数据，比在操作过程中系统需要维护一些临时数据，比如果用户需要将查询结果排序，则会创建临时如果用户需要将查询结果排序，则会创建临时表并在临时表中将数据排序后返回给用户表并在临时表中将数据排序后返回给用户 n n系统表SQL Server SQL Server 将定义服务器配置及其所有表的将定义服务器配置及其所有表的数据存储在一组特殊的表中，这组表称为系统数据存储在一组特殊的表中，这组表称为系统表。任何用户都

46、不应该直接更改系统表表。任何用户都不应该直接更改系统表 555556565656修改表结构n n修改表结构使用ALTER TABLE语句n n修改表结构增加列增加列删除列删除列修改列的数据类型修改列的数据类型改变列的长度等改变列的长度等n n在表没有数据的情况下，可以对列进行任意修改或删除 565657575757修改表结构-增加列和修改列5757USEBookShopDbGOALTERTABLEUserInfoADDemailchar(20)GO增加列增加列给UserInfo表增加列email,字符类型长度是20，非空修改列修改列USEBookShopDbGOALTERTABLEUserIn

47、foALTERCOLUMNaddrvarchar(150)notnull增加UserInfo表的字段addr长度，将其增加到150，类型不变58585858修改表结构-删除列5858删除列删除列删除UserInfo表email列USEBookShopDbGOALTERTABLEUserInfoDROPCOLUMNemailGO59595959删除表n n删除表 DROP TABLE UserInfoDROP TABLE UserInfo如果删除的表和其他表有关联关系，必须先删除如果删除的表和其他表有关联关系，必须先删除子表，然后再删除主表。否则系统会弹出错误子表，然后再删除主表。否则系统会弹出

48、错误 595960606060总结n n约束的维护修改表添加约束修改表添加约束n nAlter table add constraint Alter table add constraint 约束名称约束名称 约束类型约束类型删除约束删除约束n n alter table drop constraint alter table drop constraint 约束名称约束名称n n建立数据库关系图 以图形方式显示数据库表之间的关系以图形方式显示数据库表之间的关系n nSQL Server中的特殊表类型 已分区表已分区表临时表临时表系统表系统表n n修改和删除表修改表结构：修改表结构：n n修改

49、添加字段修改添加字段Alter table Alter table 表名表名 add add 字段名称字段名称 数数据类型据类型n n修改表修改字段修改表修改字段 alter table alter table 表名表名 alter column alter column 字段名称字段名称 数据类型数据类型n n修改表删除字段修改表删除字段 alter table alter table 表名表名 drop drop 字段名称字段名称删除表删除表Drop table Drop table 表名表名 6060第4章 SQLServer数据管理616162626262本章目标n n了解SQL和T-

50、SQLn nT-SQL实现数据的增加、修改、删除数据操作626263636363SQL和T-SQLn nSQL(Structure Query Language,结构化查询语言) 主要功能就是同各种数据库建立联系，进行沟主要功能就是同各种数据库建立联系，进行沟通。通。用于存取数据以及查询、更新和管理关系数据用于存取数据以及查询、更新和管理关系数据系统系统 n nSQL语言主要包括 数据操纵语言（数据操纵语言（DMLDML）n n用来操作数据库中的数据，包括插入数据用来操作数据库中的数据，包括插入数据（INSERTINSERT）、修改数据（）、修改数据（UPDATEUPDATE）、删除数据）、删

51、除数据（DELETEDELETE）、查询数据（）、查询数据（SELECTSELECT）等操作）等操作 数据定义语言（数据定义语言（DDLDDL）n n用于创建或修改数据库里的对象，如创建用于创建或修改数据库里的对象，如创建Create Create tabletable、修改、修改Alter table Alter table 、删除、删除Drop tableDrop table等等 数据控制语言（数据控制语言（DCLDCL） n n用来控制用户对数据库数据的访问权限用来控制用户对数据库数据的访问权限, ,如：如：GrantGrant、RevokeRevoke等等636364646464T-S

52、QL介绍n nT-SQLT-SQLT-SQL（Transact-SQLTransact-SQL）是）是SQL ServerSQL Server的核的核心心 n n所有与所有与SQL ServerSQL Server实例通信的应用程序，都是通实例通信的应用程序，都是通过发送过发送T-SQLT-SQL语句到服务器来完成对数据库的操作语句到服务器来完成对数据库的操作 T-SQLT-SQL是对标准是对标准SQLSQL的扩展的扩展 n n是在标准是在标准SQLSQL的基础上增加了变量定义、函数、流的基础上增加了变量定义、函数、流程控制等编程要素程控制等编程要素 n n语法约定 语句不区分大小写语句不区分

53、大小写 646465656565插入数据6565语法格式INSERTINTO表名表名（列名，（列名，n)VALUES(值，值，n)【例例4-1】向BookShopDb数据库的UserInfo表中插入所有列数据USEBookShopDbGO-批处理结束标志，表示执行批处理结束标志，表示执行INSERTINTOUserInfo(UserID,UserName,Sex,Age,hiredate,tele,Addr)VALUES(001,王琳琳王琳琳,女女,26,2005-1-24,07313677867,湖南长沙湖南长沙)INSERTINTOUserInfoVALUES(002,张家辉张家辉,男男,

54、27,2004-3-5,02034657654,广东广州广东广州)INSERTINTOUserInfo(UserID,UserName)VALUES(003,张田田张田田)插入部分列时列名不能省略插入所有列时列名可省略66666666插入数据6666【例例4-3】向UserInfo表插入数据，尝试输入性别为其他内容INSERTINTOUserInfo(UserID,UserName,Sex,Age,hiredate,tele,Addr)VALUES(004,周晨阳周晨阳,中中,30,2003-4-2,02734652345,湖北宜昌湖北宜昌)标识列字段数据的插入【例例4-5】向向Categor

55、y表插入数据,Category带有标识列CategoryIDINSERTINTOCategory(CategoryName)VALUES(计算机基础理计算机基础理论论)67676767使用使用SELECTINTO语句拷贝表语句拷贝表n nSELECT INTO SELECT INTO 可将几个表或视图中的数据组合成一个表可将几个表或视图中的数据组合成一个表 查看表数据：查看表数据：select * from copyUserInfoselect * from copyUserInfon n若只拷贝表结构：若只拷贝表结构： SELECT * INTO NewUserInfo FROM SELEC

56、T * INTO NewUserInfo FROM UserInfo where 1=2 UserInfo where 1=2 6767【例例4-9】使用SELECT.INTO拷贝UserInfo表年龄大于等于30的数据生成一新表CopyUserInfoSELECT * INTO CopyUserInfo FROM UserInfo WHEREAge=3068686868更新数据n n语法格式6868UPDATE表名表名SETWHERE更新条件更新条件【例例4-10】修改修改UserInfo表数据，把UserID为007的用户的住址改为甘肃酒泉UPDATEUserInfoSETAddr=甘肃酒

57、泉甘肃酒泉WHEREUserID=00769696969修改数据6969【例例4-11】如图完善UserInfo表数据,把用户号003用户的年龄、参加工作日期分别修改为33，2004-8-30UPDATEUserInfoSETAge=33,hiredate=2004-8-30WHEREUserID=003【例例4-12】把用户001的参加工作日期加1天，地址改为湖南湘潭UPDATEUserInfoSEThiredate=hiredate+1,Addr=湖南湘潭湖南湘潭WHEREUserID=001注意：修改数据如果只修改一条记录，一般都按主键字段修改。70707070删除数据n n语法：707

58、0DELETEFROM表名表名WHERE条件条件【例例4-14】删除参加工作日期在删除参加工作日期在2004-1-1之后的用户信息DELETEFROMUserInfoWHEREhiredate2004-1-1【例例4-15】删除BookInfo表中书号是B0001的记录DELETEFROMBookInfoWHEREBookID=B0001因为要删除BookInfo表的书号是B0001的记录被SalesDetails表引用，所以必须先删除SalesDetails表的相应记录，才能删除BookInfo表的记录71717171使用TRUNCATETABLE语句n n语法：7171TRUNCATETA

59、BLE表名表名【例例4-16】删除CopyUserInfo表的所有数据TRUNCATETABLECopyUserInfo72727272总结n n增加单行数据用INSERT INTOINSERT INTOVALUESVALUES语句语句n n修改数据用UPDATE UPDATE 表名表名 SET SET 字段名字段名=新值新值 WHERE WHERE 条件。语句条件。语句n n删除数据用DELETE FROM WHERE DELETE FROM WHERE 语句语句n n快速删除整个表中的数据用TRUNCATE TABLE 语句 TRUNCATE TABLE TRUNCATE TABLE 表名

60、表名7272第5章 简单查询和函数737374747474本章目标n n掌握查询的基本语法n n掌握各种函数的应用字符串函数字符串函数日期函数日期函数数字函数数字函数n n掌握函数在查询中的应用747475757575查询查询7575查询请求查询请求查询结果集查询结果集结果集结果集76767676如何查询查询性别“男”的所有用户767677777777查询查询 语法：7777SELECTFROMWHEREORDERBYASC或DESC78787878查询数据n n返回所有的行和列返回所有的行和列查询表中所有字段时，可以用星号查询表中所有字段时，可以用星号* *来代替字来代替字段名段名n n返回

61、部分行和列返回部分行和列筛选部分字段，必须明确指定字段名筛选部分字段，必须明确指定字段名【例例5-25-2】查询查询查询查询BookInfoBookInfo表的表的表的表的BookName(BookName(书名书名书名书名),),作作作作者者者者(Author),(Author),出版日期出版日期出版日期出版日期(publishDate),(publishDate),类别类别类别类别ID(CategoryID),ID(CategoryID),价格价格价格价格(Price)(Price)等字段的数据等字段的数据等字段的数据等字段的数据 7878SELECT*FROMBookInfoSELECT

62、BookName,Author,publishDate,CategoryIDFROMBookInfo79797979查询-使用别名7979SELECTbookNameas书名书名,Authoras作者作者,publishDateas出版日期出版日期,CategoryIDas类别类别,priceas价格价格FROMbookInfo1，用，用as关键字关键字【例例5-3】用中文名称来显示书的信息,3种不同使用别名方法2，用空格，用空格3，用，用=SELECTbookName书名书名,Author作者作者,publishDate出版日期出版日期,CategoryID类别类别,price价格价格FRO

63、MbookInfoSELECT书名书名=bookName,作者作者=Author,出版日期出版日期=publishDate,类别类别=CategoryID,价格价格=priceFROMbookInfo80808080查询-使用wheren n使用WHERE子句筛选满足条件的数据行 8080【例例5-4】查询2007年以后出版的所有书籍的书名、作者、出版日期和价格SELECTbookName,author,publishDate,priceFROMBookInfoWHEREPublishDate2007-01-0181818181函数n nT-SQL函数表示可以接受零个、一个或多个输入值，并返回

64、一个标量值或一组值8181 表5-1 常用T-SQL函数函数类别函数类别 说明说明 字符串函字符串函字符串函字符串函数数数数 对字符串（对字符串（对字符串（对字符串（char char 或或或或 varcharvarchar）输入值执行运算，然后）输入值执行运算，然后返回一个字符串或数字值。返回一个字符串或数字值。日期和时日期和时日期和时日期和时间函数间函数间函数间函数 对日期和时间输入值执行运算，然后返回字符串、数对日期和时间输入值执行运算，然后返回字符串、数对日期和时间输入值执行运算，然后返回字符串、数对日期和时间输入值执行运算，然后返回字符串、数字或日期和时间值。字或日期和时间值。字或日

65、期和时间值。字或日期和时间值。数学函数数学函数数学函数数学函数 基于作为函数的参数提供的输入值执行运算，然后返基于作为函数的参数提供的输入值执行运算，然后返基于作为函数的参数提供的输入值执行运算，然后返基于作为函数的参数提供的输入值执行运算，然后返回数字值。回数字值。回数字值。回数字值。系统函数系统函数系统函数系统函数返回系统配置信息、返回系统配置信息、返回系统配置信息、返回系统配置信息、返回有关数据库和数据库对象的返回有关数据库和数据库对象的返回有关数据库和数据库对象的返回有关数据库和数据库对象的信息、执行运算后返回信息、执行运算后返回信息、执行运算后返回信息、执行运算后返回 SQL Ser

66、ver SQL Server 实例中有关值、实例中有关值、实例中有关值、实例中有关值、对象和设置的信息，对象和设置的信息，对象和设置的信息，对象和设置的信息，返回有关用户和角色的信息等。返回有关用户和角色的信息等。返回有关用户和角色的信息等。返回有关用户和角色的信息等。82828282字符串函数n n字符串函数 字符串函数用于对字符串数据进行处理，如字字符串函数用于对字符串数据进行处理，如字符串截取、查找子串、计算字符串长度等，并符串截取、查找子串、计算字符串长度等，并返回一个字符串或数值返回一个字符串或数值 828283838383字符串函数838384848484字符串函数的使用8484S

67、ELECTbookName,Author,PATINDEX(%数据库数据库%,bookName)POSFROMBookInfoWHEREPATINDEX(%数据库数据库%,bookName)0【例例5-8】使用日期函数获取今天的日期时间及明天的日期和时间使用日期函数获取今天的日期时间及明天的日期和时间【例例5-7】使用使用PATINDEX函数查询函数查询BookInfo表中包含表中包含数据库数据库的书名和作的书名和作者以及者以及数据库数据库出现的位置出现的位置SELECTGETDATE()今天今天,DATEADD(DAY,1,GETDATE()明天明天85858585日期和时间函数858586

68、868686系统函数868687878787总结n n掌握查询的基本语法从表中筛选记录的时候用从表中筛选记录的时候用SELECTSELECT语句语句通过给列起别名的方式简化代码通过给列起别名的方式简化代码n n掌握各种函数的应用字符串函数字符串函数日期函数日期函数数字函数数字函数n n掌握函数在查询中的应用8787第6章 检索数据888889898989本章目标n n掌握WHERE子句中运算符的使用n n掌握使用Top关键字和使用Distinct消除重复行n n掌握模糊查询Like,between,in的用法898990909090在WHERE子句中使用运算符n nSELECT通过WHERE子

69、句构造记录的筛选条件，从而选择出满足条件的记录 n n记录的筛选条件：运算符运算符 函数函数使用使用 IS NULLIS NULL条件条件909091919191运算符n n使用比较运算符和逻辑运算符 9191【例6-1】查询BookInfo表中价格低于的书名、作者和价格SELECTBookName,Author,PriceFROMBookInfoWHEREPrice40【例6-2】查询BookInfo表中价格在30到80之间（包括边界）的书名、价格和说明SELECTBookName,price,ContentFROMBookInfoWHEREPrice=3092929292使用ISNULL条

70、件n n使用 IS NULL条件9292【例6-3】查询UserInfo中电话（tele）为空的用户信息SELECTUserID,UserName,Sex,Age,hiredate,tele,AddrFROMUserInfoWHEREteleisnull93939393查询中使用函数9393【例6-5】在BookInfo表中，查询书名长度小于10的图书书名、作者和书名长度SELECTbookname,Author,LEN(bookName)FROMBookInfoWHERELEN(bookname)1094949494设置结果集格式n n使用ORDER BY排序数据 9494【例6-6】在Bo

71、okInfo表中，按出版日期的先后顺序，查询价格低于45的图书的书号、书名、价格、作者和出版日期。SELECTbookid,bookName,Price,Author,PublishDateFROMBookInfoWHEREPriceb.PriceWHEREb.BookID=B0002125125125125总结总结n n对数据进行统计使用对数据进行统计使用对数据进行统计使用对数据进行统计使用SQLSQLSQLSQL内置的聚合函数内置的聚合函数内置的聚合函数内置的聚合函数 MaxMax MinMin SumSum AvgAvg CountCountn n对数据进行分组用对数据进行分组用对数据进

72、行分组用对数据进行分组用GROUP BYGROUP BYGROUP BYGROUP BY，分组条件筛选用，分组条件筛选用，分组条件筛选用，分组条件筛选用HavingHavingHavingHavingn n查询的数据来自于多个表用关联查询查询的数据来自于多个表用关联查询查询的数据来自于多个表用关联查询查询的数据来自于多个表用关联查询n n关联查询关联查询连接连接 内联接内联接n n内联接筛选多表中完全匹配的行内联接筛选多表中完全匹配的行内联接筛选多表中完全匹配的行内联接筛选多表中完全匹配的行 外部联接外部联接n n左外联接以左表为主筛选数据左外联接以左表为主筛选数据左外联接以左表为主筛选数据左

73、外联接以左表为主筛选数据n n右外联接以右表为主筛选数据右外联接以右表为主筛选数据右外联接以右表为主筛选数据右外联接以右表为主筛选数据 自联接自联接n n自联接将表自身通过别名关联自联接将表自身通过别名关联自联接将表自身通过别名关联自联接将表自身通过别名关联125125第8章 子查询126126127127127127本章目标n n掌握子查询的使用子查询返回单一值子查询返回单一值子查询返回值列表子查询返回值列表子查询的存在性测试子查询的存在性测试EXISTSEXISTSn n掌握集合运算并集并集(Union)(Union)交集交集(Intersection)(Intersection)减减(E

74、xcept)(Except)127127128128128128子查询n n问题：显示价格最高书籍的书号、书名和价格 n n解决方法：查询出最高价格查询出最高价格n nselect MAX(price) from BookInfoselect MAX(price) from BookInfo以价格为条件查询出书号、书名和价格以价格为条件查询出书号、书名和价格n nselect bookid,bookname,price from BookInfoselect bookid,bookname,price from BookInfo where Price=where Price=(select

75、MAX(price) from (select MAX(price) from BookInfo)BookInfo) 128128外部查询内部查询129129129129子查询n n子查询就是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询，n n子查询也称为内部查询，而包含子查询的语句也称为外部查询 n n子查询的SELECT 查询总是使用圆括号括起来。基本语法：129129SELECT 列名.FROM 表名WHERE 条件 =(SELECT 列名 FROM 表名 WHERE 条件）130130130130返回单个值的子查询n n当子查询返回单

76、个值时，外部查询条件和子查询之间使用比较运算符（、=、(SELECTAVG(price)FROMBookInfo)比较运算符131131131131返回单个值的子查询 查询书名为查询书名为“ “JavaWebJavaWeb开发技术详解开发技术详解” ”的出版社的出版社IDID和出和出版年限版年限 n nSELECT publisherID FROM BookInfo WHERE SELECT publisherID FROM BookInfo WHERE BookName =JavaWebBookName =JavaWeb开发技术详解开发技术详解 n nSELECT YEAR(publishD

77、ate) FROM BookInfo WHERE SELECT YEAR(publishDate) FROM BookInfo WHERE BookName =JavaWebBookName =JavaWeb开发技术详解开发技术详解 代码为：代码为：n nSELECT BookID,BookName,publishDate,PublisherID SELECT BookID,BookName,publishDate,PublisherID FROM BookInfo FROM BookInfo WHERE PublisherID =(SELECT publisherID FROM WHERE

78、PublisherID =(SELECT publisherID FROM BookInfo WHERE BookName =JavaWebBookInfo WHERE BookName =JavaWeb开发技术详解开发技术详解) ) AND YEAR(PublishDate) = (SELECT AND YEAR(PublishDate) = (SELECT YEAR(publishDate) FROM BookInfo WHERE YEAR(publishDate) FROM BookInfo WHERE BookName =JavaWebBookName =JavaWeb开发技术详解开发

79、技术详解 ) )131131【例8-2】查询具有和“JavaWeb开发技术详解”相同出版社和相同出版年限的书籍的书号、书名和出版日期解决方法：解决方法：132132132132返回值列表的子查询n n子查询返回值列表即子查询返回的不是单个值，外部查询的检索条件WHERE和子查询之间使用以下运算符 132132133133133133返回值列表的子查询【例8-3】查询有销售记录的人员信息先求子查询，即在销售主表先求子查询，即在销售主表SalesMasterSalesMaster中查中查找销售人员找销售人员ID ID n nSELECT UserID FROM SalesMasterSELECT

80、UserID FROM SalesMaster因为销售人员不止一个，所以外部查询和子查因为销售人员不止一个，所以外部查询和子查询之间使用询之间使用ININn nSELECT * FROM UserInfo SELECT * FROM UserInfo WHERE UserID IN (SELECT UserID FROM WHERE UserID IN (SELECT UserID FROM SalesMaster) SalesMaster) 133133134134134134返回值列表的子查询n n【例8-3】显示价格高于类别JAVA的所有书籍的书号、书名、作者和价格。n n分析：先求出类

81、别是先求出类别是“ “JAVAJAVA“ “的所有书籍的价格列表的所有书籍的价格列表 n nSELECT Price,BookID,CategoryID FROM SELECT Price,BookID,CategoryID FROM BookInfo WHERE CategoryID =BookInfo WHERE CategoryID = (SELECT categoryid FROM Category WHERE (SELECT categoryid FROM Category WHERE CategoryName =JAVA)CategoryName =JAVA)外部查询和子查询之间应

82、该使用外部查询和子查询之间应该使用ALLALLn nSELECT BookID ,BookName ,Author,Price SELECT BookID ,BookName ,Author,Price FROM BookInfo WHERE Price ALL(SELECT FROM BookInfo WHERE Price ALL(SELECT Price FROM BookInfo WHERE CategoryID Price FROM BookInfo WHERE CategoryID =(SELECT categoryid FROM Category =(SELECT categor

83、yid FROM Category WHERE CategoryName =JAVA)WHERE CategoryName =JAVA) ) ) 134134值列表135135135135EXISTS存在性测试存在性测试n n相关子查询在子查询中引用外部查询的列，即子查询的执在子查询中引用外部查询的列，即子查询的执行与外部查询有关行与外部查询有关 135135【例8-4】显示同类图书中价格最高的的书籍信息，显示内容包括书号、书名、价格和类别。分析：对BookInfo表的每一条记录，与同类图书的价格比较，求出最高价格的书籍SELECTBookID,BookName,Price,CategoryI

84、DFROMBookInfoaWHEREa.Price=(SELECTmax(b.price)FROMBookInfobWHEREa.CategoryID=b.CategoryID)136136136136EXISTS存在性测试n nEXISTS存在性测试 指定一个子查询，测试行是否存在，也就是对指定一个子查询，测试行是否存在，也就是对于外部查询的每一行，检查子查询是否是空集于外部查询的每一行，检查子查询是否是空集合，如不是空集合，主查询返回值，否则主查合，如不是空集合，主查询返回值，否则主查询没有返回值询没有返回值 该题目涉及销售细表该题目涉及销售细表SalesDetailsSalesDeta

85、ils和书籍表和书籍表BookInfo,BookInfo,因为查询的是书籍信息，所以因为查询的是书籍信息，所以BookInfoBookInfo表应该放在外部查询位置，销售细表表应该放在外部查询位置，销售细表SalesDetailsSalesDetails应放在子查询位置应放在子查询位置 136136【例8-5】查询已销售的书籍信息，显示内容包括书号、书名和作者。SELECTBookID,BookName,AuthorFROMBookInfoWHEREEXISTS(SELECT*FROMSalesDetailsWHEREBookID=BookInfo.BookID)137137137137NOT

86、 ExistsNOT Exists子查询子查询【例例8-68-6】查询没有销售记录的用户信息，显示内查询没有销售记录的用户信息，显示内容包括用户容包括用户IDID、用户名和入职月份。、用户名和入职月份。 分析：分析：n n该题目外部查询是用户该题目外部查询是用户UserInfoUserInfo表，子查询是销售表，子查询是销售主表主表SalesMaster,SalesMaster,由于查询的是没有销售记录的用由于查询的是没有销售记录的用户，所以使用户，所以使用NOT EXISTSNOT EXISTS关键字判断子查询是否关键字判断子查询是否有记录返回有记录返回 137137用户id用户idSELE

87、CTa.UserId,a.UserName,YEAR(a.hiredate)入职年份入职年份FROMUserInfoaWHERENOTEXISTS(SELECT*FROMSalesMasterbWHEREb.UserID=a.UserID)138138138138集合操作n n集合操作是一种以两个或多个SELECT 查询结果集为操作对象，通过对这些查询结果集实施并、交、差等集合运算，获取新的结果集的一种查询方法 n n 集合操作的运算符主要包括以下3种 并集并集(UNION)(UNION)交集交集(INTERSECT)(INTERSECT)差差(EXCEPT)(EXCEPT)138138139

88、139139139并集（UNION）n n并集并集是将两个或更多查询的结果合并为单个结并集是将两个或更多查询的结果合并为单个结果集，该结果集包含联合查询中的所有查询的果集，该结果集包含联合查询中的所有查询的全部行全部行 n nUNION的使用限制：集合操作符所涉及的查询应有相同的列数，对集合操作符所涉及的查询应有相同的列数，对应的列必须具有相同的数据类型。应的列必须具有相同的数据类型。所显示的行上面的标签来自第一个所显示的行上面的标签来自第一个SELECTSELECT语语句。句。n nUNION和UNION ALLUNION ALL:UNION ALL:返回两个查询所选定的所有行，返回两个查询

89、所选定的所有行，包括重复行包括重复行UNION:UNION:未指定未指定ALL,ALL,返回的结果集中不包括重返回的结果集中不包括重复行复行 139139140140140140并集（UNION）【例例8-78-7】 把把“ “机械工业出版社机械工业出版社” ”出版的图书和出版的图书和“ “人民邮电出版社人民邮电出版社” ”出版社的图书一起显示输出出版社的图书一起显示输出l l查询“机械工业出版社”的图书的T-SQL如下l l查询“人民邮电出版社”的图书的T-SQL如下 140140SELECTBookID,BookName,Author,PublisherIDFROMBookInfoWHER

90、EPublishERID=(SELECTPublisherIDFROMPublisherWHEREPublisherName=机械工业出版社机械工业出版社)SELECTBookID,BookName,Author,PublisherIDFROMBookInfoWHEREPublishERID=(SELECTPublisherIDFROMPublisherWHEREPublisherName=人民邮电出版社人民邮电出版社)SELECTBookID,BookName,Author,PublisherIDFROMBookInfoWHEREPublishERID=(SELECTPublisherIDF

91、ROMPublisherWHEREPublisherName=机械工业出版社机械工业出版社)UNIONSELECTBookID,BookName,Author,PublisherIDFROMBookInfoWHEREPublishERID=(SELECTPublisherIDFROMPublisherWHEREPublisherName=人民邮电出版社人民邮电出版社)141141141141并集（UNION）【例8-8】将新数据显示在查询中141141SELECT009AS用户用户ID,张三张三as用户名用户名,男男as性别性别,20as年龄年龄UNIONSELECTUserID,UserNa

92、me,Sex,AgeFROMUserInfo142142142142交集(INTERSECT)n n交集(INTERSECT) INTERSECTINTERSECT运算符比较两个查询的结果，返运算符比较两个查询的结果，返回重复值，即返回两个结果集中的交集，两者回重复值，即返回两个结果集中的交集，两者都包含的记录都包含的记录 INTERSECTINTERSECT运算符的使用限制与运算符的使用限制与UNIONUNION运算运算符相同。符相同。【例例8-98-9】查询有销售记录的书籍查询有销售记录的书籍142142SELECTBookIDas书书IDFROMSalesDetailsINTERSECT

93、SELECTBookIDFROMBookInfo143143143143差(EXCEPT)n n差(EXCEPT)运算符比较两个查询的结果，返回非重复值。运算符比较两个查询的结果，返回非重复值。EXCEPTEXCEPT运算符获取在结果集运算符获取在结果集A A中但不在结果集中但不在结果集B B中的记录，即结果集中的记录，即结果集A A减去结果集减去结果集B B EXCEPTEXCEPT运算符的使用限制与运算符的使用限制与UNIONUNION运算符相运算符相同同 143143144144144144差(EXCEPT)【例例8-108-10】查询查询BookInfoBookInfo表中价格低于表中

94、价格低于4040的书的书籍中去掉籍中去掉20062006年出版的书籍信息，显示内容包年出版的书籍信息，显示内容包括书号、书名、价格和出版日期括书号、书名、价格和出版日期 144144SELECTBookID书书ID,BookName书名书名,Price价格价格,PublishDate出版日期出版日期FROMBookInfoWHEREPrice40EXCEPTSELECTBookID,BookName,Price,PublishDateFROMBookInfoWHEREYEAR(PublishDate)=2006145145145145总结总结n n子查询就是嵌套在子查询就是嵌套在SELECTS

95、ELECT，INSERTINSERT，UPDATEUPDATE和和DELETEDELETE中的查询。中的查询。n n一般子查询不依赖于外部查询，执行时先执行内部一般子查询不依赖于外部查询，执行时先执行内部查询再执行外部查询，通常用比较运算符和查询再执行外部查询，通常用比较运算符和ININ，NOT INNOT IN关键字。关键字。n n相关子查询即子查询的条件依赖于外部查询，执行相关子查询即子查询的条件依赖于外部查询，执行时先执行外时先执行外 部查询，然后根据外部查询返回的记录行数重复部查询，然后根据外部查询返回的记录行数重复执行内部查询通执行内部查询通 常用常用EXISTSEXISTS，NOT

96、 EXISTSNOT EXISTS关关键字。键字。n n集合运算集合运算 结果集要进行合并时用结果集要进行合并时用UNIONUNION运算符。运算符。 结果集取交集用结果集取交集用INTERSECTINTERSECT运算符。运算符。 结果集相减用结果集相减用EXCEPTEXCEPT运算符。运算符。145145146146146146第第9 9章章 数据库的设计数据库的设计147147147147本章目标本章目标了解设计数据库的步骤了解设计数据库的步骤掌握掌握数据库设计模型数据库设计模型ER模型模型掌握使用范式规范化数据库掌握使用范式规范化数据库148148148148为什么需要设计数据库为什么

97、需要设计数据库给宠物盖个小窝需要设计吗？给宠物盖个小窝需要设计吗？修建一座大厦需要设计吗？修建一座大厦需要设计吗？149149149149什么是数据库什么是数据库设计设计?什么是数据库设计什么是数据库设计?l数据库设计就是规划和结构化数据库中的数据对象数据库设计就是规划和结构化数据库中的数据对象以及这些数据对象之间关系的过程。以及这些数据对象之间关系的过程。150150150150数据库数据库设计设计的重要性的重要性良好的数据库设计良好的数据库设计l节省数据的存储空间节省数据的存储空间l能够保证数据的完整性能够保证数据的完整性l方便进行数据库应用系统的开发方便进行数据库应用系统的开发糟糕的数据

98、库设计：糟糕的数据库设计：l数据冗余、存储空间浪费数据冗余、存储空间浪费l内存空间浪费内存空间浪费l数据更新和插入的异常数据更新和插入的异常151151151151设计数据库的方法和步骤设计数据库的方法和步骤一。需求分析：一。需求分析：收集数据库所需要的信息内容和用户对处理收集数据库所需要的信息内容和用户对处理的要求，加以规格化和分析，以书面形式确定下来的要求，加以规格化和分析，以书面形式确定下来二。概念设计：二。概念设计：逻辑结构设计是将概念结构转换为某个逻辑结构设计是将概念结构转换为某个DBMS所支持的数据模型，并对其进行优化所支持的数据模型，并对其进行优化。三。逻辑结构：三。逻辑结构：将

99、概念结构转换为某个将概念结构转换为某个DBMS所支持的数据所支持的数据模型，并对其进行优化。模型，并对其进行优化。四。物理设计：四。物理设计：一个完整的能实现的数据库结构一个完整的能实现的数据库结构。五。数据库的实施：五。数据库的实施：根据物理设计的结果产生一个具体的数根据物理设计的结果产生一个具体的数据库和它的应用程序，并把原始数据装入数据库。据库和它的应用程序，并把原始数据装入数据库。六。数据库的运行和维护：六。数据库的运行和维护：数据库应用系统经过试运行后即数据库应用系统经过试运行后即可投入正式运行可投入正式运行152152152152E-R模型模型在需求分析阶段根据用户的需求分析出用户

100、真正需要存储的在需求分析阶段根据用户的需求分析出用户真正需要存储的相关数据，以及数据之间的关系。能够标识数据以及关系相关数据，以及数据之间的关系。能够标识数据以及关系的方法即的方法即ER模型。模型。实体实体(Entity)即客观存在的事物以及一些抽象的概念。而即客观存在的事物以及一些抽象的概念。而实体往往是一些名词实体往往是一些名词。比如学生，老师等。比如学生，老师等属性属性(Attribute)用于描述实体的特征，比如可以用学号，用于描述实体的特征，比如可以用学号，姓名，年龄等属性描述学生实体的特性。姓名，年龄等属性描述学生实体的特性。关系关系(Relation)即实体和实体之间的联系。即实

101、体和实体之间的联系。比如一个老师比如一个老师教多个学生。教多个学生。153153153153绘制绘制E-R图图E-R（EntityRelationship）实体关系图）实体关系图符合符合含义含义实体，一般是名词实体，一般是名词属性，一般是名词属性，一般是名词关系，一般是动词关系，一般是动词154154154154绘制绘制E-R图图学习学习学生学生姓名姓名学号学号课程号课程号课程名课程名课时课时课程课程155155155155绘制绘制E-R图图映射基数映射基数一对一一对一(人：身份证）人：身份证）XXXXYYYYXXXXYYYY一对多（部门：员工）一对多（部门：员工）XXXXYYY多对一（学生：

102、课程）多对一（学生：课程）XXXXYYYY多对多（读者：图书）多对多（读者：图书）156156156156E-R图图学生管理学生管理系统系统E-R图图157157157157将将E-R图转换为表图转换为表u所有的实体转换为表，实体的属性就是表的字段所有的实体转换为表，实体的属性就是表的字段u一对一关系的两个实体最终变为一张表，因为两个实一对一关系的两个实体最终变为一张表，因为两个实体的记录是一一对应的。体的记录是一一对应的。u一对多关系中，在一对多关系中，在“多多”的一端添加的一端添加“一一”的一端的的一端的主键字段作为主键字段作为“多多”端表的一个属性并给该字段添加约端表的一个属性并给该字段

103、添加约束为外键。束为外键。u多对多关系中，创建一个中间表（由关系生成的表），多对多关系中，创建一个中间表（由关系生成的表），该表除了取关系本身的属性做字段外，将关系的该表除了取关系本身的属性做字段外，将关系的“多多”端的所有的实体的主键字段组合起来作为该表的主键端的所有的实体的主键字段组合起来作为该表的主键158158158158将将E-R图转化为数据表图转化为数据表学生管理学生管理系统表结构系统表结构159159159159非规范化数据库非规范化数据库编号姓名年龄籍贯职务基本工资津贴加班时间实际工资E001王军华35辽宁沈阳工程师3000500154250E002李艳45江苏常州总工程师50

104、00150057250E003和晓33甘肃兰州高工400080055200E004张忠国27湖南长沙助工2500200203100E005马国明30甘肃武威高工4000800105600非规范化数据库存在以下问题：非规范化数据库存在以下问题：u数据冗余数据冗余u更新异常更新异常u删除异常删除异常u插入异常插入异常工资表容纳了所有数据，存在什么问题？工资表容纳了所有数据，存在什么问题？160160160160数据规范化数据规范化仅有好的仅有好的RDBMS并不足以避免数据冗余，必须在数据并不足以避免数据冗余，必须在数据库的设计中创建好表的结构库的设计中创建好表的结构DrE.F.codd最初定义了规

105、范化的三个级别，范式是具最初定义了规范化的三个级别，范式是具有最小冗余的表结构。这些范式是：有最小冗余的表结构。这些范式是：l第一范式第一范式(1stNFFirstNormalForm)l第二范式第二范式(2ndNFSecondNormalForm)l第三范式第三范式(3rdNFThirdNormalForm)161161161161第一范式第一范式(1stNF)第一范式的目标是确保每列的原子性第一范式的目标是确保每列的原子性如果每列都是不可再分的最小数据单元（也称为最小的原如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（子单元），则满足第一范式（1NF）籍贯籍贯辽

106、宁沈阳江苏常州甘肃兰州湖南长沙甘肃武威省份省份城市城市辽宁沈阳江苏常州甘肃兰州湖南长沙甘肃武威162162162162第二范式第二范式(2ndNF)如果一个关系满足如果一个关系满足1NF，并且除了主键以外的其他列，都依，并且除了主键以外的其他列，都依赖与该主键，则满足第二范式（赖与该主键，则满足第二范式（2NF）第二范式要求每个表只描述一件事情第二范式要求每个表只描述一件事情职务编号职务名称1工程师2总工程师3高工4助工职务表：职务表： 编号加班时间实际工资E001154250E00257250E00355200E004203100E005105600编号姓名年龄省份城市职务编号基本工资E00

107、1王军华35辽宁沈阳13000E002李艳45江苏常州25000.职务表职务表工资表工资表员工表员工表163163163163第三范式第三范式(3rdNF)如果一个关系满足如果一个关系满足2NF，并且除了主键以外的其他列，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（都不传递依赖于主键列，则满足第三范式（3NF）编号姓名年龄省份城市职务编号E001王军华 35辽宁沈阳1E002李艳45江苏常州2职务编号职务名称基本工资津贴1工程师30005002总工程师50001500.职务表职务表职务表职务表164164164164规范化和性能的关系规范化和性能的关系为满足某种商业目标，数据

108、库性能比规范化数据库更重要为满足某种商业目标，数据库性能比规范化数据库更重要l通通过过在在给给定定的的表表中中添添加加额额外外的的字字段段，以以大大量量减减少少需需要要从从中中搜搜索索信信息所需的时间息所需的时间进行规范化的同时，还需要综合考虑数据库的性能进行规范化的同时，还需要综合考虑数据库的性能l规规范范化化级级别别越越高高，数数据据冗冗余余越越少少，插插入入异异常常，删删除除异异常常，更更新新异常也越少，但是查询性能低异常也越少，但是查询性能低165165165165总结总结在需求分析阶段，设计数据库的一般步骤为：在需求分析阶段，设计数据库的一般步骤为：l收集信息收集信息l标识对象标识对

109、象l标识每个对象的属性标识每个对象的属性l标识对象之间的关系标识对象之间的关系在概要设计阶段和详细设计阶段，设计数据库的步骤为：在概要设计阶段和详细设计阶段，设计数据库的步骤为：l绘制绘制E-R图图l将将E-R图转换为表格图转换为表格l应用三大范式规范化表格应用三大范式规范化表格166166166166总结总结为了设计结构良好的数据库，需要遵守一些专门的规则，为了设计结构良好的数据库，需要遵守一些专门的规则，称为数据库的设计范式。称为数据库的设计范式。l第一范式（第一范式（1NF）的目标：确保每列的原子性。）的目标：确保每列的原子性。l第二范式（第二范式（2NF）的目标：确保表中的每列，都和主

110、键相关）的目标：确保表中的每列，都和主键相关。l第三范式（第三范式（3NF）的目标：确保每列都和主键列直接相关，而不）的目标：确保每列都和主键列直接相关，而不是间接相关是间接相关。167167167167第第1010章章 T-SQLT-SQL编程编程168168168168本章目标本章目标掌握如何定义变量并赋值掌握如何定义变量并赋值掌握如何输出显示数据掌握如何输出显示数据掌握掌握IF、WHILE、CASE逻辑控制语句逻辑控制语句理解理解SQL中批处理的概念中批处理的概念169169169169使用变量使用变量变量分为局部变量和全局变量变量分为局部变量和全局变量局部变量局部变量l使用使用DECL

111、ARE定义定义,声明时必须在变量名前加声明时必须在变量名前加l局部变量必须先声明后赋值局部变量必须先声明后赋值如如:变量名变量名vname定义成定义成vname全局变量全局变量l标识为两个标识为两个标记标记()l由由SQLServer提供的，不能由用户创建。提供的，不能由用户创建。l全局变量提供关于全局变量提供关于SQLServer的当前状态信息的当前状态信息如如:SQLServer当前版本号当前版本号:version170170170170局部变量的声明和赋值局部变量的声明和赋值局部变量的声明局部变量的声明l语法语法:l例如例如:局部变量的赋值有两种方法局部变量的赋值有两种方法l语法语法:l

112、例如例如DECLAREvariable_nameDataTypeDECLAREcountint-声明一个局部变量声明一个局部变量countDECLAREenamevarchar(8)-声明一个局部变量声明一个局部变量ename,最最多可存储个字符多可存储个字符SETvariable_name=value或或SELECTvariable_name=valueSETcount=10或或SELECTcount=10SETename=SMITH或或SELECTename=SMITH171171171171局部变量的使用局部变量的使用SET赋值语句一般用于赋给变量指定的数据常量赋值语句一般用于赋给变量指

113、定的数据常量SELECT赋值语句一般用于从表中查询数据，然后再赋给变量。赋值语句一般用于从表中查询数据，然后再赋给变量。需要注意的是需要注意的是:SELECT语句需要确保筛选的记录不多于一条。如果查语句需要确保筛选的记录不多于一条。如果查询的记录多于一条，将把最后一条记录的值赋给变量。询的记录多于一条，将把最后一条记录的值赋给变量。根据用户名查找和根据用户名查找和“张敏张敏“的地址相同的用户信息的地址相同的用户信息DECLAREnamevarchar(20)定义用户名定义用户名DECLAREaddrvarchar(20)定义地址定义地址SETname=张敏张敏使用使用set给用户名赋值给用户名

114、赋值SELECTaddr=addrFROMUserInfo使用使用select存储张敏的地址存储张敏的地址WHEREUserName=nameSELECT*FROMUserInfoWHEREAddr=addr筛选数据筛选数据172172172172局部变量的特点局部变量的特点一个一个SELECT语句可以初始化多个局部变量。语句可以初始化多个局部变量。DECLAREbookNamenvarchar(100),authornvarchar(100)-声明多个变量声明多个变量SETbookName=java,author=孙天琴孙天琴-使用使用SET给多个变量赋值给多个变量赋值SElECTbookN

115、ame=c#,author=明日明日-使用使用SELECT给多个变量赋值给多个变量赋值SETbookName=(SELECTBookNameFROMBookInfo)-SET将多个查询的结果赋值给变量出错将多个查询的结果赋值给变量出错SELECTauthor=AuthorfromBookInfo-使用使用SELECT将查询的最有一个值赋给变量将查询的最有一个值赋给变量SETauthor=(SELECTAuthorFROMBookInfoWHEREPrice0)-查询结果不存在时将查询结果不存在时将NULL赋给变量赋给变量SELECTauthor=林林林林SELECTauthor=AuthorF

116、ROMBookInfoWHEREPrice10)printconvert(varchar(10),saleDate,112)+销售良好销售良好,销售的数量是销售的数量是+cast(quantityasvarchar(10)+本本elseif(quantity50then优秀优秀whenSUM(b.quantity)between30and50then良好良好whenSUM(b.quantity)between10and210then一般一般whenSUM(b.quantity)between1and10then差差else没有销售没有销售ENDfromBookInfoaleftjoinSale

117、sDetailsbona.BookID=b.BookIDgroupbya.BookNameorderbySUM(b.quantity)desc186186186186WHILE循环语句循环语句设置重复执行设置重复执行SQL语句或语句块的条件。只要指定的条语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用件为真，就重复执行语句。可以使用BREAK和和CONTINUE关键字在循环内部控制关键字在循环内部控制WHILE循环中语句的循环中语句的执行执行语法语法:WHILE（条件）（条件）语句或语句块语句或语句块BREAK187187187187WHILE循环语句示例循环语句示例T-SQL

118、代码实现九九乘法表代码实现九九乘法表DECLAREiintDECLAREjintDECLAREstrvarchar(110)SETi=1WHILEi10BEGINSETj=1SETstr=WHILEj=iBEGINSETstr=str+CAST(iASvarchar(1)+x+CAST(jASvarchar(1)+=+CAST(i*jASvarchar(2)+SETj=j+1ENDprintstrSETi=i+1END188188188188批处理批处理批处理是包含一个或多个批处理是包含一个或多个SQL语句的组，从应用程语句的组，从应用程序一次性地发送到序一次性地发送到SQLServer执行执

119、行SQLServer将批处理语句编译成一个可执行单元，将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一此单元称为执行计划。执行计划中的语句每次执行一条条客户端应用程序客户端应用程序SQLServer服务器服务器批处理语句：批处理语句：语句语句1语句语句2GO189189189189GO命令命令GO是是SQLServer的批处理命令，只有代码编辑器才能识的批处理命令，只有代码编辑器才能识别并处理，编辑其他应用程序就不能使用该命令。由于每别并处理，编辑其他应用程序就不能使用该命令。由于每个批处理之间是独立的，因此，在一个批处理出现错误时，个批处理之间是独立的，因此，

120、在一个批处理出现错误时，并不会影响其他批处理中并不会影响其他批处理中SQL代码的运行。一般是将一些代码的运行。一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定。务需求和代码编写者决定。190190190190批处理示例批处理示例创建一个历史销售纪录表创建一个历史销售纪录表His_Sales(Id，销售日期，图书，销售日期，图书ID，书名，销售数量，折扣，价格，销售价格，书名，销售数量，折扣，价格，销售价格)。将。将2013年之前的销售记录当成历史记录插入该表。年之前的销售记录当成历史记录插入该表。-创建历史销

121、售记录表创建历史销售记录表createtableHis_Sales(Idintidentity(1,1)primarykey,BookIdchar(5)notnull,BookNamevarchar(20)notnull,SaleDatedatetime,Quantityint,Discountfloat,PriceMoney,SalePriceMoney)go191191191191批处理示例批处理示例-插入历史记录插入历史记录insertintoSalesselectb.BookID,c.BookName,a.SalesDate,b.Quantity,b.Discount,b.Price,

122、b.Discount*b.PricefromSalesMastera,SalesDetailsb,BookInfocwherea.SalesMasterID=b.SalesMasterIDandb.BookID=c.BookIDanddatepart(yy,a.SalesDate)2013go-删除销售明细记录删除销售明细记录deletefromSalesDetailswhereSalesMasterIDin(selectSalesMasterIDfromSalesMasterwheredatepart(yy,SalesDate)2013)Go-删除销售主记录删除销售主记录deletefrom

123、SalesMasterwheredatepart(yy,SalesDate)2013go192192192192总结总结变量是可以存储数据值的对象。可以使用变量向变量是可以存储数据值的对象。可以使用变量向SQL语语句传递数据。句传递数据。SQLServer在在T-SQL中支持下列两种类型的变量：全局中支持下列两种类型的变量：全局变量和局部变量变量和局部变量局部变量以局部变量以开始，需要定义开始，需要定义;全局变量以全局变量以开始，不开始，不需要定义。需要定义。局部变量的赋值有两种方式：使用局部变量的赋值有两种方式：使用SET语句或语句或SELECT语句。语句。输出结果也有两种方式：输出结果也有

124、两种方式：print语句和语句和SELECT语句。语句。批处理是以一个单元发送的一条或多条批处理是以一个单元发送的一条或多条SQL语句的集合。语句的集合。每个批处理编译成一个执行计划。批处理可以提高语句每个批处理编译成一个执行计划。批处理可以提高语句执行效率，批处理结束的标志是执行效率，批处理结束的标志是“GO”注释是程序代码中的描述性的文字字符串，也称为注解，注释是程序代码中的描述性的文字字符串，也称为注解，编译器会忽略这些内容。编译器会忽略这些内容。控制流语句提供了条件操作所需的顺序和逻辑。控制流语句提供了条件操作所需的顺序和逻辑。语句块使用语句块使用BEGINEND19319319319

125、3第第1111章章 数据库事务数据库事务194194194194本章目标本章目标理解事务的概念理解事务的概念理解事务的特性、分类理解事务的特性、分类在在SQLServer中启动、提交和回滚事务中启动、提交和回滚事务掌握事务的隔离级别掌握事务的隔离级别195195195195为什么需要事务为什么需要事务事务能确保把对多个数据操作作为一个单元来处理事务能确保把对多个数据操作作为一个单元来处理例如：银行转帐例如：银行转帐l问题问题:假设张三的银行卡有元，李四的卡有假设张三的银行卡有元，李四的卡有元，共计元。现在张三要转元给元，共计元。现在张三要转元给李四，则要更新张三的帐户，减少余额元，更李四，则要

126、更新张三的帐户，减少余额元，更新李四帐户余额，增加元。如果整个流程没有新李四帐户余额，增加元。如果整个流程没有出错则最后张三余额元，李四元，共出错则最后张三余额元，李四元，共计元。数据和转帐之前是一致的，假设在减少计元。数据和转帐之前是一致的，假设在减少了张三的余额后，系统掉电或者出现意外李四了张三的余额后，系统掉电或者出现意外李四的帐户还没完成更新。则最终的数据元，那怎的帐户还没完成更新。则最终的数据元，那怎么解决？么解决？196196196196事务的概念及特性事务的概念及特性事务提供了一种机制、是一个操作序列，它包含了一组数事务提供了一种机制、是一个操作序列，它包含了一组数据库操作命令，

127、并且所有的命令作为一个整体一起向系统据库操作命令，并且所有的命令作为一个整体一起向系统提交或撤消操作请求提交或撤消操作请求事务是作为单个逻辑工作单元执行的一系列操作事务是作为单个逻辑工作单元执行的一系列操作事务的四个特性事务的四个特性:l原子性（原子性（Atomicity）：事务能确保把对多个数据修改作为一个单）：事务能确保把对多个数据修改作为一个单元来处理，也就是原子操作。元来处理，也就是原子操作。l一致性（一致性（Consistency）：当事务完成时，数据必须处于一致状）：当事务完成时，数据必须处于一致状态态l隔离性（隔离性（Isolation）:对数据进行修改的所有并发事务是彼此隔离对

128、数据进行修改的所有并发事务是彼此隔离的。这表明事务必须是独立的，它不应以任何方式依赖或影响其的。这表明事务必须是独立的，它不应以任何方式依赖或影响其他事务他事务l持久性（持久性（Durability）：当事务完成之后，它对于系统的影响是永）：当事务完成之后，它对于系统的影响是永久性的。久性的。197197197197事务的分类事务的分类事务分类事务分类l显式事务显式事务l隐性事务隐性事务l自动提交事务自动提交事务显式事务显式事务:显式事务是显式地定义其开始和结束的事务显式事务是显式地定义其开始和结束的事务BEGINTRANSACTION数据库操作如数据库操作如:插入记录插入记录数据库操作如数据

129、库操作如:删除记录删除记录COMMITTRANSACTION198198198198事务的分类事务的分类隐性事务隐性事务:通过通过Transact-SQL的的SETIMPLICIT_TRANSACTIONSON语句，将隐性事务模式设置为打开语句，将隐性事务模式设置为打开。SETIMPLICIT_TRANSACTIONSONGO/*第一次执行第一次执行Insert语句的时候将自动启动一个隐性事务语句的时候将自动启动一个隐性事务*/INSERTINTOImpTranVALUES(1,aaa)INSERTINTOImpTranVALUES(2,bbb)GO/*提交第一个事务提交第一个事务*/COMM

130、ITTRANSACTIONGO/*执行执行SELECT语句将启动第二个隐性事务语句将启动第二个隐性事务*/SELECTCOUNT(*)FROMImpTranGOINSERTINTOImpTranVALUES(3,ccc)GOSELECT*FROMImpTranGO/*提交第二个事务提交第二个事务*/COMMITTRANSACTIONGOSETIMPLICIT_TRANSACTIONSOFFGO199199199199事务的分类事务的分类自动提交事务自动提交事务:所有所有Transact-SQL语句在完成时，都会提语句在完成时，都会提交或回滚。如果一条语句成功完成，则将其提交，如果遇交或回滚。如

131、果一条语句成功完成，则将其提交，如果遇到任何错误，则将其回滚到任何错误，则将其回滚;默认操作模式默认操作模式200200200200用用T-SQL表示事务表示事务Transact-SQL使用下列语句来管理事务使用下列语句来管理事务l开始事务：开始事务：BEGINTRANSACTIONl提交事务：提交事务：COMMITTRANSACTIONl回滚（撤消）事务：回滚（撤消）事务：ROLLBACKTRANSACTION下列变量在事务处理中非常有用。下列变量在事务处理中非常有用。lERRORlTRANCOUNT201201201201事务的应用事务的应用示例：示例：客户购买图书客户购买图书USEBoo

132、kShopDbGO-一个客户在销售员编号为的用户处购买了三种书，生成销售记录一个客户在销售员编号为的用户处购买了三种书，生成销售记录-SETNOCOUNTON-不显示受影响的行数信息不显示受影响的行数信息GOBEGINTRANSACTION-开始事务开始事务DECLAREerrINT-保存错误编号保存错误编号PRINT客户开始买书客户开始买书,事务开始事务开始SETerr=0-插入销售主表插入销售主表SalesMasterINSERTINTOSalesMasterVALUES(GETDATE(),001)-声明局部变量保存声明局部变量保存SalesMasterIDSETerr=err+ERRO

133、RDECLARESalesMasterIDINT-获取最新的获取最新的SalesMasterID202202202202SELECTSalesMasterID=IDENTITY-购买三本书购买三本书INSERTINTOSalesDetailsVALUES(SalesMasterID,B0001,1,0.9,45)SETerr=err+ERRORINSERTINTOSalesDetailsVALUES(SalesMasterID,B0003,1,1,118)SETerr=err+ERRORINSERTINTOSalesDetailsVALUES(SalesMasterID,B0004,2,0.8

134、,28)SETerr=err+ERROR-购买完成购买完成,根据错误编号决定是否执行成功根据错误编号决定是否执行成功IFerr=0BEGINPRINT购买成功，事务结束购买成功，事务结束COMMITTRANSACTIONENDELSEBEGINROLLBACKTRANSACTIONPRINT购买失败，事务回滚购买失败，事务回滚ENDGO203203203203事务的隔离级别事务的隔离级别事务的隔离级别是指一个事务必须和其他事务在进行资源事务的隔离级别是指一个事务必须和其他事务在进行资源或者数据更改时相隔离的程度或者数据更改时相隔离的程度较低的隔离级别可以增强许多用户同时访问数据的能力，较低的隔

135、离级别可以增强许多用户同时访问数据的能力，但也增加了用户可能遇到的并发副作用（例如脏读或丢失但也增加了用户可能遇到的并发副作用（例如脏读或丢失更新）的数量。相反，较高的隔离级别减少了用户可能遇更新）的数量。相反，较高的隔离级别减少了用户可能遇到的并发副作用的类型，但需要更多的系统资源，并增加到的并发副作用的类型，但需要更多的系统资源，并增加了一个事务阻塞其他事务的可能性了一个事务阻塞其他事务的可能性204204204204SQLServer支持的隔离级别支持的隔离级别READUNCOMMITTED未提交读（隔离事务的最低级别）未提交读（隔离事务的最低级别）READCOMMITTED已提交读（数

136、据库引擎的默认级别）已提交读（数据库引擎的默认级别）REPEATABLEREAD可重复读可重复读SERIALIZABLE可序列化（隔离事务的最高级别，事务之可序列化（隔离事务的最高级别，事务之间完全隔离）间完全隔离）205205205205未提交读未提交读-第一个事务第一个事务USEBookShopDbGOSETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;GOBEGINTRANSACTIONGOSELECT*FROMpublisherWHEREPublisherID=1GOUPDATEPublisherSETPublisherName=PublisherN

137、ame+2WHEREPublisherID=1GOSELECT*FROMpublisherWHEREPublisherID=1GOWAITFORDELAY00:00:15GOROLLBACKTRANSACTION;GOSELECT*FROMpublisherWHEREPublisherID=1GO206206206206-第二个事务第二个事务USEBookShopDbGOSETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;GOBEGINTRANSACTIONGOSELECT*FROMpublisherWHEREPublisherID=1GOCOMMIT;GO

138、207207207207可重复读可重复读-第一个事务第一个事务USEBookShopDbGOSETTRANSACTIONISOLATIONLEVELREPEATABLEREAD;GOBEGINTRANSACTIONGOSELECT*FROMpublisherWHEREPublisherID=1GOWAITFORDELAY00:00:15GOSELECT*FROMpublisherWHEREPublisherID=1GOCOMMITTRANSACTION;GO208208208208-第二个事务第二个事务USEBookShopDbGOSETTRANSACTIONISOLATIONLEVELREP

139、EATABLEREAD;GOBEGINTRANSACTIONGOSELECT*FROMpublisherWHEREPublisherID=1GOUPDATEPublisherSETPublisherName=PublisherName+2WHEREPublisherID=1GOSELECT*FROMpublisherWHEREPublisherID=1GOCOMMITTRANSACTION;SELECT*FROMpublisherWHEREPublisherID=1GO209209209209数据库死锁数据库死锁在数据库的并发应用中在数据库的并发应用中,多个请求同时向数据库发送请求多个请求同时

140、向数据库发送请求,如果各个进程均占有不会释放的资源如果各个进程均占有不会释放的资源,但因互相申请被其他但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态。进程所站用不会释放的资源而处于的一种永久等待状态。这种状态就是常说的死锁这种状态就是常说的死锁210210210210死锁示例死锁示例USEBookShopDbGOBEGINTRANSACTIONUPDATEPublisherSETPublisherName=PublisherName+publisherWHEREPublisherID=1WAITFORDELAY00:00:20SELECT*FROMcategoryROLLB

141、ACKTRANSACTION等待等待20秒之后显示查询结果：秒之后显示查询结果：211211211211-第二个查询第二个查询USEBookShopDbGOBEGINTRANSACTIONUPDATEPublisherSETPublisherName=PublisherName+publisherWHEREPublisherID=1WAITFORDELAY00:00:20SELECT*FROMcategoryROLLBACKTRANSACTION等待等待20秒之后显示查询结果：秒之后显示查询结果：212212212212死锁解决方法死锁解决方法1.SQLServer自动选择一条自动选择一条SQ

142、L语句作牺牲品，我们会发语句作牺牲品，我们会发现两个查询中第一个正常执行，第二个则抛出错误。现两个查询中第一个正常执行，第二个则抛出错误。2.按照同一顺序访问资源：调换查询二中按照同一顺序访问资源：调换查询二中Update语句和语句和Select语句的顺序。语句的顺序。3.SELECT语句加语句加With(NoLock)提示提示4.降低事务的隔离级别降低事务的隔离级别SELECT*FROMcategoryWITH(NOLOCK)SELECT*FROMPublisherWITH(NOLOCK)213213213213总结总结事务是一系列不可分割的操作，要么全部都作要么全都不事务是一系列不可分割的

143、操作，要么全部都作要么全都不做。做。事务具有原子性，一致性，隔离性，持久性事务具有原子性，一致性，隔离性，持久性事务隔离级别越高，并发行越低事务隔离级别越高，并发行越低事务如果设计不合理会导致死锁事务如果设计不合理会导致死锁214214214214第第1212章章 索引和视图索引和视图 215215215215本章目标本章目标掌握视图的优点掌握视图的优点掌握视图的创建和使用掌握视图的创建和使用掌握索引的有点掌握索引的有点掌握索引的创建掌握索引的创建216216216216视图的概念及优点视图的概念及优点概念概念:l视图是从不同的视角查看数据库中一个或多个表中数据的方法。视图是视图是从不同的视角

144、查看数据库中一个或多个表中数据的方法。视图是一种虚拟表，它的数据并不真正存储，仅保存视图定义一种虚拟表，它的数据并不真正存储，仅保存视图定义优点优点:l对最终用户的好处对最终用户的好处结果更容易理解。 l对开发人员的好处对开发人员的好处简化查询 限制数据检索提高安全性 217217217217创建视图创建视图创建视图的方法有两种：使用创建视图的方法有两种：使用SSMS和和T-SQL语句语句n使用使用SSMS创建视图创建视图n使用使用T-SQL语句创建视图语句创建视图语法语法:CREATEVIEWview_nameAS示例示例:创建视图，显示人民邮电出版社出版的价格大于创建视图，显示人民邮电出版

145、社出版的价格大于20元的书籍信息元的书籍信息218218218218示例代码示例代码CREATEVIEWv_BookInfoASSELECTBookID,BookName,Author,ISBN,PublishDate,Price,Content,CategoryName,PublisherNameFROMBookInfoaINNERJOINCategorybONa.CategoryID=b.CategoryIDINNERJOINPublishercONa.PublisherID=c.PublisherIDWHERE(c.PublisherName=人民邮电出版社人民邮电出版社)AND(Pri

146、ce20)219219219219使用视图使用视图CREATEVIEWv_SalesInfoASSELECTd.UserName用户用户,a.SalesDate销售日期销售日期,c.BookName书名书名,b.Quantity数量数量,b.Price价格价格,b.Discount折扣折扣,b.Price*b.Discount销售价格销售价格FROMSalesMasterainnerjoinSalesDetailsbONa.SalesMasterID=b.SalesMasterIDinnerjoinBookInfocONb.BookID=c.BookIDinnerjoinUserInfodON

147、a.UserID=d.UserID-使用视图查询使用视图查询2012年年1月月1日日李玲的详细销售信息李玲的详细销售信息SELECT*FROMv_SalesInfoWHERE销售日期销售日期=2012-1-1AND用户用户=李玲李玲220220220220修改和删除视图修改和删除视图修改视图修改视图语法语法:删除视图删除视图语法语法:ALTERVIEWview_nameASDROPVIEW视图名视图名DROPVIEWv_SalesInfo示例示例:删除视图删除视图v_SalesInfo221221221221视图的限制视图的限制只能在当前数据库中创建视图。只能在当前数据库中创建视图。视图名称必

148、须遵循标识符的规则，且对每个架构都必须唯视图名称必须遵循标识符的规则，且对每个架构都必须唯一。此外，该名称不得与该架构包含的任何表的名称相同。一。此外，该名称不得与该架构包含的任何表的名称相同。不能将规则或不能将规则或DEFAULT定义与视图相关联。定义与视图相关联。定义视图的查询不能包含定义视图的查询不能包含ORDERBY子句，除非在子句，除非在SELECT语句的选择列表中还有一个语句的选择列表中还有一个TOP子句。子句。不能创建临时视图，也不能对临时表创建视图。不能创建临时视图，也不能对临时表创建视图。222222222222索引索引索引的概念索引的概念l索引用来排序数据以加快搜索和排序操

149、作的速度索引用来排序数据以加快搜索和排序操作的速度l索引：是索引：是SQLServer编排数据的内部方法。它为编排数据的内部方法。它为SQLServer提提供一种方法来编排查询数据的路由供一种方法来编排查询数据的路由l索引包含由表或视图中的一列或多列生成的键，使索引包含由表或视图中的一列或多列生成的键，使SQLServer可以快速有效地查找与键值关联的行可以快速有效地查找与键值关联的行.223223223223索引的分类索引的分类l聚集索引聚集索引:聚集索引根据数据行的键值在表或视图中排序聚集索引根据数据行的键值在表或视图中排序和存储这些数据行和存储这些数据行l非聚集索引非聚集索引:非聚集索引

150、具有独立于数据行的结构非聚集索引具有独立于数据行的结构，它包，它包含含非聚集索引键值，并且每个键值项都有指向包含该键值非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。的数据行的指针。l唯一索引唯一索引：唯一索引确保索引键不包含重复的值唯一索引确保索引键不包含重复的值。l复合索引复合索引：在多个列上同时创建索引在多个列上同时创建索引。l索引视图索引视图：在视图上创建的索引在视图上创建的索引。l全文索引全文索引：一种特殊类型的基于标记的功能性索引一种特殊类型的基于标记的功能性索引，用于用于帮助在字符串数据中搜索复杂的词语帮助在字符串数据中搜索复杂的词语.224224224224创建

151、索引创建索引创建索引有两种方法创建索引有两种方法:l使用使用SSMS(SQLServerManagementStudio)lT-SQL语句语句语法语法:其中：其中：UNIQUE指定唯一索引，可选指定唯一索引，可选CLUSTERED、NOCLUSTERED指定是聚集索引还是非聚集索引指定是聚集索引还是非聚集索引,可选。可选。CREATEUNIQUECLUSTEREDNONCLUSTEREDINDEXindex_nameONtable_name(column_name,column_name。)225225225225创建索引创建索引我们经常使用书的作者进行查询，所以需要针对作者我们经常使用书的作

152、者进行查询，所以需要针对作者(author)创建索引创建索引USEBookShopDbGO/*-检查是否存在该索引（索引存放在系统表检查是否存在该索引（索引存放在系统表sysindexes中中)-*/IFEXISTS(SELECTnameFROMsysindexesWHEREname=IX_BookInfo_Author)DROPINDEXBookInfo.IX_BookInfo_Author-删除索引删除索引-创建索引创建索引CREATENONCLUSTEREDINDEXIX_BookInfo_AuthorONBookInfo(author)-创建索引之后查询数据可以指定创建索引之后查询数据

153、可以指定SQLServer查询数据的方式查询数据的方式SELECT*FROMBookInfoWITH(INDEX=IX_BookInfo_Author)WHEREAuthorLIKE王王%226226226226创建索引的指导原则创建索引的指导原则避免对经常更新的表进行过多的索引，并且索引列要尽避免对经常更新的表进行过多的索引，并且索引列要尽可能少可能少使用多个索引可以提高更新少而数据量大的查询的性能。使用多个索引可以提高更新少而数据量大的查询的性能。大量索引可以提高不修改数据的查询（例如大量索引可以提高不修改数据的查询（例如SELECT语句）的性能，因为查询优化器有更多的索引可供选择，语句）

154、的性能，因为查询优化器有更多的索引可供选择，从而可以确定最快的访问方法从而可以确定最快的访问方法经常作为查询条件的列适合创建索引经常作为查询条件的列适合创建索引经常用于排序经常用于排序,分组的列适合创建索引分组的列适合创建索引经常作为联接条件的列适合创建索引经常作为联接条件的列适合创建索引表中包含很少数据行不适合创建索引表中包含很少数据行不适合创建索引(搜索索引所花的搜索索引所花的时间比搜索数据行花的时间还多时间比搜索数据行花的时间还多)数据行重复项很多的列不适合做索引数据行重复项很多的列不适合做索引227227227227总结总结视图是一种虚拟表，通常是作为执行查询的结果视图是一种虚拟表，通

155、常是作为执行查询的结果而创建的，视图充当对查询中指定表的筛选器。而创建的，视图充当对查询中指定表的筛选器。建立索引有助于快速检索数据，索引分为唯一索建立索引有助于快速检索数据，索引分为唯一索引、组合索引、聚集索引和非聚集索引。引、组合索引、聚集索引和非聚集索引。聚集索引基于数据行的键值在表内排序和存储这聚集索引基于数据行的键值在表内排序和存储这些数据。一个表只能有一个聚集索引，非聚集索些数据。一个表只能有一个聚集索引，非聚集索引指定表的逻辑顺序。因此，一个表可以有多个引指定表的逻辑顺序。因此，一个表可以有多个非聚集索引。非聚集索引。创建索引会提高查询性能，同时会降低增，删，创建索引会提高查询性

156、能，同时会降低增，删，改操作的性能。所以要根据实际情况创建合适的改操作的性能。所以要根据实际情况创建合适的索引。索引。228228228228第第1313章章 存储过程存储过程 229229229229本章目标本章目标了解存储过程的优点了解存储过程的优点掌握常用的系统存储过程掌握常用的系统存储过程掌握如何创建存储过程掌握如何创建存储过程掌握如何调用存储过程掌握如何调用存储过程230230230230存储过程介绍存储过程介绍l存储过程是在数据库管理系统中保存的，预先编译的存储过程是在数据库管理系统中保存的，预先编译的并能实现某种功能的并能实现某种功能的SQL程序。程序。l存储过程相当于编程语言存

157、储过程相当于编程语言(如如JAVA等等)中的方法，就是中的方法，就是由由SQL语句和控制语句组成的能够完成特定某个功能的语句和控制语句组成的能够完成特定某个功能的预编译语句的集合。存储过程是保存在数据库服务器中预编译语句的集合。存储过程是保存在数据库服务器中的，可以直接在的，可以直接在SQLServer客户端中调用也可以通过程客户端中调用也可以通过程序语言调用序语言调用.231231231231存储过程的优点存储过程的优点存储过程的优点存储过程的优点:l允许模块化程序设计允许模块化程序设计只需创建过程一次并将其存储在数据库中，以后即可在程序中调只需创建过程一次并将其存储在数据库中，以后即可在程

158、序中调用该过程任意次用该过程任意次l允许更快执行允许更快执行存储过程将比存储过程将比Transact-SQL批代码的执行要快批代码的执行要快l减少网络流量减少网络流量存储过程存储在后端数据库中不需要通过网络传输存储过程存储在后端数据库中不需要通过网络传输l可作为安全机制使用可作为安全机制使用即使对于没有直接执行存储过程中语句权限的用户，也可授予他执即使对于没有直接执行存储过程中语句权限的用户，也可授予他执行该存储过程的权限行该存储过程的权限232232232232存储过程中的语句存储过程中的语句存储过程中的语句存储过程中的语句存储过程-单个SELECT语句SELECT语句块可以包含SELECT

159、语句与逻辑控制语句SQLServer中的存储过程与其他语言中的过程或函数类似，中的存储过程与其他语言中的过程或函数类似，它们的共同特征是它们的共同特征是:l它们都接收输入参数，并向调用过程或语句返回值。它们都接收输入参数，并向调用过程或语句返回值。l它们都包含在数据库中执行操作或调用其他存储过程的编程语它们都包含在数据库中执行操作或调用其他存储过程的编程语句。句。l它们都向调用过程返回状态值，指示执行过程是否成功它们都向调用过程返回状态值，指示执行过程是否成功233233233233常用的系统存储过程常用的系统存储过程SQLServer提供系统存储过程，它们是一组预编译的提供系统存储过程，它们

160、是一组预编译的T-SQL语句语句所有系统存储过程的名称都以所有系统存储过程的名称都以“_sp”开头。系统存储过程位于开头。系统存储过程位于master数据库中数据库中系统存储过程 说 明sp_databasessp_databases列出服务器上的所有数据库sp_helpdbsp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedbsp_renamedb更改数据库的名称sp_tablessp_tables返回当前环境下可查询的对象的列表sp_columnssp_columns返回某个表列的信息sp_helpsp_help查看某个表的所有信息sp_helpconstraints

161、p_helpconstraint查看某个表的约束sp_helpindexsp_helpindex查看某个表的索引sp_stored_proceduressp_stored_procedures列出当前环境中的所有存储过程sp_helptextsp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本234234234234常用的系统存储过程的使用常用的系统存储过程的使用EXECsp_server_info-返回服务器信息返回服务器信息EXECsp_databases-返回服务器数据库信息返回服务器数据库信息EXECsp_who-返回当前登录用户信息和进程信息

162、返回当前登录用户信息和进程信息EXECsp_tables-返回表信息返回表信息EXECsp_helpdb-返回特定数据库信息返回特定数据库信息示例示例:其他系统存储过程的使用其他系统存储过程的使用一些系统存储过程必须在特定的数据库中使用一些系统存储过程必须在特定的数据库中使用,大多数在所有大多数在所有数据库中可用数据库中可用235235235235创建存储过程创建存储过程创建存储过程创建存储过程lSSMS:可视化的方式可视化的方式lT-SQL:代码代码使用使用CREATEPROCEDURE语句创建存储过程。所有的存储过语句创建存储过程。所有的存储过程都创建在当前数据库中程都创建在当前数据库中语

163、法语法:CREATEPROCEDURE存储过程名存储过程名参数参数1数据类型数据类型=默认值默认值OUTPUT,.,参数参数n数据类型数据类型=默认值默认值OUTPUTASSQL语句语句其中其中, ,参数部分为可选参数部分为可选236236236236创建简单的存储过程创建简单的存储过程-使用存储过程返回所有的书籍信息使用存储过程返回所有的书籍信息USEBookShopGO-判断存储过程判断存储过程select_books是否存在是否存在IFOBJECT_ID(SELECT_books,P)ISNOTNULLDROPPROCEDURESELECT_books;GO-创建存储过程创建存储过程CR

164、EATEPROCEDURESELECT_booksASSELECTId,Title,Author,PublishDate,UnitPrice,ISBNFROMbooksGO237237237237调用存储过程调用存储过程语法语法:调用示例存储过程调用示例存储过程EXEC过程名过程名参数参数EXECUTESELECT_books238238238238创建带参数的存储过程创建带参数的存储过程CREATEPROCEDURE存储过程名存储过程名参数参数1数据类型数据类型=默认值默认值OUTPUT,.,参数参数n数据类型数据类型=默认值默认值OUTPUTASSQL语句语句存储过程中的参数可分为存储过程

165、中的参数可分为2 2种种: :l输入参数输入参数: :可以在调用时向存储过程传递参数可以在调用时向存储过程传递参数,此参数可用来在存此参数可用来在存储过程中传入值储过程中传入值l输出参数输出参数: :如果希望返回值，则可以使用输出参数，输出参数后如果希望返回值，则可以使用输出参数，输出参数后有有“OUTPUT”标记，执行存储过程后，将把返回值存放在输出标记，执行存储过程后，将把返回值存放在输出参数中，可供其他参数中，可供其他T-SQL语句读取访问语句读取访问CREATEPROCEDURE语句中声明一个或多个变量作为参数语句中声明一个或多个变量作为参数参数参数数据类型数据类型=默认值默认值创建存

166、储过程的语法创建存储过程的语法239239239239创建带输入参数的存储过程示例创建带输入参数的存储过程示例-1示例：示例：查询指定的出版社出版的书籍查询指定的出版社出版的书籍USEBookShopGOIFOBJECT_ID(SELECT_WITH_pub,P)ISNOTNULLDROPPROCEDURESELECT_WITH_pub;GOCREATEPROCEDURESELECT_WITH_pubpubnameVARCHAR(20)ASSELECTtitle,author,publishdateisbn,nameASpublishernameFROMbooksainnerjoinpubli

167、shersbONa.publisherid=b.idANDb.name=pubnameGO-执行存储过程执行存储过程EXECSELECT_WITH_pub人民邮电出版社人民邮电出版社GO240240240240创建带输入参数的存储过程示例创建带输入参数的存储过程示例-2问题：按出版社和类别模糊查询问题：按出版社和类别模糊查询USEBookShopGOIFOBJECT_ID(SELECT_book_parm,P)ISNOTNULLDROPPROCEDURESELECT_book_parm;GOCREATEPROCEDURESELECT_book_parmcateVARCHAR(20),pubna

168、meVARCHAR(20)ASSELECTBookName,author,isbn,publishdate,Price,b.CategoryNameAScategoryname,c.PublisherNameASpublishernameFROMbookinfoa,categoryb,publishercWHEREa.categoryid=b.CategoryIDANDa.publisherid=c.PublisherIDANDb.CategoryNameLIKE%+cate+%ANDc.PublisherNameLIKE%+pubname+%GO-执行存储过程执行存储过程EXECSELECT

169、_book_parmC,北京北京GO241241241241创建带默认参数的存储过程创建带默认参数的存储过程示例：示例：按照指定价格和出版日期查找图书，价格参数提供默按照指定价格和出版日期查找图书，价格参数提供默认值为认值为100，出版日期参数默认值为系统日期，出版日期参数默认值为系统日期CREATEPROCEDURESELECT_pubdatepriceDECIMAL=100,pubdateDATETIME=NULLASIF(pubdateISNULL)SETpubdate=GETDATE()SELECTId,Title,Author,PublishDate,UnitPrice,ISBNFR

170、OMbooksWHEREunitpricepriceANDpublishdate=pubdateGO-测试使用默认参数，即测试使用默认参数，即:price=100,pubdate=getdateEXECSELECT_pubdateGO-测试用户给定的参数值，不使用默认参数测试用户给定的参数值，不使用默认参数EXECSELECT_pubdate10,2009-1-1GO242242242242创建带输出参数的存储过程示例创建带输出参数的存储过程示例-1CREATEPROCSUM_salesbooknameVARCHAR(20)OUTPUT,moneyDECIMALOUTPUTAS-统计书籍销售信

171、息统计书籍销售信息SELECTa.BookName书名书名,isnull(SUM(b.quantity),0)销售数量销售数量,ISNULL(SUM(b.quantity*b.price*b.discount),0)销售总价销售总价FROMBookInfoaleftjoinSalesDetailsbONa.BookID=b.BookIDGROUPBYa.BookNameORDERBYSUM(b.Quantity)DESC-返回销售金额最低的书籍返回销售金额最低的书籍SELECTTOP1bookname=a.bookname,money=SUM(b.Quantity*b.Price*b.Disc

172、ount)FROMBookInfoainnerjoinSalesDetailsbONa.BookID=b.BookIDGROUPBYa.BookNameORDERBYSUM(b.Quantity*b.Price*b.Discount)问题：问题：统计每本书的销售情况统计每本书的销售情况,显示书的名称显示书的名称,销售数量销售数量,销售总金额销售总金额,并返回已销售的书籍中销售金额最低的书的书名和金额并返回已销售的书籍中销售金额最低的书的书名和金额243243243243执行带输出参数的存储过程执行带输出参数的存储过程DECLAREnameVARCHAR(20)DECLAREmoneyDECIM

173、ALEXECUTESUM_salesnameOUTPUT,moneyOUTPUTPRINT销售金额最少的书销售金额最少的书:+name+,金额为：金额为：+CAST(moneyASVARCHAR(4)244244244244处理存储过程中的错误处理存储过程中的错误RAISERROR(消息消息id|消息文本消息文本|局部变量局部变量,错误严重级别错误严重级别,状态状态消息消息id:使用使用sp_addmessage存储在存储在sys.messages目目录视图中的用户定义错误消息号。用户定义错误消息的错录视图中的用户定义错误消息号。用户定义错误消息的错误号应当大于误号应当大于50000。如果未指

174、定，则。如果未指定，则RAISERROR引引发一个错误号为发一个错误号为50000的错误消息的错误消息消息文本：用户定义的消息消息文本：用户定义的消息错误严重级别：用户定义的与该消息关联的严重级别，任错误严重级别：用户定义的与该消息关联的严重级别，任何用户都可以指定何用户都可以指定0到到18之间的严重级别、之间的严重级别、状态：状态：0-255的整数的整数245245245245RAISERROR示例示例编写一个存储过程，查询某出版社在指定的日期之后出版的书籍信息，并能编写一个存储过程，查询某出版社在指定的日期之后出版的书籍信息，并能返回该出版社销售的最好的书籍名称和销售数量。如果用户输入的日

175、期超过返回该出版社销售的最好的书籍名称和销售数量。如果用户输入的日期超过系统日期则提示错误，不能执行查询。系统日期则提示错误，不能执行查询。CREATEPROCSELECT_errpublisherVARCHAR(50),pubdateDATETIME,countINTOUTPUT,bookNameVARCHAR(50)OUTPUTASDECLAREpubIdINT-保存出版社保存出版社IdIF(pubdatepubdate246246246246-返回销售最号的书籍名称和销售数量返回销售最号的书籍名称和销售数量SELECTTOP1BookName=a.BookName,count=SUM(b

176、.Quantity)FROMBookInfoa,SalesDetailsbWHEREa.BookID=b.BookIDANDa.PublisherID=pubIdANDa.PublishDatepubdateGROUPBYa.BookNameORDERBYSUM(b.Quantity)DESCENDELSEBEGINRAISERROR(出版日期不能大于系统日期出版日期不能大于系统日期,16,1)return-返回返回ENDRAISERROR示例示例247247247247执行存储过程执行存储过程DECLAREpubnameVARCHAR(50)DECLAREdateDATETIMEDECLAR

177、EnameVARCHAR(50)DECLAREnumINTSETpubname=机械工业出版社机械工业出版社SETdate=2000-1-1EXECSELECT_errpubname,date,bookName=nameOUTPUT,count=numOUTPUTDECLAREerrINTSETerr=ERRORIF(err0)BEGINPRINT系统出错系统出错,请重新输入日期请重新输入日期ENDELSEBEGINPRINTpubname+在在+convert(VARCHAR(10),date,133)+之后出版的书籍信息之后出版的书籍信息:+name+销售的最好，销售了销售的最好，销售了+

178、CAST(numASVARCHAR(10)+本本END248248248248总结存储过程是存储在数据库服务器端并且已经预编译好的存储过程是存储在数据库服务器端并且已经预编译好的SQL语句语句。存储过程存储过程有利于实现模块化编程，执行效率高，安全性好有利于实现模块化编程，执行效率高，安全性好存存储过程根据需要可以定义输入参数，输出参数储过程根据需要可以定义输入参数，输出参数。为了提高为了提高存储过程的存储过程的健壮性可以使用健壮性可以使用RAISERROR语句。语句。249249249249第第1414章章 数据库触发器数据库触发器 250250250250本章目标本章目标了解触发器的用途了

179、解触发器的用途理解触发器的工作原理理解触发器的工作原理掌握如何使用掌握如何使用inserted表和表和deleted表表掌握如何创建掌握如何创建INSERT、UPDATE、DELETE触发器触发器251251251251触发器介绍触发器介绍触发器是在数据库中发生事件时自动执行的特殊存储过程，触发器是在数据库中发生事件时自动执行的特殊存储过程，这些事件主要是发生在表上的这些事件主要是发生在表上的DML（INSERT,UPDATE,DELETE）操作）操作触发器与数据操作有关触发器与数据操作有关在数据库服务器端实现业务规则和强制业务规则在数据库服务器端实现业务规则和强制业务规则l存储过程实现业务规

180、则存储过程实现业务规则l约束强制业务规则约束强制业务规则强制业务规则强制业务规则l约束约束l触发器触发器触发器可以实现比约束更复杂的数据完整性触发器可以实现比约束更复杂的数据完整性252252252252触发器的特点触发器的特点触发器是在对表进行插入、更新或删除操作时自动执行的触发器是在对表进行插入、更新或删除操作时自动执行的存储过程存储过程触发器通过事件进行触发而被执行的触发器通过事件进行触发而被执行的特点特点:l与表相关联与表相关联l自动触发自动触发l不能直接调用不能直接调用l是事务的一部分是事务的一部分253253253253触发器的类型触发器的类型DML触发器触发器DML触发器是当数据

181、库服务器中发生数据操作语言触发器是当数据库服务器中发生数据操作语言(DML)事件时要执行的操作。事件时要执行的操作。DDL触发器触发器DDL触发器是一种特殊的触发器，它在响应数据定义语言触发器是一种特殊的触发器，它在响应数据定义语言(DDL)语句时触发。语句时触发。登录触发器登录触发器登录触发器将为响应登录触发器将为响应LOGON事件而激发存储过程。事件而激发存储过程。254254254254DML触发器的种类触发器的种类AFTER触发器触发器lAFTER触发器要求只有执行某一操作触发器要求只有执行某一操作INSERT、UPDATE、DELETE之后触发器才被触发且只能在表上定义之后触发器才被

182、触发且只能在表上定义INSTEADOF触发器触发器l可在表上定义可在表上定义INSTEADOF触发器，也可以在视图上定义触发器，也可以在视图上定义INSTEADOF触发器触发器CLR触发器触发器lCLR触发器可以是触发器可以是AFTER触发器或触发器或INSTEADOF触发器，它触发器，它将执行在托管代码（在将执行在托管代码（在.NETFramework中创建并在中创建并在SQLServer中上载的程序集的成员）中编写的方法，而不用执行中上载的程序集的成员）中编写的方法，而不用执行Transact-SQL存储过程。存储过程。255255255255AFTER触发器触发器AFTER触发器包括触发

183、器包括:lINSERT触发器：当向表中插入数据时触发，自动执行触发器所定触发器：当向表中插入数据时触发，自动执行触发器所定义的义的SQL语句。语句。lUPDATE触发器：当更新表中某列、多列时触发，自动执行触发触发器：当更新表中某列、多列时触发，自动执行触发器所定义的器所定义的SQL语句。语句。lDELETE触发器：当删除表中记录时触发，自动执行触发器所定触发器：当删除表中记录时触发，自动执行触发器所定义的义的SQL语句。语句。触发器的两个特殊表触发器的两个特殊表l插入表（插入表（inserted表）表）l删除表（删除表（deleted表表):这两个表是逻辑表，并且是由系统管理的，存储在内存中

184、，不是存储这两个表是逻辑表，并且是由系统管理的，存储在内存中，不是存储在数据库中，因此，不允许用户直接对其修改。在数据库中，因此，不允许用户直接对其修改。256256256256inserted和和deleted表表触发器触发时触发器触发时：l系统自动在内存中创建系统自动在内存中创建deleted表或表或inserted表表l只读，不允许修改只读，不允许修改；触发器执行完成后，自动删除触发器执行完成后，自动删除inserted表表l临时保存了插入或更新后的记录行临时保存了插入或更新后的记录行l可以从可以从inserted表中检查插入的数据是否满足业务需求表中检查插入的数据是否满足业务需求l如果

185、不满足，则向用户报告错误消息，并回滚插入操作如果不满足，则向用户报告错误消息，并回滚插入操作deleted表表l临时保存了删除或更新前的记录行临时保存了删除或更新前的记录行l可以从可以从deleted表中检查被删除的数据是否满足业务需求表中检查被删除的数据是否满足业务需求l如果不满足，则向用户报告错误消息，并回滚插入操作如果不满足，则向用户报告错误消息，并回滚插入操作257257257257inserted和和deleted表表修改操作inserted表deleted表增加增加(INSERT)记录记录存放新增的存放新增的记录记录-删删除除(DELETE)记录记录-存放被存放被删删除的除的记录记

186、录修改修改(UPDATE)记录记录存放更新后的存放更新后的记录记录存放更新前的存放更新前的记录记录inserted表和deleted表存放的信息258258258258创建触发器创建触发器语法语法:CREATETRIGGERtrigger_nameONtable_nameWITHENCRYPTIONFORDELETE,INSERT,UPDATEAST-SQL语句GOWITHENCRYPTION表示加密触发器定义的SQL文本DELETE,INSERT,UPDATE指定触发器的类型259259259259插入触发器插入触发器问题问题:在类别表中插入数据时不能插入名称相似度比较高的类别，如：类在类别

187、表中插入数据时不能插入名称相似度比较高的类别，如：类别别“数据库数据库”和和“数据库基础数据库基础”我们认为是相似的名称，即前我们认为是相似的名称，即前4个字个字符相同的类别判定为相同类别。符相同的类别判定为相同类别。CREATETRIGGERInsert_CategoryONCategoryFORINSERTASDECLAREnameVARCHAR(20)DECLAREcountINTSELECTname=categoryNameFROMinsertedSELECTcount=count(*)FROMCategoryWHEREsubstring(CategoryName,1,4)LIKEna

188、me+%IF(count0)BEGINPRINT不能插入相同的类别名称不能插入相同的类别名称ROLLBACKTRANSACTIONEND测试代码：测试代码：INSERTINTOcategoryVALUES(计算机基础计算机基础)260260260260修改触发器修改触发器问题问题:允许用户修改书的出版日期，新的出版日期最多比旧的出版日期晚允许用户修改书的出版日期，新的出版日期最多比旧的出版日期晚一个月，并且要小于等于系统日期，否则修改失败。一个月，并且要小于等于系统日期，否则修改失败。CREATETRIGGERUPDATE_bookinfoONBookInfoFORUPDATEASDECLAR

189、EoldDATETIMEDECLAREnewDATETIME-获取旧的出版日期获取旧的出版日期SELECTold=PublishDateFROMdeleted-获取新的出版日期获取新的出版日期SELECTnew=PublishDateFROMinserted-日期差值最大为天日期差值最大为天IF(datediff(dy,old,new)=31ORnewGETDATE()BEGINPRINT新的出版日期不能比旧出版日期大一个月新的出版日期不能比旧出版日期大一个月ROLLBACKTRANSACTIONEND-将书号为将书号为B0001的书籍出版日期推后天的书籍出版日期推后天UPDATEBookIn

190、foSETPublishDate=PublishDate+40WHEREBookId=B0001261261261261列级触发器列级触发器UPDATE触发器除了跟踪数据的变化（修改）外，还可以检查是否修改了某列的数据使用UPDATE（列）函数检测是否修改了某列图书的图书的ISBN不能修改不能修改CREATETRIGGERUPDATE_BookColumnONBookInfoAFTERUPDATEASIF(UPDATE(ISBN)BEGINPRINTISBN一旦确定不能修改一旦确定不能修改RAISERROR(系统提示：系统提示：ISBN不能修改！不能修改！,16,1);END测试代码如下：测试

191、代码如下：UPDATEBookInfoSETISBN=9787505393929WHEREBookId=B0001262262262262删除触发器删除触发器要求要求2000年之前出版的书籍不能删除年之前出版的书籍不能删除CREATETRIGGERDELETE_bookONBookInfoAFTERDELETEASDECLAREpublishDateDATETIMESELECTpublishDate=publishDateFROMDeletedIF(publishDate0)System.out.println(“插入成功插入成功!”);elseSystem.out.println(“插入失败

192、插入失败!”);279279279279总结总结Java中通过中通过JDBC访问数据库访问数据库JDBC提供了统一的数据库访问方法提供了统一的数据库访问方法280280280280第第1616章章 数据库维护数据库维护281281281281本章目标本章目标l掌握数据库维护的意义掌握数据库维护的意义l掌握掌握SQLServer的数据库备份的数据库备份l掌握数据库还原掌握数据库还原282282282282数据库备份数据库备份在任何一个使用计算机系统的场合，确保数据库中数据在任何一个使用计算机系统的场合，确保数据库中数据的完整性和安全性是一个非常重要的环节。无论是计算的完整性和安全性是一个非常重要

193、的环节。无论是计算机硬件系统的故障还是软件系统的瘫痪都有可能对我们机硬件系统的故障还是软件系统的瘫痪都有可能对我们的正常工作带来巨大冲击甚至出现灾难性的后果。如何的正常工作带来巨大冲击甚至出现灾难性的后果。如何有效地解决这个问题，那就是通过备份和还原做好数据有效地解决这个问题，那就是通过备份和还原做好数据库的维护。库的维护。所谓备份就是在某种介质所谓备份就是在某种介质(磁盘或磁带磁盘或磁带)上存储数据库结上存储数据库结构和数据的拷贝，以便在数据库遭到破坏的时候能够还构和数据的拷贝，以便在数据库遭到破坏的时候能够还原数据库。原数据库。283283283283备份前的计划备份前的计划为了将系统安全

194、完整的备份，应该在具体备份执行之前，根为了将系统安全完整的备份，应该在具体备份执行之前，根据具体的环境和条件制定一个完善可行的备份计划，以取据具体的环境和条件制定一个完善可行的备份计划，以取保数据库系统的安全保数据库系统的安全。确定备份的频率。确定备份的频率。确定备份的内容。确定备份的内容。备份使用的介质。备份使用的介质。确定备份工作的负责人确定备份工作的负责人确定使用在线备份还是脱机备份。确定使用在线备份还是脱机备份。确定是否使用备份服务器。确定是否使用备份服务器。确定备份存储的地方。确定备份存储的地方。确定备份存储的期限确定备份存储的期限284284284284备份设备备份设备为了执行备份

195、操作，在使用之前所创建的备份文件称为永久为了执行备份操作，在使用之前所创建的备份文件称为永久性的备份文件。这些永久性的备份文件也称为备份设备性的备份文件。这些永久性的备份文件也称为备份设备创建永久备份文件有两种方式创建永久备份文件有两种方式：一。一。使用系统存储过程使用系统存储过程sp_addumpdevice二二。使用使用SQLServerManagementStudio，如下图，如下图285285285285备份类型备份类型完全数据库备份完全数据库备份增量数据库备份增量数据库备份事务日志备份事务日志备份数据库文件和文件组备份数据库文件和文件组备份286286286286事务日志的恢复类型事

196、务日志的恢复类型完整的恢复模式完整的恢复模式大容量日志记录的恢复模式大容量日志记录的恢复模式简单的恢复模式简单的恢复模式287287287287执行数据库备份执行数据库备份示例：对数据库执行完整备份，差异备份，日志备份示例：对数据库执行完整备份，差异备份，日志备份一。创建数据库，创建表，增加数据一。创建数据库，创建表，增加数据二。执行完整备份二。执行完整备份三。增加一张表三。增加一张表四。执行增量备份四。执行增量备份五。向新增的表中插入数据五。向新增的表中插入数据六。执行日志备份六。执行日志备份288288288288数据库还原数据库还原所谓数据库还原就是将数据库恢复到之前的某个状态。所谓数据

197、库还原就是将数据库恢复到之前的某个状态。备份可以防止数据库遭受破坏，介质失效或用户错误。备备份可以防止数据库遭受破坏，介质失效或用户错误。备份是还原数据库最容易和最能防止意外的有效方法。没有份是还原数据库最容易和最能防止意外的有效方法。没有备份，所有的数据都可能丢失，而且将造成不可挽回的损备份，所有的数据都可能丢失，而且将造成不可挽回的损失，这是就不得不从源头重建数据。有了备份，万一数据失，这是就不得不从源头重建数据。有了备份，万一数据库被损坏，就可以使用备份来还原数据库。还原数据库是库被损坏，就可以使用备份来还原数据库。还原数据库是装载数据库的备份，然后应用事务日志重建的过程。应用装载数据库

198、的备份，然后应用事务日志重建的过程。应用事务日志之后，数据库就会回到最后一次事务日志备份之事务日志之后，数据库就会回到最后一次事务日志备份之前的状况。当数据库被还原后，数据库中所有的数据都被前的状况。当数据库被还原后，数据库中所有的数据都被替换掉。替换掉。289289289289数据库还原的方式数据库还原的方式完全还原方式完全还原方式简单还原方式简单还原方式大容量日志记录还原方式大容量日志记录还原方式290290290290执行数据库还原执行数据库还原一。执行完整的数据库还原，选择完整备份的备份文件将一。执行完整的数据库还原，选择完整备份的备份文件将还原到数据库最初创建的还原到数据库最初创建的

199、状态。状态。二。执行差异还原，选择差异备份的备份文件并选择恢复二。执行差异还原，选择差异备份的备份文件并选择恢复状态为状态为“回滚未提交的事务，并将数据库处于可用的状态回滚未提交的事务，并将数据库处于可用的状态”将数据库还原到差异备份时的状态。将数据库还原到差异备份时的状态。三。执行完全的数据库还原，恢复到最初的状态。三。执行完全的数据库还原，恢复到最初的状态。四。执行事务日志还原，选择事务日志备份的备份文件并四。执行事务日志还原，选择事务日志备份的备份文件并选择恢复状态为选择恢复状态为“回滚未提交的事务，并将数据库处于可回滚未提交的事务，并将数据库处于可用的状态用的状态”将数据库还原到事务日志备份时的状态。将数据库还原到事务日志备份时的状态。291291291291总结总结l备份是为了避免数据库在意外收到破坏时能将损失降备份是为了避免数据库在意外收到破坏时能将损失降到最低到最低lSQLServer既可以执行数据库备份又可以执行事务日既可以执行数据库备份又可以执行事务日志备份志备份l还原可以使数据库恢复到故障点或者任意的时刻还原可以使数据库恢复到故障点或者任意的时刻