Mysql练习-多表关联查询

select distinct 字段1,...,字段n from 库.表
where 条件
group by 分组字段
having 过滤
order by 排序字段
limit n;

create database test;

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(255) NOT NULL,
`grade_id` int(11) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

INSERT INTO `class` VALUES ('1', '一年一班', '1');
INSERT INTO `class` VALUES ('2', '二年一班', '2');
INSERT INTO `class` VALUES ('3', '三年二班', '3');
INSERT INTO `class` VALUES ('4', '一年二班', '1');
INSERT INTO `class` VALUES ('5', '一年三班', '1');
INSERT INTO `class` VALUES ('6', '一年四班', '1');
INSERT INTO `class` VALUES ('7', '二年二班', '2');
INSERT INTO `class` VALUES ('8', '二年三班', '2');
INSERT INTO `class` VALUES ('9', '三年一班', '3');
INSERT INTO `class` VALUES ('10', '四年一班', '4');
INSERT INTO `class` VALUES ('11', '五年一班', '5');
INSERT INTO `class` VALUES ('12', '五年二班', '5');
INSERT INTO `class` VALUES ('13', '五年三班', '5');
INSERT INTO `class` VALUES ('14', '五年四班', '5');
INSERT INTO `class` VALUES ('15', '五年五班', '5');

DROP TABLE IF EXISTS `class_grade`;
CREATE TABLE `class_grade` (
`gid` int(11) NOT NULL AUTO_INCREMENT,
`gname` varchar(255) NOT NULL,
PRIMARY KEY (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `class_grade` VALUES ('1', '一年级');
INSERT INTO `class_grade` VALUES ('2', '二年级');
INSERT INTO `class_grade` VALUES ('3', '三年级');
INSERT INTO `class_grade` VALUES ('4', '四年级');
INSERT INTO `class_grade` VALUES ('5', '五年级');
INSERT INTO `class_grade` VALUES ('6', '六年级');

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL,
`cname` varchar(255) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `course` VALUES ('1', '生物', '1');
INSERT INTO `course` VALUES ('2', '体育', '1');
INSERT INTO `course` VALUES ('3', '物理', '2');
INSERT INTO `course` VALUES ('4', '地理', '1');

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`score` varchar(255) DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
INSERT INTO `score` VALUES ('1', '1', '1', '60');
INSERT INTO `score` VALUES ('2', '1', '2', '59');
INSERT INTO `score` VALUES ('3', '2', '2', '99');
INSERT INTO `score` VALUES ('4', '3', '1', '10');
INSERT INTO `score` VALUES ('5', '3', '2', '88');
INSERT INTO `score` VALUES ('6', '4', '2', '89');
INSERT INTO `score` VALUES ('7', '5', '1', '56');
INSERT INTO `score` VALUES ('8', '5', '2', '12');
INSERT INTO `score` VALUES ('9', '6', '1', '23');
INSERT INTO `score` VALUES ('10', '7', '2', '43');
INSERT INTO `score` VALUES ('11', '8', '1', '65');
INSERT INTO `score` VALUES ('12', '8', '2', '76');
INSERT INTO `score` VALUES ('13', '9', '1', '98');
INSERT INTO `score` VALUES ('14', '10', '2', '57');
INSERT INTO `score` VALUES ('15', '11', '1', '76');
INSERT INTO `score` VALUES ('16', '11', '2', '65');
INSERT INTO `score` VALUES ('17', '12', '2', '55');
INSERT INTO `score` VALUES ('18', '2', '1', '30');
INSERT INTO `score` VALUES ('19', '1', '4', '59');

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(255) NOT NULL,
`gender` enum('女','男') NOT NULL DEFAULT '男',
`class_id` int(11) NOT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;


INSERT INTO `student` VALUES ('1', 'lucy', '女', '1');
INSERT INTO `student` VALUES ('16', '学生名07', '男', '3');
INSERT INTO `student` VALUES ('2', 'alicy', '女', '1');
INSERT INTO `student` VALUES ('3', 'laowang', '男', '2');
INSERT INTO `student` VALUES ('4', 'tom', '男', '2');
INSERT INTO `student` VALUES ('5', 'jack', '男', '2');
INSERT INTO `student` VALUES ('6', '学生名01', '男', '1');
INSERT INTO `student` VALUES ('7', '学生名02', '女', '1');
INSERT INTO `student` VALUES ('8', '学生名03', '男', '2');
INSERT INTO `student` VALUES ('9', '学生名04', '男', '1');
INSERT INTO `student` VALUES ('10', '学生名05', '男', '2');
INSERT INTO `student` VALUES ('11', '学生名06', '男', '1');
INSERT INTO `student` VALUES ('12', '学生名07', '男', '2');

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL,
`tname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `teacher` VALUES ('0', '赵老师');
INSERT INTO `teacher` VALUES ('1', '张老师');
INSERT INTO `teacher` VALUES ('2', '李老师');
INSERT INTO `teacher` VALUES ('3', '王老师');
INSERT INTO `teacher` VALUES ('4', '谢老师');

DROP TABLE IF EXISTS `teacher2cls`;
CREATE TABLE `teacher2cls` (
`tcid` int(11) NOT NULL AUTO_INCREMENT,
`tid` int(11) NOT NULL,
`cid` int(11) NOT NULL,
PRIMARY KEY (`tcid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;


INSERT INTO `teacher2cls` VALUES ('1', '1', '1');
INSERT INTO `teacher2cls` VALUES ('2', '1', '2');
INSERT INTO `teacher2cls` VALUES ('3', '2', '1');
INSERT INTO `teacher2cls` VALUES ('4', '3', '2');
INSERT INTO `teacher2cls` VALUES ('5', '3', '5');
INSERT INTO `teacher2cls` VALUES ('6', '4', '11');
INSERT INTO `teacher2cls` VALUES ('7', '3', '12');