List of table's names only
SELECT `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='db name'
AND `TABLE_NAME`='table name';
Select all rows with the same value
SELECT e1.*
FROM employees e1, employees e2
WHERE e1.phoneNumber = e2.phoneNumber
AND e1.id != e2.id;
or:
SELECT *
FROM (
SELECT *,COUNT(*) OVER(PARTITION BY phone_number) as Phone_CT
FROM YourTable
) sub
WHERE Phone_CT > 1
ORDER BY phone_number, employee_ids
Describe all tables in a database
SELECT *
FROM information_schema.columns
WHERE table_schema = 'name of db'
ORDER BY table_name,ordinal_position
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'name of db'
ORDER BY table_name,ordinal_position
List all database size
SELECT table_schema 'Database Name',
SUM(data_length + index_length) 'Size in Bytes',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB'
FROM information_schema.tables
GROUP BY table_schema;
Add 24 hours to a datetime column
UPDATE pemeriksaan_daftar
SET created_at = ADDTIME(created_at, '24:00:00');
Count data from today
SELECT COUNT(*) AS count_of_today
FROM news
WHERE DATE(created_at) = CURDATE()
Count data from current month
SELECT COUNT(*) AS count_of_month
FROM news
WHERE MONTH(created_at) = MONTH(now())
AND YEAR(created_at) = YEAR(now())
Different between timestamp in minutes
SELECT TIMESTAMPDIFF(MINUTE, created_at, CURRENT_TIMESTAMP)
FROM akun
WHERE email = 'john@gmail.com';
Age between two dates in years
SELECT TIMESTAMPDIFF(YEAR, dateOfBirth, dateOfDeceased) AS age
FROM ages
WHERE id = 1;