基本テーブル構造とサンプルデータ

まず、学習用のデータベースとテーブルを準備します。

-- データベース作成
CREATE DATABASE sample_db CHARSET=utf8mb4;
USE sample_db;

-- 生徒テーブル
CREATE TABLE learners (
    uid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(50) DEFAULT '',
    years TINYINT UNSIGNED DEFAULT 0,
    stature DECIMAL(5,2),
    sex ENUM('male','female','neutral','private') DEFAULT 'private',
    group_id INT UNSIGNED DEFAULT 0,
    archived BIT DEFAULT 0
);

-- クラステーブル
CREATE TABLE groups (
    gid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(60) NOT NULL
);

次に、テストデータを挿入します。

-- learners テーブルへのデータ投入
INSERT INTO learners VALUES
(0,'Yamada',19,178.50,'male',1,0),
(0,'Tanaka',20,165.30,'female',2,1),
(0,'Suzuki',31,183.00,'male',1,0),
(0,'Watanabe',61,172.00,'male',2,1),
(0,'Sato',39,158.00,'female',1,0),
(0,'Kobayashi',29,149.00,'private',2,1),
(0,'Takahashi',19,170.00,'female',1,1),
(0,'Ito',37,NULL,'male',1,0),
(0,'Yamamoto',28,180.00,'male',2,0),
(0,'Nakamura',26,164.00,'female',2,0),
(0,'Kato',34,161.00,'neutral',3,1),
(0,'Yoshida',13,179.00,'female',4,0),
(0,'Sasaki',13,169.00,'male',4,0),
(0,'Yamaguchi',35,175.00,'female',5,0);

-- groups テーブルへのデータ投入
INSERT INTO groups VALUES (0, "DataScience_01"), (0, "WebDev_02");

基本SELECT操作

全カラム取得:

SELECT * FROM learners;

特定カラム選択:

SELECT full_name, years FROM learners;

別名（Alias）の使用:

SELECT uid AS student_id, full_name AS name, sex AS gender FROM learners;

重複排除:

SELECT DISTINCT sex FROM learners;

条件フィルタリング（WHERE句）

比較演算子:

SELECT * FROM learners WHERE years > 25;

論理演算子:

SELECT * FROM learners WHERE years >= 20 AND sex = 'female';

パターンマッチ（LIKE）:

SELECT * FROM learners WHERE full_name LIKE 'Yama%';

範囲指定:

SELECT * FROM learners WHERE uid BETWEEN 5 AND 10;

NULLチェック:

SELECT * FROM learners WHERE stature IS NULL;

結果のソート（ORDER BY）

単一カラム降順:

SELECT * FROM learners ORDER BY years DESC;

複数カラムソート:

SELECT * FROM learners ORDER BY years DESC, stature DESC;

集計関数

件数カウント:

SELECT COUNT(*) FROM learners;

最大値・最小値:

SELECT MAX(years), MIN(years) FROM learners;

合計・平均:

SELECT SUM(years), AVG(stature) FROM learners WHERE sex = 'male';

グループ化（GROUP BY）

性別ごとの人数:

SELECT sex, COUNT(*) AS total FROM learners GROUP BY sex;

グループ内連結:

SELECT sex, GROUP_CONCAT(full_name) FROM learners GROUP BY sex;

HAVINGによる絞り込み:

SELECT sex, COUNT(*) FROM learners GROUP BY sex HAVING COUNT(*) > 2;

ページネーション（LIMIT）

先頭3件取得:

SELECT * FROM learners LIMIT 0, 3;

ページング計算式:

SELECT * FROM learners LIMIT (page_num - 1) * page_size, page_size;

結合クエリ（JOIN）

内部結合:

SELECT l.full_name, g.title 
FROM learners l INNER JOIN groups g ON l.group_id = g.gid;

左外部結合:

SELECT l.full_name, g.title 
FROM learners l LEFT JOIN groups g ON l.group_id = g.gid;

自己参照テーブル

地域階層構造の例:

CREATE TABLE regions (
    rid INT PRIMARY KEY,
    rname VARCHAR(50),
    parent_id INT
);

-- 都道府県レベルの取得
SELECT COUNT(*) FROM regions WHERE parent_id IS NULL;

-- 特定都道府県の市区町村取得
SELECT child.* FROM regions child
JOIN regions parent ON child.parent_id = parent.rid
WHERE parent.rname = 'Tokyo';

サブクエリ

スカラー型サブクエリ:

SELECT * FROM learners 
WHERE years > (SELECT AVG(years) FROM learners);

列型サブクエリ:

SELECT title FROM groups 
WHERE gid IN (SELECT DISTINCT group_id FROM learners);

行型サブクエリ:

SELECT * FROM learners 
WHERE (stature, years) = (
    SELECT MAX(stature), MAX(years) FROM learners
);