mysqlの練習　備忘録

# mysqlの練習用の備忘録です。
やりたいことはvagrantのローカル環境にてmysqlの使い方を忘れないように
練習用の備忘録です。
※ドットインストールさんの動画を参考にしています。
動画見ながらだと時間がかかるのでテキストベースに落とし込んでおこうと思いました。

## 主な内容
1. DBの作成、削除
2. テーブルの作成、削除
3. データの挿入、編集、削除
4. カラムの挿入、編集、削除
5. データの検索、応用検索
6. データの結合検索

※基本的にmysqlが導入されている状態です。

## 練習内容


◆作業用ユーザーを作成、rootユーザーでログインする。

```lang:
mysql -u root -p
```
◆rootユーザーからDBを作成する。

```lang:
create database blog_app;
```

◆作業用ユーザーの作成

```lang:
grant all on blog_app.* to dbuser@localhost identified by '123456';
```

◆確認用

```lang:
exit;
```

```lang:
mysql -u dbuser -p blog_app
```

◆動作確認　テーブルの作成

```lang:
create table users (
id int,
name varchar(255),
email varchar(255),
password char(32)
);
```

◆テーブルを確認

```lang:
show tables;
```

◆テーブルを削除

```lang:
drop table users;
※念のためテーブルを確認します。
```

◆テーブルを作成

```lang:
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)
);
```

◆データを挿入

```lang:
insert into users (name,email,password,score,memo,created) values ('taiti','taiti@stone-rise.com','123456','5','test','2015-04-20 11:00:00');
```

◆データ確認

```lang:
select * from users;
```

◆ここまでのテストでのtableは一旦削除

```lang:
drop table users;
```

◆新しくtableを作成

```lang:
create table users (
id int not null primary key auto_increment,
name varchar(255),
email varchar(255),
team enum('blue','red','yellow'),
score double,
created datetime
);
```

◆テスト用のデータ作成

```lang:
insert into users (name,email,team,score,created) values 
('mikami','mikami@stone-rise.com','blue',5.5,'2012-05-11 11:00:00'),
('fkoji','fkoji@stone-rise.com','yellow',8.2,'2012-06-21 12:00:00'),
('domino','domino@stone-rise.jp','red',2.3,'2012-06-21 13:00:00'),
('sasaki','sasaki@stone-rise.com','blue',4.5,'2012-06-25 14:00:00'),
('kimura','','yellow',7.4,'2012-06-28 15:00:00'),
('nakamura','nakamura@stone-rise.jp','blue',4.2,'2012-06-29 16:00:00');
```

◆確認用
◆◆すべて表示させる

```lang:
select * from users;
```

◆◆!=の後以外を表示させる

```lang:
select * from users where score != 5.5;

◆◆<>の後以外を表示させる

```lang:
select * from users where score <> 5.5;
```

◆◆あいまい検索

```lang:
select * from users where email like '%@stone-rise.com';
```

◆◆あいまい検索 _これで文字数を指定している　

```lang:
select * from users where email like '%@stone-rise.__';
```

◆◆範囲の指定　between

```lang:
select * from users where score between 5.0 and 8.0;
```

◆◆範囲の指定　in（予め設定した範囲内にあるか）

```lang:
select * from users where team in ('red', 'yellow');
```

◆◆応用

```lang:
select * from users where score >= 4.0 and team = 'blue';
select * from users where score >= 4.0 or team = 'blue';
```

◆◆並び替え スコア順に並び替える

```lang:
select * from users order by score;
```

◆◆並び替え スコア順に並び替える　逆に表示させる

```lang:
select * from users order by score desc;
select * from users order by name desc;
```

◆◆並び替え　件数の指定

```lang:
select * from users limit 3;
select * from users limit 2, 2;
```

◆◆並び替え　スコアの大きい順　上から3つ

```lang:
select * from users order by score desc limit 3;
```

◆データの集計、データがいくつあるか確認

```lang:
select count(*) from users;
```

◆カラムにあるデータの確認

```lang:
select distinct team from users;
```

◆カラムのデータの最大値の確認

```lang:
select max(score) from users;
```

◆カラムのデータの平均値を求める

```lang:
select avg(score) from users;
```

◆カラムのデータの合計値を求める

```lang:
select sum(score) from users;
```

◆グループごとの合計値を確認する

```lang:
select sum(score) from users group by team;
```

◆ランダムでデータを取得できる

```lang:
select rand();
select * from users order by rand() limit 1;
```

◆文字列や日付から検索
-文字列の数を検索する

```lang:
select email, length(email) from users;
```

-連結の関数にラベルをつけて検索する。

```lang:
select concat(name, '(', team, ')') as label from users;
```

-文字列の検索

```lang:
select name, substring(team, 1, 1) from users;
```

-現在時刻の取得

```lang:
select now();
```

-作成月の取得

```lang:
select name, month(created) from users;
```

-日付の差分を取る

```lang:
select name, datediff(now(), created) from users;
```

◆データの更新と修正
-データの更新

```lang:
update users set email = 'kimura@stone-rise.jp' where id = 5;
select * from users;
```

-削除（scoreのなになに以下）

```lang:
delete from users where score <= 3.0;
select * from users;
```

◆テーブルの構造を変更してみる
-構造確認

```lang:
desc users;
```

-カラムを追加する。

```lang:
alter table users add full_name varchar(255) after name;
desc users;
```

-カラムの構造を変更する。（varcharを変更するなど）

```lang:
alter table users change full_name full_name varchar(100);
desc users;
```

-カラムを削除する。

```lang:
alter table users drop full_name;
desc users;
```

-キーをつける。

```lang:
alter table users add index email (email);
desc users;
```

-キーを削除する。

```lang:
alter table users drop index email;
desc users;
```

-table名を変更する。

```lang:
alter table users rename blog_users;
show tables;
```

◆結合検索

-データ準備

```lang:
create table users (
  id int not null primary key auto_increment,
  name varchar(255),
  email varchar(255),
  team enum('blue','red','yellow'),
  score double,
  created datetime
);
```
 
```lang:
insert into users (name,email,team,score,created) values 
('taguchi','taguchi@stone-rise.com','blue',5.5,'2012-05-11 11:00:00'),
('fkoji','fkoji@stone-rise.com','yellow',8.2,'2012-06-21 12:00:00'),
('nakamura','nakamura@stone-rise.com','red',2.3,'2012-06-21 13:00:00');
```

```lang:
create table posts (
  id int not null primary key auto_increment,
  user_id int not null,
  title varchar(255),
  body text,
  created datetime
);
```

```lang: 
insert into posts (user_id,title,body,created) values 
(1, 'title-1 by taguchi', 'body-1','2012-05-11 14:00:00'),
(1, 'title-2 by taguchi', 'body-2','2012-05-11 12:00:00'),
(2, 'title-3 by fkoji', 'body-3','2012-05-11 13:00:00'),
(3, 'title-4 by nakamura', 'body-4','2012-05-11 10:00:00'),
(3, 'title-5 by nakamura', 'body-5','2012-05-11 09:00:00');
```

◆データ確認

```lang:
select * from users;
```

◆◆複数のテーブルをまたがって検索　タイトルとユーザー名を表示させる
selectの後に表示させるカラムを入れる、from users,postsでテーブルを入れる、whereで条件指定します。
紐付けたいカラムを指定します。

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