SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`gradeID` int(4) NOT NULL,
`gradeName` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`gradeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES ('1', '一年级');
INSERT INTO `grade` VALUES ('2', '二年级');
INSERT INTO `grade` VALUES ('3', '三年级');
-- ----------------------------
-- Table structure for result
-- ----------------------------
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
`studentNo` int(4) NOT NULL,
`subjectNo` int(4) NOT NULL,
`examDate` datetime NOT NULL,
`studentResule` int(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Records of result
-- ----------------------------
INSERT INTO `result` VALUES ('1', '3', '2021-11-28 20:05:56', '90');
INSERT INTO `result` VALUES ('1', '3', '2021-11-28 20:06:16', '80');
INSERT INTO `result` VALUES ('1', '3', '2021-11-28 20:06:56', '70');
INSERT INTO `result` VALUES ('2', '1', '2021-11-28 20:07:29', '70');
INSERT INTO `result` VALUES ('2', '2', '2021-11-28 20:07:43', '60');
INSERT INTO `result` VALUES ('3', '2', '2021-11-28 20:08:08', '60');
INSERT INTO `result` VALUES ('4', '4', '2021-11-28 20:08:40', '55');
INSERT INTO `result` VALUES ('4', '2', '2021-11-28 20:08:53', '100');
INSERT INTO `result` VALUES ('4', '3', '2021-11-28 20:09:03', '99');
INSERT INTO `result` VALUES ('5', '2', '2021-11-28 20:10:06', '55');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`studentNo` int(4) NOT NULL COMMENT '学号',
`loginPwd` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT '密码',
`studentName` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT '学生姓名',
`sex` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '男' COMMENT '性别',
`gradeId` int(4) unsigned DEFAULT NULL COMMENT '年级编号',
`phone` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '联系电话',
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT '地址不详' COMMENT '地址',
`bornDate` datetime DEFAULT NULL COMMENT '出生日期',
`email` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '邮件账号',
`identityCard` varchar(18) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentNo`),
UNIQUE KEY `identityCard` (`identityCard`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='学生表';
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '123', '袋鼠', '男', '3', '111111111', '地址不详', '2021-11-28 19:23:35', '163@.com', '111111111111111111');
INSERT INTO `student` VALUES ('2', '234', '乌鸦', '男', '2', '222222222', '地址不详', '2021-11-28 19:24:37', 'qq@.com', '222222222222222222');
INSERT INTO `student` VALUES ('3', '345', '恐龙', '女', '1', '333333333', '地址不详', '2021-11-28 19:25:12', 'shouhu@.com', '333333333333333333');
INSERT INTO `student` VALUES ('4', '456', '鱿鱼', '男', '3', '4444444444444444', '地址不详', '2021-11-28 19:25:52', 'huya@.com', '444444444444444444');
INSERT INTO `student` VALUES ('5', '567', '鸭子', '女', '2', '55555555555555555', '地址不详', '2021-11-03 19:26:33', 'facebook@.com', '555555555555555555');
INSERT INTO `student` VALUES ('6', '678', '蜈蚣', '男', '1', '66666666666666', '地址不详', '2021-01-12 19:27:51', 'meta@.com', '666666666666666666');
INSERT INTO `student` VALUES ('7', '789', '老鹰', '女', '3', '777777777777', '地址不详', '2020-02-28 19:30:06', '126@.com', '777777777777777777');
INSERT INTO `student` VALUES ('8', '890', '野猪', '男', '2', '88888888888', '地址不详', '1999-11-28 19:31:48', 'koyo@.com', '888888888888888888');
INSERT INTO `student` VALUES ('9', '000', '蝙蝠', '男', '1', '99999999999999999999', '地址不详', '2020-10-08 19:32:55', 'weixin@.com', '999999999999999999');
-- ----------------------------
-- Table structure for subject
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
`subjectNo` int(4) NOT NULL,
`subjectName` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`classHour` int(4) DEFAULT NULL,
`gradeID` int(4) DEFAULT NULL,
PRIMARY KEY (`subjectNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO `subject` VALUES ('1', '物理', '50', '3');
INSERT INTO `subject` VALUES ('2', '化学', '50', '3');
INSERT INTO `subject` VALUES ('3', '英语', '35', '1');
INSERT INTO `subject` VALUES ('4', '地理', '35', '1');
INSERT INTO `subject` VALUES ('5', '语文', '50', '1');
INSERT INTO `subject` VALUES ('6', '生物', '50', '2');
SQL：
SELECT * from grade;
SELECT * from result;
SELECT * from student;
SELECT * from subject;
1.查询1年级的全部学生信息：
SELECT stu.studentName,stu.gradeId,res.studentResule from result as res,student as stu
where stu.studentNo = res.studentNo
and stu.gradeId ='1';
2.查询2年级的全部学生的姓名和电话
SELECT stu.studentName,stu.gradeId ,stu.gradeId as '那个年级' from student as stu where stu.gradeId='2';
3.查询1年级的全部女同学信息
SELECT stu.* from student as stu where stu.sex ='女' and stu.gradeId ='1' ;
4.查询课时超过30的科目信息
SELECT sub.* from `subject` as sub where sub.classHour >30;
5.查询1年级的科目名称：
SELECT sub.subjectName from `subject` as sub WHERE sub.gradeID ='1' ;
6.查询2年级所有男同学的姓名和地址：
SELECT stu.* from student as stu where stu.gradeId='2' and stu.sex='男'
7.查询无电子邮件的学生姓名和年级信息
SELECT * from student where email is null;
8.查询2年级并在1990后出生学生
SELECT * from student where bornDate> '1990-12-31 00:00';
9.查询参加了物理考试的学生信息和成绩
SELECT * from `subject` as sub ,result as res ,student as stu where res.subjectNo=sub.subjectNo
AND stu.studentNo = res.studentNo
and sub.subjectName='物理';
10.查询参加了物理科目考试的学员的总成绩和平均成绩
SELECT SUM(res.studentResule), avg(res.studentResule) from result as res , `subject` as sub where sub.subjectNo = res.subjectNo
and sub.subjectName ='物理'
11. 按照生日时间排序
SELECT * from student as stu ORDER BY stu.bornDate DESC;
12.查询年龄最小的学生的姓名及所在年级
SELECT min(student.bornDate),studentName from student ;
13.查询2016年2月17日之后出生的前5名的学生的学号和分数
SELECT * from student as stu where stu.bornDate > '2016-11-07' LIMIT 5;