Mysql行转列 列转行

行转列
准备数据：

CREATE TABLE tb_score(
    id INT(11) NOT NULL auto_increment,
    userid VARCHAR(20) NOT NULL COMMENT '用户id',
    subject VARCHAR(20) COMMENT '科目',
    score DOUBLE COMMENT '成绩',
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO tb_score(userid,subject,score) VALUES ('001','语文',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','数学',92);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','英语',80);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','语文',88);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','数学',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','英语',75.5);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','语文',70);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','数学',85);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','英语',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);

查询数据表中的内容：

SELECT * FROM tb_score;

如果我们需要将 subject 字段的多行内容选出来，作为结果集中的不同列，并根据 userid 进行分组显示对应的score，怎么实现呢？
使用 case…when…then 进行行转列

SELECT userid,
  SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) AS '语文',
  SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) AS '数学',
  SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) AS '英语',
  SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) AS '政治' 
FROM tb_score 
GROUP BY userid;

注意点：SUM() 是为了能够使用 GROUP BY 根据 userid 进行分组，因为每一个 userid 对应的 subject=“语文” 的记录只有一条，所以 SUM() 的值就等于对应那一条记录的 score 的值。
使用 SUM(IF()) 生成列 + WITH ROLLUP + 子查询生成汇总行

SELECT IFNULL(userid,'total') AS userid,
  SUM(IF(`subject`='语文',score,0)) AS 语文,
  SUM(IF(`subject`='数学',score,0)) AS 数学,
  SUM(IF(`subject`='英语',score,0)) AS 英语,
  SUM(IF(`subject`='政治',score,0)) AS 政治,
  SUM(IF(`subject`='total',score,0)) AS total
FROM(
    SELECT userid,IFNULL(`subject`,'total') AS `subject`,SUM(score) AS score
    FROM tb_score
    GROUP BY userid,`subject`
    WITH ROLLUP
    HAVING userid IS NOT NULL
)AS A 
GROUP BY userid
WITH ROLLUP;

使用 SUM(IF()) 生成列 + UNION 生成汇总行

SELECT 
  userid,
  SUM(IF(`subject` = '语文', score, 0)) AS 语文,
  SUM(IF(`subject` = '数学', score, 0)) AS 数学,
  SUM(IF(`subject` = '英语', score, 0)) AS 英语,
  SUM(IF(`subject` = '政治', score, 0)) AS 政治,
  SUM(score) AS TOTAL 
FROM tb_score 
GROUP BY userid 
UNION
SELECT 
  'TOTAL',
  SUM(IF(`subject` = '语文', score, 0)) AS 语文,
  SUM(IF(`subject` = '数学', score, 0)) AS 数学,
  SUM(IF(`subject` = '英语', score, 0)) AS 英语,
  SUM(IF(`subject` = '政治', score, 0)) AS 政治,
  SUM(score) 
FROM tb_score

使用 SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行

SELECT 
  IFNULL(userid, 'TOTAL') AS userid,
  SUM(IF(`subject` = '语文', score, 0)) AS 语文,
  SUM(IF(`subject` = '数学', score, 0)) AS 数学,
  SUM(IF(`subject` = '英语', score, 0)) AS 英语,
  SUM(IF(`subject` = '政治', score, 0)) AS 政治,
  SUM(score) AS TOTAL 
FROM tb_score 
GROUP BY userid WITH ROLLUP ;

使用 group_concat() 合并字段显示

SELECT 
  userid,
  GROUP_CONCAT(`subject`, ":", score) AS 成绩 
FROM
  tb_score 
GROUP BY userid

列转行
准备数据：

CREATE TABLE tb_score1(
    id INT(11) NOT NULL auto_increment,
    userid VARCHAR(20) NOT NULL COMMENT '用户id',
    cn_score DOUBLE COMMENT '语文成绩',
    math_score DOUBLE COMMENT '数学成绩',
    en_score DOUBLE COMMENT '英语成绩',
    po_score DOUBLE COMMENT '政治成绩',
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('001',90,92,80,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('002',88,90,75.5,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('003',70,85,90,82);

查询数据表中的内容：

SELECT * FROM tb_score1;

如果我们需要将 userid 的每个科目分数分散成一条记录显示出来，怎么实现呢？

SELECT userid,'语文' AS course,cn_score AS score FROM tb_score1
UNION ALL
SELECT userid,'数学' AS course,math_score AS score FROM tb_score1
UNION ALL
SELECT userid,'英语' AS course,en_score AS score FROM tb_score1
UNION ALL
SELECT userid,'政治' AS course,po_score AS score FROM tb_score1
ORDER BY userid

这里将每个 userid 对应的多个科目的成绩查出来，通过 UNION ALL 将结果集加起来，达到上图的效果。

获取数据库表中的字段
1.获取指定字段

select COLUMN_NAME,COLUMN_COMMENT from information_schema.COLUMNS 
where table_name = 'edc_database';

2.获取所有

select * from information_schema.COLUMNS 
where table_name = 'edc_database';

3. 指定表名和数据库

SELECT f.* FROM  (  SELECT t.ORDINAL_POSITION as ID, t.COLUMN_NAME as column_name, left(t.IS_NULLABLE,1) as nullable, t.DATA_TYPE as data_type, t.CHARACTER_MAXIMUM_LENGTH as data_length  ,t.NUMERIC_SCALE as scale, t.COLUMN_COMMENT as column_comment FROM INFORMATION_SCHEMA.COLUMNS as t WHERE table_name = '表名' and t.TABLE_SCHEMA = '库名' ) f