mysql 相关

insert ... select ...

INSERT INTO SWR_TRANSFERIN_FAIL (TXID, APP_NO, CLM_NO, STATUS, CREATE_TIME, UPDATE_TIME)
select TXID, APP_NO, null, STATUS, CREATE_TIME, UPDATE_TIME
from SWR_TRANSFERIN_FAIL03;

1. 配置连接地址

jdbc:mysql://192.168.181.132:3306/test_mybatis?characterEncoding=utf-8&useUnicode=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true　　#允许多条 sql 一起执行

2. mysql 查字段说明：

SELECT
    t.TABLE_NAME AS '表名',
    t.COLUMN_NAME AS '字段名称',
  t.COLUMN_TYPE AS '数据类型',
  CASE IFNULL(t.COLUMN_DEFAULT,'Null') 
        WHEN '' THEN '空字符串' 
        WHEN 'Null' THEN 'NULL' 
        ELSE t.COLUMN_DEFAULT END  AS '默认值',
    CASE t.IS_NULLABLE WHEN 'YES' THEN '是' ELSE '否' END AS '是否可空',   
    t.COLUMN_COMMENT AS '字段说明'
FROM information_schema.COLUMNS t 
WHERE t.TABLE_SCHEMA='dev_core'  
--  AND t.TABLE_NAME='web_ply_base' 
-- and t.COLUMN_name = 't_edr_end_tm' 
AND t.COLUMN_COMMENT like '%risk%'

3. 批量更新 - update inner join

UPDATE A as aa
INNER JOIN B as bb ON bb.bid = aa.bid
AND bb.sid=aa.sid
AND bb.grid=aa.grid
SET aa.pv=bb.pv,
    aa.uv=bb.uv,
    aa.pay_amount=bb.pay_amount
WHERE aa.dtime=?

4. 时间格式到毫秒

CREATE TABLE `swr_busi_sql_log` (
  `id` varchar(50) NOT NULL COMMENT '主键',
  `txid` varchar(50) NOT NULL COMMENT '流水号',
  `service` varchar(50) DEFAULT NULL,
  `runsql` longtext COMMENT '执行SQL记录',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_time2` timestamp(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP(2),
  PRIMARY KEY (`id`),
  KEY `txid` (`txid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

5. ONLY_FULL_GROUP_BY

6. 分页查询

7. SQL 执行顺序

8. 访问权限管理

9. 流程函数

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(11) NOT NULL COMMENT 'ID',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `math` int(11) DEFAULT NULL COMMENT '数学',
  `english` int(11) DEFAULT NULL COMMENT '英语',
  `chinese` int(11) DEFAULT NULL COMMENT '语文',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学员成绩表';

select * from score;

select id,name,
        case 
                when math >= 60 then '及格' 
                when math >= 80 then '优秀'
                ELSE '不及格'
        end 'math',
        case 
                when english >= 60 then '及格' 
                when english >= 80 then '优秀'
                ELSE '不及格'
        end 'english',
        case 
                when chinese >= 60 then '及格' 
                when chinese >= 80 then '优秀'
                ELSE '不及格'
        end 'chinese'
from score;