mysql字符串拼接函数CONCAT()、CONCAT_WS()、GROUP_CONCAT()的使用

建表插数据

CREATE TABLE `score_info`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuName` VARCHAR(22) DEFAULT NULL,
`course` VARCHAR(22) DEFAULT NULL,
`score` INT(11) DEFAULT NULL,
PRIMARY KEY(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

INSERT INTO `score_info` VALUES ('1','张三','语文','87');
INSERT INTO `score_info` VALUES ('2','张三','数学','91');
INSERT INTO `score_info` VALUES ('3','张三','英语','90');
INSERT INTO `score_info` VALUES ('4','李四','语文','95');
INSERT INTO `score_info` VALUES ('5','李四','数学','87');
INSERT INTO `score_info` VALUES ('6','李四','英语','93');
INSERT INTO `score_info` VALUES ('7','王五','语文','83');
INSERT INTO `score_info` VALUES ('8','王五','数学','78');
INSERT INTO `score_info` VALUES ('9','王五','英语','98');

 

CONCAT(str1,str2,…)

SELECT CONCAT(student_name,course,score) FROM score_info;

查询结果：

如果需要添加分割符，直接在中间添加符号的字符串

SELECT CONCAT(student_name,",",course,"#",score,"@") FROM score_info;

查询结果：

CONCAT_WS(separator,str1,str2,…)

与CONCAT()函数功能相同只是把分割符号放在方法里面的第一个参数

SELECT CONCAT_WS("@%",student_name,course,score) FROM score_info;

查询结果：

GROUP_CONCAT(expr)

SELECT GROUP_CONCAT(score),student_name FROM score_info GROUP BY student_name

查询结果：