MySQL Basics

Introduction to MySQL

MySQL is a popular relational database management system used to store, manage, and retrieve data for websites and applications. It works seamlessly with PHP, making it a core technology for dynamic web development.

Creating Tables

Tables are the fundamental building blocks of a database. Each table stores data in rows and columns, with a primary key to uniquely identify records.

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Proper table design ensures data integrity and efficient queries.

Inserting Data

To add records to a table, use the INSERT INTO statement:

INSERT INTO users (username, email)
VALUES ('MasterGeshu', 'geshu@example.com');

Consistent data formatting and validation help maintain a clean database.

Querying Data

Retrieve data using the SELECT statement:

SELECT id, username, email
FROM users
WHERE email LIKE '%@example.com';

Filtering, sorting, and limiting results optimizes database performance.

Joins & Relationships

Relational databases connect multiple tables through keys. Joins allow combining data from related tables for comprehensive queries.

SELECT users.username, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Understanding relationships is essential for complex applications and maintaining normalized databases.

Conclusion

Mastering MySQL fundamentals such as table creation, inserting data, queries, and joins is vital for web developers. Combining PHP and MySQL enables dynamic, data-driven websites that are robust and scalable.