--使用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())	--创建时间，默认为系统时间
)


select StuId 学号 ,StuName 姓名,StuSex 性别 from StuInfo

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
--查询学生信息表（stuInfo）中的学号，姓名，地址，以及将：姓名+:+手机号 组成新列 “邮箱”

select 邮箱=(StuName+':'+StuPhone) from StuInfo
--查询学生信息，省份是广东省或者是广西省的，用in
select *  from StuInfo where StuProvince in('广东省','广西省')


--比较运算符
--逻辑运算符：and or not
--范围运算符：表达式 between A and B,范围是[A,B]闭区间
--			  表达式 not between A and B,范围不在[A,B]闭区间，在（-∞，a) or (b,+∞）范围
--列表运算符：in(项1,项2,......); 
--			  not in(项1,项2,......)
--空值运算符：is null 判断字段是否为空值，
--			  is not null
--			
--查询学号1-5的每个学生的总成绩和平均分信息，并且只需要平均分大于等于60分数据，同时按照总分的降序排列
select StuId ,avg(Score) 平均分, sum(Score) 总分 from Scores
where StuId between 1 and 5 
group by StuId
having avg(Score)>=60 
order by sum(Score) desc

--条件查询
--1.连接多个条件，范围查询
--查询性别为男，成绩在80-90的学生信息
select * from Scores
inner join StuInfo on StuInfo.StuId=Scores.StuId
where StuSex='男' and Score between 80 and 90

--3.列表运算符
--查询学生省份为广东、广西的信息
select * from StuInfo where StuProvince in('广东省','广西省')


--4.空值运算符
--查询没有省份信息的学生信息
select * from StuInfo where StuProvince is null
select * from StuInfo where StuProvince is not null
--5.模糊查询
--查询学生姓名包含天的学生信息
select * from StuInfo where StuName like '%天%'

--查询学生姓名以天结束的三个字学生信息
select * from StuInfo where StuName like '__天'


--6.聚合查询
--查询分数大于60的成绩信息数
select count(Score)分数大于60的成绩信息数 from Scores where Score>60

--7.分组聚合查询（结果过滤）
--按学号进行分组统计，并只显示平均分大于60的信息
select StuId 学号,AVG(Score)平均分 from Scores
group by StuId
having avg(Score)>60

--8.排序
--查询在80-90之间的成绩信息，并按照倒序进行排列
select * from Scores where Score between 80 and 90 order by Score desc

--9.连接查询
--查询成绩第一名的同学的学生信息，姓名，性别，年龄
select * from Scores where Score in (select max(Score) from Scores
inner join StuInfo on StuInfo.StuId=Scores.StuId)

select StuName 姓名,StuSex 性别,StuBrithday from Scores
inner join StuInfo on StuInfo.StuId=Scores.StuId
group by StuName,StuSex,StuBrithday,Score
having score in (select MAX(Score)from Scores)