MySQL多表查詢、事務(wù)與索引的實踐與應(yīng)用操作

更新時間：2025年04月10日 09:54:01 作者：碼銀

本文圍繞MySQL數(shù)據(jù)庫操作展開,通過構(gòu)建部門與員工管理、餐飲業(yè)務(wù)相關(guān)的數(shù)據(jù)庫表,并填充測試數(shù)據(jù),系統(tǒng)地闡述了多表查詢的多種方式,包括內(nèi)連接、外連接和不同類型的子查詢,同時介紹了事務(wù)的處理以及索引的創(chuàng)建、查詢和刪除操作,感興趣的朋友一起看看吧

摘要：本文圍繞MySQL數(shù)據(jù)庫操作展開，通過構(gòu)建部門與員工管理、餐飲業(yè)務(wù)相關(guān)的數(shù)據(jù)庫表，并填充測試數(shù)據(jù)，系統(tǒng)地闡述了多表查詢的多種方式，包括內(nèi)連接、外連接和不同類型的子查詢，同時介紹了事務(wù)的處理以及索引的創(chuàng)建、查詢和刪除操作。

關(guān)鍵詞：MySQL；多表查詢；事務(wù)；索引

一、引言

在數(shù)據(jù)庫管理與開發(fā)過程中，多表查詢、事務(wù)管理以及索引優(yōu)化是提升數(shù)據(jù)處理效率和數(shù)據(jù)完整性的關(guān)鍵技術(shù)。本文通過實際案例詳細(xì)展示這些技術(shù)在MySQL數(shù)據(jù)庫中的具體應(yīng)用。

二、數(shù)據(jù)準(zhǔn)備

2.1 部門與員工表的創(chuàng)建及數(shù)據(jù)插入

部門表（tb_dept）：用于存儲部門相關(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)建時間',
    update_time DATETIME NOT NULL COMMENT '修改時間'
) 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）：通過 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 '性別, 說明: 1 男, 2 女',
    image VARCHAR(300) COMMENT '圖像',
    job TINYINT UNSIGNED COMMENT '職位, 說明: 1 班主任,2 講師, 3 學(xué)工主管, 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()),
-- 省略部分插入數(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)建時間',
    update_time DATETIME NOT NULL COMMENT '更新時間'
) COMMENT '分類';

菜品表（dish）：記錄菜品的各項屬性，與分類表通過 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 '菜品價格',
    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 關(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 '套餐價格',
    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 '套餐';

套餐菜品關(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 '套餐菜品中間表';

插入測試數(shù)據(jù)：向上述餐飲業(yè)務(wù)相關(guān)表插入大量測試數(shù)據(jù)，涵蓋各類菜品、套餐及其關(guān)聯(lián)信息。

三、多表查詢操作

3.1 基本多表查詢

通過連接 tb_emp 和 tb_dept 表，獲取員工所屬部門信息。

SELECT * FROM tb_emp, tb_dept WHERE tb_emp.dept_id = tb_dept.id;

3.2 內(nèi)連接

隱式內(nèi)連接：查詢員工姓名及所屬部門名稱，可通過起別名提高可讀性。

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)連接：同樣實現(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 外連接

左外連接：獲取員工表所有員工姓名及對應(yīng)的部門名稱，包括無部門員工。

SELECT e.name, d.name FROM tb_emp e LEFT JOIN tb_dept d ON e.dept_id = d.id;

右外連接：獲取部門表所有部門名稱及對應(yīng)的員工名稱，包括無員工部門。

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，再查詢該部門員工。

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，再查詢對應(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 = '咨詢部');

行子查詢：查詢與“韋一笑”入職日期及職位都相同的員工信息，可通過兩種方式實現(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ù)多表查詢需求

查詢低價菜品信息：獲取價格低于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)為“起售”且菜品數(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';

查詢低于平均價格菜品信息：先計算菜品平均價格，再查詢低于該平均價格的菜品。

SELECT AVG(price) FROM dish;
SELECT * FROM dish WHERE price < (SELECT AVG(price) FROM dish);

四、事務(wù)操作

4.1 事務(wù)處理流程

在刪除部門及相關(guān)員工操作中，使用事務(wù)確保數(shù)據(jù)一致性。

-- 開啟事務(wù)
START TRANSACTION;
-- 刪除部門
DELETE FROM tb_dept WHERE id = 2;
-- 刪除部門下的員工
DELETE FROM tb_emp WHERE dept_id = 2;
-- 提交事務(wù)
COMMIT;
-- 回滾事務(wù)（若中途出錯）
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ù)與索引的實踐與應(yīng)用的文章就介紹到這了,更多相關(guān)MySQL多表查詢、事務(wù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL多表查詢、事務(wù)與索引的實踐與應(yīng)用操作

目錄

一、引言

二、數(shù)據(jù)準(zhǔn)備

2.1 部門與員工表的創(chuàng)建及數(shù)據(jù)插入

2.2 餐飲業(yè)務(wù)相關(guān)表的創(chuàng)建及數(shù)據(jù)插入

三、多表查詢操作

3.1 基本多表查詢

3.2 內(nèi)連接

3.3 外連接

3.4 子查詢

3.5 餐飲業(yè)務(wù)多表查詢需求

四、事務(wù)操作

4.1 事務(wù)處理流程

五、索引操作

5.1 索引的創(chuàng)建、查詢與刪除

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL多表查詢、事務(wù)與索引的實踐與應(yīng)用操作

目錄

一、引言

二、數(shù)據(jù)準(zhǔn)備

2.1 部門與員工表的創(chuàng)建及數(shù)據(jù)插入

2.2 餐飲業(yè)務(wù)相關(guān)表的創(chuàng)建及數(shù)據(jù)插入

三、多表查詢操作

3.1 基本多表查詢

3.2 內(nèi)連接

3.3 外連接

3.4 子查詢

3.5 餐飲業(yè)務(wù)多表查詢需求

四、事務(wù)操作

4.1 事務(wù)處理流程

五、索引操作

5.1 索引的創(chuàng)建、查詢與刪除

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

二、數(shù)據(jù)準(zhǔn)備

三、多表查詢操作

五、索引操作

5.1 索引的創(chuàng)建、查詢與刪除