MYSQL SQL

 1、相关度查询：

SELECT  a.`id`AS `uuid`, b.`tid`,b.`title` ,
( IF(LOCATE(',1227,',  b.`tid`), 1, 0) + IF(LOCATE(',762,',  b.`tid`), 1, 0) + IF(LOCATE(',422,',  b.`tid`), 1, 0)  ) AS `score`
FROM `usermat` a  LEFT JOIN `mat` b ON a.`cid`= b.`id` 
WHERE ( ( b.`tid` LIKE '%,1227,%')  OR  ( b.`tid` LIKE '%,762,%')  OR  ( b.`tid` LIKE '%,422,%')) 
 AND  b.`id`!='14355'  ORDER BY `score`DESC,a.`id`DESC

 

2、修改密码

UPDATE user SET password=PASSWORD('123456') WHERE user='root';
FLUSH PRIVILEGES;

 

3、不存在时候插入

INSERT INTO `nb_topic` ( `topic_title`) SELECT '雅居乐' FROM DUAL WHERE NOT EXISTS(SELECT 'topic_title' FROM `nb_topic` WHERE `topic_title`='雅居乐');

 4、group

select b.*,a.num from (SELECT count(mid) as num, max(id) as max_id FROM `my_download_record` group by mid ORDER BY `id`) a left join `my_download_record` b  on a.max_id = b.id order by b.id desc

 5、多条更新

UPDATE user SET `name`= CASE 
    WHEN id='1' AND rank='2' THEN 'kate' 
    WHEN id='4' AND rank='3' THEN 'jim' 
    WHEN id='5' AND rank='7' THEN 'george' 
    ELSE `name` END,
`age`= CASE 
    WHEN id='1' AND rank='2' THEN '16'
    WHEN id='4' AND rank='3' THEN '18' 
    WHEN id='5' AND rank='7' THEN '20' 
    ELSE `age` END 
WHERE `id` IN (1,4,5)