use master
go
--判断数据库是否存在，有则删除
if (exists (select * from sys.databases where name = 'MySchol'))
    drop database MySchol
--创建数据库
create database MySchol
on(
    name = 'MySchol',
    filename = 'S:\SQL\MySchol.mdf'
)
log on(
    name = 'testHome_log',
    filename = 'S:\SQL\MySchol_log.ldf'
)
go

-- 使用(切换到数据库)数据库
use MySchol
--判断数据库表是否存在，有则删除
if (exists (select * from sys.objects where name = 'Student'))
    drop database Student
--创建表Student
create table Student(
    StuID int not null,
    StuName nvarchar(20) not null,
    StuSex nchar(1) not null,
    StuGrade int not null,
    StuEmail nvarchar(20) null,
		StuAddress nvarchar(20) null DEFAULT '四川'
)

if (exists (select * from sys.objects where name = 'ScoreInfo'))
    drop database ScoreInfo

create table ScoreInfo(
    ID int not null,
    ScoreInfo float null,
    SubjectId int not null,
    ExamDate datetime not null,
    StuEmail nvarchar(20) null,
)
go

--添加Student表主键
alter table Student
add constraint pk_id primary key(StuID);
--性别约束
alter table Student
add constraint xingbie check(StuSex='男' or StuSex='女');
--添加外键约束
alter table ScoreInfo
add constraint fk_cid foreign key (ID) references Student(StuID)
--分数约束
alter table ScoreInfo
add constraint fenshu check(ScoreInfo > -1 AND ScoreInfo < 150);

--Student插入数据
insert into Student values(1, '张三', '男', 1, 'zhangsan@qq.com', '南京');
insert into Student values(2, '王五', '男', 2, 'wangwu@qq.com', '深圳');
insert into Student values(3, '张丽', '女', 1, 'zhangli@qq.com', '四川');
insert into ScoreInfo values(1, 65, 1, '2018-7-21',null);
insert into ScoreInfo values(1, 75.5, 2, '2018-7-21',null);
insert into ScoreInfo values(2, 87, 1, '2018-7-21',null);
insert into ScoreInfo values(2, 46, 2, '2018-7-21',null);
insert into ScoreInfo values(3, 53.6, 1, '2018-7-21',null);

--查询张三成绩
select ScoreInfo as '张三' from ScoreInfo;
--查询所有科目都合格的学生--由于刚学不久，只能凑合实现
SELECT StuName FROM Student WHERE StuID IN
(SELECT COUNT(*) as '合格科目数' FROM ScoreInfo WHERE ScoreInfo > 60 GROUP BY ID HAVING ID>1)