SQL Serverの基礎と便利な書き方集

プログラミング
スポンサーリンク

はじめに

こんにちは！なたでです！

SQL Serverを最近勉強しています。基本的な書き方やちょっとしたつまずきポイント、実務で役立つTIPSをまとめてみました。

初心者が気になる書き方から、自分の備忘録も兼ねて解説しています。これからSQL Serverに取り組む方や、同じ部分で悩んでいる方の参考になればうれしいです。

TIPS

部分一致でレコードを抽出したい

文字列の先頭や一部が一致するデータを探したい時はLIKE句を使います。

SELECT *
FROM students
WHERE student_name LIKE '山田%'; -- student_nameが「山田」で始まる

レコードを削除したい

DELETEで削除できます。SELECT * のような * は不要です。

DELETE FROM students WHERE grade < 3;

句の順番を知りたい

句（FROM, WHERE, ORDER BYなど）は順番があります。基本形は以下のような形です。

SELECT カラム名
FROM テーブル名
[JOIN テーブル名 ON 結合条件]
[WHERE 条件]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ソート条件]

UPDATEとINSERTの違い

レコードを更新するUPDATEと挿入するINSERTの書き方は次の違いがあります。

-- 新規追加
INSERT INTO students (student_name, email) VALUES ('鈴木太郎', 'taro.suzuki@example.com');

\-- 既存データの更新
UPDATE students SET email = 'newmail@example.com' WHERE student\_name = '鈴木太郎';

INSERTで列名を省略する

すべての列に順番通り値を入れるなら、列名を省略しても動作します。テーブルの列の順番や数が1つでも変わるとエラーや誤登録につながるので、実務では省略せずに列名を書くのが安全です。

-- カラム名を省略（すべての列を順番通りに埋める場合のみ）
INSERT INTO subjects
VALUES ('S001', '数学', 3, '2024-04-01');

INSERTでカラムのレコードを省略する

一部の列だけ値を指定した場合、指定しなかった列（たとえばcreditsやcreated_atなど）はNULLやデフォルト値になります。

INSERT INTO subjects (subject_id, subject_name)
VALUES ('S002', '英語');

INSERTで複数レコードを追加する

通常のINSERT文は1行だけですが、SQL Server（や多くのDBMS）では複数のレコードを一度に追加する書き方もできます。

INSERT INTO classes (class_id, class_name, teacher_id)
VALUES
  ('C001', '1年A組', 'T001'),
  ('C002', '1年B組', 'T002'),
  ('C003', '2年A組', 'T003');

UPDATEで複数のカラムを同時に変える

, で区切ることで複数のカラムを同時に変更できます。

UPDATE
  subjects
SET
  credits = 4,
  created_at = '2024-04-01'
WHERE
  subject_id = 'S001';

予約語のカラム名を使いたい

カラム名が予約語（例：KEYなど）の場合は、角括弧[]で囲む必要があります。

SELECT
  [LEVEL],
  subject_name
FROM
  subjects
WHERE
  [LEVEL] = 3;

最初／最後の100件だけ取得する

TOP句とORDER BYを組み合わせて取得できます。

-- 上から100件
SELECT TOP 100 * FROM students ORDER BY enrolled_at ASC;

-- 下から100件（最新100件）
SELECT TOP 100 * FROM students ORDER BY enrolled_at DESC;

ソート順を複数指定する

「まずAでソート、Aが同じ時はBでソート…」という複数条件の並び替えは、ORDER BY カラム1, カラム2 ... のようにカンマで並べればOKです。

たとえば「クラスごと、その中で点数が高い順」であれば、次のような順番になります。

  1. まずは、class_name（クラス名）で昇順（A→Z順 ASC）
  2. クラス内ではscore（点数）が高い順（降順 DESC）
SELECT
  *
FROM
  students
ORDER BY
  class_name ASC,
  score DESC;

NULLの扱いたい

NULLとの比較は必ずIS NULLやIS NOT NULLを使います。=は使えないので注意。

-- NG: WHERE graduated_at = NULL
-- OK:
WHERE graduated_at IS NULL

指定した条件で集計したい

GROUP BYというグループ化句と、SUMやCOUNT(*)と呼ばれる集約関数を組み合わせます。

例えば、クラスごとの生徒数を出したい時は以下のように記載します。

SELECT
  class_name,
  COUNT(*) AS student_count
FROM
  students
GROUP BY
  class_name;

集約関数の例

  • SUM(カラム) ：合計値
  • COUNT(*) ：件数
  • MAX(カラム) ：最大値
  • MIN(カラム) ：最小値
  • AVG(カラム) ：平均値

集約関数を指定しない場合

以下のような場合は「class_name（クラス名）の重複を消した一覧」しか返ってきません。

SELECT class_name
FROM students
GROUP BY class_name;

これはDISTINCTと同じ動作となり、動作はするものの意味はない文となります。

レコードが存在するかチェックしたい

条件に一致するデータがあるかだけ知りたい時はSELECT 1が使えます。

SELECT 1 FROM subjects WHERE subject_name = '物理';

WHERE文で条件を追加したい

WHERE文では、ANDやORで条件追加が可能です。&といったものは利用できませんし、ORDER BYのソート順で利用したような, も利用できません。

-- OK
WHERE is_active = 1 AND grade >= 3

-- NG
WHERE is\_active = 1, grade >= 3 -- ←エラー

動的に変更したい場合、SQL文ではありませんが、コード上で作成すると楽です。C#では条件を配列にして組み立てると、AND/ORの抜けや重複を防げます

var whereList = new List<string>();
if (minScore != null) whereList.Add("score >= @MinScore");
if (maxScore != null) whereList.Add("score <= @MaxScore");
string whereClause = string.Join(" AND ", whereList);

内部結合と外部結合を知りたい

内部結合と外部結合のイメージ

厳密ではありませんが、AND/OR的な感覚な動作をします。

  • 内部結合（INNER JOIN） = ANDイメージ
    • → 両方にないと出力されない
  • 外部結合（LEFT JOIN） = ORイメージ（左側優先）
    • → 片方（主に左）にだけあれば、右がNULLでも出る

内部結合（INNER JOIN）

両方のテーブルに合致するデータだけが抽出、片方にしかなければ結果に出ません。

SELECT s.student_id, s.student_name, c.class_name
FROM students AS s
INNER JOIN classes AS c ON s.class_id = c.class_id;
  • studentsにだけいる生徒 → 出力されない
  • classesにだけあるクラス → 出力されない
  • 両方に合致 → 出力される

外部結合（LEFT JOIN）

左側（FROMで書いた方）のデータは必ず全部出て、右側（JOIN側）は無ければNULLで表示されます。

SELECT s.student_id, s.student_name, c.class_name
FROM students AS s
LEFT JOIN classes AS c ON s.class_id = c.class_id;
  • studentsにだけいる生徒 → 出力される（classes側はNULL）
  • classesにだけあるクラス → 出力されない（studentsが基準）
  • 両方に合致 → 出力される

サブクエリを使用したい

SQL Serverには、OUTER APPLY と CROSS APPLY という便利なJOINの仲間があります。これらは次のような『行ごとに違うサブクエリ（副問い合わせ）」の結果を結合したいとき』に使います。

  • 「各生徒ごとに、最新のテスト結果だけほしい」
  • 「注文ごとに、一番高い商品だけ取得したい」

「親テーブルごとに、明細から1件だけ」といった動的な絞り込みは、普通のINNER JOINやLEFT JOINでは難しいです。JOINは基本「結合条件でそのまま結合」するだけなので、「1件だけ」や「上位N件だけ」をテーブルごとに取得が苦手なのです。

そこでAPPLY句を使います。

  • CROSS APPLY
    • 親テーブルの各行に対して、サブクエリの結果があれば結合。なければ親の行ごと出ない
      • =INNER JOIN的
  • OUTER APPLY
    • 親テーブルの各行に対して、サブクエリの結果があれば結合。なければサブクエリ側がNULLになる
      • =LEFT JOIN的

利用例

各生徒の「最新の成績だけ」取得したい場合は次のようになります。

SELECT
  s.student_id,
  s.student_name,
  r.score,
  r.exam_date
FROM
  students AS s
  OUTER APPLY (
    SELECT
      TOP 1 *
    FROM
      results AS r
    WHERE
      r.student_id = s.student_id
    ORDER BY
      r.exam_date DESC
  ) AS r;

以下のような動作を行っています。

  • 各生徒ごとに「results」テーブルからその生徒の最新1件だけを抽出
  • 成績が1件もない生徒も一覧に出したいのでOUTER APPLYを利用
    • CROSS APPLYに変えた場合は、成績がない生徒は一覧に表示されなくなります（INNER JOINと同じイメージ）

まとめ

  • 通常のJOINは「テーブル同士の結合」、テーブルごとに何件か選ぶといった柔軟な抽出は不
  • APPLY句は「サブクエリで動的に1件だけ抽出」などが可能
    • OUTER APPLY … サブクエリ結果がなくても親テーブル側は出したいとき
    • CROSS APPLY … サブクエリ結果がない親行は表示したくないとき

主キーを変更したい

主キーは、設定されている組み合わせを解除する必要があります。この組み合わせは主キー制約名で設定されており、いったん解除します。事前に主キー制約名を調べておく必要があります。DBへアクセスソフトで確認が可能な場合が多いです。

ALTER TABLE students
DROP CONSTRAINT PK_students_student_id;

そして再度主キーを設定します。

ALTER TABLE students
ADD CONSTRAINT PK_students_new PRIMARY KEY (student_id, enrolled_on);

ALTERとは

主キーを変えたり、カラムの型を変えたりする際に、ALTERが登場します。これは、制約や構造を変更する際に使用するコマンドです。主キーの変更、カラムの追加、削除、型の変更などに利用できます。

カラムの型を変えたい

以下のようにすれば型を変えられることが出来ます。

-- 学年（grade）をINT型からVARCHAR(10)型に変更したい場合
ALTER TABLE students
ALTER COLUMN grade VARCHAR(10);

すでに既存データがある場合は変換されますが、変換できない場合エラーが発生します。

変換が出来ないものは特定して修正するか削除する必要があります。例えば、以下は数値へキャストできないデータを特定することができます。

SELECT student_id, grade
FROM students
WHERE TRY_CAST(grade AS INT) IS NULL AND grade IS NOT NULL;

PostgreSQLなら

今回は、SQL Serverなので使えないのですが、PostgreSQLであればUSINGを使用することで、列を変換する際に型変換も同時に実行できます。せっかくなので紹介します。

以下は文字から数値へ変換させる場合です。

ALTER TABLE students
ALTER COLUMN grade TYPE INTEGER
USING CAST(grade AS INTEGER);

他にも数値でなければNULLで変換という形も書けます。

ALTER TABLE students
ALTER COLUMN grade TYPE INTEGER
USING 
  CASE 
    WHEN grade ~ '^\d+$' THEN grade::integer
    ELSE NULL
  END;

おわりに

今回は、SQL Serverの基本的な操作やよく使うTIPSを中心にまとめました。

実際に手を動かしてみると、思ったより細かい落とし穴や書き方の工夫が多いことに気づきます。これからSQLを書く方や、ちょっと忘れてしまった方の助けになれば幸いです。

最後まで読んでいただき、ありがとうございました。

スポンサーリンク

コメント

タイトルとURLをコピーしました