Example queries for wappler users (I will update for wappler users in my spare time. )
List databases, tables, columns
SELECT
t.table_schema AS 'Database'
,t.table_name AS 'Table'
,t.table_type AS 'Table Type'
,c.column_name AS 'Column'
,c.data_type AS 'Data Type'
FROM information_schema.tables t
JOIN information_schema.columns c ON t.table_schema = c.table_schema AND t.table_name = c.table_name
WHERE t.table_schema NOT IN( 'mysql','information_schema')
ORDER BY t.table_schema,t.table_type,t.table_name,c.ordinal_position;
Date of 1st day of last, this, next month
you can find ;
Date of first day of previous month:
concat(left(curdate() - interval 1 month, 8), '-01')
Date of first day of this month:
concat(left(curdate(), 8), '-01')
Date of first day of next month:
concat(left(curdate() + interval 1 month, 8), '-01')
Date of Monday of this week
select adddate(date('2019-06-13'), interval 2-dayofweek('2019-06-13') day ) as 1stdayofweek;
Datetime difference
Find the difference between two datetime values in seconds, minutes, hours or days. If dt1 and dt2 are datetime values of the form ‘yyyy-mm-dd hh:mm:ss’, the number of seconds between dt1 and dt2 is
UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 )
To get the number of minutes divide by 60, for the number of hours divide by 3600, and for the number of days, divide by 3600 * 24.
Peak visit counts by datetime period
You need visits table (id int, start datetime, end datetime) , if you want to try 
and you wish to track peak visit counts.
id | start | end
A simple solution is to self-join on non-matching IDs and overlapping visit times, group by ID, then order by the resulting counts:
SELECT a.id,group_concat(b.id) as Overlaps, count(b.id)+1 as OverlapCount FROM visits a JOIN visits b on a.id < b.id and a.start < b.end and b.start < a.end GROUP BY a.id ORDER BY OverlapCount DESC;
Find Duplicate Rows
SELECT xxx FROM your_table_name GROUP BY xxx HAVING COUNT(*) > 1;
Last updated: