sqlserver创建表实例

创建表语句，用案例说话

创建这四个表，并插入信息
学生（学号，姓名，年龄，性别，所在系）
课程（课程号，课程名，先行课，学分）
选课（学号，课程号，成绩）
系部（系号，系名称，系主任）

create database Test;	//创建数据库Test
//建表的顺序不能该表Department->Cource->Student->SCource
create table Department(	//系部表
	DNo		int,
	DName		nchar(10),
	DHost		nchar(10),
	primary key(DNo),		//设置DNo为主键
);
create table Cource(	//课程表
	CNo		int	,
	CName		nchar(10),
	PreCource		nchar(10),
	TGrade		int,
	primary key(Cno),
);
create table Student(	//学生表，由于学生表需要对系部表关联，所以先创建系部表
	SNo		int	,
	SName		nchar(10),
	SAge		int,
	SSex		nchar(2),
	SDept		int,
	primary key(SNo),
	foreign key(SDept) references Department(DNo),	//设置SDept为DNo的外键
);
create table SCource(	//选课表,同理要建在学生表与课程表之下
	SNo		int,
	CNo		int	,
	Grade		int,
	primary key(SNo,Cno),
	foreign key(SNo) references Student(SNo),
	foreign key(CNo) references Cource(CNo),
);
//插入相关数据
insert into Department values(1,'计算机工程系','宋江');
insert into Department values(2,'化学工程系','卢俊义');
insert into Department values(3,'电子工程系','吴用');
insert into Department values(4,'法学系','公孙胜');
insert into Department values(5,'数学系','关胜');
insert into Department values(6,'应用化学系','林冲');
insert into Department values(7,'无机化学','秦明');
insert into Department values(8,'数学类','呼延灼');
insert into Department values(9,'媒体创意','花荣');
insert into Department values(10,'物理系','柴进');
insert into Department values(11,'广告学','李应');

insert into Cource  values(1,'计算机导论',NULL,100);
insert into Cource  values(2,'c语言基础',NULL,100);
insert into Cource  values(3,'java基础','c语言基础',100);
insert into Cource  values(4,'web前端应用','c语言基础',100);
insert into Cource  values(5,'面向对象程序设计','java基础',100);
insert into Cource  values(6,'javaweb程序设计','java基础',100);
insert into Cource  values(7,'计算机组成原理','计算机导论',100);
insert into Cource  values(8,'数据库原理',NULL,100);
insert into Cource  values(9,'大学英语',NULL,100);
insert into Cource  values(10,'大学物理',NULL,100);
insert into Cource  values(11,'大学体育',NULL,100);

insert into Student values(1,'刘一',18,'女',1);
insert into Student values(2,'陈二',18,'男',3);
insert into Student values(3,'张三',18,'男',2);
insert into Student values(4,'李四',18,'男',5);
insert into Student values(5,'王五',18,'女',4);
insert into Student values(6,'赵六',18,'男',5);
insert into Student values(7,'孙七',18,'女',6);
insert into Student values(8,'周八',18,'男',9);
insert into Student values(9,'吴九',18,'女',10);
insert into Student values(10,'郑十',18,'女',6);
insert into Student values(11,'覃十一',18,'女',7);

insert into SCource values(1,1,89);
insert into SCource values(1,2,76);
insert into SCource values(2,3,87);
insert into SCource values(3,4,64);
insert into SCource values(4,4,97);
insert into SCource values(5,5,75);
insert into SCource values(6,5,63);
insert into SCource values(7,6,87);
insert into SCource values(7,7,45);
insert into SCource values(8,7,86);
insert into SCource values(9,8,87);
insert into SCource values(10,1,86);
insert into SCource values(10,2,54);
insert into SCource values(10,3,76);
insert into SCource values(10,4,87);
//查询所有表，查看是否插入成功
select * from Student;
select * from Cource;
select * from SCource;
select * from Department;