MySQL Database Part-3

In the previous article, we explored the MySQL query fundamentals which is the foundation to write and run query in the MySQL. In this article, we will look into the practical examples for different statements available in MySQL to retrieve, store and manage large scale of data.

Table of contents:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• MySQL 6 Statements
• Built-in functions
• JOINs

1 – Data Definition Language (DDL)

1.1 – CREATE

The CREATE statement in MySQL is a fundamental Data Definition Language command used to define and establish new objects within your database.

To create a database, run the below query in your query tool.

CREATE DATABASE K_University;

In the above query, CREATE is a statement, DATABASE is an object, and K_University is a database name you want to create database with this name.

Now we can create table in this database named K_University. Check the below query to create table with name students in the database.

CREATE TABLE students(
student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
Date_of_Birth DATE NOT NULL
);

The above query create a new table named students in the university database. TABLE is an object and students is the table name. We define 4 columns for this table. student_id is a numeric number generated by system and we define it as PRIMARY KEY so this student_id is unique. INT is a data type for this column. I will explain PRIMARY KEY and other keys used in the database at the end of article so at this time you can keep in mind that this is unique key. For first_name and last_name data type is VARCHAR and NOT NULL means these columns are not empty. For Date_of_Birth column we use data type DATE to store student date of birth.

1.2 – ALTER

ALTER statement in MySQL modify the structure of an existing database object. The below query will add mobile_number column to the students table.

ALTER TABLE students
ADD COLUMN mobile_number VARCHAR(25);

1.3 – DROP

The DROP statement delete existing object from the database. When you DROP and object, it and all its associated data are permanently removed from the database.

ALTER TABLE students
DROP COLUMN mobile;

1.4 – RENAME

The RENAME statement is used to change the name of an existing database object. It is primarily use for tables but can also apply to databases.

RENAME TABLE students TO student;

1.5 – TRUNCATE

TRUNCATE statement quickly removes all rows from the table while keeping the structure.

TRUNCATE TABLE student;

2 – Data Manipulation Language (DML)

2.1 – INSERT

The INSERT statement in MySQL is a fundamental Data Manipulation Language (DML) command and this command add new rows(records) into table. The below query add new record in the table student.

INSERT INTO student (first_name, last_name, date_of_birth, mobile_number) VALUES ('John', 'Doe', '2002-04-27', 799000111);

2.2 – UPDATE

UPDATE statement in MySQL modify existing data within one or more rows of a table.

UPDATE student SET first_name = 'Jon';

You can also update the data using WHERE clause this way:

UPDATE student SET date_of_birth = '2003-04-27' WHERE student_id = 1;

2.3 – DELETE

The DELETE statement in MySQL remove rows from the table. You pass your condition using WHERE clause with DELETE command.

DELETE FROM student WHERE student_id = 1;

3 – MySQL 6 Statements

3.1 – SELECT & FROM

Users most frequently use the SELECT statement in MySQL to retrieve data from one or more tables in their databases. WHERE clause is also used with SELECT to retrieve column data from the table.

SELECT * FROM student;
SELECT subject FROM faculty;
SELECT date_of_birth FROM student;

3.2 – WHERE

The WHERE clause in MySQL is a fundamental part of SELECT, UPDATE, and DELETE statements. Its primary purpose is to filter rows based on logical condition ensuring that only the rows that satisfy that condition are included in the operation.

SELECT first_name, last_name
FROM faculty
WHERE department = 'Computer Science';

3.3 – GROUP BY

The GROUP BY clause in MySQL is a fundamental part of the SELECT statement that allows you to group rows that have the same values in specified columns into a set of summary rows. For each group you can then apply aggregate functions like COUNT(), SUM(), AVG(), MIN(), MAX() to calculate a single summary value for that group.

SELECT department_id, count(faculty_id) AS total_faculty
FROM Faculty
GROUP BY department_id;

3.4 – HAVING

The HAVING clause filters groups of rows based on a specified condition after the GROUP BY clause creates those groups and calculates aggregate functions.

SELECT department_id, count(faculty_id) AS total_faculty
FROM Faculty
GROUP BY department_id
HAVING department_name = 'Computer Science';

3.5 – ORDER BY

The ORDER BY clause in MySQL sorts the result set of a SELECT query based on one or more specified columns. It allows you to present your retrieved data in a meaningful order, whether ascending (A-Z, 0-9) or descending (Z-A, 9-0).

SELECT first_name, last_name, date_of_birth
FROM student
ORDER BY first_name DESC;

4 – Built-in functions

4.1 – COUNT

COUNT function count total number of rows from the table.

SELECT COUNT(*) AS total_students FROM student;

4.2 – AVG

AVG function calculates the average value of a numeric column.

SELECT AVG(age) AS average_age FROM student;

4.3 – SUM

The SUM function calculates the sum of a set of values.

SELECT faculty_id, SUM(salary) AS total_salary
FROM faculty
GROUP BY faculty_id;

4.4 – MIN & MAX

These functions return smallest and biggest value in a column.

SELECT MIN(salary) AS lowest_salary FROM faculty;
SELECT MAX(salary) AS highest_salary FROM faculty;

4.5 DISTINCT

DISTINCT keyword with SELECT statement to retrieve only unique values from one or more columns in a table.

SELECT DISTINCT first_name, last_name FROM student;

5 – JOINs

JOINs are fundamental operations used to combine rows from two or more tables based on a related column between them.

5.1 – INNER JOIN

Inner Join returns only the rows that have matching values in both tables based on the join condition.

SELECT E.employee_name, D.department_name,
FROM Employees AS E
INNER JOIN Departments AS D
ON E.department_id = D.department_id;

5.2 – LEFT JOIN

Left Join returns all rows from the left table and the matching rows from the right table.

SELECT E.employee_name, D.department_name
FROM Employees as E
LEFT JOIN Departments AS D
ON E.department_id = D.department_id;

5.3 – RIGHT JOIN

Right Join returns all rows from the right table and the matching rows from the left table.

SELECT E.employee_name, D.department_name
FROM Employees as E
RIGHT JOIN Departments AS D
ON E.department_id = D.department_id;

5.4 – UNION

Union returns all data from the first table, with all data from the second table appended to the end. Make sure that your two SELECT statements have the same number of columns, columns are in the same order, and columns in each table have similar data types.

SELECT
'student' AS type,
first_name,
last_name,
college_name
FROM student

UNION

SELECT
'faculty' AS type,
first_name,
last_name,
college_name
FROM faculty;

Conclusion

This part 3 of our MySQL Database series has delved into various practical query examples. By exploring these essential statements and clauses, you are now better equipped to perform real-world data analysis and extract meaningful insights from your MySQL databases. Keep practicing these queries, as hands-on experience is key to mastering SQL.