MYSQL基本まとめ

SQL(MySQL)についてのまとめ

SHOW文

テーブル名をすべて表示

SHOW tables;

これで登録されているすべてのテーブルを見ることができます

登録されているカラムを表示

SHOW columns from users; -- columnsをfieldsでも可
DESCRIBE users; --DESC users;に省略可

これでテーブルに登録されているカラムの情報を見ることができます。

SELECT文

特定のカラムを取得

SELECT * FROM users; -- カラムをすべて取得
SELECT id,name FROM users; -- idとnameを取得

*を指定するとすべて取得できる。
複数のカラムを取得するときは , で区切る

指定した件数のレコードを取り出す(LIMIT,OFFSET)

SELECT id,name FROM users LIMIT 10; -- 1~10件目まで取得
SELECT id,name FROM users LIMIT 10 OFFSET 10; -- 11~20件目を取得

LIMITで指定した件数のレコードを取得できます。
また、OFFSETをつけることで飛ばすことが可能。上の例だと11~20番目のレコードを取得。
mysqlではOFFSETのみだとエラーになるので、すべて取得する場合はSELECT * FROM users LIMIT 10000000 OFFSET 10;とする

並び替え(ORDER BY)

SELECT * FROM users ORDER BY age ASC; --年齢が若い順
SELECT * FROM users ORDER BY created_at DESC; --新着順

ORDER BY カラム名で並び替えができる。ASCは昇順、DESCは降順。
デフォルトはASC

集約関数(count,sum,avg,max,min)

SELECT count(id) FROM users; --　個数
SELECT avg(rating) FROM reviews; --平均値
SELECT sum(price) FROM products; -- 合計
SELECT max(price) FROM products; -- 最大値
SELECT min(price) FROM products; -- 最小値

関数(カラム名)でそのカラムの平均や個数、最大値など求められる

グループ化(GROUP BY)

SELECT grade,count(*) FROM students GROUP BY grade; --学年ごとの人数取得
SELECT product_id, AVG(rating) FROM reviews GROUP BY product_id; -- 商品ごとの平均レビュー

GROUP BYでグループ化することができます。要はそのカラムの要素別にまとめれます。
先ほどの集約関数と組み合わせることでそのグループの個数や平均、最大値が分かります。
SELECT grade,count(*) FROM students GRUOP BY gradeは学年ごとのレコード数(人数)を取得できます。
出力は以下の通り。countの中身はnullじゃないならidでもnameとかでもいいです。ただしnull場合はカウントされないので*にするのが無難

grade	count(*)
1	200
2	210
3	205

カラム名を変更する(AS)

SELECT grade AS '学年',count(*) AS '人数' FROM students GRUOP BY grade;

カラム名に対しAS 別名にすることで任意の名前で取得できる。
以下のように出力される。

学年	人数
1	200
2	210
3	205

文字列を結合(CONCAT())

SELECT
CONCAT(grade,'年生') AS '学年' , count(*) AS '人数'
FROM students GROUP BY grade;

CONCAT()を使うことで文字列を結合して出力してくれる。
以下のように出力される

学年	人数
1年生	200
2年生	210
3年生	205

WHERE文

条件を満たすレコードを取得

SELECT * FROM users WHERE id = 1; -- idが1であるもの
SELECT * FROM users WHERE id > 10; --idが11以上のものを取得

whereは取得するレコードに対して条件を付けることが可能

複数の条件または一つの条件を満たすレコードの取得(AND,OR)

SELECT * FROM users WHERE id >= 1 AND id <= 10; -- 1~10のidを取得
SELECT * FROM users WHERE age >= 18 OR id <= 30; -- idが30以下であるもの又は18歳以上

where句の条件に対して、ANDまたはORを使うことができる
上はidが1以上かつ10以下であるもの。下は年齢が18以上かidが30以下であるもの。

範囲の条件を満たすレコードの取得(BETWEEN)

SELECT * FROM users WHERE id BETWEEN 1 AND 10;
SELECT * FROM users WHERE birth BETWEEN '2020-01-01' AND '2021-01-01';

カラム名　BETWEEN A AND BでAからBまでの範囲に含まれるものを取得できる
上のクエリはSELECT * FROM users WHERE id >= 1 AND id <= 10;と同じ

いずれかに一致するレコードの取得(IN)

SELECT * FROM users WHERE id IN (1,4,9,16,25);

IN(値1,値2,..)とすることで複数の条件に一致するレコードを得られる。
これもORを使うことで再現は可能。

文字列の部分一致検索(LIKE)

SELECT * FROM users WHERE name LIKE '%amethyst%';
SELECT * FROM users WHERE name LIKE '%amethyst';
SELECT * FROM users WHERE name LIKE 'amethyst%';
SELECT * FROM users WHERE name LIKE '_methyst';
SELECT * FROM users WHERE email LIKE '[e-mail]'; 

LIKEを使うことで部分一致検索をすることができる。
%は0文字以上の文字列、_は任意の一文字を表します。
WHERE name LIKE '%amethyst%'はamethystを含む文字列（例：theamethystrock）
WHERE name LIKE '%amethyst'はamethystで終わる文字列（例：blueamethyst）
WHERE name LIKE 'amethyst%'はamethystから始まる文字列（例：amethyststone)
WHERE name LIKE '_methyst'は1文字+methystの文字列（例：bmethyst)
ちなみにaとあは違うものとみなされるのでローマ字で検索してもヒットしません

テーブル結合

テーブル同士の結合について。以下のテーブルを想定しています。

productsテーブル

id	name	price
1	リンゴ	200
2	ブドウ	300
3	バナナ	400

reviewsテーブル

id	product_id	rating
1	1	★4
2	1	★5
3	3	★3

内部結合(INNER JOIN)

SELECT * FROM products
INNER JOIN reviews ON products.id = reviews.product_id
ORDER BY products.id ASC;

INNER JOIN テーブル名　ON 結合させるカラムでテーブル同士を結合させることができます
クエリの出力は以下の通り

id	name	price	id	product_id	rating
1	リンゴ	200	1	1	★4
1	リンゴ	200	2	1	★5
3	バナナ	400	3	3	★3

このようにレビューされなかった商品(id=2)については結合されません。これを内部結合という。

外部結合(LEFT JOIN , RIGHT JOIN)

SELECT * FROM products
LEFT JOIN reviews ON products.id = reviews.product_id;
SELECT * FROM products
RIGHT JOIN reviews ON products.id = reviews.product_id;

LEFT JOIN テーブル名 ON 結合させるカラム名で結合させることができる
外部結合は内部と違って関連のないレコードも出力します。つまり残します。
外部結合はLEFTとRIGHTがあり、それぞれどちらを残すかの判断です。
普通は残したい方をFROMに用いるので基本的にはLEFT。
今回の例ではRIGHTにしても出力は上のINNERと変わりません。
product_id(レビューする商品)が存在しなかった場合に出力が変わります
クエリの出力は以下の通り(LEFTの場合)

id	name	price	id	product_id	rating
1	リンゴ	200	1	1	★4
1	リンゴ	200	2	1	★5
2	ブドウ	300	NULL	NULL	NULL
3	バナナ	400	3	3	★3

重複を許可しない(DISTINCT)

SELECT DISTINCT name FROM products
INNER JOIN reviews ON products.id = reviews.product_id; --リンゴ、バナナが出力

DISTINCT カラム名で重複なしで取得できる。
これによってパフォーマンスを少し向上できる。

条件分岐(CASE~END)

SELECT id,
CASE
 WHEN rating >= 4 THEN '高評価'
 WHEN rating >= 3 THEN 'そこそこ'
 ELSE '低評価'
END
AS '評価'
FROM reviews;

条件分岐をしたい場合、まずCASE~ENDで囲います。
あとはif文と同じようにWHEN 条件 THEN 値という感じです。上から順に分岐。
また、ELSEをつけることでどの条件にも一致しなかった場合の処理が可能

サブクエリ

サブクエリとはクエリの中にクエリを書くこと。

WEHREを用いたサブクエリ

SELECT * FROM reviews
WHERE rating > (SELECT avg(rating) FROM reviews);　--平均よりも高いレビュー

()で囲うことでサブクエリを書くことができる。このクエリでは全レビューの平均値よりも高いレビューのものを表示

FROMを用いたサブクエリ

SELECT name
FROM (SELECT * FROM users WHERE age >= 18) AS adults; -- 成人の名前を取得

FROMを用いることで対象となるテーブルに対しても条件を課せられる。
この場合、ASで別名(エイリアス)が必須です

INNER JOINを用いたサブクエリ

SELECT products.name, latest_reviews.rating,latest_reviews.content
FROM products
INNER JOIN (
  SELECT * FROM reviews
  WHERE created_at IN (
    SELECT MAX(created_at) FROM reviews GROUP BY product_id
  )
) AS latest_reviews
ON products.id = latest_reviews.product_id; -- 各商品の一番最近のレビューを取得

INNER JOINでもテーブル名にあたる部分でサブクエリを使うことができます。
上のクエリはWHERE created_at IN (SELECT MAX(created_at) FROM reviews GROUP BY product_id)で各商品ごとの最新であるものを指しています。(MAX(created_at)が重複してない限り)
FROM同様エイリアス(AS句)を忘れずに

最後に

今回はsql(mysql)の基礎を学んだので自分なりにまとめてみました。他のDBでも基本的には同じらしいです。クエリや説明が間違ってたらすみません😢