网硕互联帮助中心从零到精通：MySQL 8.0企业级部署与SQL基础实战全攻略
在云原生时代，数据库运维能力是每个后端工程师和DBA的必备技能。本文将带你在一台全新的Ubuntu 24.04服务器上，从零开始完成MySQL 8.0的部署安装、安全配置，并通过一个完整的员工管理系统案例，实战演练SQL基础操作的方方面面——从建库建表到复杂查询，从索引优化到事务控制，从视图到存储过程，一网打尽。
一、环境介绍
服务器配置
| 操作系统 | Ubuntu 24.04.4 LTS |
| MySQL版本 | 8.0.46 (官方apt源) |
| 服务器IP | 1.94.229.94 (公网) / 192.168.0.145 (私有) |
| CPU核心 | 8核 |
| 内存 | 14GB |
| 磁盘 | 40GB (可用34GB) |
| 主机名 | ecs-489e-510b-0001 |
环境预检查
# 查看系统版本
root@ecs-489e-510b-0001:~# cat /etc/os-release | head -3
PRETTY_NAME="Ubuntu 24.04.4 LTS"
NAME="Ubuntu"
# 查看内存
root@ecs-489e-510b-0001:~# free -h
total used free shared buff/cache available
Mem: 14Gi 530Mi 14Gi 2.5Mi 465Mi 14Gi
# 查看CPU核心数
root@ecs-489e-510b-0001:~# nproc
8
二、MySQL 8.0安装部署
2.1 更新软件源
root@ecs-489e-510b-0001:~# apt-get update -y
Get:45 http://repo.huaweicloud.com/ubuntu noble-security/multiverse Translation-en [11.1 kB]
Fetched 11.3 MB in 5s (2,213 kB/s)
Reading package lists... Done
2.2 安装MySQL Server
root@ecs-489e-510b-0001:~# DEBIAN_FRONTEND=noninteractive apt-get install -y mysql-server
Setting up mysql-server (8.0.46-0ubuntu0.24.04.4) ...
Setting up libcgi-fast-perl (1:2.17-1) ...
Processing triggers for man-db (2.12.0-4build2) ...
安装完成后验证版本：
root@ecs-489e-510b-0001:~# mysql –version
mysql Ver 8.0.46-0ubuntu0.24.04.4 for Linux on x86_64 ((Ubuntu))
2.3 启动并设置开机自启
root@ecs-489e-510b-0001:~# systemctl start mysql
root@ecs-489e-510b-0001:~# systemctl enable mysql
Synchronizing state of mysql.service with SysV service script with /usr/lib/systemd/systemd-sysv-install.
Executing: /usr/lib/systemd/systemd-sysv-install enable mysql
root@ecs-489e-510b-0001:~# systemctl status mysql
● mysql.service – MySQL Community Server
Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
Active: active (running) since Sun 2026-09-06 01:52:48 CST; 6s ago
Main PID: 8213 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 18060)
Memory: 363.8M (peak: 378.7M)
2.4 配置root用户与远程访问
Ubuntu 24.04安装的MySQL 8.0默认root用户使用auth_socket插件，只能本地免密登录。我们需要切换为mysql_native_password并设置密码，同时允许远程连接：
— 设置root密码并切换认证插件
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1qaz@WSX';
— 创建允许远程连接的root用户
CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1qaz@WSX';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
2.5 修改bind-address允许远程连接
编辑/etc/mysql/mysql.conf.d/mysqld.cnf，将bind-address从127.0.0.1改为0.0.0.0：
# 修改前
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
# 修改后
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0
重启MySQL并验证：
root@ecs-489e-510b-0001:~# systemctl restart mysql
root@ecs-489e-510b-0001:~# mysql -u root -p'1qaz@WSX' -e "SELECT VERSION();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+———–+
| VERSION() |
+———–+
| 8.0.46 |
+———–+
root@ecs-489e-510b-0001:~# ss -tlnp | grep 3306
LISTEN 0 151 0.0.0.0:3306 0.0.0.0:* users:(("mysqld",pid=8213,fd=23))
2.6 数据目录检查
root@ecs-489e-510b-0001:~# ls -la /var/lib/mysql/ | head -20
-rw-r—– 1 mysql mysql 56 Sep 6 01:53 auto.cnf
-rw-r—– 1 mysql mysql 180 Sep 6 01:53 binlog.000001
-rw-r—– 1 mysql mysql 404 Sep 6 01:53 binlog.000002
-rw-r—– 1 mysql mysql 1169 Sep 6 01:54 binlog.000003
-rw-r—– 1 mysql mysql 157 Sep 6 01:54 binlog.000004
-rw-r—– 1 mysql mysql 3500 Sep 6 01:54 ib_buffer_pool
-rw-r—– 1 mysql mysql 12582912 Sep 6 01:54 ibdata1
-rw-r—– 1 mysql mysql 12582912 Sep 6 01:54 ibtmp1
root@ecs-489e-510b-0001:~# df -h /var/lib/mysql/
Filesystem Size Used Avail Use% Mounted on
/dev/vda1 40G 3.8G 34G 11% /
三、SQL基础实操
3.1 创建数据库
mysql> CREATE DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.01 sec)
mysql> SHOW DATABASES;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+——————–+
5 rows in set (0.00 sec)
3.2 创建员工表
USE testdb;
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
department VARCHAR(30) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
hire_date DATE NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> DESC employees;
+————+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————+————–+——+—–+———+—————-+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| age | int | NO | | NULL | |
| department | varchar(30) | NO | | NULL | |
| salary | decimal(10,2)| NO | | NULL | |
| hire_date | date | NO | | NULL | |
+————+————–+——+—–+———+—————-+
3.3 插入20条测试数据
INSERT INTO employees (name, age, department, salary, hire_date) VALUES
('张伟', 28, '技术部', 15000.00, '2022-03-15'),
('李娜', 32, '市场部', 12000.00, '2021-06-20'),
('王芳', 25, '技术部', 13000.00, '2023-01-10'),
('刘洋', 35, '管理部', 25000.00, '2019-08-01'),
('陈静', 29, '人事部', 11000.00, '2022-09-15'),
('杨光', 31, '技术部', 18000.00, '2020-04-18'),
('赵敏', 27, '市场部', 11500.00, '2023-03-22'),
('黄磊', 33, '技术部', 20000.00, '2019-11-05'),
('周婷', 26, '人事部', 10500.00, '2023-07-01'),
('吴强', 38, '管理部', 30000.00, '2018-02-14'),
('郑爽', 24, '技术部', 12500.00, '2023-09-10'),
('孙杰', 30, '市场部', 13500.00, '2021-12-01'),
('马丽', 28, '人事部', 10800.00, '2022-05-18'),
('朱涛', 36, '技术部', 22000.00, '2018-07-20'),
('胡燕', 29, '市场部', 12800.00, '2022-02-28'),
('林峰', 34, '管理部', 28000.00, '2019-05-15'),
('何秀', 25, '技术部', 13200.00, '2023-04-10'),
('高勇', 31, '市场部', 14000.00, '2020-10-08'),
('罗静', 27, '人事部', 11200.00, '2023-02-14'),
('谢明', 37, '技术部', 24000.00, '2018-09-25');
mysql> SELECT COUNT(*) AS total FROM employees;
+——-+
| total |
+——-+
| 20 |
+——-+
3.4 基础查询与条件查询
全表查询：
mysql> SELECT * FROM employees;
+—-+——+—–+———–+———-+————+
| id | name | age | department| salary | hire_date |
+—-+——+—–+———–+———-+————+
| 1 | 张伟 | 28 | 技术部 | 15000.00 | 2022–03–15 |
| 2 | 李娜 | 32 | 市场部 | 12000.00 | 2021–06–20 |
| 3 | 王芳 | 25 | 技术部 | 13000.00 | 2023–01–10 |
| 4 | 刘洋 | 35 | 管理部 | 25000.00 | 2019–08–01 |
| 5 | 陈静 | 29 | 人事部 | 11000.00 | 2022–09–15 |
| 6 | 杨光 | 31 | 技术部 | 18000.00 | 2020–04–18 |
| 7 | 赵敏 | 27 | 市场部 | 11500.00 | 2023–03–22 |
| 8 | 黄磊 | 33 | 技术部 | 20000.00 | 2019–11–05 |
| 9 | 周婷 | 26 | 人事部 | 10500.00 | 2023–07–01 |
| 10 | 吴强 | 38 | 管理部 | 30000.00 | 2018–02–14 |
| 11 | 郑爽 | 24 | 技术部 | 12500.00 | 2023–09–10 |
| 12 | 孙杰 | 30 | 市场部 | 13500.00 | 2021–12–01 |
| 13 | 马丽 | 28 | 人事部 | 10800.00 | 2022–05–18 |
| 14 | 朱涛 | 36 | 技术部 | 22000.00 | 2018–07–20 |
| 15 | 胡燕 | 29 | 市场部 | 12800.00 | 2022–02–28 |
| 16 | 林峰 | 34 | 管理部 | 28000.00 | 2019–05–15 |
| 17 | 何秀 | 25 | 技术部 | 13200.00 | 2023–04–10 |
| 18 | 高勇 | 31 | 市场部 | 14000.00 | 2020–10–08 |
| 19 | 罗静 | 27 | 人事部 | 11200.00 | 2023–02–14 |
| 20 | 谢明 | 37 | 技术部 | 24000.00 | 2018–09–25 |
+—-+——+—–+———–+———-+————+
20 rows in set (0.00 sec)
条件查询——薪资大于15000的员工：
mysql> SELECT * FROM employees WHERE salary > 15000;
+—-+——+—–+———–+———-+————+
| id | name | age | department| salary | hire_date |
+—-+——+—–+———–+———-+————+
| 4 | 刘洋 | 35 | 管理部 | 25000.00 | 2019–08–01 |
| 6 | 杨光 | 31 | 技术部 | 18000.00 | 2020–04–18 |
| 8 | 黄磊 | 33 | 技术部 | 20000.00 | 2019–11–05 |
| 10 | 吴强 | 38 | 管理部 | 30000.00 | 2018–02–14 |
| 14 | 朱涛 | 36 | 技术部 | 22000.00 | 2018–07–20 |
| 16 | 林峰 | 34 | 管理部 | 28000.00 | 2019–05–15 |
| 20 | 谢明 | 37 | 技术部 | 24000.00 | 2018–09–25 |
+—-+——+—–+———–+———-+————+
7 rows in set (0.00 sec)
多条件查询——技术部且年龄大于30岁：
mysql> SELECT * FROM employees WHERE department='技术部' AND age > 30;
+—-+——+—–+———–+———-+————+
| id | name | age | department| salary | hire_date |
+—-+——+—–+———–+———-+————+
| 6 | 杨光 | 31 | 技术部 | 18000.00 | 2020–04–18 |
| 8 | 黄磊 | 33 | 技术部 | 20000.00 | 2019–11–05 |
| 14 | 朱涛 | 36 | 技术部 | 22000.00 | 2018–07–20 |
| 20 | 谢明 | 37 | 技术部 | 24000.00 | 2018–09–25 |
+—-+——+—–+———–+———-+————+
4 rows in set (0.00 sec)
BETWEEN、IN、LIKE查询：
— 年龄在25到30岁之间
mysql> SELECT * FROM employees WHERE age BETWEEN 25 AND 30;
— 返回10条记录
— 技术部和管理部员工
mysql> SELECT * FROM employees WHERE department IN ('技术部','管理部');
— 返回11条记录
— 姓"张"的员工
mysql> SELECT * FROM employees WHERE name LIKE '张%';
+—-+——+—–+———–+———-+————+
| id | name | age | department| salary | hire_date |
+—-+——+—–+———–+———-+————+
| 1 | 张伟 | 28 | 技术部 | 15000.00 | 2022–03–15 |
+—-+——+—–+———–+———-+————+
3.5 聚合函数与GROUP BY
全局聚合统计：
mysql> SELECT COUNT(*) AS total, AVG(salary) AS avg_salary,
–> MAX(salary) AS max_salary, MIN(salary) AS min_salary,
–> SUM(salary) AS total_salary FROM employees;
+——-+————+————+————+————–+
| total | avg_salary | max_salary | min_salary | total_salary |
+——-+————+————+————+————–+
| 20 | 16315.0000 | 30000.00 | 10500.00 | 326300.00 |
+——-+————+————+————+————–+
按部门分组统计：
mysql> SELECT department, COUNT(*) AS count, AVG(salary) AS avg_salary,
–> SUM(salary) AS total_salary FROM employees GROUP BY department;
+———–+——-+————+————–+
| department| count | avg_salary | total_salary |
+———–+——-+————+————–+
| 技术部 | 8 | 16837.5000 | 134700.00 |
| 市场部 | 5 | 12760.0000 | 63800.00 |
| 管理部 | 3 | 27666.6667 | 83000.00 |
| 人事部 | 4 | 10875.0000 | 43500.00 |
+———–+——-+————+————–+
HAVING过滤分组——人数大于3的部门：
mysql> SELECT department, COUNT(*) AS count
–> FROM employees GROUP BY department HAVING COUNT(*) > 3;
+———–+——-+
| department| count |
+———–+——-+
| 技术部 | 8 |
| 市场部 | 5 |
| 人事部 | 4 |
+———–+——-+
排序与分页：
— 薪资Top 5
mysql> SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
+—-+——+—–+———–+———-+————+
| id | name | age | department| salary | hire_date |
+—-+——+—–+———–+———-+————+
| 10 | 吴强 | 38 | 管理部 | 30000.00 | 2018–02–14 |
| 16 | 林峰 | 34 | 管理部 | 28000.00 | 2019–05–15 |
| 4 | 刘洋 | 35 | 管理部 | 25000.00 | 2019–08–01 |
| 20 | 谢明 | 37 | 技术部 | 24000.00 | 2018–09–25 |
| 14 | 朱涛 | 36 | 技术部 | 22000.00 | 2018–07–20 |
+—-+——+—–+———–+———-+————+
3.6 多表JOIN查询
首先创建部门表：
CREATE TABLE departments (
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(30) NOT NULL,
manager VARCHAR(50),
location VARCHAR(50),
budget DECIMAL(12,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO departments (dept_name, manager, location, budget) VALUES
('技术部', '刘洋', '北京', 500000.00),
('市场部', '李娜', '上海', 300000.00),
('管理部', '吴强', '北京', 800000.00),
('人事部', '陈静', '广州', 200000.00);
INNER JOIN——关联员工与部门：
mysql> SELECT e.name, e.salary, d.dept_name, d.location
–> FROM employees e INNER JOIN departments d
–> ON e.department = d.dept_name;
+——+——+———–+———-+
| name |salary| dept_name | location |
+——+——+———–+———-+
| 张伟 |15000 | 技术部 | 北京 |
| 李娜 |12000 | 市场部 | 上海 |
| 王芳 |13000 | 技术部 | 北京 |
| 刘洋 |25000 | 管理部 | 北京 |
| 陈静 |11000 | 人事部 | 广州 |
| 杨光 |18000 | 技术部 | 北京 |
| 赵敏 |11500 | 市场部 | 上海 |
| 黄磊 |20000 | 技术部 | 北京 |
| 周婷 |10500 | 人事部 | 广州 |
| 吴强 |30000 | 管理部 | 北京 |
| 郑爽 |12500 | 技术部 | 北京 |
| 孙杰 |13500 | 市场部 | 上海 |
| 马丽 |10800 | 人事部 | 广州 |
| 朱涛 |22000 | 技术部 | 北京 |
| 胡燕 |12800 | 市场部 | 上海 |
| 林峰 |28000 | 管理部 | 北京 |
| 何秀 |13200 | 技术部 | 北京 |
| 高勇 |14000 | 市场部 | 上海 |
| 罗静 |11200 | 人事部 | 广州 |
| 谢明 |24000 | 技术部 | 北京 |
+——+——+———–+———-+
20 rows in set (0.00 sec)
LEFT JOIN + GROUP BY——部门人数与平均薪资统计：
mysql> SELECT d.dept_name, d.manager, COUNT(e.id) AS emp_count, AVG(e.salary) AS avg_salary
–> FROM departments d LEFT JOIN employees e ON d.dept_name = e.department
–> GROUP BY d.dept_name, d.manager;
+———–+——–+———–+————+
| dept_name | manager| emp_count | avg_salary |
+———–+——–+———–+————+
| 技术部 | 刘洋 | 8 | 16837.5000 |
| 市场部 | 李娜 | 5 | 12760.0000 |
| 管理部 | 吴强 | 3 | 27666.6667 |
| 人事部 | 陈静 | 4 | 10875.0000 |
+———–+——–+———–+————+
3.7 子查询
查询高于平均薪资的员工：
mysql> SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
+—-+——+—–+———–+———-+————+
| id | name | age | department| salary | hire_date |
+—-+——+—–+———–+———-+————+
| 1 | 张伟 | 28 | 技术部 | 15000.00 | 2022–03–15 |
| 4 | 刘洋 | 35 | 管理部 | 25000.00 | 2019–08–01 |
| 6 | 杨光 | 31 | 技术部 | 18000.00 | 2020–04–18 |
| 8 | 黄磊 | 33 | 技术部 | 20000.00 | 2019–11–05 |
| 10 | 吴强 | 38 | 管理部 | 30000.00 | 2018–02–14 |
| 12 | 孙杰 | 30 | 市场部 | 13500.00 | 2021–12–01 |
| 14 | 朱涛 | 36 | 技术部 | 22000.00 | 2018–07–20 |
| 15 | 胡燕 | 29 | 市场部 | 12800.00 | 2022–02–28 |
| 16 | 林峰 | 34 | 管理部 | 28000.00 | 2019–05–15 |
| 18 | 高勇 | 31 | 市场部 | 14000.00 | 2020–10–08 |
| 20 | 谢明 | 37 | 技术部 | 24000.00 | 2018–09–25 |
+—-+——+—–+———–+———-+————+
11 rows in set (0.00 sec)
相关子查询——各部门薪资最高的员工：
mysql> SELECT e1.name, e1.salary FROM employees e1
–> WHERE e1.salary = (SELECT MAX(e2.salary) FROM employees e2 WHERE e2.department = e1.department);
+——+———-+
| name | salary |
+——+———-+
| 吴强 | 30000.00 |
| 谢明 | 24000.00 |
| 高勇 | 14000.00 |
| 陈静 | 11000.00 |
+——+———-+
4 rows in set (0.00 sec)
3.8 索创建与EXPLAIN执行计划分析
创建索引：
— 单列索引
CREATE INDEX idx_department ON employees(department);
CREATE INDEX idx_salary ON employees(salary);
— 复合索引
CREATE INDEX idx_dept_salary ON employees(department, salary);
mysql> SHOW INDEX FROM employees;
+———–+—————–+—————–+————–+
| Table | Key_name | Seq_in_index | Column_name |
+———–+—————–+—————–+————–+
| employees | PRIMARY | 1 | id |
| employees | idx_department | 1 | department |
| employees | idx_salary | 1 | salary |
| employees | idx_dept_salary | 1 | department |
| employees | idx_dept_salary | 2 | salary |
+———–+—————–+—————–+————–+
EXPLAIN分析——有索引的查询：
mysql> EXPLAIN SELECT * FROM employees WHERE department='技术部';
+—-+————-+———–+——-+———————+———————+
| id | select_type | table | type | possible_keys | key |
+—-+————-+———–+——-+———————+———————+
| 1 | SIMPLE | employees | ref | idx_department,... | idx_department |
+—-+————-+———–+——-+———————+———————+
— type=ref，使用索引查找，效率高
EXPLAIN分析——复合索引查询：
mysql> EXPLAIN SELECT * FROM employees WHERE department='技术部' AND salary > 15000;
+—-+————-+———–+——-+———————+———————+
| id | select_type | table | type | possible_keys | key |
+—-+————-+———–+——-+———————+———————+
| 1 | SIMPLE | employees | range | idx_dept_salary,... | idx_dept_salary |
+—-+————-+———–+——-+———————+———————+
— type=range，使用复合索引范围扫描
EXPLAIN分析——无索引的全表扫描：
mysql> EXPLAIN SELECT * FROM employees WHERE age > 30;
+—-+————-+———–+——+—————+——+
| id | select_type | table | type | possible_keys | key |
+—-+————-+———–+——+—————+——+
| 1 | SIMPLE | employees | ALL | NULL | NULL |
+—-+————-+———–+——+—————+——+
— type=ALL，全表扫描，需要为age字段添加索引
优化建议：当type列为ALL时表示全表扫描，应考虑添加索引。ref和range是较为理想的访问类型。
3.9 事务操作演示
COMMIT提交演示：
— 查看初始值
mysql> SELECT name, salary FROM employees WHERE id=1;
+——+———-+
| name | salary |
+——+———-+
| 张伟 | 15000.00 |
+——+———-+
— 开启事务，加薪1000
mysql> BEGIN;
mysql> UPDATE employees SET salary=salary+1000 WHERE id=1;
mysql> SELECT name, salary FROM employees WHERE id=1;
+——+———-+
| name | salary |
+——+———-+
| 张伟 | 16000.00 |
+——+———-+
— 提交事务
mysql> COMMIT;
— 验证：数据已永久修改
mysql> SELECT name, salary FROM employees WHERE id=1;
+——+———-+
| name | salary |
+——+———-+
| 张伟 | 16000.00 |
+——+———-+
ROLLBACK回滚演示：
— 开启事务，减薪5000
mysql> BEGIN;
mysql> UPDATE employees SET salary=salary–5000 WHERE id=1;
mysql> SELECT name, salary FROM employees WHERE id=1;
+——+———-+
| name | salary |
+——+———-+
| 张伟 | 11000.00 |
+——+———-+
— 回滚事务
mysql> ROLLBACK;
— 验证：数据恢复到事务前
mysql> SELECT name, salary FROM employees WHERE id=1;
+——+———-+
| name | salary |
+——+———-+
| 张伟 | 16000.00 |
+——+———-+
要点：BEGIN开启事务后，修改不会立即生效。COMMIT使修改永久生效，ROLLBACK撤销所有修改。这是保证数据一致性的核心机制。
3.10 视图创建与使用
创建高薪员工视图：
CREATE VIEW v_high_salary_employees AS
SELECT name, department, salary, hire_date
FROM employees WHERE salary > 15000;
mysql> SELECT * FROM v_high_salary_employees;
+——+———–+———-+————+
| name | department| salary | hire_date |
+——+———–+———-+————+
| 刘洋 | 管理部 | 25000.00 | 2019–08–01 |
| 杨光 | 技术部 | 18000.00 | 2020–04–18 |
| 黄磊 | 技术部 | 20000.00 | 2019–11–05 |
| 吴强 | 管理部 | 30000.00 | 2018–02–14 |
| 朱涛 | 技术部 | 22000.00 | 2018–07–20 |
| 林峰 | 管理部 | 28000.00 | 2019–05–15 |
| 谢明 | 技术部 | 24000.00 | 2018–09–25 |
+——+———–+———-+————+
7 rows in set (0.00 sec)
创建部门汇总视图：
CREATE VIEW v_dept_summary AS
SELECT d.dept_name, d.manager, d.location,
COUNT(e.id) AS emp_count, AVG(e.salary) AS avg_salary, SUM(e.salary) AS total_salary
FROM departments d LEFT JOIN employees e ON d.dept_name = e.department
GROUP BY d.dept_name, d.manager, d.location;
mysql> SELECT * FROM v_dept_summary;
+———–+——–+———-+———–+————+————–+
| dept_name | manager| location | emp_count | avg_salary | total_salary |
+———–+——–+———-+———–+————+————–+
| 技术部 | 刘洋 | 北京 | 8 | 16837.5000 | 134700.00 |
| 市场部 | 李娜 | 上海 | 5 | 12760.0000 | 63800.00 |
| 管理部 | 吴强 | 北京 | 3 | 27666.6667 | 83000.00 |
| 人事部 | 陈静 | 广州 | 4 | 10875.0000 | 43500.00 |
+———–+——–+———-+———–+————+————–+
3.11 存储过程创建与调用
创建按部门查询员工的存储过程：
DELIMITER //
CREATE PROCEDURE sp_get_emp_by_dept(IN dept_name VARCHAR(30))
BEGIN
SELECT * FROM employees WHERE department = dept_name ORDER BY salary DESC;
END //
DELIMITER ;
— 调用：查询技术部员工
mysql> CALL sp_get_emp_by_dept('技术部');
+—-+——+—–+———–+———-+————+
| id | name | age | department| salary | hire_date |
+—-+——+—–+———–+———-+————+
| 20 | 谢明 | 37 | 技术部 | 24000.00 | 2018–09–25 |
| 14 | 朱涛 | 36 | 技术部 | 22000.00 | 2018–07–20 |
| 8 | 黄磊 | 33 | 技术部 | 20000.00 | 2019–11–05 |
| 6 | 杨光 | 31 | 技术部 | 18000.00 | 2020–04–18 |
| 1 | 张伟 | 28 | 技术部 | 16000.00 | 2022–03–15 |
| 17 | 何秀 | 25 | 技术部 | 13200.00 | 2023–04–10 |
| 3 | 王芳 | 25 | 技术部 | 13000.00 | 2023–01–10 |
| 11 | 郑爽 | 24 | 技术部 | 12500.00 | 2023–09–10 |
+—-+——+—–+———–+———-+————+
8 rows in set (0.00 sec)
创建部门统计存储过程：
DELIMITER //
CREATE PROCEDURE sp_dept_stats()
BEGIN
SELECT department, COUNT(*) AS emp_count,
AVG(salary) AS avg_sal, MAX(salary) AS max_sal, MIN(salary) AS min_sal
FROM employees GROUP BY department;
END //
DELIMITER ;
mysql> CALL sp_dept_stats();
+———–+———–+————+———-+———-+
| department | emp_count | avg_sal | max_sal | min_sal |
+———–+———–+————+———-+———-+
| 技术部 | 8 | 16837.5000 | 24000.00 | 12500.00 |
| 市场部 | 5 | 12760.0000 | 14000.00 | 11500.00 |
| 管理部 | 3 | 27666.6667 | 30000.00 | 25000.00 |
| 人事部 | 4 | 10875.0000 | 11200.00 | 10500.00 |
+———–+———–+————+———-+———-+
创建调薪存储过程：
DELIMITER //
CREATE PROCEDURE sp_update_salary(IN emp_id INT, IN increase DECIMAL(10,2))
BEGIN
DECLARE old_sal DECIMAL(10,2);
SELECT salary INTO old_sal FROM employees WHERE id = emp_id;
UPDATE employees SET salary = salary + increase WHERE id = emp_id;
SELECT emp_id AS employee_id, old_sal AS old_salary,
old_sal + increase AS new_salary, increase AS increase_amount;
END //
DELIMITER ;
— 调用：给ID=2的员工加薪2000
mysql> CALL sp_update_salary(2, 2000);
+————-+————+————+—————-+
| employee_id | old_salary | new_salary | increase_amount|
+————-+————+————+—————-+
| 2 | 12000.00 | 14000.00 | 2000.00 |
+————-+————+————+—————-+
3.12 最终数据汇总
mysql> SELECT 'employees' AS table_name, COUNT(*) AS record_count FROM employees
–> UNION ALL
–> SELECT 'departments', COUNT(*) FROM departments;
+————+————–+
| table_name | record_count |
+————+————–+
| employees | 20 |
| departments| 4 |
+————+————–+
四、总结
本文在一台Ubuntu 24.04服务器上完成了以下工作：
| 1 | MySQL 8.0.46安装部署 | ✅ 完成 |
| 2 | root用户配置与远程访问 | ✅ 完成 |
| 3 | 创建数据库与员工表 | ✅ 完成 |
| 4 | 插入20条测试数据 | ✅ 完成 |
| 5 | 基础查询、条件查询、聚合函数 | ✅ 完成 |
| 6 | GROUP BY、ORDER BY、LIMIT | ✅ 完成 |
| 7 | 多表JOIN查询 | ✅ 完成 |
| 8 | 子查询（含相关子查询） | ✅ 完成 |
| 9 | 索引创建与EXPLAIN分析 | ✅ 完成 |
| 10 | 事务操作（COMMIT/ROLLBACK） | ✅ 完成 |
| 11 | 视图创建与使用 | ✅ 完成 |
| 12 | 存储过程创建与调用 | ✅ 完成 |
关键收获：
安装要点：Ubuntu 24.04通过apt安装MySQL 8.0非常简单，但需注意默认root用户使用auth_socket插件，需手动切换为mysql_native_password才能使用密码登录和远程连接。
SQL基础：从DDL（建库建表）到DML（增删改查），从简单查询到多表关联、子查询，SQL是数据操作的核心语言。掌握聚合函数、分组排序、分页查询是基本功。
索引优化：通过EXPLAIN分析执行计划，可以直观看到查询是否使用了索引。type列为ALL表示全表扫描，应添加索引优化；ref和range是较为理想的访问类型。
事务控制：BEGIN/COMMIT/ROLLBACK是保证数据一致性的核心机制。在金融、电商等场景中，事务是不可或缺的。
高级对象：视图可以简化复杂查询、提供数据安全隔离；存储过程可以封装业务逻辑、减少网络传输、提高执行效率。
本文所有操作均在真实服务器上执行，所有输出均为真实记录。下一篇将讲解MySQL主从复制搭建，敬请关注。
评论前必须登录！
注册