List of My Sql Commands

CheatSheet_MySQL.md

🏁 MY SQL CHEATS 🚀

To Support Multi Language 📍

jdbc:mysql://localhost:3306/databasename??useUnicode=true&characterEncoding=utf-8

COUNT OF COLUMNS at BOTTOM 📍

SELECT 
       IFNULL(EXAM_CENTRE, 'Total') AS Master_Code ,
            ac
FROM (
      SELECT EXAM_CENTRE,COUNT(EXAM_CENTRE) AS ac
FROM mpsc_appln_flow 
WHERE `EXAM_CODE`="ABC000001" 
GROUP BY EXAM_CENTRE  WITH ROLLUP
     ) AS DT

Result 📍

Master_Code	COUNTS
DEPT1	55
DEPT2	45
TOTAL	100

SELECT COUNT OF OCCURENCE FROM TABLE A TO TABLE B (LOOPING) 📍

SELECT 
EMP.EMP_NAME,
EMP.EMP_ID,
Count(CASE WHEN (DAT.ATTENDANCE_STATUS='AS001') THEN DAT.ATTENDANCE_STATUS END) AS NO_OF_PRESENT,
Count(CASE WHEN (DAT.ATTENDANCE_STATUS='AS002') THEN DAT.ATTENDANCE_STATUS END) AS NO_OF_ABSENT,
Count(CASE WHEN (DAT.ATTENDANCE_STATUS='AS003') THEN DAT.ATTENDANCE_STATUS END) AS NO_OF_OD
FROM DEPT_ATTENDANCE_T DAT,
EMPLOYEE_M EMP  
WHERE 
EMP.EMP_ID = DAT.EMP_ID 
    AND EMP.DEPT_CODE = DAT.DEPT_CODE 
    AND DAT.DEPT_CODE = 'SC1502001' 
    AND DAT.ATTENDANCE_DT BETWEEN '2018-08-30' 
    AND '2018-09-04' 
group by EMP.EMP_NAME,EMP.EMP_ID

SELECT ALL Duplicate Rows 📍

SELECT 
  COLUMN_ABC 
FROM
  TABLENAME 
WHERE ADD_INFO1 IN 
  (SELECT 
    COLUMN_ABC 
  FROM
    TABLENAME 
  GROUP BY COLUMN_ABC 
  HAVING COUNT(*) > 1)

SELECT ALL TABLES WITH COLUMN NAMES 📍

SELECT * 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('COLUMN1','COLUMN2')
        AND TABLE_SCHEMA="TABLE_NAME"

Basic Util Cmds 📍

SHOW VARIABLES;

SHOW PROCESSLIST;

SET GLOBAL event_scheduler = ON;

Create EVENTS/Schedulers 📍

DELIMITER $$

ALTER EVENT `MY_EVENT` ON SCHEDULE EVERY 1 MINUTE STARTS '2018-12-31 23:00:01' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN 
     
-- WRITE Your COMMENTS 
       INSERT INTO TABLE_NAME (`YEAR`, `COLUMN2`,`TIME_STAMP`) 
  (SELECT 
    YEAR(NOW()),Value2,(NOW())); 
      
-- Update Query
        UPDATE 
    TABLE_NAME 
  SET
    COLUMN2 = "VALUE" 
  WHERE COLUMN3 = "VALUE";
      END$$

DELIMITER ;