1. 建立数据库
create database homework
use homework
go
2. 创建表格
- 创建学籍表
```
create table student(
Sid int not null unique,
Sname varchar(10) ,
primary key(Sid)
);
```
- 创建课程表
create table course(
Cid int not null unique,
Cname varchar(10) ,
primary key(Cid)
);
- 创建成绩单
create table sc(
Cid int not null,
Sid int not null,
grade int,
primary key(Sid,Cid)
);
3. 插入数据
- 学籍表数据
insert into student(Sid,Sname)values (201701,'张三');
insert into student(Sid,Sname)values (201702,'李四');
insert into student(Sid,Sname)values (201703,'王五');
insert into student(Sid,Sname)values (201704,'孙六');
- 课程表数据
insert into course(Cid,Cname)values (220001,'数据库');
insert into course(Cid,Cname)values (220002,'操作系统');
insert into course(Cid,Cname)values (220003,'编译原理');
insert into course(Cid,Cname)values (220004,'计算机网络');
- 成绩单数据
--数据库成绩
insert into sc(Cid,Sid,grade)values(220001,201701,90);
insert into sc(Cid,Sid,grade)values(220001,201702,80);
insert into sc(Cid,Sid,grade)values(220001,201703,85);
insert into sc(Cid,Sid,grade)values(220001,201704,70);
--操作系统成绩
insert into sc(Cid,Sid,grade)values(220002,201701,55);
insert into sc(Cid,Sid,grade)values(220002,201702,73);
insert into sc(Cid,Sid,grade)values(220002,201703,66);
insert into sc(Cid,Sid,grade)values(220002,201704,77);
--编译原理成绩
insert into sc(Cid,Sid,grade)values(220003,201701,78);
insert into sc(Cid,Sid,grade)values(220003,201702,87);
insert into sc(Cid,Sid,grade)values(220003,201703,66);
insert into sc(Cid,Sid,grade)values(220003,201704,56);
--计算机网络
insert into sc(Cid,Sid,grade)values(220004,201701,45);
insert into sc(Cid,Sid,grade)values(220004,201702,68);
insert into sc(Cid,Sid,grade)values(220004,201703,98);
insert into sc(Cid,Sid,grade)values(220004,201704,82);
4.显示结果
- 使用内连接查询显示成绩
select student.Sname as '姓名',course.Cname as '课程',sc.grade as '成绩'
from sc join course
on sc.Cid=course.Cid
join student
on sc.Sid=student.Sid
order by course.Cid ASC ,student.Sid ASC;
- 使用左外连接查询显示成绩
select student.Sname as '姓名',b1.grade as '数据库',b2.grade as
'操作系统',b3.grade as '编译原理',b4.grade as '计算机网络'
from student
left join sc as b1
on b1.Cid = 220001 and b1.Sid=student.Sid
left join sc as b2
on b2.Cid = 220002 and b2.Sid=student.Sid
left join sc as b3
on b3.Cid = 220003 and b3.Sid=student.Sid
left join sc as b4
on b4.Cid = 220004 and b4.Sid=student.Sid
- 使用子查询显示成绩
select Sname,
(select grade from sc s1 where s1.Sid=st.Sid and s1.Cid=220001) as '数据库',
(select grade from sc s1 where s1.Sid=st.Sid and s1.Cid=220002) as '操作系统',
(select grade from sc s1 where s1.Sid=st.Sid and s1.Cid=220003) as '编译原理',
(select grade from sc s1 where s1.Sid=st.Sid and s1.Cid=220004) as '计算机网络'
from student st
结果与2中使用左外连接查询一致。
- 使用case语句查询成绩
select student.Sname as '姓名',
sum(case Cid when '220001' then grade else null end) as '数据库',
sum(case Cid when '220002' then grade else null end) as '操作系统',
sum(case Cid when '220003' then grade else null end) as '编译原理',
sum(case Cid when '220004' then grade else null end) as '计算机网络'
from sc,student
where student.Sid=sc.Sid
group by student.Sname
- 使用存储过程，自行判断课程的数量
```
declare @sql varchar(1000);
set @sql = 'select student.Sname as ''姓名'',';
select @sql = @sql+'
sum(case Cname when '''+Cname+'''then grade else null end) as '''+Cname+''','
from course
set @sql =left(@sql,len(@sql)-1)+'
from sc,student,course
where student.Sid=sc.Sid and course.Cid=sc.Cid
group by student.Sname'
exec(@sql);
```
结果和4中使用case语句查询一致。
