复杂sql合集

1.插入

A.插入子查询结果：

INSERT INTO table2 (column1, column2)
SELECT column1, column2
FROM table1
WHERE condition;


B.基于其他表数据的条件插入（INSERT ... SELECT）：

INSERT INTO employees (name, department, position)
SELECT applicant_name, department, 'New Hire'
FROM applicants
WHERE application_status = 'Approved';


C.使用变量和条件逻辑插入：

INSERT INTO orders (order_date, order_status, customer_id)
SELECT CURDATE(), 'Pending', c.customer_id
FROM customers AS c
WHERE c.account_balance > 1000 AND NOT EXISTS (
    SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id AND o.order_status = 'Pending'
);


D.多行插入对应不同的条件：

INSERT INTO products (product_name, stock_quantity, price)
VALUES 
('Product 1', 50, (SELECT price FROM pricing WHERE product='Product 1')),
('Product 2', 30, (SELECT price FROM pricing WHERE product='Product 2')),
('Product 3', 20, (SELECT price FROM pricing WHERE product='Product 3'));


E.使用事务进行多表插入（确保ACID属性）：

START TRANSACTION;

INSERT INTO customers (customer_name, contact_name, address, city, postal_code, country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

INSERT INTO orders (customer_id, order_date, status)
VALUES (LAST_INSERT_ID(), NOW(), 'Shipped');

2.更新

A.更新时联接其他表：

UPDATE products AS p
JOIN inventory AS i ON p.id = i.product_id
SET p.price = p.price * 1.1, 
    i.quantity = i.quantity - 5
WHERE i.quantity < 10;

这个 UPDATE 语句通过 JOIN 操作同时更新了 products 表中的价格和 inventory 表中的数量。


B.使用子查询进行更新：

UPDATE orders
SET status = 'Processed'
WHERE customer_id IN (
    SELECT id FROM customers WHERE balance_due > 1000
);

在这里，我们只更新那些客户余额超过 1000 的订单状态。


C.使用 ORDER BY 和 LIMIT 更新数据：

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Sales'
ORDER BY hire_date ASC
LIMIT 10;

此命令将增加最先被雇用的 10 名销售部门员工的工资。


D.根据条件更新多个表：

UPDATE items, orders_items
SET items.quantity = items.quantity - orders_items.quantity,
    orders_items.shipped_date = NOW()
WHERE items.id = orders_items.item_id
AND orders_items.order_id = 1234;

这段代码同时更新了 items 和 orders_items 表，减少库存数量并设置发货日期。


E.利用 CASE 语句进行条件更新：

UPDATE employees
SET bonus = CASE
    WHEN performance = 'excellent' THEN salary * 0.20
    WHEN performance = 'good' THEN salary * 0.15
    ELSE salary * 0.10
END
WHERE year = 2023;

3.复杂的查询

A.多表 JOIN 查询：

SELECT employees.name, employees.salary, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.id
WHERE employees.salary > (SELECT AVG(salary) FROM employees)
ORDER BY employees.salary DESC;

这个查询返回薪资高于公司平均水平的员工信息和他们所在的部门，结果按薪资降序排列。


B.使用 GROUP BY 和 HAVING 的聚合查询：

SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5 AND AVG(salary) > 50000;

此查询显示每个部门的员工数量和平均薪资，但只限于那些有超过5名员工且平均薪资超过50,000的部门。


C.子查询和 EXISTS 操作符：

SELECT product_name, category_name
FROM products AS p
WHERE EXISTS (
    SELECT 1 FROM inventory AS i WHERE p.id = i.product_id AND i.quantity < 10
)
AND NOT EXISTS (
    SELECT 1 FROM order_details AS od WHERE p.id = od.product_id
);

这个查询返回库存少于10件且未在任何订单中出现的产品名称和类别。


D.复杂条件和多层次子查询：

SELECT e.name, e.position, m.name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id
WHERE e.salary > (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department_id = (
        SELECT id 
        FROM departments 
        WHERE department_name = 'Sales'
    )
);

这个查询检索所有薪资高于销售部平均薪资的员工及其经理的名字。


E.WITH 语句（Common Table Expressions，CTEs）：

WITH ranked_salaries AS (
    SELECT name, salary, RANK() OVER (ORDER BY salary DESC) salary_rank
    FROM employees
)
SELECT name, salary
FROM ranked_salaries
WHERE salary_rank <= 5;


F.带有聚合和HAVING子句的复杂连接

SELECT e.department_id, d.name, AVG(e.salary) AS average_salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
GROUP BY e.department_id
HAVING AVG(e.salary) > (SELECT AVG(salary) FROM employees)
ORDER BY average_salary DESC;


G.SELECT中的子查询

SELECT name, 
       (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS order_count
FROM customers c
WHERE c.registered_at >= '2022-01-01';


H.相关子查询

SELECT p1.name, p1.price
FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p1.category_id = p2.category_id
)


I.CTE(公共表表达式)与窗口函数

WITH ranked_sales AS (
    SELECT s.id, s.date, s.amount, 
           RANK() OVER (PARTITION BY s.salesperson_id ORDER BY s.amount DESC) AS rank
    FROM sales s
)
SELECT id, date, amount
FROM ranked_sales
WHERE rank = 1;


J.连接时使用大小写

SELECT e.id, e.name, d.name AS department,
CASE 
    WHEN e.salary < 30000 THEN 'Low'
    WHEN e.salary BETWEEN 30000 AND 70000 THEN 'Medium'
    ELSE 'High'
END AS salary_level
FROM employees e
JOIN departments d ON e.department_id = d.id;

4.复杂的删除

A.删除与特定条件匹配的记录：

DELETE FROM orders
WHERE customer_id NOT IN (SELECT id FROM customers WHERE active = 1);

这个命令会删除所有非活跃客户的订单。


B.多表 DELETE JOIN 操作：

DELETE e, d
FROM employees AS e
JOIN departments AS d ON e.department_id = d.id
WHERE d.department_name = 'Sales' AND e.salary < 30000;

这条语句将同时从 employees 和 departments 表中删除销售部门工资低于30,000的员工和相应的部门信息。


C.使用 LIMIT 和 ORDER BY 的删除：

DELETE FROM messages
ORDER BY created_at DESC
LIMIT 10;

此命令将删除最新的10条消息记录。


D.带有子查询和 EXISTS 关键字的删除：

DELETE FROM products
WHERE EXISTS (
    SELECT * FROM order_details
    WHERE order_details.product_id = products.id AND order_details.quantity = 0
);

此语句删除了所有没有在订单详情中显示（即数量为0）的产品。


F.删除依赖于复杂条件和聚合函数的记录：

DELETE FROM users
WHERE id IN (
    SELECT u.id FROM users AS u
    LEFT JOIN orders AS o ON u.id = o.user_id
    GROUP BY u.id
    HAVING COUNT(o.id) = 0
);