# SQLの使い方
## 0. SQLを導入する
### 0.1. SQLとは？
SQLはデータベースからデータを取り出す際に使われる**プログラミング言語**です．
SQLを記述することで，データベース内から所望のデータを入手したり，数え上げたりすることができます．また，データベースからデータを取得するために送る命令はクエリと呼ばれます．なのでSQLはクエリを記述するための言語といえます．
SQLでは特に関係データベース(Relational Database: RDB)を扱う場合が多いです．RDBの具体例として，たとえばSNSの制作を考えた際一つのユーザはフォローフォロワーというユーザ間の関係や投稿した記事との関係など様々なデータが異なるデータ間と関連づけて扱う必要があります．そのようなデータを扱う際に便利なデータベースがRDBです．
またほかによく聞くものとしてMySQLやSQLite,PostgeSQLなど名前にSQLが含まれるような者がありますがこれらはSQLを扱うことのできるソフトウェアの名前です．リレーショナルデータベース管理システム(RDB Management System, RDBMS)とも呼ばれます．
データベースは簡単に言えば表の集合体です．RDBでは，表同士の関係を「主キー」をもとに関連付けさせます．
たとえば以下のデータベースを，電化製品の購入データのデータベースであるとします．以下に示すユーザ用の表と購入履歴用の表がデータベースの内容です．
| ユーザID | 名前 | 年齢 |
| ---:|:----:|:----:|
| 001 | Taro | 30 |
| 002 | Jiro | 19 |
| 003 | Saburo | 22 |
| 004 | Hanako | 26 |
| 購入ID | 店舗 | 購入者 |商品|
| ---:|:----:|:----:|-:|
| 101 | Y電機 | 002 |Oculus Quest|
| 102 | 99電機 | 001 |GeForce RTX2080ti|
| 103 | Bカメラ | 001 |Mac Pro|
| 104 | Sマップ | 004 |Bose QuietComfort 35|
主キーはそれぞれの表においてユーザIDと購入IDです．この値は表内で一意である必要があります．この一意な値を使うことで，購入履歴の購入者とユーザを主キーの値から参照することで，商品とユーザを関連づけることができます．
### 0.2. macにMySQLを入れよう
#### インストール
Brewは既に入っていると思いますので以下のコマンドで完了です．
```shell
$ brew install mysql
```
バージョンを確認してエラーが出なければOKです．
```shell
$ mysql --version
mysql Ver 8.0.16 for osx10.14 on x86_64 (Homebrew)
```
#### 起動
MySQLはインストールしただけでは起動しません．以下のコマンドで起動させます．
```shell
$ mysql.server start
Starting MySQL
.. SUCCESS!
```
#### 停止
使わない場合は以下のコマンドで停止させます．
```shell
$ mysql.server stop
Shutting down MySQL
.. SUCCESS!
```
#### 情報表示
brew infoを使うことでMySQLの最初の起動方法などを確認することが可能です．
```shell
$ brew info mysql
# ...(中略)
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
mysql_secure_installation
MySQL is configured to only allow connections from localhost by default
To connect run:
mysql -uroot
To have launchd start mysql now and restart at login:
brew services start mysql
Or, if you don't want/need a background service you can just run:
mysql.server start
# ...
```
### 0.3. 初めてのログイン
実運用するデータベースなどの場合は，MySQLを扱うためには適切な権限を持ったユーザを作成する必要があります．今回の場合は，とりあえずすべての権限を持ったユーザでログインします．`mysql`コマンドの後に`-u ユーザ名`と入力することで，予めMySQLで登録したユーザでMySQLのDBを操作できる対話型のコマンド入力画面にログインすることができます．
デフォルトでは`-u root`と入力することで全権限を持ったユーザで(パスワードなしで)ログインすることができます．
```shell
$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.16 Homebrew
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
```
上のように`mysql>`と表示されたら準備完了です．
#### (備考)ルートにパスワードをつける
インストールの際に出てきたテキストの中にもあるが以下のコマンドで，rootにパスワードをつけることが可能です．
```shell
$ mysql_secure_installation
```
パスワード付きでログインするときは以下のように`-p`オプションをつけます．そうするとパスワードが聞かれますので先ほど設定したパスワードを入力します．
```shell
$ mysql -u root -p
Enter password:
```
## 1. 取得クエリ
では，MySQLを使って実際にデータベースからデータを取得させてみましょう．
データベースを扱う際にはまず何よりもデータがないと何もできませんので，MySQL公式が配布しているサンプルデータを利用します．mysqlから`exit`と入力してBashにいったん移ります．
Gitからサンプルデータをダウンロードします．
```shell
$ git clone https://github.com/datacharmer/test_db.git
$ cd test_db
$ mysql -u root < employees.sql
```
このデータは，雇用社員の個人情報や給与が記録された比較的大きなデータベースです．
まずは，このデータベースの特徴を掴むためいくつかのデータを表示させるクエリを投げてみましょう．
### 1.1. SELECT
#### 1.1. データベースを選択する
まずはどのデータベースを使用するかを選択します．
MySQL内のデータベースを表示する際は以下のコマンドを打ちます．
```sql
$ SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| employees |
| mysql |
| performance_schema |
| sys |
+--------------------+
```
今回導入したデータベースはemployeesです．このデータベースを今回は使うので以下のコマンドでデータベースを選択します．
```sql
$ USE employees;
```
#### 1.2. データベース内の表の一覧を表示
データベース$D$に属する表の一覧を取得するには以下のコマンドを打ちます．
```sql
$ SHOW TABLES FROM D
```
今回は以下のように打ちます．
```sql
$ SHOW TABLES FROM employees;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
```
#### 1.3. 表の中のデータを取得
あるデータベースの中の表$T$のデータを実際に取得するには以下のコマンドを打ちます．
```sql
$ SELECT * FROM T;
```
ただし，このコマンドの場合全てのデータが取得されてしまいます．なのでデータのうち，頭の$N$個のデータを取得するように指定したい場合や，$A$番目から$A+B$番目までのデータを指定したい場合には，以下のようにコマンドを打ちます．
```sql
$ SELECT * FROM T LIMIT N;
$ SELECT * FROM T LIMIT B OFFSET A;
```
さて，ここでSELECTが登場したわけですが，これは表の中から特定の列を抽出する命令です．「\*」は**全ての列**を抽出するためのエイリアスです．特定の列を取り出したい際にはカンマ区切りでSELECTの後ろに列の名前を記述します．
```sql
$ SELECT age,name,gender FROM T;
```
#### ⭐️演習⭐️
**1. employeeデータベースのemployeeの表から5件データを表示させよう**
**2. employeeデータベースのemployeeの表から，last_name,first_nameの列のみ抽出し，5件データを表示させよう**
### 1.2. WHERE
WHEREは条件を指定して条件を満たすデータのみを表示させるようにする命令です．
記述方法は主にSELECT...FROM...の後ろに以下のように記述します．
```sql
$ SELECT [column] FROM [table] WHERE [condition]
```
WHEREでは以下の演算子が使えます．
| 演算子 | 意味 |
| :--: | -------- |
| = |等しい |
| != |等しくない |
| <> |等しくない |
| < |より大きい |
| > |より小さい/未満 |
| <= |以上 |
| >= |以下 |
| AND |かつ |
| OR |または |
| NOT |否定 |
| BETWEEN A AND B |A以上B以下 |
| IN |後述 |
| LIKE |後述 |
これを使うと例えば今回のデータセットのうちsalariesの表は以下の列があるので
```sql
$ SELECT * FROM salaries LIMIT 3;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 60117 | 1986-06-26 | 1987-06-26 |
| 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| 10001 | 66074 | 1988-06-25 | 1989-06-25 |
+--------+--------+------------+------------+
```
from_dateが1988年5月1日から3日のデータを調べたければ
```sql
$ SELECT * FROM salaries WHERE from_date BETWEEN '1988-05-01' AND '1988-05-03';
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10606 | 43384 | 1988-05-01 | 1989-05-01 |
| 11224 | 40000 | 1988-05-01 | 1989-05-01 |
| 11329 | 61965 | 1988-05-01 | 1989-05-01 |
... (略)
| 499304 | 65298 | 1988-05-02 | 1989-05-02 |
+--------+--------+------------+------------+
599 rows in set (0.99 sec)
```
のように入力することで条件を指定してデータを抽出することができます．
#### 1.2.1. 演算子IN
INは条件を複数設定することができます．
```sql
SELECT * FROM users WHERE age IN(20, 30, 40);
```
のように入力し，列ageが20,30,40のいずれかに該当するデータを表usersから抽出します．
#### 1.2.2. 演算子LIKE
文字列が格納された列に対して一致検索を行うことができます．具体的には，ワイルドカードの文字`%`と`_`というものが存在して，それぞれ「任意の長さの文字列」と「任意の１文字」に該当するワイルドカードです．
すなわちデータが
```
慶應大学
早稲田大学
慶應大学院
```
のようにあった際，
```sql
$ SELECT * FROM universities LIKE '%大学'
慶應大学
早稲田大学
```
は文字列が〜大学でおわるものを抽出し，
```sql
$ SELECT * FROM universities LIKE '__大学'
慶應大学
```
は文字列が(何らかの２文字)大学のものを抽出します．
#### ⭐️演習⭐️
**1. 表employeesのうち列genderが'F'であるものを10件取得しよう**
**2. 表employeesのうち列birth_dateが「年の一の位が9の年の9月9日である」データを取得しよう**
**3. 表employeesのうち列last_nameが「'Yoshizawa'か'Meriste'」かつgenderが'F'であるデータを取得しよう**
### 1.3. ORDER BY
結果を昇順/降順に並び替えることができます．
昇順
```sql
$ SELECT * FROM salaries ORDER BY salary LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 253406 | 38623 | 2002-02-20 | 9999-01-01 |
| 49239 | 38735 | 1996-09-17 | 1997-09-17 |
| 281546 | 38786 | 1996-11-13 | 1997-06-26 |
| 15830 | 38812 | 2001-03-12 | 2002-03-12 |
| 64198 | 38836 | 1989-10-20 | 1990-10-20 |
+--------+--------+------------+------------+
```
降順
```sql
$ SELECT * FROM salaries ORDER BY salary DESC LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 43624 | 158220 | 2002-03-22 | 9999-01-01 |
| 43624 | 157821 | 2001-03-22 | 2002-03-22 |
| 254466 | 156286 | 2001-08-04 | 9999-01-01 |
| 47978 | 155709 | 2002-07-14 | 9999-01-01 |
| 253939 | 155513 | 2002-04-11 | 9999-01-01 |
+--------+--------+------------+------------+
```
## 2. 計数クエリ
データの個数や平均などを調べる際にもSQLのコマンドを使うことができます．
### 2.1. SUM,AVG
SUMを使うことで合計が，AVGで平均が得られます．
例: 給与の列の合計値
```sql
$ SELECT SUM(salary) FROM salaries;
+--------------+
| SUM(salary) |
+--------------+
| 181480757419 |
+--------------+
```
例: 給与の列の平均値
```sql
$ SELECT AVG(salary) FROM salaries;
+-------------+
| AVG(salary) |
+-------------+
| 63810.7448 |
+-------------+
```
### 2.2. COUNT
データの個数を調べることができます．
```sql
$ SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
| 300024 |
+----------+
```
WHEREと組み合わせれば条件を満たすデータの個数を調べることができます．
```sql
$ SELECT COUNT(*) FROM salaries WHERE salary > 90000;
+----------+
| COUNT(*) |
+----------+
| 234267 |
+----------+
```
### 2.3. MIN,MAX関数
列の最小値をMIN,最大値をMAXで得ることができます．
```sql
$ SELECT MIN(salary) FROM salaries;
+-------------+
| MIN(salary) |
+-------------+
| 38623 |
+-------------+
```
```sql
$ SELECT MAX(salary) FROM salaries;
+-------------+
| MAX(salary) |
+-------------+
| 158220 |
+-------------+
```
#### ⭐️演習⭐️
**1. 表titlesが何行あるか調べよう**
**2. 表titlesのうち列titleがSから始まる文字列であるデータがいくつあるか調べよう**
**3. 表salariesのうち列salaryが100000以下かつfrom_dateが1988年である個数を調べよう**
**4. 表salariesのうち列salaryが70000以下であるデータに対してその平均を求めよう**
## 3. 結合クエリ
結合クエリはテーブルの列の値を照合して，他のテーブルや自分のテーブル自身と結合するクエリです．
他のテーブルの主キーの列を持ったテーブルに対して結合を使えばその列の主キーがどのようなデータを表すか表示することができます．
### 3.1. 他のテーブルとの結合
これは最初に示したDBの例です．
usersテーブル
| ユーザID | 名前 | 年齢 |
| ---:|:----:|:----:|
| 001 | Taro | 30 |
| 002 | Jiro | 19 |
| 003 | Saburo | 22 |
| 004 | Hanako | 26 |
goodsテーブル
| 購入ID | 店舗 | 購入者 |商品|
| ---:|:----:|:----:|-:|
| 101 | Y電機 | 002 |Oculus Quest|
| 102 | 99電機 | 001 |GeForce RTX2080ti|
| 103 | Bカメラ | 001 |Mac Pro|
| 104 | Sマップ | 004 |Bose QuietComfort 35|
これに対して
```sql
$ SELECT * FROM goods
JOIN users
ON goods.購入者 = users.ユーザID;
```
のようなクエリを使用すると結合した表
| 購入ID | 店舗 | 購入者 |商品|ユーザID | 名前 | 年齢 |
| ---:|:----:|:----:|-:|---:|:----:|:----:|
| 101 | Y電機 | 002 |Oculus Quest| 002 | Jiro | 19 |
| 102 | 99電機 | 001 |GeForce RTX2080ti|001 | Taro | 30 |
| 103 | Bカメラ | 001 |Mac Pro|001 | Taro | 30 |
| 104 | Sマップ | 004 |Bose QuietComfort 35|004 | Hanako | 26 |
が得られます．
デモデータの場合はdept_empテーブルとdepartmentsテーブルをそれぞれのdept_noに応じて結合するといった場合には
```sql
$ SELECT * FROM dept_emp
JOIN departments
ON dept_emp.dept_no = departments.dept_no;
```
のように書くことができます．
また，結合には内部結合と外部結合があり，それぞれ照合先の存在しないデータに対する取り扱いが少し異なります．詳しく説明すると長くなるので[Qiitaのこちらの記事](https://qiita.com/naoki_mochizuki/items/3fda1ad6594c11d7b43c)を参照ください．
### 3.2. GROUP BY,CREATE TABLE,自己結合
GROUP BYは結合ではありませんが同じデータを集約することができる点で結合クエリとともに使用頻度が高いです．
GROUP BYは同じユーザが表内に複数のデータを持っているときにそれらを集計する際に便利です．
デモデータの場合，salariesに対して，emp_no(従業員ID)ごとのsalaryの平均値を求めた表を出力したい場合には以下のコマンドが使えます．
```sql
$ SELECT emp_no, AVG(salary)
FROM salaries
GROUP BY emp_no;
+--------+-------------+
| emp_no | AVG(salary) |
+--------+-------------+
| 10001 | 75388.9412 |
| 10002 | 68854.5000 |
| 10003 | 43030.2857 |
...
```
AS を使うことで列に対して別名をつけることができます．
```sql
$ SELECT emp_no, AVG(salary) AS average
FROM salaries
GROUP BY emp_no;
+--------+------------+
| emp_no | average |
+--------+------------+
| 10001 | 75388.9412 |
| 10002 | 68854.5000 |
| 10003 | 43030.2857 |
...
```
この集計結果を新たな表として保存したい場合にはCREATE TABLEを使います．
```sql
$ CREATE TABLE ave_salaries
AS
SELECT emp_no, AVG(salary) AS average
FROM salaries
GROUP BY emp_no;
```
これで先ほどの結果がave_salariesという表として保存されます．
次に，自己結合は表自身との結合を行うことができます．
自己結合を扱うために簡単なデータをいちから作ってみます．
表を新たに作る場合は先ほどのCREATE TABLEを使います．
ただし今回は一からつくるので列の作成を行う必要があります．
```sql
$ CREATE TABLE table_name (
列名 列の型,
列名 列の型,
...
)
```
のように指定します．
列の型は以下のものが使えます．
(引用: https://www.atmarkit.co.jp/ait/articles/0103/23/news003.html)
これを用いて例えば次のようにsampleテーブルを作ります
```sql
$ CREATE TABLE sample (
emp_id int,
name nvarchar(64), --64文字までの可変長文字列
boss_id int
);
```
このテーブルに実際にデータを入れる際にはINSERT文を使います．
```sql
$ INSERT INTO sample VALUES (
1,
'鈴木一郎',
3
);
$ INSERT INTO sample VALUES (
2,
'西郷隆盛',
3
);
$ INSERT INTO sample VALUES (
3,
'坂本龍馬',
NULL --該当するデータがない際はNULLを代入します．
);
```
```sql
$ SELECT * FROM sample;
+--------+--------------+---------+
| emp_id | name | boss_id |
+--------+--------------+---------+
| 1 | 鈴木一郎 | 3 |
| 2 | 西郷隆盛 | 3 |
| 3 | 坂本龍馬 | NULL |
+--------+--------------+---------+
```
これでようやく下準備が完了です．
SQLの自己結合は自分自身のテーブルに別名をつけて異なる列の値をもとに結合する子よができます．
上の例の場合，それぞれの社員の上司の名前を知りたいと言った際には次のようなクエリで自己結合を行います．
```sql
$ SELECT employee.emp_id, employee.name, boss.name AS bossname
FROM sample employee
LEFT OUTER JOIN sample boss
ON employee.boss_id = boss.emp_id;
+--------+--------------+--------------+
| emp_id | name | bossname |
+--------+--------------+--------------+
| 1 | 鈴木一郎 | 坂本龍馬 |
| 2 | 西郷隆盛 | 坂本龍馬 |
| 3 | 坂本龍馬 | NULL |
+--------+--------------+--------------+
```
まず，同一のsampleにemployeeとbossという別名をつけています．employeeのboss_idに一致するものをsampleのemp_idから探し，結合を行います．結合結果のうち，employeeのIDとname,及びbossのnameにbossnameという別名をつけて抽出します．
## ⭐️演習⭐️
下のコピペ部分を貼り付けて新たなテーブルを2つ作成してください．
### コピペ
```sql
CREATE TABLE ex_userlist (
user_id int,
name NVARCHAR(64),
salary int,
boss_id int
);
INSERT INTO ex_userlist VALUES (1, '織田信長', 3000000, 7);
INSERT INTO ex_userlist VALUES (2, '豊臣秀吉', 9000000, 1);
INSERT INTO ex_userlist VALUES (3, '明智光秀', 1000000, 2);
INSERT INTO ex_userlist VALUES (4, '与謝野晶子', 6000000, 5);
INSERT INTO ex_userlist VALUES (5, '卑弥呼', 10000000, 1);
INSERT INTO ex_userlist VALUES (6, '高須克弥', 17000000, 4);
INSERT INTO ex_userlist VALUES (7, 'ビルゲイツ', 900000000, NULL);
CREATE TABLE ex_goods (
shop_id int,
goods NVARCHAR(64),
price int,
user_id int
);
INSERT INTO ex_goods VALUES (1, 'ニンテンドーSwitch', 50000, 5);
INSERT INTO ex_goods VALUES (2, 'Nvidia RTX 2080ti', 170000, 6);
INSERT INTO ex_goods VALUES (3, 'モニター', 40000, 4);
INSERT INTO ex_goods VALUES (4, '他の藩の領土', 10000000, 2);
INSERT INTO ex_goods VALUES (5, '刀', 10000, 2);
INSERT INTO ex_goods VALUES (6, '天下', 101, 1);
INSERT INTO ex_goods VALUES (7, '勾玉', 60000, 5);
INSERT INTO ex_goods VALUES (8, '寄付', 1000000000, 7);
```
1. ex_goodsとex_userlistの全データを表示させよう
2. ex_userlistをsalaryが多い順に並び替えて表示させよう
3. ex_goodsのうちpriceが10000から100000円までのものを表示させよう
4. ex_goodsのうちpriceが10000から100000円までのものの個数を表示させよう
5. ex_userlistのうちsalaryが7000000以下であるデータの平均値をもとめよう．
6. ex_goodsとex_userlistを結合し，
|商品ID|商品名|価格|買った人の名前|
|---|---|---|---|
|1|ニンテンドーSwitch|50000|卑弥呼|
|...||||
となるようなクエリを投げよう
7. ex_userlistに対して
|ユーザID|名前|年収|上司の名前|
|---|---|---|---|
|1|織田信長|3000000|ビルゲイツ|
|...||||
となるようなクエリを投げよう
## 備考
### フレームワークとSQL
FlaskやDjango,Ruby on Railsなどのフレームワークを使ってSQLを扱うと言った際には，このようなSQL文を実際にデータベースに対して投げるということはあまりせず，FlaskやDjangoならPythonの文法，RoRならRubyの文法を使ってデータベースから値を取り出すことが多いです．そのため，実務でFWを使うと言った際には生のSQLを打つ機会というのはなかなかないかもしれません．ただ，SQL自身の仕様をよく理解することでより深くそのFWを使ったデータの扱い方を把握できるようになります．
また，データベースに対するマイグレーション機能(既存のテーブルにあらたな列を作成するなど)，モデル機能などこのSQLで習った概念とはまた異なるデータの扱い方を利用して効率的にデータを管理しています．
特にWebアプリなどを作る際にはMVCアーキテクチャと呼ばれる構造が主流でよく使われています．
https://qiita.com/tentom/items/de95f63bc9e2da2bd0bf
### SequelPro
SequelProで検索！
GUIでいい感じにDBを閲覧することができます．
Sign in with Wallet
Connect another wallet