MySQL: 排序, 修改

Published: by Creative Commons Licence

IF

If we only have two choices:

if (condition, choice 1, choice 2)

i.e.

select employee_id, 
    if (employee_id % 2 = 1 and left(name, 1) != 'M', salary, 0) bonus
from Employees

CASE … END

If choices are more then two, CASE is good choice., shown below.

case
    when condition_1 then choice_1
    when condition_2 then choice_2
    ...
    else 0
end as bonus

i.e.

select employee_id,
    case
        when employee_id % 2 = 1 and left(name, 1) <> 'M' then salary
        else 0
    end as bonus
from Employees

UPDATE

update table_name
set
    ... (use IF or CASE)

i.e.

Table: Salary

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| name        | varchar  |
| sex         | ENUM     |
| salary      | int      |
+-------------+----------+
id is the primary key for this table.
The sex column is ENUM value of type ('m', 'f').
The table contains information about an employee.
  • Write an SQL query to swap all 'f' and 'm' values (i.e., change all 'f' values to 'm' and vice versa) with a single update statement and no intermediate temporary tables.
  • Note that you must write a single update statement, do not write any select statement for this problem.

(use IF)

update salary
set
    sex = if (sex='f', 'm', 'f')

(or use CASE)

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

DELETE

We can first select what we want, and then change the select to delete.

delete p1.*
from Person p1, Person p2
where p1.Email=p2.Email and p1.id > p2.id