-- ----------------------------------表结构--------------------------------------
-- 学生表tblStudent(编号StuId、姓名StuName、年龄StuAge、性别StuSex)
-- 课程表tblCourse(课程编号CourseId、课程名称CourseName、教师编号TeaId)
-- 成绩表tblScore(学生编号StuId、课程编号CourseId、成绩Score)
-- 教师表tblTeacher(教师编号TeaId、姓名TeaName)
-- -------------------------------------------------------------------------------
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
select a.StuId
from tblScore a ,tblScore b
where a.StuId = b.StuId
and a.CourseId='001'
and b.CourseId='002'
and a.Score>b.Score;
2、查询平均成绩大于60分的同学的学号和平均成绩;
Select StuId,Avg(Score) as AvgScore From tblScore
Group By StuId
Having Avg(Score)>60;
3、查询所有同学的学号、姓名、选课数、总成绩;
select tblstudent.StuId,tblstudent.StuName,COUNT(tblscore.CourseId),sum(tblscore.Score)
FROM tblstudent,tblscore
WHERE tblstudent.StuId = tblscore.StuId
GROUP BY tblstudent.StuId;
4、查询姓“李”的老师的个数;
SELECT COUNT(1),tblteacher.TeaName
FROM tblteacher
WHERE tblteacher.TeaName LIKE '李%';
5、查询没学过“叶平”老师课的同学的学号、姓名;
SELECT * FROM tblstudent WHERE StuId not in(
SELECT DISTINCT StuId FROM tblscore WHERE CourseId IN(
SELECT tblcourse.CourseId FROM tblcourse WHERE TeaId IN
(SELECT tblteacher.TeaId from tblteacher WHERE tblteacher.TeaName = '叶平')
)
);
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
SELECT DISTINCT s.StuId,s.StuName
FROM tblstudent s
WHERE s.StuId IN
(SELECT DISTINCT sc.StuId
FROM tblscore sc
WHERE sc.CourseId='001' AND sc.StuId IN
(SELECT DISTINCT sc.StuId
FROM tblscore sc
WHERE sc.CourseId='002')
);
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
SELECT * FROM tblstudent WHERE StuId in(
SELECT DISTINCT StuId FROM tblscore WHERE CourseId IN(
SELECT tblcourse.CourseId FROM tblcourse WHERE TeaId IN
(SELECT tblteacher.TeaId from tblteacher WHERE tblteacher.TeaName = '叶平')
)
);
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
Select StuId,StuName From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId='001')>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId='002')
9、查询所有课程成绩小于60分的同学的学号、姓名;
SELECT s.StuId,s.StuName
FROM tblstudent s
WHERE s.StuId IN
(SELECT DISTINCT sc.StuId
FROM tblscore sc
WHERE sc.Score<60);
10、查询没有学全所有课的同学的学号、姓名;
Select StuId,StuName From tblStudent st
Where (Select Count(1) From tblScore sc Where st.StuId=sc.StuId)<
(Select Count(1) From tblCourse);
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
SELECT DISTINCT c.StuId,s.StuName
FROM tblscore c,tblstudent s
WHERE c.CourseId IN(SELECT sc.CourseId
FROM tblscore sc
WHERE sc.StuId = '1001')
AND c.StuId = s.StuId;
12、查询至少学过学号为“1001”同学所有课程的其他同学学号和姓名; --------(这道题目我做不出来,我的理解是选取学生表的学生姓名和学号,学生表的哪些?那些学过某一个同学学过的课程的那一些)
-----------------------------------------------------------------------答案我不能理解 这个似乎有歧义!!!
Select StuId,StuName From tblStudent
Where StuId In
(
Select Distinct StuId From tblScore Where CourseId Not In
(Select CourseId From tblScore Where StuId='1001'));
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;--------(直说 我思考过 没有做出来)
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
15、删除学习“叶平”老师课的SC表记录;
16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、'002'号课的平均成绩;
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT c.CourseName AS 课程ID,MAX(sc.Score) 最高分,MIN(sc.Score) AS 最低分
FROM tblscore sc,tblcourse c
WHERE c.CourseId = sc.CourseId
GROUP BY sc.CourseId;
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 (百分数后如何格式化为两位小数??)
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004)
21、查询不同老师所教不同课程平均分从高到低显示
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(001),马克思(002),UML (003),数据库(004) 格式:[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
24、查询学生平均成绩及其名次
SELECT sc.StuId,s.StuName,avg(sc.Score) AS 平均成绩
FROM tblstudent s,tblscore sc
WHERE s.StuId = sc.StuId
GROUP BY sc.StuId
ORDER BY 平均成绩 DESC;
25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
26、查询每门课程被选修的学生数
27、查询出只选修了一门课程的全部学生的学号和姓名
28、查询男生、女生人数
SELECT s.StuSex,COUNT(1) as 人数
FROM tblstudent s
GROUP BY s.StuSex;
29、查询姓“张”的学生名单
SELECT s.StuId,s.StuName
FROM tblstudent s
WHERE s.StuName LIKE '张%';
30、查询同名同性学生名单,并统计同名人数
SELECT s.StuName,s.StuSex,COUNT(1) AS 人数
FROM tblstudent s
GROUP BY s.StuName,s.StuSex;
31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime)
32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数
35、查询所有学生的选课情况;
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
37、查询不及格的课程,并按课程号从大到小排列
38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
39、求选了课程的学生人数
40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩
41、查询各个课程及相应的选修人数
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
43、查询每门功成绩最好的前两名
44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
45、检索至少选修两门课程的学生学号
46、查询全部学生都选修的课程的课程号和课程名
47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
48、查询两门以上不及格课程的同学的学号及其平均成绩
49、检索“004”课程分数小于60,按分数降序排列的同学学号 (ok)
-- 学生表tblStudent(编号StuId、姓名StuName、年龄StuAge、性别StuSex)
-- 课程表tblCourse(课程编号CourseId、课程名称CourseName、教师编号TeaId)
-- 成绩表tblScore(学生编号StuId、课程编号CourseId、成绩Score)
-- 教师表tblTeacher(教师编号TeaId、姓名TeaName)
-- -------------------------------------------------------------------------------
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
select a.StuId
from tblScore a ,tblScore b
where a.StuId = b.StuId
and a.CourseId='001'
and b.CourseId='002'
and a.Score>b.Score;
2、查询平均成绩大于60分的同学的学号和平均成绩;
Select StuId,Avg(Score) as AvgScore From tblScore
Group By StuId
Having Avg(Score)>60;
3、查询所有同学的学号、姓名、选课数、总成绩;
select tblstudent.StuId,tblstudent.StuName,COUNT(tblscore.CourseId),sum(tblscore.Score)
FROM tblstudent,tblscore
WHERE tblstudent.StuId = tblscore.StuId
GROUP BY tblstudent.StuId;
4、查询姓“李”的老师的个数;
SELECT COUNT(1),tblteacher.TeaName
FROM tblteacher
WHERE tblteacher.TeaName LIKE '李%';
5、查询没学过“叶平”老师课的同学的学号、姓名;
SELECT * FROM tblstudent WHERE StuId not in(
SELECT DISTINCT StuId FROM tblscore WHERE CourseId IN(
SELECT tblcourse.CourseId FROM tblcourse WHERE TeaId IN
(SELECT tblteacher.TeaId from tblteacher WHERE tblteacher.TeaName = '叶平')
)
);
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
SELECT DISTINCT s.StuId,s.StuName
FROM tblstudent s
WHERE s.StuId IN
(SELECT DISTINCT sc.StuId
FROM tblscore sc
WHERE sc.CourseId='001' AND sc.StuId IN
(SELECT DISTINCT sc.StuId
FROM tblscore sc
WHERE sc.CourseId='002')
);
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
SELECT * FROM tblstudent WHERE StuId in(
SELECT DISTINCT StuId FROM tblscore WHERE CourseId IN(
SELECT tblcourse.CourseId FROM tblcourse WHERE TeaId IN
(SELECT tblteacher.TeaId from tblteacher WHERE tblteacher.TeaName = '叶平')
)
);
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
Select StuId,StuName From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId='001')>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId='002')
9、查询所有课程成绩小于60分的同学的学号、姓名;
SELECT s.StuId,s.StuName
FROM tblstudent s
WHERE s.StuId IN
(SELECT DISTINCT sc.StuId
FROM tblscore sc
WHERE sc.Score<60);
10、查询没有学全所有课的同学的学号、姓名;
Select StuId,StuName From tblStudent st
Where (Select Count(1) From tblScore sc Where st.StuId=sc.StuId)<
(Select Count(1) From tblCourse);
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
SELECT DISTINCT c.StuId,s.StuName
FROM tblscore c,tblstudent s
WHERE c.CourseId IN(SELECT sc.CourseId
FROM tblscore sc
WHERE sc.StuId = '1001')
AND c.StuId = s.StuId;
12、查询至少学过学号为“1001”同学所有课程的其他同学学号和姓名; --------(这道题目我做不出来,我的理解是选取学生表的学生姓名和学号,学生表的哪些?那些学过某一个同学学过的课程的那一些)
-----------------------------------------------------------------------答案我不能理解 这个似乎有歧义!!!
Select StuId,StuName From tblStudent
Where StuId In
(
Select Distinct StuId From tblScore Where CourseId Not In
(Select CourseId From tblScore Where StuId='1001'));
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;--------(直说 我思考过 没有做出来)
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
15、删除学习“叶平”老师课的SC表记录;
16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、'002'号课的平均成绩;
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT c.CourseName AS 课程ID,MAX(sc.Score) 最高分,MIN(sc.Score) AS 最低分
FROM tblscore sc,tblcourse c
WHERE c.CourseId = sc.CourseId
GROUP BY sc.CourseId;
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 (百分数后如何格式化为两位小数??)
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004)
21、查询不同老师所教不同课程平均分从高到低显示
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(001),马克思(002),UML (003),数据库(004) 格式:[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
24、查询学生平均成绩及其名次
SELECT sc.StuId,s.StuName,avg(sc.Score) AS 平均成绩
FROM tblstudent s,tblscore sc
WHERE s.StuId = sc.StuId
GROUP BY sc.StuId
ORDER BY 平均成绩 DESC;
25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
26、查询每门课程被选修的学生数
27、查询出只选修了一门课程的全部学生的学号和姓名
28、查询男生、女生人数
SELECT s.StuSex,COUNT(1) as 人数
FROM tblstudent s
GROUP BY s.StuSex;
29、查询姓“张”的学生名单
SELECT s.StuId,s.StuName
FROM tblstudent s
WHERE s.StuName LIKE '张%';
30、查询同名同性学生名单,并统计同名人数
SELECT s.StuName,s.StuSex,COUNT(1) AS 人数
FROM tblstudent s
GROUP BY s.StuName,s.StuSex;
31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime)
32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数
35、查询所有学生的选课情况;
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
37、查询不及格的课程,并按课程号从大到小排列
38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
39、求选了课程的学生人数
40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩
41、查询各个课程及相应的选修人数
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
43、查询每门功成绩最好的前两名
44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
45、检索至少选修两门课程的学生学号
46、查询全部学生都选修的课程的课程号和课程名
47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
48、查询两门以上不及格课程的同学的学号及其平均成绩
49、检索“004”课程分数小于60,按分数降序排列的同学学号 (ok)
50、删除“002”同学的“001”课程的成绩
/*
Navicat MySQL Data Transfer
Source Server : huangjin
Source Server Version : 50168
Source Host : localhost:3306
Source Database : myschool
Target Server Type : MYSQL
Target Server Version : 50168
File Encoding : 65001
Date: 2015-10-21 17:03:08
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tblcourse`
-- ----------------------------
DROP TABLE IF EXISTS `tblcourse`;
CREATE TABLE `tblcourse` (
`CourseId` varchar(3) NOT NULL,
`CourseName` varchar(20) NOT NULL,
`TeaId` varchar(3) NOT NULL,
PRIMARY KEY (`CourseId`),
CONSTRAINT `tblcourse_ibfk_1` FOREIGN KEY (`CourseId`) REFERENCES `tblteacher` (`TeaId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tblcourse
-- ----------------------------
INSERT INTO `tblcourse` VALUES ('001', '企业管理', '002');
INSERT INTO `tblcourse` VALUES ('002', '马克思', '008');
INSERT INTO `tblcourse` VALUES ('003', 'UML', '006');
INSERT INTO `tblcourse` VALUES ('004', '数据库', '007');
INSERT INTO `tblcourse` VALUES ('005', '逻辑电路', '006');
INSERT INTO `tblcourse` VALUES ('006', '英语', '003');
INSERT INTO `tblcourse` VALUES ('007', '电子电路', '005');
INSERT INTO `tblcourse` VALUES ('008', '毛泽东思想概论', '004');
INSERT INTO `tblcourse` VALUES ('009', '西方哲学史', '012');
INSERT INTO `tblcourse` VALUES ('010', '线性代数', '017');
INSERT INTO `tblcourse` VALUES ('011', '计算机基础', '013');
INSERT INTO `tblcourse` VALUES ('012', 'AUTO CAD制图', '015');
INSERT INTO `tblcourse` VALUES ('013', '平面设计', '011');
INSERT INTO `tblcourse` VALUES ('014', 'Flash动漫', '001');
INSERT INTO `tblcourse` VALUES ('015', 'Java开发', '009');
INSERT INTO `tblcourse` VALUES ('016', 'C#基础', '002');
INSERT INTO `tblcourse` VALUES ('017', 'Oracl数据库原理', '010');
-- ----------------------------
-- Table structure for `tblscore`
-- ----------------------------
DROP TABLE IF EXISTS `tblscore`;
CREATE TABLE `tblscore` (
`StuId` varchar(10) NOT NULL,
`CourseId` varchar(3) NOT NULL,
`Score` float DEFAULT NULL,
PRIMARY KEY (`StuId`,`CourseId`),
KEY `CourseId` (`CourseId`),
CONSTRAINT `tblscore_ibfk_1` FOREIGN KEY (`StuId`) REFERENCES `tblstudent` (`StuId`),
CONSTRAINT `tblscore_ibfk_2` FOREIGN KEY (`CourseId`) REFERENCES `tblcourse` (`CourseId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tblscore
-- ----------------------------
INSERT INTO `tblscore` VALUES ('1000', '004', '16');
INSERT INTO `tblscore` VALUES ('1000', '014', '75');
INSERT INTO `tblscore` VALUES ('1001', '001', '96');
INSERT INTO `tblscore` VALUES ('1001', '002', '87');
INSERT INTO `tblscore` VALUES ('1001', '003', '90');
INSERT INTO `tblscore` VALUES ('1001', '010', '85');
INSERT INTO `tblscore` VALUES ('1002', '001', '42');
INSERT INTO `tblscore` VALUES ('1002', '002', '87');
INSERT INTO `tblscore` VALUES ('1002', '003', '70');
INSERT INTO `tblscore` VALUES ('1002', '004', '55');
INSERT INTO `tblscore` VALUES ('1002', '010', '65');
INSERT INTO `tblscore` VALUES ('1002', '016', '100');
INSERT INTO `tblscore` VALUES ('1003', '001', '32');
INSERT INTO `tblscore` VALUES ('1003', '003', '70');
INSERT INTO `tblscore` VALUES ('1003', '005', '70');
INSERT INTO `tblscore` VALUES ('1003', '006', '78');
INSERT INTO `tblscore` VALUES ('1003', '010', '85');
INSERT INTO `tblscore` VALUES ('1003', '011', '21');
INSERT INTO `tblscore` VALUES ('1004', '001', '83');
INSERT INTO `tblscore` VALUES ('1004', '002', '87');
INSERT INTO `tblscore` VALUES ('1004', '004', '42');
INSERT INTO `tblscore` VALUES ('1004', '007', '90');
INSERT INTO `tblscore` VALUES ('1005', '001', '23');
INSERT INTO `tblscore` VALUES ('1006', '001', '99');
INSERT INTO `tblscore` VALUES ('1006', '003', '59');
INSERT INTO `tblscore` VALUES ('1006', '004', '70');
INSERT INTO `tblscore` VALUES ('1006', '006', '46');
INSERT INTO `tblscore` VALUES ('1006', '015', '85');
INSERT INTO `tblscore` VALUES ('1007', '002', '87');
INSERT INTO `tblscore` VALUES ('1007', '003', '72');
INSERT INTO `tblscore` VALUES ('1007', '006', '84');
INSERT INTO `tblscore` VALUES ('1007', '011', '85');
INSERT INTO `tblscore` VALUES ('1008', '001', '94');
INSERT INTO `tblscore` VALUES ('1008', '004', '34');
INSERT INTO `tblscore` VALUES ('1008', '006', '32');
INSERT INTO `tblscore` VALUES ('1008', '012', '85');
INSERT INTO `tblscore` VALUES ('1008', '013', '97');
INSERT INTO `tblscore` VALUES ('1009', '001', '96');
INSERT INTO `tblscore` VALUES ('1009', '002', '82');
INSERT INTO `tblscore` VALUES ('1009', '003', '90');
INSERT INTO `tblscore` VALUES ('1009', '008', '92');
INSERT INTO `tblscore` VALUES ('1009', '010', '82');
INSERT INTO `tblscore` VALUES ('1010', '001', '96');
INSERT INTO `tblscore` VALUES ('1010', '002', '87');
INSERT INTO `tblscore` VALUES ('1010', '003', '90');
INSERT INTO `tblscore` VALUES ('1011', '009', '24');
INSERT INTO `tblscore` VALUES ('1012', '003', '30');
INSERT INTO `tblscore` VALUES ('1012', '009', '25');
INSERT INTO `tblscore` VALUES ('1013', '001', '16');
INSERT INTO `tblscore` VALUES ('1013', '002', '37');
INSERT INTO `tblscore` VALUES ('1013', '006', '42');
INSERT INTO `tblscore` VALUES ('1013', '007', '55');
INSERT INTO `tblscore` VALUES ('1013', '012', '34');
INSERT INTO `tblscore` VALUES ('1013', '014', '86');
INSERT INTO `tblscore` VALUES ('1013', '016', '44');
-- ----------------------------
-- Table structure for `tblstudent`
-- ----------------------------
DROP TABLE IF EXISTS `tblstudent`;
CREATE TABLE `tblstudent` (
`StuId` varchar(10) NOT NULL,
`StuName` varchar(10) NOT NULL,
`StuAge` int(10) DEFAULT NULL,
`StuSex` char(1) NOT NULL,
PRIMARY KEY (`StuId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tblstudent
-- ----------------------------
INSERT INTO `tblstudent` VALUES ('1000', '张无忌', '18', '男');
INSERT INTO `tblstudent` VALUES ('1001', '周芷若', '19', '女');
INSERT INTO `tblstudent` VALUES ('1002', '杨过', '19', '男');
INSERT INTO `tblstudent` VALUES ('1003', '赵敏', '18', '女');
INSERT INTO `tblstudent` VALUES ('1004', '小龙女', '17', '女');
INSERT INTO `tblstudent` VALUES ('1005', '张三丰', '18', '男');
INSERT INTO `tblstudent` VALUES ('1006', '令狐冲', '19', '男');
INSERT INTO `tblstudent` VALUES ('1007', '任盈盈', '20', '女');
INSERT INTO `tblstudent` VALUES ('1008', '岳灵珊', '19', '女');
INSERT INTO `tblstudent` VALUES ('1009', '韦小宝', '18', '男');
INSERT INTO `tblstudent` VALUES ('1010', '康敏', '17', '女');
INSERT INTO `tblstudent` VALUES ('1011', '萧峰', '19', '男');
INSERT INTO `tblstudent` VALUES ('1012', '黄蓉', '18', '女');
INSERT INTO `tblstudent` VALUES ('1013', '郭靖', '19', '男');
INSERT INTO `tblstudent` VALUES ('1014', '周伯通', '19', '男');
INSERT INTO `tblstudent` VALUES ('1015', '瑛姑', '20', '女');
INSERT INTO `tblstudent` VALUES ('1016', '李秋水', '21', '女');
INSERT INTO `tblstudent` VALUES ('1017', '黄药师', '18', '男');
INSERT INTO `tblstudent` VALUES ('1018', '李莫愁', '18', '女');
INSERT INTO `tblstudent` VALUES ('1019', '冯默风', '17', '男');
INSERT INTO `tblstudent` VALUES ('1020', '王重阳', '17', '男');
INSERT INTO `tblstudent` VALUES ('1021', '郭襄', '18', '女');
-- ----------------------------
-- Table structure for `tblteacher`
-- ----------------------------
DROP TABLE IF EXISTS `tblteacher`;
CREATE TABLE `tblteacher` (
`TeaId` varchar(3) NOT NULL,
`TeaName` varchar(10) NOT NULL,
PRIMARY KEY (`TeaId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tblteacher
-- ----------------------------
INSERT INTO `tblteacher` VALUES ('001', '姚明');
INSERT INTO `tblteacher` VALUES ('002', '叶平');
INSERT INTO `tblteacher` VALUES ('003', '叶开');
INSERT INTO `tblteacher` VALUES ('004', '孟星魂');
INSERT INTO `tblteacher` VALUES ('005', '独孤求败');
INSERT INTO `tblteacher` VALUES ('006', '裘千仞');
INSERT INTO `tblteacher` VALUES ('007', '裘千尺');
INSERT INTO `tblteacher` VALUES ('008', '赵志敬');
INSERT INTO `tblteacher` VALUES ('009', '阿紫');
INSERT INTO `tblteacher` VALUES ('010', '郭芙蓉');
INSERT INTO `tblteacher` VALUES ('011', '佟湘玉');
INSERT INTO `tblteacher` VALUES ('012', '白展堂');
INSERT INTO `tblteacher` VALUES ('013', '吕轻侯');
INSERT INTO `tblteacher` VALUES ('014', '李大嘴');
INSERT INTO `tblteacher` VALUES ('015', '花无缺');
INSERT INTO `tblteacher` VALUES ('016', '金不换');
INSERT INTO `tblteacher` VALUES ('017', '乔丹');