mysql笔记

　　

query_insert = "insert into pospal_kf.question (question, answer, status, createtime, updatetime) VALUES ('%s', '%s', 0,now(), now())" %(question,answer)

%s要加引号 

UPDATE pospal_kf.similarquestion SET questionid=%d WHERE id=%d

simi_id = int(row['相似问题ID'])
pandas读取id会读取为float,需要转化成Int

答案需要strip掉后面的换行

answer = row['答案'].strip()

SELECT * FROM pospal_kf.similarquestion WHERE question like '%会员充值要怎么操作%';
SELECT * FROM pospal_kf.question WHERE id= (SELECT questionid FROM pospal_kf.similarquestion WHERE question like '%会员充值要怎么操作%')

 

INSERT INTO pospal_kf.question (question, answer, status, createtime, updatetime) VALUES ("wenti", "daan", 0, now(), now())

其中，now要加括号才行now()

UPDATE dz_join SET close='1' ,pay='1', send='1' WHERE cid='1'

 

SELECT a.questionid,a.question From similarquestion a INNER JOIN question b ON a.questionid=b.id AND b.status=0

交集  

 

数据库里出现很多\r\n和空格

一轮一轮做，用trim比较安全

UPDATE question SET answer=trim(answer);
UPDATE question SET answer=trim("\r\n" from answer);
UPDATE question SET answer=trim(answer);
UPDATE question SET answer=trim("\r\n" from answer);

 

 

select id as 标准问题ID, question as 标准问题,"-" as 相似问题, answer as 答案 from question where question like "%进货单%"
Union All
select b.id as 标准问题ID, b.question as 标准问题, a.question as 相似问题, b.answer as 答案
from similarquestion a inner join question b on a.questionid=b.id
where a.question like "%进货单%"

 

查两轮，把两轮的结果合并，使用union 关键字，注意，union是去除两个表里，重复的，而union all是不去除重复的