数据库课程设计操作代码.sql

这个是我二年级写的一个教务管理系统 大家可以下来

--创建数据库
create database 教务管理系统
on
(
name=教务管理系统_data,
filename='F:\sql作业\教务管理系统_data.mdf',
size=20MB,
maxsize=200MB,
filegrowth=5MB
)
log on
(
name=XSGL_log,
filename='F:\sql作业\XSGL_log.ldf',
size=5MB,
maxsize=50MB,
filegrowth=1MB
)
--创建学生表
create table student (
     sname varchar(10) not null,
     sno char(10) not null unique,    
     ssex char(2) default '男',
     classno char(10) not null,
     birthday  smalldatetime  not null,
     address  char(50),
     Primary key (sno),
     )
--创建教师表
create table teacher (
     tno char(10) not null unique,
     tname varchar(10) not null, 
     tsex char(2) not null ,
     birthday  smalldatetime ,
     duty char(10) not null,--职务
     post char(10), --职称
     politic char(10),--政治面貌
     office char(10),--办公室
     telephone  char(10) not null ,
     address char(50),--住址
     Primary key (tno));
--创建班级表
     create table class (
     classno char(10) not null unique,
     departmentno varchar(50) not null ,
     gross_number  int  ,
     enter_date  smalldatetime,
     monitor  varchar(10),
     major char(10),--专业
     Primary key (classno));

--创建课程信息表
create table course (
     cno char(10) not null unique ,
     cname varchar(20) not null ,
     tno  char(10),
     credit int not null ,--学分
     period int not null ,--学时
     Primary key (cno));

--创建学生成绩表
create table grade (
     sno char(10) not null unique,
     cno char(10) not null,
     grade int not null,
     Primary key (sno));
--创建系信息表
create table department (
     department_name varchar(50) not null,
     departmentno varchar(50) not null unique ,
     gross_class int  ,
     director varchar(50),--系主任
     Primary key (departmentno));
--创建授课信息表
     create table teacheing(
     tno char(10) ,
     cno char(10) primary key)

select s.sno,sname,g.cno,grade from student s,grade g
where s.sno=g.sno


select * from teacher,teacheing
where  teacher.tno=teacheing.tno

create  table sc
(sno char(10) not null unique ,
 cno char(10) not null,

)

--学生选课视图
create view courseInformation as
select s.sno,s.sname,sc.cno,c.cname
from student s,sc,course c
where s.sno=sc.sno and sc.cno=c.cno
select * from courseInformation

--建立索引
create unique index index_sno on student(sno)
create unique index index_cno on course(cno)

--delete 触发器
create trigger trigger_student on student
after delete 
as
declare @sno char(10),@classno char(10) 
begin 
select @sno=sno,@classno=classno
from deleted 
delete from grade where sno=@sno
update class set gross_number=gross_number-1
where classno=@classno
end
--检验
 delete from student where sno='20043306'
--insert 触发器
create trigger insert_trigger on student
after insert
as
declare @classno char(10)
begin select @classno=classno
from inserted
update class set gross_number=gross_number+1
where classno=@classno
end
insert into student(sname,sno,ssex,classno) values('各个','20043319','女','20041011')
---供选课程表（视图）
create view for_select_course 
as 
select c.cno,c.cname,t.tno,t.tname,t.politic
from course c,teacher t
where c.tno=t.tno

--测试
select * from for_select_course
--创建存储过程（1）
create procedure proc_by_sno 
@sno char(10)
as 
select * from student where sno= @sno

execute proc_by_sno '20043309'

--存储过程（2）
create procedure proc_by_sname
@sname char(10)
as
select * from student where sname=@sname
--存储过程（3）
create procedure pro_by_class
@classno char(10)
as
select * from student where classno=@classno
--实施check约束
alter table grade
add constraint ck_grade
check (grade>0 and grade<100)

--视图创建班级成绩单
create view total_mark_by_classno
as
select  s.sno,s.sname, sum(grade) 总成绩
from student s,grade g
where s.sno=g.sno
group by s.sno,s.sname
order by sum(grade) desc




select  s.sno,s.sname, sum(grade) from student
 s,grade g,class cla where s.sno=g.sno 
and cla.classno='20041016'group by s.sno,s.sname
--规则
create rule  polictic as @政治面貌 like '党员[团员][群众]'
--绑定规则
sp_bindrule  polictic,  'teacher.politic'