SQL 第06章 表的创建与管理

第6章

表的创建和管理-P45

6.1创建表6.2表结构的修改6.3表的重命名与删除6.4向表中添加、更新、删除数据

6.5数据完整性的概念与实施方法练习返回目录26.1创建表

表是存储各种数据的载体，具有以下特点：l在特定的数据库中表名是唯一的，在特定的表中，列名是唯一的，但不同的表可以有相同的列名。l表是由行和列组成的，行又称为记录，列被称为字段。行和列的次序是任意的；l每个表最多1024列，每行最多8060字节的用户数据；l数据行在表中是唯一的，行的唯一性可以通过定义主键来实现。在一个表中，不允许有两个完全相同的行存在。36.1创建表

6.1.1设计表的结构定义表中的列（字段）的名称、每列的数据类型和宽度。数据类型指定了在每列中存储的数据的类型，例如文本、数字、日期等等。宽度指定了可以向列中输入多少个字符或数字，也可以使用用户自定义型数据。除此之外，还需要设定表中列是否允许为空，是否有缺省值，是否设置为标识列。41.几个与表结构设计相关的概念⑴NULL与NOTNULL在数据库中NULL是一个特殊值，表示数值未知。NULL不同于空字符或数字0，也不同于零长度字符串。比较两个空值或将空值与任何其它数值相比均返回未知，如果某个列上的空值属性为NULL，表示接受空值；空值属性为NOTNULL，表示拒绝空值。51.几个与表结构设计相关的概念⑴NULL与NOTNULL

在程序代码中，要检查空值，以便只在具有有效（或非空）数据的行上进行某些计算。执行计算时消除空值很重要，因为如果包含空值列，某些计算（如平均值）会不准确。若要测试数据列中的空值，可在WHERE子句中使用ISNULL或ISNOTNULL。例如：select*frombookwherepriceisnull

注意：为了减少对已有查询或报表的维护和可能的影响，建议尽量少使用空值。

6⑵缺省值（DEFAULT）缺省值表示在用户未输入数据时列的取值。⑶计算列（CALCULATEDCOLUMN）一个表的某些列的取值是由基于定义该列的表达式计算得到的。该列不是存储数据的列，是表的虚拟列，该列中的值并未存储在表中，而是在运行时经过计算而得到的。7⑷标识列（IDENTITY）IDENTITY属性使得某一列的取值是基于上一行的列值和为该列定义的步长自动生成的。IDENTITY列的值可以唯一地标识表中的一行。定义一个IDENTITY列，必须给出一个种子值（初始值），一个步长值（增量）。在一个IDENTITY列定义后，每当向表中插入一行数据时，IDENTITY列就会自动产生下一个值。在插入数据行的语句中，不应包含IDENTITY列的值，该列的值由系统自动给出。8定义IDENTITY列时，应注意如下几点：①每张表只允许有一个IDENTITY列。②IDENTITY列不能被更新。③IDENTITY列不允许有NULL值。④IDENTITY列只能用于具有下列数据类型之一的列：INT、SMALLINT、TINYINT、NUMERIC(小数部分为0)、DECIMAL（小数部分为0）。92.表结构设计示例以teacher表结构设计为例，teacher表有9个列，其中，teacher_id为主键，性别采用BIT数据类型，0表示女教师，1表示男教师，默认值为1，邮政编码使用前面第三章定义过的用户自定义的数据类型postalcode。具体查看jwgl数据库106.1.2利用Transact-SQL语言创建表语法格式如下:CREATETABLEtable_name(column_namedata_type[null|notnull][,…n])在上述语法形式中：table_name：为新创建的表指定的名字。column_name：列名。data_type

：列的数据类型和宽度。null|notnull

：指定该列是否允许为空。[,…n]：允许创建多个字段。11【例6-1】用CREATETABLE语句创建表department。该表有3个字段：系号（department_id）、系名（department_name）、系主任（department_header）。

usejwgl

go

CREATETABLEdepartment

(department_idchar(6)NOTNULL,department_namenvarchar(20)NOTNULL,department_headernvarchar(8)NOTNULL

)表创建完成后，如要查看表的属性，可执行系统存储过程sp_help<表名>。例如：sp_helpdepartment126.1.3利用SQLServerManagementStudio创建表以创建数据库JWGL中表teacher为例，下面进行使用SQLServerManagementStudio创建表的介绍。1)进入SQLServerManagementStudio，分别点击“数据库”、“JWGL”、“表”，再点击菜单“操作”、“新建表”（或右击“表”，在弹出的快捷菜单上点击“新建表”）;2)依次输入字段名teacher_id、teacher_name、sex、birth、department_id、profession、telephone、home_addr、postal_code及相应的数据类型、字段长度等设置值。注意，postal_code字段的数据类型使用用户自定义的数据类型postal_code;136.1.3利用SQLServerManagementStudio创建表3)设置计算列与标识列。l

标识列的设置：选择要设置为标识列的列，在对应“列属性”的设计框中单击“标识规范”项左边的“+”，在展开的“公式”项对应的“标识增量”和“标识种子”对应的输入栏中输入标识增量和标识种子值即可。l

计算列的设置：选择要设置为计算列的列，在对应“列属性”的设计框中单击“计算所得的列规范”项左边的“+”，在展开的“公式”项对应的输入栏输入公式,例如a+b146.2表结构的修改修改表的结构有两种方法：1.使用SQLServerManagementStudio修改2.使用ALTERTABLE命令156.2表结构的修改6.2.1使用ALTERTABLE语句修改表结构使用ALTERTABLE语句可以为表添加或删除列，也可以修改列性质。1.ALTERTABLE语句的语法形式：ALTERTABLEtable{[ALTERCOLUMNcolumn_name{new_data_type[(precision[,scale])]

[NULL|NOTNULL]}]

|ADD

{[<add_column_nameadd_data_type>]}[,...n]

|DROPCOLUMN

{drop_colum_name}[,...n]}16在以上语法形式中：column_name：要修改的列名。

new_data_type：要修改列的新数据类型。

precision：是指定数据类型的精度。

scale：是指定数据类型的小数位数。

add_column_name：要添加到表中的列名。

add_data_type

：要添加到表中的列的数据类型。

drop_colum_name：要从表中删除的列名

[,...n]：可以有多个列。172.ALTERTABLE语句使用示例【例6-2】将表book的book_name字段的长度先改为32，再改回30。usejwglaltertable

bookaltercolumn

book_namevarchar(32)notnullaltertablebookaltercolumn

book_namevarchar(30)notnull182.ALTERTABLE语句使用示例【例6-3】将表book增加两个字段：book_copy_no，publish_dateuse

jwglGoAltertablebookaddbook_copy_novarchar(10),publish_datesmalldatetime注意：添加列，无需加column19【例6-4】将表book的新增加的两个字段book_copy_no，publish_date删除use

jwglgoAltertablebookdropcolumnbook_copy_no,publish_date206.2.2使用SQLServerManagementStudio修改表的结构使用SQLServerManagementStudio修改表结构的步骤如下：①启动SQLServerManagementStudio。②分别点击“数据库”、“JWGL”、“表”，SQLServerManagementStudio右边区域显示数据库JWGL中所有的表。③右击要修改的表（假设要修改的表为teacher），在系统弹出的快捷菜单点击“设计表”。21④如要增加一列，先选择新增加列的新位置，然后右击鼠标，在系统弹出的快捷菜单点击“插入列”，这时，窗口会在选定列的前面出现一个空行，你只要在空行里输入相应的列信息就可以了。⑤如要删除一列，可右击要删除的列，在系统弹出的快捷菜单点击“删除列”。⑥如要更改列的名称、数据类型、宽度可以在此窗口上直接修改。⑦修改完成后，点击工具栏上的保存按钮“”即可。226.3表的重命名与删除使用SQLServerManagementStudio或T-SQL语句可以对数据库中的不再使用的表进行删除，也可以对表进行重新命名。但要注意，对于要删除的表必须确认是没用的表，而重命名一个表将会导致引用该表的存储过程、视图、触发器无效。所以，对于表的重命名与删除要慎重。236.3表的重命名与删除1.使用T-SQL语言进行重命名表使用系统存储过程sp_rename。语法形式如下：

sp_rename

table_old_name,table_new_name如sp_renamebook1,book2删除表使用DROPTABLE命令。DROPTABLE命令的语法形式如下：

DROPTABLE

table_name如droptablebook22.使用SQLServerManagementStudio进行进入SQLServerManagementStudio、分别点击“数据库”、“JWGL”、“表”，右击要删除或重命名的表，在系统弹出的快捷菜单上，分别点击“重命名表”或“删除表”即可。246.4向表中添加、更新、删除数据6.4.1使用T-SQL语言进行1.插入数据向表中插入数据使用的是INSERTINTO语句。INSERTINTO语句的语法形式是：

INSERT[INTO]{table_name|view_name}[(column_list）]{VALUES(values_list)|select_statement}其中：

table_name|view_name

：要插入数据的表名及视图名。

column_list

：要插入数据的字段名。

values_list

：与column_list相对应的字段的值。

select_statement

：通过查询向表插入数据的条件语句。25在使用INSERTINTO语句向表插入数据时要注意以下几点：①当向表中所有列都插入新数据时，可以省略列名表，但是必须保证VALUES后的各数据项位置同表定义时顺序一致。②要保证表定义时的非空列必须有值，即使这个非空列没有出现在插入语句中，也必须如此。③字符型和日期型值插入时要加入单引号’’。④没有列出的数据类型应该具有以下属性之一：IDENTITY属性、Timestamp数据类型、具有null属性或者有一个缺省值。对于具有IDENTITY属性的列，其值由系统给出，用户不必往表中插入数据。26【例6-5】向teacher表中插入一行数据，具体数据如下：教师标识：dep04_006，姓名：李微,性别：女（0）,出生年月：5-31-1965,系号：dep_04,职位：讲师,联系电话：8802976,家庭住址：南京古平岗35号,邮政编码：210008USE

jwglINSERTINTO

teacher(teacher_id,teacher_name,sex,birth,department_id,profession,telephone,home_addr,postalcode)VALUES('dep04_006','李微',0,'5-31-1965','dep_04','讲师','8802976','南京古平岗35号','210008')27在用INSERTINTO插入数据时，也可以省略字段名列表，但使用时一定要小心，因为在INSERTINTO语句中省略了字段名列表，所以，VALUES后数值的顺序一定要与表中定义列的顺序相同，否则，要么插入数据不成功，要么插入数据成功但结果不正确。一行数据所造成的问题很难发现，但危害很大。因此，建议插入数据时要写上字段名表。28使用INSERT语句一次只能插入一行数据，而在INSERT语句中加入查询子句SELECT，通过SELECT子句从其他表或视图中选出数据，再将其插入到指定的表中，可以一次插入多行数据。Inserttable_nameSelectcolumn_listFromtable_listWherecondition例：insertstudent11Select*fromstudentwhereclass_id=‘g99402’注意首先要创建一个跟student结构一样的空表student11，可以用脚本生成方法创建表29

使用SELECT子句插入数据时应该注意以下几点：使用SELECT子句插入数据时，table_name和table_1ist可以是相同的表，也可以是不同的表要插入的数据表table_name必须已经存在，也就是说，不能向不存在的表中插入数据要插入数据的表的列和SELECT子句中列的数量和顺序必须相同，列的数据类型也要相同，如果不相同，SQLServer也要能自动转化。302.更新数据SQLServer中，对数据的修改是通过UPDTAE语句实现的。使用UPDATE语句可以一次修改一行数据，也可以一次修改多行数据，甚至整张表的数据。无论哪种修改，都要求修改前后的数据类型和数据个数相同。312.更新数据它的一般语法是：UPDATE{table_name|view_name}SET{column_list|variable_list}=expression[WHEREsearch_conditions]在这个语法形式中：

table_name|view_name

：要更新数据的表名或视图名。

column_list|variable_list

：要更新数据的字段列表或变量列表。expression：更新后新的数据值。WHEREsearch_conditions

更新数据所应满足的条件。应该指出的是，UPDATE只能在一张表上操作，并且更新后的数据必须满足表原先的约束条件,否则，数据更新将不会成功。32【例6-6】将示例5插入的一行数据中李微的职位由“讲师”改为“副教授”。USEjwglUPDATEteacherSETpr