--------------------
-- 建库部分
--------------------
-- 创建学生管理系统数据库，名称为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);
-- 分数表 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 ('软件一班');
insert into Class (ClassName) values ('软件二班');
insert into Class (ClassName) values ('计算机应用技术班');

-- 软件一班有3个同学，姓名、性别、生日、家庭住址  分别是：
--	刘正、男、2000-01-01、广西省桂林市七星区空明西路10号鸾东小区
--	黄贵、男、2001-03-20、江西省南昌市青山湖区艾溪湖南路南150米广阳小区
--	陈美、女、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-10','安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光', 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,NULL,'广东省广州市白云区金沙洲岛御洲三街恒大绿洲', 3);

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

insert into ClassCourse (ClassId, CourseId) values (1, 1);
insert into ClassCourse (ClassId, CourseId) values (1, 2);
insert into ClassCourse (ClassId, CourseId) values (1, 3);
insert into ClassCourse (ClassId, CourseId) values (1, 4);

-- 软件二班开设课程，课程名称和学时分别为：
--	数据库高级应用、3
--	javascript编程基础、3
--	web前端程序设计基础、4
--	动态网页设计.net基础、6

insert into ClassCourse (ClassId, CourseId) values (2, 1);
insert into ClassCourse (ClassId, CourseId) values (2, 2);
insert into ClassCourse (ClassId, CourseId) values (2, 3);
insert into ClassCourse (ClassId, CourseId) values (2, 4);

-- 计算机应用技术班开设课程，课程名称和学时分别为：
--	数据库高级应用、3
--	javascript编程基础、3
--	web前端程序设计基础、4
--	动态网页设计php基础、6
insert into Course(CourseName, CourseCredit) values ('动态网页设计php基础', 6);

insert into ClassCourse (ClassId, CourseId) values (3, 1);
insert into ClassCourse (ClassId, CourseId) values (3, 2);
insert into ClassCourse (ClassId, CourseId) values (3, 3);
insert into ClassCourse (ClassId, CourseId) values (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 (1, 1, 80);
insert into Score (StudentId, CourseId, Score) values (1, 2, 78);
insert into Score (StudentId, CourseId, Score) values (1, 3, 65);
insert into Score (StudentId, CourseId, Score) values (1, 4, 90);

insert into Score (StudentId, CourseId, Score) values (2, 1, 60);
insert into Score (StudentId, CourseId, Score) values (2, 2, 77);
insert into Score (StudentId, CourseId, Score) values (2, 3, 68);
insert into Score (StudentId, CourseId, Score) values (2, 4, 88);

insert into Score (StudentId, CourseId, Score) values (3, 1, 88);
insert into Score (StudentId, CourseId, Score) values (3, 2, 45);
insert into Score (StudentId, CourseId, Score) values (3, 3, 66);
insert into Score (StudentId, CourseId, Score) values (3, 4, 75);
go

--	江文、数据库高级应用、56
--	江文、javascript编程基础、80
--	江文、web前端程序设计基础、75
--	江文、动态网页设计.net基础、66
--	钟琪、数据库高级应用、88
--	钟琪、javascript编程基础、79
--	钟琪、web前端程序设计基础、72
--	钟琪、动态网页设计.net基础、85

insert into Score (StudentId, CourseId, Score) values (4, 1, 56);
insert into Score (StudentId, CourseId, Score) values (4, 2, 80);
insert into Score (StudentId, CourseId, Score) values (4, 3, 75);
insert into Score (StudentId, CourseId, Score) values (4, 4, 66);

insert into Score (StudentId, CourseId, Score) values (5, 1, 88);
insert into Score (StudentId, CourseId, Score) values (5, 2, 79);
insert into Score (StudentId, CourseId, Score) values (5, 3, 72);
insert into Score (StudentId, CourseId, Score) values (5, 4, 85);


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

insert into Score (StudentId, CourseId, Score) values (7, 1, 84);
insert into Score (StudentId, CourseId, Score) values (7, 2, 90);
insert into Score (StudentId, CourseId, Score) values (7, 3, 92);
insert into Score (StudentId, CourseId, Score) values (7, 5, 78);

insert into Score (StudentId, CourseId, Score) values (8, 1, 58);
insert into Score (StudentId, CourseId, Score) values (8, 2, 59);
insert into Score (StudentId, CourseId, Score) values (8, 3, 65);
insert into Score (StudentId, CourseId, Score) values (8, 5, 75);

insert into Score (StudentId, CourseId, Score) values (9, 1, 48);
insert into Score (StudentId, CourseId, Score) values (9, 2, 67);
insert into Score (StudentId, CourseId, Score) values (9, 3, 71);
insert into Score (StudentId, CourseId, Score) values (9, 5, 56);
insert into Score (StudentId, CourseId, Score) values (9, 5, 56);
go

--------------------
-- 删除数据部分
--------------------
-- 分数表这边 分数表 最后一条插入重复了，请帮忙删除这条数据
select * from Score order by ScoreId desc;
delete from Score where ScoreId = 37;

--------------------
-- 修改数据部分
--------------------
-- 计算机应用技术班 的 欧阳天天 生日写错了，正确的生日应该是：2000-04-06，请用sql进行修改。（update）
update Student set StudentBirth='2000-04-06' where StudentId = 7;
go
-- 计算机应用技术班 的 徐长卿 的 javascript编程基础 分数填错，正确的分数应该是：61，请用sql进行修改。（update）
update Score set Score = 61 where StudentId = 8 and CourseId = 2;
go


select * from Class;
select * from Student;
select * from Course;
select * from ClassCourse;
select * from Score;

select studentid 学生id,studentname 学生姓名, studentsex 性别,studentbirth 生日,StudentAddress 住址 from student;
select studentid 学生id,studentname 学生姓名, studentsex 性别,studentbirth 生日,StudentAddress from student where   studentsex=2 ;
select  studentid 学生id,studentname 学生姓名, studentsex 性别,studentbirth 生日,StudentAddress  from student where StudentBirth between '2000-01-01' and '2000-12-31';

create table  stuinfo(
stuno nvarchar (50) not null,
stuname nvarchar (50),
stuage int not null,
stuaddress nvarchar(255) not null,
stuseat int not null identity(1, 1),
stusex	tinyint not null,
);
create table stuexam(
examno int identity(1, 1) not null,
stuno   nvarchar(255) not null,
writtenexam int not null,
labexam int not null,
);
insert into stuinfo(stuno,stuname,stuage,stuaddress,stusex)
values('s2501','张秋利',20,'美国硅谷',1)
insert into stuinfo(stuno,stuname,stuage,stuaddress,stusex)
values('s2502','李斯文',18,'湖北武汉',0)
insert into stuinfo(stuno,stuname,stuage,stuaddress,stusex)
values('s2503','马文才',22,'湖南长沙',1)
insert into stuinfo(stuno,stuname,stuage,stuaddress,stusex)
values('s2504','欧阳俊雄',21,'湖北武汉',0)
insert into stuinfo(stuno,stuname,stuage,stuaddress,stusex)
values('s2505','梅超风',20,'湖北武汉',1)
insert into stuinfo(stuno,stuname,stuage,stuaddress,stusex)
values('s2506','陈旋风',19,'美国硅谷',1)
insert into stuinfo(stuno,stuname,stuage,stuaddress,stusex)
values('s2507','陈风',20,'美国硅谷',0)

insert into stuexam(stuno,writtenexam,labexam)
values('s2501',50,70),
       ('s2502',60,65),
	   ('s2503',86,85),
	   ('s2504',40,80),
	   ('s2505',70,90),
	   ('s2506',85,90)
	    
select stuno 学号,stuname 姓名,stuage 年龄,stuaddress 住址,stuseat 座位号,stusex 性别 from stuinfo;
select stuname,stuage, stuaddress from stuinfo;
select stuno as  学号, writtenexam 笔试,机试=labexam from stuexam;
select stuno 学号,stuname 姓名,stuaddress 住址,stuname+'@'+stuaddress 邮箱 from stuinfo;
select stuno,writtenexam,labexam examno from stuexam;