50個查詢系列-建表和插入資料

weixin_33894992發表於2016-11-29

為了練習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
Go
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','Java開發','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

到這裡我們的表格和插入資料已經完成了。

 

相關文章