Technology

MySQL Basic Samples

This is the trace of my learning process.

DISTINCT and Arithmetics

SELECT DISTINCT CITY FROM STATION
WHERE ID % 2 = 0;

COUNT AND DISTINCT

SELECT COUNT(CITY)-COUNT(DISTINCT CITY) FROM STATION;

MIN and MAX

SELECT MAX(POPULATION)-MIN(POPULATION) FROM CITY;

ROUND and TRUNCATE

SELECT ROUND(1.298, 1), TRUNCATE(1.298, 1); # => 1.3 1.2

SELECT TRUNCATE(SUM(LAT_N), 4) FROM STATION
WHERE 38.7880 < LAT_N AND LAT_N < 137.2345;

ORDER BY and LIMIT

SELECT CITY, LENGTH(CITY) AS LEN FROM STATION
ORDER BY LEN, CITY
LIMIT 1;

SUBSTRING

SELECT NAME FROM STUDENTS
WHERE MARKS > 75
ORDER BY SUBSTRING(NAME, -3), ID;

Extract the last three letters from names.

Pattern Matchings

There are two different bodies of pattern matchings available in MySQL, namely standard SQL pattern matchings and the extended regular expressions. The standard one returns true if the whole string matches the pattern whereas the extended regular expressions return true if a part of the string matches the pattern.

Standard SQL Pattern Matchings

_ any single character
% an arbitrary number of characters
Use LIKE or NOT LIKE instead of = or <> as a comparison operator

SELECT * FROM pet WHERE name LIKE 'b%';

Pets, whose name starts with a 'b'

SELECT * FROM pet WHERE name LIKE '%fy';

Pets, whose name ends with an 'fy'

SELECT * FROM pet WHERE name LIKE '%w%';

Pets, whose name contains a 'w'

SELECT * FROM pet WHERE name LIKE '_____';

Pets, whose name is 5 characters long.

Extended Regular Expressions

. matches any single character
[abc] matches a, b, or c
* matches zero or more instances of the patterns preceding it
{n} repeat the preceding pattern n-times
^ and $ are used to anchor the pattern matching point to the beginning and the end

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');

Pets, whose name starts with a 'b'

There are some ways to force the pattern to match case-sensitively.

SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');

'c' is a match-control character.

SELECT DISTINCT CITY FROM STATION
WHERE REGEXP_LIKE(CITY, '^[aeiou].*[aeiou]$');

Cities, whose name starts and ends with a vowel.

Aggregations

SELECT (salary * months) AS earning, count(*) FROM EMPLOYEE
GROUP BY 1
ORDER BY earning DESC
LIMIT 1;

GROUP BY n means GROUP by the nth column in the SELECT statement.

Subqueries

SELECT ROUND(LONG_W,4)
FROM STATION
WHERE LAT_N = (SELECT MAX(LAT_N) FROM STATION WHERE LAT_N < 137.2345);

You HAVE TO name the table you made by a subquery.

SELECT TRUNCATE(SQRT(POW(C-A, 2)+POW(D-B, 2)), 4)
FROM (
    SELECT MIN(LAT_N) AS A, MIN(LONG_W) AS B, MAX(LAT_N) AS C, MAX(LONG_W) AS D
    FROM STATION
) AS _;

Joins

Joins make a new table from two tables.

Inner Join: The new table consists of rows which successfully joined on the columns.
Left Join: The new table consists of all the rows from the left table, part of which are extended with the rows from the right table.
Right Join: The new table consists of all the rows from the right table, part of which are extended with the rows from the left table.

SELECT SUM(City.population)
FROM CITY
INNER JOIN COUNTRY
ON CITY.CountryCode = COUNTRY.Code
WHERE Country.Continent = "Asia";