实验3代码.doc

create database EDUC
on
(name=student_data,
filename='D:\ student_data.mdf',
size=10MB,
maxsize=50MB,
filegrowth=5%
)
log on
(name=student_log,
filename='D:\ student_log.ldf',
size=2MB,
maxsize=5MB,
filegrowth=1MB
)

create table student
(
sno char(5) primary key,
sname char(8) not null,
ssex char(2),
dno char(20),
birthday smalldatetime,
entime smalldatetime,
native char(20),
policy char(10)
)

create table teacher
(
tno char(5) primary key,
tname char(8) not null,
sex char(2),
birthday smalldatetime,
dno char(20),
education char(10),
pname char(10)
)

create table course
(
cno char(5) primary key,
cname char(20) not null,
credit tinyint,
semester tinyint,
tno char(5),
foreign key(tno) references teacher(tno)
)

create table sc
(
sno char(5) not null,
cno char(5) not null,
score decimal(5,1),
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno),
)

alter table sc
add id int identity(1,1),qdtime datetime

alter table sc
alter column qdtime smalldatetime

alter table sc
drop column id,qdtime

insert student values ('10000','高黎明','男','计算机学院',1986-11-05,2004-9-12,'北京','团员')
insert student values ('10001','张强','男','计算机学院',1985-01-14,2004-9-12,'洛阳','党员')
insert student values ('10002','王亚伟','男','计算机学院',1988-03-04,2005-9-15,'重庆','党员')
insert student values ('10003','牛晶晶','女','计算机学院',1988-03-25,2005-9-15,'北京','团员')
insert student values ('10004','张伟','男','计算机学院',1989-10-15,2006-9-10,'上海','党员')
insert student values ('10005','马永慧','女','计算机学院',1987-06-14,2006-9-10,'广州','团员')
insert student values ('10006','孟战','男','计算机学院',1986-08-01,2006-9-10,'郑州','党员')
insert student values ('10007','黄永超','男','计算机学院',1986-07-05,2006-9-10,'重庆','党员')
insert student values ('10008','王建国','男','体育学院',1986-02-27,2005-9-15,'北京','团员')
insert student values ('10009','刘防超','男','体育学院',1986-01-03,2004-9-12,'上海','党员')
insert student values ('10010','王莹莹','女','体育学院',1986-05-10,2004-9-12,'洛阳','团员')
insert student values ('10011','张亚菲','女','体育学院',1986-05-09,2005-9-15,'郑州','党员')
insert student values ('10012','王刚','男','体育学院',1986-12-02,2005-9-15,'天津','团员')
insert student values ('10013','李刚','男','历史文化学院',1986-12-19,2006-9-10,'北京','团员')
insert student values ('10014','叶晨','女','历史文化学院',1986-11-01,2006-9-10,'武汉','团员')
insert student values('10015','罗淑燕','女','历史文化学院',1986-10-24,2005-09-15,'洛阳','党员')
insert student values('10016','薛志强','男','历史文化学院',1986-02-09,2005-09-15,'北京','党员')
insert student values('10017','汤腾飞','男','历史文化学院',1986-03-01,2005-09-15,'武汉','团员')
insert student values('10018','朱永强','男','历史文化学院',1986-06-22,2006-09-10,'天津','团员')
insert student values('10019','赵亮亮','女','外语学院',1986-06-19,2006-09-10,'北京','党员')
insert student values('10020','吴永城','男','外语学院',1986-07-04,2005-09-15,'重庆','团员')

insert teacher values('1','李雅飞','女',1968-06-25,'计算机学院','本科','教授')
insert teacher values('10','刘强','男',1976-02-07,'历史文化学院','博士','副教授')
insert teacher values('11','吕世杰','男',1978-01-25,'体育学院','本科','助教')
insert teacher values('12','李英','女',1977-03-02,'外语学院','博士','副教授')
insert teacher values('2','张晓晨','女',1971-11-03,'体育学院','研究生','教授')
insert teacher values('3','秦力伟','男',1976-07-12,'计算机学院','研究生','讲师')
insert teacher values('4','马宏伟','男',1974-02-15,'计算机学院','博士','讲师')
insert teacher values('5','常卫国','男',1965-01-25,'体育学院','本科','副教授')
insert teacher values('6','王超','男',1970-01-25,'体育学院','研究生','副教授')
insert teacher values('7','王永刚','男',1978-09-02,'历史文化学院','研究生','讲师')
insert teacher values('8','许利','女',1969-03-10,'外语学院','博士','教授')
insert teacher values('9','冯新杰','男',1965-05-05,'外语学院','本科','副教授')

insert course values('1','大学英语',6,NULL,null)
insert course values('10','模拟电路',4,null,null)
insert course values('11','日语',4,null,null)
insert course values('12','体育教育',4,null,null)
insert course values('13','足球',4,null,null)
insert course values('14','排球',4,null,null)
insert course values('16','舞蹈',4,null,null)
insert course values('17','装潢设计',4,null,null)
insert course values('18','德语',4,null,null)
insert course values('2','大学语文',6,null,null)
insert course values('3','计算机网络',4,null,null)
insert course values('4','操作系统',5,null,null)
insert course values('5','数据库应用',4,null,null)
insert course values('6','世界史',4,null,null)
insert course values('7','中国古代史',4,null,null)
insert course values('8','中国现代史',5,null,null)
insert course values('9','数据结构',6,null,null)
insert into sc values('10018','1',null)
insert into sc values('10001','14',40.0)
insert into sc values('10020','14',49.0)
insert into sc values('10007','3',50.0)
insert into sc values('10020','5',50.0)
insert into sc values('10013','14',52.0)
insert into sc values('10001','7',55.0)
insert into sc values('10015','1',55.0)
insert into sc values('10020','8',56.0)
insert into sc values('10004','9',60.0)
insert into sc values('10014','13',67.0)
insert into sc values('10019','6',68.0)
insert into sc values('10014','3',69.0)
insert into sc values('10009','3',70.0)
insert into sc values('10014','5',70.0)
insert into sc values('10019','13',70.0)
insert into sc values('10000','1',74.0)
insert into sc values('10014','1',75.0)
insert into sc values('10018','14',75.0)
insert into sc values('10004','10',76.0)
insert into sc values('10018','5',76.0)
insert into sc values('10009','12',77.0)
insert into sc values('10001','4',80.0)
insert into sc values('10008','1',80.0)
insert into sc values('10017','12',80.0)
insert into sc values('10006','5',83.0)

create table student1
(
sno char(5) primary key,
sname char(8) not null,
ssex char(2),
dno char(20),
birthday smalldatetime,
entime smalldatetime,
native char(20),

policy char(10)
)

insert into student1
select * from student

update student1
set dno='体育学院' where sno='10001'

delete from student1
where dno='体育学院'

drop table student1

alter table student
add unique(sname)

alter table student
add constraint cheak_ssex check(ssex in('男','女'))

alter table student
with nocheck
add check (birthday between 1987-01-01 and getdate())

alter table student
add default '团员' for policy

create rule GKK_number
as @number in (1-8)
exec sp_bindrule GKK_number,'course.semester'
exec sp_unbindrule'course.semester'
drop rule GKK_number

create default MR_sex as '男'
exec sp_bindefault MR_sex,'student.ssex'
exec sp_unbindefault'student.ssex'
drop default MR_sex