第5章 表的管理

第5章表的管理,1,本章内容,5.1SQLServer表概述5.2表的创建与维护5.3表中数据的维护,2,学习要点,基本知识设计表基本数据类型自定义数据类型表操作(SSMS方式和T-SQL方式)创建表、修改表查看表、删除表记录操作使用SSMS操作记录T-SQL插入、修改、删除记录,5.1SQLServer表概述,定义表的结构：DDL操纵表的数据：DML,在关系数据库中，每一个关系都体现为一张二维表，使用表来存储和操作数据的逻辑结构，表是数据库中最重要的数据对象。表（Table）是按照行（Row）和列（Column）的格式组织和存储数据的数据库对象。表的操作：,基本知识,设计表：确定需要什么样的表，各表中都应该包括哪些数据以及各个表之间的关系和存取权限等等，这个过程称之为设计表,学习要点,基本知识,设计表：需确定项目表中每一列的名称。表中每一列的数据类型和宽度。表中的列中是否允许空值。表中的列是否需要约束、默认设置或规则。表是否需要约束。表所需要的索引的类型和需要建立索引的列。表间的关系，即确定哪些列是主键，哪些是外键,学习要点,基本数据类型：SQLServer2005表中的每一列都必须指出该列可存储的数据类型以下为SQLServer2005常用的数据类型,数据类型简介,1.字符数据类型字符数据类型是使用最多的数据类型，可以用它来存储各种字母、数字符号、特殊符号。SQLServer2005支持的字符数据类型有char、varchar、text、Nchar、nvarchar、ntext等六种。前三种是非unicode字符数据，后三种是unicode字符数据。,5.1.1基本数据类型SQLServer2005支持整型、字符型、货币型和日期时间等多种基本数据类型。2.二进制数据类型SQLServer用binary、varbinary和image三种数据类型存储二进制数据。,3.日期和时间数据类型日期和时间数据类型用于存储日期和时间的结合体，SQLServer2005支持的日期时间数据类型有datetime、smalldatetime等。4.逻辑数据类型逻辑数据类型bit占用1个字节的存储空间，其值为0或1。如果输入0或1以外的值，将被视为1。,4.1.2基本数据类型5.整型数据类型整型数据类型是最常用的数据类型之一。SQLServer2005支持的整数类型有Int、smallint、bigint和tinyint四种。,6.浮点数据类型浮点数据类型用于存储十进制小数，SQLServer2005支持的浮点数据类型分为real、float、decimal和numeric等四种。,7.货币数据类型货币数据类型用于存储货币值，在使用货币数据类型时，应在数据前加上货币符号。SQLServer2005支持money和smallmoney两种。,8.其它数据类型SQLServer2005中包含了一些用于数据存储的特殊数据类型。,Timestamp：时间戳数据类型，用于自动记录插入或删除操作的时间。注意：服务器不允许用户指定时间戳值。sql_variant：可变数据类型。该类型的变量可用来存放大部分SQLServer数据类型的值，最大长度为8016字节，不支持text、ntext、timestamp和sql_variant类型。table：表类型。这是一种特殊的数据类型，存储供以后处理的结果集。uniqueidentifier：GUID类型(GlobalUniqueIDentifier,全局惟一标识符)。,自定义数据类型：用户定义数据类型是基于SQLServer2005中的系统基本数据类型的创建【例5-1】创建此类型时必须提供三个参数名称新数据类型所依据的系统数据类型为空性（数据类型是否允许空值）删除【例5-2】,学习要点创建、删除,创建自定义类型,基本语句格式【例5-1】创建用户自定义数据类型,USEStudentEXECsp_addtypebirthday,VARCHAR(14),NOTNULL,学习要点创建、删除,sp_addtype用户数据类型名,系统数据类型,为空性,基本语句格式【例5-2】删除用户定义的数据类型birthday,USEstudentEXECsp_droptypebirthday,sp_droptype用户数据类型名,注意：如果在表定义内使用某个用户定义的数据类型，或者将某个规则或默认值绑定到这种数据类型，那么就不能除去它,学习要点创建、删除,删除自定义类型,5.1SQLServer表概述,5.1.2空值和默认值空值(NULL)空值不同于空白(空字符串)或数值零，通常表示未填写、未知(Unknown)、不可用或将在以后添加的数据。默认值是指表中数据的默认取值，默认值对象是数据库的对象不依附于具体的表对象，即默认值对象的作用范围是整个数据库。,5.1SQLServer表概述,5.1.3约束约束定义了关于列中允许值的规则，SQLServer通过限制列中数据、行中数据和表之间数据来保证数据的完整性。非空值约束(NotNull)默认约束(Default)惟一性约束(Unique）主键约束(PrimaryKey，也称主关键字约束)外键约束(ForeignKey，也称为外部关键字约束),5.2表的创建与维护数据表是数据库中最重要的对象，是相关联的行列数据的集合，整个数据库中的数据都是物理存储在各个数据表中的。数据表的主要内容包括：1)表的名字，每个表都必须有一个名字。表名必须遵循SQLServer2005的命名规则，且最好能够使表名准确表达表格的内容。2)表中各列的名字和数据类型，包括基本数据类型及自定义数据类型。3)表的主码和外码信息。4)表中哪些列允许为空。5)表中哪些列需要索引。6)表中哪些列需要绑定约束对象、默认值对象或规则对象。,5.2表的创建与维护,使用SQLServer管理平台对表进行操作使用SQLServer管理平台创建和修改表使用SQLServer管理平台设计数据库关系在SQLServer管理平台中删除表使,21,表操作,创建表SSMS方式Step1:服务器组选中数据库右键新建表,学习要点step1、step2、step3,创建表SSMS方式Step2:输入列名、数据类型、长度和为空性等项目,学习要点step1、step2、step3,表操作,创建表SSMS方式Step3:点击窗口标题栏上的或工具栏上按钮输入表名,学习要点step1、step2、step3T-SQL方式,表操作,5.2表的创建与维护,使用Transact-SQL语句创建表语法格式：CREATETABLEdatabase_name.owner.|owner.table_name(|column_nameAScomputed_column_expression|:=CONSTRAINTconstraint_name|PRIMARYKEY|UNIQUE,.n)ONfilegroup|DEFAULTTEXTIMAGE_ONfilegroup|DEFAULT,创建表T-SQL方式基本语句格式如下：示例【例5-3】,CREATETABLE表名（列名数据类型列级完整性约束条件，列名数据类型列级完整性约束条件.，表级完整性约束条件）,5.2表的创建与维护,【例5-3】创建一个名为“information”的表,UseSalesGOCREATETABLEinformation(s_noCHAR(11)PRIMARYKEY,s_nameCHAR(8)notnull,s_sexCHAR(2),s_birthdatetime,s_addressVARCHAR(30),s_classCHAR(10),学习要点T-SQL方式,【例5-4为表指定文件组。,CREATETABLESrmation(s_noCHAR(11)PRIMARYKEY,s_nameCHAR(8)notnull,s_sexCHAR(2),s_birthdatetime,s_addressVARCHAR(30),s_classCHAR(10)ONPRIMARY,将该表放置在PRIMARY文件组中,【例5-5对计算列使用表达式。,CREATETABLESalarys(姓名varchar(10),基本工资money,奖金money,总计AS基本工资+奖金),该列为计算列，不能手动赋值或修改,【例5-6自动获取字段值。,CREATETABLEusers(编号identity(1,1)NOTNULL,用户代码varchar(10),登陆时间ASGetdate(),用户名ASUser_name(),利用函数自动获取值,表名前面加#表示：本地临时表表名前面加#表示：全局临时表,【例5-7创建临时表。,CREATETABLE#student(学号varchar(8)PRIMARYKEY,姓名varchar(10),性别varchar(2),班级varchar(10),修改表SSMS方式Step1:服务器组选中数据库选中表右键设计表,学习要点step1、step2,表操作,修改表SSMS方式Step2:点击窗口标题栏上的或工具栏上按钮保存,学习要点step1、step2T-SQL方式,表操作,修改表T-SQL方式基本语句格式如下：示例【例5-813】添加列、修改列、删除列添加、删除约束重命名表,ALTERTABLE表名ALTERCOLUMN列名新数据类型ADD新列名数据类型完整性约束DROP完整性约束名列名,学习要点step1、step2T-SQL方式,表操作,示例,添加列、修改列、删除列【例5-8】要在Information表中添加一个长为20个字符，名为S_major的类型为CHAR的列【例5-9】将Information表中的s_birth数据类型改为CHAR型，且宽度为10【例5-10】在Information表中删除列S_major,ALTERTABLEInformationADDS_majorCHAR（20）,ALTERTABLEInformationALTERCOLUMNs_birthCHAR(10),ALTERTABLEInformationDROPCOLUMNS_major,学习要点T-SQL方式,添加、删除约束【例5-11】为Information表中S_name列添加唯一性约束【例5-12】删除Information表中S_name的唯一性约束,ALTERTABLEInformationADDUNIQUE(S_name),ALTERTABLEInformationDROPUNIQUE(S_name),学习要点T-SQL方式,示例,重命名表：基本语句格式：sp_rename当前表名,新表名【例5-13】将表information改名为infosp_renameinformation,info,学习要点T-SQL方式,示例,表操作,查看表企业管理器方式方法：服务器组选中数据库选中表右键,学习要点T-SQL方式,查看表T-SQL方式基本语句：sp_help功能：sp_help系统存储过程查看数据库对象的结构示例：【例5-14】查看表information信息,USEstudentEXECsp_helpinformation,学习要点SSMS,表操作,删除表企业管理器方式Step1:服务器组选中数据库选中表右键,学习要点step1、step2,表操作,删除表企业管理器方式Step2,学习要点step1、step2T-SQL方式,表操作,删除表T-SQL方式基本语句：DROPTABLE注意：定义有外键约束的表必须先删除外键约束，才能删除。系统表不能使用DROPTABLE语句删除。表定义一旦删除，表中的数据、在此表上建立的索引都将自动被删除掉，而建立在此表上的视图虽仍然保留，但已无法引用。因此执行删除操作一定要格外小心,学习要点step1、step2T-SQL方式,表操作,例5-16删除当前数据库内的表。USESalesGODROPTABLEemployee本例从当前数据库Sales中删除employee表及其数据和索引。例5-17删除另外一个数据库内的表。DROPTABLESales.dbo.employee本例删除Sales数据库内的employee表。可以在任何数据库内执行此操作。,编辑记录企业管理器方式直接在下图中所示的表格中输入、修改和删除表中的记录。记录操作完成后，根据提示保存操作结果则完成表中记录的操作,学习要点SSMST-SQL方式,5.3表中数据的维护,1、插入数据T-SQL方式示例：插入所有列【例5-18】插入指定列【例5-19】,INSERTINTO表名（属性列1，属性列2.VALUES（常量1，常量2.）,学习要点SSMST-SQL方式,5.3表中数据的维护,INSERTINTO表名（属性列1，属性列2.SELECT语句,示例,插入所有列：【例5-18】将一个新学生记录插入到information表中（20031201001,王玉梅,女,1986-5-18,湖南株洲,电商031）,INSERTINTOInformationVALUES（20031201001,王玉梅,女,1986-5-18,湖南株洲,电商031）,学习要点插入记录,插入指定列：【例5-19】插入一个学生记录的指定字段(20021003010,刘奇,男),INSERTINTOInformation（S_no，S_name，S_sex）VALUES（20021003010,刘奇,男）,学习要点插入记录,示例,当插入值少于列的个数或插入值与列的顺序不同时，需要显式指定列名,INSERTINTOkaoping_table(id,name)SELECTst_id,st_nameFROMstudentGO,【例5-20】创建一个人员考评表kaoping_table，该表包括3个字段：编号id，数据类型为char(10)；姓名name，数据类型为varchar(10)；评价pingjia，数据类型为varchar(10)。将学生表student中的学号和姓名数据插入到考评表kaoping_table中。,CREATETABLEkaoping_table(idchar(10),namevarchar(10),pingjiavarchar(10)GO,插入查询的结果：,插入数据（续）,注意：INSERTVALUES语句一次只能向表中插入一条记录。当向表中所有列插入数据时，列名可以省略不写，但列值必须与表中定义的列名顺序一致。建议写出列名。当向表中插入数据的顺序与列顺序不同时，必须写列名。当向表中某些列插入数据，某些列不插入数据时，必须写列名。INSERT语句不能为计算列、标识列指定列值。,【例5-20】将数据装载到带有标识符的表,CREATETABLEcustomer(idbigintNOTNULLIDENTITY(0,1),namevarchar(10),addressvarchar(50)GOINSERTINTOcustomer(name,address)VALUES(张三,中山东路29号)GOSETIDENTITY_INSERTcustomerONINSERTINTOcustomer(id,name,address)VALUES(100,张三,中山东路29号),插入带有标识符的表：,允许标识列的手动插入,允许系统自动为新行生成标识值,5.3表中数据的维护,2、修改数据T-SQL方式示例：修改单条记录【例5-21】修改多条记录【例5-22】修改所有记录【例5-23】,UPDATE表名SET列名=表达式，列名=表达式.FROM表名WHERE条件；,学习要点修改记录,指定更新的数据来自一个或多个表或视图,示例,修改单条记录：【例5-21】学生“周天”的家由“广东广州”搬到“湖南株洲”，则通过以下语句对其基本信息进行更新,UPDATEInformationSETS_address=湖南株洲WHERES_name=周天,学习要点修改记录,修改多条记录：【例5-22】将班级“信息041”改为“信息042”，可以通过以下语句来实现。,UPDATEInformationSETS_class=信息042WHERES_class=信息041,学习要点修改记录,示例,修改所有记录：【例5-23】将班级为“20021001”课程号为“1003c”，的成绩统一设置为75,UPDATEScoreSETgrade=75WHEREleft(S_no,7)=20021001andC_no=1003c,学习要点修改记录,示例,使用来自另一个表的信息：【例5-24】从Sell_order表和Goods表中的数据计算每个销售订单的收费。,UPDATESell_orderSETcost=Sell_order.order_Num*Goods.priceFROMSell_order,GoodsWHERESell_order.goods_id=Goods.goods_id,学习要点修改记录,示例,5.3表中数据的维护,删除数据T-SQL方式DELETE语句可删除表或视图中的一行或多行，每一行的删除都将被记入日志。示例：删除指定记录【例5-25】删除所有记录【例5-26】,DELETEFROM表名WHERE条件,学习要点删除记录,删除指定记录：【例5-25】假设学号为20031001001的学生中途因故辍学，则需要在学生基本信息表中删除该记录,DELETEFROMInformationWHERES_no=20031001001,学习要点删除记录,示例,删除所有记录：【例5-26】删除所有的学生课程成绩记录,DELETEFROMScore,学习要点删除记录,示例,删除所有记录：,快速清空表，而不会把每一行的删除操作都记入日志。TRUNCATETABLE表名注意：比较TRUNCATETABLE、DROPTABLE、DELETE的区别,学习要点删除记录,示例,课堂练习,【练习5-1】在student数据库中创建别名数据类型name，基于系统数据类型varchar(10)，允许空值。,USEstudentGOCREATETYPEnameFROMvarchar(10)NULLGO,【练习5-2】创建班级表class，表结构参见下表，不包括约束信息。,USEstudentGOCREATETABLEteacher(th_idchar(6)PRIMARYKEY,th_namevarchar(10)notnull,th_genderchar(2)nullCHECK(th_gender=男orth_gender=女),th_positionchar(6)null,th_typechar(4)nullDEFAULT专职)GO,课堂练习,【例5-3】修改学生表student，向表中增加2列。增加“民族”列，列名为st_nation，数据类型为char(2)，允许空值；增加“籍贯”列，列名为st_native，数据类型为char(6)，允许空值。,【例5-4】向学生表student的部分列中插入一条记录。学号：2005010102，姓名：张意，出生日期：1989-12-1，性别：男，班号：01。,USEstudentGOINSERTINTOstudent(st_id,st_name,st_birth,st_gender,st_clid)VALUES(2005010102,张意,1989-12-1,男,01)GO,USEstudentGOCREATETABLEkaoping_table(