UPDATE tf_user t1 INNER JOIN tf_image_user t2 ON t1.nickname= t2.imageName
SET t1.portraitUrl=t2.imgaeUrl
WHERE t1.nickname= t2.imageName
2、对字段进行排序
(SELECT c.name,c.nameFirstLetter,0 AS otype, SUBSTRING(c.nameFirstLetter,1,1) AS nfl FROM tf_circle c
WHERE 1=1
AND SUBSTRING(c.nameFirstLetter,1,1) BETWEEN 'a' AND 'Z'
)
UNION
(SELECT c.name,c.nameFirstLetter,1 AS otype, SUBSTRING(c.nameFirstLetter,1,1) AS nfl FROM tf_circle c
WHERE 1=1
AND SUBSTRING(c.nameFirstLetter,1,1) NOT BETWEEN 'a' AND 'Z'
)
ORDER BY otype,nfl
WHERE 1=1
AND SUBSTRING(c.nameFirstLetter,1,1) BETWEEN 'a' AND 'Z'
)
UNION
(SELECT c.name,c.nameFirstLetter,1 AS otype, SUBSTRING(c.nameFirstLetter,1,1) AS nfl FROM tf_circle c
WHERE 1=1
AND SUBSTRING(c.nameFirstLetter,1,1) NOT BETWEEN 'a' AND 'Z'
)
ORDER BY otype,nfl
IFNULL(expr1,expr2)
如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值，取决于它被使 用的上下文环境。
使用IS NULL和IS NOT NULL操作符：mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
请注意在MySQL中，0或 NULL意味着假,而其它值意味着真。布尔运算的默认真值是1。根据以上的NULL值操作结果，最终使用此种方式解决：SELECT o.oid,o.moneyreceipt,o.moneyget,o.thecurrency,o.status FROM qorder o WHERE IFNULL(o.status,'pending') <>'cancel'
IF()返回一个数字或字符串值，取决于它被使用的上下文。
mysql> select IF(1>2,2,3); -> 3
mysql> select IF(1<2,'yes','no'); -> 'yes'