MySQL 练习＜1＞

MySQL 练习

大家好呀，我是小笙，今天我来分享一些 Leetcode 上的MySQL的练习

1757. 可回收且低脂的产品

写出 SQL 语句，查找既是低脂又是可回收的产品编号。

# 创建表
Create table If Not Exists Products 
(
    product_id int, 
    low_fats ENUM('Y', 'N'), 
    recyclable ENUM('Y','N')
)
-- product_id 是这个表的主键。
-- low_fats 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品。
-- recyclable 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品可回收，而 'N' 表示不可回收。

# 插入数据
insert into Products (product_id, low_fats, recyclable) values ('0', 'Y', 'N')
insert into Products (product_id, low_fats, recyclable) values ('1', 'Y', 'Y')
insert into Products (product_id, low_fats, recyclable) values ('2', 'N', 'Y')
insert into Products (product_id, low_fats, recyclable) values ('3', 'Y', 'Y')
insert into Products (product_id, low_fats, recyclable) values ('4', 'N', 'N')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

示例

Products 表：
+-------------+----------+------------+
| product_id  | low_fats | recyclable |
+-------------+----------+------------+
| 0           | Y        | N          |
| 1           | Y        | Y          |
| 2           | N        | Y          |
| 3           | Y        | Y          |
| 4           | N        | N          |
+-------------+----------+------------+
Result 表：
+-------------+
| product_id  |
+-------------+
| 1           |
| 3           |
+-------------+
只有产品 id 为 1 和 3 的产品，既是低脂又是可回收的产品
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

代码示例

select product_id from Products
            where low_fats = 'Y' AND recyclable = 'Y';
1
2

---

584. 寻找用户推荐人

写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都不是 2

# 创建表
Create table If Not Exists Customer 
(
    id int, 
    name varchar(25), 
    referee_id int
)

# 插入数据
insert into Customer (id, name, referee_id) values ('1', 'Will', 'None')
insert into Customer (id, name, referee_id) values ('2', 'Jane', 'None')
insert into Customer (id, name, referee_id) values ('3', 'Alex', '2')
insert into Customer (id, name, referee_id) values ('4', 'Bill', 'None')
insert into Customer (id, name, referee_id) values ('5', 'Zack', '1')
insert into Customer (id, name, referee_id) values ('6', 'Mark', '2')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

示例

+------+------+-----------+
| id   | name | referee_id|
+------+------+-----------+
|    1 | Will |      NULL |
|    2 | Jane |      NULL |
|    3 | Alex |         2 |
|    4 | Bill |      NULL |
|    5 | Zack |         1 |
|    6 | Mark |         2 |
+------+------+-----------+
// 结果
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

代码示例

# 因为查询结果只会包含 WHERE 子句里的判断结果为 true 的行，不包含判断结果为 false 和 unknown 的行
select name from customer
        where referee_id != 2 or referee_id is null;
1
2
3

---

196. 删除重复的电子邮箱

编写一个SQL查询来 删除 所有重复的电子邮件，只保留一个id最小的唯一电子邮件

# 创建表
Create table If Not Exists Person (
    Id int, 
    Email varchar(255)
)

# 插入数据
insert into Person (id, email) values ('1', 'john@example.com')
insert into Person (id, email) values ('2', 'bob@example.com')
insert into Person (id, email) values ('3', 'john@example.com')
1
2
3
4
5
6
7
8
9
10

示例 :

输入: 
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

代码实现

DELETE 
    p1 
FROM 
    Person p1,Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id
1
2
3
4
5
6

---

1527. 患某种疾病的患者

写一条 SQL 语句，查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1

# 创建表
Create table If Not Exists Patients (
    patient_id int, 
    patient_name varchar(30), 
    conditions varchar(100)
)

# 插入数据
insert into Patients (patient_id, patient_name, conditions) values ('1', 'Daniel', 'YFEV COUGH')
insert into Patients (patient_id, patient_name, conditions) values ('2', 'Alice', '')
insert into Patients (patient_id, patient_name, conditions) values ('3', 'Bob', 'DIAB100 MYOP')
insert into Patients (patient_id, patient_name, conditions) values ('4', 'George', 'ACNE DIAB100')
insert into Patients (patient_id, patient_name, conditions) values ('5', 'Alain', 'DIAB201')
1
2
3
4
5
6
7
8
9
10
11
12
13

示例:

输入：
Patients表：
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 1          | Daniel       | YFEV COUGH   |
| 2          | Alice        |              |
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 |
| 5          | Alain        | DIAB201      |
+------------+--------------+--------------+
输出：
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 | 
+------------+--------------+--------------+
解释：Bob 和 George 都患有代码以 DIAB1 开头的疾病。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

代码实现

select
    patient_id,
    patient_name,
    conditions
from 
    Patients
where
    conditions like "DIAB1%" or  conditions like "% DIAB1%"
1
2
3
4
5
6
7
8

---

1873. 计算特殊奖金

写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以’M’开头，那么他的奖金是他工资的100%，否则奖金为0。返回的结果集请按照employee_id排序

# 创建表
Create table If Not Exists Employees (
    employee_id int, 
    name varchar(30), 
    salary int
)

# 插入数据
insert into Employees (employee_id, name, salary) values ('2', 'Meir', '3000')
insert into Employees (employee_id, name, salary) values ('3', 'Michael', '3800')
insert into Employees (employee_id, name, salary) values ('7', 'Addilyn', '7400')
insert into Employees (employee_id, name, salary) values ('8', 'Juan', '6100')
insert into Employees (employee_id, name, salary) values ('9', 'Kannon', '7700')
1
2
3
4
5
6
7
8
9
10
11
12
13

示例：

输入：
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  |
+-------------+-------+
解释：
因为雇员id是偶数，所以雇员id 是2和8的两个雇员得到的奖金是0
雇员id为3的因为他的名字以'M'开头，所以，奖金是0
其他的雇员得到了百分之百的奖金
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

代码实现

select 
    employee_id,if(employee_id % 2 = 1 and not(name like 'M%'),salary,0) as bonus 
from 
    Employees
order by 
    employee_id；
1
2
3
4
5
6

---