MySQL select 文いろいろ

基本的な select

mysql> select * from city limit 10;
+----+----------------+-------------+---------------+------------+
| ID | Name           | CountryCode | District      | Population |
+----+----------------+-------------+---------------+------------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 |
|  3 | Herat          | AFG         | Herat         |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |
|  6 | Rotterdam      | NLD         | Zuid-Holland  |     593321 |
|  7 | Haag           | NLD         | Zuid-Holland  |     440900 |
|  8 | Utrecht        | NLD         | Utrecht       |     234323 |
|  9 | Eindhoven      | NLD         | Noord-Brabant |     201843 |
| 10 | Tilburg        | NLD         | Noord-Brabant |     193238 |
+----+----------------+-------------+---------------+------------+
10 rows in set (0.00 sec)

select (リテラル、ビルトイン関数)

mysql> select ID, 'Hello', Population * 2, LOWER(District) from city limit 10;
+----+-------+----------------+-----------------+
| ID | Hello | Population * 2 | LOWER(District) |
+----+-------+----------------+-----------------+
|  1 | Hello |        3560000 | kabol           |
|  2 | Hello |         475000 | qandahar        |
|  3 | Hello |         373600 | herat           |
|  4 | Hello |         255600 | balkh           |
|  5 | Hello |        1462400 | noord-holland   |
|  6 | Hello |        1186642 | zuid-holland    |
|  7 | Hello |         881800 | zuid-holland    |
|  8 | Hello |         468646 | utrecht         |
|  9 | Hello |         403686 | noord-brabant   |
| 10 | Hello |         386476 | noord-brabant   |
+----+-------+----------------+-----------------+
10 rows in set (0.00 sec)

テーブルからデータを取得しない select

mysql> select version(), user(), database();
+-----------+----------------+------------+
| version() | user()         | database() |
+-----------+----------------+------------+
| 5.7.34    | root@localhost | world      |
+-----------+----------------+------------+
1 row in set (0.00 sec)

列エイリアスをつける

mysql> select ID, 'Hello' greet, Population * 2 double_population, LOWER(District) district_lower from city limit 10;
+----+-------+-------------------+----------------+
| ID | greet | double_population | district_lower |
+----+-------+-------------------+----------------+
|  1 | Hello |           3560000 | kabol          |
|  2 | Hello |            475000 | qandahar       |
|  3 | Hello |            373600 | herat          |
|  4 | Hello |            255600 | balkh          |
|  5 | Hello |           1462400 | noord-holland  |
|  6 | Hello |           1186642 | zuid-holland   |
|  7 | Hello |            881800 | zuid-holland   |
|  8 | Hello |            468646 | utrecht        |
|  9 | Hello |            403686 | noord-brabant  |
| 10 | Hello |            386476 | noord-brabant  |
+----+-------+-------------------+----------------+
10 rows in set (0.00 sec)

重複の削除
いくつか重複しているcountryCodeに対して

mysql> select countryCode from city limit 10;
+-------------+
| countryCode |
+-------------+
| ABW         |
| AFG         |
| AFG         |
| AFG         |
| AFG         |
| AGO         |
| AGO         |
| AGO         |
| AGO         |
| AGO         |
+-------------+
10 rows in set (0.00 sec)

distinct で重複を削除

mysql> select distinct countryCode from city limit 10;
+-------------+
| countryCode |
+-------------+
| ABW         |
| AFG         |
| AGO         |
| AIA         |
| ALB         |
| AND         |
| ANT         |
| ARE         |
| ARG         |
| ARM         |
+-------------+
10 rows in set (0.00 sec)