Skip to main content

MySQL Syntax

Introduction​

MySQL syntax refers to the set of rules that define how MySQL statements and commands must be structured to be correctly interpreted by the MySQL database server. Understanding MySQL syntax is essential for anyone working with databases, as it forms the foundation for querying, manipulating, and managing data effectively.

In this guide, we'll explore the fundamental syntax elements of MySQL, from basic statement structure to more complex query patterns. Whether you're writing simple data retrieval queries or complex data manipulation operations, mastering MySQL syntax will help you communicate with your database efficiently and effectively.

Basic MySQL Statement Structure​

All MySQL statements follow a consistent structure that makes them readable and predictable.

General Syntax Pattern​

sql
COMMAND action_specifics
[FROM source]
[WHERE conditions]
[additional_clauses];

Key points to remember:

  • MySQL statements typically end with a semicolon ;
  • MySQL is not case-sensitive for keywords (SELECT is the same as select)
  • However, table and column names might be case-sensitive depending on your server configuration

Your First MySQL Statement​

Let's start with a simple SELECT statement:

sql
SELECT first_name, last_name FROM users;

Input:

sql
SELECT first_name, last_name FROM users;

Output:

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John       | Doe       |
| Jane       | Smith     |
| Robert     | Johnson   |
+------------+-----------+
3 rows in set (0.00 sec)

MySQL Data Types​

Understanding data types is crucial for defining tables and ensuring data integrity.

Numeric Types​

TypeDescriptionRange
INTStandard integer-2^31 to 2^31-1
TINYINTVery small integer-128 to 127
SMALLINTSmall integer-32,768 to 32,767
MEDIUMINTMedium-sized integer-8,388,608 to 8,388,607
BIGINTLarge integer-2^63 to 2^63-1
FLOATSingle precision floating-pointVaries
DOUBLEDouble precision floating-pointVaries
DECIMALFixed-point numberDepends on precision

String Types​

TypeDescriptionMaximum Size
CHARFixed-length string255 characters
VARCHARVariable-length string65,535 characters
TEXTLarge text65,535 characters
MEDIUMTEXTMedium-sized text16,777,215 characters
LONGTEXTVery large text4,294,967,295 characters

Date and Time Types​

TypeDescriptionFormat
DATEDate'YYYY-MM-DD'
TIMETime'HH:MM:SS'
DATETIMEDate and time'YYYY-MM-DD HH:MM:SS'
TIMESTAMPTimestamp'YYYY-MM-DD HH:MM:SS'
YEARYearYYYY

Database and Table Operations​

Creating a Database​

sql
CREATE DATABASE database_name;

Example:

sql
CREATE DATABASE bookstore;

To use this database:

sql
USE bookstore;

Creating a Table​

sql
CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    columnN datatype constraints
);

Let's create a table for our bookstore:

sql
CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(50) NOT NULL,
    genre VARCHAR(30),
    publication_year YEAR,
    price DECIMAL(6,2) NOT NULL
);

Inserting Data​

sql
INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

Example:

sql
INSERT INTO books (title, author, genre, publication_year, price)
VALUES 
('The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 1925, 12.99),
('To Kill a Mockingbird', 'Harper Lee', 'Fiction', 1960, 14.50),
('1984', 'George Orwell', 'Dystopian', 1949, 11.75);

Basic Query Structure​

The SELECT statement is the most common command for retrieving data.

Simple SELECT Query​

sql
SELECT column1, column2, ...
FROM table_name;

Example:

sql
SELECT title, author, price 
FROM books;

Output:

+------------------------+--------------------+-------+
| title                  | author             | price |
+------------------------+--------------------+-------+
| The Great Gatsby       | F. Scott Fitzgerald| 12.99 |
| To Kill a Mockingbird  | Harper Lee         | 14.50 |
| 1984                   | George Orwell      | 11.75 |
+------------------------+--------------------+-------+
3 rows in set (0.00 sec)

Adding Conditions with WHERE​

The WHERE clause filters results based on specified conditions.

sql
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

sql
SELECT title, author, price 
FROM books 
WHERE price < 13.00;

Output:

+------------------+--------------------+-------+
| title            | author             | price |
+------------------+--------------------+-------+
| The Great Gatsby | F. Scott Fitzgerald| 12.99 |
| 1984             | George Orwell      | 11.75 |
+------------------+--------------------+-------+
2 rows in set (0.00 sec)

Sorting Results with ORDER BY​

sql
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC];

Example:

sql
SELECT title, publication_year 
FROM books 
ORDER BY publication_year ASC;

Output:

+------------------------+------------------+
| title                  | publication_year |
+------------------------+------------------+
| The Great Gatsby       | 1925             |
| 1984                   | 1949             |
| To Kill a Mockingbird  | 1960             |
+------------------------+------------------+
3 rows in set (0.00 sec)

Update and Delete Operations​

Updating Records​

sql
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

sql
UPDATE books
SET price = 13.25
WHERE title = 'The Great Gatsby';

Deleting Records​

sql
DELETE FROM table_name
WHERE condition;

Example:

sql
DELETE FROM books
WHERE publication_year < 1930;

Advanced Query Techniques​

Joins​

Joins combine rows from two or more tables based on a related column.

INNER JOIN​

sql
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Let's create an authors table for our example:

sql
CREATE TABLE authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    author_name VARCHAR(50) NOT NULL,
    birth_year YEAR,
    nationality VARCHAR(30)
);

INSERT INTO authors (author_name, birth_year, nationality)
VALUES 
('F. Scott Fitzgerald', 1896, 'American'),
('Harper Lee', 1926, 'American'),
('George Orwell', 1903, 'British');

ALTER TABLE books
ADD COLUMN author_id INT,
ADD FOREIGN KEY (author_id) REFERENCES authors(author_id);

UPDATE books SET author_id = 1 WHERE author = 'F. Scott Fitzgerald';
UPDATE books SET author_id = 2 WHERE author = 'Harper Lee';
UPDATE books SET author_id = 3 WHERE author = 'George Orwell';

Now we can join the tables:

sql
SELECT books.title, authors.author_name, authors.nationality
FROM books
INNER JOIN authors ON books.author_id = authors.author_id;

Output:

+------------------------+--------------------+------------+
| title                  | author_name        | nationality|
+------------------------+--------------------+------------+
| The Great Gatsby       | F. Scott Fitzgerald| American   |
| To Kill a Mockingbird  | Harper Lee         | American   |
| 1984                   | George Orwell      | British    |
+------------------------+--------------------+------------+
3 rows in set (0.00 sec)

Aggregate Functions​

Aggregate functions perform calculations on a set of values.

Common aggregate functions:

  • COUNT() - counts rows
  • SUM() - calculates sum
  • AVG() - calculates average
  • MIN() - finds minimum value
  • MAX() - finds maximum value

Example:

sql
SELECT 
    COUNT(*) as total_books,
    AVG(price) as average_price,
    MAX(price) as highest_price,
    MIN(price) as lowest_price
FROM books;

Output:

+-------------+---------------+---------------+--------------+
| total_books | average_price | highest_price | lowest_price |
+-------------+---------------+---------------+--------------+
| 3           | 13.08         | 14.50         | 11.75        |
+-------------+---------------+---------------+--------------+
1 row in set (0.00 sec)

GROUP BY Clause​

The GROUP BY clause groups rows with the same values.

sql
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

Example:

sql
SELECT 
    nationality, 
    COUNT(*) as author_count
FROM authors
GROUP BY nationality;

Output:

+------------+--------------+
| nationality| author_count |
+------------+--------------+
| American   | 2            |
| British    | 1            |
+------------+--------------+
2 rows in set (0.00 sec)

MySQL Operators​

Comparison Operators​

OperatorDescription
=Equal to
<> or !=Not equal to
<Less than
>Greater than
<=Less than or equal to
>=Greater than or equal to

Logical Operators​

OperatorDescription
ANDTRUE if all conditions are TRUE
ORTRUE if any condition is TRUE
NOTReverses the result of a condition

Example:

sql
SELECT title, price, publication_year
FROM books
WHERE (price > 12.00 AND publication_year > 1950) OR genre = 'Dystopian';

Real-world Application Example​

Let's create a more comprehensive example of a bookstore management system:

sql
-- Create database
CREATE DATABASE IF NOT EXISTS bookstore_management;
USE bookstore_management;

-- Create tables
CREATE TABLE publishers (
    publisher_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    established_year YEAR,
    country VARCHAR(50)
);

CREATE TABLE authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE,
    biography TEXT
);

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    publisher_id INT,
    publication_date DATE,
    isbn VARCHAR(13) UNIQUE,
    genre VARCHAR(50),
    pages INT,
    price DECIMAL(6,2) NOT NULL,
    FOREIGN KEY (publisher_id) REFERENCES publishers(publisher_id)
);

CREATE TABLE book_authors (
    book_id INT,
    author_id INT,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

CREATE TABLE inventory (
    inventory_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    stock_count INT NOT NULL DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

-- Insert sample data
INSERT INTO publishers (name, established_year, country) VALUES
('Penguin Random House', 1927, 'USA'),
('HarperCollins', 1989, 'USA'),
('Oxford University Press', 1586, 'UK');

INSERT INTO authors (first_name, last_name, birth_date, biography) VALUES
('J.K.', 'Rowling', '1965-07-31', 'British author best known for Harry Potter series'),
('Stephen', 'King', '1947-09-21', 'American author of horror, supernatural fiction, and fantasy'),
('Agatha', 'Christie', '1890-09-15', 'British crime novelist, short story writer and playwright');

INSERT INTO books (title, publisher_id, publication_date, isbn, genre, pages, price) VALUES
('Harry Potter and the Philosopher\'s Stone', 1, '1997-06-26', '9780747532699', 'Fantasy', 223, 20.99),
('The Shining', 2, '1977-01-28', '9780385121675', 'Horror', 447, 18.50),
('Murder on the Orient Express', 3, '1934-01-01', '9780062693662', 'Mystery', 256, 14.99);

INSERT INTO book_authors (book_id, author_id) VALUES
(1, 1), -- Harry Potter by J.K. Rowling
(2, 2), -- The Shining by Stephen King
(3, 3); -- Murder on the Orient Express by Agatha Christie

INSERT INTO inventory (book_id, stock_count) VALUES
(1, 25),
(2, 13),
(3, 8);

Now let's perform some meaningful queries:

Query 1: Finding available books with author information​

sql
SELECT 
    b.title,
    CONCAT(a.first_name, ' ', a.last_name) AS author_name,
    p.name AS publisher,
    b.genre,
    b.price,
    i.stock_count
FROM 
    books b
JOIN 
    book_authors ba ON b.book_id = ba.book_id
JOIN 
    authors a ON ba.author_id = a.author_id
JOIN 
    publishers p ON b.publisher_id = p.publisher_id
JOIN 
    inventory i ON b.book_id = i.book_id
WHERE 
    i.stock_count > 0
ORDER BY 
    i.stock_count DESC;

Query 2: Finding books that need restocking​

sql
SELECT 
    b.title,
    i.stock_count,
    CASE 
        WHEN i.stock_count < 10 THEN 'Critical'
        WHEN i.stock_count < 20 THEN 'Low'
        ELSE 'Adequate'
    END AS stock_status
FROM 
    books b
JOIN 
    inventory i ON b.book_id = i.book_id
ORDER BY 
    i.stock_count ASC;

Query 3: Total inventory value by publisher​

sql
SELECT 
    p.name AS publisher,
    COUNT(b.book_id) AS total_books,
    SUM(i.stock_count) AS total_copies,
    SUM(b.price * i.stock_count) AS inventory_value
FROM 
    publishers p
JOIN 
    books b ON p.publisher_id = b.publisher_id
JOIN 
    inventory i ON b.book_id = i.book_id
GROUP BY 
    p.publisher_id
ORDER BY 
    inventory_value DESC;

Common MySQL Mistakes to Avoid​

  1. Forgetting Semicolons: Every MySQL statement must end with a semicolon (;).

  2. Not Using WHERE in UPDATE/DELETE: Without a WHERE clause, these operations affect all rows.

    sql
    -- Dangerous! Updates all records
    UPDATE books SET price = 20.00;

    -- Safe: Updates only specific records
    UPDATE books SET price = 20.00 WHERE book_id = 1;

  3. Using Reserved Words as Names: Avoid using MySQL reserved words for table or column names. If necessary, use backticks to escape them.

    sql
    -- Problematic table name (order is a reserved word)
    CREATE TABLE order (...); -- Error

    -- Correct approach
    CREATE TABLE `order` (...); -- Works, but not recommended
    CREATE TABLE book_order (...); -- Better approach

  4. Ignoring Data Types: Choose appropriate data types for your columns to optimize storage and performance.

  5. Not Indexing Properly: Missing indexes on frequently queried columns can significantly slow down your database.

MySQL Best Practices​

  1. Be Specific in SELECT Statements: Select only the columns you need rather than using SELECT *.

    sql
    -- Bad practice
    SELECT * FROM books;

    -- Good practice
    SELECT title, author, price FROM books;

  2. Use Meaningful Table and Column Names: Names should be descriptive and follow a consistent naming convention.

  3. Comment Your Queries: For complex queries, add comments to explain your logic.

    sql
    -- Find all books published after 2000 with stock less than 5
    SELECT 
        title, 
        publication_date,
        stock_count
    FROM 
        books
    JOIN 
        inventory USING (book_id)
    WHERE 
        YEAR(publication_date) > 2000
        AND stock_count < 5;

  4. Use Transactions for Multiple Operations: Wrap related operations in transactions to ensure data consistency.

    sql
    START TRANSACTION;

    UPDATE inventory SET stock_count = stock_count - 1 WHERE book_id = 1;
    INSERT INTO sales (book_id, sale_date, quantity, price) VALUES (1, CURDATE(), 1, 20.99);

    COMMIT;

  5. Normalize Your Database: Follow database normalization principles to reduce redundancy and improve data integrity.

Summary​

In this guide, we've covered the fundamental aspects of MySQL syntax:

  • Basic statement structure and how to create and manipulate databases and tables
  • Understanding and working with various data types
  • Constructing queries using SELECT with various clauses like WHERE, ORDER BY, and GROUP BY
  • Advanced query techniques including joins and aggregate functions
  • Real-world application examples through a bookstore management system
  • Common mistakes to avoid and best practices to follow

Mastering MySQL syntax is an ongoing journey. As you become comfortable with the basics, you'll discover more advanced features that can help you solve complex data problems efficiently.

Additional Resources and Exercises​

Resources​

Exercises​

  1. Basic Exercise: Create a table called customers with fields for customer ID, name, email, and registration date. Insert five sample records.

  2. Query Exercise: Write a query to find all books in the Fantasy genre that have more than 15 copies in stock.

  3. Joins Exercise: Write a query that lists all authors along with the number of books they've written, including authors who haven't written any books yet.

  4. Advanced Exercise: Create a view called bestsellers that shows the top 5 books based on sales (you'll need to create a sales table and add some sample data first).

  5. Database Design Exercise: Design a database schema for a library management system including tables for books, members, loans, and librarians.

By practicing these exercises, you'll reinforce your understanding of MySQL syntax and become more proficient in database management and querying.


If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)