-- 去掉字段修改时默认的当前时间
`back_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,-- 插入时显示当前的系统时间
原有的表字段的格式:`back_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '返回时间', -- 修改时默认显示当前的系统时间
ALTER TABLE call_call_info CHANGE back_time back_time timestamp NULL COMMENT '返回时间'; -- 通过CHANGE关键字去掉修改数据时,该字段默认显示当前的系统时间
-- update语句中嵌套 case when 语句
UPDATE call_call_info c
set c.back_time = (
case
when (select count(*) from transfer_history a where a.nid = c.nid and a.current_status != 0) >0 -- 作为条件
then (select a.start_time from transfer_history a where a.nid = c.nid and a.current_status = 0 AND operator !='-1' ORDER BY a.start_time desc LIMIT 1) -- 取出start_time的值,赋给back_time
else null end -- 如果没有值就赋为null
)
WHERE c.status=0
-- 将call_call_info 表的数据插入到call_call_over_info 表
INSERT INTO call_call_over_info (
repayment_id,
call_loan_nid,
user_id,
real_name,
cont_id_card
) SELECT
repayment_id,
call_loan_nid,
user_id,
real_name,
cont_id_card
FROM
call_call_info WHERE STATUS in ('2','4') AND repayment_id NOT in (SELECT repayment_id FROM call_call_info) -- 剔除条件,筛选出call_call_over_info 表已经存在的相同的repayment_id 条件
-- 修改表字段长度:alter TABLE call_loan_call_info modify cont_mobile varchar(16)
-- 拼接的用法:
SELECT NAME,concat('UPDATE SYS_USER SET employee_status= 0 WHERE USER_ID =',USER_ID,';') FROM SYS_USER WHERE USER_ID IN ('1','2','36','503','504','505')//sql拼接用法
超级管理员 UPDATE SYS_USER SET employee_status= 0 WHERE USER_ID =1;
管理员 UPDATE SYS_USER SET employee_status= 0 WHERE USER_ID =2;
-- str_to_date()函数;在mysql里面利用str_to_date()把字符串转换为日期。
select str_to_date('2008-4-2 15:3:28','%Y-%m-%d %H:%i:%s'); 同 DATE_FORMAT一样。
-- IFNULL(expr1,expr2)的用法:
假如expr1 不为 NULL,则 IFNULL() 的返回值为 expr1; 否则其返回值为expr2。IFNULL()的返回值是数字或是字符串
列如:ifnull(overdue_fee,0) overdue_fee不为null则返回overdue_fee,否则返回0.
建表加字段的时间设置
`update_time` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP //每次更新,更新时间自动修改
`create_time` timestamp NOT NULL DEFAULT NOW(),-- 默认创建时间是当前时间
overdue_amount >= #{pd.minAmount} //大于等于 overdue_amount <= #{pd.maxAmount} //小于等于