気ままに趣味旅行

面白いコト「拡がる/拡げる」

データベース、SQLの基礎1

どうもhiroakiです。

 

今回はデータベースを操作する言語、SQLについて紹介したいと思います。ウェブサービスなどを作る場合に必要となってきますので、押さえましょう。

SQLとは

SQLエスキューエル)はリレーショナルデータベースを操作するための言語です。SQLを使えば、データベースのデータを検索したり、データの追加・更新・削除などができます。

データベースとは、テキストや数値などのデータを保存するためのツールです。データベースを使うことでデータの検索、蓄積が簡単にできます。中でも、各データを複数の表(テーブル)に保存し、表を組み合わせて利用するデータベースをリレーショナルデータベース(RDB)と呼びます。リレーションとは、複数のテーブルを組み合わせる場合の関連付けのことです。重複しますが、このリレーショナルデータベースを操作するためにSQLが使われます。

今回は「データベースが既に作られていると仮定して」、SQLを説明していきます。

ここでは次のようなテーブル「users」があると仮定します。

id name old sex
1 hiroaki 25 man
2 tom 5 man
3 beru 11 women

この時、id, name, old, sexはカラム(列名、フィールド)と呼びます。また、カラムに対して、行のデータをレコードと呼びます。

このように、「データベースはカラム(列)単位でみていく」と覚えてください。

 

では、SQLに関してです。

usersテーブルの全データを取得するSQLを書くと次のようになります。ファイル拡張子は「.sql」になります。

  1. /* practice1.sql */
  2. SELECT * FROM users;

SELECTではテーブルのどのカラムを表示させるか記述します。SQLは大文字で書いても小文字で書いてもいいです。なので、「select *」でも動きます。
次に、SELECTの後ろに半角スペースを開けて取得するカラム名を書きます。全カラムを取得する場合は「*」です。

そして、次に「FROM テーブル名」と書きます。これで、usersテーブルからデータを取得できます。

最後に「;」をつけることを忘れないでください。このように、SQL文はSELECTで始まり、セミコロンで終わります。

ちなみに「/**/」でコメントを書くことができます。

 

また、次のようにカラム名を指定することも可能です。1つの場合はコンマをなくしてもらえれば大丈夫です。SQL文は一行で書ききることも可能ですし、改行することもできます。初めのうちは、見やすくするため改行して書いた方が覚えやすいかもしれません。

「--」では行末までがコメントとなります。

  1. -- practice1.sql
  2. SELECT id,name
  3. FROM users;

 

次は全データの中から条件に合うデータだけを取り出しましょう。条件は WHERE の後ろに書きます。「WHERE」が意味するのは、「どこのレコード(横の列)を取得するか」です。

usersテーブルのsexカラムのデータがmanの場合のnameとkanaを選択する場合は次のように指定します。

  1. select name,kana from users
  2. where sex="man";

文字列を指定する場合には「""」で囲みます。また、whereは「==」ではなく、「=」で指定します。

 

次にテーブルusersのうち、「性別が男で年齢が5歳の名前を選択し」ます。これは「sex=manかつold=5の条件に当てはまるnameカラムをselectする」という意味ですね。

  1. select name
  2. from users
  3. where sex="man" and old=5;

文字列のみ「""」で囲みます。

他にもWHEREにはOR、NOTなど指定できます。また、比較演算子のように値の大小の比較、日付の期間などいろいろな条件を指定できますので試してみてください。

 

データをグルーピングするにはGROUP BYを使います。これを使うことで、カラムの値が同じ集合ごとにグルーピングできます。

  1. SELECT sex, count(*) FROM users
  2. GROUP BY sex
  3. limit 2;

「LIMIT 数字」は、上から(数字)個を出力します。上の場合は2つですね。

「COUNT(カラム)」は関数です。 SELECT name, COUNT(*)のように関数を指定すると、グルーピングされた集合に対する演算の結果が出力されます。

関数 説明
count データ数
min 最小
max 最大
avg 平均
sum 合計

注意点ですが、COUNT関数でカラム名を指定した場合、null(何もない、空白)のデータ数は計算されません。nullの数も含めてデータの数を計算したい場合は、COUNT関数で 全カラム(*)を指定します。* を使った場合、特定のカラムのデータの数ではなく、レコードの数を計算するので、nullの数を含めたデータの数を数えられます。

また、グループ化するには、集計関数を取得するSQLの語尾に「GROUP BY カラム名」を追加します。GROUP BYを用いる場合、SELECT文で使えるのは、GROUP BYに指定しているカラム名(上記の場合、sex)と、集計関数(上記の場合、count(*))のみです。これは考えてみれば、例えば元々a、b、cなどと分かれていたカラム同士をあるひとつの項目ごとにグルーピングするなら、相手項目は合計や平均などの集計数でなければ、数値が合いませんからね。 

グループカラム sum(price)
A(a,b,c) 2000(a,b,c)
B(d,e) 1200(d,e)
C(f) 200(f)

 

カラム名などに別名をつけるには、「~として」という意味のASを使います。次のように「カラム名 AS "名前"」とすることで、カラム名に定義する名前を指定できます。すると出力後のデータテーブル名が変更されます。

  1. SELECT name as "家族" 
  2. FROM users
  3. where old>4;

 

またこれまで、上記のようにwhereなどを組み合わせてきましたが、SQLでは下記の順番に処理します。記述する順番と実行される順番が違うのは、仕方ないですが、重要なので押さえましょう。

FROM →ON、JOIN→ WHERE → GROUP BY → 関数→HAVING → SELECT、DISTINCT → ORDER BY→LIMIT

ちなみに、上記のように、グルーピング後に条件を指定したい場合は、whereは使えません。

この場合は、「GROUP BY カラム名 HAVING 条件」を使います。次はcustomerテーブルがあると仮定し「from customer」を指定します。

  1. SELECT rank, COUNT(*)
  2. FROM customers
  3. WHERE user_id =2
  4. GROUP BY rank
  5. HAVING COUNT(*) <=12;

このようにHAVING COUNT(*) <= 12をつけることで、GROUP BYでグルーピングした後で、データを絞り込めます。

 

「ある文字を含むデータ」を取得したい場合は、「〜のような」という意味の「LIKE演算子」を用います。

次のようにすると、「指定したカラムが〇〇を含む(〇〇のような)レコード」という条件になります。

「%」や「_」はワイルドカードといい、文字の代わりを意味します。「%」は0文字以上の任意の文字列を表し、「_」は任意の1文字を表します。うまく使ってください。

  1. SELECT * FROM items where itemcode like '%A-G_';

ちなみに、何のデータも保存されていない場合、フィールドはNULLとなりますが、NULLのデータを取得するためには「〜がNULLである」という意味になる、「IS NULL」というSQLを用います。例えば「WHERE カラム名 IS NULL」とすることで、「指定したカラムがNULLであるデータ」を取得することが可能です。

 

「カラムに特定の要素が含まれるかどうか」はINを使って判断します。次のようにすれば、customer_idが1, 2, 3のデータを取得できます。

  1. SELECT * FROM earnings
  2. where customer_id IN(1,2,3)

 

また、あるカラムが、指定した範囲に入っているか確かめるには「カラム BETWEEN X AND Y」と書きます。

  1. SELECT *FROM items
  2. where price between 1501 and 1599 LIMIT 5;

 

順番を並び替えるには「ORDER BY カラム名」を使います。ORDER BYは〜順に並べるという意味です。データを並び替えるためには、「並べ替えたいカラム名」と「並べ方」を指定する必要があります。

取得したデータに対して、「ORDER BY カラム名 ASC」とすることで、カラム名で昇順のソートができます。逆にDESCとすれば降順にソートできます。
ASCはascending orderの略で昇順を意味します。このASCはデフォルトで設定されますので省略可能です。

  1. SELECT *
  2. FROM items
  3. ORDER BY price desc
  4. LIMIT 5;

 

 

今回は以上になります。

次回もさらに詳しく説明していきますので、参考にしてみてください。

 

では。