第5章数据表的管理与使用-完

第五章数据表的管理与使用本章主要内容15.1数据类型25.2创建表35.3修改表45.4删除表55.5数据管理65.6数据完整性5数据表的管理与使用SQLServer的表是最重要的数据库对象，用户数据分别存放在各个表中，对数据的增加、删除、修改和查询等都是通过表的操作来实现的。本章主要内容15.1数据类型25.2创建表35.3修改表45.4删除表55.5数据管理65.6数据完整性5.1数据类型在创建表时，必须为表中的每列指派一种数据类型，数据类型确定了该列数据的取值范围，它决定了数据在计算机中的存储格式、存储长度等属性。SQLServer数据类型分为系统数据类型和用户自定义数据类型。SQLServer2008中常用的系统数据类型如表5.1所示。5.1数据类型表5.1SQLServer2008中常用的系统数据类型分类说明数据类型数字数据整数型bigint，int，smallint，tinyint精确数值型decimal，numeric浮点型float，real货币型money，smallmoney位型bit字符数据字符型char，varchar，Text(改用varchar(MAX))Unicode字符型nchar，nvarchar，ntext(改用nvarchar(MAX))日期时间数据日期时间型datetime，smalldatetime二进制数据二进制型binary，varbinary，Image(改用varbinary(MAX))1.数字数据（1）整数型数据Bigint：-263～263-1，精度19位，长度8个字节，-9223372036854775808到9223372036854775807Int：-231～231-1，精度10位，长度4个字节，-2147483648到2147483647Smallint：-215～215-1，精度5位，长度2个字节，-32768到32767Tinyint：0～255，精度3位，长度1个字节，0到255该类别的数据仅包含数字，包括正数、负数及分数等。1.数字数据（2）精确数值型用于存储带有小数点且小数点后位数确定的实数，包括decimal和Numeric两类。功能上两者完全等价。声明格式：numeric(p[,s])|decimal(p[,s])，其中p为精度，s为小数位数。精度：指数值中所存储的十进制数据的总位数。小数位数：指数值数据中小数点右边可以有的数字位数的最大值。如7654.321的精度为7，小数位数为3。注意：声明该类型的数据时，其小数位数必须小于精度。1.数字数据（3）浮点型是指不能精确表示数据的精度的数据类型，使用此类型来存储数据时，可能会损失部分精度，它可以用于处理取值范围非常大且对精度要求不是十分高的数值。real：-3.40E+38～3.40E+38，精度7位，长度4个字节float：-1.79E+308～1.79E+308，精度15位，长度8个字节1.数字数据（4）货币型专门用于处理货币的数据类型：money和smallmoney，它们用十进制数表示货币值,相当于4位小数的DECIMAL值。money:精度19位，小数位数为4Smallmoney:精度为10，小数位数为4当向表中插入货币型数据时，可在数据前面加上货币符号($)，并且数据中间不能有逗号；如果货币值为负数，需要在$后面加上负号。如：$777.90$620$-120.801.数字数据（5）位型Bit类型用于存储整数，取值在0、1或NULL，常用于逻辑数据的存取。2.字符数据字符数据用于存储汉字、英文字母、数字、标点和各种符号。在输入时，需要用单引号括起来。char(n)和nchar(n)：定长的字符数据类型，n表示字符型数据的长度。varchar(n)和nvarchar(n)：变长的字符数据类型，n表示串中字符的最大长度。用于存放大量的字符数据的文本型text和ntext，现在分别用varchar(MAX)和nvarchar(MAX)来替换使用。3.日期时间数据Datetime：表示日期范围1753-1-1~9999-12-31的日期和时间数据，精度为3ms。数据长度为8个字节Smalldatetime：表示日期范围1900-1-1~2079-6-6的日期和时间数据，精度为1分钟。数据长度为4个字节4.二进制数据二进制数据类型表示的是位数据流，一般用于存储二进制的大对象，如声音、图片、多媒体等。Binary(n)：存储固定长度的二进制数据varbinary(n)：存储可变长度的二进制数据Image图像数据类型用于存放图片、照片等，实际存储的是可变长度的二进制数据，建议用varbinary(MAX)替换。本章主要内容15.1数据类型25.2创建表35.3修改表45.4删除表55.5数据管理65.6数据完整性5.2创建表在SQLServer中建立数据库后，就可以在该数据库中创建表了。为了确定新表的结构，用户需要决定以下内容：表中的列数、每列的列名、数据类型及数据的长度、列是否可以接受空值、是否使用约束(例如：对各列的取值范围进行限制)以及确定主键和外键等。创建表可以通过SSMS窗口界面方式或T-SQL命令方式来实现。5.2.1使用窗口方式创建表下面使用窗口方式在“student”数据库中创建“学生”表，学生表的结构定义如表5.2所示。字段名称数据类型长度是否为空是否主键学号char12否是姓名varchar8否性别char2否出生日期datetime是班级代码char9否表5.2学生表结构其中学号为定长字符串，12位；姓名为变长字符串，最长8位，可存储最多4个汉字；性别定长字符串，2为，可存储一个汉字“男”或“女”。操作步骤如下：5.2.1使用窗口方式创建表步骤一1.打开SSMS，在【对象资源管理器】窗口中依次展开【数据库】→【student】，右击【表】节点，在弹出的快捷菜单中选择【新建表】命令就能打开【表设计器】，如图5.1所示。图5.1表的右键菜单窗口5.2.1使用窗口方式创建表步骤二2.设计表结构。在【表设计器】界面中可进行表结构的输入，如图5.2所示。图5.2表设计器录入窗口5.2.1使用窗口方式创建表步骤三3.设置主键。在“学号”列上右键，在弹出的菜单中选择【设置主键】命令即可将“学号”字段设置为主键，表示该列的值是唯一不重复的，如图5.4所示。图5.3设计学生表结构窗口图5.4设置主键窗口设置成功后，“学号”的前面会有一个钥匙样的图标，如图5.5所示。图5.5主键设置成功窗口5.2.1使用窗口方式创建表4.设置表名。点击工具栏的【保存】按钮，出现输入表名对话框，如图5.6所示。在其中录入“学生”，【确定】后即可看到“student”数据库中已经出现刚建立的“学生”表，如图5.7所示。步骤四图5.6输入表名称窗口图5.7创建表成功窗口5.2.2使用命令方式创建表可以用T-SQL中的CreateTable语句来创建表，基本语法格式如下：CREATETABLEtable_name(字段1数据类型列的特征,字段2数据类型列的特征,…)其中，列的特征包括：是否为空（NULL）、是否为标识列（自动编号）、是否有默认值、是否为主键等。创建表的语法有很多选项，这里仅列出最常用的一些，后续会逐渐补充。本章主要内容15.1数据类型25.2创建表35.3修改表45.4删除表55.5数据管理65.6数据完整性5.3修改表表创建后，可以根据需要进行修改，如更改表名、增加列、删除列、修改已有列的属性（列名、数据类型、是否为空值）、调整列的顺序。可以通过窗口方式或命令方式修改表。注意:表名更改后，与此相关的某些对象（如视图）以及通过表名与表相关的存储过程将无效，因此一般不建议更改表名。5.3.1使用窗口方式修改表修改“student”数据库下“学生”表的步骤如下：步骤一1.打开SSMS，在【对象资源管理器】窗口中展开【数据库】节点，选择“student”数据库，找到要修改的“学生”表。右击“学生”表，在弹出的快捷菜单中选择【设计】命令，如图5.10所示。图5.10表的右键快捷菜单窗口5.3.1使用窗口方式修改表步骤二2.在打开的【表设计器】窗口，与创建表时相同，可以对表进行修改，例如插入列、删除列、修改列名、主键、数据类型等，如图5.11所示。图5.11表设计器修改窗口5.3.1使用窗口方式修改表步骤三3.修改完成后点击【保存】按钮以使所做的修改生效。5.3.2使用命令方式修改表修改表使用altertable语句来实现。其语法格式如下：ALTERTABLEtable_name{ALTERCOLUMNcol_name/*修改已有列的属性*/{new_data_type}|ADD{[colume_definition]}[,…n]/*增加新列*/|DROPCOLUMNcol_name[,…n]/*删除列*/}【例5.5】在学生表中增加“家庭地址”列，数据类型为varchar(30),不允许为空。在查询编辑器中输入并执行以下语句即可：usestudentgoaltertable学生add家庭地址varchar(30)notnullgo【例5.6】修改学生表中的“家庭地址”列，将数据类型改为varchar(50)，允许为空，代码如下。usestudentgoaltertable学生altercolumn家庭地址varchar(50)nullgo【例5.7】删除学生表中的“家庭地址”列，代码如下。usestudentgoaltertable学生dropcolumn家庭地址go本章主要内容15.1数据类型25.2创建表35.3修改表45.4删除表55.5数据管理65.6数据完整性5.4.1使用窗口方式删除表找到要删除的表，右键，在弹出的快捷菜单中选择【删除】，出现【删除对象】对话框，单击【确定】即可，如图5.12所示。图5.12删除表窗口5.4.2使用命令方式删除表删除表使用droptable语句。语法格式：DROPTABLEtable_name其中table_name是要被删除的表名。如要删除“student”数据库中的“学生”表，在查询编辑器窗口中输入以下内容即可。usestudentgodroptable学生Go本章主要内容15.1数据类型25.2创建表35.3修改表45.4删除表55.5数据管理65.6数据完整性5.5数据管理表是存储数据的地方，在表创建完成后，就可以对表进行数据管理操作，包括：向表中添加数据、修改数据和删除数据。可以使用窗口或命令方式管理数据。图5.13编辑表数据窗口使用窗口方式进行数据管理较为简单。在SSMS中，找到要管理的“学生”表，右键，弹出快捷菜单，如图5.13所示。5.5数据管理选择【编辑前200行】，出现数据管理窗口，如图5.14所示。在其中可以进行数据记录的浏览、添加、修改和删除操作。还可以通过表右键快捷菜单中的【选择前1000行】来查看表中的数据。5.5.1插入数据插入数据的语法格式如下：INSERT[INTO]table_name/*表名*/{[(column_list)]/*列名的列表，用逗号分隔*/VALUES(expression_list)/*值的列表，用逗号分隔*/}其中：（1）[INTO]:可选关键字，可省略（2）列名：可选，如果省略，则按照表结构依次添加所有列（3）多个列或多个值之间用逗号分隔开（4）值的数目、顺序和类型要与列相对应5.5.1插入数据【例5.8】向“系部”表添加一条记录，代码如下：usestudentgoinsert系部(代码,名称)values('15','计算机系')go还可以用省略列名的写法，代码如下：usestudentgoinsert系部values('15','计算机系')go5.5.1插入数据【例5.9】向“专业“表添加一条记录，代码如下：usestudentgoinsert专业(代码,名称,系部代码)values('1501','软件工程','15')go5.5.1插入数据【例5.