MySQL SQL Syntax Quick Reference

01-gpt.md

MySQL SQL Syntax Quick Reference

Database Operations

-- Create a database
CREATE DATABASE dbname;

-- Use a database
USE dbname;

-- Drop a database
DROP DATABASE dbname;

Table Operations

-- Create a table
CREATE TABLE tablename (
    id INT PRIMARY KEY AUTO_INCREMENT,
    column1 VARCHAR(255) NOT NULL,
    column2 INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Show tables
SHOW TABLES;

-- Describe table structure
DESCRIBE tablename;

-- Drop a table
DROP TABLE tablename;

-- Alter a table
ALTER TABLE tablename ADD column3 DATE;
ALTER TABLE tablename MODIFY column1 VARCHAR(100);
ALTER TABLE tablename DROP COLUMN column2;

Data Manipulation

-- Insert data
INSERT INTO tablename (column1, column2) VALUES ('value1', 123);

-- Insert multiple rows
INSERT INTO tablename (column1, column2) VALUES ('value1', 123), ('value2', 456);

-- Select data
SELECT * FROM tablename;
SELECT column1, column2 FROM tablename WHERE column2 > 100 ORDER BY column1 DESC LIMIT 10;

-- Update data
UPDATE tablename SET column1 = 'newvalue' WHERE id = 1;

-- Delete data
DELETE FROM tablename WHERE id = 1;

Query Clauses

-- WHERE clause
SELECT * FROM tablename WHERE column1 = 'value';

-- ORDER BY clause
SELECT * FROM tablename ORDER BY column1 ASC, column2 DESC;

-- LIMIT clause
SELECT * FROM tablename LIMIT 5 OFFSET 10;

-- GROUP BY and HAVING
SELECT column1, COUNT(*) FROM tablename GROUP BY column1 HAVING COUNT(*) > 1;

Joins

-- INNER JOIN
SELECT a.*, b.* FROM tableA a INNER JOIN tableB b ON a.id = b.a_id;

-- LEFT JOIN
SELECT a.*, b.* FROM tableA a LEFT JOIN tableB b ON a.id = b.a_id;

-- RIGHT JOIN
SELECT a.*, b.* FROM tableA a RIGHT JOIN tableB b ON a.id = b.a_id;

Indexes

-- Create index
CREATE INDEX idx_column1 ON tablename (column1);

-- Drop index
DROP INDEX idx_column1 ON tablename;

Users & Privileges

-- Create user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- Grant privileges
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';

-- Revoke privileges
REVOKE ALL PRIVILEGES ON dbname.* FROM 'username'@'localhost';

-- Show users
SELECT user, host FROM mysql.user;

Useful Commands

-- Show databases
SHOW DATABASES;

-- Show columns
SHOW COLUMNS FROM tablename;

-- Show current database
SELECT DATABASE();

-- Show current user
SELECT USER();

Variables

MySQL supports two main types of variables: user-defined variables and system variables. User-defined variables are session-specific and can be used to store temporary values, while system variables control the operation of the MySQL server.

User-Defined Variables

• User-defined variables are prefixed with @ and exist for the duration of the session.
• They can be assigned and used in SQL statements.

Assigning Values

-- Assign a value using SET
SET @myvar = 10;

-- Assign a value using SELECT
SELECT @myvar := 20;

-- Assign from a query result
SELECT column1 INTO @myvar FROM mytable WHERE id = 1;

Using Variables

-- Use in expressions
SELECT @myvar + 5 AS result;

-- Use in WHERE clause
SELECT * FROM mytable WHERE id = @myvar;

-- Use in UPDATE
UPDATE mytable SET column1 = @myvar WHERE id = 2;

SELECT INTO Variable

-- Assign a single value
SELECT column1 INTO @myvar FROM mytable WHERE id = 1;

-- Assign multiple values
SELECT column1, column2 INTO @var1, @var2 FROM mytable WHERE id = 1;

Using Variables in Loops and Procedures

-- Example in a stored procedure
DELIMITER //
CREATE PROCEDURE example_proc()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE val INT;
    DECLARE cur CURSOR FOR SELECT id FROM mytable;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO val;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- Use variable val here
        UPDATE mytable SET processed = 1 WHERE id = val;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

Using Variables in INSERT/UPDATE Statements

-- Use variable in INSERT
INSERT INTO mytable (column1) VALUES (@myvar);

-- Use variable in UPDATE
UPDATE mytable SET column1 = @myvar WHERE id = 1;

Using Variables in Table Names (Dynamic SQL)

• MySQL does not support using variables directly as table or column names in static SQL.
• To use variables for table names, you must use prepared statements (dynamic SQL).

-- Example: Dynamic table name with prepared statement
SET @table = 'mytable';
SET @sql = CONCAT('SELECT * FROM ', @table, ' WHERE id = 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Example: Create Table with Variable Table Name

SET @tablename = 'new_table';
SET @sql = CONCAT('CREATE TABLE ', @tablename, ' (id INT PRIMARY KEY, name VARCHAR(50))');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Example: Insert Using Variable Table Name

SET @tablename = 'mytable';
SET @sql = CONCAT('INSERT INTO ', @tablename, ' (column1) VALUES (123)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Local Variables in Stored Programs

• Local variables are declared with DECLARE and only exist within stored procedures, functions, or triggers.

-- Declare and use local variable in a procedure
DELIMITER //
CREATE PROCEDURE local_var_example()
BEGIN
    DECLARE counter INT DEFAULT 0;
    SET counter = counter + 1;
    SELECT counter;
END //
DELIMITER ;

System Variables

• System variables control server operation.
• Global variables: @@global.var_name
• Session variables: @@session.var_name or just @@var_name

-- View all system variables
SHOW VARIABLES;

-- View a specific variable
SHOW VARIABLES LIKE 'max_connections';

-- Set a session variable
SET SESSION sql_mode = 'TRADITIONAL';

-- Set a global variable (requires privileges)
SET GLOBAL max_connections = 200;

Notes

• User-defined variables are case-insensitive.
• Local variables (in stored programs) are case-sensitive.
• User-defined variables can be used in most SQL statements, but not as identifiers (table/column names) without dynamic SQL.
• Always use DEALLOCATE PREPARE after using prepared statements to free resources.

More date and time operations

MySQL provides a wide range of functions and operations for working with dates and times. Below are commonly used examples for manipulating, formatting, and calculating with date and time values.

Getting Current Date and Time

SELECT NOW();         -- Current date and time (YYYY-MM-DD HH:MM:SS)
SELECT CURDATE();     -- Current date (YYYY-MM-DD)
SELECT CURTIME();     -- Current time (HH:MM:SS)
SELECT UTC_DATE();    -- Current UTC date
SELECT UTC_TIME();    -- Current UTC time
SELECT UTC_TIMESTAMP(); -- Current UTC date and time

Extracting Parts of Dates and Times

SELECT YEAR(NOW());        -- Year (e.g., 2025)
SELECT MONTH(NOW());       -- Month (1-12)
SELECT DAY(NOW());         -- Day of month (1-31)
SELECT HOUR(NOW());        -- Hour (0-23)
SELECT MINUTE(NOW());      -- Minute (0-59)
SELECT SECOND(NOW());      -- Second (0-59)
SELECT DAYOFWEEK(NOW());   -- Day of week (1=Sunday, 7=Saturday)
SELECT DAYOFYEAR(NOW());   -- Day of year (1-366)
SELECT WEEK(NOW());        -- Week number (0-53)
SELECT QUARTER(NOW());     -- Quarter (1-4)
SELECT DATE(NOW());        -- Date part only
SELECT TIME(NOW());        -- Time part only

Formatting Dates and Times

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');           -- 2025-07-23
SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y');      -- Wednesday, July 23, 2025
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');           -- 14:35:00
SELECT TIME_FORMAT(NOW(), '%h:%i %p');           -- 02:35 PM

Parsing Strings to Dates

SELECT STR_TO_DATE('23-07-2025', '%d-%m-%Y');    -- 2025-07-23
SELECT STR_TO_DATE('07/23/25', '%m/%d/%y');      -- 2025-07-23

Date and Time Arithmetic

Adding and Subtracting

-- Add days, months, years, etc.
SELECT DATE_ADD('2025-07-23', INTERVAL 10 DAY);      -- 2025-08-02
SELECT DATE_ADD('2025-07-23', INTERVAL 2 MONTH);     -- 2025-09-23
SELECT DATE_ADD('2025-07-23 10:00:00', INTERVAL 3 HOUR); -- 2025-07-23 13:00:00

-- Subtract days, months, years, etc.
SELECT DATE_SUB('2025-07-23', INTERVAL 5 DAY);       -- 2025-07-18
SELECT DATE_SUB('2025-07-23', INTERVAL 1 YEAR);      -- 2024-07-23

-- Add or subtract using arithmetic
SELECT '2025-07-23' + INTERVAL 1 DAY;                -- 2025-07-24
SELECT '2025-07-23' - INTERVAL 1 MONTH;              -- 2025-06-23

TIMESTAMPDIFF and DATEDIFF

SELECT DATEDIFF('2025-08-01', '2025-07-23');         -- 9 (days)
SELECT TIMESTAMPDIFF(DAY, '2025-07-23', '2025-08-01'); -- 9
SELECT TIMESTAMPDIFF(MONTH, '2025-01-01', '2025-07-23'); -- 6
SELECT TIMESTAMPDIFF(YEAR, '2010-01-01', '2025-07-23');  -- 15
SELECT TIMESTAMPDIFF(HOUR, '2025-07-23 10:00:00', '2025-07-23 15:00:00'); -- 5

TIMEDIFF

SELECT TIMEDIFF('14:00:00', '09:30:00');             -- 04:30:00
SELECT TIMEDIFF('2025-07-23 14:00:00', '2025-07-23 09:30:00'); -- 04:30:00

Rounding and Truncating Dates

SELECT DATE(NOW());                                  -- Truncate to date
SELECT TIME(NOW());                                  -- Truncate to time
SELECT LAST_DAY('2025-07-23');                       -- Last day of the month (2025-07-31)
SELECT MAKEDATE(2025, 204);                          -- 204th day of 2025 (2025-07-23)
SELECT MAKETIME(14, 30, 0);                          -- 14:30:00

Comparing Dates and Times

SELECT * FROM events WHERE event_date = CURDATE();
SELECT * FROM events WHERE event_date BETWEEN '2025-07-01' AND '2025-07-31';
SELECT * FROM events WHERE event_time > '12:00:00';

Converting Between Date/Time Types

SELECT CAST('2025-07-23' AS DATETIME);               -- 2025-07-23 00:00:00
SELECT CAST('14:30:00' AS TIME);                     -- 14:30:00
SELECT UNIX_TIMESTAMP('2025-07-23 14:30:00');        -- 1753271400 (seconds since epoch)
SELECT FROM_UNIXTIME(1753271400);                    -- 2025-07-23 14:30:00

Extracting and Manipulating with EXTRACT

SELECT EXTRACT(YEAR FROM NOW());                     -- 2025
SELECT EXTRACT(MONTH FROM NOW());                    -- 7
SELECT EXTRACT(DAY FROM NOW());                      -- 23
SELECT EXTRACT(HOUR_MINUTE FROM NOW());              -- 1435

Working with Time Zones

-- Show current time zone
SELECT @@global.time_zone, @@session.time_zone;

-- Set session time zone
SET time_zone = '+00:00';        -- UTC
SET time_zone = 'America/New_York';

-- Convert between time zones
SELECT CONVERT_TZ('2025-07-23 14:00:00', 'UTC', 'America/New_York');

Useful Date/Time Functions

SELECT ADDDATE('2025-07-23', INTERVAL 5 DAY);        -- 2025-07-28
SELECT SUBDATE('2025-07-23', INTERVAL 3 DAY);        -- 2025-07-20
SELECT DAYNAME('2025-07-23');                        -- Wednesday
SELECT MONTHNAME('2025-07-23');                      -- July
SELECT WEEKDAY('2025-07-23');                        -- 2 (0=Monday)
SELECT PERIOD_DIFF(202507, 202401);                  -- 6 (months between periods)

Auto-updating Timestamps

-- Automatically set and update timestamp on row changes
CREATE TABLE example (
    id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Notes

• MySQL date and time types: DATE, TIME, DATETIME, TIMESTAMP, YEAR
• Always use single quotes for date/time literals.
• Functions like NOW() and CURDATE() are evaluated per statement, not per row.

02-gemini.md

MySQL SQL Syntax Quick Reference

A quick reference guide for common MySQL statements and syntax.

Table of Contents

• Comments
• Data Definition Language (DDL)
  • Databases
  • Tables
• Data Manipulation Language (DML)
• Data Query Language (DQL) - The SELECT Statement
• Joins
• Common Functions
  • Aggregate Functions
  • String Functions
  • Numeric Functions
  • Date & Time Functions
• Indexes
• Transactions
• Data Control Language (DCL)

Comments

-- This is a single-line comment
# This is also a single-line comment (less common)
/*
This is a
multi-line comment.
*/

Data Definition Language (DDL)

Used to define or modify the database structure.

Databases

-- Create a new database
CREATE DATABASE [IF NOT EXISTS] database_name;

-- Select a database to use for subsequent queries
USE database_name;

-- Show all available databases
SHOW DATABASES;

-- Delete a database permanently
DROP DATABASE [IF EXISTS] database_name;

Tables

-- Create a new table with various column types and constraints
CREATE TABLE [IF NOT EXISTS] table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    description TEXT,
    salary DECIMAL(10, 2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Show the structure of a table
DESCRIBE table_name;
-- or
SHOW COLUMNS FROM table_name;

-- Modify an existing table
ALTER TABLE table_name
    ADD COLUMN new_column VARCHAR(50),      -- Add a column
    DROP COLUMN old_column,                 -- Remove a column
    MODIFY COLUMN existing_column TEXT,     -- Change a column's data type
    RENAME COLUMN old_name TO new_name,     -- Rename a column
    ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES other_table(id); -- Add a constraint

-- Rename a table
ALTER TABLE old_table_name RENAME TO new_table_name;

-- Delete a table and all its data
DROP TABLE [IF EXISTS] table_name;

-- Delete all data from a table (faster than DELETE, resets auto-increment)
TRUNCATE TABLE table_name;

Data Manipulation Language (DML)

Used to manage data within tables.

-- Insert a new row into a table
INSERT INTO table_name (column1, column2, column3)
VALUES ('value1', 'value2', 123);

-- Insert multiple rows at once
INSERT INTO table_name (column1, column2) VALUES
('value1a', 'value2a'),
('value1b', 'value2b');

-- Update existing rows that match a condition
UPDATE table_name
SET column1 = 'new_value1', column2 = 456
WHERE condition; -- IMPORTANT: Always use a WHERE clause!

-- Delete rows that match a condition
DELETE FROM table_name
WHERE condition; -- IMPORTANT: Always use a WHERE clause!

Data Query Language (DQL) - The SELECT Statement

Used to retrieve data from tables.

-- Select all columns from a table
SELECT * FROM table_name;

-- Select specific columns from a table
SELECT column1, column2 FROM table_name;

-- Select data with filtering
SELECT * FROM table_name WHERE column1 > 100;

-- Common WHERE clause operators
-- =, !=, <, >, <=, >=, AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL, IS NOT NULL

-- Using WHERE with various operators
SELECT * FROM products WHERE price BETWEEN 50 AND 100;
SELECT * FROM users WHERE city IN ('New York', 'London', 'Tokyo');
SELECT * FROM users WHERE name LIKE 'J%'; -- Starts with J
SELECT * FROM users WHERE name LIKE '%son'; -- Ends with son
SELECT * FROM users WHERE email LIKE '%@example.com%'; -- Contains
SELECT * FROM users WHERE manager_id IS NULL;

-- Sort the results
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC; -- ASC is default

-- Limit the number of results (for pagination)
SELECT * FROM table_name LIMIT 10;        -- First 10 rows
SELECT * FROM table_name LIMIT 50, 10;    -- Skip 50 rows, return next 10 (offset, count)

-- Group rows that have the same values in specified columns into summary rows
SELECT department, COUNT(id) as employee_count
FROM employees
GROUP BY department;

-- Filter groups based on aggregate functions
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Joins

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

-- INNER JOIN: Returns records that have matching values in both tables
SELECT orders.id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

-- LEFT JOIN: Returns all records from the left table, and the matched records from the right
SELECT users.name, profiles.bio
FROM users
LEFT JOIN profiles ON users.id = profiles.user_id;

-- RIGHT JOIN: Returns all records from the right table, and the matched records from the left
SELECT users.name, profiles.bio
FROM users
RIGHT JOIN profiles ON users.id = profiles.user_id;

-- FULL OUTER JOIN (emulated in MySQL with UNION)
SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT * FROM table1
RIGHT JOIN table2 ON table1.id = table2.id;

Common Functions

Aggregate Functions

Used with GROUP BY or to summarize an entire table.

SELECT
    COUNT(id),          -- Total number of (non-null) rows
    SUM(price),         -- Sum of values in a column
    AVG(price),         -- Average of values
    MIN(price),         -- Minimum value
    MAX(price)          -- Maximum value
FROM products;

String Functions

SELECT
    CONCAT(first_name, ' ', last_name), -- Concatenate strings
    LENGTH(name),                       -- Get string length in bytes
    UPPER(name),                        -- Convert to uppercase
    LOWER(name),                        -- Convert to lowercase
    SUBSTRING(email, 1, 5),             -- Extract a substring
    TRIM(name)                          -- Remove leading/trailing spaces
FROM users;

Numeric Functions

SELECT
    ROUND(price, 2),  -- Round to a number of decimal places
    CEIL(price),      -- Round up to the nearest integer
    FLOOR(price),     -- Round down to the nearest integer
    ABS(value)        -- Get the absolute value
FROM products;

Date & Time Functions

SELECT
    NOW(),                  -- Current date and time
    CURDATE(),              -- Current date
    CURTIME(),              -- Current time
    DATEDIFF('2023-12-25', '2023-01-01'), -- Difference in days between two dates
    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') -- Format a date
FROM orders;

Indexes

Used to speed up the performance of queries.

-- Create an index on one or more columns
CREATE INDEX idx_lastname ON employees (last_name);
CREATE INDEX idx_fullname ON employees (last_name, first_name);

-- Drop an index
DROP INDEX idx_lastname ON employees;

Note: Primary Keys and Unique constraints are automatically indexed.

Transactions

A sequence of operations performed as a single logical unit of work.

-- Start a new transaction
START TRANSACTION;

-- Perform some operations
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If everything is fine, save the changes
COMMIT;

-- If something went wrong, undo all changes since the transaction started
-- ROLLBACK;

Data Control Language (DCL)

Used to manage user access and permissions.

-- Create a new user
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- Grant privileges to a user on a specific database or table
GRANT SELECT, INSERT, UPDATE ON my_database.* TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON my_database.my_table TO 'username'@'localhost';

-- Revoke privileges from a user
REVOKE UPDATE ON my_database.* FROM 'username'@'localhost';

-- Delete a user
DROP USER 'username'@'host';

Of course. Here is the comprehensive section on MySQL variables, formatted exactly as you requested.

Variables

In MySQL, variables are powerful constructs that allow you to store values temporarily for use in later statements within the same session. There are two main types: user-defined session variables and local variables used in stored programs.

User-Defined Variables (@variable_name)

These variables are tied to your specific user session. They are initialized when you set them and last until you disconnect. The names are case-insensitive and are prefixed with an @ symbol.

Defining and Assigning Values

You can assign a value using SET or within a SELECT statement.

-- Using the SET statement (preferred for simple assignment)
SET @user_id = 105;
SET @user_name = 'Alice';
SET @last_login = NOW();

-- Using a SELECT statement
-- Note the use of the walrus operator `:=` for assignment inside other statements
SELECT @total_users := COUNT(*) FROM users;

-- You can then retrieve the value like any column
SELECT @user_id, @user_name, @last_login, @total_users;

Selecting Data Into Variables (SELECT ... INTO)

This is extremely useful for capturing a single value (or a full row) from a query result without needing to process a result set in your application.

Important: The query for SELECT ... INTO should return exactly one row. If it returns multiple rows, you will get an error. If it returns no rows, the variables will not be updated and will retain their previous values (or be NULL if not initialized).

-- Declare variables to hold the output
SET @employee_name = NULL;
SET @employee_salary = NULL;

-- Fetch values from a single row into the variables
SELECT
    full_name,
    salary
INTO
    @employee_name,
    @employee_salary
FROM
    employees
WHERE
    id = 42
LIMIT 1; -- Using LIMIT 1 is a good practice to ensure a single row

-- Now you can use these variables
SELECT CONCAT(@employee_name, ' has a salary of ', @employee_salary) AS result;

Updating a Field Using a Variable

Variables are perfect for holding calculated values that you then want to use in an UPDATE statement.

-- Step 1: Calculate the average salary for a department and store it
SELECT AVG(salary) INTO @avg_dept_salary FROM employees WHERE department_id = 5;

-- Step 2: Use this calculated value to update a summary table or another record
UPDATE department_summary
SET
    average_salary = @avg_dept_salary,
    last_updated = NOW()
WHERE
    department_id = 5;

Advanced Usage: Dynamic SQL with Prepared Statements

A common question is whether a variable can be used for a table or column name. You cannot do this directly. SQL identifiers (like table names, column names, database names) must be known when the statement is parsed. They cannot be substituted by a variable at execution time.

The Solution: Build the entire SQL query as a string, and then use Prepared Statements to execute it.

Example: Creating a Table with a Dynamic Name

-- 1. Set a variable for the dynamic part of your table name
SET @table_suffix = '2024_q3';

-- 2. Build the entire DDL query as a string using CONCAT()
SET @create_table_sql = CONCAT(
    'CREATE TABLE IF NOT EXISTS sales_', @table_suffix, ' (',
    '  id INT AUTO_INCREMENT PRIMARY KEY,',
    '  product_id INT,',
    '  sale_amount DECIMAL(10, 2),',
    '  sale_date DATE',
    ');'
);

-- 3. Prepare the statement from the string variable
PREPARE my_dynamic_statement FROM @create_table_sql;

-- 4. Execute the prepared statement
EXECUTE my_dynamic_statement;

-- 5. (Good practice) Clean up the prepared statement
DEALLOCATE PREPARE my_dynamic_statement;

-- You can now verify the table was created
SHOW TABLES LIKE 'sales_2024_q3';

Example: Adding a Column with a Dynamic Name

The same principle applies to ALTER TABLE or any other statement where you need a dynamic identifier.

SET @new_column_name = 'contact_email';
SET @target_table = 'customers';

SET @alter_sql = CONCAT(
    'ALTER TABLE ', @target_table,
    ' ADD COLUMN ', @new_column_name, ' VARCHAR(255);'
);

PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verify the column was added
DESCRIBE customers;

Local Variables (In Stored Programs)

Local variables are declared inside stored programs like procedures, functions, and triggers using the DECLARE keyword. They do not use the @ prefix. Their scope is limited to the BEGIN ... END block in which they are declared.

DELIMITER //

CREATE PROCEDURE sp_get_user_email(IN p_user_id INT)
BEGIN
    -- Declare a local variable. It is only visible inside this procedure.
    DECLARE v_user_email VARCHAR(255);
    DECLARE v_user_found BOOLEAN DEFAULT FALSE;

    -- Select a value into the local variable
    SELECT email, TRUE INTO v_user_email, v_user_found
    FROM users
    WHERE id = p_user_id;

    -- Use the local variable
    IF v_user_found THEN
        SELECT v_user_email AS user_email;
    ELSE
        SELECT 'User not found.' AS user_email;
    END IF;

END //

DELIMITER ;

-- To use it:
-- CALL sp_get_user_email(1);

More date and time operations

MySQL provides a rich set of functions to manipulate and query date and time values. These are essential for everything from logging timestamps to calculating business metrics.

Getting the Current Date and Time

These functions retrieve the server's current date and time without any arguments.

-- Get the current full date and time (YYYY-MM-DD HH:MI:SS)
SELECT NOW();

-- Get the current date only (YYYY-MM-DD)
SELECT CURDATE();

-- Get the current time only (HH:MI:SS)
SELECT CURTIME();

-- Get the current UTC date and time
SELECT UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME();

Extracting Parts of a Date or Time

Often you need just the year, month, day, or a specific component from a DATETIME or TIMESTAMP value.

SET @my_datetime = '2024-07-15 22:30:05';

SELECT
    YEAR(@my_datetime) AS `year`,          -- 2024
    MONTH(@my_datetime) AS `month_num`,    -- 7
    MONTHNAME(@my_datetime) AS `month_name`,-- 'July'
    DAY(@my_datetime) AS `day_of_month`, -- 15
    DAYNAME(@my_datetime) AS `day_name`,    -- 'Monday'
    DAYOFWEEK(@my_datetime) AS `day_of_week`,-- 2 (1=Sunday, 2=Monday,...)
    WEEKDAY(@my_datetime) AS `weekday_idx`,-- 0 (0=Monday, 1=Tuesday,...)
    DAYOFYEAR(@my_datetime) AS `day_of_year`,-- 197
    HOUR(@my_datetime) AS `hour`,          -- 22
    MINUTE(@my_datetime) AS `minute`,      -- 30
    SECOND(@my_datetime) AS `second`;      -- 5

The EXTRACT function provides a more standard SQL way to do the same thing.

SELECT EXTRACT(YEAR FROM '2024-07-15 22:30:05') AS `year`; -- 2024
SELECT EXTRACT(MONTH FROM '2024-07-15') AS `month`; -- 7
SELECT EXTRACT(DAY FROM '2024-07-15') AS `day`; -- 15
SELECT EXTRACT(HOUR_MINUTE FROM '2024-07-15 22:30:05') AS `hr_min`; -- 2230

Formatting Dates into Strings (DATE_FORMAT)

DATE_FORMAT is one of the most useful date functions, allowing you to convert a date into a custom string format.

SET @my_timestamp = '2024-10-05 09:08:03';

SELECT DATE_FORMAT(@my_timestamp, '%Y-%m-%d') AS `iso_date`; -- '2024-10-05'
SELECT DATE_FORMAT(@my_timestamp, '%m/%d/%Y') AS `us_date`; -- '10/05/2024'
SELECT DATE_FORMAT(@my_timestamp, '%W, %M %e, %Y') AS `full_readable`; -- 'Saturday, October 5, 2024'
SELECT DATE_FORMAT(@my_timestamp, 'Events on %W start at %h:%i %p') AS `custom_msg`; -- 'Events on Saturday start at 09:08 AM'

Common Format Specifiers:

• %Y: Year, 4 digits (e.g., 2024)
• %y: Year, 2 digits (e.g., 24)
• %m: Month, numeric (01-12)
• %c: Month, numeric (1-12)
• %M: Full month name (e.g., 'October')
• %b: Abbreviated month name (e.g., 'Oct')
• %d: Day of the month, 2 digits (01-31)
• %e: Day of the month, numeric (1-31)
• %W: Full weekday name (e.g., 'Saturday')
• %a: Abbreviated weekday name (e.g., 'Sat')
• %H: Hour (00-23)
• %h or %I: Hour (01-12)
• %i: Minutes (00-59)
• %s: Seconds (00-59)
• %p: AM or PM

Converting Strings to Dates (STR_TO_DATE)

This is the inverse of DATE_FORMAT. It parses a string into a MySQL date/time value. The format string must perfectly match the input string.

-- The format string '%m/%d/%Y' exactly matches the structure of '04/15/2024'
SELECT STR_TO_DATE('04/15/2024', '%m/%d/%Y'); -- Result: 2024-04-15

-- A more complex example
SELECT STR_TO_DATE('Tue, 01 Jan 2023 15:20', '%a, %d %b %Y %H:%i'); -- Result: 2023-01-01 15:20:00

Date and Time Arithmetic

Use DATE_ADD and DATE_SUB to add or subtract intervals from a date.

SET @start_date = '2024-03-15';

-- Adding intervals
SELECT DATE_ADD(@start_date, INTERVAL 1 MONTH) AS `plus_1_month`; -- '2024-04-15'
SELECT DATE_ADD(@start_date, INTERVAL 6 MONTH) AS `plus_6_months`;-- '2024-09-15'
SELECT DATE_ADD(@start_date, INTERVAL 14 DAY) AS `plus_14_days`; -- '2024-03-29'
SELECT DATE_ADD(@start_date, INTERVAL 2 YEAR) AS `plus_2_years`; -- '2026-03-15'

-- Subtracting intervals
SELECT DATE_SUB(NOW(), INTERVAL 30 MINUTE) AS `30_min_ago`;
SELECT DATE_SUB(CURDATE(), INTERVAL 1 QUARTER) AS `last_quarter`;

-- You can also use the `+` and `-` operators with INTERVAL
SELECT '2024-01-01' + INTERVAL 45 DAY; -- '2024-02-15'
SELECT '2024-01-01 12:00:00' - INTERVAL 8 HOUR; -- '2024-01-01 04:00:00'

Calculating Differences Between Dates

DATEDIFF: Returns the difference in days between two dates.

-- DATEDIFF(end_date, start_date)
SELECT DATEDIFF('2025-01-01', '2024-12-25') AS `days_between`; -- 7

TIMESTAMPDIFF: The most versatile function for finding the difference in a specified unit.

-- TIMESTAMPDIFF(unit, start_datetime, end_datetime)

SET @birth_date = '1990-05-20';
SET @current_date = '2024-07-15';

-- Classic example: Calculate age
SELECT TIMESTAMPDIFF(YEAR, @birth_date, @current_date) AS `age_in_years`; -- 34

-- Other units
SELECT TIMESTAMPDIFF(MONTH, @birth_date, @current_date) AS `age_in_months`; -- 410
SELECT TIMESTAMPDIFF(DAY, @birth_date, @current_date) AS `age_in_days`; -- 12474
SELECT TIMESTAMPDIFF(HOUR, '2024-01-01 08:00:00', '2024-01-02 10:00:00') AS `hours_between`; -- 26

Unix Timestamps

These functions convert between MySQL's DATETIME format and the standard Unix timestamp (seconds since '1970-01-01 00:00:00' UTC).

-- Get the current Unix timestamp
SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP(NOW());

-- Convert a specific date to a Unix timestamp
SELECT UNIX_TIMESTAMP('2024-01-01 00:00:00'); -- 1704067200 (adjusts for session time_zone)

-- Convert a Unix timestamp back to a readable DATETIME format
SELECT FROM_UNIXTIME(1704067200); -- '2024-01-01 00:00:00'