SQL数据库复习2

根据项目要求，创建数据库，创建各种表格及相应的关系

if exists(select * from sysdatabases where name='SchoolDB')
drop database SchoolDB
go


create database SchoolDB
on primary(
name='Stu_data',
filename='D:\Stu_data.mdf',
size=5mb,
maxsize=unlimited,
filegrowth=5%
)
log on(
name='Stu_log',
filename='D:\Stu_log.ldf',
size=5mb,
maxsize=unlimited,
filegrowth=5%
)
go
 use SchoolDB
 if exists(select * from sysobjects where name='T_student')
 drop table T_student
 go


 create table T_student
 (
 StuID varchar(20) primary key not null,
 StuName varchar(50) not null,
 StuSex varchar (2) not null,
 StuBirthday datetime not null,
 Class varchar (30) not null
 )
 go


if exists(select * from sysobjects where name='T_teacher')
drop table T_teacher
go
create table T_teacher
(
TeacherID varchar(20) primary key not null,
TName varchar(50) not null,
Tbirthday datetime not null,
Tsex char(2) not null,
Post varchar(20) not null
)
go


if exists(select * from sysobjects where name='T_course')
drop table T_course
go
create table T_course 
(
CouID varchar(20) primary key not null,
TeacherID varchar(20) ,
CouName varchar(50) not null,
Credit float not null,
Period int not null
)
go
if exists(select * from sysobjects where name='T_score')
drop table T_score
go
create table T_score
(
StuID varchar(20) primary key not null,
CouID varchar(20) not null,
grade float 
)
go
alter table T_score add constraint FK_StuID foreign key(StuID) references T_student(StuID)
go
alter table T_score add constraint FK_CouID foreign key(CouID) references T_course(CouID)
go
alter table T_course add constraint FK_TeacherID foreign key(TeacherID) references T_teacher(TeacherID)
go


 --alter table T_course drop FK_TeacherID


alter table T_teacher add constraint CK_Post check ( Post='助教'or Post='讲师'or Post='副教授'or Post='教授')
go
alter table T_teacher drop CK_Post


insert into T_student values ('STU001','张三','男',1992-10-30,'网络1011')
go
insert into T_student values ('STU002','王鑫','男',1991-6-9,'软件1011')
insert into T_student values('STU003','杨晓霞','女',1993-11-20,'商务1011')
go
insert into T_teacher values('TCH001','高海平',1965-6-23,'男','教授')
insert into T_teacher values('TCH002','赵强',1978-20-25,'男','副教授')
insert into T_teacher values ('TCH003','周小平',1980-11-9,'男','讲师')
insert into T_teacher values('TCH004','张倩',1985-11-20,'女','助教')
go
insert into T_course values ('COU001','TCH001','软件工程',3,60)
insert into T_course values ('COU002','TCH002','C语言程序设计',3,90)
insert into T_course values ('COU003','TCH003','数据结构',4,90)
go


insert into T_score values('STU001','COU001',90)
insert into T_score values('STU002','COU002',90)
insert into T_score values('STU003','COU003',90)
go


insert into T_student values('STU005','刘忠','男',1992-10-12,'信息1211')
go


update T_course set Credit=5 where CouName='数据结构'
go


select CouID ,CouName from T_course  where Credit <= 3 
go


select StuName from T_student  
select StuID from T_score
select CouID from T_course where CouName='C语言程序设计'
select StuName from T_student where StuID in(select StuID from T_score where CouID in (select CouID from T_course where CouName='C语言程序设计'))
go


--创建视图--


if exists(select * from sysobjects where name='a_View')
drop view a_View
go
create view a_View as
select 学生姓名=s.StuName,老师姓名=t.TName,考试成绩=sc.grade from T_student s,T_teacher t ,T_score sc,T_course c
where sc.StuID=s.StuID and sc.CouID=c.CouID and c.TeacherID=t.TeacherID
go
select * from a_View


select * from T_score
select * from T_course
select * from T_student
select * from T_teacher
  
 if exists(select * from sysobjects where name='P_avg' and type='P')
drop proc P_avg
go
 create proc P_avg 
 (
@kename varchar(30)
 )
 as
select avg(s.grade) from T_course c,T_score s where c.CouID=s.CouID and  c.CouName=@kename
 go


 exec P_avg '数据结构'