sql.sql

实现简单学生选课

-----------------------------------------------------------------------

--准备：创建数据库
use master

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'roommaster')
	DROP DATABASE [roommaster]
GO

CREATE DATABASE roommaster  
ON (NAME = N'roommaster_Data', FILENAME = N'D:\roommaster_Data.MDF' , SIZE = 5, FILEGROWTH = 10%) 
LOG ON (NAME = N'roommaster_Log', FILENAME = N'D:\roommaster_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
GO

use roommaster
go

--1、创建Department表
-------------------------------------
create table major
(
majorno char(4) primary key,
majorName varchar(30) not null unique
)
-------------------------------------
go
--2,创建student表
create table student
(
	studentno varchar(10) primary key,
	studentname varchar(20) not null,
	majorno char(4) not null foreign key references major(majorno),
	smima varchar(10) not null default '123456'
	
)

-------------------------------------
go
--3,创建teacher表
create table teacher
(
	teacherno varchar(8) primary key,
	teachername varchar(20) not null,
	tmima varchar(10) not null default '123456'
)
--------------------------------------
go
--4,创建room表
--drop table room
create table room
(	
	roomno char(10)primary key,
	located varchar(30)
)
--------------------------------------
go
--5,创建course表
--drop table course
create table course
(
	courseno char(8) primary key,
	coursename varchar(30) not null,
	majorno char(4) not null foreign key references major(majorno),
	teacherno varchar(8) not null foreign key references teacher(teacherno),
	roomno char(10) not null foreign key references room(roomno),
	thetime varchar(50) not null
)
--------------------------------------
go

--6,创建choosed表
create table choosed
(
	studentno varchar(10) not null foreign key references student(studentno),
	courseno char(8) not null foreign key references course(courseno)
)
---------------------------------------
go

--7.建立存储过程spdeleteteacher,参数@teacher,删除老师之前先删除老师所教的课程

create procedure spdeleteteacher
(
	@teacherno char(8)
)
as
begin transaction spdeleteteacher	
	delete from course where courseno in (select courseno from course where teacherno=@teacherno)
	if @@Error<>0  rollback transaction spdeleteteacher
	delete from teacher where teacherno=@teacherno
	if @@Error<>0  rollback transaction spdeleteteacher
	else commit transaction spdeleteteacher
-----------------------------------------------------
--exec spdeleteteacher '01001'
go
----向student表中增加属性coursecount----------------------------------------------------
alter table student add coursecount int not null default 0
update student set courseCount=(select count(*) from choosed where choosed.studentno=student.studentno)
-------------------------------------------------------
go
---9,触发器
create trigger tgUpdatecourseCountForInsert
on choosed
for Insert
as
	update student set courseCount=courseCount+(select count(studentno) from inserted where inserted.studentno=student.studentno) 
	where studentno in(select studentno from inserted)
go

create trigger tgUpdatecourseCountForDelete
on choosed
for delete
as
	update student set courseCount=courseCount-(select count(studentno) from deleted where deleted.studentno=student.studentno) 
	where studentno in(select studentno from deleted)
go
--------------------------------------------------------
--9，创建视图
create	view teachedby as
select studentname,teachername
from student,choosed,course,teacher
where student.studentno=choosed.studentno and choosed.courseno=course.courseno and course.teacherno=teacher.teacherno


--8.建立存储过程spdeletecourse,参数@courseno,删除课程之前先删除与该课程有关的选课信息

create procedure spdeletecourse
(
	@courseno char(8)
)
as
begin transaction spdeletecourse	
	delete from choosed where studentno in (select studentno from choosed where courseno=@courseno) and courseno=@courseno
	if @@Error<>0  rollback transaction spdeletecourse
	delete from course where courseno=@courseno
	if @@Error<>0  rollback transaction spdeletecourse
	else commit transaction spdeleteteacher

----exec spdeletecourse '00102'







---向teacher表中增加属性tcoursecount-----------------------------------------------------
alter table teacher add tcoursecount int not null default 0
update teacher set tcourseCount=(select count(*) from course where course.teacherno=teacher.teacherno)
-------------------------------------------------------
go
---9,触发器
create trigger tgUpdatetcourseCountForInsert
on course
for Insert
as
	update teacher set tcourseCount=tcourseCount+(select count(courseno) from inserted where inserted.teacherno=teacher.teacherno) 
	where teacherno in(select teacherno from inserted)
go

create trigger tgUpdatetcourseCountForDelete
on course
for delete
as
	update teacher set tcourseCount=tcourseCount-(select count(courseno) from deleted where deleted.teacherno=teacher.teacherno) 
	where teacherno in(select teacherno from deleted)
go

----------------------
---有部分存储过程和视图在企业管理器中直接创建