1. 常用函数
(1) GROUP_CONCAT(字段 order by 字段)
eg: 对应多个手机号时, 可查出多个手机号, 在一个字段中返回
2. 多个计算结果合并为一条SQL
SELECT COUNT(CASE WHEN `status`=0 THEN 1 END) as todo,
COUNT(CASE WHEN `status`=1 THEN 1 END) as doing,
COUNT(CASE WHEN `status`=2 THEN 1 END) as finsh
3. ANY_VALUE(column_name)
MySQL5.7之后,sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态,在此模式下, target list中的值要么是来自于聚合函数(sum、avg、max等)的结果,要么是来自于group by list中的表达式的值。网上有很多通过修sql_mode的方式来解决此问题。但除此方法,MySQL 也提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝
5. information_schema 数据表说明 参考博文
6. datetime和timestamp区别
datetime | timestamp | |
占用空间 | 8字节 | 4字节 |
表示范围 | '1000-01-01 00:00:00' to '9999-12-31 23:59:59' | '1970-01-01 00:00:01' to '2038-01-19 03:14:07' |
时区 | 不会进行时区的检索 | 自动检索当前时区并进行转换 |
7. 循环执行SQL
CREATE PROCEDURE demo()
begin
set @i=1;
while @i<=10
do
INSERT into department VALUES(@i,CONCAT("部门",@i),CURRENT_TIMESTAMP);
set @i=@i+1;
end while;
END
8. 存储过程批量更新表字段脚本
CREATE PROCEDURE updateColumn()
BEGIN
IF NOT EXISTS(SELECT column_name FROM information_schema.columns WHERE table_name="employee" AND column_name="a")
THEN ALTER TABLE employee ADD a VARCHAR(20);
END IF;
IF EXISTS(SELECT column_name FROM information_schema.columns WHERE table_name="employee" AND column_name="b")
THEN ALTER TABLE employee MODIFY COLUMN b VARCHAR(200);
END IF;
IF EXISTS(SELECT column_name FROM information_schema.columns WHERE table_name="employee" AND column_name="b")
THEN ALTER TABLE employee CHANGE COLUMN b b2 VARCHAR(200);
END IF;
IF EXISTS(SELECT column_name FROM information_schema.columns WHERE table_name="employee" AND column_name="b2")
THEN ALTER TABLE employee DROP COLUMN b2;
END IF;
END;
CALL updateColumn();
DROP PROCEDURE updateColumn;