SQL入门基本语句练习
1、大的国家
SQL Schema
World 表：
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| name | varchar |
| continent | varchar |
| area | int |
| population | int |
| gdp | int |
+-------------+---------+
name 是这张表的主键。
这张表的每一行提供：国家名称、所属大陆、面积、人口和 GDP 值。
如果一个国家满足下述两个条件之一，则认为该国是 大国 ：
- 面积至少为 300 万平方公里（即，
3000000 km2），或者 - 人口至少为 2500 万（即
25000000）
编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。
按 任意顺序 返回结果表。
查询结果格式如下例所示。
示例：
输入：
World 表：
+-------------+-----------+---------+------------+--------------+
| name | continent | area | population | gdp |
+-------------+-----------+---------+------------+--------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000000 |
| Albania | Europe | 28748 | 2831741 | 12960000000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000000 |
| Andorra | Europe | 468 | 78115 | 3712000000 |
| Angola | Africa | 1246700 | 20609294 | 100990000000 |
+-------------+-----------+---------+------------+--------------+
输出：
+-------------+------------+---------+
| name | population | area |
+-------------+------------+---------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+-------------+------------+---------+
- 方法一：使用WHERE子句和OR
SELECT `name`,population ,area FROM World WHERE population >= 25000000 OR area >= 3000000
- 方法二：使用
WHERE子句和UNION
SELECT `name`,population,area from World WHERE population >= 25000000
UNION
SELECT `name`,population,area from World WHERE area >= 3000000
UNION运算符用于将两个查询的结果合并在一起，并去除重复项。需要注意的是，两个SELECT查询的列必须相同才能使用UNION运算符。
- UNION 与 UNION ALL 的区别是什么？
UNION：合并多个或多个查询结果集，并去除重复的结果行，如果两个或多个查询返回的行有相同的值，那么UNION只会保留一行。
UNION ALL：合并两个或多个查询结果集，并保留所有行，包括重复行。如果两个或多个查询返回的行有相同的值，那么UNION ALL会将它们都保留下来。
使用UNION操作符需要进行去重操作，所以它的执行效率可能比使用UNION ALL操作符要低。
2、可回收且低脂的产品
表：Products
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_id | int |
| low_fats | enum |
| recyclable | enum |
+-------------+---------+
product_id 是这个表的主键。
low_fats 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品。
recyclable 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品可回收，而 'N' 表示不可回收。
写出 SQL 语句，查找既是低脂又是可回收的产品编号。
返回结果 无顺序要求 。
查询结果格式如下例所示：
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 的产品，既是低脂又是可回收的产品。
- 其实很简单，就是提到了枚举类型，把自己搞蒙了
SELECT product_id FROM Products
WHERE
low_fats = 'Y' AND recyclable = 'Y';
3、寻找用户推荐人
给定表 customer ，里面保存了所有客户信息和他们的推荐人。
+------+------+-----------+
| id | name | referee_id|
+------+------+-----------+
| 1 | Will | NULL |
| 2 | Jane | NULL |
| 3 | Alex | 2 |
| 4 | Bill | NULL |
| 5 | Zack | 1 |
| 6 | Mark | 2 |
+------+------+-----------+
写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都 不是 2。
对于上面的示例数据，结果为：
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+
- 题解
思路主要是把不等于2的筛选出来，里面包含NULL和其他数字id。难点在于怎么筛选Null的数据。我也忘记了这个 < >记录一下。
SELECT `name` FROM customer WHERE referee_id != 2 OR
referee_id IS NULL
MySQL中使用三值逻辑，也称三值布尔逻辑。这是因为MySQL的布尔类型（BOOL）不是二进制（true/false)而是，三个值之一：TRUE，FALSE或者NULL。其中NULL表示位置或者不确定的值。不同于其他编程语言中的undefined和null值，因此在MySQL中的布尔值运算在计算结果是都需要考虑NULL情况。通常使用使用IS NULL或IS NOT NULL运算符。
所以，MySQL的三值逻辑可以帮助处理在计算时出现未知或不确定值的情况，但需要在编写查询语句时特别注意处理NULL值的情况，避免产生不正确的结果。
-
拓展
-
·NULL的含义
NULL有两种意思，“未知”unkown和“inapplicable”不适用，比如，不知道带眼睛的人的眼睛是什么颜色，这就是未知，因为人身上是有眼睛的，只要不把他的眼睛摘掉就是未知，但是这个人的眼睛一定是有颜色的。不知道冰箱的眼睛是什么颜色的，这就是不适用，眼睛这个属性不适用于冰箱上。
如今，DBMS都将这两种情况归为了一类，采用了三值逻辑！
-
为什么是IS NULL，而不是“ = NULL”？
比如以下的 写法就是错误的
SELECT `name` FROM customer WHERE referee_id != 2 OR referee_id = NULL 很奇怪是吗？我们引向中
=就表示相等关系。但是对NULL使用为此得到的结果就是unknown。 在SQL的保留字中，很多都被归于谓词一类，例如>,<>,= 等比较谓词，以及BETWEEN, LIKE, IN, IS NULL等。谓词是一种特殊的函数，返回值是真值，前面提到的谓词，返回值都是true，false，unkown，SQL是三值逻辑，所以有三种真值。
-
注意：SELECT查询结果只会包含语句里面where 里面 判断结果为true的行！不包括false和unkown的行。且不仅是等号，对NULL使用其他比较谓词（eg: > NULL），结果也是nuknown。
-
重点理解：
NULL不是值，所以不能对其使用谓词，如果使用谓词，结果是unknown。可以把它当做一个没有值的标记，所以是不能使用谓词的，因为谓词只用于比较值。因此对非值的NULL使用比较谓词是没有意义的
-
如何理解IS NULL?是两个单词？IS空格NULL?
NULL不属于关系型数据库中的某种类型。
我们应该把IS NULL看作一个谓词，如果可以IS_NULL或许更合适。
-
-
三值逻辑运算
unknown小写，是第三个真值。与作为NULL的一种UNKNOWN(未知)是不同的东西。小写是明确的布尔类型的真值，后者大写的既不是值也不是变量。为了对比不同：看x=x的情况。
作者：橡树😎
链接：https://leetcode.cn/problems/find-customer-referee/solutions/1432066/san-by-xiang-shu-k-7ywp/
来源：力扣（LeetCode）
3、从不订购的客户
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表：
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如给定上述表格，你的查询应返回：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
- 组合子查询：使用子查询和
NOT IN子句
SELECT `Name` AS 'Customers' FROM Customers cus
WHERE cus.Id NOT IN (SELECT DISTINCT CustomerId FROM Orders)
4、计算特殊奖金
表: Employees
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| employee_id | int |
| name | varchar |
| salary | int |
+-------------+---------+
employee_id 是这个表的主键。
此表的每一行给出了雇员id ，名字和薪水。
写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以’M’开头，那么他的奖金是他工资的100%，否则奖金为0。
Return the result table ordered by employee_id.
返回的结果集请按照employee_id排序。
查询结果格式如下面的例子所示。
示例 1:
输入：
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。
其他的雇员得到了百分之百的奖金。
-
自己思考的，感觉很复杂，也做出来了，把思路记录在这里，等以后回来看看有何想法哈
-
根据题目所说的雇员id为奇数，且名字不为M开头奖金是他工资的100%。那么我先把这一部分人查出来
SELECT employee_id, salary AS bonus FROM Employees WHERE MOD ( employee_id, 2 ) = 1 AND LEFT ( NAME, 1 )!= 'M'-
其他的（id为偶数，id为奇数 姓名开始字母为M的）
SELECT employee_id, 0 AS bonus FROM Employees WHERE MOD ( employee_id, 2 ) = 0 OR LEFT ( NAME, 1 ) = 'M'基于昨天学到的UNION 把它们去重联合起来，再来一次排序
SELECT s.employee_id, s.bonus FROM ( SELECT employee_id, salary AS bonus FROM Employees WHERE MOD ( employee_id, 2 ) = 1 AND LEFT ( NAME, 1 )!= 'M' UNION SELECT employee_id, 0 AS bonus FROM Employees WHERE MOD ( employee_id, 2 ) = 0 OR (LEFT ( NAME, 1 ) = 'M' MOD ( employee_id, 2 ) = 1 ) ) s ORDER BY s.employee_id ASC;
-
这应该是我忘了一年多的学生能想到的了吧~~
-
-
拓展学习环节
-
常规解法：Like + %取余
再一次强调：
< >和 != 是一个意思！SQL中的IF语句模板格式
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE default_result END
-
`LEFT(string, length)`: 其中，`string` 表示要提取子字符串的原始字符串，`length` 表示要提取的字符数。
SELECT LEFT('Hello World', 3): 这将返回字符串 “Hel”，因为它从左侧开始提取了前三个字符。同样，我们可以使用 LEFT 函数从数据库表格的某个列中提取子字符串：
SELECT LEFT(column_name, 5)
FROM table_name;
这将返回一个包含从 column_name 中提取的前五个字符的结果集，该结果集由 table_name 表格中的所有行组成。
- 方法二：使用CASE多分支语句
SELECT employee_id,
(CASE WHEN MOD ( employee_id, 2) = 1 AND LEFT ( `name`, 1 ) != 'M' THEN salary
WHEN MOD ( employee_id, 2 ) = 0 OR LEFT ( `name`, 1 )= 'M' THEN 0
END ) bonus
FROM
Employees
ORDER BY employee_id
- 方法三：IF判断
SELECT employee_id,
IF(MOD(employee_id,2) = 1 AND LEFT(`name`,1) != 'M',salary,0) AS bonus
FROM Employees
ORDER BY employee_id ASC;
SELECT employee_id,
(CASE
WHEN MOD(employee_id,2) = 1 AND LEFT(`name`,1) != 'M' THEN salary
WHEN MOD(employee_id,2) = 0 OR LEFT(`name`,1) = 'M' THEN 0
ELSE 0
END
) AS bonus
FROM Employees
ORDER BY employee_id ASC
5、变更性别
Salary 表：
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| name | varchar |
| sex | ENUM |
| salary | int |
+-------------+----------+
id 是这个表的主键。
sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。
本表包含公司雇员的信息。
请你编写一个 SQL 查询来交换所有的 'f' 和 'm' （即，将所有 'f' 变为 'm' ，反之亦然），仅使用 单个 update 语句 ，且不产生中间临时表。
注意，你必须仅使用一条 update 语句，且 不能 使用 select 语句。
查询结果如下例所示。
示例 1:
输入：
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' 。
- 解法一：使用IF
更新的语法：
UPDATE Salary SET sex=IF(sex='m','f','m');
- 方法二：使用CASE WHEN THEN ELSE END
UPDATE Salary
SET sex =(
CASE WHEN sex = 'm' THEN'f'
ELSE 'm'
END
);
#等同于
UPDATE Salary SET sex=CASE
WHEN sex = 'm' THEN 'f'
ELSE 'm'
END;
- 方法三：ASCII码转换
update salary set sex = char(ASCII('f')+ASCII('m') - ASCII(sex));
#等同
update salary set sex = char(211 - ascii(sex))
6、删除重复的电子邮箱
表: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
id是该表的主键列。
该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。
编写一个 SQL 删除语句来 删除 所有重复的电子邮件，只保留一个id最小的唯一电子邮件。
以 任意顺序 返回结果表。 （注意： 仅需要写删除语句，将自动对剩余结果进行查询）
查询结果格式如下所示。
示例 1:
输入:
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 p1
FROM Person p1
LEFT JOIN (
SELECT MIN(id) AS id, email
FROM Person
GROUP BY email
) p2 ON p1.id = p2.id AND p1.email = p2.email
WHERE p2.id IS NULL;
这样一看就知道WHERE条件里为什么p2.id IS NULL 了吧！
-
知识拓展学习
-
DELETE用法
DELETE是一种在SQL中用于删除表格的命令。DELETE可与从表格中删除一行或者多行数据。
-
语法结构
DELETE FROM table_name WHERE condition; #eg DELETE FROM students WHERE name = 'Alice'; -
需要注意的是，使用 DELETE 命令删除数据是一个非常危险的操作，因为一旦数据被删除，它就无法恢复。因此，在执行 DELETE 命令之前，一定要仔细检查 WHERE 子句，并确保只删除必要的行。
-
-
聚合函数
-
GROUP BY 排序
GROUP BY 语句是一种用于将查询结果按照指定的列进行分组的语句。在 GROUP BY 语句中，需要指定一个或多个列名，用于对查询结果进行分组。
在 GROUP BY 语句中，可以指定一个或多个列名作为分组依据，这些列名必须是 SELECT 语句中的列名或者是聚合函数的结果。在分组的结果集中，每个分组的行都是具有相同分组依据的行的集合，可以对每个分组进行聚合函数的计算，例如 COUNT、SUM、AVG 等。
在 GROUP BY 语句中，指定一个列名作为分组依据时，该列中相同的值将被视为一组。也就是说，对于给定的列名，GROUP BY 语句将会根据该列中的值，将查询结果按照相同的值分成多个组，并对每个组执行相应的聚合函数计算。
如果在 GROUP BY 语句中指定多个列名，这将按照指定的列名依次进行分组。对于每个分组，将根据指定列的值进行分组，并将具有相同值的行放入同一个组中。在分组的过程中，如果指定的多个列的值都相同，则这些行将被放在同一个分组中。
- LEFT JOIN ON
-
7、按日期分组销售产品
表 Activities：
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| sell_date | date |
| product | varchar |
+-------------+---------+
此表没有主键，它可能包含重复项。
此表的每一行都包含产品名称和在市场上销售的日期。
编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。
查询结果格式如下例所示。
示例 1:
输入：
Activities 表：
+------------+-------------+
| sell_date | product |
+------------+-------------+
| 2020-05-30 | Headphone |
| 2020-06-01 | Pencil |
| 2020-06-02 | Mask |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible |
| 2020-06-02 | Mask |
| 2020-05-30 | T-Shirt |
+------------+-------------+
输出：
+------------+----------+------------------------------+
| sell_date | num_sold | products |
+------------+----------+------------------------------+
| 2020-05-30 | 3 | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2 | Bible,Pencil |
| 2020-06-02 | 1 | Mask |
+------------+----------+------------------------------+
解释：
对于2020-05-30，出售的物品是 (Headphone, Basketball, T-shirt)，按词典序排列，并用逗号 ',' 分隔。
对于2020-06-01，出售的物品是 (Pencil, Bible)，按词典序排列，并用逗号分隔。
对于2020-06-02，出售的物品是 (Mask)，只需返回该物品名。
- 方法一：COUNT、GROUP_CONCAT、SEPARATOR 、GROUP BY 、ORDER BY
思路 先按照时间日期分组，将一组内的字段升序拼接，最后排序。
SELECT sell_date,COUNT(DISTINCT product ) AS num_sold
,GROUP_CONCAT(DISTINCT product ORDER BY product ASC SEPARATOR ',')as products
FROM activities
GROUP BY sell_date
ORDER BY sell_date;
group_concat([distinct] 要连接的字段 [order by 排序字段] [separator ‘分隔符’])，内部收默认排序，不加分隔符默认 ‘,'.
8、患某种疾病的患者
- LeeCode传送
- 题目描述
患者信息表： Patients
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| patient_id | int |
| patient_name | varchar |
| conditions | varchar |
+--------------+---------+
patient_id （患者 ID）是该表的主键。
'conditions' （疾病）包含 0 个或以上的疾病代码，以空格分隔。
这个表包含医院中患者的信息。
写一条 SQL 语句，查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1 。
按 任意顺序 返回结果表。
查询结果格式如下示例所示。
示例 1:
输入：
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 开头的疾病。
- 方法一：主要运用模糊查询%的使用，
SELECT patient_id,patient_name,conditions
FROM patients
WHERE conditions LIKE 'DIAB1%' OR conditions LIKE '% DIAB1%';
-
方法二：正则表达式匹配
- 不可直接使用
CONDITIONS REGEXP 'DIAB1'筛选，因为DIAB1可能不是前缀 - 不同的疾病使用一个空格分开，所以患有I型糖尿病的患者的conditions有两种情况、
- 糖尿病位于第一个时: 以
DIAB1开始，即CONDITIONS REGEXP '^DIAB1 - 糖尿病不是第一个时: 含有
空格DIAB1，即CONDITIONS REGEXP '\\sDIAB1，其中’\s’表示空格.
- 糖尿病位于第一个时: 以
SELECT * FROM PATIENTS WHERE CONDITIONS REGEXP '^DIAB1|\\sDIAB1' - 不可直接使用
-
方法三：LEFT，locate
select *
from Patients
where left(conditions, 5) = "DIAB1" || locate(" DIAB1", conditions) != 0;
9、丢失的雇员
-
题目描述
表: Employees
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| employee_id | int |
| name | varchar |
+-------------+---------+
employee_id 是这个表的主键。
每一行表示雇员的id 和他的姓名。
表: Salaries
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| employee_id | int |
| salary | int |
+-------------+---------+
employee_id is 这个表的主键。
每一行表示雇员的id 和他的薪水。
写出一个查询语句，找到所有 丢失信息 的雇员id。当满足下面一个条件时，就被认为是雇员的信息丢失：
- 雇员的 姓名 丢失了，或者
- 雇员的 薪水信息 丢失了，或者
返回这些雇员的id employee_id ， 从小到大排序 。
查询结果格式如下面的例子所示。
示例 1：
输入：
Employees table:
+-------------+----------+
| employee_id | name |
+-------------+----------+
| 2 | Crew |
| 4 | Haven |
| 5 | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5 | 76071 |
| 1 | 22517 |
| 4 | 63539 |
+-------------+--------+
输出：
+-------------+
| employee_id |
+-------------+
| 1 |
| 2 |
+-------------+
解释：
雇员1，2，4，5 都工作在这个公司。
1号雇员的姓名丢失了。
2号雇员的薪水信息丢失了。
- 方法一：使用NOT IN 、UNION
(SELECT employee_id FROM employees
WHERE employee_id not IN
(SELECT e.employee_id FROM employees e, salaries s WHERE e.employee_id = s.employee_id)
UNION
SELECT employee_id FROM salaries
WHERE employee_id not IN
(SELECT e.employee_id FROM employees e, salaries s WHERE e.employee_id = s.employee_id)
)
ORDER BY employee_id ASC;
- 方法二：直接求差集
select employee_id from
(
select employee_id, count(1) as cnt
from
(
select employee_id from Employees
#联合但是不去重
union all
select employee_id from Salaries
) as x
group by employee_id
#在分组里面进行统计个数的结果集集中筛选统计次数为1的，在两个表中id会出现两个才说明没有没有缺失。
having cnt=1
) as y order by employee_id;
- 拓展sql中count(1)、count(*)与count(列名)的区别
10、每个产品在不同商店的价格
表：Products
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_id | int |
| store1 | int |
| store2 | int |
| store3 | int |
+-------------+---------+
这张表的主键是product_id（产品Id）。
每行存储了这一产品在不同商店store1, store2, store3的价格。
如果这一产品在商店里没有出售，则值将为null。
请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。
输出结果表中的 顺序不作要求 。
查询输出格式请参考下面示例。
示例 1：
输入：
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0 | 95 | 100 | 105 |
| 1 | 70 | null | 80 |
+------------+--------+--------+--------+
输出：
+------------+--------+-------+
| product_id | store | price |
+------------+--------+-------+
| 0 | store1 | 95 |
| 0 | store2 | 100 |
| 0 | store3 | 105 |
| 1 | store1 | 70 |
| 1 | store3 | 80 |
+------------+--------+-------+
解释：
产品0在store1，store2,store3的价格分别为95,100,105。
产品1在store1，store3的价格分别为70,80。在store2无法买到。
先把单独商店的哪里列查出来更改名为 store，在使用Union联合
select product_id,'store1' as 'store',store1 as 'price' from products where store1 is not null
UNION
SELECT product_id , 'store2' as 'store', store2 as 'price' FROM products where store2 is not null
UNION
SELECT product_id , 'store3' as 'store', store3 as 'price' FROM products where store3 is not null
ORDER BY product_id
11、树节点
给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。
+----+------+
| id | p_id |
+----+------+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+----+------+
树中每个节点属于以下三种类型之一：
- 叶子：如果这个节点没有任何孩子节点。
- 根：如果这个节点是整棵树的根，即没有父节点。
- 内部节点：如果这个节点既不是叶子节点也不是根节点。
写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：
+----+------+
| id | Type |
+----+------+
| 1 | Root |
| 2 | Inner|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
+----+------+
解释
-
节点 ‘1’ 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 ‘2’ 和 ‘3’ 。
-
节点 ‘2’ 是内部节点，因为它有父节点 ‘1’ ，也有孩子节点 ‘4’ 和 ‘5’ 。
-
节点 ‘3’, ‘4’ 和 ‘5’ 都是叶子节点，因为它们都有父节点同时没有孩子节点。
-
样例中树的形态如下：
1 / \ 2 3 / \ 4 5
注意
如果树中只有一个节点，你只需要输出它的根属性。
方法一：CASE 分支语句
SELECT id,
case
WHEN p_id is NULL THEN 'Root'
when id NOT IN (SELECT DISTINCT p_id FROM tree WHERE p_id is NOT NULL) THEN 'Leaf'
ELSE 'Inner'
END as 'Type'
FROM tree
12、组合两个表
SQL Schema
表: Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
personId 是该表的主键列。
该表包含一些人的 ID 和他们的姓和名的信息。
表: Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
addressId 是该表的主键列。
该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。
编写一个SQL查询来报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，则报告为空 null 。
以 任意顺序 返回结果表。
SELECT p.firstName,p.lastName,a.city,a.state FROM Person AS p
LEFT JOIN Address AS a ON p.personId = a.personId
-
拓展总结
-
inner join：2表值都存在
-
outer join：附表中值可能存在null的情况。
①A inner join B：取交集
②A left join B：取A全部，B没有对应的值，则为null
③A right join B：取B全部，A没有对应的值，则为null
④A full outer join B：取并集，彼此没有对应的值为null
上述4种的对应条件，在on后填写。
-
13、进店却未进行过交易的顾客
表：Visits
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| visit_id | int |
| customer_id | int |
+-------------+---------+
visit_id 是该表的主键。
该表包含有关光临过购物中心的顾客的信息。
表：Transactions
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| transaction_id | int |
| visit_id | int |
| amount | int |
+----------------+---------+
transaction_id 是此表的主键。
此表包含 visit_id 期间进行的交易的信息。
有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个 SQL 查询，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。
返回以 任何顺序 排序的结果表。
- 方法一：
先从交易表中查出交易的visit_id(去重)，在Visits中查询customer_id，计数visit_id，
条件是在交易表中没有出现的visits_id
SELECT customer_id,count(visit_id) as count_no_trans
FROM Visits
WHERE visit_id not in (SELECT DISTINCT visit_id FROM Transactions)
GROUP BY customer_id
- 方法二：左连接
由此一看，只有蓝色的那三个是买了东西的能连接成完整的记录(没有null)，其他的就有null（红色的那些），如下如，我们再一次分组求和，计数。
SELECT customer_id,COUNT(customer_id) FROM Visits v
LEFT JOIN Transactions t ON v.visit_id = t.visit_id
WHERE transaction_id IS NULL
GROUP BY customer_id
14、上升的温度
表： Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id 是这个表的主键
该表包含特定日期的温度信息
编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。
返回结果 不要求顺序 。
查询结果格式如下例。
示例 1：
输入：
Weather 表：
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
输出：
+----+
| id |
+----+
| 2 |
| 4 |
+----+
解释：
2015-01-02 的温度比前一天高（10 -> 25）
2015-01-04 的温度比前一天高（20 -> 30）
- 方法一：交叉连接
SELECT w1.id FROM Weather AS w1
CROSS JOIN Weather as w2 ON TIMESTAMPDIFF(day,w1.recordDate,w2.recordDate) = -1
WHERE w1.temperature > w2.temperature;
或
SELECT w1.id FROM Weather AS w1
CROSS JOIN Weather as w2 ON DATEDIFF(w1.recordDate,w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;
思路：这题主要是使用了交叉连接和时间相互减去的相差天数函数TIMESTAMPDIFF和DATEDIFF。
- 知识点拓展
交叉联结会将两个表中所有的数据两两组合。如下图，是对表“text”自身进行交叉联结的结果：
直接使用交叉联结的业务需求比较少见，往往需要结合具体条件，对数据进行有目的的提取，本题需要结合的条件就是“前一天”，我们需要在众多数据中找到当天比前一天高的温度。
datediff(日期1, 日期2)： 得到的结果是日期1与日期2相差的天数。 如果日期1比日期2大，结果为正；如果日期1比日期2小，结果为负。
timestampdiff(时间类型, 日期1, 日期2) 这个函数和上面diffdate的正、负号规则刚好相反。 日期1大于日期2，结果为负，日期1小于日期2，结果为正。
在“时间类型”的参数位置，通过添加“day”, “hour”, “second”等关键词，来规定计算天数差、小时数差、还是分钟数差。
- 方法二：笛卡尔积 值等连接
inner join(等值连接) 只返回两个表中联结字段相等的行on表示链接条件。
SELECT w1.id
FROM weather w1
INNER JOIN weather w2 ON DATEDIFF(w1.recordDate,w2.recordDate) = 1 and w1.Temperature > w2.Temperature
- 考点：
- 考察逻辑思维能力；
- 考察多表联结；
- 针对时间的处理语句是在业务中经常用到的，需要熟练掌握。
- 考察对不同sql数据格式处理的掌握程度。
15、销售员
表: SalesPerson
+-----------------+---------+
| Column Name | Type |
+-----------------+---------+
| sales_id | int |
| name | varchar |
| salary | int |
| commission_rate | int |
| hire_date | date |
+-----------------+---------+
sales_id 是该表的主键列。
该表的每一行都显示了销售人员的姓名和 ID ，以及他们的工资、佣金率和雇佣日期。
表: Company
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| com_id | int |
| name | varchar |
| city | varchar |
+-------------+---------+
com_id 是该表的主键列。
该表的每一行都表示公司的名称和 ID ，以及公司所在的城市。
- 方法一：使用
OUTER JOIN和NOT IN[Accepted]
用一个临时表保存向 RED 公司销售过东西的人，然后利用姓名信息将这个表和 salesperson 表建立联系。
注意： “LEFT OUTER JOIN” 也可以写作 “LEFT JOIN” 。
SELECT s.`name`
FROM SalesPerson s
WHERE s.sales_id NOT IN
(SELECT o.sales_id
FROM Orders o
LEFT JOIN Company c ON o.com_id = c.com_id
WHERE c.name = 'RED' )
16、查询近30天活跃用户数
DATEDIFF()
DATEDIFF(x1,x2) – 返回x1-x2的时间差值，其中x1,x2为column_name或时间字符串。
先过滤不在时间范围内的所有值，在考虑按时间日期分组，查出所需要的值即可。
SELECT
activity_date AS day,
COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE DATEDIFF('2019-07-27',activity_date)>=0 AND DATEDIFF('2019-07-27',activity_date)<30
GROUP BY activity_date
17、游戏玩法分析 I
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
表的主键是 (player_id, event_date)。
这张表展示了一些游戏玩家在游戏平台上的行为活动。
每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。
写一条 SQL 查询语句获取每位玩家 第一次登陆平台的日期。
查询结果的格式如下所示：
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 |
+-----------+-------------+
- 思路：
使用player_id 进行分组，日期去最小的那一条数据，按日期排序取最小的那一条记录。
SELECT player_id , MIN(event_date) as first_login FROM Activity
GROUP BY player_id HAVING MIN(event_date)
17、每天的领导和合伙人
SELECT date_id,make_name,COUNT(DISTINCT lead_id) as unique_leads , COUNT(DISTINCT partner_id) as unique_partners
FROM DailySales
GROUP BY date_id,make_name
- 思路：使用日期和make_name进行分组，在去重统计领导和伙伴id。
18、2020年最后一次登录
表: Logins
+----------------+----------+
| 列名 | 类型 |
+----------------+----------+
| user_id | int |
| time_stamp | datetime |
+----------------+----------+
(user_id, time_stamp) 是这个表的主键。
每一行包含的信息是user_id 这个用户的登录时间。
编写一个 SQL 查询，该查询可以获取在 2020 年登录过的所有用户的本年度 最后一次 登录时间。结果集 不 包含 2020 年没有登录过的用户。
返回的结果集可以按 任意顺序 排列。
查询结果格式如下例。
示例 1:
输入：
Logins 表:
+---------+---------------------+
| user_id | time_stamp |
+---------+---------------------+
| 6 | 2020-06-30 15:06:07 |
| 6 | 2021-04-21 14:06:06 |
| 6 | 2019-03-07 00:18:15 |
| 8 | 2020-02-01 05:10:53 |
| 8 | 2020-12-30 00:46:50 |
| 2 | 2020-01-16 02:49:50 |
| 2 | 2019-08-25 07:59:08 |
| 14 | 2019-07-14 09:00:00 |
| 14 | 2021-01-06 11:59:59 |
+---------+---------------------+
输出：
+---------+---------------------+
| user_id | last_stamp |
+---------+---------------------+
| 6 | 2020-06-30 15:06:07 |
| 8 | 2020-12-30 00:46:50 |
| 2 | 2020-01-16 02:49:50 |
+---------+---------------------+
解释：
6号用户登录了3次，但是在2020年仅有一次，所以结果集应包含此次登录。
8号用户在2020年登录了2次，一次在2月，一次在12月，所以，结果集应该包含12月的这次登录。
2号用户登录了2次，但是在2020年仅有一次，所以结果集应包含此次登录。
14号用户在2020年没有登录，所以结果集不应包含。
- 思考：将日期使用
date_format转换为字符串比较是否是包含2020，在按用户id分组取最大的日期。
SELECT user_id, MAX(time_stamp) as last_stamp FROM Logins AS l
WHERE date_format(time_stamp, '%y%y') LIKE '2020'
GROUP BY user_id
- 拓展
date_format的使用
| 格式 | 描述 |
|---|---|
| %a | 缩写星期名 |
| %b | 缩写月名 |
| %c | 月，数值 |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值（00-31） |
| %e | 月的天，数值（0-31） |
| %f | 微秒 |
| %H | 小时（00-23） |
| %h | 小时（01-12） |
| %I | 小时（01-12） |
| %i | 分钟，数值（00-59） |
| %j | 年的天（001-366） |
| %k | 小时（0-23） |
| %l | 小时（1-12） |
| %M | 月名 |
| %m | 月，数值（00-12） |
| %p | AM或PM |
| %r | 时间，12-小时（hh:mm:ssAM或PM） |
| %S | 秒（00-59） |
| %s | 秒（00-59） |
| %T | 时间,24-小时（hh:mm:ss） |
| %U | 周（00-53）星期日是一周的第一天 |
| %u | 周（00-53）星期一是一周的第一天 |
| %V | 周（01-53）星期日是一周的第一天，与%X使用 |
| %v | 周（01-53）星期一是一周的第一天，与%x使用 |
| %W | 星期名 |
| %w | 周的天（0=星期日,6=星期六） |
| %X | 年，其中的星期日是周的第一天，4位，与%V使用 |
| %x | 年，其中的星期一是周的第一天，4位，与%v使用 |
| %Y | 年，4位 |
| %y | 年，2位 |
19、查找每个员工花费的总时间
表: Employees
+-------------+------+
| Column Name | Type |
+-------------+------+
| emp_id | int |
| event_day | date |
| in_time | int |
| out_time | int |
+-------------+------+
(emp_id, event_day, in_time) 是这个表的主键。
该表显示了员工在办公室的出入情况。
event_day 是此事件发生的日期，in_time 是员工进入办公室的时间，而 out_time 是他们离开办公室的时间。
in_time 和 out_time 的取值在1到1440之间。
题目保证同一天没有两个事件在时间上是相交的，并且保证 in_time 小于 out_time。
编写一个SQL查询以计算每位员工每天在办公室花费的总时间（以分钟为单位）。 请注意，在一天之内，同一员工是可以多次进入和离开办公室的。 在办公室里一次进出所花费的时间为out_time 减去 in_time。
返回结果表单的顺序无要求。
查询结果的格式如下：
Employees table:
+--------+------------+---------+----------+
| emp_id | event_day | in_time | out_time |
+--------+------------+---------+----------+
| 1 | 2020-11-28 | 4 | 32 |
| 1 | 2020-11-28 | 55 | 200 |
| 1 | 2020-12-03 | 1 | 42 |
| 2 | 2020-11-28 | 3 | 33 |
| 2 | 2020-12-09 | 47 | 74 |
+--------+------------+---------+----------+
Result table:
+------------+--------+------------+
| day | emp_id | total_time |
+------------+--------+------------+
| 2020-11-28 | 1 | 173 |
| 2020-11-28 | 2 | 30 |
| 2020-12-03 | 1 | 41 |
| 2020-12-09 | 2 | 27 |
+------------+--------+------------+
雇员 1 有三次进出: 有两次发生在 2020-11-28 花费的时间为 (32 - 4) + (200 - 55) = 173, 有一次发生在 2020-12-03 花费的时间为 (42 - 1) = 41。
雇员 2 有两次进出: 有一次发生在 2020-11-28 花费的时间为 (33 - 3) = 30, 有一次发生在 2020-12-09 花费的时间为 (74 - 47) = 27。
- 考点：SUM函数 分组使用
# SELECT event_day AS `day` , emp_id ,SUM(out_time) - SUM(in_time) as total_time
# FROM Employees
# GROUP BY emp_id,event_day
SELECT event_day AS `day` , emp_id ,SUM(out_time - in_time) as total_time
FROM Employees
GROUP BY emp_id,event_day
20、 股票的资本损益
Stocks 表：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| stock_name | varchar |
| operation | enum |
| operation_day | int |
| price | int |
+---------------+---------+
(stock_name, day) 是这张表的主键
operation 列使用的是一种枚举类型，包括：('Sell','Buy')
此表的每一行代表了名为 stock_name 的某支股票在 operation_day 这一天的操作价格。
保证股票的每次'Sell'操作前，都有相应的'Buy'操作。
编写一个SQL查询来报告每支股票的资本损益。
股票的资本损益是一次或多次买卖股票后的全部收益或损失。
以任意顺序返回结果即可。
- 方法一：Case语句 SUM 函数的应用 Group BY
SELECT stock_name,
SUM(CASE WHEN operation = 'Sell' THEN price ELSE 0 END) -
SUM(CASE WHEN operation = 'Buy' THEN price ELSE 0 END) AS capital_gain_loss
FROM Stocks
GROUP BY stock_name;
- 方法二：组合查询，先按操作类型分组求和在连表相减
SELECT te1.stock_name,(te1.sellall - tem.buyall) as 'capital_gain_loss'
FROM
(SELECT s1.stock_name ,SUM(price) as sellall
FROM Stocks as s1
WHERE operation = 'Sell'
GROUP BY s1.stock_name ) as te1
LEFT JOIN (SELECT s2.stock_name , SUM(price) as buyall
FROM Stocks as s2
WHERE operation = 'Buy'
GROUP BY s2.stock_name ) as tem ON tem.stock_name = te1.stock_name
21、排名靠前的旅行者
表：Users
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表单主键。
name 是用户名字。
表：Rides
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| user_id | int |
| distance | int |
+---------------+---------+
id 是该表单主键。
user_id 是本次行程的用户的 id, 而该用户此次行程距离为 distance 。
写一段 SQL , 报告每个用户的旅行距离。
返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。
- 方法一：LEFT JOIN ON的使用 GROUP BY ，Order BY的使用
SELECT u.name, (CASE WHEN distance is NULL THEN 0 ELSE SUM(distance) END) as 'travelled_distance' FROM Rides AS r
right JOIN Users AS u ON r.user_id = u.id
GROUP BY r.user_id
ORDER BY travelled_distance DESC ,`name` ASC;
- 方式二：
先用姓名升序排列，在对集合距离进行降序排列
SELECT name,IF(aa.distance IS NULL,0, aa.distance) travelled_distance
FROM(
SELECT name,sum(distance)as distance
FROM Users u LEFT JOIN Rides r
ON r.user_id = u.id
GROUP BY u.id
ORDER BY u.name
) as aa
ORDER BY aa.distance DESC
SELECT name,IFNULL(sum(distance),0) travelled_distance
FROM Users u LEFT JOIN Rides r
ON r.user_id = u.id
GROUP BY u.id
ORDER BY travelled_distance DESC, name
22、市场分析 I
- 方式一：小白子查询
SELECT u1.user_id as buyer_id ,u1.join_date,IF(tem.orders_in_2019 IS NOT NULL,tem.orders_in_2019,0) as orders_in_2019
FROM users u1
LEFT JOIN
(SELECT user_id,join_date, (CASE WHEN DATE_FORMAT(order_date,"%Y") LIKE '2019%' THEN COUNT(*) ELSE 0 END) AS orders_in_2019 FROM users as u
LEFT JOIN Orders as o ON o.buyer_id = u.user_id
LEFT JOIN Items AS i ON o.item_id = i.item_id
WHERE DATE_FORMAT(order_date,"%Y") LIKE '2019'
GROUP BY user_id) AS tem
ON u1.user_id = tem.user_id
- 方法二：进阶
select Users.user_id as buyer_id, join_date, ifnull(UserBuy.cnt, 0) as orders_in_2019
from Users
left join (
select buyer_id, count(order_id) cnt
from Orders
where order_date between '2019-01-01' and '2019-12-31'
group by buyer_id
) UserBuy
on Users.user_id = UserBuy.buyer_id
3104
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
