Introduction to Databases and MySQL

# CHAPTER 1

Introduction to Databases and MySQL #

Most databases begin life as a spreadsheet, and the spreadsheet works fine — until it does not.

The failure is predictable. Two people open it at once and one person's edits vanish. A customer's name is spelled three different ways across four sheets, so counting them honestly becomes impossible. Someone deletes a row that another sheet depended on and nothing warns them. The file reaches a size where opening it takes a minute.

Every one of those problems is something a database management system was built to solve. MySQL enforces that a customer exists before an order can reference them, lets fifty people write simultaneously without overwriting each other, guarantees that a half-finished transaction leaves no trace, and answers questions across millions of rows in milliseconds.

MySQL specifically has been the default choice for web applications for over two decades — it is the M in the LAMP stack, and it runs behind an enormous share of the sites you use daily. It is free, extensively documented, and available on every hosting platform you are likely to encounter, which makes it a sensible first database even if you later move to PostgreSQL or something else.

We start with the concepts rather than the syntax, because SELECT statements are easy to memorise and meaningless without knowing what a relation, a key and a schema actually are. You will have MySQL installed and your first database created before the chapter ends.

1. What is a Database? #

• Store massive amounts of data securely.

• Retrieve specific data in milliseconds.

• Update or modify data safely.

• Delete outdated data.

A database is controlled by a Database Management System (DBMS).

2. Types of Databases #

1. 1. Relational Databases (SQL): Data is stored in rigid, structured tables with rows and columns (like a spreadsheet). Examples: MySQL, PostgreSQL, Oracle.

1. 2. Non-Relational Databases (NoSQL): Data is stored in flexible, document-like structures without rigid tables. Examples: MongoDB, Redis.

*This course focuses exclusively on Relational Databases.*

3. What are Relational Databases? #

• A user buys a product.

• Instead of copying all the user's information into the Orders table, the Orders table simply stores a "Link" (a relationship) pointing back to the specific user in the Users table.

4. What is MySQL? #

5. Why use MySQL? #

• Open Source: It is free to use.

• Speed: It is highly optimized for fast reading, making it perfect for websites.

• Scalability: It can handle databases ranging from 10 rows to millions of rows.

• The LAMP Stack: It is the "M" in the legendary LAMP stack (Linux, Apache, MySQL, PHP) that powers WordPress and over 40% of the internet.

6. Database Use Cases #

• E-commerce: Storing products, shopping carts, and customer transaction history.

• Social Media: Storing user profiles, friend lists, and posts.

• Content Management Systems (CMS): WordPress uses MySQL to store all blog posts, comments, and site settings.

7. Mini Project: Create Your First Database #

-- This is a SQL comment.
-- The command to create a new database is simply: CREATE DATABASE

CREATE DATABASE tech_blog;

-- To tell the system we want to actively use this new database:
USE tech_blog;

*Congratulations! You just created the digital container that will hold all your future tables and data.*

8. Common Mistakes #

• Confusing MySQL with SQL: SQL is the language (the grammar and vocabulary). MySQL is the actual software program (the engine) that understands the SQL language.

• Using Spreadsheets instead of Databases: Beginners often try to use Excel files to store app data. Spreadsheets cannot handle multiple users reading/writing at the exact same millisecond; databases are designed specifically for high-concurrency traffic.

9. Best Practices #

• Naming Conventions: Always name your databases using lowercase letters and underscores (snake_case). For example, my_store_db is excellent. MyStore Database will cause massive errors in code.

10. Exercises #

1. 1. What is the difference between a Relational Database and a Non-Relational Database?

1. 2. Write the SQL command to create a database named hospital_records.

11. MCQ Quiz with Answers #

12. Interview Questions #

• Q: Explain why a web application needs a Database Management System instead of just saving user data to a .txt or .csv file on the server.

• Q: What is the fundamental difference between a Relational Database (like MySQL) and a NoSQL database (like MongoDB)?

13. FAQs #

14. Summary #

15. Next Chapter Recommendation #