MySQL Tutorial

MySQL is an open-source relational database management system (RDBMS). It is the most popular database system used with PHP and the web.

What is MySQL?

MySQL is a database system used on the web.
MySQL is an RDBMS that runs on a server.
MySQL is ideal for both small and large applications.
MySQL is very fast, reliable, and easy to use.
MySQL compiles on a number of platforms.

Installation Basics

MySQL can be installed on Windows, macOS, and Linux.

Using XAMPP or WAMP (Easiest for Beginners)

For local development, tools like XAMPP, WAMP, or MAMP install MySQL alongside Apache and PHP instantly.

Command Line Tool

Once installed, you can access MySQL via the command line:

mysql -u root -p

Basic Syntax Rules

MySQL syntax is similar to standard SQL with a few unique quirks.

SQL keywords are NOT case sensitive (select is the same as SELECT), but it's standard practice to capitalize them.
Table and database names MAY be case sensitive depending on the operating system (e.g., Linux vs Windows).
A semicolon (;) is required at the end of each SQL statement.

CREATE Database

The CREATE DATABASE statement is used to create a new MySQL database.

CREATE DATABASE testdb;

Show all Databases

SHOW DATABASES;

DROP Database

The DROP DATABASE statement is used to drop an existing SQL database.

Warning! Dropping a database deletes all the tables and data inside it permanently.

DROP DATABASE testdb;

SELECT Database

Before you can create tables or manipulate data, you must "select" the database you want to use.

USE Statement

USE testdb;

CREATE Table

The CREATE TABLE statement creates a new table in a database.

MySQL Auto-Increment

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.

CREATE TABLE Users (
    UserID int NOT NULL AUTO_INCREMENT,
    Username varchar(255) NOT NULL,
    Email varchar(255),
    PRIMARY KEY (UserID)
);

Show Tables

SHOW TABLES;
DESCRIBE Users; -- Views the structure of the table

MySQL Data Types

Data types dictate what kind of data can be stored in a column.

Type Category	Examples
String Types	`VARCHAR`, `CHAR`, `TEXT`, `ENUM`
Numeric Types	`INT`, `TINYINT`, `FLOAT`, `DECIMAL`
Date & Time Types	`DATE`, `DATETIME`, `TIMESTAMP`, `TIME`
JSON	`JSON` (MySQL 5.7+ supports native JSON data type)

ALTER Table

Modify an existing table's structure.

Add Column

ALTER TABLE Users ADD Age int;

Modify Column Type

ALTER TABLE Users MODIFY COLUMN Age varchar(3);

Rename Column

ALTER TABLE Users CHANGE Age UserAge int;

DROP & TRUNCATE Table

Deleting tables or emptying their contents.

DROP TABLE

Deletes the table entirely.

DROP TABLE Users;

TRUNCATE TABLE

Deletes all data in the table, but keeps the table structure. Resets AUTO_INCREMENT counters.

TRUNCATE TABLE Users;

INSERT INTO Statement

Adds new rows of data into a table.

Inserting Single Row

INSERT INTO Users (Username, Email) 
VALUES ('JohnDoe', 'john@example.com');

Inserting Multiple Rows (MySQL specific efficiency)

INSERT INTO Users (Username, Email) VALUES 
('JaneDoe', 'jane@example.com'),
('Mike', 'mike@example.com'),
('Sarah', 'sarah@example.com');

UPDATE Statement

Modifies existing records.

Always use the WHERE clause, otherwise all rows will be updated!

UPDATE Users 
SET Email = 'newjohn@example.com' 
WHERE Username = 'JohnDoe';

DELETE Statement

Deletes existing records.

DELETE FROM Users WHERE Username = 'Mike';

REPLACE Statement

A MySQL extension to standard SQL. It works like INSERT, but if an old row has the same primary key or unique index as the new row, the old row is deleted before the new row is inserted.

REPLACE INTO Users (UserID, Username, Email) 
VALUES (1, 'JohnUpdated', 'updated@example.com');

SELECT & DISTINCT

Fetches data from a database.

Select All

SELECT * FROM Users;

Select Specific Columns

SELECT Username, Email FROM Users;

Select Distinct

Returns only unique values.

SELECT DISTINCT Email FROM Users;

WHERE Clause

Filters records based on specific conditions.

SELECT * FROM Users WHERE UserID > 5;

ORDER BY

Sorts the result-set.

Ascending (Default)

SELECT * FROM Users ORDER BY Username ASC;

Descending

SELECT * FROM Users ORDER BY Username DESC;

LIMIT & OFFSET

MySQL uses the LIMIT clause to constrain the number of rows returned. This is essential for pagination.

Return only 5 records

SELECT * FROM Users LIMIT 5;

Pagination using OFFSET

Skip the first 10 records, and return the next 5.

SELECT * FROM Users LIMIT 5 OFFSET 10;
-- Alternatively: LIMIT 10, 5;

AND, OR, NOT, IN

Logical operators used inside the WHERE clause.

SELECT * FROM Users WHERE Age > 18 AND Age < 30;

SELECT * FROM Users WHERE Username IN ('John', 'Jane', 'Mike');

LIKE & Wildcards

Used in a WHERE clause to search for a specified pattern in a column.

%: represents zero, one, or multiple characters.
_: represents a single character.

SELECT * FROM Users WHERE Email LIKE '%@gmail.com';

INNER JOIN

Combines rows from two or more tables, based on a related column between them.

INNER JOIN returns records that have matching values in both tables.

SELECT Orders.OrderID, Users.Username
FROM Orders
INNER JOIN Users ON Orders.UserID = Users.UserID;

LEFT & RIGHT JOIN

Outer joins include unmatched records from one side of the join.

LEFT JOIN

Returns all records from the left table, and the matched records from the right table.

SELECT Users.Username, Orders.OrderID
FROM Users
LEFT JOIN Orders ON Users.UserID = Orders.UserID;

RIGHT JOIN

Returns all records from the right table, and the matched records from the left table.

String Functions

MySQL provides numerous functions to manipulate strings.

CONCAT(a, b): Adds two or more strings together.
LOWER() / UPPER(): Converts case.
SUBSTRING(string, start, length): Extracts a substring.

SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Users;

Numeric Functions

Mathematical functions built into MySQL.

ROUND(value, decimals): Rounds a number.
CEIL() / FLOOR(): Rounds up or down to the nearest integer.
RAND(): Generates a random number between 0 and 1.

SELECT ROUND(Price, 2) FROM Products;

Date Functions

MySQL comes with powerful Date and Time parsing functions.

NOW(): Returns the current date and time.
CURDATE(): Returns the current date.
DATEDIFF(date1, date2): Returns the difference between two dates.
DATE_FORMAT(date, format): Formats dates.

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS FormattedDate;

User Management

Creating users and granting privileges in MySQL.

Create User

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Grant Privileges

GRANT ALL PRIVILEGES ON testdb.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Indexes

Indexes are used to find rows with specific column values quickly.

Without an index, MySQL must begin with the first row and then read through the entire table.

CREATE INDEX idx_username ON Users (Username);

Drop Index

ALTER TABLE Users DROP INDEX idx_username;

Import & Export Data

MySQL provides command-line tools to dump (export) and import databases.

Exporting (Dumping) a Database using mysqldump

mysqldump -u root -p testdb > backup.sql

Importing a Database

First create the empty database, then import the file:

mysql -u root -p testdb < backup.sql