MySQL多表查詢、事務與索引的實踐與應用操作
更新時間：2025年04月10日 09:54:01 作者：碼銀
本文圍繞MySQL數據庫操作展開,通過構建部門與員工管理、餐飲業(yè)務相關的數據庫表,并填充測試數據,系統(tǒng)地闡述了多表查詢的多種方式,包括內連接、外連接和不同類型的子查詢,同時介紹了事務的處理以及索引的創(chuàng)建、查詢和刪除操作,感興趣的朋友一起看看吧
摘要：本文圍繞MySQL數據庫操作展開，通過構建部門與員工管理、餐飲業(yè)務相關的數據庫表，并填充測試數據，系統(tǒng)地闡述了多表查詢的多種方式，包括內連接、外連接和不同類型的子查詢，同時介紹了事務的處理以及索引的創(chuàng)建、查詢和刪除操作。
關鍵詞：MySQL；多表查詢；事務；索引
一、引言
在數據庫管理與開發(fā)過程中，多表查詢、事務管理以及索引優(yōu)化是提升數據處理效率和數據完整性的關鍵技術。本文通過實際案例詳細展示這些技術在MySQL數據庫中的具體應用。
二、數據準備
2.1 部門與員工表的創(chuàng)建及數據插入
部門表（tb_dept）：用于存儲部門相關信息。
CREATE TABLE tb_dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主鍵ID',
name VARCHAR(10) NOT NULL UNIQUE COMMENT '部門名稱',
create_time DATETIME NOT NULL COMMENT '創(chuàng)建時間',
update_time DATETIME NOT NULL COMMENT '修改時間'
) COMMENT '部門表';
INSERT INTO tb_dept (id, name, create_time, update_time) VALUES
(1, '學工部', NOW(), NOW()),
(2, '教研部', NOW(), NOW()),
(3, '咨詢部', NOW(), NOW()),
(4, '就業(yè)部', NOW(), NOW()),
(5, '人事部', NOW(), NOW());員工表（tb_emp）：通過 dept_id 與部門表關聯，記錄員工詳細信息。
CREATE TABLE tb_emp (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
username VARCHAR(20) NOT NULL UNIQUE COMMENT '用戶名',
password VARCHAR(32) DEFAULT '123456' COMMENT '密碼',
name VARCHAR(10) NOT NULL COMMENT '姓名',
gender TINYINT UNSIGNED NOT NULL COMMENT '性別, 說明: 1 男, 2 女',
image VARCHAR(300) COMMENT '圖像',
job TINYINT UNSIGNED COMMENT '職位, 說明: 1 班主任,2 講師, 3 學工主管, 4 教研主管, 5 咨詢師',
entrydate DATE COMMENT '入職時間',
dept_id INT UNSIGNED COMMENT '部門ID',
create_time DATETIME NOT NULL COMMENT '創(chuàng)建時間',
update_time DATETIME NOT NULL COMMENT '修改時間'
) COMMENT '員工表';
INSERT INTO tb_emp(id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time) VALUES
(1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000 - 01 - 01', 2, NOW(), NOW()),
(2, 'zhangwuji', '123456', '張無忌', 1, '2.jpg', 2, '2015 - 01 - 01', 2, NOW(), NOW()),
-- 省略部分插入數據
(17, 'chenyouliang', '123456', '陳友諒', 1, '17.jpg', NULL, '2015 - 03 - 21', NULL, NOW(), NOW());2.2 餐飲業(yè)務相關表的創(chuàng)建及數據插入
分類表（category）：區(qū)分菜品分類與套餐分類。
CREATE TABLE category(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主鍵ID',
name VARCHAR(20) NOT NULL UNIQUE COMMENT '分類名稱',
type TINYINT UNSIGNED NOT NULL COMMENT '類型 1 菜品分類 2 套餐分類',
sort TINYINT UNSIGNED NOT NULL COMMENT '順序',
status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '狀態(tài) 0 禁用，1 啟用',
create_time DATETIME NOT NULL COMMENT '創(chuàng)建時間',
update_time DATETIME NOT NULL COMMENT '更新時間'
) COMMENT '分類';菜品表（dish）：記錄菜品的各項屬性，與分類表通過 category_id 關聯。
CREATE TABLE dish(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主鍵ID',
name VARCHAR(20) NOT NULL UNIQUE COMMENT '菜品名稱',
category_id INT UNSIGNED NOT NULL COMMENT '菜品分類ID',
price DECIMAL(8, 2) NOT NULL COMMENT '菜品價格',
image VARCHAR(300) NOT NULL COMMENT '菜品圖片',
description VARCHAR(200) COMMENT '描述信息',
status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '狀態(tài), 0 停售 1 起售',
create_time DATETIME NOT NULL COMMENT '創(chuàng)建時間',
update_time DATETIME NOT NULL COMMENT '更新時間'
) COMMENT '菜品';套餐表（setmeal）：存儲套餐信息，與分類表通過 category_id 關聯。
CREATE TABLE setmeal(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主鍵ID',
name VARCHAR(20) NOT NULL UNIQUE COMMENT '套餐名稱',
category_id INT UNSIGNED NOT NULL COMMENT '分類id',
price DECIMAL(8, 2) NOT NULL COMMENT '套餐價格',
image VARCHAR(300) NOT NULL COMMENT '圖片',
description VARCHAR(200) COMMENT '描述信息',
status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '狀態(tài) 0:停用 1:啟用',
create_time DATETIME NOT NULL COMMENT '創(chuàng)建時間',
update_time DATETIME NOT NULL COMMENT '更新時間'
) COMMENT '套餐';套餐菜品關聯表（setmeal_dish）：建立套餐與菜品之間的聯系。
CREATE TABLE setmeal_dish(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主鍵ID',
setmeal_id INT UNSIGNED NOT NULL COMMENT '套餐id ',
dish_id INT UNSIGNED NOT NULL COMMENT '菜品id',
copies TINYINT UNSIGNED NOT NULL COMMENT '份數'
) COMMENT '套餐菜品中間表';插入測試數據：向上述餐飲業(yè)務相關表插入大量測試數據，涵蓋各類菜品、套餐及其關聯信息。
三、多表查詢操作
3.1 基本多表查詢
通過連接 tb_emp 和 tb_dept 表，獲取員工所屬部門信息。
SELECT * FROM tb_emp, tb_dept WHERE tb_emp.dept_id = tb_dept.id;
3.2 內連接
隱式內連接：查詢員工姓名及所屬部門名稱，可通過起別名提高可讀性。
SELECT tb_emp.name, tb_dept.name FROM tb_emp, tb_dept WHERE tb_emp.dept_id = tb_dept.id; SELECT e.name, d.name FROM tb_emp e, tb_dept d WHERE e.dept_id = d.id;
顯式內連接：同樣實現查詢員工姓名及所屬部門名稱。
SELECT tb_emp.name, tb_dept.name FROM tb_emp JOIN tb_dept ON tb_emp.dept_id = tb_dept.id;
3.3 外連接
左外連接：獲取員工表所有員工姓名及對應的部門名稱，包括無部門員工。
SELECT e.name, d.name FROM tb_emp e LEFT JOIN tb_dept d ON e.dept_id = d.id;
右外連接：獲取部門表所有部門名稱及對應的員工名稱，包括無員工部門。
SELECT e.name, d.name FROM tb_emp e RIGHT JOIN tb_dept d ON e.dept_id = d.id; -- 等同于 SELECT e.name, d.name FROM tb_dept d LEFT JOIN tb_emp e ON e.dept_id = d.id;
3.4 子查詢
標量子查詢：
查詢“教研部”的所有員工信息，先獲取教研部 id，再查詢該部門員工。
SELECT id FROM tb_dept WHERE name = '教研部'; SELECT * FROM tb_emp WHERE dept_id = (SELECT id FROM tb_dept WHERE name = '教研部');
- 查詢在“方東白”入職之后的員工信息，先獲取方東白入職時間，再查詢晚于該時間入職的員工。
SELECT entrydate FROM tb_emp WHERE name = '方東白'; SELECT * FROM tb_emp WHERE entrydate > (SELECT entrydate FROM tb_emp WHERE name = '方東白');
列子查詢：查詢“教研部”和“咨詢部”的所有員工信息，先獲取兩個部門 id，再查詢對應部門員工。
SELECT id FROM tb_dept WHERE name = '教研部' OR name = '咨詢部'; SELECT * FROM tb_emp WHERE dept_id IN (SELECT id FROM tb_dept WHERE name = '教研部' OR name = '咨詢部');
行子查詢：查詢與“韋一笑”入職日期及職位都相同的員工信息，可通過兩種方式實現。
SELECT entrydate, job FROM tb_emp WHERE name = '韋一笑'; -- 方式一 SELECT * FROM tb_emp WHERE entrydate = (SELECT entrydate FROM tb_emp WHERE name = '韋一笑') AND job = (SELECT job FROM tb_emp WHERE name = '韋一笑'); -- 方式二 SELECT * FROM tb_emp WHERE (entrydate, job) = (SELECT entrydate, job FROM tb_emp WHERE name = '韋一笑');
表子查詢：查詢入職日期在“2006 - 01 - 01”之后的員工信息及其部門名稱，先獲取符合日期條件的員工，再連接部門表獲取部門名稱。
SELECT * FROM tb_emp WHERE entrydate > '2006 - 01 - 01'; SELECT e.*, d.name FROM (SELECT * FROM tb_emp WHERE entrydate > '2006 - 01 - 01') e, tb_dept d WHERE e.dept_id = d.id;
3.5 餐飲業(yè)務多表查詢需求
查詢低價菜品信息：獲取價格低于10元的菜品名稱、價格及分類名稱。
SELECT d.name, d.price, c.name FROM dish d, category c WHERE d.category_id = c.id AND d.price < 10;
查詢特定價格與狀態(tài)菜品信息：查詢價格在10元（含）到50元（含）之間且狀態(tài)為“起售”的菜品信息，包括無分類菜品。
SELECT d.name, d.price, c.name FROM dish d LEFT JOIN category c ON d.category_id = c.id WHERE d.price BETWEEN 10 AND 50 AND d.status = 1;
查詢各分類最貴菜品信息：展示每個分類下最貴菜品的分類名稱和價格。
SELECT c.name, MAX(d.price) FROM dish d, category c WHERE d.category_id = c.id GROUP BY c.name;
查詢特定條件分類名稱：獲取菜品狀態(tài)為“起售”且菜品數量大于等于3的分類名稱。
SELECT c.name, COUNT(*) FROM dish d, category c WHERE d.category_id = c.id AND d.status = 1 GROUP BY c.name HAVING COUNT(*) >= 3;
查詢套餐包含菜品信息：展示“商務套餐A”包含的菜品相關信息。
SELECT s.name, s.price, d.name, d.price, sd.copies FROM setmeal s, setmeal_dish sd, dish d WHERE s.id = sd.setmeal_id AND sd.dish_id = d.id AND s.name = '商務套餐A';
查詢低于平均價格菜品信息：先計算菜品平均價格，再查詢低于該平均價格的菜品。
SELECT AVG(price) FROM dish; SELECT * FROM dish WHERE price < (SELECT AVG(price) FROM dish);
四、事務操作
4.1 事務處理流程
在刪除部門及相關員工操作中，使用事務確保數據一致性。
-- 開啟事務 START TRANSACTION; -- 刪除部門 DELETE FROM tb_dept WHERE id = 2; -- 刪除部門下的員工 DELETE FROM tb_emp WHERE dept_id = 2; -- 提交事務 COMMIT; -- 回滾事務（若中途出錯） ROLLBACK; SELECT * FROM tb_dept; SELECT * FROM tb_emp;
五、索引操作
5.1 索引的創(chuàng)建、查詢與刪除
創(chuàng)建索引：為 tb_sku 表的 sn 字段和 tb_emp 表的 name 字段創(chuàng)建索引。
CREATE INDEX idx_sku_sn ON tb_sku(sn); CREATE INDEX idx_emp_name ON tb_emp(name);
查詢索引信息：查看 tb_emp 表的索引情況。
SHOW INDEX FROM tb_emp;
刪除索引：刪除 tb_emp 表中 name 字段的索引。
DROP INDEX idx_emp_name ON tb_emp;
到此這篇關于MySQL多表查詢、事務與索引的實踐與應用的文章就介紹到這了,更多相關MySQL多表查詢、事務內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了解決myBatis中刪除條件的拼接問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-11-11
MySQL分組的時候遇到ONLY_FULL_GROUP_BY報錯問題及解決方案
這篇文章主要介紹了MySQL分組的時候遇到ONLY_FULL_GROUP_BY報錯問題及解決方案,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2025-04-04
下面這段Linux的Shell腳本用于每日自動備份MySQL數據庫，可通過Linux的crontab每天定時執(zhí)行2013-11-11
如何獲取SqlServer2005表結構(字段,主鍵,外鍵,遞增,描述)
本篇文章是對如何獲取SqlServer2005表結構(字段,主鍵,外鍵,遞增,描述)的方法進行了詳細的分析介紹，需要的朋友參考下2013-06-06
本文解析MySQL TEXT類型存儲極限及工程實踐,本文通過實例代碼給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友參考下吧2022-10-10
如何在SQL Server中實現 Limit m,n 的功能
本篇文章是對在SQL Server中實現 Limit m,n功能的方法進行了詳細的分析介紹，需要的朋友參考下2013-06-06
Mysql調優(yōu)Explain工具詳解及實戰(zhàn)演練(推薦)
這篇文章主要介紹了Mysql調優(yōu)Explain工具詳解及實戰(zhàn)演練,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-03-03
MySQL中的日志包括：錯誤日志、二進制日志、通用查詢日志、慢查詢日志等等。這里主要介紹下比較常用的兩個功能：通用查詢日志和慢查詢日志,需要的朋友可以參考下2018-10-10
MySQL limit分頁大偏移量慢的原因及優(yōu)化方案
這篇文章主要介紹了MySQL limit分頁大偏移量慢的原因及優(yōu)化方案，幫助大家更好的理解和使用MySQL數據庫，感興趣的朋友可以了解下2020-11-11
本文介紹了如何查看MySQL數據庫中每個表的占用磁盤空間大小和行數,通過執(zhí)行特定的SQL查詢語句,可以實現對數據庫表的大小和行數的統(tǒng)計2026-05-05