SQL窗口函数面试题与解答2

 

查找每个部门薪资排名前两名的员工

SELECT * FROM ( SELECT 'OrangeDBM' as AUTHOR,department, name, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn <= 2;

示例：日环比销售额 SELECT 'OrangeDBM' as AUTHOR,date, sales, LAG(sales) OVER (ORDER BY date) AS prev_day_sales FROM sales;

示例：将当前日期与下一日期进行比较

SELECT 'OrangeDBM' as AUTHOR,date, sales, LEAD(sales) OVER (ORDER BY date) AS next_day_sales FROM sales;

计算每日环比增长率

SELECT 'OrangeDBM' as AUTHOR,date, sales - LAG(sales) OVER (ORDER BY date) AS growth FROM sales;

SQL窗口函数面试题与解答1

示例：按薪资对员工进行排序
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

示例：按部门对员工进行排名
SELECT 'OrangeDBM' as AUTHOR,department, name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

SELECT 'OrangeDBM' as AUTHOR,name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

SELECT 'OrangeDBM' as AUTHOR,name, salary, RANK() OVER (ORDER BY salary DESC) AS rn
FROM employees;

SELECT 'OrangeDBM' as AUTHOR,name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rn
FROM employees;

SQL学习路线地图

 

-- 创建学生表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- 插入新数据 (Create)
INSERT INTO students (id, name, age) VALUES (1, '张三', 20);


-- 查找年龄大于 18 的学生，按年龄降序排列
SELECT DISTINCT name, age 
FROM students 
WHERE age > 18 
ORDER BY age DESC 
LIMIT 5;


-- 计算平均年龄
SELECT AVG(age) FROM students;

-- 合并姓名与固定字符串
SELECT CONCAT('学生姓名: ', name) AS student_info FROM students;


-- 查询学生及其选修的课程名
SELECT students.name, courses.course_name
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.id;


-- 查找成绩高于平均分的学生 ID
SELECT student_id 
FROM grades 
WHERE score > (SELECT AVG(score) FROM grades);


CREATE TABLE orders (
    order_id INT PRIMARY KEY,  -- 主键：唯一标识
    user_id INT NOT NULL,      -- 非空约束
    FOREIGN KEY (user_id) REFERENCES users(id) -- 外键：关联用户表
);


-- 为姓名列创建索引以加速查询
CREATE INDEX idx_student_name ON students(name);

-- 创建一个只显示优秀学生的视图
CREATE VIEW elite_students AS
SELECT name, score FROM students WHERE score > 90;


START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 如果没有错误则提交，否则执行 ROLLBACK;
COMMIT;

-- 当删除学生时，自动记录日志
CREATE TRIGGER after_student_delete
AFTER DELETE ON students
FOR EACH ROW
INSERT INTO logs (action, student_id) VALUES ('DELETE', OLD.id);

SQL场景化面试题

 

SQL 代码：

WITH RankedOrders AS (
    SELECT *,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id, product
        ORDER BY id ASC
    ) as rn
    FROM orders
)
DELETE FROM orders
WHERE id IN (
    SELECT id FROM RankedOrders
    WHERE rn > 1
);

SQL学习应避免的5大误区

 

SQL思维导图

 

SQL 核心分类

1. DDL（数据定义语言）

用于定义或更改数据库的结构。

ALTER（更改）：

ADD（添加）：列、约束（唯一约束、非空约束、主键、外键、检查约束、默认值）

REMOVE（移除）：列、约束

MODIFY（修改）：数据类型、默认值

VIEW（查看）：数据库、表、视图

DROP（删除）：表、数据库

TRUNCATE（截断）：表

2. DML（数据操纵语言）

用于管理对象内部的数据。

SELECT（查询）：指定列、整张表 (*)

INSERT（插入）：数据至表

UPDATE（更新）：字段

DELETE（删除）：字段

3. DCL 与 TCL（数据/事务控制）

用于权限管理及事务管理。

GRANT（授权）/ REVOKE（撤销权限）

COMMIT（提交）/ ROLLBACK（回滚）/ SAVEPOINT（保存点）

4. 查询子句与逻辑

ALIAS（别名）：AS

GRO​​UP BY（分组）：按列分组、HAVING（分组过滤）

ORDER BY（排序）：升序 (ASC)、降序 (DESC)

JOIN（连接）：内连接 (INNER)、左连接 (LEFT)、右连接 (RIGHT)、全连接 (FULL)

WHERE（条件子句/运算符）：比较运算符（<, >, =, 等）、AND/OR/NOT、BETWEEN、LIKE、IN、ANY、ALL、EXISTS

5. 函数

AGGREGATE FUNCTIONS（聚合函数）：AVG()、SUM()、COUNT()、MIN()、MAX()

WINDOW FUNCTIONS（窗口函数）：

OVER()、ROW_NUMBER()

RANK()、DENSE_RANK()

NTILE()、LAG()、LEAD()

SQL学习路线

 

SQL示例

SELECT 
    Product_Name AS Name,           -- ALIAS
    SUM(Amount) AS Total_Sales      -- FUNCTIONS (SUM)
FROM Sales
WHERE Region IN ('North', 'East')   -- WHERE & IN
GROUP BY Product_Name               -- GROUP BY
HAVING SUM(Amount) > 1000           -- HAVING
ORDER BY Total_Sales DESC;          -- ORDER BY & DESC

SQL Concepts

1. ALIAS

• AS

2. GROUP BY

• COLUMN
  • HAVING

3. WHERE

• LIKE
• IN
• BETWEEN
• ANY
• EXIST
• AND OR NOT

4. FUNCTIONS

• AVG( )
• SUM( )
• COUNT( )
• MIN( )
• MAX( )

5. JOINS

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

6. ORDER BY

• ASC
• DESC

SQL 问与答6

 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products`  (
  `id` int(0) NULL DEFAULT NULL,
  `productname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `category` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `price` int(0) NULL DEFAULT NULL,
  `stock_qty` int(0) NULL DEFAULT NULL,
  `supplier_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES (1, 'Laptop', 'Electronics', 75000, 15, 101);
INSERT INTO `products` VALUES (2, 'Mouse', 'Electronics', 1500, 50, 102);
INSERT INTO `products` VALUES (3, 'Chair', 'Furniture', 8000, 25, 103);
INSERT INTO `products` VALUES (4, 'Keyboard', 'Electronics', 2500, 30, 102);
INSERT INTO `products` VALUES (5, 'Desk', 'Furniture  ', 15000, 10, 103);
INSERT INTO `products` VALUES (6, 'Monitor', 'Electronics', 25000, 20, 101);
INSERT INTO `products` VALUES (7, 'Table', 'Furniture', 5000, 40, 104);
INSERT INTO `products` VALUES (8, 'Webcam', 'Electronics', 3000, 35, 102);

SET FOREIGN_KEY_CHECKS = 1;

在每个类别内按价格对产品进行排序

SELECT 'OrangeDBM' as AUTHOR, product_name, category, price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products;

查找拥有超过 3 种产品的供应商

SELECT 'OrangeDBM' as AUTHOR, supplier_id, COUNT(*)
FROM products
GROUP BY supplier_id
HAVING COUNT(*) > 3;

查找价格最高的 3 件产品

SELECT DISTINCT 'OrangeDBM' as AUTHOR, price
FROM products
ORDER BY price DESC
LIMIT 3;

SQL 问与答5

 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products`  (
  `id` int(0) NULL DEFAULT NULL,
  `productname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `category` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `price` int(0) NULL DEFAULT NULL,
  `stock_qty` int(0) NULL DEFAULT NULL,
  `supplier_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES (1, 'Laptop', 'Electronics', 75000, 15, 101);
INSERT INTO `products` VALUES (2, 'Mouse', 'Electronics', 1500, 50, 102);
INSERT INTO `products` VALUES (3, 'Chair', 'Furniture', 8000, 25, 103);
INSERT INTO `products` VALUES (4, 'Keyboard', 'Electronics', 2500, 30, 102);
INSERT INTO `products` VALUES (5, 'Desk', 'Furniture  ', 15000, 10, 103);
INSERT INTO `products` VALUES (6, 'Monitor', 'Electronics', 25000, 20, 101);
INSERT INTO `products` VALUES (7, 'Table', 'Furniture', 5000, 40, 104);
INSERT INTO `products` VALUES (8, 'Webcam', 'Electronics', 3000, 35, 102);

SET FOREIGN_KEY_CHECKS = 1;

按供应商统计产品总数

SELECT 'OrangeDBM' as AUTHOR, supplier_id, COUNT(*) AS product_count
FROM products
GROUP BY supplier_id;

查找包含超过 2 个产品的类别

SELECT 'OrangeDBM' as AUTHOR, category, COUNT(*) AS total_products
FROM products
GROUP BY category
HAVING COUNT(*) > 2;

查找整体第二高的价格

SELECT 'OrangeDBM' as AUTHOR, MAX(price)
FROM products
WHERE price < (SELECT MAX(price) FROM products);

查找最昂贵的电子产品

SELECT 'OrangeDBM' as AUTHOR, productname, price
FROM products
WHERE category = 'Electronics'
AND price = (SELECT MAX(price) FROM products WHERE category = 'Electronics');

SQL 问与答4

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products`  (
  `id` int(0) NULL DEFAULT NULL,
  `productname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `category` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `price` int(0) NULL DEFAULT NULL,
  `stock_qty` int(0) NULL DEFAULT NULL,
  `supplier_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES (1, 'Laptop', 'Electronics', 75000, 15, 101);
INSERT INTO `products` VALUES (2, 'Mouse', 'Electronics', 1500, 50, 102);
INSERT INTO `products` VALUES (3, 'Chair', 'Furniture', 8000, 25, 103);
INSERT INTO `products` VALUES (4, 'Keyboard', 'Electronics', 2500, 30, 102);
INSERT INTO `products` VALUES (5, 'Desk', 'Furniture  ', 15000, 10, 103);
INSERT INTO `products` VALUES (6, 'Monitor', 'Electronics', 25000, 20, 101);
INSERT INTO `products` VALUES (7, 'Table', 'Furniture', 5000, 40, 104);
INSERT INTO `products` VALUES (8, 'Webcam', 'Electronics', 3000, 35, 102);

SET FOREIGN_KEY_CHECKS = 1;

 计算每个产品类别的平均价格

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;

查找价格高于其所属类别平均价格的产品

SELECT productname, category, price
FROM products p
WHERE price > (SELECT AVG(price)
FROM products
WHERE category = p.category);

找出每个类别中价格最高的产品

SELECT category, MAX(price) AS max_price
FROM products
GROUP BY category;

查找库存数量少于 20 的产品

SELECT 'OrangeDBM' as AUTHOR, productname, stock_qty
FROM products
WHERE stock_qty < 20;

SQL 问与答3

 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(0) NULL DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `salary` int(0) NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);

SET FOREIGN_KEY_CHECKS = 1;

9. 按薪资对员工进行排序

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

10. 查找重复的薪资

SELECT salary, COUNT() FROM employees GROUP BY salary HAVING COUNT() > 1;

11. 薪资最高两位

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 2;

SQL 问与答2

 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(0) NULL DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `salary` int(0) NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);

SET FOREIGN_KEY_CHECKS = 1;

5. 统计各部门的员工人数

SELECT department, COUNT(*) AS total_employees FROM employees GROUP BY department;

6. 查找员工人数超过 2 人的部门

SELECT department, COUNT() AS total FROM employees GROUP BY department HAVING COUNT() > 2;

7. 查找第二高的薪资

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

8. 查找没有上级的员工

SELECT name FROM employees WHERE manager_id IS NULL;

SQL 问与答1

 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(0) NULL DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `salary` int(0) NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);

SET FOREIGN_KEY_CHECKS = 1;

1. 计算各部门的平均薪资

SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department;

2. 查找薪资高于部门平均水平的员工

SELECT name, department, salary FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department = e.department );

3. 查找每个部门的最高薪资

SELECT department, MAX(salary) AS max_salary FROM employees GROUP BY department;

SQL速查3

 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(0) NULL DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `salary` int(0) NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);

SET FOREIGN_KEY_CHECKS = 1;

11.CASE WHEN

SELECT name, CASE WHEN salary > 40000 THEN 'High' ELSE 'Low' END AS category FROM employees;

12.子查询

SELECT name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees);

13.RANK()

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

14.DISTINCT

SELECT DISTINCT department FROM employees;

15.LIKE

SELECT * FROM employees WHERE name LIKE 'A%';

SQL速查2

 

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(0) NULL DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `salary` int(0) NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);

-- ----------------------------

-- Table structure for departments
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments`  (
  `DepartmentID` int(0) NOT NULL AUTO_INCREMENT,
  `DepartmentName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`DepartmentID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES (1, 'HR');
INSERT INTO `departments` VALUES (2, 'Finance');
INSERT INTO `departments` VALUES (3, 'Engineering');
INSERT INTO `departments` VALUES (4, 'Sales');
INSERT INTO `departments` VALUES (5, 'Marketing');
INSERT INTO `departments` VALUES (6, 'IT');
INSERT INTO `departments` VALUES (7, 'Operations');
INSERT INTO `departments` VALUES (8, 'Customer Service');
INSERT INTO `departments` VALUES (9, 'Research and Department');
INSERT INTO `departments` VALUES (10, 'Legal');
INSERT INTO `departments` VALUES (11, 'Quality Assurance');

SET FOREIGN_KEY_CHECKS = 1;

6.AVG()

SELECT AVG(salary) FROM employees;

7.GROUP BY

SELECT department, AVG(salary) FROM employees GROUP BY department;

8.HAVING

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 40000;

9.INNER JOIN

SELECT e.name, d.departmentname FROM employees e INNER JOIN departments d ON e.department = d.departmentname;

10.LEFT JOIN

SELECT e.name, d.departmentname FROM employees e LEFT JOIN departments d ON e.department = d.departmentname;

SQL速查1

 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(0) NULL DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `salary` int(0) NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);

SET FOREIGN_KEY_CHECKS = 1;

1.SELECT

SELECT name, salary FROM employees;

2.FROM

SELECT * FROM employees;

3.WHERE

SELECT * FROM employees WHERE salary > 30000;

4.ORDER BY

SELECT * FROM employees ORDER BY salary DESC;

5.COUNT()

SELECT COUNT(*) FROM employees;

精选 SQL 问题及解答2

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for sales

-- ----------------------------

DROP TABLE IF EXISTS `sales`;

CREATE TABLE `sales`  (
  `SaleID` int(0) NOT NULL,
  `EmployeeID` int(0) NULL DEFAULT NULL,
  `Department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Sales` decimal(10, 2) NULL DEFAULT NULL,
  `Date` date NULL DEFAULT NULL,
  PRIMARY KEY (`SaleID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sales
-- ----------------------------
INSERT INTO `sales` VALUES (1, 101, 'Electronics', 500.00, '2023-08-01');
INSERT INTO `sales` VALUES (2, 102, 'Electronics', 300.00, '2023-08-03');
INSERT INTO `sales` VALUES (3, 101, 'Furniture', 150.00, '2023-08-02');
INSERT INTO `sales` VALUES (4, 103, 'Electronics', 250.00, '2023-08-04');
INSERT INTO `sales` VALUES (5, 104, 'Furniture', 200.00, '2023-08-02');
-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(0) NULL DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `salary` int(0) NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);
-- ----------------------------
-- Table structure for customers
-- ----------------------------
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers`  (
  `customer_id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `phone` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  PRIMARY KEY (`customer_id`) USING BTREE,
  UNIQUE INDEX `email`(`email`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of customers
-- ----------------------------
INSERT INTO `customers` VALUES (1, 'John Doe', 'john@example.com', '1234567890');
INSERT INTO `customers` VALUES (2, 'Jane Smith', 'jane@example.com', '0987654321');
INSERT INTO `customers` VALUES (5, 'Blau Hoss', 'Blau@example.com', '11111111');

SET FOREIGN_KEY_CHECKS = 1;

问题描述：按日期计算销售额的累计总计。

解决方案

SELECT date, sales, SUM(sales) OVER(ORDER BY date) AS running_total FROM sales;

问题描述：找出薪资高于平均水平的员工。

解决方案

SELECT name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees );

问题描述：找出总薪资最高的部门。

解决方案

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department ORDER BY total_salary DESC LIMIT 1;

问题描述：找出至少下过一单的客户。

解决方案

SELECT name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE c.customer_id = o.customer_id );

问题描述：删除重复记录，但保留其中一条唯一记录。

解决方案

DELETE FROM employees WHERE id NOT IN ( SELECT MIN(id) FROM employees GROUP BY name );

精选 SQL 问题及解答1

 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(0) NULL DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `salary` int(0) NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);

DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers`  (
  `customer_id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `phone` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  PRIMARY KEY (`customer_id`) USING BTREE,
  UNIQUE INDEX `email`(`email`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of customers
-- ----------------------------
INSERT INTO `customers` VALUES (1, 'John Doe', 'john@example.com', '1234567890');
INSERT INTO `customers` VALUES (2, 'Jane Smith', 'jane@example.com', '0987654321');
INSERT INTO `customers` VALUES (5, 'Blau Hoss', 'Blau@example.com', '11111111');

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `id` int(0) NULL DEFAULT NULL,
  `customer_id` int(0) NULL DEFAULT NULL,
  `product` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `order_date` datetime(0) NULL DEFAULT NULL
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES (1, 101, 'Laptop', '2026-01-15 00:00:00');
INSERT INTO `orders` VALUES (3, 102, 'Phone', '2026-01-16 00:00:00');
INSERT INTO `orders` VALUES (4, 101, 'Tablet', '2026-01-17 00:00:00');

SET FOREIGN_KEY_CHECKS = 1;

问题描述：从 employees 表中查找第二高的薪资。

解决方案

SELECT MAX(salary) FROM employees WHERE salary < ( SELECT MAX(salary) FROM employees );

问题描述：编写一条查询语句，查找第 3 高的薪水。

解决方案

SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER(ORDER BY salary DESC) r FROM employees ) t WHERE r = 3;

问题描述：找出 employees 表中所有重复的姓名。

解决方案

SELECT name, COUNT() FROM employees GROUP BY name HAVING COUNT() > 1;

问题描述：找出未下过任何订单的客户。

解决方案

SELECT c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.customer_id IS NULL;

问题描述：找出每个部门中薪资最高的3名员工。

解决方案

SELECT * FROM ( SELECT name, department, salary, ROW_NUMBER() OVER( PARTITION BY department ORDER BY salary DESC ) r FROM employees ) t WHERE r <= 3;

SQL查询之窗口函数真实数据分析案例

 
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(0) NULL DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `salary` int(0) NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);

-- Table structure for sales
-- ----------------------------
DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales`  (
  `SaleID` int(0) NOT NULL,
  `EmployeeID` int(0) NULL DEFAULT NULL,
  `Department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Sales` decimal(10, 2) NULL DEFAULT NULL,
  `Date` date NULL DEFAULT NULL,
  PRIMARY KEY (`SaleID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of sales
-- ----------------------------
INSERT INTO `sales` VALUES (1, 101, 'Electronics', 500.00, '2023-08-01');
INSERT INTO `sales` VALUES (2, 102, 'Electronics', 300.00, '2023-08-03');
INSERT INTO `sales` VALUES (3, 101, 'Furniture', 150.00, '2023-08-02');
INSERT INTO `sales` VALUES (4, 103, 'Electronics', 250.00, '2023-08-04');
INSERT INTO `sales` VALUES (5, 104, 'Furniture', 200.00, '2023-08-02');

-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products`  (
  `id` int(0) NULL DEFAULT NULL,
  `productname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `category` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `price` int(0) NULL DEFAULT NULL,
  `stock_qty` int(0) NULL DEFAULT NULL,
  `supplier_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES (1, 'Laptop', 'Electronics', 75000, 15, 101);
INSERT INTO `products` VALUES (2, 'Mouse', 'Electronics', 1500, 50, 102);
INSERT INTO `products` VALUES (3, 'Chair', 'Furniture', 8000, 25, 103);
INSERT INTO `products` VALUES (4, 'Keyboard', 'Electronics', 2500, 30, 102);
INSERT INTO `products` VALUES (5, 'Desk', 'Furniture  ', 15000, 10, 103);
INSERT INTO `products` VALUES (6, 'Monitor', 'Electronics', 25000, 20, 101);
INSERT INTO `products` VALUES (7, 'Table', 'Furniture', 5000, 40, 104);
INSERT INTO `products` VALUES (8, 'Webcam', 'Electronics', 3000, 35, 102);

SET FOREIGN_KEY_CHECKS = 1;

薪资最高的前三名

SELECT * FROM ( SELECT name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn <= 3;

销售额累计总计

SELECT date, sales, SUM(sales) OVER(ORDER BY date) AS running_total FROM sales;

按类别对产品进行排名

SELECT productname, category, price, RANK() OVER(PARTITION BY category ORDER BY price DESC) AS rn FROM products;