实验3SQLSERVER数据表管理

1、实验3：SQL SERVER数据表管理 一、实验目的1、学会在SSMS图形界面和T-SQL语句创建表。二、 实验内容1、创建各个表在实验2建立的数据库EDUC中，根据分析需要如下几个表： 表2.1 student表（学生信息表）字段名称类 型宽 度允许空值主 键说 明snochar8NOT NULL是学生学号snamechar8NOT NULL学生姓名sexchar2NULL学生性别nativechar20NULL籍贯birthdaysmalldate4NULL学生出生日期dnochar6NULL学生所在院系spnochar8NULL专业代码（外键）classnochar4NULL班级号ent

2、imesmalldate4NULL学生入校时间homevarchar40NULL学生家庭住址telvarchar40NULL学生联系电话表2.2 course表（课程信息表）字段名称类 型宽 度允许空值主 键说 明.ochar10NOT NULL是课程编号spnochar8NULL专业代码（外键）.amechar20NOT NULL课程名称ctnotinyint1NULL课程类型编号（外键）experimenttinyint1NULL实验时数lecturetinyint1NULL授课学时semestertinyint1NULL开课学期credittinyint1NULL课程学分表2.3 stu

3、dent_course表（学生选课成绩表）字段名称类 型宽 度允许空值主 键说 明snochar8NOT NULL是学生学号tcidsmallint2NOT NULL是上课编号scoretinyint1NULL学生成绩表2.4 teacher表（教师信息表）字段名称类 型宽 度允许空值主 键说 明tnochar8NOT NULL是教师编号tnamechar8NOT NULL教师姓名sexchar2NULL教师性别birthdaysmalldate4NULL教师出生日期dnochar6NULL教师所在院系pnotinyint1NULL教师职务或职称编号homevarchar40NULL教师家庭住

4、址zipcodechar6NULL邮政编码telvarchar40NULL联系电话emailvarchar40NULL电子邮件表2.5 teacher_course表（教师上课课表）字段名称类 型宽 度允许空值主 键说 明tcidsmallint2NOT NULL是上课编号tnochar8NULL教师编号（外键）spnochar8NULL专业代码（外键）classnochar4NULL班级号.ochar10NOT NULL课程编号（外键）semesterchar6NULL学期schoolyearchar10NULL学年classtimevarchar40NULL上课时间classroomvar

5、char40NULL上课地点weektimetinyint1NULL每周课时数Ø 使用SSMS建立EDUC数据库中的各个表Ø 根据如上表结构用SQL语句创建各表，并将创建表的SQL语句以.sql文件的形式保存在磁盘上。Ø 使用SSMS图形界面删除建立的表Student_course,Student和Course，在查询分析器中用sql语句删除表Tearch_course和表Teacher。在SQL Server 2005 的查询分析器中，用sql语句创建student表。CREATE TABLE student( snochar(8) PRIMARY KEY, -

6、学号(主键) snamechar (8) NOT NULL,-姓名 sexchar(2),-性别 nativechar(20),-籍贯 birthdaysmalldatetime,-出生日期 dnochar(6),-所在院系 spnochar(8),-专业代码(外键) classnochar(4),-班级号 entimesmalldatetime,-入校时间 homevarchar (40),-家庭住址 telvarchar (40)-联系电话)2、修改表的定义Ø 将Student表中的birthday字段设为不能为空（not null）。Ø 用SQL语句将Student表

7、中的属性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 表名

8、0; 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  约束名 u

9、nique(列名)(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)

10、添加外键约束： 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_ren

11、ame '原表名','新表名'（11） 重命名列名：exec sp_rename '表名.列名','表名.新列名'3、使用数据库实验中涉及两张表：部门表、职工表。部门表包括部门号、名称、经理名、地址、电话号以及一个部门号主键约束和一个名称的唯一性约束。职工表包括职工号、姓名、年龄、职务、工资、部门号以及一个职工号的主键约束、一个部门好的外键约束和一个年龄的检查约束。本实验通过对这两张表的操作来验证数据库约束的相关概念。(1) 建立数据库和相关表结构use qixindrop table 职工drop table 部门 create

12、 table 部门( 部门号     char(4), 名称     varchar(20) not null, 经理名     varchar(8), 地址     varchar(50), 电话号     varchar(20), constraint PK_部门号 primary key(部门号), constraint

13、U_名称 unique(名称) create table 职工 ( 职工号     char(4) , 姓名     varchar(8) not null , 年龄     int , 职务     varchar(10) , 工资     money, 部门号     char(4), 

14、;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 部门 val

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

16、#39;UQ_部门_5812160E'。不能在对象 '部门' 中插入重复键。(4) 验证检查（CHECK）约束insert into 部门 values('0002','教务科','李四','湖北汽车工业学院','8238787')insert into 部门 values('0003','人事科','王二','湖北汽车工业学院','8238787') insert into 职工 values('000

17、1','张伟','30','科长',2000,'0001')insert into 职工 values('0002','李红','25','副科长',1500,'0001')insert into 职工 values('0003','王强','33','科长',2000,'0002')insert into 职工 values('0004','

18、;赵东','34','副科长',1500,'0002')insert into 职工 values('0005','陈三','29','科长',2000,'0003')insert into 职工 values('0006','孙波','28','副科长',1500,'0003')如果执行insert into 职工 values('0007','陈红','70','副科长',1500,'0003')，则会INSERT 语句与 COLUMN CHECK 约束 'CK_职工_年龄_656C112C' 冲突。该冲突发生于数据库 'qixin'，表 '职工', column '年龄'。 (5) 验证外键（FOREIGN）约