SQLServer创建数据表.doc

/*SQL Server中关键字、表名、列名都是不区分大小写。如果想要在脚本命令区分大小写可执行命令：alter database 数据库名collate Chinese_PRC_CI_AS*/-11311110805-软件技术班-骆巍create database teston primary(name = testdata,filename = D:javaDatabaseDATAtestdata.mdf,size = 10,maxsize = 500,filegrowth = 5)log on(name = testLog,filename = D:javaDatabaseDATAtestLog.ldf,size = 3,filegrowth = 10%)-创建数据表use class -选择数据库gocreate table Department -创建表( DepartmentID char(4) primary key, -primary key 是设置主键DepartmentName varchar(20) not null unique, -unique是设置唯一DepartmentHeader varchar(8) not null, -not null 是非空TeacherNum int)use classgocreate table Class(ClassID char(8) primary key,ClassName varchar(20) not null,Monitor char(8),StudentNum int check(StudentNum = 0), -check约束DepartmentID char(4) foreign key references Department(DepartmentID), -foreign key 定义外键references用于指定外键所引用的表及表的主键)use classgocreate table Student(StudentID char(12) primary key,StudentName char(8) not null,Sex char(2) not null check(Sex = 男 or Sex = 女),Birth date not null,HomeAddr varchar(80),EntranceTime date default getdate(), - getdate（）是得到系统时间的方法ClassID char(8) references Class(ClassID) -行级约束时可以省略foreign key)create table Course(CourseID char(8) primary key,CourseNmae varchar(60) not null,BookName varchar(80) not null,Credit int not null)create table Grade(CourseID char(8) references Course(CourseID),StudentID char(12) references Student(StudentID),Semester int not null,SchoolYear int,Grade numeric(5,1) check(Grade= 0),primary key(CourseID, StudentID) -在定义完CourseID和StudentID列后定义主键约束，这种约束为表级约束)use classgocreate table Teacher(TeacherID char(8) primary key,TeacherName char(8) not null,Sex char(2) not null check(Sex = 男 or Sex = 女),Birth date,Profession char(8) check(Profession in(教授, 副教授,讲师, 助教),-还可以这样写(Profession = 教授 or Profession = 副教授 or Profession = 讲师 or Profession = 助教)Telephone varchar(20),HomeAddr varchar(50),DepartmentID char(4) references Department(DepartmentID)create table Schedule(TeacherID char(8) references Teacher(TeacherID),CourseID char(8) references Course(CourseID),ClassID char(8) references Class(ClassID),Semester int not null,SchoolYear int not null,ScheduleIdent varchar(40) not null,Classroom varchar(20) not nullprimary key(