--如下,可直接复制到SQLServer的查询中执行use master
go
--创建数据库
if(db_id('studentDB')) is not null
drop database [studentDB]
create database [studentDB]
go
use [studentDB]
go
--创建学生表
create table [student]
(
[studentId] int identity(1,1)
primary key,
[stuName] varchar(50) not null
)
gouse [studentDB]
go
--创建成绩表
create table [score]
(
[studentId] int not null,
[subjectName] varchar(50) not null,
[score] int
)
go
--添加外键约束
--学生ID
alter table [score]
add constraint FK_score_student
foreign key([studentId])
references [student] ([studentId])
go
--插入测试数据
insert [student]
select '张三' union
select '李四' union
select '
王五' union
select '高六' union
select '赵七'
goinsert [score]
select 1,'语文',50 union
select 1,'数学',51 union
select 1,'英语',52 union
select 2,'语文',60 union
select 2,'数学',61 union
select 2,'英语',62 union
select 3,'语文',70 union
select 3,'数学',71 union
select 3,'英语',72 union
select 4,'数学',null union
select 4,'语文',100 union
select 4,'英语',null
go
use [studentDB]
go
--创建返回学生选课情况的
存储过程alter procedure [pro_GetStudentScore]
@subjectName varchar(50)
as
select stu.[studentId],stu.[stuName],isnull(sco.[subjectName],'该生没选择任何课程') [课程],isnull(convert(varchar(50),sco.[score]),'成绩表中没有这门课的成绩') [成绩]
from [student] stu left join [score] sco on(stu.[studentId]=sco.[studentId])
where sco.[subjectName]=@subjectName
go
--exec [pro_GetStudentScore] '英语'
--select * from score