MySQL Cheatsheet

Functions

floor
ceil
round

select floor(1.2), floor(1.6), ceil(1.2), ceil(1.6), round(1.2), round(1.6);
-- | 1 | 1 | 2 | 2 | 1 | 2 |

select sum(POPULATION), avg(POPULATION)
from CITY
where COUNTRYCODE = 'AU';

left
right
lower
upper

select left('hello', 1), right('hello', 1), lower('HELLO'), upper('hello');
-- | h | o | hello | HELLO |

select max(POPULATION) - min(POPULATION)
from CITY;

cast

-- convert char to integer
select cast('10' as unsigned), cast('-10' as signed)
-- | 10 | -10 |

-- convert char to decimal
select cast('10.22' as Decimal(10, 2)), cast('-10.22' as Decimal(10, 2))
-- | 10.22 | -10.22 |

-- convert number to char
select right(cast(10 as char), 1)
-- | 0 |

replace
regexp_replace

select replace('10045', '0', ''), regexp_replace('10045', '[0-2]', '')
-- | 145 | 45 |

case

select case 
        when (A = B and B = C) then 'Equilateral'
        when (A + B <= C or A + C <= B or B + C <= A) then 'Not A Triangle'
        when (A = B or B = C or A = C) then 'Isosceles'
        else 'Scalene'
    end
from TRIANGLES

partition by

WITH RankedOccupations AS (
  SELECT 
    Occupation,
    Name,
    ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS ranking
  FROM Occupations
)
SELECT 
  MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor,
  MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor,
  MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer,
  MAX(CASE WHEN Occupation = 'Actor' THEN Name END) AS Actor
FROM RankedOccupations
GROUP BY ranking
ORDER BY ranking;

It's used to partition rows of table into groups. It is always used inside OVER() clause.

Functions​

Functions