第5章创建和管理表

第五章创建和管理表•表是包含数据库中所有数据的数据库对象，用来存储各种各样的信息。•在SQLServer2000中，一个数据库中最多可以创建200万个表，用户创建数据库表时，最多可以定义1024列,每行最多8060字节。在同一数据库的不同表中，可以有相同的字段，但在同一个表中不允许有相同的字段，而且每个字段都要求数据类型相同。•在创建表之前，需要定义表中的列（字段）的名称，同时还需要定义每列的数据类型和宽度。数据类型指定了在每列中存储的数据的类型，例如文本、数字、日期等等。宽度指定了可以向列中输入多少个字符或数字，也可以使用用户自定义型数据。除此之外，还需要设定表中列是否允许为空，是否有缺省值，是否设置为标识列。5.1创建表的结构一、语法格式：CREATETABLE表名(列名数据类型和宽度[NULL|NOTNULL]|[表约束][,……n])•1.几个与表结构设计相关的概念•⑴NULL与NOTNULL•在数据库中NULL是一个特殊值，表示数值未知。•NULL不同于空字符或数字0，也不同于零长度字符串。•比较两个空值或将空值与任何其它数值相比均返回未知，这是因为每个空值均为未知。空值通常表示未知、不可用或以后添加数据。•如果某个列上的空值属性为NULL，表示接受空值；空值属性为NOTNULL，表示拒绝空值。•在程序代码中，要检查空值以便只在具有有效（或非空）数据的行上进行某些计算。执行计算时消除空值很重要，因为如果包含空值列，某些计算（如平均值）会不准确。若要测试数据列中的空值，可在WHERE子句中使用ISNULL或ISNOTNULL。•注意：为了减少对已有查询或报表的维护和可能的影响，建议尽量少使用空值。•⑵缺省值（DEFAULT）•缺省值表示在用户未输入数据时列的取值。•⑶计算列（CALCULATEDCOLUMN）•一个表的某些列的取值是由基于定义该列的表达式计算得到的。该列不是存储数据的列，是表的虚拟列，该列中的值并未存储在表中，而是在运行时经过计算而得到的。•⑷标识列（IDENTITY）•IDENTITY属性使得某一列的取值是基于上一行的列值和为该列定义的步长自动生成的。IDENTITY列的值可以唯一地标识表中的一行。•定义一个IDENTITY列，必须给出一个种子值（初始值），一个步长值（增量）。在一个IDENTITY列定义后，每当向表中插入一行数据时，IDENTITY列就会自动产生下一个值。在插入数据行的语句中，不应包含IDENTITY列的值，该列的值由系统自动给出。•定义IDENTITY列时，应注意如下几点：•①每张表只允许有一个IDENTITY列。•②IDENTITY列不能被更新。•③IDENTITY列不允许有NULL值。•④IDENTITY列只能用于具有下列数据类型之一的列：INT、SMALLINT、TINYINT、NUMERIC(小数部分为0)、DECIMAL（小数部分为0）。二、创建表的示例例1创建学生表CREATETABLEstudent(sidintnotnull,namevarchar(8)notnull,sexchar(2)notnull,agesmallintnotnull,endatedatetimenull,specialityvarchar(16)null,addressvarchar(40)null)例2创建课程表CREATETABLEcourse(cidsmallintnotnull,coursevarchar(20)notnull,creditdec(4,1)null,teachervarchar(8)null)例3创建成绩单表CREATETABLEreport(Sidintnotnull,cidsmallintnotnull,resultNumeric(5,1)notNULL)●5.2设置约束使用T-SQL语句设置约束1．设置主键约束语法格式：[CONSTRAINT约束名称]PRIMARYKEY[(字段名[,……n])]例4：创建课程表course2，同时创建主键CREATETABLEcourse2(课程号smallintNOTNULLPRIMARYKEY,课程名varchar(20)NOTNULL,学分dec(4,1)NULL,任课教师varchar(8)NULL)例5：创建成绩单表report2，同时创建组合主键CREATETABLEreport2(学号intNOTNULL,课程号smallintNOTNULL,成绩numeric(5,1)NOTNULL,CONSTRAINTpk_cid_sidPRIMARYKEY(学号,课程号))2．设置唯一性约束语法格式：[CONSTRAINT约束名称]UNIQUE[(字段名[,……n])]例6：创建学生表student2，同时创建主键和唯一性约束。CREATETABLEstudent2(学号intNOTNULLPRIMARYKEY,姓名varchar(8)NOTNULLUNIQUE,性别char(2)NOTNULL,年龄smallintNOTNULL,入学日期datetimeNULL,所学专业varchar(16)NULL,家庭住址varchar(40)NULL)•例7:创建report3表,同时创建组合主键及外键约束.CREATETABLEreport3(学号intNOTNULL,课程号smallintNOTNULL,成绩numeric(5,1)NOTNULL,CONSTRAINTpk_cid_sidPRIMARYKEY(学号,课程号),CONSTRAINTFK_reportforeignkey(课程号)REFERENCEScourse2(课程号))•例8：创建学生表student3，同时创建主键和姓名列默认值“无名”的约束。CREATETABLEstudent3(学号intNOTNULLPRIMARYKEY,姓名varchar(8)DEFAULT‘无名’,性别char(2)NOTNULL,年龄smallintNOTNULL,入学日期datetimeNULL,所学专业varchar(16)NULL,家庭住址varchar(40)NULL)•例9：创建report4及计算列总分。•CREATETABLEreport4(•学号intNOTNULL,•高数smallintNOTNULL,•英语smallintNOTNULL,•数据库smallintNOTNULL,•总分as(高数+英语+数据库))5.2修改表结构的T-SQL命令语法格式：ALTERTABLEtable{[ALTERCOLUMN列名{新的数据类型}]|ADD{[列定义]}[,……n]|DROP{COLUMN列名}[,……n]}说明：请参见教材P93页例1：删除学生表（student）姓名列ALTERTABLEstudentDROPCOLUMNname例2：增加学生表（student）姓名列ALTERTABLEstudentADDnamechar(8)●5.3向表中插入数据一、INSERT语句的一般用法：INSERT[INTO]{表名|视图名}[（列名列表）]{VALUES(数据值)}例：INSERTINTOcourseVALUES(1,'数据结构',3.0,'张老师')二、INSERT…SELECT语句插入符合条件的多行例：INSERTINTOreport(sid)SELECTsidFROMstudentWHEREage18三、插入部分数据1.在INSERT子句中,指定要插入数据的列名。2.在VALUES子句中，列出与列名对应的数据，列名的顺序和数据的顺序应该完全对应。3.在INSERT子句中，没有列出的列应该至少具有这四种类型之一：⑴具有IDENTITY属性；⑵Timestamp数据类型；⑶允许NULL；⑷或者有一个指定的默认值。否则会出现错误信息。例：INSERTINTOcourse(cid,course,teacher)VALUES(3,'Java程序设计','李老师')●5.4修改表中的数据格式：UPDATE{表名|视图名}SET{列名}=列值表达式[，……n]WHERE子句注：在使用UPDATE语句时，如没有使用WHERE子句，那么就对表中所有的行进行修改。例：将所有学分加1分UPDATEcoursesetcredit=credit+1●5.5删除表中的数据格式：DELETE[FROM]表名WHERE查询子句注：如果在DELETE语句中没有指定WHERE子句，那么就将表中所有的记录全部删除。例1:删除course1中的所有数据DELETEcourse1例2:删除课程号=1的记录DELETEcourseWHEREcid=1●5.6删除表使用DROPTABLE语句删除表语句格式：DROPTABLE表名例：从当前数据库中删除course1表。DROPTABLEcourse1●5.7在表中检索数据1．检索表中的全部信息，即全部列和全部行。使用语句：Select*fromstudent2．使用select语句插入数据⑴新建另一个表course1，其结构与course相同；⑵将course表中的数据插入到course1中；INSERTcourse1Select*fromcourse●5.8使用企业管理器表的主、外键建立•需要给一个表建立每一个主键，方法是把光标置于要设定为主键的列名上，在工具栏中点击按钮，就把当前列设置成了主键。•建立好了数据库和表后，就可以把我们自己的数据输入到表中了，输入数据到表中的方法很简单，只要打开表就可以在其中输入了，打开表的方法是选中要输入数据的表，然后鼠标右键单击，选择“打开表”菜单中的“返回所有行”或“返回首行”，就可以打开表，然后只要用户把光标置于第一个空白行就可以添加数据了(如图b)；或者用户也可以在操作菜单中选择“打开表”菜单下的相应菜单项来打开表。●1．分别对student、course、report表建立主键。见操作示例2．建立report表与student及course的外键连接见操作示例5.9创建关系图见操作示例。