《SQL Server数据库应用技术实例教程》课件任务三

任务：了解SQLServer2005支持的数据类型，创建自定义数据类型。设计JXGL数据库表结构，应用SSMS和T-SQL语言两种方法，创建、修改JXGL数据库中的表，向表中输入数据，对表中数据进行简单管理。3.1T-SQL语言的数据类型3.2设计“高职院校教学管理系统”中表的结构3.3创建表3.4管理表3.5操作表中的数据3.6知识进阶任务三向数据库输入数据

—创建和管理SQLServer2005数据库表了解T-SQL语言支持的基本数据类型创建用户自定义数据类型使用SSMS创建、修改JXGL数据库表使用CREATETABLE语句创建数据库表，使用ALTERTABLE语句修改数据库表使用SSMS向JXGL数据库输入新数据，删除、更新已有数据使用T-SQL语言向JXGL数据库输入新数据，删除、更新已有数据本章学习目标创建和管理SQLServer2005数据库表创建和管理SQLServer2005数据库表3.1T-SQL语言的数据类型任务：了解T-SQL语言支持的基本数据类型，掌握用户自定义数据类型的方法。3.1T-SQL语言的数据类型SQL语言提供了25种系统数据类型，还支持用户根据需要创建基于系统数据类型的自定义数据类型，这些数据类型T-SQL可用。1．系统数据类型

SQLServer2005中的系统数据类型归纳为下列类别：精确数值、近似数值、日期时间、货币、字符串、Unicode字符串、二进制字符串和其他数据类型，如教材表3-1“SQLServer2005系统数据类型”所示。创建和管理SQLServer2005数据库表2．使用系统数据类型应注意的问题精确整数型数据bit型数据当列为8bit或更少时，以1个字节存储；为9～16bit时，以2个字节存储。货币型数据money型以2个4字节存储，精确到万分之一。smallmoney型数据以2个2字节存储，精确到万分之一。精确小数型数据decimal和numeric在表示数据精度方面没有区别，但只有numeric数据类型才可作为主键列。近似数值型数据该类型用于存储小数点不固定的数值，一般不用于存储货币。3.1T-SQL语言的数据类型创建和管理SQLServer2005数据库表日期/时间型数据datetime型以8字节存储，精确到百分之三秒或3.33毫秒；smalldatetime型使用4字节存储，精确到分钟。字符型数据char型字符串在输入长度大于定义长度时，会被截断；而小于定义长度时，会以空格填充。varchar和text型的存储空间会根据实际字符串长度而变化。Unicode字符型数据Unicode统一字符编码标准用于支持英语语种的字符数据。用两个字节存储一个字符。输入该类型数据时需在字符前加前导标识符N，但表中不会存储该标识符。二进制型数据SQLServer2005采用十六进制存储二进制数据。3.1T-SQL语言的数据类型创建和管理SQLServer2005数据库表3．使用SSMS创建用户自定义数据类型（操作步骤省略）4．使用T-SQL创建用户自定义数据类型（1）使用系统存储过程sp_droptype可以删除用户自定义的数据类型。代码如下：

USEJXGLGOsp_droptypepostcodeGO3.1T-SQL语言的数据类型执行该代码，将把用户自定义数据类型postcode从“用户定义数据类型”列表中删除创建和管理SQLServer2005数据库表（2）使用系统存储过程sp_addtype可以创建用户自定义数据类型。基本语法如下：

sp_addtype[@typ_ename=]type,[@phystype=]system_data_type[,[@nulltype=]'null_type']【例3-1】使用sp_addtype在JXGL中创建一个名为code的自定义数据类型。

USEJXGLGOsp_addtypecode,'char(6)','NULL'GO3.1T-SQL语言的数据类型其中：

type_name：用户定义数据类型的名称。

@phystype：用户定义数据类型所基于的系统数据类型名称，包括其长度、精度、小数位数。

@nulltype：是否允许为空值。值为'NULL'或'NOTNULL'，默认值为'NOTNULL'。

提示：只能删除还没有被用户绑定的用户定义数据类型。或者说，不能删除正在被表或其他数据库对象使用的用户定义数据类型。创建和管理SQLServer2005数据库表任务：规范存储数据的数据库环境，设计JXGL数据库中表的结构。创建和管理SQLServer2005数据库表3.2设计“高职院校教学管理系统”中表的结构创建用户数据库后，还无法将数据保存在数据库中，即此时的数据库是一个空库。必须在数据库中创建用来存放数据的“容器”，这个“容器”就是表。表是数据库中最重要的对象，是用来存储和操纵数据的逻辑结构。开发一个数据库系统需要经过一个“两次抽象”的过程，在第一次抽象过程中，要分析所开发的数据库系统包含哪些实体，分析每个实体应该至少包含的属性，以及实体之间的联系，并使用E-R图将抽象结果表示出来；第二次抽象过程，需要将E-R图转换为数据模型即数据库表，每个属性就是表中的每个列或字段。在设计表的过程中，最重要的任务之一，是为表确定主键。按以上原则及“高职院校教学管理系统”数据库逻辑设计结果，确定系统数据库表的逻辑结构如教材中的表3-2至3-10所示。3.2设计“高职院校教学管理系统”中表的结构创建和管理SQLServer2005数据库表任务：使用SSMS及T-SQL的CREATETABLE语句创建JXGL数据库表。3.3创建表创建和管理SQLServer2005数据库表3.3.1使用SSMS创建表3.3.2使用T-SQL语句创建表依据不同的分类标准，SQLServer2005中的表有以下分类。按照表中数据存储的时间分为永久表和临时表。永久表一旦创建，除非人工删除，否则一直存在；临时表中的数据只在数据库运行期间临时存在，退出连接数据库后，表也将从内存中删除。按照表的用途分为系统表和用户表。系统表用来维护SQLServer服务器工作，存储系统级信息；用户表一般为永久表，由用户创建，用于开发数据库应用系统。使用SSMS创建数据库JXGL中的student_info表:，步骤如教材所述。

3.3.1使用SSMS创建表创建和管理SQLServer2005数据库表

创建表的T-SQL语句是CREATETABLE，基本语法如下：CREATETABLEtable_name(column_namedata_type[column_constraint][,column_namedata_type[column_constraint]…][,<table_constraint>])3.3.2使用T-SQL语句创建表其中：

table_name：表名称。表名必须符合标识符规则，最多包含128个字符，在数据库中必须唯一。

column_name：列的名称，列名称必须符合标识符规则，在表中必须唯一。

data_type：指定列的数据类型。

column_constraint：列级约束。对应列采取的限制措施，如非空性、默认值、唯一性、完整性等（关于约束的概念将在任务四中介绍）。

table_constraint：表级约束。当完整性约束涉及该表的多个属性列时，必须定义在表级上。这些定义连同对应的列约束被存储在系统中。当对数据库进行相关操作时，系统自动检查操作的合法性。创建和管理SQLServer2005数据库表1．【例3-2】使用CREATETABLE语句创建表teacher_info。USEJXGLGOCREATETABLEteacher_info( teac_idchar(8)PRIMARYkey,--将teac_id设置为表的主键

teac_namenvarchar(20)NOTNULL, dep_idchar(2)NOTNULLDEFAULT('J'),--该列设置非空和默认值

genderbitNOTNULLDEFAULT(1), professionnvarchar(20)DEFAULT(N'副教授'), telephonevarchar(14), thome_addrnvarchar(50)NOTNULLDEFAULT(N'不详'), tzipcodecode)GO3.3.2使用T-SQL语句创建表

提示：选择表teacher_info，右击，在快捷菜单中选择“编写表脚本为”→“CREATE到”→“新建查询窗口”或“文件”或“剪贴板”命令，可以在不同的位置产生创建该表的SQL脚本。创建和管理SQLServer2005数据库表任务：使用SSMS及T-SQL的ALTERTABLE语句、DROPTABLE语句修改表结构或删除用户表。3.4管理表创建和管理SQLServer2005数据库表3.4.1使用SSMS管理表3.4.2使用T-SQL管理表1．查看表在“对象资源管理器”中查看该表的常规、权限和扩展属性。2．修改表（1）在“对象资源管理器”中选择某个表，在该窗口中对表进行修改，如添加、删除列，更改列名、数据类型、列宽度。（2）在“对象资源管理器”中选择某个表，打开“表属性”对话框，修改表的权限属性值。3．删除表在“对象资源管理器”中选择某个表，打开“删除对象”对话框，确认被删除对象后，单击“确定”按钮。4．重命名表在“对象资源管理器”中选择某个表，在表名位置上直接输入新的表名即可重命名该表。3.4.1使用SSMS管理表

提示：修改表结构时，一是尽量在没有数据操作的情况下进行，二是修改包含数据的表结构时要保证修改前后数据类型的兼容性，否则会造成数据丢失。创建和管理SQLServer2005数据库表1．查看表使用系统存储过程sp_helptable_name可以查看指定表的所有属性信息。2．修改表使用ALTERTABLE语句可以修改表结构，包括添加新列及其约束，删除原有的列及其约束，更改数据类型、列宽度等。基本语法如下：

ALTERTABLE<table_name>[ALTERCOLUMN<column_name><type_name>][ADD<new_column_name><type_name>[column_constraint]][DROP[column_constraint_name]|[column_name]3.4.2使用T-SQL管理表其中：

ALTERCOLUMN子句：修改列名及数据类型、列宽度，一个ALTERTABLE语句中只包含一个ALTERCOLUMN子句。

ADD子句：添加新列及其约束，一个ALTERTABLE语句中只包含一个ADD子句。

DROP子句：删除列或列约束，删除列时在DROP后加关键字COLUMN。一个ALTERTABLE语句中只包含一个DROP子句。。创建和管理SQLServer2005数据库表（1）修改列。【例3-3】修改表book_info中的列book_name，数据类型改为nvarchar型，列宽度为20。代码如下：

ALTERTABLEbook_infoALTERCOLUMNbook_namenvarchar(20)GO（2）删除列及其约束。当列包含约束时，不能直接进行修改或删除，必须首先删除约束。【例3-4】修改表book_info中的列book_id，数据类型改为int型。代码如下：

ALTERTABLEbook_infoALTERCOLUMNbook_idintGO3.4.2使用T-SQL管理表执行该代码，提示修改主键列数据类型出错，则需要执行如下代码，先删除该列中的约束：

ALTERTABLEbook_infoDROPPK_book_infoGO创建和管理SQLServer2005数据库表【例3-5】删除表book_info中的列book_id。代码如下：

USEJXGLGOALTERTABLEbook_infoDROPPK_book_infoGOALTERTABLEbook_infoDROPCOLUMNbook_idGO3.4.2使用T-SQL管理表创建和管理SQLServer2005数据库表（3）添加新列及其约束。【例3-6】向表book_info中添加一个新列price，数据类型为numeric型，精度为5，小数位数为2，设置非空属性，默认值为100。代码如下：USEJXGLGOALTERTABLEbook_infoADDpricenumeric(5,2)NOTNULLDEFAULT(100.00)GO3.4.2使用T-SQL管理表创建和管理SQLServer2005数据库表（4）添加约束。【例3-7】修改课程信息表course_info，将列course_id设置为表的主键。代码如下：

USEJXGLGOALTERTABLEcourse_infoADDCONSTRAINTpk_course_infoPRIMARYKEY(course_id)GO3.4.2使用T-SQL管理表

提示：一个ALTERTABLE语句只能修改一列，但可以添加或删除多个列，只需在各列名之间用逗号“，”隔开。默认情况下删除的是约束，要修改或删除带约束的列，必须首先删除约束。创建和管理SQLServer2005数据库表3．删除表使用DROPTABLE语句删除表。【例3-8】使用DROPTABLE语句，删除数据库JXGL中的表classroom_info。代码如下：USEJXGLGODROPTABLEclassroom_infoGO3.4.2使用T-SQL管理表

提示：表一旦被删除，表的结构定义、数据、全文索引、约束和索引都将从数据库中永久删除，原来存放表及其索引的存储空间被释放给其他数据库对象。创建和管理SQLServer2005数据库表3.5操作表中的数据任务：使用SSMS及T-SQL的INSERTINTO语句向表中输入数据，使用SSMS及T-SQL的DELETE、UPDATE语句删除、更新表中的数据。创建和管理SQLServer2005数据库表3.5.1使用SSMS操作表中的数据3.5.2使用T-SQL操作表中的数据

提示：①对标识列、允许为空的列、有默认值的列、计算列、能自动取值的列，可以不输入具体的值，系统会为这些列自动赋值。除以上几列外，其余的必须输入数据，否则系统会提示错误。②表的某些列可能会带有某种约束条件，输入、修改数据时，必须符合这些设置的条件。如果不小心输入了不符合要求的数据，系统会弹出提示信息框提示用户输入的数据非法。如图3-6所示。③对设置默认值的列在输入非法数据时会以默认值自动取代。④输入货币型数据的格式为“$12.22”。3.5.1使用SSMS操作表中的数据创建和管理SQLServer2005数据库表1．使用INSERT语句输入数据使用T-SQL的INSERTINTO语句，可以向表中输入数据。基本语法如下：INSERT[INTO]<table_name|view_name>[columnlist]VALUES(valueslist|selectstatement)3.5.2使用T-SQL操作表中的数据其中：columnlist：要输入数据的列的清单。valueslist：要向表中输入的数据的清单。selectstatement：通过查询向当前表中输入数据的查询语句。使用创建和管理SQLServer2005数据库表INSERT语句输入数据时应注意以下问题：⑴当向表中所有列输入数据时，columnlist子句可以缺省。若缺省该子句，数据清单中数据的顺序一定与表中列的顺序一致。⑵当要输入数据的表中包含有标识列、允许为空的列、设置默认值的列时，可以不输入该列的值。⑶当向字符型、日期型列输入数据时，要以单引号引起来。（1）向表中输入一行数据。【例3-9】向表teacher_info插入一条合法记录。代码如下：USEJXGLGOINSERTINTOteacher_info(teac_id,teac_name,depart_id,gender,profession,telephone,thome_addr,tzipcode)VALUES('TD009',N'董卫刚','TD',1,N'讲师',,N'济南市燕子山路号,'250014')图3-7向表中插入一行数据GO3.5.2使用T-SQL操作表中的数据注意：columnlist子句缺省时，数据清单中数据的顺序和数目一定与表中列的顺序和数目一致。代码如下：INSERTINTOteacher_infoVALUES('TG0013',N'汪虹','TG',1,N'副教授',,N'济南市解放路','250014')GO创建和管理SQLServer2005数据库表（2）输入一行中的部分列。当表中包含设置了默认值及允许为null值的列时，可不输入数据，但列名清单不能缺省。【例3-10】向表teacher_info插入一条合法记录。代码如下：USEJXGLGOINSERTINTOteacher_info(teac_id,teac_name,dep_id,gender,profession)--列名清单只列出需要输入数据的列VALUES('TJ0110',N'姜艳','J',0,N'教授')--输入清单中列出的部分列的数据GO3.5.2使用T-SQL操作表中的数据创建和管理SQLServer2005数据库表（3）输入多行数据。使用INSERT…SELECT语句可以一次向表中插入多行数据，其中SELECT语句可以从其他表中查询出多行数据。【例3-11】将表table_3（请用户自行创建该表）中的所有数据插入到表book_info中去。代码如下：USEJXGLGOINSERTbook_infoSELECT*--“*”表示查询出表table_3中的所有列FROMtable_3GO3.5.2使用T-SQL操作表中的数据

提示：使用SELECT语句查询出的数据，与将插入数据的列的数目、数据类型、顺序和宽度要一致。创建和管理SQLServer2005数据库表（3）输入多行数据。使用INSERT…SELECT语句可以一次向表中插入多行数据，其中SELECT语句可以从其他表中查询出多行数据。【例3-11】将表table_3（请用户自行创建该表）中的所有数据插入到表book_info中去。代码如下：USEJXGLGOINSERTbook_infoSELECT*--“*”表示查询出表table_3中的所有列FROMtable_3GO3.5.2使用T-SQL操作表中的数据

提示：使用SELECT语句查询出的数据，与将插入数据的列的数目、数据类型、顺序和宽度要一致。创建和管理SQLServer2005数据库表2．使用DELETE语句删除表中的数据行表中没有意义的数据，使用DELETE语句以行为单位删除。基本语法如下：DELETE[FROM]table_name|view_nameWHEREsearch_condition【例3-12】删除表book_info中书价（book_price）高于30元钱的书的信息。代码如下：DELETEFROMbook_infoWHEREbook_price>=30GO3.5.2使用T-SQL操作表中的数据

其中：参数search_condition是一个逻辑表达式，用来描述被删除的数据应该满足的条件。当该子句缺省时，表（或视图）中的数据都将被删除，但表结构（视图定义）仍然存在于数据库中。创建和管理SQLServer2005数据库表

提示：使用DELETE语句一次最少删除一行数据，而不是删除表中指定的部分列的数据。删除行时，系统不会提供确认信息，所以用户使用该语句要谨慎。

提示：当缺少WHERE子句时，删除整张表。【例3-13】删除表book_info中所有书的信息。代码如下：USEJXGLGODELETEbook_infoGO3．使用UPDATE语句更新表中的数据使用UPDATE语句可以更新表的一行、一行中的某些列，也可以更新表中所有行或所有行的某些列。基本语法如下：UPDATEtable_nameSETcolumn_name=express|NULL|DEFAULT[,…n][WHERElogical_express]3.5.2使用T-SQL操作表中的数据创建和管理SQLServer2005数据库表其中：SET子句：指定要被更新的列及其新的值。WHERE子句：指定修改条件，只有满足条件的那些行的列值才被修改为SET子句指定的数据。express：新数据可以是常量或表达式。（1）更新表中所有行的某一列。【例3-14】将表student_grade中所有同学的成绩提高5分。USEJXGLGOUPDATEstudent_gradeSETgrade=grade+5GO3.5.2使用T-SQL操作表中的数据

其中：“grade=grade+5”表示将grade列的值在原来值的基础上增加5。。创建和管理SQLServer2005数据库表（2）更新表中某些行的某一列。【例3-15】将表student_grade中学号为J07072141的同学所有课程的成绩提高5分（该同学选修了4门课程）。代码如下：USEJXGLGOUPDATEstudent_gradeSETgrade=grade+5WHEREstud_id='J07072141'GO3.5.2使用T-SQL操作表中的数据创建和管理SQLServer2005数据库表（3）更新表中多个行的多个列。【例3-16】将表student_grade中成绩为70分以下的同学的成绩提高5分，学分提高警惕0.2分。代码如下：USEJXGLGOUPDATEstudent_gradeSETgrade=grade+5,stud_credit=stud_credit+0.2WHEREgrade<=70GO3.5.2使用T-SQL操作表中的数据创建和管理SQLServer2005数据库表任务：理解第一范式、第二范式、第三范式的含义及作用，在表中应用标识列、计算列的方法。

创建和管理SQLServer2005数据库表3.6.1范式3.6.2关于标识列和计算列3.6知识进阶构造关系型数据库必须遵循一定的规则，在关系型数据库中这种规则称为范式。关系型数据库中关系（表）必须满足一定的要求，即满足不同的范式。目前关系型数据库有6种范式：第一范式（简称1NF）、第二范式（简称2NF）、第三范式（简称3NF）、第四范式（简称4NF）、第五范式（简称5NF）和第六范式（简称6NF）。满足最低要求的范式是第一范式，在第一范式的基础上进一步满足更多要求的称为第二范式，其余范式依此类推。一般说来，关系型数据库只需满足第三范式即可。3.6.1范式创建和管理SQLServer2005数据库表1．第一范式在任何一个关系型数据库中，第一范式是对关系模式的基本要求。反之，不满足第一范式的数据库不是关系数据库。第一范式指表的每一列都是不可分割的基本数据项，同一列中不能有多个值。即实体集（表）中的某个属性有多个值或者重复的属性。如果出现重复的属性就可能需要定义一个新的实体集，新的实体集由重复的属性构成。新实体集与原来实体集之间为一对多关系。简而言之，第一范式就是关系模式中无重复的列。3.6.1范式创建和管理SQLServer2005数据库表2．第二范式第二范式是在第一范式基础上建立起来的，即满足第二范式必须先满足第一范式。第二范式要求表中的每行必须可以被唯一区分，为实现区分，通常需要为表加上一个列以存储各个行的唯一标识，通常就是主键，实体的其他属性完全依赖于主键。简而言之，第二范式就是非主键属性不能部分依赖（必须完全依赖）于主键。3.6.1范式创建和管理SQLServer2005数据库表3．第三范式满足第三范式必须先满足第二范式。第三范式要求一个数据库表中不能包含已经在其他表中包含的非主键，或者说只能包含另一个表中的主键。例如，存在一个系信息表（department_info），其中每个系有系编号、系名称、系主任、该系的教师人数等信息。那么在教师信息表中列出系编号后就不能再将系名称、系主任、该系的教师人数等与系有关的信息加入教师信息表（teacher_info）中。如果不存在系信息表，则根据第三范式也应该构建它，否则会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其他表的非主键。3.6.1范式创建和管理SQLServer2005数据库表1．标识列（IDENTITY列）标识列是这样的列：给定一个初始值，称为种子；给定一个递增值，称为步