MySQL Database — Study Notes

1. Introduction

A database is an organised collection of related data that is stored and managed electronically so that it can be easily accessed, updated, and retrieved. In the modern world, every website, bank, school, and hospital uses a database to store its records. MySQL is one of the most popular open-source relational database management systems (RDBMS) in the world. It uses Structured Query Language (SQL) to perform operations on the data, and it is widely used with web technologies such as PHP, Python, and Java to build dynamic websites.

A Relational Database Management System stores data in the form of tables. A table is a collection of rows and columns, where each column represents a field or attribute and each row represents a record. For example, a school database may have a STUDENT table with columns such as ROLL_NO, NAME, CLASS, and MARKS. Relationships can be established between tables using keys, which is what makes the system relational. MySQL supports these relational features along with data integrity and security.

In this chapter we will learn about database concepts such as tables, rows, columns, and keys, and then study the practical SQL commands used to create, manage, and query data. We will cover DDL commands such as CREATE and ALTER, DML commands such as INSERT, UPDATE, and DELETE, and the most important query command SELECT. Students will also learn constraints like PRIMARY KEY and FOREIGN KEY, and how to use operators and clauses in queries.

2. Database Terminology

Understanding the basic terminology is essential before learning SQL commands.

• Database: A collection of interrelated data stored together in an organised manner.
• Table: A structure in a database that stores data in rows and columns.
• Record (Row): A single complete set of information in a table. For example, one student's complete details form one record.
• Field (Column): A single attribute of a record. For example, NAME is a field that stores the names of students.
• Primary Key: A column or set of columns whose values uniquely identify each record in a table. A primary key cannot have NULL values or duplicate values.
• Foreign Key: A column in one table that refers to the primary key of another table, creating a relationship between the two tables.
• Candidate Key: A column that can serve as the primary key. Among the candidate keys, one is chosen as the primary key.

For example, in a STUDENT table, ROLL_NO can serve as the primary key because no two students have the same roll number. This terminology is frequently tested in examinations.

3. Introduction to SQL and Data Types

SQL is the standard language for defining and manipulating data in a relational database. SQL commands are divided into categories based on their function.

• DDL (Data Definition Language): Used to define the structure of the database. Commands include CREATE, ALTER, and DROP.
• DML (Data Manipulation Language): Used to manipulate the data. Commands include INSERT, UPDATE, and DELETE.
• DQL (Data Query Language): Used to retrieve data. The SELECT command belongs to this category.
• DCL (Data Control Language): Used to control access. Commands include GRANT and REVOKE.

MySQL supports many data types for table columns, including:

• INT: for whole numbers such as roll numbers.
• VARCHAR(n): for variable-length text up to n characters.
• CHAR(n): for fixed-length text of exactly n characters.
• DATE: for dates in the format YYYY-MM-DD.
• DECIMAL(p,s): for numbers with decimal places.
• TEXT: for long text strings.

4. Creating and Modifying Tables (DDL)

The CREATE TABLE command is used to create a new table. The syntax is:

CREATE TABLE student (
    roll_no INT PRIMARY KEY,
    name VARCHAR(30),
    class VARCHAR(10),
    marks INT
);

The PRIMARY KEY constraint ensures that the roll_no column uniquely identifies each row and cannot contain NULL. Once a table is created, its structure can be changed with ALTER TABLE. The ALTER TABLE command can add a new column, modify an existing column, or drop a column.

ALTER TABLE student ADD phone VARCHAR(12);
ALTER TABLE student MODIFY name VARCHAR(40);

The DROP TABLE command removes a table completely along with its data. These commands define the structure, so great care must be taken before using DROP.

5. Inserting and Updating Data (DML)

The INSERT INTO command adds new records to a table. Values must be written in the same order as the columns.

INSERT INTO student VALUES (1, 'Riya', 'X', 92);
INSERT INTO student (roll_no, name) VALUES (2, 'Arjun');

The UPDATE command modifies the data of existing records. The SET clause specifies which columns to change, and the WHERE clause specifies which records to update. Without a WHERE clause, all records would be updated, which is dangerous.

UPDATE student SET marks = 95 WHERE roll_no = 2;

The DELETE command removes records from the table. Like UPDATE, it should be used with a WHERE clause to target specific records.

DELETE FROM student WHERE roll_no = 3;

6. Retrieving Data with SELECT

The SELECT command is the most important and most frequently used SQL command. It retrieves data from the database. The basic syntax is:

SELECT column1, column2 FROM table_name;

• SELECT * FROM student; retrieves all columns and all records.
• SELECT name, marks FROM student; retrieves only the specified columns.
• SELECT name FROM student WHERE marks > 90; filters records based on a condition.
• SELECT DISTINCT class FROM student; removes duplicate values from the result.
• SELECT name FROM student ORDER BY marks DESC; sorts the result in ascending (ASC) or descending (DESC) order.
• SELECT COUNT(*) FROM student; counts the number of records.
• SELECT MAX(marks), MIN(marks), AVG(marks) FROM student; computes aggregate values.

The WHERE clause can use comparison operators (=, >, <, >=, <=, !=) and logical operators (AND, OR, NOT). The LIKE operator is used for pattern matching, for example WHERE name LIKE 'A%' finds names beginning with the letter A.

7. SQL Constraints

Constraints are rules applied to table columns to ensure the accuracy and integrity of the data.

• NOT NULL: Ensures a column cannot have NULL (empty) values.
• UNIQUE: Ensures all values in a column are different.
• PRIMARY KEY: Combines NOT NULL and UNIQUE and uniquely identifies each record.
• FOREIGN KEY: Links a column to the primary key of another table, enforcing referential integrity.
• CHECK: Ensures values in a column satisfy a given condition, such as CHECK (marks >= 0).
• DEFAULT: Provides a default value for a column when no value is given.

These constraints protect the database from invalid data and are an important topic in both theory and practical exams.

8. Advantages of MySQL and Databases

• Reduced redundancy: Data is stored once and shared, avoiding duplication.
• Data consistency: Constraints and transactions keep data accurate.
• Data security: Access can be controlled with passwords and privileges.
• Data sharing: Multiple users can access the same data simultaneously.
• Efficient retrieval: Indexes and queries allow fast searching and sorting.
• Backup and recovery: Databases support regular backup and restore operations.

MySQL in particular is free, open-source, fast, reliable, and compatible with all major operating systems, which explains its enormous popularity.

Quick Revision Tables

Mind Map

Important Diagrams (SVG)

Diagram 1: Relational Database Structure

Diagram 2: SQL Command Categories

Common Mistakes

1. Running UPDATE or DELETE without a WHERE clause, which modifies or removes all records in the table.
2. Forgetting to enclose text values in single quotes in INSERT and WHERE clauses, such as writing WHERE name = Riya instead of WHERE name = 'Riya'.
3. Confusing DROP with DELETE. DROP removes the entire table, while DELETE removes records but keeps the table.
4. Giving duplicate or NULL values to a primary key column, which is not allowed.
5. Confusing the order of WHERE and ORDER BY clauses. ORDER BY must come after WHERE.
6. Using VARCHAR without a size, or using the wrong data type for a column.
7. Writing UPDATE without the SET clause, which makes the command invalid.

Exam Tips

1. Learn the full forms: SQL (Structured Query Language), DDL, DML, DQL, and DCL.
2. Practise writing a CREATE TABLE statement with a primary key and at least two data types.
3. Memorise that INSERT adds, UPDATE modifies, and DELETE removes data, with WHERE used for targeting.
4. Be able to write SELECT queries with WHERE, ORDER BY, and aggregate functions like COUNT and MAX.
5. Remember that a primary key is NOT NULL and UNIQUE, and a foreign key links two tables.
6. Practise practical commands on a MySQL console or online playground before the exam.

Conclusion

Databases are the storage engines behind all modern applications, and MySQL is one of the most widely used database systems in the world. In this chapter we learned the fundamental terminology of relational databases, including tables, records, fields, and keys. We studied the SQL command categories and practised the DDL commands CREATE, ALTER, and DROP, the DML commands INSERT, UPDATE, and DELETE, and the DQL command SELECT with its powerful clauses and aggregate functions. Constraints such as PRIMARY KEY, FOREIGN KEY, NOT NULL, and UNIQUE protect the integrity of the data. With these skills, students can connect a database to a web application and build real, dynamic systems.