Support Online
MirrorLooking Glass
GenixNodeGenixNode
Client Panel
Skip to main content

Using MySQL Functions

💡 What Will You Learn in This Guide?​

In this guide, you will learn how to transform your data using **mathematical, textual, historical and aggregate functions in MySQL.
You'll discover computing at the database level and reducing application load with functions such as ROUND(), LOWER(), DATEDIFF() and AVG().

⚙️ 1️⃣ Sample Database Setup​

Connect to MySQL​

mysql -u genixnode_dev -p

This command logs in to the MySQL server with the user you specify.

Create New Database

CREATE DATABASE kitap_evi;

Creates a sample database named book_house.

Activate the Database

USE kitap_evi;

Runs subsequent operations on book_house.

Create Sample Table

CREATE TABLE stok (
    kitap_id INT,
    yazar VARCHAR(50),
    baslik VARCHAR(200),
    giris_tarihi DATE,
    adet INT,
    fiyat DECIMAL(5,2),
    PRIMARY KEY (kitap_id)
);

Creates a table to hold book information.

Add Data

INSERT INTO stok VALUES
(1, 'Orhan Pamuk', 'Kara Kitap', '2022-10-01', 4, 20.83),
(2, 'Elif Şafak', 'Aşk', '2022-10-04', 12, 42.13),
(3, 'Sabahattin Ali', 'Kürk Mantolu Madonna', '2022-09-23', 7, 21.99),
(4, 'Ahmet Ümit', 'Beyoğlu Rapsodisi', '2022-07-23', 9, 17.43),
(5, 'Yaşar Kemal', 'İnce Memed', '2022-10-01', 14, 23.15);

Adds 5 sample book records.

2️⃣ Mathematical Functions​

ROUND – Rounding Numbers​

SELECT baslik, fiyat, ROUND(fiyat) AS yuvarlanmis_fiyat FROM stok;

Rounds book prices to the nearest whole number.

ROUND (operational)

SELECT baslik, fiyat, adet, ROUND(fiyat * adet, 1) AS toplam_stok_degeri FROM stok;

Performs price × quantity and rounds the result to 1 decimal place.

✏️ 3️⃣ Text Functions​

LOWER – Converting to Lower Case​

SELECT LOWER(baslik) AS baslik_kucuk FROM stok;

Converts all text in the title column to lowercase.

UPPER – Conversion to Uppercase​

SELECT UPPER(yazar) AS yazar_buyuk FROM stok;

Makes author names all capital letters.

CONCAT – Concatenating Texts​

SELECT CONCAT(yazar, ' | ', baslik) AS yazar_baslik FROM stok;

Author name and title “|” combines with the symbol.

📅 4️⃣ Date Functions​

YEAR, MONTH, DAY – Date Shredding​

SELECT giris_tarihi, YEAR(giris_tarihi) AS yil, MONTH(giris_tarihi) AS ay, DAY(giris_tarihi) AS gun FROM stok;

It shows the entry dates of the books as year, month and day.

DATEDIFF – Day Difference Calculation​

SELECT baslik, DATEDIFF(CURRENT_DATE(), giris_tarihi) AS gunden_beri FROM stok;

Calculates the difference in days between the book entry date and today's date.

Note: DATEDIFF syntax may vary by database. This example is specific to MySQL.

📊 5️⃣ Aggregate Functions​

COUNT, MAX, AVG – Summary Calculations​

SELECT COUNT(baslik) AS toplam_kitap,
       MAX(fiyat) AS en_yuksek_fiyat,
       AVG(fiyat) AS ortalama_fiyat
FROM stok;

Lists the total number of books, highest and average price.

FunctionDescription
AVG()Averages the values ​​in the column.
COUNT()Returns the number of non-empty records.
MAX()Finds the largest value.
MIN()Finds the smallest value.
SUM()Calculates the sum of all values.

❓ Frequently Asked Questions (FAQ)​

  1. Do SQL functions affect performance?

Yes, but when used correctly it reduces application load. Complex operations on large data sets require attention.

  1. Why do we nickname the function result AS?

Functions do not create a default column name. We define a readable temporary name using AS.

  1. What SQL functions are common to all databases?

COUNT, SUM, AVG, MAX, MIN functions are common to most RDBMS.

  1. What does GROUP BY do?

It is used to apply aggregate functions on specific groups (e.g. each author).

🚀 Result​

With this guide, you gained the ability to process raw data directly in the query using MySQL functions. Now you can transform and optimize performance on the database side instead of the application. ☁️ Try the secure and scalable SQL infrastructure on the GenixNode platform now!