MySQL - Statements Reference

Welcome, future database wizards! Today, we're diving into the magical world of MySQL statements. Don't worry if you've never written a line of code before - by the end of this tutorial, you'll be conjuring up database spells like a pro!

Data Definition Statements

Let's start with the building blocks of our database kingdom: Data Definition Statements. These are the commands we use to create, modify, and delete the structure of our database.

CREATE TABLE

Imagine you're building a library. The first thing you need is bookshelves, right? In MySQL, our "bookshelves" are tables. Let's create one!

CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(50),
    publication_year INT
);

This spell... I mean, statement, creates a table called "books" with four columns:

id: A unique identifier for each book
title: The book's title (up to 100 characters)
author: The book's author (up to 50 characters)
publication_year: The year the book was published

ALTER TABLE

Oops! We forgot to add a column for the book's genre. No worries, we can alter our table:

ALTER TABLE books
ADD COLUMN genre VARCHAR(30);

This adds a new column called "genre" to our "books" table.

DROP TABLE

If we decide we don't need our table anymore (maybe we're switching to e-books?), we can remove it:

DROP TABLE books;

Be careful with this one, though! It's like setting fire to your bookshelf - there's no going back!

Data Manipulation Statements

Now that we have our structure, let's fill it with data!

INSERT

Time to add some books to our library:

INSERT INTO books (title, author, publication_year, genre)
VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 1925, 'Classic');

This adds one book to our table. You can add multiple books in one go:

INSERT INTO books (title, author, publication_year, genre)
VALUES 
('To Kill a Mockingbird', 'Harper Lee', 1960, 'Fiction'),
('1984', 'George Orwell', 1949, 'Science Fiction');

SELECT

Now, let's see what books we have:

SELECT * FROM books;

This retrieves all columns (*) from the "books" table. You can be more specific:

SELECT title, author FROM books WHERE publication_year < 1950;

This fetches only the title and author of books published before 1950.

UPDATE

Oops, we made a mistake! "1984" is actually dystopian fiction:

UPDATE books
SET genre = 'Dystopian Fiction'
WHERE title = '1984';

This changes the genre for the book titled "1984".

DELETE

Let's say we want to remove all books published before 1930:

DELETE FROM books
WHERE publication_year < 1930;

Be careful with DELETE - like DROP, it's permanent!

Transactional and Locking Statements

Sometimes, we need to make sure a series of operations happen all at once, or not at all. That's where transactions come in.

START TRANSACTION

START TRANSACTION;
INSERT INTO books (title, author, publication_year, genre)
VALUES ('Pride and Prejudice', 'Jane Austen', 1813, 'Romance');
UPDATE books SET publication_year = 1960 WHERE title = 'To Kill a Mockingbird';
COMMIT;

This ensures both the INSERT and UPDATE happen together. If there's an error, neither will take effect.

ROLLBACK

If we change our mind mid-transaction:

START TRANSACTION;
DELETE FROM books WHERE publication_year < 1900;
-- Oops, we didn't mean to do that!
ROLLBACK;

ROLLBACK cancels all changes made since START TRANSACTION.

Prepared Statements

Prepared statements are like recipes - you set them up once, then use them multiple times with different ingredients.

PREPARE stmt FROM 'SELECT * FROM books WHERE author = ?';
SET @a = 'George Orwell';
EXECUTE stmt USING @a;

This prepares a statement to find books by a specific author, then executes it for George Orwell.

Compound Statements

Compound statements let us group multiple statements together. They're often used in stored procedures and functions.

DELIMITER //
CREATE PROCEDURE CountBooks()
BEGIN
    DECLARE bookCount INT;
    SELECT COUNT(*) INTO bookCount FROM books;
    SELECT CONCAT('There are ', bookCount, ' books in the library.') AS Message;
END //
DELIMITER ;

CALL CountBooks();

This creates a procedure that counts the books and displays a message.

Variables in Stored Programs

Variables in stored programs are like little boxes where we can store information temporarily.

DELIMITER //
CREATE PROCEDURE OldestBook()
BEGIN
    DECLARE oldestYear INT;
    DECLARE oldestTitle VARCHAR(100);

    SELECT MIN(publication_year), title 
    INTO oldestYear, oldestTitle 
    FROM books;

    SELECT CONCAT('The oldest book is ', oldestTitle, ' published in ', oldestYear) AS Message;
END //
DELIMITER ;

CALL OldestBook();

This procedure finds the oldest book and tells us about it.

MySQL Flow Control Statements

Flow control statements help us make decisions in our code.

DELIMITER //
CREATE PROCEDURE CategoryByYear(IN pub_year INT)
BEGIN
    DECLARE category VARCHAR(20);
    IF pub_year < 1900 THEN
        SET category = 'Very Old';
    ELSEIF pub_year < 2000 THEN
        SET category = 'Classic';
    ELSE
        SET category = 'Modern';
    END IF;
    SELECT category AS 'Book Category';
END //
DELIMITER ;

CALL CategoryByYear(1925);

This categorizes a book based on its publication year.

MySQL Cursors Statements

Cursors allow us to iterate through a result set one row at a time.

DELIMITER //
CREATE PROCEDURE ListBooks()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE book_title VARCHAR(100);
    DECLARE cur CURSOR FOR SELECT title FROM books;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO book_title;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SELECT book_title;
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;

CALL ListBooks();

This procedure lists all book titles one by one.

MySQL Condition Handling Statements

Condition handlers let us gracefully handle errors and warnings.

DELIMITER //
CREATE PROCEDURE SafeInsert(IN book_title VARCHAR(100), IN book_author VARCHAR(50))
BEGIN
    DECLARE EXIT HANDLER FOR 1062
    BEGIN
        SELECT 'Error: Duplicate entry' AS Message;
    END;

    INSERT INTO books (title, author) VALUES (book_title, book_author);
    SELECT 'Book added successfully' AS Message;
END //
DELIMITER ;

CALL SafeInsert('The Great Gatsby', 'F. Scott Fitzgerald');

This procedure safely inserts a book, handling the case where the book might already exist.

And there you have it! You've just taken your first steps into the world of MySQL statements. Remember, practice makes perfect, so don't be afraid to experiment with these commands. Before you know it, you'll be managing databases like a seasoned pro!

Credits: Image by storyset

Previous Tutorial:

MySQL - Useful Functions