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

目的

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

流れ

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

データベースとは？

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

引用：ORACLE基礎から始めるデータベース入門セミナー

RDB (Relational Database)型が主流

1件のデータを複数の項目(フィールド)の集合として表現し、データの集合をテーブルと呼ばれる表で表す方式。ID番号や名前などのキーとなるデータを利用して、データの結合や抽出を容易に行うことができる。中小規模のデータベースでは最も一般的な方法。データベースの操作にはSQLと呼ばれる言語を使うのが一般的。

引用:e-Words IT用語辞典

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

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

MySQLとは？

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

引用:e-Words IT用語辞典

とりあえず試してみよう

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

mysql -u root -p

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

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

[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;

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

mysql> exit

SQLを書いてみよう

SQLとは？

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

引用:e-Words IT用語辞典

基本的なSQL文

select

select * from films_title;

where

select * from films_title where category_id = 3;

count

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

sum

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

insert

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

update

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

delete

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

create table

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

他こんな型があります。

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

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

limit

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

asc と descがあります。

group by

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

join

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

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

select からの insert

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(副問い合わせ)

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

クライアントソフトでラクラク操作

• Navicat
• MySQL Workbench
  • これを使ってConoHa内のMySQLに接続するには以下の方法がおすすめ
  • MySQL WorkBenchで外部サーバへSSHを通して接続する方法
• mysqladmin
• HeidiSQL

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

データベースを作ろう

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

主要グループ会社一覧 - GMOインターネット株式会社

• 最低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側でテーブルの作成

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

ファイルの読み込み

ファイルはUTF-8に！

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

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

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ページ目から参照してください。

データベース基礎