summary: This article focuses on MySQL database operations. By building database tables related to departmental management and catering business and filling in test data, it systematically explains multiple methods of multi-table query, including internal connections, external connections and different types of subqueries. It also introduces transaction processing and index creation, query and deletion operations.
Keywords: MySQL; multi-table query; transaction; index
1. Introduction
In the process of database management and development, multi-table query, transaction management and index optimization are key technologies to improve data processing efficiency and data integrity. This article uses actual cases to show the specific application of these technologies in MySQL databases in detail.
2. Data preparation
2.1 Creation of department and employee tables and data insertion
Department table (tb_dept): used to store department-related information.
CREATE TABLE tb_dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Primary Key ID',
name VARCHAR(10) NOT NULL UNIQUE COMMENT 'Department Name',
create_time DATETIME NOT NULL COMMENT 'Create time',
update_time DATETIME NOT NULL COMMENT 'Modify time'
) COMMENT 'Department Table';
INSERT INTO tb_dept (id, name, create_time, update_time) VALUES
(1, 'Student and Work Department', NOW(), NOW()),
(2, 'Teaching and Research Department', NOW(), NOW()),
(3, 'Consultation Department', NOW(), NOW()),
(4, 'Employment Department', NOW(), NOW()),
(5, 'Human Affairs Department', NOW(), NOW());memberWorksheet (tb_emp):passdept_idAssociate with the department table to record employee details.
CREATE TABLE tb_emp (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
username VARCHAR(20) NOT NULL UNIQUE COMMENT 'username',
password VARCHAR(32) DEFAULT '123456' COMMENT 'password',
name VARCHAR(10) NOT NULL COMMENT 'Name',
gender TINYINT UNSIGNED NOT NULL COMMENT 'Gender, Description: 1 male, 2 female',
image VARCHAR(300) COMMENT 'image',
job TINYINT UNSIGNED COMMENT 'Position, Description: 1 class teacher, 2 lecturer, 3 student supervisor, 4 teaching and research supervisor, 5 consultant',
entrydate DATE COMMENT 'Organization time',
dept_id INT UNSIGNED COMMENT 'Department ID',
create_time DATETIME NOT NULL COMMENT 'Create time',
update_time DATETIME NOT NULL COMMENT 'Modify time'
) COMMENT 'Employee Table';
INSERT INTO tb_emp(id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time) VALUES
(1, 'jinyong', '123456', 'Jin Yong', 1, '', 4, '2000 - 01 - 01', 2, NOW(), NOW()),
(2, 'zhangwuji', '123456', 'Zhang Wuji', 1, '', 2, '2015 - 01 - 01', 2, NOW(), NOW()),
-- Omit some of the inserted data
(17, 'chenyouliang', '123456', 'Chen Youliang', 1, '', NULL, '2015 - 03 - 21', NULL, NOW(), NOW());2.2 Creation of related tables in catering business and data insertion
Classification table (category): Distinguish between dish classification and set menu classification.
CREATE TABLE category(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Primary Key ID',
name VARCHAR(20) NOT NULL UNIQUE COMMENT 'Category Name',
type TINYINT UNSIGNED NOT NULL COMMENT 'Type 1 dish category 2 set menu category',
sort TINYINT UNSIGNED NOT NULL COMMENT 'order',
status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Status 0 Disabled, 1 Enabled',
create_time DATETIME NOT NULL COMMENT 'Create time',
update_time DATETIME NOT NULL COMMENT 'Update time'
) COMMENT 'Classification';Dish list (dish): Record the various attributes of the dishes and pass the classification tablecategory_idRelated.
CREATE TABLE dish(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Primary Key ID',
name VARCHAR(20) NOT NULL UNIQUE COMMENT 'Dish name',
category_id INT UNSIGNED NOT NULL COMMENT 'Dish Category ID',
price DECIMAL(8, 2) NOT NULL COMMENT 'Dish prices',
image VARCHAR(300) NOT NULL COMMENT 'Dish pictures',
description VARCHAR(200) COMMENT 'Description Information',
status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Status, 0 Discontinued 1 Starting,
create_time DATETIME NOT NULL COMMENT 'Create time',
update_time DATETIME NOT NULL COMMENT 'Update time'
) COMMENT 'Dish';Package table (setmeal): Store package information and pass the classification tablecategory_idRelated.
CREATE TABLE setmeal(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Primary Key ID',
name VARCHAR(20) NOT NULL UNIQUE COMMENT 'Package Name',
category_id INT UNSIGNED NOT NULL COMMENT 'Category id',
price DECIMAL(8, 2) NOT NULL COMMENT 'Package price',
image VARCHAR(300) NOT NULL COMMENT 'picture',
description VARCHAR(200) COMMENT 'Description Information',
status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Status 0: Disable 1: Enable',
create_time DATETIME NOT NULL COMMENT 'Create time',
update_time DATETIME NOT NULL COMMENT 'Update time'
) COMMENT 'combo';Set menu product association table (setmeal_dish): Establish a connection between the set menu and the dishes.
CREATE TABLE setmeal_dish(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Primary Key ID',
setmeal_id INT UNSIGNED NOT NULL COMMENT 'Packard id',
dish_id INT UNSIGNED NOT NULL COMMENT 'Dish id',
copies TINYINT UNSIGNED NOT NULL COMMENT 'Number of copies'
) COMMENT 'Set menu intermediate table';Insert test data: Insert a large amount of test data into the above-mentioned catering business related tables, covering all kinds of dishes, sets and their related information.
3. Multi-table query operation
3.1 Basic multi-table query
By connectingtb_empandtb_depttable to obtain information about the department to which the employee belongs.
SELECT * FROM tb_emp, tb_dept WHERE tb_emp.dept_id = tb_dept.id;
3.2 Internal connection
Implicit internal connection: Check the employee’s name and department name to which the employee belongs, and improve readability by aliasing it.
SELECT tb_emp.name, tb_dept.name FROM tb_emp, tb_dept WHERE tb_emp.dept_id = tb_dept.id; SELECT , FROM tb_emp e, tb_dept d WHERE e.dept_id = ;
Explicit internal connection: Also realize the query of employee names and department names.
SELECT tb_emp.name, tb_dept.name FROM tb_emp JOIN tb_dept ON tb_emp.dept_id = tb_dept.id;
3.3 External connection
Left outer connection: Obtain the employee table all employee names and corresponding department names, including employees without department.
SELECT , FROM tb_emp e LEFT JOIN tb_dept d ON e.dept_id = ;
Right outer connection: Obtain the department table all department names and corresponding employee names, including the department without employee.
SELECT , FROM tb_emp e RIGHT JOIN tb_dept d ON e.dept_id = ; -- Equivalent to SELECT , FROM tb_dept d LEFT JOIN tb_emp e ON e.dept_id = ;
3.4 Subquery
Scalar subquery：
Inquiry of all employee information in the "Teaching and Research Department" and obtain the Teaching and Research Department first.id, and then check the employees of the department.
SELECT id FROM tb_dept WHERE name = 'Teaching and Research Department'; SELECT * FROM tb_emp WHERE dept_id = (SELECT id FROM tb_dept WHERE name = 'Teaching and Research Department');
- Check the information of employees after "Fang Dongbai" joins, first obtain the time of Fang Dongbai's entry, and then check the employees who join later than that time.
SELECT entrydate FROM tb_emp WHERE name = 'Fang Dongbai'; SELECT * FROM tb_emp WHERE entrydate > (SELECT entrydate FROM tb_emp WHERE name = 'Fang Dongbai');
Column subquery: Query all employee information from the "Teaching and Research Department" and "Consultation Department" and obtain two departments firstid, and then check the employees of the corresponding department.
SELECT id FROM tb_dept WHERE name = 'Teaching and Research Department' OR name = 'Consultation Department'; SELECT * FROM tb_emp WHERE dept_id IN (SELECT id FROM tb_dept WHERE name = 'Teaching and Research Department' OR name = 'Consultation Department');
Line query: Query employee information with the same date and position as "Wei Yixiao", which can be achieved in two ways.
SELECT entrydate, job FROM tb_emp WHERE name = 'Wei Yixiao'; -- Method one SELECT * FROM tb_emp WHERE entrydate = (SELECT entrydate FROM tb_emp WHERE name = 'Wei Yixiao') AND job = (SELECT job FROM tb_emp WHERE name = 'Wei Yixiao'); -- Method 2 SELECT * FROM tb_emp WHERE (entrydate, job) = (SELECT entrydate, job FROM tb_emp WHERE name = 'Wei Yixiao');
Table subquery: Query the employee information and department name after the onboarding date of "2006 - 01 - 01", first obtain the employees who meet the date conditions, and then connect to the department table to obtain the department name.
SELECT * FROM tb_emp WHERE entrydate > '2006 - 01 - 01'; SELECT e.*, FROM (SELECT * FROM tb_emp WHERE entrydate > '2006 - 01 - 01') e, tb_dept d WHERE e.dept_id = ;
3.5 Multi-table query requirements for catering business
Query low-priced dishes information: Get the name, price and classification name of the dish price less than 10 yuan.
SELECT , , FROM dish d, category c WHERE d.category_id = AND < 10;
Query specific prices and status dishes information: Query the information of dishes with prices between 10 yuan (inclusive) and 50 yuan (inclusive) and the status is "starting sale", including unclassified dishes.
SELECT , , FROM dish d LEFT JOIN category c ON d.category_id = WHERE BETWEEN 10 AND 50 AND = 1;
Query the most expensive dishes in each category: Display the category name and price of the most expensive dishes under each category.
SELECT , MAX() FROM dish d, category c WHERE d.category_id = GROUP BY ;
Query specific conditional classification names: Obtain the classification name with the dish status of "starting sale" and the number of dishes is greater than or equal to 3.
SELECT , COUNT(*) FROM dish d, category c WHERE d.category_id = AND = 1 GROUP BY HAVING COUNT(*) >= 3;
Query the menu contains the information: Display the dishes related information contained in "Business Package A".
SELECT , , , , FROM setmeal s, setmeal_dish sd, dish d WHERE = sd.setmeal_id AND sd.dish_id = AND = 'Business Package A';
Query the information of dishes below average prices: First calculate the average price of the dishes, and then check the dishes below the average price.
SELECT AVG(price) FROM dish; SELECT * FROM dish WHERE price < (SELECT AVG(price) FROM dish);
4. Transaction operation
4.1 Transaction Process
In the operation of deletion departments and related employees, use transactions to ensure data consistency.
-- Start a transaction START TRANSACTION; -- Delete the department DELETE FROM tb_dept WHERE id = 2; -- Delete the department下的员工 DELETE FROM tb_emp WHERE dept_id = 2; -- Submit transactions COMMIT; -- Roll back transactions（If something goes wrong in the middle） ROLLBACK; SELECT * FROM tb_dept; SELECT * FROM tb_emp;
V. Index operation
5.1 Creation, query and deletion of indexes
Create an index:fortb_skuTable ofsnFields andtb_empTable ofnameFields create index.
CREATE INDEX idx_sku_sn ON tb_sku(sn); CREATE INDEX idx_emp_name ON tb_emp(name);
Query index information:Checktb_empIndex status of the table.
SHOW INDEX FROM tb_emp;
Delete the index:deletetb_empIn the tablenameIndex of the field.
DROP INDEX idx_emp_name ON tb_emp;
This is the article about the practice and application of MySQL multi-table query, transactions and indexes. For more related MySQL multi-table query and transaction content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!