SQL入門 - データベース操作の基本を身につける

# はじめに
SQL（Structured Query Language）は、データベースから情報を取得したり操作したりするための言語です。この記事では、SQLの基本的な概念と使い方を学びます。

## SQLとは？
SQLは、リレーショナルデータベースと呼ばれる特定の種類のデータベースを操作するための標準的な言語です。データの挿入、更新、削除、取得など、あらゆる操作を行うことができます。

## 学習方法について

以下の無料で利用できるオンライン学習サイトがお勧めです

https://sqlab.net/

## リレーショナルデータベースについて

リレーショナルデータベース（RDBMS）は、データを構造化した形（通常はテーブル）で保存し、それぞれのデータ項目が関連性を持つように設計されています。以下に、主要なリレーショナルデータベース管理システム（RDBMS）の例をいくつか挙げます。

1. **MySQL**
オープンソースのRDBMSで、ウェブベースのアプリケーション（特にPHP環境）でよく使われます。その特徴として、手頃なパフォーマンス、豊富なツール、広範なプラットフォーム互換性があります。

2. **PostgreSQL**
高度なRDBMSで、オープンソースでありながら多くのエンタープライズ級の機能を提供します。複雑なクエリ、多数の同時接続、高い信頼性などが求められる場面でよく使われます。

3. **Oracle Database**
世界で最も広く使われているエンタープライズ級RDBMSの一つで、非常に大規模なデータベースと高度なデータ処理が可能です。

4. **SQL Server**
Microsoftが開発しているRDBMSで、特にWindows環境での利用が一般的です。使いやすさと統合されたBI（ビジネスインテリジェンス）ツールが特徴です。

5. **SQLite**
軽量で高速なオープンソースのRDBMSで、データベース全体を単一のファイルとして保存します。小〜中規模のアプリケーションや組み込み型のシステムで使われることが多いです。

以上のように、各RDBMSはそれぞれ特徴と利点があり、用途により適したものを選ぶことが重要です。

## リレーショナルデータベースの共通コマンド
後述する以下の基本的なSQLの構文はどのデータベースシステムでも共通です。

- SELECT：テーブルの列を選択する
- FROM：テーブルを選択する
- JOIN：2つ以上のテーブルを結合し、関連するデータを取得する
- WHERE：特定の条件でフィルタリングする
- GROUP BY：特定の列からデータの集計をする（合計、平均、カウントなど）
- HAVING：GROUP BYの結果をフィルタリングする
- ORDER BY：並べ替えする

ただし、一部の高度な機能や特定の関数（日付と時間の操作、文字列操作など）は、データベースシステムにより異なる書き方や挙動をする場合があります。したがって、特定のデータベースシステムで作業を行う場合は、そのデータベースのドキュメンテーションを参照することが重要です。

## SQLを使うための準備
SQLを学ぶためには、MySQLやPostgreSQLなどのリレーショナルデータベース管理システム（RDBMS）をローカルにインストールすることもありますが、オンラインの学習ツールを利用するのが手軽で便利です。今回はMAPMのMySQLを使用します

## MySQLに接続する

コマンドラインからMySQLに接続するためには以下のコマンドを使います：
```bash
mysql -h [hostname] -u [username] -p
```
- [hostname]
MySQLサーバーのホスト名またはIPアドレス
（ローカルで動作している場合はlocalhost）
- [username]
MySQLのユーザー名
-pオプションを使うと、コマンドを実行した後にパスワードの入力を求められます。
MampのMySQL接続情報は次のとおりです
![スクリーンショット 2023-08-01 12.41.46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2765288/f88136e4-5480-d811-bdce-d923ab2df571.png)

MampのMySQLに接続する際は以下のコマンドを使いました
```bash
mysql -u root -p -S /Applications/MAMP/tmp/mysql/mysql.sock
```
- -S /Applications/MAMP/tmp/mysql/mysql.sock
MySQLサーバーとの通信に使用するUNIXソケットのパスを指定します。

上記コマンドは主にMacでMAMPを使用している場合のもので、Windowsや他の環境ではソケットのパスが異なるか、またはソケットを使用せずにホスト名とポート番号を直接指定することが一般的です。

## SQLでテーブルを作成するコマンド
```sql:company_dbというデータベースを作成する
CREATE DATABASE company_db;
```
```sql:データベースを操作対象に選択する
USE company_db;
```
```sql:見本のテーブル（employees）を作成する
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10, 2),
    department_id INT
);
```
```sql:見本のテーブル（departments)を作成する
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
```

```sql:見本のテーブルにデータを注入する
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  salary INT,
  department_id INT
);

-- 「employees（従業員）」という名前のテーブルを作成します
-- 以下の4つの列（フィールド）があります。

-- id: 従業員を一意に識別するための番号（ここでは主キーとして設定）
-- name: 従業員の名前（最大255文字のテキスト）
-- salary: 従業員の給料（整数）
-- department_id: 従業員が所属する部署のID（整数）

INSERT INTO employees (id, name, salary, department_id)
VALUES
  (1, 'Bob', 50000, 1),
  (2, 'Alice', 60000, 2),
  (3, 'Charlie', 70000, 1),
  (4, 'Dave', 55000, 3),
  (5, 'Eve', 75000, 2),
  (6, 'Frank', 80000, 3);

--「employees」テーブルに新たな6つのレコードを追加します
-- 「Bob」という名前の従業員は給料が50000で、部署1に所属していることを示しています

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

-- 「departments（部署）」という名前のテーブルを作成します
-- このテーブルには以下の2つの列（フィールド）があります。

-- id: 部署を一意に識別するための番号（ここでは主キーとして設定）
-- name: 部署の名前（最大255文字のテキスト）

INSERT INTO departments (id, name)
VALUES
  (1, 'HR'),
  (2, 'Sales'),
  (3, 'IT');
-- departments」テーブルに新たに3つのレコードを追加します
-- 各レコードは、指定された列の値のリストとして表示されます。
-- IDが1の部署は「HR」という名前であることを示しています。
```

**employeesテーブル**
| id | name | salary |department_id|
|:-----------|------------:|:------------:|------------:|
| 1       | Bob        | 50000         | 1        |
| 2     | Alice      | 60000       | 2        |
| 3       | Charlie        | 70000         | 1        |
| 4         | Dave          | 55000           | 3        |
| 5       | Eve       | 75000       | 2        |
| 6    | Frank     | 80000      | 3        |

**departmentsテーブル**
| id | name | 
|:-----------|------------:|
| 1    | HR        |
| 2     | Sales      |
| 3       | IT        |

## SQLでテーブルを操作するコマンド





SQLの基本コマンドは以下の通りです。

- SELECT：データを取得します。
- FROM：データが取得されるテーブルを指定します。
- WHERE：特定の条件を満たすデータをフィルタリングします。

これらのコマンドを組み合わせてデータベースに対して`問い合わせ`することをSQLクエリと呼びます

以下に参考になるクエリを提示します
### 検索条件をつけてデータを取得する(WHERE)
```sql:WHERE
SELECT name, salary
FROM employees
WHERE salary > 50000;
```
### 2つのテーブルを結合させる(JOIN)
employeesテーブルから給料が50,000より大きいすべての従業員のnameとsalaryを選択します」という操作を実行します。
```sql:JOIN
SELECT employees.name, departments.name
FROM employees
JOIN departments ON employees.department_id = departments.id;
```
- SELECT employees.name, departments.name
取得したいデータを指定しています
`employeesテーブルのname列`と`departmentsテーブルのname列`を選択しています。

- FROM employees
主テーブルを指定しています
このクエリではemployeesが主テーブルです。

- JOIN departments ON employees.department_id = departments.id
二つのテーブルを結合する方法を指定しています
`employeesテーブルのdepartment_id列`と`departmentsテーブルのid列`が一致するレコードを結合しています。この一致する列の値をキーと呼びます。

このクエリの結果は、各従業員の名前と、その従業員が所属する部門の名前のリストになります。
### グループ化(GROUP BY)
```sql:GROUP BY
SELECT departments.name, AVG(employees.salary)
FROM employees
JOIN departments ON employees.department_id = departments.id
GROUP BY departments.name;
```
このSQLクエリは、employeesテーブルとdepartmentsテーブルを結合し、それぞれの部門について平均給与を計算します。




- **GROUP BY departments.name**
結果をどのようにグループ化するかを指定しています。具体的には、部門名（departments.name）でグループ化します。この結果、それぞれの部門について一つの行が得られ、その行には部門の名前とその部門の平均給与が含まれます。

したがって、このクエリの結果は、各部門の名前と、その部門の従業員の平均給与を一覧したものになります。

### グループから条件検索する（HAVING)
```sql
SELECT departments.name, AVG(employees.salary)
FROM employees
JOIN departments ON employees.department_id = departments.id
GROUP BY departments.name
HAVING AVG(employees.salary) > 50000;
```
- **HAVING**
GROUP BYで生成されたグループをフィルタリングします。

このクエリの結果は平均給料が50,000より高い部門を選択するようになります

### 並び替え（ORDER BY）
```sql:ORDER BY
SELECT departments.name, AVG(employees.salary)
FROM employees
JOIN departments ON employees.department_id = departments.id
GROUP BY departments.name
HAVING AVG(employees.salary) > 50000
ORDER BY AVG(employees.salary) DESC;
```
- **ORDER BY**
結果を特定の列の値に基づいて並べ替えます。

このクエリは部門の平均給料を降順（高いものから低いものへ）に並べ替える例です。

## SQLの書き方（順序）

SQLのクエリは特定の順序で書く必要があります。その順序は以下の通りです

1. SELECT
1. FROM
1. JOIN（もしあれば）
1. WHERE（もしあれば）
1. GROUP BY（もしあれば）
1. HAVING（もしあれば）
1. ORDER BY（もしあれば）

たとえば、次のようなクエリが考えられます。
```sql:SQLの順序
SELECT employees.name, AVG(employees.salary)
FROM employees
JOIN departments ON employees.department_id = departments.id
WHERE departments.name = 'HR'
GROUP BY employees.name
HAVING AVG(employees.salary) > 50000
ORDER BY AVG(employees.salary) DESC;
```
このように各節（SELECT、FROM、JOINなど）は特定の順序で記述され、それぞれが特定の機能を果たします。これらの節を適切に組み合わせることで、さまざまなデータ操作や分析を行うことができます。

ただし、必ず全ての節を使用するわけではありません。問題や目的によって、必要な節だけを選んで使うことができます。例えば、全ての従業員の名前を取得するだけなら

```sql
SELECT name FROM employees;
```
 のように書くことができます。

## まとめ

SQLは、データ分析やデータベース管理、アプリケーション開発など、あらゆる領域で使われる強力なツールです。基本コマンドをマスターすることで、あらゆる種類のデータから価値ある情報を引き出す能力を身につけることができます。