MySQL

Hi devs, Today we are going to see some Must know MYSQL Predefined functions, These functions can save a-lot of your time. Mysql has so-many function and they are to deal with different data types,

You can change formatting, values or do calculations from your query.

WHEN and THEN

If i want to select users data from users table i will get it as

select * from users ;

Now it will return we all columns name, email, age, gender etc.

Here you can see gender is given as “M” and “F”. But i want output as “MALE” and “Female”. So i can change my query using When and then. here is how you do it.

SELECT
    first_name, last_name,
    salary, age,
    CASE gender
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
    END
FROM users;

So here we are using Case and then we used when and then. out put will be now as

IF CONDITION

We can filter data inside sql queries, No need to do it afterwords. Here is how you do it. I want to get users data but if age more than 65 that means employee is retired otherwise still employed.

SELECT
    first_name, last_name,
    salary, age, IF(age>65, "RETIRED", "EMPLOYEE")
FROM users;

And output looks like this

Nice Right.

IFNULL

Quite similar, If value is null in table we can return something else, Here is the example

SELECT
    first_name, last_name,
    salary, age, IFNULL(NULL, 'N/A Email')
FROM users WHERE email='NULL';

Now it returns

So if column value is NULL it is replaced with provided value.

DATE_FORMAT

Want to display time but after changing its formatting, Yes you can do it straight in the query. and here is how you do it.

SELECT
    first_name, last_name,
    salary, age,
    DATE_FORMAT(created_at, "%d-%m-%Y")
FROM users;

Original date and time format we have in table can be seen from the “First image” in this post, Now the format is changed by DATE_FORMAT. so output looks like.

You can even get day name and month name from query, Here is how

SELECT
    first_name, last_name,
    salary, age,
    DAY(created_at), 
    DAYNAME(created_at), 
    MONTH(created_at), 
    MONTHNAME(created_at), 
    YEAR(created_at)
FROM users;

And output is

Add NOW to get current date and time

SELECT
    NOW()
FROM users;

ADD – SUBTRACT TIME FROM DATE

get a date add 10 days into it. all in one query.

SELECT
	 created_at,
     ADDDATE(created_at, INTERVAL 10 DAY)
FROM users;

You can add time or Subtract time

SELECT
	 created_at,
     ADDDATE(created_at, INTERVAL 10 DAY),
     ADDDATE(created_at, INTERVAL 30 MINUTE),
     ADDDATE(created_at, INTERVAL -1 HOUR),
     ADDDATE(created_at, INTERVAL -2 MONTH)
FROM users;

Out put is like

TRUNCATE

Ok this is also useful so lets say amount is shown as “345.456453”.

you can TRUNCATE it as given below

TRUNCATE("345.456453", 2)  // this will return 345.45 

SUM, MIN, MAX

You might have seen these bad girls before. we can simply get Sum of all values in column and maximum value in a column or minimum value in column.

SELECT
     SUM(salary),
     MAX(salary),
     MIN(salary)
FROM users;

Out put is like

Same way there is AVG to find out Average

So if we want to know average salary in this users table.

SELECT
     TRUNCATE(AVG(salary),2)
FROM users;

Here i have used TRUNCATE and AVE together you can do that too. Output is

STRING FUNCTIONS

SELECT
     UPPER(first_name),
     UCASE(first_name),
     REVERSE(first_name),
     TRIM('  some text  '),
     SUBSTRING_INDEX(email, "@", 1),
     email,
     last_name,
     SUBSTRING(last_name, 2, 3),
     INSERT(first_name, 1,5, "USER: ")
FROM users;

First 2 are “UPPERCASE” and “UCASE” they work as same for making string capital. Reverse of UCASE is LCASE for lowering the casing.

Then reverse will reverse the string. After that we have TRIM it removes white space from left and right of the string.

SUBSTRING_INDEX will return the string where it finds the substring.

INSERT this will insert your defined value in string and replace the string between indexes mentioned.

Here is the output