有没有哪位佬帮我看看这个程序具体的执行顺序是什么感觉多一行少一行都是错的
-- 创建数据库
CREATE DATABASE IF NOT EXISTS db_final_project;
USE db_final_project;
-- 部门表
DROP TABLE IF EXISTS dept;
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
name VARCHAR(50) NOT NULL COMMENT '部门名称'
) COMMENT='部门表';
INSERT INTO dept(name) VALUES
('研发部'),('市场部'),('财务部'),('销售部'),('总经办');
-- 员工表
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
name VARCHAR(50) NOT NULL COMMENT '姓名',
age TINYINT COMMENT '年龄',
job VARCHAR(20) COMMENT '职位',
salary INT COMMENT '薪资',
entrydate DATE COMMENT '入职日期',
managerid INT COMMENT '直属上司ID',
dept_id INT COMMENT '部门ID',
INDEX idx_emp_dept(dept_id),
INDEX idx_emp_salary(salary),
INDEX idx_emp_entry(entrydate)
) COMMENT='员工表';
INSERT INTO emp(name,age,job,salary,entrydate,managerid,dept_id) VALUES
('张三',45,'总经理',20000,'2010-01-01',NULL,5),
('李四',30,'项目经理',15000,'2012-05-10',1,1),
('王五',28,'开发工程师',9000,'2015-08-20',2,1),
('赵六',35,'市场总监',16000,'2011-03-15',1,2),
('孙七',50,'财务主管',18000,'2013-11-30',1,3),
('周八',26,'销售代表',8000,'2016-07-01',4,4),
('吴九',22,'实习生',4000,'2020-09-01',2,1);
-- 薪资等级表
DROP TABLE IF EXISTS salgrade;
CREATE TABLE salgrade(
grade INT COMMENT '等级',
losal INT COMMENT '最低薪资',
hisal INT COMMENT '最高薪资'
) COMMENT='薪资等级表';
INSERT INTO salgrade VALUES
(1,0,5000),(2,5001,10000),(3,10001,15000),(4,15001,20000);
-- 题目一:复杂查询与优化
-- 1. CTE与中位数
-- 题目一:复杂查询与优化
-- 1. CTE与中位数
WITH dept_stats AS (
SELECT
d.id AS dept_id,
d.name AS dept_name,
AVG(e.salary) AS avg_salary,
(
SELECT e2.entrydate
FROM (
SELECT entrydate, @rownum := @rownum + 1 AS row_num
FROM emp e2, (SELECT @rownum := 0) r
WHERE e2.dept_id = d.id
ORDER BY entrydate
) e2
WHERE e2.row_num = (
SELECT CEILING(COUNT(*) / 2)
FROM emp e3
WHERE e3.dept_id = d.id
)
) AS med_entry
FROM dept d
LEFT JOIN emp e ON d.id = e.dept_id
GROUP BY d.id, d.name
)
-- 查询薪资高于部门平均且入职日期早于中位数的员工
SELECT
ds.dept_name AS '部门名称',
e.name AS '员工姓名',
e.salary AS '薪资',
e.entrydate AS '入职日期'
FROM emp e
JOIN dept_stats ds ON e.dept_id = ds.dept_id
WHERE e.salary > ds.avg_salary AND e.entrydate < ds.med_entry;
-- 2. 性能分析与优化
-- 原始执行计划
EXPLAIN FORMAT=JSON
SELECT * FROM emp WHERE dept_id = 1 AND salary > 10000;
-- 优化1: 创建覆盖索引
ALTER TABLE emp ADD INDEX idx_emp_dept_salary(dept_id, salary);
-- 优化2: 创建物化视图
CREATE VIEW emp_dept_stats AS
SELECT
d.id AS dept_id,
d.name AS dept_name,
COUNT(e.id) AS emp_count,
AVG(e.salary) AS avg_salary,
MAX(e.salary) AS max_salary,
MIN(e.salary) AS min_salary
FROM dept d
LEFT JOIN emp e ON d.id = e.dept_id
GROUP BY d.id, d.name;
-- 优化后执行计划
EXPLAIN FORMAT=JSON
SELECT * FROM emp WHERE dept_id = 1 AND salary > 10000;
-- 题目二:窗口函数与分区分析
-- 1. 窗口函数排名 -- Student:2023123456张三
WITH dept_top3 AS (
SELECT
d.name AS dept_name,
e.name AS emp_name,
e.salary,
ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS salary_rank
FROM emp e
JOIN dept d ON e.dept_id = d.id
)
SELECT
dept_name AS '部门名称',
MAX(salary) - MIN(salary) AS '薪资差距'
FROM dept_top3
WHERE salary_rank <= 3
GROUP BY dept_name;
-- 2. 分区表设计 -- Student:2023123456张三
-- 创建分区表
DROP TABLE IF EXISTS emp_partitioned;
CREATE TABLE emp_partitioned(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
name VARCHAR(50) NOT NULL COMMENT '姓名',
age TINYINT COMMENT '年龄',
job VARCHAR(20) COMMENT '职位',
salary INT COMMENT '薪资',
entrydate DATE COMMENT '入职日期',
managerid INT COMMENT '直属上司ID',
dept_id INT COMMENT '部门ID',
INDEX idx_emp_dept(dept_id),
INDEX idx_emp_salary(salary)
) COMMENT='员工表(分区)'
PARTITION BY RANGE (YEAR(entrydate)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 加载数据到分区表
INSERT INTO emp_partitioned SELECT * FROM emp;
-- 验证分区裁剪
EXPLAIN SELECT * FROM emp_partitioned WHERE entrydate BETWEEN '2020-01-01' AND '2020-12-31';
