Fading Coder

One Final Commit for the Last Sprint

Home > Tech > Content

MySQL Data Query Language and Multi-Table Design

Tech 2

Data Query Language (DQL)

DQL (Data Query Language) is used to retrieve records from database tables. The primary keyword for queries is SELECT. Query operations are fundamental in database systems, often used more frequently than insert, update, or delete operations. Data displayed on websites and applications typically originates from database queries, which may involve conditions, sorting, and pagination.

Basic Syntax

SELECT
    field_list
FROM
    table_list
WHERE
    condition_list
GROUP BY
    grouping_fields
HAVING
    post_grouping_conditions
ORDER BY
    sorting_fields
LIMIT
    pagination_parameters

This comprehensive syntax can be broken down into:

  • Basic queries (without conditions)
  • Conditional queries (WHERE)
  • Grouping queries (GROUP BY)
  • Sorting queries (ORDER BY)
  • Pagination queries (LIMIT)

Test Data Setup

CREATE DATABASE sample_db;
USE sample_db;

CREATE TABLE employees (
    emp_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Employee ID',
    user_name VARCHAR(20) NOT NULL UNIQUE COMMENT 'Username',
    pass_word VARCHAR(32) DEFAULT '123456' COMMENT 'Password',
    full_name VARCHAR(10) NOT NULL COMMENT 'Full Name',
    emp_gender TINYINT UNSIGNED NOT NULL COMMENT 'Gender: 1 Male, 2 Female',
    emp_image VARCHAR(300) COMMENT 'Profile Image',
    emp_role TINYINT UNSIGNED COMMENT 'Role: 1 Supervisor, 2 Instructor, 3 Coordinator, 4 Manager',
    hire_date DATE COMMENT 'Hire Date',
    created_at DATETIME NOT NULL COMMENT 'Creation Time',
    updated_at DATETIME NOT NULL COMMENT 'Last Update Time'
) COMMENT 'Employee Records';

INSERT INTO employees (emp_id, user_name, pass_word, full_name, emp_gender, emp_image, emp_role, hire_date, created_at, updated_at) VALUES
    (1, 'jinyong', '123456', 'Jin Yong', 1, '1.jpg', 4, '2000-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (2, 'zhangwuji', '123456', 'Zhang Wuji', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:37'),
    (3, 'yangxiao', '123456', 'Yang Xiao', 1, '3.jpg', 2, '2008-05-01', '2022-10-27 16:35:33', '2022-10-27 16:35:39');

Basic Queries

  • Select specific fields: 
    SELECT full_name, hire_date FROM employees;
  • Select all fields: 
    SELECT * FROM employees;
  • Assign aliases: 
    SELECT full_name AS employee_name, hire_date AS start_date FROM employees;
  • Remove duplicates: 
    SELECT DISTINCT emp_role FROM employees;

Conditional Queries

SELECT field_list FROM table_name WHERE condition_list;

Common comparison operators:

  • >, >=, <, <=, =, <>, !=
  • BETWEEN ... AND ...
  • IN (...)
  • LIKE (with _ for single character, % for multiple characters)
  • IS NULL

Logical operators:

  • AND or &&
  • OR or ||
  • NOT or !

Examples:

SELECT * FROM employees WHERE full_name = 'Yang Xiao';
SELECT * FROM employees WHERE emp_id <= 5;
SELECT * FROM employees WHERE emp_role IS NULL;
SELECT * FROM employees WHERE pass_word != '123456';
SELECT * FROM employees WHERE hire_date BETWEEN '2000-01-01' AND '2010-01-01';
SELECT * FROM employees WHERE emp_role IN (2, 3, 4);
SELECT * FROM employees WHERE full_name LIKE '张%';

Aggregate Functions

Aggregate functions perform calculations on a column and return a single value. They ignore NULL values.

Common functions:

  • COUNT(): Counts rows
  • MAX(): Returns maximum value
  • MIN(): Returns minimum value
  • AVG(): Calculates average
  • SUM(): Computes sum

Examples:

SELECT COUNT(*) FROM employees;
SELECT MIN(hire_date) FROM employees;
SELECT MAX(hire_date) FROM employees;
SELECT AVG(emp_id) FROM employees;
SELECT SUM(emp_id) FROM employees;

Grouping Queries

SELECT field_list FROM table_name [WHERE conditions] GROUP BY grouping_field [HAVING post_grouping_conditions];

Examples:

SELECT emp_gender, COUNT(*) FROM employees GROUP BY emp_gender;
SELECT emp_role, COUNT(*) FROM employees WHERE hire_date <= '2015-01-01' GROUP BY emp_role HAVING COUNT(*) >= 2;

Sorting Queries

SELECT field_list FROM table_name [WHERE conditions] [GROUP BY grouping_field] ORDER BY field1 sort_order1, field2 sort_order2 ...;

Sort orders:

  • ASC: Ascending (default)
  • DESC: Descending

Examples:

SELECT * FROM employees ORDER BY hire_date ASC;
SELECT * FROM employees ORDER BY hire_date DESC;
SELECT * FROM employees ORDER BY hire_date ASC, updated_at DESC;

Pagination Queries

SELECT field_list FROM table_name LIMIT start_index, record_count;

Examples:

SELECT * FROM employees LIMIT 0, 5;
SELECT * FROM employees LIMIT 5, 5;

Start index calculasion: start_index = (page_number - 1) * records_per_page

Practical Examples

  1. Conditional pagination with sorting:
SELECT * FROM employees 
WHERE full_name LIKE '张%' AND emp_gender = 1 AND hire_date BETWEEN '2000-01-01' AND '2015-12-31'
ORDER BY updated_at DESC
LIMIT 0, 10;
  1. Data aggregation with conditional logic:
SELECT IF(emp_gender=1, 'Male', 'Female') AS gender_category, COUNT(*) AS total_count
FROM employees
GROUP BY emp_gender;

SELECT CASE emp_role
    WHEN 1 THEN 'Supervisor'
    WHEN 2 THEN 'Instructor'
    WHEN 3 THEN 'Coordinator'
    WHEN 4 THEN 'Manager'
    ELSE 'Unassigned'
END AS role_description,
COUNT(*) AS role_count
FROM employees
GROUP BY emp_role;

Multi-Table Design

Database table relationships typical fall into three categoreis:

  • One-to-Many (Many-to-One)
  • Many-to-Many
  • One-to-One

One-to-Many Relationships

Example: Departments and Employees

CREATE TABLE departments (
    dept_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Department ID',
    dept_name VARCHAR(10) NOT NULL UNIQUE COMMENT 'Department Name',
    created_at DATETIME NOT NULL COMMENT 'Creation Time',
    updated_at DATETIME NOT NULL COMMENT 'Last Update Time'
) COMMENT 'Department Information';

ALTER TABLE employees ADD COLUMN department_id INT UNSIGNED COMMENT 'Department Reference';

Implementation: Add a foreign key in the "many" side table referencing the primary key of the "one" side table.

Foreign Key Constraints

Foreign keys maintain referential integrity between related tables.

ALTER TABLE employees
ADD CONSTRAINT fk_department_id
FOREIGN KEY (department_id) REFERENCES departments(dept_id);

Considerations:

  • Physical foreign keys can impact performance in distributed systems
  • Logical foreign keys (application-level enforcement) are often preferred in modern development

One-to-One Relationships

Example: User profiles and identification documents

CREATE TABLE user_profiles (
    profile_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Profile ID',
    user_name VARCHAR(10) NOT NULL COMMENT 'User Name',
    user_gender TINYINT UNSIGNED NOT NULL COMMENT 'Gender',
    contact_number CHAR(11) COMMENT 'Contact Number',
    education_level VARCHAR(10) COMMENT 'Education Level'
) COMMENT 'User Profile Information';

CREATE TABLE user_identification (
    id_doc_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Document ID',
    nationality VARCHAR(10) NOT NULL COMMENT 'Nationality',
    birth_date DATE NOT NULL COMMENT 'Date of Birth',
    id_number CHAR(18) NOT NULL COMMENT 'Identification Number',
    issuing_authority VARCHAR(20) NOT NULL COMMENT 'Issuing Authority',
    valid_from DATE NOT NULL COMMENT 'Validity Start Date',
    valid_until DATE COMMENT 'Validity End Date',
    profile_ref INT UNSIGNED NOT NULL UNIQUE COMMENT 'Profile Reference',
    CONSTRAINT fk_profile_ref FOREIGN KEY (profile_ref) REFERENCES user_profiles(profile_id)
) COMMENT 'User Identification Documents';

Implementation: Add a foreign key in either table with a UNIQUE constraint.

Many-to-Many Relationships

Example: Students and Courses

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Student ID',
    student_name VARCHAR(10) COMMENT 'Student Name',
    student_number VARCHAR(10) COMMENT 'Student Number'
) COMMENT 'Student Records';

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Course ID',
    course_name VARCHAR(10) COMMENT 'Course Name'
) COMMENT 'Course Catalog';

CREATE TABLE student_course_enrollment (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Enrollment ID',
    enrolled_student INT NOT NULL COMMENT 'Student Reference',
    enrolled_course INT NOT NULL COMMENT 'Course Reference',
    CONSTRAINT fk_enrolled_course FOREIGN KEY (enrolled_course) REFERENCES courses(course_id),
    CONSTRAINT fk_enrolled_student FOREIGN KEY (enrolled_student) REFERENCES students(student_id)
) COMMENT 'Student Course Enrollment Records';

Implementation: Create a junction table with foreign keys referencing both related tables.

Practical Design Example: Restaurant Management System

CREATE TABLE menu_categories (
    category_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Category ID',
    category_name VARCHAR(20) NOT NULL UNIQUE COMMENT 'Category Name',
    category_type TINYINT UNSIGNED NOT NULL COMMENT 'Type: 1 Food, 2 Combo',
    display_order TINYINT UNSIGNED NOT NULL COMMENT 'Display Order',
    category_status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Status: 0 Disabled, 1 Enabled',
    created_at DATETIME NOT NULL COMMENT 'Creation Time',
    updated_at DATETIME NOT NULL COMMENT 'Last Update Time'
) COMMENT 'Menu Categories';

CREATE TABLE food_items (
    item_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Item ID',
    item_name VARCHAR(20) NOT NULL UNIQUE COMMENT 'Item Name',
    item_category INT UNSIGNED NOT NULL COMMENT 'Category Reference',
    item_price DECIMAL(8, 2) NOT NULL COMMENT 'Item Price',
    item_image VARCHAR(300) NOT NULL COMMENT 'Item Image',
    item_description VARCHAR(200) COMMENT 'Item Description',
    item_status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Status: 0 Unavailable, 1 Available',
    created_at DATETIME NOT NULL COMMENT 'Creation Time',
    updated_at DATETIME NOT NULL COMMENT 'Last Update Time'
) COMMENT 'Food Items';

CREATE TABLE meal_combos (
    combo_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Combo ID',
    combo_name VARCHAR(20) NOT NULL UNIQUE COMMENT 'Combo Name',
    combo_category INT UNSIGNED NOT NULL COMMENT 'Category Reference',
    combo_price DECIMAL(8, 2) NOT NULL COMMENT 'Combo Price',
    combo_image VARCHAR(300) NOT NULL COMMENT 'Combo Image',
    combo_description VARCHAR(200) COMMENT 'Combo Description',
    combo_status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Status: 0 Unavailable, 1 Available',
    created_at DATETIME NOT NULL COMMENT 'Creation Time',
    updated_at DATETIME NOT NULL COMMENT 'Last Update Time'
) COMMENT 'Meal Combos';

CREATE TABLE combo_items (
    combo_item_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Combo Item ID',
    combo_reference INT UNSIGNED NOT NULL COMMENT 'Combo Reference',
    item_reference INT UNSIGNED NOT NULL COMMENT 'Item Reference',
    item_quantity TINYINT UNSIGNED NOT NULL COMMENT 'Item Quantity'
) COMMENT 'Combo Item Associations';
Tags: MySQLsqlDatabase DesignDQLForeign Keys
Back to List

Prev: Implementing a Java Student Management System with Authentication

Next: Implementing Plugin Architecture in WPF Without External Tools

Related Articles

Understanding Strong and Weak References in Java

Strong References Strong reference are the most prevalent type of object referencing in Java. When an object has a strong reference pointing to it, the garbage collector will not reclaim its memory. F...

Comprehensive Guide to SSTI Explained with Payload Bypass Techniques

Introduction Server-Side Template Injection (SSTI) is a vulnerability in web applications where user input is improper handled within the template engine and executed on the server. This exploit can r...

Implement Image Upload Functionality for Django Integrated TinyMCE Editor

Django’s Admin panel is highly user-friendly, and pairing it with TinyMCE, an effective rich text editor, simplifies content management significantly. Combining the two is particular useful for bloggi...

Leave a Comment

Anonymous

◎Feel free to join the discussion and share your thoughts.

Copyright © fadingcoder.top