实验3：SQL SERVER数据表管理.docx

编号：时间：2021年x月x日书山有路勤为径，学海无涯苦作舟页码：第1页 共1页 实验3：SQL SERVER数据表管理 一、实验目的1、学会在SSMS图形界面和T-SQL语句创建表二、 实验内容1、创建各个表在实验2建立的数据库EDUC中，根据分析需要如下几个表： 表2.1 student表（学生信息表）字段名称类 型宽 度允许空值主 键说 明snochar8NOT NULL是学生学号snamechar8NOT NULL学生姓名sexchar2NULL学生性别nativechar20NULL籍贯birthdaysmalldate4NULL学生出生日期dnochar6NULL学生所在院系spnochar8NULL专业代码（外键）classnochar4NULL班级号entimesmalldate4NULL学生入校时间homevarchar40NULL学生家庭住址telvarchar40NULL学生联系表2.2 course表（课程信息表）字段名称类 型宽 度允许空值主 键说 明cnochar10NOT NULL是课程编号spnochar8NULL专业代码（外键）cnamechar20NOT NULL课程名称ctnotinyint1NULL课程类型编号（外键）experimenttinyint1NULL实验时数lecturetinyint1NULL授课学时semestertinyint1NULL开课学期credittinyint1NULL课程学分表2.3 student_course表（学生选课成绩表）字段名称类 型宽 度允许空值主 键说 明snochar8NOT NULL是学生学号tcidsmallint2NOT NULL是上课编号scoretinyint1NULL学生成绩表2.4 teacher表（教师信息表）字段名称类 型宽 度允许空值主 键说 明tnochar8NOT NULL是教师编号tnamechar8NOT NULL教师姓名sexchar2NULL教师性别birthdaysmalldate4NULL教师出生日期dnochar6NULL教师所在院系pnotinyint1NULL教师职务或职称编号homevarchar40NULL教师家庭住址zipcodechar6NULL邮政编码telvarchar40NULL联系emailvarchar40NULL电子邮件表2.5 teacher_course表（教师上课课表）字段名称类 型宽 度允许空值主 键说 明tcidsmallint2NOT NULL是上课编号tnochar8NULL教师编号（外键）spnochar8NULL专业代码（外键）classnochar4NULL班级号cnochar10NOT NULL课程编号（外键）semesterchar6NULL学期schoolyearchar10NULL学年classtimevarchar40NULL上课时间classroomvarchar40NULL上课地点weektimetinyint1NULL每周课时数Ø 使用SSMS建立EDUC数据库中的各个表Ø 根据如上表结构用SQL语句创建各表，并将创建表的SQL语句以.sql文件的形式保存在磁盘上。

Ø 使用SSMS图形界面删除建立的表Student_course,Student和Course，在查询分析器中用sql语句删除表Tearch_course和表Teacher在SQL Server 2005 的查询分析器中，用sql语句创建student表CREATE TABLE student( sno char(8) PRIMARY KEY, --学号(主键) sname char (8) NOT NULL, --姓名 sex char(2), --性别 native char(20), --籍贯 birthday smalldatetime, --出生日期 dno char(6), --所在院系 spno char(8), --专业代码(外键) classno char(4), --班级号 entime smalldatetime, --入校时间 home varchar (40), --家庭住址 tel varchar (40) --联系)2、修改表的定义Ø 将Student表中的birthday字段设为不能为空（not null）Ø 用SQL语句将Student表中的属性sno char(8)改成varchar(20)类型。

Ø 用企业管理器在Course表中添加一列year ，类型为varchar（4），默认置为空Ø 用SQL语句在year字段添加约束，year 的属性值在2004-2008之间Ø 用SQL语句将Course表中的year字段删除Ø Alter TABLE参考语法(1) 向表中添加字段：Alter table [表名] add [列名] 类型ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL(2) 删除字段：Alter table [表名]  drop column [列名]ALTER TABLE dbo.doc_exb DROP COLUMN column_b(3) 修改表中字段类型 （可以修改列的类型，是否为空）：Alter table [表名] alter column [列名] 类型ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2)(4) 添加主键：Alter table [表名] add constraint [ 约束名] primary key( [列名])(5) 添加唯一约束：Alter table [表名] add constraint [ 约束名] unique([列名])(6) 添加表中某列的默认值：Alter table [表名] add constraint [约束名] default(默认值) for [列名]ALTER TABLE dbo.doc_exz ADD CONSTRAINT col_b_def DEFAULT 50 FOR column_b(7)添加check约束：Alter table [表名] add constraint [约束名] check (内容)ALTER TABLE dbo.doc_exd WITH NOCHECK ADD CONSTRAINT exd_check CHECK (column_a > 1)(8) 添加外键约束： Alter table [表名] add constraint [约束名]  foreign key(列名) referencese 另一表名(列名)ALTER TABLE Person.ContactBackupADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID) REFERENCES Person.Person (BusinessEntityID) ;(9) 删除约束：Alter table [表名] drop constraint [约束名]  (10) 重命名表：exec sp_rename '[原表名]','[新表名]'（11） 重命名列名：exec sp_rename '[表名].[列名]','[表名].[新列名]'3、使用数据库实验中涉及两张表：部门表、职工表。

部门表包括部门号、名称、经理名、地址、号以及一个部门号主键约束和一个名称的唯一性约束 职工表包括职工号、姓名、年龄、职务、工资、部门号以及一个职工号的主键约束、一个部门好的外键约束和一个年龄的检查约束 本实验通过对这两张表的操作来验证数据库约束的相关概念1) 建立数据库和相关表结构use qixindrop table 职工drop table 部门 create table 部门( 部门号     char(4), 名称     varchar(20) not null, 经理名     varchar(8), 地址     varchar(50), 号     varchar(20), constraint PK_部门号 primary key(部门号), constraint U_名称 unique(名称)) create table 职工 ( 职工号     char(4) , 姓名     varchar(8) not null , 年龄     int , 职务     varchar(10) , 工资     money, 部门号     char(4), constraint PK_职工号 primary key(职工号), constraint FK_部门号 foreign key(部门号) references 部门(部门号) on delete cascade, constraint CK_年龄 check(年龄<=60)) (2) 验证主键（PRIMARY KEY）约束 insert into 部门 values('0001','财务科','张三','湖北汽车工业学院','8238787')如果再次insert into 部门 values('0001','财务科','张三','湖北汽车工业学院','8238787')，则会违反了 PRIMARY KEY 约束 'PK__部门__571DF1D5'。

不能在对象 '部门' 中插入重复键3)验证唯一性（UNIQUE）约束 如果执行insert into 部门 values('0002','财务科','张三','湖北汽车工业学院','8238787')则会违反了 UNIQUE KEY 约束 'UQ__部门__5812160E'。