1.建表和数据
学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`classid` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-------------------------------- Records of student
------------------------------
INSERT INTO `student` VALUES ('1','小王','1');
INSERT INTO `student` VALUES ('2','小明','1');
INSERT INTO `student` VALUES ('3','小李','2');
INSERT INTO `student` VALUES ('4','小刘','2');
INSERT INTO `student` VALUES ('5','小多','2');
班级表
DROP TABLE IF EXISTS `class1`;
CREATE TABLE `class1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;-------------------------------- Records of class1
------------------------------
INSERT INTO `class1` VALUES ('1','一班');
INSERT INTO `class1` VALUES ('2','二班');
INSERT INTO `class1` VALUES ('3','三班');
成绩表
DROP TABLE IF EXISTS `cj`;
CREATE TABLE `cj` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stid` int(11) DEFAULT NULL,
`score` int(255) DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-------------------------------- Records of cj
------------------------------
INSERT INTO `cj` VALUES ('1','1','80');
INSERT INTO `cj` VALUES ('2','2','10');
INSERT INTO `cj` VALUES ('3','3','60');
INSERT INTO `cj` VALUES ('4','4','70');
INSERT INTO `cj` VALUES ('5','5','65');
2.三张表格left join关联查询(以班级为依据条件查询)
select c.`name` as className,s.`name` as stuName,j.score as stuScore
from(class1 c LEFT JOIN student s on c.id=s.classid)
LEFT JOIN cj j
on s.id=j.stid
GROUP BY c.`name`;