【基礎知識編】mysql入門

環境

windows10
macOS Monterey 12.0.1
DockerDesktop 4.6.1
mysql8.0

環境構築

Dockerでmysqlとphpmyadminの環境を構築します

ソースコードの取得

git clone https://github.com/masayan1126/mysql-practice.git

cd mysql-practice

コンテナ起動

docker-compose up -d

phpMyAdminの確認

ブラウザでhttp://localhost:13000/を開き、ログインしておく

サンプルDBを作成する

https://dev.mysql.com/doc/index-other.html

上記のリンクから world databaseのzipファイルをダウンロードし、phpmyadminからsqlファイルをインポート

上記を行うと、worldというデータベースが作成され、3つのテーブルが作成されるとともに、サンプルデータが挿入されます

これで準備は完了です。DBの構成はざっと以下の通りです

country
- 主キー
  Code
city
- 主キー
  ID
- 外部キー
  CountryCode
countrylanguage
- 主キー
  CountryCode
  Language
- 外部キー
  CountryCode

基礎

前提知識

mysqlはリレーショナルデータベース（RDB　※以下単にデータベース、ないしDBと記載する）を操作するための言語のひとつ
データベースはテーブル(表)からなり、テーブルはレコード(行)とカラム(列)からなる
エクセルでいうと、データべースがBook、テーブルがSheetのようなイメージ
カラムはカラム名とデータ型で構成されている
データの取得操作(SELECT)が一番重要
使用できるデータ型
https://dev.mysql.com/doc/refman/8.0/ja/data-types.html
各種キー
- 目的に応じて特定のカラムに付与される情報のこと
- 主キー
  - レコードを一意に特定するための主キーの候補となりえる情報。また、非null
  - 基本的にシステムによって一意な値が割り振られ保存される（もともとからあるカラムだと一意に特定しにくいため
- 候補キー
  レコードを一意に特定するための主キーの候補となりえる情報。複数存在する可能性がある
- 代理キー
  候補キーのうち、主キーとされなかったキー
- 複合キー
  複数の項目を組み合わせてデータが一意に識別できる情報
- 外部キー
  複数のテーブルの関係を結び付けるためのキー(リレーションシップ)
性別などの文字列型でも数値型でも可能なカラム
- 数値型にした方がデータベースの容量節約になる
- ISO5218によると以下の通り
  - 0.不明, 1.男.2, 女, 9.適用不能
  - カラム名としては、genderではなくsexを使用する
真偽値のtrueは1、falseは0として扱われる
コメントアウト
- #select ～
- -- select ～

レコードの検索

select文を使用する
countryテーブルの全件取得
- アスタリスクで全カラム
  カラムを指定する場合よりも処理が遅くなる
```
select * from country
```
- カラム指定
```
select Code, Name,Population from country
```

selectの結果もテーブル形式(=selectは、テーブルから別のテーブルを作成する処理)
limit句で取得するレコードの件数を制限することが可能
制限かけてない場合と比べて、処理が速くなる
```
SELECT * FROM city ORDER BY Population DESC LIMIT 5
```

カラム、テーブルの別名

countryテーブルからカラム指定(国コード、国名、GNP)でレコードを取得

as句でカラム名、テーブル名に別名を付けることが可能

カラム

select Code, Name, GNP as "国民総生産", Population from country

テーブル

select Code, Name, Population from country as c

また、asは省略可能

カラム

select Code, Name, GNP "国民総生産", Population from country

テーブル

select Code, Name, Population from country c

並び替え

orderby
- ascが昇順、descが降順
- 未指定なら昇順
- countryテーブルのGNP降順で取得
```
select * from country order by GNP DESC
```

条件指定

等価演算は一般的なプログラミング言語とは違ってイコール1つでOK
whereでcountryテーブルの国コードがFRAの国のレコードを取得
```
SELECT CODE, NAME, Population FROM country WHERE CODE = "FRA"
```
andで指定した全条件を満たす場合に真(true)とする
cityテーブルから国コードがJPNで、かつ人口が100万人以上の都市
```
select * from city where CountryCode = "JPN" and Population >= 1000000
```
orで指定した全条件のうちいずれかを満たす場合に真(true)とする
cityテーブルから国コードがJPNで、または人口が100万人以上の都市
```
select * from city where CountryCode = "JPN" or Population >= 1000000
```
betweenで範囲指定
cityテーブルから人口が50万～100万の都市
```
select * from city where Population between 500000 and 1000000
```
in(指定した値のリストと一致するレコード)
countryテーブルのうち、独立年が1991,1973,1950のレコード
```
select * from country where IndepYear in (1991, 1973, 1950)
```
notで否定
countryテーブルのうち、独立年が1991,1973,1950以外のレコード
```
select * from country where IndepYear not in (1991, 1973, 1950)
```
likeで部分一致
- %でワイルドカード指定
  countryテーブルのうち、国家元首の名前がe(E)から始まるレコード
```
select * from country where HeadOfState like "e%”
```
is nullとis not null
countryテーブルのうち、がNULLのレコードを取得
```
select * from country where IndepYear is null
```
countryテーブルのうち、がNULLではないレコードを取得
```
select * from country where IndepYear is not null
```

レコード数のカウント

countでレコード数のカウント
countryテーブルのうち、独立年が1991,1973,1950のレコードの件数
```
select count(*) from country where IndepYear in (1991, 1973, 1950)
```

応用

グルーピングと集計

group by
- 注意するべきエラー
  - SELECT list is not in GROUP BY clause and contains nonaggregated column ‘（フィールド名）’ which is not functionally dependent on columns in GROUP BY clause
- 例えば、以下のようなsql文で上記のエラーが生じる可能性があります
```
SELECT Name, SUM(Population) Population FROM city group by CountryCode
-- group by句でNameが指定されていないにもかかわらず、selectでNameが指定されている。
```
- 上記のエラーは下記の2点を理解し、守っていれば発生しない
  - group by句に指定する基準のカラムが、SELECTのカラム指定に含まれていること
  - ただし、MAX() や MIN() などの集計関数を使っていればSELECTのカラム指定に含まれていなくてもOK
合計
- sum
  cityテーブルをCountryCode単位でグループ化して、グループごとの人口の合計と、CountryCodeをカラムにもつレコードを取得
```
SELECT CountryCode, SUM(Population) Population FROM city group by CountryCode
```
平均
- ave
  cityテーブルをCountryCode単位でグループ化して、グループごとの人口の平均と、CountryCodeをカラムにもつレコードを取得
```
SELECT CountryCode, AVG(Population) Population FROM city group by CountryCode
```
最大/最小
- max
  cityテーブルをCountryCode単位でグループ化して、グループごとの人口の最大値と、CountryCodeをカラムにもつレコードを取得
```
SELECT CountryCode, MAX(Population) Population FROM city group by CountryCode
```
- min
  cityテーブルをCountryCode単位でグループ化して、グループごとの人口の最小値と、CountryCodeをカラムにもつレコードを取得
```
SELECT CountryCode, MIN(Population) Population FROM city group by CountryCode
```
having
- 集計した値を更に条件で絞る
- whereでは、group byする前のテーブルに対して条件指定がされるので、group byした後のテーブルに対してさらに条件指定で絞りたい場合はhaving
  cityテーブルをCountryCode単位でグループ化して、グループごとの人口の合計と、CountryCodeをカラムにもつレコードを取得し、さらに人口の合計が3000より大きいレコード
```
select CountryCode, sum(Population) Population from city group by CountryCode having Population < 3000
```

結合

内部結合

JOIN(INNER JOIN)
結合条件(on)をもとに、両方のテーブルに存在しているレコードのみ返す
外部結合より高速

countryテーブルとcityテーブルをCountryCodeで内部結合

SELECT FROM country JOIN city ON country.Code = city.CountryCode

joinの結果重複したレコードがある場合はDISTINCTで重複行を削除できる
例えば、countryテーブルとcityテーブルをCountryCodeで内部結合し、cityのDistrict(日本の場合は都道府県)を取得するとすると、以下のようにレコードが重複します
```
select country.Code, country.Name, city.District from country join city on country.Code = city.CountryCode;
```
重複を削除する場合は以下のようにします
```
select distinct country.Code, country.Name, city.District from country join city on country.Code = city.CountryCode;
```

外部結合

左外部結合(LEFT OUTER JOIN)と右外部結合(RIGHT OUTER JOIN)がある
結合条件(on)をもとに、両方のテーブルに存在しているレコードも片方しか存在していないレコードも返す
説明用のため、cityテーブルからCountryCodeがVIRのレコードを消す(countryにはCountryCodeがVIRのレコードがあるが、cityにはない状態)

LEFT OUTER JOIN(左側のテーブルが軸)

下記例だと、countryを軸にするので、cityにはCountryCodeがVIRのレコードがないが、countryにはあるので、結果に含まれる(nullで)

countryテーブルとcityテーブルをCountryCodeで左外部結合

SELECT FROM country LEFT OUTER JOIN city ON country.Code = city.CountryCode

RIGHT OUTER JOIN(右側のテーブルが軸)

下記例だと、cityを軸にするので、cityにはCountryCodeがVIRのレコードがないため、結果から省かれる

countryテーブルとcityテーブルをCountryCodeで右外部結合

SELECT FROM country RIGHT OUTER JOIN city ON country.Code = city.CountryCode

条件分岐

case

クエリ内で条件分岐がかける
人口が1億より多い国をA, 5000万以上1億未満をB,それ以外をCとしてカラム(Group)を追加して表示する

-- 一億以上ならA,5000万以上1億未満ならB,それ以外C

SELECT
*,
CASE 
  WHEN Population >= 100000000 THEN "A" 
  WHEN Population < 100000000 AND Population >= 50000000 THEN "B" 
  ELSE "C"
END AS "population_group"
FROM
  country

サブクエリ

select文のカラム指定の箇所や、where句、from句に別のクエリ結果を指定することが可能
クエリがわかりにくくなるのと、実行速度が遅くなるのでできる限り使用しないほうが良い。

countryテーブルから人口が平均以上の国を抽出する

SELECT
*
FROM
  country
WHERE
  Population > (
    SELECT
      AVG(Population)
    FROM
     country
  )

上記のような、外側のクエリの値をサブクエリ内で使用しているクエリのことを相関サブクエリという

おまけ

下記サイトで、sqlの練習問題を解くと結構身につきます

まとめ

いかがでしたでしょうか。本記事では、mysql入門ということで、mysqlを操作するための環境構築から基礎知識や基本的な操作方法、応用的な使い方まで一気に説明しています。