第章数据表的管理一.ppt

1 1第5章 数据表的管理2 2数据库表管理数据类型数据表结构的创建数据表结构的修改与删除数据表内容的增加与修改数据表的索引3 3一、数据类型系统数据类型用户自定义数据类型4 41.系统数据类型二进制数据：Binary、Varbinary和Image字符数据：Char、Varchar和TextUnicode数据：Nchar，Nvarchar和Ntext日期和时间数据：Datetime和Smalldatetime数字数据：精确数字、近似数字5 5精确数字整数：bigint、int、smallint、tinyint、bit小数：decimal和numeric货币：money和smallmoney近似数字float、real6 62.用户定义的数据类型用户定义的数据类型基于在MicrosoftSQLServer中提供的数据类型。当几个表中必须存储同一种数据类型时，并且为保证这些列有相同的数据类型、长度和可空性时，可以使用用户定义的数据类型。7 7（1）用户自定义类型的定义利用系统存储过程sp_addtype其语法形式如下：execsp_addtypetype,system_data_bype,null_type其中，type是用户定义的数据类型的名称。system_data_type是系统提供的数据类型，例如Decimal、Int、Char等。null_type表示该数据类型是如何处理空值的，必须使用单引号引起来，如NULL、NOTNULL等。8 8例1为数据库teachingDataA创建一个用户定义的数据类型Sname，该数据类型为基于系统数据类型变长为8的字符，且不允许为空。Execsp_addtypeSname,Varchar(8),NotNull9 9CREATETYPE定义数据类型其语法方式如下：CREATETYPEtype_nameFROMbase_type(precision,scale)NULL|NOTNULL其中，typename：用户自定义的数据类型的名称。base_type：表示建立的数据类型所基于的由SQLServer提供的数据类型。1010例2为数据库teachingDataB自定义名为myDept（工作部门）的数据类型为varchar(20)，允许为空。方法一（命令方式）CREATETYPEmyDeptFROMvarchar(20)方法二（对象资源管理器）1111（2）用户自定义类型的删除利用系统存储过程sp_droptype其语法形式如下：execsp_droptypetype利用对象资料管理器1212二、表结构的创建使用菜单命令创建使用命令方式创建数据表的导入1313（1）使用菜单命令创建利用“对象资源管理器”1414列属性选择不同的数据类型会有不同的列属性，常用的列属性说明如下：【说明】是用来说明该字段的含义。【默认值或绑定】在表中输入新的记录值时，如果没有给该字段赋值，则默认值就是该字段的值。【精度】是用来说明该字段数据类型的宽度，即位数。【小数位数】是用来说明该字段数据类型的小数位数。【是标识】表示该字段是表中的一个标识列，即新增的字段值为等差数列，其类型必须为数据值数据，有此属性的字段会自动产生一个值，无需用户输入。【标识种子】等差数列的第一个数字。【标识增量】等差数列的公差。1515例3在teachDataA中新建一数据表tchInfo，其表结构如下图所示：1616常用的设置关键字的设置右击要设置为关键字的字段左侧（如果要设置一组关键字，则可以用Ctrl选中多个字段），选择“设置关键字”。自动编号的设置-必须是int类型的数据在列属性中设置“标识规范”（“是标识”、“标识种子”、“标识增量”）默认值的设置在列属性中设置“默认值或绑定”1717数据取值范围的约束右击相应的字段选择“CHECK约束”设置为惟一索引右击相应的字段选择“索引/键”1818例4小黄在建立了数据表TchInfo以后，为了输入数据方便起见，要为其中的性别一栏即字段“sex”设置一个默认值“男”，并且，考虑到信息输入的正确性，他希望字段“sex”的值要么为“男”，要么为“女”，不允许有其它值。问：他应该如何操作？1919步骤1：在MicrosoftSQLServerManagementStudio的【对象资源管理器】中展开数据库teachingData，右击表dbo.TchInfo，以弹出的快捷菜单中选择【修改】命令，设置字段“Sex”的列属性【默认值或绑定】为：男，如图所示。2020步骤2右击列名“Sex”，在弹出的快捷菜单中选择【CHECK约束】命令，在【CHECK约束】对话框中单【添加】按钮，然后输入表达式：sex=男orsex=女，如下图所示。2121问题：小黄为了输入方便，希望在输入过程中教师编号问题：小黄为了输入方便，希望在输入过程中教师编号TIDTID的起始数据库为的起始数据库为1000000110000001，以后每输入一个教师，以后每输入一个教师，其编号其编号TIDTID自动加自动加1 1，因此他根据所学知识，在，因此他根据所学知识，在MicrosoftSQLServerManagementStudioMicrosoftSQLServerManagementStudio窗口中的窗口中的列属性中展开列属性中展开【标识规范标识规范】试图修改试图修改【（是标识）（是标识）】的的值为值为“是是”，但发现这一栏是灰色的，他无法进行设置，但发现这一栏是灰色的，他无法进行设置，为什么？应该如何操作才能符合他的要求？为什么？应该如何操作才能符合他的要求？解决方案：【标识规范】只对整型数据有效，所以小黄应该先将教师编号TID的数据类型设置为int类，然后再展开【标识规范】，修改【（是标识）】值为“是”，【标识种子】为“10000001”、【标识增量】为“1”2222（2）使用命令CREATETABLE创建表结构创建表结构的语法形式为：CREATETABLETable_name（column_name1data_typeNULL|NOTNULLPRIMARYKEY|UNIQUEFOREIGNKEY(column_name)REFERENCESref_table(ref_column)，column_name2data_type)2323例5在teachingDataB中建立StuInfo表（学生信息表）CREATETABLEStuInfo(SIDchar(8)primarykey,-将SID设置为主键Snamechar(10)NOTNULL,-Sname不允许取空值Sexchar(2)default男check(sex=男orsex=女),BirthDaysmalldatetimeNULL,Deptchar(20)NULL,Majorchar(20)NULL,Classchar(10)NULL,Gradechar(10)NULL,Sageint)2424例6在teachingDataB中建立TchInfo表（教师信息表）CREATETABLETchInfo(TIDchar(8)primarykey,-设置主键TNamechar(10)NOTNULL,Sexchar(2)NULL,BirthDaysmalldatetimeNULL,Titlenchar(10)NULL,Deptchar(20)NULL)2525例7在teachingDataB中建立CourseInfo表（选课信息表）CREATETABLECourseInfo(CIDchar(8)primarykey,CNamechar(20)NOTNULL,CCredittinyintNULL,CPropertychar(10)NULL)2626例8在teachingDataB中建立ScoreInfo表（学生成绩表）CREATETABLEScoreInfo(CIDchar(8)NOTNULL,-不允许取空值SIDchar(8)NOTNULL,-不允许取空值TIDchar(8)NULL,Scorenumeric(3,0)NULL,Schyearchar(9)NULL,Termchar(1)NULL,FOREIGNKEY(CID)REFERENCESCourseInfo(CID),/*表级完整性约束条件，CID是外码，被参照表是CourseInfo*/FOREIGNKEY(SID)REFERENCESStuInfo(SID),/*表级完整性约束条件，SID是外码，被参照表是StuInfo*/FOREIGNKEY(TID)REFERENCESTchInfo(TID),/*表级完整性约束条件，TID是外码，被参照表是TchInfo*/ConstraintscoInfo_primPRIMARYKEY(CID,SID)/*表级完整性约束条件，设置组合主键*/2727说明：(1)在创建ScoreInfo表时，由于要将CID字段关联CourseInfo，因此CourseInfo表必须要存在才行，这就要求在建表时先要创建CourseInfo表。同样，StuInfo表和TchInfo也要在ScoreInfo表之前创建。(2)将两个字段组合起来作为主键，只能采用表级完整性进行定义。2828三、表结构的修改与删除利用菜单命令实现使用Transact-SQL语句来实现2929（1）利用菜单命令实现表结构的修改和删除步骤1：在Microsoft SQL Server Management Studio 的“对象资源管理器”窗口中展开相应的数据库和表，右击需要修改的数据表（如StuInfo表），然后选择【修改】命令，打开表设计器窗口。步骤2：在表设计器窗口中，可以直接对已有的字段进行修改，修改列名、数据类型等。也可以右击某个字段，如“Sex”，在属性菜单中，可以选择插入列和删除列，插入列的操作与建表结构时的操作是一样的。步骤3：表中字段修改完成后，单击工具栏上的【保存】按钮，即可完成表结构的修改。3030表的删除可以在Microsoft SQL Server Management Studio 的“对象资源管理器”窗口中展开相应的数据库和表，右击需要删除的数据表，然后选择【删除】命令即可删除相应的数据表。3131（2）用命令语句修改表结构ALTER TABLE Table_nameADD column_name data_type NULL|NOT NULL PRIMARY KEY|UNIQUEFOREIGN KEY(column_name)REFERENCES ref_table(ref_column)DROP CONSTRAINT constraint_name|column column_name ALTER COLUMN ；虽然从语法格式上来看比较复杂，但实际上该命令语句一次只能修改一个参数。3232例例9向StuInfo表（学生信息表）中增加“家庭住址（Saddr）”列，其数据类型为字符型。ALTER TABLE StuInfo ADD Saddr varchar(20)不论基本表中原来是否已有数据，新增加的列一律为空值。3333例例10将表StuInfo中的年龄字段Sage的数据类型由int改为tinyint，设置其默认值为20，并规定年龄必须在10-60之间。ALTER TABLE StuInfo ALTER COLUMN Sage tinyintALTER TABLE StuInfo ADD CONSTRAINT Defa_age DEFAULT 20 FOR SageALTER TABLE StuInfo ADD CONSTRAINT CK_age CHECK(Sage10 AND Sage60)3434例例11增加学生姓名必须取唯一值的约束条件。ALTER TABLE StuInfo ADD UNIQUE(Sname)3535例例12删除StuInfo表（学生信息表）中属性列“家庭住址（Saddr）”。ALTER TABLE StuInfo Drop column Saddr3636例13修改表tchInfo结构，要求字段TID只能为数字。ALTERTABLEtchInfoADDCONSTRAINTCK_TIDCHECK(TIDIN(00,000,000-99,999,999)3737删除表可以使用DROPTABLE命令来删除表。最简单也是最常用的形式为：DROP TABLE table_name例如，要删除ScoreInfo表，则可以在查询编辑器中输入命令：DROPTABLEScoreInfo；3838但有时直接删除基本表，会出现误删的情况，比如，我们已经给这个表建立的索引、视图、触发器等，而它们都还有用，这时我们就不能随意删除基本表。这种情况要用参数RESTRICT或CASCADE来进行处理。完整的语句格式为：DROP TABLE RESTRICT|CASCADE；RESTRICT：删除表是有限制的，它不能被其他表的约束所引用，如果存在依赖该表的对象，则此表不能被删除；CASCADE：删除该表没有限制，在删除基本表的同时，相关的依赖对象一起删除。3939例如，删除ScoreInfo表（含相关的索引、视图、触发器等），则可以在查询编辑器中输入命令：DROP TABLE ScoreInfo CASCADE；4040删除字段命令格式：ALTERTABLEtable_nameDROPCOLUMNcolumn_name4141例14删除表StuInfo中的字段grade,其命令语句如下：ALTERTABLEStuInfoDROPCOLUMNgrade4242四、数据表内容的增加与修改表数据的操作主要有三种：插入数据修改数据删除数据43431、插入数据使用菜单命令来完成用Transact-SQL的标准命令INSERT语句来完成4444使用Microsoft SQL Server Management Studio可以在“对象资源管理器”中右击需要添加数据的表选择“打开表”命令，然后直接在表中输入数据。4545插入数据命令格式：INSERT INTO (column_list)VALUES(data_values)功能：将新元组插入到指定表中。对于INTO子句中没有出现的属性列，新记录在这些列上将取空值。注意:在表定义时说明了为NOTNULL的属性列不能取空值，否则会出错。如果INTO子句中没有指明属性列，则新插入的记录必须在每个属性列上均有值。属性列的顺序可与表定义中的顺序不一致。可以只指定部分属性列(未指定属性列取值为空)。VALUES子句对新元组的各个属性赋值，字符串常数要用单引号括起来。VALUES子句提供的值必须与INTO子句匹配，即值的个数相同，值的类型一致。4646例15将一个课程信息元组（课程号：00000001；课程名：高等数学；学分：3；课程类别：基础课）插入到CourseInfo表中。可以使用下面的语句来实现：可以使用下面的语句来实现：INSERTINTOCourseInfo(CID,CName,CCredit,CProperty)VALUES(00000001,高等数学,3,基础课);或：或：INSERTINTOCourseInfo(CID,CName,CProperty,CCredit)VALUES(00000001，高等数学，基础课，3)或：或：INSERTINTOCourseInfoVALUES(00000001,高等数学,3,基础课)4747如果要想向表中插入成批数据，用INSERT-VALUES来解决显然是极为不方便的。在这种情况下用INSERT-SELECT语句就非常有效。它可以将一个或一组表中的数据插入到另外一个表中。插入子查询结果的语句格式：INSERT INTO (column_list)SELECT sub-sentence说明:(1)INTO子句与插入元组时的要求类似；2）SELECT子句目标列必须与INTO子句匹配，即值的个数和值的类型均要求一致。4848例16对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：在teachingDataB数据库中建一个新表，其中一列用于保存学年信息，另一列用于保存相应学年的学生平均成绩。CREATETABLESchyear_Score(SchyearCHAR(9),/*学年*/Avg_ScoreSMALLINT)；/*学年平均成绩*/第二步：对ScoreInfo表按Schyear分组，把学年和学生的平均成绩成绩插入到新表Schyear_Score中。INSERTINTOSchyear_Score(Schyear,Avg_score)SELECTSchyear,AVG(Score)FROMScoreInfoGROUPBYSchyear49492、修改数据格式：UPDATESET=，=,WHERE；功能：修改指定table_name表中满足WHERE子句条件的记录。说明：SET子句用于指定要修改的列或变量名称的列表，用于指定要更改的列的名称。是修改后指定列的新值，它可以是一个常量，也可以是一个表达式或其他表中的数据。5050例17将高等数学的学分改为5分。UPDATECourseInfoSETCCredit=5WHERECName=高等数学；5151例18将所有课程的学分增加1分。UPDATECourseInfoSETCCredit=CCredit+15252把所有男生的成绩加5分。UPDATEScoreInfoSETScore=Score+5WHERE男=(SELECTSexFROMStuInfoWHEREStuInfo.SID=ScoreInfo.SID)；53533、删除数据格式：DELETE FROM WHERE；功能：删除指定表中满足WHERE子句条件的所有元组。如果省略WHERE子句，表示删除表中的全部元组，但表的结构仍然存在。即DELETE只能删除表中的数据。5454例19删除表TchInfo中工号为10040002的教师的记录。DELETEFROMTchInfoWHERETID10040002；5555例20删除表ScoreInfo中所有的学生成绩的记录。DELETEFROMScoreInfo；5656删除表ScoreInfo中所有男生的成绩记录。DELETEFROMScoreInfoWHERE男=(SELECTSexFROMStuInfoWHEREStuInfo.SID=ScoreInfo.SID)；5757小结数据表结构的创建CREATETABLE数据表结构的修改ALTERTABLE记录的添加INSERTINTO-VALUESINSERTINTO-SELECT记录的修改UPDATA记录的删除DELETE建立索引的常用命令格式为：CREATEUNIQUECLUSTEREDINDEX5858上机实验完成PPT中的所有例题。