第3章向数据库输入数据

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

SQLServer2005中的系统数据类型归纳为下列类别：精确数值、近似数值、日期时间、货币、字符串、Unicode字符串、二进制字符串和其他数据类型。系统提供的数据类型种类数据类型数值整数int,bigint,smallint,tinyint精确数值decimal,numeric近似数值float,real货币money,smallmoney日期和时间

datetime,smalldatetime字符Non-Unicodechar,varchar,varchar(max),textUnicodenchar,nvarchar,nvarchar(max),ntext二进制binary,varbinary,varbinary(max)图像image全局标识符uniqueidentifierXMLxml特殊bit,cursor,timestamp,sysname,table,sql_variant参考“SQLServer联机丛书”中的“SQLserver语言参考”——“Transact-SQL参考”——“数据类型”精确和近似数值数据类型固定长度和可变长度字符数据类型大型数据值：text ntextvarchar(max)nvarchar(max)varbinary(max)image使用系统数据类型应注意的问题精确整数型数据bit型数据当列为8bit或更少时，以1个字节存储；为9～16bit时，以2个字节存储。近似数值型数据该类型用于存储小数点不固定的数值，一般不用于存储货币。字符型数据char型字符串在输入长度大于定义长度时，会被截断；而小于定义长度时，会以空格填充。varchar和text型的存储空间会根据实际字符串长度而变化。Unicode字符型数据Unicode统一字符编码标准用于支持英语语种的字符数据。用两个字节存储一个字符。输入该类型数据时需在字符前加前导标识符N，但表中不会存储该标识符。2.用户自定义数据类型

——别名数据类型当多个表必须在一个列中存储相同类型的数据，而您必须确保这些列具有相同的数据类型、长度和为空性时，可以使用用户自定义数据类型即别名类型。别名类型是基于SQLServer2005中提供的系统数据类型：允许进一步细化数据类型在特定数据库中定义在数据库内必须有唯一的名称创建别名数据类型时，必须提供下列参数：Name新数据类型基于的系统数据类型为空性（数据类型是否允许空值）

如果未明确定义为空性，系统将基于数据库或连接的ANSINULL默认设置进行指定。创建别名数据类型的方法：使用SSMS：在数据库中依次展开：“可编程性”——“类型”——“用户定义数据类型”使用T-SQL语句别名数据类型创建别名数据类型SQL语句格式CREATETYPE[schema_name.]type_name{FROMbase_type[(precision[,scale])][NULL|NOTNULL]参数说明：schema_name

别名数据类型或用户定义类型所属架构的名称。type_name

别名数据类型或用户定义类型的名称。类型名称必须符合标识符的规则。别名数据类型base_type别名数据类型所基于的数据类型，由SQLServer提供。precision对于decimal或numeric，指示可保留的十进制数字位数的最大值，包括小数点左边和右边的数字。scale对于decimal或numeric，指示十进制数字的小数点右边最多可保留多少位，它必须小于或等于精度值。NULL|NOTNULL

指定此类型是否可容纳空值。如果未指定，则默认值为NULL。使用Transact-SQL创建别名数据类型--创建名为postcode的别名数据类型，--指定其数据类型为char型，长度为6，可空CREATETYPEdbo.postcodeFROMchar(6)NULL任务： 规范存储数据的数据库环境，设计JXGL数据库中表的结构。3.2设计“高职院校教学管理系统”中表的结构3.2设计“高职院校教学管理系统”中表的结构创建用户数据库后，还无法将数据保存在数据库中，即此时的数据库是一个空库。必须在数据库中创建用来存放数据的“容器”，这个“容器”就是表。表是是用来存储和操纵数据的逻辑结构。开发一个数据库系统需要经过一个“两次抽象”的过程，在第一次抽象过程中，要分析所开发的数据库系统包含哪些实体，分析每个实体应该至少包含的属性，以及实体之间的联系，并使用E-R图将抽象结果表示出来；第二次抽象过程，需要将E-R图转换为数据模型即数据库表，每个属性就是表中的每个列或字段。15学生主键：学号外键：班号班级主键：班号外键：系号包含1n系部主键：系号包含1n教师主键：教师编号外键：系号包含1n课程主键：课号外键：教师编号教材编号排课1n成绩nm外键：课号外键：学号教材主键：教材编号外键：教师编号班级编号课程编号任务：使用SSMS及T-SQL的CREATETABLE语句创建JXGL数据库表。3.3.1使用SSMS创建表3.3.2使用T-SQL语句创建表3.3创建表依据不同的分类标准，SQLServer2005中的表有以下分类。按照表中数据存储的时间分为永久表和临时表。永久表一旦创建，除非人工删除，否则一直存在；临时表中的数据只在数据库运行期间临时存在，退出连接数据库后，表也将从内存中删除。按照表的用途分为系统表和用户表。系统表用来维护SQLServer服务器工作，存储系统级信息；用户表一般为永久表，由用户创建，用于开发数据库应用系统。例：使用SSMS创建数据库JXGL中的student_info表3.3.1使用SSMS创建表创建表的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：表级约束。当完整性约束涉及该表的多个属性列时，必须定义在表级上。这些定义连同对应的列约束被存储在系统中。当对数据库进行相关操作时，系统自动检查操作的合法性。1．【例3-2】使用CREATETABLE语句创建表teacher_info。USEJXGLGOCREATETABLEteacher_info( teac_idchar(8)PRIMARYkey,

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语句创建表在创建表时，某些列定义为NULL或NOT

NULL，这两种定义指明了是否在该列中输入数据。如果一个列中有NULL值（允许空值），意味着该列中可以没有数据。具有NULL值的列是一种特殊的数据状态，它既不是数值，也不是字符，而意味着一种未知状态。例如，在student_info表中的home_addr列设置为NULL值，意味着当不明确某一学生的家庭住址时，可以暂时处于空的状态（相当于输入“地址不详”）。表中的主键不能为NULL值。NULL值（空值）的作用1．标识列（IDENTITY列）标识列是这样的列：给定一个初始值，称为种子；给定一个递增值，称为步长；则每一个列值总是由前一行该列的值加上步长得来。定义标识列要注意以下问题：（1）一个表只能定义一个标识列。（2）标识列的数据类型只能是int、smallint或tinyint型，或者是小数位数为0的decimal、numeric类型3.6.2关于标识列和计算列（3）需要指定标识的种子和步长值，若不指定，二者默认值均为1。（4）标识列的值不能人工输入，由系统按种子和步长值自动产生，标识列的值不能被更新。（5）标识列不允许空值，也不能指定默认值（6）标识列可以定义为表的主键。标识列的定义列名数据类型IDENTITY[(标识种子，标识增量)]2．计算列计算列指其值由同一表中的其他列的值或值的表达式计算得来。计算列的值不能人工输入，也不能人工更新。除非用户指定，否则计算列是没有存储在表中的虚拟列。当在查询中引用计算列时，都将重新计算该列的值。如果计算列涉及的列的值发生变化，将自动更新计算列的值。创建表时，在“表设计器”窗口的“列编辑”区中指定某列，在“列属性编辑区”中展开“计算所得的列规范”列表，在“公式”栏中输入计算公式。3.6.2关于标识列和计算列使用SQL语句定义计算列：列名as计算列表达式使用CREATETABLE语句创建包含计算列的表。代码如下

CREATETABLEtable_1

（a1intNOTNULL,b1intNOTNULL,c1asa1+b1 )GO任务：使用SSMS及T-SQL的ALTERTABLE语句、DROPTABLE语句修改表结构或删除用户表。3.4.1使用SSMS管理表3.4.2使用T-SQL管理表3.4管理表

1．查看表在“对象资源管理器”中查看该表的常规、权限和扩展属性。2．修改表（1）在“对象资源管理器”中选择某个表，在该窗口中对表进行修改，如添加、删除列，更改列名、数据类型、列宽度。（2）在“对象资源管理器”中选择某个表，打开“表属性”对话框，修改表的权限属性值。3.4.1使用SSMS管理表提示：修改表结构时，一是尽量在没有数据操作的情况下进行，二是修改包含数据的表结构时要保证修改前后数据类型的兼容性，否则会造成数据丢失。3．删除表在“对象资源管理器”中选择某个表，打开“删除对象”对话框，确认被删除对象后，单击“确定”按钮。4．重命名表在“对象资源管理器”中选择某个表，在表名位置上直接输入新的表名即可重命名该表。3.4.2使用T-SQL管理表1．查看表使用系统存储过程sp_helptable_name可以查看指定表的所有属性信息。execsp_helpteacher_info2.修改表的结构ALTERTABLE[数据库名.架构名.]表名{ALTERCOLUMN

列名新数据类型(长度)[NULL|NOTNULL]|ADD<列定义>|DROPCOLUMN

列名[,…n]}修改表的结构useJXGLGOaltertablestudent_infoaltercolumnmarknumeric(5,1)goaltertablestudent_infoaddaddressvarchar(30),postcodechar(6)goaltertablestudent_infoDROPcolumnaddressgo3.删除表DROPTABLE表名（1）修改列。【例3-3】修改表book_info中的列book_name，数据类型改为nvarchar型，列宽度为20。代码如下：

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

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

USEJXGLGOALTERTABLEcourse_infoADDCONSTRAINTpk_course_infoPRIMARYKEY(course_id)GO提示：一个ALTERTABLE语句只能修改一列，但可以添加或删除多个列，只需在各列名之间用逗号“，”隔开。要修改或删除带约束的列，必须首先删除约束。3．删除表使用DROPTABLE语句删除表。【例3-8】使用DROPTABLE语句，删除数据库JXGL中的表classroom_info。代码如下：USEJXGLGODROPTABLEclassroom_infoGO提示：表一旦被删除，表的结构定义、数据、全文索引、约束和索引都将从数据库中永久删除，原来存放表及其索引的存储空间被释放给其他数据库对象。

任务：使用SSMS及T-SQL的INSERTINTO语句向表中输入数据，使用SSMS及T-SQL的DELETE、UPDATE语句删除、更新表中的数据。3.5.1使用SSMS操作表中的数据3.5.2使用T-SQL操作表中的数据3.5操作表中的数据提示：①对标识列、允许为空的列、有默认值的列、计算列，可以不输入具体的值，系统会为这些列自动赋值。其余的列必须输入数据，否则系统会提示错误。②表的某些列可能会带有某种约束条件，输入、修改数据时，必须符合这些设置的条件。如果不小心输入了不符合要求的数据，系统会弹出提示信息框提示用户输入的数据非法3.5.1使用SSMS操作表中的数据1．使用INSERT语句输入数据格式：INSERT[INTO]<table_name>[(column_namelist)]VALUES(valueslist|selectstatement)其中：columnlist：要输入数据的列的清单。valueslist：要向表中输入的数据的清单。selectstatement：通过查询向当前表中输入数据的查询语句。使用INSERT语句输入数据时应注意以下问题：⑴当向表中所有列输入数据时，columnlist子句可以缺省。若缺省该子句，数据清单中数据的顺序一定与表中列的顺序一致。⑵当要输入数据的表中包含有标识列、允许为空的列、设置默认值的列时，可以不输入该列的值。⑶当向字符型、日期型列输入数据时，要以单引号引起来。42（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')GO注意：columnlist子句缺省时，数据清单中数据的顺序和数目一定与表中列的顺序和数目一致。代码如下：INSERTINTOteacher_infoVALUES('TG0013',N'汪虹','TG',1,N'副教授','',N'济南市解放路','250014')GO43（2）输入一行中的部分列。当表中包含设置了默认值及允许为null值的列时，可不输入数据，但列名清单不能缺省。【例3-10】向表teacher_info插入一条合法记录。USEJXGLGOINSERTINTOteacher_info(teac_id,teac_name,dep_id,gender,profession)--列名清单只列出需要输入数据的列VALUES('TJ0110',N'姜艳','J',0,N'教授')--输入清单中列出的部分列的数据GO2．使用DELETE语句删除表中的数据行基本语法如下：DELETE[FROM]table_nameWHEREsearch_condition其中： 参数search_condition是一个逻辑表达式，用来描述被删除的数据应该满足的条件。当该子句缺省时，表（或视图）中的数据都将被删除，但表结构（视图定义）仍然存在于数据库中。练习：向表department_info中插入一条记录，各列的值依次为‘D’，‘电子工程系’,’温佳亮’USEJXGLGOINSERTINTOdepartment_info(dep_name,dep_head)VALUES(’电子工程系’,’温佳亮’)GO【例3-12】删除表book_info中书价（book_price）高于30元钱的书的信息。代码如下：DELETEFROMbook_infoWHEREbook_price>=30GO提示：使用delete语句删除数据时，是以行为单位进行删除。删除行时，系统不会提供确认信息，所以用户使用该语句要谨慎。当缺少WHERE子句时，删除整张表。【例3-13】删除表book_info中所有书的信息USEJXGLGODELETEbook_infoGO3．使用UPDATE语句更新表中的数据使用UPDATE语句可以修改表中的数据。基本语法如下：UPDATEtable_nameSETcolumn_name=express,[……][WHERElogical_express]其中：SET子句：指定要被更新的列及其新的值。WHERE子句：指定修改条件，只有满足条件的那些行的列值才被修改为SET子句指定的数据。express：新数据可以是常量或表达式。【例3-14】将表student_grade中所有同学的成绩提高5分。USEJXGLGOUPDATEstudent_gradeSETgrade=grade+5GO（1）更新表中所有行的某一列。【例3-15】将表student_grade中学号为J07072141的同学所有课程的成绩提高5分。代码如下：USEJXGLGOUPDATEstudent_gradeSETgrade=grade+5WHEREstud_id='J07072141'GO（2）更新表中某些行的某一列【