MySQLの基本(データベースの操作)3

前回の続きです。

データの集計

前回使用したusers_with_teamを用意

create table users (
  id int unsigned primary key auto_increment,
  name varchar(20),
  score float
);

insert into users (name, score) values ('taguchi', 5.8);
insert into users (name, score) values ('fkoji', 8.2);
insert into users (name, score) values ('dotinstall', 6.1);
insert into users (name, score) values ('Tanaka', 4.2);
insert into users (name, score) values ('yamada', null);
insert into users (name, score) values ('tashiro', 7.9);

drop table if exists users_with_team;
create table users_with_team as
select
  id,
  name,
  score,
  case
    when score > 8.0 then 'Team-A'
    when score > 6.0 then 'Team-B'
    else 'Team-C'
  end as team
from users;

-----------------------------------------------------------------------------------

○　NULL以外のレコード数を抽出
select count(score) from users_with_team;

+--------------+
| count(score) |
+--------------+
|            5 |
+--------------+

○　NULL以外のレコード数を抽出
select count(id) from users_with_team;

+-----------+
| count(id) |
+-----------+
|         6 |
+-----------+

○　NULLも含めたレコード数を抽出
select count(*) from users_with_team;

+----------+
| count(*) |
+----------+
|        6 |
+----------+

○　指定したカラムの合計値を取得
select sum(score) from users_with_team;

+-------------------+
| sum(score)        |
+-------------------+
| 32.19999980926514 |
+-------------------+

select min(score) from users_with_team;

+-------------------+
| min(score)        |
+-------------------+
| 4.199999809265137 |
+-------------------+

○　指定したカラムの最大値を取得
select max(score) from users_with_team;

+-------------------+
| max(score)        |
+-------------------+
| 8.199999809265137 |
+-------------------+

○　指定したカラムの平均値を取得
select avg(score) from users_with_team;

+-------------------+
| avg(score)        |
+-------------------+
| 6.439999961853028 |
+-------------------+

○　指定したカラムで重複を削除し表示
select distinct team from users_with_team;  

+--------+
| team   |
+--------+
| Team-C |
| Team-A |
| Team-B |
+--------+

○　指定したカラムで重複を削除しレコード数を表示
select count(distinct team) from users_with_team;

+----------------------+
| count(distinct team) |
+----------------------+
|                    3 |
+----------------------+


○　指定したカラムの合計を指定のカラムごとにグループ化し抽出
select sum(score) from users_with_team group by team;

+-------------------+--------+
| sum(score)        | team   |
+-------------------+--------+
| 8.199999809265137 | Team-A |
|                14 | Team-B |
|                10 | Team-C |
+-------------------+--------+

○　group byに条件を指定するときは、whereではなくhavingを使用する
select sum(score),team from users_with_team group by team having sum(score) > 10;

+------------+--------+
| sum(score) | team   |
+------------+--------+
|         14 | Team-B |
+------------+--------+

viewの作成

○　抽出条件をテーブルのように扱える。 複雑な抽出条件を何度も使う場合に便利。
○　viewの作成方法
　create view '作成するview名' as select '以降viewにしたい条件'

drop table if exists users;
create table users (
  id int unsigned primary key auto_increment,
  name varchar(20),
  score float
);

insert into users (name, score) values ('taguchi', 5.8);
insert into users (name, score) values ('fkoji', 8.2);
insert into users (name, score) values ('dotinstall', 6.1);
insert into users (name, score) values ('Tanaka', 4.2);
insert into users (name, score) values ('yamada', null);
insert into users (name, score) values ('tashiro', 7.9);

-----------------------------------------------------------------------------------

drop view if exists top3;
create view top3 as select * from users order by score desc limit 3;

○　viewを使用するときは以下のようにしてできる。
select * from top3;
+----+------------+-------+
| id | name       | score |
+----+------------+-------+
|  2 | fkoji      |   8.2 |
|  6 | tashiro    |   7.9 |
|  3 | dotinstall |   6.1 |
+----+------------+-------+

複数のテーブルを扱う

○　ブログのようなものを想定し、postsテーブルが複数のコメントを持つことにする。

drop table if exists posts;
create table posts (
  id int unsigned primary key auto_increment,
  title varchar(255),
  body text
);
drop table if exists comments;
create table comments (
  id int unsigned primary key auto_increment,
  post_id int unsigned not null,　　←どのpostsへのコメントか判断するカラム
  body text
);


insert into posts (title, body) values ('title 1', 'body 1');
insert into posts (title, body) values ('title 2', 'body 2');
insert into posts (title, body) values ('title 3', 'body 3');

insert into comments (post_id, body) values (1, 'first comment for post 1');
insert into comments (post_id, body) values (1, 'second comment for post 1');
insert into comments (post_id, body) values (3, 'first comment for post 3');
insert into comments (post_id, body) values (4, 'first comment for post 4');


○　複数のテーブルを紐づけてデータを抽出する方法
  inner join  --２つのテーブルに共通するレコードがあれば抽出

select * from posts inner join comments on posts.id = comments.post_id;

+----+---------+--------+----+---------+---------------------------+
| id | title   | body   | id | post_id | body                      |
+----+---------+--------+----+---------+---------------------------+
|  1 | title 1 | body 1 |  1 |       1 | first comment for post 1  |
|  1 | title 1 | body 1 |  2 |       1 | second comment for post 1 |
|  3 | title 3 | body 3 |  3 |       3 | first comment for post 3  |
+----+---------+--------+----+---------+---------------------------+


  outer join　--２つのテーブルで一致しないデータも含め抽出　どちらのテーブルを軸にするかで構文が異なる

　・ left outer join
  　左に書かれたテーブル（posts）を軸にする。postsに関連するレコードがcommentsになくてもpostsのレコードを表示する。関連するレコードがない場合はNULLで表示される。

select * from posts left outer join comments on posts.id = comments.post_id;

+----+---------+--------+------+---------+---------------------------+
| id | title   | body   | id   | post_id | body                      |
+----+---------+--------+------+---------+---------------------------+
|  1 | title 1 | body 1 |    1 |       1 | first comment for post 1  |
|  1 | title 1 | body 1 |    2 |       1 | second comment for post 1 |
|  3 | title 3 | body 3 |    3 |       3 | first comment for post 3  |
|  2 | title 2 | body 2 | NULL |    NULL | NULL                      |
+----+---------+--------+------+---------+---------------------------+

  ・ right outer join
  　右に書かれたテーブル（comments）を軸にする。commentsに関連するレコードがpostsになくてもcommentsのレコードを表示する。関連するレコードがない場合はNULLで表示される。

+------+---------+--------+----+---------+---------------------------+
| id   | title   | body   | id | post_id | body                      |
+------+---------+--------+----+---------+---------------------------+
|    1 | title 1 | body 1 |  1 |       1 | first comment for post 1  |
|    1 | title 1 | body 1 |  2 |       1 | second comment for post 1 |
|    3 | title 3 | body 3 |  3 |       3 | first comment for post 3  |
| NULL | NULL    | NULL   |  4 |       4 | first comment for post 4  |
+------+---------+--------+----+---------+---------------------------+

○　外部キー制約
  上記の例だと、commentsに外部キー制約を設定することでposts_idが存在しないcommentsレコードを作成しようとするとエラーになる。以下作成方法。
alter table comments add constraint '設定する制約の名前' foreign key (post_id) references posts (id);