はじめに みなさん、プログラム内で週番号を扱ったことがあるでしょうか。 例えば、2021年の23週目、という形でデータを持つことを考えます。 その場合に注意するべきなのは、週の初めを1周として扱うのか、0週として扱うのか、ということです。 しかも、プログラミング言語ごとに週番号の扱い方が異なるので注意が必要です。 しっかりと理解しておかないと、かなり危険なのでまとめたいと思います。 今回の記事では週の始まりを月曜日として考えます。 Pythonにおける週番号の扱い Pythonのリファレンスでは以下となっています。 strftime() と strptime() の書式コード %W 0埋めした10進数で表記した年中の週番号 (週の始まりは月曜日とする)。新年の最初の月曜日に先立つ日は 0週に属するとします。 つまり、1月1日が月曜日かどうかでその年が0週から始まるか、1週から始めるのかが変化する、ということになります。 Pythonで日付から週番号を取得する場合 では、日付から週番号を取得してみましょう。 from datetime import datetime # 2021年の1月1日は金曜日 date1 = '2021-01-01' #2024年の1月1日は月曜日 date2 = '2024-01-01' # result: date1 week number: 0 print(f'date1 week number: {int(datetime.strptime(date1,"%Y-%m-%d").strftime("%W"))}') # result: date1 week number: 1 print(f'date2 week number: {int(datetime.strptime(date2,"%Y-%m-%d").strftime("%W"))}') 上記のコードの結果を解説すると、2021年1月1日の週番号は0週として取得され、2024年1月1日の週番号は1週として取得される、ということになります。 Pythonで週番号から日付を取得する場合 次は週番号から週のはじめの日付を取得してみましょう。 先ほどの結果でPythonにおいて2021年は0週から始まり、2024年は1週から始まる、ということが分かりました。 では、2021年と2024年の第2週目の週の始まりの日付を求めてみます。 from datetime import datetime # 2021年の2週目 date1 = '2021-W2' # 2024年の2週目 date2 = '2024-W2' # result: 2021-01-11 00:00:00 print(datetime.strptime(f'{date1}-1', '%Y-W%W-%w')) # result: 2024-01-08 00:00:00 print(datetime.strptime(f'{date2}-1', '%Y-W%W-%w')) コードでは分かりにくいので、図で説明します。 以下の図では、赤枠が0週目、緑枠が1週目、黄色枠が2週目、となります。 1月1日が含まれる週を1週目とする、という考えではなく年の初めの月曜日が含まれる週を1週目とする、という考えなので、単純に週番号を考えていると週の数え方が年によってずれる、ということが分かるかと思います。 JavaScriptにおける週番号の扱いについて JavaScript(以下JSとする)においてライブラリを使わずに日付を扱うことはないかと思うので、今回はDay.jsの場合に絞って解説します。 Day.jsで日付から週番号を取得する場合 const dayjs = require("dayjs"); const weekOfYear = require("dayjs/plugin/weekOfYear"); const updateLocale = require("dayjs/plugin/updateLocale"); dayjs.extend(weekOfYear); dayjs.extend(weekOfYear); dayjs.extend(updateLocale); dayjs.updateLocale("en", { weekStart: 1, // OPTIONAL, set the start of a week. If the value is 1, Monday will be the start of week instead of Sunday。 yearStart: 1, // OPTIONAL, the week that contains Jan 4th is the first week of the year. }); dayjs.locale("en"); const day1 = "2021-01-01"; const day2 = "2024-01-01"; // result: 1 console.log(dayjs(day1, "YYYY-MM-DD").week()); // result: 1 console.log(dayjs(day2, "YYYY-MM-DD").week()); 上記結果を見ると、2021年と2024年両方とも1月1日は1週目として取得されています。 つまり、1月1日が含まれる週が第1週目として扱うことができている、ということになります。 Day.jsで週番号から日付を取得する場合 次は週番号から週の開始の日付を取得する処理を見てみましょう。 以下のコードでは2021年と2024年の第2週目の週の開始の日付を取得します。 const dayjs = require("dayjs"); const weekOfYear = require("dayjs/plugin/weekOfYear"); const updateLocale = require("dayjs/plugin/updateLocale"); dayjs.extend(weekOfYear); dayjs.extend(weekOfYear); dayjs.extend(updateLocale); dayjs.updateLocale("en", { weekStart: 1, // OPTIONAL, set the start of a week. If the value is 1, Monday will be the start of week instead of Sunday。 yearStart: 1, // OPTIONAL, the week that contains Jan 4th is the first week of the year. }); dayjs.locale("en"); const day1 = "2021"; const week1 = 2; const day2 = "2024"; const week2 = 2; // result: 2021-01-04 console.log(dayjs(day1, "YYYY").week(week1).startOf('week').format("YYYY-MM-DD")); // result: 2024-01-08 console.log(dayjs(day2, "YYYY").week(week2).startOf('week').format("YYYY-MM-DD")); コードだと分かりにくいので、図で説明します。 Day.jsの場合は1月1日が含まれる週が必ず第1週目として扱うことができていることが分かります。 MySQLにおける週番号の扱いについて 次はMySQLにおける週番号の扱いを見ていきます。 日付および時間関数 WEEK(date[,mode]) この関数は、date に対応する週番号を返します。 2 つの引数を取る形式の WEEK() を使用すると、週が日曜日と月曜日のどちらから始まるのか、および戻り値が 0 から 53までと 1 から 53 までのどちらの範囲内であるのかを指定できます 週番号の0~53か1~53かについては、その年の最初の週を0週とするか、前年の53週(52週の場合もある)とするかどうか、という意味になります。 今回はPythonと合わせるため0~53週とします。 MySQLで日付から週番号を取得する場合 SELECT WEEK('2021-01-01',5); # result: 0 SELECT WEEK('2024-01-01',5); # result: 1 上記の結果を見ると分かりますが、Pythonと同じく1月1日が月曜日かどうかで結果が変わります。 2021年は0週から始まり、2024年は1週から始まります。 MySQLで週番号から日付を取得する場合 実際のコードを見てみましょう。 #2021年の第1週目 SELECT STR_TO_DATE('202101 Monday', '%x%v %W'); # result: 2021-01-04 #2024年の第1週目 SELECT STR_TO_DATE('202401 Monday', '%x%v %W'); # result: 2024-01-01 ここで注意すべきは、週番号から日付を取得する場合、月曜日始まりで0周開始の条件で変換することができない、ということです。 理由はDATE_FORMAT関数のmodeが0~3までしか対応していないことが原因です。 DATE_FORMAT(date,format) なので、MySQLのWEEK関数の出力を使ってそのままDATE_FORMAT関数を利用しても正しく処理できない可能性がある、ということに注意してください。 ※MySQLにあまり精通していないので、他の方法があればコメントでご指摘頂きたいです。 まとめ 言語ごとに週番号の扱い方が異なります。 ISOで決められている週番号の管理方法もありますが、第1週が前年度の週として扱われたりと人間がロジックを考える場合は少し考えにくい部分が難点かと思っています。 いずれにしても、しっかりと言語ごとの週番号の管理方法を理解しておかないと、思わぬ落とし穴にはまりますのでご注意ください。

Dockerは起動済み appコンテナ内でマイグレーションを行おうとすると下記のエラーが出る。 Illuminate\Database\QueryExceptionSQLSTATE[HY000] [1045] Access denied for user 'test'@'172.19.0.2' (using password: YES) (SQL: select count(*) as aggregate from `articles`) 結論 docker-compose down --volumes docker-compose build docker-compose up -d このコマンドで Access denied for user 'test'@'172.19.0.2' (using password: YES) 部分のエラーは解決 後半のエラー部分は .env　(laravelの) DB_PORT=3306 docker-compose.yml ports: - '4306:3306' 上記のようにすることで解決。 元々は.envファイルの中のDB_PORTが4306になっていたので3306に修正。 docker-compose.ymlをいじったらコンテナの起動をし直すことを忘れずに。 考えられる原因 前半部分 docker-compose down --volumes このコマンドはコンテナ・イメージ・ボリューム・ネットワーク・未定義コンテナを全てを一括消去するコマンドなので何か履歴が残っていたことが原因？ 後半部分 DBに接続する際のport番号の認識が誤っていたことが原因。 .envのport番号とdocker-compose.ymlの右側のport番号がコンテナのport番号に対応(同じにする) docker-compose.ymlの左側のport番号はホストのport番号(Mk-2やSequel Proに接続する際に入力するport番号はこっち)

HEROKUアプリケーションの確認 HEROKUにログイン heroku login HEROKUのアプリケーション一覧の確認 heroku list HEROKU アプリケーションの作成と削除 HEROKUのページから作成と削除を行うことができますが、コマンドでも可能です。 作成 heroku create <アプリ名> アプリのrename heroku rename <新しい名前> --app <古い名前> 削除 heroku apps:destroy --app アプリ名 ClearDB(MySQL)との接続 localでないDB(MySQL)を使用したい場合は、アプリケーションを作成し、ResourcesのAdd-onesでClearDBを追加します。 次にconfigを確認しプログラム(Go)にDBと接続するための接続情報を加えます。 heroku config --app <アプリ名> 自分でconfigを追加していない場合は基本的にDATABASE_URLが帰ってきます。 DATABASE_URLを分解することでプログラム(Go)をDBに接続することができます。DATABASE_URLのフォーマットが下記のようになっています。 DATABASE_URL='mysql://username:password@hostname/database_name?reconnect=true' 下記の4つがDBに接続するために必要になります。 UserName Password HostName Database_name MacのTerminalで環境変数としてDBの接続情報をセット export DB_USER= \ DB_PASS= \ DB_IP=127.0.0.1 \ DB_PORT=3306 \ DB_NAME= 外部DBへのアクセス 外部のMySQLDBに接続するためのコマンドです。 コマンドにpasswordを使用すると警告が出ますが、まぁ問題ないと思います。 mysql --host=hoge --user=hoge --password=hoge <Database_name> アプリを開く heroku open HEROKU Logの確認 使っていないアプリケーションや現在使用中のアプリケーションのログの取得コマンド heroku logs --tail --app <アプリ名> アプリケーションの再起動 何か動作が怪しい時などにとりあえず再起動します。 heroku restart --app <アプリ名> HEROKUへのデプロイ デプロイ方法1 git push heroku master git push heroku <現在いるブランチ名>:master デプロイ方法2 コンテナでデプロイする方法です。下記の記事を参考にデプロイしました。 正直かなり前にやって以来コンテナでデプロイはしていないのであまり覚えていません。今度の機会に追記したいと思います。 ありがとうございました。

Rails×React×Dockerにて開発環境を構築したので、アウトプット用に残しておきます。 下記の記事を参考にして構築していきました。 DockerでRuby on Rails + Reactを別々にアプリ作成する環境構築手順 Docker使ってReact × Rails(API)の環境構築 前提知識の確認 自分は初学者でrailsでしかWebアプリを構築した事がなかったのですが、Dockerで開発環境を構築する場合は、 rails newをする前に、いくつかのファイルを用意する必要があります。 ※詳しくはこちらが分かり易い Docker 公式ドキュメントの Rails Quickstart 完全解説 簡単に伝えると、プロジェクトのディレクトを作成した後rails newをする前にDocker環境構築用のファイルをいくつか作成した後で、 rails newやcreate-react-app行う。 フォルダ構造は下記のような形からスタートする アプリ名 backend Dockerfile entrypoint.sh Gemfile Gemfile.lock frontend Dockerfile docker-compose.yml ①ファイルを用意していく 上記構造のディレクトリとファイルを作成したら、中身を記述していく。 ※アプリ名と記載がある部分は、ご自分で作成中のアプリ名に変更して下さい。 docker-compose.yml docker-compose.yml version: "3" services: db: image: mariadb command: mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci environment: MYSQL_DATABASE: "アプリ名_development" MYSQL_ROOT_PASSWORD: "password" volumes: - mysql-data:/var/lib/mysql/data - /tmp/dockerdir:/etc/mysql/conf.d/ ports: - 3306:3306 backend: build: context: ./backend/ dockerfile: Dockerfile command: bash -c "rm -f tmp/pids/server.pid && bundle exec rails s -p 3001 -b '0.0.0.0'" volumes: - .:/アプリ名 ports: - "3001:3001" depends_on: - db frontend: build: context: ./frontend/ dockerfile: Dockerfile volumes: - ./frontend:/usr/src/app/frontend working_dir: /usr/src/app/frontend command: sh -c "npm start --host 0.0.0.0 --port 3000" ports: - "3000:3000" stdin_open: true volumes: mysql-data: {} dbには、mysqlを使用しております。 rails(backend)側では、3001番ポートで立ち上がるよう設定しており、react(frontend)側では3000番ポートにしてあります。 docker-compose.yml backend: build: ports: - "3001:3001" frontend: build: ports: - "3000:3000" rails側のファイル用意（全部で4つ） Dockerfile entrypoint.sh Gemfile Gemfile.lock Dockerfile(アプリ名/backend/Dockerfile) Dokcerfile. FROM ruby:2.7.2 RUN apt-get update -qq && apt-get install -y build-essential libpq-dev nodejs RUN mkdir /アプリ名 WORKDIR /アプリ名 COPY Gemfile /アプリ名/Gemfile COPY Gemfile.lock /アプリ名/Gemfile.lock RUN bundle install COPY . /アプリ名 # Add a script to be executed every time the container starts. COPY entrypoint.sh /usr/bin/ RUN chmod +x /usr/bin/entrypoint.sh ENTRYPOINT ["entrypoint.sh"] EXPOSE 3001 # Start the main process. CMD ["rails", "server", "-b", "0.0.0.0"] Gemfile Gemfile. source 'https://rubygems.org' gem 'rails', '5.2.5' railsのバージョンは5.2.5にしてあります。 最近発生していたmimemagi関連のエラーを回避する為ですが、 rails6系が良い方は、6.0.3.6又は6.1.3.1であれば、エラー回避出来るようなので、どちらかを指定して下さい。 ※詳しくは下記を参照下さい RailsのGPL混入問題についてまとめ（mimemagic） Gemfile.lock touch Gemfile.lockで作成したら、空ファイルのままで大丈夫です。 entrypoint.sh entrypoint.sh #!/bin/bash set -e # Remove a potentially pre-existing server.pid for Rails. rm -f /アプリ名/tmp/pids/server.pid # Then exec the container's main process (what's set as CMD in the Dockerfile). exec "$@" これで、rails側の準備はOKです。 react側のファイル用意 Dockerfile(アプリ名/frontend/Dockerfile) FROM node:14.15.1-alpine ※この記述だけで大丈夫です。 ②Dockerコマンドを実行する 順番に実行して下さい。 アプリ名 $ docker-compose run backend rails new . --force --no-deps --database=mysql --api アプリ名 $ docker-compose build アプリ名 $ docker-compose run --rm frontend sh -c "npm install -g create-react-app && create-react-app frontend" 成功すると、見慣れたディレクトリ構造になってるかと思います。 database.yml(backend/config/database.yml)を修正する database.yml default: &default adapter: mysql2 encoding: utf8 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> username: 自分で設定 password: "自分で設定" host: db socket: /var/run/mysqld/mysqld.sock development: <<: *default database: アプリ名_development test: <<: *default database: アプリ名_test usernameとpasswordは、お好きに設定して下さい。mysqlをターミナルから使用する際に求められます。 再びDockerコマンドを実行 アプリ名 $ docker-compose up アプリ名 $ docker-compose run api rake db:create 下記の画面が出ていれば、成功 rails(localhost:3001) react(localhost:3000) 最後に 記事をまとめるのは、難しいですね・・・ 初めてなので、稚拙な部分も沢山あったかと思いますが、 不明点や記述の仕方に対するフィードバックが御座いましたら、お教え頂けますと幸いです。

0 はじめに 最近は、DBとか触らないのですが、なんとなくドットインストールでMySQLを学んでみたかったため学習 学んだサイト：https://dotinstall.com/lessons/basic_mysql_beginner 　　　　　　　https://dotinstall.com/lessons/basic_mysql_advanced 初級1 MySQLとは ・広く使われているデータベース管理システム ・MySQL にはそこから派生した MariaDB というプロジェクトもある MySQL, MariaDBの公式サイト MySQL https://mysql.com MariaDB https://mariadb.com/ 初級2 今回はドットインストールのブラウザの実行環境を使って学習 １、SQLファイル作成 main.sql SELECT VERSION(); SELECT 5 + 3 ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +-----------------+ | VERSION() | +-----------------+ | 10.4.13-MariaDB | +-----------------+ +-------+ | 5 + 3 | +-------+ | 8 | +-------+ ~ $ 初級3 データベース用語について ・データベースではこのひとつひとつの表のことをテーブルと呼びます ・テーブルで格納されている一行一行のデータのことをレコード、そしてこの列のことをカラムと呼ぶ ・SQL 、 Structured Query Language と呼ばれていて、このような命令はデータベースに問い合わせをするという意味で、クエリと呼ばれます ・クエリですが、慣習的に SQL があらかじめ用意している命令は大文字、そして自分で付けるテーブル名やカラム名などは小文字にすることが多いので、その点も意識しておくといいでしょう 初級4 テーブルを作ってみよう １、SQLファイル作成 main.sql --テーブル名ですが複数のレコードを管理するので、複数形にすることが多い --カラムにどんな値が入るのか、そのデータ型を指定する必要があるので、あとで詳しく見ていきますが、 --message は 140 文字までの文字列、 likes は整数という意味で integer の　INT にしてあげれば OK です。 CREATE TABLE posts ( message VARCHAR(140), likes INT ); -- DESC テーブル名 とすると、テーブルの構造を確認することができる DESC posts; -- SHOW TABLES; とするとテーブルの一覧を確認することができます SHOW TABLES; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | message | varchar(140) | YES | | NULL | | | likes | int(11) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ +-----------------+ | Tables_in_myapp | +-----------------+ | posts | +-----------------+ ~ $ 初級5 レコードを挿入しよう １、SQLファイル作成 main.sql --単にテーブルを削除したいなら DROP TABLE テーブル名でいいのですが、 -- posts が存在していなかったらエラーになってしまうので、こちらで　IF EXISTS としてあげます DROP TABLE IF EXISTS posts; CREATE TABLE posts ( message VARCHAR(140), likes INT ); DESC posts; SHOW TABLES; --INSERTの文字列はシングルクォーテーションか、ダブルクォーテーションで囲ってあげるので覚えておきましょう --INSERTのレコードはまとめて挿入することもできて、このように VALUES のあとにカンマ区切りで書いてあげれば OK です --INSERT INTO posts (message, likes) VALUES ('Thanks', 12); --INSERT INTO posts (message, likes) VALUES ('Arigato', 4); --下記のINSERTは上２つと全く同じ意味になります。 INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Arigato', 4); SELECT * FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | message | varchar(140) | YES | | NULL | | | likes | int(11) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ +-----------------+ | Tables_in_myapp | +-----------------+ | posts | +-----------------+ +---------+-------+ | message | likes | +---------+-------+ | Thanks | 12 | | Arigato | 4 | +---------+-------+ ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | message | varchar(140) | YES | | NULL | | ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | message | varchar(140) | YES | | NULL | | | likes | int(11) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ +-----------------+ | Tables_in_myapp | +-----------------+ | posts | +-----------------+ +---------+-------+ | message | likes | +---------+-------+ | Thanks | 12 | | Arigato | 4 | +---------+-------+ ~ $ 初級6 コメントとかについて １、SQLファイル作成 main.sql --コメントは3 つの方法があって、 -- とするか、 # を使うと行末までがコメントになります。 --もしくは /**/ で囲うこともできて、この場合は何行でもコメントを書いていくことができますね -- comment # comment /* comment comment */ DROP TABLE IF EXISTS posts; CREATE TABLE posts ( message VARCHAR(140), likes INT ); INSERT INTO posts (message, likes) VALUES --('Thanks', 12), ('Arigato', 4); SELECT * FROM posts; 初級7 データ型を見ていこう １、データ型について MySQL で扱えるデータ型はたくさんあって、よく使うのは上記あたり ２、整数型について 扱える数の幅が違うので、状況によって使い分けていけばいいのですが、通常は INT を使う ここでマイナスの値を扱う必要がなければ、データ型に UNSIGNED キーワードをつけてあげることによって、さらに扱える数の幅を広げることができるということも知っておくといいかと思います ３、実数型について 小数点を含む実数の表現ですが、 DECIMAL は固定小数点、 FLOAT と DOUBLE が浮動小数点という方式になりますが、浮動小数点は細かいところで誤差が出てくる数値なので、通常は DECIMAL のほうを使ってあげれば OK かと思います ４、文字列型について 商品コードなど固定長のデータには CHAR 、 文字数がバラバラになるようなデータの場合は VARCHAR 、それより長くなる文字列には TEXT を使ってあげれば OK です 特定の文字列に限定して、そのうちのひとつだけの値を格納したいなら ENUM 、特定の文字列のうちの複数の値なら SET を使います ５、真偽型について TRUE か FLASE のどちらかを保持することができますが、内部的に TRUE が 1 、 FLASE が 0 として管理されているので、実は 1 桁の TINYINT と同じ型だったりします 0 か 1 か、真か偽かといった二択の値を保持する時に使ってあげましょう ６、日時型について 日時に関しては日付だったら DATE 、時間だけだったら TIME 、両方扱いたいなら DATETIME を使ってあげれば OK です 初級8 数値、文字列を扱ってみよう １、SQLファイル作成 main.sql --投稿postのような文字数があまり定まっていないようなものは VARCHAR を使ってあげれば OK --この丸括弧の中では、最大文字数を指定することができるのですが、 --こう書くと日本語英語関係なく 140 文字までという意味になります --いいねlike数はマイナスの値を想定していないので、 UNSIGNED を使ってあげれば扱える数値の範囲が広くなります --投稿したときの気分moodを小数点を含む 10点満点で --管理できるようにします。（小数点も扱える DECIMALを利用） --DECIMAL の場合、丸括弧に続けて全体で 4 桁だよ、そのうち小数点以下は 2 桁だよ、と指定 --マイナス値を想定していないので、 UNSIGNED にしてあげれば扱える数の幅が広くなります --固定長の文字列には CHAR を使えば OK で、 --例えば言語langコードを 2 文字で保持したかったら、このように丸括弧の中に文字数を書いてあげれば OK DROP TABLE IF EXISTS posts; CREATE TABLE posts ( message VARCHAR(140), likes INT UNSIGNED, mood DECIMAL(4, 2) UNSIGNED, lang CHAR(2) ); INSERT INTO posts (message, likes, mood, lang) VALUES ('Thanks', 12, 7.825, 'EN'), ('Arigato', 4, 4.2138, 'JA'); SELECT * FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +---------+-------+------+------+ | message | likes | mood | lang | +---------+-------+------+------+ | Thanks | 12 | 7.83 | EN | | Arigato | 4 | 4.21 | JA | +---------+-------+------+------+ ~ $ --mood ですが、小数点以下 2 桁までと指定した場合、 --そこで四捨五入されている点にも注意しておいてください 初級9 ENUM型を扱ってみよう １、SQLファイル作成 main.sql -- category というカラムを作ってあげて ENUM に対して 'Gadget' , 'Game' , 'Business' のうち --どれかのみが格納できるよと書いてあげます DROP TABLE IF EXISTS posts; CREATE TABLE posts ( message VARCHAR(140), likes INT, category ENUM('Gadget', 'Game', 'Business') ); --'Fashion'という文字はENUMとして登録されていないので、INSERT実行時にエラーとなる INSERT INTO posts (message, likes, category) VALUES ('Thanks', 12, 'Gadget'), ('Arigato', 4, 'Game'), -- ('Merci', 4, 'Fashion'); ('Merci', 4, 'Business'); SELECT * FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +---------+-------+----------+ | message | likes | category | +---------+-------+----------+ | Thanks | 12 | Gadget | | Arigato | 4 | Game | | Merci | 4 | Business | +---------+-------+----------+ ~ $ ３、SQLファイルを作成（インデックス番号での登録） main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( message VARCHAR(140), likes INT, category ENUM('Gadget', 'Game', 'Business') ); -- Gadget ですが、上の一覧の 1 番目のなので 1 、 Game は 2 番目なので 2 、 Buisiness は 3 番目なので 3 と書き換えることができます INSERT INTO posts (message, likes, category) VALUES ('Thanks', 12, 1), ('Arigato', 4, 2), ('Merci', 4, 3); -- インデックス番号を指定しても同じ結果になります SELECT * FROM posts; ４、ターミナルの実行結果（インデックス番号での登録） ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +---------+-------+----------+ | message | likes | category | +---------+-------+----------+ | Thanks | 12 | Gadget | | Arigato | 4 | Game | | Merci | 4 | Business | +---------+-------+----------+ ~ $ 初級10 SET型を扱ってみよう １、SQLファイル作成 main.sql --ENUM はこれらからひとつしか選べませんでしたが、もし複数選べるようにしたいなら、こちらを SET 型にしてあげます -- カラム名も分かりやすいように複数形にしてあげましょう DROP TABLE IF EXISTS posts; CREATE TABLE posts ( message VARCHAR(140), likes INT, categories SET('Gadget', 'Game', 'Business') -- 2^0, 2^1, 2^2, ... ); -- データを挿入する際にはカンマ区切りで値を渡してあげます -- 渡す値は順不同で OKですが、 -- カンマ以外に空白などを入れてはいけないので注意しましょう -- それから変な値を入れるとはじかれるのは ENUM 型と一緒で、たとえばということで、 --こちらで 'Personal' と指定すると -- INSERT実行時にエラーになる INSERT INTO posts (message, likes, categories) VALUES ('Tnanks', 12, 'Gadget,Game'), ('Arigato', 4, 'Business'), -- ('Arigato', 4, 'Personal'), ('Merci', 4, 'Business,Gadget'); SELECT * FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +---------+-------+-----------------+ | message | likes | categories | +---------+-------+-----------------+ | Tnanks | 12 | Gadget,Game | | Arigato | 4 | Business | | Merci | 4 | Gadget,Business | +---------+-------+-----------------+ ~ $ ３、SQLファイル作成（内部的数値で挿入） main.sql -- SET 型ですが、内部的に値を数値で管理していて、左から 2 の 0 乗である 1 、 -- 2 の 1 乗である 2 、 2 の 2 乗である 4 といった具合に管理されています。 DROP TABLE IF EXISTS posts; CREATE TABLE posts ( message VARCHAR(140), likes INT, categories SET('Gadget', 'Game', 'Business') -- 2^0, 2^1, 2^2, ... ); -- Gadget は 1 、 Game は 2 なので、 1 + 2 で 3 で表現できます。 -- Business に関しては 4 -- Business が 4 、 Gadget が 1 なので、 4 + 1 で 5 としてあげれば OK INSERT INTO posts (message, likes, categories) VALUES ('Tnanks', 12, 3), ('Arigato', 4, 4), ('Merci', 4, 5); SELECT * FROM posts; ４、ターミナルの実行結果（内部的数値で挿入） ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +---------+-------+-----------------+ | message | likes | categories | +---------+-------+-----------------+ | Tnanks | 12 | Gadget,Game | | Arigato | 4 | Business | | Merci | 4 | Gadget,Business | +---------+-------+-----------------+ ~ $ --実行結果は同じになる 初級11 真偽値、日時を扱ってみよう １、SQLファイル作成 main.sql -- 真偽値ですが、下書きかどうかを is_draft で管理　BOOL利用 -- レコードが作成された日時を created で管理　DATETIME利用 DROP TABLE IF EXISTS posts; CREATE TABLE posts ( message VARCHAR(140), likes INT, is_draft BOOL, created DATETIME ); -- 真偽値ですが、 TRUE か FALSE もしくは TRUE は 1 FALSE は 0 なので、このように書いてあげても OK です -- 時間を省略すると 0 時 0 分 0 秒になります --現在の日時を表す NOW() というキーワードも使えます INSERT INTO posts (message, likes, is_draft, created) VALUES ('Thanks', 12, TRUE, '2020-10-11 15:32:05'), ('Arigato', 4, FALSE, '2020-10-12'), ('Merci', 4, 0, NOW()); SELECT * FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +---------+-------+----------+---------------------+ | message | likes | is_draft | created | +---------+-------+----------+---------------------+ | Thanks | 12 | 1 | 2020-10-11 15:32:05 | | Arigato | 4 | 0 | 2020-10-12 00:00:00 | | Merci | 4 | 0 | 2021-02-14 23:22:38 | +---------+-------+----------+---------------------+ ~ $ --真偽値は 0 、 1 で表現されていて、 --こちらの 2 番目のレコードの created は 0 時 0 分 0 秒 になっていますし、 --3 番目のレコードの created は今の収録時の日時にちゃんとなっているようです 初級12 NULLの扱いを見てみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( message VARCHAR(140), likes INT -- likes INT NOT NULL -- likes INT DEFAULT 0 ); INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Arigato', 4), ('Merci', 4); -- レコードの挿入ですが、実は全てのカラムに値を設定していなくても OK です。 -- message だけ指定して、レコードを挿入してみましょう。 INSERT INTO posts (message) VALUES ('Gracias'); SELECT * FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +---------+-------+ | message | likes | +---------+-------+ | Thanks | 12 | | Arigato | 4 | | Merci | 4 | | Gracias | NULL | +---------+-------+ ~ $ --値を設定しなかった場合、何もないという意味の NULL という特殊な値になります ３、SQLファイル作成（NOT　NULLにした場合） main.sql -- 値が設定されていなかったら、エラーではじきたいという場合もあります -- その場合はカラムに NOT NULL とつけてあげれば OK です DROP TABLE IF EXISTS posts; CREATE TABLE posts ( message VARCHAR(140), -- likes INT likes INT NOT NULL -- likes INT DEFAULT 0 ); INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Arigato', 4), ('Merci', 4); INSERT INTO posts (message) VALUES ('Gracias'); SELECT * FROM posts; ４、ターミナルの実行結果（NOT　NULLにした場合） ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql ERROR 1364 (HY000) at line 16: Field 'likes' doesn't have a default value ~ $ --ちゃんとエラーになっています ５、SQLファイル作成（DEFAULTの設定） main.sql -- 値が設定されていなかった場合にエラーではじくのではなくて、 -- デフォルト値を設定してあげることもできて、その場合は DEFAULT としたあとに、 -- デフォルトの値を書いてあげれば OK です DROP TABLE IF EXISTS posts; CREATE TABLE posts ( message VARCHAR(140), -- likes INT -- likes INT NOT NULL likes INT DEFAULT 0 ); INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Arigato', 4), ('Merci', 4); INSERT INTO posts (message) VALUES ('Gracias'); SELECT * FROM posts; ６、ターミナルの実行結果（DEFAULTの設定） ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +---------+-------+ | message | likes | +---------+-------+ | Thanks | 12 | | Arigato | 4 | | Merci | 4 | | Gracias | 0 | +---------+-------+ ~ $ --こうすると、エラーがなくなって 0 になるはずです 初級13 値に制限をつけてみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; --値の範囲に制限をつける方法を見ていきます。 --likes は 0 以上 200 以下の値だけにしたいという場合は CHECK を使ってこのように書いてあげれば OK です。 --message には重複した値を入れたくなかった場合、こちらに UNIQUE と付けてあげてください。 CREATE TABLE posts ( message VARCHAR(140) UNIQUE, likes INT CHECK (likes >= 0 AND likes <= 200) ); INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Arigato', 4), ('Merci', 154), ('Arigato', 10); SELECT * FROM posts; ２、オンラインターミナルの実行結果 --message には重複した値が入れられないので、たとえばこちらで 'Arigato' ともう一回入れてもエラーになる ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql ERROR 1062 (23000) at line 7: Duplicate entry 'Arigato' for key 'message' 初級14 主キーっを設定してみよう １、SQLファイル作成（AUTO_INCREMENTなし） main.sql --たいていの場合、 id という名前で NULL ではない整数の連番にするので、 INT NOT NULL としてあげましょう。 --こうしたレコードを一意に特定するためのカラムですが、 PRIMARY KEY という指定をすることで、 -- id をこのテーブルのプライマリーキーつまり、主キーにすることができます。 --主キーにしておくと、 id の値をうっかり入れ忘れたり、 --値が重複していたときにエラーにしてくれるというメリットがあります。 DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (id, message, likes) VALUES (1, 'Thanks', 12), (2, 'Arigato', 4), (3, 'Merci', 4); SELECT * FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql ERROR 1062 (23000) at line 9: Duplicate entry '2' for key 'PRIMARY' ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 2 | Arigato | 4 | | 3 | Merci | 4 | +----+---------+-------+ ３、SQLファイル作成（AUTO_INCREMENTあり） main.sql --連番ですが、自動で振ることもできたりします。 --PRIMARY KEY 指定した場合にしか使えないのですが、こちらで AUTO_INCREMENT としてあげてください。 DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); --値を挿入しなければ自動的に連番になるので、こちらでは id を挿入せずに確かめてみましょう。 INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Arigato', 4), ('Merci', 4); SELECT * FROM posts; ４、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 2 | Arigato | 4 | | 3 | Merci | 4 | +----+---------+-------+ 初級15 SELECTでデータを抽出しよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Arigato', 4), ('Merci', 4), ('Gracias', 15), ('Danke', 23); \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT id, message FROM postsの実行結果↓↓'; SELECT id, message FROM posts; -- 抽出の記号→> >= < <= != <> = 等が使える \! echo 'SELECT * FROM posts WHERE likes >= 10の実行結果↓↓'; SELECT * FROM posts WHERE likes >= 10; \! echo 'SELECT * FROM posts WHERE message = 'Danke'の実行結果↓↓'; SELECT * FROM posts WHERE message = 'Danke'; \! echo 'SELECT * FROM posts WHERE message != 'Danke'の実行結果↓↓'; SELECT * FROM posts WHERE message != 'Danke'; \! echo 'SELECT * FROM posts WHERE message <> 'Danke'の実行結果↓↓'; SELECT * FROM posts WHERE message <> 'Danke'; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 2 | Arigato | 4 | | 3 | Merci | 4 | | 4 | Gracias | 15 | | 5 | Danke | 23 | +----+---------+-------+ SELECT id, message FROM postsの実行結果↓↓ +----+---------+ | id | message | +----+---------+ | 1 | Thanks | | 2 | Arigato | | 3 | Merci | | 4 | Gracias | | 5 | Danke | +----+---------+ SELECT * FROM posts WHERE likes >= 10の実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 4 | Gracias | 15 | | 5 | Danke | 23 | +----+---------+-------+ SELECT * FROM posts WHERE message = Dankeの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 5 | Danke | 23 | +----+---------+-------+ SELECT * FROM posts WHERE message != Dankeの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 2 | Arigato | 4 | | 3 | Merci | 4 | | 4 | Gracias | 15 | +----+---------+-------+ SELECT * FROM posts WHERE message <> Dankeの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 2 | Arigato | 4 | | 3 | Merci | 4 | | 4 | Gracias | 15 | +----+---------+-------+ ~ $ 初級16 条件を組み合わせてみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Arigato', 4), ('Merci', 4), ('Gracias', 15), ('Danke', 23); -- AND なおかつ -- OR もしくは --下記2つは同じ意味になる \! echo 'SELECT * FROM posts WHERE likes >= 10 AND likes <= 20の実行結果↓↓'; SELECT * FROM posts WHERE likes >= 10 AND likes <= 20; \! echo 'SELECT * FROM posts WHERE likes BETWEEN 10 AND 20の実行結果↓↓'; SELECT * FROM posts WHERE likes BETWEEN 10 AND 20; --NOT BETWEENっていうのができる \! echo 'SELECT * FROM posts WHERE likes NOT BETWEEN 10 AND 20の実行結果↓↓'; SELECT * FROM posts WHERE likes NOT BETWEEN 10 AND 20; --下記2つは同じ意味になる \! echo 'SELECT * FROM posts WHERE likes = 4 OR likes = 12の実行結果↓↓'; SELECT * FROM posts WHERE likes = 4 OR likes = 12; \! echo 'SELECT * FROM posts WHERE likes IN (4, 12)の実行結果↓↓'; SELECT * FROM posts WHERE likes IN (4, 12); \! echo 'SELECT * FROM posts WHERE likes NOT IN (4, 12)の実行結果↓↓'; SELECT * FROM posts WHERE likes NOT IN (4, 12); ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM posts WHERE likes >= 10 AND likes <= 20の実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 4 | Gracias | 15 | +----+---------+-------+ SELECT * FROM posts WHERE likes BETWEEN 10 AND 20の実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 4 | Gracias | 15 | +----+---------+-------+ SELECT * FROM posts WHERE likes NOT BETWEEN 10 AND 20の実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 2 | Arigato | 4 | | 3 | Merci | 4 | | 5 | Danke | 23 | +----+---------+-------+ SELECT * FROM posts WHERE likes = 4 OR likes = 12の実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 2 | Arigato | 4 | | 3 | Merci | 4 | +----+---------+-------+ SELECT * FROM posts WHERE likes IN (4, 12)の実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 2 | Arigato | 4 | | 3 | Merci | 4 | +----+---------+-------+ SELECT * FROM posts WHERE likes NOT IN (4, 12)の実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 4 | Gracias | 15 | | 5 | Danke | 23 | +----+---------+-------+ ~ $ 初級17 LIKEと%で文字を抽出しよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('Thank you!', 12), ('thanks 100%', 4), ('Gracias', 4), ('Arigato_gozaimasu', 15), ('Arigato! desu', 23); \! echo 'SELECT * FROM posts WHERE message = 'Gracias'の実行結果↓↓'; SELECT * FROM posts WHERE message = 'Gracias'; -- LIKEキーワードの特殊記号は下記2行 -- %: 0文字以上の任意の文字 -- _: 任意の1文字 --% を使えば前方一位の検索をすることができて、たとえば 't' から始まるメッセージだけを抽出したい場合は、 --以下のように記載 \! echo 'SELECT * FROM posts WHERE message LIKE 't%'の実行結果↓↓'; SELECT * FROM posts WHERE message LIKE 't%'; --ただ、大文字小文字を区別したい場合は BINARY というキーワードを使う --これにより、小文字の 't' から始まるレコードだけが抽出できる \! echo 'SELECT * FROM posts WHERE message LIKE BINARY 't%'の実行結果↓↓'; SELECT * FROM posts WHERE message LIKE BINARY 't%'; --'su' で終わるという条件の書き方 \! echo 'SELECT * FROM posts WHERE message LIKE '%su'の実行結果↓↓'; SELECT * FROM posts WHERE message LIKE '%su'; -- 'i' を含むという条件の書き方 \! echo 'SELECT * FROM posts WHERE message LIKE '%i%'の実行結果↓↓'; SELECT * FROM posts WHERE message LIKE '%i%'; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM posts WHERE message = Graciasの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 3 | Gracias | 4 | +----+---------+-------+ SELECT * FROM posts WHERE message LIKE t%の実行結果↓↓ +----+-------------+-------+ | id | message | likes | +----+-------------+-------+ | 1 | Thank you! | 12 | | 2 | thanks 100% | 4 | +----+-------------+-------+ SELECT * FROM posts WHERE message LIKE BINARY t%の実行結果↓↓ +----+-------------+-------+ | id | message | likes | +----+-------------+-------+ | 2 | thanks 100% | 4 | +----+-------------+-------+ SELECT * FROM posts WHERE message LIKE %suの実行結果↓↓ +----+-------------------+-------+ | id | message | likes | +----+-------------------+-------+ | 4 | Arigato_gozaimasu | 15 | | 5 | Arigato! desu | 23 | +----+-------------------+-------+ SELECT * FROM posts WHERE message LIKE %i%の実行結果↓↓ +----+-------------------+-------+ | id | message | likes | +----+-------------------+-------+ | 3 | Gracias | 4 | | 4 | Arigato_gozaimasu | 15 | | 5 | Arigato! desu | 23 | +----+-------------------+-------+ ~ $ 初級18 LIKEと_文字列を抽出しよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('Thank you!', 12), ('thanks 100%', 4), ('Gracias', 4), ('Arigato_gozaimasu', 15), ('Arigato! desu', 23); -- %: 0文字以上の任意の文字 -- _: 任意の1文字 --essage が任意の 1 文字がふたつ続いて、その次が a でその後が 0 文字以上の任意の文字抽出 \! echo 'SELECT * FROM posts WHERE message LIKE '__a%'の実行結果↓↓'; SELECT * FROM posts WHERE message LIKE '__a%'; -- NOT LIKEもできる \! echo 'SELECT * FROM posts WHERE message NOT LIKE '__a%'の実行結果↓↓'; SELECT * FROM posts WHERE message NOT LIKE '__a%'; -- % と _ の文字自体を検索したい場合 --% を含むレコードだけを抽出したくて、 % を含むという意味で部分一致の書き方を使ってこう書いても、 --実はうまくいかない --下記だと全て抽出される \! echo 'SELECT * FROM posts WHERE message LIKE '%%%'の実行結果↓↓'; SELECT * FROM posts WHERE message LIKE '%%%'; -- 真ん中の % がその文字自体を表現する場合は、 \ をその前に付けてあげれば OK \! echo 'SELECT * FROM posts WHERE message LIKE '%\\%%'の実行結果↓↓'; SELECT * FROM posts WHERE message LIKE '%\%%'; -- _ が入ったレコードだけを抽出したかったら、 \_ としてあげる \! echo 'SELECT * FROM posts WHERE message LIKE '%\\_%'の実行結果↓↓'; SELECT * FROM posts WHERE message LIKE '%\_%'; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM posts WHERE message LIKE __a%の実行結果↓↓ +----+-------------+-------+ | id | message | likes | +----+-------------+-------+ | 1 | Thank you! | 12 | | 2 | thanks 100% | 4 | | 3 | Gracias | 4 | +----+-------------+-------+ SELECT * FROM posts WHERE message NOT LIKE __a%の実行結果↓↓ +----+-------------------+-------+ | id | message | likes | +----+-------------------+-------+ | 4 | Arigato_gozaimasu | 15 | | 5 | Arigato! desu | 23 | +----+-------------------+-------+ SELECT * FROM posts WHERE message LIKE %%%の実行結果↓↓ +----+-------------------+-------+ | id | message | likes | +----+-------------------+-------+ | 1 | Thank you! | 12 | | 2 | thanks 100% | 4 | | 3 | Gracias | 4 | | 4 | Arigato_gozaimasu | 15 | | 5 | Arigato! desu | 23 | +----+-------------------+-------+ SELECT * FROM posts WHERE message LIKE %\%%の実行結果↓↓ +----+-------------+-------+ | id | message | likes | +----+-------------+-------+ | 2 | thanks 100% | 4 | +----+-------------+-------+ SELECT * FROM posts WHERE message LIKE %\_%の実行結果↓↓ +----+-------------------+-------+ | id | message | likes | +----+-------------------+-------+ | 4 | Arigato_gozaimasu | 15 | +----+-------------------+-------+ ~ $ 初級19 NULLのレコードを抽出しよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Arigato', 4), ('Merci', NULL), ('Gracias', 15), ('Danke', NULL); --以下、12 以外が抽出されるのですが、 NULL のレコードは抽出されない \! echo 'SELECT * FROM posts WHERE likes != 12の実行結果↓↓'; SELECT * FROM posts WHERE likes != 12; --以下、ORを利用して12 以外と NULL のレコードは抽出される \! echo 'SELECT * FROM posts WHERE likes != 12 OR likes IS NULLの実行結果↓↓'; SELECT * FROM posts WHERE likes != 12 OR likes IS NULL; --以下、NULL 以外のレコードは抽出される \! echo 'SELECT * FROM posts WHERE likes IS NOT NULLの実行結果↓↓'; SELECT * FROM posts WHERE likes IS NOT NULL; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM posts WHERE likes != 12の実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 2 | Arigato | 4 | | 4 | Gracias | 15 | +----+---------+-------+ SELECT * FROM posts WHERE likes != 12 OR likes IS NULLの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 2 | Arigato | 4 | | 3 | Merci | NULL | | 4 | Gracias | 15 | | 5 | Danke | NULL | +----+---------+-------+ SELECT * FROM posts WHERE likes IS NOT NULLの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 2 | Arigato | 4 | | 4 | Gracias | 15 | +----+---------+-------+ ~ $ 初級20 抽出結果を並び替えよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Merci', 4), ('Arigato', 4), ('Gracias', 15), ('Danke', 8); \! echo 'SELECT * FROM posts ORDER BY likesの実行結果↓↓'; SELECT * FROM posts ORDER BY likes; --逆順 \! echo 'SELECT * FROM posts ORDER BY likes DESCの実行結果↓↓'; SELECT * FROM posts ORDER BY likes DESC; --likes の数が同じだったときに、こちらをアルファベット順に並び替えたかったら、 --カンマ区切りでさらに message を加える \! echo 'SELECT * FROM posts ORDER BY likes DESC, messageの実行結果↓↓'; SELECT * FROM posts ORDER BY likes DESC, message; --上位3件 \! echo 'SELECT * FROM posts ORDER BY likes DESC, message LIMIT 3の実行結果↓↓'; SELECT * FROM posts ORDER BY likes DESC, message LIMIT 3; --最初の 2 件を除外して、その後 3 件という抽出をしたい場合、 --先頭 0 から数えて 0 1 2 件目から 3 件分抽出したいという書き方をします --OFFSET というキーワードを使うのですが、抽出するのは 3 件で、 0 1 2 件目からというのは OFFSET 2 と書く \! echo 'SELECT * FROM posts ORDER BY likes DESC, message LIMIT 3 OFFSET 2の実行結果↓↓'; SELECT * FROM posts ORDER BY likes DESC, message LIMIT 3 OFFSET 2; --それから別の書き方もできて、先頭から数えて 0 1 2 件目から 3 件分といった書き方もできる \! echo 'SELECT * FROM posts ORDER BY likes DESC, message LIMIT 2, 3の実行結果↓↓'; SELECT * FROM posts ORDER BY likes DESC, message LIMIT 2, 3; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM posts ORDER BY likesの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 2 | Merci | 4 | | 3 | Arigato | 4 | | 5 | Danke | 8 | | 1 | Thanks | 12 | | 4 | Gracias | 15 | +----+---------+-------+ SELECT * FROM posts ORDER BY likes DESCの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 4 | Gracias | 15 | | 1 | Thanks | 12 | | 5 | Danke | 8 | | 2 | Merci | 4 | | 3 | Arigato | 4 | +----+---------+-------+ SELECT * FROM posts ORDER BY likes DESC, messageの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 4 | Gracias | 15 | | 1 | Thanks | 12 | | 5 | Danke | 8 | | 3 | Arigato | 4 | | 2 | Merci | 4 | +----+---------+-------+ SELECT * FROM posts ORDER BY likes DESC, message LIMIT 3の実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 4 | Gracias | 15 | | 1 | Thanks | 12 | | 5 | Danke | 8 | +----+---------+-------+ SELECT * FROM posts ORDER BY likes DESC, message LIMIT 3 OFFSET 2の実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 5 | Danke | 8 | | 3 | Arigato | 4 | | 2 | Merci | 4 | +----+---------+-------+ SELECT * FROM posts ORDER BY likes DESC, message LIMIT 2, 3の実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 5 | Danke | 8 | | 3 | Arigato | 4 | | 2 | Merci | 4 | +----+---------+-------+ ~ $ 初級21 数値の関数を見ていこう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Merci', 4), ('Arigato', 4), ('Gracias', 15), ('Danke', 8); --いくつかの演算子が用意されていて、足し算は + 、引き算は - 、掛け算は * 、割り算は / 、 --そして余りを求めるには % を使います -- + - * / % \! echo 'SELECT likes * 500 / 3 FROM postsの実行結果↓↓'; SELECT likes * 500 / 3 FROM posts; --結果の見出しですが、 AS で別名を付けて分かりやすくすることもできます \! echo 'SELECT likes * 500 / 3 AS bonus FROM postsの実行結果↓↓'; SELECT likes * 500 / 3 AS bonus FROM posts; --() がついた命令を関数と呼ぶ --端数を切り捨てたい場合は FLOOR() --端数を切り上げたい場合は CEIL() --四捨五入をしたい場合は、 ROUND() --ROUND 関数ですが、桁数を指定することもできて、小数点以下二桁で丸めたいという場合は、 --こちらにカンマ区切りでこのように桁数を指定してあげてください \! echo 'SELECT likes * 500 / 3 AS bonus,FLOOR(likes * 500 / 3) AS floor,CEIL(likes * 500 / 3) AS ceil,ROUND(likes * 500 / 3) AS round,ROUND(likes * 500 / 3, 2) AS round2 FROM postsの実行結果↓↓'; SELECT likes * 500 / 3 AS bonus, FLOOR(likes * 500 / 3) AS floor, CEIL(likes * 500 / 3) AS ceil, ROUND(likes * 500 / 3) AS round, ROUND(likes * 500 / 3, 2) AS round2 FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT likes * 500 / 3 FROM postsの実行結果↓↓ +-----------------+ | likes * 500 / 3 | +-----------------+ | 2000.0000 | | 666.6667 | | 666.6667 | | 2500.0000 | | 1333.3333 | +-----------------+ SELECT likes * 500 / 3 AS bonus FROM postsの実行結果↓↓ +-----------+ | bonus | +-----------+ | 2000.0000 | | 666.6667 | | 666.6667 | | 2500.0000 | | 1333.3333 | +-----------+ SELECT likes * 500 / 3 AS bonus,FLOOR(likes * 500 / 3) AS floor,CEIL(likes * 500 / 3) AS ceil,ROUND(likes * 500 / 3) AS round,ROUND(likes * 500 / 3, 2) AS round2 FROM postsの実行結果↓↓ +-----------+-------+------+-------+---------+ | bonus | floor | ceil | round | round2 | +-----------+-------+------+-------+---------+ | 2000.0000 | 2000 | 2000 | 2000 | 2000.00 | | 666.6667 | 666 | 667 | 667 | 666.67 | | 666.6667 | 666 | 667 | 667 | 666.67 | | 2500.0000 | 2500 | 2500 | 2500 | 2500.00 | | 1333.3333 | 1333 | 1334 | 1333 | 1333.33 | +-----------+-------+------+-------+---------+ ~ $ 初級22 文字列の関数を見ていこう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Merci', 4), ('Arigato', 4), ('ありがとう', 4), ('Gracias', 15), ('Danke', 8); --文字列の一部を切り出す関数ですが、 SUBSTRING()を使用 --message と message の 3 文字目以降を切り出した文字列を抽出 \! echo 'SELECT message, SUBSTRING(message, 3) FROM postsの実行結果↓↓'; SELECT message, SUBSTRING(message, 3) FROM posts; --3 文字目から 2 文字分を切り出したい場合 \! echo 'SELECT message, SUBSTRING(message, 3, 2) FROM postsの実行結果↓↓'; SELECT message, SUBSTRING(message, 3, 2) FROM posts; --もしくはマイナスの値を与えると、末尾から何文字分という意味になります \! echo 'SELECT message, SUBSTRING(message, -2) FROM postsの実行結果↓↓'; SELECT message, SUBSTRING(message, -2) FROM posts; --message といいねの数をハイフンで繋ぎたいなら、 CONCAT() という関数を使用 \! echo 'SELECT CONCAT(message, '-', likes) FROM postsの実行結果↓↓'; SELECT CONCAT(message, '-', likes) FROM posts; --message と message の文字数を抽出 \! echo 'SELECT message, LENGTH(message) AS len FROM postsの実行結果↓↓'; SELECT message, LENGTH(message) AS len FROM posts; --この LENGTH() ですが、日本語のデータだと少しおかしなことになります --日本語の場合は LENGTH() ではなくて、 CHARLENGTH() にしてあげてください \! echo 'SELECT message, CHAR_LENGTH(message) AS len FROM postsの実行結果↓↓'; SELECT message, CHAR_LENGTH(message) AS len FROM posts; --日本語が問題になるのは、 LENGTH() だけで、 SUBSTRING() は正しく動作する \! echo 'SELECT message, SUBSTRING(message, 3, 2) FROM postsの実行結果↓↓'; SELECT message, SUBSTRING(message, 3, 2) FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT message, SUBSTRING(message, 3) FROM postsの実行結果↓↓ +-----------------+-----------------------+ | message | SUBSTRING(message, 3) | +-----------------+-----------------------+ | Thanks | anks | | Merci | rci | | Arigato | igato | | ありがとう | がとう | | Gracias | acias | | Danke | nke | +-----------------+-----------------------+ SELECT message, SUBSTRING(message, 3, 2) FROM postsの実行結果↓↓ +-----------------+--------------------------+ | message | SUBSTRING(message, 3, 2) | +-----------------+--------------------------+ | Thanks | an | | Merci | rc | | Arigato | ig | | ありがとう | がと | | Gracias | ac | | Danke | nk | +-----------------+--------------------------+ SELECT message, SUBSTRING(message, -2) FROM postsの実行結果↓↓ +-----------------+------------------------+ | message | SUBSTRING(message, -2) | +-----------------+------------------------+ | Thanks | ks | | Merci | ci | | Arigato | to | | ありがとう | とう | | Gracias | as | | Danke | ke | +-----------------+------------------------+ SELECT CONCAT(message, -, likes) FROM postsの実行結果↓↓ +-----------------------------+ | CONCAT(message, '-', likes) | +-----------------------------+ | Thanks-12 | | Merci-4 | | Arigato-4 | | ありがとう-4 | | Gracias-15 | | Danke-8 | +-----------------------------+ SELECT message, LENGTH(message) AS len FROM postsの実行結果↓↓ +-----------------+------+ | message | len | +-----------------+------+ | Thanks | 6 | | Merci | 5 | | Arigato | 7 | | ありがとう | 15 | | Gracias | 7 | | Danke | 5 | +-----------------+------+ SELECT message, CHAR_LENGTH(message) AS len FROM postsの実行結果↓↓ +-----------------+------+ | message | len | +-----------------+------+ | Thanks | 6 | | Merci | 5 | | Arigato | 7 | | ありがとう | 5 | | Gracias | 7 | | Danke | 5 | +-----------------+------+ SELECT message, SUBSTRING(message, 3, 2) FROM postsの実行結果↓↓ +-----------------+--------------------------+ | message | SUBSTRING(message, 3, 2) | +-----------------+--------------------------+ | Thanks | an | | Merci | rc | | Arigato | ig | | ありがとう | がと | | Gracias | ac | | Danke | nk | +-----------------+--------------------------+ ~ $ 初級23 日時の関数を見ていこう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, created DATETIME, PRIMARY KEY (id) ); INSERT INTO posts (message, likes, created) VALUES ('Thanks', 12, '2020-05-01'), ('Merci', 4, '2020-05-03'), ('Arigato', 4, '2020-06-14'), ('Gracias', 15, '2020-07-04'), ('Danke', 8, '2020-08-22'); --created から年月日を抜き出す --それぞれ YEAR 関数、 MONTH 関数、 DAY 関数を使ってあげる \! echo 'SELECT created, YEAR(created) FROM postsの実行結果↓↓'; SELECT created, YEAR(created) FROM posts; \! echo 'SELECT created, MONTH(created) FROM postsの実行結果↓↓'; SELECT created, MONTH(created) FROM posts; \! echo 'SELECT created, DAY(created) FROM postsの実行結果↓↓'; SELECT created, DAY(created) FROM posts; --好きなフォーマットで抽出したい場合、 DATE_FORMAT() という関数が使えます --フォーマットのための記号はいくつかあるのですが、 DATE_FORMAT() を使って -- created を英語表記の月、日、年、曜日としたい場合 \! echo 'SELECT created, DATE_FORMAT(created, '%M %D %Y, %W') AS date FROM postsの実行結果↓↓'; SELECT created, DATE_FORMAT(created, '%M %D %Y, %W') AS date FROM posts; --日付の計算などもできて、たとえば created から 7 日後を抽出したい場合は --DATE_ADD() を使って 7 日後は INTERVAL 7 DAY としてあげてください \! echo 'SELECT created, DATE_ADD(created, INTERVAL 7 DAY) AS next FROM postsの実行結果↓↓'; SELECT created, DATE_ADD(created, INTERVAL 7 DAY) AS next FROM posts; --現在の日付よりどれくらい前かを知りたいなら、 DATEDIFF() という関数が使えます --_ が要らないのですが、 created と NOW() を渡してあげると、その差を計算してくれます \! echo 'SELECT created, NOW(), DATEDIFF(created, NOW()) AS diff FROM postsの実行結果↓↓'; SELECT created, NOW(), DATEDIFF(created, NOW()) AS diff FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT created, YEAR(created) FROM postsの実行結果↓↓ +---------------------+---------------+ | created | YEAR(created) | +---------------------+---------------+ | 2020-05-01 00:00:00 | 2020 | | 2020-05-03 00:00:00 | 2020 | | 2020-06-14 00:00:00 | 2020 | | 2020-07-04 00:00:00 | 2020 | | 2020-08-22 00:00:00 | 2020 | +---------------------+---------------+ SELECT created, MONTH(created) FROM postsの実行結果↓↓ +---------------------+----------------+ | created | MONTH(created) | +---------------------+----------------+ | 2020-05-01 00:00:00 | 5 | | 2020-05-03 00:00:00 | 5 | | 2020-06-14 00:00:00 | 6 | | 2020-07-04 00:00:00 | 7 | | 2020-08-22 00:00:00 | 8 | +---------------------+----------------+ SELECT created, DAY(created) FROM postsの実行結果↓↓ +---------------------+--------------+ | created | DAY(created) | +---------------------+--------------+ | 2020-05-01 00:00:00 | 1 | | 2020-05-03 00:00:00 | 3 | | 2020-06-14 00:00:00 | 14 | | 2020-07-04 00:00:00 | 4 | | 2020-08-22 00:00:00 | 22 | +---------------------+--------------+ SELECT created, DATE_FORMAT(created, %M %D %Y, %W) AS date FROM postsの実行結果↓↓ +---------------------+----------------------------+ | created | date | +---------------------+----------------------------+ | 2020-05-01 00:00:00 | May 1st 2020, Friday | | 2020-05-03 00:00:00 | May 3rd 2020, Sunday | | 2020-06-14 00:00:00 | June 14th 2020, Sunday | | 2020-07-04 00:00:00 | July 4th 2020, Saturday | | 2020-08-22 00:00:00 | August 22nd 2020, Saturday | +---------------------+----------------------------+ SELECT created, DATE_ADD(created, INTERVAL 7 DAY) AS next FROM postsの実行結果↓↓ +---------------------+---------------------+ | created | next | +---------------------+---------------------+ | 2020-05-01 00:00:00 | 2020-05-08 00:00:00 | | 2020-05-03 00:00:00 | 2020-05-10 00:00:00 | | 2020-06-14 00:00:00 | 2020-06-21 00:00:00 | | 2020-07-04 00:00:00 | 2020-07-11 00:00:00 | | 2020-08-22 00:00:00 | 2020-08-29 00:00:00 | +---------------------+---------------------+ SELECT created, NOW(), DATEDIFF(created, NOW()) AS diff FROM postsの実行結果↓↓ +---------------------+---------------------+------+ | created | NOW() | diff | +---------------------+---------------------+------+ | 2020-05-01 00:00:00 | 2021-02-28 23:16:32 | -303 | | 2020-05-03 00:00:00 | 2021-02-28 23:16:32 | -301 | | 2020-06-14 00:00:00 | 2021-02-28 23:16:32 | -259 | | 2020-07-04 00:00:00 | 2021-02-28 23:16:32 | -239 | | 2020-08-22 00:00:00 | 2021-02-28 23:16:32 | -190 | +---------------------+---------------------+------+ ~ $ 初級24 レコードの更新をしてみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Merci', 4), ('Arigato', 4), ('Gracias', 15), ('Danke', 8); --更新前 \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; --likes に 10 を渡して抽出したとしても、元データの値が変わるわけではありません \! echo 'SELECT likes + 10 FROM postsの実行結果↓↓'; SELECT likes + 10 FROM posts; --likes が 10 以上のものについて likes を 5 増やしてみましょう \! echo 'UPDATE posts SET likes = likes + 5 WHERE likes >= 10の実行結果↓↓'; UPDATE posts SET likes = likes + 5 WHERE likes >= 10; --複数のカラムの値を一気に更新することもできます --UPPER() は全て大文字にするという関数 \! echo 'UPDATE posts SET likes = likes + 5, message = UPPER(message) WHERE likes >= 10の実行結果↓↓'; UPDATE posts SET likes = likes + 5, message = UPPER(message) WHERE likes >= 10; --更新後 \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 2 | Merci | 4 | | 3 | Arigato | 4 | | 4 | Gracias | 15 | | 5 | Danke | 8 | +----+---------+-------+ SELECT likes + 10 FROM postsの実行結果↓↓ +------------+ | likes + 10 | +------------+ | 22 | | 14 | | 14 | | 25 | | 18 | +------------+ UPDATE posts SET likes = likes + 5 WHERE likes >= 10の実行結果↓↓ UPDATE posts SET likes = likes + 5, message = UPPER(message) WHERE likes >= 10の実行結果↓↓ SELECT * FROM postsの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | THANKS | 22 | | 2 | Merci | 4 | | 3 | Arigato | 4 | | 4 | GRACIAS | 25 | | 5 | Danke | 8 | +----+---------+-------+ ~ $ 初級25 レコードの削除をしてみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Merci', 4), ('Arigato', 4), ('Gracias', 15), ('Danke', 8); --削除前 \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; --likes が 10 より小さいものを削除してみましょう DELETE FROM posts WHERE likes < 10; --レコードを削除したあとにデータを挿入した場合に、連番がどうなるか見ておきましょう --この時点で 1 から 5 まであったデータが、 1 と 4 だけになったのですが、次が 2 になるのか、 -- 5 になるのか、 6 になるのか気になるところです --削除後 \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; --削除したあとに INSERT 文でデータを挿入してみましょう INSERT INTO posts (message, likes) VALUES ('Xie Xie', 10); --一度使われた連番は使われずに次の連番である 6 になっているので、こうした挙動にも注意しておきましょう \! echo 'の実行結果↓↓'; SELECT * FROM posts; --それから、全件削除した場合でもこれは同じで、こちらで DELETE FROM posts; としたあとに INSERT とすると、 --これでも 6 になっているので注意しておいてください DELETE FROM posts; INSERT INTO posts (message, likes) VALUES ('Xie Xie', 10); \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; --ただここで連番を最初から振り直したい場合もあります --その場合ですが、テーブルごといったん削除して再作成する、 TRUNCATE を使ってあげれば OK です TRUNCATE TABLE posts; INSERT INTO posts (message, likes) VALUES ('Xie Xie', 10); -- 1 からになっています \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 2 | Merci | 4 | | 3 | Arigato | 4 | | 4 | Gracias | 15 | | 5 | Danke | 8 | +----+---------+-------+ SELECT * FROM postsの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 4 | Gracias | 15 | +----+---------+-------+ の実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Thanks | 12 | | 4 | Gracias | 15 | | 6 | Xie Xie | 10 | +----+---------+-------+ SELECT * FROM postsの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 7 | Xie Xie | 10 | +----+---------+-------+ SELECT * FROM postsの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | Xie Xie | 10 | +----+---------+-------+ ~ $ 初級26 作成日、更新日時を自動で設定する １、SQLファイル作成 main.sql --レコードの作成日時や更新日時を自動で設定する方法 --created 、 updated を DATETIME にする。 --DEFAULT を使ってレコードが挿入された時点での日時を NOW() で設定 --また、レコードが更新された時にその時点での日時を自動で更新することもできて、 --その場合は ON UPDATE としてあげて、その時点での日時なので NOW() とする DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, created DATETIME DEFAULT NOW(), updated DATETIME DEFAULT NOW() ON UPDATE NOW(), PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('Thanks', 12), ('Merci', 4), ('Arigato', 4), ('Gracias', 15), ('Danke', 8); \! echo 'SELECT id, created, updated FROM postsの実行結果↓↓'; SELECT id, created, updated FROM posts; --SLEEP という命令を使って 3 秒待つことができる。 \! echo 'SELECT SLEEP(3)の実行結果↓↓'; SELECT SLEEP(3); UPDATE posts SET likes = 100 WHERE id = 1; --現在の日時になっていて、 3 秒経ったあとに表示される id が 1 のレコードを見てあげると、 -- created の 3 秒後で updated がちゃんと更新されているのがわかります。 \! echo 'SELECT id, created, updated FROM postsの実行結果↓↓'; SELECT id, created, updated FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT id, created, updated FROM postsの実行結果↓↓ +----+---------------------+---------------------+ | id | created | updated | +----+---------------------+---------------------+ | 1 | 2021-03-05 23:19:39 | 2021-03-05 23:19:39 | | 2 | 2021-03-05 23:19:39 | 2021-03-05 23:19:39 | | 3 | 2021-03-05 23:19:39 | 2021-03-05 23:19:39 | | 4 | 2021-03-05 23:19:39 | 2021-03-05 23:19:39 | | 5 | 2021-03-05 23:19:39 | 2021-03-05 23:19:39 | +----+---------------------+---------------------+ SELECT SLEEP(3)の実行結果↓↓ +----------+ | SLEEP(3) | +----------+ | 0 | +----------+ SELECT id, created, updated FROM postsの実行結果↓↓ +----+---------------------+---------------------+ | id | created | updated | +----+---------------------+---------------------+ | 1 | 2021-03-05 23:19:39 | 2021-03-05 23:19:42 | | 2 | 2021-03-05 23:19:39 | 2021-03-05 23:19:39 | | 3 | 2021-03-05 23:19:39 | 2021-03-05 23:19:39 | | 4 | 2021-03-05 23:19:39 | 2021-03-05 23:19:39 | | 5 | 2021-03-05 23:19:39 | 2021-03-05 23:19:39 | +----+---------------------+---------------------+ ~ $ 初級27 テーブルの設計を変更してみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); \! echo '最初のCREATE TABLE実行時の実行結果↓↓'; --DESCでテーブルの項目やデータ型の詳細を表示 DESC posts; --テーブル名 ADD としてあげて、追加したいカラム名とそのデータ型を指定してあげます ALTER TABLE posts ADD username VARCHAR(255); \! echo 'ALTER TABLE posts ADD username VARCHAR(255)の実行結果↓↓'; DESC posts; DROP TABLE posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); \! echo '最初のCREATE TABLE実行時の実行結果↓↓'; DESC posts; --idカラムの後にusernameを追加 ALTER TABLE posts ADD username VARCHAR(255) AFTER id; \! echo 'ALTER TABLE posts ADD username VARCHAR(255) AFTER id実行時の実行結果↓↓'; DESC posts; DROP TABLE posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); \! echo '最初のCREATE TABLE実行時の実行結果↓↓'; DESC posts; --最初のカラム名に追加 ALTER TABLE posts ADD username VARCHAR(255) FIRST; \! echo 'ALTER TABLE posts ADD username VARCHAR(255) FIRST 実行時の実行結果↓↓'; DESC posts; DROP TABLE posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); \! echo '最初のCREATE TABLE実行時の実行結果↓↓'; DESC posts; --messageカラムを削除 ALTER TABLE posts DROP message; \! echo 'ALTER TABLE posts DROP message 実行時の実行結果↓↓'; DESC posts; DROP TABLE posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); \! echo '最初のCREATE TABLE実行時の実行結果↓↓'; DESC posts; --pointsカラムをint型にする --CHANGE を使った場合、 MySQL はなるべく既存のデータを変換して保持しようとしますが、 --データが消えてしまうこともあるので十分注意しましょう ALTER TABLE posts CHANGE likes points INT; \! echo 'ALTER TABLE posts CHANGE likes points INT 実行時の実行結果↓↓'; DESC posts; DROP TABLE IF EXISTS messages; --最後にテーブルの名前をあとから変える方法ですが、 --ALTER TABLE テーブル名 RENAME で新しい名前としてあげれば OK です ALTER TABLE posts RENAME messages; \! echo 'ALTER TABLE posts RENAME messages 実行時の実行結果↓↓'; --messagesテーブルを表示 SHOW TABLES; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql 最初のCREATE TABLE実行時の実行結果↓↓ +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | message | varchar(140) | YES | | NULL | | | likes | int(11) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ ALTER TABLE posts ADD username VARCHAR(255)の実行結果↓↓ +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | message | varchar(140) | YES | | NULL | | | likes | int(11) | YES | | NULL | | | username | varchar(255) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 最初のCREATE TABLE実行時の実行結果↓↓ +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | message | varchar(140) | YES | | NULL | | | likes | int(11) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ ALTER TABLE posts ADD username VARCHAR(255) AFTER id実行時の実行結果↓↓ +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | username | varchar(255) | YES | | NULL | | | message | varchar(140) | YES | | NULL | | | likes | int(11) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 最初のCREATE TABLE実行時の実行結果↓↓ +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | message | varchar(140) | YES | | NULL | | | likes | int(11) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ ALTER TABLE posts ADD username VARCHAR(255) FIRST 実行時の実行結果↓↓ +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | username | varchar(255) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | | message | varchar(140) | YES | | NULL | | | likes | int(11) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 最初のCREATE TABLE実行時の実行結果↓↓ +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | message | varchar(140) | YES | | NULL | | | likes | int(11) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ ALTER TABLE posts DROP message 実行時の実行結果↓↓ +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | likes | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+----------------+ 最初のCREATE TABLE実行時の実行結果↓↓ +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | message | varchar(140) | YES | | NULL | | | likes | int(11) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ ALTER TABLE posts CHANGE likes points INT 実行時の実行結果↓↓ +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | message | varchar(140) | YES | | NULL | | | points | int(11) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ ALTER TABLE posts RENAME messages 実行時の実行結果↓↓ +-----------------+ | Tables_in_myapp | +-----------------+ | messages | +-----------------+ ~ $ 応用1 集計関数を使おう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('post-1', 12), ('post-2', 8), ('post-3', 11), ('post-4', 3), ('post-5', NULL), ('post-6', 9), ('post-7', 4), ('post-8', NULL), ('post-9', 31); --likes の NULL を除いた個数を調べるには COUNT(likes) としてあげる \! echo 'SELECT COUNT(likes) FROM postsの実行結果↓↓'; SELECT COUNT(likes) FROM posts; --単に全体の行数を取得したいなら NULL ではないことが保証されている主キーで数えてあげるといいでしょう \! echo 'SELECT COUNT(id) FROM postsの実行結果↓↓'; SELECT COUNT(id) FROM posts; --SELECT COUNT(*) としても全体の行数を取得することができます \! echo 'SELECT COUNT(*) FROM postsの実行結果↓↓'; SELECT COUNT(*) FROM posts; \! echo 'SELECT SUM(likes) FROM postsの実行結果↓↓'; SELECT SUM(likes) FROM posts; \! echo 'SELECT AVG(likes) FROM postsの実行結果↓↓'; SELECT AVG(likes) FROM posts; \! echo 'SELECT MAX(likes) FROM postsの実行結果↓↓'; SELECT MAX(likes) FROM posts; \! echo 'SELECT MIN(likes) FROM postsの実行結果↓↓'; SELECT MIN(likes) FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT COUNT(likes) FROM postsの実行結果↓↓ +--------------+ | COUNT(likes) | +--------------+ | 7 | +--------------+ SELECT COUNT(id) FROM postsの実行結果↓↓ +-----------+ | COUNT(id) | +-----------+ | 9 | +-----------+ SELECT COUNT(*) FROM postsの実行結果↓↓ +----------+ | COUNT(*) | +----------+ | 9 | +----------+ SELECT SUM(likes) FROM postsの実行結果↓↓ +------------+ | SUM(likes) | +------------+ | 78 | +------------+ SELECT AVG(likes) FROM postsの実行結果↓↓ +------------+ | AVG(likes) | +------------+ | 11.1429 | +------------+ SELECT MAX(likes) FROM postsの実行結果↓↓ +------------+ | MAX(likes) | +------------+ | 31 | +------------+ SELECT MIN(likes) FROM postsの実行結果↓↓ +------------+ | MIN(likes) | +------------+ | 3 | +------------+ ~ $ 応用2 GROUP BYでグループ化 １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; --DISTINCT というキーワードを使えば、重複を省いたデータを抽出することができます \! echo 'SELECT DISTINCT area FROM postsの実行結果↓↓'; SELECT DISTINCT area FROM posts; --area ごとにいいね数の合計 \! echo 'SELECT area, SUM(likes) FROM posts GROUP BY areaの実行結果↓↓'; SELECT area, SUM(likes) FROM posts GROUP BY area; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+-------+---------+ | id | message | likes | area | +----+---------+-------+---------+ | 1 | post-1 | 12 | Tokyo | | 2 | post-2 | 8 | Fukuoka | | 3 | post-3 | 11 | Tokyo | | 4 | post-4 | 3 | Osaka | | 5 | post-5 | 8 | Tokyo | | 6 | post-6 | 9 | Osaka | | 7 | post-7 | 4 | Tokyo | | 8 | post-8 | 10 | Osaka | | 9 | post-9 | 31 | Fukuoka | +----+---------+-------+---------+ SELECT DISTINCT area FROM postsの実行結果↓↓ +---------+ | area | +---------+ | Tokyo | | Fukuoka | | Osaka | +---------+ SELECT area, SUM(likes) FROM posts GROUP BY areaの実行結果↓↓ +---------+------------+ | area | SUM(likes) | +---------+------------+ | Fukuoka | 39 | | Osaka | 22 | | Tokyo | 35 | +---------+------------+ ~ $ 応用3 HAVINGで抽出条件を指定しよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --以下コメントのSQLはエラーになる --理由は、WHERE が GROUP BY より前に処理されるので、 --後ろに書いてはいけないというルールがあるからです -- SELECT -- area, -- SUM(likes) -- FROM -- posts -- GROUP BY -- area -- WHERE -- SUM(likes) > 30; --上記のエラーを防ぐため、もし GROUP BY した結果に条件を付けたかったら WHERE ではなく、 --HAVING という命令を使ってあげる必要があります \! echo 'SELECT area, SUM(likes) FROM posts GROUP BY area HAVING SUM(likes) > 30の実行結果↓↓'; SELECT area, SUM(likes) FROM posts GROUP BY area -- WHERE HAVING SUM(likes) > 30; --WHERE と組み合わせて使った例も見ておきましょう --GROUP BY より前に書く必要があるので、たとえばということで、 --こちらで WHERE likes が 10 より大きいもの、と書いてあげましょう --その場合ですが、 posts の全てのレコードから likes が 10 より大きいものだけを抽出したあとに GROUP BY してくれます --今回だと likes が 10 より大きいものなので、 Tokyo の 12 、 Tokyo の 11 、 Fukuoka の 31 が --グループ化して集計されるはずです --こうした挙動の理解が大事です \! echo 'SELECT area, SUM(likes) FROM posts WHERE likes > 10GROUP BY areaの実行結果↓↓'; SELECT area, SUM(likes) FROM posts WHERE likes > 10 GROUP BY area; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT area, SUM(likes) FROM posts GROUP BY area HAVING SUM(likes) > 30の実行結果↓↓ +---------+------------+ | area | SUM(likes) | +---------+------------+ | Fukuoka | 39 | | Tokyo | 35 | +---------+------------+ SELECT area, SUM(likes) FROM posts WHERE likes > 10GROUP BY areaの実行結果↓↓ +---------+------------+ | area | SUM(likes) | +---------+------------+ | Fukuoka | 31 | | Tokyo | 23 | +---------+------------+ ~ $ 応用4 IF(),CASEを扱ってみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --10 より大きかったら A チーム、それ以外は B チームとしたい場合、 --IF likes が 10 より大きかったら A 、そうじゃなかったら B と書いてあげます \! echo 'SELECT *, IF(likes > 10, 'A', 'B') AS team FROM postsの実行結果↓↓'; SELECT *, IF(likes > 10, 'A', 'B') AS team FROM posts; --CASE という命令も使えて、その場合はいくらでも条件を増やせるので、たとえば、 -- likes が 10 より大きかったら A チーム、 10 以下だけど 5 より大きかったら B チーム、 --そしてそれ以外は C チームと書きたい場合は、このように書いてあげれば OK です \! echo 'SELECT *, CASE WHEN likes > 10 THEN 'A' WHEN likes > 5 THEN 'B' ELSE 'C' END AS team FROM postsの実行結果↓↓'; SELECT *, CASE WHEN likes > 10 THEN 'A' WHEN likes > 5 THEN 'B' ELSE 'C' END AS team FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT *, IF(likes > 10, A, B) AS team FROM postsの実行結果↓↓ +----+---------+-------+---------+------+ | id | message | likes | area | team | +----+---------+-------+---------+------+ | 1 | post-1 | 12 | Tokyo | A | | 2 | post-2 | 8 | Fukuoka | B | | 3 | post-3 | 11 | Tokyo | A | | 4 | post-4 | 3 | Osaka | B | | 5 | post-5 | 8 | Tokyo | B | | 6 | post-6 | 9 | Osaka | B | | 7 | post-7 | 4 | Tokyo | B | | 8 | post-8 | 10 | Osaka | B | | 9 | post-9 | 31 | Fukuoka | A | +----+---------+-------+---------+------+ SELECT *, CASE WHEN likes > 10 THEN A WHEN likes > 5 THEN B ELSE C END AS team FROM postsの実行結果↓↓ +----+---------+-------+---------+------+ | id | message | likes | area | team | +----+---------+-------+---------+------+ | 1 | post-1 | 12 | Tokyo | A | | 2 | post-2 | 8 | Fukuoka | B | | 3 | post-3 | 11 | Tokyo | A | | 4 | post-4 | 3 | Osaka | C | | 5 | post-5 | 8 | Tokyo | B | | 6 | post-6 | 9 | Osaka | B | | 7 | post-7 | 4 | Tokyo | C | | 8 | post-8 | 10 | Osaka | B | | 9 | post-9 | 31 | Fukuoka | A | +----+---------+-------+---------+------+ ~ $ 応用5 抽出結果を別テーブルにしよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); DROP TABLE IF EXISTS posts_tokyo; --抽出結果を別テーブルとして切り出す方法 --as をつけて作成 area = 'Tokyo'をつけて絞ってデータ抽出して、 --posts_tokyoテーブルを作成 CREATE TABLE posts_tokyo AS SELECT * FROM posts WHERE area = 'Tokyo'; DROP TABLE IF EXISTS posts_copy; --抽出条件で WHERE を外してあげれば、全てのレコードを抽出して --posts_copyテーブルを作成 CREATE TABLE posts_copy AS SELECT * FROM posts; DROP TABLE IF EXISTS posts_skeleton; --データの構造だけコピーして中身のレコードはいらないよ、という場合は LIKE テーブル名としてあげれば OK です CREATE TABLE posts_skeleton LIKE posts; --table一覧を表示 SHOW TABLES; \! echo 'SELECT * FROM posts_tokyoの実行結果↓↓'; SELECT * FROM posts_tokyo; \! echo 'SELECT * FROM posts_copyの実行結果↓↓'; SELECT * FROM posts_copy; \! echo 'SELECT * FROM posts_skeletonの実行結果↓↓'; SELECT * FROM posts_skeleton; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql +-----------------+ | Tables_in_myapp | +-----------------+ | posts | | posts_copy | | posts_skeleton | | posts_tokyo | +-----------------+ SELECT * FROM posts_tokyoの実行結果↓↓ +----+---------+-------+-------+ | id | message | likes | area | +----+---------+-------+-------+ | 1 | post-1 | 12 | Tokyo | | 3 | post-3 | 11 | Tokyo | | 5 | post-5 | 8 | Tokyo | | 7 | post-7 | 4 | Tokyo | +----+---------+-------+-------+ SELECT * FROM posts_copyの実行結果↓↓ +----+---------+-------+---------+ | id | message | likes | area | +----+---------+-------+---------+ | 1 | post-1 | 12 | Tokyo | | 2 | post-2 | 8 | Fukuoka | | 3 | post-3 | 11 | Tokyo | | 4 | post-4 | 3 | Osaka | | 5 | post-5 | 8 | Tokyo | | 6 | post-6 | 9 | Osaka | | 7 | post-7 | 4 | Tokyo | | 8 | post-8 | 10 | Osaka | | 9 | post-9 | 31 | Fukuoka | +----+---------+-------+---------+ SELECT * FROM posts_skeletonの実行結果↓↓ ~ $ 応用6 VIEWを扱ってみよう １、SQLファイル作成(view作成なし) main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --posts_tokyo ですが、 posts テーブルから一部のデータを抽出した別のテーブルなので、 --当然ですがこちらを更新しても posts_tokyo が自動的に更新されるという訳ではありません。 DROP TABLE IF EXISTS posts_tokyo; --postsテーブルからareaがTokyoのデータを抽出して、posts_tokyoテーブルを作成 CREATE TABLE posts_tokyo AS SELECT * FROM posts WHERE area = 'Tokyo'; --posts テーブルを更新 UPDATE posts SET likes = 15 WHERE id = 1; --当然ですが、posts のほうは更新されますが、 posts_tokyo のほうは更新されない \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM posts_tokyoの実行結果↓↓'; SELECT * FROM posts_tokyo; ２、ターミナルの実行結果(view作成なし) ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+-------+---------+ | id | message | likes | area | +----+---------+-------+---------+ | 1 | post-1 | 15 | Tokyo | | 2 | post-2 | 8 | Fukuoka | | 3 | post-3 | 11 | Tokyo | | 4 | post-4 | 3 | Osaka | | 5 | post-5 | 8 | Tokyo | | 6 | post-6 | 9 | Osaka | | 7 | post-7 | 4 | Tokyo | | 8 | post-8 | 10 | Osaka | | 9 | post-9 | 31 | Fukuoka | +----+---------+-------+---------+ SELECT * FROM posts_tokyoの実行結果↓↓ +----+---------+-------+-------+ | id | message | likes | area | +----+---------+-------+-------+ | 1 | post-1 | 12 | Tokyo | | 3 | post-3 | 11 | Tokyo | | 5 | post-5 | 8 | Tokyo | | 7 | post-7 | 4 | Tokyo | +----+---------+-------+-------+ ~ $ ３、SQLファイル作成(view作成あり) main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); DROP VIEW IF EXISTS posts_tokyo_view; --VIEW という仕組みを使えば元テーブルと連動する仮想的なテーブルを作ることができます。 --VIEW ですが、こちらの抽出条件だけを保持した仮想的なテーブルで実行する度に、 --元データから再度値を抽出してくれるという仕組みになっています。 CREATE VIEW posts_tokyo_view AS SELECT * FROM posts WHERE area = 'Tokyo'; --１、２と同じように更新 UPDATE posts SET likes = 15 WHERE id = 1; --posts が 15 で VIEW は実行する度に、またデータを抽出するので、 --posts_tokyo_viewも 15 になっています。 \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM posts_tokyo_viewの実行結果↓↓'; SELECT * FROM posts_tokyo_view; ４、ターミナルの実行結果(view作成あり) ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+-------+---------+ | id | message | likes | area | +----+---------+-------+---------+ | 1 | post-1 | 15 | Tokyo | | 2 | post-2 | 8 | Fukuoka | | 3 | post-3 | 11 | Tokyo | | 4 | post-4 | 3 | Osaka | | 5 | post-5 | 8 | Tokyo | | 6 | post-6 | 9 | Osaka | | 7 | post-7 | 4 | Tokyo | | 8 | post-8 | 10 | Osaka | | 9 | post-9 | 31 | Fukuoka | +----+---------+-------+---------+ SELECT * FROM posts_tokyo_viewの実行結果↓↓ +----+---------+-------+-------+ | id | message | likes | area | +----+---------+-------+-------+ | 1 | post-1 | 15 | Tokyo | | 3 | post-3 | 11 | Tokyo | | 5 | post-5 | 8 | Tokyo | | 7 | post-7 | 4 | Tokyo | +----+---------+-------+-------+ ~ $ 応用7 UNIONで抽出結果をまとめる １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --likes の大きい順に並び替え \! echo 'SELECT * FROM posts ORDER BY likes DESCの実行結果↓↓'; SELECT * FROM posts ORDER BY likes DESC; --こちらの上位 3 名と最後の 1 つだけを抽出 \! echo 'SELECT * FROM posts ORDER BY likes DESC LIMIT 3の実行結果↓↓'; SELECT * FROM posts ORDER BY likes DESC LIMIT 3; \! echo 'SELECT * FROM posts ORDER BY likes LIMIT 1の実行結果↓↓'; SELECT * FROM posts ORDER BY likes LIMIT 1; --上のSQLと同様、上位 3 名と最後の 1 つだけを抽出 --（結果を１つにするため、UNION ALLを利用） \! echo '(SELECT * FROM posts ORDER BY likes DESC LIMIT 3) UNION ALL (SELECT * FROM posts ORDER BY likes LIMIT 1)の実行結果↓↓'; (SELECT * FROM posts ORDER BY likes DESC LIMIT 3) UNION ALL (SELECT * FROM posts ORDER BY likes LIMIT 1); --UNION を使う場合、この2つのクエリのカラム数とデータ型が --一致している必要がありますが、 --結果をこのように縦に繋げたいときに便利 ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM posts ORDER BY likes DESCの実行結果↓↓ +----+---------+-------+---------+ | id | message | likes | area | +----+---------+-------+---------+ | 9 | post-9 | 31 | Fukuoka | | 1 | post-1 | 12 | Tokyo | | 3 | post-3 | 11 | Tokyo | | 8 | post-8 | 10 | Osaka | | 6 | post-6 | 9 | Osaka | | 2 | post-2 | 8 | Fukuoka | | 5 | post-5 | 8 | Tokyo | | 7 | post-7 | 4 | Tokyo | | 4 | post-4 | 3 | Osaka | +----+---------+-------+---------+ SELECT * FROM posts ORDER BY likes DESC LIMIT 3の実行結果↓↓ +----+---------+-------+---------+ | id | message | likes | area | +----+---------+-------+---------+ | 9 | post-9 | 31 | Fukuoka | | 1 | post-1 | 12 | Tokyo | | 3 | post-3 | 11 | Tokyo | +----+---------+-------+---------+ SELECT * FROM posts ORDER BY likes LIMIT 1の実行結果↓↓ +----+---------+-------+-------+ | id | message | likes | area | +----+---------+-------+-------+ | 4 | post-4 | 3 | Osaka | +----+---------+-------+-------+ (SELECT * FROM posts ORDER BY likes DESC LIMIT 3) UNION ALL (SELECT * FROM posts ORDER BY likes LIMIT 1)の実行結果↓↓ +----+---------+-------+---------+ | id | message | likes | area | +----+---------+-------+---------+ | 9 | post-9 | 31 | Fukuoka | | 1 | post-1 | 12 | Tokyo | | 3 | post-3 | 11 | Tokyo | | 4 | post-4 | 3 | Osaka | +----+---------+-------+---------+ ~ $ 応用8 サブクエリを使ってみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; --ここで likes の平均を横に表示したかった場合、 --普通に考えると、 * のあとに likes の平均をこのように表示すればいいのではないかと思うかもしれませんが --下記ではうまくいきません --AVG 関数は全てのレコードを集計してひとつのレコードにしてしまうので、このような結果になってしまいます \! echo 'SELECT *, AVG(likes) AS avg FROM postsの実行結果↓↓'; SELECT *, AVG(likes) AS avg FROM posts; --普通の SELECT の結果に加えて、レコードごとに集計関数を使うことで、こちらにうまく結果が表示されているようです --こういう場合、SELECT の中でさらに SELECT 文を使ってあげれば OK です --このようにクエリの中で使うクエリのことをサブクエリと呼ぶ --サブクエリを駆使すればかなり複雑なことができますが、 --その分クエリがこのように長くなったり、速度が遅くなったりするので、 --データが多いときには注意して使う必要があるという点も注意しておきましょう \! echo 'SELECT *, (SELECT AVG(likes) FROM posts) AS avg FROM postsの実行結果↓↓'; SELECT *, (SELECT AVG(likes) FROM posts) AS avg FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+-------+---------+ | id | message | likes | area | +----+---------+-------+---------+ | 1 | post-1 | 12 | Tokyo | | 2 | post-2 | 8 | Fukuoka | | 3 | post-3 | 11 | Tokyo | | 4 | post-4 | 3 | Osaka | | 5 | post-5 | 8 | Tokyo | | 6 | post-6 | 9 | Osaka | | 7 | post-7 | 4 | Tokyo | | 8 | post-8 | 10 | Osaka | | 9 | post-9 | 31 | Fukuoka | +----+---------+-------+---------+ SELECT *, AVG(likes) AS avg FROM postsの実行結果↓↓ +------+---------+-------+-------+---------+ | id | message | likes | area | avg | +------+---------+-------+-------+---------+ | 1 | post-1 | 12 | Tokyo | 10.6667 | +------+---------+-------+-------+---------+ SELECT *, (SELECT AVG(likes) FROM posts) AS avg FROM postsの実行結果↓↓ +----+---------+-------+---------+---------+ | id | message | likes | area | avg | +----+---------+-------+---------+---------+ | 1 | post-1 | 12 | Tokyo | 10.6667 | | 2 | post-2 | 8 | Fukuoka | 10.6667 | | 3 | post-3 | 11 | Tokyo | 10.6667 | | 4 | post-4 | 3 | Osaka | 10.6667 | | 5 | post-5 | 8 | Tokyo | 10.6667 | | 6 | post-6 | 9 | Osaka | 10.6667 | | 7 | post-7 | 4 | Tokyo | 10.6667 | | 8 | post-8 | 10 | Osaka | 10.6667 | | 9 | post-9 | 31 | Fukuoka | 10.6667 | +----+---------+-------+---------+---------+ ~ $ 応用9 相関サブクエリを使ってみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --別名avgは平均が表示されている状態です --area ごとの平均も表示したかった場合、どうするか考えてみましょう --の場合ですが、最初のレコードを処理しているときには、 --全体から area が Tokyo のレコードだけを抜き出して、平均を計算してあげれば良さそうです --次のレコードを処理するときには全体から area が Fukuoka のレコードだけ抜き出してあげて、 --平均を計算してあげればいいですね --そのようにクエリを書いていきましょう --こちらにもうひとつサブクエリを書いてあげて、 area_avg としてあげましょう。 --そのうえで WHERE 条件で全体の area と、今処理しているレコードの area が一緒のものを抽出してあげればいいですね --だこれだと、どっちがどっちかがわからないので、抽出元のテーブルに別名をつけてあげましょう --こちらは t1 、そして今処理しているほうを t2 としてあげましょう --t1とt2は＝を逆で書いても問題なし --こちらの area は t1 の area にしてあげたいので、 テーブル名.カラム名 としてあげます -- t2 の area にしたいので、このように書いてあげれば OK ですね --Tokyo の平均は 8.75 なのですが、 --ちゃんとレコードごとに抽出できているようです --Fukuoka の平均は 19.5 なので、こちらもうまくいっていますね --Osaka に関しても、平均は 7.3 なので、こちらもレコードごとにうまく抽出できているようです --このように大本のクエリと関連付けながら、実行しているサブクエリのことを相関サブクエリと呼ぶので、 --用語として覚えておくといいでしょう \! echo 'SELECT *, (SELECT AVG(likes) FROM posts) AS avg, (SELECT AVG(likes) FROM posts AS t2 WHERE t1.area = t2.area) AS area_avg FROM posts AS t1の実行結果↓↓'; SELECT *, (SELECT AVG(likes) FROM posts) AS avg, (SELECT AVG(likes) FROM posts AS t2 WHERE t1.area = t2.area) AS area_avg FROM posts AS t1; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT *, (SELECT AVG(likes) FROM posts) AS avg, (SELECT AVG(likes) FROM posts AS t2 WHERE t1.area = t2.area) AS area_avg FROM posts AS t1の実行結果↓↓ +----+---------+-------+---------+---------+----------+ | id | message | likes | area | avg | area_avg | +----+---------+-------+---------+---------+----------+ | 1 | post-1 | 12 | Tokyo | 10.6667 | 8.7500 | | 2 | post-2 | 8 | Fukuoka | 10.6667 | 19.5000 | | 3 | post-3 | 11 | Tokyo | 10.6667 | 8.7500 | | 4 | post-4 | 3 | Osaka | 10.6667 | 7.3333 | | 5 | post-5 | 8 | Tokyo | 10.6667 | 8.7500 | | 6 | post-6 | 9 | Osaka | 10.6667 | 7.3333 | | 7 | post-7 | 4 | Tokyo | 10.6667 | 8.7500 | | 8 | post-8 | 10 | Osaka | 10.6667 | 7.3333 | | 9 | post-9 | 31 | Fukuoka | 10.6667 | 19.5000 | +----+---------+-------+---------+---------+----------+ ~ $ 応用10 抽出条件にサブクエリを使ってみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --これまで抽出するカラムとして、サブクエリを使ってきましたが、 -- WHERE のあとの抽出条件や抽出元のテーブルにも使うことができるので、そのあたりも見ておきましょう --今回は抽出条件として使ってみたいので、いったん消してあげて、 --この中から 1 番大きな likes を持つレコードを抽出してみましょう --今回だと post-9 ですね \! echo 'SELECT * FROM posts WHERE likes = (SELECT MAX(likes) FROM posts)の実行結果↓↓'; SELECT * FROM posts WHERE likes = (SELECT MAX(likes) FROM posts); ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM posts WHERE likes = (SELECT MAX(likes) FROM posts)の実行結果↓↓ +----+---------+-------+---------+ | id | message | likes | area | +----+---------+-------+---------+ | 9 | post-9 | 31 | Fukuoka | +----+---------+-------+---------+ ~ $ 応用11 抽出元にサブクエリを使ってみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --area ごとの投稿数を出してみましょう --COUNT を使ってあげて n という別名をつけてあげて、 GROUP BY で area ごとと指定してあげればいいですね \! echo 'SELECT area, COUNT(*) AS n FROM posts GROUP BY areaの実行結果↓↓'; SELECT area, COUNT(*) AS n FROM posts GROUP BY area; --ここでこの抽出結果である 2 3 4 の平均を出したかった場合を考えてみましょう --の場合、いったんこの結果を別テーブルに切り出して集計するという方法もありますが、 --サブクエリを使う方法もあります --どうするかというと、この結果を t というテーブルだと考えると、 -- n の平均を出せばいいので、そのようにまずはクエリを組み立ててあげましょう --n の平均を t から抽出する、と書いてあげればいいですね --また、ここで t はこちらの上に書いたクエリの結果なので、そのままサブクエリとして置き換えてあげれば OK です --ただし、サブクエリを抽出元のテーブルとして使った場合、必ず別名をつける必要があるので、こちらには AS t としてあげてください --こちらの平均である 3 がちゃんと抽出できたのが分かります \! echo 'SELECT AVG(n) FROM (SELECT area, COUNT(*) AS n FROM posts GROUP BY area) AS tの実行結果↓↓'; SELECT AVG(n) FROM (SELECT area, COUNT(*) AS n FROM posts GROUP BY area) AS t; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT area, COUNT(*) AS n FROM posts GROUP BY areaの実行結果↓↓ +---------+---+ | area | n | +---------+---+ | Fukuoka | 2 | | Osaka | 3 | | Tokyo | 4 | +---------+---+ SELECT AVG(n) FROM (SELECT area, COUNT(*) AS n FROM posts GROUP BY area) AS tの実行結果↓↓ +--------+ | AVG(n) | +--------+ | 3.0000 | +--------+ ~ $ 応用12 ウインドウ関数を使ってみよう １、ウィンドウ関数を使うと、テーブルを パーティション(PARTITION) と呼ばれる単位で集計して その結果を各レコードの横に追加してくれます。 　ウィンドウ関数はMySQL 8.0から使用可能となります。 ２、たとえば、このテーブル全体をパーティションに設定して、 likes の合計を集計すると、 下記2つの図のような形になります。 ↓↓ likesの合計 ３、特定のカラム、たとえば area でパーティションを設定することもできて、 そうすると、パーティションは2つの図のようにTokyoとOsakaになる Tokyoパーティション↓↓ Osakaパーティション↓↓ そして、下記の図のように、それぞれTokyo、Osakaの合計がこのように横に追加されるといった具合ですね。 Tokyoの合計↓↓ Osakaの合計↓↓ ４、また、パーティションのサブセットである フレーム(FRAME) という単位を使えばさらに細かい集計をすることもできます。 例えば、上記のlikes を小さい順に以下2つの図のように並び替える そして、下記の図のように、それぞれのレコードまでの累計を集計することもできます。 上記は、パーティションの中で下記の図のように、フレームをパーティションの先頭からそのレコードまで と設定することで実現できます。 一行目だったらフレームは下記の図のようになるので、そこまでの累計は 4 になります。 次のレコードだったらパーティションの先頭からそのレコードまでなので、 フレームは下記の図のようになり、そしてそこまでの累計は 4 + 11 で 15 　※下記の図において、15がsumカラムの2行目に表示されている！！ 3 つ目のレコードだったらフレームは下記の図のようになり、 そこまでの累計は 4 + 11 + 12 で 27 ※下記の図において、27がsumカラムの3行目に表示されている！！ また、次のレコードに移ったときはパーティションは下記になる（Osakaエリア） 下記の図のように、パーティションの先頭からそのレコードまでで、このレコードまでの累計は 8 次のレコードではフレームが下記の図のようにんあるので、そこまでの累計は 8 + 22 で 30 ５、１～４の内容からウィンドウ関数を使えばきめ細かい単位で集計をすることができるようになります。 　　一部の処理はサブクエリでも実装できたりしますが、ウィンドウ関数を使うとすっきりと書ける 応用13 PARTITIONを設定してみよう ※１～２と３～４は書き方は違うが、同じ処理 １、SQLファイル作成（WINDOW 別名 as PARTION by ~は利用しないパターン） main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --ウィンドウ関数を使ってみましょう --ブクエリでも見ましたが、この横に likes の全体の平均と area ごとの平均を出してみましょう --ウィンドウ関数では、今まで見てきた集計関数をそのまま使えるので AVG(likes) としてあげて、 --ウィンドウ関数として使うなら OVER と書いてあげます。 --分かりやすく avg という別名をつけてあげましょうそのうえでパーティションを設定するのですが、 --全体をひとつのパーティションにするなら、単なる丸括弧で OK です -- -- --次に、 area ごとの平均ですね。 --OVER のなかでパーティションを指定してあげれば OK です。 --いったんコピーしてあげて、こちらでパーティションを設定するには、 --PARTITION BY area としてあげれば area ごとにパーティションを区切って平均を集計してくれます --別名は area_avg にしておきましょう -- -- --それから、ついでに area ごとの likes の合計も出してみましょう --SUM に変えてあげれば OK ですね。別名も分かりやすく area_sum としてあげましょう --実行して結果を見ると、area ごとの平均、 area ごとの合計がちゃんと追加されているので OK そうです --ウィンドウ関数を使うとサブクエリと違って、このようにすっきり書くことができるので、 --使いこなせるようになっておきましょう \! echo 'SELECT *, AVG(likes) OVER () AS avg, AVG(likes) OVER (PARTITION BY area) AS area_avg, SUM(likes) OVER (PARTITION BY area) AS area_sum FROM postsの実行結果↓↓'; SELECT *, AVG(likes) OVER () AS avg, AVG(likes) OVER (PARTITION BY area) AS area_avg, SUM(likes) OVER (PARTITION BY area) AS area_sum FROM posts; ２、ターミナルの実行結果（WINDOW 別名 as PARTION by ~は利用しないパターン） ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT *, AVG(likes) OVER () AS avg, AVG(likes) OVER (PARTITION BY area) AS area_avg, SUM(likes) OVER (PARTITION BY area) AS area_sum FROM postsの実行結果↓↓ +----+---------+-------+---------+---------+----------+----------+ | id | message | likes | area | avg | area_avg | area_sum | +----+---------+-------+---------+---------+----------+----------+ | 2 | post-2 | 8 | Fukuoka | 10.6667 | 19.5000 | 39 | | 9 | post-9 | 31 | Fukuoka | 10.6667 | 19.5000 | 39 | | 4 | post-4 | 3 | Osaka | 10.6667 | 7.3333 | 22 | | 6 | post-6 | 9 | Osaka | 10.6667 | 7.3333 | 22 | | 8 | post-8 | 10 | Osaka | 10.6667 | 7.3333 | 22 | | 7 | post-7 | 4 | Tokyo | 10.6667 | 8.7500 | 35 | | 3 | post-3 | 11 | Tokyo | 10.6667 | 8.7500 | 35 | | 5 | post-5 | 8 | Tokyo | 10.6667 | 8.7500 | 35 | | 1 | post-1 | 12 | Tokyo | 10.6667 | 8.7500 | 35 | +----+---------+-------+---------+---------+----------+----------+ ~ $ ３、SQLファイル作成（WINDOW 別名 as PARTION by ~は利用するパターン） main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --OVER のこのあとの指定ですが、同じようなものがある場合は別名をつけることができます --その場合は WINDOW としてあげて、別名 AS 、そしてこちらの指定を書いてあげれば OK です --うすると、この指定の代わりに w という別名を使うことができるので、この二行を短く書き換えることができますね \! echo 'SELECT *, AVG(likes) OVER () AS avg, AVG(likes) OVER w AS area_avg, SUM(likes) OVER w AS area_sum FROM posts WINDOW w AS (PARTITION BY area)の実行結果↓↓'; SELECT *, AVG(likes) OVER () AS avg, AVG(likes) OVER w AS area_avg, SUM(likes) OVER w AS area_sum FROM posts WINDOW w AS (PARTITION BY area); ４、ターミナルの実行結果（WINDOW 別名 as PARTION by ~は利用するパターン） ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT *, AVG(likes) OVER () AS avg, AVG(likes) OVER w AS area_avg, SUM(likes) OVER w AS area_sum FROM posts WINDOW w AS (PARTITION BY area)の実行結果↓↓ +----+---------+-------+---------+---------+----------+----------+ | id | message | likes | area | avg | area_avg | area_sum | +----+---------+-------+---------+---------+----------+----------+ | 2 | post-2 | 8 | Fukuoka | 10.6667 | 19.5000 | 39 | | 9 | post-9 | 31 | Fukuoka | 10.6667 | 19.5000 | 39 | | 4 | post-4 | 3 | Osaka | 10.6667 | 7.3333 | 22 | | 6 | post-6 | 9 | Osaka | 10.6667 | 7.3333 | 22 | | 8 | post-8 | 10 | Osaka | 10.6667 | 7.3333 | 22 | | 7 | post-7 | 4 | Tokyo | 10.6667 | 8.7500 | 35 | | 3 | post-3 | 11 | Tokyo | 10.6667 | 8.7500 | 35 | | 5 | post-5 | 8 | Tokyo | 10.6667 | 8.7500 | 35 | | 1 | post-1 | 12 | Tokyo | 10.6667 | 8.7500 | 35 | +----+---------+-------+---------+---------+----------+----------+ ~ $ 応用14 FRAMEを設定してみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --likes の小さい順に並び替えたうえで累計を集計していきたいので、 ORDER BY likesを利用 --そのうえでフレームを設定したいのですが、実はデフォルトでパーティションの先頭から --そのレコードまでになるので、このままで累計が集計できるはずです \! echo 'SELECT *, SUM(likes) OVER ( PARTITION BY area ORDER BY likes ) AS area_sum FROM postsの実行結果↓↓'; SELECT *, SUM(likes) OVER ( PARTITION BY area ORDER BY likes ) AS area_sum FROM posts; ２、ターミナルの実行結果 下記の実行結果を見ると、 最初のレコードのフレームはこの一行なので、合計は 8 そして二行目のレコードのフレームはパーティション(Fukuoka)の先頭からそのレコードまでなので、 8 + 31 で 39 次のレコードではパーティション(Osaka)が変わるので、パーティションの先頭からの合計で 3 次のレコードのフレームはパーティションの先頭からそのレコードまでで 3 + 9 で 12 といった感じにパーティションごとにちゃんと累計が集計できているのが分かります ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT *, SUM(likes) OVER ( PARTITION BY area ORDER BY likes ) AS area_sum FROM postsの実行結果↓↓ +----+---------+-------+---------+----------+ | id | message | likes | area | area_sum | +----+---------+-------+---------+----------+ | 2 | post-2 | 8 | Fukuoka | 8 | | 9 | post-9 | 31 | Fukuoka | 39 | | 4 | post-4 | 3 | Osaka | 3 | | 6 | post-6 | 9 | Osaka | 12 | | 8 | post-8 | 10 | Osaka | 22 | | 7 | post-7 | 4 | Tokyo | 4 | | 5 | post-5 | 8 | Tokyo | 12 | | 3 | post-3 | 11 | Tokyo | 23 | | 1 | post-1 | 12 | Tokyo | 35 | +----+---------+-------+---------+----------+ ~ $ ３、SQLファイル作成(フレームの設定を変更したい場合のパターン row between~) main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --フレームの設定を変更したい場合も見てあげましょう --たとえば、パーティションの中で前後一行をフレームにしたい場合は -- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING としてあげれば OK です \! echo 'SELECT *, SUM(likes) OVER ( PARTITION BY area ORDER BY likes ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS area_sum FROM postsの実行結果↓↓'; SELECT *, SUM(likes) OVER ( PARTITION BY area ORDER BY likes ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS area_sum FROM posts; ４、ターミナルの実行結果(フレームの設定を変更したい場合のパターン row between~) やや分かりづらいので 下記の実行結果でTokyo の例で見てあげると、 一行目はパーティションの先頭なので前の一行はないんですけれども、次の行との合計でちゃんと 12 になっています 次のレコードのフレームは前後一行ずつなので、こちらの 4 と 11 と 8 を足し合わせて ちゃんと 23 になっているので、うまくいっているようです フレームの指定方法は他にもありますが、よりきめ細かい単位で集計したい場合に便利なので、使いこなせるようになっておきましょう ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT *, SUM(likes) OVER ( PARTITION BY area ORDER BY likes ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS area_sum FROM postsの実行結果↓↓ +----+---------+-------+---------+----------+ | id | message | likes | area | area_sum | +----+---------+-------+---------+----------+ | 2 | post-2 | 8 | Fukuoka | 39 | | 9 | post-9 | 31 | Fukuoka | 39 | | 4 | post-4 | 3 | Osaka | 12 | | 6 | post-6 | 9 | Osaka | 22 | | 8 | post-8 | 10 | Osaka | 19 | | 7 | post-7 | 4 | Tokyo | 12 | | 5 | post-5 | 8 | Tokyo | 23 | | 3 | post-3 | 11 | Tokyo | 31 | | 1 | post-1 | 12 | Tokyo | 23 | +----+---------+-------+---------+----------+ ~ $ 応用15 RANK()、DENSE_RANK()を使ってみよう ※OVERを使用したときに使える １、SQLファイル作成(row numberを使用) main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --OVER を使ったときだけ使える関数があるので、よく使うものを見ていきましょう -- --ここで likes の小さい順に並び替えたあとにその連番が欲しいといった場合を考えてみます --その場合、 ROW_NUMBER() という関数が使えるので、このように書いてあげましょう --OVER の中では ORDER BY likes としてあげればいいですね --今回パーティションは全体を対象にしているので、 --パーティションを省略して ORDER BY だけになっている点にも注意しましょう \! echo 'SELECT *, ROW_NUMBER() OVER (ORDER BY likes) as num FROM postsの実行結果↓↓'; SELECT *, ROW_NUMBER() OVER (ORDER BY likes) as num FROM posts; ２、ターミナルの実行結果(row numberを使用) 下記↓↓実行結果を見ると、likes の小さい順に並び替えたあとにちゃんと連番になっています ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT *, ROW_NUMBER() OVER (ORDER BY likes) as num FROM postsの実行結果↓↓ +----+---------+-------+---------+-----+ | id | message | likes | area | num | +----+---------+-------+---------+-----+ | 4 | post-4 | 3 | Osaka | 1 | | 7 | post-7 | 4 | Tokyo | 2 | | 5 | post-5 | 8 | Tokyo | 3 | | 2 | post-2 | 8 | Fukuoka | 4 | | 6 | post-6 | 9 | Osaka | 5 | | 8 | post-8 | 10 | Osaka | 6 | | 3 | post-3 | 11 | Tokyo | 7 | | 1 | post-1 | 12 | Tokyo | 8 | | 9 | post-9 | 31 | Fukuoka | 9 | +----+---------+-------+---------+-----+ ~ $ ３、SQLファイル作成(rank、dense_rankを使用) main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --ただ、ここで連番ではなくて、順位が知りたかったとします --今回の場合、こちらが 8 で同着なので、それを考慮して順位をつけたいなら、 --そのための関数があるので、見ていきましょう --ふたつの方法があって、 RANK 関数を使うか、 DENSE_RANK 関数を使えば OK です \! echo 'SELECT *, ROW_NUMBER() OVER (ORDER BY likes) as num, RANK() OVER (ORDER BY likes) as rank, DENSE_RANK() OVER (ORDER BY likes) as dense FROM postsの実行結果↓↓'; SELECT *, ROW_NUMBER() OVER (ORDER BY likes) as num, RANK() OVER (ORDER BY likes) as rank, DENSE_RANK() OVER (ORDER BY likes) as dense FROM posts; ４、ターミナルの実行結果(rank、dense_rankを使用) 下記、実行結果を見ると、同着がちゃんと同じ順位(3番の事)になっていて、 ただ RANK() の場合はその次の順位が飛ばされていて(5になっている)、 DENSE_RANK() の場合は 次の順位が飛ばされない(4になっている)という違いがあります ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT *, ROW_NUMBER() OVER (ORDER BY likes) as num, RANK() OVER (ORDER BY likes) as rank, DENSE_RANK() OVER (ORDER BY likes) as dense FROM postsの実行結果↓↓ +----+---------+-------+---------+-----+------+-------+ | id | message | likes | area | num | rank | dense | +----+---------+-------+---------+-----+------+-------+ | 4 | post-4 | 3 | Osaka | 1 | 1 | 1 | | 7 | post-7 | 4 | Tokyo | 2 | 2 | 2 | | 5 | post-5 | 8 | Tokyo | 3 | 3 | 3 | | 2 | post-2 | 8 | Fukuoka | 4 | 3 | 3 | | 6 | post-6 | 9 | Osaka | 5 | 5 | 4 | | 8 | post-8 | 10 | Osaka | 6 | 6 | 5 | | 3 | post-3 | 11 | Tokyo | 7 | 7 | 6 | | 1 | post-1 | 12 | Tokyo | 8 | 8 | 7 | | 9 | post-9 | 31 | Fukuoka | 9 | 9 | 8 | +----+---------+-------+---------+-----+------+-------+ ~ $ 応用16 LAG()、LEAD()を扱ってみよう ※１～２、３～４は同じ処理になる １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --n 個前、 n 個後のレコードの値を求めることができる関数について見ていきましょう --1 個前のレコードの likes の値を求めたかったら LAG() を使って、このように書いてあげれば OK です --今回はパーティションは全体にして likes の小さい順で並び変えた上で集計していきましょう --1 個後のレコードの likes の値は LEAD() とすれば OK です \! echo 'SELECT *, LAG(likes, 1) OVER (ORDER BY likes) as lag, LEAD(likes, 1) OVER (ORDER BY likes) as lead FROM postsの実行結果↓↓'; SELECT *, LAG(likes, 1) OVER (ORDER BY likes) as lag, LEAD(likes, 1) OVER (ORDER BY likes) as lead FROM posts; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT *, LAG(likes, 1) OVER (ORDER BY likes) as lag, LEAD(likes, 1) OVER (ORDER BY likes) as lead FROM postsの実行結果↓↓ +----+---------+-------+---------+------+------+ | id | message | likes | area | lag | lead | +----+---------+-------+---------+------+------+ | 4 | post-4 | 3 | Osaka | NULL | 4 | | 7 | post-7 | 4 | Tokyo | 3 | 8 | | 5 | post-5 | 8 | Tokyo | 4 | 8 | | 2 | post-2 | 8 | Fukuoka | 8 | 9 | | 6 | post-6 | 9 | Osaka | 8 | 10 | | 8 | post-8 | 10 | Osaka | 9 | 11 | | 3 | post-3 | 11 | Tokyo | 10 | 12 | | 1 | post-1 | 12 | Tokyo | 11 | 31 | | 9 | post-9 | 31 | Fukuoka | 12 | NULL | +----+---------+-------+---------+------+------+ ~ $ ３、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --1 個前、 1 個後のレコードの値を出してくれるのですが、 1 の場合は省略できるので、 --このように書いても１～２と同じ意味になります \! echo 'SELECT *, LAG(likes) OVER (ORDER BY likes) as lag, LEAD(likes) OVER (ORDER BY likes) as lead FROM postsの実行結果↓↓'; SELECT *, -- LAG(likes, 1) OVER (ORDER BY likes) as lag, -- LEAD(likes, 1) OVER (ORDER BY likes) as lead LAG(likes) OVER (ORDER BY likes) as lag, LEAD(likes) OVER (ORDER BY likes) as lead FROM posts; ４、ターミナルの実行結果 実行結果で、先頭のレコードから見ていくと lag は 1 つ前のレコードの値、当然ないので NULL になります lead の場合は次のレコードの likes の値なので 4 が抽出できているのが分かります 次のレコードで見ると lag は 1 つ前のレコードの値なので 3 lead は次のレコードの値なので、ちゃんと 8 になっていますね ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT *, LAG(likes) OVER (ORDER BY likes) as lag, LEAD(likes) OVER (ORDER BY likes) as lead FROM postsの実行結果↓↓ +----+---------+-------+---------+------+------+ | id | message | likes | area | lag | lead | +----+---------+-------+---------+------+------+ | 4 | post-4 | 3 | Osaka | NULL | 4 | | 7 | post-7 | 4 | Tokyo | 3 | 8 | | 5 | post-5 | 8 | Tokyo | 4 | 8 | | 2 | post-2 | 8 | Fukuoka | 8 | 9 | | 6 | post-6 | 9 | Osaka | 8 | 10 | | 8 | post-8 | 10 | Osaka | 9 | 11 | | 3 | post-3 | 11 | Tokyo | 10 | 12 | | 1 | post-1 | 12 | Tokyo | 11 | 31 | | 9 | post-9 | 31 | Fukuoka | 12 | NULL | +----+---------+-------+---------+------+------+ ~ $ ５、SQLファイル作成（差分取得） main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO posts (message, likes, area) VALUES ('post-1', 12, 'Tokyo'), ('post-2', 8, 'Fukuoka'), ('post-3', 11, 'Tokyo'), ('post-4', 3, 'Osaka'), ('post-5', 8, 'Tokyo'), ('post-6', 9, 'Osaka'), ('post-7', 4, 'Tokyo'), ('post-8', 10, 'Osaka'), ('post-9', 31, 'Fukuoka'); --前のレコードからの差分なども表現することができます --どうするかというと、 likes から 1 つ前の likes を引いてあげれば差分になるはずです \! echo 'SELECT *, likes - LAG(likes) OVER (ORDER BY likes) as diff FROM postsの実行結果↓↓'; SELECT *, -- LAG(likes, 1) OVER (ORDER BY likes) as lag, -- LEAD(likes, 1) OVER (ORDER BY likes) as lead -- LAG(likes) OVER (ORDER BY likes) as lag, -- LEAD(likes) OVER (ORDER BY likes) as lead likes - LAG(likes) OVER (ORDER BY likes) as diff FROM posts; ６、ターミナルの実行結果（差分取得） 実行結果を見ると、次のレコードで前のレコードからどれだけ増えたかが分かります 3 が 4 になったので、差分は 1 4 が 8 になったので、差分は 4 といった具合ですね ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT *, likes - LAG(likes) OVER (ORDER BY likes) as diff FROM postsの実行結果↓↓ +----+---------+-------+---------+------+ | id | message | likes | area | diff | +----+---------+-------+---------+------+ | 4 | post-4 | 3 | Osaka | NULL | | 7 | post-7 | 4 | Tokyo | 1 | | 5 | post-5 | 8 | Tokyo | 4 | | 2 | post-2 | 8 | Fukuoka | 0 | | 6 | post-6 | 9 | Osaka | 1 | | 8 | post-8 | 10 | Osaka | 1 | | 3 | post-3 | 11 | Tokyo | 1 | | 1 | post-1 | 12 | Tokyo | 1 | | 9 | post-9 | 31 | Fukuoka | 19 | +----+---------+-------+---------+------+ ~ $ 応用17 トランザクションについて理解しよう 次はトランザクションという仕組みについて見ていきいのですが、ちょっと下記の図で説明していきましょう では posts テーブルがこのようにあったとして、 post-1 に最後にしたいいねが実は間違いで post-2 にいいねするはずだった、という場合を考えてみます その場合、データを修正するには 下記の図のように、post-1 から 1 いいね引いてあげて（12→11）、 post-2 のほうに 1 いいね足してあげれば（8→9）良さそうです。 コードで言うと、下記の図のUPDATEを利用する ただ、これらのコードを実行する途中で、下記の図みたいになんらかの障害が起きて 2 番目の UPDATE が実行されなかったり、もしくは他のユーザーがデータベースを操作していて、 post-2 のデータをうっかり50とかに書き換えていたりしていた場合、データの整合性が取れなくなってしまいます そこで、上記の図みたいに途中で他の操作をされたくない一連の処理をする場合、 下記の図みたいに START TRANSACTION と COMMIT で囲ってあげれば OK です そうすると、これらの処理をしている間は他の処理を受け付けなくなるので、 データの整合性が保たれるといった仕組みですね なお、下記の図みたいにひとつの UPDATE を実行して、 post-1 のいいねが変更(12→11)されたあとに、 次のUPDATE文で障害が発生してこちらの処理が途中で止まってしまった場合、 ROLLBACK という命令を使うことで途中まで処理が進んでいても、先ほどの変更を取り越すこともできます 上記の図のような実装をするときはエラーを検出しないといけないので、 他の言語と組み合わせる必要があって、たとえば PHP だったら、下記の図のようなコードを使うことになります。 他の言語と組み合わせる方法については追々学習していくことになりますが、 一連の処理でデータの整合性を保つにはトランザクションを使えば良いということを知っておきましょう 応用18 トランザクションを使ってみよう １、SQLファイル作成(commit) main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('post-1', 12), ('post-2', 8), ('post-3', 11), ('post-4', 3), ('post-5', 5), ('post-6', 9), ('post-7', 4), ('post-8', 10), ('post-9', 31); --前回の例の通りに実装をしてみましょう --post-1 に付けた最後のいいねが間違いだったとします --これを修正するには id が 1 のレコードに関しては、いいねを 1 減らす、 --そして id が 2 のレコードについては、いいねを 1 増やすと書いてあげれば OK ですね --ただ、この途中で邪魔が入って欲しくない場合、トランザクションを使えばいいですね --START TRANSACTION としてあげて、処理の終わりで COMMIT としてあげれば OK です START TRANSACTION; UPDATE posts SET likes = likes - 1 WHERE id = 1; UPDATE posts SET likes = likes + 1 WHERE id = 2; COMMIT; \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; ２、ターミナルの実行結果(commit) 下記、ちゃんと 1 いいね移動して 11(12→11) 、 9(8→9) になっているので OK そうです ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | post-1 | 11 | | 2 | post-2 | 9 | | 3 | post-3 | 11 | | 4 | post-4 | 3 | | 5 | post-5 | 5 | | 6 | post-6 | 9 | | 7 | post-7 | 4 | | 8 | post-8 | 10 | | 9 | post-9 | 31 | +----+---------+-------+ ~ $ ３、SQLファイル作成(rollback) main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, PRIMARY KEY (id) ); INSERT INTO posts (message, likes) VALUES ('post-1', 12), ('post-2', 8), ('post-3', 11), ('post-4', 3), ('post-5', 5), ('post-6', 9), ('post-7', 4), ('post-8', 10), ('post-9', 31); --こちらid=2のUPDATE処理がうまくいかなかったとします --その場合ですが、 COMMIT ではなくて ROLLBACK としてあげれば、こちらの処理はなかったことにできるはずです START TRANSACTION; UPDATE posts SET likes = likes - 1 WHERE id = 1; -- UPDATE posts SET likes = likes + 1 WHERE id = 2; ROLLBACK; \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; ４、ターミナルの実行結果(rollback) 下記、id が 1 のレコードについて UPDATE をかけたのにも関わらず、 12 のまま(11になっていない)なのが分かります ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+-------+ | id | message | likes | +----+---------+-------+ | 1 | post-1 | 12 | | 2 | post-2 | 8 | | 3 | post-3 | 11 | | 4 | post-4 | 3 | | 5 | post-5 | 5 | | 6 | post-6 | 9 | | 7 | post-7 | 4 | | 8 | post-8 | 10 | | 9 | post-9 | 31 | +----+---------+-------+ ~ $ 応用19 テーブルを分割してみよう 今まで見てきた posts テーブルですが、投稿にコメントを付けたくなった場合も考えてみましょう。 その場合いくつかの管理方法がありますが、 ひとつはこちらにカラムを追加してコメントも管理できるようにする方法ですね。 ただ、コメントは複数付けられるのが普通なので、その度にカラムを増やしていってもいいのですが、 いくつ増やせばいいか分からないですし、コメントが付かないレコードも出てくるので、 このあたりの領域が無駄になってしまいます。 また、別の方法としては、カラムはひとつだけ増やして post-1 に別のコメントが付いたら このようにレコードを追加するという方法もあります。 ただ、この場合 post-1 の箇所が重複しているので、 その分容量が大きくなったり、またこの post-1 の文言を変更したくなったら、 全てのレコードを更新しなくてはいけないので、処理も遅くなってしまいます。 そこで、テーブルを分割してデータを管理しやすくする、といったテクニックがよく使われます。 今回だと posts テーブルのほかに comments テーブルを作ってあげて、 こちらにはどの post に紐づくかのカラムを持たせてあげればいいですね。 またこちらにも主キーidを設定しておくと、個々のレコードが特定できるようになって便利かと思います。 このように、テーブルを分離してあげれば先ほど見たような無駄な領域ができることも無くなりますし、 たとえば post-1 の文言を更新したくなったときも、この一箇所を更新するだけでいいですね。 なお、このようにデータを管理しやすくするためにテーブルを分割していくことを正規化という 応用20 複数のテーブルを扱ってみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); DROP TABLE IF EXISTS comments; CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), PRIMARY KEY (id) ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); INSERT INTO comments (post_id, comment) VALUES (1, 'comment-1-1'), (1, 'comment-1-2'), (3, 'comment-3-1'), (4, 'comment-4-1'); \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM commentsの実行結果↓↓'; SELECT * FROM comments; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+ | id | message | +----+---------+ | 1 | post-1 | | 2 | post-2 | | 3 | post-3 | +----+---------+ SELECT * FROM commentsの実行結果↓↓ +----+---------+-------------+ | id | post_id | comment | +----+---------+-------------+ | 1 | 1 | comment-1-1 | | 2 | 1 | comment-1-2 | | 3 | 3 | comment-3-1 | | 4 | 4 | comment-4-1 | +----+---------+-------------+ ~ $ 応用21 内部結合､外部結合について見ていこう post-1 には 2 つ comment がついていて、 post-2 には comment なし、 そして post-3 には comment が 1 件、そしてなぜか posts に紐づいていない comment が 1 件ある状態です。 では、ここでこれらのテーブルを結合させて、データを抽出していきたいのですが、大きく 2 つの方法があります。 ひとつ目は内部結合という方法で、これは 2 つのテーブルに共通のデータを取得する方法です。 今回だと posts にも comments にもデータがあるのは post-1 と post-3 だけになるので、 内部結合をすると、結果はこのようになります。 また、 2 つ目の方法は外部結合という方法です。 外部結合ですが、左のテーブルを軸にした左外部結合、右のテーブルを軸にした右外部結合があります。 左外部結合の場合ですが、 posts を軸にしてそれに対応する comment があれば取得するので、このようになります。 また、 comment がない箇所は NULL になるので、その点に注意しておきましょう。 comment がついているかどうかに関わらず、投稿の一覧ページを作るときなどに使えばいいですね。 では、一方右外部結合ですが、今度は comment だけ全て取得して、 そこに紐づいた投稿があれば取得するので、結果はこのようになります。 また、該当する投稿がなければそこは NULL になるので、その点にも注意しておきましょう。 こちらはそこに紐づいた投稿があるかどうかに関わらず、 comment の一覧を表示したいときなどに使えばいいですね。 応用22 内部結合を使ってみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); DROP TABLE IF EXISTS comments; CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), PRIMARY KEY (id) ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); INSERT INTO comments (post_id, comment) VALUES (1, 'comment-1-1'), (1, 'comment-1-2'), (3, 'comment-3-1'), (4, 'comment-4-1'); --posts と comments を内部結合したい場合は、 posts INNER JOIN comments としてあげて、 --紐づけるカラムを ON で表現してあげます。 --INNER を省略すると内部結合になるので、単に JOIN と書いても上と全く同じ意味になります。 --特定のカラムだけを抽出したい場合は、テーブル名を付けてあげて、抽出すれば OK です。 --posts の id と posts の message と comments の comment だけ抽出したい場合は、 --下記のように書いてあげれば OK です。 --id は両方にあるのでテーブル名を付ける必要がありますが、 --message や comment はそれぞれのテーブルにしかないので、省略してあげることもできます --内部結合は型が一致しない場合は、エラーではないが警告が出る \! echo 'SELECT posts.id, message, comment FROM posts JOIN comments ON posts.id = comments.post_idの実行結果↓↓'; SELECT -- * -- posts.id, posts.message, comments.comment posts.id, message, comment FROM -- posts INNER JOIN comments ON posts.id = comments.post_id; posts JOIN comments ON posts.id = comments.post_id; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT posts.id, message, comment FROM posts JOIN comments ON posts.id = comments.post_idの実行結果↓↓ +----+---------+-------------+ | id | message | comment | +----+---------+-------------+ | 1 | post-1 | comment-1-1 | | 1 | post-1 | comment-1-2 | | 3 | post-3 | comment-3-1 | +----+---------+-------------+ ~ $ 応用23 外部結合を使ってみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); DROP TABLE IF EXISTS comments; CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), PRIMARY KEY (id) ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); INSERT INTO comments (post_id, comment) VALUES (1, 'comment-1-1'), (1, 'comment-1-2'), (3, 'comment-3-1'), (4, 'comment-4-1'); --全てのカラムを抽出 \! echo 'SELECT * FROM posts JOIN comments ON posts.id = comments.post_idの実行結果↓↓'; SELECT * FROM posts JOIN comments ON posts.id = comments.post_id; --LEFT OUTER JOIN とすればいいのですが、実は OUTER は省略できるので、 --単に LEFT JOIN と書いてあげても左外部結合になります。 --左外部結合では posts から全てレコードが抽出されていて、 --それに関連する comment があれば取得して、そしてなければ NULL で埋めてくれます。 \! echo 'SELECT * FROM posts LEFT JOIN comments ON posts.id = comments.post_idの実行結果↓↓'; SELECT * FROM -- posts LEFT OUTER JOIN comments ON posts.id = comments.post_id; posts LEFT JOIN comments ON posts.id = comments.post_id; --右外部結合は、RIGHT JOIN --下記外部結合ですが、まず comments から全てのレコードが抽出されて、 --それに関連する投稿があれば取得して、なければ NULL で埋めてくれます。 \! echo 'SELECT * FROM posts RIGHT JOIN comments ON posts.id = comments.post_idの実行結果↓↓'; SELECT * FROM posts RIGHT JOIN comments ON posts.id = comments.post_id; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM posts JOIN comments ON posts.id = comments.post_idの実行結果↓↓ +----+---------+----+---------+-------------+ | id | message | id | post_id | comment | +----+---------+----+---------+-------------+ | 1 | post-1 | 1 | 1 | comment-1-1 | | 1 | post-1 | 2 | 1 | comment-1-2 | | 3 | post-3 | 3 | 3 | comment-3-1 | +----+---------+----+---------+-------------+ SELECT * FROM posts LEFT JOIN comments ON posts.id = comments.post_idの実行結果↓↓ +----+---------+------+---------+-------------+ | id | message | id | post_id | comment | +----+---------+------+---------+-------------+ | 1 | post-1 | 1 | 1 | comment-1-1 | | 1 | post-1 | 2 | 1 | comment-1-2 | | 3 | post-3 | 3 | 3 | comment-3-1 | | 2 | post-2 | NULL | NULL | NULL | +----+---------+------+---------+-------------+ SELECT * FROM posts RIGHT JOIN comments ON posts.id = comments.post_idの実行結果↓↓ +------+---------+----+---------+-------------+ | id | message | id | post_id | comment | +------+---------+----+---------+-------------+ | 1 | post-1 | 1 | 1 | comment-1-1 | | 1 | post-1 | 2 | 1 | comment-1-2 | | 3 | post-3 | 3 | 3 | comment-3-1 | | NULL | NULL | 4 | 4 | comment-4-1 | +------+---------+----+---------+-------------+ ~ $ 応用24 外部キー制約を設定する １、SQLファイル作成(comment-4-1でエラーになる) main.sql --今まで見てきた comments のデータですが、 post-4 がないのに、それに対して --comment が付けられるのは少しおかしいような気がします。 --こうした変なデータが入り込まないようにするための外部キー制約について見ていきましょう。 --今回 comments テーブルの post_id に対して、 posts テーブルの id に --その値が存在しなかったら弾いてくれるように設定しておきましょう --どうするかというと、 FOREIGN KEY としてあげて、 post_id に対して --posts テーブルの id を参照して、そこに値がなければ弾いてねとREFERRENCESを使う --外部キーの制約によって変なデータが追加できなくなります DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), PRIMARY KEY (id), FOREIGN KEY (post_id) REFERENCES posts(id) ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); INSERT INTO comments (post_id, comment) VALUES (1, 'comment-1-1'), (1, 'comment-1-2'), (3, 'comment-3-1'); (4, 'comment-4-1'); SELECT * FROM posts; SELECT * FROM comments; ２、ターミナルの実行結果(comment-4-1でエラーになる) ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql ERROR 1064 (42000) at line 34: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '4, 'comment-4-1')' at line 1 ~ $ ３、SQLファイル作成(comment-4-1でコメントアウトして、エラーを防ぐ) main.sql --posts テーブルは今 comments テーブルに紐づいているので、このまま削除すると --comments テーブルのデータが宙ぶらりんになって整合性が取れなくなるので、先にcommentテーブルを削除する --postsから削除するとエラーになる DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), PRIMARY KEY (id), FOREIGN KEY (post_id) REFERENCES posts(id) ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); INSERT INTO comments (post_id, comment) VALUES (1, 'comment-1-1'), (1, 'comment-1-2'), (3, 'comment-3-1'); -- (4, 'comment-4-1'); \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM commentsの実行結果↓↓'; SELECT * FROM comments; ４、ターミナルの実行結果(comment-4-1でコメントアウトして、エラーを防ぐ) ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+ | id | message | +----+---------+ | 1 | post-1 | | 2 | post-2 | | 3 | post-3 | +----+---------+ SELECT * FROM commentsの実行結果↓↓ +----+---------+-------------+ | id | post_id | comment | +----+---------+-------------+ | 1 | 1 | comment-1-1 | | 2 | 1 | comment-1-2 | | 3 | 3 | comment-3-1 | +----+---------+-------------+ ~ $ 応用25 データの整合性を保とう １、SQLファイル作成（DELETEエラーになる） main.sql --外部キーが設定された状態で posts テーブルのほうに変更を加えたかった場合を見てみます。 --こちらで post-3 を削除してみましょう。 --id が 3 のレコードを削除すればいいので、 DELETE FROM posts WHERE id = 3 としてあげればいいでしょう。 --ただ、この場合 post-3 に紐づく comment がこちらに存在しているので、 --データ整合性を保持するために、このままではうまくいかずエラーになる DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), PRIMARY KEY (id), FOREIGN KEY (post_id) REFERENCES posts(id) -- ON DELETE CASCADE -- ON UPDATE CASCADE ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); INSERT INTO comments (post_id, comment) VALUES (1, 'comment-1-1'), (1, 'comment-1-2'), (3, 'comment-3-1'); DELETE FROM posts WHERE id = 3; --UPDATE posts SET id = 100 WHERE id = 1; \! echo 'の実行結果↓↓'; SELECT * FROM posts; \! echo 'の実行結果↓↓'; SELECT * FROM comments; ２、ターミナルの実行結果（DELETEエラーになる） ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql ERROR 1451 (23000) at line 34: Cannot delete or update a parent row: a foreign key constraint fails (`myapp`.`comments`, CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)) ~ $ ３、SQLファイル作成（ON DELETE　CASCADE設定） main.sql DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); --ここでデータの整合性を取るために、 posts のほうでレコードが削除されたら、 --合わせて comments のほうでも紐づくレコードが削除されるように設定してみましょう --そういったこともできて、どうするかというと、こちらで ON DELETE CASCADE としてあげます。 --実行すると、post-3 が削除されていて、 comment からも関連するレコードが消えているのが分かります。 CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), PRIMARY KEY (id), FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE -- ON UPDATE CASCADE ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); INSERT INTO comments (post_id, comment) VALUES (1, 'comment-1-1'), (1, 'comment-1-2'), (3, 'comment-3-1'); DELETE FROM posts WHERE id = 3; --UPDATE posts SET id = 100 WHERE id = 1; \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM commentsの実行結果↓↓'; SELECT * FROM comments; ４、ターミナルの実行結果（ON DELETE　CASCADE設定） ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+ | id | message | +----+---------+ | 1 | post-1 | | 2 | post-2 | +----+---------+ SELECT * FROM commentsの実行結果↓↓ +----+---------+-------------+ | id | post_id | comment | +----+---------+-------------+ | 1 | 1 | comment-1-1 | | 2 | 1 | comment-1-2 | +----+---------+-------------+ ~ $ ５、SQLファイル作成（ON UPDATE　CASCADE設定） main.sql DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); --DELETE だけではなくて、 UPDATE に関しても設定することができます --こちらで ON UPDATE CASCADE としてみましょう --こうしておくと、 posts のデータが更新されると、こちらの紐づくデータも更新されるようになります。 CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), PRIMARY KEY (id), FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); INSERT INTO comments (post_id, comment) VALUES (1, 'comment-1-1'), (1, 'comment-1-2'), (3, 'comment-3-1'); --DELETE FROM posts WHERE id = 3; --今回紐づいているのは id なので、 id を更新してみましょう。 --こちらの id が 100 になって、それに関連するデータも 100 で更新されているのが分かります。 --外部キー制約を使うと、このようにデータの整合性が取れるようになるので、慣れておきましょう。 UPDATE posts SET id = 100 WHERE id = 1; \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM commentsの実行結果↓↓'; SELECT * FROM comments; ６、ターミナルの実行結果（ON UPDATE　CASCADE設定） ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +-----+---------+ | id | message | +-----+---------+ | 2 | post-2 | | 3 | post-3 | | 100 | post-1 | +-----+---------+ SELECT * FROM commentsの実行結果↓↓ +----+---------+-------------+ | id | post_id | comment | +----+---------+-------------+ | 1 | 100 | comment-1-1 | | 2 | 100 | comment-1-2 | | 3 | 3 | comment-3-1 | +----+---------+-------------+ ~ $ 応用26 LAST_INSERT_ID()を使ってみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), PRIMARY KEY (id), FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); INSERT INTO comments (post_id, comment) VALUES (1, 'comment-1-1'), (1, 'comment-1-2'), (3, 'comment-3-1'); --このあたりで新しく投稿を追加したとしましょう。 --その場合ですが、 INSERT 文を使ってあげればいいですね --ただ、ここで、 post_id が何になるかですが、今追加した投稿は 4番目なので 4 としてあげればいいでしょう。 INSERT INTO posts (message) VALUES ('new post!'); ----では、 post_id は 4 にして comment は適当に 'new comment' としてあげましょう。 --ただ、これでもいいのですが、 comment を挿入するたびにいちいち今のように数えるのも面倒だったりします。 --INSERT INTO comments (post_id, comment) VALUES --(4, 'new comment'); --そこで MySQL では直前に挿入されたレコードの id を調べる命令が用意されていて、 --LAST_INSERT_id としてあげれば自動的にこちらには 4 が入ってくれるはずです。 --LAST_INSERT_id も便利なので、慣れておいてください。 INSERT INTO comments (post_id, comment) VALUES (LAST_INSERT_ID(), 'new comment'); \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM commentsの実行結果↓↓'; SELECT * FROM comments; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+-----------+ | id | message | +----+-----------+ | 1 | post-1 | | 2 | post-2 | | 3 | post-3 | | 4 | new post! | +----+-----------+ SELECT * FROM commentsの実行結果↓↓ +----+---------+-------------+ | id | post_id | comment | +----+---------+-------------+ | 1 | 1 | comment-1-1 | | 2 | 1 | comment-1-2 | | 3 | 3 | comment-3-1 | | 4 | 4 | new comment | +----+---------+-------------+ ~ $ 応用27 コメントにコメントをつけよう １、SQLファイル作成 main.sql --次ですが、今の投稿とコメントがどういう状態か、 --ちょっと下記のコメント図でコメントでまとめてみましょう。 --3 つ投稿があって、 post-1 と post-3 に comment がついている状態ですね。 --post-1 には 2 つ comment がついていて、 post-3 には 1 つ comment がついてる状態です。 --ではここで comment に対して comment が付けられるようにしたかったとしましょう。 --たとえば comment-1-2 に対してさらに 2 つ comment が付いたとします。 --さらにこちらにはもう 1 つ comment がついたとしましょう。 --その場合、どうデータを管理していくかですが、 comment がどの親を comment に持つかを保持しておけば良さそうです。 -- /* post-1 comment-1-1 comment-1-2 comment-1-2-1 comment-1-2-1-1 comment-1-2-2 post-2 post-3 comment-3-1 */ DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); --では、こちらに parent_id というカラムを用意してあげましょう。 CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), parent_id INT, PRIMARY KEY (id), FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); --その上でまず、この 3 つの comment ですが、 --親となる comment がないのでこちらに関しては NULL としておきましょう。 --その上で comment-1-2 に対して 2 つ comment を付けたので、こちらでレコードを挿入してあげます。 --post は 1 に関するものでよくて、 comment はちょっと更新してあげて、 --そして parent_id ですが、 2 番目の comment なので、こちらは 2 にしてあげればいいでしょう。 --それからさらに comment-1-2-1 に対してもうひとつ comment を付けたので、レコードを挿入してあげましょう。 --post_id は 1 でよくて、こちらは更新してあげて、そして parent_id ですが、 -- 4 番目の comment についているので、こちらを 4 にしてあげれば良いでしょう。 INSERT INTO comments (post_id, comment, parent_id) VALUES (1, 'comment-1-1', NULL), (1, 'comment-1-2', NULL), (3, 'comment-3-1', NULL), (1, 'comment-1-2-1', 2), (1, 'comment-1-2-2', 2), (1, 'comment-1-2-1-1', 4); \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM commentsの実行結果↓↓'; SELECT * FROM comments; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+ | id | message | +----+---------+ | 1 | post-1 | | 2 | post-2 | | 3 | post-3 | +----+---------+ SELECT * FROM commentsの実行結果↓↓ +----+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +----+---------+-----------------+-----------+ | 1 | 1 | comment-1-1 | NULL | | 2 | 1 | comment-1-2 | NULL | | 3 | 3 | comment-3-1 | NULL | | 4 | 1 | comment-1-2-1 | 2 | | 5 | 1 | comment-1-2-2 | 2 | | 6 | 1 | comment-1-2-1-1 | 4 | +----+---------+-----------------+-----------+ ~ $ 応用28 コメントのコメントを抽出してみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), parent_id INT, PRIMARY KEY (id), FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); INSERT INTO comments (post_id, comment, parent_id) VALUES (1, 'comment-1-1', NULL), (1, 'comment-1-2', NULL), (3, 'comment-3-1', NULL), (1, 'comment-1-2-1', 2), (1, 'comment-1-2-2', 2), (1, 'comment-1-2-1-1', 4); /* post-1 comment-1-1 comment-1-2 comment-1-2-1 comment-1-2-1-1 comment-1-2-2 post-2 post-3 comment-3-1 */ \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM commentsの実行結果↓↓'; SELECT * FROM comments; --comment に comment が付けられるようになったので、次は 2 番目の comment に付いているすべての comment を抽出する方法について考えてみましょう。 --その場合、まず 2 番目の comment を親に持つ comment を抽出すればいいので、このようにしてあげるとこちらとこちらの comment が取得できるはずです。 \! echo 'SELECT * FROM comments WHERE parent_id = 2の実行結果↓↓'; SELECT * FROM comments WHERE parent_id = 2; --では、その次の階層の comment を取得したいのですが、こちらの comments テーブルから parent_id がここで抽出した id と一致するものを抽出してあげればいいですね。 --したがって comments テーブルとこちらの結果を comments テーブルの parent_id とこの結果の id で内部結合してあげれば良さそうです。 --では、この結果を t というテーブルだと仮定して、ちょっとクエリを組み立ててみましょう。 --comments から抽出したいので、このように書いてあげて t を内部結合してあげます。 --その上で、 comments テーブルの parent_id と t の id を紐づけてあげれば良いでしょう。 --また、この t ですが、こちらのクエリを結果なので、サブクエリを使って、ちょっと複雑になりますが、ここに書いてあげましょう。 \! echo 'SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.idの実行結果↓↓'; SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.id; --あとは、これらの結果を縦にくっつければいいので、 UNION ALL を使ってあげれば良いでしょう。 --ただ、見ての通り SQL が長くなりすぎますし、もっと深い階層に comment が付いたらさらに長くなってしまいます \! echo 'SELECT * FROM comments WHERE parent_id = 2 UNION ALL SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.idの実行結果↓↓'; SELECT * FROM comments WHERE parent_id = 2 UNION ALL SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.id; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+ | id | message | +----+---------+ | 1 | post-1 | | 2 | post-2 | | 3 | post-3 | +----+---------+ SELECT * FROM commentsの実行結果↓↓ +----+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +----+---------+-----------------+-----------+ | 1 | 1 | comment-1-1 | NULL | | 2 | 1 | comment-1-2 | NULL | | 3 | 3 | comment-3-1 | NULL | | 4 | 1 | comment-1-2-1 | 2 | | 5 | 1 | comment-1-2-2 | 2 | | 6 | 1 | comment-1-2-1-1 | 4 | +----+---------+-----------------+-----------+ SELECT * FROM comments WHERE parent_id = 2の実行結果↓↓ +----+---------+---------------+-----------+ | id | post_id | comment | parent_id | +----+---------+---------------+-----------+ | 4 | 1 | comment-1-2-1 | 2 | | 5 | 1 | comment-1-2-2 | 2 | +----+---------+---------------+-----------+ SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.idの実行結果↓↓ +----+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +----+---------+-----------------+-----------+ | 6 | 1 | comment-1-2-1-1 | 4 | +----+---------+-----------------+-----------+ SELECT * FROM comments WHERE parent_id = 2 UNION ALL SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.idの実行結果↓↓ +----+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +----+---------+-----------------+-----------+ | 4 | 1 | comment-1-2-1 | 2 | | 5 | 1 | comment-1-2-2 | 2 | | 6 | 1 | comment-1-2-1-1 | 4 | +----+---------+-----------------+-----------+ ~ $ ３、UNION ALLについて ・ORDER BYまたLIMITを使う場合、SELECTに括弧が必要になる ・UNION ALLでレコードを連結する場合、カラム数を揃える必要がある 応用29 CTEを使ってみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), parent_id INT, PRIMARY KEY (id), FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); INSERT INTO comments (post_id, comment, parent_id) VALUES (1, 'comment-1-1', NULL), (1, 'comment-1-2', NULL), (3, 'comment-3-1', NULL), (1, 'comment-1-2-1', 2), (1, 'comment-1-2-2', 2), (1, 'comment-1-2-1-1', 4); /* post-1 comment-1-1 comment-1-2 comment-1-2-1 comment-1-2-1-1 comment-1-2-2 post-2 post-3 comment-3-1 */ \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM commentsの実行結果↓↓'; SELECT * FROM comments; \! echo 'SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.idの実行結果↓↓'; SELECT * FROM comments WHERE parent_id = 2 UNION ALL SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.id; --前回の28で見たような階層が深くなっていくデータを処理したい場合、 CTE という仕組みが使えます。 --CTE は Common Table Expression の略で、クエリ内で使える一時的なテーブルのことですね。 --CTE には再帰的な CTE と、再帰的ではない CTE があって、再帰的な CTE が今回実現したい、 --階層が深くなっていくデータを処理するための方法になります。 --また、再帰的ではない CTE はサブクエリを分かりやすく書き換えるためのものですね。 --せっかくなので、再帰的ではない CTE も見ておきましょう。 --では、前回見た上記のクエリで「サブクエリ」を使っているので、サブクエリを CTE で書き換えてみましょう。 --再帰的ではない CTE を使うには上のほうで WITH としてあげて、 CTE の名前を好きに付けてあげて、 -- AS としてあげて、 CTE としたいサブクエリをここに書いてあげます。 --このクエリを t として下のクエリで使えるようになるので、このあたりはこのように書き換えてあげれば OK でしょう。 --こうすると、この上と全く同じ意味になります。 --CTE はここからここまでがひとつの命令になるので、セミコロンは最後のひとつだけになります。 --再帰的ではない CTE を使うとサブクエリを上に持ってきて、やや分かりやすく書くことができるので、 --使い方に慣れておきましょう \! echo 'WITH t AS (SELECT * FROM comments WHERE parent_id = 2) SELECT comments.* FROM comments JOIN t ON comments.parent_id = t.idの実行結果↓↓'; WITH t AS ( SELECT * FROM comments WHERE parent_id = 2 ) SELECT comments.* FROM comments JOIN t ON comments.parent_id = t.id; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+ | id | message | +----+---------+ | 1 | post-1 | | 2 | post-2 | | 3 | post-3 | +----+---------+ SELECT * FROM commentsの実行結果↓↓ +----+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +----+---------+-----------------+-----------+ | 1 | 1 | comment-1-1 | NULL | | 2 | 1 | comment-1-2 | NULL | | 3 | 3 | comment-3-1 | NULL | | 4 | 1 | comment-1-2-1 | 2 | | 5 | 1 | comment-1-2-2 | 2 | ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+ | id | message | +----+---------+ | 1 | post-1 | | 2 | post-2 | | 3 | post-3 | +----+---------+ SELECT * FROM commentsの実行結果↓↓ +----+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +----+---------+-----------------+-----------+ | 1 | 1 | comment-1-1 | NULL | | 2 | 1 | comment-1-2 | NULL | | 3 | 3 | comment-3-1 | NULL | | 4 | 1 | comment-1-2-1 | 2 | | 5 | 1 | comment-1-2-2 | 2 | | 6 | 1 | comment-1-2-1-1 | 4 | +----+---------+-----------------+-----------+ SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.idの実行結果↓↓ +----+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +----+---------+-----------------+-----------+ | 4 | 1 | comment-1-2-1 | 2 | | 5 | 1 | comment-1-2-2 | 2 | | 6 | 1 | comment-1-2-1-1 | 4 | +----+---------+-----------------+-----------+ WITH t AS (SELECT * FROM comments WHERE parent_id = 2) SELECT comments.* FROM comments JOIN t ON comments.parent_id = t.idの実行結果↓↓ +----+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +----+---------+-----------------+-----------+ | 6 | 1 | comment-1-2-1-1 | 4 | +----+---------+-----------------+-----------+ ~ $ 応用30 再帰的なCTEを組み立てよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), parent_id INT, PRIMARY KEY (id), FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); INSERT INTO comments (post_id, comment, parent_id) VALUES (1, 'comment-1-1', NULL), (1, 'comment-1-2', NULL), (3, 'comment-3-1', NULL), (1, 'comment-1-2-1', 2), (1, 'comment-1-2-2', 2), (1, 'comment-1-2-1-1', 4); /* post-1 comment-1-1 comment-1-2 comment-1-2-1 comment-1-2-1-1 comment-1-2-2 post-2 post-3 comment-3-1 */ \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM commentsの実行結果↓↓'; SELECT * FROM comments; \! echo 'SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.idの実行結果↓↓'; SELECT * FROM comments WHERE parent_id = 2 UNION ALL SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.id; --上のSQLを再起的な CTEでかいていきます --どうするかというと WITH RECURSIVE としてあげて CTE の名前を書いてあげます。 --そして最初に実行する処理、 2 回目以降に再起的に実行する処理を書いて UNION ALL で繋いであげます。 --では、最初に実行する処理ですが 2 番目のコメントに付いた最初の階層のコメントを --取得すればいいので、このクエリをそのまま貼り付けてあげれば OK でしょう。 --次にその下の階層を取得する処理なので、とりあえずこちらをそのまま書いてあげましょう。 --では、これがどう処理されるかですが、最初の階層を取得するとそれを t にしてこちらの処理をしてくれます。 --したがってこちらは t で表現できるので、このようにしてあげればいいですね。 --またこちらの処理が終わったらその結果を t にして、 --結果が無くなるまでこちらの処理を再起的に実行してくれるので、 --どれだけ階層が深くなっても、コメントを取得してくれるはずです。 --あとはその結果をまとめて表示したいので、このように書いてあげましょう --それから、ここからここまでがひとつの命令になるので、 --セミコロンは最後にひとつとなる点にも注意しておいてください \! echo 'WITH RECURSIVE t AS (SELECT * FROM comments WHERE parent_id = 2 UNION ALL SELECT comments.* FROM comments JOIN t ON comments.parent_id = t.id) SELECT * FROM tの実行結果↓↓'; WITH RECURSIVE t AS ( -- n = 1 最初に実行する処理 SELECT * FROM comments WHERE parent_id = 2 UNION ALL -- n >= 2 2 回目以降に再起的に実行する処理 SELECT comments.* FROM comments JOIN t ON comments.parent_id = t.id ) SELECT * FROM t; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+ | id | message | +----+---------+ | 1 | post-1 | | 2 | post-2 | | 3 | post-3 | +----+---------+ SELECT * FROM commentsの実行結果↓↓ +----+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +----+---------+-----------------+-----------+ | 1 | 1 | comment-1-1 | NULL | | 2 | 1 | comment-1-2 | NULL | | 3 | 3 | comment-3-1 | NULL | | 4 | 1 | comment-1-2-1 | 2 | | 5 | 1 | comment-1-2-2 | 2 | | 6 | 1 | comment-1-2-1-1 | 4 | +----+---------+-----------------+-----------+ SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.idの実行結果↓↓ +----+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +----+---------+-----------------+-----------+ | 4 | 1 | comment-1-2-1 | 2 | | 5 | 1 | comment-1-2-2 | 2 | | 6 | 1 | comment-1-2-1-1 | 4 | +----+---------+-----------------+-----------+ WITH RECURSIVE t AS (SELECT * FROM comments WHERE parent_id = 2 UNION ALL SELECT comments.* FROM comments JOIN t ON comments.parent_id = t.id) SELECT * FROM tの実行結果↓↓ +------+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +------+---------+-----------------+-----------+ | 4 | 1 | comment-1-2-1 | 2 | | 5 | 1 | comment-1-2-2 | 2 | | 6 | 1 | comment-1-2-1-1 | 4 | +------+---------+-----------------+-----------+ ~ $ 応用31 階層が深いコメントを抽出しよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); CREATE TABLE comments ( id INT NOT NULL AUTO_INCREMENT, post_id INT, comment VARCHAR(140), parent_id INT, PRIMARY KEY (id), FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); --せっかくなのでさらに深い階層にコメントを付けてみて、それも取得できるか見てあげましょう。 --comments テーブルにもうひとつレコードを挿入してあげます --'new comment' としてあげて、 parent_id ですが、最後のコメントに付けるので、 --6 番目にしてあげればいいですね --実行してみると、ちゃんと深い階層のコメントまで一気に取得できています。 --再起的な処理はやや難しいので、初回で全てを理解する必要はないのですが、 --こうした階層が深いデータを処理したいときには CTE が便利ということを知っておくといいでしょう INSERT INTO comments (post_id, comment, parent_id) VALUES (1, 'comment-1-1', NULL), (1, 'comment-1-2', NULL), (3, 'comment-3-1', NULL), (1, 'comment-1-2-1', 2), (1, 'comment-1-2-2', 2), (1, 'comment-1-2-1-1', 4), (1, 'new comment', 6);; /* post-1 comment-1-1 comment-1-2 comment-1-2-1 comment-1-2-1-1 comment-1-2-2 post-2 post-3 comment-3-1 */ \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM commentsの実行結果↓↓'; SELECT * FROM comments; \! echo 'SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.idの実行結果↓↓'; SELECT * FROM comments WHERE parent_id = 2 UNION ALL SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.id; --上のSQLを再起的な CTEでかいていきます --どうするかというと WITH RECURSIVE としてあげて CTE の名前を書いてあげます。 --そして最初に実行する処理、 2 回目以降に再起的に実行する処理を書いて UNION ALL で繋いであげます。 --では、最初に実行する処理ですが 2 番目のコメントに付いた最初の階層のコメントを取得すればいいので、 --このクエリをそのまま貼り付けてあげれば OK でしょう。 --次にその下の階層を取得する処理なので、とりあえずこちらをそのまま書いてあげましょう。 --では、これがどう処理されるかですが、最初の階層を取得するとそれを t にしてこちらの処理をしてくれます。 --したがってこちらは t で表現できるので、このようにしてあげればいいですね。 --またこちらの処理が終わったらその結果を t にして、 --結果が無くなるまでこちらの処理を再起的に実行してくれるので、 --どれだけ階層が深くなっても、コメントを取得してくれるはずです。 --あとはその結果をまとめて表示したいので、このように書いてあげましょう --それから、ここからここまでがひとつの命令になるので、 --セミコロンは最後にひとつとなる点にも注意しておいてください \! echo 'WITH RECURSIVE t AS (SELECT * FROM comments WHERE parent_id = 2 UNION ALL SELECT comments.* FROM comments JOIN t ON comments.parent_id = t.id) SELECT * FROM tの実行結果↓↓'; WITH RECURSIVE t AS ( -- n = 1 最初に実行する処理 SELECT * FROM comments WHERE parent_id = 2 UNION ALL -- n >= 2 2 回目以降に再起的に実行する処理 SELECT comments.* FROM comments JOIN t ON comments.parent_id = t.id ) SELECT * FROM t; ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+---------+ | id | message | +----+---------+ | 1 | post-1 | | 2 | post-2 | | 3 | post-3 | +----+---------+ SELECT * FROM commentsの実行結果↓↓ +----+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +----+---------+-----------------+-----------+ | 1 | 1 | comment-1-1 | NULL | | 2 | 1 | comment-1-2 | NULL | | 3 | 3 | comment-3-1 | NULL | | 4 | 1 | comment-1-2-1 | 2 | | 5 | 1 | comment-1-2-2 | 2 | | 6 | 1 | comment-1-2-1-1 | 4 | | 7 | 1 | new comment | 6 | +----+---------+-----------------+-----------+ SELECT comments.* FROM comments JOIN ( SELECT * FROM comments WHERE parent_id = 2 ) AS t ON comments.parent_id = t.idの実行結果↓↓ +----+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +----+---------+-----------------+-----------+ | 4 | 1 | comment-1-2-1 | 2 | | 5 | 1 | comment-1-2-2 | 2 | | 6 | 1 | comment-1-2-1-1 | 4 | +----+---------+-----------------+-----------+ WITH RECURSIVE t AS (SELECT * FROM comments WHERE parent_id = 2 UNION ALL SELECT comments.* FROM comments JOIN t ON comments.parent_id = t.id) SELECT * FROM tの実行結果↓↓ +------+---------+-----------------+-----------+ | id | post_id | comment | parent_id | +------+---------+-----------------+-----------+ | 4 | 1 | comment-1-2-1 | 2 | | 5 | 1 | comment-1-2-2 | 2 | | 6 | 1 | comment-1-2-1-1 | 4 | | 7 | 1 | new comment | 6 | +------+---------+-----------------+-----------+ ~ $ 応用32、33 TRIGGERを使ってみよう １、SQLファイル作成(logテーブル挿入確認) main.sql --あるテーブルで何らかの変更が起きた時に、それをトリガーにして何らかの処理をすることができる、 --トリガーという仕組みについて見ていきましょう。 DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS posts; --もし logs テーブルが存在していたら削除、もしトリガーが存在していたら削除としたいので、 -- DROP TRIGGER 文を使ってあげます。 DROP TABLE IF EXISTS logs; DROP TRIGGER IF EXISTS posts_update_trigger; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); --posts テーブルが更新されたら、他のテーブルにログが残るようにしてあげましょう --logs テーブルを作っていきたいので、ざっと上をコピーしてあげて、このあたりで作っていきます --テーブル名は logs にしてあげて、 created カラムも追加してあげましょう --こちらは DEFAULT で挿入された日時にしておきます。 CREATE TABLE logs ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), created DATETIME DEFAULT NOW(), PRIMARY KEY (id) ); --次に、トリガーの作り方ですが CREATE TRIGGER という文を使ってあげます --そのあとに、トリガーの名前を好きに付けられるのですが、 --今回は posts がアップデートされたときのトリガーなので、このように付けておきましょう --そのあとに、どのタイミングでどんな処理をするかを指定するのですが --AFTER UPDATE ON としてあげて posts が更新されたあとに、と指定してあげます。 --そのあと、更新されたひとつひとつの行に対して logs テーブルにデータを挿入していきたいので、 --少し長いのですがこのように書いてあげれば OK です。 --なお、トリガーはアップデートだけに使えるというわけではなくて、 -- INSERT や DELETE のタイミングでも使えますし、 --ここも AFTER ではなくて BEFORE とすれば処理前にトリガーを実行することができます。 CREATE TRIGGER posts_update_trigger AFTER UPDATE ON posts FOR EACH ROW INSERT INTO logs (message) VALUES ('Updated'); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); --トリガーができたのでアップデートをしてあげて、 logs テーブルにちゃんとログが残るか確認してみましょう --今回は id が 1 のレコードの message を更新してみましょう --これで logs テーブルにレコードが挿入されるはずなので、 SELECT * FROM logs で確認してみたいと思います --実行してあげると...こうですね、ちゃんと post-1 の中身が更新されていて、 -- logs テーブルにも Updated というログが残っていますね UPDATE posts SET message = 'post-1 updated' WHERE id = 1; \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM logsの実行結果↓↓'; SELECT * FROM logs; ２、ターミナルの実行結果(logテーブル挿入確認) ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+----------------+ | id | message | +----+----------------+ | 1 | post-1 updated | | 2 | post-2 | | 3 | post-3 | +----+----------------+ SELECT * FROM logsの実行結果↓↓ +----+---------+---------------------+ | id | message | created | +----+---------+---------------------+ | 1 | Updated | 2021-04-24 16:12:23 | +----+---------+---------------------+ ~ $ ３、SQLファイル作成（ログに、更新前、更新後の値を含める方法、トリガーの内容確認方法） main.sql DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS posts; DROP TABLE IF EXISTS logs; DROP TRIGGER IF EXISTS posts_update_trigger; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), PRIMARY KEY (id) ); CREATE TABLE logs ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), created DATETIME DEFAULT NOW(), PRIMARY KEY (id) ); --ログに、更新前、更新後の値を含める方法も見ておきましょう。 --トリガーでは OLD と NEW という特殊なキーワードが使えるので CONCAT 関数を使って、 --このように値を組み立ててあげます --message の更新前、更新後の値を挿入してくれます CREATE TRIGGER posts_update_trigger AFTER UPDATE ON posts FOR EACH ROW INSERT INTO logs (message) VALUES -- ('Updated'); (CONCAT(OLD.message, ' -> ', NEW.message)); INSERT INTO posts (message) VALUES ('post-1'), ('post-2'), ('post-3'); UPDATE posts SET message = 'post-1 updated' WHERE id = 1; \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; \! echo 'SELECT * FROM logsの実行結果↓↓'; SELECT * FROM logs; --設定されているトリガーの一覧を見るには、 SHOW TRIGGERS を使ってあげれば OK です。 --では、見てあげると...こうですね、最後にトリガーの情報が表示されているのですが、ちょっと見づらいですね。 \! echo 'SHOW TRIGGERSの実行結果↓↓'; SHOW TRIGGERS; --このように、横に結果が長くなってしまう場合、最後のセミコロンを \G に変えてあげれば OK です。 --トリガーの名前と内容もこちらで確認できるのがわかりますね。 \! echo 'SHOW TRIGGERS\Gの実行結果↓↓'; SHOW TRIGGERS\G ４、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+----------------+ | id | message | +----+----------------+ | 1 | post-1 updated | | 2 | post-2 | | 3 | post-3 | +----+----------------+ SELECT * FROM logsの実行結果↓↓ ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+----------------+ | id | message | +----+----------------+ | 1 | post-1 updated | | 2 | post-2 | | 3 | post-3 | +----+----------------+ SELECT * FROM logsの実行結果↓↓ +----+--------------------------+---------------------+ | id | message | created | +----+--------------------------+---------------------+ | 1 | post-1 -> post-1 updated | 2021-04-24 16:18:39 | +----+--------------------------+---------------------+ +----------------------+--------+-------+---------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +----------------------+--------+-------+---------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+ | posts_update_trigger | UPDATE | posts | INSERT INTO logs (message) VALUES (CONCAT(OLD.message, ' -> ', NEW.message)) | AFTER | 2021-04-24 16:18:39.95 | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | dbuser@% | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci | +----------------------+--------+-------+---------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+------- ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +----+----------------+ | id | message | +----+----------------+ | 1 | post-1 updated | | 2 | post-2 | | 3 | post-3 | +----+----------------+ SELECT * FROM logsの実行結果↓↓ +----+--------------------------+---------------------+ | id | message | created | +----+--------------------------+---------------------+ | 1 | post-1 -> post-1 updated | 2021-04-24 16:19:36 | +----+--------------------------+---------------------+ SHOW TRIGGERSの実行結果↓↓ +----------------------+--------+-------+---------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +----------------------+--------+-------+---------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+ | posts_update_trigger | UPDATE | posts | INSERT INTO logs (message) VALUES (CONCAT(OLD.message, ' -> ', NEW.message)) | AFTER | 2021-04-24 16:19:36.47 | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | dbuser@% | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci | +----------------------+--------+-------+---------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+ SHOW TRIGGERS\Gの実行結果↓↓ *************************** 1. row *************************** Trigger: posts_update_trigger Event: UPDATE Table: posts Statement: INSERT INTO logs (message) VALUES (CONCAT(OLD.message, ' -> ', NEW.message)) Timing: AFTER Created: 2021-04-24 16:19:36.47 sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Definer: dbuser@% character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8mb4_general_ci ~ $ 応用34 外部ファイルからデータ読み込もう 手順1　以下の様にsqlファイルとCSVファイルの２つのファイル用意 $ ls data.csv main.sql $ 手順2　SQLファイル作成(main.sql) main.sql DROP TABLE IF EXISTS posts; --別のファイルからデータを読み込む方法について見ていきます --posts テーブルがあって、 id 、 message 、 likes 、 area が設定されています --また今回は、 data.csv（手順3を用意） というファイルを用意しておきました CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); --では、こちらのデータを読み込んでいきたいのですが、同じサーバにある外部ファイルを読み込むには、 LOAD DATA LOCAL INFILE としてあげて、ファイル名を指定してあげます。 --それをどちらのテーブルに流し込むかを INTO TABLE で指定してあげましょう。 --それから、ファイル形式に応じていくつかのオプションが必要です。 --まず、項目の区切りがカンマだったら、 FIELDS TERMINATED BY を使ってあげてください。 --それから、行の区切りも指定してあげる必要があって、今回 \n を改行として使っているので、このように書いてあげます。 --そのうえで、データをどのフィールドに挿入するかを最後に指定してあげます。 --message 、 likes 、 area の順番だったので、このように書けば OK ですね --見出し行がレコードとして挿入されるのを防ぐため、データの一行目をスキップしたいなら、さらに記述が必要で、こちらに IGNORE 1 LINES としてあげれば OK です --外部ファイルの形式によっては、さらにオプションが必要な場合もありますが、こうした操作もできるようになっておきましょう LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE posts FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (message, likes, area); --では、これでデータが流し込めるはずなので、 SELECT * FROM posts で確認してみましょう。 --id については自動で連番が振られるので、うまくいっているようです \! echo 'SELECT * FROM postsの実行結果↓↓'; SELECT * FROM posts; 手順3　CSVファイルを用意 ・一行目が見出しで、それ以降は message 、 likes 、 area のデータが入っているファイル ・なお、こちらには id の連番が含まれていない点に注意しておきましょう data.csv message,likes,area post-1,136,Tokyo post-2,27,Osaka post-3,125,Osaka post-4,40,Kanazawa post-5,19,Wakayama post-6,101,Wakayama post-7,125,Osaka post-8,67,Kanazawa post-9,137,Tokyo post-10,77,Kumamoto post-11,4,Kanazawa post-12,167,Kagoshima post-13,101,Nara post-14,170,Kumamoto post-15,169,Nagoya post-16,59,Wakayama post-17,97,Kumamoto post-18,179,Nagoya post-19,26,Kumamoto post-20,60,Mito post-21,147,Osaka post-22,14,Hiroshima post-23,192,Fukuoka post-24,124,Kumamoto post-25,45,Wakayama post-26,16,Niigata post-27,1,Wakayama post-28,14,Wakayama post-29,128,Wakayama post-30,46,Fukuoka post-31,32,Mito post-32,17,Niigata post-33,30,Tokyo post-34,25,Saitama post-35,113,Kyoto post-36,193,Tokyo post-37,184,Kyoto post-38,178,Kumamoto post-39,50,Niigata post-40,14,Nagoya post-41,90,Osaka post-42,34,Hiroshima post-43,152,Mito post-44,16,Wakayama post-45,48,Nagoya post-46,64,Tokyo post-47,94,Hiroshima post-48,111,Kanazawa post-49,30,Nagoya post-50,24,Mito post-51,153,Kanazawa post-52,115,Niigata post-53,129,Nara post-54,29,Kumamoto post-55,121,Niigata post-56,153,Kagoshima post-57,31,Kumamoto post-58,120,Kumamoto post-59,198,Mito post-60,104,Kumamoto post-61,172,Saitama post-62,197,Saitama post-63,88,Kanazawa post-64,144,Nagoya post-65,45,Niigata post-66,194,Nagoya post-67,102,Hiroshima post-68,60,Fukuoka post-69,81,Kanazawa post-70,190,Fukuoka post-71,102,Niigata post-72,38,Kagoshima post-73,159,Mito post-74,111,Kumamoto post-75,186,Mito post-76,124,Kanazawa post-77,66,Hiroshima post-78,77,Tokyo post-79,122,Kumamoto post-80,162,Kanazawa post-81,108,Tokyo post-82,156,Hiroshima post-83,133,Kumamoto post-84,31,Fukuoka post-85,37,Tokyo post-86,165,Niigata post-87,161,Saitama post-88,137,Fukuoka post-89,84,Mito post-90,186,Osaka post-91,74,Nara post-92,2,Mito post-93,10,Nagoya post-94,135,Kanazawa post-95,199,Nagoya post-96,46,Wakayama post-97,62,Nagoya post-98,108,Kanazawa post-99,18,Wakayama post-100,6,Nagoya post-101,179,Niigata post-102,83,Nagoya post-103,118,Kanazawa post-104,116,Nara post-105,140,Fukuoka post-106,132,Kumamoto post-107,36,Tokyo post-108,131,Fukuoka post-109,59,Kagoshima post-110,174,Tokyo post-111,199,Osaka post-112,166,Kumamoto post-113,121,Niigata post-114,44,Fukuoka post-115,162,Tokyo post-116,24,Fukuoka post-117,134,Kumamoto post-118,95,Nara post-119,135,Saitama post-120,102,Tokyo post-121,137,Nara post-122,192,Kagoshima post-123,51,Nagoya post-124,29,Kyoto post-125,155,Kagoshima post-126,21,Nagoya post-127,161,Hiroshima post-128,35,Nagoya post-129,138,Fukuoka post-130,49,Kagoshima post-131,185,Kagoshima post-132,65,Osaka post-133,135,Saitama post-134,195,Nara post-135,180,Kyoto post-136,188,Hiroshima post-137,161,Mito post-138,155,Kanazawa post-139,96,Tokyo post-140,79,Osaka post-141,175,Kanazawa post-142,1,Kagoshima post-143,50,Wakayama post-144,60,Hiroshima post-145,100,Saitama post-146,38,Osaka post-147,176,Kumamoto post-148,129,Wakayama post-149,165,Nara post-150,170,Mito post-151,109,Nagoya post-152,166,Kumamoto post-153,62,Kagoshima post-154,42,Nara post-155,104,Kanazawa post-156,112,Nara post-157,165,Saitama post-158,8,Nara post-159,32,Saitama post-160,180,Mito post-161,23,Nagoya post-162,44,Kagoshima post-163,165,Tokyo post-164,175,Niigata post-165,61,Kagoshima post-166,192,Kyoto post-167,78,Tokyo post-168,89,Osaka post-169,149,Kyoto post-170,129,Wakayama post-171,188,Tokyo post-172,170,Fukuoka post-173,112,Kanazawa post-174,147,Tokyo post-175,28,Hiroshima post-176,186,Wakayama post-177,68,Fukuoka post-178,84,Tokyo post-179,105,Kyoto post-180,88,Nara post-181,194,Niigata post-182,126,Kagoshima post-183,78,Kyoto post-184,162,Kyoto post-185,69,Mito post-186,72,Mito post-187,18,Osaka post-188,59,Hiroshima post-189,127,Nagoya post-190,98,Kanazawa post-191,115,Hiroshima post-192,36,Wakayama post-193,199,Nara post-194,46,Hiroshima post-195,105,Nagoya post-196,38,Saitama post-197,163,Niigata post-198,41,Kagoshima post-199,104,Nagoya post-200,40,Osaka 手順4　ターミナルの実行結果を確認する ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SELECT * FROM postsの実行結果↓↓ +-----+----------+-------+-----------+ | id | message | likes | area | +-----+----------+-------+-----------+ | 1 | post-1 | 136 | Tokyo | | 2 | post-2 | 27 | Osaka | | 3 | post-3 | 125 | Osaka | | 4 | post-4 | 40 | Kanazawa | | 5 | post-5 | 19 | Wakayama | | 6 | post-6 | 101 | Wakayama | | 7 | post-7 | 125 | Osaka | | 8 | post-8 | 67 | Kanazawa | | 9 | post-9 | 137 | Tokyo | | 10 | post-10 | 77 | Kumamoto | | 11 | post-11 | 4 | Kanazawa | | 12 | post-12 | 167 | Kagoshima | | 13 | post-13 | 101 | Nara | | 14 | post-14 | 170 | Kumamoto | | 15 | post-15 | 169 | Nagoya | | 16 | post-16 | 59 | Wakayama | | 17 | post-17 | 97 | Kumamoto | | 18 | post-18 | 179 | Nagoya | | 19 | post-19 | 26 | Kumamoto | | 20 | post-20 | 60 | Mito | | 21 | post-21 | 147 | Osaka | | 22 | post-22 | 14 | Hiroshima | | 23 | post-23 | 192 | Fukuoka | | 24 | post-24 | 124 | Kumamoto | | 25 | post-25 | 45 | Wakayama | | 26 | post-26 | 16 | Niigata | | 27 | post-27 | 1 | Wakayama | | 28 | post-28 | 14 | Wakayama | | 29 | post-29 | 128 | Wakayama | | 30 | post-30 | 46 | Fukuoka | | 31 | post-31 | 32 | Mito | | 32 | post-32 | 17 | Niigata | | 33 | post-33 | 30 | Tokyo | | 34 | post-34 | 25 | Saitama | | 35 | post-35 | 113 | Kyoto | | 36 | post-36 | 193 | Tokyo | | 37 | post-37 | 184 | Kyoto | | 38 | post-38 | 178 | Kumamoto | | 39 | post-39 | 50 | Niigata | | 40 | post-40 | 14 | Nagoya | | 41 | post-41 | 90 | Osaka | | 42 | post-42 | 34 | Hiroshima | | 43 | post-43 | 152 | Mito | | 44 | post-44 | 16 | Wakayama | | 45 | post-45 | 48 | Nagoya | | 46 | post-46 | 64 | Tokyo | | 47 | post-47 | 94 | Hiroshima | | 48 | post-48 | 111 | Kanazawa | | 49 | post-49 | 30 | Nagoya | | 50 | post-50 | 24 | Mito | | 51 | post-51 | 153 | Kanazawa | | 52 | post-52 | 115 | Niigata | | 53 | post-53 | 129 | Nara | | 54 | post-54 | 29 | Kumamoto | | 55 | post-55 | 121 | Niigata | | 56 | post-56 | 153 | Kagoshima | | 57 | post-57 | 31 | Kumamoto | | 58 | post-58 | 120 | Kumamoto | | 59 | post-59 | 198 | Mito | | 60 | post-60 | 104 | Kumamoto | | 61 | post-61 | 172 | Saitama | | 62 | post-62 | 197 | Saitama | | 63 | post-63 | 88 | Kanazawa | | 64 | post-64 | 144 | Nagoya | | 65 | post-65 | 45 | Niigata | | 66 | post-66 | 194 | Nagoya | | 67 | post-67 | 102 | Hiroshima | | 68 | post-68 | 60 | Fukuoka | | 69 | post-69 | 81 | Kanazawa | | 70 | post-70 | 190 | Fukuoka | | 71 | post-71 | 102 | Niigata | | 72 | post-72 | 38 | Kagoshima | | 73 | post-73 | 159 | Mito | | 74 | post-74 | 111 | Kumamoto | | 75 | post-75 | 186 | Mito | | 76 | post-76 | 124 | Kanazawa | | 77 | post-77 | 66 | Hiroshima | | 78 | post-78 | 77 | Tokyo | | 79 | post-79 | 122 | Kumamoto | | 80 | post-80 | 162 | Kanazawa | | 81 | post-81 | 108 | Tokyo | | 82 | post-82 | 156 | Hiroshima | | 83 | post-83 | 133 | Kumamoto | | 84 | post-84 | 31 | Fukuoka | | 85 | post-85 | 37 | Tokyo | | 86 | post-86 | 165 | Niigata | | 87 | post-87 | 161 | Saitama | | 88 | post-88 | 137 | Fukuoka | | 89 | post-89 | 84 | Mito | | 90 | post-90 | 186 | Osaka | | 91 | post-91 | 74 | Nara | | 92 | post-92 | 2 | Mito | | 93 | post-93 | 10 | Nagoya | | 94 | post-94 | 135 | Kanazawa | | 95 | post-95 | 199 | Nagoya | | 96 | post-96 | 46 | Wakayama | | 97 | post-97 | 62 | Nagoya | | 98 | post-98 | 108 | Kanazawa | | 99 | post-99 | 18 | Wakayama | | 100 | post-100 | 6 | Nagoya | | 101 | post-101 | 179 | Niigata | | 102 | post-102 | 83 | Nagoya | | 103 | post-103 | 118 | Kanazawa | | 104 | post-104 | 116 | Nara | | 105 | post-105 | 140 | Fukuoka | | 106 | post-106 | 132 | Kumamoto | | 107 | post-107 | 36 | Tokyo | | 108 | post-108 | 131 | Fukuoka | | 109 | post-109 | 59 | Kagoshima | | 110 | post-110 | 174 | Tokyo | | 111 | post-111 | 199 | Osaka | | 112 | post-112 | 166 | Kumamoto | | 113 | post-113 | 121 | Niigata | | 114 | post-114 | 44 | Fukuoka | | 115 | post-115 | 162 | Tokyo | | 116 | post-116 | 24 | Fukuoka | | 117 | post-117 | 134 | Kumamoto | | 118 | post-118 | 95 | Nara | | 119 | post-119 | 135 | Saitama | | 120 | post-120 | 102 | Tokyo | | 121 | post-121 | 137 | Nara | | 122 | post-122 | 192 | Kagoshima | | 123 | post-123 | 51 | Nagoya | | 124 | post-124 | 29 | Kyoto | | 125 | post-125 | 155 | Kagoshima | | 126 | post-126 | 21 | Nagoya | | 127 | post-127 | 161 | Hiroshima | | 128 | post-128 | 35 | Nagoya | | 129 | post-129 | 138 | Fukuoka | | 130 | post-130 | 49 | Kagoshima | | 131 | post-131 | 185 | Kagoshima | | 132 | post-132 | 65 | Osaka | | 133 | post-133 | 135 | Saitama | | 134 | post-134 | 195 | Nara | | 135 | post-135 | 180 | Kyoto | | 136 | post-136 | 188 | Hiroshima | | 137 | post-137 | 161 | Mito | | 138 | post-138 | 155 | Kanazawa | | 139 | post-139 | 96 | Tokyo | | 140 | post-140 | 79 | Osaka | | 141 | post-141 | 175 | Kanazawa | | 142 | post-142 | 1 | Kagoshima | | 143 | post-143 | 50 | Wakayama | | 144 | post-144 | 60 | Hiroshima | | 145 | post-145 | 100 | Saitama | | 146 | post-146 | 38 | Osaka | | 147 | post-147 | 176 | Kumamoto | | 148 | post-148 | 129 | Wakayama | | 149 | post-149 | 165 | Nara | | 150 | post-150 | 170 | Mito | | 151 | post-151 | 109 | Nagoya | | 152 | post-152 | 166 | Kumamoto | | 153 | post-153 | 62 | Kagoshima | | 154 | post-154 | 42 | Nara | | 155 | post-155 | 104 | Kanazawa | | 156 | post-156 | 112 | Nara | | 157 | post-157 | 165 | Saitama | | 158 | post-158 | 8 | Nara | | 159 | post-159 | 32 | Saitama | | 160 | post-160 | 180 | Mito | | 161 | post-161 | 23 | Nagoya | | 162 | post-162 | 44 | Kagoshima | | 163 | post-163 | 165 | Tokyo | | 164 | post-164 | 175 | Niigata | | 165 | post-165 | 61 | Kagoshima | | 166 | post-166 | 192 | Kyoto | | 167 | post-167 | 78 | Tokyo | | 168 | post-168 | 89 | Osaka | | 169 | post-169 | 149 | Kyoto | | 170 | post-170 | 129 | Wakayama | | 171 | post-171 | 188 | Tokyo | | 172 | post-172 | 170 | Fukuoka | | 173 | post-173 | 112 | Kanazawa | | 174 | post-174 | 147 | Tokyo | | 175 | post-175 | 28 | Hiroshima | | 176 | post-176 | 186 | Wakayama | | 177 | post-177 | 68 | Fukuoka | | 178 | post-178 | 84 | Tokyo | | 179 | post-179 | 105 | Kyoto | | 180 | post-180 | 88 | Nara | | 181 | post-181 | 194 | Niigata | | 182 | post-182 | 126 | Kagoshima | | 183 | post-183 | 78 | Kyoto | | 184 | post-184 | 162 | Kyoto | | 185 | post-185 | 69 | Mito | | 186 | post-186 | 72 | Mito | | 187 | post-187 | 18 | Osaka | | 188 | post-188 | 59 | Hiroshima | | 189 | post-189 | 127 | Nagoya | | 190 | post-190 | 98 | Kanazawa | | 191 | post-191 | 115 | Hiroshima | | 192 | post-192 | 36 | Wakayama | | 193 | post-193 | 199 | Nara | | 194 | post-194 | 46 | Hiroshima | | 195 | post-195 | 105 | Nagoya | | 196 | post-196 | 38 | Saitama | | 197 | post-197 | 163 | Niigata | | 198 | post-198 | 41 | Kagoshima | | 199 | post-199 | 104 | Nagoya | | 200 | post-200 | 40 | Osaka | +-----+----------+-------+-----------+ ~ $ その他捕捉 ・カンマ区切りでないファイルでも FIELDS TERMINATED BY が使える ・環境によっては改行が今回使ってるのと違う別の表現になることもある ・LINES TERMINATED BY '\n'を入れなくてもよい場合がある 応用35　インデックスを使ってよう １、インデックスという仕組みについて見ていきたいのですが、図で説明していきましょう。 今たとえばこういった posts テーブルがあったとして、 このテーブルから area が Kyoto のレコードを抽出したかったとします。 その場合、 MySQL では先頭から順番にひとつずつ見ていって、合致するものを探していきます。 ただ、この方法だと全てのレコードを最初から最後まで見ていくことになるので、 レコード数が膨大になってくるとすごく時間がかかるという問題があります。 データベースではよく検索されるカラムに対してインデックスというものを付けることができます。 下記みたいなものですね。 このインデックスですが、元データとは別に保持される索引のようなデータで、 どのデータがどこにあるのか、やや複雑なデータ構造で保持されています。 そしてこのデータ構造ですが、あらかじめ値を整列させたうえで範囲ごとに区切られているため、 上から順番にひとつずつ見ていくよりも、ずっと効率的に検索することができるという仕組みになっています。 ただし、インデックスを作ると検索は早くなるのですが、逆にデータの挿入や更新、 そして削除をすると、こちらのインデックスのデータをいちいち再構築しなくてはなりません。 また、インデックスの分、データベースに必要な容量が増えてしまうというデメリットもあります。 したがって、インデックスはデータベースの運用状況を見ながら、 必要なカラムに対して付け外しができるようになっておくといいでしょう。 それから、主キーに関しては PRIMARY というインデックスが実は自動的に生成されます。 したがって、この例だと id を使った検索はすでに高速に動作するということを知っておくといいでしょう。 応用36 EXPLAINでクエリを分析しよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE posts FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (message, likes, area); --インデックスですが、まずは今設定されているものを確認してみましょう --前回説明したとおり、主キーには自動的にインデックスが作られているはずなので、その情報が出てくるはずです。 --では、 SHOW INDEX FROM テーブル名としてあげます。 --ちゃんと id のフィールドに PRIMARY というインデックスが設定されていますね。 \! echo 'SHOW INDEX FROM posts\Gの実行結果↓↓'; SHOW INDEX FROM posts\G --それから実際に id を使った検索で、このインデックスが使われているかどうか、確認する方法も見ておきます。 --では id を使った検索として、 SELECT * FROM posts WHERE id = 30 を使ってみましょう。 --このようにクエリを書いてあげて、このクエリでどのインデックスが使われているかは、 --先頭に EXPLAIN を付けてあげれば調べることができます。 --では、見てあげると...こうですね、いろいろありますが、 --見るべき項目は key と rows で、 key はクエリで実際に使われたインデックスの名前、 --rows は検索対象となるレコード数の見積もりです。 --今回だとちゃんと PRIMARY キーが使われていますし、 --rows は一行だけなので高速に動作しているがわかりますね。 --今回だとちゃんと PRIMARY キーが使われていますし、 rows は一行だけなので高速に動作しているがわかりますね。 \! echo 'EXPLAIN SELECT * FROM posts WHERE id = 30\Gの実行結果↓↓'; EXPLAIN SELECT * FROM posts WHERE id = 30\G ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SHOW INDEX FROM posts\Gの実行結果↓↓ *************************** 1. row *************************** Table: posts Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql SHOW INDEX FROM posts\Gの実行結果↓↓ *************************** 1. row *************************** Table: posts Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 199 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: EXPLAIN SELECT * FROM posts WHERE id = 30\Gの実行結果↓↓ *************************** 1. row *************************** id: 1 select_type: SIMPLE table: posts type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: ~ $ 応用37 インデックスを設定してみよう １、SQLファイル作成 main.sql DROP TABLE IF EXISTS posts; CREATE TABLE posts ( id INT NOT NULL AUTO_INCREMENT, message VARCHAR(140), likes INT, area VARCHAR(20), PRIMARY KEY (id) ); LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE posts FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (message, likes, area); --次はインデックスが設定されていないクエリでも確認してみましょう。 --こちらコメントにして EXPLAIN で調べていきます。 --では今回、インデックスが設定されていないメディアを使ったクエリについて調べてみましょう。 --実行して、key を見ると NULL になっていて、インデックスが使われていないことがわかります。 --また、 rows は 200 件になっているので、最初から最後まで見てから検索結果を返しているので、 --低速なクエリであることもわかりますね。 \! echo 'EXPLAIN SELECT * FROM posts WHERE area = 'Kyoto'\Gの実行結果↓↓'; EXPLAIN SELECT * FROM posts WHERE area = 'Kyoto'\G; --では今回、エリアで頻繁に検索するようになったとしてインデックスを設定してあげましょう。 --CREATE TABLE で設定することもできるのですが、あとから付け外しすることが多いので --ALTER TABLE を使っていきましょう。 --インデックスを追加するには ADD INDEX としてあげて、インデックスの名前を付けてあげます。 --そのうえで、どのカラムに対してのインデックスかを指定してあげれば OK です。 \! echo 'ALTER TABLE posts ADD INDEX index_area(area)の実行結果↓↓'; ALTER TABLE posts ADD INDEX index_area(area); --SHOW INDEX で確認してみましょう。 --こうですね、インデックスの一覧では PRIMARY と area カラムにインデックスが設定されていて、 --EXPLAIN の結果を見ると今度は key でちゃんと index_area が使われているのがわかりますし、 --rows も大幅に減っているのでこのクエリが高速に動作するようになったのがわかります。 --エリアでの検索が遅いなと感じるようになったら、このようにインデックスを設定してあげるといいでしょう。 \! echo 'SHOW INDEX FROM posts\Gの実行結果↓↓'; SHOW INDEX FROM posts\G \! echo 'EXPLAIN SELECT * FROM posts WHERE area = 'Kyoto'\Gの実行結果↓↓'; EXPLAIN SELECT * FROM posts WHERE area = 'Kyoto'\G --それから、インデックスを外す方法についても見ておきましょう。 --その場合ですが、 ALTER TABLE の DROP INDEX を使ってあげます。 --インデックス名を指定してあげればいいですね。 --そうすると、こちらでインデックスを追加しているのですが、 --そのあとにインデックスを外しているので SHOW INDEX では PRIMARY KEY だけが表示されるはずです。 --インデックスについてはもっと奥が深いのですが、データが増えてきてパフォーマンスが落ちてきたら --EXPLAIN を使って調査しつつ、適切にインデックスを設定してあげるといいでしょう。 \! echo 'ALTER TABLE posts DROP INDEX index_areaの実行結果↓↓'; ALTER TABLE posts DROP INDEX index_area; \! echo 'SHOW INDEX FROM posts\Gの実行結果↓↓'; SHOW INDEX FROM posts\G ２、ターミナルの実行結果 ~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql EXPLAIN SELECT * FROM posts WHERE area = Kyoto\Gの実行結果↓↓ *************************** 1. row *************************** id: 1 select_type: SIMPLE table: posts type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 199 Extra: Using where ALTER TABLE posts ADD INDEX index_area(area)の実行結果↓↓ SHOW INDEX FROM posts\Gの実行結果↓↓ *************************** 1. row *************************** Table: posts Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 28 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: posts Non_unique: 1 Key_name: index_area Seq_in_index: 1 Column_name: area Collation: A Cardinality: 28 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: EXPLAIN SELECT * FROM posts WHERE area = Kyoto\Gの実行結果↓↓ *************************** 1. row *************************** id: 1 select_type: SIMPLE table: posts type: ref possible_keys: index_area key: index_area key_len: 83 ref: const rows: 9 Extra: Using index condition ALTER TABLE posts DROP INDEX index_areaの実行結果↓↓ SHOW INDEX FROM posts\Gの実行結果↓↓ *************************** 1. row *************************** Table: posts Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 28 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: ~ $ 勉強終了