CREATE TABLE `class` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`createdate` datetime DEFAULT NULL,
`username` varchar(255) DEFAULT NULL,
`pwd` varchar(255) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`sex` varchar(255) DEFAULT NULL,
`introduce` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
CREATE TABLE `result` (
`rid` int(11) NOT NULL AUTO_INCREMENT COMMENT '成绩编号',
`testname` varchar(255) DEFAULT NULL COMMENT '测试名称',
`score` double(255,2) DEFAULT NULL COMMENT '成绩',
`studentid` int(255) DEFAULT NULL,
PRIMARY KEY (`rid`),
KEY `studentid` (`studentid`),
CONSTRAINT `result_ibfk_1` FOREIGN KEY (`studentid`) REFERENCES `class` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
DML
INSERT into class VALUES(0,'2024-02-25','香菱','123','15614333260',18,'女','万民堂大厨');
INSERT into class VALUES(0,'2024-02-25','凝光','123','15614333260',26,'女','璃月七星天权星');
INSERT into class VALUES(0,'2024-02-25','刻晴','123','15614333260',23,'女','璃月七星玉衡星');
INSERT into class VALUES(0,'2024-02-25','北斗','123','15614333260',26,'女','大船长');
INSERT into class VALUES(0,'2024-02-25','胡桃','123','15614333260',18,'女','往生堂第77代堂主');
SELECT * FROM class;
INSERT into result values(0,'政治',96,1);
INSERT into result values(0,'地理',95,1);
INSERT into result values(0,'政治',94,1);
INSERT into result values(0,'英语',93,1);
INSERT into result values(0,'历史',92,1);
INSERT into result values(0,'化学',91,1);
INSERT into result values(0,'Java',96,1);
INSERT into result values(0,'Java',95,1);
INSERT into result values(0,'政治',94,1);
INSERT into result values(0,'物理',93,1);
INSERT into result values(0,'物理',92,1);
INSERT into result values(0,'化学',91,1);
DQL
# 分组显示时,只用被分组的列数据有效,其余无效
SELECT testname'学科名称',
sum(score)'科目总分数',
count(score)'科目考试人数',
avg(score)'科目平均成绩',
max(score)'科目最高分',
min(score)'科目最低分' # 最后一个不写,
FROM result
where studentid<5 # 具体的列信息筛选用where
GROUP BY testname; # 分组
HAVING avg(score)>70 # 聚合数据筛选用having
ORDER BY avg(score)desc; # ORDER BY一定在后方,最后是limit
# 分组显示时,只有被分组的列数据有效,其余无效
SELECT studentid'学号',username'学生姓名',introduce'学生简介',
sum(score)'学生各科目总成绩',
count(score)'学生参与考试科目数量',
avg(score)'学生所有科目平均成绩',
max(score)'学生所有科目成绩最高分',
min(score)'学生所有科目成绩最低分'
FROM result INNER JOIN class on result.studentid=class.id
WHERE sex ='女'
GROUP BY studentid,username,introduce
having avg(score)>90
ORDER BY avg(score) desc;
SELECT * FROM result INNER JOIN class on result.studentid=class.id