目次
- 目次
- はじめに
- SQL（Structured Query Language）
- Macでのmysqlインストール
- 使い方
- SQLの結果を行と列を交換して、垂直表示する
- SQLとCSVファイルのデータ変換
- DAO（Data Access Object）
- 初心者向けのおすすめの資料
- MyEnigma Supporters
はじめに
最近のWebサービスでは、
大量のデータを保持したり、
データを利用しやすくするために、
データベースを使用します。
しかし、一般的に使用されるデータベースは、
Structured Query Language (SQL)という
少し特殊な言語で操作されるため、
プログラミング経験者でも少し取っ付きづらいと感じていました。
そこで今回は、Web初心者の自分が、
データベースとSQLについて調べてみたので、
基礎的な内容についてまとめておきたいと思います。
リレーショナルデータベース
データベースには、データの構造によって
いくつか種類があります。
現在、データベースで一番使用されているのは、
リレーショナルデータベース（Relational Database：RDB）というものです。
RDBは、Excelシートのように列と行からなる
二次元の表の形式でデータを管理するため、
理解しやすく、広く利用されています。
また、SQL（Structured Query Language）
という専用の言語を用いてデータを操作するのもRDBの特徴です。
加えて、RDBでは行単位でデータを読み書きします。
このRDBのデータベースソフトはいくつかありますが、
有名なものとしては、下記のソフトが上げられます。
今回の記事では、最も広く使用されているRDBである
MySQLを使用することを想定しています。
ちなみに、RDB以外のデータ構造のデータベースのことを
一般的にNoSQLと呼ばれます。
NoSQLの代表的なデータベースとしては、redisなどが上げられます。
データベースの用語について
データベースで良く使用する用語は下記にまとめておきます。
スプレッドシートで想像するとわかりやすいかと思うので、
スプレッドシートで例えて説明していこうと思います。
- データベース
データベースそのもののこと。
大抵は一つのアプリに一つのDBで運用します。
スプレッドシートにおいては、ファイルそのもののイメージです。
- テーブル
データベースにおける、それぞれの表のこと
スプレッドシートにおいては、各シートのイメージです。
- フィールド
テーブルにおける各種データの種類(名前)のこと
スプレッドシートにおいては、各データのヘッダ名のようなイメージです。
- レコード
テーブルにおける各種データのこと
各フィールドをひとまとめにしたデータで校正されます。
スプレッドシートにおいては、各データ行のようなイメージです。
SQL（Structured Query Language）
SQLは先ほど説明した通り、
RDBを操作するための言語です。
SQL文はその役割により3つに分類されます。
1. DDL（Data Definition Language）
DDLは、データを格納する入れ物である
データベースやテーブルなどを作成したり、
削除したりする時に使うコマンドです。
DDLに分類される命令は下記のようなものがあります。
CREATE ：データベースやテーブルなどを作成
DROP ：データベースやテーブルなどを削除
ALTER ：データベースやテーブルなどの構成を変更
2. DML（Data Manipulation Language）
DMLは、テーブルの行を検索したり、
変更したりする時に使う言語です。
DMLに分類される命令は下記のものがあります。
SELECT：テーブルから行を検索
INSERT：テーブルに新規行を登録
UPDATE：テーブルの行を更新
DELETE：テーブルの行を削除する
3. DCL（Data Control Language)
DCLは、データベースに対して行なった変更を確定したり、
取り消したりするコマンドです。
また、RDBのユーザがテーブルなどを操作する
権限の設定も実施するコマンドも含まれます。
DCLに分類される命令は下記のものがあります。
COMMIT ：データベースに対して行なった変更を確定
ROLLBACK ：データベースに対して行なった変更を取り消す
GRANT ：ユーザに操作の権限を与える
REVOKE ：ユーザから操作の権限を無くす
また、SQLは下記のような文法ルールがあります。
セミコロン（;）で一文を終える
キーワードの大文字・小文字は区別されない
データ名には、半角文字のアルファベット、数字、アンダーバー（_）のみを使える
データ名の最初は半角のアルファベットにする
データ名は重複してはいけない
Macでのmysqlインストール
MacでMySQLをインストール場合は、
下記のようにインストールします。
(homebrewを使っていることを想定しています。)
brew update brew install mysql
使い方
MySQLを使う時の基本的なコマンドや、
コマンドの説明を下記で説明していきます。
データベースの作成系
- mysqlの起動方法
mysql.server start
- rootでログイン
mysql -u root
- ユーザ作成と権限付加
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root';
mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'%' WITH GRANT OPTION;
- データベースを作成
goodsデータベースを作成します。
create database goods;
- 現在のデータベース一覧を表示
show databases;
- データベースを削除
testデータベースを削除します。
drop database test;
- データベースを変更
hogedbのデータベースに変更する場合です。
use hogedb;
- テーブルを追加する
下記のようにフィールドとその型、
そしてオプションを指定して作成します。
create table users( id int auto_increment primary key, name varchar(255) , email varchar(255) unique sex enum('M','F') default 'M' );
使用できる型は
| 型名 | 説明 |
|---|---|
| int | 整数型 |
| double | 浮動小数点型 |
| char | 固定の文字列 |
| varchar | 可変長の文字列 |
| text | 可変長の文字列 |
| date | 日付 |
| datetime | 時刻 |
| enum | 列挙型 |
が代表的なものになります。
charは固定長の文字列なので、
その長さよりも短い文字列を入れた場合、
残りの部分は半角スペースで埋められます。
また、varchar(varying(可変)) は可変長なので、
その長さよりも短い文字列を入れても、
残りの部分は埋められません。
オプションも下記のものが代表的なものです。
| オプション名 | 説明 |
|---|---|
| not null | 入力を必須にする |
| default hoge | デフォルト値を使用する |
| auto_increment | 自動連番 |
| unique | 重複はエラーにする |
| primary key | 主キーに設定(検索が早くなる) |
- テーブルの一覧を表示する
show tables;
- テーブルを削除する
table_nameテーブルを削除します。
drop table table_name
- テーブルの構造(フィールドなど)を見る
table_nameテーブルのデータ構造を確認します。
desc table_name
テーブルのフィールド定義を変更する
テーブルのフィールドの定義を変更する場合は、
alter文を使います。
- すでに作られたテーブルに列(新しいデータフィールド)を追加する場合
(tableNameテーブルに、varchar型のdataNameフィールドを追加する場合)
alter table tableName add column dataName varchar(100);
- フィールドの型を変更する場合
alter table tableName change dataName dataName varchar(10);
- すでに作られたテーブルの列を削除する場合
(tableNameテーブルから、dataNameフィールドを削除する)
alter table tableName drop dataName;
- テーブルの名前を変更する
alter table tableName rename tableName2;
データ取得系
- データを出力する
すべてのデータを出力する
select * from tableName;
指定したフィールドのみを取得する
select name1, name2 from tableName;
また、フィールド名が多い場合は
select * from tableName \G
とするとフィールドを縦に並べてくれて見やすくなります。
- フィールド名のリストを表示
あるテーブルのフィールド名は下記で取得できます。
show columns from table_name;
- フィールド名でテーブルを検索
あるフィールド名を持ったテーブルを検索したい時は、
下記のコマンドを使います。
select table_name, column_name from information_schema.columns where column_name = 'serarchColumnName' and table_schema = 'dbname;
フィールド名を変更してデータ取得
select '商品データ' as index, data id from shohin;
- 重複を削除してデータ取得
select distinct dataType from table;
- データを検索して出力する
dataIDフィールドが10のもののみ取得する場合
select dataID from table where dataID='10'
(where文はfrom文の直後に記述する)
where文で使用できる演算子は下記の表の通りです。
| 演算子 | 説明 |
|---|---|
| = | 等しい |
| <> | 等しくない |
| != | 等しくない(MySQLの場合はこれでもOK) |
| > | より大きい |
| >= | 以上 |
| < | より小さい |
| <= | 以下 |
| NOT | 論理値の否定 |
| AND | 論理積 |
| OR | 論理和 |
注意点として、あるフィールドの値がNULLであるものを
選択したい場合は、"= NULL"ではなく、
"is NULL"という文を使います。
同様に、NULLでないものは、
"is not NULL"で選択できます。
- where文であいまい検索(正規表現検索)
where文の中で、like文を使うとあいまい検索(正規表現検索)を実現できます
select * from table where email like %@gmail.com
select * from table where email like %@gmail.__
ちなみに%は任意の文字数の文字列
_は任意のいち文字の文字列になります。
- 範囲を指定して検索
between文を使うことで、
簡単に範囲指定して検索することもできます。
select * from table where score between 0.5 and 1.0
- 表示件数を制限する
limit文を使うと、表示するレコード数を制限したりできます。
select * from table limit 3
- データ表示の順番を指定する
order by文を使います
降順の場合(大 -> 小)
select data from table order by data desc;
昇順の場合(小 -> 大)
select data from table order by data asc;
並び順を指定しない場合は昇順になります。
- 乱数を使ってランダムにレコードを抽出する
rand()という関数とorder byを使うことで、
ランダムにレコードを抽出することができるようになります。
(ランダムに一つレコードを取得する場合)
select * from table order by rand() limit 1;
- 文字列用の便利関数
文字列の長さを求める
select length(data) from table
文字列を繋げる
select concat (data1, '-', data2) from table;
文字列の一部のみを抽出
select substring(data,1,1) from table;
- 便利な日時関数
現在の日時を取得
select now();
日時変数の月を取得する
select month(data) from table;
日付の日数差分を取得する
select datediff(now(), update) from table;
- select文を入れ子にする(サブクエリ)
select文で検索した結果を使って、
再びselect文で検索したい場合は、
サブクエリという手法を使います。
具体的にはWhere文の中に括弧で
select文を入れるだけです。
SELECT SalesOrderID FROM Sales.SalesOrderDetail WHERE ID = (SELECT ID FROM products WHERE Name = 'Chain')
イコールを使う場合は、
括弧の中のselect文の結果が、
一つの値にならないといけませんが
複数行のデータで検索をする場合は、inを使います。
SELECT SalesOrderID FROM Sales.SalesOrderDetail WHERE ID in (SELECT ID FROM products WHERE Name = 'Chain')
- 複数のテーブルからデータを同期して取得する
fromの後に複数のテーブルを指定し、
ドットで各テーブルのフィールドを指定することで、
複数のテーブルからデータを取得できます。
また、where文でidなどを同期させることで、
複数のテーブルのデータを同期させてデータを取得することができます。
select table1.data1 table2.data2 from table1, table2 where table1.id=table2.id
データ更新系
- レコードを入力する
insert into table_name (recordename1,recordname2) values (1,'hoge');
すべてのデータに対してデータを入力する際は、
フィールド名リストは省略することができます。
insert into table_name values (1,'hoge');
(tableに２つのフィールドしかない場合)
また、同じフィールドに複数のレコードを一回で挿入したい場合は、
カンマ区切りで挿入することができます。
insert into table_name (recordename1,recordname2) values (1,'hoge'), (2,'hoge1'), (3,'hoge2');
- 他のテーブルからデータを取得し、挿入する
insert ~ selectを使うことで他のテーブルのデータを、
別のテーブルにコピーすることができます。
insert info table1 (data1, data2) select data1, data2 from table2;
- レコードを削除する
すべてのレコードを削除する場合
delete from data;
- 条件指定してレコードを削除する
delete from data where score <5.0;
- レコードを更新する
すでに登録されたレコードを更新する場合は、
update文を使えばOKです。
update table set name="hogehoge" where id = 1;
(where文が無いとすべてのnameフィールドが変更されます)
データ集計系
- 行数を数える
select count(col1) from table;
ちなみにcount(*)はNULLを含む行数を出力し、
count("列名");は、NULLを除外する行数を出力します。
また重複を削除して、種類を取得したい場合は
select count (distinct col10) from table;
とします。
- 合計を求める
select sum(sales) from table;
ちなみにNULLは0として扱われ、合計値からは無視されます
- 平均値を求める
select avg(sales) from table;
- 最大値、最小値を求める
select max(sales) from table;
select min(sales) from table;
ちなみに、maxとmin関数は、
日付などにも使用できます。
(一番新しい/古い時刻のデータ抽出など)
- グループ毎にわけて集計する
各グループ毎のカウントを出せます。
select count(*) from table group by dataType;
また、where文で絞り込んでから、グループ毎の集計も可能です。
select count(*) from table where salenum < 10 group by dataType;
ちなみにgroup byを使った後のデータは、ソートされません。
また、 having文を使うことで、
分割したグループのさらなる絞り込みもできます。
SQLファイルを読み込む
すべてのデータベースの処理を
いちいちコマンドラインから実行していると大変なので、
定型化されたSQLコマンドをファイルとして保存しておいて、
そのファイルを読み込む形でデータベースの処理を実施することが出来ます。
まず、.sqlというファイルにSQLコマンドを記述します。
そして、下記の２つの方法でSQLファイルを読み込んで、
実行することが出来ます。
1. シェル上から読み込ませる
シェル上から、sqlファイルを読み込ませたい場合は、
下記のように、mysqlにログインするコマンドに加えて、
sqlファイルのパスを指定することで読み込ませることができます。
mysql -u root < /home/sample.sql
2. sourceコマンドを使用する
mysqlにログインした状態で、
下記のsourceコマンドで
sqlファイルのパスを指定することで
実行することができます。
source /home/sample.sql
データベースのバックアップと復元
データベースのバックアップは
mysqldumpのコマンドを使って、下記のように実施できます。
mysqldump -u root dbname > db.dump.sql
バックアップで作成されたファイルは
通常のSQLが記述されたファイルになります。
またバックアップされたファイルを読み込んで復元したい場合は、
先ほどの外部sqlファイルの読み込みと同じ方法で復元できます。
mysql -u root dbname < /home/db.dump.sql
dbnameはちゃんと既に作成されたDBにしないと、
ERROR 1046 (3D000) at line 28: No database selected
というエラーができるので注意です。
その他
- コメント
一行コメントの場合 (ハイフン２個)
-- コメント
(Mysqlでは--の後に半角スペースが必要)
複数行コメントの場合
(C++と同じスラッシュアスタリスクで囲う)
/ このSQLは データIDを検索するものです/
MySQL serverが立ち上がっているか確認
mysqladmin ping
SQLの問い合わせ結果をHTML形式で表示する
ログイン時に -H を付ける
mysql -u root -p -H
すると、その後はHTML形式で結果が得られる
SQLの結果を行と列を交換して、垂直表示する
下記のように末尾に\Gをつける
select * from table \G;
SQLファイルを読み込んで実行する時にコマンドを表示させる
-vを付けることで、コマンドを描画できる
mysql -u root -v dbname < hoge.sql
mysqlのコマンドヘルプを見る
mysql -?
mysqlのバージョンを確認
mysql --version
下記のように、ログインした後、
statusコマンドでも見れる
status
SQLとCSVファイルのデータ変換
DBデータを解析する際はやはりcsvにして、
MATLABやpythonで解析したいので、
CSVファイルへの出力は重要です。
CSVファイルにエクスポート
下記のコマンドでできます。
SELECT * FROM users INTO OUTFILE '/tmp/users.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
csvは区切りなので、FIELDS TERMINATED BY ','を指定します。
OPTIONALLY ENCLOSED BY '"'は文字列を囲う記号を指定できます。(省略可)
また、mysqldumpコマンドでも -T オプションでCSV形式の出力ができるようです。
また、csvファイルの一番最初の行に、フィールド名(カラム名)を入れたい場合は、
下記のようにselect文を重ねることで実現できます。
SELECT "data1","data2" union SELECT data1, data2 FROM users INTO OUTFILE '/tmp/users.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
最初のselectがヘッダの文字列になります。
CSVファイルからインポート
下記のコマンドで可能です。
LOAD DATA INFILE "c:/data.csv" INTO TABLE data FIELDS TERMINATED BY ',' ENCLOSED BY '"';
DAO（Data Access Object）
データベースにアクセスするための窓口となるオブジェクトのことを
DAO（Data Access Object）と呼びます
このようなオブジェクトを作るクラスは、
*DAOという名前で設計されることが多いようです。
初心者向けのおすすめの資料
ドットインストールのスクリーンキャスト
やはり、一番最初はスクリーンキャストを一通り見ると
データベースの役割や機能の概略を広く理解することができ、
その後の書籍での勉強も捗るかと思います。
ドットインストールはソフトウェア系技術を学ぶ上では、
素晴らしい最初の一歩だと思います。
『SQL ゼロからはじめるデータベース操作』
また上記の書籍はデータベース初心者に
おすすめの本としてよく紹介されている名著です。
データベースの基礎的な部分から、
各データベースに依存しない
基礎的なSQLを学ぶことができるので、
非常に参考になりました。
また実際に手を動かして
データベースの動きを勉強出来る所も良かったです。
『実践ハイパフォーマンスMySQL』
実際にMySQLを使って、
Webサービスなどを作りたい場合は、
より実践的な知識として上記の本をおすすめします。
これまでの資料ではあまり出てこなかった、
サービスとしての最適化や、
ベンチマークの方法など、
実際にMySQLを大規模に利用する時に
必要な知識を得ることができるはずです。
参考資料
データベースの作成(CREATE DATABASE文) - データベースの作成 - MySQLの使い方
MyEnigma Supporters
もしこの記事が参考になり、
ブログをサポートしたいと思われた方は、
こちらからよろしくお願いします。
