1、CREATE DATABASE SchoolInfo -创建数据库-创建Department表Create table Department(DepID int primary key,DepName varchar(20) NOT NULL, Total int)-创建Course表Create table Course(CourseID int primary key,CourseName varchar(20) not null, Credit int)-创建学生表Create table Student(StuID char(10) constraint pkStuID primary
2、 key constraint chkStuID check(StuID like A,B,Z0-90-90-90-90-9) ,StuName varchar(20) not null,StuAge int constraint chkStuAge check(StuAge between 15 and 100) ,StuSex char(2) constraint chkStuSex check(StuSex in(男,女) constraint defStuSex default 男,StuCity varchar(20) ,StuScore float constraint chkSt
3、uScore check(StuScore between 0 and 100) ,DepID int constraint fkDepID foreign key references Department(DepID)-创建SC表Create table SC(StuID char(10) constraint fkStuID foreign key references Student(StuID) ,CourseID int constraint fkCourseID foreign key references Course(CourseID) Constraint pkStuCou
4、rse primary key(StuID,CourseID) , Score float constraint chkScore check(Score between 0 and 100)create table Teacher(TeaID char(10) primary key,TeaName varchar(20),TeaTitle varchar(20),CourseNum int )create table Salary(TeaID char(10) constraint fkTeaID foreign key references Teacher(TeaID) constrai
5、nt pkTeaID primary key,Wage money)CREATE TABLE Employee(EmpID char(10) primary key,EmpName varchar(20) NOT NULL,EmpBirthDate datetime)-插入记录insert into department values(1,computer,95)insert into department values(2,math,100)insert into student values(A00001,Tom,19,男,南京市,95,1)insert into student valu
6、es(A00002,Jack,21,男,上海市,88,2)insert into student values(A00003,Nancy,23,女,北京市,70,1)insert into student values(A00004,Droa,21,女,南京市,92,2)insert into student values(A00005,Jerome,24,男,上海市,77,1)insert into student values(A00006,Andy,18,女,北京市,80,2)insert into course values(1,database,3)insert into cours
7、e values(2,math,4)insert into course values(3,english,4)insert into SC values(A00001,1,90)insert into SC values(A00001,2,85)insert into SC values(A00001,3,90)insert into SC values(A00002,1,90)insert into SC values(A00003,2,80)insert into SC values(A00004,3,85)insert into SC values(A00005,1,90)insert into Teacher values(T0001,Mary,讲师,50)insert into Teacher values(T0002,Tom,教授,40)insert into Teacher values(T0003,Mike,副教授,40)insert into Employee values(E001,Mary,1996-01-01)insert into Employee values(E002,Tom,1994-03-01)insert into Employee values(E003,Jack,1995-01-01)
