## A Detailed Introduction to MySQL 🧑💻
**MySQL** is one of the world's most popular **Relational Database Management Systems (RDBMS)**. Think of a database as a highly organized electronic filing cabinet. Instead of paper files, you have tables. An RDBMS like MySQL is the software you use to create, manage, and interact with these tables.
It was first released in 1995 and is now owned by Oracle Corporation. It's an **open-source** tool, which means its source code is freely available for anyone to use, inspect, and modify. MySQL stores data in **tables**, which are made up of **rows** and **columns**, much like a spreadsheet. It uses a language called **SQL (Structured Query Language)** to manage this data.
### Key Features of MySQL
* **Open-Source & Free:** It's free to download and use, making it accessible for everyone from students to large corporations.
* **Cross-Platform:** MySQL can run on various operating systems, including Windows, macOS, and Linux.
* **High Performance:** It is known for its speed and reliability, capable of handling large volumes of data efficiently.
* **Scalable:** It can be used for small personal projects as well as for large, complex applications serving millions of users.
* **Secure:** MySQL includes robust security features, such as password encryption and user access control, to protect your data.
* **Standard SQL:** It uses SQL, the standard language for interacting with relational databases.
-----
## Installation of XAMPP
To use MySQL easily on a local machine, we'll install **XAMPP**. XAMPP is a free software package that bundles everything we need: **A**pache (a web server), **M**ariaDB (a replacement for MySQL), **P**HP, and **P**erl. For our purposes, MariaDB works almost identically to MySQL, using the same commands.
1. **Download:** Go to the official Apache Friends website ([https://www.apachefriends.org](https://www.apachefriends.org)) and download the installer for your operating system (Windows, macOS, or Linux).
2. **Run the Installer:** Double-click the downloaded file to start the installation.
3. **Select Components:** You'll be asked which components to install. Ensure that **MySQL** and **phpMyAdmin** are checked. You can uncheck other components like FileZilla FTP Server, Mercury Mail Server, and Tomcat if you don't need them.
4. **Choose Installation Folder:** Select a destination folder. The default (`C:\xampp` on Windows) is usually fine.
5. **Complete Installation:** Follow the on-screen prompts to complete the installation. It might take a few minutes.
### Operating the XAMPP Control Panel
After installation, you can manage the services from the XAMPP Control Panel.
1. **Launch:** Open the XAMPP Control Panel.
2. **Start Services:** You will see a list of modules. For database work, you only need to start **Apache** and **MySQL**. Click the **Start** button next to each of them.
3. **Admin Panel:** Once MySQL is running, you can click the **Admin** button on the MySQL row. This will open **phpMyAdmin** in your web browser, which is a graphical tool to manage your databases. You can also use the **Shell** button to open a command-line interface to interact with MySQL directly.
-----
## Introduction to DDL, DML, and TCL
SQL commands are divided into sub-languages based on their function:
* **DDL (Data Definition Language):** Used to define or modify the database structure. These commands work on tables, schemas, and other database objects.
* *Examples:* `CREATE`, `ALTER`, `DROP`.
* **DML (Data Manipulation Language):** Used to manage data within the tables. These are the most common SQL commands.
* *Examples:* `SELECT`, `INSERT`, `UPDATE`, `DELETE`.
* **TCL (Transaction Control Language):** Used to manage transactions in the database, ensuring data integrity.
* *Examples:* `COMMIT`, `ROLLBACK`.
-----
## DDL (Data Definition Language) Commands 📝
DDL commands are used to build and manage the structure of your database and tables.
### Basic Data Types in SQL
When you create a table, you must define the type of data each column will hold.
| Data Type | Description |
| ------------------- | ------------------------------------------------------------------------------------------------------- |
| `CHAR(n)` | Fixed-length character string. `n` is the size. It pads with spaces if the string is shorter than `n`. |
| `VARCHAR(n)` | Variable-length character string. `n` is the maximum size. It only uses the space needed by the string. |
| `INT` or `INTEGER` | A standard whole number. |
| `SMALLINT` | A smaller whole number, uses less storage than `INT`. |
| `NUMERIC(p, d)` | A fixed-point number. `$p$` is the total number of digits (precision), and `$d$` is the number of digits after the decimal point (scale). E.g., `NUMERIC(5, 2)` can store `123.45`. |
| `FLOAT` | A single-precision floating-point number (approximate value). |
| `DOUBLE PRECISION` | A double-precision floating-point number (more precise than `FLOAT`). |
| `DATE` | Stores a date value (YYYY-MM-DD). |
| `TIME` | Stores a time value (HH:MM:SS). |
### Creating a Database
This command creates a new database.
* **Syntax:**
```sql
CREATE DATABASE database_name;
```
* **Example:**
```sql
CREATE DATABASE school;
```
To start using this database, you must select it:
```sql
USE school;
```
### Dropping a Database
This command permanently deletes an entire database. **Use with caution\!**
* **Syntax:**
```sql
DROP DATABASE database_name;
```
* **Example:**
```sql
DROP DATABASE school;
```
### Creating a Table
This is the core DDL command. It defines a new table, its columns, and their data types.
* **Schema Definition & Constraints:** The structure of a table is its **schema**. When creating a table, you can add **constraints** to enforce rules on the data.
* **`PRIMARY KEY`**: Uniquely identifies each record in a table. It cannot contain `NULL` values.
* **`FOREIGN KEY`**: Links a column in one table to the primary key of another table, enforcing referential integrity.
* **`NOT NULL`**: Ensures a column cannot have a `NULL` value.
* **`UNIQUE`**: Ensures all values in a column are different.
* **`CHECK`**: Ensures that all values in a column satisfy a specific condition.
* **`DEFAULT`**: Sets a default value for a column when no value is specified.
* **Syntax:**
```sql
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
PRIMARY KEY (column_name),
FOREIGN KEY (column_name) REFERENCES other_table(other_column)
);
```
* **Example:**
Let's create a `students` table.
```sql
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
enrollment_date DATE,
gpa NUMERIC(3, 2) CHECK (gpa >= 0.0 AND gpa <= 4.0),
major VARCHAR(30) DEFAULT 'Undeclared'
);
```
### Altering a Table
The `ALTER TABLE` command is used to add, delete, or modify columns in an existing table.
* **Adding a column:**
* **Syntax:** `ALTER TABLE table_name ADD column_name datatype;`
* **Example:** Add an email column to the `students` table.
```sql
ALTER TABLE students ADD email VARCHAR(100) UNIQUE;
```
* **Modifying a column (changing data type):**
* **Syntax:** `ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;`
* **Example:** Change the `major` column to hold longer names.
```sql
ALTER TABLE students MODIFY COLUMN major VARCHAR(50);
```
* **Adding a constraint:**
* **Syntax:** `ALTER TABLE table_name ADD CONSTRAINT constraint_name_optional constraint_definition;`
* **Example:** Let's assume we have a `majors` table and want to add a foreign key to `students`.
```sql
-- First create the majors table
CREATE TABLE majors (
major_id INT PRIMARY KEY,
major_name VARCHAR(50) UNIQUE
);
-- Now alter the students table to link to it
ALTER TABLE students ADD COLUMN major_id INT;
ALTER TABLE students ADD CONSTRAINT fk_major
FOREIGN KEY (major_id) REFERENCES majors(major_id);
```
### Dropping a Table
This command permanently deletes a table and all its data. **Be careful\!**
* **Syntax:**
```sql
DROP TABLE table_name;
```
* **Example:**
```sql
DROP TABLE students;
```
### Renaming a Table
This command changes the name of an existing
```sql
RENAME TABLE old_table_name TO new_table_name;
```
* **Example:**
```sql
RENAME TABLE students TO learners;
```
### Truncating a Table
This command removes all rows from a table, but the table structure (columns, constraints) remains. It's much faster than `DELETE` for removing all records.
* **Syntax:**
```sql
TRUNCATE TABLE table_name;
```
* **Example:**
```sql
TRUNCATE TABLE learners;
```
-----
## DML (Data Manipulation Language) Commands 🔄
DML commands are used to insert, retrieve, modify, and delete data within your tables.
First, let's set up our sample tables and data.
```sql
-- Create our tables again
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
enrollment_date DATE,
gpa NUMERIC(3, 2)
);
CREATE TABLE courses (
course_id VARCHAR(10) PRIMARY KEY,
course_name VARCHAR(50),
credits INT
);
-- Insert sample data
INSERT INTO students VALUES
(101, 'Anish', 'Sharma', '2024-05-20', 3.5),
(102, 'Bipana', 'Rai', '2023-08-15', 3.8),
(103, 'Chetan', 'Gurung', '2024-05-20', 2.9),
(104, 'Deepa', 'Thapa', '2022-08-15', 3.9),
(105, 'Anish', 'KC', '2023-08-15', 3.2);
INSERT INTO courses VALUES
('CS101', 'Intro to Programming', 3),
('MA201', 'Calculus I', 4),
('PH101', 'Physics for Engineers', 4);
```
### `INSERT INTO`
Adds new rows of data to a table.
* **Syntax:**
```sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
```
*If you are adding values for all columns in the correct order, you can omit the column list.*
* **Example:**
```sql
INSERT INTO students (student_id, first_name, last_name, enrollment_date, gpa)
VALUES (106, 'Gita', 'Pandey', '2024-05-20', 3.1);
```
### `SELECT`
Retrieves data from one or more tables. This is the most frequently used SQL command.
* **`FROM` Clause:** Specifies the table to retrieve data from.
* **`WHERE` Clause:** Filters records based on a specified condition.
* **Syntax:**
```sql
SELECT column1, column2, ...
FROM table_name
WHERE condition;
```
* **Examples:**
* Select all data from the `students` table.
```sql
SELECT * FROM students;
```
* Select only the first name and last name of all students.
```sql
SELECT first_name, last_name FROM students;
```
* Select students with a GPA greater than 3.7.
```sql
SELECT first_name, last_name, gpa FROM students WHERE gpa > 3.7;
```
### `UPDATE`
Modifies existing records in a table. **Always use a `WHERE` clause with `UPDATE`** unless you intend to change every single row.
* **Syntax:**
```sql
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
```
* **Example:**
Update Chetan Gurung's GPA to 3.0.
```sql
UPDATE students
SET gpa = 3.0
WHERE student_id = 103;
```
### `DELETE`
Removes existing records from a table. **Always use a `WHERE` clause with `DELETE`** unless you intend to delete every row.
* **Syntax:**
```sql
DELETE FROM table_name WHERE condition;
```
* **Example:**
Delete the student with `student_id` 106.
```sql
DELETE FROM students WHERE student_id = 106;
```
### Advanced `SELECT` Clauses and Functions
#### String Functions
| Function | Description | Example | Result |
| ----------------------- | --------------------------------------------------- | ------------------------------------------------- | ------------------ |
| `UPPER(str)` | Converts a string to upper-case. | `SELECT UPPER(first_name) FROM students;` | ANISH, BIPANA... |
| `LOWER(str)` | Converts a string to lower-case. | `SELECT LOWER(first_name) FROM students;` | anish, bipana... |
| `CONCAT(str1, str2,...)`| Joins two or more strings together. | `SELECT CONCAT(first_name, ' ', last_name) FROM students;` | Anish Sharma... |
| `LEFT(str, len)` | Extracts a number of characters from a string (from the left). | `SELECT LEFT(course_name, 4) FROM courses;` | Intr, Calc, Phys |
| `RIGHT(str, len)` | Extracts a number of characters from a string (from the right). | `SELECT RIGHT(course_name, 10) FROM courses;` | ogramming, Calculus I, Engineers |
| `REPEAT(str, count)` | Repeats a string a specified number of times. | `SELECT REPEAT('*', 5);` | `*****` |
| `REVERSE(str)` | Reverses a string. | `SELECT REVERSE('hello');` | `olleh` |
| `LENGTH(str)` | Returns the length of a string in bytes. | `SELECT LENGTH(first_name) FROM students WHERE student_id = 101;` | 5 |
#### Ordering the Display (`ORDER BY`)
Sorts the result set in ascending (`ASC`) or descending (`DESC`) order.
* **Syntax:**
```sql
SELECT column1, column2 FROM table_name ORDER BY column_to_sort ASC|DESC;
```
* **Example:**
List students by their GPA, from highest to lowest.
```sql
SELECT first_name, gpa FROM students ORDER BY gpa DESC;
```
#### Logical Operators (`AND`, `OR`, `NOT`)
Used in the `WHERE` clause to combine multiple conditions.
* **`AND`**: Displays a record if all conditions are `TRUE`.
* **`OR`**: Displays a record if any of the conditions are `TRUE`.
* **`NOT`**: Displays a record if the condition is `FALSE`.
* **Example:**
Find students who enrolled in 2023 and have a GPA above 3.5.
```sql
SELECT first_name, enrollment_date, gpa
FROM students
WHERE enrollment_date >= '2023-01-01' AND gpa > 3.5;
```
#### Aggregate Functions
These functions perform a calculation on a set of values and return a single value.
| Function | Description |
|-------------|-------------------------------------------|
| `COUNT()` | Counts the number of rows. |
| `SUM()` | Calculates the sum of values. |
| `AVG()` | Calculates the average of values. |
| `MIN()` | Returns the minimum value in a set. |
| `MAX()` | Returns the maximum value in a set. |
* **Example:**
Find the average GPA, the total number of students, and the highest GPA.
```sql
SELECT AVG(gpa), COUNT(*), MAX(gpa) FROM students;
```
#### `DISTINCT` Keyword
Returns only unique values in a result set.
* **Example:**
Find the unique enrollment dates.
```sql
SELECT DISTINCT enrollment_date FROM students;
```
#### `LIKE` Clause and Wildcards
Used in a `WHERE` clause to search for a specified pattern in a column.
* `%` (Percent sign): Represents zero, one, or multiple characters.
* `_` (Underscore): Represents a single character.
* **Example:**
Find all students whose first name starts with 'A'.
```sql
SELECT first_name, last_name FROM students WHERE first_name LIKE 'A%';
```
Find all students whose last name has 'ha' as the second and third letters.
```sql
SELECT first_name, last_name FROM students WHERE last_name LIKE '_ha%';
```
#### `GROUP BY` Clause
Groups rows that have the same values into summary rows. It's often used with aggregate functions.
* **Example:**
Count the number of students who enrolled on each specific date.
```sql
SELECT enrollment_date, COUNT(student_id) AS number_of_students
FROM students
GROUP BY enrollment_date;
```
#### `HAVING` Clause
Filters the results of a `GROUP BY` query. `WHERE` filters rows *before* grouping, while `HAVING` filters groups *after* grouping.
* **Example:**
Show enrollment dates where more than one student enrolled.
```sql
SELECT enrollment_date, COUNT(student_id)
FROM students
GROUP BY enrollment_date
HAVING COUNT(student_id) > 1;
```
#### `IN` Operator
Allows you to specify multiple values in a `WHERE` clause. It's a shorthand for multiple `OR` conditions.
* **Example:**
Find students with IDs 101, 103, or 105.
```sql
SELECT * FROM students WHERE student_id IN (101, 103, 105);
```
#### `BETWEEN` Operator
Selects values within a given range. The values can be numbers, text, or dates.
* **Example:**
Find students with a GPA between 3.0 and 3.8.
```sql
SELECT * FROM students WHERE gpa BETWEEN 3.0 AND 3.8;
```
#### `CASE` Statement
Goes through conditions and returns a value when the first condition is met (like an if-then-else statement).
* **Example:**
Categorize students based on their GPA.
```sql
SELECT first_name, gpa,
CASE
WHEN gpa > 3.7 THEN 'Excellent'
WHEN gpa > 3.0 THEN 'Good'
ELSE 'Needs Improvement'
END AS gpa_status
FROM students;
```
#### `LIMIT` Clause
Specifies the maximum number of records to return.
* **Example:**
Get the top 3 students with the highest GPA.
```sql
SELECT first_name, gpa FROM students ORDER BY gpa DESC LIMIT 3;
```
#### `ROUND()` Function
Rounds a number to a specified number of decimal places.
* **Example:**
Get the average GPA rounded to one decimal place.
```sql
SELECT ROUND(AVG(gpa), 1) AS avg_gpa FROM students;
```
### Joins
Joins are used to combine rows from two or more tables based on a related column.
#### `INNER JOIN`
Returns records that have matching values in both tables.
* **Example:** Let's create an `enrollments` table to link students and courses.
```sql
CREATE TABLE enrollments (
student_id INT,
course_id VARCHAR(10),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
INSERT INTO enrollments VALUES (101, 'CS101'), (102, 'CS101'), (103, 'MA201');
-- Now, let's find which students are taking which courses.
SELECT students.first_name, courses.course_name
FROM students
INNER JOIN enrollments ON students.student_id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.course_id;
```
#### `OUTER JOIN` (`LEFT JOIN` / `RIGHT JOIN`)
* **`LEFT JOIN`**: Returns all records from the left table (`students`), and the matched records from the right table (`enrollments`). The result is `NULL` from the right side if there is no match.
* **`RIGHT JOIN`**: Returns all records from the right table, and the matched records from the left table.
* **Example:**
Show all students and the courses they are enrolled in. Students not enrolled in any course will also be listed.
```sql
SELECT students.first_name, courses.course_name
FROM students
LEFT JOIN enrollments ON students.student_id = enrollments.student_id
LEFT JOIN courses ON enrollments.course_id = courses.course_id;
```
-----
## TCL (Transaction Control Language) Commands ⏳
TCL commands are used to manage transactions, which are sequences of operations performed as a single logical unit of work.
### `COMMIT`
Saves all the transactions to the database since the last `COMMIT` or `ROLLBACK`.
* **Example:**
By default, most database systems are in "auto-commit" mode. To manually control transactions, you start one:
```sql
START TRANSACTION;
-- Make a change
UPDATE students SET gpa = 4.0 WHERE student_id = 101;
-- The change is now permanent and visible to other users
COMMIT;
```
### `ROLLBACK`
Undoes transactions that have not yet been saved to the database.
* **Example:**
```sql
START TRANSACTION;
-- Delete a student by mistake
DELETE FROM students WHERE student_id = 102;
-- Realize the mistake and undo the delete
ROLLBACK;
-- The student with ID 102 is now back in the table.
```
-- Drop the database if it already exists to avoid errors on re-running the script.
DROP DATABASE IF EXISTS school;
-- Create a new database named 'school'.
CREATE DATABASE school;
-- Select the 'school' database to use for the subsequent commands.
USE school;
-- Create a table named 'students' with columns for student details, including GPA.
-- The 'id' column is the primary key and will auto-increment for each new student.
-- The 'gpa' column is a DECIMAL type to store values like 3.75 and has a CHECK constraint.
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
phone_number VARCHAR(15),
address VARCHAR(100),
date_of_birth DATE,
gpa DECIMAL(3, 2) CHECK (gpa >= 0.00 AND gpa <= 4.00)
);
-- Insert 15 records of random Nepali students into the 'students' table, now including GPA.
INSERT INTO students (first_name, last_name, email, phone_number, address, date_of_birth, gpa) VALUES
('Aarav', 'Shrestha', 'aarav.shrestha@example.com', '9841234567', 'Kathmandu', '2005-04-12', 3.80),
('Sita', 'Lama', 'sita.lama@example.com', '9851098765', 'Pokhara', '2006-08-22', 3.50),
('Ram', 'Thapa', 'ram.thapa@example.com', '9849876543', 'Biratnagar', '2005-11-05', 3.25),
('Gita', 'Gurung', 'gita.gurung@example.com', '9860123456', 'Dharan', '2007-01-30', 3.90),
('Hari', 'Rai', 'hari.rai@example.com', '9818765432', 'Butwal', '2006-03-15', 2.85),
('Priya', 'Adhikari', 'priya.adhikari@example.com', '9803456789', 'Hetauda', '2005-07-19', 3.65),
('Bikash', 'Tamang', 'bikash.tamang@example.com', '9813210987', 'Chitwan', '2006-09-01', 3.40),
('Anjali', 'Magar', 'anjali.magar@example.com', '9845551212', 'Nepalgunj', '2007-02-14', 3.75),
('Suresh', 'Karki', 'suresh.karki@example.com', '9851123321', 'Bhaktapur', '2005-06-25', 3.10),
('Manisha', 'Bhandari', 'manisha.bhandari@example.com', '9841987654', 'Lalitpur', '2006-10-11', 3.95),
('Nabin', 'KC', 'nabin.kc@example.com', '9808123456', 'Janakpur', '2005-12-28', 2.90),
('Sunita', 'Oli', 'sunita.oli@example.com', '9847654321', 'Ghorahi', '2007-05-09', 3.30),
('Rajesh', 'Yadav', 'rajesh.yadav@example.com', '9819876543', 'Birgunj', '2006-02-18', 3.00),
('Sabina', 'Pokhrel', 'sabina.pokhrel@example.com', '9843210987', 'Itahari', '2005-09-03', 3.55),
('Kiran', 'Chhetri', 'kiran.chhetri@example.com', '9861112233', 'Dhangadhi', '2006-07-07', 3.70);
-- Query to select all records from the students table to verify the data.
SELECT * FROM students;
Sign in with Wallet
Connect another wallet