MySQL Database Part-2

In the previous article, we explored MySQL, including its features, popular tools to run query, use cases, and common applications. In this article, we will delve into 6 essential query statements, built-in functions, and the use of JOINs in MySQL.

Table of Contents:

1. Introduction to MySQL Query Syntax & Structure
2. Data Types in MySQL
3. The Big 6 Statements & Clauses of MySQL queries
4. Built-in functions
5. JOINs

As discussed in our previous article, there are several popular tools available to run MySQL queries. You can choose any of them to get started. So, let’s dive in and run our first MySQL query!

1- Introduction to MySQL Query Syntax & Structure

1.1 – Structure

• Database: A collection of related data organized into tables.
• Table: A structured set of data consisting of rows and columns.
• Record: A single row in a table, representing a specific instance of the data.

1.2 Syntax

Data Definition Language (DDL)

• CREATE: Create databases, tables, and other objects.
• ALTER: Modifies existing objects.
• DROP: Delete tables and other objects.
• RENAME: Rename table and objects.
• TRUNCATE: Removes all data from a table while keeping the structure.

Data Manipulation Language (DML)

• SELECT: Retrieves data from table.
• INSERT: Add new data into tables.
• UPDATE: Modify existing data into tables.
• DELETE: Removes data from tables.

2 – Data Types in MySQL

MySQL supports various data types for storing different kind of information.

• Numeric: INT, FLOAT, DECIMAL, etc.
• Date and Time: DATE, DATETIME, TIMESTAMP, etc.
• String: CHAR, VARCHAR, TEXT, etc.
• Other: BOOLEAN, BLOB, etc.

3 – The Big 6 Statements & Clauses of MySQL queries

1. SELECT: Column name.
2. FROM: Table name.
3. WHERE: Optional – Logical condition
4. GROUP BY – Optional – Column name
5. HAVING – Optional – Only use with GROUP BY – Logical Condition
6. ORDER BY – Optional – Always comes last – Column name

4 – Built-in functions

• String Functions: CONCAT, LENGTH, UPPER, LOWER, TRIM, SUBSTRING and many more.
• Numeric Functions: ABS, ROUND, FLOOR, RAND, SIGN, etc.
• Date and Time Functions: NOW, CURTIME, CURDATE, YEAR, UNIX_TIMESTAMP, etc.
• Aggregate Functions: COUNT, SUM, AVG, MIN, MAX, etc.
• Control Flow Functions: IF, CASE WHEN, IFNULL, NULLIF, etc.
• Information Functions: DATABASE, USER, VERSION, LAST_INSERT_ID

5 – JOINs

MySQL supports several types of JOIN clauses, each with a different way of handling matching and non-matching rows.

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• UNION

Conclusion

In this article, we explored the foundational concepts of MySQL, including query syntax and structure, the Big 6 SQL statements, commonly used built-in functions, and different types of joins.

In the next article, we’ll take this knowledge a step further by walking through practical examples for each statement, function, and join—helping you solidify your understanding through hands-on experience.