SQL入門編の忘備録1

paizaでSQLを復習したので忘備録を書いてみる。
これを見直してSQLの文法見直そうかと思ってる。

SQL入門編

◆SQL入門編1: SQLの基本文法を学ぶ

02:データベースを準備しよう

サンプルデータベースを作成するSQL文

--playersテーブル作成
CREATE TABLE players (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(32),
  level INT,
  job_id INT
);
--jobsテーブル作成
CREATE TABLE jobs (
  id INT NOT NULL PRIMARY KEY,
  job_name VARCHAR(10),
  vitality INT,
  strength INT,
  agility INT,
  intelligence INT,
  luck INT
);



INSERT
  INTO players(id,name,level,job_id)
  VALUES
    (1,"パイザ",12,6),
    (2,"ケン",7,2),
    (3,"リン",1,1),
    (4,"ユウ",3,3),
    (5,"クレア",10,4),
    (6,"ショウ",5,2),
    (7,"さくら",7,1),
    (8,"ジャック",5,4),
    (9,"ロック",12,6),
    (10,"じゅん",1,NULL);

INSERT
  INTO jobs(id, job_name, vitality, strength, agility, intelligence, luck)
  VALUES
    (1,"戦士",8,8,4,4,3),
    (2,"盗賊",3,3,8,5,7),
    (3,"狩人",5,5,7,5,4),
    (4,"魔法使い",3,2,6,8,6),
    (5,"僧侶",5,5,3,7,5),
    (6,"勇者",10,10,10,10,10);

#03:テーブルの中身を見てみよう 
今回取り上げたSQL文の例
-- 全てのデータを取り出す
SELECT * FROM players;

-- 一部のカラムだけ取得する
SELECT name, level FROM players;

-- 一部の行だけ取得する
SELECT * FROM players WHERE level >= 7;

-- 複数の条件を組み合わせる
SELECT * FROM players WHERE level >= 7 AND job_id <> 6;

--条件指定とカラム選択を組み合わせる
SELECT name, level FROM players WHERE level >= 7;

 WHERE句の条件指定
構文  意味  例
a = b   a と b が等しい    level = 10
a < b   a が b よりも小さい  level < 15
a > b   a が b よりも大きい  level > 7
a <= b  a が b 以下である level >= 15
a >= b  a が b 以上である level >= 7
a <> b  a と b が等しくない  level <> 1

条件をつなげるキーワード    意味
AND 両方の条件が成立した場合
OR  どちらか一方の条件が成立した場合

#04:いろいろな情報を取り出そう 

-- データ件数を表示する
SELECT COUNT(*) FROM players;

--条件に合ったデータの件数を表示する
SELECT COUNT(*) FROM players WHERE job_id = 6;

-- データを並び替えて取得する
SELECT * FROM players ORDER BY level;

-- データを並び替えて取得する 逆順
SELECT * FROM players ORDER BY level DESC;

--上位3件だけ表示す
SELECT * FROM players ORDER BY level DESC LIMIT 3;

-- 職業ごとに人数を集計する
SELECT job_id, COUNT(*) FROM players GROUP BY job_id;

＃05:データを追加・更新・削除しよう 

-- データを追加する
INSERT INTO players(id,name,level,job_id) VALUES(11, "霧島1号", 1, 1);

-- データを追加して表示する
INSERT INTO players(id,name,level,job_id) VALUES(12, "霧島2号", 1, 1);
SELECT * FROM players;

-- 一度に複数のデータを追加する
INSERT INTO players(id,name,level,job_id)
VALUES
(13, "霧島3号", 1, 1),
(14, "霧島4号", 1, 1)
;
SELECT * FROM players;

-- データを更新する
UPDATE players SET level = 10 WHERE id = 11;
SELECT * FROM players;

-- データを更新する。1増加
UPDATE players SET level = level + 1 WHERE id = 12;
SELECT * FROM players;

-- データを削除する
DELETE FROM players WHERE id = 13;
SELECT * FROM players;

-- データを削除する
DELETE FROM players WHERE id >= 11;
SELECT * FROM players;

＃06:2つのテーブルを結合しよう 

-- テーブルを結合して表示する（内部結合）
SELECT * FROM players INNER JOIN jobs ON jobs.id = players.job_id;

-- テーブルを結合して表示する(左結合)
SELECT * FROM players LEFT JOIN jobs ON jobs.id = players.job_id;

-- テーブルを結合して表示する(右結合)
SELECT * FROM players RIGHT JOIN jobs ON jobs.id = players.job_id;

＃07:結合したテーブルを操作しよう 

-- 結合したテーブルを操作する
SELECT * FROM players INNER JOIN jobs ON jobs.id = players.job_id;

-- 結合したテーブルで、指定カラムだけ表示
SELECT name, level, vitality FROM players INNER JOIN jobs ON jobs.id = players.job_id;

-- 結合したテーブルで、条件に合った行だけ表示
SELECT name, level, strength FROM players INNER JOIN jobs ON jobs.id = players.job_id WHERE strength >= 5;

-- 職業ごとに人数を集計する
SELECT job_id, job_name, COUNT(*) FROM players INNER JOIN jobs ON jobs.id = players.job_id GROUP BY job_id;