1873.计算特殊奖金

表: Employees
+-------------+---------+
| 列名        | 类型     |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
| salary      | int     |
+-------------+---------+
employee_id 是这个表的主键。
此表的每一行给出了雇员id ，名字和薪水。
写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以'M'开头，那么他的奖金是他工资的100%，否则奖金为0。
返回的结果集请按照employee_id排序
1
2
3
4
5
6
7
8
9
10
11
12

答案解析

IF 表达式

IF( expr1 , expr2 , expr3)
如果expr1为真返回expr2，否则返回expr3

IFNULL(expr1 , expr2)
如果expr1是null， 返回expr2，否则返回expr1
1
2
3
4
5

SELECT employee_id, 
IF(MOD(employee_id, 2) != 0 AND LEFT(name, 1) != 'M', salary, 0) bonus
FROM Employees
ORDER BY employee_id
1
2
3
4

627.变更性别

Salary 表：
+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| name        | varchar  |
| sex         | ENUM     |
| salary      | int      |
+-------------+----------+
id 是这个表的主键。
sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。
请你编写一个 SQL 查询来交换所有的 'f' 和 'm' （即，将所有 'f' 变为 'm' ，反之亦然），仅使用 单个 update 语句 ，且不产生中间临时表。
1
2
3
4
5
6
7
8
9
10
11
12

答案解析

update基本使用：
update 表 set 字段1 = ‘值1’ where 字段2=‘值2’

UPDATE Salary SET sex = IF(sex = 'm', 'f', 'm')
1

196.删除重复的电子邮箱

表: Person
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id是该表的主键列。该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。
编写一个 SQL 删除语句来 删除 所有重复的电子邮件，只保留一个id最小的唯一电子邮件。以 任意顺序 返回结果表。
1
2
3
4
5
6
7
8
9

答案解析

DELETE 基本使用

DELETE FROM table1 WHERE table1.id = 1
1

DELETE关联表进行删除， 内连接子句用法： t1 INNER JOIN t2 ON 连接条件

DELETE table1 
FROM table1 INNER JOIN table2 ON table1.id = table2.id
WHERE table2.type = 'a' AND table1.id = 2 
1
2
3

这句SQL意思是table1 和table2 左按 id 做内连接 （ID一样的接成一行），删除type是 ‘a’ 的和 id 是2的

这道题需要用到自联接，要执行自联接，可以使用内连接或左连接子句
可以把表按照 email相同进行连接，然后删除 p1.id > p2.id 的行

DELETE p1 
FROM Person p1 INNER JOIN(Person p2) ON p1.email = p2.email  
WHERE p1.id > p2.id
1
2
3

也可以这样写：
两个表都是自己，做内连接，没有 ON条件，相当于笛卡尔积， 然后删除 email 相同且 p1.id > p2.id的

DELETE p1
FROM Person p1 INNER JOIN(Person p2) 
WHERE p1.email = p2.email AND p1.id > p2.id
1
2
3

复习+总结

SELECT 用法

最基本的：

SELECT 列名 FROM 表明 WHERE 条件
1

表达式用法

IF(expr1, expr2, expr3)    expr1 为true则返回expr2， 否则返回expr3
IFNULL(expr1, expr2)       expr1 为NULL则返回expr2， 否则返回expr1
ISNULL(expr1)     expr1是NULL则返回1，否则返回0
NOT IN(list)  不在list内 
1
2
3
4

UPDATE 用法

最基本的： WHERE找到满足条件的记录，然后把其中某个字段改变

UPDATE 表 SET 字段1="值1"  WHERE 字段2="值2"
1

连接用法

a,b是表1，表2的别名

表1 a INNER JOIN(表2 b) ON 连接条件（不写条件就是笛卡尔积） 
1

DELETE用法

最基本的：把表中字段为某值的找出来删掉

DELETE FROM 表 WHERE 字段="值"
1

DELETE关联表进行删除, 删除表 t1 中 id 为2 且 t2表中有 id 同样为2 类型为 ’a‘ 的记录

DELETE t1 
FROM t1 INNER JOIN(t2) ON t1.id = t2.id 
WHERE t1.id = 2 AND t2.type = 'a'
1
2
3