search
LoginSignup
2
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

placoleプラコレ Advent Calendar 2020 Advent Calendar 2020 Day 22

posted at

updated at

Organization

基本的なSQL構文集

本記事はプラコレAdvent Calendar 2020の記事です!
覚えておきたいSQLをまとめました!
(学んだことは随時追加更新していこうと思います!)

基本操作

テーブルを作成

テーブルを作成するにはCREATE TABLE文を使う。

create table テーブル名 (カラム名 データ型 オプション, ・・・);
  • テーブルを作成
SQL
create table testtable (id integer primary key, name text not null unique, age integer);
実行結果
CREATE TABLE

テーブルに新しい行を挿入

insert into テーブル名(カラム名、カラム名、カラム名)
values (値、値、値);
  • 1行挿入する
SQL
insert into testtable(id, name, age)
values (1, 'Alice', 20);
実行結果
INSERT 0 1    ← 1行挿入されたことを表す
  • 複数行挿入する
SQL
insert into testtable(id, name, age)
values(1, 'Alice', 20)
     ,(2, 'Bob'  , 30)
     ,(3, 'Cathy', 40);
実行結果
INSERT 0 3    ← 3行挿入されたことを表す

テーブルの行を検索

select カラム名 from テーブル名;
  • テーブルに含まれる行をすべて表示
SQL
select *             ← 「*」はすべての列を表示
from testtable;      ← テーブルの行をすべて表示
実行結果
id   | name  | age 
-----+-------+----- 
1    | Alice | 20
2    | Bob   | 30
3    | Cathy | 40
(3 rows)
  • 名前が「Bob」の行だけを検索、また名前と年齢の列だけを表示
SQL
select name, age       ← name列とage列だけを表示
from testtable
where name = 'Bob';    ← nameが「Bob」の行だけを表示
実行結果
id   | name  | age 
-----+-------+----- 
2    | Bob   | 30
(1 rows)

テーブルの行を更新

update 文では条件を指定しなければすべての行が更新される。

  • update文を使って、すべての行を更新する
SQL
update testtable
set age = age + 1;
実行結果
UPDATE 3      ← 「3行更新された」という意味
  • ある特定の行だけを更新する
SQL
update testtable
set age = 27
where name = 'Bob';
実行結果
UPDATE 1      ← 「1行更新された」という意味

テーブルから行を削除

  • 条件に該当する行を削除する
SQL
delete from testtable
where name = 'Bob';
実行結果
DELETE 1      ← 「1行削除した」という意味
  • すべての行を削除する
SQL
delete from testtable;
実行結果
DELETE 3

テーブルを削除

SQL
drop table testtable;
実行結果
DROP TABLE    ← 「テーブルが削除された」ことを表す

SELECT文

使用するテーブル

members
id   | name     | height | gender
-----+----------+--------+--------
101  | エレン     | 170    | M
102  | ミカサ     | 170    | F
103  | アルミン   | 163    | M
104  | ジャン     | 175    | M
105  | サシャ     | 168    | F
106  | コニー     | 158    | M

select句 : 値の出力

何らかの値を出力するには、select句を使う。

SQL
select 'Hello';
実行結果
?column?
----------
Hello
(1 row)

計算式の指定もできる。

SQL
select 1 + 2 + 3 + 4;
実行結果
?column?
----------
10
(1 row)

from句 : テーブルを指定する

テーブルを指定してその中身(つまりテーブルに含まれている行)を出力するには、from句を使う。

・全ての行を検索する

SQL
select * 
from members;
実行結果
id   | name     | height | gender
-----+----------+--------+--------
101  | エレン     | 170    | M
102  | ミカサ     | 170    | F
103  | アルミン   | 163    | M
104  | ジャン     | 175    | M
105  | サシャ     | 168    | F
106  | コニー     | 158    | M

・select句に列名を指定する

SQL
select gender, name
from members;
gender  | name
--------+----------
M       | エレン
F       | ミカサ
M       | アルミン
M       | ジャン
F       | サシャ
M       | コニー
(6 rows)

where句 : 行を選択する

ある特定の行だけを選ぶ(あるいは除外する)には、where句で条件を指定する

SQL
select *
from members
where name = 'ミカサ';
実行結果
id   | name     | height | gender
-----+----------+--------+--------
102  | ミカサ     | 170    | F
(1 rows)

andやorで条件をつなげることもできる。

SQL
select * from members
where gender = 'M'
   or height >= 170;
実行結果
id   | name     | height | gender
-----+----------+--------+--------
101  | エレン     | 170    | M
102  | ミカサ     | 170    | F
103  | アルミン   | 163    | M
104  | ジャン     | 175    | M
106  | コニー     | 158    | M
(5 rows)

andでつなげた条件をさらにorでつなぐこともできる

SQL
select * from members
where (gender = 'M' and height >= 170)
   or (gender = 'F' and height < 170);
実行結果
id   | name     | height | gender
-----+----------+--------+--------
101  | エレン     | 170    | M
104  | ジャン     | 175    | M
105  | サシャ     | 168    | F
(3 rows)

order by句 : 整列する

小さい順や大きい順に並び替えることを整列(Sort)という。
select文では、order by句を使って整列する。

SQL
select *
from members
order by name;
実行結果
id   | name     | height | gender
-----+----------+--------+--------
103  | アルミン   | 163    | M
101  | エレン    | 170    | M
106  | コニー    | 158    | M
105  | サシャ    | 168    | F
104  | ジャン    | 175    | M
102  | ミカサ    | 170    | F
(6 rows)

順番を逆にしたい場合はdescをつける

SQL
select *
from members
order by height desc;
実行結果
id   | name     | height | gender
-----+----------+--------+--------
104  | ジャン    | 175    | M
101  | エレン    | 170    | M
102  | ミカサ    | 170    | F
105  | サシャ    | 168    | F
103  | アルミン   | 163    | M
106  | コニー    | 158    | M
(6 rows)

order by句をつけないと順番は不定になる。
なるべくorder by句をつけるようにする。
同じ値のときは順番が定まらないので、ID順にするとよい。

limit句とoffset句 : 範囲を指定

先頭のn行だけ出力するにはlimit句を使う。
逆に、先頭からのn行は出力しない(スキップする)にはoffset句を使う。

先頭の 3 行をスキップしてから、続く 1 行だけを出力する。

SQL
select * from members order by id
limit 1 offset 3;
実行結果
id   | name     | height | gender
-----+----------+--------+--------
104  | ジャン    | 175    | M
(1 rows)

DBエンジン内部の実行順序

  • select句はいちばん最後に実行
  • それ以外は上から順に実行

※厳密には、select句はいちばん最後ではなく、order by句の前に実行される。
基本は上記のように覚えておけば問題ない。

グループ化と集約関数

group by 句

group by 句を使うと、テーブルの内容をグループに分けて集約できる。

代表的な集約関数は以下。
集約関数は複数の値を受け取り、1つの値を返すような関数のこと。

・合計する(sum( ))
・平均する(avg( ))
・最大値(max( ))
・最小値(min( ))
・行数を数える(count( ))

SQL
select gender, avg(height)
from members
group by gender;

桁数を指定する(to_char(x, y))

avg(height)の結果が165.0000000000の時
to_char(avg(height), '999.99')で165.00にすることができる。

nullを強制的に0に変換する(coalesce(x, y))

min(height)の結果がnullのとき、
coalesce(min(height), 0)で、nullを強制的に0に変換できる。

having句

group by 句によって行をグループ化できる。
having句を使うと、それらのグループに対して選択条件を指定できる。

性別でグループ化してから、平均身長が170cm以上のグループだけを表示

SQL
select gender, avg(height)
from members
group by gender
having avg(height) >= 170;

where句とhaving句の違い

・where句はテーブルの行を対象とする。group by句の前に実行される。
・having句はグループを対象とする。group by句の後に実行される。

よくある間違い

group by 句を使うと、select句に指定できるのは
「グループ化のキー」と「集約関数の呼び出し」だけ。
これ以外を指定すると、構文エラーになる。

グループ化のキーに式を使う

名前の長さでグループ化し、それぞれの人数を数える

SQL
select length(name), count(*)
from members
group by lenght(name);

列名は式の一種なので、当然group by 句にも式を指定できる。

その他の集約関数

・ string_agg( ):文字列を連結する
・ array_agg( ):複数の値を配列にする
・ json_object_agg( ):JSON データを作る
・ json_agg( ):JSON 配列を作る

便利な機能

コメント

・範囲コメント

SQL
/* ←ここから範囲コメント   
 何書いても飛ばす  
ここまでが範囲コメント→ */

・行コメント

-- ← ここから行末までが行コメント
select *  ← 行頭じゃなくても行コメント
from users;

列の別名

SQLでは列や式に別名(Alias)をつけることができる。

別名をつける理由は以下の通り。
(a)ヘッダのラベル名を変えるため
(b)長い列名や複雑な式に簡単な名前をつけて参照するため
(c)同じ名前を2回書かずに済ますため

SQL
select id as ID, name as 名前, height as 身長
from members;

(asは省略可能)

実行結果
id  |  名前 | 身長
----+------+------
102 | ミカサ | 170
105 | サシャ | 168
(2 rows)

※実行結果のラベルは「ID」だけ変わらずに「id」のままになっている。
なぜかというと、SQLでは基本的に大文字と小文字を区別しない仕様なので、
「ID」と指定しても「id」と同じだと見なされるため。
この場合は「"ID"」のようにダブルクォーテーションで囲むとよい。

テーブルの別名

SQLでは、テーブルにも別名(Alias)が付けられる。

SQL
select m.id, m.name, m.height
from members m 
where m.gender = 'F'
order by m.id;

こうする理由は2つある。
(a)SQLのキーワードと同じ名前の列名を使うとき、キーワードでないことを明示するため。
(b)1つのSQLで複数のテーブルを扱う場合、どのテーブルの列名なのかを明示するため。

演算子

文字列結合演算子

文字列を結合するには「||」という演算子を使います。

SQL
select 'Hello, ' || 'World' || '!';
実行結果
?column?
---------------
Hello, World!
(1 row)

パターンマッチ演算子

「like」という演算子を使うと、文字列がパターンにマッチしているかどうかを調べることができる。

「%」・・・どんな文字列にもマッチする(0文字でもよい)。
「_」・・・どんな1文字にもマッチする。

・「ン」で終わる名前だけを検索

SQL
select name
from members
where name like '%ン';

in演算子

「in」演算子は、複数の値を列挙して、その中に指定値が含まれているかどうかを調べる演算子。

SQL
select *
from members
where name in ('エレン', 'ミカサ', 'アルミン')
order by id;

null

nullとは値(データ)がないことを表すキーワード。

nullかどうかを調べるには、「is null」と「is not null」を使う。

SQL
select name
from members
where height is null;

最後に

以上です!また続編書きます!

明日は僕の師匠であるオッスンさんです!
記事楽しみにしています!

Now hiring!
プラコレでは、自由な未来をつくるために
一緒に冒険したいエンジニア・デザイナーを募集しています!
https://www.wantedly.com/projects/262436
運営サービス
PLACOLE(プラコレウェディング)
DRESSY(ドレシー)byプラコレ
farny(ファーニー)byプラコレ

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
What you can do with signing up
2
Help us understand the problem. What are the problem?