数据库技术：SQL-SERVER-2000数据库管理与开发

SQLSERVER2000

数据库管理与开发目录SQLSERVER2000概述数据库管理表及其应用数据的查询与更新使用视图和索引Transact-SQL程序设计使用规则、默认值与触发器SQLServer中的安全管理备份和还原不同数据格式的转换数据库的事务管理数据库设计第1章SQLSERVER2000概述本章目标了解SQLServer2000SQLServer2000启动和停止管理服务器的方法使用SQLServer2000提供的管理工具和实用程序回忆以前学习的知识生活中遇到的大量数据，需要解决哪些问题？数据保存、查询、排序、筛选、删除……在Execl中学习的相关内容？数据清单、自动筛选和高级筛选、分类汇总、数据透视图和透视表Execl数据库数据库的发展史萌芽阶段——文件系统使用磁盘文件来存储数据初级阶段——第一代数据库出现了网状模型、层次模型的数据库中级阶段——第二代数据库关系型数据库和结构化查询语言高级阶段——新一代数据库“关系-对象”型数据库早期数据保存方式—

书面文件在数据库出现之前，人们书面文档来存储数据容易丢失数据数据重复、冗余难于查阅和使用维护成本高早期数据保存方式—计算机文件

操作、修改、查找数据1磁盘计算机系统进行数据处理2传送信息磁带可以以不同的文件格式进行数据保存今天，人们依然采用文件方式保存一些数据为什么使用数据库？条理分明检索方便维护容易减少错误信息挖掘名姓聘任时间KarlaJablonski1994-03-11MartineRance1992-02-05MargaretSmith1988-09-29GaryThomas1988-08-09计算机化数据库的好处数据结构化

数据能够共享

数据冗余度小，易扩充

数据与程序的独立性较高

对数据实行集中统一控制

可以建立和遵循某些标准便于维护数据完整性能够实现数据的安全性MSSQLServer2000是一个大型的管理数据库系统是一个多层的客户/服务器数据库系统采用请求-应答工作方式SQLServer2000是一种关系型数据库.以二维表的方式存储数据.数据库服务器响应和提供数据应用程序数据库操作和查询数据库和应用程序的请求-应答工作方式数据库模型模型：事物抽象特征或者过程的描述数据模型：描述用来存储数据的容器；在该容器中存储和检索数据的过程；数据库模型分类：层次模型网状模型关系模型关系模型记录和记录之间通过属性之间的关系来进行连接保证数据独立性，并形成数据集之间的关系753862981成绩学号刘三阳3李东2张明1姓名学号刘三阳753姓名成绩学号基于关系模型的数据库目前较流行的关系型数据库:桌面数据库MSAccess大型网络数据库MSSQLServer、Oracle和Sybase、DB2等SQLServer2000的版本包括企业版、标准版、个人版、开发版和用于测试的评估版，用户可以根据自己的需要和软、硬件环境选用相应的产品。企业版：最大支持32个CPU、64G内存的多处理器系统，适合于大规模商业应用；标准版：最大支持4个CPU、2G内存的多处理器系统，适合于中小规模的商业应用；个人版：支持两个CPU的处理器系统，适合于个人或小型工作组的实验应用；开发版：适合于开发人员应用；评估版：适合于开发人员体验SQLServer2000的新功能。SQLServer2000的新特性支持XML支持多实例改进的数据仓库及智能化业务(BI)支持微软Windows2000操作系统改进的性能和可扩展性改进的向导界面改进的查询分析器改进的DTSSQLServer2000的实例安装SQLServer2000数据服务器就是安装SQLServer2000数据库引擎实例一台计算机上可安装多个SQLServer数据库引擎实例在单台计算机上可以运行的实例数目取决于可用资源，SQLServer2000最多支持16个实例SQLServer实例类型默认实例

一台计算机上只能有一个默认实例默认实例可以是SQLServer的任何版本

命名实例

安装SQLServer2000的系统要求

企业版：WinNTServer、Win2000Server、

WinAdvancedServer、Win2000DataCenter

标准版：WinNTServer、Win2000Server、

WinAdvancedServer、Win2000DataCenter

个人版：Win98、WinMe、WinNTWorkstation、

Win2000Professional及更高版本Windows系统 开发版：WinNTWorkstation、Win2000Professional

及更高版本Windows系统 客户端：WinNT、Win2000、WinMe、Win98等SQLServer2000管理工具简介服务管理器：服务启动、暂停和停止管理企业管理器：可以完成几乎所有管理工作查询分析器：允许输入、执行SQL语句并返回结果客户端网络实用工具：客户端数据库连接工具事件探查器：数据库事件管理工具数据导入/导出工具：用于数据的传递与转换服务管理器SQLServer服务SQLServer代理服务MSDTC服务每个SQLServer实例都有一个SQLServer服务，默认实例名为MSSQLServer在对SQLServer数据库进行任何操作之前，必须启动本地或远程SQLServer服务器。使用服务管理器使用命令服务管理：运行：NetStartmssqlserver暂停：Netpausemssqlserver继续：Netcontinuemssqlserver停止：Netstopmssqlserver启动和停止SQLServer服务SQLServer的日常管理是在企业管理器中进行的，在使用企业管理器管理本地或远程SQLServer服务器时，必须先在企业管理器中对该服务器注册。在SQLServer企业管理器中可以完成注册服务器的操作。SQLServer服务器注册成功后，在企业管理器中依次展开MicrosoftSQLServers、SQLServer组、服务器节点，在服务器节点上指示灯是绿色的，表示服务器连接成功。注册服务器和创建服务器组使用企业管理器组注册数据库数据库对象数据库表记录在服务器节点上指示灯是绿色的，表示服务器连接成功登录查询分析器使用查询分析器对象浏览器总结SQLServer2000的版本启动和停止SQLServer服务注册服务器和创建服务器组SQLServer2000管理工具简介SQLServer2000的两种安全模式第2章数据库管理本章目标掌握数据库系统的组成明确数据库文件分类掌握SQLServer2000的系统数据库能够进行用户数据库管理学习数据库的分离与附加什么是数据库？数据库就是按照一定的组织结构存储在计算机存储介质上的相关数据的集合。数据库具有以下的特点：结构化：数据库文件及文件内部数据都有一定的结构；独立性：数据的逻辑组织与物理存储方式无关；数据共享，减少冗余：数据集中存储，统一管理；安全性与完整性：保证数据的正确性和可靠性数据冗余和数据完整性示例

学号

姓名1

张明2

王东3

李健3

赵彬失去了数据完整性学号必须唯一

学号

姓名1

张明2

王东3

李健4

赵彬

学号成绩

姓名189

张明276

王东380

李健467

赵彬存在数据冗余什么是DBMS？数据库管理系统是用来帮助用户建立、使用和管理数据库的软件系统，简称DBMS。数据库管理系统的功能：建立：建立数据库结构，并存储用户数据；操作：插入、修改、删除、检索、统计汇总、打印等；其他：数据安全性、数据完整性、并发控制等管理计算机基本系统数据库系统的组成有关应用所需要的工作数据的集合，是数据库的主体数据库管理系统数据库物理数据库描述数据库关于各级数据结构的描述数据，通常由一个数据词典系统管理SQLServer数据库SQLServer2000是一种采用SQL语言的关系数据库管理系统，是以文件的形式存储在磁盘上的(以文件组形式进行组织管理)，数据库文件(使用便于管理的规则命名)根据作用不同分为三种。文件名扩展名存在数量作用(存放内容)主数据文件.MDF仅有1个数据和数据库启动信息辅助数据文件.NDF0个或多个存放数据事务日志文件.LDF1个或多个存放数据库的修改信息数据库对象数据库中的数据按不同形式组织在一起，构成了不同的数据库对象数据库对象在磁盘上没有对应的文件SQLServer2000中有以下数据库对象：表、视图、存储过程、触发器、用户自定义数据类型、用户自定义函数、索引、规则、默认、约束等文件组将多个数据文件存放在不同的磁盘上并把多个数据文件组成一个或多个文件组。数据库根据组内数据文件的大小，按比例地写入组内所有数据文件，使多个磁盘同时并行工作、提高读写速度Northwnd.ldfE:\日志文件…

Orders

Customers

Products

OrdHistYear2

OrdHistYear1

主文件组Northwnd.mdfC:\用户定义文件组OrdHist1.ndfOrdHist2.ndfD:\E:\sys…

sys…

sysusers

sysobjects

示范数据库Northwind的物理文件结构——文件组和数据存储tempdb临时数据库，临时数据可被自动删除，不需要特殊权限，系统中仅有一个，空间不足可自动扩展系统数据库用户数据库mastermodelmsdbpubsNorthwindUser1distribution示例数据库主控数据库，用于管理其他数据库和保存SQLServer系统信息模板数据库，当创建一个用户数据库时其内容自动复制到新数据库中SQLServer代理使用其安排报警、作业并记录操作员系统数据库系统数据库示例数据库系统表SQLServer2000中的每个数据库都包含系统表系统表在创建数据库时自动产生系统表用来记录SQLServer组件所需的数据SQLServer的操作能否成功，取决于系统表中信息的完整性任何用户都不应直接修改系统表常用的master系统表系统表名称用途sys_server_info保存服务器及安装信息spt_values用于保存系统表的特征值sysconfigures保存服务器选项的代码、名称、当前设置sysdatabases保存所有数据库的代码、名称、创建日期等syslogins保存系统所有登录账号sysdevices用于保存系统数据库设备和备份的信息常用数据库系统表系统表名称用途sysobjects用于保存数据库中所有的数据库对象的信息，包括表、视图、存储过程、规则等systypes记录系统数据类型和用户定义的数据类型sysfiles记录数据库的数据文件和日志文件的信息sysdevices用于保存数据库的文件组信息sysusers记录数据库中所有的用户和角色创建用户数据库一个数据库是包含表、视图、存储过程及触发器等数据库对象的容器，在数据库中建立的各种数据库对象都保存在数据库文件中创建数据库之前应考虑好数据库的拥有者、数据库的初始容量、最大容量、增长量及数据库文件存放的路径等因素要创建数据库，用户必须具有相应的权限可以使用企业管理器、CREATEDATABASE语句和向导三种方法创建数据库3-1演示使用企业管理器创建数据库…数据文件的逻辑名数据文件的物理名文件组日志文件不属于任何文件组createdatabase

数据库名

[on[primary]

{([name=

数据文件的逻辑名称,]

filename=

‘数据文件的物理名称’

,

[size=

数据文件的初始大小,][maxsize=数据文件的最大容量,][filegrowth=

数据文件的增长量])}[,…n]

logon{([name=

事务日志文件的逻辑名称,]

filename=

‘事务日志文件的物理名称’

,

[size=

事务日志文件的初始大小,][maxsize=事务日志文件的最大容量,][filegrowth=

事务日志文件的增长量])}[,…n]

用CREATEDATABASE语句创建数据库

例2.1用语句创建一个名为students的数据库，包含一个主数据文件和一个事务日志文件。这两个文件都放在‘d:\data’目录中。

主数据文件逻辑文件名为‘studentdata’，物理文件名为‘student_data.mdf’，初始容量大小为10MB，最大容量为50MB，每次增长量为25%。

事务日志文件的逻辑文件名为‘studentlog’，物理文件名为‘student_log.ldf’，初始容量大小为10MB，最大容量为不受限制，每次增长量为2MB。createdatabasestudentsonprimary(name=studentdata,filename='d:\data\student_data.mdf',size=10,maxsize=50,filegrowth=25%)logon(name=studentlog,filename='d:\data\student_log.ldf',size=10mb,maxsize=unlimited,filegrowth=2mb)示例用T-SQL语句在C:\DATA\与D:\DATA\文件夹中创建一个教师信息数据库teacher，该数据库包含：一个主数据文件逻辑名‘teacherdata1’，物理名‘C:\DATA\tdata1.mdf’,初始容量1MB，最大容量10MB，每次增长量为15%。一个辅助数据文件逻辑名‘teacherdata2’物理名‘D:\DATA\tdata2.ndf’,初始容量2MB，最大容量15MB，每次增长量为2MB。一个事务日志文件逻辑名‘teacherlog’物理名‘D:\DATA\teacherlog.ldf’，初始容量512KB，最大容量不受限制，每次增长量为512KB。createdatabaseteacheronprimary(name=teacherdata1,filename='C:\DATA\tdata1.mdf',size=1,maxsize=10,filegrowth=15%),(name=teacherdata2,filename='C:\DATA\tdata2.ndf',size=2,maxsize=15,filegrowth=2)logon(name=teacherlog,filename='D:\DATA\teacherlog.ldf',size=512kb,maxsize=unlimited,filegrowth=512kb)(答案)SQL语句在书写时不区分大小写，一条语句可以写在多行上，但不能将多条语句写在一行上。演示使用向导创建数据库…创建数据库3-3完成后会询问“是否马上为新创建的数据库创建维护计划”。维护计划就是设置何时自动定期对数据库进行维护。查看数据库信息数据库信息主要包括三方面：基本信息、维护信息和空间使用情况。使用企业管理器查看数据库信息演示使用企业管理器查看数据库信息…使用sp_helpdb查看数据库信息语法：[execute]sp_helpdb[数据库名]配置数据库选项仅允许特殊用户访问数据库数据库只能读取，不能修改当最后一个用户退出，SQLServer将自动关闭该数据库并释放其所占的系统资源数据库文件中未使用空间超过文件大小的25%，将自动缩小空间，缩小后数据库文件的大小不能小于初始大小。设置只读时，此选项无效

对数据库访问权限进行设置和修改

修改数据库特性，同样也可以使用‘企业管理器’alterdatabase数据库名addfile<文件格式>[tofilegroup文件组]|addlogfile<文件格式>|removefile逻辑文件名|addfilegroup文件组名|removefilegroup文件组名|modifyfile<文件格式>|modifyfilegroup文件组名，文件组属性(name=数据文件的逻辑名称

[，filename=‘数据文件的物理名称’]

[，size=数据文件的初始大小][，maxsize=数据文件的最大容量|unlimited][，filegrowth=数据文件的增长量])在修改文件的“分配的空间”项时，所改动的值必须大于现有的空间值。例2.5用语句向students数据库添加一个名为studentsfilegroup的文件组，并将student_dataadd.ndf添加到此文件组中。--添加文件组alterdatabasestudentsaddfilegroupstudentsfilegroupgo--添加数据文件，并将其加入新文件组alterdatabasestudentsaddfile(name=studentdataadd,filename='d:\data\student_dataadd.ndf')tofilegroupstudentsfilegroupgo--查看数据库信息executesp_helpdbstudentsgo示例将students数据库原有主数据文件studentsdata的物理文件名改为C:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\students.mdf，初始大小改为2MB；按2MB自动增长到最大容量20MB。alterdatabasestudentsModifyfile(name=studentsdata,filename=‘C:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\students.mdf

',size=2,maxsize=20,filegrowth=2(答案)删除数据库删除一个数据库会删除该数据库中的所有对象，从而释放出该数据库所占用的磁盘空间注意当数据库处于正在使用、正在被恢复和正在参与复制三种状态之一时，不能删除该数据库若要删除的数据库正在被其他用户所使用，则可以先断开服务器与该用户的连接，然后删除该数据库系统数据库中的master、model和tempdb不能被删除，msdb虽然可以被删除，但删除msdb后很多服务(比如SQLServer代理服务)将无法使用，因为这些服务在运行时会用到msdb删除数据库2–1演示在企业管理器中删除数据库…演示在查询分析器中删除数据库…(例2.6)删除数据库2–2创建数据库关于数据库的命令createdatabasealterdatabasedropdatabase修改数据库删除数据库sp_helpdb查看数据库定义信息分离数据库实际上是从SQLServer系统中删除数据库

组成该数据库的数据文件和事务日志文件依然完好无损的保存在磁盘上

注意只有固定服务器角色成员sysadmin才可执行分离操作系统数据库master、model

和tempdb数据库无法从系统分离出去

数据库与SQLServer系统的分离若要执行分离操作，必须先单击“清除”按钮清除连接

使用这些数据文件和事务日志文件可以将数据库再附加到任何SQLServer系统中，而且数据库在新系统中的使用状态与它分 离时的状态完全相 同附加数据库只需要 指定要附加到SQL Server系统的数据 库主数据文件的路 径与文件名

将数据库附加到SQLServer系统总结概念：数据库、DBMS数据库系统的组成SQLServer数据库文件分类数据库对象SQLServer的系统数据库用户数据库的创建、修改和删除查看数据库信息设置数据库选项数据库的分离与附加第3章表及其应用本章目标理解表的概念掌握表的创建、修改、删除查看表的信息表是相关联的行列集合。表是按照行列结构存储数据的。描述一个个体属性的总和称为一条记录。描述个体的一个属性称为一个字段，也称数据项。结构和数据记录是表的两大组成部分。表的概念回顾数据库数据库分类系统数据库示例数据库用户自定义数据库数据库文件数据库对象本节目标表的概念数据类型表的创建、修改、删除查看表的信息表3.1人事表姓名性别年龄电话职位张三男30234567市场部经理李四男28987657开发部经理王五女34987456电脑操作员赵六女225245652教师行/记录列/属性/字段/数据项表是相关联的行列集合。表是按照行列结构存储数据的。描述一个个体属性的总和称为一条记录。描述个体的一个属性称为一个字段，也称数据项。结构和数据记录是表的两大组成部分。SQLServer中创建表的限制

每个数据库里最多有20亿个表。每个表上最多可创建一个聚集索引，249个非聚集索引。每个表最多可以设置1,024个字段。每条记录最多占8,060B，但不包括text字段和image字段。创建表前的考虑

数据库中要存放哪些数据，这些数据如何划分到表中。确定每个表需要哪些列，即决定表所包含的属性有哪些。SQLServer的数据类型包括两类：系统定义的数据类型

用户定义的数据类型数据类型是用来表现数据的特性的，表中的每列数据必须是同一种类型的数据，列的数据类型确定了该列数据的取值范围。系统定义数据类型charvarchartextncharnvarcharntextmoneysmallmoneybinaryvarbinaryimagedatetimesmalldatetimebittinyintsmallintintbigintnumericdecimalfloatreal二进制货币数字字符SQLServer2000数据类型整数位日期时间型统一字符型精确小数近似数值char(2)nchar(2)decimal(5,2)0/1以外的任何值都会作为1来处理字符型需用引号引起来需用引号引起来存储图像等数据带有4位小数的decimal类型的数据域完整性实体完整性参照完整性数据完整性正确性+一致性=表中所有行唯一，即所有记录都是可以区分的表中指定列的数据具有正确的数据类型、格式和有效的数据范围有关联的两个或两个以上表之间数据的一致性。可以通过建立主键和外键约束来实现。数据完整性可以通过各种约束、规则、默认、触发器等来保证外键约束(foreignkey)数据完整性保证

——约束：对输入数据取值范围和格式的限制唯一约束(unique)检查约束(check)默认值约束(default)空值约束(null)主键约束(primarykey)主键约束

每个表仅能定义一个主键主键值是表中记录的标识主键列可以有一个或多个列组合而成主键值不可为空(null)

主键值不能重复

image和text类型的列不能做主键是一列或几列的组合用于保证非主键的不可重复字段可以为表定义多个唯一约束只能为表定义一个主键约束该列允许为空值，但只允许一个空值存在唯一约束可以定义在允许空值的列上主键约束只能定义在不允许为空值的列上唯一约束不改变字段的物理存储位置记录按主键值的指定顺序存储唯一约束设置外键约束字段的表称为子表，它所引用的表称为父表外键可以是单一字段，也可以是多个字段的组合外键所引用父表中的字段必须是创建了主键约束或唯一约束的列子表中外键字段添加的新数据，必须在父表存在子表中引用父表数据的记录未删除，父表中被引用数据不能被删除外键约束子表

记录如果数据项不在父表中添加父表如果导致子表产生孤立记录删除外键约束：用来建立两个表之间的关联检查约束

用指定的条件检查限制输入数据的取值范围是否正确用于保证数据的参照完整性和域完整性如：成绩必须>0

默认值约束是给某个字段绑定一个默认的初始值输入记录时若没有给出该字段的数据，则自动填入默认值以保证数据的域完整性对设置了默认值约束的字段若输入数据时则以输入的数据为准可为常量、内置函数或表达式默认值约束

空值NULL是不知道或不能确定的特殊数据不等同于数值0和字符的空格空值约束就是设置某个字段是否允许为空用以保证数据的实体完整性和域完整性空值约束在企业管理器中创建数据库表每个表必须有一个主键字段不能存在重复字段每个表必须包含单个实体的信息表的每个字段必须依赖于主键字段所有非主键字段不能相互依赖字段必须符合命名规则createtable表名

({字段名列属性列约束}[,…n])使用T-SQL创建表数据类型[(长度)][null|notnull][identity(初始值,步长值)]列属性的格式[constraint约束名]primarykey[(列名)][constraint约束名]unique[(列名)][constraint约束名][foreiginkey[(外键列)]]references引用表名(引用列)[constraint约束名]check(检查表达式)[constraint约束名]default默认值列约束的格式使用T-SQL创建表

列名称CREATETABLECa (CaId

CaName De Pi

数据类型

intIDENTITY(1,1)

nvarchar(15)ntextChar(10)空或非空

primarykey,NOTNULL,NULL,NULL)CREATETABLECa(CaId

int

IDENTITY(1,1)

primarykey,CaNamenvarchar(15)NOTNULL,DentextNULL,PiChar(10)NULL)例3.1用语句创建customers表，记录客户的基本信息。

表中包含如下内容：客户编号、姓名、地址、电话和出生日期。

设置客户编号字段为主键约束，电话字段为唯一约束，姓名字段不能为空。createtablecustomers(客户编号intprimarykey,

姓名varchar(20)notnull,

地址varchar(50),

电话varchar(20)unique,

出生日期datetime)例3.2用语句创建goods表，记录货品的基本信息。

表中包含如下内容：货品名称、库存量、供应商、状态和价格。

设置字段为主键约束，状态字段为类型。createtablegoods(货品名称varchar(20)primarykey,

库存量int,

供应商varchar(50),

状态bitdefault0,

价格money)货品名称bit例3.3用语句创建orders表，记录客户订购货品的订单信息。

表中包含内容：订单号、货品名称、客户编号、数量、总金额和订货日期。

设置订单号字段为自动编号，初始值为1，递增量为1，同时设置为主键。

客户编号字段为外键约束。

数量字段检查约束为数量大于0，同时不能为空。

订货日期默认值，为系统日期。createtableorders(订单号

intidentity(1,1)primarykey,

货品名称

varchar(20)notnullreferencesgoods(货品名称),

客户编号

intforeignkeyreferencescustomers(客户编号),

数量

intnotnullcheck(数量>0),

总金额

money,

订货日期

datetimedefaultgetdate())练习图书信息表字段名类型大小说明图书编号Char10主键作者vchar20出版社vchar60定价money——读者信息表字段名类型大小说明读者编号char8主键姓名char8性别char2默认‘男’身份证号char18联系电话vchar20借阅信息表字段名类型大小说明图书编号Char10组合主键读者编号Char8借阅时间Datetime——默认时间：系统当前日期归还时间Datetime——是否超期bit——默认值‘0’超期罚款money——图书信息管理数据库1设置/查看约束除了在语句中设置约束，还可以通过‘企业管理器’进行设置。设置主键约束设置唯一约束设置外键约束设置检查约束添加/查看数据使用‘企业管理器’添加/查看数据通过Insert语句添加数据查看表信息通过‘企业管理器’.[execute]

sp_help

[表名]两种方法。使用‘企业管理器’：‘所有任务’→‘显示相关性’查看表之间的依赖关系修改表定义altertable表名

add列名数据类型[(长度)][null|notnull] |altercolumn列名数据类型[(长度)][null|notnull]

|dropcolumn字段名

[,…n] |addconstraint约束定义

[,…n] |dropconstraint约束名

[,…n] |nocheckconstraint约束名

|checkconstraint约束名

使用ADD子句添加列AlterTable表名

Add列名数据类型[(长度)][NULL|NotNULL]若向已存在记录的表中添加列，新添加的列可以设置允许为空；若不允许为空，则需指定默认值。演示示例3.4…使用ADDCONSTRAINT子句添加约束AlterTable表名

AddConstraint约束定义[,……]演示示例3.5…约束对象5-1USEAA

CREATETABLEABCASDintDefault8,ASDEvarchar(20)Default'UNKNOWN')DEFAULT约束USEAAALTERTABLEABC

ADDCONSTRAINTDF_ASDDEFAULT10FORASD约束对象5–2CREATETABLEABCD(ASDint,ASDEvarchar(80)Default'UNKNOWN')CHECK约束USEABCDALTERTABLEABCDADDCONSTRAINTCK_ASD

CHECK(ASD>0ANDASD<100)约束对象5-4CREATETABLEAAC(ASDintCHECK(ASD<100)PRIMARYKEY,ASDEint)UNIQUE约束USENorthwindALTERTABLEAACADDCONSTRAINTU_ASDEUNIQUE(ASDE)使用DROPCONSTRAINT子句删除约束AlterTable表名

DropConstraint约束名[,……]演示示例3.6…使用DROPCOLUMN子句删除列AlterTable表名

DropColumn字段名[,……]演示示例3.7…在删除列时，必须先删除基于该列的索引和约束后，才能删除该列。删除表定义droptable表名

[,…n]droptable语句不能删除系统表。如果一个表被其他表通过外键约束引用，那么必须先删除设置了外键约束的表，或删除其外键约束。总结概念：表、数据完整性、数据类型、数据约束表的创建、修改和删除为表添加数据查看表的信息定义信息表的约束表中的数据表之间的依赖关系第4章数据的查询与更新本章目标掌握数据查询简单查询统计指定数据源子查询使用语句进行数据更新添加数据修改数据删除数据SELECT语句的基本语法格式select列名1，列名2，……，列名n[into新表名][from表名1，表名2，……，表名n][where条件表达式][groupby列名1，列名2，……，列名n][having条件表达式][orderby列名1[asc|desc]，……，列名n[asc|desc]]使用select子句字段列表中的列可以是表中所定义的列，也可以是派生列。派生列就是由多个列运算后产生的列，或者是利用算术函数计算后产生的列。例4.1查询customers表中的所有信息。Usemarketselect*fromcustomers输出表中所有列select*from数据源例4.2从customers表中选取‘姓名’和‘电话’两个字段作为查询的输出字段。Usemarketselect姓名,电话fromcustomers输出表中部分列select字段列表from数据源例4.3从goods表中选取‘货品名称’和‘总价格’两个字段作为查询的输出字段，同时要求输出一‘总价格’信息，其中‘总价格’=‘库存量’╳‘价格’计算确定，‘货品名称’列的别名改为‘商品名称’。Usemarketselect货品名称

as商品名称,总价格=库存量*价格

fromgoods为结果集中的列指定别名格式一：select列别名=原列名from数据源格式二：select原列名as列别名from数据源例4.4从orders表中选取‘货品名称’字段，要求删除重复记录。过滤结果集中的重复行selectdistinct

字段列表from数据源Usemarketselectdistinct货品名称fromorders例4.5查询customers表的前三条记录的信息。Usemarketselecttop3*fromcustomers限制返回行数selecttopn[percent]

字段列表from数据源使用into子句通过select…into可以创建一个新表并将查询结果记录添加到新表中。用户在执行一个带有into子句的select语句时，必须拥有在目标数据库上创建表的权限。

select…into不能与compute子句一起使用。新表所含的列为select指定列名，行由where确定。新表可以是临时表，也可以是永久表。其中，临时表表名以‘#’开头。使用where子句通过where子句查询满足特定条件的记录。在where子句中，组成表达式的运算符有六种：比较运算符、范围运算符、列表运算符、模式匹配符、空值判断符、逻辑运算符。例4.7查询goods表中库存量大于等于100的货品记录。Usemarketselect*fromgoodswhere库存量>=1001、比较运算符——用来比较大小测试表达式比较运算符比较值其中，比较运算符有：>>==<<=<>!=!>!<例4.8查询goods表中库存量不在100~500之间的货品记录。Usemarketselect*fromgoodswhere库存量

notbetween100and5002、范围运算符——用来判断列值是否在指定范围内测试表达式[not]between起始值and终止值测试表达式必须与起始值和终止值的数据类型相同。例4.9查询customers表中姓名是‘王兰’、‘李红’的客户信息。Usemarketselect*fromcustomerswhere姓名in(‘王兰’,‘李红’)3、列表运算符——用来判断给定列值是否在给定值的列表中测试表达式[not]in(列值1,…列值n)例4.10查询customers表中姓李的客户信息。select*fromcustomerswhere姓名like'李%'4、模式匹配运算符——用来判断字符型数据值是否与指定通配格式相符测试表达式[not]like‘通配符’通配符包括：%：代表0个或多个字符的任意字符串

_：代表单个字符

[]：代表指定范围内的单个字符

[^]：代表不在指定范围内的单个字符例4.11查询goods表中不知道供应商名称的货品信息，即供应商列为空值。Usemarketselect*fromgoodswhere供应商isnull5、空值运算符——用来判断所指定的列值是否为空值测试表达式is[not]

null例4.12用两种方法查询goods表中库存量在100~1000之间的货品记录。Usemarketselect*fromgoodswhere库存量

between100and1000select*fromgoodswhere库存量>=100and库存量<=10006、逻辑运算符——用来连接多个条件，以便构成一个复杂的查询条件逻辑表达式1and|or逻辑表达式2

not逻辑表达式使用orderby子句

orderby{列名[asc|desc]}[,…n]例4.13查询orders中的所有信息，在查询结果中按‘货品名称’降序显示，在‘货品名称’相同情况下，按订货的‘数量’升序显示。Usemarketselect*fromordersorderby货品名称desc,数量使用集合函数集合函数可将查询结果中的记录进行汇总计算，并将满足条件的记录汇总生成一条新记录。max([all|distinct]列名)sum([all|distinct]列名)avg([all|distinct]列名)min([all|distinct]列名)count([*|all|distinct]列名)例4.14计算goods表中所有已定价货品的平均价格、总价、最高价、最低价及记录的个数。select平均价=avg(价格),sum(价格)as总价格,max(价格)最高价,

最低价=min(价格),

定价记录个数=count(价格)fromgoods使用groupby子句

groupby列名[having条件表达式]按照指定的列，将列值相同的记录分组，对分组进行汇总计算。每一组生成一条记录。

select子句中的列名必须在groupby子句中存在的

where是对表中记录进行筛选；having则是对分组进行筛选。所以having子句中可以有聚合函数，而where子句中不能有聚合函数。有时两者可以互换。GROUPBY子句2–1USEnorthwindSELECTproductid,orderid

,quantityFROMorderhistSELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductidproductidtotal_quantity115235345productidorderidquantity11511102110222531153230productidtotal_quantity235SELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistWHEREproductid=2GROUPBYproductidGROUPBY子句2-2USEnorthwindSELECTproductid,orderid,quantityFROMorderhistUSEnorthwindSELECTproductid,SUM(quantity)AStotal_quantityFROMorderhist

GROUPBYproductid

HAVINGSUM(quantity)>=30productidtotal_quantity235345productidorderidquantity11511102110222531153230例4.15统计orders表中各种货品的订货总数量。select货品名称,sum(数量)fromordersgroupby货品名称例4.16用两种方法统计orders表中除pen以外各种货品的订货总数量。select货品名称,sum(数量)fromordersgroupby货品名称

having货品名称<>'pen'select货品名称,sum(数量)fromorderswhere货品名称<>'pen'groupby货品名称使用compute子句

compute聚集函数[by列名]与集合函数的区别是compute子句不仅要显示汇总数据，还要显示参加汇总的记录的详细信息，而集合函数仅显示汇总数据。

使用computeby子句时必须先按照分组列进行排序，所以computeby子句必须与orderby子句连用。例4.17统计orders表各种货品订货总数量，并给出明细表。Usemarketselect*fromordersorderby货品名称

computesum(数量)by货品名称指定数据源联接内联接外联接自联接交叉联接指定选择查询的来源时，最简单的情况是from子句中仅指定一个表名。在实际应用中，一个选择查询往往需要从多个表中查询数据，这就需要使用连接查询。使用交叉连接交叉连接又称非限制连接，它将两个表不加任何约束地组合在一起。格式一：select列名列表from表名1crossjoin表名2格式二：select列名列表from表名1,表名2例4.18将customers表和orders表进行交叉连接，观察结果。select*fromcustomerscrossjoinordersselect*fromcustomers,orderssalesbuyer_idprod_idqty114323151553711421003buyersbuyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyer_id1234USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyers,sales交叉连接查询结果buyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyer_idqty111115151515AdamBarr25SeanChai2……使用内连接内连接又称自然连接，它将两个表中满足连接条件的记录组合在一起。内连接就是将交叉连接产生的结果集经过连接条件过滤后得到的。格式一：select列名列表from表名1[inner]join表名2on表名1.列名=表名2.列名格式二：select列名列表from表名1,表名2where表名1.列名=表名2.列名salesbuyer_idprod_idqty114323151553711421003buyersbuyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyer_id1234查询结果buyer_nameAdamBarrAdamBarrErinO’MeliaEvaCoretsbuyer_idqty11431553711ErinO’Melia41003USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersINNERJOINsalesONbuyers.buyer_id=sales.buyer_id内连接例4.19用内连接方法连接customers和orders两个表，观察连接后的结果。selectc.客户编号,c.姓名,o.数量,o.总金额

fromcustomersascinnerjoinordersasoonc.客户编号=o.客户编号selectc.客户编号,c.姓名,o.数量,o.总金额

fromcustomersasc,ordersasowherec.客户编号=o.客户编号使用外连接外连接又分为左外连接、右外连接、全外连接三种。例4.20使用左外连接查询goods表和orders表，获取存在订货的货品名称、订货日期和订货数量。selectgoods.货品名称,订货日期,数量

fromgoodsleftjoinordersongoods.货品名称=orders.货品名称左外连接就是将左表的所有记录分别与右表的每一条记录进行连接组合，结果集中除返回内部连接的记录外，还包括左表不符合条件记录并在右表相应列填上null。select列名列表from表名1left[outer]join表名2on表名1.列名=表名2.列名salesbuyer_idprod_idqty114323151553711421003buyersbuyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyer_id1234查询结果buyer_nameAdamBarrAdamBarrErinO’MeliaEvaCoretsbuyer_idqty11431553711ErinO’Melia41003SeanChaiNULLNULLUSEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersLEFTOUTERJOINsalesONbuyers.buyer_id=sales.buyer_id左外连接例4.21使用右外连接查询goods表和orders表，获取存在订货的货品名称、订货日期和订货数量。selectgoods.货品名称,订货日期,数量fromordersrightjoingoods

ongoods.货品名称=orders.货品名称右外连接与左外连接正好相反。select列名列表from表名1right[outer]join表名2on表名1.列名=表名2.列名例4.22使用全外连接查询goods表和orders表，获取存在订货的货品名称、订货日期和订货数量。selectgoods.货品名称,订货日期,数量

fromgoodsfulljoinordersongoods.货品名称=orders.货品名称全外连接就是将左表的所有记录分别与右表的每一条记录进行连接组合，结果集中除返回内部连接的记录外，还包括两表不符合条件记录并在其他相应列填上null。select列名列表from表名1full[outer]join表名2on表名1.列名=表名2.列名子查询如果一个select语句能够返回一个单值或一列值并嵌套在一个select、insert、update或delete语句中，则称之为子查询或内层查询，而包含一个子查询的语句则称为主查询或外层查询。子查询可实现多层嵌套，且要用圆括号括起来。常把子查询用在外层查询的where子句或having子句中，与比较或逻辑运算符一起构成查询条件。

子查询又分为嵌套子查询和相关子查询。嵌套子查询与相关子查询嵌套子查询总是最先执行最内层查询。相关子查询的执行过程依赖于值的外部查询。相关子查询的WHERE

子句引用外部查询的FROM

子句中的表。这就是说子查询是以循环的方式进行处理的——

对外部查询选择的每行都执行一次。例4.26从goods表中检索数据，列出高于平均价格的货品信息。select*fromgoodswhere

价格> (selectavg(价格)fromgoods)使用子查询进行比较测试通过比较运算符（>，>=，<，<=，=，<>，!=，!>，!<），将一个表达式的值与子查询返回的单值进行比较。如果比较运算的结果为TRUE，则比较测试也返回TRUE。例4.27从orders表中检索数据，以查询有订单的客户信息。select*fromcustomerswhere

客户编号in (select

客户编号fromorders)使用子查询进行集成员测试通过逻辑运算符IN或NOTIN将一个表达式的值与子查询返回的一列值进行比较。使用IN运算符时，如果该表达式的值与此列中的任何一个值相等，则集成员测试返回TRUE，否则返回FALSE；使用NOTIN的测试结果则正好取反。使用子查询进行存在性测试通过逻辑运算符EXISTS或NOTEXISTS，检查子查询返回的结果集是否包含有记录。使用EXISTS运算符时，如果子查询的结果集中包含一条或多条记录，则存在性测试返回TRUE，否则返回FALSE；使用NOTEXISTS的测试结果则正好取反。例4.28从orders表中检索数据，以查询有订单的客户信息。select*fromcustomerswhereexists (select*fromorderswhere customers.客户编号=orders.客户编号)例4.29用IN子句完成上例的操作。select*fromcustomers where客户编号in(select客户编号fromorders)使用子查询进行批量比较测试使用子查询进行批量比较测试时，除了使用各种比较运算符外，还需要用到两个逻辑运算符：ANY和ALL。1.使用ANY运算符进行批量比较测试测试表达式比较运算符ANY(子查询)通过比较运算符将一个表达式的值与子查询返回的一列值中的每一个进行比较。只要有一次比较的结果为TRUE，则ANY测试返回TRUE。例4.30从customers表中检索数据，以查询一次订货数量大于等于20的客户编号和姓名。selecta.客户编号,a.姓名

fromcustomersawhere20<=any(select数量

fromorderswherea.客户编号=orders.客户编号)2.使用ALL运算符进行批量比较测试测试表达式比较运算符ALL(子查询)通过比较运算符将一个表达式的值与子查询返回的一列值中的每一个进行比较。只要有一次比较的结果为FALSE，则ALL测试返回FALSE。例4.31从goods表中检索数据，以查询在所有的货品中，价格最高的货品信息。select*fromgoods where价格>=all(select价格

fromgoods)添加数据使用INSERT语句向表中添加数据有两种方式：1、INSERT…VALUES，直接给各列赋值，一次只能添加一条记录；2、INSERT…SELECT，将select子句产生的结果集添加到表中，一次可添加多条记录。1、使用INSERT…VALUESinsert表名[(字段列表)]values(值列表)向表中所有字段赋值时，字段列表可省；对于指定了默认值的字段，若使用默认值可用default关键字；不能对标识列、计算列进行赋值；在向表中添加数据时不能违反完整性约束。例4.32向orders表添加一条新记录，给所有列提供列值。insertordersvalues('book',1,10,250,'1/23/2003')由于‘订货日期’设定了默认值，所有可以使用默认值insertordersvalues('book',1,10,250,default)字符型和日期型数据需要用单引号括起来例4.33创建一个与customers表结构一样的表cq，使该表中仅有重庆客户的信息。select*intocqfromcustomerswhere2=4insertcqselect*fromcustomerswhere地址like'重庆%'2、使用INSERT…SELECTinsert表名[(字段列表)]select字段列表from表名where条件表达式Select子句不能使用小括号括起来update目标表名set{列名=表达式}[,…n][from另一表名][where条件表达式]修改数据例4.34将goods表中的库存量大于100的货品的定价降低10%。updategoodsset价格=价格*0.9where库存量>=100例4.35把orders表中的“总金额”用该货品在goods表中的“价格”与在orders表中的订货“数量”的乘积代替，并显示修改后的记录。updateordersset总金额=价格*数量

fromgoodswhereorders.货品名称=goods.货品名称selecta.货品名称,a.价格,b.数量,b.总金额

fromgoodsasa,ordersasbwherea.货品名称=b.货品名称删除数据可以使用DELETE语句从表中删除满足指定条件的若干行记录；也可以使用TRUNCATETABLE语句从表中快速删除所有记录。删除数据2、使用TRUNCATE语句1、使用DELETE语句delete表名[from另一表名][where条件表达式]truncatetable表名快速删除方法，delete语句在删除每一行时都要记录日志，truncate则通过释放表数据页面的方法来删除表中数据，只记录数据页面的释放操作，并且删除的数据是不可恢复的，而delete还可以通过事务回滚来恢复删除的数据。例4.36删除goods表中的所有记录。deletegoods例4.37删除goods表中的未知定价的记录。deletegoodswhere价格isnull例4.38删除orders表中地址为重庆的客户订货记录。deleteordersfromcustomerswhereorders.客户编号=customers.客户编号

and地址='重庆'例4.39删除orders表中的所有订单。truncatetableorders使用企业管理器查询与更新记录

选择“打开表”的“查询”命令，显示“查询设计器”窗口：关系图窗格、网格窗格、SQL窗格和结果窗格更改查询类型：选择：SELECT……FROM

从中插入：INSERT……SELECT

插入到：INSERT……VALUES

更新：UPDATE

删除：DELETE

创建表：SELECT……INTO……FROM查询更新记录演示使用企业管理器查询与更新记录…总结使用select语句实现查询简单查询统计多表联合查询：交叉连接、