Mysql

MySQL Database Part-2

by

In the previous article, we explored MySQL, including its features, popular tools for running queries, use cases, and common applications. In this MySQL Query Tutorial, we will take the next step and explore MySQL query syntax and structure, commonly used data types, the six important clauses used in SELECT queries, built-in functions, and different types of JOINs.

If you are new to MySQL, don’t worry. We will look at each concept with simple explanations and practical examples so you can understand how these concepts are used when working with databases.

If you have not checked my previous article about MySQL Database then you can check it here.

MySQL Database Part-1

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
  6. Putting It All Together

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

Before writing queries, it is important to understand how information is organized inside a database and how SQL queries are structured.

1.1 – Database Structure

A MySQL database generally contains tables, and tables contain data organized into rows and columns.

  • Database: A collection of related data organized into tables and other database objects.
  • Table: A structured collection of data organized into rows and columns.
  • Column: Represents a specific type of information, such as a name, email address, price, or date.
  • Row/Record: Represents one individual entry in a table.

For example, a users table might look like this:

idnameemail
1Johnjohn@example.com
2Smithsmith@example.com

Here, id, name, and email are columns, while each horizontal entry represents a row or record.

1.2 – DDL and DML

SQL commands can be grouped into different categories. Two important categories when working with MySQL are Data Definition Language (DDL) and Data Manipulation Language (DML).

Data Definition Language (DDL) is used to create or modify the structure of database objects.

  • CREATE: Create databases, tables, and other objects.
  • ALTER: Modifies the structure of existing objects.
  • DROP: Deletes tables and other database objects.
  • RENAME: Renames tables and other objects.
  • TRUNCATE: Removes all rows from a table while keeping its structure.

For example:

CREATE TABLE products ( 
  id INT, 
  name VARCHAR(100), 
  price DECIMAL(10,2) 
);

Data Manipulation Language (DML) is used to add, modify, retrieve, and remove data from tables.

  • SELECT: Retrieves data from table.
  • INSERT: Adds new data to tables.
  • UPDATE: Modifies existing data.
  • DELETE: Removes data from tables.

For example:

INSERT INTO products (id, name, price) 
VALUES (1, 'Laptop', 750.00);

Understanding the difference between database structure and the data stored inside that structure makes it easier to understand SQL commands.

2 – Data Types in MySQL

Every column in a MySQL table has a data type. The data type defines what kind of information can be stored in that column.

Choosing an appropriate data type helps keep your database organized and can also improve storage efficiency.

2.1 – Numeric Data Types

Numeric data types are used for numbers.

  • INT: Commonly used for whole numbers such as IDs and quantities.
  • FLOAT: Used for floating-point numbers.
  • DECIMAL: Useful when exact decimal values are required, such as prices and financial amounts.

For example:

quantity INT, 
price DECIMAL(10,2)

For monetary values, DECIMAL is generally preferred because it is designed for exact decimal calculations.

2.2 – String Data Types

String types are used to store text.

  • CHAR: Suitable for fixed-length strings.
  • VARCHAR: Commonly used for variable-length text such as names and email addresses.
  • TEXT: Useful for larger blocks of text such as descriptions or comments.

For example:

name VARCHAR(100),
description TEXT

2.3 – Date and Time Data Types

MySQL provides several data types for storing dates and times.

  • DATE: Stores a date.
  • DATETIME: Stores both date and time.
  • TIMESTAMP: Stores a date and time and is commonly used for tracking timestamps.

For example:

created_at DATETIME

2.4 – Other Data Types

MySQL also supports other types such as BOOLEAN and BLOB, which can be used for specific requirements.

The appropriate data type depends on the kind of information your application needs to store.

3 – The Big 6 Clauses of a MySQL SELECT Query

A SELECT query can contain several clauses that work together to retrieve and organize data.

A commonly used structure is:

SELECT column_name
FROM table_name
WHERE condition
GROUP BY column_name
HAVING condition
ORDER BY column_name;

Let’s understand each part.

3.1 – SELECT

The SELECT clause specifies the columns you want to retrieve.

SELECT name, email
FROM users;

This query retrieves the name and email columns from the users table.

3.2 – FROM

The FROM clause specifies the table from which the data should be retrieved.

SELECT *
FROM users;

Here, users is the table from which MySQL retrieves the data.

3.3 – WHERE

The WHERE clause filters individual rows based on a condition.

SELECT *
FROM users
WHERE age > 18;

Only users whose age is greater than 18 will be returned.

3.4 – GROUP BY

The GROUP BY clause groups rows that have the same value in one or more columns. It is commonly used with aggregate functions such as COUNT(), SUM(), and AVG().

SELECT country, COUNT(*) AS total_users
FROM users
GROUP BY country;

This query groups users by country and counts how many users belong to each country.

3.5 – HAVING

The HAVING clause filters groups after the GROUP BY operation.

SELECT country, COUNT(*) AS total_users
FROM users
GROUP BY country
HAVING COUNT(*) > 5;

This returns only countries that have more than five users.

A useful way to remember the difference is:

WHERE filters rows, while HAVING filters grouped results.

3.6 – ORDER BY

The ORDER BY clause sorts the final result.

SELECT name, age
FROM users
ORDER BY age DESC;

ASC sorts values in ascending order, while DESC sorts them in descending order.

4 – Built-in functions

MySQL provides many built-in functions that help you perform operations on strings, numbers, dates, groups of records, and other types of data.

4.1 – String Functions

String functions are useful when working with text.

Some commonly used functions are:

  • CONCAT() – Combines strings.
  • LENGTH() – Returns the length of a string.
  • UPPER() – Converts text to uppercase.
  • LOWER() – Converts text to lowercase.
  • TRIM() – Removes leading and trailing spaces.
  • SUBSTRING() – Extracts part of a string.

For example:

SELECT UPPER(name)
FROM users;

This converts the values in the name column to uppercase.

You can also combine values:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

4.2 – Numeric Functions

Numeric functions are useful when performing calculations.

Common examples include: ABS(), ROUND(), FLOOR(), RAND(), and SIGN().

For example:

SELECT ROUND(price, 2)
FROM products;

This rounds the price to two decimal places.

4.3 – Date and Time Functions

MySQL provides functions for working with dates and times.

Some commonly used functions include: NOW(), CURDATE(), CURTIME(), YEAR(), and UNIX_TIMESTAMP().

For example:

SELECT YEAR(created_at)
FROM users;

This extracts the year from the created_at value.

4.4 – Aggregate Functions

Aggregate functions perform calculations on multiple rows.

Common aggregate functions include:

  • COUNT() – Counts records or values.
  • SUM() – Calculates a total.
  • AVG() – Calculates an average.
  • MIN() – Finds the minimum value.
  • MAX() – Finds the maximum value.

For example:

SELECT AVG(price) AS average_price
FROM products;

This calculates the average price of all products.

Aggregate functions are especially useful when combined with GROUP BY.

4.5 – Control Flow Functions

Control flow functions allow you to apply conditions within a query.

For example:

SELECT name,
  CASE
    WHEN age >= 18 THEN 'Adult'
    ELSE 'Minor'
  END AS age_group
FROM users;

This categorizes users based on their age.

Other commonly used functions include IF(), IFNULL(), and NULLIF().

4.6 – Information Functions

Information functions provide information about the current database environment.

Some examples include: DATABASE(), USER(), VERSION(), and LAST_INSERT_ID().

These functions can be useful when you need information about the current database, user, MySQL version, or recently generated ID.

5 – JOINs

In a real-world application, related information is often stored in different tables rather than putting everything into one large table.

For example, you might have a users table and an orders table. The users table stores customer information, while the orders table stores order information.

JOINs allow you to retrieve related data from multiple tables.

MySQL supports several types of JOINs, including:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN

5.1 – INNER JOIN

An INNER JOIN returns rows where there is a matching value in both tables.

SELECT users.name, orders.order_id
FROM users
INNER JOIN orders
ON users.id = orders.user_id;

This query returns users who have matching orders.

5.2 – LEFT JOIN

A LEFT JOIN returns all rows from the left table and matching rows from the right table.

SELECT users.name, orders.order_id
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;

This can be useful when you want to display all users, including users who have not placed an order.

5.3 – RIGHT JOIN

A RIGHT JOIN works similarly to a LEFT JOIN, but it returns all rows from the right table and matching rows from the left table.

SELECT users.name, orders.order_id
FROM users
RIGHT JOIN orders
ON users.id = orders.user_id;

The choice between different JOIN types depends on which table’s records you need to retain when there is no matching record in the other table.

5.4 – UNION

UNION is sometimes mentioned alongside JOINs, but it is important to understand that UNION is not a JOIN.

UNION combines the results of two or more compatible SELECT queries.

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

JOINs combine related data from tables, while UNION combines the result sets of separate queries.

6 – Putting It All Together

Now that we have looked at the individual clauses, let’s see how several of them can work together in one query.

SELECT country, COUNT(*) AS total_users
FROM users
WHERE age >= 18
GROUP BY country
HAVING COUNT(*) > 5
ORDER BY total_users DESC;

Here is what each part does:

  1. SELECT chooses the country and calculates the number of users.
  2. FROM specifies the users table.
  3. WHERE filters the records and keeps users aged 18 or older.
  4. GROUP BY groups the users by country.
  5. HAVING keeps only countries with more than five users.
  6. ORDER BY sorts the final results from the highest number of users to the lowest.

This example demonstrates how the different clauses can work together to create a more meaningful query.

Conclusion

In this article, we explored some important MySQL concepts, including database structure, DDL and DML commands, common data types, the six major clauses used in SELECT queries, built-in functions, and different types of JOINs. We also looked at how these concepts can be combined to create practical queries.

These concepts provide a strong foundation for working with MySQL. In the next article, we’ll take this knowledge further with more practical examples of queries, functions, filtering, grouping, and JOINs so you can see how they are used in real-world scenarios.

If you enjoyed this article, we’d love to hear your thoughts. Feel free to share your feedback, questions, or suggestions in the comments section below.

If you have any questions or would like to get in touch with us, please don’t hesitate to reach out us.

1 thought on “MySQL Database Part-2”

  1. Pingback: MySQL Database Part-3 | Code Hacks

Leave a Comment