1、并集: a和b所有元素的集合
-- 全连接 ====并集: a和b所有元素的集合
SELECT a.id id , a.user_name ausername, a.score ascore , b.user_name busername ,b.score bscore FROM cj a LEFT JOIN cj_copy b on a.id=b.id
UNION
SELECT a.id id , a.user_name ausername, a.score ascore , b.user_name busername ,b.score bscore FROM cj a RIGHT JOIN cj_copy b on a.id= b.id
2、交集: 属于a又属于b的所有元素构成的集合
-- 等值连接 ====交集: 属于a又属于b的所有元素构成的集合
SELECT a.id id , a.user_name ausername, a.score ascore , b.user_name busername ,b.score bscore FROM cj a INNER JOIN cj_copy b on a.id= b.id
3、差集:所有属于a且不属于b的元素构成的集合
-- 非等值连接 ====差集:所有属于a且不属于b的元素构成的集合
SELECT a.id id , a.user_name ausername, a.score ascore , b.user_name busername ,b.score bscore FROM cj a LEFT JOIN cj_copy b on a.id= b.id WHERE b.id IS NULL
4、行转列
--行转列
SELECT MAX(id),class_name,GROUP_CONCAT(user_name) from cj_copy GROUP BY class_name
5、列转行
SELECT a.id, a.user_name, a.class_name,
substring_index( substring_index(a.score,',', b.help_topic_id + 1),',' ,- 1) AS shareholder
FROM
cj_copy a
JOIN mysql.help_topic b ON b.help_topic_id
length(a.score)- length(REPLACE (a.score, ',', '')) + 1)
数据表
cj表)
SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure forcj-- ----------------------------DROP TABLE IF EXISTS `cj`;
CREATE TABLE `cj` (
`id`int(10) NOT NULL AUTO_INCREMENT,
`user_name` varchar(10) DEFAULT NULL,
`class_name` varchar(10) DEFAULT NULL,
`score` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ----------------------------
--Records of cj-- ----------------------------INSERT INTO `cj` VALUES ('1', 'yang', '1', '90');
INSERT INTO `cj` VALUES ('4', 'zhou', '2', '90');
INSERT INTO `cj` VALUES ('5', 'qin', '2', '80');
cj_copy表)
SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure forcj_copy-- ----------------------------DROP TABLE IF EXISTS `cj_copy`;
CREATE TABLE `cj_copy` (
`id`int(10) NOT NULL AUTO_INCREMENT,
`user_name` varchar(10) DEFAULT NULL,
`class_name` varchar(10) DEFAULT NULL,
`score` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ----------------------------
--Records of cj_copy-- ----------------------------INSERT INTO `cj_copy` VALUES ('2', 'wen', '1', '80,70,60');
INSERT INTO `cj_copy` VALUES ('3', 'jie', '1', '70');
INSERT INTO `cj_copy` VALUES ('5', 'qin', '2', '80');
INSERT INTO `cj_copy` VALUES ('6', 'qin', '3', '90');