第二章：数据库的实现

1. use master  go  exec sp_configure 'show advanced options',1  go  reconfigure  go  exec sp_configure 'xp_cmdshell',1  go  reconfigure  go  exec xp_cmdshell 'mkdir d:\project'  go  if exists(select * from sysdatabases where name='myschool')  drop database myschool  create database myschool  on primary(name='myschool_data',filename='d:\project\myschool.mdf',size=10,filegrowth=20%)  log on(name='myschool_log',filename='d:\project\myschool.ldf',size=3,maxsize=20,filegrowth=1)  use myschool  go  if exists(select * from sysobjects where name='subject')  drop table subject  create table subject(  subjectno int identity(1,1) not null,  subjectname nvarchar(50),  classhour int,  gradeid int)  if exists(select * from sysobjects where name='result')  drop table result  create table result(  studentno int not null,  subjectno int not null,  examdate datetime not null,  studentresult int not null)  if exists(select * from sysobjects where name='student')  drop table student  create table student(  studentno int not null,  loginpwd nvarchar(50) not null,  studentname nvarchar(50) not null,  sex bit not null,  gradeid int not null,  phone varchar(50),  address nvarchar(255),  borndate datetime not null,  email varchar(50),  id varchar(18) not null)  if exists(select * from sysobjects where name='grade')  drop table grade  create table grade(  gradeid int not null,  gradename nvarchar(50) not null)  alter table grade  add constraint pk_gradeid primary key(gradeid)  alter table student  add constraint pk_studentno1 primary key(studentno)  alter table student  add constraint uq_id unique(id)  alter table student  add constraint ck_borndate check(borndate>='1980-01-01')  alter table student  add constraint df_address default('地址不详') for address  alter table student  add constraint fk_studentno1 foreign key(studentno) references student(studentno)  alter table subject  add constraint pk_subjectno primary key(subjectno)  alter table subject  add constraint ck_subjectname check(subjectname!=null)  alter table subject  add constraint ck_classhour check(classhour>=0)  alter table subject  add constraint fk_gradeid foreign key(gradeid) references grade(gradeid)  alter table result  add constraint pk_studentno2 primary key(studentno,subjectno,examdate)  alter table result  add constraint df_examdate default(getdate()) for examdate  alter table result  add constraint ck_studentresult check(studentresult<=100 or studentresult>=0)  alter table result  add constraint fk_studentno2 foreign key(studentno) references student(studentno)  alter table result  add constraint fk_studentno3 foreign key(subjectno) references subject(subjectno)