ShowDocs
搜索文章Ctrl K

外观

← 返回MySQL列表
应用实践

MySQL 查询练习：筛选、NULL 与分页 ​

MySQL 查询练习 ​

查询 ​

可回收且低脂的产品 ​

  • 元数据

    sql
    Create table If Not Exists Products (product_id int PRIMARY KEY, low_fats ENUM('Y', 'N'), recyclable ENUM('Y','N'));
Truncate table Products;
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');

  • 解释

    • product_id: 是该表的主键（具有唯一值的列）
    • low_fats: 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品
    • recyclable: 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品可回收，而 'N' 表示不可回收

  • 题解

    sql
    select product_id from Products where low_fats = 'Y' and recyclable = 'Y';

寻找用户推荐人 ​

  • 元数据

    sql
    Create table If Not Exists Customer (id int PRIMARY KEY, name varchar(25), referee_id int);
Truncate table Customer;
insert into Customer (id, name) values ('1', 'Will');
insert into Customer (id, name) values ('2', 'Jane');
insert into Customer (id, name, referee_id) values ('3', 'Alex', '2');
insert into Customer (id, name) values ('4', 'Bill');
insert into Customer (id, name, referee_id) values ('5', 'Zack', '1');
insert into Customer (id, name, referee_id) values ('6', 'Mark', '2');

  • 解释

    • referee_id 推荐 ID
    • 在 SQL 中，id 是该表的主键列
    • 该表的每一行表示一个客户的 id、姓名以及推荐他们的客户的 id
    • 找出那些 没有被 id = 2 的客户 推荐 的客户的姓名

  • 题解: <>

    sql
    select name from Customer where referee_id != 2 or referee_id is NULL;
SELECT name FROM Customer WHERE NOT referee_id <=> 2;

大的国家 ​

  • 元数据
sql
Create table If Not Exists World (name varchar(255) PRIMARY KEY, continent varchar(255), area int, population int, gdp bigint);
Truncate table World;
insert into World (name, continent, area, population, gdp) values ('Afghanistan', 'Asia', '652230', '25500100', '20343000000');
insert into World (name, continent, area, population, gdp) values ('Albania', 'Europe', '28748', '2831741', '12960000000');
insert into World (name, continent, area, population, gdp) values ('Algeria', 'Africa', '2381741', '37100000', '188681000000');
insert into World (name, continent, area, population, gdp) values ('Andorra', 'Europe', '468', '78115', '3712000000');
insert into World (name, continent, area, population, gdp) values ('Angola', 'Africa', '1246700', '20609294', '100990000000');

  • 解释

    • name 是该表的主键（具有唯一值的列）
    • 这张表的每一行提供：国家名称、所属大陆、面积、人口和 GDP 值
    • 如果一个国家满足下述两个条件之一，则认为该国是 大国 ：
      • 面积至少为 300 万平方公里（即，3000000 km2），或者
      • 人口至少为 2500 万（即 25000000）
    • 编写解决方案找出 大国 的国家名称、人口和面积

  • 题解

    sql
    select name,population,area from World where area >= 3000000 or population >= 25000000;

文章预览 1 ​

  • 元数据

    sql
    Create table If Not Exists Views (article_id int, author_id int, viewer_id int, view_date date);
Truncate table Views;
insert into Views (article_id, author_id, viewer_id, view_date) values ('1', '3', '5', '2019-08-01');
insert into Views (article_id, author_id, viewer_id, view_date) values ('1', '3', '6', '2019-08-02');
insert into Views (article_id, author_id, viewer_id, view_date) values ('2', '7', '7', '2019-08-01');
insert into Views (article_id, author_id, viewer_id, view_date) values ('2', '7', '6', '2019-08-02');
insert into Views (article_id, author_id, viewer_id, view_date) values ('4', '7', '1', '2019-07-22');
insert into Views (article_id, author_id, viewer_id, view_date) values ('3', '4', '4', '2019-07-21');
insert into Views (article_id, author_id, viewer_id, view_date) values ('3', '4', '4', '2019-07-21');

  • 解释

    • 此表可能会存在重复行。（换句话说，在 SQL 中这个表没有主键）
    • 此表的每一行都表示某人在某天浏览了某位作者的某篇文章。
    • 请注意，同一人的 author_id 和 viewer_id 是相同的。
    • 请查询出所有浏览过自己文章的作者
    • 结果按照 id 升序排列

  • 题解

    sql
    select distinct author_id as id from Views where author_id = viewer_id order by id;

Other ​

原笔记此处没有题面、样例或解答；请使用上方四道完整查询题。

MySQL 分页查询实践 ​

从来源文档中剥离的技术说明。示例与约束沿用原文，使用前按实际环境核对。

分页查询 ​

下面假定 id 是唯一递增的排序键；ORDER BY 才保证分页顺序。下一页使用上一页末行的 id 作为边界，不能只靠 WHERE 与 LIMIT 推断返回顺序。

sql
-- ✅ 推荐：基于主键分页
SELECT id, name 
FROM user_info 
WHERE id > 1000
ORDER BY id
LIMIT 100;

-- ❌ 不推荐：OFFSET 偏移量大时性能差
SELECT id, name 
FROM user_info
ORDER BY id
LIMIT 100000, 100;

适用范围 ​

延伸阅读 ​

查询语法、排序与分页参数见 MySQL 查询语法与函数。

继续阅读