为了练习sql语句，需要先建表：

需要建立如下的表格：

 

 

 

 

tabstudent和tblteacher都是普通的表，但是talscore和tablcourse都要有主外键的关系。各自的建表语句如下：

tblteacher表：

CREATE TABLE `tblteacher` (  `TeaId` VARCHAR(3) NOT NULL,  `TeaName` VARCHAR(20) CHARACTER SET utf8 NOT NULL,  PRIMARY KEY  (`TeaId`)) ENGINE=INNODB DEFAULT CHARSET=latin1

tblstudent表：

CREATE TABLE `tblstudent` (  `StuId` VARCHAR(5) NOT NULL,  `StuName` VARCHAR(10) CHARACTER SET utf8 NOT NULL,  `StuAge` INT(11) DEFAULT NULL,  `StuSex` CHAR(1) CHARACTER SET utf8 NOT NULL,  PRIMARY KEY  (`StuId`)) ENGINE=INNODB DEFAULT CHARSET=latin1

 

tablcourse表：

CREATE TABLE `tablcourse` (  `CourseId` VARCHAR(4) NOT NULL,  `CourseName` VARCHAR(10) CHARACTER SET utf8 NOT NULL,  `TeaId` VARCHAR(3) NOT NULL,  PRIMARY KEY  (`CourseId`),  KEY `FK_tablcourse` (`TeaId`),  CONSTRAINT `FK_tablcourse` FOREIGN KEY (`TeaId`) REFERENCES `tblteacher` (`TeaId`)) ENGINE=INNODB DEFAULT CHARSET=latin1

 

talscore表：

CREATE TABLE `talscore` (  `StuId` VARCHAR(5) NOT NULL,  `CourseId` VARCHAR(4) NOT NULL,  `Score` FLOAT DEFAULT NULL,  KEY `FK_Courseid` (`CourseId`),  KEY `FK_Stuid` (`StuId`),  CONSTRAINT `FK_Courseid` FOREIGN KEY (`CourseId`) REFERENCES `tablcourse` (`CourseId`),  CONSTRAINT `FK_Stuid` FOREIGN KEY (`StuId`) REFERENCES `tblstudent` (`StuId`)) ENGINE=INNODB DEFAULT CHARSET=latin1

当然我们用得是视图化的方式建表（上面的建表语句都是视图化建表后自动生成的）：

 步骤：

单击Tables的右键，建立表格。

尤其要注意的是我们为了中文不乱码（打中文时会出现？这种情况）。在Collation的选择utf-8.

每张表格如下：

 

 

 

 

 

 

 

建立好表格之后，我们插入数据：

/*********************************  建库建表建约束，插入数据  ******************************************/

Use master

if db_id('MySchool') is not null

 Drop Database MySchool

Create Database MySchool

go

Use MySchool

go

create table tblStudent

(

 StuId varchar(5) primary key,

 StuName nvarchar(10) not null,

 StuAge int,

 StuSex nchar(1) not null

)

create table tblTeacher

(

 TeaId varchar(3) primary key, 

 TeaName varchar(10) not null

)

create table tblCourse

(

 CourseId varchar(3) primary key,

 CourseName nvarchar(20) not null, 

 TeaId varchar(3) not null foreign key references tblTeacher(teaId)

)

create table tblScore

(

 StuId varchar(5) not null foreign key references tblStudent(stuId),

 CourseId varchar(3) not null foreign key references tblCourse(CourseId),

 Score float

)

----------------------------------表结构----------------------------------------------------

--学生表tblStudent（编号StuId、姓名Stuname、年龄Stuage、性别Stusex）

--课程表tblCourse（课程编号CourseId、课程名称CourseName、教师编号TeaId）

--成绩表tblScore（学生编号StuId、课程编号CourseId、成绩Score）

--教师表tblTeacher（教师编号TeaId、姓名TeaName）

--------------------------------插入数据-------------------------------------------------

insert into tblStudent

select '1000','张无忌',18,'男' union

select '1001','周芷若',19,'女' union

select '1002','杨过',19,'男' union

select '1003','赵敏',18,'女' union

select '1004','小龙女',17,'女' union

select '1005','张三丰',18,'男' union

select '1006','令狐冲',19,'男' union

select '1007','任盈盈',20,'女' union

select '1008','岳灵珊',19,'女' union

select '1009','韦小宝',18,'男' union

select '1010','康敏',17,'女' union

select '1011','萧峰',19,'男' union

select '1012','黄蓉',18,'女' union

select '1013','郭靖',19,'男' union

select '1014','周伯通',19,'男' union

select '1015','瑛姑',20,'女' union

select '1016','李秋水',21,'女' union

select '1017','黄药师',18,'男' union

select '1018','李莫愁',18,'女' union

select '1019','冯默风',17,'男' union

select '1020','王重阳',17,'男' union

select '1021','郭襄',18,'女' 

go

insert  into tblTeacher

select '001','姚明' union

select '002','叶平' union

select '003','叶开' union

select '004','孟星魂' union

select '005','独孤求败' union

select '006','裘千仞' union

select '007','裘千尺' union

select '008','赵志敬' union

select '009','阿紫' union

select '010','郭芙蓉' union

select '011','佟湘玉' union

select '012','白展堂' union

select '013','吕轻侯' union

select '014','李大嘴' union

select '015','花无缺' union

select '016','金不换' union

select '017','乔丹'

go

insert into tblCourse

select '001','企业管理','002' union

select '002','马克思','008' union

select '003','UML','006' union

select '004','数据库','007' union

select '005','逻辑电路','006' union

select '006','英语','003' union

select '007','电子电路','005' union

select '008','思想概论','004' union

select '009','西方哲学史','012' union

select '010','线性代数','017' union

select '011','计算机基础','013' union

select '012','AUTO CAD制图','015' union

select '013','平面设计','011' union

select '014','Flash动漫','001' union

select '015','开发','009' union

select '016','C#基础','002' union

select '017','Oracl数据库原理','010'

go

insert into tblScore

select '1001','003',90 union

select '1001','002',87 union

select '1001','001',96 union

select '1001','010',85 union

select '1002','003',70 union

select '1002','002',87 union

select '1002','001',42 union

select '1002','010',65 union

select '1003','006',78 union

select '1003','003',70 union

select '1003','005',70 union

select '1003','001',32 union

select '1003','010',85 union

select '1003','011',21 union

select '1004','007',90 union

select '1004','002',87 union

select '1005','001',23 union

select '1006','015',85 union

select '1006','006',46 union

select '1006','003',59 union

select '1006','004',70 union

select '1006','001',99 union

select '1007','011',85 union

select '1007','006',84 union

select '1007','003',72 union

select '1007','002',87 union

select '1008','001',94 union

select '1008','012',85 union

select '1008','006',32 union

select '1009','003',90 union

select '1009','002',82 union

select '1009','001',96 union

select '1009','010',82 union

select '1009','008',92 union

select '1010','003',90 union

select '1010','002',87 union

select '1010','001',96 union

select '1011','009',24 union

select '1011','009',25 union

select '1012','003',30 union

select '1013','002',37 union

select '1013','001',16 union

select '1013','007',55 union

select '1013','006',42 union

select '1013','012',34 union

select '1000','004',16 union

select '1002','004',55 union

select '1004','004',42 union

select '1008','004',34 union

select '1013','016',86 union

select '1013','016',44 union

select '1000','014',75 union

select '1002','016',100 union

select '1004','001',83 union

select '1008','013',97

go

到这里我们的表格和插入数据已经完成了。