MySql数据库复杂查询示例
2023-12-31 10:33:57
创建数据库表
复杂查询涉及到了多个表,以下为相应的简化版建表语句示例:
- 部门表(departments):
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(255) NOT NULL,
parent_id INT DEFAULT NULL, -- 可用于自连接查询的外键
FOREIGN KEY (parent_id) REFERENCES departments(department_id)
);
INSERT INTO departments (department_id, department_name) VALUES
(1, 'IT'), (2, 'HR'), (3, 'Sales'), (4, 'Finance');
-- 对于自连接查询,假设部门IT是Sales的上级
UPDATE departments SET parent_id = 1 WHERE department_name = 'Sales';
- 员工表(employees):
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(255) NOT NULL,
salary DECIMAL(10, 2) NOT NULL,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(department_id)
);
INSERT INTO employees (emp_id, emp_name, salary, dept_id) VALUES
(1, 'John Doe', 50000, 1),
(2, 'Jane Smith', 60000, 1),
(3, 'Mike Johnson', 70000, 2),
(4, 'Emily Brown', 80000, 3),
(5, 'Tom Wilson', 90000, 4);
- 客户表(customers)和订单表(orders):
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(255) NOT NULL,
last_purchase DATE
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(20),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Customer A'),
(2, 'Customer B');
INSERT INTO orders (order_id, customer_id, order_date, status) VALUES
(1, 1, '2022-01-01', 'Pending'),
(2, 1, '2022-02-01', 'Completed'),
(3, 2, '2022-01-15', 'Pending');
请注意,以上仅为简化的示例,实际数据库设计时应根据具体业务需求进行更详细的字段定义及数据插入。
MySQL数据库复杂查询示例:
-
子查询(嵌套查询):
-- 查询每个部门薪水最高的员工信息 SELECT d.dept_name, e.emp_name, e.salary FROM departments AS d JOIN employees AS e ON d.dept_id = e.dept_id WHERE e.salary = ( SELECT MAX(salary) FROM employees WHERE dept_id = d.dept_id );
-
联合查询(UNION 或 UNION ALL):
-- 获取所有在表A和表B中都存在的用户ID SELECT user_id FROM table_A UNION SELECT user_id FROM table_B; -- 包含重复项的联合查询 SELECT user_id FROM table_A UNION ALL SELECT user_id FROM table_B;
-
连接查询(JOIN):
-- 使用INNER JOIN获取每个订单对应的客户信息 SELECT o.order_id, c.customer_name, o.order_date FROM orders AS o INNER JOIN customers AS c ON o.customer_id = c.customer_id;
-
自连接查询(Self Join):
-- 查询某个部门及其所有下属部门的信息 SELECT t1.department_name AS ParentDept, t2.department_name AS SubDept FROM departments AS t1 LEFT JOIN departments AS t2 ON t2.parent_id = t1.department_id;
-
分组查询与聚合函数:
-- 按照部门统计平均薪水 SELECT dept_id, AVG(salary) as avg_salary FROM employees GROUP BY dept_id; -- 分组后过滤条件(HAVING) SELECT dept_id, COUNT(*) as employee_count FROM employees GROUP BY dept_id HAVING COUNT(*) > 5;
-
窗口函数(OVER clause):
-- 对每个部门内的员工薪水进行排名 SELECT emp_id, salary, dept_id, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rank_in_dept FROM employees;
-
多表更新:
-- 更新订单状态的同时更新对应客户的最后购买日期 UPDATE orders o JOIN ( SELECT order_id, MAX(order_date) as last_purchase_date FROM orders GROUP BY customer_id ) t ON o.order_id = t.order_id SET o.status = 'Completed', c.last_purchase = t.last_purchase_date JOIN customers c ON o.customer_id = c.customer_id WHERE o.status = 'Pending';
以上各示例展示了MySQL中常见的复杂查询类型,实际应用时会根据业务需求选择合适的查询语句组合。
文章来源:https://blog.csdn.net/qq_23488347/article/details/135313314
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!