DB、SQL 勉強まとめ

投稿の目的

• 個人的な勉強のためのメモとして上げているので暖かい目で見守ってください。

RDBMS(リレーショナルデータベースマネージメントシステム)

• RDBを管理するためのソフトウェアの総称。
• RDBMSの種類
  • Oracle
  • Mysql
  • Microsoft SQL server
  • PostgreSQL
  • SQLite

MySQL 操作　

mysql -h ホスト名 -u ユーザー名 -D データベース名

• mysqlでデータベースに接続する。

SQL(ストラクチャードクエリランゲージ)

• SQLとはデータベースの操作などを行うための言語です。

データを操作するもの

show

• テーブルやカラムの情報を確認できる。

show tables;  /* 現在のDBのテーブル一覧を表示 */

show columns from テーブル名; /* 指定したテーブルのカラム情報を表示 */

SELECT

• データの取得する際に行う。

SELECT カラム名 FROM テーブル名;

INSERT

• データを新規で追加する際に行う。
• カラム名は複数指定することができ、カラム数と同じ数をVALUESに指定します。

INSERT INTO テーブル名(カラム名) VALUES(追加するデータ);

UPDATE

• データ更新する際に行う。
• 条件を指定しないと指定したカラムのデータすべてに変更が適用されます。

UPDATE テーブル名 SET カラム名 = 'データ' WHERE 条件

DELETE

• データを削除する際に行う。
• DELETEもUPDATE同様条件を指定しないと指定したテーブルのデータすべてが削除されます。

DELETE FROM テーブル名 WHERE 条件

データを絞り込む

WHERE

• 条件を付けてデータを絞り込む。
• 「=(イコール)」で値を指定できる。

SELECT * FROM shop WHERE id = 20;

LIKE

• %は何でも何文字でも曖昧検索が可能。
• _は一文字曖昧検索が可能。
• 「NOT」を付ける事で不要なデータを除去ができる。

/* 指定された文字が含まれているのを表示 */
SELECT * FROM shop WHERE shop_name LIKE '%さん%'; 
SELECT * FROM shop WHERE shop_name LIKE '%屋さん%'; 

/* 末尾に指定された文字が含まれている */
SELECT * FROM shop WHERE shop_name LIKE '%屋さん';

/* 先頭に指定された文字が含まれている */
SELECT * FROM shop WHERE shop_name LIKE 'ピザ%';

/* 先頭と末尾に指定された文字が含まれている */
SELECT * FROM shop WHERE shop_name LIKE 'ピザ%さん';
SELECT * FROM shop WHERE shop_name LIKE 'お%さん';
SELECT * FROM shop WHERE shop_name LIKE 'お%屋さん';

/* 指定されている文字が含まれていたら除去 */
SELECT * FROM shop WHERE shop_name NOT LIKE '%ピザ%';

/* この間は何文字かを指定 */
SELECT * FROM shop WHERE shop_name LIKE 'お__屋さん';

IN

• 複数の値を指定できる。

SELECT * FROM shop WHERE shop_name IN('おそば屋さん', 'おうどん屋さん');

or

– 結果は同じだけど、書き方が複雑になるので同一カラムで複数の値を検索する場合は「or」では検索しない

SELECT * FROM shop where shop_name = 'おそば屋さん' or shop_name = 'おうどん屋さん';

between

• 指定した範囲でデータを検索する。
• NOTは否定を指しています。

SELECT * FROM shop where id between 2 AND 7;

SELECT * FROM shop where id NOT between 2 AND 7;

、 <、 >=、 <= (不等号)

SELECT * FROM shop where id >= 10;

and、or、!

• andはかつ、両方という意味です。
• orはまたは、どっちかという意味です。
• !は否定を指しています。

SELECT * FROM shop where id = 2 AND shop_name = 'お寿司屋さん';

SELECT * FROM shop where id = 2 or shop_name = 'お寿司屋さん';

SELECT * FROM shop where id != 2 AND shop_name = 'お寿司屋さん';

-- != は別の書き方もあります。

SELECT * FROM shop where id <> 2;

データを並び替える

Order by(オーダーバイ)

• Order byは並び替えを行うことができます。
• ORDER BYはカラムの後にasc(昇順), desc(降順)を指定できる。デフォルトはasc

SELECT * FROM shop ORDER BY id;

SELECT * FROM shop ORDER BY id DESC;

• WHEREで絞った後に並べ替えも可能

SELECT * FROM shop WHERE id > 2 ORDER BY id DESC;

データを集計

count

• 件数を算出する

SELECT COUNT(*) FROM shop;

SELECT COUNT(shop_name) FROM shop;

• このSQLお店の名前で重複したものをカウントしない。

SELECT COUNT(distinct shop_name) FROM shop;

グループ化

Group by

• データを集計する軸となるカラムを指定する。

SELECT shop_name, COUNT(*) FROM shop GROUP BY shop_name;

SELECT shop_name, COUNT(*) FROM shop WHERE shop_name != 'ピザ屋さん' GROUP BY shop_name;

having

• Group byで集計したものを絞り込む。

SELECT shop_name, COUNT(*) FROM shop GROUP BY shop_name HAVING count(*) >= 2; 

as

• AS(アズ(読み方):エイリアス)を使ってシンプルにすることが可能。

SELECT
    shop_name, 
    COUNT(*) AS cnt
FROM 
    shop 
GROUP BY 
    shop_name 
HAVING cnt >= 2; 

SUM

• 合計値を取得

SELECT SUM(id) FROM shop;

• グループ化も可能

SELECT SUM(id) FROM shop GROUP BY category_id;

MAX

• 最大値を算出する

SELECT MAX(id) FROM shop;

• グループ化も可能

SELECT MAX(id) FROM shop GROUP BY category_id;

MIN

• 最小値を算出する

SELECT MIN(id) FROM shop;

• グループ化も可能

SELECT MIN(id) FROM shop GROUP BY category_id;

AVG

• 平均値を算出する

SELECT AVG(id) FROM shop;

• グループ化も可能

SELECT AVG(id) FROM shop GROUP BY category_id;

テーブルを結合する

join

• テーブル同士を結合すること。めっちゃ頻出。

• joinの種類は大きく分けて2つ

  • 内部結合(INNER JOIN)
    • 互いの条件に一致するレコードをのみを抽出する
    • 片方のテーブルにしか存在しない値は間引かれる。
  • 外部結合(OUTER JOIN)
    • 結合元と結合先で値が一致しないものも抽出する。
  • LEFT OUTER JOIN
    • 左側のテーブルを軸に結合する。
  • RIGHT OUTER JOIN
    • 右側のテーブルを軸に結合する。
  • FULL OUTER JOIN
    • 結合の優先順位が存在しない。

INNER JOIN

• 一致するものだけを取得する

SELECT * FROM users AS u INNER JOIN microposts AS m ON u.id = m.user_id;  

select
    *
from
    users as u
    inner join microposts as m
        on u.id = m.user_id;

• 名前、順番なども変更が可能

select
    u.id as 'ユーザーID',
    m.user_id as '結合ID',
    m.id as 'ポストID',
    u.name as '氏名',
    u.email as 'メールアドレス',
    m.content as '投稿'
from
    users as u
    inner join microposts as m
        on u.id = m.user_id;

• テーブル名を反対にしても結果は同じ

select
    u.id as 'ユーザーID',
    m.user_id as '結合ID',
    m.id as 'ポストID',
    u.name as '氏名',
    u.email as 'メールアドレス',
    m.content as '投稿'
from
    microposts as m
    inner join users as u
        on u.id = m.user_id;

LEFT JOIN

• usersを軸にして結合して値が存在しない場合はNULL表示

SELECT 
    *
FROM
    users AS u
    LEFT JOIN microposts AS m
        ON u.id = m.user_id;

RIGHT JOIN

• micropostsを軸にして結合して値が存在しない場合はNULL表示

SELECT
    *
FROM
    users AS u
    RIGHT JOIN microposts AS m
        ON u.id = m.user_id;