MySQLの基本的な使い方【データの集計他】

前回の記事ではMySQLのデータの抽出に関しておもにみてきました。

前回の記事
MySQLの基本的な使い方【データの抽出】

今回はデータの集計やテーブルの結合なでについて書いています。

データの集計

※users_teamテーブルがあるものして書いています。

データの集計で使える関数にはおもに以下のようなものがあります。

• count()⇀ レコードの数をカウントする
• sum()⇀ 合計
• min()⇀ 最小値
• max()⇀ 最大値
• avg()⇀ 平均値

select count(*) from users_team;
select min(score) from users_team;
select max(score) from users_team;
select avg(score) from users_team;

distinkt

distinktを使用するとレコードを重複したレコードを分類してくれます。distinctはカラム名を指定します。

count(distinkt)とすると重複したレコードをとりぞいた状態での数を取得できます

select distinct fieldname from users_team;
select count(distinct) fieldname from users_team;

group by、having

group byはグループ集計で使用します

select sum(round(score)), team from users_team group by team;

グループ化したあとのデータに条件を指定して抽出するにはwhereではなくhavingを使います。havingはグループ化したカラムや集計した値にしかつかうことができません。

select sum(round(score)), team from users_team group by team having sum(score) > 10;

group byはwhereと一緒に使うこともできます。その場合はwhereで抽出したあとのデータがグループ化されます。

select sum(round(score)), team from users_team where id > 2 group by team having sum(score) > 10;

サブクエリ

サブクエリはややこしいのでコードだけのせときます。

–users_alphaテーブル–
+—-+——–+——-+
| id | name | score |
+—-+——–+——-+
| 1 | suzuki | 10 |
| 2 | tanaka | 20 |
| 3 | saito | 15 |
+—-+——–+——-+

select sum(alpha.score) from(
select * from users_alpha
) as alpha;

+——————+
| sum(alpha.score) |
+——————+
| 45 |
+——————+

view

viewをつかうと抽出条件をまとめて他で使うことができます

drop table if exists users_alpha;
create table users_alpha(
  id int unsigned primary key auto_increment,
  name varchar(16),
  score int default 1
);

insert into users_alpha(name, score) values
  ('hamaguti', 10),
  ('matuguti', 20),
  ('kuramoto', 30),
  ('tanaka', 40),
  ('suzuki', 50),
  ('saito', 60);

create view top3 as select * from users_alpha order by score desc limit 3;
select * from top3;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  6 | saito  |    60 |
|  5 | suzuki |    50 |
|  4 | tanaka |    40 |
+----+--------+-------+;
-- show tables; でviewの確認
-- drop view xxx; でviewの削除

テーブルの結合

テーブルの結合は内部結合と外部結合がありますが、今回は内部結合のコードを書いておきます

内部結合の場合は主キーと一致するものだけが結合されます。

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 not null,
  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');

select * from posts;
select * from comments;

select * from posts 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  |
|  2 | title 2 | body 2 |  2 |       1 | second comment for post 1 |
|  3 | title 3 | body 3 |  3 |       3 | first comment for post 3  |
+----+---------+--------+----+---------+---------------------------+

last_insert_id()

とりあえずこうなります↓

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 not null,
  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');

delete from posts where id = 2;
insert into posts(title, body) values ('new title', 'new body');
insert into comments(post_id, body) values (last_insert_id(), 'first comment for new post');

select * from posts;
select * from comments;

select * from posts 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   |
|  4 | new title | new body |  4 |       4 | first comment for new post |
+----+-----------+----------+----+---------+----------------------------+

データ挿入と更新の時刻

current_timestampを使うことによって自動的に現在時刻でレコードが作成されます。

データの更新時には on update current_timestampを使うとデータを更新したときの日時で日付が更新されます。

create table posts(
  id int unsigned primary key auto_increment,
  title varchar(255),
  body text,
  created datetime default current_timestamp,
  updated datetime default current_timestamp on update current_timestamp -- 更新時の日時
  );
 

where句で日付の抽出をするには以下のようにします。

select * from posts where created > '2017-01-01';

日付を加算するには以下のように書けばよいようです。

select created, date_add(created, interval 14 day) from posts;
select created, date_add(created, interval 2 week) from posts;

日付のフォーマットを指定

select created, date_format(created, '%W %M %Y') from posts;

データベースの書き出し

データベースの書き出しはmySQLのシェルからぬけておこないmysqldumpを使います

mysqldump -u ユーザー名 -p データベース名 > バックアップファイル名.sql

データを間違えてけしてしまったときなどは、バックアップしたファイルはMySQLのシェルでよみこんであげるだけでデータを復元することができます

source backup.sql