--------------------
-- 建库部分
--------------------
-- 创建学生管理系统数据库,名称为Student
use master;
go
-- 检测原库是否存在,如果存在则删除
if exists (select * from sys.databases where name = 'Student')
	drop database Student;
go

-- 创建一个库
create database Student;
go
-- 使用这个库
use Student;
go


--------------------
-- 建表部分
--------------------
-- 创建班级表,存储班级信息,其中字段包含:班级id、班级名称
create table Class(
	ClassId int not null identity(1,1),
	ClassName nvarchar(50) not null
);
go

-- 创建学生表,存储学生信息,其中字段保护:学生id、姓名、性别、生日、家庭住址,所属班级id
create table Student (
	StudentId int not null identity(1, 1),
	StudentName nvarchar(50),
	StudentSex tinyint not null,
	StudentBirth date,
	StudentAddress nvarchar(255) not null
);
go

-- 创建课程表,存储课程信息,其中字段包含:课程id、课程名称、课程学分
create table Course(
	CourseId int identity(1,1),
	CourseName nvarchar(50),
	CourseCredit int
);
go

-- 创建班级课程表,存储班级课程信息,其中字段包含:自增id、班级id、课程id
create table ClassCourse(
	ClassCourseId int identity(1,1),
	ClassId int,
	CourseId int
);
go

-- 创建分数表,存储学生每个课程分数信息,其中字段包含:分数id、学生id、课程id、分数
create table Score(
	ScoreId int identity(1,1),
	StudentId int,
	CourseId int,
	Score int
);
go


-- 学生表建好了,细想一下少了一个所属班级的字段
-- 给学生表 Student 增加一个所属班级id字段 
alter table Student add ClassId int not null;
go

----------------------------------------
-- 创建约束部分,使用alter进行修改
----------------------------------------
-- 班级表 ClassId 字段需要设置为主键(主键约束)
alter table Class add constraint PK_Class_ClassId primary key (ClassId);
-- 学生表 StudentId 字段需要设置为主键(主键约束)
alter table Student add constraint PK_Student_StudentId primary key (StudentId);
-- 课程表 CourseId 字段需要设置为主键(主键约束)
alter table Course add constraint PK_Course_CourseId primary key (CourseId);
-- 班级课程表 ClassCourseId 字段需要设置为主键(主键约束)
alter table ClassCourse add constraint PK_ClassCourse_ClassCourseId primary key (ClassCourseId);
-- 分数表 ScoreId 字段需要设置为主键(主键约束)
alter table Score add constraint PK_Score_ScoreId primary key (ScoreId);

-- 学生表 StudentName 不允许为空(非空约束)
alter table Student alter column StudentName nvarchar(50) not null;

-- 班级表 ClassName 需要唯一(唯一约束)
alter table Class add constraint UQ_Class_ClassName unique(ClassName);
-- 课程表 CourseName 需要唯一(唯一约束)
alter table Course add constraint UQ_Course_CourseName unique(CourseName);

-- 学生表 ClassId 增加默认值为0(默认值约束)
alter table Student add constraint DF_Student_ClassId default(0) for ClassId;

-- 学生表 StudentSex 只能为1或者2(Check约束)
alter table Student add constraint CK_Student_StudentSex check(StudentSex=1 or StudentSex=2 or StudentSex=0);
-- 分数表 Score 字段只能大于等于0(check约束)
alter table Score add constraint CK_Score_Score check(Score>=0);
-- 课程表 CourseCredit 字段只能大于0(check约束)
alter table Course add constraint CK_Course_CourseCredit check(CourseCredit>=0);

-- 班级课程表ClassId 对应是 班级表ClassId 的外键 (外键约束)
alter table ClassCourse add constraint FK_ClassCourse_ClassId foreign key (ClassId) references Class(ClassId);
-- 班级课程表CourseId 对应是 课程表CourseId 的外键 (外键约束)
alter table ClassCourse add constraint FK_ClassCourse_CourseId foreign key (CourseId) references Course(CourseId);
-- 分数表StudentId 对应是 学生表StudentId 的外键 (外键约束)
alter table Score add constraint FK_Score_StudentId foreign key (StudentId) references Student(StudentId);
-- 分数表CourseId 对应是 课程表CourseId 的外键 (外键约束)
alter table Score add constraint FK_Score_CourseId foreign key (CourseId) references Course(CourseId);

--学校开设了3个班级:软件一班、软件二班、计算机应用技术班。请插入班级表相关数据。
insert into Class (ClassName) values ('软件一班'),
('软件二班'),
('计算机应用技术班');

--软件一班有3个同学,姓名、性别、生日、家庭住址 分别是:

-- 刘正、男(1)、2000年01月01日、广西省桂林市七星区空明西路10号鸾东小区
-- 黄贵、男、2001年03月20日、江西省南昌市青山湖区艾溪湖南路南150米广阳小区
-- 陈美、女(2)、2000年07月08日、福建省龙岩市新罗区曹溪街道万达小区
insert into student(studentname,StudentSex,StudentBirth,StudentAddress,classid)
values('刘正',1,'2000-01-01','广西省桂林市七星区空明西路10号鸾东小区',1);
insert into student(studentname,StudentSex,StudentBirth,StudentAddress,classid)
values('黄贵',1,'2001-03-20','江西省南昌市青山湖区艾溪湖南路南150米广阳小区',1);
insert into student(studentname,StudentSex,StudentBirth,StudentAddress,classid)
values('陈美',2,'2000-07-08','福建省龙岩市新罗区曹溪街道万达小区',1)

--软件二班有2个同学,姓名、性别、生日、家庭住址 分别是:
-- 江文、男、2000年08月10日、安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光
-- 钟琪、女、2001年03月21日、湖南省长沙市雨花区红花坡社区
insert into student(StudentName,studentsex,studentbirth,studentaddress,classid)
values('江文',1,'2000-08-10','安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光',2)
insert into student(studentname,studentsex,studentbirth,studentaddress,classid)
values('钟琪',2,'2001-03-21','湖南省长沙市雨花区红花坡社区',2)

--计算机应用技术班有4个同学,姓名、性别、生日、家庭住址 分别是:
-- 曾小林、男、1999年12月10日、安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光
-- 欧阳天天、女、2000年04月05日、湖北省武汉市洪山区友谊大道与二环线交汇处融侨悦府
-- 徐长卿、男、2001年01月30日、江苏省苏州市苏州工业园区独墅湖社区
-- 李逍遥、男、1999年11月11日、广东省广州市白云区金沙洲岛御洲三街恒大绿洲
insert into student(StudentName,studentsex,studentbirth,studentaddress,classid)
values('曾小林',1,'1999-12-11','安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光',3)
insert into student(StudentName,studentsex,studentbirth,studentaddress,classid)
values('欧阳天天',2,'2000-04-05','湖北省武汉市洪山区友谊大道与二环线交汇处融侨悦府',3)
insert into student(StudentName,studentsex,studentbirth,studentaddress,classid)
values('徐长卿',1,'2001-01-30','江苏省苏州市苏州工业园区独墅湖社区',3)
insert into student(StudentName,studentsex,studentbirth,studentaddress,classid)
values('李逍遥',1,'1999-11-11','广东省广州市白云区金沙洲岛御洲三街恒大绿洲',3)

 
--有2个学生尚未分配班级,姓名、性别、生日、家庭住址 分别是:
-- 东方不败 保密 1999年12月11日 河北省平定州西北四十余里的猩猩滩
-- 令狐冲 男 2000年08月11日 陕西省渭南市华阴市玉泉路南段
insert into student(StudentName,studentsex,studentbirth,studentaddress,classid)
values ('东方不败',0,'1999-12-11','河北省平定州西北四十余里的猩猩滩',0)
insert into student(StudentName,studentsex,studentbirth,studentaddress,classid)
values('令狐冲',1,'2000-08-11','陕西省渭南市华阴市玉泉路南段',0)


--软件一班开设课程,课程名称和学分分别为:
-- 数据库高级应用、3
-- javascript编程基础、3
-- web前端程序设计基础、4
-- 动态网页设计.net基础、6
insert into Course(CourseName, CourseCredit)
 values ('数据库高级应用', 3),
 ('javascript编程基础', 3),
 ('web前端程序设计基础', 4),
('动态网页设计.net基础', 6),
('动态网页设计php基础',6);
insert into classcourse(classid,courseid)
values(1,1),(1,2),(1,3),(1,4)

--软件二班开设课程,课程名称和学时分别为:
-- 数据库高级应用、3
-- javascript编程基础、3
-- web前端程序设计基础、4
-- 动态网页设计.net基础、6
insert into classcourse(classid,courseid)
values(2,1),(2,2),(2,3),(2,4)
--计算机应用技术班开设课程,课程名称和学时分别为:
-- 数据库高级应用、3
-- javascript编程基础、3
-- web前端程序设计基础、4
-- 动态网页设计php基础、6
insert into classcourse(classid,courseid)
values (3,1),(3,2),(3,3),(3,5)



--考试完成后,各学生各课程得分:
-- 刘正、数据库高级应用、80
-- 刘正、javascript编程基础、78
-- 刘正、web前端程序设计基础、65
-- 刘正、动态网页设计.net基础、90
--黄贵、数据库高级应用、60
-- 黄贵、javascript编程基础、77
-- 黄贵、web前端程序设计基础、68
-- 黄贵、动态网页设计.net基础、88
-- 陈美、数据库高级应用、88
-- 陈美、javascript编程基础、45
-- 陈美、web前端程序设计基础、66
-- 陈美、动态网页设计.net基础、75
insert into Score(studentid,courseid,score)
values(9,1,80),(9,2,78),(9,3,65),(9,4,90),
(11,1,60),(11,2,77),(11,3,68),(11,4,88),
(15,1,88),(15,2,45),(15,3,66),(15,4,75)

-- 江文、数据库高级应用、56
-- 江文、javascript编程基础、80
-- 江文、web前端程序设计基础、75
-- 江文、动态网页设计.net基础、66
-- 钟琪、数据库高级应用、88
-- 钟琪、javascript编程基础、79
-- 钟琪、web前端程序设计基础、72
-- 钟琪、动态网页设计.net基础、85
-- 曾小林、数据库高级应用、68
-- 曾小林、javascript编程基础、88
-- 曾小林、web前端程序设计基础、73
-- 曾小林、动态网页设计php基础、63
insert into Score(studentid,courseid,score)
values(1,1,56),(1,2,80),(1,3,75),(1,4,66),
(2,1,88),(2,2,79),(2,3,72),(2,4,85),
(3,1,68),(3,2,88),(3,3,73),(3,5,63)
-- 欧阳天天、数据库高级应用、84
-- 欧阳天天、javascript编程基础、90
-- 欧阳天天、web前端程序设计基础、92
-- 欧阳天天、动态网页设计php基础、78
-- 徐长卿、数据库高级应用、58
-- 徐长卿、javascript编程基础、59
-- 徐长卿、web前端程序设计基础、65
-- 徐长卿、动态网页设计php基础、75
-- 李逍遥、数据库高级应用、48
-- 李逍遥、javascript编程基础、67
-- 李逍遥、web前端程序设计基础、71
-- 李逍遥、动态网页设计.net基础、56
-- 李逍遥、数据库高级应用、48
insert into Score(studentid,courseid,score)
values(4,1,84),(4,2,90),(4,3,92),(4,5,78),
(5,1,58),(5,2,59),(5,3,65),(5,5,75),
(6,1,68),(6,2,88),(6,3,73),(6,4,63),(6,1,68)


update student set studentbirth='2000-04-06' where studentname='欧阳天天'
update score set score=61 where studentid=5 and scoreid=2 
 update student set studentsex=2,classid=1 where studentname='江文'
 update student set classid=2 where studentname='徐长卿'or studentname='李逍遥'
 update Course set CourseCredit=CourseCredit+1
 select distinct studentID from student

 select * from class
 select * from student
 select * from Score
 select * from Course
 select * from ClassCourse
 select studentname,studentaddress from student,class
 select top 20 percent * from student
 select distinct classid from student 
 select distinct studentid from classcourse,student
 select * from Course order by Courseid desc
 select * from score order by score
 select distinct * from student order by studentbirth desc
 select studentid,studentname,studentsex,studentbirth,studentaddress from student where classid=1
 select studentid,studentname,studentsex,studentbirth,studentaddress from student where studentsex=2
 select distinct studentid,studentname,studentsex,studentbirth,studentaddress from student where (studentbirth>='2000-01-01' and studentbirth<='2000-12-31')