■現在の日時を求める関数
CURRENT_DATE()) AS 現在日時
■二つの日付の間の期間を整数で求める
TIMESTAMPDIFF(YEAR, (誕生日), (現在の日時))
ゲームユーザーのプレイ期間を計算するという練習問題はこんな感じ。。
SELECT
userID AS ユーザーID,
MIN(startTime) AS 開始日,
MAX(endTime) AS 終了日,
DATE(max(endTime)) - DATE(min(startTime)) +1 AS プレイ期間
FROM
eventlog
GROUP BY userID;
ゲームユーザーの年齢を計算する練習問題はこんな感じ。。
SELECT
userID AS ユーザーID,
year(current_date()) AS 現在年,
birth AS 生年月日,
year(current_date())-year(birth) AS 数え年,
Timestampdiff(year,birth,current_date()) AS 満年齢
FROM
users;
-- ユーザーごとの合計獲得金額と平均獲得金額
SELECT
eventlog.userID AS "ユーザーID",
SUM(events.increase_gold) AS "合計",
AVG(events.increase_gold) AS "平均"
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID
GROUP BY eventlog.userID
having sum(events.increase_gold) >= 50;
=======================================
-- 全てのデータを取り出す
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
【データを表示する】=======================================
-- データ件数を表示する
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;
【データの追加削除】=======================================
-- データを追加する
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;
【テーブル結合】=======================================
-- テーブルを結合して表示する（内部結合）
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;
【結合したテーブルの操作】=================================
-- 結合したテーブルを操作する
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;
エラーが出て実行されない時は以下をチェック。
※SELECTのカンマ忘れ、カンマ多すぎ注意！
※テーブル連結時のテーブル名不足！
※WHEREでAND忘れ！
【SQLのログ解析】 =======================================
-- 日次のアクセス数を求める
SELECT DATE(startTime), COUNT(logID)
FROM eventlog
GROUP BY DATE(startTime);
-- ある期間のアクセス数を求める
SELECT DATE(startTime), COUNT(logID)
FROM eventlog
WHERE DATE(startTime) BETWEEN "2015-04-01" AND "2015-04-30"
GROUP BY DATE(startTime);
-- 月次のアクセス数を求める
SELECT DATE_FORMAT(startTime, '%Y-%m'), COUNT(logID)
FROM eventlog
GROUP BY DATE_FORMAT(startTime, '%Y-%m');
--カラム名を別名で表示する
SELECT userID AS "アクティブユーザー"
FROM users;
--重複した行を省いて表示する
SELECT DISTINCT userID AS "アクティブユーザー"
FROM users;
--空のカラムの行を表示する
SELECT userID AS "アクティブユーザー"
FROM users
WHERE deleted_at IS NULL;
=======================================
