常用SQL数据类型和Java数据度类型的对应关系

Java与数据库对应的类型尽量使用类

其余对应关系就没必要做笔记了

对于Java日期类LocalDateTime是java8提供的全新日期和时间api类，而且相比Date类线程更安全

Date有的LocalDateTime都有，Date没有的LocalDateTime也有

对于浮点数float和double都是浮点型，而decimal是定点型

float和double求SUM的结果都是不精确的，只有decimal求SUM得到的是精准数值，decimal 类型是适合财务和货币计算的128位数据类型。

delete和truncate语句的使用

假设有一种test表，字段id为主键 自增长

使用delete和truncate都可以清空表的数据

-- 不会影响自增长 delete可以是table和view 可以回滚
delete from test

-- 自增长会清零 truncate只能对table 不能回滚 速度比delete快
truncate table test

具体差别

• delete语句
  1. delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。
  2. delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。
  3. delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。
• truncate语句
  1. truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
  2. truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。
  3. 对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
  4. truncatetable不能用于参与了索引视图的表。

参考链接 https://www.cnblogs.com/zhizhao/p/7825469.html

CURRENT_TIMESTAMP的使用

用于作为创建时间的默认值

create table test(
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    -- ...
    createtime datetime DEFAULT CURRENT_TIMESTAMP -- 在插入本表数据时会根据当前时间设定
)

大批量数据插入(存储过程)

有一个test表，结构如下

往此表里大批量插入数据，用到函数和存储过程

定义两个函数，一个随机生成字符串作为name，一个随机生成数字作为num

定义一个存储过程，调用上面的两个函数

存储过程和函数最大的区别就是，函数有返回值，存储过程没返回值

-- 从哪里开始  以$$表示结束
delimiter $$
-- 创建随机生成字符串的函数
create function rand_string(n int) returns VARCHAR(255)
BEGIN
-- 定义变量 设置默认值
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
-- 随机生成字符串
set return_str=concat(return_str,SUBSTRING(chars_str,floor(1+RAND()*52),1));
set i = i+1;
end while;
RETURN  return_str;
-- 这个函数写完了
end $$

delimiter $$
-- 创建随机生成数字的函数
CREATE FUNCTION rand_num() RETURNS int(5)
begin
DECLARE i int DEFAULT 0;
set i = floor(100+rand()*10);
return i;
end $$


delimiter $$
-- 创建存储过程 两个入参
create procedure insert_test(in start int(10),in max_num int(10))
begin 
 declare i int default 0;
 -- 设置自动提交为0 不关闭就是写一次就一个commit浪费性能
 set autocommit=0;
 repeat
 -- 调用函数返回随机字符串和随机数
 insert into test(id,name,num) values(start+i,rand_string(6),rand_num());
 set i = i+1;
 until i = max_num
 end repeat;
 commit;
end $$

-- 以 ; 表示结束
delimiter ;
-- 调用存储过程 插入一万条数据
call insert_test(1,10000);

查询表结构

如果要编写代码生成器，需要根据表的列名来遍历

SELECT
COLUMN_NAME 属性名,
COLUMN_TYPE 数据类型,
COLUMN_COMMENT 说明
FROM
INFORMATION_SCHEMA.COLUMNS
where
-- wx 为数据库名称，到时候只需要修改成你要导出表结构的数据库即可
table_schema = 'wx'
AND
-- article为表名，到时候换成你要导出的表的名称
-- 如果不写的话，默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了，所以还是建议写上要导出的名名称
table_name = 'article'

查询案例

更详细的查询

-- chapter为表名
show full columns from chapter;

查询某个字段为最大值的整条数据

数据有这么多，但是我只想根据trigger_last_time的最大值获取每个task_id唯一的数据

使用max聚合函数

select task_id, max(trigger_last_time) last_exec from discovery_job_info
where task_id is not null
group by task_id

但是这样只能查询每一个task_id对应最大的trigger_last_time,查不到其他字段信息
因为max是聚合函数，所以除了max函数里的字段外，select的字段只能写group by后的字段
像这样就会报错

select task_id, max(trigger_last_time) last_exec,status from discovery_job_info
where task_id is not null
group by task_id

所以需要再包一层，使用in关键字

select task_id,trigger_last_time last_exec,status from discovery_job_info
where (task_id,trigger_last_time) in
(
    select task_id, max(trigger_last_time) last_exec from discovery_job_info
    where task_id is not null
    group by task_id
)

查询当前日期之前的x天/年/月/日/时/分/秒

date_sub函数表示之前
date_add函数表示之后
以月为例

-- 查询当前月和前一月 用union all整合
SELECT DATE_FORMAT(DATE(NOW()),"%Y-%m") as time
union all
SELECT DATE_FORMAT(date_sub(DATE(NOW()), interval 1 month),"%Y-%m") as time

DATE_FORMAT函数用来格式化日期
interval关键字后的日期单位参数可选 YEAR , MONTH , DAY , HOUR , MINUTE , SECOND