MySQL实战面试题(附案例答案+建表语句+模拟数据+案例深度解析)，练完直接碾压面试官

小白的大数据之旅

发布于 2024-11-20 18:39:11

14000

代码可运行

文章被收录于专栏：最新Python入门基础合集最新Python入门基础合集

运行总次数：0

代码可运行

案例1

建表语句与模拟数据

用户表 users

CREATE TABLE users (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    username VARCHAR(50) NOT NULL,  
    email VARCHAR(100) NOT NULL UNIQUE,  
    signup_date DATE NOT NULL  
);  
  
INSERT INTO users (username, email, signup_date) VALUES  
('Alice', 'alice@example.com', '2023-01-01'),  
('Bob', 'bob@example.com', '2023-02-15'),  
('Charlie', 'charlie@example.com', '2023-01-10'),  
('David', 'david@example.com', '2023-03-01');

订单表 orders

CREATE TABLE orders (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    user_id INT,  
    order_date DATE NOT NULL,  
    amount DECIMAL(10, 2) NOT NULL,  
    FOREIGN KEY (user_id) REFERENCES users(id)  
);  
  
INSERT INTO orders (user_id, order_date, amount) VALUES  
(1, '2023-01-15', 100.00),  
(2, '2023-02-20', 150.00),  
(1, '2023-03-05', 75.00),  
(3, '2023-01-20', 90.00);

题目一：查询2023年1月注册的所有用户。

答案：

SELECT * FROM users WHERE YEAR(signup_date) = 2023 AND MONTH(signup_date) = 1;

解析：

使用YEAR()和MONTH()函数从signup_date字段中提取年份和月份，并匹配给定的条件。

题目二：查询每个用户的订单总数和总金额。

答案：

SELECT u.username, COUNT(o.id) AS order_count, SUM(o.amount) AS total_amount  
FROM users u  
LEFT JOIN orders o ON u.id = o.user_id  
GROUP BY u.id, u.username;

解析：

通过左连接users和orders表，按用户分组，并使用COUNT()和SUM()函数计算每个用户的订单数量和总金额。

题目三：查询没有下过订单的用户。

答案：

SELECT * FROM users  
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);

或者

SELECT * FROM users  
LEFT JOIN orders ON users.id = orders.user_id  
WHERE orders.id IS NULL;

解析：

第一种方法使用子查询找出所有下过订单的用户ID，然后在主查询中排除这些ID。第二种方法通过左连接users和orders表，并检查orders.id是否为NULL来找出没有订单的用户。

题目四：查询2023年2月订单金额最高的用户及其订单金额。

答案：

SELECT u.username, MAX(o.amount) AS max_amount  
FROM orders o  
JOIN users u ON o.user_id = u.id  
WHERE YEAR(o.order_date) = 2023 AND MONTH(o.order_date) = 2  
GROUP BY u.id, u.username;

注意：这个查询实际上返回的是每个符合条件用户的最大订单金额，但题目表述可能暗示只找一个用户。如果需要确切地只找一个用户（如果有多个用户并列最高），则可能需要更复杂的查询或使用窗口函数（如果MySQL版本支持）。

解析：

首先通过日期条件筛选出2023年2月的订单，然后按用户分组，并使用MAX()函数找出每个用户的最大订单金额。

案例2

建表语句与模拟数据

商品表 products

CREATE TABLE products (  
    product_id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(100) NOT NULL,  
    category VARCHAR(50) NOT NULL,  
    price DECIMAL(10, 2) NOT NULL  
);  
  
INSERT INTO products (name, category, price) VALUES  
('Laptop', 'Electronics', 999.99),  
('Smartphone', 'Electronics', 599.99),  
('Book', 'Books', 29.99),  
('Coffee Mug', 'Kitchenware', 12.99),  
('T-Shirt', 'Clothing', 34.99);

销售记录表 sales

CREATE TABLE sales (  
    sale_id INT AUTO_INCREMENT PRIMARY KEY,  
    product_id INT,  
    quantity INT NOT NULL,  
    sale_date DATE NOT NULL,  
    FOREIGN KEY (product_id) REFERENCES products(product_id)  
);  
  
INSERT INTO sales (product_id, quantity, sale_date) VALUES  
(1, 2, '2023-04-01'),  
(2, 5, '2023-04-02'),  
(3, 3, '2023-04-01'),  
(4, 1, '2023-04-03'),  
(1, 1, '2023-04-04');

题目一：查询每个商品类别的商品数量。

答案：

SELECT category, COUNT(*) AS product_count  
FROM products  
GROUP BY category;

解析：

通过GROUP BY子句按category字段分组，并使用COUNT(*)函数计算每个类别的商品数量。

题目二：查询2023年4月份销售数量最多的商品名称及其销售数量。

答案：

SELECT p.name, SUM(s.quantity) AS total_quantity  
FROM sales s  
JOIN products p ON s.product_id = p.product_id  
WHERE YEAR(s.sale_date) = 2023 AND MONTH(s.sale_date) = 4  
GROUP BY p.product_id, p.name  
ORDER BY total_quantity DESC  
LIMIT 1;

解析：

首先通过连接sales和products表获取销售记录的商品信息，然后使用WHERE子句筛选出2023年4月份的销售记录。接着按商品分组并计算销售数量，最后通过ORDER BY和LIMIT子句找出销售数量最多的商品。

题目三：查询没有销售记录的商品。

答案：

SELECT * FROM products  
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM sales);

或者

SELECT p.*  
FROM products p  
LEFT JOIN sales s ON p.product_id = s.product_id  
WHERE s.sale_id IS NULL;

解析：

第一种方法使用子查询找出有销售记录的商品ID，然后在主查询中排除这些ID。第二种方法通过左连接products和sales表，并检查sales.sale_id是否为NULL来找出没有销售记录的商品。

题目四：查询每个商品的销售总额，并按销售总额降序排列。

答案：

SELECT p.name, SUM(s.quantity * p.price) AS total_sales  
FROM sales s  
JOIN products p ON s.product_id = p.product_id  
GROUP BY p.product_id, p.name  
ORDER BY total_sales DESC;

解析：

通过连接sales和products表，计算每个商品的销售总额（数量乘以单价），然后按销售总额降序排列。

案例3(Mysql 8.0+版本)

建表语句与模拟数据

员工表 employees

CREATE TABLE employees (  
    employee_id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(100) NOT NULL,  
    department_id INT,  
    salary DECIMAL(10, 2) NOT NULL,  
    hire_date DATE NOT NULL,  
    FOREIGN KEY (department_id) REFERENCES departments(department_id)  
);  
  
INSERT INTO employees (name, department_id, salary, hire_date) VALUES  
('John Doe', 1, 70000.00, '2020-01-01'),  
('Jane Smith', 2, 65000.00, '2020-02-15'),  
('Alice Johnson', 1, 68000.00, '2020-03-01'),  
('Bob Brown', 3, 85000.00, '2019-12-15'),  
('Charlie Davis', 2, 62000.00, '2020-01-10');

部门表 departments

CREATE TABLE departments (  
    department_id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(100) NOT NULL  
);  
  
INSERT INTO departments (name) VALUES  
('Engineering'),  
('Marketing'),  
('Finance');

项目表 projects

CREATE TABLE projects (  
    project_id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(100) NOT NULL,  
    manager_id INT,  
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)  
);  
  
INSERT INTO projects (name, manager_id) VALUES  
('Project X', 1),  
('Project Y', 2),  
('Project Z', 3);

题目一：查询每个部门薪资最高的员工信息（包括员工姓名、部门名称和薪资）。

答案：

（使用子查询）：

SELECT e.name, d.name AS department_name, e.salary  
FROM employees e  
JOIN departments d ON e.department_id = d.department_id  
WHERE (e.department_id, e.salary) IN (  
    SELECT department_id, MAX(salary)  
    FROM employees  
    GROUP BY department_id  
);

注意：上述查询在某些数据库系统中可能无法直接工作，因为它依赖于子查询返回的结果集与主查询中的多个列进行匹配。在MySQL中，更稳妥的方法是使用窗口函数（如果可用）或进行更复杂的自连接。

使用窗口函数的版本（MySQL 8.0+）

WITH RankedEmployees AS (  
    SELECT e.name, d.name AS department_name, e.salary,  
           RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rank  
    FROM employees e  
    JOIN departments d ON e.department_id = d.department_id  
)  
SELECT name, department_name, salary  
FROM RankedEmployees  
WHERE rank = 1;

题目二：查询每个项目及其经理的姓名，以及该经理所在部门的名称。

答案：

SELECT p.name AS project_name, e.name AS manager_name, d.name AS department_name  
FROM projects p  
JOIN employees e ON p.manager_id = e.employee_id  
JOIN departments d ON e.department_id = d.department_id;

题目三：查询每个部门的平均薪资，以及该部门薪资高于公司平均薪资的员工数量。

答案：

WITH AvgSalary AS (  
    SELECT AVG(salary) AS company_avg_salary  
    FROM employees  
),  
DeptAvg AS (  
    SELECT department_id, AVG(salary) AS dept_avg_salary  
    FROM employees  
    GROUP BY department_id  
),  
DeptDetails AS (  
    SELECT d.name AS department_name,  
           da.dept_avg_salary,  
           as1.company_avg_salary,  
           COUNT(*) FILTER (WHERE e.salary > as1.company_avg_salary) AS above_avg_count  
    FROM departments d  
    JOIN DeptAvg da ON d.department_id = da.department_id  
    JOIN AvgSalary as1  
    JOIN employees e ON d.department_id = e.department_id  
    GROUP BY d.department_id, da.dept_avg_salary, as1.company_avg_salary  
)  
SELECT * FROM DeptDetails;

注意：这个查询使用了CTE（公用表表达式）来组织子查询，并通过FILTER子句计算薪资高于公司平均薪资的员工数量。不过，请注意，MySQL直到8.0版本才支持FILTER子句，如果版本较低，可能需要使用CASE语句代替。

题目四：查询入职时间最早的员工信息，以及他/她管理的项目名称（如果有的话）。

答案

SELECT e.name, p.name AS project_name  
FROM employees e  
LEFT JOIN projects p ON e.employee_id = p.manager_id  
WHERE e.hire_date = (  
    SELECT MIN(hire_date) FROM employees  
)  
ORDER BY p.project_id;  -- 如果有多个最早入职的员工且他们管理的项目不同，则按项目ID排序

案例4

建表语句与模拟数据

用户表 (users)

CREATE TABLE users (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    username VARCHAR(50) NOT NULL UNIQUE,  
    email VARCHAR(100) NOT NULL UNIQUE,  
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  
);  
  
INSERT INTO users (username, email) VALUES  
('alice', 'alice@example.com'),  
('bob', 'bob@example.com'),  
('charlie', 'charlie@example.com');

订单表 (orders)

CREATE TABLE orders (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    user_id INT,  
    product_name VARCHAR(100),  
    quantity INT,  
    order_date DATE,  
    FOREIGN KEY (user_id) REFERENCES users(id)  
);  
  
INSERT INTO orders (user_id, product_name, quantity, order_date) VALUES  
(1, 'Laptop', 1, '2023-04-01'),  
(2, 'Smartphone', 2, '2023-04-02'),  
(1, 'Tablet', 1, '2023-04-03'),  
(3, 'Headphones', 1, '2023-04-04');

题目一: 查询每个用户的用户名、电子邮件以及他们订购的商品总数。

答案：

SELECT u.username, u.email, COUNT(o.id) AS total_orders  
FROM users u  
LEFT JOIN orders o ON u.id = o.user_id  
GROUP BY u.id;

解析:

我们使用了LEFT JOIN来连接users表和orders表，确保即使某些用户没有订单也能被包含在结果中。使用COUNT(o.id)来计算每个用户的订单总数。通过GROUP BY u.id来按用户分组，以便为每个用户聚合订单数量。

题目二: 查询在2023年4月2日之后下单的用户，包括用户名和订单日期。

答案：

SELECT u.username, o.order_date  
FROM users u  
JOIN orders o ON u.id = o.user_id  
WHERE o.order_date > '2023-04-02';

解析:

我们使用了INNER JOIN来连接users表和orders表，因为我们只关心有订单的用户。使用WHERE子句来过滤出订单日期在2023年4月2日之后的记录。选择username和order_date作为输出列。

题目三: 查询订单总数最多的用户及其订单总数。

答案：

SELECT u.username, COUNT(o.id) AS total_orders  
FROM users u  
JOIN orders o ON u.id = o.user_id  
GROUP BY u.id  
ORDER BY total_orders DESC  
LIMIT 1;

解析:

类似于第一个问题，我们使用了JOIN和GROUP BY来按用户分组并计算订单总数。通过ORDER BY total_orders DESC将结果按订单总数降序排列。使用LIMIT 1来获取订单总数最多的用户。

案例5

建表语句与模拟数据

用户表 (users)

CREATE TABLE users (  
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',  
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',  
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '电子邮件'  
);  
  
INSERT INTO users (username, email) VALUES  
('alice', 'alice@example.com'),  
('bob', 'bob@example.com'),  
('charlie', 'charlie@example.com');

订单表 (orders)

CREATE TABLE orders (  
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',  
    user_id INT NOT NULL COMMENT '用户ID',  
    product_name VARCHAR(100) NOT NULL COMMENT '产品名称',  
    quantity INT NOT NULL COMMENT '数量',  
    order_date DATE NOT NULL COMMENT '订单日期',  
    FOREIGN KEY (user_id) REFERENCES users(id)  
);  
  
INSERT INTO orders (user_id, product_name, quantity, order_date) VALUES  
(1, 'Laptop', 1, '2023-04-01'),  
(2, 'Smartphone', 2, '2023-04-02'),  
(1, 'Tablet', 1, '2023-04-03'),  
(3, 'Headphones', 1, '2023-04-04'),  
(2, 'Smartphone', 1, '2023-04-05');

题目一: 查询每个用户的用户名及其订单总数。

解题思路:

使用JOIN连接users和orders表。
使用GROUP BY按用户分组。
使用COUNT聚合函数计算每个用户的订单总数。

答案：

SELECT u.username, COUNT(o.id) AS total_orders  
FROM users u  
JOIN orders o ON u.id = o.user_id  
GROUP BY u.id, u.username;

解析:

JOIN用于连接用户表和订单表，以便获取每个用户的订单信息。
GROUP BY用于按用户分组，确保每个用户只出现一次在结果集中。
COUNT(o.id)计算每个用户组的订单总数。

题目二: 查询每个产品名称的总销售数量（即该产品被购买的总数量）。

解题思路:

直接对orders表进行GROUP BY操作，按产品名称分组。
使用SUM聚合函数计算每个产品组的销售数量总和。

答案:

SELECT product_name, SUM(quantity) AS total_sales  
FROM orders  
GROUP BY product_name;

解析:

GROUP BY按产品名称分组，确保每个产品只出现一次在结果集中。
SUM(quantity)计算每个产品组的销售数量总和。

题目3: 查询每个用户在2023年4月份下的订单数量及订单总金额

（假设每种产品的单价固定，Laptop为999.99, Smartphone为599.99, Tablet为399.99, Headphones为99.99）。

解题思路:

使用JOIN连接users和orders表。
使用WHERE子句过滤出2023年4月份的订单。
使用CASE语句或临时表/子查询来确定每种产品的价格，并计算订单总金额。
使用GROUP BY按用户分组，并使用COUNT和SUM进行聚合。

由于直接在SQL中处理动态价格可能稍显复杂，这里简化处理，假设价格已知：

答案:

SELECT   
    u.username,   
    COUNT(o.id) AS order_count,  
    SUM(  
        CASE   
            WHEN o.product_name = 'Laptop' THEN o.quantity * 999.99  
            WHEN o.product_name = 'Smartphone' THEN o.quantity * 599.99  
            WHEN o.product_name = 'Tablet' THEN o.quantity * 399.99  
            WHEN o.product_name = 'Headphones' THEN o.quantity * 99.99  
            ELSE 0  
        END  
    ) AS total_amount  
FROM users u  
JOIN orders o ON u.id = o.user_id  
WHERE YEAR(o.order_date) = 2023 AND MONTH(o.order_date) = 4  
GROUP BY u.id, u.username;

解析:

JOIN和WHERE子句用于连接和过滤出2023年4月份的订单。
CASE语句用于根据产品名称确定单价，并计算每个订单的金额。
SUM聚合函数用于计算每个用户的订单总金额。
COUNT聚合函数用于计算每个用户的订单数量。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2024-09-19，如有侵权请联系 cloudcommunity@tencent.com 删除

连接

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度