★★★数据库练习★★★讲解.doc

1 创建数据库和表练习 创建数据库 create database test on primary (name = testdata, filename = D:\java\Database\DATA\testdata.mdf, size = 10, maxsize = 500, filegrowth = 5 ) log on (name = testLog, filename = D:\java\Database\DATA\testLog.ldf, size = 3, filegrowth = 10% ) --创建数据表 use class --选择数据库 go create 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 class go create 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 class go create 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 class go create 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(T