Help us understand the problem. What is going on with this article?

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;

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away