MySQL基本（続き）抽出

#はじめに
[前回](https://qiita.com/I_S-657/items/249c13316b43c1425cff)の続きです

前回はさらっと書いただけですが今回は少し踏み込んだ事を書きたいと思います。
なので実行結果をしっかり書き挙動を紹介できればと思います。

今回は前回も少し書いたのですが抽出関連をみていきたいと思います。
##環境
[VirtualBox](https://www.virtualbox.org/)
[Vagrant](https://www.vagrantup.com/)
[Cent0S7](https://www.centos.org/)
MySQL（Ver 8.0.19）



[こちらで構築の紹介をさせていただいてます参考にどうぞ](https://qiita.com/I_S-657/items/63528d09995138c9e46d)
[MySQL版](https://qiita.com/I_S-657/items/8e2eb94367c418000fd9)

##基本データ
投入データ

```sql:app.sql
drop table if exists db01;

create table db01 (
  id int unsigned primary key auto_increment,
  name varchar(20),
  age int unsigned,
  from_ja varchar(20)
);

insert into db01(name,age,from_ja)values('hoge',18,'東京');


insert into db01(name,age,from_ja)values
 ('fuga',27,'大阪'),
 ('piyo',38,'愛知');

drop table if exists db02;
create table db02 (
  id int unsigned,
  message varchar(255)
);

insert into db02(id,message)values(1,'ほげほげ');
insert into db02(id,message)values(2,'フガフガ');


select * from db01;
select * from db02;
```


データその１

```
mysql> desc db01;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)  | YES  |     | NULL    |                |
| age     | int unsigned | YES  |     | NULL    |                |
| from_ja | varchar(20)  | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
```

データその２

```
mysql> desc db02;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| 　id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| message | varchar(255) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
```

```
mysql> select *from db01;
+----+------+------+---------+
| id | name | age  | from_ja |
+----+------+------+---------+
|  1 | hoge |   18 | 東京    |
|  2 | fuga |   27 | 大阪    |
|  3 | piyo |   38 | 愛知    |
+----+------+------+---------+

```
データその３

```
mysql> select * from db02;
+-------+--------------+
| 　id  | message      |
+-------+--------------+
|     1 | ほげほげ       |
|     2 | フガフガ       |
+-------+--------------+
```

##SELECT
データを指定して表示させる

```
mysql> select [カラム名] from [テーブル名];
```

```sql:select.sql
-- 全てのカラム--
select * from db01;

-- カラムをしていして--
select name,age from db01;

-- 条件つきで--

select * from db01 where age >= 20;

select * from db01 where age between 20 and 30;

select * from db01 where  name in('hogera','fuga');

select * from db01 limit 2;

-- 並び替え
select * from db01 order by age;

select * from db01 order by age desc;
```

実行結果

```
mysql> \. ./select.sql
+-------+------+------+---------+
| 　id  | name | age  | from_ja |
+-------+------+------+---------+
|     1 | hoge |   18 | 東京    |
|     2 | fuga |   27 | 大阪    |
|     3 | piyo |   38 | 愛知    |
+-------+------+------+---------+
3 rows in set (0.00 sec)

+------+------+
| name | age  |
+------+------+
| hoge |   18 |
| fuga |   27 |
| piyo |   38 |
+------+------+
3 rows in set (0.00 sec)

+-------+------+------+---------+
| 　id  | name | age  | from_ja |
+-------+------+------+---------+
|     2 | fuga |   27 | 大阪    |
|     3 | piyo |   38 | 愛知    |
+-------+------+------+---------+
2 rows in set (0.00 sec)

+-------+------+------+---------+
| 　id  | name | age  | from_ja |
+-------+------+------+---------+
|     2 | fuga |   27 | 大阪    |
+-------+------+------+---------+
1 row in set (0.00 sec)

+-------+------+------+---------+
| 　id  | name | age  | from_ja |
+-------+------+------+---------+
|     2 | fuga |   27 | 大阪    |
+-------+------+------+---------+
1 row in set (0.00 sec)

+-------+------+------+---------+
| 　id  | name | age  | from_ja |
+-------+------+------+---------+
|     1 | hoge |   18 | 東京    |
|     2 | fuga |   27 | 大阪    |
+-------+------+------+---------+
2 rows in set (0.00 sec)

+-------+------+------+---------+
| 　id  | name | age  | from_ja |
+-------+------+------+---------+
|     1 | hoge |   18 | 東京    |
|     2 | fuga |   27 | 大阪    |
|     3 | piyo |   38 | 愛知    |
+-------+------+------+---------+
3 rows in set (0.00 sec)

+-------+------+------+---------+
| 　id  | name | age  | from_ja |
+-------+------+------+---------+
|     3 | piyo |   38 | 愛知    |
|     2 | fuga |   27 | 大阪    |
|     1 | hoge |   18 | 東京    |
+-------+------+------+---------+
3 rows in set (0.01 sec)

```

##LIKE
文字列検索

```sql:like.sql
-- ワイルドカード
select * from db01 where name like 'h%';

select * from db01 where name like '%g%';

select * from db01 where name like '%o';

-- 文字数指定
select * from db01 where name like '_o%';
```
実行結果

```
mysql> \. ./like.sql
+-------+------+------+---------+
| 　id  | name | age  | from_ja |
+-------+------+------+---------+
|     1 | hoge |   18 | 東京    |
+-------+------+------+---------+
1 row in set (0.00 sec)

+-------+------+------+---------+
| 　id  | name | age  | from_ja |
+-------+------+------+---------+
|     1 | hoge |   18 | 東京    |
|     2 | fuga |   27 | 大阪    |
+-------+------+------+---------+
2 rows in set (0.00 sec)

+-------+------+------+---------+
| 　id  | name | age  | from_ja |
+-------+------+------+---------+
|     3 | piyo |   38 | 愛知    |
+-------+------+------+---------+
1 row in set (0.00 sec)

+-------+------+------+---------+
| 　id  | name | age  | from_ja |
+-------+------+------+---------+
|     1 | hoge |   18 | 東京    |
+-------+------+------+---------+
1 row in set (0.01 sec)
```

##VIEW 


```
create view [テーブル名] as select [抽出条件]
```

```sql

drop view if exists view_tabele;

create view view_tabele as select * from db01 order by age desc;

select * from view_tabele;
```

##内部結合
2つのテーブルで一致したデータ行のみ取得します。



```
mysql> select db01.id, db01.name, db01.age, db01.from_ja, db02.message from db01 join db02 on db01.id = db02.id;
+----+------+------+---------+--------------+
| id | name | age  | from_ja | message      |
+----+------+------+---------+--------------+
|  1 | hoge |   18 | 東京    | ほげほげ     |
|  2 | fuga |   27 | 大阪    | フガフガ     |
+----+------+------+---------+--------------+
```

##外部結合
2つのテーブルで一致しないデータも含めてどちらか一方のテーブルのデータを全て取得します。
どちらを主にするかでかわってくるので注意が必要

```
mysql> select db01.id, db01.name, db01.age, db01.from_ja ,db02.message from db01 right join db02 on db01.id = db02.id;
+------+------+------+---------+--------------+
| id   | name | age  | from_ja | message      |
+------+------+------+---------+--------------+
|    1 | hoge |   18 | 東京    | ほげほげ     |
|    2 | fuga |   27 | 大阪    | フガフガ     |
+------+------+------+---------+--------------+
2 rows in set (0.00 sec)
```

#まとめ
いかがだったでしょうか抽出は基本ではありますが条件を指定したり結合したりと奥が深いですね。
次は制約やサブクエリなどについて書きたいところです。