持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第31天，点击查看活动详情

1.函数

1.1case when then函数

--简单case函数
case sex
  when '1' then '男'
  when '2' then '女’
  else '其他' end
--case搜索函数
case when sex = '1' then '男'
     when sex = '2' then '女'
     else '其他' end

1.2行变列方法

mysql行转列的方法：

1、利用“SUM(CASE 表名 WHEN 字段名 THEN score ELSE 0 END) as 字段名”操作转换；

2、利用“SUM(IF(表名=字段名,score,0)) as 字段名”操作转换。

建表语句：

DROP TABLE IF EXISTS tb_score;
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；

mysql> select * from tb_score
    -> ;
+----+--------+---------+-------+
| id | userid | subject | score |
+----+--------+---------+-------+
|  1 | 001    | 语文    |    90 |
|  2 | 001    | 数学    |    92 |
|  3 | 001    | 英语    |    80 |
|  4 | 002    | 语文    |    88 |
|  5 | 002    | 数学    |    90 |
|  6 | 002    | 英语    |  75.5 |
|  7 | 003    | 语文    |    70 |
|  8 | 003    | 数学    |    85 |
|  9 | 003    | 英语    |    90 |
| 10 | 003    | 政治    |    82 |
+----+--------+---------+-------+
10 rows in set (0.00 sec)

1.2.1 使用case...when....then实现行转列

其实就是将原来的subject字段的多行内容选出来，作为结果集中的不同列，并根据主键userid进行分组显示对应的score。在1.2.3中用if也是这个思路。

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

之前的结果：

执行之后行变列的结果：

企业微信截图_16532969936908.png

1.2.2 MySQL 默认配置only_full_group

大家可能会很好奇，上面为什么还要加上SUM函数呢，这个问题问得好。高版本的mysql分组的列必须是唯一索引才行。

ps：如果执行

SELECT userid,CASE `subject` WHEN '语文' THEN score ELSE 0 END as '语文' FROM tb_score group by userid;

会报如下错误：

这是因为由于默认的 MySQL 配置中 sql_mode 配置了 only_full_group，需要 GROUP BY 中包含所有在 SELECT 中出现的字段。

only_full_group_by说明： only_full_group_by ：使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行，即改为如下就可以：

 SELECT userid, SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文' FROM tb_score group by userid;

only_full_group_by的配置看输入select @@sql_mode; 进行查询

select @@sql_mode;

这个配置文件在my.cnf中，通过修改配置文件也可以解决如上的错误：

$ vim /usr/local/etc/my.cnf
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1.2.3通过if实现行转列

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 '政治' 
FROM tb_score 
GROUP BY userid;

结果与1.2.1中一样，这里就不赘述了。

1.3 explain 查看select执行时所用的索引

通过 explain 命令获取 select 语句的执行计划，通过 explain 我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。

建表及添加索引：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `upTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pk_users_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

查看表的索引：

# 查看索引
show keys from users

通过explain查看select的执行计划：

 explain select * from users where name = 'aaa';

对其中字段的说明：

type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
- ALL: 扫描全表
- index: 扫描全部索引树
- range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
- ref: 使用非唯一索引或非唯一索引前缀进行的查找
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
- const, system: 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。system是const类型的特例，当查询的表只有一行的情况下，使用system。
possible_keys: 表示查询时可能使用的索引。
key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。
key_len列，索引长度。
rows列，扫描行数。估算的找到所需的记录所需要读取的行数。
extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。

上面查询name，之所以用到了索引，是因为建表的时候给name这个字段加了索引。

下面来一个不走索引的查询：

 explain select * from users where password = 'aa';

可以看到possible_keys和key都为null，表示没有用到索引，并且type连接类型是ALL，扫描全表，这个效率可以说是非常低了。

行变列的方法以及一些函数