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