使用T-SQL实现以下要求:
要求如下:
1,添加数据库:MySchool
2,添加学生基础表:Student
3,添加学生成绩表:ScoreInfo
4,两张表结构分别如下
Student表结构:(20分)
变量名 | 数据类型 | 是否为空 | 描 述 |
StuID | int |
| 主键,标识列,学号 |
StuName | nvarchar(20) | 否 | 学生姓名 |
StuSex | nchar(1) | 否 | 性别(必须是男或女) |
StuGrade | int | 否 | 年级编号 |
StuEmail | nvarchar(20) | 是 | 邮箱 |
StuAddress | nvarchar(20) | 是 | 地址(默认“四川”) |
ScoreInfo表结构(20分)
变量名 | 数据类型 | 是否为空 | 描 述 |
ID | int |
| StudentInfo的表外键ID,学号 |
ScoreInfo | float | 是 | 分数(分数必须:分数 大于-1并且小于150) |
SubjectId | int | 否 | 科目编号 |
ExamDate | datetime | 否 | 考试时间 |
5,根据表结构添加关系和约束。(20分)
6,使用T-SQL添加表数据(每张表数据3行以上,10分)
StudentInfo表数据
ID | Name | SexID | Grade | | Address |
1 | 张三 | 男 | 1 | zhangsan@qq.com | 南京 |
2 | 王五 | 男 | 2 | wangwu@qq.com | 深圳 |
3 | 张丽 | 女 | 1 | zhangli@qq.com | 四川 |
… | … | … | … | … | … |
Score表数据
ID | ScoreInfo | SubjectId | ExamDate |
1 | 65 | 1 | 2018-7-21 |
1 | 75.5 | 2 | 2018-7-21 |
2 | 87 | 1 | 2018-7-21 |
2 | 46 | 2 | 2018-7-21 |
3 | 53.6 | 1 | 2018-7-21 |
… | … | … | … |
7,使用T-SQL查询出学员“张三“所有科目成绩。(15分)
8,使用T-SQL查询出所有大于60分以上学员成绩。(15分)
代码如下:
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)
如有不对的地方,欢迎指出!
如有更好的实现方法,欢迎分享!