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

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数据存在的必要性可以存储大量的数据，便于用户进行检索和管可以存储大量的数据，便于用户进行检索和管理理 可以保持数据的一致性、完整性，降低数据冗可以保持数据的一致性、完整性，降低数据冗余余 实现应用程序的数据共享和安全实现应用程序的数据共享和安全 利用数据库可以智能的对数据进行分析和统计利用数据库可以智能的对数据进行分析和统计 n n数据库的发展史 第一代数据库第一代数据库层次模型和网状模型层次模型和网状模型 n n各种各种DBMS DBMS 软件都是基于某种数据模型的软件都是基于某种数据模型的 n nIBM IBM 公司在公司在1969 1969 年推出了世界上第一个层次数据年推出了世界上第一个层次数据库系统库系统IMS IMS 第二代数据库第二代数据库关系型数据库关系型数据库 n n关系数据库是建立在关系模型之上的数据库关系数据库是建立在关系模型之上的数据库 第三代数据库第三代数据库面向对象数据库面向对象数据库 n n处于发展阶段，未来趋势处于发展阶段，未来趋势4 44 44 4数据库的基本概念n n数据库的基本概念数据和信息数据和信息n n数据中所包含的意义就是信息数据中所包含的意义就是信息 数据库数据库n n保存有组织的数据的容器保存有组织的数据的容器关系数据库关系数据库n n关系数据库是基于关系模型建立的，由一系列二维表关系数据库是基于关系模型建立的，由一系列二维表格组成，将数据分类存储在多个二维表中，用关系格组成，将数据分类存储在多个二维表中，用关系（外键）来体现各个表之间的关系。（外键）来体现各个表之间的关系。订单订单订单订单 客户客户客户客户 产品产品产品产品 产品数据库表数数数数 据据据据 表表表表存储过程存储过程存储过程存储过程视视视视 图图图图.5 55 55 5数据库的基本概念n n数据库的基本概念表、实体和记录表、实体和记录 n n实体是客观存在的事物，比如：桌子，椅子，电脑实体是客观存在的事物，比如：桌子，椅子，电脑实体是客观存在的事物，比如：桌子，椅子，电脑实体是客观存在的事物，比如：桌子，椅子，电脑行(记录)6 66 66 6数据库的基本概念n n数据库的基本概念数据冗余和数据完整性数据冗余和数据完整性 n n数据冗余即相同的数据存在了多个地方数据冗余即相同的数据存在了多个地方 n n数据完整性是指数据库中数据的有效性和相容性数据完整性是指数据库中数据的有效性和相容性编号编号姓名姓名年龄年龄民族民族部门部门1 1王涛王涛3333汉族汉族人事管理部人事管理部2 2李梅李梅2727汉族汉族人事管理部人事管理部市市场营销场营销部部回族回族2727李梅李梅2 2财务财务管理部管理部汉汉族族3333王涛王涛1 1部部门门民族民族年年龄龄姓名姓名编编号号7 77 77 7SQLServer2008简介n nSQL Server2008SQL Server2008简介简介 SQLSQL ServerServer是微软公司开发的一款关系型数据库产品，是微软公司开发的一款关系型数据库产品，具有成本低，易上手，工具全等优点。具有成本低，易上手，工具全等优点。适用于大型或超大型数据库服务器端。微软的软件的适用于大型或超大型数据库服务器端。微软的软件的特点是版本分得细，可适合各种使用者不同的需要特点是版本分得细，可适合各种使用者不同的需要 n nSQL Server2008SQL Server2008的体系结构的体系结构 SQL Server2008SQL Server2008系统由系统由4 4个部分组成：个部分组成：n n数据库引擎数据库引擎n nAnalysis ServicesAnalysis Servicesn nReporting ServicesReporting Servicesn nIntegration ServicesIntegration Servicesn n数据库引擎数据库引擎 数据库引擎是数据库引擎是Microsoft SQL Server2008Microsoft SQL Server2008系统的核系统的核心服务，是存储和处理关系（表格）类型的数据或心服务，是存储和处理关系（表格）类型的数据或XMLXML文档数据的服务，负责完成数据的存储、处理和文档数据的服务，负责完成数据的存储、处理和安全管理。安全管理。例如：创建数据库、创建表、创建视图、查询数据和例如：创建数据库、创建表、创建视图、查询数据和访问数据库等操作，都是由数据库引擎完成的访问数据库等操作，都是由数据库引擎完成的 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 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 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 Configuration 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模式即客户机模式即客户机/服务器服务器（Client/ServerClient/Server）模式的简称，在该种模）模式的简称，在该种模式下，服务器通常是在硬件配置和性能比式下，服务器通常是在硬件配置和性能比较高、安装了服务器软件的计算机，客户较高、安装了服务器软件的计算机，客户机就是安装了客户端软件的一般的计算机机就是安装了客户端软件的一般的计算机 111111111111连接与断开数据库服务器连接与断开数据库服务器n n连接与断开数据库服务器连接与断开数据库服务器身份验证：连接之前，需要选择身份验证模式，身份验证：连接之前，需要选择身份验证模式，身份验证：连接之前，需要选择身份验证模式，身份验证：连接之前，需要选择身份验证模式，n n系统提供了两种身份验证模式：系统提供了两种身份验证模式：系统提供了两种身份验证模式：系统提供了两种身份验证模式：WindowsWindows身份验证：用当前登录操作系统的身份去登录身份验证：用当前登录操作系统的身份去登录身份验证：用当前登录操作系统的身份去登录身份验证：用当前登录操作系统的身份去登录.SQL ServerSQL Server身份验证：用身份验证：用身份验证：用身份验证：用WindowsWindows加加加加SQL ServerSQL Server的混合模式进行验证，需要用合法的的混合模式进行验证，需要用合法的的混合模式进行验证，需要用合法的的混合模式进行验证，需要用合法的SQL ServerSQL Server登录用登录用登录用登录用户去访问户去访问户去访问户去访问n n默认系统在安装以后会自动产生一个默认系统在安装以后会自动产生一个默认系统在安装以后会自动产生一个默认系统在安装以后会自动产生一个sasa的登录用户，的登录用户，的登录用户，的登录用户，具有最高权限。具有最高权限。具有最高权限。具有最高权限。断开数据库服务器断开数据库服务器断开数据库服务器断开数据库服务器 121212121212启动和停止SQLServer服务n n启动、停止、暂停和重新启动SQL Server服务 使用使用SQL ServerSQL 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 131313131313SQLServerManagementStudio(SSMS)介绍n nSSMS简介 SQL ServerSQL Server是作为单独的是作为单独的WindowsWindows进程在基进程在基于于WindowsWindows的计算机上运行的，它可以运行在的计算机上运行的，它可以运行在独立的桌面计算机上，也可以运行在服务器或独立的桌面计算机上，也可以运行在服务器或者网络计算机上者网络计算机上 .SQL ServerSQL Server作为服务运行，该服务由作为服务运行，该服务由WindowsWindows自身进行监控自身进行监控 SSMSSSMS中的一个重要的工具就是是查询编辑器中的一个重要的工具就是是查询编辑器（query editor)query editor)n nSQL Server Management Studio(SSMS)的使用 141414141414创建数据库登录账户n n创建数据库登录账户在实际应用中，经常要根据不同的用户设置不在实际应用中，经常要根据不同的用户设置不同的访问权限来限制对数据库的操作同的访问权限来限制对数据库的操作 151515151515总结n n数据库存在的必要性和数据库的发展使用数据库可以安全，高效的实现数据的存储使用数据库可以安全，高效的实现数据的存储和管理和管理数据和信息的概念数据和信息的概念n nSQL Server2008数据库只适合Windows平台n nSQL Server2008的安装方法n n服务器上的后台服务n nSQL Server2008服务端组件和客户端工具n n创建登录的方法161616161616n n使用数据库可以安全，高效的实现数据的存储和管理n n数据和信息n n数据库文件包括：主数据文件，次要数据文件和日志文件，每个主数据文件，次要数据文件和日志文件，每个数据库至少要包含一个数据文件和日志文件数据库至少要包含一个数据文件和日志文件n n文件组是逻辑上对数据文件进行管理的一种方式n n通过分离数据库可以将数据库分离成物理的文件n n附加数据库可以将物理文件恢复成数据库第2章 数据库管理171718181818本章目标n n学会创建和维护数据库的方法n n掌握数据库的组成及数据文件的含义n n掌握数据库的分离和附加方法n n学会使用帮助文档181819191919数据库组成数据库组成1919n n数据文件（主要数据文件（主要数据文件（主要数据文件（主要/次要）和日志文件次要）和日志文件次要）和日志文件次要）和日志文件 数据文件包含数据和对象：如表、索引、视图等数据文件包含数据和对象：如表、索引、视图等数据文件包含数据和对象：如表、索引、视图等数据文件包含数据和对象：如表、索引、视图等 日志文件包含恢复数据库中的所有事务所需的信息日志文件包含恢复数据库中的所有事务所需的信息日志文件包含恢复数据库中的所有事务所需的信息日志文件包含恢复数据库中的所有事务所需的信息数据库数据库主数据文件(.mdf)次要数据文件(.ndf)日志文件(.ldf)20202020数据库文件和文件组简介n n主数据文件主数据文件 包含数据库的启动信息，用户数据和对象存储在此文包含数据库的启动信息，用户数据和对象存储在此文件中，每个数据库只能有一个主数据文件，建议扩展件中，每个数据库只能有一个主数据文件，建议扩展名是名是 .mdf.mdfn n次要数据文件次要数据文件 次要数据文件是可选的，由用户定义并存储用户数据。次要数据文件是可选的，由用户定义并存储用户数据。建议扩展名建议扩展名.ndf.ndfn n事务日志文件事务日志文件 保存用于恢复数据库的日志信息。每个数据库必须至保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件，建议扩展名少有一个日志文件，建议扩展名.ldf.ldf202021212121数据库数据库n n系统数据库系统数据库系统数据库系统数据库2121mastermastertempdbtempdbmsdbmsdbStudentsDBStudentsDBBookShopDBBookShopDB用户数据库用户数据库modelmodel4个系统数据库个系统数据库用户自己创建22222222系统数据库n n4个系统数据库MasterMastern n它存储它存储 SQL Server SQL Server 系统的所有系统级信息系统的所有系统级信息 ,它包它包括：括：登录帐户、链接服务器和系统配置设置。登录帐户、链接服务器和系统配置设置。所有其他数据库的存在、数据库文件的位置所有其他数据库的存在、数据库文件的位置 SQL ServerSQL Server的初始化信息的初始化信息 modelmodeln n在在 SQL Server SQL Server 实例上创建的所有数据库的模板实例上创建的所有数据库的模板 Msdb Msdb n n由由 SQL Server SQL Server 代理用来计划警报和作业代理用来计划警报和作业 tempdb tempdb n n是连接到是连接到 SQL Server SQL Server 实例的所有用户都可用的全实例的所有用户都可用的全局资源，它保存所有临时表和临时存储过程局资源，它保存所有临时表和临时存储过程 222223232323文件组文件组n nBookShopDBBookShopDB的物理文件结构的物理文件结构的物理文件结构的物理文件结构2323BookShopDBBookShopDB.ldf.ldfE:日志文件日志文件日志文件日志文件 BookInfoBookInfo PublisherPublisher UserInfoUserInfo 主文件组主文件组主文件组主文件组BookShopDB.mdfBookShopDB.mdfC:用户定义文件组用户定义文件组用户定义文件组用户定义文件组Book1.ndfBook1.ndfBook2.ndfBook2.ndfD:syssys syssys sysuserssysusers sysobjectssysobjects 242424242424数据库的建立数据库的建立-可视化方式可视化方式25252525数据库的建立-使用代码创建n n代码创建数据库代码创建数据库252526262626数据库设置n n数据库的设置数据库右键数据库右键-属性属性 -选项选项-只读只读 -收缩收缩262627272727数据库的修改修改数据库：可以在修改数据库：可以在【文件文件】选项页通过修改选项页通过修改数据文件修改数据库数据文件修改数据库 272728282828数据库删除n n可视化方式 n n代码方式Drop database testDrop database test282829292929分离数据库分离数据库n n分离数据库 分离数据库是指将数据分离数据库是指将数据库从库从 SQL Server SQL Server 实例中删除实例中删除292930303030附加数据库n n附加数据库附加是分离的反操作。附加是分离的反操作。就可以使用分离后的数据就可以使用分离后的数据文件和日志文件将数据库文件和日志文件将数据库附加到任何附加到任何 SQL Server SQL Server 实例，实例，包括分离该数据库的服务器包括分离该数据库的服务器 303031313131总结n n数据库文件包括：主数据文件，次要数据文件和日志文件，每个主数据文件，次要数据文件和日志文件，每个数据库至少要包含一个数据文件和日志文件数据库至少要包含一个数据文件和日志文件n n文件组是逻辑上对数据文件进行管理的一种方式n n创建数据库可视化方式创建可视化方式创建代码方式创建代码方式创建n n维护数据库数据库的修改和删除数据库的修改和删除n n通过分离数据库可以将数据库分离成物理的文件n n附加数据库可以将物理文件恢复成数据库n n使用帮助文档3131第3章-1 SQLServer表管理323233333333本章目标n n掌握数据表、数据完整性、主键和外键的基本概念掌握数据表、数据完整性、主键和外键的基本概念n n掌握掌握SQLServerSQLServer数据类型数据类型n n创建表的时候要设定字段名，选取合适的数据类型和约束创建表的时候要设定字段名，选取合适的数据类型和约束n n如果要保证某个列唯一并且自动增长，设定如果要保证某个列唯一并且自动增长，设定IDENTITYIDENTITY约束约束n n主键是保证实体完整性的约束，建议每个表有一个主键主键是保证实体完整性的约束，建议每个表有一个主键n n外键保证表之间的引用完整性，从表中的数据依赖于主表外键保证表之间的引用完整性，从表中的数据依赖于主表n n在建表时设置在建表时设置 主键约束主键约束 外键约束外键约束 检查约束检查约束 默认约束默认约束 空值约束空值约束 标识列标识列 333334343434数据表基本概念n n表书数据库中最重要的数据对象，数据存储在表表书数据库中最重要的数据对象，数据存储在表里里n n数据表由行和列组成数据表由行和列组成343435353535数据完整性n n数据完整性实体完整性实体完整性n n将行定义为特定表的唯一实体。即表中的每一行数将行定义为特定表的唯一实体。即表中的每一行数据都代表一个不同的实体，不能存在相同的数据行据都代表一个不同的实体，不能存在相同的数据行 域完整性域完整性n n域完整性指特定列项的有效性域完整性指特定列项的有效性 引用完整性引用完整性n n用来限制两个有关联关系的表之间的数据用来限制两个有关联关系的表之间的数据 353536363636主键和外键n n主键表通常具有包含唯一标识表中每一行值的一个表通常具有包含唯一标识表中每一行值的一个或一多个字段或一多个字段 特点特点n n一个表只能有一个主键一个表只能有一个主键n n主键中的列不能为空主键中的列不能为空n n如果一个表的主键由多个列组成，该主键也叫如果一个表的主键由多个列组成，该主键也叫“组组合主键合主键”n n外键外键约束保证了数据库中两个表中数据的引用外键约束保证了数据库中两个表中数据的引用完整性完整性 将一个表的一列（或列组合）定义为引用其他将一个表的一列（或列组合）定义为引用其他表的主键，则引用表中的这个列（或列组合）表的主键，则引用表中的这个列（或列组合）就称为外键就称为外键 363637373737主键和外键3737主键主键外键外键38383838数据表n n建立数据表，建表时需考虑：字段的名称字段的名称数据类型数据类型是否允许为是否允许为NullNull是否自动增长等是否自动增长等 383839393939数据类型n nSqlserverSqlserver有以下数据有以下数据类型类型数据类型类别数据类型类别数据类型类别数据类型类别 具体类型具体类型具体类型具体类型 精确数据精确数据精确数据精确数据 bigintbigint，decimaldecimal，intint，numericnumeric，smallintsmallint，moneymoney，tinyinttinyint，smallmoneysmallmoney，bitbit 近似数字近似数字近似数字近似数字 float,realfloat,real字符串字符串字符串字符串 char,varchar,text char,varchar,text UnicodeUnicode字符字符字符字符串串串串 nchar,nvarchar,ntext nchar,nvarchar,ntext 二进制字符串二进制字符串二进制字符串二进制字符串 binary,image,varbinary binary,image,varbinary 日期类型日期类型日期类型日期类型 datetime,smalldatetime datetime,smalldatetime 其他类型其他类型其他类型其他类型 Uniqueidentifier,xml,Uniqueidentifier,xml,tabletable等等等等 393940404040创建表n n创建表的实质就是定义表结构以及约束等属性。n nSQL 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 )4141CREATETABLE表名表名(字段字段1数据类型数据类型列的特征，列的特征，字段字段2数据类型数据类型列的特征，列的特征，。)42424242建表【示例3-1】：建立用户表：建立用户表UserInfo,UserInfo,建表时设建表时设置主键并设置相应字段的置主键并设置相应字段的not nullnot null（非空）属性（非空）属性 4242USEBookShopDb-将当前数据库设置为BookShopDbGOCREATETABLEUserInfo-创建用户表(UserIDchar(3)PRIMARYKEY,-UerID为主键UserNamechar(10)notnull,Sexchar(2)notnull,Ageint,hiredatedatetime,telechar(15),Addrvarchar(100)GO43434343建表n n表的IDENTITY 属性标识列字段的数据类型只能是标识列字段的数据类型只能是decimaldecimal，intint，numericnumeric，smallintsmallint，bitintbitint，tinyinttinyint每个表最多有一列具有每个表最多有一列具有IDENTITYIDENTITY属性，且该属性，且该列不能为空、不允许有默认值列不能为空、不允许有默认值IDENTITYIDENTITY属性的语法属性的语法:IDENTITY(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 4444USEBookShopDb-将当前数据库设置为BookShopDbGOCREATETABLEPublisher(PublisherIDintIDENTITY(1,1)PRIMARYKEY,-自动编号（标识列）PublisherNamevarchar(50)notnull-出版社名称，必须输入)GO45454545建表时使用约束4545USEBookShopDb-将当前数据库设置为BookShopDbGOCREATETABLEBookInfo/*创建BookInfo数据表*/(BookIDchar(15)PRIMARYKEY,-书号，主键BookNamevarchar(100)notnull,-书名Authorvarchar(50)notnull,-作者ISBNchar(13),-ISBN,null可以省略PublishDatedatetimenotnull,-出版日期PublisherIDintnotnullREFERENCESPublisher(PublisherID),-外键CategoryIDintnotnullREFERENCESCategory(CategoryID),-外键Pricemoney,-价格Contenttext-介绍)GO46464646建表时使用约束4646USEBookShopDb-将当前数据库设置为BookShopDbGODroptableuserInfoCREATETABLEUserInfo-创建用户表(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建表时使用约束4747USEBookShopDb-将当前数据库设置为BookShopDb,GOCREATETABLESalesMaster/*创建SalesMaster销售主表*/(SalesMasterIDintidentityprimarykey,-销售id,自动增长，主键SalesDatedatetimenotnull,-销售日期UserIDchar(3)notnull),CONSTRAINTfk_UserIDFOREIGNKEY(UserID)REFERENCESUserInfo(UserID)ONDELETECASCADE【示例示例3-7】建立建立SalesMaster销售主表，通过设定外键与UserInfo表之间建立联系48484848总结n n掌握数据表的概念和含义掌握数据表的概念和含义n n数据完整性、主键和外键的基本概念数据完整性、主键和外键的基本概念 主键是保证实体完整性的约束，建议每个表有一个主键主键是保证实体完整性的约束，建议每个表有一个主键 外键保证表之间的引用完整性，从表中的数据依赖于主外键保证表之间的引用完整性，从表中的数据依赖于主表表n n创建数据表创建数据表 创建表的时候要设定字段名，选取合适的数据类型和约创建表的时候要设定字段名，选取合适的数据类型和约束束 如果要保证某个列唯一并且自动增长，设定如果要保证某个列唯一并且自动增长，设定IDENTITYIDENTITY约束约束n n掌握掌握SQLServerSQLServer数据类型，三大类：数据类型，三大类：数字、字符和日期数字、字符和日期n n在建表时设置在建表时设置 主键约束主键约束 外键约束外键约束 检查约束检查约束 默认约束默认约束 空值约束空值约束 标识列标识列 4848第3章-2 SQLServer表管理494950505050本章目标n n约束的维护修改表添加约束修改表添加约束删除约束删除约束n n建立数据库关系图 以图形方式显示数据库表之间的关系以图形方式显示数据库表之间的关系n nSQL Server中的特殊表类型 已分区表已分区表临时表临时表系统表系统表n n修改和删除表修改表结构、删除表修改表结构、删除表505051515151约束的维护n n添加约束语法5151ALTERTABLE表名表名ADDCONSTRAINT约束名约束类型具体的约束说明约束名约束类型具体的约束说明USEBookShopDb-选择数据库，以下示例从略选择数据库，以下示例从略GOALTERTABLEUserInfoADDCONSTRAINTpk_UserIDPRIMARYKEY(UserID)添加主键约束添加主键约束添加外键约束添加外键约束ALTERTABLESalesDetailsADDCONSTRAINTfk_BookIDFOREIGNKEY(BookID)REFERENCESBookInfo(BookID)52525252约束的维护n n删除约束语法5252ALTERTABLE表名表名DROPCONSTRAINT约束名约束名ALTERTABLEUserInfoDROPCONSTRAINTpk_UserID53535353数据库关系图n n已建立了数据库中各个数据表之间的关系，可通过SQL Server提供的数据库关系图，即可以图表的形式显示各个数据表之间的关系n n如果表之间的关系没有建立，也可在通过数据库关系图建立数据表之间的关系 535354545454数据库关系图545455555555SQLServer中的特殊表类型n n分区表当表数据非常大时可以将一个表水平划分为多当表数据非常大时可以将一个表水平划分为多个表单独存储，并且可以存储在多个不同的物个表单独存储，并且可以存储在多个不同的物理文件中。这样的表就是分区表理文件中。这样的表就是分区表 n n临时表在操作过程中系统需要维护一些临时数据，比在操作过程中系统需要维护一些临时数据，比如果用户需要将查询结果排序，则会创建临时如果用户需要将查询结果排序，则会创建临时表并在临时表中将数据排序后返回给用户表并在临时表中将数据排序后返回给用户 n n系统表SQL Server SQL Server 将定义服务器配置及其所有表的将定义服务器配置及其所有表的数据存储在一组特殊的表中，这组表称为系统数据存储在一组特殊的表中，这组表称为系统表。任何用户都不应该直接更改系统表表。任何用户都不应该直接更改系统表 555556565656修改表结构n n修改表结构使用ALTER TABLE语句n n修改表结构增加列增加列删除列删除列修改列的数据类型修改列的数据类型改变列的长度等改变列的长度等n n在表没有数据的情况下，可以对列进行任意修改或删除 565657575757修改表结构-增加列和修改列5757USEBookShopDbGOALTERTABLEUserInfoADDemailchar(20)GO增加列增加列给UserInfo表增加列email,字符类型长度是20，非空修改列修改列USEBookShopDbGOALTERTABLEUserInfoALTERCOLUMNaddrvarchar(150)notnull增加UserInfo表的字段addr长度，将其增加到150，类型不变58585858修改表结构-删除列5858删除列删除列删除UserInfo表email列USEBookShopDbGOALTERTABLEUserInfoDROPCOLUMNemailGO59595959删除表n n删除表 DROP TABLE UserInfoDROP TABLE UserInfo如果删除的表和其他表有关联关系，必须先删除如果删除的表和其他表有关联关系，必须先删除子表，然后再删除主表。否则系统会弹出错误子表，然后再删除主表。否则系统会弹出错误 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修改添加字段修改添加字段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-SQLn nT-SQL实现数据的增加、修改、删除数据操作626263636363SQL和T-SQLn nSQL(Structure Query Language,结构化查询语言)主要功能就是同各种数据库建立联系，进行沟主要功能就是同各种数据库建立联系，进行沟通。通。用于存取数据以及查询、更新和管理关系数据用于存取数据以及查询、更新和管理关系数据系统系统 n nSQL语言主要包括 数据操纵语言（数据操纵语言（DMLDML）n n用来操作数据库中的数据，包括插入数据用来操作数据库中的数据，包括插入数据（INSERTINSERT）、修改数据（）、修改数据（UPDATEUPDATE）、删除数据）、删除数据（DELETEDELETE）、查询数据（）、查询数据（SELECTSELECT）等操作）等操作 数据定义语言（数据定义语言（DDLDDL）n n用于创建或修改数据库里的对象，如创建用于创建或修改数据库里的对象，如创建Create Create tabletable、修改、修改Alter table Alter table、删除、删除Drop tableDrop table等等 数据控制语言（数据控制语言（DCLDCL）n n用来控制用户对数据库数据的访问权限用来控制用户对数据库数据的访问权限,如：如：GrantGrant、RevokeRevoke等等636364646464T-SQL介绍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语法约定 语句不区分大小写语句不区分大小写 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,张家辉张家辉,男男,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】向向Category表插入数据,Category带有标识列CategoryIDINSERTINTOCategory(CategoryName)VALUES(计算机基础理计算机基础理论论)67676767使用使用SELECTINTO语句拷贝表语句拷贝表n nSELECT INTO SELECT INTO 可将几个表或视图中的数据组合成一个表可将几个表或视图中的数据组合成一个表 查看表数据：查看表数据：select *from copyUserInfoselect *from copyUserInfon n若只拷贝表结构：若只拷贝表结构：SELECT*INTO NewUserInfo FROM SELECT*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=甘肃酒泉甘肃酒泉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语法：7070DELETEFROM表名表名WHERE条件条件【例例4-14】删除参加工作日期在删除参加工作日期在2004-1-1之后的用户信息DELETEFROMUserInfoWHEREhiredate2004-1-1【例例4-15】删除BookInfo表中书号是B0001的记录DELETEFROMBookInfoWHEREBookID=B0001因为要删除BookInfo表的书号是B0001的记录被SalesDetails表引用，所以必须先删除SalesDetails表的相应记录，才能删除BookInfo表的记录71717171使用TRUNCATETABLE语句n n语法：7171TRUNCATETABLE表名表名【例例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 表名表名7272第5章 简单查询和函数737374747474本章目标n n掌握查询的基本语法n n掌握各种函数的应用字符串函数字符串函数日期函数日期函数数字函数数字函数n n掌握函数在查询中的应用747475757575查询查询7575查询请求查询请求查询结果集查询结果集结果集结果集76767676如何查询查询性别“男”的所有用户767677777777查询查询 语法：7777SELECTFROMWHEREORDERBYASC或DESC78787878查询数据n n返回所有的行和列返回所有的行和列查询表中所有字段时，可以用星号查询表中所有字段时，可以用星号*来代替字来代替字段名段名n n返回部分行和列返回部分行和列筛选部分字段，必须明确指定字段名筛选部分字段，必须明确指定字段名【例例5-25-2】查询查询查询查询BookInfoBookInfo表的表的表的表的BookName(BookName(书名书名书名书名),),作作作作者者者者(Author),(Author),出版日期出版日期出版日期出版日期(publishDate),(publishDate),类别类别类别类别ID(CategoryID),ID(CategoryID),价格价格价格价格(Price)(Price)等字段的数据等字段的数据等字段的数据等字段的数据 7878SELECT*FROMBookInfoSELECTBookName,Author,publishDate,CategoryIDFROMBookInfo79797979查询-使用别名7979SELECTbookNameas书名书名,Authoras作者作者,publishDateas出版日期出版日期,CategoryIDas类别类别,priceas价格价格FROMbookInfo1，用，用as关键字关键字【例例5-3】用中文名称来显示书的信息,3种不同使用别名方法2，用空格，用空格3，用，用=SELECTbookName书名书名,Author作者作者,publishDate出版日期出版日期,CategoryID类别类别,price价格价格FROMbookInfoSELECT书名书名=bookName,作者作者=Author,出版日期出版日期=publishDate,类别类别=CategoryID,价格价格=priceFROMbookInfo80808080查询-使用wheren n使用WHERE子句筛选满足条件的数据行 8080【例例5-4】查询2007年以后出版的所有书籍的书名、作者、出版日期和价格SELECTbookName,author,publishDate,priceFROMBookInfoWHEREPublishDate2007-01-0181818181函数n nT-SQL函数表示可以接受零个、一个或多个输入值，并返回一个标量值或一组值8181 表5-1 常用T-SQL函数函数类别函数类别 说明说明 字符串函字符串函字符串函字符串函数数数数 对字符串（对字符串（对字符串（对字符串（char char 或或或或 varcharvarchar）输入值执行运算，然后）输入值执行运算，然后返回一个字符串或数字值。返回一个字符串或数字值。日期和时日期和时日期和时日期和时间函数间函数间函数间函数 对日期和时间输入值执行运算，然后返回字符串、数对日期和时间输入值执行运算，然后返回字符串、数对日期和时间输入值执行运算，然后返回字符串、数对日期和时间输入值执行运算，然后返回字符串、数字或日期和时间值。字或日期和时间值。字或日期和时间值。字或日期和时间值。数学函数数学函数数学函数数学函数 基于作为函数的参数提供的输入值执行运算，然后返基于作为函数的参数提供的输入值执行运算，然后返基于作为函数的参数提供的输入值执行运算，然后返基于作为函数的参数提供的输入值执行运算，然后返回数字值。回数字值。回数字值。回数字值。系统函数系统函数系统函数系统函数返回系统配置信息、返回系统配置信息、返回系统配置信息、返回系统配置信息、返回有关数据库和数据库对象的返回有关数据库和数据库对象的返回有关数据库和数据库对象的返回有关数据库和数据库对象的信息、执行运算后返回信息、执行运算后返回信息、执行运算后返回信息、执行运算后返回 SQL Server SQL Server 实例中有关值、实例中有关值、实例中有关值、实例中有关值、对象和设置的信息，对象和设置的信息，对象和设置的信息，对象和设置的信息，返回有关用户和角色的信息等。返回有关用户和角色的信息等。返回有关用户和角色的信息等。返回有关用户和角色的信息等。82828282字符串函数n n字符串函数 字符串函数用于对字符串数据进行处理，如字字符串函数用于对字符串数据进行处理，如字符串截取、查找子串、计算字符串长度等，并符串截取、查找子串、计算字符串长度等，并返回一个字符串或数值返回一个字符串或数值 828283838383字符串函数838384848484字符串函数的使用8484SELECTbookName,Author,PATINDEX(%数据库数据库%,bookName)POSFROMBookInfoWHEREPATINDEX(%数据库数据库%,bookName)0【例例5-8】使用日期函数获取今天的日期时间及明天的日期和时间使用日期函数获取今天的日期时间及明天的日期和时间【例例5-7】使用使用PATINDEX函数查询函数查询BookInfo表中包含表中包含数据库数据库的书名和作的书名和作者以及者以及数据库数据库出现的位置出现的位置SELECTGETDATE()今天今天,DATEADD(DAY,1,GETDATE()明天明天85858585日期