56,677
社区成员
发帖
与我相关
我的任务
分享
Navicat Premium Data Transfer
Source Server : 本机
Source Server Type : MySQL
Source Server Version : 80012
Source Host : localhost:3306
Source Schema : test
Target Server Type : MySQL
Target Server Version : 80012
File Encoding : 65001
Date: 11/03/2019 23:26:12
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for actor
-- ----------------------------
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`actor_id` int(11) NOT NULL AUTO_INCREMENT,
`actor_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '演员名称',
PRIMARY KEY (`actor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 COMMENT='演员表';
-- ----------------------------
-- Records of actor
-- ----------------------------
BEGIN;
INSERT INTO `actor` VALUES (4, '古月方源');
INSERT INTO `actor` VALUES (5, '凤九歌');
INSERT INTO `actor` VALUES (6, '吴帅');
INSERT INTO `actor` VALUES (8, '龙公');
INSERT INTO `actor` VALUES (9, '星宿仙尊');
COMMIT;
-- ----------------------------
-- Table structure for genre
-- ----------------------------
DROP TABLE IF EXISTS `genre`;
CREATE TABLE `genre` (
`genre_id` int(11) NOT NULL AUTO_INCREMENT,
`genre_name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`genre_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- ----------------------------
-- Records of genre
-- ----------------------------
BEGIN;
INSERT INTO `genre` VALUES (1, '科幻');
INSERT INTO `genre` VALUES (2, '剧情');
INSERT INTO `genre` VALUES (3, '喜剧');
INSERT INTO `genre` VALUES (4, '恐怖');
COMMIT;
-- ----------------------------
-- Table structure for movie
-- ----------------------------
DROP TABLE IF EXISTS `movie`;
CREATE TABLE `movie` (
`movie_id` int(11) NOT NULL AUTO_INCREMENT,
`movie_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`movie_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=156 DEFAULT CHARSET=utf8 COMMENT='电影表';
-- ----------------------------
-- Records of movie
-- ----------------------------
BEGIN;
INSERT INTO `movie` VALUES (1, '蛊真人');
INSERT INTO `movie` VALUES (2, '教父');
INSERT INTO `movie` VALUES (3, '七武士');
INSERT INTO `movie` VALUES (4, '天堂电影院');
COMMIT;
-- ----------------------------
-- Table structure for movie_actor
-- ----------------------------
DROP TABLE IF EXISTS `movie_actor`;
CREATE TABLE `movie_actor` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`movie_id` int(11) NOT NULL DEFAULT '0' COMMENT '电影id',
`actor_id` int(11) NOT NULL COMMENT '演员id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='电影、演员中间表';
-- ----------------------------
-- Records of movie_actor
-- ----------------------------
BEGIN;
INSERT INTO `movie_actor` VALUES (1, 1, 4);
INSERT INTO `movie_actor` VALUES (2, 1, 5);
INSERT INTO `movie_actor` VALUES (3, 1, 5);
INSERT INTO `movie_actor` VALUES (4, 1, 8);
INSERT INTO `movie_actor` VALUES (5, 1, 9);
INSERT INTO `movie_actor` VALUES (6, 2, 3);
INSERT INTO `movie_actor` VALUES (7, 3, 5);
INSERT INTO `movie_actor` VALUES (8, 2, 9);
INSERT INTO `movie_actor` VALUES (9, 3, 8);
INSERT INTO `movie_actor` VALUES (10, 3, 9);
INSERT INTO `movie_actor` VALUES (11, 4, 8);
INSERT INTO `movie_actor` VALUES (12, 5, 4);
COMMIT;
-- ----------------------------
-- Table structure for movie_genre
-- ----------------------------
DROP TABLE IF EXISTS `movie_genre`;
CREATE TABLE `movie_genre` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`movie_id` int(11) NOT NULL DEFAULT '0' COMMENT '电影id',
`genre_id` int(11) NOT NULL DEFAULT '0' COMMENT '分类id',
PRIMARY KEY (`id`),
KEY `asset_id` (`movie_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='电影、分类中间表';
-- ----------------------------
-- Records of movie_genre
-- ----------------------------
BEGIN;
INSERT INTO `movie_genre` VALUES (1, 1, 2);
INSERT INTO `movie_genre` VALUES (2, 1, 4);
INSERT INTO `movie_genre` VALUES (3, 2, 2);
INSERT INTO `movie_genre` VALUES (4, 3, 2);
INSERT INTO `movie_genre` VALUES (5, 3, 4);
INSERT INTO `movie_genre` VALUES (6, 3, 3);
INSERT INTO `movie_genre` VALUES (7, 4, 2);
INSERT INTO `movie_genre` VALUES (8, 4, 3);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
-- 你试试这个语句
select *
from (
select
m.movid_id ,
m.movid_name ,
group_concat(g.genre_name) genre_name
FROM
movie m
left join movie_genre mg on mg.movie_id = m.movie_id
left join genre g on g.genre_id = mg.genre_id
group by m.movie_id
) a join
(
select
m.movid_id ,
m.movid_name ,
group_concat(g.actor_name) actor_name
FROM
movie m
left join movie_actor ma on mg.movie_id = m.movie_id
left join actor a on g.actor_id = mg.actor_id
group by m.movie_id
) b on a.movid_id = b.movid_id