Find the duplicate values
we have 'group by' clause to group the similar data and 'having' to put conditions in the query.
Example: find duplicate phone numbers
select phone, count(phone) from table_name group by phone having count(phone)>1
Find the row with 2nd largest value.
For finding the largest and shortest values, we have in build function 'MAX' and 'MIN'.
Approach 1: In this approach, we are finding the max value and then ignoring this max to find the second max value.
SELECT MAX(column_name) FROM table_name WHERE column_name < (SELECT MAX(column_name) FROM table_name)
Approach 2: What if we require nth largest value? To achieve this by 1st approach will be difficult. There is one way for such a case
For example, get the 5th largest value
SELECT * FROM table_name order by column_name desc LIMIT 1 OFFSET 4
Replace query
This query will be helpful if you want to replace a part of the string in the table.
UPDATE table_name SET `col-1` = REPLACE(`col-1`, 'old string', 'new-string')
Delete or truncate row having foreign key constrain
You should be 100% sure if you going to delete or truncate a table with a foreign key as it may break your foreign key constraints
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table1;
TRUNCATE table2;
SET FOREIGN_KEY_CHECKS = 1;
SET the logs of each query
Sometimes we require logs to debug
Table-based logs
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
you can check log in DB mysql and table general_log
File Based Logs
SET GLOBAL log_output = "FILE";
SET GLOBAL general_log_file = "/path/to/your/logfile.log";
SET GLOBAL general_log = 'ON';
Don't forget to off the logs, if you are not using them.