SQLの基本的な使い方
MySQLの公式のサンプルのDBを引っ張ってきていじってみました。
基本的な動きと実行結果をまとめてみました。
MySQLはHomebrewでインストールして、
クエリの実行や実行結果の確認で使用したのはMySQL Workbenchです。
最初はネットページを参考に学習してましたが、途中から切り替えて主に以下の動画を参考にしました。
初学者に安心安全な山浦さんのイメージ。
どのDBを使用するか。(use)
use world;
実行結果
18:27:13 use world 0 row(s) affected 0.00051 sec
無事選択できたみたい。
一応確認。
SELECT database();
大丈夫みたい。
データをとってくる基本。(SELECT)
SELECT id ,name
FROM city;
id,nameをカラムという。
全てとってきたいときは*を使えばOK。
重複したくないとき。ユニークなデータを取りたい時。(DISTINCT)
DISTINCTを使う。
※regionはカラム名。
SELECT DISTINCT(region)
FROM country;
条件を指定したいとき(WHERE、AND,ORなどの使い方)
大陸がasiaの国をとってきたい。
SELECT
FROM country
WHERE continent = 'Asia';
AND(どちらも満たす)
大陸がasiaで人口が2000000よりも多い国をとってきたい。
SELECT *
FROM country
WHERE continent = 'Asia' AND population > 2000000;
OR(どちらかみたす)
アジア大陸かヨーロッパ大陸かどちらかに属する国をとってくる
SELECT *
FROM country
WHERE continent = 'Asia' or continent = 'Europe';
BETWEEN(間のものをとってきたい)
国の面積で700000から1000000の間の国をとってきたい。
SELECT *
FROM country
WHERE surfacearea BETWEEN 700000 AND 1000000;
IN,NOT IN
地域が'Western Africa''Middle East'どちらか当てはまる国をとってくる。
SELECT *
FROM country
WHERE region IN ('Western Africa','Middle East') ;
さっきのorでも書けるが冗長になるので、同じカラムを使う場合はこちらの方が望ましい模様。
どちらも当てはまってほしくない場合はNOT INを使う。
LIKE
aが頭文字の国をとってきたい
SELECT *
FROM country
WHERE name LIKE 'a%';
なお、後方一致、部分一致、完全一致なども使えるよう。
#### IS NULL , IS NOT NULL
独立年がわからないデータをとってくる
SELECT *
FROM country
WHERE indepyear IS NULL;
取ってくるレコード数を制限する(LIMIT)
SQLの実行時データ数が多いと負荷がかかりすぎたり、フリーズしてしまったりすることがあるらしい。
こちらから取ってくるレコード数とかも制限することができる。
例えば一つ上の独立年のコードの後ろにLIMITをつけてあげる。
SELECT *
FROM country
WHERE indepyear IS NULL
LIMIT 3;
こんな感じで制限できる。
並び替え。昇順、降順(ORDER BY,DESC)
ORDER BYを使うと基本的に昇順になる。
SELECT *
FROM country
WHERE population > 1
ORDER BY population
LIMIT 10;
降順にしたい場合はDESCを使う。
SELECT *
FROM country
WHERE population > 1
ORDER BY population DESC
LIMIT 10;
種類ごとに集計をかけたい(GROUP BY)
地域ごとの国の数を数えたい。
SELECT region, COUNT(name)
FROM country
GROUP BY region
order by COUNT(name);
どんな大陸なのかも調べたいな。
SELECT region, continent, COUNT(name)
FROM country
GROUP BY region
order by COUNT(name);
16:26:50 SELECT region, continent, COUNT(name) FROM country GROUP BY region order by COUNT(name) LIMIT 0, 1000 Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.country.Continent' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.00046 sec
あれ、エラー。
きちんとGROUP BYに指定することが必要のよう。
SELECT region, continent, COUNT(name)
FROM country
GROUP BY region ,continent
order by COUNT(name);
テーブル結合(INNER JOIN,OUTER JOIN)
SELECT *
FROM country as c
INNER JOIN countrylanguage as cl ON c.code = cl.countrycode;
国のテーブルのコードと国の言語のテーブルのレコードを一致したものをとってくることができた。
しかし、死ぬ程余計なデータが入ってきている。
これも先で出てきている、SELECT文でカラムを指定してあげれば良い。
SELECT c.name , cl.countrycode
FROM country as c
INNER JOIN countrylanguage as cl ON c.code = cl.countrycode;
こんな感じで指定できる。
条件にマッチしないレコードも残したい時(LEFT OUTER JOIN)
今回はNULLがない、条件にマッチするものが基本的にないのであれだが、
場合によっては条件にマッチしないものがある時もある。
そんなときはLEFT OUTER JOINを使う。
SELECT *
FROM country as c
LEFT OUTER JOIN countrylanguage as cl ON c.code = cl.countrycode;
CASE文 if文みたいなもの
SELECT
CASE WHEN region IN ('Central Africa') THEN '中央アフリカ'
WHEN region IN ('Southern and Central Asia') THEN '中央アジア'
ELSE NULL
END as region,
population
FROM country;
こんな感じで取得される。
数値を足して集計したいって
ときは次のような感じ。
SELECT
CASE WHEN region IN ('Central Africa') THEN '中央アフリカ'
WHEN region IN ('Southern and Central Asia') THEN '中央アジア'
ELSE NULL
END as region,
SUM(population)
FROM country
GROUP BY
CASE WHEN region IN ('Central Africa') THEN '中央アフリカ'
WHEN region IN ('Southern and Central Asia') THEN '中央アジア'
ELSE NULL
END;
ちなみにGROUP BYがないとエラーが起きる。
14:02:46 SELECT CASE WHEN region IN ('Central Africa') THEN '中央アフリカ' WHEN region IN ('Southern and Central Asia') THEN '中央アジア' ELSE NULL END as region, SUM(population) FROM country LIMIT 0, 1000 Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'world.country.Region'; this is incompatible with sql_mode=only_full_group_by 0.00039 sec
理由としては、クエリ内で集計関数（ここではSUM関数）が使用されており、非集約列と集約関数が同時に存在する場合、通常はGROUP BY句が必要になる。GROUP BY句を使用せずに集計関数を含むSELECT文を記述する場合、非集約列は使用できない。（GROUP BYとの関連性がないため。)
サブクエリ
国の面積について、平均以上の大きさの国を出したい。
SELECT AVG(surfacearea) AS avgsa
FROM country;
これで平均が出るので、これを元にSQLを実行。
SELECT *
FROM country
WHERE surfacearea >= 63248;
こんな感じで出る。しかし、面倒。
一つのでSQL文で終わらす方法がサブクエリ
要は「クエリの中にクエリを入れられる」って感じらしい。
SELECT *
FROM country
WHERE surfacearea >= (SELECT AVG(surfacearea)
FROM country);
こんな形で()でクエリを挿入することができる。
相関サブクエリ
一つ前では国々の全体の面積の平均で出した。
地位域ごとの平均を計算し、その平均以上の面積の国々を出したいときは」どうすれば良いのだろうか。
SELECT name ,region,surfacearea
FROM country as c1
WHERE surfacearea >= (SELECT AVG(surfacearea)
FROM country as c2
WHERE c1.region = c2.region
GROUP BY region);
こんな形になる。
国のテーブルから名前、地域、表面積を出して、regionごとに面積の平均を出すのだけど、条件は全体のクエリのregionと面積のAVGを出す時クエリ内のregionが一致するものの表面積を出す。
これをWHERE指定しないと、regionごとの面積のAVGが複数個並んだ実行結果が入ってしまい、エラーになる。
以下、エラー結果。
17:11:34 SELECT name ,region,surfacearea FROM country as c1 WHERE surfacearea >= (SELECT AVG(surfacearea) FROM country as c2 GROUP BY region) LIMIT 0, 1000 Error Code: 1242. Subquery returns more than 1 row 0.0029 sec
比較演算子を使用していおり、単数の値が返ることを期待されてるのに、複数個返ってきていると言われてしまう。
番外編
平均値も実行結果に表示したい時は以下のようにして、INNER JOINを使うとうまくできる。
SELECT c1.name, c1.region, c1.surfacearea, c2.avg_surfacearea
FROM country as c1
INNER JOIN (
SELECT region, AVG(surfacearea) as avg_surfacearea
FROM country
GROUP BY region
) as c2 ON c1.region = c2.region
WHERE c1.surfacearea >= c2.avg_surfacearea;
サブクエリ使用時の注意点
実行速度は遅いので使わなくて済むなら使わないほうが無難。
2段階以上の集計時には使わざるをえない。
どんなクエリが実行されているのか1個1個確認してみると良い。
Discussion