MySQL多表查詢、事務(wù)與索引的實(shí)踐與應(yīng)用操作
摘要：本文圍繞MySQL數(shù)據(jù)庫(kù)操作展開(kāi)，通過(guò)構(gòu)建部門與員工管理、餐飲業(yè)務(wù)相關(guān)的數(shù)據(jù)庫(kù)表，并填充測(cè)試數(shù)據(jù)，系統(tǒng)地闡述了多表查詢的多種方式，包括內(nèi)連接、外連接和不同類型的子查詢，同時(shí)介紹了事務(wù)的處理以及索引的創(chuàng)建、查詢和刪除操作。
關(guān)鍵詞：MySQL；多表查詢；事務(wù)；索引
一、引言
在數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)過(guò)程中，多表查詢、事務(wù)管理以及索引優(yōu)化是提升數(shù)據(jù)處理效率和數(shù)據(jù)完整性的關(guān)鍵技術(shù)。本文通過(guò)實(shí)際案例詳細(xì)展示這些技術(shù)在MySQL數(shù)據(jù)庫(kù)中的具體應(yīng)用。
二、數(shù)據(jù)準(zhǔn)備
2.1 部門與員工表的創(chuàng)建及數(shù)據(jù)插入
部門表（tb_dept）：用于存儲(chǔ)部門相關(guān)信息。
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)建時(shí)間',
update_time DATETIME NOT NULL COMMENT '修改時(shí)間'
) COMMENT '部門表';
INSERT INTO tb_dept (id, name, create_time, update_time) VALUES
(1, '學(xué)工部', NOW(), NOW()),
(2, '教研部', NOW(), NOW()),
(3, '咨詢部', NOW(), NOW()),
(4, '就業(yè)部', NOW(), NOW()),
(5, '人事部', NOW(), NOW());員工表（tb_emp）：通過(guò) dept_id 與部門表關(guān)聯(lián)，記錄員工詳細(xì)信息。
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 '性別, 說(shuō)明: 1 男, 2 女',
image VARCHAR(300) COMMENT '圖像',
job TINYINT UNSIGNED COMMENT '職位, 說(shuō)明: 1 班主任,2 講師, 3 學(xué)工主管, 4 教研主管, 5 咨詢師',
entrydate DATE COMMENT '入職時(shí)間',
dept_id INT UNSIGNED COMMENT '部門ID',
create_time DATETIME NOT NULL COMMENT '創(chuàng)建時(shí)間',
update_time DATETIME NOT NULL COMMENT '修改時(shí)間'
) 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', '張無(wú)忌', 1, '2.jpg', 2, '2015 - 01 - 01', 2, NOW(), NOW()),
-- 省略部分插入數(shù)據(jù)
(17, 'chenyouliang', '123456', '陳友諒', 1, '17.jpg', NULL, '2015 - 03 - 21', NULL, NOW(), NOW());2.2 餐飲業(yè)務(wù)相關(guān)表的創(chuàng)建及數(shù)據(jù)插入
分類表（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)建時(shí)間',
update_time DATETIME NOT NULL COMMENT '更新時(shí)間'
) COMMENT '分類';菜品表（dish）：記錄菜品的各項(xiàng)屬性，與分類表通過(guò) category_id 關(guān)聯(lián)。
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 '菜品價(jià)格',
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)建時(shí)間',
update_time DATETIME NOT NULL COMMENT '更新時(shí)間'
) COMMENT '菜品';套餐表（setmeal）：存儲(chǔ)套餐信息，與分類表通過(guò) category_id 關(guān)聯(lián)。
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 '套餐價(jià)格',
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)建時(shí)間',
update_time DATETIME NOT NULL COMMENT '更新時(shí)間'
) COMMENT '套餐';套餐菜品關(guān)聯(lián)表（setmeal_dish）：建立套餐與菜品之間的聯(lián)系。
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 '份數(shù)'
) COMMENT '套餐菜品中間表';插入測(cè)試數(shù)據(jù)：向上述餐飲業(yè)務(wù)相關(guān)表插入大量測(cè)試數(shù)據(jù)，涵蓋各類菜品、套餐及其關(guān)聯(lián)信息。
三、多表查詢操作
3.1 基本多表查詢
通過(guò)連接 tb_emp 和 tb_dept 表，獲取員工所屬部門信息。
SELECT * FROM tb_emp, tb_dept WHERE tb_emp.dept_id = tb_dept.id;
3.2 內(nèi)連接
隱式內(nèi)連接：查詢員工姓名及所屬部門名稱，可通過(guò)起別名提高可讀性。
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;
顯式內(nèi)連接：同樣實(shí)現(xiàn)查詢員工姓名及所屬部門名稱。
SELECT tb_emp.name, tb_dept.name FROM tb_emp JOIN tb_dept ON tb_emp.dept_id = tb_dept.id;
3.3 外連接
左外連接：獲取員工表所有員工姓名及對(duì)應(yīng)的部門名稱，包括無(wú)部門員工。
SELECT e.name, d.name FROM tb_emp e LEFT JOIN tb_dept d ON e.dept_id = d.id;
右外連接：獲取部門表所有部門名稱及對(duì)應(yīng)的員工名稱，包括無(wú)員工部門。
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 子查詢
標(biāo)量子查詢：
查詢“教研部”的所有員工信息，先獲取教研部 id，再查詢?cè)摬块T員工。
SELECT id FROM tb_dept WHERE name = '教研部'; SELECT * FROM tb_emp WHERE dept_id = (SELECT id FROM tb_dept WHERE name = '教研部');
- 查詢?cè)?ldquo;方東白”入職之后的員工信息，先獲取方東白入職時(shí)間，再查詢晚于該時(shí)間入職的員工。
SELECT entrydate FROM tb_emp WHERE name = '方東白'; SELECT * FROM tb_emp WHERE entrydate > (SELECT entrydate FROM tb_emp WHERE name = '方東白');
列子查詢：查詢“教研部”和“咨詢部”的所有員工信息，先獲取兩個(gè)部門 id，再查詢對(duì)應(yīng)部門員工。
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 = '咨詢部');
行子查詢：查詢與“韋一笑”入職日期及職位都相同的員工信息，可通過(guò)兩種方式實(shí)現(xiàn)。
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 = '韋一笑');
表子查詢：查詢?nèi)肼毴掌谠?ldquo;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è)務(wù)多表查詢需求
查詢低價(jià)菜品信息：獲取價(jià)格低于10元的菜品名稱、價(jià)格及分類名稱。
SELECT d.name, d.price, c.name FROM dish d, category c WHERE d.category_id = c.id AND d.price < 10;
查詢特定價(jià)格與狀態(tài)菜品信息：查詢價(jià)格在10元（含）到50元（含）之間且狀態(tài)為“起售”的菜品信息，包括無(wú)分類菜品。
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;
查詢各分類最貴菜品信息：展示每個(gè)分類下最貴菜品的分類名稱和價(jià)格。
SELECT c.name, MAX(d.price) FROM dish d, category c WHERE d.category_id = c.id GROUP BY c.name;
查詢特定條件分類名稱：獲取菜品狀態(tài)為“起售”且菜品數(shù)量大于等于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;
查詢套餐包含菜品信息：展示“商務(wù)套餐A”包含的菜品相關(guān)信息。
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 = '商務(wù)套餐A';
查詢低于平均價(jià)格菜品信息：先計(jì)算菜品平均價(jià)格，再查詢低于該平均價(jià)格的菜品。
SELECT AVG(price) FROM dish; SELECT * FROM dish WHERE price < (SELECT AVG(price) FROM dish);
四、事務(wù)操作
4.1 事務(wù)處理流程
在刪除部門及相關(guān)員工操作中，使用事務(wù)確保數(shù)據(jù)一致性。
-- 開(kāi)啟事務(wù) START TRANSACTION; -- 刪除部門 DELETE FROM tb_dept WHERE id = 2; -- 刪除部門下的員工 DELETE FROM tb_emp WHERE dept_id = 2; -- 提交事務(wù) COMMIT; -- 回滾事務(wù)（若中途出錯(cuò)） 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;
到此這篇關(guān)于MySQL多表查詢、事務(wù)與索引的實(shí)踐與應(yīng)用的文章就介紹到這了,更多相關(guān)MySQL多表查詢、事務(wù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL子查詢操作,結(jié)合實(shí)例形式較為詳細(xì)的分析了mysql表的創(chuàng)建、常見(jiàn)子查詢運(yùn)算與關(guān)鍵字使用技巧,需要的朋友可以參考下2018-06-06
SQL函數(shù)實(shí)現(xiàn)Group_concat用法
GROUP_CONCAT用于將每個(gè)分組內(nèi)的值連接成一個(gè)字符串,并通過(guò)指定的分隔符進(jìn)行分隔,本文主要介紹了SQL函數(shù)實(shí)現(xiàn)Group_concat用法,具有一定的參考價(jià)值,感興趣的可以了解一下2024-08-08
Mysql及Navicat中設(shè)置字段自動(dòng)填充當(dāng)前時(shí)間及修改時(shí)間實(shí)現(xiàn)
這篇文章主要給大家介紹了關(guān)于Mysql及Navicat中設(shè)置字段自動(dòng)填充當(dāng)前時(shí)間及修改時(shí)間實(shí)現(xiàn)的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2023-07-07
這篇文章主要介紹了SQL 列不同的表查詢結(jié)果合并操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-10-10
用C語(yǔ)言操作MySQL數(shù)據(jù)庫(kù)的通用方法
在為MySQL提供接口方面，C語(yǔ)言具有更好的安全性和性能，本篇文章介紹了C語(yǔ)言操作MySQL數(shù)據(jù)庫(kù)的通用方法，需要了解的朋友可以參考下2015-07-07
Mysql觸發(fā)器在PHP項(xiàng)目中用來(lái)做信息備份、恢復(fù)和清空
這篇文章主要介紹了Mysql觸發(fā)器在PHP項(xiàng)目中用來(lái)做信息備份、恢復(fù)和清空的相關(guān)資料,需要的朋友可以參考下2017-11-11
在windows環(huán)境下配置并隨意切換兩種mysql版本的方法
這篇文章主要介紹了mysql:如何在windows環(huán)境下配置并隨意切換兩種mysql版本,本文通過(guò)圖文并茂的形式給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-01-01
MySQL如何基于Explain關(guān)鍵字優(yōu)化索引功能
這篇文章主要介紹了MySQL如何基于Explain關(guān)鍵字優(yōu)化索引功能,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-10-10
MySQL?Community?Server?8.0.29安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了MySQL?Community?Server?8.0.29安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-06-06
SQL重復(fù)記錄查詢 查詢多個(gè)字段、多表查詢、刪除重復(fù)記錄的方法
下面小編就為大家?guī)?lái)一篇SQL重復(fù)記錄查詢 查詢多個(gè)字段、多表查詢、刪除重復(fù)記錄的方法。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2016-09-09