LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@toshikawa(川俣 利久)

【SQL】MySQL公式サンプルデータベースを使って検索してみる

Posted at

https://qiita.com/yukibe/items/fc6016348ecf4f3b10bf
を教えてもらったので、ググってでてきた
https://gist.github.com/mdang/9a4a8063ebea3b829b8025746643ade1
の問題をやってみる。

データの確認

city

desc_city
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

MULについては
https://qiita.com/qyuser/items/2c535888b6a899685821
を参照。

country

desc_country
mysql> desc country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field          | Type                                                                                  | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code           | char(3)                                                                               | NO   | PRI |         |       |
| Name           | char(52)                                                                              | NO   |     |         |       |
| Continent      | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO   |     | Asia    |       |
| Region         | char(26)                                                                              | NO   |     |         |       |
| SurfaceArea    | decimal(10,2)                                                                         | NO   |     | 0.00    |       |
| IndepYear      | smallint                                                                              | YES  |     | NULL    |       |
| Population     | int                                                                                   | NO   |     | 0       |       |
| LifeExpectancy | decimal(3,1)                                                                          | YES  |     | NULL    |       |
| GNP            | decimal(10,2)                                                                         | YES  |     | NULL    |       |
| GNPOld         | decimal(10,2)                                                                         | YES  |     | NULL    |       |
| LocalName      | char(45)                                                                              | NO   |     |         |       |
| GovernmentForm | char(45)                                                                              | NO   |     |         |       |
| HeadOfState    | char(60)                                                                              | YES  |     | NULL    |       |
| Capital        | int                                                                                   | YES  |     | NULL    |       |
| Code2          | char(2)                                                                               | NO   |     |         |       |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)

countrylanguage

desc_coutnrylanguage
mysql> desc countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | decimal(4,1)  | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

エクササイズ

  • Using count, get the number of cities in the USA
  • Find out what the population and average life expectancy for people in Argentina (ARG) is
  • Using IS NOT NULL, ORDER BY, LIMIT, what country has the highest life expectancy?
  • Using LEFT JOIN, ON, what is the capital of Spain (ESP)?
  • Using LEFT JOIN, ON, list all the languages spoken in the 'Southeast Asia' region

BONUS

  • Select 25 cities around the world that start with the letter 'F' in a single SQL query.

Using count, get the number of cities in the USA

count_USA
mysql> select count(*) as count, CountryCode from city where CountryCode="USA";
+-------+-------------+
| count | CountryCode |
+-------+-------------+
|   274 | USA         |
+-------+-------------+
1 row in set (0.00 sec)

解説

cityテーブルにCountryCode列があるので、WHEREで抽出してみた。

Find out what the population and average life expectancy for people in Argentina (ARG) is

ARG_popurlation&LifeExpectancy
mysql> select Name,Population,LifeExpectancy from country 
     where Code="ARG";
+-----------+------------+----------------+
| Name      | Population | LifeExpectancy |
+-----------+------------+----------------+
| Argentina |   37032000 |           75.1 |
+-----------+------------+----------------+
1 row in set (0.00 sec)

解説

こんどはcountryテーブルからCodeを指定して、PopulationとLifeExpectancyを抽出

ここまでは、どの列にどんな値が入っているかわかれば簡単。

Using IS NOT NULL, ORDER BY, LIMIT, what country has the highest life expectancy?

Highest_LifeExpectancy
mysql> select Name,LifeExpectancy 
     from country
     where LifeExpectancy IS NOT NULL
     ORDER BY LifeExpectancy desc
     LIMIT 1;
+---------+----------------+
| Name    | LifeExpectancy |
+---------+----------------+
| Andorra |           83.5 |
+---------+----------------+
1 row in set (0.00 sec)

解説

普段はSplunkなのでIS NOT NULLってどう使うのかわからなかった:sweat:

is_not_null
mysql> help is not null;
Name: 'IS NOT NULL'
Description:
Syntax:
IS NOT NULL

Tests whether a value is not NULL.

URL: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html

Examples:
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1, 1, 0

NULLじゃなければTRUEとのこと

Using LEFT JOIN, ON, what is the capital of Spain (ESP)?

capital_ESP
mysql> select country.Name,city.Name 
    from country LEFT JOIN city
    ON city.ID=country.Capital
    where country.Code="ESP"; 
+-------+--------+
| Name  | Name   |
+-------+--------+
| Spain | Madrid |
+-------+--------+
1 row in set (0.00 sec)

解説

結果的には
http://www.innovato.com/splunk/SQLSplunk.html
ここに載っていたけど、ONで条件を書く必要があった。

Using LEFT JOIN, ON, list all the languages spoken in the 'Southeast Asia' region

laungage
mysql> select countrylanguage.Language,country.Name,country.Region 
    from country LEFT JOIN countrylanguage
    ON countrylanguage.CountryCode=country.Code
    where country.Region="Southeast Asia";
+---------------+-------------+----------------+
| Language      | Name        | Region         |
+---------------+-------------+----------------+
| Chinese       | Brunei      | Southeast Asia |
| English       | Brunei      | Southeast Asia |
| Malay         | Brunei      | Southeast Asia |
| Malay-English | Brunei      | Southeast Asia |
| Bali          | Indonesia   | Southeast Asia |
| Banja         | Indonesia   | Southeast Asia |
| Batakki       | Indonesia   | Southeast Asia |
| Bugi          | Indonesia   | Southeast Asia |
| Javanese      | Indonesia   | Southeast Asia |
| Madura        | Indonesia   | Southeast Asia |
| Malay         | Indonesia   | Southeast Asia |
| Minangkabau   | Indonesia   | Southeast Asia |
| Sunda         | Indonesia   | Southeast Asia |
| Chinese       | Cambodia    | Southeast Asia |
| Khmer         | Cambodia    | Southeast Asia |
| Tšam          | Cambodia    | Southeast Asia |
| Vietnamese    | Cambodia    | Southeast Asia |
| Lao           | Laos        | Southeast Asia |
| Lao-Soung     | Laos        | Southeast Asia |
| Mon-khmer     | Laos        | Southeast Asia |
| Thai          | Laos        | Southeast Asia |
| Burmese       | Myanmar     | Southeast Asia |
| Chin          | Myanmar     | Southeast Asia |
| Kachin        | Myanmar     | Southeast Asia |
| Karen         | Myanmar     | Southeast Asia |
| Kayah         | Myanmar     | Southeast Asia |
| Mon           | Myanmar     | Southeast Asia |
| Rakhine       | Myanmar     | Southeast Asia |
| Shan          | Myanmar     | Southeast Asia |
| Chinese       | Malaysia    | Southeast Asia |
| Dusun         | Malaysia    | Southeast Asia |
| English       | Malaysia    | Southeast Asia |
| Iban          | Malaysia    | Southeast Asia |
| Malay         | Malaysia    | Southeast Asia |
| Tamil         | Malaysia    | Southeast Asia |
| Bicol         | Philippines | Southeast Asia |
| Cebuano       | Philippines | Southeast Asia |
| Hiligaynon    | Philippines | Southeast Asia |
| Ilocano       | Philippines | Southeast Asia |
| Maguindanao   | Philippines | Southeast Asia |
| Maranao       | Philippines | Southeast Asia |
| Pampango      | Philippines | Southeast Asia |
| Pangasinan    | Philippines | Southeast Asia |
| Pilipino      | Philippines | Southeast Asia |
| Waray-waray   | Philippines | Southeast Asia |
| Chinese       | Singapore   | Southeast Asia |
| Malay         | Singapore   | Southeast Asia |
| Tamil         | Singapore   | Southeast Asia |
| Chinese       | Thailand    | Southeast Asia |
| Khmer         | Thailand    | Southeast Asia |
| Kuy           | Thailand    | Southeast Asia |
| Lao           | Thailand    | Southeast Asia |
| Malay         | Thailand    | Southeast Asia |
| Thai          | Thailand    | Southeast Asia |
| Portuguese    | East Timor  | Southeast Asia |
| Sunda         | East Timor  | Southeast Asia |
| Chinese       | Vietnam     | Southeast Asia |
| Khmer         | Vietnam     | Southeast Asia |
| Man           | Vietnam     | Southeast Asia |
| Miao          | Vietnam     | Southeast Asia |
| Muong         | Vietnam     | Southeast Asia |
| Nung          | Vietnam     | Southeast Asia |
| Thai          | Vietnam     | Southeast Asia |
| Tho           | Vietnam     | Southeast Asia |
| Vietnamese    | Vietnam     | Southeast Asia |
+---------------+-------------+----------------+
65 rows in set (0.00 sec)

解説

continent
mysql> select * from country where Continent="Asia";

まず、どこの列にSoutheast Asiaがあるのかわからなかったので、先のクエリーで検索。

Regionにあることがわかったので、そこからクエリーを組み立てた。

Select 25 cities around the world that start with the letter 'F' in a single SQL query.

25F_city
mysql> select Name,CountryCode from city where Name like 'F%' limit 25;
+---------------------------+-------------+
| Name                      | CountryCode |
+---------------------------+-------------+
| Fagatogo                  | ASM         |
| Florencio Varela          | ARG         |
| Formosa                   | ARG         |
| Francistown               | BWA         |
| Fortaleza                 | BRA         |
| Feira de Santana          | BRA         |
| Franca                    | BRA         |
| Florianópolis             | BRA         |
| Foz do Iguaçu             | BRA         |
| Ferraz de Vasconcelos     | BRA         |
| Francisco Morato          | BRA         |
| Franco da Rocha           | BRA         |
| Fuenlabrada               | ESP         |
| Faridabad                 | IND         |
| Firozabad                 | IND         |
| Farrukhabad-cum-Fatehgarh | IND         |
| Faizabad                  | IND         |
| Fatehpur                  | IND         |
| Firenze                   | ITA         |
| Foggia                    | ITA         |
| Ferrara                   | ITA         |
| Forlì                     | ITA         |
| Fukuoka                   | JPN         |
| Funabashi                 | JPN         |
| Fukuyama                  | JPN         |
+---------------------------+-------------+
25 rows in set (0.00 sec)

解説

LIKEはSplunkと変わりがないので、そのまま検索。

まとめ

どの列のどんな値が入っているのかの調べ方がまだまだ弱いので、そこらへんを重点的に調べていきたいと思います。

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?