4 MySQL 语法技巧

[TOC]

1 where 语句中实现 case when

• case

WHERE
  pw='correct'
  AND CASE WHEN id<800 THEN success=1 ELSE TRUE END 
  AND YEAR(timestamp)=2011

goals：return success=1 (which can be TRUE or FALSE) in case id < 800, or always return TRUE otherwise.

• solution

where
    pw = 'correct'
    and (id > 800 or success = 1)
    and year(timestamp)=2011

2 order by before group by

• case

SELECT wp_posts.* FROM wp_posts
    WHERE wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
    GROUP BY wp_posts.post_author           
    ORDER BY wp_posts.post_date DESC

goals：The example query produces unusable results as its not always the latest post that is returned.

• solution

subquery：

SELECT wp_posts.* FROM 
(
    SELECT * 
    FROM wp_posts
    ORDER BY wp_posts.post_date DESC
) AS wp_posts
WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author

join（the best solution!）：

select * from `wp_posts`
inner join 
(
    select max(post_date) as `max_post_date`, post_author
    from `wp_posts`
    where wp_posts.post_status='publish'
        AND wp_posts.post_type='post'
    group by wp_post.post_author
) as p
    on wp_posts.post_author = p.post_author
    and wp_posts.post_date = p.max_post_date
where wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
order by wp_posts.post_date desc

3 COALESCE() 函数

此函数返回参数中第一个不为 null 参数的值

• case

goals：取出系统分、专家评分，若专家分不为 null 则以专家评分为准

• solution

coalesce(sme_score, sys_score, 0)

4 load 命令

load 在大批量的导入和导出数据时效率非常高

LOAD DATA LOCAL INFILE 'C:/Users/hkf/Desktop/task_ningxia.txt' REPLACE INTO TABLE `task_ningxia` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;