MySQL 数据库开发、设计常用（持续更新）
The more a man learns ,the more he knows his ignorance.
学然后知不足。
前言
在开发中经常遇到一些简单的但是又突然想不起来或者记不清楚的东西，然后就去查资料，所以就想自己把一些常用的东西记录下来，以后查资料查自己的岂不是更快？说 do 就 do，go！go！go！
ps：如果能帮到大家一些固然是好的，但是不喜勿喷，谢谢！（本篇是关于数据库的，主要是 MySQL，排序随机的，主要记录常用的，偏基础简单的）
数据中的数据类型对应的 Java 数据类型
| 数据库类型 | Java 类型 | 栗子🌰 |
| INT | Integer | 主键、记录数字类型的数据等 |
| TINYINT | Integer | 分类（宽度为4），或者true、false（宽度为1的时候） |
| BIGINT | Long | 主键、INT不够用的时候 |
| CHAR | Sting | 固定长度的字符（性别） |
| VARCHAR | String | 用的最多的，一般的 string 类型都用它，宽度看需求 |
| TEXT | String | 长文本 |
| DECIMAL | BigDecimal | 金钱 money |
| DATETIME | Date | 时间、日期 |
不局限于这几种类型，而且对应关系也不是唯一的，比如无符号和有符号，数据库选择无符号的时候可能 Java 类型需要更高一级，如数据库的 INT 为无符号的时候就会超出 Integer 的范围从而需要选择 Long 类型来映射，具体详情可以在网上查阅相关资料。
创建表格的模板
1 CREATE TABLE `sys_statistical_data` ( 2 `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id（自增）', 3 `data` INT(11) NOT NULL COMMENT '数据', 4 `type` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '类型（0-每天登陆用户量|1-每月登陆的用户量）', 5 `time` datetime NOT NULL COMMENT '数据对应的时间', 6 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', # 默认创建时间为插入操作的当前时间 7 `update_time` datetime COMMENT '修改时间', 8 PRIMARY KEY (`id`) 9 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='数据统计表';
COLLATE：字符集，推荐 utf8_general_ci
SQL 对数据的操作
查找某个时间字段记录在指定日期（day）的数据
1 select COUNT(`id`) from sys_user where to_days(`user_lastlogin_time`) = to_days(NOW()); # 查询最后登陆时间日期与今天相同的数据（今天登录的总数）
查询上个月某个字段的总和
SELECT SUM(`data`) FROM sys_statistical_data WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( `time`, '%Y%m' ) ) =1 # 查询上个月 data 字段的总和，用现在的年月份减去数据记录中的年月份等于1来筛选出上个月的数据
1 SELECT DATEDIFF(NOW(),(SELECT user_create_time FROM sys_user WHERE id = "1209015972111863808"));
1 SELECT DISTINCT course_name FROM sys_course WHERE school_id = '1209017944516874241'
1 SELECT IFNULL((SELECT 1 FROM user WHERE cellphone = #{cellphone} LIMIT 1),0); # 存在返回1，不存在返回0，比查询实体的效率高，mybatis mapper中的写法
查询本月数据
select * from sys_statistical_data WHERE DATE_FORMAT( `time`, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' ) AND type = 0; # 本月数据，time 是字段名
查询上月数据
select * from sys_statistical_data WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( `time`, '%Y%m' ) ) =1; # 上月的数据
UNION 和 UNION ALL 中的 ORDER BY 会报错，需要把单次查询用（）括起来
1 (SELECT twelve.time AS `time`,IFNULL(ssd.`data`,0) AS `data` FROM (SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `time` 2 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `time` 3 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `time` 4 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `time` 5 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `time` 6 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `time` 7 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `time` 8 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `time` 9 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `time` 10 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `time` 11 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `time` 12 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 12 MONTH), '%Y-%m') AS `time` ) AS `twelve` 13 LEFT JOIN (SELECT distinct `data`,DATE_FORMAT(`time`, '%Y-%m') AS `time` FROM sys_statistical_data WHERE `type` = 7 GROUP BY `time`) AS ssd ON twelve.`time` = ssd.`time` ORDER BY `time`) 14 UNION ALL 15 (SELECT twelve.time AS `time`,IFNULL(ssd.`data`,0) AS `data` FROM (SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `time` 16 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `time` 17 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `time` 18 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `time` 19 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `time` 20 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `time` 21 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `time` 22 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `time` 23 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `time` 24 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `time` 25 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `time` 26 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 12 MONTH), '%Y-%m') AS `time` ORDER BY `time`) AS `twelve` 27 LEFT JOIN (SELECT distinct `data`,DATE_FORMAT(`time`, '%Y-%m') AS `time` FROM sys_statistical_data WHERE `type` = 10 GROUP BY `time`) AS ssd ON twelve.`time` = ssd.`time` ORDER BY `time`)
UNION 和 UNION ALL 导致子查询总的 ORDER BY 不起作用，原因是 UNION 中的 ORDER BY 需要和 LIMIT 一起使用
1 (SELECT twelve.time AS `time`,IFNULL(ssd.`data`,0) AS `data` FROM (SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `time` 2 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `time` 3 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `time` 4 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `time` 5 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `time` 6 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `time` 7 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `time` 8 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `time` 9 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `time` 10 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `time` 11 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `time` 12 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 12 MONTH), '%Y-%m') AS `time` ) AS `twelve` 13 LEFT JOIN (SELECT distinct `data`,DATE_FORMAT(`time`, '%Y-%m') AS `time` FROM sys_statistical_data WHERE `type` = 7 GROUP BY `time`) AS ssd ON twelve.`time` = ssd.`time` ORDER BY `time` LIMIT 12) 14 UNION ALL 15 (SELECT twelve.time AS `time`,IFNULL(ssd.`data`,0) AS `data` FROM (SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `time` 16 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `time` 17 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `time` 18 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `time` 19 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `time` 20 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `time` 21 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `time` 22 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `time` 23 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `time` 24 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `time` 25 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `time` 26 UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 12 MONTH), '%Y-%m') AS `time`) AS `twelve` 27 LEFT JOIN (SELECT distinct `data`,DATE_FORMAT(`time`, '%Y-%m') AS `time` FROM sys_statistical_data WHERE `type` = 10 GROUP BY `time`) AS ssd ON twelve.`time` = ssd.`time` ORDER BY `time` LIMIT 12)
欲しいのはあなただけです。
