Introduction to Databases and MySQL 📊🛢️

---

    

---

    

Introduction to Databases and MySQL 📊🛢️
1️⃣ What is a Database?
A database is a structured collection of data that allows for efficient storage, retrieval, and management. It is used in applications ranging from websites and mobile apps to enterprise software.

Types of Databases:
Relational Databases (RDBMS) – Uses tables, rows, and columns (e.g., MySQL, PostgreSQL, Oracle, SQL Server).

NoSQL Databases – Handles unstructured or semi-structured data (e.g., MongoDB, Firebase, Redis).

Cloud Databases – Hosted and managed on cloud platforms (e.g., Amazon RDS, Google Cloud SQL).

2️⃣ What is MySQL?
MySQL is a popular open-source relational database management system (RDBMS) that stores data in tables and uses Structured Query Language (SQL) for data operations.

✔ Fast and scalable 🚀
✔ Free and widely used 🎯
✔ Works with many programming languages (PHP, Java, Python)

3️⃣ Setting Up MySQL
✅ 3.1 Installing MySQL
You can install MySQL in different ways:

Standalone Installation – Download from MySQL website.

Using XAMPP (for PHP Development) – Comes with MySQL and phpMyAdmin.

Docker – docker pull mysql

✅ 3.2 Accessing MySQL
After installation, you can access MySQL via:

Command Line:

mysql -u root -p



phpMyAdmin (GUI for MySQL)

MySQL Workbench (Official GUI tool)

4️⃣ MySQL Database Basics
✅ 4.1 Creating a Database

 



CREATE DATABASE school;

✅ 4.2 Using a Database

USE school;

✅ 4.3 Creating a Table

 

CREATE TABLE students (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(50) NOT NULL,

    age INT,

    email VARCHAR(100) UNIQUE

);

✔ AUTO_INCREMENT – Automatically generates a unique ID.
✔ PRIMARY KEY – Ensures uniqueness.
✔ NOT NULL – Field cannot be empty.
✔ UNIQUE – Prevents duplicate values.

5️⃣ Data Operations in MySQL
✅ 5.1 Inserting Data

 

INSERT INTO students (name, age, email)

VALUES ('Alice', 22, 'alice@example.com');

✅ 5.2 Retrieving Data

 

SELECT * FROM students;



SELECT name, age FROM students WHERE age > 20;

✅ 5.3 Updating Data

 

UPDATE students SET age = 23 WHERE name = 'Alice';

✅ 5.4 Deleting Data

 

DELETE FROM students WHERE name = 'Alice';

6️⃣ MySQL Relationships
✅ 6.1 Foreign Keys (One-to-Many Relationship)

 



CREATE TABLE courses (

    id INT AUTO_INCREMENT PRIMARY KEY,

    course_name VARCHAR(100) NOT NULL

);



CREATE TABLE enrollments (

    id INT AUTO_INCREMENT PRIMARY KEY,

    student_id INT,

    course_id INT,

    FOREIGN KEY (student_id) REFERENCES students(id),

    FOREIGN KEY (course_id) REFERENCES courses(id)

);

✔ FOREIGN KEY – Links students to enrollments.
✔ This allows one student to enroll in many courses.

7️⃣ MySQL Advanced Concepts

✅ 7.1 Joins (Combining Tables)
🔹 Inner Join

 

SELECT students.name, courses.course_name 

FROM students

JOIN enrollments ON students.id = enrollments.student_id

JOIN courses ON enrollments.course_id = courses.id;

🔹 Left Join

 

SELECT students.name, courses.course_name

FROM students

LEFT JOIN enrollments ON students.id = enrollments.student_id

LEFT JOIN courses ON enrollments.course_id = courses.id;

✅ 7.2 Indexing (Optimizing Queries)

 

CREATE INDEX idx_student_name ON students(name);

✅ 7.3 Transactions (Ensuring Data Integrity)

START TRANSACTION;

UPDATE students SET age = 24 WHERE id = 1;

COMMIT;

8️⃣ Connecting MySQL with Programming Languages
✅ 8.1 PHP (Using MySQLi)

 

<?php

$conn = new mysqli("localhost", "root", "", "school");

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

$sql = "SELECT * FROM students";

$result = $conn->query($sql);

while ($row = $result->fetch_assoc()) {

    echo "Name: " . $row["name"] . "<br>";

}

$conn->close();

?>

✅ 8.2 Java (Using JDBC)

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/school", "root", "password");

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM students");

while (rs.next()) {

    System.out.println(rs.getString("name"));

}

con.close();

9️⃣ Security Best Practices
✔ Use prepared statements to prevent SQL Injection
✔ Encrypt sensitive data before storing it
✔ Restrict database permissions for users
✔ Regularly backup databases

Example of Prepared Statements (PHP)

 

$stmt = $conn->prepare("SELECT * FROM students WHERE email = ?");

$stmt->bind_param("s", $email);

$stmt->execute();

🔟 Summary
✅ MySQL is a relational database used to store structured data.
✅ SQL is used to create, read, update, and delete data.
✅ Tables are connected using foreign keys for relational integrity.
✅ Joins allow data retrieval from multiple tables.
✅ Indexing improves query performance.
✅ MySQL integrates with languages like PHP, Java, Python, etc.