SQLの勉強メモ

概要

• 勉強に使用した環境はMySQL
• 語句や句・演算子・集約関数などの基本的なものをまとめた
• DB作成・テーブル作成・データ挿入などの基本的操作をまとめた
• 今後も更新予定

勉強環境

• OS: macOS Ventura 13.2.1
• チップ: Apple M1
• DB: MySQL

MySQLのバージョン確認

mysql -V
mysql  Ver 14.14 Distrib 5.7.41, for osx10.18 (x86_64) using  EditLine wrapper

MySQLをHomebrewでインストールして起動する流れ

# brewでインストール
brew install mysql

# 起動
brew services start mysql@5.7

# ログイン
mysql -u root

# ログアウト
[Ctl + D]

# 停止
brew services stop mysql@5.7

SQLの勉強ができるサイト

• SQLZOO
  • https://sqlzoo.net/wiki/SQL_Tutorial
  • 無料で問題に取り組める
  • SQLを記載する問題やクイズ形式のものがある
  • 英語
    • 日本語も対応しているが、翻訳の精度が不十分
    • 日本語の通りに問題を解くとたまに間違いになるので、英語のままでDeepLなどで翻訳した方が良いかも

語句

• 句
• 演算子
• 集約関数
• 内部結合
• 外部結合
• 左外部結合
• 右外部結合
• 完全外部結合
• 集約キー

句・演算子・集約関数

句

/* カラムを選択 */
SELECT

/* テーブルを選択 */
FROM

/* 条件指定, 条件として集約関数は使えない */
WHERE

/* カラムxについて昇順でsort */
ORDER BY x

/* カラムxについて降順でsort */
ORDER BY x DESC

/* レコードを1件目から10件取得する */
LIMIT 10

/* xでグループ化, xは集約キーと呼ばれる */
GROUP BY x

/* カラムに名前をつける */
AS 金額

/* 条件指定, グループ化した結果を絞り込む, amountの合計が90以上なら */
HAVING SUM(amount) >= 90

/* 重複したレコードを省略して表示 */
SELECT DISTINCT x FROM y;

演算子

/* x <= ? <= y の範囲 */
BETWEEN x AND y
/* AND */
AND
/* OR */
OR
/* 否定 */
NOT
/* xカラムは集合(1, 2, 3)に含まれているか */
x IN (1, 2, 3)
/* 文字列が含まれているか */
LIKE
/* 前方一致, 先頭文字がABC */
WHERE x LIKE 'ABC%'
/* 後方一致, 末尾文字がABC */
WHERE x LIKE '%ABC'
/* 部分一致, ABCが含まれている */
WHERE x LIKE '%ABC%'

集約関数

/* 条件 */
LENGTH()

/* 数を数える, レコードの数など */
COUNT()

/* 合計 */
SUM()

/* 平均 */
AVG()

/* 最小 */
MIN()

/* 最大 */
MAX()

/* 日付 */
DATE()

/* 日付, 年度と月で集計 */
DATE_FORMAT(x, '%Y-%m')

/* 年度 */
YEAR()

未分類

/* グループ分け */
CASE
	WHEN level >= 4 THEN 'High'
	WHEN level >= 2 THEN 'Middle'
  ELSE 'Low'
END AS 'Class'

/* カラム名に別の文字などを連結する */
CONCAT

/* 現在の日時を取得 */
CURRENT_DATE()

/* 2つの日時の間の期間を整数で取得 */
TIMESTAMPDIFF(YEAR, xx, CURRENT_DATE())

型・制約

型

INTEGER：整数
DATE：日付
DATETIME：日時型
DECIMAL(3,1)：小数, 総桁数3, 小数点以下1桁, これにより値の範囲が限定される
VARCHAR(20)：文字列, 最大文字数20

制約

PRIMARY KEY：重複を許容しない, NULLを許容しない
DEFAULT xxx：DEFAULT値をxxxと設定
UNIQUE：重複を許容しない
NOT NULL：NULLを許容しない

基本的な操作

既存のSQLスクリプトからDBを作成

SOURCE /aaa/aa/aa.sql

DB, table作成

/* DB作成 */
CREATE DATABASE aaa;

/* DB一覧 */
show databases;

/* DBを指定しなくても良くなるUSEコマンド (MySQL限定) */
/* aaaデータベースを指定 */
USE aaa;

/* テーブル作成 */
CREATE TABLE a(
	id INTEGER PRIMARY KEY,
	published_on DATE UNIQUE NOT NULL,
	title VARCHHAR(31) NOT NULL,
	temperature DECIMAL(3,1),
	content TEXT
);

/* テーブル一覧 */
show tables;

/* aテーブルのレコード一覧 */
SELECT * FROM a;

データの追加・更新・削除

/* aテーブルのレコード一覧から、contentカラムがNULLであるレコードを取得 */
SELECT * FROM a WHERE content IS NULL;

/* aテーブルのレコード一覧から、contentカラムがNULLでないレコードを取得 */
SELECT * FROM a WHERE content IS NOT NULL;

/* レコードを追加 */
/* staffテーブルにレコードを2つ追加 */
INSERT INTO staff(staff_id, name) VALUES (4, 'Tanaka'), (5, 'Shiomura');

/* フィールドの値を更新 */
/* staffテーブルでstaff_idが3であるレコードのusernameカラムのフィールドの値をMacに、first_nameカラムのフィールドの値をWindに変更する */
UPDATE staff SET username = 'Mac', first_name = 'Wind' WHERE staff_id = 3;

/* フィールドの値を増減 */
UPDATE staff SET price = price - 1 WHERE staff_id = 3;

/* staffテーブルの全てのレコードのpriceカラムを変更する */
UPDATE staff SET price = price - 1;

/* 条件に一致するレコード削除 */
DELETE FROM staff WHERE staff_id = 5;

/* 全てのレコード削除, 1件1件削除していく処理 */
DELETE FROM staff;
/* or */
/* DELETEよりも高速に動作, 丸ごと削除して再び作成する処理 */
TRUNCATE TABLE staff;

サブクエリ

• SQLの実行結果を別のSQLで利用する

• FROMの後ろで使用することが多い

• 基本形

SELECT *
FROM (サブクエリ) AS (サブクエリ名);

結合

内部結合(INNER JOIN)

• NULLのあるレコードはスキップされる
• 結合対象の２つのテーブルについて、一致しないカラムを指定したときは、おそらくスキップされる

/* paymentテーブルとcustomerテーブルをc_idで内部結合する例 */
SELECT * FROM payment
INNER JOIN customer ON payment.c_id = customer.c_id;

外部結合(OUTER JOIN)

• 左：FROM句で指定されたテーブル
• 右：OUTER JOINの後に指定したテーブル

左外部結合(LEFT OUTER JOIN)

• よく使用される
• 左の条件に一致しない右のレコードはNULLになる

/* paymentテーブルとcustomerテーブルをc_idで左外部結合する例 */
SELECT * FROM payment
LEFT OUTER JOIN customer ON payment.c_id = customer.c_id;

右外部結合(RIGHT OUTER JOIN)

• 右の条件に一致しない左のレコードはNULLになる

/* paymentテーブルとcustomerテーブルをc_idで右外部結合する例 */
SELECT * FROM payment
RIGHT OUTER JOIN customer ON payment.c_id = customer.c_id;

完全外部結合

• 執筆中

クロス集計

• 執筆中

さいごに

間違いがあればご指摘頂けると助かります。(m_ _m)

参考

• SQLZOO