Hey all 👋,
Hope you all doing great and today we gonna discuss few MySQL topics that are often ignored or say it advanced.
To whom this blog is intended for? Absolute beginners? Intermediates ? To say it is intended for someone between beginner to Intermediate level, A Basic SQL knowledge is sufficient to understand.
Lets get started 🚀🚀
Table of contents :
CASE Statements
These are similar to if-else statements, The CASE statement goes through a list of conditions and returns the result corresponding to first condition that is true. If none of conditions are met it returns the result from else statement.
Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
Example
SELECT
(CASE
WHEN follower_count >= 100 THEN "Celebrity"
ELSE "Poor Regular User"
END) as "User Type"
FROM user_table
/* No Celebrities are harmed in making of this code */
The above statement returns the user type based on his follower count. If it is more than 100 followers then returns "Celebrity" else returns as "Poor Regular User"
IFNULL
This function takes 2 Arguments, if the first argument is NULL, it returns second argument.
Usually used to replace a NULL value with a default value while displaying the data.
Syntax
IFNULL( column_name, alternate_value )
Example
SELECT IFNULL (amount,0) FROM user_table
If value from amount column is null then 0 is returned in place of null value
COALESCE
This function takes 2 or more arguments and It works similar to IFNULL, but returns the first Non NULL argument among the list of arguments, if every argument is NULL then it returns NULL.
Syntax
COALESCE(argument_1, argument_2...., argument_n)
Example
SELECT COALESCE(phonenumber_1, phonenumber_2, phone number_3) FROM user_table
If phone number 1 is null then it checks for phone number 2, even if it is null then it returns phone number 3
Stored Procedure
A stored procedure is a group of SQL statements that has been created and stored in the database.
It accepts input parameters so that only one procedure can be used on the network by multiple clients using different input data.
Syntax
--For creating procedure
DELIMITER &&
CREATE PROCEDURE procedure_name ([[IN | OUT | INOUT] Parameter_name datatype ]... )
BEGIN
sql_statements...
END &&
DELIMITER ;
-- The delimiter is used to differentiate between the end of the procedure and the sql statements.
For executing procedure
CALL procedure_name ( arguments... );
Example
--Creating getvalues procedure which prints out data in users_table when called
DELIMITER &&
CREATE PROCEDURE getvalues ()
BEGIN
SELECT * from users_table;
END&&
DELIMITER;
-- calling procedure
CALL getvalues();
Window Functions (Available only for MySQL 8 +)
Window functions perform calculations on a set of rows that are related together. These related rows are termed as windows.
They Do not merge the rows like GROUP BY, they preserve the existing data.
Mainly there are 3 Categories of window functions.
-
Aggregate Window Functions
SUM(), MAX(), MIN(), AVG(). COUNT()
-
Ranking Window Functions
RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
-
Value Window Functions
LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
Syntax
window_function ([column1....]) OVER ([ PARTITION BY partition_list ] [ ORDER BY order_list])
-- [] = contents inside square brackets are optional
RANK
This function will assign rank to each row within a partition, if there is a tie between the values then they will be assigned same rank and the next rank will be ( previous rank + no of previous duplicates )
Example rank sequence : 1 2 2 4 5 5 7
Example :
First, lets see what's there in the table
SELECT * FROM student
Now lets execute the RANK query
SELECT *, RANK() OVER (ORDER BY marks DESC) as "Rank" from student
You can now see the result is sorted and every person is given a rank value, though the rank is not continuous at row with name vikram
DENSE_RANK
Did you notice discontinuity in rank values when using RANK? Dense Rank does the same thing as RANK but with no discontinuity in rank values.
Considering the same above example, lets execute dense query and see the result
select *, DENSE_RANK() OVER (ORDER BY marks DESC) as "Dense Rank" from student
You can now observe no discontinuity of rank values at row with name as vikram
ROW_NUMBER
This function is used to give sequential number for each row within its partition.
Lets have a look at the table data before executing the ROW_NUMBER query
SELECT * FROM student
You can see students from different departments, using ROW_NUMBER function we can assign each row a sequential number starting from 1 based on department( dept ) he is from.
SELECT *, ROW_NUMBER() OVER(PARTITION BY dept) AS "row number" FROM student;
You can observe each row of a particular department is given a sequential row number, this is what ROW_NUMBER function can do.
Good Bye👋
That's it for the blog, these are my 2 cents about some tricky SQL concepts. For more information on concepts, you can refer to javapoint
I hope you got to learn something new and will be able to do better with MySQL. If there are any further questions please feel free to ask.
Top comments (23)
test3
test 4
test 5
test 6
test 7
test
testing5
testing6
testing8
test
testing1
testing20
testing21
testing10
testing11
testing12
testing9
testing7
Some comments have been hidden by the post's author - find out more