MySQL SQL Fundamentals and Query Operations

SQL Overview

SQL (Structured Query Language) serves as the standard communication protocol for relational database systems. It provides a comprehensive command set specifically designed for database operations, enabling users to issue declarative instructions without worrying about implementation details.

SQL Syntax Standards

  1. SQL keywords are case-insensitive, though uppercase is conventionally preferred. String literals, however, maintain their case sensitviity.

  2. Statements can span single or multiple lines, terminated by a semicolon. Keywords cannot be abbreviated or split across lines.

  3. Whitespace and indentation enhance readability. Placing clauses on separate lines improves maintainability.

SELECT * FROM users
WHERE username = "admin";

  1. Comments use -- for single-line and /* */ for multi-line blocks.

  2. Long statements can be broken across lines for better formatting.

  3. SQL commands are categorized into DDL, DML, and DCL:

-- DML (Data Manipulation Language)
-- Handles data retrieval and modification: SELECT, UPDATE, INSERT, DELETE

-- DDL (Data Definition Language)
-- Manages object creation and structure: CREATE, ALTER, DROP

-- DCL (Data Control Language)
-- Controls access permissions: GRANT, DENY, REVOKE

Database-Level Operations

-- Create a new database
CREATE DATABASE IF NOT EXISTS company_db CHARACTER SET utf8mb4;

-- List all databases
SHOW DATABASES;

-- View database creation details
SHOW CREATE DATABASE company_db;

-- Modify database charset
ALTER DATABASE company_db CHARACTER SET utf8mb4;

-- Delete a database
DROP DATABASE IF EXISTS company_db;

-- Switch active database
USE company_db;

-- Identify current database
SELECT DATABASE();

Table Structure Management

Creating Tables

CREATE TABLE staff (
    id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50),
    department VARCHAR(30),
    hire_date DATE,
    salary DECIMAL(10,2),
    is_active TINYINT(1) DEFAULT 1
);

Key constraints include:

  • PRIMARY KEY: Ensures uniqueness and non-null values
  • UNIQUE: Prevents duplicate values
  • NOT NULL: Prohibits empty values
  • AUTO_INCREMENT: Automatically generates sequential values for primary keys

Inspecting Table Metadata

DESC staff;
SHOW COLUMNS FROM staff;
SHOW TABLES;
SHOW CREATE TABLE staff;

Modifying Table Structure

-- Add new columns
ALTER TABLE staff ADD COLUMN phone VARCHAR(20) AFTER emp_name;
ALTER TABLE staff ADD COLUMN title VARCHAR(30) FIRST;

-- Modify column definitions
ALTER TABLE staff MODIFY salary DECIMAL(12,2) UNSIGNED;
ALTER TABLE staff MODIFY title VARCHAR(40) AFTER department;

-- Rename columns
ALTER TABLE staff CHANGE COLUMN title job_title VARCHAR(35);

-- Remove columns
ALTER TABLE staff DROP COLUMN phone;

-- Rename entire table
RENAME TABLE staff TO employees;

-- Change charset
ALTER TABLE employees CHARACTER SET utf8mb4;

Deleting Tables

DROP TABLE employees;

Data Manipulation Operations

Inserting Records

-- Single row insertion with column specification
INSERT INTO employees (emp_name, department, hire_date, salary)
VALUES ('john_smith', 'engineering', '2023-01-15', 8500.00);

-- Single row insertion without column specification
INSERT INTO employees VALUES
(NULL, 'jane_doe', 'marketing', '2022-11-20', 7200.00, 1);

-- Partial column insertion
INSERT INTO employees (emp_name, salary) VALUES
('new_hire', 5500.00);

-- Multiple rows at once
INSERT INTO employees (emp_name, department, salary) VALUES
('alice_wang', 'sales', 6800.00),
('bob_zhang', 'sales', 7100.00),
('carol_li', 'hr', 6400.00);

-- SET syntax for insertion
INSERT INTO employees SET emp_name = 'david_chen', salary = 9000.00;

Updating Records

UPDATE employees SET salary = salary * 1.05 WHERE department = 'sales';

UPDATE employees SET hire_date = '2023-06-01' WHERE emp_name = 'john_smith';

Deleting Records

-- Conditional deletion
DELETE FROM employees WHERE emp_name = 'inactive_user';

-- Remove all records (identity counter persists)
DELETE FROM employees;
ALTER TABLE employees AUTO_INCREMENT = 1;

-- Truncate clears all data and resets counter
TRUNCATE TABLE employees;

Note: After inserting a record with id=7, subsequent AUTO_INCREMENT values will continue from 7.

Query Operations

Basic Query Syntax

SELECT [DISTINCT] column_list FROM table_name
[WHERE conditions]
[GROUP BY grouping_column]
[HAVING group_conditions]
[ORDER BY sort_column [ASC|DESC]]
[LIMIT offset, row_count];

Sample Data Setup

CREATE TABLE exam_scores (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(20),
    mathematics DECIMAL(5,2),
    physics DECIMAL(5,2),
    chemistry DECIMAL(5,2)
);

INSERT INTO exam_scores VALUES
(1, 'tom_huang', 92, 88, 95),
(2, 'lisa_wu', 78, 91, 83),
(3, 'mike_zhou', 65, 72, 68),
(4, 'sarah_lin', 88, 94, 91),
(5, 'kevin_ma', 73, 85, 77),
(6, 'emma_fan', 81, 96, 62);

Simple SELECT Queries

-- Retrieve all records
SELECT * FROM exam_scores;

-- Select specific columns
SELECT student_name, mathematics FROM exam_scores;

-- Eliminate duplicate results
SELECT DISTINCT mathematics FROM exam_scores;

-- Use computed expressions with aliases
SELECT student_name,
       mathematics + physics + chemistry AS total_score
FROM exam_scores;

SELECT student_name,
       mathematics + 10 AS adjusted_math
FROM exam_scores;

Filtering with WHERE Clause

-- Exact match
SELECT * FROM exam_scores WHERE student_name = 'tom_huang';

-- Numeric comparison
SELECT student_name, mathematics FROM exam_scores WHERE mathematics > 85;

-- Aggregate condition
SELECT student_name, total_score FROM exam_scores
WHERE (mathematics + physics + chemistry) > 260;

-- Range filtering
SELECT * FROM exam_scores WHERE physics BETWEEN 80 AND 95;

-- Membership testing
SELECT * FROM exam_scores WHERE mathematics IN (88, 92, 95);

-- Pattern matching with wildcards
SELECT * FROM exam_scores WHERE student_name LIKE 's%';
-- % matches any sequence, _ matches single character

-- Combining conditions
SELECT * FROM exam_scores
WHERE mathematics > 80 AND physics >= 90;

Sorting Results

-- Ascending sort (default)
SELECT * FROM exam_scores ORDER BY mathematics;


-- Descending sort
SELECT student_name,
       (mathematics + physics + chemistry) AS total
FROM exam_scores ORDER BY total DESC;

-- Multiple sort criteria
SELECT * FROM exam_scores
WHERE student_name LIKE 'e%'
ORDER BY mathematics DESC, physics DESC;

Grouping with GROUP BY

CREATE TABLE product_orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(30),
    unit_price DECIMAL(8,2),
    order_date DATE,
    category VARCHAR(20)
);

INSERT INTO product_orders (product_name, unit_price, order_date, category) VALUES
('laptop', 5500.00, '2024-03-15', 'electronics'),
('wireless_mouse', 89.00, '2024-03-15', 'electronics'),
('mechanical_keyboard', 299.00, '2024-03-15', 'electronics'),
('standing_desk', 1200.00, '2024-03-16', 'furniture'),
('monitor_stand', 150.00, '2024-03-16', 'furniture'),
('office_chair', 680.00, '2024-03-16', 'furniture'),
('desk_lamp', 45.00, '2024-03-17', 'furniture');

-- Group and aggregate
SELECT category, SUM(unit_price) AS category_total
FROM product_orders GROUP BY category;

-- Filter grouped results with HAVING
SELECT category, SUM(unit_price) AS total_revenue
FROM product_orders GROUP BY category
HAVING SUM(unit_price) > 500;

-- GROUP_CONCAT for grouped string aggregation
SELECT category, GROUP_CONCAT(product_name) AS items
FROM product_orders GROUP BY category;

HAVING vs WHERE: WHERE filters rows before aggregation; HAVING filters groups after aggregation. HAVING supports aggregate functions while WHERE does not.

Aggregate Functions

-- COUNT operations
SELECT COUNT(*) FROM exam_scores;
SELECT COUNT(physics) FROM exam_scores WHERE physics > 85;
SELECT COUNT(*) FROM exam_scores
WHERE (mathematics + physics + chemistry) > 250;

-- SUM operations
SELECT SUM(mathematics) AS math_total FROM exam_scores;
SELECT SUM(mathematics) AS math_sum,
       SUM(physics) AS physics_sum,
       SUM(chemistry) AS chemistry_sum
FROM exam_scores;

-- AVG operations
SELECT AVG(IFNULL(mathematics, 0)) AS avg_math FROM exam_scores;
SELECT AVG((mathematics + physics + chemistry)) AS avg_total
FROM exam_scores;

-- MAX and MIN operations
SELECT MAX((mathematics + physics + chemistry)) AS highest_score
FROM exam_scores;

SELECT MIN((mathematics + physics + chemistry)) AS lowest_score
FROM exam_scores;

-- Handle NULL values with IFNULL
SELECT MAX(IFNULL(mathematics, 0)) FROM exam_scores;

Limiting Results

-- First 3 records
SELECT * FROM exam_scores LIMIT 3;

-- Skip 2, then take 5
SELECT * FROM exam_scores LIMIT 2, 5;

Regular Expression Matching

SELECT * FROM employees WHERE emp_name REGEXP '^j';
SELECT * FROM employees WHERE emp_name REGEXP 'n$';
SELECT * FROM employees WHERE emp_name REGEXP 'a{2}';

Foreign Key Relationships

Creating Foreign Keys

-- Parent table: departments
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(30),
    location VARCHAR(50)
);

INSERT INTO departments (dept_name, location) VALUES
('engineering', 'building_a'),
('marketing', 'building_b'),
('finance', 'building_c');

-- Child table: employees (with optional foreign key constraint)
CREATE TABLE staff_members (
    staff_id INT PRIMARY KEY AUTO_INCREMENT,
    staff_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
) ENGINE=INNODB;

INSERT INTO staff_members (staff_name, dept_id) VALUES
('person_one', 1),
('person_two', 2),
('person_three', 1);

Adding and Removing Foreign Keys

ALTER TABLE staff_members
ADD CONSTRAINT fk_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

ALTER TABLE staff_members DROP FOREIGN KEY fk_dept;

ON DELETE/UPDATE Actions

-- CASCADE: Delete/update child records when parent is deleted/updated
CREATE TABLE staff_members (
    staff_id INT PRIMARY KEY AUTO_INCREMENT,
    staff_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
    ON DELETE CASCADE
);

-- SET NULL: Set child foreign key to NULL when parent is deleted
CREATE TABLE staff_members (
    staff_id INT PRIMARY KEY AUTO_INCREMENT,
    staff_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
    ON DELETE SET NULL
);

-- RESTRICT: Prevent deletion/update of parent with children
-- NO ACTION: Similar to RESTRICT in MySQL

Multi-Table Queries

Preparation

CREATE TABLE company_employee (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(50),
    age INT,
    dept_id INT
);

INSERT INTO company_employee (emp_name, age, dept_id) VALUES
('worker_alpha', 25, 100),
('worker_beta', 32, 101),
('worker_gamma', 28, 101),
('worker_delta', 41, 102),
('worker_epsilon', 29, 100),
('worker_zeta', 35, 103);

CREATE TABLE company_department (
    dept_id INT,
    dept_name VARCHAR(100)
);

INSERT INTO company_department VALUES
(100, 'operations'),
(101, 'development'),
(102, 'support'),
(103, 'logistics');

Cross Join (Cartesian Product)

SELECT * FROM company_employee, company_department;

Inner Join

SELECT e.emp_id, e.emp_name, e.age, d.dept_name
FROM company_employee e
INNER JOIN company_department d ON e.dept_id = d.dept_id;

-- Equivalent implicit join
SELECT e.emp_id, e.emp_name, e.age, d.dept_name
FROM company_employee e, company_department d
WHERE e.dept_id = d.dept_id;

Outer Joins

-- Left outer join: all from left table + matched from right
SELECT e.emp_id, e.emp_name, e.age, d.dept_name
FROM company_employee e
LEFT JOIN company_department d ON e.dept_id = d.dept_id;

-- Right outer join: all from right table + matched from left
SELECT e.emp_id, e.emp_name, e.age, d.dept_name
FROM company_employee e
RIGHT JOIN company_department d ON e.dept_id = d.dept_id;

-- Full outer join (emulated via UNION)
(SELECT e.emp_id, e.emp_name, e.age, d.dept_name
FROM company_employee e
RIGHT JOIN company_department d ON e.dept_id = d.dept_id)
UNION
(SELECT e.emp_id, e.emp_name, e.age, d.dept_name
FROM company_employee e
LEFT JOIN company_department d ON e.dept_id = d.dept_id);

-- UNION removes duplicates; UNION ALL preserves all rows

Compound Condition Joins

SELECT DISTINCT d.dept_name
FROM company_employee e
JOIN company_department d ON e.dept_id = d.dept_id
WHERE e.age >= 30;

SELECT e.emp_id, e.emp_name, e.age, d.dept_name
FROM company_employee e
JOIN company_department d ON e.dept_id = d.dept_id
ORDER BY e.age ASC;

Subqueries

-- IN subquery
SELECT * FROM company_employee
WHERE dept_id IN (SELECT dept_id FROM company_department);

-- Comparison operators in subquery
SELECT d.dept_id, d.dept_name
FROM company_department d
WHERE dept_id IN (
    SELECT DISTINCT dept_id FROM company_employee WHERE age > 28
);

-- EXISTS subquery
SELECT * FROM company_employee e
WHERE EXISTS (
    SELECT 1 FROM company_department d WHERE d.dept_id = 102
);

Note: When EXISTS returns TRUE, the outer query executes; when FALSE, results are empty.

Tags: MySQL sql database Query foreign-key

Posted on Thu, 03 Sep 2026 16:01:25 +0000 by BrianWald

Hot Tags

©2026 Freaks City