mysql左连接右连接内连接外连接区别

 
DROP TABLE IF EXISTS `clas`;
CREATE TABLE `clas` (
  `id` tinyint(3) NOT NULL AUTO_INCREMENT,
  `classname` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Records of clas
-- ----------------------------
INSERT INTO `clas` VALUES ('1', '班级1');
INSERT INTO `clas` VALUES ('2', '班级2');
INSERT INTO `clas` VALUES ('3', '班级3');
INSERT INTO `clas` VALUES ('4', '班级4');
INSERT INTO `clas` VALUES ('5', '班级5');
INSERT INTO `clas` VALUES ('6', '班级6');
 
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  `cid` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '小李', '1');
INSERT INTO `user` VALUES ('2', '小张', '2');
INSERT INTO `user` VALUES ('3', '小王', '1');
INSERT INTO `user` VALUES ('4', '小赵', '3');
INSERT INTO `user` VALUES ('5', '小孙', '4');
INSERT INTO `user` VALUES ('6', '小丁', '3');
INSERT INTO `user` VALUES ('7', '小钱', '1');
INSERT INTO `user` VALUES ('8', '小周', '3');
insert into user values (9,'小黑',7);

select a.classname 班级,count(b.id) 人数 from clas a left join user b on a.id=b.cid group by b.cid;

select a.classname 班级,count(b.id) 人数 from clas a left join user b on a.id=b.cid group by b.cid having 人数>2;<br>
select cid 班级id,count(id) 人数 from user group by cid having 人数>2;

select a.classname 班级,count(b.id) as 人数  from clas a left join user b on a.id=b.cid group by b.cid having 人数=0;

select a.name,b.classname from user a left join clas b on a.cid=b.id;

select a.name,b.classname from user a right join clas b on a.cid=b.id;

select a.name,b.classname from user a inner join clas b on a.cid=b.id;

select a.name,b.classname from user a left join clas b on a.cid=b.id union select a.name,b.classname from user a right join clas b on a.cid=b.id;