数据库实验二——DDL

由于老师并没有提供标准答案，所以所有的任务都由本人独立完成，如有错误，在所难免，如果您发现了哪些地方有问题，欢迎与我交流，谢谢！

数据字典

实验要求

创建数据库
创建所有的表，包括主键和外键，并根据你的理解，定义相关的UNIQUE，NOT NULL和CHECK约束；
使用alter命令修改表结构，删除教师表中的“title” 字段，然后再添加到表中；
修改学生表中的“sex” 字段的类型为char(2)，，默认值为“m”；
删除教师表中的’sex’字段后，在教师表中添加任意一条数据，然后添加类型为char（2）的‘sex‘字段，该字段不能为空，添加是否成功?如果失败分析失败的原因并进行必要的操作使‘sex’字段添加成功；
修改学生表，对类型为varchar(18)的“IDcard”字段添加check约束，要求该字段的长度为18，并且只能由数字组成，并且指定该字段为候选键；
设置课程表中“credit”的check约束为大于0；设置请假单表的“state”字段默认值为“未审核”，设置“state”字段的check约束为“未审核”、“审核已通过”、“审核不通过”；
删除（7）增加的约束；
使用sql语句查询所有创建表的表结构；

实验过程

step1 创建数据库

CREATE DATABASE lab2

Step2 创建所有表和键

USE lab2
GO

CREATE TABLE student (
	studentNum VARCHAR(16) PRIMARY KEY,
	studentName VARCHAR(8) not null,
	sex VARCHAR(4) not null,
	class TINYINT not null,
	IDcard CHAR(18),
)
--增加约束
ALTER TABLE student add constraint ck_sex check(sex='m' or sex='f')

--创建教师表
CREATE TABLE teacher(
	teacherNum VARCHAR(8) not null PRIMARY KEY,
	teacherName VARCHAR(8) not null,
	sex CHAR(2) not null,
	title VARCHAR(12)
)

--创建课程表
CREATE TABLE lesson(
	lessonNum VARCHAR(8) not null PRIMARY KEY,
	lessonName VARCHAR(8) not null,
	year CHAR(4),
	term CHAR(2),
	credit TINYINT,
	teacherNum VARCHAR(8) not null REFERENCES teacher(teacherNum),
	beforeLessonNum VARCHAR(8)
)

--创建选课表
CREATE TABLE stu_choose_lesson (
	studentNum VARCHAR(16) not null REFERENCES student(studentNum),
	lessonNum VARCHAR(8) not null REFERENCES lesson(lessonNum),
	score TINYINT,
	PRIMARY KEY (studentNum, lessonNum)
)

--创建请假单表
CREATE TABLE leave_apply (
	applyNum VARCHAR(16) not null PRIMARY KEY,
	applyTime DATE not null,
	reason VARCHAR(128) not null,
	state VARCHAR(16) not null,
	studentNum VARCHAR(16) not null REFERENCES student(studentNum),
	lessonNum VARCHAR(8) not null REFERENCES lesson(lessonNum),
	teacherNum VARCHAR(8) not null REFERENCES teacher(teacherNum),
)
--添加外键约束和检查约束
ALTER TABLE leave_apply ADD CONSTRAINT FK_st_le FOREIGN KEY(lstudentNumessonNum) REFERENCES stu_choose_lesson(studentNum,lessonNum)

--创建消息视图
GO
CREATE VIEW message AS
SELECT applyNum, applyTime, state FROM leave_apply

Step3：使用alter命令修改表结构，删除教师表中的“title” 字段，然后再添加到表中

--删除教师表中的'title'字段
GO
ALTER TABLE teacher DROP COLUMN title

--title添加到表中
ALTER TABLE teacher ADD title VARCHAR(12)

Step4: 修改学生表中的“sex” 字段的类型为char(2)，默认值为“m”；

--删除原有的约束
ALTER TABLE student DROP ck_sex
--修改字段类型并增加约束和默认值
ALTER TABLE student ALTER COLUMN sex char(2)
ALTER TABLE student ADD CONSTRAINT ck_sex check(sex = 'm' or sex = 'f') , DEFAULT 'm' for sex

Step5: 删除教师表中的’sex’字段后，在教师表中添加任意一条数据，然后添加类型为char（2）的‘sex‘字段，该字段不能为空，添加是否成功?如果失败分析失败的原因并进行必要的操作使‘sex’字段添加成功

--删除教师的sex列
ALTER TABLE teacher DROP COLUMN sex
--随便插入一列值
INSERT INTO teacher values (233,'李林','教授') 
--查看插入情况
SELECT * FROM teacher
--加入将sex加入

ALTER TABLE teacher ADD sex char(2) not null
--插入失败

根据报错信息：“ALTER TABLE 只允许添加满足下述条件的列: 列可以包含 Null 值；或者列具有指定的DEFAULT定义；或者要添加的列是标识列或时间戳列；或者，如果前几个条件均未满足，则表必须为空以允许添加此列。不能将列“sex”添加到非空表“teacher”中，因为它不满足上述条件。”

由于我们设置了sex不为空，如果加入sex属性，那么之前我们插入的元组中，sex的属性会为空，这是不允许的，所以我们根据提示信息，加入default约束：

ALTER TABLE teacher ADD sex char(2) not null DEFAULT 'm'
--插入成功
SELECT * FROM  teacher

Step6: 修改学生表，对类型为varchar(18)的“IDcard”字段添加check约束，要求该字段的长度为18，并且只能由数字组成，并且指定该字段为候选键；

此处对IDcard有三个约束，长度约束、内容约束和唯一性约束

ALTER TABLE student ADD CONSTRAINT ck_ID CHECK(LEN(IDcard) = 18 and IDcard  like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')  
ALTER TABLE student ADD unique(IDcard)  --设置为候选键

随意插入几组数据进行测试：

INSERT INTO student VALUES (201810137620, '我谔谔', 'm',6 , 114514)  
--INSERT 语句与 CHECK 约束"ck_ID"冲突。该冲突发生于数据库"lab2"，表"dbo.student", column 'IDcard'。  
  
INSERT INTO student VALUES (201810137620, '我谔谔', 'm',6 , '114514')  
--INSERT 语句与 CHECK 约束"ck_ID"冲突。该冲突发生于数据库"lab2"，表"dbo.student", column 'IDcard'。    
	  
INSERT INTO student VALUES (201810137622, '随便2', 'm', 6 , 'a20821200004103017')  
--INSERT 语句与 CHECK 约束"ck_ID"冲突。该冲突发生于数据库"lab2"，表"dbo.student", column 'IDcard'。    
INSERT INTO student VALUES (201810137620, '我谔谔', 'm',6 , '420821288884103018')  
--成功插入    
INSERT INTO student VALUES (201810137621, '随便', 'm', 6 , 420821288884103017)  
--成功插入

Step7：设置课程表中“credit”的check约束为大于0；设置请假单表的“state”字段默认值为“未审核”，设置“state”字段的check约束为“未审核”、“审核已通过”、“审核不通过”；

ALTER TABLE lesson ADD CONSTRAINT ck_credit CHECK(credit > 0)

ALTER TABLE leave_apply ADD CONSTRAINT ck_state check(state = '未审核' or state = '审批已通过' or state = '审批未通过'), DEFAULT '未审核' for  state

Step8: 删除（7）增加的约束

直接通过约束名来进行删除

ALTER TABLE lesson DROP CONSTRAINT ck_credit  
ALTER TABLE leave_apply DROP CONSTRAINT ck_state

Step9: 使用sql语句查询所有创建表的表结构

使用存储过程来查看每张表

sp_columns student  
sp_columns teacher  
sp_columns lesson  
sp_columns stu_choose_lesson  
sp_columns leave_apply

实验二Over