case when语句

SELECT *,CASE WHEN age > 18 THEN '成年人' ELSE '未成年人' END AS '身份' FROM student

需要注意的是，CASE WHEN 每个条件对应的数据类型必须是一致的，否则会报错。其它，在实际开发中还可以把参数传入insert、update、delete语句

insert into student(name,age) values('小周',case when #{A}='小周' then 18 end);

update student set age = (case when #{A} = 1 then 18 when #{A} = 2 then 19 else 20 end)

delete student where age = (case when #{A} = '18' then '18' when #{A} = '19' then '19' end);

行转列

SELECT name,

max(CASE WHEN course='语文' THEN score ELSE 0 END ) AS '语文',

max(CASE WHEN course='数学' THEN score ELSE 0 END )AS '数学'

FROM score GROUP BY name

数据批量更新

mysql

UPDATE score a inner join score_new b ON a.id = b.id set a.score= b.score

sqlserver

UPDATE score a set a.score= b.score from score_new b where a.id = b.id

oracle用以下格式：

UPDATE A SET (A1, A2, A3) = (SELECT B1, B2, B3 FROM B WHERE A.ID = B.ID)

插入或者更新

INSERT INTO `score` VALUES (1, '小明', '语文', 95) on DUPLICATE KEY UPDATE score=96

根据主键判断有记录存在时，只更新on后面的字段

动态SQL

delimiter //
create procedure insert(in name varchar(250),in course varchar(250),in score INT)
BEGIN  
    SET @sqlStmt = CONCAT('INSERT INTO score VALUES (1, ',name,', ',course,', ',score,');');
    PREPARE stmt FROM @sqlStmt;
    EXECUTE stmt;#执行SQL
    DEALLOCATE PREPARE stmt; #释放连接
END;
//
delimiter ;

索引优化

应该建立索引的情况

1.在经常用在连接的列上创建索引，可以加快连接的速度；
2.在经常使用在Where子句中的列上面创建索引，加快条件的判断速度；
3.为经常出现在关键字order by、group by、distinct后面的字段，建立索引。
4.索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

应该避免建立索引的情况
1.频繁进行数据操作的表,尤其插入操作比较多的表，不要建立太多的索引；
2.无用的索引要及时删除，避免对执行计划造成负面影响；

索引失效的情况

有牛人总结为七字口诀：“模型数空运最快”，我直接拿来分享了

模：使用like进行模糊查询的时候，以百分号%开头的，索引就会失效。

型：代表数据类型，数据类型错误了，索引也会失效。

数：函数，对索引的字段使用内部函数的，索引也会失效。这种情况，应该建立基于函数的索引。

空：null，索引不存储空值，如果没有声明nou null，数据库会认为有可能存在空值，也不会按照索引进行计算。

运：运算，对索引列进行加减乘除等运算的，会造成索引失效。

最：复合索引中索引列的顺序非常重要，如果不是按照索引列最左列开始查找，则无法使用索引。

快：全表扫描最快，如果数据库认为全表扫描更快的时候，它就不会使用索引。

mysqldump导出

1.导出整个数据库

mysqldump -u 用户名 -p 数据库名 > 导出的文件名

2.导出一个表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

3.导入

mysql>source d:/dbname.sql

说明：大批量数据情况下推荐使用mysqldump，导入会很快

刚踩了一个坑

有两个字段大小判断的SQL在命令窗口下执行没有问题，但是把SQL生成视图就有问题了。经过排查发现，原因是两个比较字段的字符集比一致（一个utf8mb4,另一个uft8），在生成视图时mysql会把字符集强行转为一致，这样就出现了问题。

上面的问题我在测试环境没有复现，但在生产环境下确实是修改完成字符集就解决问题了。我猜想与数据量有关系，因为生产环境那张表的数据量还是挺大的。有知道原因的小伙伴请留言，谢谢！

大家在用mysql创建表时，尽量选择utf8mb4字符集，在开发过程中还要确认每个字段的字符集情况。不然有可能出现不可描述的情况！

每天一个小知识，每天进步一点点！！！[加油][加油][加油]