[Server & Network General] MySQL: ドットインストール「MySQL入門」のまとめ

memo.

やっぱり忘れてしまうので、長いけれどもまとめておく。

#01 MySQLとは何か?

#02 データベース用語について

スプレッドシートでたとえると。
* データベース(ファイル)
* テーブル(シート)
* フィールド(列)
* レコード(行)

#03 データベースを操作してみよう

% mysql -u root
/* パスワードを設定する */
> set password for root@localhost=password('hogehoge');
> exit;
% mysql -u root -p
/* データベースを作成する */
> create database blog_app;
/* データベースの一覧を表示する */
> show databases;
/* データベースを削除する */
> drop database blog_app;
/* 利用するデータベースを指定する */
> use blog_app;

#04 作業ユーザーを設定しよう

/* 作業ユーザーを作成する */
> grant all on blog_app.* to dbuser@localhost identified by 'dbuserpass';
> exit;
/* 作業ユーザーでログインする */
% mysql -u dbuser -p blog_app;

#05 テーブルを操作してみよう

/* テーブルを作成する */
create table users (
  id int,
  name varchar(255),
  email varchar(255),
  password char(32)
);
/* テーブルの一覧を表示する */
> show tables;
/* テーブルを削除する */
> drop table users;

### #06 扱えるデータ型について
```sql
create table users (
  id int,
  name varchar(255),
  email varchar(255),
  password char(32),
  score double,
  sex enum('male', 'female'),
  memo text,
  created datetime
);

* 数値
* int 整数
* double 小数点
* 文字列
* char 固定長(長さの決まっている)
* varchar 可変長
* text
* 日付
* date
* datetime
* それ以外
* enum 列挙型(あらかじめ入力値が決まっている)
> * [MySQL :: MySQL 5.1 リファレンスマニュアル :: 10 データタイプ](http://dev.mysql.com/doc/refman/5.1/ja/data-types.html)
### #07 フィールドの高度な設定について

create table users (
  id int not null auto_increment primary key,
  name varchar(255),
  email varchar(255) unique,
  password char(32),
  score double,
  sex enum('male', 'female') default 'male',
  memo text,
  created datetime,
  key score (score)
);

* not null -- 入力を必須とする
* default 'male' -- ディフォルト値
* auto_increment -- 自動連番
* 索引(インデックス)
検索時に速くなる。挿入時にはインデックスを張り直すので遅くなる。バランス。
* primary key -- 主キー(1つ。テーブル内でレコードを一意に特定する。)
* key score (score) -- キー(いくつでも。索引キー。よく検索するものに。)
* unique -- ユニークキー(テーブル内で重複値が入ってきたらエラーではじく。)
### #08 レコードを挿入してみよう

/* テーブルの構造を表示する */
> desc users;

```sql
insert into users (name,email,password,score,memo,created) values ('suzuki','suzuki@exsample.com','password','4.6','memommmm.','2012-06-12 11:00:00');

### #09 レコードを抽出してみよう
```sql
/* テーブル(users)から、すべて(*)のフィールドを抽出する */
select * from users;
select name, email from users;
/* フィールドを縦に並べて表示するオプション */
select * from users \G

### #10 条件付きで抽出してみよう (1)

/* 条件付きでレコードを抽出する */
select * from users where score >= 5.0;
select * from users where team != 'red';
select * from users where created > '2012-06-01 00:00:00';
/* あいまいな条件で抽出する */
select * from users where email like '%@example.com';
select * from users where email like '%@example.__'; -- この場合は2文字のみ(ex. jp)

### #11 条件付きで抽出してみよう (2)

/* 範囲を指定して抽出する */
select * from users where score between 5.0 and 8.0;
select * from users where team in ('red', 'yellow');
/* 条件を組み合わせる */
select * from users where score >= 4.0 and team = 'blue';

### #12 並び替え、件数の制限について

/* 並び替え */
select * from users order by score; -- 昇順
select * from users order by score desc; -- 降順
/* 件数の制限 */
select * from users limit 3;
select * from users limit 3, 2; -- 開始位置 3 から 2 件

### #13 データの集計をしてみよう

/* 件数のカウント */
select count(*) from users;
/* 重複のないフィールドに含まれる値 */
select distinct team from users;
/* 最大値(max), min, avg, sum */
select max(score) from users;
/* group by グループ毎の集計 */
select team, avg(score) from users group by team;
/* 乱数を発生 */
select rand();
select * from users order by rand() limit 1;

### #14 文字列、日付関数を使ってみよう

/* 文字列の長さを求める */
select email,length(email) from users;
/* 文字列を結合する */
select concat(name,'(',team,')') from users;
/* 結果表示時の命令に別名を付ける
concat(name,'(',team,')') を label とスッキリ表示
*/
select concat(name,'(',team,')') as label from users;
/* 文字列の一部のみを表示
team の 1文字目から 1文字目 */
select name,substring(team,1,1) from users;
/* 日付関数
2012-06-27 19:30:00 */
select now();
/* create された月を表示する */
select name,month(created) from users;
/* 日付の差分を日数で求める */
select name, datediff(now(), created) from users;

> * [MySQL :: MySQL 5.1 リファレンスマニュアル :: 11 関数と演算子](https://dev.mysql.com/doc/refman/5.1/ja/functions.html)
### #15 レコードの更新、削除をしてみよう

/* レコードの更新
update テーブル名 set フィールド名 where 条件; */
update users set email = 'kimura@dotinstall.jp' where id = 5;
/* レコードの削除
delete from テーブル名 where 条件;
 */
delete from users where score<= 3.0;

> * [MySQLで複数行を一括でUPDATEする - Qiita [キータ]](http://qiita.com/masuidrive/items/0671ea7efa91a99c0268)
### #16 テーブルの構造を変更してみよう

/* フィールドを追加する
alter table テーブル名 add 追加するフィールド名 型 after fooの後に;
*/
alter table users add full_name varchar(255) after name;
/* 変更する */
alter table users change full_name full_name varchar(100);
/* 削除する */
alter table users drop full_name;
/* インデックスを付ける
alter table テーブル名 add index 付ける名前 (フィールド名);
 */
alter table users add index email (email);
alter table users drop index email;
/* テーブル名を変更する */
alter table users rename blog_users;

### #17 複数のテーブルを扱ってみよう

select users.name,posts.title from users,posts where users.id=posts.user_id;
select users.name,posts.title,posts.created from users,posts where users.id=posts.user_id order by posts.created desc;

> * [外部結合(LEFT JOIN句, RIGHT JOIN句) - データの取得 - MySQLの使い方](http://www.dbonline.jp/mysql/select/index15.html)
### #18 外部ファイルのコマンドを実行する

commands.sql
drop table if exists users; -- 上書き
create table users (
  name varchar(255),
  email varchar(255)
);
insert into users (name,email) values ('tom','tom@example.com');

% mysql -u dbuser -p blog_app < commands.sql

### #19 バックアップ、復元をしてみよう

/* バックアップ /
% mysqldump -u dbuser -p blog_app > blog_app.dump.sql
/
復元 */
% mysql -u dbuser -p blog_app < blog_app.dump.sql
“`