SQL-Server-2005复习资料-(3).ppt

第4章数据库管理本章导读本章主要介绍了有关数据表的操作 包括表的创建 修改 删除和建立索引等操作以及表中数据维护的有关操作和方法 基本掌握使用SQLServerManagementStudio和T SQL语句对表的操作和表的数据操作 包括创建表 删除表 对表中字段建立索引 向表中增加数据 修改数据 删除数据等 读者要对SQLServer2005中的数据类型有一个清楚的认识 4 1数据库的创建与管理 4 1 1SQLServer系统数据库在第一次安装SQLServer时 安装了数据库示例 将默认安装系统数据库 数据库快照和示例数据库 示例数据库包含Northwind数据库 Pubs数据库 AdventureWorks数据库和AdventureWorksDW数据库 1 系统数据库系统数据库中包含以下6个数据库 1 Master数据库Master数据库是SQLServer系统最重要的数据库 它包含用户登录信息 系统配置设置信息 服务器配置信息 SQLServer的初始化信息 其他系统数据库及用户数据库的相关文件信息 2 Model数据库Model数据库为所有用户数据库提供数据库模板 它含有Master数据库所有系统表的子集 该系统数据库是每个用户定义数据库所需要的 当创建用户数据库时 系统自动把该模板数据库的所有信息复制到新建的数据库中 Model数据库是Tempdb数据库的基础 对Model数据库的任何改动都将反映在Tempdb数据库中 3 Msdb数据库Msdb数据库是代理服务器数据库 该系统数据库记录有关作业 警报 操作员和调度等信息 是SQLAgent处理存储系统任务的地方 如果在数据库上安放一个夜间运行的备份日程表 那么在Msdb上就会存在一个对应表项 4 Tempdb数据库Tempdb数据库是一个连接到SQLServer实例的所有用户都可用的全局资源的临时数据库 它为所有的临时表 临时存储过程及其他临时操作提供存储空间 5 Resource数据库Resource数据库是一个资源数据库 它包含了SQLServer2005包括的所有系统对象 6 Distribution数据库Distribution数据库是一个分发数据库 在利用数据库复制技术实现数据同步更新时要进行配置出版服务器 它是在完成了出版服务器的设置 并且在系统为该服务器的数形结构中添加一个复制监视器后生成的 2 数据库快照数据库快照是SQLServer2005企业版中新增的功能 数据库快照是源数据库的只读 静态视图 多个快照可以位于一个源数据库中 并且可以作为数据库始终驻留在同一服务器实例上 创建快照时 每个数据库快照在事务上与源数据库一致 在被数据库所有者显式删除之前 快照始终存在 4 1数据库的创建与管理 4 1 2数据库的创建 1 准备创建数据库 1 确定数据库的名称 所有者 创建数据库的用户 数据库名称 studentcourse2 确定存储该数据库的数据文件的大小及文件空间增长方式 确定关系 索引 及系统存储参数的配置 确定数据库的存取方法 主数据文件 逻辑名称studentcourse 物理文件名 C Data studentcourse mdf 初始大小 3MB 最大空间 UNLIMITED 空间增加量 1MB 属于文件组primary 次数据文件 逻辑名称secondsc 物理文件名 C mydb secondsc ndf 初始大小 3MB 最大空间 50MB 空间增加量 1MB 属于文件组group1 日志文件 逻辑名称studentcourse log 物理文件名 C Log studentcourse log ldf 始初大小 1MB 最大空间 20MB 空间增加量 10 索引 每一数据表关于主关键字建立索引文件 2 使用SQLServerManagementStudio创建数据库 3 1关系数据库设计思路 图4 1 创建数据库 界面 图4 2 新建数据库 对话框 3 使用Transact SQL语言创建数据库 1 命令格式CREATEDATABASE ON PRIMARY N N LOGON N 其中 N 表示设置文件属性 格式如下 NAME 逻辑文件名 FILENAME 物理文件名称 SIZE 数据库文件的初始容量值 MAXSIZE 物理文件的最大容量值 UNLIMITED FILEGROWTH 增加容量值 N 其中 N 表示设置文件组属性 格式如下 FILEGROUP文件组名称 DEFAULT N 2 参数说明 1 放在 中的 表示整个 括起来的选项都可省略 如果不省 则 括起的选项不能省 使用 分隔的多个选项 表示只能选择其中一个 2 数据库的名称必须符合标识符规则 最长为128个字符 数据库名称在SQLServer的实例中必须唯一 数据库的逻辑文件名是数据库在SQLServer中的标识符 FILENAME指定数据库物理文件名称和路径 它和数据库逻辑名称一一对应 文件组的逻辑名称必须在数据库中唯一 不能是系统提供的名称PRIMARY和PRIMARY LOG 3 ON 定义数据文件 PRIMARY 定义主文件组中的文件 LOGON 定义日志文件 一个数据库只能有一个主文件 如果没有定义主文件 列在数据文件项的第一个文件就是主文件 4 数据库文件容量单位可以是KB MB GB TB 缺省值为MB 长度必须为整数 主文件的 最小容量是Model数据库的主文件长度 对于其他类型文件 最小长度为512KB 5 MAXSIZE 指定物理文件的最大容量 如果不设置文件的最大尺寸 那么文件的增长最大值将是磁盘的所有空间 UNLIMITED选项允许文件增长到磁盘已满 6 FILEGROWTH 指定文件每次增加容量的大小或百分比 当FILEGROWTH 0时 表示文件不增长 7 DEFAULT 指定命名文件组为数据库中的默认文件组 3 使用Transact SQL语言创建数据库 图4 11用命令创建Studentcourse数据库界面 CREATEDATABASEstudentcourse 逻辑数据名称 studentcourseONPRIMARY ON子句指出文件属于PRIMARY组 NAME studentcourse NAME指出了对应的逻辑文件名FILENAME C DATA studentcourse mdf FILENAME指出物理文件名SIZE 3072KB Size指出了初始分配空间MAXSIZE UNLIMITED MAXSIZE指出了最大空间FILEGROWTH 1024KB FILEGROWTH指出了文件空间的增长量FILEGROUP group1 FILEGROUP子句指出次数据文件 NDF NAME N secondsc FILENAME N C mydb secondsc ndf SIZE 3072KB MAXSIZE 51200KB FILEGROWTH 1024KB LOGON LOGON子句指出新建数据库的日志文件 LDF NAME N studentcourse log FILENAME C LOG studentcourse log ldf SIZE 1024KB MAXSIZE 20480KB FILEGROWTH 10 3 创建学生选课数据库 4 1 3管理数据库 1 查看数据库信息 1 命令格式EXECsp helpdb 数据库名 2 功能查看指定数据库的相关数据文件信息 数据库拥有者 创建时间等信息 若缺省数据库名 则显示所有数据库信息 例4 1 查看学生选课 studentcourse 数据库的信息 方法一 使用SQLServerManagementStudio查看数据库信息方法二 使用系统存储过程命令查看数据库信息 EXECsp helpdbstudentcourse 例4 2 查看所有数据库信息 EXECsp helpdb 4 1 3管理数据库 2 打开数据库 1 命令格式USE2 功能使指定数据库成为当前数据库 例4 3 打开学生选课 studentcourse 数据库 方法一 使用SQLServerManagementStudio打开数据库方法二 使用命令 USEstudentcourse 3 修改数据库 1 命令格式AlterDatabase数据库名 AddFile N ToFilegroup文件组名称 AddLogFile N RemoveFile逻辑文件名称 WithDelete ModifyFile ModifyName 新数据库名称 AddFilegroup新增文件组名称 RemoveFilegroup文件组名称 ModifyFilegroup原文件组名称 文件组属性 Name 新文件组名称 2 功能AddFile 向数据库添加文件 AddLogfile 向数据库添加日志文件 RemoveFile 从数据库中删除文件 ModifyFile 对文件进行修改 包括SIZE FILEGROWTH和MAXSIZE 每次只能对一个属性进行修改 ModifyName 重新命名数据库 Add Remove ModifyFilegroup 向数据库中添加删除修改文件组 例4 4 向数据库Studentcourse中添加一个名为group2的文件组 并在该文件组中添加一个名为Studentcourse2 路径为默认的次数据文件 初始值大小为2MB 最大值为50MB 文件以1MB增长 再添加一个名为Studentcourse Log2的日志文件 初始值大小为1MB 最大值为100MB 文件以10 增长 然后对数据库中Studentcourse2文件重命名为Studentcourse 2 最后把该文件从数据库中移除 方法一 使用SQLServerManagementStudio方法二 使用SQL语言修改数据库 命令如下所示 alterDATABASEstudentcourseaddfilegroupgroup2 新增group2文件组GO 3 修改数据库 4 1 3管理数据库 4 1 3管理数据库 alterDATABASEstudentcourseaddfile NAME studentcourse2 新增studentcourse2次数据文件FILENAME C ProgramFiles MicrosoftSQLServer MSSQL 1 MSSQL DATA studentcourse2 ndf SIZE 2MB MAXSIZE 50MB FILEGROWTH 1MB tofilegroupgroup2GoalterDATABASEstudentcoursemodifyfile 修改studentcourse主数据文件的文件增长为2MB NAME studentcourse FILEGROWTH 2MB GoalterDATABASEstudentcourseaddLOGfile 新增studentcourse log2日志文件 NAME N studentcourse log2 FILENAME C ProgramFiles MicrosoftSQLServer MSSQL 1 MSSQL DATA studentcourse log2 ldf SIZE 1MB MAXSIZE 100MB FILEGROWTH 10 GoalterDATABASEstudentcoursemodifyfile name studentcourse2 newname studentcourse 2 对数据库中studentcourse2次数据文件重命名成studentcourse 2GoalterDATABASEstudentcourseremovefilestudentcourse 2 从数据库中移除studentcourse 2次要数据文件 4 删除数据库1 命令格式DROPDATABASE N 2 功能其功能是删除指定数据库 1 命令格式DBCCSHRINKDATABASE 数据库名 Target Percent Notruncate Truncateonly 2 功能选择Notruncate选项可以产生由数据库所保留的自由空间 而且这些空间不被操作系统使用 而Truncateonly选项的作用与其相反 它将自由空间留给操作系统 如果只需要压缩单一的一个文件 则使用DBCCSHRINKFILE 命令 要注意一个数据库不能被压缩到小于模板数据库的大小 例4 6 压缩学生选课studentcourse数据库 使其最大可用空间为30 方法一 使用SQLServerManagementStudio压缩数据库方法二 使用SQL命令压缩数据库DBCCSHRINKDATABASE Studentcourse 30 4 1 3管理数据库 5 压缩数据库 4 1 3管理数据库 例4 7 压缩学生选课数据库studentcourse中的一个secondsc次数据文件 将其压缩为2MB 方法一 使用SQLServerManagementStudio方法二 使用SQL命令 DBCCSHRINKFILE secondsc 2 5 压缩数据库 4 2 1数据系统视图 1 Sysobjects系统视图2 Syscolumns系统视图3 Sysindexes系统视图4 Sysusers系统视图5 Sysdatabases系统视图6 Sysdepends系统视图7 Sysconstraints系统视图 4 2数据表的创建 SQLServer2005除了用户定义的数据表以外 还提供了系统表 它记录了服务器配置及数据存储信息 但在SQLServer2005中用户无法直接查询或更新系统表 SQLServer2005将系统数据存储在隐藏 资源 表中 只有通过具有权限的管理员连接 方可调用和查看 低级用户必须使用系统视图 从隐藏表和隐藏函数中获得系统信息 数据类型及其确定原则决定要使用的数据类型时 请参照以下原则 1 为列选择一个合适的长度 2 如果属性值的长度不会大幅改变 就使用固定长度数据类型 Char和Nchar 3 如果用户存储的字符串来源于不同的国家 就使用Unicode数据类型 Unicode比Char支持更大范围的字符集 SQLServer的九大类数据类型1 整型数据类型 Integer 2 精确数字数据类型 ExactNumeric 3 近似数字数据类型 ApproximateNumeric 4 货币数据类型 Monetary 5 日期和时间数据类型 DateTime 6 字符数据类型 Character 7 二进制数据类型8 特殊数据类型9 自定义数据类型 UDT EXEC 4 2 2数据类型 例4 10 建一个以Datetime为基础的出生日期 Birthday 可为空的数据类型 方法一 使用SQLServerManagementStudio方法二 使用SQL命令 EXECSP ADDTYPEBirthday Datetime Null 4 2 2数据类型 4 2 3创建数据表结构 1 熟悉CREATETABLE语句的格式1 CREATETABLE语句的简化格式CREATETABLE 服务器名 数据库名 架构名 数据库名 架构名 架构名 数据库表名 列名 数据类型 NOTNULL identity 初值 步长 DEFAULT默认值 UNIQUE PRIMARYKEY CLUSTERED NONCLUSTERED 列名 数据类型 NOTNULL DEFAULT默认值 UNIQUE n 列名AS计算列值的表达式 n CONSTRAINT主键约束名 PRIMARYKEY 属性名 CONSTRAINT检查约束名 CHECK 逻辑表达式 n FOREIGNKEY 外键属性 REFERENCES参照表 参照属性 n ON 文件组 默认文件组 2 CREATETABLE语句的说明 例4 11 创建数据库 book shop 的数据表book 数据表由书号 书名 出版社 出版日期 单价 数量 总价 单价 数量 电子邮件地址和数据库表使用者字段组成 其中书号列定义为主键并且为系统自动编号即标识列 种子值 起始值 为1000 增量为1 要求出版社字段的值只能是高教 浙大 电子和中央四个之一 电子邮件地址字段中必须包含 符号 单价必须大于0 数量必须大于等于0 出版日期的默认值设置为当前日期函数 1 标识IDENTITY属性定义列时 可以为一个表中的其中一列指定一个特殊的自动增长标识属性 每一个表只能有一个标识属性 标识属性有两个参数 标识初始值和增量 标识属性的数据类型只能是Bigint Int Smallint Tinyint Decimal和Numeric 如果对Decimal或Numeric数据类型设置标识属性 必须把它们定义为有0个小数位 标识属性不能是空值 系统自动根据种子值和增量更新标识列值 例如 设置book表中属性 书号 为标识列 CREATETABLEbook 书号Intidentity 1000 1 NOTFORREPLICATION 2 计算所得的列我们还可以创建一种称为计算所得的列的特殊列 它包含一个涉及表中一个或多个其他列的计算公式 在默认情况下 计算所得的列包含计算公式的定义 但在物理上不存储数据 返回数据时 应用该计算公式以返回一个结果值 然而 通过使用PERSISTED关键字 可以强制一个计算所得的列在物理上存储数据 该关键字使公式计算在插入或修改行时发生 然后将计算结果存储在表中 例如 设置book表中属性 总价 为计算所得的列 CREATETABLEbook 总价AS单价 数量 数据库表使用者ASUSER NAME 3 空值NULL约束例如 设置book表中属性 书名 不允许为空值 CREATETABLEbook 书名Char 20 NOTNULL 4 PRIMARYKEY约束CREATETABLEbook 书号IntPRIMARYKEYCLUSTERED 或CREATETABLEbook 书号Int CONSTRAINTPK snoPRIMARYKEYCLUSTERED 或CREATETABLEbook 书号Int CONSTRAINTPK snoPRIMARYKEYCLUSTERED 书号 5 UNIQUE约束UNIQUE约束相应属性列的取值必须唯一 允许存在空值 例如 对book表中属性 书名 建立一个唯一约束 CREATETABLEbook 书名Char 20 UNIQUE 或CREATETABLEbook 书名Char 20 CONSTRAINTU snUNIQUE 书名 6 DEFAULT约束每个属性只能有一个默认值 默认值可以包含常量值 函数或NULL 但不能引用表中的其他列 数据类型为Timestamp或具有IDENTITY属性的列上不能定义默认值 使用INSERT和UPDATE语句时 系统会自动提供默认值 例如 设置book表中属性 出版日期 的默认值为当前日期 CREATETABLEbook 出版日期DatetimeDEFAULT getdate 7 CHECK约束每个属性列可以有多个CHECK约束 约束条件是逻辑表达式 不能引用其他表 列级CHECK约束只能引用被约束的列 表级CHECK约束只能应用同一表中的列 当执行INSERT和DELETE语句时 将检查数据是否满足约束 例如 约束book表中属性 出版社 只能取值为高教 浙大 电子和中央四个出版社之一 电子邮件地址字段中必须包含 符号 单价必须大于0 数量必须大于等于0 CREATETABLEbook 出版社char 20 单价SmallintCHECK 单价 0 数量SmallintCHECK 数量 0 电子邮件地址varchar 25 check 电子邮件地址like CHECK 出版社IN 高教 浙大 电子 中央 8 FOREIGNKEY约束受FOREIGNKEY约束的属性的列中只能输入NULL值或者在被引用的属性列中存在的值 CREATETABLESC 课程号char 3 FOREIGNKEY 课程号 REFERENCESC 课程号 或CREATETABLESC 课程号char 3 CONSTRAINT课程号 FKFOREIGNKEY 课程号 REFERENCESC 课程号 方法一 使用SQLServerManagementStudio方法二 使用命令 EXECsp helpdbstudentcourseCREATEdatabasebookshopGoCREATETABLEbook 书号intidentity 1000 1 NOTFORREPLICATIONPRIMARYKEYCLUSTERED 书名char 20 notnull 出版社char 20 出版日期datetimeDEFAULT getdate 单价smallintcheck 单价 0 数量smallintcheck 数量 0 总价as单价 数量 电子邮件地址varchar 25 check 电子邮件地址like check 出版社in 高教 浙大 电子 中央 2 CREATETABLE语句的说明 例4 12 创建数据库学生选课 studentcourse 的数据表S C SC 数据表结构如表3 6 表3 7 表3 8所示各表的完整性约束如表3 9 表3 10 表3 11所示 方法一 使用SQLServerManagementStudio创建数据表方法二 使用SQL命令 1 创建课程表C的语句如下 CREATETABLEC 课程号Char 3 NOTNULL 课程名Varchar 20 NOTNULL 学分SmallintNULL 预选课程号Char 3 NULL 教师Char 8 NULL CONSTRAINTFK PcnoFOREIGNKEY 预选课程号 REFERENCESC 课程号 CONSTRAINTCK CnoCHECK 课程号Like 0 9 0 9 CONSTRAINTPK CPRIMARYKEYCLUSTERED 课程号ASC ON PRIMARY 2 CREATETABLE语句的说明 例4 12 创建数据库学生选课 studentcourse 的数据表S C SC 数据表结构如表3 6 表3 7 表3 8所示方法二 使用SQL命令 2 创建学生基本信息表S的语句如下 CREATETABLES 学号Char 6 NOTNULLDEFAULT J0400 姓名Char 8 NOTNULL 性别Char 2 NOTNULL 出生日期DatetimeNOTNULLDEFAULT 19800101 系Varchar 20 NOTNULL 电话Char 8 NULL CHECK 学号Like A Z 0 9 0 9 0 9 0 9 CHECK 性别 女 OR性别 男 CHECK 电话Like 0 9 0 9 0 9 0 9 0 9 0 9 0 9 CONSTRAINTPK SPRIMARYKEYCLUSTERED 学号ASC ON PRIMARY 2 CREATETABLE语句的说明 例4 12 创建数据库学生选课 studentcourse 的数据表S C SC 数据表结构如表3 6 表3 7 表3 8所示各表的完整性约束如表3 9 表3 10 表3 11所示 方法二 使用SQL命令 3 创建学生选课数据表SC的语句如下 CREATETABLESC 学号Char 6 NOTNULL 课程号Char 3 NOTNULL 成绩SmallintNULL FOREIGNKEY 课程号 REFERENCESC 课程号 FOREIGNKEY 学号 REFERENCESS 学号 CHECK 成绩 0 AND成绩 100 PRIMARYKEYCLUSTERED 学号ASC 课程号ASC 2 CREATETABLE语句的说明 例4 13 返回有关所有对象的信息 USEMaster GOEXECSp Help GO 例4 14 返回学生选课 studentcourse 中学生表的信息 USEStudentcourseGOEXECSp Helps 4 2 5查看数据表 1 修改表的结构数据表创建以后 经常会需要对原先的某些定义进行一定的修改 例如添加 修改 删除列以及各种约束 修改列属性值 1 命令格式ALTERTABLE ALTERCOLUMN类型 宽度 NULL NOTNULL ADD类型 宽度 NULL NOTNULL 完整性约束 n DROPCOLUMN CASCADE RESTRICT n DROP CONSTRAINT ALL n 2 功能ALTERTABLE 将要修改的当前数据库中的指定数据表的表名 ALTERCOLUMN 修改当前数据库中的指定数据表的指定属性 ADD 向当前数据库中的指定数据表增加指定属性或列级完整性约束 DROPCOLUMN 删除当前数据库中的指定数据表中的指定属性 DROP 删除当前数据库中的指定数据表中的指定列级完整性约束 4 2 6修改数据表 例4 15 修改当前数据库 studentcourse 中S表的系属性改成char 25 增加一个入学时间字段 它的数据类型为datetime 并设置默认值为getdate 最后删除入学时间字段 方法二 使用命令 ALTERTABLEsALTERCOLUMN系char 25 GoALTERTABLEsADD入学时间datetimeGoALTERTABLEsADDCONSTRAINTDF sjDEFAULT getdate for入学时间或增加入学时间字段与默认值进行一次性设置 操作如下 ALTERTABLEsADD入学时间datetimeCONSTRAINTDF sjDEFAULT getdate ALTERTABLEsdropDF sjALTERTABLEsdropcolumn入学时间注意 要删除字段 必须先删除该字段的所有约束 4 2 6修改数据表 2 修改表的名称 1 命令格式Sp rename 2 功能重命名当前数据库中的指定数据表名 注意 当重命名表时 表名在包含该表的各数据库关系图中自动更新 当保存表或关系图时 表名在数据库中被更新 在重命名表之前需慎重考虑 如果现有查询 视图 用户定义函数 存储过程或程序引用该表 则更改表名将使这些对象无效 例4 16 重命名数据库bookshop的数据表book名称 改为 书籍资料 方法一 使用SQLServerManagementStudio方法二 使用SQL命令 Sp rename book 书籍资料 4 2 6修改数据表 1 命令格式DROPTABLE表名2 功能删除表 例4 17 删除当前数据库中的表S 方法一 使用SQLServerManagementStudio方法二 使用SQL命令 DROPTABLES 例4 18 删除其他数据库中的表 DROPTABLEStudentcourse dbo s注意 基本表定义一旦删除 表中的数据和在此表上建立的索引将自动被删除掉 DELETE命令用于从一个表中删除行 而要删除整个表 应使用DROPTABLE命令 要执行该命令 执行者必须是Sysadmin固定服务器角色的成员 数据库所有者固定数据库角色的成员或者是该表的所有者 4 2 7删除数据表 4 3 1使用SQLServerManagementStudio插入 修改与删除数据 4 3数据库表的操作 4 3 2使用T SQL语句进行插入 修改和删除数据 1 插入数据 2 更新数据 3 删除记录 插入数据记录的方法有三种 第一种是利用SQLServerManagementStudio 第二种是使用SELECT查询语句 本节暂不介绍 第三种是使用INSERT命令 1 命令格式INSERTINTO数据表名 列名表 VALUES 元组值 INSERTINTO数据表名 列名表 SELECT查询语句INSERTINTO数据表名 列名表 DEFAULTVALUES2 功能向指定数据表的属性列插入数据 VALUES后跟的元组值为属性列提供数据 其中列名表中的属性排列顺序和VALUES后跟的元组值的排列顺序要一致 对应的数据类型要一致 如果没有指定列名表 则表示数据表中的所有属性列 如果在列名表中没有列出所有的属性列 对于未指定的列 如果该列有默认值 则以默认值填充 如果允许空值NULL 则向该列插入空值NULL 如果具有IDENTITY属性 则插入下一个增量值 如果具有Timestamp数据类型 则获取当前的时间戳值 DEFAULTVALUES 选项会将默认值插入到该属性列中 如果某列没有默认值 允许则向该列插入空值NULL 如果某列不允许空值也没有默认值 则会出错 1 插入数据 例4 19 以下示例使用属性列显式指定插入到每个列的值 方法一 使用SQLServerManagementStudio方法二 使用SQL命令 USEStudentcourseGOINSERTINTOs 学号 姓名 性别 出生日期 系 VALUES L0401 张云龙 男 1987 11 11 路桥系 GoSELECT FROMs 1 插入数据 例4 20 将查询结果插入数据表 如将学号 L0401 成绩80以及课程表中所有课程号插入到sc中 方法一 使用SQLServerManagementStudio方法二 使用SQL命令 USEStudentcourseGOINSERTINTOscSELECT J0401 课程号 80FROMcGoSELECT FROMsc 1 插入数据 1 命令格式UPDATE基本表名SET列名 值表达式 列名 值表达式 WHERE条件表达式 2 功能更新指定基本表 满足WHERE子句条件的记录的指定属性值 其中值表达式可以是常量 变量 表达式 若缺省WHERE 则修改表中的所有元组 但在进行修改操作时 需注意数据库的一致性 使用UPDATE可以一次修改多列的值 这样可以提高效率 例4 21 使用简单UPDATE语句更新s表中的所有行出生日期列中的值变为原出生日期值加1 方法一 使用SQLServerManagementStudio方法二 使用SQL命令USEStudentcourseGOUPDATEsSET出生日期 出生日期 1 2 更新数据 例4 22 带WHERE子句使用UPDATE语句 将选C01课程的学号是L0401的学生的成绩改成85分 USEStudentcourseGOUPDATEscSET成绩 85WHERE课程号 C01 AND学号 L0401 GOSELECT FROMscGO 例4 23 带子查询的UPDATE语句将张云龙学生的成绩减少5分 USEStudentcourseGOUPDATEscSET成绩 成绩 5WHERE学号IN SELECT学号FROMsWHERE姓名 张云龙 GOSELECT FROMsc 2 更新数据 SQL的删除操作是指从基本表或视图中删除一条或多条元组记录 1 命令格式DELETEFROM基本表名 WHERE条件表达式 2 功能从指定表中删除满足WHERE子句条件的所有元组 若缺省WHERE 则删除表中的全部元组 DELETE语句删除的是表中的数据 而不是表的定义或表的结构 例4 24 删除学号为L0401的学生选课信息 DELETEFROMscWhere学号 j0401 GOSELECT FROMsc 例4 25 从SC表中删除所有行DELETEFROMscGOSELECT FROMsc 3 删除记录 4 删除所有行 1 命令格式TRUNCATETABLE 数据库名 架构名 架构名 表名 2 功能使用TRUNCATETABLE命令删除所有行 例4 26 下面的示例使用TRUNCATE命令从学生选课数据表SC中删除所有行 TRUNCATETABLEstudentcourse DBO sc注 TRUNCATE命令删除表中的所有行 而不记录单个行删除操作 TRUNCATETABLE在功能上与没有WHERE子句的DELETE语句相同 但是 TRUNCATETABLE速度更快 使用的系统资源和事务日志资源更少 1 索引的作用1 加速数据检索2 优化查询3 强制数据完整性 4 4索引管理 4 4 1索引概述索引是数据库中一种特殊的对象 它是对数据表中一个或多个字段的值进行排序而创建的一种存储结构 主要用于提高表中数据的查询速度 数据库中的索引与书籍中的目录类似 在一本书中 利用目录可以快速查找所需要的信息 而无须阅读整本书 在数据库中 可以利用索引快速查找需要的数据 而无须对整个表进行扫描 4 4 1索引概述 索引是SQLServer使用的内部表结构 它是基于表中的一个或多个列的值 提供对表中行的快速存取 2 索引的分类SQLSERVER2005支持在表中任何列上定义索引 按索引的组织方式 可以分为两类 聚集索引和非聚集索引 1 聚集索引在聚集索引中 表中各记录的物理顺序与索引的逻辑顺序相同 只有在表中建立了一个聚集索引后 数据才会按照索引键值的顺序存储到表中 由于一个表中的数据只能按照一种顺序存储 所以在表中只能建立一个聚集索引 通常在主键上创建聚集索引 2 非聚集索引非聚集索引是完全独立于数据行的结构 表中的数据行不按非聚集索引的顺序排序和存储 在非聚集索引内 从索引行指向数据行的指针称为行定位器 在检索数据时 SQLServer先在非聚集索引上搜索 找到相关信息后 再利用行定位器 找到数据表中的数据行 一个表上可以建立多个非聚集索引 3 唯一索引唯一索引要求所有数据行中任意两行中的被索引列不能存在重复值 1 命令格式CREATE UNIQUE CLUSTERED NOCLUSTERED INDEX索引名ON数据表名 视图名 字段名表 ASC DESC n WITH PAD INDEX FILLFACTOR 填充因子 IGNORE DUP KEY STATISTICS NORECOMPUTE ON文件组名 2 功能见P89 4 4 2创建索引 注意 索引可以加快数据检索