mysql-quick-guide

MySQL - Quick Guide

MySQL - Introduction

What is a Database?

A database is a specialized application designed to efficiently store, manage, and retrieve large collections of data. While simple data can be stored in files or in-memory structures, a database provides robust APIs for creating, accessing, managing, searching, and replicating data with superior performance, reliability, and security.

Today, Relational Database Management Systems (RDBMS) are the standard for managing structured data. In an RDBMS, data is organized into tables. These tables can be linked, or related, to one another using keys, which allows for complex queries and data integrity.

A modern Relational Database Management System (RDBMS) is a software that:

• Enables you to implement a database with tables, columns, and indexes.
• Guarantees referential integrity between rows of various tables using constraints.
• Updates indexes automatically to maintain query performance.
• Interprets a Standard Query Language (SQL) to query and manipulate data from various tables.

RDBMS Terminology

Before we dive into MySQL, let’s review some key database terminology:

• Database: A structured collection of tables containing related data.
• Table: A collection of related data entries, organized in rows and columns, much like a spreadsheet.
• Column: A vertical entity in a table that contains all information of one specific type (e.g., a user_email column).
• Row: A horizontal entity in a table, representing a single record or entry (e.g., all the data for one specific user). Also known as a tuple.
• Redundancy: Storing the same data in multiple places. Generally avoided through a process called normalization, but sometimes used intentionally in denormalization for performance gains.
• Primary Key: A constraint that uniquely identifies each record in a table. The primary key column must contain unique values and cannot contain NULL values.
• Foreign Key: A key used to link two tables together. It’s a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
• Composite Key: A primary key that consists of more than one column, used when a single column is not sufficient to uniquely identify a record.
• Index: A data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space. It’s like the index at the back of a book.
• Referential Integrity: A property of data stating that all its references are valid. In the context of RDBMS, it ensures that a foreign key value always points to an existing row in the referenced table.

Why Choose MySQL?

MySQL is a fast, reliable, and easy-to-use RDBMS, making it a popular choice for developers and businesses of all sizes. Originally created by the Swedish company MySQL AB, it is now developed and maintained by Oracle Corporation. Its popularity stems from several key advantages:

• Open Source: MySQL Community Edition is free to use under the GPL license, making it accessible to everyone.
• Powerful and Scalable: It handles a large subset of the functionality of the most expensive and powerful database packages. It can manage large databases with billions of rows.
• Standard SQL: It uses a standard and well-known SQL data language, making it easy for developers to learn and use.
• Cross-Platform and Language-Agnostic: MySQL runs on virtually all platforms, including Linux, macOS, and Windows. It has robust connectors for all major programming languages like PHP, Python, Java, C++, Go, and more.
• Fast Performance: It is architected for speed and works exceptionally well even with large data sets, especially for read-heavy workloads.
• Vibrant Ecosystem: MySQL is widely supported by cloud providers (AWS, Google Cloud, Azure), hosting companies, and a vast array of third-party tools. Forks like MariaDB and Percona Server also contribute to a healthy and competitive ecosystem.
• Secure and Reliable: It includes robust features for security and data integrity, including transaction support with the InnoDB storage engine, which is the default.

Before You Begin

This tutorial is designed for beginners but assumes you have a basic understanding of using a command-line terminal. While the SQL concepts are universal, our code examples will focus on a modern PHP environment using the PDO extension for database interaction.

Having some familiarity with PHP will be beneficial, but is not strictly required to understand the SQL concepts.

MySQL - Installation

Modern installation methods for MySQL are straightforward. We’ll cover the most common platforms.

Installing MySQL with a Package Manager (Linux)

The recommended way to install MySQL on Linux is with your distribution’s package manager.

On Debian/Ubuntu:

sudo apt update
sudo apt install mysql-server

On RHEL/CentOS/Fedora:

sudo dnf install mysql-server

After installation, it’s highly recommended to run the included security script:

sudo mysql_secure_installation

This script will guide you through setting a root password, removing anonymous users, and other important security hardening steps.

Installing MySQL on Windows

For Windows, the official MySQL Installer is the best approach. It’s a wizard-based installer that bundles the MySQL server, tools like MySQL Workbench (a GUI client), and connectors for various programming languages.

1. Download the MySQL Installer from the official MySQL Downloads page.
2. Run the installer and choose the “Developer Default” setup type, which is a great starting point.
3. Follow the on-screen instructions. The installer will guide you through configuration, including setting the root password.
4. Ensure the installer adds the MySQL bin directory to your system’s PATH environment variable so you can run MySQL commands from any terminal window.

Installing MySQL with Docker (Cross-Platform)

Using Docker is an excellent, modern way to run MySQL in an isolated container, which is perfect for development without affecting your host system.

First, ensure you have Docker installed. Then, run this command to pull the latest MySQL image and start a container:

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my_secret_password -d -p 3306:3306 mysql:latest

• --name some-mysql: Gives your container a memorable name.
• -e MYSQL_ROOT_PASSWORD=...: Sets the root password for the database.
• -d: Runs the container in detached mode (in the background).
• -p 3306:3306: Maps port 3306 on your host machine to port 3306 inside the container, allowing you to connect to it.

Verifying MySQL Installation

Once installed, you can verify that the server is running and connect to it.

1. Check Server Status

Use the mysqladmin utility to check the server version and status.

mysqladmin --version -u root -p

It will prompt for the root password you set during installation and should produce output similar to this:

mysqladmin  Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)

If you get an error, your installation may have a problem or the server might not be running.

2. Connect with the MySQL Client

Connect to your server using the mysql command-line client.

mysql -u root -p

After entering your password, you should see the mysql> prompt. Now you can run SQL commands.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

To exit the client, type exit or \q and press Enter.

MySQL - Administration

Starting and Stopping the MySQL Server

The method for managing the MySQL service depends on your operating system.

Linux (with systemd)

Most modern Linux distributions use systemd.

# Start the server
sudo systemctl start mysqld

# Stop the server
sudo systemctl stop mysqld

# Restart the server
sudo systemctl restart mysqld

# Check the server's status
sudo systemctl status mysqld

Note: The service name might be mysql instead of mysqld on some systems (like Debian/Ubuntu).

Windows

On Windows, MySQL typically runs as a service. You can manage it from the Services application (services.msc). Find “MySQL” in the list, right-click it, and you can Start, Stop, or Restart it.

Modern User Account Management

Best Practice: Never modify the mysql.user table directly. Always use the CREATE USER, GRANT, REVOKE, and DROP USER SQL statements to manage users and their permissions. This is safer and more reliable.

Creating a New User

Use the CREATE USER statement. The user is identified by both a username and the host from which they can connect.

CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'a_very_secure_password';

• 'webapp' is the username.
• 'localhost' means this user can only connect from the same machine where the MySQL server is running. Use '%' to allow connections from any host (use with caution).
• IDENTIFIED BY ... sets the user’s password. MySQL will automatically handle hashing.

Granting Privileges

A new user has no permissions by default. Use the GRANT statement to assign privileges. It’s best practice to grant only the necessary permissions (Principle of Least Privilege).

-- Grant basic CRUD (Create, Read, Update, Delete) privileges on all tables in the 'TUTORIALS' database
GRANT SELECT, INSERT, UPDATE, DELETE ON TUTORIALS.* TO 'webapp'@'localhost';

-- Apply the privilege changes
FLUSH PRIVILEGES;

The FLUSH PRIVILEGES; command reloads the grant tables, making the changes take effect immediately. It’s often not required for GRANT statements but is good practice to know.

Common Administrative Commands

Here is a list of important MySQL commands you will use frequently:

• USE database_name;: Selects a database to work on. All subsequent queries will be run against this database.
• SHOW DATABASES;: Lists all databases accessible to the current user.
• SHOW TABLES;: Shows all tables in the currently selected database.
• SHOW COLUMNS FROM table_name; (or DESCRIBE table_name;): Shows the columns, data types, and other information for a table.
• SHOW INDEX FROM table_name;: Displays all indexes on a table, including the PRIMARY KEY.
• SHOW CREATE TABLE table_name;: Shows the CREATE TABLE statement needed to recreate the table, including its structure and indexes.

MySQL - Connecting with PHP (Using PDO)

While MySQL works with many programming languages, PHP remains a popular choice for web development. We will use PHP Data Objects (PDO) for all our examples. PDO provides a consistent, modern, and secure interface for accessing databases in PHP.

Why PDO? The older mysql_* functions have been removed from PHP because they lacked support for modern database features and were prone to security vulnerabilities like SQL Injection. PDO is the modern standard because:

• Security: It supports prepared statements, which are the best defense against SQL Injection.
• Consistency: It provides a consistent set of functions for interacting with any supported database (MySQL, PostgreSQL, SQLite, etc.).
• Error Handling: It offers robust exception-based error handling.
• Modern Features: It supports transactions, fetching data into objects, and other advanced features.

MySQL - Connection

Connecting from the Command Line

As shown in the installation section, you connect to MySQL from the command prompt using the mysql client:

# Connect as the root user (will prompt for password)
mysql -u root -p

# Connect as a specific user to a specific host
mysql -u webapp -p -h database.server.com

Once connected, you will see the mysql> prompt. You can disconnect at any time by typing exit.

mysql> exit
Bye

Connecting with a Modern PHP Script (PDO)

To connect to MySQL with PDO, you create a new PDO object. The constructor requires a Data Source Name (DSN), username, and password.

Best Practice: Always wrap your connection logic in a try...catch block to handle potential connection errors gracefully.

Example: Complete Connection Script

Create a file named db_connect.php.

<?php

$host = '127.0.0.1'; // or 'localhost'
$db   = 'TUTORIALS';
$user = 'webapp';
$pass = 'a_very_secure_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected to the $db database successfully!";
} catch (\PDOException $e) {
    // In a real application, you would log this error and show a generic message.
    // Never expose detailed error messages to the public.
    error_log($e->getMessage());
    die("Database connection failed. Please try again later.");
}

// The connection is now stored in the $pdo variable.
// When the script finishes, the connection is automatically closed.

?>

Key parts of the script:

• $dsn: The Data Source Name string. It tells PDO which driver to use (mysql), the host, database name, and character set.
• $options: An array of configuration options for the PDO connection. PDO::ERRMODE_EXCEPTION is crucial as it makes PDO throw exceptions on errors, which we can catch.

MySQL - Create Database

Using mysqladmin

You need administrative privileges to create a database. From the command line, mysqladmin is a quick way to do this.

# This will prompt for the root password
mysqladmin -u root -p create TUTORIALS

Using an SQL Statement

Inside the mysql> client, you can use the CREATE DATABASE SQL command.

CREATE DATABASE TUTORIALS CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Best Practice: Always specify the character set and collation when creating a database to ensure proper handling of international characters. utf8mb4 is the recommended standard.

Using a PHP Script (PDO)

To execute a simple command that doesn’t return data, like CREATE DATABASE, you can use the PDO::exec() method. Note that you must connect to MySQL without specifying a database name in the DSN, as the database doesn’t exist yet.

<?php

$host = '127.0.0.1';
$user = 'root'; // Needs create privileges
$pass = 'root_password';
$charset = 'utf8mb4';

// DSN without dbname
$dsn = "mysql:host=$host;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
];

$dbNameToCreate = 'TUTORIALS';

try {
    $pdo = new PDO($dsn, $user, $pass, $options);

    // Use backticks to escape the database name in case it's a reserved keyword
    $sql = "CREATE DATABASE `$dbNameToCreate` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";

    $pdo->exec($sql);

    echo "Database `$dbNameToCreate` created successfully!";

} catch (\PDOException $e) {
    error_log($e->getMessage());
    die("Could not create database: " . $e->getMessage());
}

?>

MySQL - Drop Database

Warning: Dropping a database is an irreversible action. All tables and data within it will be permanently deleted. Be absolutely certain before running this command.

Using mysqladmin

From the command line, you can use mysqladmin drop.

mysqladmin -u root -p drop TUTORIALS

# It will ask for confirmation:
# Dropping the database is potentially a very bad thing to do.
# Any data stored in the database will be destroyed.
#
# Do you really want to drop the 'TUTORIALS' database [y/N] y
# Database "TUTORIALS" dropped

Using a PHP Script (PDO)

Similar to creating a database, you can use PDO::exec() to run the DROP DATABASE command.

<?php

// ... (use the same connection setup as the create example) ...

$dbNameToDrop = 'TUTORIALS';

try {
    $pdo = new PDO($dsn, $user, $pass, $options);

    $sql = "DROP DATABASE `$dbNameToDrop`";
    $pdo->exec($sql);

    echo "Database `$dbNameToDrop` deleted successfully!";

} catch (\PDOException $e) {
    error_log($e->getMessage());
    die("Could not delete database: " . $e->getMessage());
}

?>

MySQL - Select Database

Selecting a Database from the Command Prompt

Once connected to the MySQL server, use the USE command to select a database to work with.

mysql> USE TUTORIALS;
Database changed

All subsequent queries will now be performed on the TUTORIALS database. Database, table, and column names in MySQL are typically case-sensitive on Linux systems, so use their exact names.

Selecting a Database with PHP (PDO)

With PDO, the most common and recommended way to select a database is to specify it in the DSN during the initial connection. This ensures your entire script session operates on the correct database.

// DSN includes 'dbname=TUTORIALS'
$dsn = "mysql:host=127.0.0.1;dbname=TUTORIALS;charset=utf8mb4";
$pdo = new PDO($dsn, $user, $pass, $options);

If you need to switch to a different database after connecting, you can still execute the USE command.

// Assuming $pdo is an existing connection
$pdo->exec('USE another_database');
echo "Switched to another_database.";

MySQL - Data Types

Choosing the correct data type for your columns is crucial for database optimization, data integrity, and performance. You should always use the smallest data type that can reliably hold all possible values for that field.

MySQL data types are broadly categorized into Numeric, Date and Time, and String types.

Numeric Data Types

MySQL supports all standard SQL numeric data types.

• TINYINT: A very small integer. Signed range: -128 to 127. Unsigned range: 0 to 255. Perfect for flags or small counters.
• SMALLINT: A small integer. Signed range: -32,768 to 32,767. Unsigned range: 0 to 65,535.
• INT (or INTEGER): A standard integer. Signed range: -2,147,483,648 to 2,147,483,647. Unsigned range: 0 to 4,294,967,295. The most common choice for integer IDs.
• BIGINT: A large integer. Used when INT is not large enough. Signed range is approx. -9.2 quintillion to 9.2 quintillion.
• DECIMAL(M,D) (or NUMERIC): A fixed-point number, excellent for financial calculations where precision is critical. M is the total number of digits, and D is the number of digits after the decimal point. E.g., DECIMAL(10, 2) can store numbers like 12345678.99.
• FLOAT: A single-precision floating-point number. Subject to potential rounding errors, not suitable for exact monetary values.
• DOUBLE: A double-precision floating-point number. More precision than FLOAT but still subject to rounding errors.
• BOOL (or BOOLEAN): A synonym for TINYINT(1). A zero value is considered false, and a non-zero value is considered true.

Date and Time Types

• DATE: Stores a date value in ‘YYYY-MM-DD’ format.
• TIME: Stores a time value in ‘HH:MM:SS’ format.
• DATETIME: Stores a date and time combination in ‘YYYY-MM-DD HH:MM:SS’ format. Range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
• TIMESTAMP: Similar to DATETIME, but with a smaller range (‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC). It has special properties: if a TIMESTAMP column has a DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attribute, its value is automatically set to the current timestamp on insert or update, respectively. It also stores values in UTC, converting them from/to the current session’s time zone.
• YEAR: Stores a year in 4-digit format.

String Types

• CHAR(M): A fixed-length string. If you define CHAR(10) and store ‘hi’, it will be padded with 8 spaces. Use for fixed-length data like postal codes or country codes.
• VARCHAR(M): A variable-length string. M represents the maximum length (up to 65,535). Use this for most text data, like names or titles.
• TEXT: For long-form text. Holds up to 65,535 characters. No need to specify a length.
• MEDIUMTEXT: Holds up to 16,777,215 characters.
• LONGTEXT: Holds up to 4,294,967,295 characters.
• BLOB: “Binary Large Objects”. Used for storing binary data like images or files directly in the database (though this is often not the recommended approach). They have corresponding TINYBLOB, MEDIUMBLOB, and LONGBLOB types.
• ENUM: An enumeration. A string object that can have only one value, chosen from a predefined list of allowed values. E.g., ENUM('pending', 'active', 'suspended').
• JSON: A native JSON data type (since MySQL 5.7). Stores JSON documents and allows for efficient access to data within them using built-in functions. Excellent for storing semi-structured data.

MySQL - Create Tables

The CREATE TABLE statement is used to define a new table. You must specify the table name, a list of column names, their data types, and any constraints.

Syntax

CREATE TABLE table_name (
    column1_name column1_type [CONSTRAINTS],
    column2_name column2_type [CONSTRAINTS],
    ...
    PRIMARY KEY (column_name)
) ENGINE=InnoDB;

Best Practice: Always specify the storage engine. InnoDB is the modern default and should be used in almost all cases as it supports transactions, foreign keys, and row-level locking.

Example: tutorials_tbl

Let’s create a table in our TUTORIALS database.

CREATE TABLE tutorials_tbl (
    tutorial_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tutorial_title VARCHAR(100) NOT NULL,
    tutorial_author VARCHAR(40) NOT NULL,
    submission_date DATE,
    PRIMARY KEY (tutorial_id)
) ENGINE=InnoDB;

Key attributes used here:

• NOT NULL: A constraint ensuring that a column cannot have a NULL value.
• AUTO_INCREMENT: Automatically generates a unique number when a new record is inserted. Typically used for primary keys.
• PRIMARY KEY: Defines the column(s) that uniquely identify each row.
• UNSIGNED: A numeric type modifier that disallows negative values, effectively doubling the positive range. Good for IDs.

Creating Tables with a PHP Script (PDO)

Use the PDO::exec() method to execute the CREATE TABLE statement.

<?php

// Assumes you have a $pdo connection object from db_connect.php
require 'db_connect.php';

try {
    $sql = "CREATE TABLE tutorials_tbl (
        tutorial_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        tutorial_title VARCHAR(100) NOT NULL,
        tutorial_author VARCHAR(40) NOT NULL,
        submission_date DATE,
        PRIMARY KEY (tutorial_id)
    ) ENGINE=InnoDB;";

    $pdo->exec($sql);

    echo "Table 'tutorials_tbl' created successfully!";

} catch (PDOException $e) {
    // A common error here is "Table already exists"
    die("Could not create table: " . $e->getMessage());
}

?>

MySQL - Drop Tables

Warning: Dropping a table is an irreversible action that permanently deletes the table’s structure and all of its data.

Syntax

DROP TABLE [IF EXISTS] table_name;

Best Practice: Using IF EXISTS prevents an error from being thrown if the table doesn’t exist, which can be useful in scripts.

Dropping Tables from the Command Prompt

mysql> USE TUTORIALS;
Database changed

mysql> DROP TABLE IF EXISTS tutorials_tbl;
Query OK, 0 rows affected (0.01 sec)

Dropping Tables Using a PHP Script (PDO)

<?php

require 'db_connect.php';

try {
    $sql = "DROP TABLE IF EXISTS tutorials_tbl";
    $pdo->exec($sql);
    echo "Table 'tutorials_tbl' deleted successfully!";
} catch (PDOException $e) {
    die("Could not delete table: " . $e->getMessage());
}

?>

MySQL - Insert Query

The INSERT INTO statement is used to add new rows of data to a table.

Syntax

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

You can also insert multiple rows at once:

INSERT INTO table_name (column1, column2, column3)
VALUES
    (value1a, value2a, value3a),
    (value1b, value2b, value3b);

Inserting Data from the Command Prompt

mysql> USE TUTORIALS;

mysql> INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date)
    -> VALUES
    -> ('Learn Modern PHP', 'John Doe', '2023-10-27'),
    -> ('Mastering MySQL 8', 'Jane Smith', NOW());
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

Note that we didn’t provide a value for tutorial_id because it is an AUTO_INCREMENT column. MySQL assigns the value automatically. NOW() is a MySQL function that returns the current date and time.

Inserting Data Securely with a PHP Script (Prepared Statements)

Security Warning: Never insert data by directly concatenating user input into an SQL string. This creates a severe security vulnerability called SQL Injection. Always use prepared statements with placeholders.

A prepared statement is a template for an SQL query. You send the template to the database first, and then you send the data separately. The database engine treats the data as literal values, not as part of the SQL command, making injection impossible.

Example: Inserting Data from a Form

First, an HTML form (add_tutorial.html):

<html>
<head><title>Add New Tutorial</title></head>
<body>
    <h2>Add New Record in MySQL Database</h2>
    <form action="insert.php" method="post">
        <p>
            <label for="title">Tutorial Title:</label>
            <input type="text" name="tutorial_title" id="title" required>
        </p>
        <p>
            <label for="author">Tutorial Author:</label>
            <input type="text" name="tutorial_author" id="author" required>
        </p>
        <p>
            <label for="date">Submission Date:</label>
            <input type="date" name="submission_date" id="date" required>
        </p>
        <input type="submit" name="add" value="Add Tutorial">
    </form>
</body>
</html>

Now, the PHP script (insert.php) that processes the form:

<?php

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    require 'db_connect.php'; // Your PDO connection

    try {
        $sql = "INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) VALUES (?, ?, ?)";

        $stmt = $pdo->prepare($sql);

        // Bind parameters from the POST request and execute
        $stmt->execute([
            $_POST['tutorial_title'],
            $_POST['tutorial_author'],
            $_POST['submission_date']
        ]);

        // Get the ID of the inserted record
        $lastId = $pdo->lastInsertId();

        echo "Data entered successfully. New record has ID: $lastId";

    } catch (PDOException $e) {
        die("Could not enter data: " . $e->getMessage());
    }
}
?>

Here, ? are anonymous placeholders. The execute() method takes an array of values that correspond to these placeholders in order. This is the safe, modern way to handle database inserts.

MySQL - Select Query

The SELECT statement is used to fetch data from one or more tables.

Syntax

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC|DESC]]
[LIMIT count]
[OFFSET start];

• You can specify which columns to return, or use * to select all columns.
• The WHERE clause filters records based on a condition.
• The ORDER BY clause sorts the result set.
• The LIMIT clause restricts the number of rows returned.
• The OFFSET clause specifies where to start returning rows from (useful for pagination).

Fetching Data from a Command Prompt

mysql> SELECT tutorial_id, tutorial_title, tutorial_author FROM tutorials_tbl;
+-------------+--------------------+-----------------+
| tutorial_id | tutorial_title     | tutorial_author |
+-------------+--------------------+-----------------+
|           1 | Learn Modern PHP   | John Doe        |
|           2 | Mastering MySQL 8  | Jane Smith      |
+-------------+--------------------+-----------------+
2 rows in set (0.00 sec)

Fetching Data Using a PHP Script (PDO)

To fetch data with PDO, you prepare your SELECT statement, execute it, and then use a fetch method like fetchAll() to get the results as an array.

Example: Displaying All Tutorials

<?php

require 'db_connect.php';

try {
    $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl ORDER BY tutorial_id DESC';

    $stmt = $pdo->query($sql); // For simple queries without user input, query() is fine.

    // Using PDO::FETCH_ASSOC returns an associative array (column name => value)
    $tutorials = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if ($tutorials) {
        echo '<ul>';
        foreach ($tutorials as $row) {
            echo '<li>';
            echo htmlspecialchars($row['tutorial_title'], ENT_QUOTES, 'UTF-8');
            echo ' by ';
            echo htmlspecialchars($row['tutorial_author'], ENT_QUOTES, 'UTF-8');
            echo ' (ID: ' . $row['tutorial_id'] . ')';
            echo '</li>';
        }
        echo '</ul>';
    } else {
        echo '<p>No tutorials found.</p>';
    }

} catch (PDOException $e) {
    die('Could not get data: ' . $e->getMessage());
}

?>

Security Note: Always escape output with htmlspecialchars() to prevent Cross-Site Scripting (XSS) attacks when displaying user-generated data on a web page.

Fetching a Single Row

If you only expect one result, use the fetch() method instead of fetchAll().

$stmt = $pdo->prepare('SELECT * FROM tutorials_tbl WHERE tutorial_id = ?');
$stmt->execute([1]);
$tutorial = $stmt->fetch(); // Returns a single row or false if not found

MySQL - WHERE Clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

Operator	Description	Example
=	Equal	... WHERE id = 10
<>or!=	Not equal	... WHERE status != 'pending'
>	Greater than	... WHERE price > 99.99
<	Less than	... WHERE stock < 10
>=	Greater than or equal to	... WHERE score >= 80
<=	Less than or equal to	... WHERE attempts <= 3
BETWEEN	Between an inclusive range	... WHERE date BETWEEN '2023-01-01' AND '2023-01-31'
IN	Matches any value in a list	... WHERE country IN ('USA', 'Canada', 'Mexico')
LIKE	Searches for a pattern (see LIKE Clause chapter)	... WHERE name LIKE 'J%'

Fetching Data Using a PHP Script with WHERE

When the WHERE clause condition depends on user input, you must use a prepared statement to stay secure.

Example: Finding tutorials by a specific author

<?php

require 'db_connect.php';

$authorToFind = 'Jane Smith'; // In a real app, this would come from user input, e.g., $_GET['author']

try {
    $sql = 'SELECT tutorial_id, tutorial_title FROM tutorials_tbl WHERE tutorial_author = ?';

    $stmt = $pdo->prepare($sql);
    $stmt->execute([$authorToFind]);

    $results = $stmt->fetchAll();

    if ($results) {
        echo "<h3>Tutorials by $authorToFind:</h3><ul>";
        foreach ($results as $row) {
            echo "<li>" . htmlspecialchars($row['tutorial_title']) . "</li>";
        }
        echo "</ul>";
    } else {
        echo "<p>No tutorials found for author: $authorToFind</p>";
    }

} catch (PDOException $e) {
    die('Query failed: ' . $e->getMessage());
}

?>

By using a placeholder ? for the author’s name, we protect the query from SQL injection, even if a malicious string is provided.

MySQL - UPDATE Query

The UPDATE statement is used to modify existing records in a table.

Syntax

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

Warning: If you omit the WHERE clause, all records in the table will be updated! Always be careful and double-check your WHERE clause.

Updating Data from the Command Prompt

mysql> UPDATE tutorials_tbl
    -> SET tutorial_title = 'Advanced PHP and OOP'
    -> WHERE tutorial_id = 1;

Updating Data Using a PHP Script

Use a prepared statement with placeholders for both the values to be set and the condition in the WHERE clause.

<?php

require 'db_connect.php';

$newTitle = 'Advanced PHP and Object-Oriented Programming';
$tutorialIdToUpdate = 1;

try {
    $sql = "UPDATE tutorials_tbl SET tutorial_title = ? WHERE tutorial_id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$newTitle, $tutorialIdToUpdate]);

    // rowCount() can tell you how many rows were affected
    $affectedRows = $stmt->rowCount();

    echo "Updated data successfully. $affectedRows row(s) were affected.";

} catch (PDOException $e) {
    die('Could not update data: ' . $e->getMessage());
}

?>

MySQL - DELETE Query

The DELETE statement is used to delete existing records from a table.

Syntax

DELETE FROM table_name WHERE condition;

Warning: If you omit the WHERE clause, all records in the table will be deleted! This is a destructive and often catastrophic mistake.

Deleting Data from the Command Prompt

mysql> DELETE FROM tutorials_tbl WHERE tutorial_id = 3;

Deleting Data Using a PHP Script

Always use a prepared statement for DELETE queries that involve user input.

<?php

require 'db_connect.php';

$tutorialIdToDelete = 3;

try {
    $sql = "DELETE FROM tutorials_tbl WHERE tutorial_id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$tutorialIdToDelete]);

    $affectedRows = $stmt->rowCount();

    echo "Deleted data successfully. $affectedRows row(s) were affected.";

} catch (PDOException $e) {
    die('Could not delete data: ' . $e->getMessage());
}

?>

MySQL - LIKE Clause

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It’s essential for implementing search functionality.

There are two wildcards often used in conjunction with the LIKE operator:

• % (The percent sign): Represents zero, one, or multiple characters.
• _ (The underscore): Represents a single character.

Examples

• WHERE name LIKE 'a%': Finds any values that start with “a”.
• WHERE name LIKE '%a': Finds any values that end with “a”.
• WHERE name LIKE '%or%': Finds any values that have “or” in any position.
• WHERE name LIKE '_r%': Finds any values that have “r” in the second position.
• WHERE name LIKE 'a_%': Finds any values that start with “a” and are at least 2 characters in length.

Using LIKE from the Command Prompt

mysql> SELECT * FROM tutorials_tbl WHERE tutorial_title LIKE '%MySQL%';
+-------------+-------------------+-----------------+-----------------+
| tutorial_id | tutorial_title    | tutorial_author | submission_date |
+-------------+-------------------+-----------------+-----------------+
|           2 | Mastering MySQL 8 | Jane Smith      | 2023-10-27      |
+-------------+-------------------+-----------------+-----------------+

Using LIKE in a PHP Script

When using LIKE with prepared statements, you include the wildcard characters (%) in the value that you bind to the placeholder.

<?php

require 'db_connect.php';

$searchTerm = 'PHP'; // From user input

try {
    $sql = "SELECT tutorial_id, tutorial_title FROM tutorials_tbl WHERE tutorial_title LIKE ?";

    $stmt = $pdo->prepare($sql);

    // Add wildcards to the search term before executing
    $stmt->execute(["%$searchTerm%"]);

    $results = $stmt->fetchAll();

    if ($results) {
        echo "<h3>Search results for '$searchTerm':</h3><ul>";
        foreach ($results as $row) {
            echo "<li>" . htmlspecialchars($row['tutorial_title']) . "</li>";
        }
        echo "</ul>";
    } else {
        echo "<p>No results found.</p>";
    }

} catch (PDOException $e) {
    die('Query failed: ' . $e->getMessage());
}

?>

MySQL - Sorting Results

The ORDER BY clause is used to sort the result-set in ascending or descending order. By default, the MySQL server is free to return rows in any order, so if the order is important, you must specify it.

Syntax

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

• ASC (Ascending): Sorts from lowest to highest (A-Z, 0-9). This is the default.
• DESC (Descending): Sorts from highest to lowest (Z-A, 9-0).
• You can sort by multiple columns. The result is sorted by the first column, then by the second for rows with the same value in the first column, and so on.

Using ORDER BY at the Command Prompt

mysql> SELECT tutorial_author, tutorial_title
    -> FROM tutorials_tbl
    -> ORDER BY tutorial_author ASC, submission_date DESC;
+-----------------+---------------------------------------------+
| tutorial_author | tutorial_title                              |
+-----------------+---------------------------------------------+
| Jane Smith      | Mastering MySQL 8                           |
| John Doe        | Advanced PHP and Object-Oriented Programming|
+-----------------+---------------------------------------------+

Using ORDER BY in a PHP Script

The ORDER BY clause is part of the SQL query string itself. If you need to let users choose the sort order, be very careful. Do not directly insert user input into the ORDER BY clause.

Best Practice: Use a whitelist to validate user-supplied sort parameters.

<?php

require 'db_connect.php';

// Whitelist of allowed columns to sort by
$allowedSortColumns = ['tutorial_title', 'tutorial_author', 'submission_date'];
// Whitelist of allowed sort directions
$allowedSortDirections = ['ASC', 'DESC'];

// Get sort parameters from user, with safe defaults
$sortColumn = $_GET['sort_by'] ?? 'submission_date';
$sortDirection = $_GET['direction'] ?? 'DESC';

// Validate user input against the whitelists
if (!in_array($sortColumn, $allowedSortColumns)) {
    $sortColumn = 'submission_date'; // Default to a safe value
}
if (!in_array(strtoupper($sortDirection), $allowedSortDirections)) {
    $sortDirection = 'DESC'; // Default to a safe value
}

try {
    // Build the query safely
    $sql = "SELECT * FROM tutorials_tbl ORDER BY `$sortColumn` $sortDirection";
    $stmt = $pdo->query($sql); // Safe because we validated the inputs
    $results = $stmt->fetchAll();

    // ... display results ...
    echo "<p>Sorting by $sortColumn $sortDirection</p>";
    // ... loop through $results and display them ...

} catch (PDOException $e) {
    die('Query failed: ' . $e->getMessage());
}

?>

Using MySQL Joins

In a relational database, data is often split across multiple tables. A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Best Practice: Use the explicit JOIN syntax (INNER JOIN, LEFT JOIN) instead of the older comma-separated syntax in the FROM clause. It is more readable and less prone to accidental cross joins.

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables. It is the most common type of join.

Example

Let’s assume we have our tutorials_tbl and a new table, authors_tbl, that stores author bios.

-- authors_tbl structure
CREATE TABLE authors_tbl (
    author_name VARCHAR(40) NOT NULL PRIMARY KEY,
    author_bio TEXT
) ENGINE=InnoDB;

-- tutorials_tbl structure (with a foreign key)
CREATE TABLE tutorials_tbl (
    tutorial_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tutorial_title VARCHAR(100) NOT NULL,
    tutorial_author VARCHAR(40) NOT NULL,
    submission_date DATE,
    FOREIGN KEY (tutorial_author) REFERENCES authors_tbl(author_name)
) ENGINE=InnoDB;

To get a list of tutorials along with their author’s bio, we can join the tables:

SELECT
    t.tutorial_title,
    t.submission_date,
    a.author_name,
    a.author_bio
FROM
    tutorials_tbl AS t
INNER JOIN
    authors_tbl AS a ON t.tutorial_author = a.author_name;

Here, AS t and AS a are table aliases, which make the query shorter and more readable.

LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN keyword returns all records from the left table (tutorials_tbl), and the matched records from the right table (authors_tbl). The result is NULL from the right side if there is no match.

This is useful when you want to list all tutorials, even those whose author might not have a bio in the authors_tbl yet.

SELECT
    t.tutorial_title,
    a.author_name,
    a.author_bio -- This will be NULL if the author is not in authors_tbl
FROM
    tutorials_tbl AS t
LEFT JOIN
    authors_tbl AS a ON t.tutorial_author = a.author_name;

Using Joins in a PHP Script

You execute a JOIN query in PHP just like any other SELECT query.

<?php

require 'db_connect.php';

try {
    $sql = "SELECT t.tutorial_title, a.author_name, a.author_bio
            FROM tutorials_tbl AS t
            INNER JOIN authors_tbl AS a ON t.tutorial_author = a.author_name";

    $stmt = $pdo->query($sql);
    $results = $stmt->fetchAll();

    // ... loop through and display the combined results ...

} catch (PDOException $e) {
    die('Query failed: ' . $e->getMessage());
}

?>

Handling MySQL NULL Values

A NULL value in a database table is a special marker used to indicate that a data value does not exist. It is different from an empty string '' or the number 0.

When filtering for NULL values, you cannot use standard comparison operators like = or !=. Instead, you must use the IS NULL or IS NOT NULL operators.

• IS NULL: This operator returns true if the column value is NULL.
• IS NOT NULL: This operator returns true if the column value is not NULL.
• <=> (NULL-safe equal to operator): This operator performs an equality comparison like the = operator but returns 1 (true) rather than NULL if both operands are NULL, and 0 (false) rather than NULL if one operand is NULL.

Using NULL operators at the Command Prompt

Assume tutorials_tbl has a submission_date that can be NULL.

-- This will NOT work as expected
mysql> SELECT * FROM tutorials_tbl WHERE submission_date = NULL;
Empty set (0.00 sec)

-- This is the correct way to find rows with a NULL date
mysql> SELECT * FROM tutorials_tbl WHERE submission_date IS NULL;

-- This is the correct way to find rows that have a date
mysql> SELECT * FROM tutorials_tbl WHERE submission_date IS NOT NULL;

Handling NULL Values in a PHP Script

You can check for NULL in your SQL query. If you need to insert a NULL value using PDO, simply pass the PHP null value.

Example: Inserting a NULL value

<?php

require 'db_connect.php';

try {
    // Let's say the submission date is optional
    $sql = "INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) VALUES (?, ?, ?)";
    $stmt = $pdo->prepare($sql);

    // Pass the PHP null type to insert a SQL NULL
    $stmt->execute(['New Draft Tutorial', 'Unassigned', null]);

    echo "Inserted a record with a NULL date.";

} catch (PDOException $e) {
    die('Insert failed: ' . $e->getMessage());
}

?>

MySQL - Regular Expressions

In addition to simple pattern matching with LIKE, MySQL supports powerful pattern matching with regular expressions using the REGEXP (or RLIKE) operator.

The following table lists some of the most common metacharacters used in MySQL regular expressions:

Pattern	What the pattern matches
^	Beginning of the string
$	End of the string
.	Any single character
[...]	Any character listed between the square brackets
[^...]	Any character not listed between the square brackets
p1|p2|p3	Alternation; matches any of the patterns p1, p2, or p3
*	Zero or more instances of the preceding element
+	One or more instances of the preceding element
{n}	n instances of the preceding element
{m,n}	m through n instances of the preceding element

Examples

Based on the table above, you can construct various complex queries.

Find all author names starting with ‘J’:

mysql> SELECT tutorial_author FROM tutorials_tbl WHERE tutorial_author REGEXP '^J';

Find all author names ending with ‘th’:

mysql> SELECT tutorial_author FROM tutorials_tbl WHERE tutorial_author REGEXP 'th$';

Find all author names containing ‘oh’ or ‘an’:

mysql> SELECT tutorial_author FROM tutorials_tbl WHERE tutorial_author REGEXP 'oh|an';

Find all author names that are exactly 4 characters long:

mysql> SELECT tutorial_author FROM tutorials_tbl WHERE tutorial_author REGEXP '^.{4}$';

MySQL - Transactions

A transaction is a sequence of operations performed as a single logical unit of work. All operations in a transaction must complete successfully. If any operation fails, the entire transaction is rolled back, and the database is left in the state it was in before the transaction began. This ensures data integrity.

ACID Properties of Transactions

Transactions are defined by four standard properties, known by the acronym ACID:

• Atomicity: Ensures that all operations within the work unit are completed successfully. If not, the transaction is aborted at the point of failure, and all previous operations are rolled back to their former state.
• Consistency: Ensures that the database remains in a consistent state before and after the transaction. Any data written to the database must be valid according to all defined rules, including constraints, cascades, and triggers.
• Isolation: Ensures that concurrent transactions produce the same result as if they were executed sequentially. One transaction’s intermediate state is hidden from other transactions.
• Durability: Ensures that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors.

Transaction Control in SQL

You control transactions with three main commands:

• START TRANSACTION (or BEGIN): Starts a new transaction.
• COMMIT: Saves all changes made in the transaction to the database, making them permanent.
• ROLLBACK: Discards all changes made in the transaction, reverting the database to its state before the transaction began.

For transactions to work, your tables must use a transaction-safe storage engine. InnoDB is the default and most widely used engine for this purpose in MySQL.

Using Transactions in a PHP Script (PDO)

PDO provides excellent, intuitive methods for controlling transactions.

Example: Transferring Funds

Imagine transferring $100 from account A to account B. This requires two UPDATE operations that must both succeed or both fail.

<?php

require 'db_connect.php';

// Account IDs and amount to transfer
$fromAccountId = 1;
$toAccountId = 2;
$amount = 100.00;

try {
    // 1. Start the transaction
    $pdo->beginTransaction();

    // 2. Debit from the first account
    $stmt1 = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
    $stmt1->execute([$amount, $fromAccountId]);

    // 3. Credit the second account
    $stmt2 = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
    $stmt2->execute([$amount, $toAccountId]);

    // 4. If we get here, both queries were successful. Commit the transaction.
    $pdo->commit();
    echo "Transfer successful!";

} catch (PDOException $e) {
    // 5. If any query fails, an exception is thrown. Roll back the transaction.
    $pdo->rollBack();
    die("Transfer failed: " . $e->getMessage());
}

?>

MySQL - ALTER Command

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. It can also be used to add and drop various constraints on an existing table.

ADD a Column

To add a new column, use ADD COLUMN. You can specify its position with FIRST or AFTER another_column.

-- Add a new column 'status' to the end of the table
ALTER TABLE tutorials_tbl ADD COLUMN status ENUM('draft', 'published', 'archived') NOT NULL DEFAULT 'draft';

-- Add a column 'views' after 'tutorial_title'
ALTER TABLE tutorials_tbl ADD COLUMN views INT UNSIGNED NOT NULL DEFAULT 0 AFTER tutorial_title;

DROP a Column

To delete an existing column, use DROP COLUMN.

ALTER TABLE tutorials_tbl DROP COLUMN views;

MODIFY or CHANGE a Column

To change the data type or constraints of a column, use MODIFY COLUMN.

-- Change the tutorial_author column to be wider
ALTER TABLE tutorials_tbl MODIFY COLUMN tutorial_author VARCHAR(50) NOT NULL;

To both rename and change the definition of a column, use CHANGE COLUMN.

-- Rename 'submission_date' to 'publish_date' and change its type
ALTER TABLE tutorials_tbl CHANGE COLUMN submission_date publish_date DATETIME;

RENAME a Table

To rename an entire table, use RENAME TO.

ALTER TABLE tutorials_tbl RENAME TO articles_tbl;

MySQL - INDEXES

A database index is a data structure that improves the speed of data retrieval operations on a table. Indexes are used by the database search engine to find records much faster, similar to using the index in a book. Without an index, the database must scan every row in the table (a “full table scan”) to find the requested data.

While indexes speed up SELECT queries and WHERE clauses, they slow down data modification operations like INSERT, UPDATE, and DELETE because the indexes must also be updated. Therefore, you should only create indexes on columns that are frequently used in search conditions.

Creating an Index

You can create an index using the CREATE INDEX statement.

-- Create a simple index on the tutorial_author column
CREATE INDEX idx_author ON tutorials_tbl (tutorial_author);

-- Create a unique index, which prevents duplicate values in the column
CREATE UNIQUE INDEX idx_unique_title ON tutorials_tbl (tutorial_title);

Adding an Index with ALTER TABLE

You can also add an index to an existing table using ALTER TABLE.

-- Add a multi-column index
ALTER TABLE tutorials_tbl ADD INDEX idx_author_date (tutorial_author, submission_date);

The PRIMARY KEY

A PRIMARY KEY is a special kind of unique index. Every table should have a primary key, as it uniquely identifies each row and is typically used for JOIN operations. A primary key column cannot contain NULL values.

-- Add a PRIMARY KEY to an existing table (column must be NOT NULL)
ALTER TABLE tutorials_tbl ADD PRIMARY KEY (tutorial_id);

Dropping an Index

To remove an index, use the DROP INDEX statement.

DROP INDEX idx_author ON tutorials_tbl;

Displaying Index Information

Use SHOW INDEX to see all indexes on a table.

mysql> SHOW INDEX FROM tutorials_tbl;
-- Or for better formatting:
mysql> SHOW INDEX FROM tutorials_tbl\G

MySQL - Temporary Tables

Temporary tables are a special feature in MySQL that allows you to store a temporary result set, which you can use for further processing within a single session.

A key characteristic of a temporary table is that it is visible only to the current database session and is automatically dropped when the session ends. This makes them very useful for complex reports or multi-step data transformations without cluttering the main database schema.

Example

Let’s create a temporary table to hold a summary of sales data.

mysql> CREATE TEMPORARY TABLE SalesSummary (
    ->     product_name VARCHAR(50) NOT NULL,
    ->     total_sales DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    ->     total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary VALUES ('SuperWidget', 5000.75, 100);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+--------------------+
| product_name | total_sales | total_units_sold   |
+--------------+-------------+--------------------+
| SuperWidget  |     5000.75 |                100 |
+--------------+-------------+--------------------+

If you issue a SHOW TABLES command, the temporary table will not be listed. If you start a new connection session, the SalesSummary table will no longer exist.

Dropping Temporary Tables

Although temporary tables are dropped automatically when the session ends, you can explicitly drop them using the standard DROP TABLE command. This is good practice to free up resources as soon as the table is no longer needed.

mysql> DROP TABLE SalesSummary;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM SalesSummary;
ERROR 1146 (42S02): Table 'TUTORIALS.SalesSummary' doesn't exist

MySQL - Clone Tables

Sometimes you need to create an exact copy of a table, including its structure, indexes, and default values. This is useful for creating backups, or for creating a test table to experiment with.

Method 1: Two-Step Clone

This method creates an empty table with the identical structure, then copies the data.

Step 1: Create an empty clone of the structure

CREATE TABLE tutorials_tbl_clone LIKE tutorials_tbl;

Step 2: Copy the data from the original table

INSERT INTO tutorials_tbl_clone SELECT * FROM tutorials_tbl;

This approach is clean, readable, and generally recommended.

Method 2: One-Step Clone (Structure Only)

You can create a new table and define its structure based on a SELECT query. However, this method does not copy indexes or AUTO_INCREMENT properties.

CREATE TABLE tutorials_tbl_backup AS SELECT * FROM tutorials_tbl;

This is quick for creating simple backups of data but is not a true structural clone.

Method 3: Manual Clone using SHOW CREATE TABLE

For full control, you can get the CREATE TABLE statement for the original table, modify it, and run it.

Step 1: Get the table’s creation statement

mysql> SHOW CREATE TABLE tutorials_tbl\G
*************************** 1. row ***************************
       Table: tutorials_tbl
Create Table: CREATE TABLE `tutorials_tbl` (
  `tutorial_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tutorial_title` varchar(100) NOT NULL,
  `tutorial_author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL,
  PRIMARY KEY (`tutorial_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
...

Step 2: Modify and execute the statement

Copy the output, change the table name from tutorials_tbl to your desired clone name (e.g., clone_tbl), and execute the modified CREATE TABLE query. Then, if needed, copy the data using the INSERT INTO ... SELECT statement from Method 1.

MySQL - Database Info

Obtaining and Using MySQL Metadata

Metadata is data about data. In MySQL, this can be:

• Query Result Information: How many rows were affected by a query?
• Schema Information: What databases, tables, and columns exist?
• Server Information: What version is the server? What are its configuration settings?

Getting Query Result Information (PHP/PDO)

Number of Rows Affected by UPDATE, DELETE, INSERT

After executing a prepared statement for UPDATE, DELETE, or INSERT, you can use the PDOStatement::rowCount() method.

$stmt = $pdo->prepare("DELETE FROM tutorials_tbl WHERE tutorial_id = ?");
$stmt->execute([10]);
$affectedRows = $stmt->rowCount();
echo "$affectedRows rows were deleted.";

Getting the ID of the Last Inserted Row

When you insert a row into a table with an AUTO_INCREMENT primary key, you can get the new ID using the PDO::lastInsertId() method.

$stmt = $pdo->prepare("INSERT INTO tutorials_tbl (tutorial_title, ...) VALUES (?, ...)");
$stmt->execute(['New Tutorial', ...]);
$newId = $pdo->lastInsertId();
echo "The new tutorial was inserted with ID: $newId";

Getting Schema Information

You can get lists of databases and tables by executing SHOW commands.

<?php
require 'db_connect.php';

// Get a list of all databases
$stmt = $pdo->query('SHOW DATABASES');
$databases = $stmt->fetchAll(PDO::FETCH_COLUMN);
print_r($databases);

// Get a list of tables in the current database
$stmt = $pdo->query('SHOW TABLES');
$tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
print_r($tables);
?>

Getting Server Metadata

You can execute simple queries to get information about the server itself.

Sr.No.	Command & Description
1	SELECT VERSION(): Returns the MySQL server version string.
2	SELECT DATABASE(): Returns the name of the current default database.
3	SELECT USER(): Returns the username and host of the current user.
4	SHOW STATUS: Provides a large list of server status indicators (e.g., uptime, total queries).
5	SHOW VARIABLES: Shows the values of server configuration variables.

<?php
require 'db_connect.php';

$version = $pdo->query('SELECT version()')->fetchColumn();
echo "Server Version: $version";
?>

Using MySQL Sequences

A sequence is a set of integers (1, 2, 3, …) that are generated in order on demand. Sequences are essential in databases because many applications require each row in a table to have a unique value, and sequences provide an easy way to generate them for primary keys.

Using an AUTO_INCREMENT Column

The simplest and most common way to create a sequence in MySQL is to define a column with the AUTO_INCREMENT attribute. MySQL will then automatically handle generating a new, unique value for that column for each new row inserted.

Example

mysql> CREATE TABLE insects (
    ->     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(30) NOT NULL,
    ->     origin VARCHAR(30) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO insects (name, origin) VALUES
    ->     ('housefly', 'kitchen'),
    ->     ('millipede', 'driveway');
Query OK, 2 rows affected (0.01 sec)

mysql> SELECT * FROM insects;
+----+-----------+----------+
| id | name      | origin   |
+----+-----------+----------+
|  1 | housefly  | kitchen  |
|  2 | millipede | driveway |
+----+-----------+----------+

Notice that we did not specify the id in our INSERT statement; MySQL generated it for us.

Obtaining AUTO_INCREMENT Values in PHP

After an INSERT query, you often need to know the ID of the new row you just created. Use the PDO::lastInsertId() method to retrieve it.

<?php
require 'db_connect.php';

$stmt = $pdo->prepare("INSERT INTO insects (name, origin) VALUES (?, ?)");
$stmt->execute(['grasshopper', 'front yard']);

$newInsectId = $pdo->lastInsertId();

echo "Successfully inserted new insect with ID: $newInsectId"; // Will likely output "3"

?>

Resequencing an Existing Table

Warning: This is a dangerous operation and should be avoided if other tables have foreign keys that reference the AUTO_INCREMENT column you are about to change. It can break your data’s referential integrity.

If you have deleted many rows and want to close the gaps in the sequence, the only way is to drop the column and add it again.

mysql> ALTER TABLE insects DROP id;
mysql> ALTER TABLE insects ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);

Starting a Sequence at a Specific Value

By default, AUTO_INCREMENT sequences start at 1. You can set a different starting value using ALTER TABLE.

-- Set the next auto-incremented value to be 100
mysql> ALTER TABLE insects AUTO_INCREMENT = 100;

MySQL - Handling Duplicates

Duplicate records in a table can lead to data inconsistency and incorrect analytics. It’s important to both prevent duplicates from being created and to clean up any that already exist.

Preventing Duplicates with Constraints

The best way to prevent duplicates is to enforce uniqueness at the database level using constraints.

• PRIMARY KEY: A primary key on one or more columns ensures that the combination of values in those columns is unique across the entire table.
• UNIQUE Index: A unique index provides the same uniqueness guarantee as a primary key, but a table can have multiple unique indexes. Unlike a primary key, a unique index can allow multiple NULL values.

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL UNIQUE, -- Usernames must be unique
    email VARCHAR(100) NOT NULL UNIQUE -- Emails must also be unique
);

If you try to insert a record that violates a PRIMARY KEY or UNIQUE constraint, MySQL will return an error.

Handling Duplicate Errors on INSERT

When an insert might violate a unique constraint, you have options besides just letting it fail.

• INSERT IGNORE: If a record doesn’t duplicate an existing record, MySQL inserts it. If it is a duplicate, MySQL silently discards the new record without generating an error.
• REPLACE INTO: If the record is new, it’s inserted. If it’s a duplicate (based on a PRIMARY or UNIQUE key), the new record replaces the old one.
• INSERT ... ON DUPLICATE KEY UPDATE: This is the most flexible option. If the record is new, it’s inserted. If it’s a duplicate, an UPDATE statement is performed instead.

-- Example of ON DUPLICATE KEY UPDATE: Insert a new user, or update their last_login time if they exist.
INSERT INTO users (username, email, last_login)
VALUES ('johndoe', 'john.doe@example.com', NOW())
ON DUPLICATE KEY UPDATE last_login = NOW();

Finding Existing Duplicates

To find duplicate rows based on certain columns, you can use GROUP BY and HAVING.

-- Find all emails that are used by more than one user
SELECT email, COUNT(*) AS repetitions
FROM users
GROUP BY email
HAVING repetitions > 1;

Removing Existing Duplicates

Removing duplicates from a live table can be tricky. One safe method is to create a new table with the unique rows and then replace the old table.

-- Step 1: Create a temporary table with the same structure
CREATE TABLE users_unique LIKE users;

-- Step 2: Add a unique index to the new table on the columns that define a duplicate
ALTER TABLE users_unique ADD UNIQUE INDEX (email);

-- Step 3: Copy the data, ignoring duplicates
INSERT IGNORE INTO users_unique SELECT * FROM users;

-- Step 4: Rename the tables (do this in a transaction if possible)
RENAME TABLE users TO users_old, users_unique TO users;

MySQL and SQL Injection

SQL Injection is one of the most common and most dangerous web application vulnerabilities. It occurs when an attacker is able to manipulate an application’s SQL queries by inserting malicious SQL code into user input.

The Vulnerability: Unsanitized Input

Consider this vulnerable PHP code, which was common in the past:

// DO NOT USE THIS CODE! IT IS VULNERABLE.
$userId = $_GET['id'];
$sql = "SELECT * FROM users WHERE id = $userId";
$result = $pdo->query($sql);

A legitimate user might provide an ID like 123. The query becomes SELECT * FROM users WHERE id = 123.

However, an attacker could provide input like 123; DROP TABLE users;. The query becomes SELECT * FROM users WHERE id = 123; DROP TABLE users;. While modern PDO drivers often prevent multiple queries (query stacking) by default, other forms of injection are still possible, such as 123 OR 1=1, which would return all users.

The Solution: Prepared Statements

The number one rule to prevent SQL injection is: never trust user input and always use prepared statements with placeholders.

A prepared statement separates the SQL command from the data. The database compiles the command template first, and then receives the data as parameters. This makes it impossible for the data to be interpreted as an SQL command.

The Secure Way to Write the Query

// THIS IS THE SECURE, CORRECT WAY

// Get user input
$userId = $_GET['id'];

// 1. Prepare the SQL statement with a placeholder (?)
$sql = "SELECT * FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);

// 2. Execute the statement, binding the user input to the placeholder
$stmt->execute([$userId]);

// 3. Fetch the results
$user = $stmt->fetch();

In this secure version, even if an attacker provides 123; DROP TABLE users;, the database treats that entire string as a single value it’s trying to find for the id column. It will simply find no match and the malicious command will never be executed.

Deprecated Practice: In the past, developers used functions like mysql_real_escape_string() to manually “escape” special characters. This approach is error-prone and is considered obsolete. Always prefer prepared statements.

MySQL - Database Export

Exporting your database is a critical task for creating backups or migrating data to another server. The primary tool for this is the mysqldump command-line utility.

Exporting Data with mysqldump

mysqldump generates a file containing a set of SQL statements that can be executed to recreate the original database objects and data.

Exporting a Single Database

# Syntax: mysqldump -u [username] -p [database_name] > [filename.sql]
$ mysqldump -u root -p TUTORIALS > tutorials_backup.sql

Exporting a Specific Table

# Syntax: mysqldump -u [username] -p [database_name] [table_name] > [filename.sql]
$ mysqldump -u root -p TUTORIALS tutorials_tbl > single_table_backup.sql

Exporting All Databases

$ mysqldump -u root -p --all-databases > all_databases_backup.sql

Best Practice for InnoDB: When backing up InnoDB tables, use the --single-transaction flag. This creates a consistent snapshot of the database at the time the dump started, without locking the tables, allowing your application to continue running without interruption.

$ mysqldump -u root -p --single-transaction TUTORIALS > consistent_backup.sql

Exporting Data with SELECT ... INTO OUTFILE

This SQL statement exports the result of a query directly to a file on the server’s local filesystem. It’s very fast and useful for generating CSV or other text-based data files.

Note: This command has security implications. The user executing the command must have the FILE privilege, and the output file cannot already exist. The file is written by the MySQL server process, so it will be owned by the mysql system user.

mysql> SELECT * FROM tutorials_tbl
    -> INTO OUTFILE '/var/lib/mysql-files/tutorials.csv'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';

The default location for INTO OUTFILE is often restricted by the secure_file_priv system variable for security reasons. You may need to check this variable’s value (SHOW VARIABLES LIKE 'secure_file_priv';) to see which directory is permitted.

MySQL - Database Import

Importing data is the process of loading data into your database, typically from a backup file created by mysqldump or a data file like a CSV.

Importing from a mysqldump File

The most common way to restore a database is by using the mysql command-line client to execute the SQL script created by mysqldump.

Step 1: Create the database (if it doesn’t exist)

The dump file usually does not contain the CREATE DATABASE statement, so you need to create it first.

mysql> CREATE DATABASE TUTORIALS;

Step 2: Import the data

Redirect the backup file into the mysql client.

# Syntax: mysql -u [username] -p [database_name] < [filename.sql]
$ mysql -u root -p TUTORIALS < tutorials_backup.sql

This will execute all the SQL statements in the file, recreating the tables and inserting the data.

Importing Data with LOAD DATA INFILE

LOAD DATA INFILE is the SQL command for bulk-loading data from a text file (like a CSV) into a table. It is extremely fast.

mysql> LOAD DATA INFILE '/var/lib/mysql-files/tutorials.csv'
    -> INTO TABLE tutorials_tbl
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 ROWS; -- Use this to skip a header row in the CSV file

The LOCAL keyword (LOAD DATA LOCAL INFILE) can be used to load a file from the client machine’s filesystem instead of the server’s. However, this is often disabled by default on both the server and client for security reasons, as it could allow a malicious server to request any file from the client’s machine.