MySql sql查询练习

master-dragon

已于 2025-08-13 22:54:37 修改

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： # 数据库-关系型-非关系型文章标签： mysql 数据库

于 2018-10-19 10:23:49 首次发布

本文链接：https://blog.csdn.net/qq_26437925/article/details/83177428

数据库-关系型-非关系型专栏收录该内容

27 篇文章

订阅专栏

本文通过对比单次查询与批量查询数据库的效率，展示了在处理大量数据时，使用in语句进行批量查询比频繁的小查询更节省时间和资源。实验结果显示，批量查询在处理1000条数据时，耗时仅为单次查询的约四分之一。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

https://leetcode.cn/problems/duplicate-emails/

子查询

select email as 'Email' from
(
    select email, count(email) as cntEmail from Person
    group by email
) as t where cntEmail > 1

group by , having

select email as 'Email' from Person
group by email
having count(email) > 1

https://leetcode.cn/problems/customers-who-never-order/description/

子查询

select name as 'Customers' from Customers where id not in
(
    select customerId from Orders
)

left join + where 条件

select t1.name as 'Customers' from Customers t1 
left join Orders t2 on t1.id = t2.customerId
where t2.customerId is null

https://leetcode.cn/problems/delete-duplicate-emails/description/

输入: 
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。

delete多表删除按条件

delete p1 from Person p1,
    Person p2
WHERE
    p1.email = p2.email AND p1.Id > p2.Id

https://leetcode.cn/problems/game-play-analysis-i/

Activity 表：
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result 表：
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1         | 2016-03-01  |
| 2         | 2017-06-25  |
| 3         | 2016-03-02  |
+-----------+-------------+

group by 需要 min、max、sum、count等操作

select player_id, min(event_date) as 'first_login'  from Activity group by player_id

https://leetcode.cn/problems/managers-with-at-least-5-direct-reports/

输入: 
Employee 表:
+-----+-------+------------+-----------+
| id  | name  | department | managerId |
+-----+-------+------------+-----------+
| 101 | John  | A          | Null      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
+-----+-------+------------+-----------+
输出: 
+------+
| name |
+------+
| John |
+------+

group by having … + join … on …

select t1.name from Employee t1
join(
    select managerId from Employee 
    where managerId is not null
    group by managerId
    having count(id) >= 5
    ) t2 
on t1.id = t2.managerId

https://leetcode.cn/problems/game-play-analysis-iv/description/

输入：
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
输出：
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
解释：
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33

多重查询，DATE_ADD、round、IFNULL等函数使用

select IFNULL(round(count(distinct(Result.player_id)) 
    / count(distinct(Activity.player_id)), 2), 0) 
as fraction
from (
    select Activity.player_id as player_id from
    (
        select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date
        from Activity
        group by player_id
    ) as Expected, Activity where 
    Expected.second_date = Activity.event_date 
    and  Activity.player_id = Expected.player_id
) as Result, Activity;