データベース基礎 2016年3月10日 JWord株式会社 サービス開発部 中川　陽平

この科目の目的 MySQLによるデータベースの 基本操作が一通りできるようになること。 ウェブアプリケーションを開発するのに必要最低限のスキルを身につけること。

1時限目 データベースとは？ 2時限目 SQLを書いてみよう 3時限目 GMOグループデータベースを作ろう 4時限目 もっと大きなデータを扱ってみよう

1時間目：データベースとは？

データベースとは？ （1） データを整理・統合して格納し、 （2） そのデータを検索・活用しやすくした （コンピュータ上に構築された）仕組み 　　　（コンピュータ上に構築された）仕組み 引用：ORACLE基礎から始めるデータベース入門セミナー http://www.oracle.com/technetwork/jp/articles/index-313275-ja.html

RDB (Relational Database)型が主流 1件のデータを複数の項目(フィールド)の集合として表現し、データの集合をテーブルと呼ばれる表で表す方式。ID番号や名前などのキーとなるデータを利用して、データの結合や抽出を容易に行うことができる。中小規模のデータベースでは最も一般的な方法。データベースの操作にはSQLと呼ばれる言語を使うのが一般的。 引用: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と打ち込んでください。

サンプルデータを投入 簡単な映画情報データベースを作ります mysql> create database filmsdb; mysql> exit # cd /tmp # wget http://163.44.164.151/filmsdb.txt # cat filmsdb.txt | mysql filmsdb # mysql filmsdb mysql> show tables; mysql> select * from films_title; mysql> select * from films_category;

2時間目： SQLを書いてみよう

SQLとは？ リレーショナルデータベースの操作を行うための言語の一つ。 引用:e-Words IT用語辞典 http://e-words.jp/w/SQL.html

基本的な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 時間が残ったらもっと説明します！

limit order by group by 他に知っておいた方がいい基本的なSQL文 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 sub query(副問い合わせ) 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 mysqladmin MySQL Workbench HeidiSQL

3時間目：GMOグループ会社一覧データベースを作成しよう

データベースを作ろう 最低6件のデータは入れよう データベース名は「gmodb」テーブル名は「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/ |

テキスト形式からのインポート CREATE TABLE gmo ( name varchar(30), CSV、TSV形式のファイルから直接インポートが可能 事前にテーブルを構築しておく必要がある CREATE TABLE gmo ( 　　name varchar(30), shihonkin bigint(20), make date, url text ) ENGINE=InnoDB; wget http://163.44.164.151/gmo.txt load data infile '/tmp/gmo.txt' into table gmo fields terminated by '\t';

テキスト形式からのインポート CSV、TSV形式のファイルから直接インポートが可能 事前にテーブルを構築しておく必要がある CREATE TABLE gmo ( 　　name varchar(30), shihonkin bigint(20), make date, url text ) ENGINE=InnoDB; wget http://163.44.164.151/gmo.txt load data infile '/tmp/gmo.txt' into table gmo fields terminated by '\t'; SQLを直接記述したテキスト形式 テーブルの構築からデータのINSERTまでほぼ自動 1時限目の授業で使ったテキストファイル http://163.44.164.151/filmsdb.txt

データのエクスポート mysqldump コマンド mysqldump -uroot gmodb SQL形式での出力 SELECTの結果をリダイレクト出力 echo "select * from gmo" | mysql -uroot gmodb > gmo_bk.txt タブ区切りでの出力 データ内に改行やタブそのものが含まれていると大変

テーブルを変更したくなったら？ ALTER TABLE ALTER TABLE gmo ADD id int FIRST; alter table gmo modify column id int auto_increment; alter table gmo add key(id);

4時間目： もっと大きなデータを扱ってみよう

大きめのデータの準備 mysql> create database appsdb; テーブルを作成 # cd /tmp # wget http://163.44.164.151/apps_table.txt # cat apps_table.txt | mysql -u root appsdb

# wget http://163.44.164.151/apps.txt.gz # gunzip apps.txt.gz データのダウンロードからインポート # cd /tmp # wget http://163.44.164.151/apps.txt.gz # gunzip apps.txt.gz mysql > use appsdb; mysql > load data infile '/tmp/apps.txt' into table apps fields terminated by '\t'; Query OK, 84273 rows affected, 1 warning (7.76 sec) Records: 84273 Deleted: 0 Skipped: 0 Warnings: 1

show warnings mysql> show warnings; +---------+------+-----------------------------------------------------------------------------------------+ | Level | Code | Message | | Warning | 1366 | Incorrect integer value: 'test' for column 'userRatingCountForCurrentVersion' at row 34 | 1 row in set (0.00 sec)

show processlist と kill mysql> show processlist; +-----+------+-----------------+--------+---------+------+-------+----------------------------------------------------------------------------+-----------+---------------+-----------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read | | 265 | root | 127.0.0.1:42855 | NULL | Sleep | 3930 | | NULL | 6 | 6 | 6 | | 266 | root | 127.0.0.1:42857 | gmodb | Sleep | 3566 | | NULL | 0 | 0 | 0 | | 277 | root | localhost | appsdb | Query | 6 | NULL | load data infile '/tmp/apps.txt' into table apps fields terminated by '\t' | 0 | 0 | 0 | | 278 | root | localhost | appsdb | Query | 0 | NULL | show processlist | 0 | 0 | 0 | 4 rows in set (0.00 sec) mysql> kill 277;

とりあえずSELECT mysql> select artistName, trackName from apps where artistName = 'JWord Inc.' limit 10; +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | artistName | trackName | | JWord Inc. | 買い物ポケットbyGMO ～通販サイトの最安値を一括検索。価格比較やバーコード検索、ランキングでお買い物を応援する無料ショッピングアプリ～ | | JWord Inc. | Find Magazine byGMO | | JWord Inc. | 黄金爆走!デコトラトーナメント | | JWord Inc. | 知らせてオッシーbyGMO - 天気予報、気象警報、電車遅延、スケジュールなどをプッシュ通知でお届け！ | | JWord Inc. | 値下げアプリbyGMO | | JWord Inc. | ホンジラスbyGMO - 買い逃したくない本の予約・発売日をプッシュ通知で リマインド！ | | JWord Inc. | Aradhana byGMO | | JWord Inc. | Secret Room of Maiden byGMO | 8 rows in set (2.32 sec)

index 本の索引と一緒の考え方 辞書に索引がなかったら？ indexは大切です。

SQL_NO_CACHE 同じSELECTは2回目以降は速く油断しがち SQL_NO_CACHE を使ってみよう mysql> select SQL_NO_CACHE trackName from apps where artistName = 'JWord Inc.';

indexを"張る" mysql> alter table apps add index idx_artist(artistName(10)); mysql> select SQL_NO_CACHE trackName from apps where artistName = 'JWord Inc.';

indexを"張る" mysql> alter table apps add index idx_artist(artistName(10)); mysql> select SQL_NO_CACHE trackName from apps where artistName = 'JWord Inc.'; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | trackName | | 買い物ポケットbyGMO ～通販サイトの最安値を一括検索。価格比較やバーコード検索、ランキングでお買い物を応援する無料ショッピングアプリ～ | | Find Magazine byGMO | | 黄金爆走!デコトラトーナメント | | 知らせてオッシーbyGMO - 天気予報、気象警報、電車遅延、スケジュールなどをプッシュ通知でお届け！ | | 値下げアプリbyGMO | | ホンジラスbyGMO - 買い逃したくない本の予約・発売日をプッシュ通知で リマインド！ | | Aradhana byGMO | | Secret Room of Maiden byGMO | 8 rows in set (0.01 sec) mysql> show create table apps; mysql> alter table apps drop index idx_artist;

likeによる検索 mysql> select SQL_NO_CACHE trackName from apps 　　　　where artistName like 'JWord%'; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | trackName | | 買い物ポケットbyGMO ～通販サイトの最安値を一括検索。価格比較やバーコード検索、ランキングでお買い物を応援する無料ショッピングアプリ～ | | Find Magazine byGMO | | 黄金爆走!デコトラトーナメント | | 知らせてオッシーbyGMO - 天気予報、気象警報、電車遅延、スケジュールなどをプッシュ通知でお届け！ | | 値下げアプリbyGMO | | ホンジラスbyGMO - 買い逃したくない本の予約・発売日をプッシュ通知で リマインド！ | | Aradhana byGMO | | Secret Room of Maiden byGMO | 8 rows in set (0.02 sec) 　　　　where artistName like '%JWord'; Empty set (2.87 sec)

explain SELECTが効率よく動作しているか確認するためのコマンド 男のコンピューター道 MySQLのEXPLAINを徹底解説!! http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html index フルインデックススキャン。 インデックス全体をスキャンする必要があるのでとても遅い。 ALL フルテーブルスキャン。 インデックスがまったく利用されていないことを示す。 OLTP系の処理では改善必須。 slidshare MySQL INDEX+EXPLAIN入門 http://www.slideshare.net/infinite_loop/mysql-indexexplain

explain mysql> explain select SQL_NO_CACHE trackName from apps where artistName like 'JWord%'; +----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | 1 | SIMPLE | apps | range | idx_artist | idx_artist | 32 | NULL | 8 | Using where | 1 row in set (0.09 sec)

explain mysql> explain select SQL_NO_CACHE trackName from apps where artistName like 'JWord%'; +----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | 1 | SIMPLE | apps | range | idx_artist | idx_artist | 32 | NULL | 8 | Using where | 1 row in set (0.09 sec) mysql> explain select SQL_NO_CACHE trackName from apps where artistName like '%JWord'; +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | 1 | SIMPLE | apps | ALL | NULL | NULL | NULL | NULL | 74468 | Using where | 1 row in set (0.00 sec)

全文検索(FULLTEXT INDEX) MyISAMエンジンにテーブルを変更 mysql> alter table apps engine=myisam; mysql> alter table apps add fulltext(artistName); mysql> select artistName from apps where match(artistname) against('jword'); mysql> alter table apps add fulltext(description); mysql> select trackName from apps where match(description) against('パズルゲーム'); 詳細について MySQLで全文検索 - FULLTEXTインデックスの基礎知識 http://www.tatamilab.jp/rnd/archives/000389.html

全文検索の日本語対応 sennaとMoroonga senna は MySQL5.1用専用 Moroonga は MySQL5.6以降 日本語の文章を形態素解析を行って単語単位で検索しやすくしてくれるものです。 slideshare MySQLを通じた全文検索エンジンSenna/groongaの利用について http://www.slideshare.net/TasukuSuenaga/mysqlsennagroonga-6139206

MyISAMとInnoDB MyISAMエンジンにテーブルを変更 InnoDBは更新系に強いく壊れにくい しかし現在InnoDBも進歩を重ねてMySQLでの標準的なエンジンはInnoDBとなっている。 しかし全文検索はMySQL5.5までMyISAMしか使えなかった。 MySQL5.6から使えるようになっているのでInnoDBを選択しない理由が無くなってきている。 既に運用されているMySQLのバージョンなどを確認して create table 時に engine を何にするかを判断するようにしましょう。 この手の話は流れが速いのでググった時など更新日付の新しいものを参考にするようにしましょう。

今日はここまで。

長時間お疲れ様でした！ データベース基礎