【基礎知識編】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を作成する

MySQL :: Other MySQL Documentation

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

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

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

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

基礎

前提知識

  • mysqlはリレーショナルデータベース(RDB ※以下単にデータベース、ないしDBと記載する)を操作するための言語のひとつ
  • データベースはテーブル(表)からなり、テーブルはレコード(行)とカラム(列)からなる
  • エクセルでいうと、データべースがBook、テーブルがSheetのようなイメージ
  • カラムはカラム名とデータ型で構成されている
  • データの取得操作(SELECT)が一番重要
  • 使用できるデータ型
    MySQL :: MySQL 8.0 リファレンスマニュアル :: 11 データ型
  • 各種キー
    • 目的に応じて特定のカラムに付与される情報のこと
    • 主キー
      • レコードを一意に特定するための主キーの候補となりえる情報。また、非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の練習問題を解くと結構身につきます

SQLBolt - Learn SQL - Introduction to SQL
SQLBolt provides a set of interactive lessons and exercises to help you learn SQL

MySQL学習におすすめの書籍

MySQL徹底入門 第4版 MySQL 8.0対応 [ yoku0825 ]
楽天ブックス
¥ 4,180(2022/08/05 21:32時点)
タイトルとURLをコピーしました