SQL文について

勉強記録　

ここではよく使うSQL文について学習したのでまとめました。

DBはMySQLで、今回はいろいろなサイトを見つつ、MySQL管理ソフトである「Sequel Pro」を使ってSQL文を書く練習もしました。

テーブルの取得

ex) SELECT * FROM テーブル名;

 

指定したテーブルの全カラムを取得

*だと全カラム取得、カラム名を指定すれば取得したいカラムだけを取得することもできる

 

 

ex) SELECT id,name FROM テーブル名;

 

指定したテーブルから、idとnameカラムのみ取得する（取得したいカラムはカンマ区切りにする）

 

条件をつけてテーブルを取得する

ex) SELECT * FROM テーブル名　WHERE id<=10;

 

指定したテーブルから、idが10以下のデータを取得する

 

SQL上で演算や関数実行もできる

ex) SELECT id*2 FROM テーブル名;

 

idカラムの値を2倍にして取得できる

その他に、max(id)で最大値、min(id)でi最小値、avg(id)で平均など使える

 

カラムに別名をつける

ex) SELECT id AS 'アイディ' FROM テーブル名;

 

指定したテーブルのidカラム名に「アイディ」という別名をつけて、取得（テーブルに変更があるのではなく、ただカラム名の表示を変えているだけ）

 

並び替え

ex) SELECT * FROM テーブル名 WHERE id <= 50 ORDER BY id ASC;

 

指定したテーブルの中でidが50以下のデータを昇順に並び替える

 ORDER BY の後ろに並べたいカラム名、並び替え方法（ASC または DESC)

 

データの内容を指定して取得する

ex) SELECT * FROM テーブル名 WHERE name = 'かなママ'；

 

指定したテーブルの中で、nameが「かなママ」のものを取得する

 

ex) SELECT * FROM テーブル名 WHERE name = 'かなママ' AND id= 1;

 

指定したテーブルの中で、nameが「かなママ」かつidが1のものを取得する

※ANDの他にORもある→〜または〜

 

 

重複しているデータをまとめる(よく使う！）

ex) SELECT DISTINCT folder_id FROM テーブル名;

指定したテーブルの中で重複しているfolder_idをまとめる

 

取得するデータ数と開始位置を指定する(よく使う！）

LIMIT : 取得件数

OFFSET : 開始位置

 

ex) SELECT * FROM テーブル名 LIMIT 10 OFFSET 0;

指定したテーブルの1行目(引数としては0)から10件取得

 

ページャー機能をつくるときに使う

ex) 1ページに20件表示、次のページは前のページの続きから20件表示

ページャーがない→一度に取得するデータ数が多くなるからシステム障害につながる

 

2つのテーブルを結合して1回のSQLでデータを取得したいとき

⭐︎内部結合（INNER JOIN)

2つのテーブルで結合条件によって一致したデータ行のみを結合する

 

ex) SELECT * FROM folders  INNER JOIN tasks ON folders.id = tasks.folder_id;

 

⭐️外部結合（LEFT OUTER JOIN または　RIGHT OUTER JOIN)

2つのテーブルで結合条件によって一致しなかったデータ行も含めて結合する

LEFTだと左側のテーブルを軸にして外部結合

RIGHTだと右側のテーブルを軸にして外部結合

 

 ex) SELECT * FROM folders  LEFT OUTER JOIN tasks ON folders.id = tasks.folder_id;

 

※結合したとき、カラム名が重複している場合は、テーブル名.カラム名で指定する

重複してないときは、テーブル名書かなくても大丈夫だけど、明示的に書くべき！

 

サブクエリ（副問い合わせ）

SQL文の中に入れ子状態で入っているSQL文のこと

ex) SELECT id,name FROM (SELECT * FROM テーブル名 WHERE id <= 50 ）AS f ;

指定したテーブルのidが50以下のデータからidとnameを取得

 

※MySQLではASでエイリアス名（別名）をつけないと、エラーになる

 

 

集計を行う

ex) SELECT folder_id, count(folder_id) FROM tasks GROUP BY folder_id;

 

GROUP BY で指定したものをグループにする（まとめる）

指定したtasksテーブルの中で、同じfolder_idをまとめて、folder_idとそれぞれの個数をカウントして取得

 

GROUP BYのあとに条件をつけたいときは、WHEREではなくHAVINGを使う

 

ex) SELECT folder_id, count(folder_id) FROM tasks GROUP BY folder_id HAVING count(folder_id) <= 50;

レコードの追加

ex) INSERT INTO テーブル名 VALUES( 35 , 'かなママ', 100) ;

 

テーブル名のあとにカラム名を省略した場合には、すべてのカラムに値を入れないといけない

 

ex) INSERT INTO テーブル名 VALUES('かなママ',100),('かにママ',200),('かぬママ',300);

 

テーブル名のあとにカラム名を指定した場合には、指定したカラムのみの値を入れることができる。

※id はオートインクリメント（自動採番）になっているから、指定しなければ勝手にid振ってくれる

でもユニークキー制約がついてるから、すでにあるidは指定できない

 

↑上のように（　）を使ってINSERTで複数のデータを入れることを、バルクインサートという

パフォーマンスが早い　バルクインサートを使わないと、1件1件レコードを追加することになるのでシステム障害につながる

 

更新

ex) UPDATE テーブル名 SET name='かなママ',folder_id = 10 WHERE id=1 ;

 

指定したテーブルのid1のnameを「かなママ」に、folder_idを10に更新する。

（WHEREをつけないと、全データがこの内容で更新されてしまう！）

 

CONCAT（文字連結）

ex) UPDATE テーブル名 SET name=CONCAT('かな','ママ'),folder_id = 10 WHERE id=1 ;

 

CONCATを使えば文字連結できる。↑だとnameが「かな」と「ママ」が連結されて「かなママ」で更新される

このようにUPDATEのときに関数を使うこともできる

 

削除

ex) DELETE FROM テーブル名 WHERE id<=50;

 

WHEREをつけないと、全データが削除されてしまう！

しかし、MysqlではDELETEしても実は消えていない（見た目上削除されただけ）

OPTIMIZE TABLE(オプティマイズテーブル）かALTER TABLE(オルターテーブル）を実行しないと、DELETEしたデータがストレージを圧迫したままになる。