《SQL第06章表的创建与管理.ppt》由会员分享，可在线阅读，更多相关《SQL第06章表的创建与管理.ppt（61页珍藏版）》请在金锄头文库上搜索。
1、第第6章章 表的创建和管理表的创建和管理 6.1 创建表创建表 6.2 表结构的修改表结构的修改 6.3 表的重命名与删除表的重命名与删除 6.4 向表中添加、更新、删除数据向表中添加、更新、删除数据 6.5 数据完整性的概念与实施方法数据完整性的概念与实施方法 练习练习返回目录返回目录6.1 创建表创建表 表是包含数据库中所有数据的数据库对象，用来存储各种各样的信息。 在SQL Server 2000中，一个数据库中最多可以创建200万个表，用户创建数据库表时，最多可以定义1024列,每行最多8060字节。在同一数据库的不同表中，可以有相同的字段，但在同一个表中不允许有相同的字段，而且每个字
2、段都要求数据类型相同。 6.1.1 设计表的结构设计表的结构在创建表之前，需要定义表中的列（字段）的名称，同时还需要定义每列的数据类型和宽度。数据类型指定了在每列中存储的数据的类型，例如文本、数字、日期等等。宽度指定了可以向列中输入多少个字符或数字，也可以使用用户自定义型数据。除此之外，还需要设定表中列是否允许为空，是否有缺省值，是否设置为标识列。21. 几个与表结构设计相关的概念几个与表结构设计相关的概念 NULL与与 NOT NULL在数据库中NULL是一个特殊值，表示数值未知。NULL不同于空字符或数字0，也不同于零长度字符串。比较两个空值或将空值与任何其它数值相比均返回未知，这是因为每
3、个空值均为未知。空值通常表示未知、不可用或以后添加数据。如果某个列上的空值属性为NULL，表示接受空值；空值属性为NOTNULL，表示拒绝空值。在程序代码中，要检查空值以便只在具有有效（或非空）数据的行上进行某些计算。执行计算时消除空值很重要，因为如果包含空值列，某些计算（如平均值）会不准确。若要测试数据列中的空值，可在WHERE子句中使用ISNULL或ISNOTNULL。注意：为了减少对已有查询或报表的维护和可能的影响，建议尽量少使用空值。3 缺省值（缺省值（DEFAULT）缺省值表示在用户未输入数据时列的取值。 计算列（计算列（CALCULATED COLUMN）一个表的某些列的取值是由基
4、于定义该列的表达式计算得到的。该列不是存储数据的列，是表的虚拟列，该列中的值并未存储在表中，而是在运行时经过计算而得到的。 标识列（标识列（IDENTITY）IDENTITY属性使得某一列的取值是基于上一行的列值和为该列定义的步长自动生成的。IDENTITY列的值可以唯一地标识表中的一行。定义一个IDENTITY列，必须给出一个种子值（初始值），一个步长值（增量）。在一个IDENTITY列定义后，每当向表中插入一行数据时，IDENTITY列就会自动产生下一个值。在插入数据行的语句中，不应包含IDENTITY列的值，该列的值由系统自动给出。4定义定义IDENTITY列时，应注意如下几点：列时，应
5、注意如下几点：每张表只允许有一个IDENTITY列。IDENTITY列不能被更新。IDENTITY列不允许有NULL值。IDENTITY列只能用于具有下列数据类型之一的列：INT、SMALLINT、TINYINT、NUMERIC(小数部分为0)、DECIMAL（小数部分为0）。通过函数ident_seed(table_name)可获得seed值，通过ident_incr（table_name）可获得步长值，使用全局变量identity可以返回标识列的数据。2. 表结构设计示例表结构设计示例以teacher表结构设计为例，teacher表有9个列，其中，teacher_id为主键，性别采用BIT
6、数据类型，0表示女教师，1表示男教师，默认值为1，邮政编码使用前面第三章定义过的用户自定义的数据类型postalcode。整个表的列性质如表6-1：(P91)56.1.2 6.1.2 利用利用Transact-SQL语言创建表语言创建表语法格式如下:CREATETABLEtable_name(column_namedata_typenull|notnull,n)在上述语法形式中：table_name：为新创建的表指定的名字。column_name：列名。data_type：列的数据类型和宽度。null|notnull：指定该列是否允许为空。,n：允许创建多个字段。6【例6-1】用CREATET
7、ABLE语句创建表department。该表有三个字段：系号（department_id）、系名（department_name）、系主任（department_header）。usejwglgoCREATETABLEdepartment(department_idchar(6)NOTNULL,department_namenvarchar(20)NOTNULL,department_headernvarchar(8)NOTNULL)表创建完成后，如要查看表的属性，可执行系统存储过程sp_help。76.1.3 6.1.3 利用企业管理器创建表利用企业管理器创建表以创建数据库JWGL中表tea
8、cher为例，下面进行使用企业管理器创建表的介绍。进入企业管理器，分别点击“数据库”、“JWGL”、“表”，再点击菜单“操作”、“新建表”（或右击“表”，在弹出的快捷菜单上点击“新建表”）;依次输入字段名teacher_id、teacher_name、sex、birth、department_id、profession、telephone、home_addr、postal_code及相应的数据类型、字段长度等设置值。注意，postal_code字段的数据类型使用用户自定义的数据类型postal_code;8设置计算列与标识列:计算列的设置：选择要设为计算列的字段，在公式对应的输入栏输入公式标识
9、列的设置：在表的任意地方按右键，点击“属性”，进入表的“属性”窗口，如图6-2，选择“表”选项卡，在“表的标识列”栏选择要设为标识列的字段，点击“关闭”回到创建新表的窗口6-1，分别输入“标识种子”和“标识递增量”即可最后点击工具栏上的存盘按钮“”即可。表创建完成后，如要查看表的属性，可在企业管理器中右击要查看的表，点击“属性”即可。本章首页本章首页本章首页本章首页96.2表结构的修改在表创建之后，由于某些原因，比如客户需求的变化，或对用户需求分析的不恰当，可能需要对表的结构进行修改。有关表的修改操作包括：增加和删除列、改变列的名称、宽度、数据类型、改变表的名称等。修改表的结构有两种方法：1.
10、使用企业管理器修改2.使用ALTERTABLE命令106.2.1 使用使用ALTER TABLE语句修改表结构语句修改表结构使用ALTERTABLE语句可以为表添加或删除列，也可以修改列性质。1. ALTER TABLE语句的语法形式：语句的语法形式：ALTERTABLEtableALTERCOLUMNcolumn_namenew_data_type(precision, scale)NULL|NOTNULL|ADD,.n|DROPCOLUMNdrop_colum_name,.n11在以上语法形式中：column_name：要修改的列名。new_data_type：要修改列的新数据类型。pre
11、cision：是指定数据类型的精度。scale：是指定数据类型的小数位数。add_column_name：要添加到表中的列名。add_data_type：要添加到表中的列的数据类型。drop_colum_name：要从表中删除的列名,.n：可以有多个列。122. ALTER TABLE语句使用示例语句使用示例【例6-2】将表book的book_name字段的长度先改为32，再改回30。usejwglaltertablebookaltercolumnbook_namevarchar(32)notnullaltertablebookaltercolumnbook_namevarchar(30)no
12、tnull【例6-3】将表book增加两个字段：book_copy_no，publish_dateusejwglgoaddbook_copy_novarchar(10),publish_datesmalldatetime13【例6-4】将表book的新增加的两个字段book_copy_no，publish_date删除usejwglgodropcolumnbook_copy_no,public_date6.2.2 使用企业管理器修改表的结构使用企业管理器修改表的结构使用企业管理器修改表结构的步骤如下：启动企业管理器。分别点击“数据库”、“JWGL”、“表”，企业管理器右边区域显示数据库JWGL
13、中所有的表。右击要修改的表（假设要修改的表为teacher），在系统弹出的快捷菜单点击“设计表”，系统将弹出如图6-3的窗口。14如要增加一列，先选择新增加列的新位置，然后右击鼠标，在系统弹出的快捷菜单点击“插入列”，这时，窗口会在选定列的前面出现一个空行，你只要在空行里输入相应的列信息就可以了。如要删除一列，可右击要删除的列，在系统弹出的快捷菜单点击“删除列”。如要更改列的名称、数据类型、宽度可以在此窗口上直接修改。修改完成后，点击工具栏上的保存按钮“”即可。本章首页本章首页本章首页本章首页156.3表的重命名与删除使用企业管理器或T-SQL语句可以对数据库中的不再使用的表进行删除，也可以对
14、表进行重新命名。但要注意，对于要删除的表必须确认是没用的表，而重命名一个表将会导致引用该表的存储过程、视图、触发器无效。所以，对于表的重命名与删除要慎重。1. 使用使用T-SQL 语言进行语言进行重命名表使用系统存储过程sp_rename。语法形式如下： sp_rename table_old_name , table_new_name删除表使用DROPTABLE命令。DROPTABLE命令的语法形式如下：DROP TABLE table_name2. 使用企业管理器进行使用企业管理器进行进入企业管理器、分别点击“数据库”、“JWGL”、“表”，右击要删除或重命名的表，在系统弹出的快捷菜单上，
15、分别点击“重命名表”或“删除表”即可。本章首页本章首页本章首页本章首页166.4向表中添加、更新、删除数据管理表中的数据有两种方式：T-SQL语句和企业管理器。6.4.1 使用使用T-SQL 语言进行语言进行1. 插入数据插入数据向表中插入数据使用的是INSERTINTO语句。INSERTINTO语句的语法形式是：INSERTINTOtable_name|view_namecolumn_list）VALUES(values_list)|select_statement其中：table_name|view_name：要插入数据的表名及视图名。column_list：要插入数据的字段名。value
16、s_list：与column_list相对应的字段的值。select_statement：通过查询向表插入数据的条件语句。17在使用在使用INSERT INTO语句向表插入数据时要注意以下几语句向表插入数据时要注意以下几点：点：当向表中所有列都插入新数据时，可以省略列名表，但是必须保证VALUES后的各数据项位置同表定义时顺序一致。要保证表定义时的非空列必须有值，即使这个非空列没有出现在插入语句中，也必须如此。字符型和日期型值插入时要加入字符型和日期型值插入时要加入单引号单引号。没有列出的数据类型应该具有以下属性之一：IDENTITY属性、Timestamp数据类型、具有null属性或者有一个
17、缺省值。对于具有IDENTITY属性的列，其值由系统给出，用户不必往表中插入数据。18【例6-5】向teacher表中插入一行数据，具体数据如下：教师标识：dep04_006，姓名：李微,性别：女（0）,出生年月：5-31-1965,系号：dep_04,职位：讲师,联系电话：8802976,家庭住址：南京古平岗35号,邮政编码：210008USEjwglGOINSERTINTOteacher(teacher_id,teacher_name,sex,birth,department_id,profession,telephone,home_addr,postalcode)VALUES(dep04
18、_006,李微,0,5-31-1965,dep_04,讲师,8802976,南京古平岗35号,210008)19在用INSERTINTO插入数据时，也可以省略字段名列表，但使用时一定要小心，因为在INSERTINTO语句中省略了字段名列表，所以，VALUES后数值的顺序一定要与表中定义列的顺序相同，否则，要么插入数据不成功，要么插入数据成功但结果不正确。一行数据所造成的问题很难发现，但危害很大。因此，建议插入数据时要写上字段名表。使用INSERT语句一次只能插入一行数据，而在INSERT语句中加入查询子句SELECT，通过SELECT子句从其他表或视图中选出数据，再将其插入到指定的表中，可以一
19、次插入多行数据。 使用使用SELECT子句插入数据时应该注意以下几点：子句插入数据时应该注意以下几点：使用SELECT子句插入数据时，table_name和table_1ist可以是相同的表，也可以是不同的表要插入的数据表table_name必须已经存在，也就是说，不能向不存在的表中插入数据要插入数据的表的列和SELECT子句中列的数量和顺序必须相同，列的数据类型也要相同，如果不相同，SQLServer也要能自动转化。202. 更新数据更新数据SQLServer中，对数据的修改是通过UPDTAE语句实现的。使用UPDATE语句可以一次修改一行数据，也可以一次修改多行数据，甚至整张表的数据。无论
20、哪种修改，都要求修改前后的数据类型和数据个数相同。它的一般语法是：UPDATEtable_name|view_nameSETcolumn_list|variable_list=expressionWHEREsearch_conditions在这个语法形式中：table_name|view_name：要更新数据的表名或视图名。column_list|variable_list：要更新数据的字段列表或变量列表。21expression：更新后新的数据值。WHEREsearch_conditions更新数据所应满足的条件。应该指出的是，UPDATE只能在一张表上操作，并且更新后的数据必须满足表原先的
21、约束条件,否则，数据更新将不会成功。【例6-6】将示例5插入的一行数据中李微的职位由“讲师”改为“副教授”。USEjwglGOUPDATEteacherSETprofession=副教授WHEREteacher_id=dep04_006223. 删除数据删除数据如果表中的数据不再需要时，可以将其删除，以释放存储空间。对表中数据的删除是用DELETE语句实现的。DELETE语句的语法形式是语句的语法形式是：DELETEFROMtable_nameWHEREsearch_conditions若不加WHERE子句，将会删除表中的所有的记录，所以使用时应特别小心。【例例6-7】将示例5插入的数据从te
22、acher表中删除掉。USEjwglGODELETEFROMteacherWHEREteacher_id=dep04_006236.4.2 使用企业管理器进行使用企业管理器进行进入企业管理器、分别点击“数据库”、“JWGL”、“表”，右击要添加数据的表，在系统弹出的快捷菜单上，分别点击“打开表”、返回“所有行”，系统将弹出如图6-4的窗口。P98可以把鼠标定位在窗口任一行的任一列上，对此单元格的数据进行修改，也可以右击单元格，在系统弹出的快捷菜单上选择“删除”将某条记录删除掉。如果把光标定位在一个新的空行上，就可以添加新的数据了。但要注意，表中的列经常是带有约束的，比如数据类型、空值约束、CH
23、ECK约束（如取值范围）等，无论修改数据，还是插入新数据，都必须遵循约束的限制，否则，修改或插入数据就可能不成功。本章首页本章首页本章首页本章首页246.5数据完整性的概念与实验方法6.5.1 数据完整性概念数据完整性概念数据完整性是指数据的正确性、完备性和一致性，是衡量数据库质量好坏的重要标准。在用INSERT、DELETE、UPDATE语句修改数据库内容时，数据的完整性可能会遭到破坏。可能会存在下列情况：无效的数据被添加到数据库的表中。如：将学生考试成绩输入成负数；SQLServer提供了对数据库中表、列实施数据完整性的方法。对表进行设计数据完整性有两个重要内容：标识列的有效值和确定如何强
24、制列中的数据完整性。251. 域完整性域完整性域完整性是指一个列的输入有效性，是否允许空值。强制域完整性的方法有：限制类型（通过设定列的数据类型）、格式（通过CHECK约束和规则）或可能值的范围（通过FOREIGNKEY约束、CHECK约束、DEFAULT定义、NOTNULL定义和规则）。2. 实体完整性实体完整性实体完整性是指保证表中所有的行唯一。实体完整性要求表中的所有行都有一个唯一标识符。这个唯一标识符可能是一列，也可能是几列的组合，称之为主键。也就是说，表中主键在所有行上必须取值唯一。强制实体完整性的方法有：索引、UNIQUE约束、PRIMARYKEY约束或IDENTITY属性。3.
25、参照完整性参照完整性参照完整性也叫引用完整性。参照完整性总是保证主关键字（被引用表）和外部关键字（引用表）之间的参照关系。它涉及两个或两个以上表数据的一致性维护。266.5.2 数据完整性实施方法数据完整性实施方法1. 声明型数据完整性声明型数据完整性声明型数据完整性一般在对象创建时定义，由SQLServer强制实施，通常使用约束、缺省值和规则来实现使用约束、缺省值和规则来实现。实现基本数据完整性的首选方法是使用声明型数据完整性。声明型数据完整性作为数据库对象说明的一部分在语法中实现，在CREATETABLE和ALTERTABLE定义中使用CONSTRAINT、DEFAULT等语句限制表中的值
26、。使用这种方法实现数据完整性简单且不易出错，系统直接将实现数据完整性的要求定义在表和列上。2. 过程型数据完整性过程型数据完整性过程型数据完整性是指由某个过程引发而实施的数据完整性。一般先写出实施数据完整性的条件，再写出强制该条件所执行的用于保证数据完整性的脚本。通常由触发器和由触发器和存储过程存储过程实现。过程型数据完整性也可以在客户机和服务器上使用其它编程语言和工具实现。276.5.3 6.5.3 约束约束1.约束的定义和类型约束的定义和类型约束是SQLServer提供的自动保持数据库完整性的一种方法。列列级级约约束束：列级约束是行定义的一部分，只能够应用在一列上。表表级级约约束束：表级约
27、束的定义独立于列的定义，可以应用在一个表中的多列上。约束有六六种种类类型型：非非空空约约束束、缺缺省省约约束束、检检查查约约束束、主主键键约束约束、唯一约束唯一约束、外键约束外键约束（参照约束）。非空约束（非空约束（NOT NULL）：表中的某些列必须存在有效值，不允许有空值出现。这是最简单的数据完整性约束，可在建表时将该列声明为NOTNULL即可。缺省约束（缺省约束（DEFALUT CONSTRAINTS）：当向数据库中的表插入数据时，如果用户没有明确给出某列的值，SQLServer自动为该列输入指定值。28检查约束（检查约束（CHECK CONSTRAINTS）：限制插入列中的值的范围。主
28、键约束（主键约束（PRIMARY KEY CONSTRAINTS）：要求主键的列上没有两行具有相同值，也没有空值。唯一约束（唯一约束（UNIQUE CONSTRAINTS）：要求表中所有行在指定的列上没有完全相同的列值。外键约束（外键约束（FROEIGN KEY CONSTRAINTS）：要求正被插入或更新的列（外键）的新值，必须在被参照表（主表）的相应列（主键）中已经存在。29不同的约束强制不同类型的数据完整性。表6-2给出了两者的对应关系。P10完整性类型约束类型域完整性非空约束DEFAULTCHECK实体完整性PRIMARYKEYUNIQUE参照完整性FOREIGNKEY302.使用T-
29、SQL语言创建、管理约束(1)使用CREATETABLE语句创建约束使用CREATETABLE语句创建约束的一般语法如下：CREATETABLEtable_name（column_namedata_typeCONSTRAINTconstraint_namePRIMARYKEYCLUSTERED|NONCLUSTERED|UNIQUECLUSTERED|NONCLUSTERED|FOREIGNKEYREFERENCESref_table(ref_column)|DEFAULTconstant_expression|CHECK(logical_expression),.n)31其中：table_n
30、ame：创建约束所在的表的名称。column_name：列名。data_type：数据类型。constraint_name：约束名。在创建、修改、实现约束时注意以下几点：可以在已有的表上创建、修改、删除约束，而不必删除并重建表。可以在应用程序中创建错误检查逻辑，测试是否违反约束。在给表添加约束时，SQL将验证表中已有数据是否满足正在添加的约束。32缺省约束缺省约束是指当向数据库中的表插入数据时，如果用户没有明确给出某列的值时，SQLServer自动为该列输入的值，缺省约束用于实现域的完整性缺省约束用于实现域的完整性。创建DEFAULT约束时应考虑以下因素：DEFAULT约束只能用于INSERT
31、语句不能用于具有IDENTITY属性的列每列只能有一个DEFAULT约束【例例6-8】为数据库JWGL中表teacher的SEX（性别）字段创建一个缺省约束，缺省值为1（男）USEJWGLGOALTERTABLEteacherADDCONSTRAINTdefault_sexDEFAULT1FORSEX33 检查约束检查约束检查约束用来指定某列可取值的清单、可取值的集合或可取值的范围。检查约束主要用于实现域完整性。创建创建CHECK约束时应考虑以下因素：约束时应考虑以下因素：当向数据库中的表执行插入或更新操作时，检查插入的新列值是否满足CHECK约束条件不能在具有IDENTITY属性，或具有ti
32、mestamp或uniqueidentifier数据类型的列上放置CHECK约束CHECK条件不能含有子查询【例例6-9】为数据库JWGL中表student_course的GRADE（成绩）字段创建一个检查约束，使得GRADE的值在0-100之间。USEJWGLGOALTERTABLEstudent_courseADDCONSTRAINTcheck_gradeCHECK(grade=0andgrade=0andgrade=0andgrade=100点击“确定”,即可完成规则grade_rule1的创建。56 默认值的绑定与解除默认值的绑定与解除假设将默认值grade_rule1绑定到stude
33、nt_course表的grade列。启动企业管理器。分别点击“数据库”、“JWGL”。点击“规则”，右击右边窗格里的规则grade_rule1，在系统弹出的快捷菜单上点击“属性”，系统将弹出如图6-14的窗口。点击“绑定UDT”可将规则grade_rule1绑定到用户自定义变量，点击“绑定列”可将默认值规则grade_rule1绑定指定表的列。这里，点击“绑定列”，系统将弹出如图6-15的窗口。在图6-15绑定规则的窗口“表”对应的下拉框上选择表student_course，在“未绑定的列”窗格上选择列grade，点击“添加”。点击“确定”，即可完成规则的绑定。如需解除某个绑定，只需点击图6-
34、15右边的窗格里的某个绑定，点击“删除”、“确定”即可。57 规则的删除规则的删除删除默认值前必须解除该默认值的所有绑定。具体步骤如下：启动企业管理器。分别点击“数据库”、“JWGL”。点击“规则”，右击右边窗格里的某个规则，在系统弹出的快捷菜单上点击“删除”即可。6.5.6 使用标识列使用标识列1. 标识列的定义标识列的定义DENTITY属性使得某一列的取值是基于上一行的列值和为该列定义的步长自动生成的。IDENTITY列的值可以唯一地标识表中的一行，可以用于实现实体完整性。有关DENTITY列属性的特点及定义DENTITY列时的注意事项，本章第一节已经介绍，这里不再重复。582. 用用T-
35、SQL语言定义标识列语言定义标识列 定义标识列的语法：定义标识列的语法：ALETRTABLEtable_nameADDcolumn_namedata_typeIDENTITY(seed,increment)其中：table_name：要添加标识列的表名。column_name：要添加的标识列的名称。data_type：要添加的标识列的数据类型。Seed：标识列的种子值。Increment：标识列的步长。59 定义标识列的示例定义标识列的示例【例6-15】给表class增加一个标识列class_no，种子值：1，步长：1。USEJWGLGOALTERTABLEclassADDclass_nointIDENTITY(1,1)3. 用企业管理器定义标识列用企业管理器定义标识列使用企业管理器定义标识列的方法在6.1.2已经介绍，这里不再重复。本章首页本章首页本章首页本章首页60作业1.用T-SQL语言在jwgl数据库中创建学生表(student),设student_id为主键,性别为男,出生日期大于1984-1-1;2.给该成绩表插入一条记录;3.修改该表任意一个记录的出生日期;4.删除学号为g9940201的一条信息;本章首页本章首页本章首页本章首页61
