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

实验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( snochar(8) PRIMARY KEY, -学号(主键) 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表中的属性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 列名b 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 列名 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 另一表名(列名)ADD 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。不能在对象 部门 中插入重复键。(4) 验证检查（CHECK）约束insert into 部门 values(0002,教务科,李四,湖北汽车工业学院,8238787)insert into 部门 values(0003,人事科,王二,湖北汽车工业学院,8238787) insert into 职工 values(0001,张伟,30,科长,2000,0001)insert into 职工 values(0002,李红,25,副科长,1500,0001)insert into 职工 values(0003,王强,33,科长,2000,0002)insert into 职工 values(0004,赵东,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）约束当指定on delete cascade时为级联删除，删除部门表记录时，职工表中相关的记录也会同时删除delete from 部门 where 部门号=0001delete from 部门 where 部门号=0002delete from 部门 where 部门号=0003如果不指定on delete cascade时默认为受限删除,删除部门表记录时，则会DELETE 语句与 COLUMN REFERENCE 约束 FK_职工_部门号_6D0D32F4 冲突。该冲突发生于数据库 qixin，表 职工, column 部门号。4、在实验2的CPXS数据库中完成如下操作CPXS数据库包含如下三个表： CP（产品编号，产品名称，价格，库存量）； XSS（客户编号，客户名称，地区，负责人，电话）； CPXSB（产品编号，客户编号，销售日期，数量，销售额）； 三个表结构如图2.1图2.3所示，请在企业管理器中完成表的创建。 图3.1 CP表结构图3.2 XSS表结构图3.3 CPXSB表结构 数据写入操作在企业管理器中输入如图2.4图2.6的CP表、XSS表和CPXSB表的样本数据。图3.4 CP表的样本数据图3.5 XSS表的样本数据图3.6 CPXSB表的样本数据