ITPub博客

首页 > 数据库 > MySQL > mysql脚本,新建表和理清表之间的联系

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

原创 MySQL 作者:心盲1029 时间:2018-07-31 15:32:24 0 删除 编辑

/*

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



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31546585/viewspace-2168737/,如需转载,请注明出处,否则将追究法律责任。

下一篇: mysql面试练习题
请登录后发表评论 登录
全部评论

注册时间:2018-07-19

  • 博文量
    15
  • 访问量
    14133