/***************************
第七单元：实施数据完整性
***************************/
/* 预备任务：创建数据库“选课”，包含一个数据文件与一个日志文件 */
CREATE DATABASE 选课
ON PRIMARY
(
NAME=选课_data,
FILENAME='D:\选课_data.mdf',
SIZE=5MB,
MAXSIZE=50MB,
FILEGROWTH=10%
)
LOG ON
(
NAME=选课_log,
FILENAME='D:\选课_log.ldf',
SIZE=2MB,
MAXSIZE=20MB,
FILEGROWTH=2MB
);
USE 选课
GO
/* 任务：创建数据表（CREATE TABLE） */
--1. 创建Department表（表设计参看P127表6-1）
CREATE TABLE Department
(
DepartNo nvarchar(2) NOT NULL,
DepartName nvarchar(20) NOT NULL
);
--2. 创建Course表（表设计参看P127表6-2）
CREATE TABLE Course
(
CouNo nvarchar(3) NOT NULL,
CouName nvarchar(30) NOT NULL,
Kind nvarchar(8) NOT NULL,
Credit decimal(2,1) NOT NULL,
Teacher nvarchar(20) NOT NULL,
DepartNo nvarchar(2) NOT NULL,
SchoolTime nvarchar(10) NOT NULL,
LimitNum decimal(5,0) NOT NULL,
WillNum decimal(5,0) NOT NULL,
ChooseNum decimal(5,0) NOT NULL
);
--3. 创建Class表（表设计参看P127表6-3）
CREATE TABLE Class
(
ClassNo nvarchar(8) NOT NULL,
ClassName nvarchar(20) NOT NULL,
DepartNo nvarchar(2) NOT NULL
);
--4. 创建Student表（表设计参看P127表6-4）
CREATE TABLE Student
(
StuNo nvarchar(8) NOT NULL,
StuName nvarchar(10) NOT NULL,
Pwd nvarchar(8) NOT NULL,
ClassNo nvarchar(8) NOT NULL
);
--5. 创建StuCou表（表设计参看P127表6-5）
CREATE TABLE StuCou
(
StuNo nvarchar(8) NOT NULL,
CouNo nvarchar(3) NOT NULL,
WillOrder tinyint NOT NULL,
State nvarchar(2) NOT NULL,
RandomNum nvarchar(50) NULL
);
/******************************
第七单元有四项任务：
1、创建约束（删除约束）
（1）主键约束（PRIMARY KEY）
（2）外键约束（FOREIGN KEY...REFRENCES...）
（3）唯一约束（UNIQUE）
（4）检查约束（CHECK）
（5）默认约束（DEFAULT）
2、创建默认值（删除默认值）
3、创建规则
4、创建标识列
******************************/
/* 任务：创建主键约束 */
/*
语法格式：ADD CONSTRAINT <约束名> <约束类型> (约束列)
*/
--1. 给Department表的DepartNo字段创建主键约束
ALTER TABLE Department
ADD CONSTRAINT pk_department PRIMARY KEY (DepartNo);
-- 大家可以打开Department表，输入两条记录，系部编号相同，单击保存按钮，会弹出错误提示框。
--2. 给Course表的CouNo字段创建主键约束
ALTER TABLE Course
ADD CONSTRAINT pk_course PRIMARY KEY (CouNo);
--3. 给Class表的ClassNo字段创建主键约束
ALTER TABLE Class
ADD CONSTRAINT pk_class PRIMARY KEY (ClassNo);
--4. 给Student表的StuNo字段创建主键约束
ALTER TABLE Student
ADD CONSTRAINT pk_student PRIMARY KEY (StuNo);
--5. 给StuCou表的StuNo与CouNo字段创建主键约束
ALTER TABLE StuCou
ADD CONSTRAINT pk_stucou PRIMARY KEY (StuNo, CouNo);
--6. 删除StuCou表的主键约束，然后再次创建
ALTER TABLE StuCou
DROP CONSTRAINT pk_stucou;
ALTER TABLE StuCou
ADD CONSTRAINT pk_stucou PRIMARY KEY (StuNo, CouNo);
/* 任务：创建外键约束 */
--1. 给Student表的ClassNo创建外键约束
ALTER TABLE Student
ADD CONSTRAINT fk_student FOREIGN KEY (ClassNo)
REFERENCES Class (ClassNo);
-- 在数据库关系图里创建Class表与Student表之间的永久性联系
--2. 给Course表的DepartNo创建外键约束
ALTER TABLE Course
ADD CONSTRAINT fk_course FOREIGN KEY (DepartNo);
REFERENCES Department(DepartNo);
-- 在数据库关系图里创建Department表与Course表之间的永久性联系
--3. 给StuCou表的StuNo与CouNo创建外键约束
ALTER TABLE StuCou
ADD CONSTRAINT fk_stucou_stuno FOREIGN KEY (StuNo)
REFERENCES Student(StuNo);
ALTER TABLE StuCou
ADD CONSTRAINT fk_stucou_couno FOREIGN KEY (CouNo)
REFERENCES Course (CouNo);
-- 在数据库关系图里创建StuCou表与Student表和Course之间的永久性联系
--4. 给Class表的DepartNo创建外键约束
ALTER TABLE Class
ADD CONSTRAINT fk_class FOREIGN KEY (DepartNo)
REFERENCES Department (DepartNo);
-- 在数据库关系图里创建Department表与Class表之间的永久性联系
/* 任务：创建唯一约束 */
--1. 给Course表的CouName创建一个唯一索引
ALTER TABLE Course
ADD CONSTRAINT un_couname UNIQUE (CouName);
--2. 给Course表插入两条记录，故意让课程名相同
/* 因为Course表的DepartNo是外键，参照Department表的主键DepartNo，必须先给主键设置值 */
INSERT INTO Department VALUES('01','信息工程系');
INSERT INTO Department VALUES('02','建筑工程系');
INSERT INTO Course VALUES ('001','大型数据库开发','信息技术',3,'李小平','01','周二5-6节',20,43,20);
INSERT INTO Course VALUES ('002','大型数据库开发','工程技术',2,'王彦荣','02','周三3-4节',25,30,25);
/* 任务：创建检查约束 */
--1. 约束Student表的StuNo字段，只允许8位数字，并且不可以为8个0
ALTER TABLE Student
ADD CONSTRAINT ck_stuno CHECK (StuNo LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' AND StuNo<>'00000000');
--2. 给Student表输入一条记录，学号为'00000000'
/* 因为Student表的ClassNo是外键，参照Class表的主键ClassNo，必须先给主键设置值 */
INSERT INTO Class VALUES ('20150001','15软件1班','01');
INSERT INTO Student VALUES ('00000000','黄飞鸿','FAFFGS02','20150001');
--3. 给Student表插入一条记录，学号为'2015x001'
INSERT INTO Student VALUES ('2015x001','张三丰','GHET56KO','20150001');
--4. 删除Student表对于StuNo的检查约束
ALTER TABLE Student
DROP CONSTRAINT ck_stuno;
--5. 选课表StuCou的报名志愿最多只允许5个
ALTER TABLE StuCou
ADD CONSTRAINT ck_willorder CHECK (WillOrder IN (1,2,3,4,5));
--6. 给Student表插入一条记录
INSERT INTO Student VALUES ('20150001','李晓红','QW45FG57','20150001');
--7. 给选课表StuCou插入一条记录，故意让WillOrder为6
INSERT INTO StuCou (StuNo,CouNo,WillOrder,State) VALUES ('20150001','001',6,'报名');
--8. 给Course表的CouNo字段设置检查约束，只能由数字构成，并且不能全为0
ALTER TABLE Course
ADD CONSTRAINT ck_couno CHECK (CouNo LIKE '[0-9][0-9][0-9]' AND CouNo<>'000');
--9. 给Course表插入一条记录，故意设置CouNo为'000'
INSERT INTO Course VALUES ('000','Java程序设计','信息技术',2,'李晓云','01','周三3-4节',25,35,25);
/* 任务：创建默认约束 */
--1. 给选课表StuCou的State字段设置默认约束，默认值为“待定”
ALTER TABLE StuCou
ADD CONSTRAINT df_state DEFAULT ('待定') FOR State;
--2. 给选课表StuCou插入一条记录，不设置State字段的值
INSERT INTO StuCou (StuNo,CouNo,WillOrder) VALUES ('20150001','001',2);
--3. 显示StuCou表的全部记录
SELECT * FROM StuCou;
--4. 删除StuCou表对State字段的默认约束
ALTER TABLE StuCou
DROP CONSTRAINT df_state;
/* 任务：创建默认值（在SQL Server后续版本里已经删除此功能，建议使用默认约束） */
--1. 给Course表的Teacher字段创建一个默认值对象
CREATE DEFAULT df_teacher AS '待定';
--2. 将默认值对象df_teacher绑定到Course表的Teacher字段
sp_bindefault df_teacher,'Course.Teacher'
--3. 验证：给Course表插入一条记录，不设置Teacher字段的值
INSERT INTO Course (CouNo,CouName,Kind,Credit,DepartNo,SchoolTime,LimitNum,WillNum,ChooseNum)
VALUES ('002','Java程序设计','信息技术',4,'01','周四3-4节',30,50,30);
--4. 查看Course表的全部记录
SELECT * FROM Course;
--5. 给Course表的Teacher字段解除默认值绑定
sp_unbindefault 'Course.Teacher'
--6. 删除默认值对象df_teacher
DROP DEFAULT df_teacher;
/* 任务：创建规则（删除规则） */
--1. 针对Course表创建规则，保证Credit字段只能取1.5、2、2.5、3、3.5、4、4.5、5
CREATE RULE credit_rule
AS @Credit IN (1.5,2,2.5,3,3.5,4,4.5,5);
--2. 将规则对象credit_rule绑定到Course表的Credit字段
sp_bindrule credit_rule,'Course.Credit'
--3. 验证：给Course表插入一条记录，故意设置学分为2.2
INSERT INTO Course VALUES ('003','Web前端开发技术','信息技术',2.2,'李小龙','01','周四3-4节',30,50,30);
--4. 将Course表的Credit解除规则绑定
sp_unbindrule 'Course.Credit'
--5. 删除规则credit_rule
DROP RULE credit_rule;
/* 任务：使用标识列 */
--1. 创建Test表（id是标识列，seed是201501，increment是1）
CREATE TABLE Test
(
id int NOT NULL IDENTITY(201501,1),
name nvarchar(8) NOT NULL,
gender nvarchar(1) NOT NULL,
age smallint NOT NULL
);
--2. 给Test表插入几条记录，不用管标识列
INSERT INTO Test (name,gender,age) VALUES ('张三丰','男',18);
INSERT INTO Test (name,gender,age) VALUES ('李晓霞','女',19);
INSERT INTO Test (name,gender,age) VALUES ('甄云文','女',18);
INSERT INTO Test (name,gender,age) VALUES ('杨臣刚','男',20);
INSERT INTO Test (name,gender,age) VALUES ('王丽霞','女',18);
--3. 查看Test表的全部记录
SELECT *
FROM Test;
