```
-- 学号:2023123456 姓名:张三
SELECT '学号:2345313524, 姓名:吕倩倩' AS StudentInfo;
-- 创建数据库
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';
```请回答上述代码剧具体执行顺序精确到每一行是否执行或者不执行
最新发布
本周实习生开始尝试多种测试任务，如日文版预RC测试、改动测试等，通过这些任务培养了系统的测试思维，并且在实际工作中发现了一些bug。在进行改动测试时，需要提前考虑可能的影响范围，并仔细检查以避免遗漏。
扫一扫
1898
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
