--使用master数据库
use master
go

--如果存在TestDB数据库删除数据库
if exists(select * from sys.databases where name='TestDB')
	drop database TestDB
go

--创建数据库
create database TestDB
go
--go批处理标志，分割sql文件；等待前面语句执行完毕再执行后面的sql语句

--使用TestDB数据库
use TestDB 
go


-----------------
--建表部分
-----------------

--创建班级表
create table ClassInfo
(
	ClassId int identity(1,1) primary key,  --主键，班级编号，标识列
	ClassName nvarchar(20) not null			--班级名称，非空
)
go

--创建学生信息表
create table StuInfo
(
	StuId int identity(1,1) primary key,	--主键，学号，标识列
	ClassId int references ClassInfo(ClassId) on delete set null, --所属班级编号，外键关联班级表的班级编号
	StuName nvarchar(10) not null,		--姓名，非空
	StuSex  nvarchar(1) default('男') check(StuSex in('男','女')),	--性别
	StuBrithday date,	--出生日期
	StuPhone nvarchar(11) check(len(StuPhone)=11) unique,--手机号，限制11位,唯一不重复
	StuProvince nvarchar(200),--地址
	CreateDate datetime default(getdate())	--创建时间，默认为系统时间
)
go

--创建课程信息表
create table CourseInfo
(
	CourseId int identity(1,1) primary key,	--课程编号，主键，标识列
	CourseName nvarchar(50) unique not null ,--课程名称，非空，唯一不重复
	CourseCredit int default(1) check(CourseCredit between 1 and 5) --学分，默认值为1，取值范围1-5
)
go

--创建成绩表
create table Scores
(
	ScoreId int identity(1,1) primary key,--成绩编号，主键，标识列
	StuId int references StuInfo(StuId),--学号，外键关联学生信息表的学号
	CourseId int references CourseInfo(CourseId),--课程编号，外键关联课程信息表的课程编号
	Score int default(0)	--成绩,默认为0
)
go



----------------
--插入数据部分
----------------
--插入班级信息表
insert into ClassInfo(ClassName)
values('软件1班'),('软件2班'),('软件3班'),('软件4班'),('软件5班'),('软件6班'),('软件7班')
go


--插入学生信息
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(1,'刘正','男','2002-08-02','13245678121','广西省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(1,'黄贵','男','2003-07-02','13345678121','江西省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(1,'陈美','女','2002-07-22','13355678125','福建省')

insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(2,'江文','男','2001-07-02','13347678181','湖南省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(2,'钟琪','女','2003-01-13','13345778129','安徽省')


insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(3,'曾小林','男','2003-05-15','13345378563','安徽省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(3,'欧阳天天','女','2002-08-19','13347878121','湖北省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(3,'李逍遥','男','2003-09-02','13345678557','广东省')


insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(4,'刘德华','男','2003-06-11','15345679557',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(4,'刘翔','男','2003-07-09','18346679589',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(4,'曾小贤','男','2003-07-02','18348979589',null)


insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'刘','男','2002-07-02','18348979509',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'陈天翔','男','2002-07-02','18348079509',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'刘能','男','2002-08-02','13245678122','广西省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'钟馗','男','2002-08-02','13245678123','广西省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'钟吴艳','女','2002-08-02','13245678124','广西省')

insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'刘欢','男','2002-07-02','13245678125',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'张庭','女','2002-07-02','13245678126',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'曹植','男','2002-08-02','13245678127','')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'曹操','男','2002-08-02','13245678128','')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'孙尚香','女','2002-08-02','13245678129','')
go



--插入课程信息
insert into CourseInfo(CourseName,CourseCredit) values('计算机基础',3)
insert into CourseInfo(CourseName,CourseCredit) values('HTML+CSS网页制作',5)
insert into CourseInfo(CourseName,CourseCredit) values('JAVA编程基础',5)
insert into CourseInfo(CourseName,CourseCredit) values('SQL Server数据库基础',4)
insert into CourseInfo(CourseName,CourseCredit) values('C#面向对象编程',5)
insert into CourseInfo(CourseName,CourseCredit) values('Winform桌面应用程序设计',5)
go

--插入成绩信息
insert into Scores (StuId, CourseId, Score) values (1, 1, 80);
insert into Scores (StuId, CourseId, Score) values (1, 2, 78);
insert into Scores (StuId, CourseId, Score) values (1, 3, 65);
insert into Scores (StuId, CourseId, Score) values (1, 4, 90);

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

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

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

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

insert into Scores (StuId, CourseId, Score) values (6, 1, 68);
insert into Scores (StuId, CourseId, Score) values (6, 2, 88);
insert into Scores (StuId, CourseId, Score) values (6, 3, 73);
insert into Scores (StuId, CourseId, Score) values (6, 5, 63);

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

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

insert into Scores (StuId, CourseId, Score) values (9, 1, 48);
insert into Scores (StuId, CourseId, Score) values (9, 2, 67);
insert into Scores (StuId, CourseId, Score) values (9, 3, 71);
insert into Scores (StuId, CourseId, Score) values (9, 5, 56);
insert into Scores (StuId, CourseId, Score) values (9, 5, 56);
go
-------------------------------------
--对查询结果进行排序
----------------------------------
--查询成绩信息，成绩降序排列
  select 成绩=Score from Scores order by Score desc

--查询成绩信息，先按照课程编号排序，再进行成绩的降序排列
  select 成绩=Score from Scores order by CourseId asc,Score desc

--查询学号为1的成绩信息，并将结果按照成绩降序排列
  select * from Scores where  StuId=1 order by score  desc

--------------------------------------------------
--聚合函数
--------------------------------------------------
--1.查询学号为1的所有课程的总成绩
  select 课程总成绩=sum(Score) from Scores where StuId=1

--2.查询成绩表中所有成绩的总和
  select 所有成绩表总和=sum(Score) from Scores 

--3.查询学号为1的平均成绩
  select 平均成绩=AVG(Score) from Scores where StuId=1

--4.查询课程编号为1的最高成绩信息
  select 最高成绩=max(Score) from Scores where StuId=1

--5.查询课程编号为1的最低成绩信息
    select 最低成绩=min(Score) from Scores where StuId=1

--6.查询学号为1的学生参加的考试次数
    select 参加考试数=count(*) from Scores where StuId=1

--7.查询课程编号为1的成绩统计信息：参考人数信息、总分、平均分、最高分、最低分
    select 参考人数信息=count(*),总分=sum(Score),平均分=avg(Score),最高分=max(Score),最低分=min(Score) from Scores where CourseId=1

--8.查询学号为1的学生的成绩统计信息：总分、平均分、最高分、最低分
    select 总分=sum(Score),平均分=avg(Score),最高分=max(Score),最低分=min(Score) from Scores where StuId=1

-------------------------------------------------------
--分组查询
--------------------------------------------------------
--9.查询每个学生的总成绩
    select 学号=StuId,总成绩=sum(Score) from Scores group by StuId


--10.查询每个学生的成绩统计信息：参考次数、总分、平均分、最高分
    select 参考次数=count(*),总分=sum(Score),平均分=avg(Score),最高分=max(Score) from Scores group by StuId


--11.查询每门课程的成绩统计信息：参考人数信息、总分、平均分、最高分、最低分
    select 参考次数=count(*),总分=sum(Score),平均分=avg(Score),最高分=max(Score),最低分=min(Score) from Scores group by CourseId

--12.查询学号1-5的学生的总成绩信息
    select 总成绩=sum(Score) from Scores where StuId between 1 and 5

--13.根据学号进行分组统计总分信息，只显示总分大于300的信息

--14.根据学号进行分组统计平均分信息，只显示平均分小于60的信息




-------------------------------------------
--对查询结果限制行数
------------------------------------------
--查询成绩表中学号信息

--查询成绩表中最高分的成绩信息

--查询成绩表中的前10条数据

--查询排名在前10%的学生成绩


--获取成绩表中第11-20条的记录