LoginSignup
2
4

More than 5 years have passed since last update.

@Brutusinインフラ勉強会

SQLの基本

Last updated at Posted at 2018-10-21

はじめに

本記事は、SQLのデータ操作に関する基本的なことについてまとめました。

SQLの書き方

SQLを書くときのポイントは以下になります。

  • 大文字と小文字
    予約語は大文字、列名やテーブル名は小文字で書くことで可読性が上がります。

  • SQLへの準拠
    一部のSQLは、データベースにより、サポートしていないSQLがあります。

環境

本記事の環境はSQLite3になります。

データベース：SQLite3

以下のテーブルを題材にしています。

  • Adressテーブル
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Aaron       075-XXXXXXXX  London      Man         30        
Edna        075-XXXXXXXX  London      Woman       21        
Dud         075-XXXXXXXX  London      Man         45        
Ham         075-XXXXXXXX  Manchester  Man         32        
Jessica                   Manchester  Woman       55        
Lucy        075-XXXXXXXX  Oxford      Woman       19        
Megan                     Oxford      Woman       20        
Sally       075-XXXXXXXX  Cambridge   Woman       25        
Ken         075-XXXXXXXX  Liverpool   Man         32
  • Adress2テーブル
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Ken         075-XXXXXXXX  Liverpool   Man         32        
Lucy        075-XXXXXXXX  Oxford      Woman       19        
Duke                      Oxford      Man         20        
Steve                     Liverpool   Man         70
  • hobbiesテーブル
name        hobby     
----------  ----------
Aaron       Soccer    
Edna        Soccer    
Ken         Baseball  
Dud         Baseball  
Jessica     basketball
Aaron       Baseball  
Edna        Baseball  
Zat         football

データ操作

SQLの文法の種別は、以下の3つに大別されます。
本記事では、データ操作言語（DML）について記載しています。

  • データ定義言語 (DDL: Data Definition Language)
  • データ操作言語 (DML: Data Manipulation Language)
  • データ制御言語 (DCL: Data Control Language)

SELECT文

データベースのテーブルから、検索してデータを取り出す場合は、SELECT文を使用します。
SELECT句に列、FROM句にテーブル名を指定します。テーブルの列は、カンマで区切って複数指定ができます。

  • 全てのレコードを選択
sqlite> SELECT * FROM Address;
  • 実行結果
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Aaron       075-XXXXXXXX  London      Man         30        
Edna        075-XXXXXXXX  London      Woman       21        
Dud         075-XXXXXXXX  London      Man         45        
Ham         075-XXXXXXXX  Manchester  Man         32        
Jessica                   Manchester  Woman       55        
Lucy        075-XXXXXXXX  Oxford      Woman       19        
Megan                     Oxford      Woman       20        
Sally       075-XXXXXXXX  Cambridge   Woman       25        
Ken         075-XXXXXXXX  Liverpool   Man         32

WHERE文

SELECT文で特定のレコードを選択する場合は、WEARE句を使用します。

  • 住所がオックスフォードを選択
sqlite> SELECT name, address FROM Address WHERE address = 'Oxford';
  • 実行結果
name        address   
----------  ----------
Lucy        Oxford    
Megan       Oxford
  • WHERE句で使用できる代表的な演算子
演算子 意味
= 〜と等しい
<> 〜と等しくない
>= 〜以上
> 〜より大きい
<= 〜以下
< 〜より小さい

WHERE句の条件指定

  • 年齢が30歳以上を選択
sqlite> SELECT name, address FROM Address WHERE age >= 30;
  • 実行結果
name        address   
----------  ----------
Aaron       London    
Dud         London    
Ham         Manchester
Jessica     Manchester
Ken         Liverpool
  • 住所がロンドン以外を選択
sqlite> SELECT name, address FROM Address WHERE address <> 'London';
  • 実行結果
name        address   
----------  ----------
Ham         Manchester
Jessica     Manchester
Lucy        Oxford    
Megan       Oxford    
Sally       Cambridge 
Ken         Liverpool
  • 住所がロンドンかつ、年齢が30歳以上を選択
sqlite> SELECT name, address, age FROM Address WHERE address = 'London' AND age >= 30;
  • 実行結果
name        address     age       
----------  ----------  ----------
Aaron       London      30        
Dud         London      45
  • 住所がロンドンまたは、年齢が30歳以上を選択
sqlite> SELECT name, address, age FROM Address WHERE address = 'London' or age >= 30;
  • 実行結果
name        address     age       
----------  ----------  ----------
Aaron       London      30        
Edna        London      21        
Dud         London      45        
Ham         Manchester  32        
Jessica     Manchester  55        
Ken         Liverpool   32

IN

INを使用することで、ORを使用することなく、複数の条件指定を簡略化できます。

  • 住所がロンドン、マンチェスター、オックスフォードのいずれかを選択
sqlite> SELECT name, address FROM Address WHERE address IN ('London', 'Manchester', 'Oxford');
  • 実行結果
name        address   
----------  ----------
Aaron       London    
Edna        London    
Dud         London    
Ham         Manchester
Jessica     Manchester
Lucy        Oxford    
Megan       Oxford
  • INを使わない場合
sqlite> SELECT name, address FROM Address WHERE address = 'London' OR address = 'Manchester' OR address = 'Oxford';

NULL

NULLLのレコードを選択する場合は、IS NULLを使用します。また、NULLでないレコードを選択する場合は、IS NOT NULLを使用します。

  • NULLのレコードを選択
sqlite> SELECT name, phone_nbr FROM Address WHERE phone_nbr IS NULL;
  • 実行結果
name        phone_nbr 
----------  ----------
Jessica               
Megan
  • NULL以外のレコードを選択
sqlite> SELECT name, phone_nbr FROM Address WHERE phone_nbr IS NOT NULL;
  • 実行結果
name        phone_nbr   
----------  ------------
Aaron       075-XXXXXXXX
Edna        075-XXXXXXXX
Dud         075-XXXXXXXX
Ham         075-XXXXXXXX
Lucy        075-XXXXXXXX
Sally       075-XXXXXXXX
Ken         075-XXXXXXXX

正規表現

正規表現は、LIKE句を使用します。

  • 名前の最後が"n"で終わる人を選択
sqlite> SELECT * FROM Address WHERE name LIKE '%n';
  • 実行結果
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Aaron       075-XXXXXXXX  London      Man         30        
Megan                     Oxford      Woman       20        
Ken         075-XXXXXXXX  Liverpool   Man         32
  • 名前が5文字で最後が"n"で終わる人を選択
sqlite> SELECT * FROM Address WHERE name LIKE '____n';
  • 実行結果
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Aaron       075-XXXXXXXX  London      Man         30        
Megan                     Oxford      Woman       20
  • LIKE句で使用できる代表的な演算子
演算子 意味
% 任意の0文字以上の文字列
_ 任意の1文字

BETWEEN

指定した範囲のレコードを検索する場合はBETWEEN句を使用します。

  • 年齢が20歳から29歳までの人を選択
sqlite> SELECT name, age FROM Address WHERE age BETWEEN 20 AND 29;
  • 実行結果
name        age       
----------  ----------
Edna        21        
Megan       20        
Sally       25

GROUP BY

GROUP BY句を使用することで、合計や平均などの集計演算ができます。

  • 性別でグループ毎にカウント
sqlite> SELECT sex, COUNT(*) FROM Address GROUP BY sex;
  • 実行結果
sex         COUNT(*)  
----------  ----------
Man         4         
Woman       5
  • SQLの代表的な集計用の関数
演算子 意味
COUNT レコード数を数える
SUM 数値を合計する
AVG 数値を平均する
MAX 最大値を求める
MIN 最小値を求める
  • 住所毎にカウント
sqlite> SELECT address, COUNT(*) FROM Address GROUP BY address;
  • 実行結果
address     COUNT(*)  
----------  ----------
Cambridge   1         
Liverpool   1         
London      3         
Manchester  2         
Oxford      2
  • 全てを集計
sqlite> SELECT COUNT(*) FROM Address;

COUNT(*)  
----------
9
  • GROUP BY()はサポートしてない
sqlite> SELECT COUNT(*) FROM Address GROUP BY();
Error: near ")": syntax error

HAVING

HAVING句はWHERE句と同じようにレコードを特定します。
違いとしては、WHERE句がレコードに対して条件指定するのに対し、HAVING句はレコードの集合に対して条件指定を行います。

  • 1人だけの住所を選択
sqlite> SELECT address, COUNT(*) FROM Address GROUP BY address HAVING COUNT(*) = 1;
  • 実行結果
address     COUNT(*)  
----------  ----------
Cambridge   1         
Liverpool   1

ORDER BY

SQLでデータを取り出す時の動きは、基本的にランダムにデータを取り出します。
よって、明示的にデータを取り出す場合はORDER BY句を使用します。
デフォルトはASC（昇順）で取り出します。逆から取り出す場合はDESC（降順）を指定します。

  • 年齢で降順
sqlite> SELECT name, age FROM Address ORDER BY age DESC;
  • 実行結果
name        age       
----------  ----------
Jessica     55        
Dud         45        
Ham         32        
Ken         32        
Aaron       30        
Sally       25        
Edna        21        
Megan       20        
Lucy        19

VIEW

ビューは仮想的なテーブルを事前に作成することで、あたかもテーブルのデータを操作しているように中身を保護します。

  • 住所別の人数を求めるSELECT文のビューを保存
sqlite> CREATE VIEW CountAddress (address, cnt) AS SELECT address, COUNT(*) FROM Address GROUP BY address;
sqlite> SELECT address, cnt FROM CountAddress;
  • 実行結果
address     cnt       
----------  ----------
Cambridge   1         
Liverpool   1         
London      3         
Manchester  2         
Oxford      2

サブクエリ

ビューの中身にSELECT文を行うことをサブクエリと呼びます。

sqlite> SELECT address, cnt FROM (SELECT address, COUNT(*) AS cnt FROM Address GROUP BY address) AS CountAddress;
  • 実行結果
address     cnt       
----------  ----------
Cambridge   1         
Liverpool   1         
London      3         
Manchester  2         
Oxford      2
  • AddressテーブルからAddress2テーブルにいる人を選択
sqlite> SELECT name FROM Address WHERE name IN (SELECT name FROM Address2);
  • 実行結果
name      
----------
Lucy      
Ken

CASE

SQLの条件分岐はCASE文を使用します。CASE式の構文は「単純CASE式」と「検索CASE式」があり、検索CASE式は単純CASE式の機能をすべて含んでいます。

  • 検索CASE式で住所の結果表示を地方に分類
sqlite> SELECT name, address, CASE WHEN address = 'London' THEN 'Central city' WHEN address = 'Manchester' THEN 'North' ELSE NULL END AS district FROM Address;
  • 実行結果
name        address     district    
----------  ----------  ------------
Aaron       London      Central city
Edna        London      Central city
Dud         London      Central city
Ham         Manchester  North       
Jessica     Manchester  North       
Lucy        Oxford                  
Megan       Oxford                  
Sally       Cambridge               
Ken         Liverpool

UNION

UNIONは重複するレコードを削除した和集合を求めます。

  • AddressテーブルとAddressテーブル2がある場合の和集合を求める
sqlite> SELECT * FROM Address UNION SELECT * FROM Address2;
  • 実行結果
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Aaron       075-XXXXXXXX  London      Man         30        
Dud         075-XXXXXXXX  London      Man         45        
Duke                      Oxford      Man         20        
Edna        075-XXXXXXXX  London      Woman       21        
Ham         075-XXXXXXXX  Manchester  Man         32        
Jessica                   Manchester  Woman       55        
Ken         075-XXXXXXXX  Liverpool   Man         32        
Lucy        075-XXXXXXXX  Oxford      Woman       19        
Megan                     Oxford      Woman       20        
Sally       075-XXXXXXXX  Cambridge   Woman       25        
Steve                     Liverpool   Man         70

INTERSECT

INTERSECTは積集合を求めます。

  • AddressテーブルとAddressテーブル2に共通するレコードを求める
sqlite> SELECT * FROM Address INTERSECT SELECT * FROM Address2;
  • 実行結果
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Ken         075-XXXXXXXX  Liverpool   Man         32        
Lucy        075-XXXXXXXX  Oxford      Woman       19

EXCEPT

EXCEPTは差集合を求めます。
EXCEPTの注意点は、SQLで指定するテーブルの順番により結果が異なることです。
（UNIONやINTERSECTはどちらを先に書いても同じ）

  • AddressテーブルからAddressテーブル2に存在するレコードを除外する
sqlite> SELECT * FROM Address EXCEPT SELECT * FROM Address2;
  • 実行結果
name        phone_nbr     address     sex         age       
----------  ------------  ----------  ----------  ----------
Aaron       075-XXXXXXXX  London      Man         30        
Dud         075-XXXXXXXX  London      Man         45        
Edna        075-XXXXXXXX  London      Woman       21        
Ham         075-XXXXXXXX  Manchester  Man         32        
Jessica                   Manchester  Woman       55        
Megan                     Oxford      Woman       20        
Sally       075-XXXXXXXX  Cambridge   Woman       25

INNER JOIN

複数のテーブルを結合する場合は、JOINを使用します。
INNER JOINは内部結合を行います。

  • Addressテーブルの「名前」と「住所」、hobbiesテーブルの「趣味」を結合して取り出す（2つのテーブルの共通部分）
sqlite> SELECT A.name, A.address, H.hobby FROM Address AS A INNER JOIN Hobbies AS H ON A.name = H.name;
  • 実行結果
name        address     hobby     
----------  ----------  ----------
Aaron       London      Baseball  
Aaron       London      Soccer    
Edna        London      Baseball  
Edna        London      Soccer    
Dud         London      Baseball  
Jessica     Manchester  basketball
Ken         Liverpool   Baseball

OUTER JOIN

OUTER JOINは外部結合を行います。

  • Addressテーブルの「名前」と「住所」、hobbiesテーブルの「趣味」を結合して取り出す（2つのテーブルに含まれている全てのデータ（※））
sqlite> SELECT A.name, A.address, H.hobby FROM Address AS A LEFT OUTER JOIN Hobbies AS H ON A.name = H.name;
  • 実行結果
name        address     hobby
----------  ----------  ----------
Aaron       London      Baseball  
Aaron       London      Soccer    
Edna        London      Baseball  
Edna        London      Soccer    
Dud         London      Baseball  
Ham         Manchester            
Jessica     Manchester  basketball
Lucy        Oxford                
Megan       Oxford                
Sally       Cambridge             
Ken         Liverpool   Baseball

（※）本記事では、左外部結合を行っているため、hobbiesテーブルのみに含まれてるデータは出力されません。hobbiesテーブルのみに含まれてるデータを出力させる場合は、「RIGHT」を指定します。また、どちらか一方ではなく、全てのデータを出力させる場合は「FULL」を指定します。

データの更新

SQLでデータを更新する場合は、以下の3種類に分類されます。

  • 挿入（INSERT）
  • 削除（DELETE）
  • 更新（UPDATE）

INSERT

データベースに1行のレコードを挿入する場合は、INSERT文を使用します。
ポイントはSQLの列のリストと値のリストは、並び順が対応している必要があります。
また、文字型は必ず引用符でくくり、数値型はくくりません。「NULL」を挿入する場合は、そのままNULLを指定します。

  • Addressテーブルに1行挿入する
sqlite> INSERT INTO Address (name, phone_nbr, address, sex, age) VALUES ('Aaron', 'Aaron', 'London', 'Man', 30);

DELETE

データベースのレコードを削除する場合はDELETE文を使用します。

  • 全てのレコードを削除
sqlite> DELETE FROM Address;
  • 特定のレコードを削除
sqlite> DELETE FROM Address WHERE address = 'Oxford';

UPDATE

データの更新は、UPDATE文を使用します。

  • 特定のレコードを更新
sqlite> UPDATE Address SET phone_nbr = '075-12345678' WHERE name = 'Aaron';
  • NULLクリア
sqlite> UPDATE Address SET address = NULL;
  • 複数の列を同時に更新
sqlite> UPDATE Address SET phone_nbr = '075-12345678', age = 31 WHERE name = 'Aaron';

おわりに

SQLの学習にSQLZOOがオススメです。

2
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
4