欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL多表查詢、事務(wù)與索引的實(shí)踐與應(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ù)。本文通過實(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）：記錄菜品的各項(xiàng)屬性，與分類表通過 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)連接：同樣實(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 外連接

左外連接：獲取員工表所有員工姓名及對應(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 = '咨詢部');

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

查詢低價菜品信息：獲取價格低于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';

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

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ù)與索引的實(shí)踐與應(yīng)用的文章就介紹到這了,更多相關(guān)MySQL多表查詢、事務(wù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評論