Mysql多表查询

零:基础数据

表1:

表2:

表三:表数据

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for runoob_tbl
-- ----------------------------
DROP TABLE IF EXISTS `runoob_tbl`;
CREATE TABLE `runoob_tbl` (
  `runoob_id` int(11) NOT NULL AUTO_INCREMENT,
  `runoob_title` varchar(100) NOT NULL,
  `runoob_author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL,
  PRIMARY KEY (`runoob_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of runoob_tbl
-- ----------------------------
INSERT INTO `runoob_tbl` VALUES ('1', '学习 PHP', '菜鸟教程', '2017-04-12');
INSERT INTO `runoob_tbl` VALUES ('2', '学习 MySQL', '菜鸟教程', '2017-04-12');
INSERT INTO `runoob_tbl` VALUES ('3', '学习 Java', 'RUNOOB.COM', '2015-05-01');
INSERT INTO `runoob_tbl` VALUES ('4', '学习 Python', 'RUNOOB.COM', '2016-03-06');
INSERT INTO `runoob_tbl` VALUES ('5', '学习 C', 'FK', '2017-04-05');
INSERT INTO `runoob_tbl` VALUES ('6', '测试null', 'null', null);


SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tcount_tbl
-- ----------------------------
DROP TABLE IF EXISTS `tcount_tbl`;
CREATE TABLE `tcount_tbl` (
  `runoob_author` varchar(255) NOT NULL DEFAULT '',
  `runoob_count` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tcount_tbl
-- ----------------------------
INSERT INTO `tcount_tbl` VALUES ('菜鸟教程', '10');
INSERT INTO `tcount_tbl` VALUES ('RUNOOB.COM ', '20');
INSERT INTO `tcount_tbl` VALUES ('Google', '22');

一:INNER JOIN

也可以不用innser join ,用where 也可以代替

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;

二: LEFT JOIN

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

三:RIGHT JOIN(跟左连接一样)

四:UNION

UNION 不能用于列出两个表中所有的 runoob_author.UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

(SELECT runoob_author FROM runoob_tbl) UNION ALL (SELECT runoob_author FROM tcount_tbl)

五:Group by

1.Group by分组

(SELECT runoob_author FROM runoob_tbl) UNION ALL (SELECT runoob_author FROM tcount_tbl)

2.group by 分组统计(SUM(id))

/*按照名字分组,然后再统计有这个名字的有多少个*/
SELECT runoob_author,count(*) FROM runoob_tbl GROUP BY runoob_author;

3.分组合计(WITH ROLLUP的使用)

/*按照名字分组,然后再统计有这个名字的id 加起来*/
SELECT runoob_author,SUM(runoob_id) FROM runoob_tbl GROUP BY runoob_author;