1. 首页首页
  2. 编程基础编程基础

SQL面试题

编程基础 • • 阅读 14

SQL面试题1 介绍 exists 和 in 都有过滤功能 他俩最大的差别就是 in 引导的子句只能对一个字段进行限制 比如 对 id 字段进行限定 select fromAwheresi 1 2 3 但是如果我们想对多个字段进行限制 使用 in 就不合适了 例如 select fromAwhere sid tid in selectsid tidfromB 不过很可惜 上面的 andnotexists

sql

1.SQL的执行顺序

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. SUM、AVG、MAX、MIN、COUNT
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. LIMIT / OFFSET

1.1 编写顺序 vs 执行顺序

SELECT d.department_name, e.employee_name, MAX(e.salary) as highest_salary FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.salary > 50000 GROUP BY d.department_name HAVING MAX(e.salary) > 60000 ORDER BY highest_salary DESC LIMIT 5;
  1. FROM employees e
  2. JOIN departments d ON e.department_id = d.department_id
  3. WHERE e.salary > 5000
  4. GROUP BY d.department_name
  5. HAVING COUNT(e.employee_id) > 2
  6. SELECT d.department_name, COUNT(e.employee_id) AS employee_count
  7. ORDER BY employee_count DESC
  8. LIMIT 3

1.1 ON过滤 vs WHERE过滤

student (学生班级表)

name class
张三 A
李四 A
王五 B

score (学生分数表)

name score
张三 100
李四 90
王五 80

需求: 返回A班学生的成绩

  • 使用LEFT JOIN查询
SELECT stu.name, stu.class, sc.name, sc.score FROM student stu LEFT JOIN score sc ON stu.name = sc.name AND stu.class = 'A'

LEFT JOIN score sc ON stu.name = sc.name AND stu.class = 'A'
只有当 student 表中的 name 与 score 表中的 name 匹配，并且 student 表中的 class 为 ‘A’ 时，才会从 score 表中获取对应的记录。

name class name score
张三 A 张三 100
李四 A 李四 90
王五 B null null
  • 使用WHERE查询
SELECT stu.name, stu.class, sc.name, sc.score FROM student stu LEFT JOIN score sc ON stu.name = sc.name WHERE stu.class = 'A'

WHERE 子句中限制了只返回 class = ‘A’ 的记录，因此最终结果只包含符合条件的学生。

name class name score
张三 A 张三 100
李四 A 李四 90

2.EXISTS、NOT EXISTS

customers (顾客表)

customer_id customer_name
1 Alice
2 Bob
3 Charlie

orders (订单表)

order_id customer_id order_total
1 1 100
2 1 150
3 2 200

2.1 EXISTS

EXISTS用于判断子查询是否返回至少一行记录。通常用于需要验证某个条件是否成立的场景。

SELECT column1, column2 FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE condition); 
SELECT customer_name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

工作原理:

  1. 执行子查询: 当SQL查询包含EXISTS时，数据库首先执行子查询。
  2. 检查返回的记录
    • 如果子查询返回至少一行记录，EXISTS的结果为TRUE。
    • 如果子查询返回空集（没有记录），EXISTS的结果为FALSE。
  3. 返回外部查询结果
    • 只有当EXISTS为TRUE时，外部查询才会返回与当前行匹配的记录。
    • 数据库在处理时会在找到第一个匹配的记录后立即停止进一步的搜索，以提高性能。

性能优化
EXISTS 通常效率较高，因为它一旦找到匹配记录就停止执行，不需要遍历整个表。这使得在处理大数据集时，EXISTS 的使用尤其高效。

2.2 NOT EXISTS

NOT EXISTS用于检查子查询是否不返回任何记录。适合用于查找不满足特定条件的情况。

SELECT column1, column2 FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE condition); 
SELECT customer_name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
  1. 执行子查询: 当SQL查询包含EXISTS时，数据库首先执行子查询。
  2. 检查返回的记录
    • 如果子查询返回任何记录，NOT EXISTS的结果为FALSE。
    • 如果子查询返回空集（没有记录），NOT EXISTS的结果为TRUE。
  3. 返回外部查询结果
    • 只有当NOT EXISTS为TRUE时，外部查询才会返回与当前行匹配的记录。
    • 数据库在找到第一个匹配的记录时会立即停止进一步的搜索，以提高性能。

性能优化
NOT EXISTS在找到第一条不匹配的记录后也会停止执行。

3.CASE

3.1 语法

CASE语句有两种主要形式：简单CASE和搜索CASE。

  • 简单CASE
    • 场景: 直接值比较
CASE department_id WHEN 1 THEN 'Sales' WHEN 2 THEN 'Marketing' WHEN 3 THEN 'HR' ELSE 'Other' END AS department_name
  • 搜索CASE
    • 场景: 当需要根据多个条件进行判断时
CASE WHEN salary < 3000 THEN 'Low' WHEN salary BETWEEN 3000 AND 6000 THEN 'Medium' WHEN salary > 6000 THEN 'High' ELSE 'Not Specified' END AS salary_level

3.2 应用场景

employee_id name salary department_id performance_score
1 Alice 5000 101 80
2 Bob 3000 102 65
3 Charlie 7000 101 95
4 David 4000 103 75
5 Eve 3500 102 85

1.条件计算
使用 CASE 语句来根据条件计算一个新的列，例如根据 performance_score 生成员工的绩效等级：

SELECT name, performance_score, CASE WHEN performance_score >= 90 THEN '优秀' WHEN performance_score >= 75 AND performance_score < 90 THEN '良好' ELSE '一般' END AS performance_level FROM employees;
name performance_score performance_level
Alice 80 良好
Bob 65 一般
Charlie 95 优秀
David 75 良好
Eve 85 良好

2.数据分组
可以使用 CASE 语句来分组数据，例如按照 salary 对员工进行收入分组：

SELECT CASE WHEN salary >= 6000 THEN '高收入' WHEN salary BETWEEN 3000 AND 5999 THEN '中等收入' ELSE '低收入' END AS income_level, COUNT(*) AS employee_count FROM employees GROUP BY income_level;
income_level employee_count
高收入 1
中等收入 4

GROUP BY在SELECT之前执行，为什么可以在GROUP BY中使用SELECT中定义的别名（例如 income_level）?
这是 SQL 语言设计的一个语法糖特性。尽管在SQL的逻辑上，GROUP BY 早于 SELECT 执行，但 SQL 允许开发者在 GROUP BY 中使用 SELECT 中定义的别名，以便减少重复编写复杂表达式。大多数主流数据库（如 MySQL、PostgreSQL、SQL Server 等）都支持这种用法。

本质上等于下面的SQL

SELECT CASE WHEN salary >= 6000 THEN '高收入' WHEN salary BETWEEN 3000 AND 5999 THEN '中等收入' ELSE '低收入' END AS income_level, COUNT(*) AS employee_count FROM employees GROUP BY CASE WHEN salary >= 6000 THEN '高收入' WHEN salary BETWEEN 3000 AND 5999 THEN '中等收入' ELSE '低收入' END;

3.排序
可以在 ORDER BY 中使用 CASE 语句进行自定义排序。假设我们根据 department_id 进行排序，但希望某个特定的部门（例如 101）排在最前面：

SELECT name, department_id FROM employees ORDER BY CASE WHEN department_id = 101 THEN 1 ELSE 2 END, name;
name department_id
Alice 101
Charlie 101
Bob 102
Eve 102
David 103

4.更新数据
可以在 UPDATE 语句中使用 CASE 语句，根据条件更新某个字段。例如，根据 performance_score 更新员工的薪资奖励：

UPDATE employees SET salary = salary + CASE WHEN performance_score >= 90 THEN 1000 WHEN performance_score >= 75 AND performance_score < 90 THEN 500 ELSE 0 END;

3.3 难度升级

1.按照国家和性别进行分组:国家–男–女

国家（country） 性别（sex） 人口（population）
中国 1 340
中国 2 260
美国 1 45
美国 2 55
加拿大 1 51
加拿大 2 49
英国 1 40
英国 2 60 
SELECT country, -- 原理:分组后,是对组进行操作,第二列的意思就是取此组中,sex=1的记录的population的总和 SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) as 男, SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) as 女 FROM sex_count GROUP BY country;
国家 男 女
中国 340 260
美国 45 55
加拿大 51 49
英国 40 60

2.根据员工销售额分类和排名

employee_id sales
1 500
2 4000
3 3000
4 8000
5 2000 
SELECT employee_id, sales, CASE WHEN sales > (SELECT AVG(sales) FROM employees) THEN CASE WHEN sales >= (SELECT MAX(sales) FROM employees) THEN 'Top Performer' WHEN sales >= (SELECT AVG(sales) + STDDEV(sales) FROM employees) THEN 'High Performer' ELSE 'Above Average' END ELSE CASE WHEN sales <= (SELECT MIN(sales) FROM employees) THEN 'Lowest Performer' ELSE 'Below Average' END END AS performance_category FROM employees;
employee_id sales performance_category
1 500 Lowest Performer
2 4000 High Performer
3 3000 Above Average
4 8000 Top Performer
5 2000 Below Average

3.按销售额百分比进行分类

sales_id sales_amount
1 1000
2 5000
3 10000
4 20000
5 3000 
SELECT sales_id, sales_amount, CASE WHEN sales_amount >= (SELECT PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY sales_amount) FROM sales_data) THEN 'Top 10%' WHEN sales_amount >= (SELECT PERCENTILE_CONT(0.80) WITHIN GROUP (ORDER BY sales_amount) FROM sales_data) THEN 'Top 20%' WHEN sales_amount >= (SELECT PERCENTILE_CONT(0.70) WITHIN GROUP (ORDER BY sales_amount) FROM sales_data) THEN 'Top 30%' ELSE 'Below 30%' END AS sales_category FROM sales_data;
sales_id sales_amount sales_category
1 1000 Below 30%
2 5000 Below 30%
3 10000 Top 30%
4 20000 Top 10%
5 3000 Below 30%

4.按商品类别生成销售额的透视表

customer_id product_type order_value
1 Electronics 500
2 Clothing 200
3 Electronics 1500
1 Clothing 300
2 Electronics 700 
SELECT customer_id, SUM(CASE WHEN product_type = 'Electronics' THEN order_value ELSE 0 END) AS electronics_sales, SUM(CASE WHEN product_type = 'Clothing' THEN order_value ELSE 0 END) AS clothing_sales FROM orders GROUP BY customer_id;
customer_id electronics_sales clothing_sales
1 500 300
2 700 200
3 1500 0

5.根据订阅到期时间更新用户的状态

subscription_id expiration_date status
1 2024-01-01 Active
2 2024-12-01 Active
3 2023-09-01 Active
4 2024-11-15 Active 
UPDATE subscriptions SET status = CASE WHEN expiration_date < CURRENT_DATE THEN 'Expired' WHEN expiration_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days' THEN 'Expiring Soon' ELSE 'Active' END WHERE status != 'Canceled';
subscription_id expiration_date status
1 2024-01-01 Active
2 2024-12-01 Active
3 2023-09-01 Expired
4 2024-11-15 Expiring Soon
今天的文章 SQL面试题分享到此就结束了，感谢您的阅读。
编程小号
上一篇 2024-12-30 13:30
下一篇 2024-12-30 13:27

相关推荐

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/bian-cheng-ji-chu/92174.html