🐬 MySQL Basics

🐬 MySQL Basics

1. What is MySQL?

  • MySQL is an open-source relational database management system (RDBMS).
  • Stores data in tables (rows & columns).
  • Uses SQL (Structured Query Language) to query and manage data.

2. Key Concepts

  • Database → Collection of related tables.
  • Table → Collection of rows & columns.
  • Row (Record) → One entry (e.g., a customer).
  • Column (Field) → Attribute (e.g., customer name).
  • Primary Key → Uniquely identifies each row.
  • Foreign Key → Links rows between two tables.

3. Basic SQL Commands in MySQL

3.1 Database Commands

-- Create a database
CREATE DATABASE mydb;

-- Use a database
USE mydb;

-- Show databases
SHOW DATABASES;

3.2 Table Commands

-- Create a table
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
);

-- Show all tables
SHOW TABLES;

-- Delete a table
DROP TABLE customers;

3.3 Insert Data

INSERT INTO customers (name, email, age)
VALUES ('John Doe', 'john@example.com', 28);

3.4 Select Data

-- Select all rows
SELECT * FROM customers;

-- Select specific columns
SELECT name, email FROM customers;

-- With condition
SELECT * FROM customers WHERE age > 25;

3.5 Update Data

UPDATE customers
SET age = 30
WHERE name = 'John Doe';

3.6 Delete Data

DELETE FROM customers WHERE id = 1;

4. Clauses

  • WHERE → Filter rows.
  • ORDER BY → Sort results.
  • GROUP BY → Group results.
  • HAVING → Conditions on grouped data.
  • LIMIT → Restrict number of rows.

Example:

SELECT age, COUNT(*) 
FROM customers 
GROUP BY age 
HAVING COUNT(*) > 1
ORDER BY age DESC
LIMIT 5;

5. Joins

  • INNER JOIN → Only matching rows.
  • LEFT JOIN → All left rows + matched right.
  • RIGHT JOIN → All right rows + matched left.
  • FULL JOIN (Not directly in MySQL, use UNION).

Example:

SELECT c.name, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

6. Constraints

  • PRIMARY KEY → Unique & not null.
  • FOREIGN KEY → References another table.
  • UNIQUE → No duplicate values.
  • NOT NULL → Column cannot be null.
  • DEFAULT → Set default value.

✅ Summary:
MySQL is a relational database. You create databases → tables → insert data → query (CRUD).

Back to blog

Leave a comment