PHP: MySQL Database Basics

All the data from the previous 27 lessons lived in memory—gone the moment you refresh. A database is a permanent home for your data. This lesson teaches you what a database is, how to design tables, and how to create your first database in MySQL.

1. What Is a Database?

A database = an organized electronic filing cabinet system. It's far better than text files at: fast lookups, concurrent reads and writes, and data integrity.

TEXT 📖 Display only

Database Server (e.g., MySQL)
 ├── Database "blog"          ← CREATE DATABASE blog;
 │    ├── Table "users"       ← CREATE TABLE users (...)
 │    │    ├── Row: User 1    ← INSERT INTO users ...
 │    │    ├── Row: User 2
 │    │    └── Row: User 3
 │    ├── Table "posts"
 │    └── Table "comments"
 └── Database "shop"

Term	Analogy	Description
Database	Excel Workbook	A project's data container
Table	Worksheet/Sheet	Stores one type of data (users table, posts table)
Column	Column A, B, C...	A field (name, age)
Row	A row of data	One record (John, 25)
Primary Key (PK)	Row number	Uniquely identifies each row

2. MySQL Installation and Connection

(1) Windows with XAMPP

XAMPP bundles MySQL + phpMyAdmin—one-click installation:

Download from https://www.apachefriends.org → Install → Start Apache and MySQL
Visit http://localhost/phpmyadmin

(2) Connecting to MySQL

BASH

# Command-line connection
mysql -u root -p
# XAMPP's root has no password by default—just press Enter

SQL

-- List existing databases
SHOW DATABASES;

-- For security, set a root password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_password';

3. Creating Databases and Tables

▶ Example: Creating a Database

SQL

-- Create a database
CREATE DATABASE myblog;

-- Select the database
USE myblog;

-- Check the current database
SELECT DATABASE();

-- Delete a database (use with caution!)
-- DROP DATABASE myblog;

▶ Try it Yourself

Output:

TEXT 📖 Display only

Output displayed

▶ Example: Creating a Users Table

Output:

TEXT 📖 Display only

Query OK, 0 rows affected

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+

SQL

-- Create the users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    age INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- View table structure
DESCRIBE users;
-- Or
SHOW COLUMNS FROM users;

Output:

TEXT 📖 Display only

Output displayed

Clause	Description
`AUTO_INCREMENT`	Auto-increment—automatically +1 with each new row
`PRIMARY KEY`	Primary key—uniquely identifies each row
`NOT NULL`	Required—cannot be empty
`UNIQUE`	Unique—cannot have duplicates
`DEFAULT`	Default value
`CURRENT_TIMESTAMP`	Current timestamp

4. Common MySQL Data Types

Type	Purpose	Example
`INT`	Integer	`age INT` → 25
`BIGINT`	Large integer	`view_count BIGINT` → 1000000
`FLOAT / DOUBLE`	Floating point	`price FLOAT` → 19.99
`DECIMAL(M,D)`	Precise decimal (money)	`price DECIMAL(10,2)` → 19.99
`VARCHAR(N)`	Variable-length string	`name VARCHAR(50)`
`TEXT`	Long text	`content TEXT`
`DATE`	Date	`birthday DATE` → 2026-06-29
`DATETIME`	Date and time	`created_at DATETIME`
`TIMESTAMP`	Timestamp	`updated_at TIMESTAMP`
`BOOLEAN`	Boolean (TINYINT(1))	`is_active BOOLEAN` → 1/0
`ENUM`	Enumerated (fixed options)	`status ENUM('active','banned')`
`JSON`	JSON data	`metadata JSON`

💡 Tip: Always use DECIMAL for monetary values, never FLOAT—floating-point numbers have precision errors that can cost you cents.

5. Basic CRUD Operations

Operation	SQL Statement	Description
Create	`INSERT INTO table VALUES (...)`	Add new rows
Read	`SELECT * FROM table WHERE ...`	Query data
Update	`UPDATE table SET col=val WHERE ...`	Modify data
Delete	`DELETE FROM table WHERE ...`	Delete data

▶ Example: Basic CRUD

Output:

TEXT 📖 Display only

Query OK, 1 row affected

---+---------+------------------
id | name    | email            
---+---------+------------------
1  | Alice   | alice@email.com  
2  | Bob     | bob@email.com    
3  | Charlie | charlie@email.com
---+---------+------------------
3 rows in set

---------+----------------
username | email          
---------+----------------
Alice    | alice@email.com
Bob      | bob@email.com  
---------+----------------
2 rows in set

+----------+
| COUNT(*) |
+----------+
| 5        |
+----------+
1 row in set

---+---------+------------------
id | name    | email            
---+---------+------------------
1  | Alice   | alice@email.com  
2  | Bob     | bob@email.com    
3  | Charlie | charlie@email.com
---+---------+------------------
3 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected

SQL

-- Insert data
INSERT INTO users (username, email, password, age) VALUES
('John', 'john@example.com', 'hashed_pw_1', 25),
('Jane', 'jane@example.com', 'hashed_pw_2', 22),
('Bob', 'bob@example.com', 'hashed_pw_3', 28);

-- Query
SELECT * FROM users;
SELECT username, email FROM users WHERE age > 23;
SELECT COUNT(*) FROM users;                    -- Count rows
SELECT * FROM users ORDER BY age DESC;         -- Sort by age descending
SELECT * FROM users LIMIT 2;                   -- Only the first 2 records

-- Update
UPDATE users SET age = 26 WHERE username = 'John';
-- ⚠️ Forgetting WHERE updates ALL rows!

-- Delete
DELETE FROM users WHERE id = 3;
-- ⚠️ Forgetting WHERE deletes ALL rows!

Output:

TEXT 📖 Display only

Output displayed

🔥 Common Mistake: UPDATE and DELETE without a WHERE clause affect all rows! This is the "nuclear button" of database operations—always double-check your WHERE condition before execution.

6. Working with phpMyAdmin

phpMyAdmin is a graphical tool for managing MySQL, perfect for newcomers who aren't comfortable with the command line:

TEXT 📖 Display only

http://localhost/phpmyadmin

Steps:
1. Click "New" on the left → enter database name myblog → Create
2. Click the myblog database → "SQL" tab
3. Paste your CREATE TABLE statement → Execute
4. Click the users table → "Insert" tab → fill out the form to insert data
5. "Browse" tab → view your data
6. "Structure" tab → view/modify column definitions

💡 Tip: It's fine to use phpMyAdmin for visual operations as a beginner, but upcoming lessons (PDO) will teach you how to operate databases from PHP code—that's how real web applications work.

7. Creating a Posts Table

SQL

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    status ENUM('draft', 'published') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

💡 Tip: FOREIGN KEY (user_id) REFERENCES users(id) ensures that the author of every post is an existing user. ON DELETE CASCADE means deleting a user automatically deletes all their posts.

❓ FAQ

Q What's the relationship between MySQL and SQL?

A SQL is the language (Structured Query Language). MySQL is a database management system (one of many software products that implement SQL). Just like JavaScript is the language and Chrome is the browser that runs it.

Q How do I choose between VARCHAR and TEXT?

A Use VARCHAR for short text (≤255 characters). Use TEXT for long text. VARCHAR can have default values; TEXT cannot. Use VARCHAR for usernames, emails, and titles. Use TEXT for article content.

Q What number does AUTO_INCREMENT start from?

A By default, it starts at 1 and increments by 1. If you delete the last row and INSERT again, the next ID will be the previous max ID + 1 (gaps are not filled).

📖 Summary

Database = electronic filing cabinet system (persistent storage + fast lookups + concurrent safety)
CREATE DATABASE name; → USE name; → CREATE TABLE (...)
Common types: INT/VARCHAR/TEXT/DECIMAL/DATETIME/BOOLEAN
PRIMARY KEY uniquely identifies each row, AUTO_INCREMENT auto-numbers them
CRUD operations: INSERT/SELECT/UPDATE/DELETE
UPDATE/DELETE without WHERE is the nuclear button ⚠️
phpMyAdmin provides visual MySQL management

📝 Exercises

In phpMyAdmin, create the myblog database and the users table (with id/username/email/password/age/created_at). Insert 5 test user records.
Create a posts table (id/user_id/title/content/status/created_at). Insert 3 article records. Use SELECT to query all published articles.
Use UPDATE to change an article's title. Use DELETE to remove an outdated article. Verify the results.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

PHP: MySQL Database Basics

1. What Is a Database?

2. MySQL Installation and Connection

(1) Windows with XAMPP

(2) Connecting to MySQL

3. Creating Databases and Tables

▶ Example: Creating a Database

▶ Example: Creating a Users Table

4. Common MySQL Data Types

5. Basic CRUD Operations

▶ Example: Basic CRUD

6. Working with phpMyAdmin

7. Creating a Posts Table

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好