MySQL
SQL

SQL実例集


はじめに

SQLの実例集です。

MySQLで検証しています。


データベース

一覧。

SHOW DATABASES;

作成。

CREATE DATABASE mydb;

削除。

DROP DATABASE mydb;

切り替え。

USE mydb;


テーブル

一覧。

SHOW TABLES;

削除。

DROP TABLE mytab;

テーブル定義の表示。

DESC mytab;

SHOW CREATE TABLE mytab;


制約

テーブルには以下の制約を設定できる。


  • 主キー制約(PRIMARY KEY)

  • 一意性制約(UNIQUE)

  • 参照制約(FOREIGN KEY)

  • 非NULL制約(NOT NULL)

  • 検査制約(CHECK)


主キー制約

特定の列、または列の組が主キーであることを保証する。

つまり、値が一意でかつNULLでないことを保証する。

CREATE TABLE user (

id INT PRIMARY KEY
);

または、

CREATE TABLE user (

id INT,
PRIMARY KEY (id)
);


一意性制約

特定の列、または列の組が候補キーであることを保証する。

つまり、値が一意であることを保証する。NULLは許容し、複数の行にNULLがあっても許容する。

CREATE TABLE user (

id INT UNIQUE
);

または、

CREATE TABLE user (

id INT,
UNIQUE (id)
);


参照制約

特定の列、または列の組が外部キーであることを保証する。

つまり、他のテーブルの特定の列の値と同じであることを保証する。NULLも許容。

CREATE TABLE country (

id INT PRIMARY KEY
);

CREATE TABLE user (
id INT PRIMARY KEY,
country_id INT,
FOREIGN KEY (country_id) REFERENCES country(id)
);


非NULL制約

特定の列がNULLでないことを保証する。

CREATE TABLE user (

id INT NOT NULL
);


検査制約

特定の列を検査し、条件に合致しない場合は許容しない。

MySQL5.5 では機能しなかった。

CREATE TABLE user (

age INT,
CHECK (age >= 0)
);


AUTO_INCREMENT

整数を自動でインクリメントする。

CREATE TABLE user (

id INT PRIMARY KEY AUTO_INCREMENT
);


DEFAULT

デフォルト値を設定する。

CREATE TABLE user (

name VARCHAR(64) DEFAULT 'anonymous'
);


実例

userテーブルとcountryテーブルの定義例。

CREATE TABLE country (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(128) NOT NULL UNIQUE,
created DATETIME NOT NULL,
modified DATETIME NOT NULL
);

CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(64) UNIQUE DEFAULT NULL,
age INT DEFAULT NULL,
country_id INT DEFAULT NULL,
created DATETIME NOT NULL,
modified DATETIME NOT NULL,
FOREIGN KEY (country_id) REFERENCES country(id)
);


SELECT

検証用のテーブル。

# 

CREATE TABLE country (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) UNIQUE
);

# 国の初期データ
INSERT INTO country (name) VALUES ('Japan');
INSERT INTO country (name) VALUES ('Taiwan');
INSERT INTO country (name) VALUES ('China');
INSERT INTO country (name) VALUES ('Korea');
INSERT INTO country (name) VALUES ('USA');
INSERT INTO country (name) VALUES (NULL);

# ユーザー
CREATE TABLE user (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL UNIQUE,
sex TINYINT(2) DEFAULT NULL COMMENT '0:male, 1:female',
age INT DEFAULT NULL,
country_id INT DEFAULT NULL,
created DATETIME NOT NULL,
modified DATETIME NOT NULL,
FOREIGN KEY(country_id) REFERENCES country(id)
);

# ユーザーの初期データ
INSERT INTO user (name, sex, age, country_id, created, modified) VALUES ('taro', 0, 20, (SELECT id FROM country WHERE name = 'Japan'), '2018/03/08 21:00:00', '2018/03/08 21:00:00');
INSERT INTO user (name, sex, country_id, created, modified) VALUES ('jiro', 0, (SELECT id FROM country WHERE name = 'Japan'), '2018/04/2 22:30:00', '2018/04/2 22:30:00');
INSERT INTO user (name, sex, age, country_id, created, modified) VALUES ('lin', 1, 16, (SELECT id FROM country WHERE name = 'Taiwan'), '2018/02/27 19:00:00', '2018/02/27 19:00:00');
INSERT INTO user (name, sex, age, country_id, created, modified) VALUES ('chen', 0, 4, (SELECT id FROM country WHERE name = 'China'), '2018/01/09 03:00:00', '2018/01/09 03:00:00');
INSERT INTO user (name, sex, age, country_id, created, modified) VALUES ('yon', 1, 20, (SELECT id FROM country WHERE name = 'Korea'), '2018/04/03 18:00:00', '2018/04/03 18:00:00');
INSERT INTO user (name, sex, age, country_id, created, modified) VALUES ('bob', 0, 30, (SELECT id FROM country WHERE name = 'USA'), '2018/02/28 15:00:00', '2018/02/28 15:00:00');
INSERT INTO user (name, sex, country_id, created, modified) VALUES ('mike', 0, NULL, '2017/05/03 18:00:00', '2017/05/03 18:00:00');

# ユーザー記事
CREATE TABLE article (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
status INT NOT NULL COMMENT '0:下書き, 1:公開',
view_counts INT NOT NULL DEFAULT 0 COMMENT '記事の閲覧数',
created DATETIME NOT NULL,
modified DATETIME NOT NULL,
FOREIGN KEY (user_id) REFERENCES user(id)
);

# ユーザー記事の初期データ
SET @user_id = (SELECT id FROM user WHERE name = 'taro');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, '太郎の日記1', '今日はいい天気だな。', 0, 3, '2018/03/09 12:00:00', '2018/03/09 12:00:00');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, '太郎の日記2', '次郎と遊ぶ約束をした。今月の6日に渋谷で会う。', 1, 512, '2018/05/02 15:00:00', '2018/05/02 12:00:00');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, '太郎の日記3', '次郎待ち。', 1, 320, '2018/05/06 12:00:00', '2018/05/06 12:00:00');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, '太郎の日記4', '次郎にすっぽかされた!ひどいやつだ。', 1, 483, '2018/05/07 07:00:00', '2018/05/07 07:00:00');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, '太郎の日記5', '次郎と連絡がとれない。何かあったのだろうか。', 1, 128, '2018/05/13 21:00:00', '2018/05/13 21:00:00');

SET @user_id = (SELECT id FROM user WHERE name = 'jiro');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, '次郎の日記1', '新しい部下が使えなくて困る。', 1, 480, '2018/04/03 23:00:00', '2018/04/03 23:00:00');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, '次郎の日記2', '日本のわびさびも理解できない奴が生意気だ。', 1, 12438, '2018/04/08 21:00:00', '2018/04/08 21:00:00');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, '次郎の日記3', '久しぶりに弟と会う。た', 1, 232, '2018/05/06 07:00:00', '2018/05/06 15:00:00');

SET @user_id = (SELECT id FROM user WHERE name = 'bob');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, 'Bob\'s diary 1', 'I\'m going to my office.', 1, 320, '2018/03/01 07:00:00', '2018/03/01 07:00:00');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, 'Bob\'s diary 2', 'My boss shout to me.', 1, 1239, '2018/04/07 21:00:00', '2018/04/07 21:00:00');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, 'Bob\'s diary 3', 'I can\'t accept my boss. I k', 1, 5238, '2018/05/03 23:00:00', '2018/05/06 02:00:00');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, 'Bob\'s diary 4', 'My boss was dead.', 0, 2, '2018/05/7 04:00:00', '2018/05/7 04:00:00');

SET @user_id = (SELECT id FROM user WHERE name = 'chen');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, '日记1', '我找到了工作', 1, 32, '2018/05/04 13:00:00', '2018/05/04 13:00:00');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, '日记2', '我发现了', 1, 63, '2018/05/06 09:00:00', '2018/05/06 09:00:00');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, '日记3', '我明白了', 1, 5, '2018/05/06 14:00:00', '2018/05/06 14:00:00');
INSERT INTO article (user_id, title, content, status, view_counts, created, modified) VALUES (@user_id, '日记4', '回家', 1, 24, '2018/05/07 12:00:00', '2018/05/07 12:00:00');

# ユーザー記事タグ
CREATE TABLE tag (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(128) NOT NULL UNIQUE
);

# ユーザー記事タグの初期データ
INSERT INTO tag (name) VALUES ('日記');
INSERT INTO tag (name) VALUES ('愚痴');
INSERT INTO tag (name) VALUES ('work');

# ユーザー記事とタグの紐付け
CREATE TABLE article_tag (
article_id INT NOT NULL,
tag_id INT NOT NULL,
PRIMARY KEY (article_id, tag_id),
FOREIGN KEY (article_id) REFERENCES article(id),
FOREIGN KEY (tag_id) REFERENCES tag(id)
);

# ユーザー記事とタグの紐付けの初期データ
SET @diary_tag = (SELECT id FROM tag WHERE name = '日記');
SET @guchi_tag = (SELECT id FROM tag WHERE name = '愚痴');
SET @work_tag = (SELECT id FROM tag WHERE name = 'work');
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '太郎の日記1'), @diary_tag);
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '太郎の日記2'), @diary_tag);
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '太郎の日記3'), @diary_tag);
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '太郎の日記4'), @diary_tag);
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '太郎の日記4'), @guchi_tag);
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '次郎の日記1'), @diary_tag);
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '次郎の日記2'), @diary_tag);
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '次郎の日記3'), @diary_tag);
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '次郎の日記1'), @guchi_tag);
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '次郎の日記2'), @guchi_tag);
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '日记1'), @work_tag);
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '日记2'), @work_tag);
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '日记3'), @work_tag);
INSERT INTO article_tag (article_id, tag_id) VALUES ((SELECT id FROM article WHERE title = '日记4'), @work_tag);

全件取得。

SELECT * FROM user;

カラムを射影して取得。

SELECT name, sex FROM user;

リミットを指定して取得。

SELECT * FROM user LIMIT 2;

1件目から2件取得(0オリジン)。

SELECT * FROM user LIMIT 1, 2;

名前を昇順にソートして取得。

SELECT * FROM user ORDER BY name;

名前を降順にソートして取得。

SELECT * FROM user ORDER BY name DESC;

性別が男。

SELECT * FROM user WHERE sex = 0;

性別が男でかつ20歳より下。

SELECT * FROM user WHERE sex = 0 AND age < 20;

性別が女または15歳より上。

SELECT * FROM user WHERE sex = 1 OR age > 15;

年齢がNULL。

SELECT * FROM user WHERE age IS NULL;

年齢がNULLでない。

SELECT * FROM user WHERE age IS NOT NULL;

名前が'taro'.

SELECT * FROM user WHERE name = 'taro';

名前に'taro'を含む。

SELECT * FROM user WHERE name LIKE '%taro%';

名前の先頭に'taro'を含む。

SELECT * FROM user WHERE name LIKE 'taro%';

名前の末尾に'taro'を含む。

SELECT * FROM user WHERE name LIKE '%taro';

年齢の平均。

SELECT AVG(age) FROM user;

年齢の合計。

SELECT SUM(age) FROM user;

名前と年齢を合成して取得。

SELECT

CONCAT(name, ' (', age , ')') AS name_age
FROM
user
WHERE
age IS NOT NULL
ORDER BY
name_age
;

テーブルにエイリアスを付けて取得。

SELECT u.name FROM user u WHERE u.age >= 20;

性別ごとに何人いるか集計。

SELECT sex, COUNT(id) FROM user GROUP BY sex;

10歳より上の年齢ごとに何人いるか集計。

SELECT age, COUNT(id) FROM user GROUP BY age HAVING age > 10;

年齢ごとに何人いるか集計し、各年齢の最大値が18より上のレコードを取得。

あまり意味のない条件だが、HAVINGでMAXが使える一例。

SELECT age, COUNT(id) FROM user GROUP BY age HAVING MAX(age) > 18;

最大の年齢。

SELECT MAX(age) FROM user;

最小の年齢。

SELECT MIN(age) FROM user;

ageがNULLなら-1を設定して取得。

SELECT name, IF(age IS NULL, -1, age) AS age FROM user;

SELECT name, IFNULL(age, -1) AS age FROM user;
SELECT name, COALESCE(age, -1) AS age FROM user;

ageがNULLならフラグを立て、NULLでないならフラグを折る。

SELECT

name,
age,
CASE
WHEN age IS NULL THEN 1
ELSE 0
END AS age_is_null
FROM
user
;

ageがNULLなら'unknown'、ageが20以上なら'adult'、その他を'child'に設定して取得。

SELECT

name,
age,
CASE
WHEN age IS NULL THEN 'unknown'
WHEN age >= 20 THEN 'adult'
ELSE 'child'
END AS age_status
FROM
user
;

プレースホルダ。

1件目から2件取得(0オリジン)。

SET @offs=1;

SET @max=2;
PREPARE STMT FROM 'SELECT * FROM user LIMIT ?, ?';
EXECUTE STMT USING @offs, @max;

ユーザ名と国名を取得。

SELECT

u.name AS user_name,
c.name AS country_name
FROM
user u
LEFT JOIN
country c
ON
u.country_id = c.id
;

ユーザ名と国名を取得。国名がNULLならUnknownに設定。

SELECT

u.name AS user_name,
IF(c.name IS NULL, 'Unknown', c.name) AS country_name
FROM
user u
LEFT JOIN
country c
ON
u.country_id = c.id
;

国名がNULLでないユーザ名と国名を取得。

SELECT

u.name AS user_name,
c.name AS country_name
FROM
user u
LEFT JOIN
country c
ON
u.country_id = c.id
WHERE
c.name IS NOT NULL
;

ユーザーごとに記事の閲覧数を合計し、ユーザ名と閲覧合計数を取得。

SELECT 

user.name AS user_name,
SUM(article.view_counts) AS sum_view_counts
FROM
article
LEFT JOIN
user
ON
user.id = article.user_id
GROUP BY
article.user_id
;

タグ毎にいくつ記事があるか集計してタグ名と記事数を降順にして取得。

SELECT

tag.name AS tag_name,
COUNT(article_tag.article_id) AS count_articles
FROM
article_tag
LEFT JOIN
tag
ON
article_tag.tag_id = tag.id
GROUP BY
article_tag.tag_id
ORDER BY
count_articles DESC
;

タグ名に紐付けられている記事数を取得する。

SET @search_tag_name = '日記';

SELECT
tag.name AS tag_name,
COUNT(article_tag.article_id) AS count_articles
FROM
tag
LEFT JOIN
article_tag
ON
article_tag.tag_id = tag.id
WHERE
tag.name = @search_tag_name
;

単語が記事のタイトルと内容、タグに含まれているか検索し、記事のidを射影する。

SET @search_word = 'diary';

SET @like_search_word = '%diary%';

SELECT
article.id
FROM
article
LEFT JOIN
article_tag
ON
article_tag.article_id = article.id
LEFT JOIN
tag
ON
article_tag.tag_id = tag.id
WHERE
tag.name = @search_word
OR
article.title LIKE @like_search_word
OR
article.content LIKE @like_search_word
;

複数の単語が記事のタイトルと内容、タグに含まれているかAND検索し、記事のidを射影する。

SET @search_word1 = '日記';

SET @like_search_word1 = '%日記%';
SET @search_word2 = '愚痴';
SET @like_search_word2 = '%愚痴%';
SET @count_search_words = 2;

SELECT
article.id
FROM
article
WHERE
@count_search_words = (
SELECT
COUNT(*)
FROM
article_tag
LEFT JOIN
tag
ON
article_tag.tag_id = tag.id
WHERE
article_tag.article_id = article.id
AND
tag.name IN (@search_word1, @search_word2)
)
OR (
article.title LIKE @like_search_word1
AND
article.title LIKE @like_search_word2
)
OR (
article.content LIKE @like_search_word1
AND
article.content LIKE @like_search_word2
)
;


時間関連

直近1年以内に登録したユーザーの名前を射影する。

SELECT user.name FROM user WHERE user.created >= NOW() - INTERVAL 1 YEAR;

直近3ヶ月以内に登録したユーザーの名前を射影する。

SELECT user.name FROM user WHERE user.created >= NOW() - INTERVAL 3 MONTH;

特定のフォーマットで時間を生成する。

SELECT DATE_FORMAT(NOW() + INTERVAL 1 YEAR , '%Y-%m-%d');


サブクエリ

平均年齢より年齢が大きいレコードの取得。

SELECT name, age FROM user WHERE age > (SELECT AVG(age) FROM user);

記事の投稿数が一番多いユーザーの名前と記事の投稿数を取得。

SELECT

grp.user_name AS user_name,
MAX(grp.count_articles) AS count_articles
FROM (
SELECT
user.name AS user_name,
COUNT(article.id) AS count_articles
FROM
article
LEFT JOIN
user
ON
user.id = article.user_id
GROUP BY
article.user_id
) AS grp
;

記事の閲覧数が一番多い国の国名と閲覧数の合計を取得する。

SELECT

grp.country_name AS country_name,
MAX(grp.sum_view_counts) AS sum_view_counts
FROM (
SELECT
country.name AS country_name,
SUM(article.view_counts) AS sum_view_counts
FROM
article
LEFT JOIN
user
ON
article.user_id = user.id
LEFT JOIN
country
ON
user.country_id = country.id
GROUP BY
country.id
) AS grp
;

記事の投稿数が一番多い国の国名と投稿数の合計を取得する。

SELECT

grp.country_name AS country_name,
MAX(grp.count_articles) AS count_articles
FROM (
SELECT
country.name AS country_name,
COUNT(article.id) AS count_articles
FROM
article
LEFT JOIN
user
ON
article.user_id = user.id
LEFT JOIN
country
ON
user.country_id = country.id
GROUP BY
country.id
) AS grp
;


参照