SQL reference
Date: April 19th 2016
Last updated: April 20th 2016
Use MySQL shell
mysql -u root -p
Find database
SHOW databases;
Select database (my examples will use the surfdiary database)
USE surfdiary;
Show all tables
SHOW tables;
Select all columns from a table (E.g. country table)
SELECT * FROM country;
Get all column names from a table
SELECT column_name FROM
information_schema.columns
WHERE table_name = 'country';
/*+------------------------+
| column_name |
+------------------------+
| id |
| common_name |
| captial |
| formal_name |
| iso4217_currency_code |
| iso4217_currency |
| iso3166_1_2Letter_code |
| iso3166_1_3Letter_code |
+------------------------+
8 rows in set (0.00 sec)
Select single column from a table
SELECT common_name FROM country;
Count not null records in a column
SELECT COUNT(common_name) FROM country;
/*+--------------------+
| Count(common_name) |
+--------------------+
| 241 |
+--------------------+
1 row in set (0.00 sec)
Count and rename the output column
SELECT COUNT(common_name) AS name FROM country;
/*+------+
| name |
+------+
| 241 |
+------+
1 row in set (0.00 sec)
Order column alphabetically
SELECT common_name FROM
country
ORDER BY common_name ASC; /* descending order with DESC
/*+----------------------------------------+
| common_name |
+----------------------------------------+
| Afghanistan |
| Aland |
| Albania |
| Algeria |
| American Samoa |
| Andorra |
...<- snipped ->
Select records BETWEEN certain letters in a table
SELECT common_name FROM
country
WHERE common_name BETWEEN 'A' and 'B';
/*+---------------------+
| common_name |
+---------------------+
| Antarctica |
| Ascension |
| Andorra |
| Afghanistan |
| Antigua and Barbuda |
| Albania |
| Armenia |
| Angola |
| Argentina |
| American Samoa |
| Austria |
| Australia |
| Aruba |
| Aland |
| Azerbaijan |
| Algeria |
+---------------------+
16 rows in set (0.00 sec)
Select records using LIKE (E.g. countries starting with 'Z')
SELECT common_name FROM
country
WHERE common_name LIKE 'Z%';
/*+-------------+
| common_name |
+-------------+
| Zambia |
| Zimbabwe |
+-------------+
2 rows in set (0.00 sec)
Select records ending with a specific letter (E.g. countries ending with 'c')
SELECT common_name FROM
country
WHERE common_name LIKE '%c';
/*+--------------------------+
| common_name |
+--------------------------+
| Central African Republic |
| Czech Republic |
| Dominican Republic |
+--------------------------+
3 rows in set (0.00 sec)
Select records that contain a set of letters (E.g. 'man')
SELECT common_name FROM
country
WHERE common_name LIKE '%man%';
/*+----------------+
| common_name |
+----------------+
| Germany |
| Isle of Man |
| Cayman Islands |
| Oman |
| Romania |
+----------------+
5 rows in set (0.00 sec)
GROUP records by a column (e.g. group by currency code)
SELECT iso4217_currency_code, COUNT(iso4217_currency_code) AS count FROM
country
GROUP BY iso4217_currency_code;
/*+-----------------------+-------+
| iso4217_currency_code | count |
+-----------------------+-------+
| | 6 |
| AED | 1 |
| AFN | 1 |
| ALL | 1 |
| AMD | 1 |
| ANG | 1 |
| AOA | 1 |
| ARS | 1 |
... <- snipped ->
ORDER the output and LIMIT to top 10
SELECT iso4217_currency_code, COUNT(iso4217_currency_code) AS count FROM
country
GROUP BY iso4217_currency_code
ORDER BY count DESC
LIMIT 10;
/*+-----------------------+-------+
| iso4217_currency_code | count |
+-----------------------+-------+
| EUR | 25 |
| USD | 14 |
| XOF | 8 |
| XCD | 7 |
| | 6 |
| XAF | 6 |
| AUD | 6 |
| NZD | 5 |
| XPF | 3 |
| SHP | 3 |
+-----------------------+-------+
10 rows in set (0.00 sec)
Find all records LIKE a search pattern (e.g. 7 letters starting with 'A')
SELECT common_name, iso4217_currency_code FROM
country
WHERE common_name LIKE 'A______';
/*+-------------+-----------------------+
| common_name | iso4217_currency_code |
+-------------+-----------------------+
| Andorra | EUR |
| Albania | ALL |
| Armenia | AMD |
| Austria | EUR |
| Algeria | DZD |
+-------------+-----------------------+
5 rows in set (0.00 sec)
Multiple WHERE clauses using AND
SELECT common_name, iso4217_currency_code FROM
country
WHERE common_name LIKE 'A______'
AND iso4217_currency_code = 'EUR';
/*+-------------+-----------------------+
| common_name | iso4217_currency_code |
+-------------+-----------------------+
| Andorra | EUR |
| Austria | EUR |
+-------------+-----------------------+
2 rows in set (0.00 sec)
Multiple WHERE clauses using OR
SELECT common_name, iso4217_currency_code FROM
country
WHERE common_name LIKE 'A______'
OR iso4217_currency_code = 'EUR';
/*+---------------------------+-----------------------+
| common_name | iso4217_currency_code |
+---------------------------+-----------------------+
| Andorra | EUR |
| Albania | ALL |
| Armenia | AMD |
| Austria | EUR |
| Aland | EUR |
| Belgium | EUR |
| Germany | EUR |
| Algeria | DZD |
| Spain | EUR |
| Finland | EUR |
| France | EUR |
| French Guiana | EUR |
| Guadeloupe | EUR |
| Greece | EUR |
| Ireland | EUR |
| Italy | EUR |
| Luxembourg | EUR |
| Monaco | EUR |
| Montenegro | EUR |
| Martinique | EUR |
| Netherlands | EUR |
| Saint Pierre and Miquelon | EUR |
| Portugal | EUR |
| Reunion | EUR |
| Slovenia | EUR |
| San Marino | EUR |
| Vatican City | EUR |
| Mayotte | EUR |
+---------------------------+-----------------------+
28 rows in set (0.00 sec)
Multiple clauses on the same column
SELECT common_name, iso4217_currency_code, COUNT(iso4217_currency_code) FROM
country
WHERE common_name LIKE 'A______'
OR iso4217_currency_code = 'EUR'
GROUP BY iso4217_currency_code
ORDER BY iso4217_currency_code;
/*+-------------+-----------------------+------------------------------+
| common_name | iso4217_currency_code | COUNT(iso4217_currency_code) |
+-------------+-----------------------+------------------------------+
| Albania | ALL | 1 |
| Armenia | AMD | 1 |
| Algeria | DZD | 1 |
| Andorra | EUR | 25 |
+-------------+-----------------------+------------------------------+
4 rows in set (0.00 sec)
Aggregation using a subquery
(using a different table: this time with City table)
SELECT * FROM
city
WHERE latitude = (
SELECT MIN(latitude) FROM
city
);
/*+-----+---------------+---------------+-------------------------------------+------------+-------------+
| id | latitude | longitude | name | country_id | province_id |
+-----+---------------+---------------+-------------------------------------+------------+-------------+
| 184 | -89.982893860 | 139.266992600 | Amundsen–Scott South Pole Station | 1 | 1 |
+-----+---------------+---------------+-------------------------------------+------------+-------------+
1 row in set (0.00 sec)
Find MAX using subquery
SELECT id, name,latitude, longitude FROM
city
WHERE latitude = (
SELECT MAX(latitude) FROM
city
);
/*+------+-------+--------------+---------------+
| id | name | latitude | longitude |
+------+-------+--------------+---------------+
| 1470 | Alert | 82.483323180 | -62.249983560 |
+------+-------+--------------+---------------+
1 row in set (0.01 sec)
Merging tables
The following examples use three tables including 1) Country (used in the examples above), 2) Province and 3) City.
Example data of country table
SELECT * FROM country LIMIT 5;
/*+----+----------------------+------------------+------------------------------+-----------------------+------------------+------------------------+------------------------+
| id | common_name | captial | formal_name | iso4217_currency_code | iso4217_currency | iso3166_1_2Letter_code | iso3166_1_3Letter_code |
+----+----------------------+------------------+------------------------------+-----------------------+------------------+------------------------+------------------------+
| 1 | Antarctica | | Antarctica | | | AQ | ATA |
| 2 | Ascension | Georgetown | | SHP | Pound | AC | ASC |
| 3 | Andorra | Andorra la Vella | Principality of Andorra | EUR | Euro | AD | AND |
| 4 | United Arab Emirates | Abu Dhabi | United Arab Emirates | AED | Dirham | AE | ARE |
| 5 | Afghanistan | Kabul | Islamic State of Afghanistan | AFN | Afghani | AF | AFG |
+----+----------------------+------------------+------------------------------+-----------------------+------------------+------------------------+------------------------+
5 rows in set (0.00 sec)
Example data of province table (one foreign key: country_id)
SELECT * FROM province LIMIT 5;
/*+----+----------+------------+
| id | name | country_id |
+----+----------+------------+
| 1 | | 12 |
| 2 | La Rioja | 11 |
| 3 | Calarasi | 183 |
| 4 | Fromager | 43 |
| 5 | Okinawa | 110 |
+----+----------+------------+
5 rows in set (0.00 sec)
Example data of City table (two foreign keys: country_id and province_id)
SELECT * FROM city LIMIT 5;
/*+----+--------------+--------------+-------------+------------+-------------+
| id | latitude | longitude | name | country_id | province_id |
+----+--------------+--------------+-------------+------------+-------------+
| 1 | 34.983000130 | 63.133299640 | Qal eh-ye | 5 | 1982 |
| 2 | 34.516701100 | 65.250000630 | Chaghcharan | 5 | 646 |
| 3 | 31.582998020 | 64.359999550 | Lashkar Gah | 5 | 591 |
| 4 | 31.112001080 | 61.886997520 | Zaranj | 5 | 1959 |
| 5 | 32.633298150 | 65.866698650 | Tarin Kowt | 5 | 113 |
+----+--------------+--------------+-------------+------------+-------------+
JOIN records by foreign key
SELECT city.name, country.common_name FROM
city, country
WHERE city.country_id = country.id
ORDER BY common_name DESC
LIMIT 10;
/*+----------------+-------------+
| name | common_name |
+----------------+-------------+
| Mazowe | Zimbabwe |
| Shamva | Zimbabwe |
| Victoria Falls | Zimbabwe |
| Zvishavane | Zimbabwe |
| Kwekwe | Zimbabwe |
| Plumtree | Zimbabwe |
| Beitbridge | Zimbabwe |
| Gwanda | Zimbabwe |
| Chiredzi | Zimbabwe |
| Masvingo | Zimbabwe |
+----------------+-------------+
10 rows in set (0.00 sec)
JOIN 3 tables using 2 foreign keys in a single table
E.g. join city, country and province using the foreign keys in the City table
SELECT city.name, country.common_name, province.name
FROM city, country, province
WHERE city.country_id = country.id
AND city.province_id = province.id
AND province.name LIKE '%New South Wales%'
LIMIT 10;
/*+---------------+-------------+-----------------+
| name | common_name | name |
+---------------+-------------+-----------------+
| Central Coast | Australia | New South Wales |
| Bourke | Australia | New South Wales |
| Tweed Heads | Australia | New South Wales |
| Ivanhoe | Australia | New South Wales |
| Wilcannia | Australia | New South Wales |
| Merimbula | Australia | New South Wales |
| Echuca | Australia | New South Wales |
| Deniliquin | Australia | New South Wales |
| Nowra | Australia | New South Wales |
| Ulladulla | Australia | New South Wales |
+---------------+-------------+-----------------+
10 rows in set (0.00 sec)
Left Join
SELECT city.name, country.common_name
FROM city
JOIN country
ON city.country_id = country.id
WHERE city.name LIKE '%Z'
LIMIT 10;
/*+-------------------------------+-------------+
| name | common_name |
+-------------------------------+-------------+
| Kondoz | Afghanistan |
| Gardiz | Afghanistan |
| Ambriz | Angola |
| Coronel Suarez | Argentina |
| Villa Carlos Paz | Argentina |
| Joaquin V. Gonzalez | Argentina |
| Ingeniero Guillermo N. Juarez | Argentina |
| La Paz | Argentina |
| Juarez | Argentina |
| Bregenz | Austria |
+-------------------------------+-------------+
10 rows in set (0.00 sec)
Union
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;