实验3：SQL SERVER数据表管理

实验3：SQLSERVER数据表管理一、实验目的1、学会在SSMS图形界面和T-SQL语句创建表。二、实验内容1、创建各个表在实验2建立的数据库EDUC中，根据分析需要如下几个表：表2.1student表（学生信息表）字段名称类型宽度允许空值主键说明snochar8NOTNULL是学生学号snamechar8NOTNULL学生姓名sexchar2NULL学生性别nativechar20NULL籍贯birthdaysmalldate4NULL学生出生日期dnochar6NULL学生所在院系spnochar8NULL专业代码（外键）classnochar4NULL班级号entimesmalldate4NULL学生入校时间homevarchar40NULL学生家庭住址telvarchar40NULL学生联系电话表2.2course表（课程信息表）字段名称类型宽度允许空值主键说明cnochar10NOTNULL是课程编号spnochar8NULL专业代码（外键）cnamechar20NOTNULL课程名称ctnotinyint1NULL课程类型编号（外键）experimenttinyint1NULL实验时数lecturetinyint1NULL授课学时semestertinyint1NULL开课学期credittinyint1NULL课程学分表2.3student_course表（学生选课成绩表）字段名称类型宽度允许空值主键说明snochar8NOTNULL是学生学号tcidsmallint2NOTNULL是上课编号scoretinyint1NULL学生成绩表2.4teacher表（教师信息表）字段名称类型宽度允许空值主键说明tnochar8NOTNULL是教师编号tnamechar8NOTNULL教师姓名sexchar2NULL教师性别birthdaysmalldate4NULL教师出生日期dnochar6NULL教师所在院系pnotinyint1NULL教师职务或职称编号homevarchar40NULL教师家庭住址zipcodechar6NULL邮政编码telvarchar40NULL联系电话emailvarchar40NULL电子邮件表2.5teacher_course表（教师上课课表）字段名称类型宽度允许空值主键说明tcidsmallint2NOTNULL是上课编号tnochar8NULL教师编号（外键）spnochar8NULL专业代码（外键）classnochar4NULL班级号cnochar10NOTNULL课程编号（外键）semesterchar6NULL学期schoolyearchar10NULL学年classtimevarchar40NULL上课时间classroomvarchar40NULL上课地点weektimetinyint1NULL每周课时数使用SSMS建立EDUC数据库中的各个表根据如上表结构用SQL语句创建各表，并将创建表的SQL语句以.sql文件的形式保存在磁盘上。使用SSMS图形界面删除建立的表Student_course,Student和Course，在查询分析器中用sql语句删除表Tearch_course和表Teacher。在SQLServer2005的查询分析器中，用sql语句创建student表。CREATETABLEstudent(snochar(8)PRIMARYKEY,--学号(主键)snamechar(8)NOTNULL,--姓名sexchar(2),--性别nativechar(20),--籍贯birthdaysmalldatetime,--出生日期dnochar(6),--所在院系spnochar(8),--专业代码(外键)classnochar(4),--班级号entimesmalldatetime,--入校时间homevarchar(40),--家庭住址telvarchar(40)--联系电话)2、修改表的定义将Student表中的birthday字段设为不能为空（notnull）。用SQL语句将Student表中的属性snochar(8)改成varchar(20)类型。用企业管理器在Course表中添加一列year，类型为varchar（4），默认置为空。用SQL语句在year字段添加约束，year的属性值在2004-2008之间。用SQL语句将Course表中的year字段删除。AlterTABLE参考语法(1)向表中添加字段：Altertable[表名]add[列名]类型ALTERTABLEdbo.doc_exaADDcolumn_bVARCHAR(20)NULL(2)删除字段：Altertable[表名]dropcolumn[列名]ALTERTABLEdbo.doc_exbDROPCOLUMNcolumn_b(3)修改表中字段类型（可以修改列的类型，是否为空）：Altertable[表名]altercolumn[列名]类型ALTERTABLEdbo.doc_exyALTERCOLUMNcolumn_aDECIMAL(5,2)(4)添加主键：Altertable[表名]addconstraint[约束名]primarykey([列名])(5)添加唯一约束：Altertable[表名]addconstraint[约束名]unique([列名])(6)添加表中某列的默认值：Altertable[表名]addconstraint[约束名]default(默认值)for[列名]ALTERTABLEdbo.doc_exzADDCONSTRAINTcol_b_defDEFAULT50FORcolumn_b(7)添加check约束：Altertable[表名]addconstraint[约束名]check(内容)ALTERTABLEdbo.doc_exdWITHNOCHECKADDCONSTRAINTexd_checkCHECK(column_a1)(8)添加外键约束：Altertable[表名]addconstraint[约束名]foreignkey(列名)referencese另一表名(列名)ALTERTABLEPerson.ContactBackupADDCONSTRAINTFK_ContactBacup_ContactFOREIGNKEY(ContactID)REFERENCESPerson.Person(BusinessEntityID);(9)删除约束：Altertable[表名]dropconstraint[约束名](10)重命名表：execsp_rename'[原表名]','[新表名]'（11）重命名列名：execsp_rename'[表名].[列名]','[表名].[新列名]'3、使用数据库实验中涉及两张表：部门表、职工表。部门表包括部门号、名称、经理名、地址、电话号以及一个部门号主键约束和一个名称的唯一性约束。职工表包括职工号、姓名、年龄、职务、工资、部门号以及一个职工号的主键约束、一个部门好的外键约束和一个年龄的检查约束。本实验通过对这两张表的操作来验证数据库约束的相关概念。(1)建立数据库和相关表结构useqixindroptable职工droptable部门createtable部门(部门号char(4),名称varchar(20)notnull,经理名varchar(8),地址varchar(50),电话号varchar(20),constraintPK_部门号primarykey(部门号),constraintU_名称unique(名称))createtable职工(职工号char(4),姓名varchar(8)notnull,年龄int,职务varchar(10),工资money,部门号char(4),constraintPK_职工号primarykey(职工号),constraintFK_部门号foreignkey(部门号)references部门(部门号)ondeletecascade,constraintCK_年龄check(年龄=60))(2)验证主键（PRIMARYKEY）约束insertinto部门values('0001','财务科','张三','湖北汽车工业学院','8238787')如果再次insertinto部门values('0001','财务科','张三','湖北汽车工业学院','8238787')，则会违反了PRIMARYKEY约束'PK__部门__571DF1D5'。不能在对象'部门'中插入重复键。(3)验证唯一性（UNIQUE）约束如果执行insertinto部门values('0002','财务科','张三','湖北汽车工业学院','8238787')则会违反了UNIQUEKEY约束'UQ__部门__5812160E'。不能在对象'部门'中插入重复键。(4)验证检查（CHECK）约束insertinto部门values('0002','教务科','李四','湖北汽车工业学院','8238787')insertinto部门values('0003','人事科','王二','湖北汽车工业学院','8238787')insertinto职工values('0001','张伟','30','科长',2000,'0001')insertinto职工values('0002','李红','25','副科长',1500,'0001')insertinto职工values('0003','王强','33','科长',2000,'0002')insertinto职工values('0004','赵东','34','副科长',1500,'0002')insertinto职工values('0005','陈三','29','科长',2000,'0003')insertinto职工values('0006','孙波','28','副科长',1500,'0003')如果执行insertinto职工values('0007','陈红','70','副科长',1500,'0003')，则会INSERT语句与COLUMNCHECK约束'CK__职工__年龄__656C112C'冲突。该冲突发生于数据库'qixin'，表'职工',column'年龄'。(5)验证外键（FOREIGN）约束当指定ondeletecascade时为级联删除，删除部门表记录时，职工表中相关的记录也会同时删除deletefrom部门where部门号='0001'deletefrom部门where部门号='0002'deletefrom部门where部门号='0003'如果不指定ondeletecascade时默认为受限删除,删除部门表记录时，则会DELETE语句与COLUMNREFERENCE约束'FK__职工__部门号__6D0D32F4'冲突。该冲突发生于数据库'qixin'，表'职工',column'部门号'。4、在实验2的CPXS数据库中完成如下操作CPXS数据库包含如下三个表：CP（产品编号，产品名称，价格，库存量）；XSS（客户编号，客户名称，地区，负责人，电话）；CPXSB（产品编号，客户编号，销售日期，数量，销售额）；三个表结构如图2.1～图2.3所示，请在企业管理器中完成表的创建。图3.1CP表结构图3.2XSS表结构图3.3CPXSB表结构数据写入操作在企业管理器中输入如图2.4～图2.6的CP表、XSS表和CPXSB表的样本数据。图3.4CP表的样本数据图3.5XSS表的样本数据图3.6CPXSB表的样本数据