1、DDL
CREATE TABLE `result` (
`rid` int(11) NOT NULL AUTO_INCREMENT COMMENT '成绩编号',
`testName` varchar(255) DEFAULT NULL COMMENT '测试名称',
`score` double(4,2) DEFAULT NULL COMMENT '成绩',
`studentId` int(11) DEFAULT NULL COMMENT '学生id',
PRIMARY KEY (`rid`),
KEY `studentId` (`studentId`),
CONSTRAINT `result_ibfk_1` FOREIGN KEY (`studentId`) REFERENCES `student` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`createDate` datetime DEFAULT NULL,
`userName` varchar(20) DEFAULT NULL,
`pwd` varchar(36) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`sex` char(2) DEFAULT '男',
`introduce` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
2、DML
SELECT studentId '学号',userName '学生名称',
sum (score) '学生各科目总分',
count (score) '考试科目数量',
AVG (score) '各科目平均分',
from result inner join student on result.studentId=student.id
GROUP BY studentId,userName
HAVING avg (score)>60
ORDER BY AVG(score) desc
SELECT * from result inner join student on result.studentId=student.id
标签:studentId,多表,DEFAULT,联结,score,result,MYSQL,NULL,id
From: https://blog.csdn.net/2302_80152430/article/details/137276514