MySql查询练习

最新推荐文章于 2024-07-09 19:54:41 发布

ClareTung

最新推荐文章于 2024-07-09 19:54:41 发布

阅读量8.9k

点赞数 5

分类专栏： mysql 文章标签： mysql 查询语句 heaving orderBy 多表查询

本文链接：https://blog.csdn.net/qq_36135928/article/details/74993035

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

--查询全部S1的学生信息。
SELECT * FROM student WHERE GradeId in (select GradeId from grade where GradeName='S1' );

--查询全部S2的学生的姓名和电话。
SELECT StudentName,Phone FROM student WHERE GradeId=2;

--查询全部S1女同学的信息。
SELECT * FROM student WHERE GradeId=2 AND Sex='女';

--查询课时超过60的科目信息。
SELECT * FROM `subject` WHERE ClassHour>60;

--S2的科目名称。
SELECT SubjectName,GradeId FROM `subject` WHERE GradeId=2;

--S2男同学的姓名和住址。
SELECT StudentName,Address FROM `student` WHERE GradeId=2 AND sex='男';

--无电子邮件的学生姓名和年级信息。
SELECT StudentName,GradeName FROM student,grade WHERE student.GradeId=grade.GradeId AND Email='';

--出生日期在1993年之后的S2的学生姓名和年级信息。
SELECT StudentName,GradeName FROM student,grade WHERE student.GradeId=grade.GradeId AND student.GradeId=2 AND SUBSTRING(BornDate FROM 1 FOR 4)>'1993';

--参加了日期为2013年11月15日的“HTML和CSS网页技术”科目考试的成绩信息。
SELECT * FROM `result` WHERE SUBSTRING(ExamDate FROM 1 FOR 10)='2013/11/15' AND SubjectId in(SELECT SubjectId FROM
`subject` WHERE SubjectName LIKE 'HTML和CSS网页技术');

--按照出生日期查询S1的学生信息。
SELECT * FROM student WHERE GradeId in(SELECT GradeId FROM grade WHERE GradeName='S1') ORDER BY BornDate;

--按日期由前到后、成绩由高到低的次序查询参加编号为1的科目考试信息。
SELECT * FROM result WHERE SubjectId=1 ORDER BY StudentResult desc;

--查询2013年3月22日参加“面向对象程序设计”考试的前5名学员成绩信息。
SELECT * FROM result WHERE SubjectId in(SELECT SubjectId FROM `subject` WHERE SubjectName='面向对象程序设计') AND SUBSTRING(ExamDate FROM 1 FOR 10)='2013/3/22' order by StudentResult DESC limit 5;

--查询s2的课时最多的科目名称及课时。
SELECT SubjectName,MAX(ClassHour) FROM `subject` where GradeId=(SELECT GradeId FROM grade WHERE gradename='S2');

--查询年龄最小的学生所在的年级及姓名。
SELECT GradeName,StudentName FROM grade,student order by BornDate desc LIMIT 1;

--查询2013年3月22日参加考试的最低分出现在哪个科目。
SELECT subjectname FROM `subject` WHERE subjectid=(select subjectid from result ORDER BY studentresult limit 1);

--查询学号为“S1101003”的学生参加过的所有考试信息，并按照时间前后次序显示。
SELECT * FROM result where StudentNo='S1101003' ORDER BY ExamDate DESC;

--查询学号为“S1101003”的学生参加过的所有考试中最高分及时间、科目
SELECT MAX(StudentResult),ExamDate,SubjectName FROM result,`subject` where result.SubjectId=`subject`.SubjectId;

--查询年龄超过20周岁的s3的学生信息。
SELECT * FROM student WHERE (YEAR(CURDATE())-SUBSTRING(BornDate FROM 1 FOR 4))>20 AND gradeid in(SELECT gradeid FROM grade WHERE gradename='S3');

--查询1月份过生日的学生信息
SELECT * FROM student WHERE (SUBSTRING(BornDate FROM 6 FOR 6))=1;

--查询今天过生日的学生姓名及所在年级。
SELECT StudentName,GradeName FROM student,grade WHERE student.GradeId=grade.GradeId AND (SUBSTRING(BornDate FROM 6 FOR 7))=MONTH(CURTIME())
AND (SUBSTRING(BornDate FROM 9 FOR 10))=DAY(CURTIME());

--查询学号为“s31003007”的学生Email的域名。
SELECT SUBSTRING_INDEX(Email,'@', -1) AS domain FROM student WHERE StudentNo='s31003007';

--查询住址为“山东”的学生姓名、电话、住址。
select studentname,phone,address FROM student where Address like '%山东%';

--查询名称中含有“数据库”字样科目名称、学时及所属年级，并按年级由低到高显示。
select SubjectName classhour,gradeid from subject where subjectname like'%数据库%' order by gradeid

--查询电话中含有以“1387”开头的学生姓名，住址和电话。
select studentname,address,phone from student where phone like'1387%';

--查询姓名为“姜*”的学号、姓名和住址，其中*代表一个字。
select studentno,studentname,address from student where studentname like '姜_';

--查询学号为S1101004的学员的科目编号为1、2、3的考试成绩信息。
select studentresult from result where studentno='s1101004'and subjectid =1 or subjectid =2 or subjectid =3;

--查询出生日期中1989/1/1到1995/12/31之间的学生信息。
select * from student where borndate between '1989/1/1' and '1995/12/31';

--统计数据库中学生总人数。
select count(*) as 总人数 from student;

--查询s1年级的总学时。
select sum(ClassHour) as S1年级的总学时 from subject WHERE GradeId =(SELECT GradeId FROM grade WHERE GradeName='S1');

--学号为S1101004的学生第一学期内考试总成绩。
SELECT SUM(StudentResult) AS 第一学期总成绩 FROM result WHERE StudentNo='S1101004' AND SubjectId in(SELECT SubjectId FROM `subject` WHERE GradeId=(SELECT GradeId FROM grade WHERE GradeName='S1'));

--学号为S1101004的学生第一学期所有考试的平均分。
SELECT AVG(StudentResult) AS 第一学期平均分 FROM result WHERE StudentNo='S1101004' AND SubjectId in(SELECT SubjectId FROM `subject` WHERE GradeId=(SELECT GradeId FROM grade WHERE GradeName='S1'));

--查询2013年3月22日的科目“C#语言开发基础”的最高分、最低分、平均分。
select max(studentresult) as 最高分,min(studentresult) as 最低分,avg(studentresult) as 平均分 from result
where subjectid=(SELECT SubjectId FROM `subject` where SubjectName='C#语言开发基础') and SUBSTRING(ExamDate FROM 1 FOR 10)='2013/3/22';

--查询2013年3月22日科目“C#语言开发基础”的及格学生的平均分。
select avg(studentresult) as 平均分 from result where subjectid=(SELECT SubjectId FROM `subject` where SubjectName='C#语言开发基础')
and SUBSTRING(ExamDate FROM 1 FOR 10)='2013/3/22' AND studentresult>=60;

--查询所有参加“Java编程基础”科目考试平均分。
select avg(studentresult) as Java编程基础科目平均分 from result
where subjectid=(SELECT SubjectId FROM `subject` WHERE SubjectName='Java编程基础');

--查询每个学期的总学时数，并按照升序排列。
select sum(classhour)as 总学时,GradeId as 年级 from subject group by gradeid;

--查询每个参加考试的学员的平均分。
select studentno as 学号,avg(studentresult)as 平均分 from result group by studentno;

--查询每门课程的平均分，并按照降序排列。
select SubjectName as 课程名,avg(studentresult)as 平均分 from `subject`,result WHERE `subject`.SubjectId=result.SubjectId group by result.subjectid order by avg(studentresult) desc;

--查询每个学生参加的所有考试的总分，并按照降序排列。
select studentno as 学号,sum(studentresult)as 总分 from result group by studentno order by avg(studentresult) desc

--查询每学期学时数超过50的课程数。
select gradename as 年级,count(*) as 学时数超过50的课程数 from subject,grade
where classhour>=50 AND subject.gradeid=grade.GradeId
group by subject.gradeid;

--查询每学期学生的平均年龄。
select GradeName as 年级,avg((YEAR(CURDATE())-SUBSTRING(BornDate FROM 1 FOR 4)))as 平均年龄 from student,grade
WHERE student.gradeid=grade.GradeId
group by student.gradeid;

--查询北京地区的每学期学生人数。
select GradeName as 年级,count(*) as 北京地区总人数 from student,grade
where address like '%北京%' AND student.gradeid=grade.GradeId
group by student.gradeid;

--查询参加考试的学生中，平均分及格的学生记录，并按照成绩降序排列
select studentno as 学号,avg(studentresult) as 平均分 from result
group by studentno
having avg(studentresult)>=60
order by avg(studentresult) desc;

--查询考试日期2013-3-22的课程的及格平均分。
select * from result,student
WHERE student.studentno=result.StudentNo
group by result.StudentNo
having avg(studentresult)>=60
order by avg(studentresult) desc;

--查询参加考试至少一次考试不及格的学生学号、不及格次数。
SELECT StudentNo,COUNT(*) AS 不及格次数 FROM result
WHERE StudentResult<60
GROUP BY StudentNo
HAVING COUNT(StudentResult)>=1;

--查询学生姓名、所属年级名称及联系电话。
SELECT studentname,gradename,phone FROM student,grade WHERE student.GradeId=grade.GradeId;

--查询年级编号为1的科目名称、年级名称及学时。
SELECT subjectname,gradename,classhour from `subject`,grade where `subject`.GradeId=grade.GradeId AND `subject`.GradeId='1';

--查询参加科目编号为1的考试的学生姓名、分数、考试日期。
SELECT studentname,studentresult,examdate from result,student
WHERE result.SubjectId='1' AND result.StudentNo=student.StudentNo;

--查询学号为S1101007的学生参加的考试科目名称、分数、考试日期。
SELECT subjectname,studentresult,examdate from result,student,`subject`
WHERE result.SubjectId=`subject`.SubjectId AND result.StudentNo=student.StudentNo
AND student.StudentNo='S1101007';

--查询参加考试的学生学号、所考科目名称、分数、考试日期.
SELECT studentno,subjectname,studentresult,examdate from result,`subject`
WHERE result.SubjectId=`subject`.SubjectId ;

--查询学生学号、姓名、考试科目名称及成绩。
SELECT result.studentno,studentname,subjectname,studentresult from result,student,`subject`
WHERE result.SubjectId=`subject`.SubjectId AND student.StudentNo=result.StudentNo;

--查询参加“Java编程基础”考试的学生姓名、成绩、考试日期。
select s.studentname as"姓名" ,r.studentresult as "成绩" ,r.examdate as "考试日期" from result as r,student as s,subject as j
where(r.studentno=s.studentno)and (r.subjectid=j.subjectid) and j.subjectname="Java编程基础";

--查询所有科目的参考信息（某些科目可能还没有被考试过）
select j.subjectname as "科目名称", r.studentno as"学号", r.studentresult as "分数" from subject as j left outer join result as r on j.subjectid=r.subjectid;

--查询没有被考过的科目信息。
select j.subjectname as "科目名称"from result as r right outer join subject as j on j.subjectid=r.subjectid where r.studentresult is null and r.studentno is null;

--所有年级对应的学生信息（需要考虑某些年级可能还没有学生就读）

select g.gradename as"",s.studentno as"学号",s.studentname as"姓名" from grade as g left outer join student as s on g.gradeid=s.gradeid;

表如下：

result成绩表：

student学生表：

subject课程表：

grade年级表：

sql脚本：

/*
Navicat MySQL Data Transfer

Source Server         : 本地mysql
Source Server Version : 50715
Source Host           : localhost:3306
Source Database       : geminno_day_07

Target Server Type    : MYSQL
Target Server Version : 50715
File Encoding         : 65001

Date: 2017-07-11 19:16:14
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`GradeId` varchar(255) DEFAULT NULL,
`GradeName` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES ('1', 'S1');
INSERT INTO `grade` VALUES ('2', 'S2');
INSERT INTO `grade` VALUES ('3', 'S3');

-- ----------------------------
-- Table structure for result
-- ----------------------------
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
`Id` varchar(255) DEFAULT NULL,
`StudentNo` varchar(255) DEFAULT NULL,
`SubjectId` varchar(255) DEFAULT NULL,
`StudentResult` varchar(255) DEFAULT NULL,
`ExamDate` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of result
-- ----------------------------
INSERT INTO `result` VALUES ('1', 'S1201302001', '1', '80', '2013/9/13 星期五');
INSERT INTO `result` VALUES ('2', 'S1201302002', '1', '45', '2013/9/13 星期五');
INSERT INTO `result` VALUES ('3', 'S1201302001', '2', '90', '2013/10/18 星期五');
INSERT INTO `result` VALUES ('5', 'S1201302002', '2', '60', '2013/10/18 星期五');
INSERT INTO `result` VALUES ('12', 'S2102001', '3', '76', '2013/9/13 星期五');
INSERT INTO `result` VALUES ('13', 'S2102002', '3', '90', '2013/9/13 星期五');
INSERT INTO `result` VALUES ('14', 'S2102003', '3', '99', '2013/9/13 星期五');
INSERT INTO `result` VALUES ('15', 'S2102004', '3', '79', '2013/9/13 星期五');
INSERT INTO `result` VALUES ('16', 'S2102005', '3', '80', '2013/9/13 星期五');
INSERT INTO `result` VALUES ('17', 'S2102006', '3', '61', '2013/9/13 星期五');
INSERT INTO `result` VALUES ('18', 'S2102007', '3', '82', '2013/9/13 星期五');
INSERT INTO `result` VALUES ('19', 'S2102008', '3', '35', '2013/9/13 星期五');
INSERT INTO `result` VALUES ('20', 'S2102009', '3', '64', '2013/9/13 星期五');
INSERT INTO `result` VALUES ('21', 'S2102010', '3', '65', '2013/9/13 星期五');
INSERT INTO `result` VALUES ('22', 'S1101001', '1', '80', '2013/12/22 星期日');
INSERT INTO `result` VALUES ('23', 'S1101002', '1', '61', '2013/12/23 星期一');
INSERT INTO `result` VALUES ('24', 'S1101003', '1', '82', '2013/12/22 星期日');
INSERT INTO `result` VALUES ('25', 'S1101004', '1', '55', '2013/12/23 星期一');
INSERT INTO `result` VALUES ('26', 'S1101005', '1', '64', '2013/12/22 星期日');
INSERT INTO `result` VALUES ('27', 'S1101006', '1', '65', '2013/12/23 星期一');
INSERT INTO `result` VALUES ('28', 'S1101007', '1', '88', '2013/12/22 星期日');
INSERT INTO `result` VALUES ('29', 'S1101008', '1', '67', '2013/12/23 星期一');
INSERT INTO `result` VALUES ('30', 'S1101009', '1', '68', '2013/12/22 星期日');
INSERT INTO `result` VALUES ('31', 'S1101010', '1', '44', '2013/12/23 星期一');
INSERT INTO `result` VALUES ('32', 'S1101011', '1', '89', '2013/12/22 星期日');
INSERT INTO `result` VALUES ('33', 'S1101012', '1', '71', '2013/12/23 星期一');
INSERT INTO `result` VALUES ('34', 'S1101013', '1', '44', '2013/12/22 星期日');
INSERT INTO `result` VALUES ('35', 'S1101014', '1', '77', '2013/12/23 星期一');
INSERT INTO `result` VALUES ('36', 'S1101015', '1', '86', '2013/12/22 星期日');
INSERT INTO `result` VALUES ('37', 'S1101016', '1', '75', '2013/12/23 星期一');
INSERT INTO `result` VALUES ('38', 'S1101017', '1', '76', '2013/12/22 星期日');
INSERT INTO `result` VALUES ('39', 'S1101018', '1', '90', '2013/12/23 星期一');
INSERT INTO `result` VALUES ('40', 'S1101019', '1', '99', '2013/12/22 星期日');
INSERT INTO `result` VALUES ('41', 'S1101020', '1', '79', '2013/12/23 星期一');
INSERT INTO `result` VALUES ('42', 'S1101001', '2', '80', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('43', 'S1101002', '2', '61', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('44', 'S1101003', '2', '82', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('45', 'S1101004', '2', '35', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('46', 'S1101005', '2', '64', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('47', 'S1101006', '2', '65', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('48', 'S1101007', '2', '88', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('49', 'S1101008', '2', '67', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('50', 'S1101009', '2', '68', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('51', 'S1101010', '2', '44', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('52', 'S1101011', '2', '75', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('53', 'S1101012', '2', '71', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('54', 'S1101013', '2', '44', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('55', 'S1101014', '2', '88', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('56', 'S1101015', '2', '86', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('57', 'S1101016', '2', '75', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('58', 'S1101017', '2', '38', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('59', 'S1101018', '2', '90', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('60', 'S1101019', '2', '99', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('61', 'S1101020', '2', '79', '2013/11/15 星期五');
INSERT INTO `result` VALUES ('62', 'S1101011', '1', '68', '2013/7/1 星期一');
INSERT INTO `result` VALUES ('63', 'S1101014', '1', '75', '2013/7/1 星期一');
INSERT INTO `result` VALUES ('64', 'S1101008', '2', '45', '2013/7/1 星期一');
INSERT INTO `result` VALUES ('65', 'S1101014', '2', '60', '2013/7/1 星期一');
INSERT INTO `result` VALUES ('66', 'S2102001', '8', '99', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('67', 'S2102002', '8', '55', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('68', 'S2102003', '8', '60', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('69', 'S2102004', '8', '70', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('70', 'S2102005', '8', '85', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('71', 'S2102006', '8', '88', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('72', 'S2102007', '8', '95', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('73', 'S2102008', '8', '44', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('74', 'S2102009', '8', '68', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('75', 'S2102010', '8', '88', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('76', 'S2102011', '8', '88', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('77', 'S1101001', '3', '88', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('78', 'S1101002', '3', '50', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('79', 'S1101003', '3', '44', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('80', 'S1101004', '3', '77', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('81', 'S1101005', '3', '75', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('82', 'S1101006', '3', '63', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('83', 'S1101007', '3', '15', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('84', 'S1101008', '3', '55', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('85', 'S1101009', '3', '69', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('86', 'S1101010', '3', '90', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('87', 'S1101011', '3', '95', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('88', 'S1101001', '4', '99', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('89', 'S1101002', '4', '60', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('90', 'S1101003', '4', '65', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('91', 'S1101004', '4', '77', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('92', 'S1101005', '4', '75', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('93', 'S1101006', '4', '63', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('94', 'S1101007', '4', '45', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('95', 'S1101008', '4', '55', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('96', 'S1101009', '4', '69', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('97', 'S1101010', '4', '90', '2013/3/22 星期五');
INSERT INTO `result` VALUES ('98', 'S1101011', '4', '99', '2013/3/22 星期五');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`StudentNo` varchar(255) DEFAULT NULL,
`LoginPwd` varchar(255) DEFAULT NULL,
`StudentName` varchar(255) DEFAULT NULL,
`Sex` varchar(255) DEFAULT NULL,
`GradeId` varchar(255) DEFAULT NULL,
`Phone` varchar(255) DEFAULT NULL,
`Address` varchar(255) DEFAULT NULL,
`BornDate` varchar(255) DEFAULT NULL,
`Email` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('S1101001', '228996245', '金蝶', '男', '1', '717215147', '天津市河西区', '1995/1/22 星期日', 'okiwcydml@ryhlo.com');
INSERT INTO `student` VALUES ('S1101002', '228996246', '洛飞', '女', '1', '666762663', '天津市南开区', '1995/4/22 星期六', 'jnqlpkdwb@nsjpt.com');
INSERT INTO `student` VALUES ('S1101003', '228996247', '凌辉', '女', '1', '353149818', '北京市海淀区成府路', '1998/6/17 星期三', 'eepispykh@oitbl.com');
INSERT INTO `student` VALUES ('S1101004', '228996251', '白燕', '男', '1', '676151367', '学生宿舍', '1995/07/11星期二', 'cxmnnrhfn@sjsam.com');
INSERT INTO `student` VALUES ('S1101005', '228996252', '夏一桐', '男', '1', '397739963', '北京市朝阳区大屯', '1994/11/25 星期五', 'npgiygxox@ootml.com');
INSERT INTO `student` VALUES ('S1101006', '228996253', '欧阳燕飞', '男', '1', '13512345678', '河南省南阳市', '1992/8/31 星期一', 'dkwkiqbrj@yrkro.com');
INSERT INTO `student` VALUES ('S1101007', '228996254', '孟祥亚', '男', '1', '13512345679', '河南省洛阳市涧西区', '1998/07/11 星期四', 'gekdinmky@ijisq.com');
INSERT INTO `student` VALUES ('S1101008', '228996257', '凌洋', '女', '1', '15812345680', '湖南省长沙', '1995/2/12 星期日', '');
INSERT INTO `student` VALUES ('S1101009', '228996259', '杨阳', '男', '1', '13512345681', '上海市长虹区', '1994/4/3 星期日', '');
INSERT INTO `student` VALUES ('S1101010', '228996263', '方晴', '男', '1', '13412345682', '山东省聊城市', '1995/9/24 星期日', '');
INSERT INTO `student` VALUES ('S1101011', '228996267', '圆荷', '女', '1', '13512344483', '河北省石家庄', '1994/5/29 星期日', 'idfwxlbjr@bkxko.com');
INSERT INTO `student` VALUES ('S1101012', '228996270', '崔今生', '女', '1', '13512345684', '河北省邯郸市', '1995/3/20 星期一', 'qrakldetd@ogtso.com');
INSERT INTO `student` VALUES ('S1101013', '228996271', '姜北', '男', '1', '578346237', '学生宿舍', '1995/4/9 星期日', 'soppebati@hwxpo.com');
INSERT INTO `student` VALUES ('S1101014', '228996273', '姜丫丫', '男', '1', '926457035', '北京市朝阳区慧忠里', '1997/9/6 星期六', 'rfkhgcxhy@oomkw.com');
INSERT INTO `student` VALUES ('S1101015', '228996274', '孙河', '男', '1', '895684949', '北京市通州区', '1995/9/7 星期四', 'kdaqojpjj@olmcy.com');
INSERT INTO `student` VALUES ('S1101016', '228996275', '王可', '男', '1', '328884827', '北京市石景山', '1995/9/8 星期五', 'bojzfsixf@qjwgw.com');
INSERT INTO `student` VALUES ('S1101017', '228996276', '赵七', '女', '1', '511686053', '北京市海淀区中关村', '1990/9/9 星期日', 'ltshcitdp@qdpeh.com');
INSERT INTO `student` VALUES ('S1101018', '228996279', '买燕', '男', '1', '155426854', '河南省周口', '1995/9/10 星期日', 'rmpnhbiop@mwrom.com');
INSERT INTO `student` VALUES ('S1101019', '228996281', '冯征', '男', '1', '436987577', '湖北省襄樊', '1992/9/10 星期四', 'mdokehdic@omcte.com');
INSERT INTO `student` VALUES ('S1101020', '228996283', '冯中', '男', '1', '786340887', '天津市南开区', '1995/9/12 星期二', 'lwspyimci@kcplq.com');
INSERT INTO `student` VALUES ('S1201302001', 'zhangsan', '张三', '男', '1', '01062768866', '解放路', '1994/1/1 星期六', 'zhangsan@126.com');
INSERT INTO `student` VALUES ('S1201302002', 'lisi89', '李四', '男', '1', '13812345678', '长江路', '1992/2/1 星期六', '未知@');
INSERT INTO `student` VALUES ('S1201302003', '123456', '王五', '男', '1', '13912345678', '学生宿舍', '1992/5/3 星期日', '未知@');
INSERT INTO `student` VALUES ('S1201302004', 'wanglili', '王丽丽', '女', '1', '13112345678', '山东省济南市文化路1号院', '1991/6/2 星期日', 'wangll@sohu.com');
INSERT INTO `student` VALUES ('S1201302005', '123456', '张峰', '男', '2', '13212345678', '学生宿舍', '1994/8/8 星期一', '未知@');
INSERT INTO `student` VALUES ('S2102001', '228996284', '包卫丽', '女', '2', '13875940258', '湖州德清', '1992/9/13 星期三', 'sakuralove2829@yahoo.com');
INSERT INTO `student` VALUES ('S2102002', '228996285', '吴可鹏', '男', '2', '13875940259', '学生宿舍', '1996/9/13 星期五', 'peggyonhkhk@yahoo.com');
INSERT INTO `student` VALUES ('S2102003', '228996287', '於炉冰', '男', '2', '13875940260', '学生宿舍', '1995/9/15 星期五', 'yyyyyyyyyyu@yahoo.com');
INSERT INTO `student` VALUES ('S2102004', '228996290', '朱月清', '女', '2', '13875940261', '衢州开化', '1995/9/16 星期六', 'yinyin01094@yahoo.com.hk');
INSERT INTO `student` VALUES ('S2102005', '228996291', '赵献星', '男', '2', '13875940262', '杭州建德', '1995/2/12 星期日', 'ada19262002@hotmail.com');
INSERT INTO `student` VALUES ('S2102006', '228996292', '吴宝玉', '女', '2', '13875940263', '湖州埭溪', '1992/2/13 星期四', '');
INSERT INTO `student` VALUES ('S2102007', '228996293', '胡梦秋', '女', '2', '13875940264', '杭州临安', '1995/2/14 星期二', 'bobo_lok13@yahoo.com');
INSERT INTO `student` VALUES ('S2102008', '228996302', '吴春红', '女', '2', '13875940265', '金华金东区', '1995/2/15 星期三', 'yintakli@yahoo.com.hk');
INSERT INTO `student` VALUES ('S2102009', '228996304', '张文俊', '男', '2', '13974807750', '绍兴上虞', '1998/2/16 星期一', 'natalielam01098@yahoo.com');
INSERT INTO `student` VALUES ('S2102010', '228996305', '乐晓燕', '男', '2', '13007426477', '台州三门', '1995/2/17 星期五', '');
INSERT INTO `student` VALUES ('S2102011', '228996307', '裴珍', '男', '2', '13873140386', '北京大兴区', '1994/12/1 星期四', 'lingling52030996@hotmail.com');
INSERT INTO `student` VALUES ('S2102012', '228996308', '周巧飞', '男', '2', '13107428484', '学生宿舍', '1994/12/2 星期五', 'yuenfunki@yahoo.com.hk');
INSERT INTO `student` VALUES ('S2102013', '228996309', '王晓文', '男', '2', '13574220088', '杭州建德', '1997/12/3 星期三', 'kykwok9@hotmail.com');
INSERT INTO `student` VALUES ('S2102014', '228996312', '卢凤华', '女', '2', '13574220089', '衢州开化', '1994/12/4 星期日', 'sulia123@yahoo.com');
INSERT INTO `student` VALUES ('S2102015', '228996316', '胡灵', '女', '2', '13574220090', '台州天台', '1994/12/5 星期一', 'chankwaiching@yahoo.com');
INSERT INTO `student` VALUES ('S2102016', '228996317', '盛立', '男', '2', '13574220091', '安徽阜阳', '1994/12/6 星期二', 'yankchan2004@yahoo.com');
INSERT INTO `student` VALUES ('S2102017', '228996318', '吴孝红', '男', '2', '13678945617', '黑龙江哈尔滨', '1994/12/7 星期三', 'ayako04620@yahoo.com');
INSERT INTO `student` VALUES ('S2102018', '228996323', '曾飞燕', '男', '2', '13678945618', '杭州临安', '1998/12/8 星期二', 'ayako04620@yahoo.com');
INSERT INTO `student` VALUES ('S2102019', '228996324', '陈科', '男', '2', '13678945619', '新疆乌鲁木齐', '1994/8/25 星期四', '');
INSERT INTO `student` VALUES ('S2102020', '228996325', '严丽丽', '男', '2', '13678945620', '新疆伊宁', '1995/7/17 星期一', 'phoebe_swy@yahoo.com');
INSERT INTO `student` VALUES ('S31003001', '228996326', '孙瑞晨', '男', '3', '13574220096', '金华兰溪', '1994/7/17 星期日', 'faat11@yahoo.com');
INSERT INTO `student` VALUES ('S31003002', '228996327', '黄金宵', '男', '3', '13574220097', '绍兴诸暨', '1994/4/8 星期五', '');
INSERT INTO `student` VALUES ('S31003003', '228996329', '陈婷婷', '男', '3', '13574220098', '衢州常山', '1994/6/20 星期一', 'mandylau1986@yahoo.com');
INSERT INTO `student` VALUES ('S31003004', '228996334', '章巧丹', '女', '3', '13174220099', '宁波宁海', '1994/8/22 星期一', '');
INSERT INTO `student` VALUES ('S31003005', '228996340', '刘丽云 ', '女', '3', '13174220100', '学生宿舍', '1994/1/25 星期二', 'raymondchoi04@yahoo.com');
INSERT INTO `student` VALUES ('S31003006', '228996341', '丁亮', '男', '3', '13174220101', '嘉兴嘉善', '1994/3/28 星期一', 'tlh10202003@yahoo.com');
INSERT INTO `student` VALUES ('S31003007', '228996342', '李明华', '女', '3', '13574220102', '金华兰溪', '1993/6/1 星期二', 'mimichoi276@yahoo.com');
INSERT INTO `student` VALUES ('S31003008', '228996343', ' 金晶', '男', '3', '13574220103', '学生宿舍', '1993/6/2 星期三', 'mapdhhljp@ncqfg.com');
INSERT INTO `student` VALUES ('S31003009', '228996346', '张世庆', '男', '3', '13574220104', '湖州双林', '1995/6/3 星期六', 'godspnrel@eblox.com');
INSERT INTO `student` VALUES ('S31003010', '228996348', '方汝滔', '男', '3', '13574220105', '衢州常山', '1993/6/4 星期五', 'wieiowzmq@jplop.com');
INSERT INTO `student` VALUES ('S31003011', '228996349', '王波', '男', '3', '13574220106', '北京朝阳区', '1993/6/5 星期六', 'wengsanc14l@yahoo.com');
INSERT INTO `student` VALUES ('S31003012', '228996351', '颜俊俊', '男', '3', '13374220107', '衢州龙游', '1993/6/6 星期日', 'wengsanc15l@yahoo.com');
INSERT INTO `student` VALUES ('S31003013', '228996352', '刘雪琼', '女', '3', '13374220108', '绍兴嵊州', '1994/9/8 星期四', 'wengsanc16l@yahoo.com');
INSERT INTO `student` VALUES ('S31003014', '228996353', '胡晓奕', '女', '3', '13374220109', '学生宿舍', '1994/12/6 星期二', 'wengsanc17l@yahoo.com');
INSERT INTO `student` VALUES ('S31003015', '228996357', '王莉莉', '女', '3', '13374220110', '湖州杨家埠', '1995/1/20 星期五', '');
INSERT INTO `student` VALUES ('S31003016', '228996362', '金辉', '男', '3', '13574220111', '台州温岭', '1994/7/7 星期四', 'wengsanc19l@yahoo.com');
INSERT INTO `student` VALUES ('S31003017', '228996364', '郑峰', '男', '3', '13574220112', '北京海淀区', '1994/12/7 星期三', 'wengsanc20l@yahoo.com');
INSERT INTO `student` VALUES ('S31003018', '228996365', '何国英', '男', '3', '13574220113', '台州温岭', '1993/5/14 星期五', '');
INSERT INTO `student` VALUES ('S31003019', '228996370', '方振', '男', '3', '13574220114', '台州三门', '1995/2/11 星期六', 'wengsanc22l@yahoo.com');
INSERT INTO `student` VALUES ('S31003020', '228996371', '雷应飞', '男', '3', '13574220115', '湖州吴兴区', '1994/3/31 星期四', 'wengsanc23l@yahoo.com');

-- ----------------------------
-- Table structure for subject
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
`SubjectId` varchar(255) DEFAULT NULL,
`SubjectName` varchar(255) DEFAULT NULL,
`ClassHour` varchar(255) DEFAULT NULL,
`GradeId` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO `subject` VALUES ('1', 'Java编程基础', '55', '1');
INSERT INTO `subject` VALUES ('2', 'HTML和CSS网页技术', '60', '1');
INSERT INTO `subject` VALUES ('3', 'C#语言开发基础', '70', '1');
INSERT INTO `subject` VALUES ('4', '使用C#语言开发数据库应用系统', '36', '1');
INSERT INTO `subject` VALUES ('5', '深入.NET平台和C#编程', '76', '2');
INSERT INTO `subject` VALUES ('6', 'SqlServer数据库', '42', '2');
INSERT INTO `subject` VALUES ('7', '基于.NET平台的软件系统分层开发', '44', '2');
INSERT INTO `subject` VALUES ('8', '面向对象程序设计', '59', '2');
INSERT INTO `subject` VALUES ('9', 'JavaScript案例开发', '40', '2');
INSERT INTO `subject` VALUES ('10', '使用Java EE技术开发电子购物系统', '55', '2');
INSERT INTO `subject` VALUES ('11', '使用ASP.NET技术开发电子购物系统', '86', '3');
INSERT INTO `subject` VALUES ('12', '开发基于Ajax和控件技术的Web应用系', '60', '3');
INSERT INTO `subject` VALUES ('13', '使用Java框架技术开发网上信息发布系统', '52', '3');
INSERT INTO `subject` VALUES ('14', '使用Java企业级技术开发企业应用', '68', '3');