mysql脚本，新建表和理清表之间的联系

/* Navicat MySQL Data Transfer Source Server         : localhost Source Server Version : 50520 Source Host           : localhost:3306 Source Database       : gupao Target Server Type    : MYSQL Target Server Version : 50520 File Encoding         : 65001 Date: 2018-07-24 22:42:54 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for baoming -- ---------------------------- DROP TABLE IF EXISTS `baoming`; CREATE TABLE `baoming` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `sid` int(11) DEFAULT NULL,   `kid` int(11) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of baoming -- ---------------------------- INSERT INTO `baoming` VALUES ('1', '1', '1'); INSERT INTO `baoming` VALUES ('2', '2', '1'); INSERT INTO `baoming` VALUES ('3', '3', '1'); INSERT INTO `baoming` VALUES ('4', '4', '2'); INSERT INTO `baoming` VALUES ('5', '5', '2'); INSERT INTO `baoming` VALUES ('6', '6', '2'); INSERT INTO `baoming` VALUES ('7', '7', '3'); INSERT INTO `baoming` VALUES ('9', '1', '3'); INSERT INTO `baoming` VALUES ('10', '3', '3'); INSERT INTO `baoming` VALUES ('11', '5', '3'); -- ---------------------------- -- Table structure for class -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` (   `cid` int(11) NOT NULL AUTO_INCREMENT,   `level` int(11) DEFAULT NULL,   `name` varchar(255) DEFAULT NULL,   PRIMARY KEY (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of class -- ---------------------------- INSERT INTO `class` VALUES ('1', '1', '班级1'); INSERT INTO `class` VALUES ('2', '1', '班级2'); INSERT INTO `class` VALUES ('3', '2', '班级3'); INSERT INTO `class` VALUES ('4', '3', '班级4'); -- ---------------------------- -- Table structure for ke -- ---------------------------- DROP TABLE IF EXISTS `ke`; CREATE TABLE `ke` (   `kid` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(255) DEFAULT NULL,   `tid` int(11) DEFAULT NULL,   PRIMARY KEY (`kid`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of ke -- ---------------------------- INSERT INTO `ke` VALUES ('1', '课程1', '2'); INSERT INTO `ke` VALUES ('2', '课程2', '1'); INSERT INTO `ke` VALUES ('3', '课程3', '5'); INSERT INTO `ke` VALUES ('4', '课程4', '5'); INSERT INTO `ke` VALUES ('5', '课程0', '0'); INSERT INTO `ke` VALUES ('6', '课程', null); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` (   `sid` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(255) DEFAULT NULL,   `sex` int(11) DEFAULT '1',   `cid` int(11) DEFAULT NULL,   PRIMARY KEY (`sid`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES ('1', '学生1', '1', '1'); INSERT INTO `student` VALUES ('2', '学生2', '1', '1'); INSERT INTO `student` VALUES ('3', '学生3', '2', '1'); INSERT INTO `student` VALUES ('4', '学生4', '2', '1'); INSERT INTO `student` VALUES ('5', '学生5', '1', '2'); INSERT INTO `student` VALUES ('6', '学生6', '1', '2'); INSERT INTO `student` VALUES ('7', '学生7', '2', '2'); INSERT INTO `student` VALUES ('8', '学生8', '2', '2'); INSERT INTO `student` VALUES ('9', '学生9', '1', '3'); INSERT INTO `student` VALUES ('10', '学生0', '1', '0'); -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` (   `tid` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(255) DEFAULT NULL,   `sex` int(11) DEFAULT '1',   PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES ('1', '教授1', '1'); INSERT INTO `teacher` VALUES ('2', '教授2', '1'); INSERT INTO `teacher` VALUES ('3', '教授3', '2'); INSERT INTO `teacher` VALUES ('4', '教授4', '2'); INSERT INTO `teacher` VALUES ('5', '大教授', '1'); INSERT INTO `teacher` VALUES ('6', '助教', '2'); 在上课的老师 SELECT DISTINCT t.name FROM ke JOIN  teacher AS t ON  ke.tid=t.tid   老师和学生的对应关系 SELECT * FROM ke JOIN  teacher AS t ON  ke.tid=t.tid JOIN  baoming AS b ON ke.kid=b.kid JOIN student AS s ON b.sid=s.sid