GTB、LAMP開発基礎の自習内容 MySQL編

# 目的

MySQLによるデータベースの基本操作が一通りできるようになること。

# 流れ

- データベースとは？
- SQLを書いてみよう
- GMOグループデータベースを作ろう
- もっと大きなデータを扱ってみよう

# データベースとは？

> 1. データを整理・統合して格納し、
> 2. そのデータを検索・活用しやすくしたコンピュータ上に構築された）仕組み

引用：[ORACLE基礎から始めるデータベース入門セミナー](http://www.oracle.com/technetwork/jp/articles/index-313275-ja.html)

## RDB (Relational Database)型が主流

> 1件のデータを複数の項目(フィールド)の集合として表現し、データの集合をテーブルと呼ばれる表で表す方式。ID番号や名前などのキーとなるデータを利用して、データの結合や抽出を容易に行うことができる。中小規模のデータベースでは最も一般的な方法。データベースの操作にはSQLと呼ばれる言語を使うのが一般的。![image](https://qiita-image-store.s3.amazonaws.com/0/111110/b3b5a29d-bfdc-3e39-f300-d3ee99b1f0f0.png)


引用:[e-Words IT用語辞典](http://e-words.jp/w/E383AAE383ACE383BCE382B7E383A7E3838AE383ABE38387E383BCE382BFE38399E383BCE382B9.html)

## いろいろあります、データベース（RDB）

- オープンソース
 - MySQL
 - PostgreSQL
- 有料
 - Microsoft ACCESS(デスクトップ向け)
 - Oracle Database
 - Microsoft SQL Server (ちょっと変わってきたけど)
 - DB2

# MySQLとは？

> マルチユーザ、マルチスレッドで動作し、高速性と堅牢性に定評がある。オープンソースなので基本的には無償で利用することができ、国内では有償でサポートを提供する企業もある。
> Windowsや各種UNIX系OSなど、多くのプラットフォームで動作するのも特長の一つ。PostgreSQLなどと並んで人気の高いシステムである。

引用:[e-Words IT用語辞典](http://e-words.jp/w/MySQL.html)

# とりあえず試してみよう

ConoHaで作成したサーバーにコンソールからログインして、以下のコマンドを打ち込んでください。

```
mysql -u root -p
```

`-u`はユーザー名を指定して、`-p`はパスワードを使ってログインするという意味になります。
パスワードはログインしたときに以下の場所に記載されています。
必要に応じてメモを残しましょう。

![mysql.png](https://qiita-image-store.s3.amazonaws.com/0/111110/02b7eb1f-66b6-6d2b-3fbe-95b84714a0f5.png)

ログインに成功すると以下のような画面になります。

```bash
[root@133-xxx-xx-xxx ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 67
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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`コマンドが無かったりする方はサーバーを作成するときのオプションが間違っていたのだと思うのですぐに作り直しましょう。

## サンプルデータを投入

簡単な映画情報データベースを作ります。

サンプルデータがあるのでまずはダウンロードします。
このときLinux操作とMySQLの操作を交互に行うことになります。
なのでターミナルをLinux用とMySQL用それぞれ立ち上げておきましょう。

### 1. Linux側でサンプルファイルのダウンロード

```
 # cd /tmp
 # wget http://163.44.164.151/filmsdb.txt
```

### 2. MySQL側でデータベースの作成

```
mysql> create database filmsdb;
```

### 3. Linux側でデータの一括投入

```
# cat filmsdb.txt | mysql -u root -p filmsdb
```

### 4. MySQL側でデータの確認

```
mysql> show databases;
mysql> use filmsdb;
mysql> show tables;
mysql> select * from films_title;
mysql> select * from films_category;
```

![image](https://qiita-image-store.s3.amazonaws.com/0/111110/a455e69d-9f1e-a30c-3248-fd9320e3829c.png)


### 5. MySQL側のログアウト方法

```
mysql> exit
```

# SQLを書いてみよう

## SQLとは？
 >リレーショナルデータベースの操作を行うための言語の一つ。

引用:[e-Words IT用語辞典](http://e-words.jp/w/SQL.html)

## 基本的なSQL文
### select

```mysql
select * from films_title;
```


### where

```mysql
select * from films_title where category_id = 3;
```

### count

```mysql
select count(*) as cnt from films_title where category_id = 3;
```

### sum

```mysq;
select sum(category_id) as total from films_title where category_id = 3;
```

### insert

```mysql
insert into films_title value (1, 'パシフィック・リム');
insert into films_title (title, category_id)  value ('パシフィック・リム', 1);
```

### update

```mysql
update films_title set category_id = 2 where title='パシフィック・リム';
```

### delete

```mysql
delete from films_title where title='パシフィック・リム';
```

### create table

```mysql
create table films(
	category varchar(64), 
	title varchar(64)
) engine=innodb;
```

他こんな型があります。

int, biging, float, double, varchar, text, blob, date, datetime, timestamp

## 他に知っておいた方がいい基本的なSQL文
### limit

```mysql
select * from films_title limit 2, 5;
order by
select * from films_title order by category_id desc;
```

asc と descがあります。

### group by

```mysql
select category_id, count(*) as cnt from films_title group by category_id;
```

### join

```mysql
select * from films_title
    left join films_category
    on films_title.category_id = films_category.id;
```

```mysql
select b.category, a.title from films_title a
    left join films_category b
    on a.category_id = b.id; 
```

### select からの insert

```mysql
insert into films
  select b.category, a.title
    from films_title a left join films_category b
    on a.category_id = b.id;
```

### sub query(副問い合わせ)

```mysql
SELECT category FROM films_category
  WHERE id in (SELECT category_id FROM films_title);
```

## クライアントソフトでラクラク操作
- [Navicat](http://jp.navicat.com/download/navicat-for-mysql)
- [MySQL Workbench](http://dev.mysql.com/downloads/workbench/)
 - これを使ってConoHa内のMySQLに接続するには以下の方法がおすすめ
  -  [MySQL WorkBenchで外部サーバへSSHを通して接続する方法](http://php-tips.com/2011/05/23/mysql-workbench-with-ssh-connection/)
- mysqladmin
- HeidiSQL

![navicat.png](https://qiita-image-store.s3.amazonaws.com/0/111110/4212c5dc-6672-a1c0-66d5-4303560702b6.png)


# GMOグループ会社一覧データベースを作成しよう

## データベースを作ろう

- GMOサイトの企業情報ページにある「主要グループ会社一覧」を参考にすること。

  [主要グループ会社一覧 - GMOインターネット株式会社](http://www.gmo.jp/company-profile/groupinfo/)

- 最低6件のデータは入れよう
- データベース名は「**gmodb**」テーブル名は「**gmo**」
- フィールドには会社名、資本金、設立年月日、URLを設定
- 資本金は数値として、設立年月は日付として正しく扱えるようにする
- サイトのURLはtext型を使う
- サンプル


| name                                 | shihonkin  | make       | url
|:------------------------------------:+:----------:+:----------:+:-----------------------
| GMOアドパートナーズ                  | 1301568500 | 1999-09-08 | http://www.gmo-ap.jp/  

## テキスト形式からのインポート

MySQLはCSV、TSV形式のファイルから直接インポートが可能です。
事前にテーブルを構築しておく必要があります。
でもSQLをちまちまデータと一緒に打つ必要がないのでとても便利です。

### MySQL側でテーブルの作成

```mysql
CREATE TABLE gmo (
  name varchar(30),  shihonkin bigint(20),  make date,  url text
) ENGINE=InnoDB;
```

### ファイルの読み込み

**ファイルはUTF-8に！**


こちらで準備したものもあります。
以下のようにすれば簡単に取り込めます。

```bash
$ cd /tmp
$ wget http://163.44.164.151/gmo.txt
```

### MySQL側でファイルを指定して一括して取り込み

```mysql
load data infile '/tmp/gmo.txt' into table gmo fields terminated by '\t';
```

SQLを直接記述したテキスト形式
テーブルの構築からデータのINSERTまでほぼ自動
1時限目の授業で使ったテキストファイル
http://163.44.164.151/filmsdb.txt

#続く・・・！

この文書は初回のGTBのデータベース基礎をもとにしています。
さらに基本を学びたい方は以下の資料をダウンロードして28ページ目から参照してください。

[データベース基礎](http://163.44.164.151/database_basic_2016.pptx)