595. 大的国家

我写出了这样的代码，很简单，但是，讨论区出了一个关于or不走索引的问题。
但是我有看到说 mysql5.0以前是一张表只能使用一次索引，但5.1以后引入了index merge。会自动将多个or条件索引分别根据条件检索再union。所以速度没有太大差别。

  1. select name,population,area
  2. from World
  3. where area >= 3000000
  4. or population >=25000000
  5. # 使用or 不会走索引，所以要使用union
  6. SELECT
  7.     name, population, area
  8. FROM
  9.     world
  10. WHERE
  11.     area >= 3000000
  13. UNION
  15. SELECT
  16.     name, population, area
  17. FROM
  18.     world
  19. WHERE
  20.     population >= 25000000

584. 寻找用户推荐人

+------+------+-----------+
| id   | name | referee_id|
+------+------+-----------+
|    1 | Will |      NULL |
|    2 | Jane |      NULL |
|    3 | Alex |         2 |
|    4 | Bill |      NULL |
|    5 | Zack |         1 |
|    6 | Mark |         2 |
+------+------+-----------+

# 如果不加 referee_id is null,就只能查出来一条结果

select name
from customer 
where referee_id != 2
OR referee_id is null

183. 从不订购的客户

我不太理解左连接加null的操作

select c.Name as Customers 
from Customers as c
left join Orders as o on c.Id = o.CustomerId
where o.Id is null

1873. 计算特殊奖金

这个还挺复杂的

SELECT
    employee_id, 
CASE WHEN 
    MOD(employee_id, 2) = 1 AND name not rlike '^M' THEN salary ELSE 0 END AS bonus 
FROM
    Employees 
ORDER BY 
    employee_id;

627. 变更性别

UPDATE salary
SET
    sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;

196. 删除重复的电子邮箱

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id