|
13、解释一下 SQL 中区别类型的连接
JOIN是 SQL 的子句，按照表之间一起的列，能够连接两个或多个表，用于合并表及检索数据。最平常的连接类型如下。
内连接（Inner Join） ：内连接是最平常的，用于返回两个或多个表中满足 JOIN 要求的所有行。
左连接（Left Join）：这种连接在右表满足 JOIN 要求时返回左表的行。
右连接（Right Join） ：类似于左连接，但在左表满足 JOIN 要求时返回右表的行。
全连接（Full Join） ：全连接在任何一个表中有匹配时返回左表和右表的所有行。
14、什么是主键（PRIMARY KEY）？
主键是独一标识每条记录的约束要求。主键不可有 NULL 值，并且所有值必须独一。
一个表只能有一个主键，但主键能够由单个或多个列构成。
15、什么是约束（constraints）？
SQL 中的约束是能够应用于表中特定数据类型的规则，用于限制特定列中的数据类型。SQL 中平常的约束类型如下。
NOT NULL - 禁止列中存储空值。
UNIQUE- 规定列中的值必须独一。主键运用 UNIQUE 约束。
PRIMARY KEY - 指定哪个字段是主键。
FOREIGN KEY - 独一标识另一个表中的一行。
16、SQL 中的 DELETE 和 TRUNCATE 语句有什么区别？
DELETE 用于从表中删除特定数据。该语句是 DML 命令，比 TRUNCATE 慢。
TRUNCATE 是 DDL命令，用于删除表中的所有行。
两者的重点区别是运用 DELETE 后能够回滚数据。
17、什么是查找优化？
低效的 SQL 查找会耗尽数据库资源，引起性能下降和服务中断。
查找优化是使 SQL 查找更有效的过程。
查找的效率越高，输出的速度就越快，并最大限度地减少对数据库的影响。
18、给定以下表格，选取最少有 10 名员工的前三个分部，并按照其员工中年薪超过 10 万美元的百分比进行排名。
employees 表
departments 表
输出结果
提示
将其分解成区别的要求子句。
前 3 个分部
年薪超过 10 万美元的员工百分比
分部最少有 10 名员工
答案
首要，运用 JOIN 连接 employees 和 departments 表。以获取每一个员工的工资和分部。 1SELECT
* 2FROM departments AS
d 3LEFT JOIN employees AS
e 4 ON
d.id = e.department_id
而后，运用 GROUP BY子句对分部进行聚合，并运用 HAVING 子句过滤掉员工少于 10 人的分部。 1SELECT2
d.name 3FROM departments AS
d 4LEFT JOIN employees AS
e 5 ON
d.id = e.department_id 6GROUP BY
d.name 7HAVING COUNT(*) >= 10
为何运用 HAVING 子句而不是 WHERE 子句？
这是由于 HAVING 能够在 GROUP BY 之后应用过滤，不必将原始查找包装在子查找中。
最后，运用 CASE WHEN 子句和AVG 函数计算百分比。 1SELECT AVG(CASE WHEN salary > 1000002 THEN 1 ELSE 0 END) AS
percentage_over_100k
AVG() 函数将表达式的每一个值相加，再将其除以值的总数。
CASE WHEN 的表达式针对薪水超过 10 万美元的员工返回 1，针对其他员工返回 0。
完整代码如下。 1SELECT AVG(CASE WHEN salary > 100000 2 THEN 1 ELSE 0 END) AS
percentage_over_100k 3 , d.name as
department_name 4 , COUNT(*) AS
number_of_employees 5FROM departments AS
d 6LEFT JOIN employees AS
e 7 ON
d.id = e.department_id 8GROUP BY
d.name 9HAVING COUNT(*) >= 1010ORDER BY 1 DESC11LIMIT 3
19、给定一个用户表，编写 SQL 查找，获取每日新增用户的累计数，每月重新起始计数。
users 表
输出：
提示
这个问题乍一看似乎能够经过 COUNT(*)，并按日期分组处理。或运用累积分布函数亦能够处理。
但实质上，要按特定的月日间隔进行分组，在月底将用户的统计数据重置为 0。
这种方式的优良在于能够得到留存率表，用于比较每一个月的累积用户数。
答案 1
WITH daily_total AS ( 2 SELECT 3 DATE(created_at) AS
dt 4 , COUNT(*) AS
cnt 5 FROM users 6 GROUP BY 1 7
) 8 9SELECT10t.dtAS date11 , SUM(u.cnt) AS
monthly_cumulative 12FROM daily_total AS
t 13LEFT JOIN daily_total AS
u 14 ON
t.dt >= u.dt 15 AND MONTH(t.dt) = MONTH
(u.dt) 16 AND YEAR(t.dt) = YEAR
(u.dt) 17GROUP BY 1
20、给定一个包括用户订阅起始和结束日期的表，查找每一个用户的订阅日期范围是不是与其他用户重叠
subscriptions 表
示例
输出结果
提示
将其视为自连接。要连接 user_id 和 start_date 字段，但要重视不要让用户与自己匹配。
答案 1SELECT2
s1.user_id 3 , MAX(CASE WHEN s2.user_id IS NOT NULL THEN 1 ELSE 0 END) AS
overlap 4FROM subscriptions AS
s1 5LEFT JOIN subscriptions AS
s2 6 ON
s1.user_id != s2.user_id 7 AND
s1.start_date <= s2.end_date 8 AND
s1.end_date >= s2.start_date 9GROUP BY 1
21、给定学生及其 SAT 考试成绩的表格，查找测试成绩最接近的两个学生及其成绩差
倘若有多个学生的最小成绩差相同，选取字母次序靠前的学生组合。
scores 表
输入
输出结果
提示
给定的问题引用了一个仅包括两列的表格，此时，必须对同一个表进行自引用，亦便是将之视为包括相同值的两个区别表格。
答案 1
WITH ScoreDifferences AS ( 2 SELECT 3 a.student AS
one_student, 4 b.student AS
other_student, 5 ABS(a.score - b.score)AS
score_diff 6 FROM 7
scores a 8 JOIN 9
scores b 10 ON11
a.id < b.id 12
) 13SELECT14
one_student, 15
other_student, 16
score_diff 17FROM18
ScoreDifferences 19ORDER BY20 score_diff ASC
, 21 one_student ASC
, 22 other_student ASC23LIMIT 1
;
22、给定两个表，一个包括用户的基本信息及其所在社区，另一个包括社区信息。查找无用户的社区
users 表
neighborhoods 表
输出
 fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)
提示
要找到无用户居住的社区，即一个表中的数据在另一个表中不存在。
答案 1SELECT
n.id, n.name 2FROM
neighborhoods n 3LEFT JOIN users u ON
n.id = u.neighborhood_id 4WHERE u.id IS NULL
;
23、给定交易表和制品表，查找制品 ID、制品价格和制品价格高于所有平均交易价格的制品的平均交易价格
transactions 表
products 表
提示:
需要找到所有交易的平均价格。
交易总价是商品价格X数量。
因此呢，需要编写子查找获取所有交易的平均值。
答案 1
WITH avg_transaction_price AS ( 2 SELECT AVG(p.price * t.quantity)AS
avg_price 3 FROM transactions AS
t 4 JOIN products AS p ON
t.product_id = p.id 5
) 6SELECT 7 p.id AS
product_id, 8 p.price AS
product_price, 9 (SELECT AVG
(p.price * t.quantity) 10 FROM transactions AS
t 11 WHERE
t.product_id = p.id 12 ) AS
avg_transaction_price 13FROM products AS
p 14WHERE p.price > (SELECT avg_price FROM
avg_transaction_price);
24、给定 `transactions` 和 `products` 两个表。假设 `transactions` 表包括超过十亿行用户购买商品的记录
查找同一用户购买的配对商品。例如，酒和开瓶器、薯片和啤酒等。
需求查找前 5 对配对商品及其名叫作。
重视
为了满足测试用例，P1 应该是字母次序靠前的商品。
transactions 表
products 表
输出结果
提示
需要将其分成几个过程。
首要，要找到用户在同一时间购买两个或更加多制品的所有实例。
而后，利用 user_id 和 created_at 进行查找。
答案 1SELECT 2 LEAST(p1.name, p2.name) AS
 1, 3 GREATEST(p1.name, p2.name) AS
2, 4 COUNT(*) AS count 5FROM 6
transactions t1 7JOIN 8
transactions t2 9 ON t1.user_id = t2.user_id AND t1.created_at = t2.created_at AND
t1.id < t2.id 10JOIN
products p1 11 ON
t1.product_id = p1.id 12JOIN
products p2 13 ON
t2.product_id = p2.id 14GROUP BY15
1, 2 16ORDER BY17 count DESC18LIMIT 5
;
25、查找每一个用户每日播放的歌曲数量
给定 song_plays 表，记录用户播放歌曲的数据。
查找每一个用户每日播放的歌曲数量。
重视
倘若某个用户在同一天播放了同一首歌两次，计数应该是两次。
答案首要，创建 song_plays 表。 1CREATE TABLE
song_plays ( 2 id INT RIMARY KEY
, 3 user_id INT
, 4 song_id INT
, 5
played_at DATETIME 6
);
而后，插进模拟数据。 1INSERT INTO song_plays (id, user_id, song_id, played_at) VALUES 2(1, 1, 101, 2024-07-01 10:00:00
), 3(2, 1, 102, 2024-07-01 11:00:00
), 4(3, 2, 101, 2024-07-01 12:00:00
), 5(4, 1, 101, 2024-07-01 12:30:00
), 6(5, 2, 103, 2024-07-02 09:00:00
), 7(6, 1, 104, 2024-07-02 10:00:00
), 8(7, 1,101, 2024-07-02 11:00:00
), 9(8, 2, 102, 2024-07-02 11:30:00
), 10(9, 2, 101, 2024-07-02 12:00:00
);
最后，SQL 查找。 1SELECT 2
user_id, 3 DATE(played_at) AS
play_date, 4 COUNT(*)AS
song_count 5FROM 6
song_plays 7GROUP BY 8
user_id, 9 DATE
(played_at) 10ORDER BY11
user_id, 12
play_date;
| 
发表于 2024-9-3 18:45:24
