前言
开坑! 本系列记录了笔试中可能会遇到的sql问题,一边记录一边复习(好记性不如烂笔头嘿嘿),欢迎同学们一起讨论.
题源 力扣
一、寻找用户推荐人(查找与排序)
1.题目要求
写出一个SQL 查询语句,计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以’M’开头,那么他的奖金是他工资的100%,否则奖金为0。返回的结果集请按照employee_id排序。查询结果格式如下面的例子所示。
#表: Employees
#employee_id 是这个表的主键。
#此表的每一行给出了雇员id ,名字和薪水。
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| employee_id | int |
| name | varchar |
| salary | int |
+-------------+---------+
示例:
输入:
Employees 表:
+-------------+---------+--------+
| employee_id | name | salary |
+-------------+---------+--------+
| 2 | Meir | 3000 |
| 3 | Michael | 3800 |
| 7 | Addilyn | 7400 |
| 8 | Juan | 6100 |
| 9 | Kannon | 7700 |
+-------------+---------+--------+
输出:
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 2 | 0 |
| 3 | 0 |
| 7 | 7400 |
| 8 | 0 |
| 9 | 7700 |
+-------------+-------+
2. 解题思路
如果一个雇员的id是奇数
- 与运算&(通过检查二进制位最后一位判断奇偶):奇数和1与运算结果是1,偶数和1与运算结果是0
- Mod(a,b) (sql中表示 a / b 的余数) :mod(id,2)=1 是指id是奇数。mod(id,2)=0 是指id是偶数。
并且
- AND
他的名字不是以’M’开头,
- 通配符(%占位符表示0个或多个字符):
用like引导,not like ‘%M’,('M%'表示M结尾) - 正则(^匹配行首):
用rlike引导,not rlike ‘^M’,或者 not regexp ^M’
注: 通配符用于路径名扩展,而正则表达式用于文本匹配。正则表达参考
那么他的奖金是他工资的100%,否则奖金为0。
- IF(expr1,expr2,expr3)
(expr1 的值为 TRUE,则返回值为 expr2 ,expr1 的值为FALSE,则返回值为 expr3):
IF(
expr1,
Salary,
0
)
返回的结果集请按照employee_id排序。
- order by employee_id
查询结果格式如下面的例子所示。
- as bonus
3. 代码示例
# Write your MySQL query statement below
SELECT
employee_id,
IF(
employee_id&1 AND name not like 'M%',
salary,
0
)AS bonus
FROM Employees
ORDER BY employee_id;
二、变更性别(修改与查找)
1.题目要求
查询来交换所有的 ‘f’ 和 ‘m’ (即,将所有 ‘f’ 变为 ‘m’ ,反之亦然),仅使用 单个 update 语句 ,且不产生中间临时表。注意,你必须仅使用一条 update 语句,且 不能 使用 select 语句。
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| name | varchar |
| sex | ENUM |
| salary | int |
+-------------+----------+
id 是这个表的主键。
sex 这一列的值是 ENUM 类型,只能从 ('m', 'f') 中取。
本表包含公司雇员的信息。
示例:
输入:
Salary 表:
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
+----+------+-----+--------+
输出:
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
+----+------+-----+--------+
解释:
(1, A) 和 (3, C) 从 'm' 变为 'f' 。
(2, B) 和 (4, D) 从 'f' 变为 'm' 。
2.解题思路
查询来交换所有的 ‘f’ 和 ‘m’
- case…when
用于计算条件列表并返回多个可能结果,该语句执行时先对条件进行判断,然后根据判断结果做出相应的操作。
# 简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
#或者
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
- IF(expr1,expr2,expr3)
不允许使用select,不产生中间临时表
- UPDATE table_name SET field1=new-value1 [WHERE Clause].
update语句用来修改或更新mysql中的数据。
另辟蹊径,题目要求m和f互换,那么可以字母和ASCII互转
- update salary set sex = char(ascii(‘m’) + ascii(‘f’) - ascii(sex));
3.代码示例
1.case…when
# Write your MySQL query statement below
UPDATE Salary
SET sex = CASE sex
WHEN 'f' THEN 'm'
ELSE 'f'
END;
#执行用时:241 ms,内存消耗:0 B
2.if
# Write your MySQL query statement below
UPDATE Salary
SET sex = IF(sex='f','m','f')
#执行用时:243 ms,内存消耗:0 B
三、删除重复(查找与删除)
1.题目要求
编写一个 SQL 删除语句来 删除 所有重复的电子邮件,只保留一个id最小的唯一电子邮件。以 任意顺序 返回结果表。 (注意: 仅需要写删除语句,将自动对剩余结果进行查询)
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
# id是该表的主键列。该表的每一行包含一封电子邮件。电子邮件将不包含大写字母
示例:
输入:
Person 表:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
输出:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
解释: john@example.com重复两次。我们保留最小的Id = 1。
2.解题思路
删除 内连接方法
- delete from table1 where table1.id = 1;
- delete table1 from table1 inner join table2 on table1.id = table2.id
where table2.type = ‘something’ and table1.id = ‘idnums’;
- 内连接,形成笛卡儿积
原表
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
自连接为笛卡尔积
+----+------------------+ ----+------------------+
| id | email | id | email |
+----+------------------+--- +------------------+
| 1 | john@example.com | 1 | john@example.com
| 2 | bob@example.com | 1 | john@example.com
| 3 | john@example.com | 1 | john@example.com
| 1 | john@example.com | 2 | bob@example.com
| 2 | bob@example.com | 2 | bob@example.com
| 3 | john@example.com | 2 | bob@example.com
| 1 | john@example.com | 3 | john@example.com
| 2 | bob@example.com | 3 | john@example.com
| 3 | john@example.com | 3 | john@example.com
+----+------------------++----+------------------+
筛选笛卡尔积中email相同的记录
+----+------------------+ ----+------------------+
| id | email | id | email |
+----+------------------+--- +------------------+
| 1 | john@example.com | 1 | john@example.com
| 3 | john@example.com | 1 | john@example.com
| 1 | john@example.com | 3 | john@example.com
| 3 | john@example.com | 3 | john@example.com
+----+------------------++----+------------------+
筛选符合条件的a表的id > b表的id,删除下表结果
| 3 | john@example.com | 1 | john@example.com
| 1 | john@example.com | 3 | john@example.com
| 3 | john@example.com | 3 | john@example.com
删除 自连接方法
- DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
3.代码示例
DELETE a
FROM Person AS a
INNER JOIN Person AS b
WHERE a.email = b.email AND a.id>b.id