select type, avg(count) from product group by type;


このように グループ化して集計関数を使うことで、家具と家電に分類して countカラムの平均値を取得することができた。

select type, sum(count) from product group by type;


それぞれ typeごとにグループ化して総数を取得することができた。

select type, max(count) from product group by type;
それぞれをグループ化して、typeごとの最大のcount数を取得することができた。

select type, min(count) from product group by type;


それぞれグループ化して、typeごとの最小のcount数を取得することができた

select type, sum(count) from product group by type;


それぞれグループ化して、typeごとの合計を取得することができた。

HAVING句を使うことで、グループ化が実行された後に条件を絞り込むことができる。

実際に操作をしてみる

select type, count(count) as count_stock from product group by type having count > 10;


上記のSQL文を実行した時にエラーが生じてしまった。

エラーの内容

ERROR 1054 (42S22): Unknown column 'count' in 'having clause'

countカラムが重複していることが原因

countカラムを別の名前のカラムに変更する


カラム名を変更するには、ALTER TABLE 文を使う

カラム名の変更の構文

ALTER TABLE テーブル名

RENAME COLUMN 古いカラム名 TO 新しいカラム名


それでは、 countカラムを stockカラムに変更する

alter table product rename column count to stock;


productテーブルを確認すると、 countカラムがstockカラムに変更されていることを確認できた。

再度having句で条件を指定して、集計関数を利用する

select type, count(stock) as count_stock from product group by type having count_stock > 1;


stockカラムを AS句で count_stockカラムに変更、

typeカラムをグループ化した後に、 HAVING句で count_stockカラムが1以上を取得している。

昇順(デフォルト) ASC

降順 DESC

select type, count(stock) as count_stock from product group by type order by count_stock asc;


それぞれ typeごとにグループ化して、カラム名をAS句で count_stock に変更して、 count_stockを昇順にして取得することができた。

降順の場合

LENGTH関数は、引数に指定した文字列の長さを取得する関数

select length("HelloWorld!!");


length関数を使うことで、HelloWorld!! の文字列を取得することができた。

先程作成した productテーブルを使用して、length関数を使ってみる。

productテーブルからnameカラムを取得して、nameカラムの文字数を取得してみる。

select length(name) from product;


それぞれのnameカラムの文字列を取得することができた。

これだと何の商品が何文字かがわからないため、nameカラムの文字数の隣に、 商品名を表示する。

select name, length(name) from product;


これで商品名と、各商品ごとの文字数を取得することができた。

次にWHERE句を使用して条件を抽出してから、文字列を取得するように操作してみる。

今回は、文字列が10文字以上の商品 (nameカラム)を取得する

select name, length(name) from product where length(name) > 10;


このように where句を使い条件を絞り、10文字以上の商品名を取得することができた。

CONCAT関数は、引数に指定した文字列を連結した文字列として取得することができる。

文字列を結合するための関数

実際に操作してみよう

まずは新しくテーブルを作成する。

humanテーブルを作成する

create table human (id int(10), last_name varchar(20), first_name varchar(20), area varchar(10));

今回は、idカラム、 last_nameカラム、 first_nameカラム、 areaカラムを作成

実際にデータを追加する

insert into human (id, last_name, first_name, area) values (1, '鈴木', '一郎', '愛知県'), (2, '佐藤', '花子', '北海道'), (3, '平山', '吾郎', '香川県'), (4, '山田', '朋子', '静岡県'), (5, '田沢', '英二', '東京都');


humanテーブルを作成

では実際にCONCAT関数を使用して、last_name と first_nameを結合していく

select concat(last_name,'',first_name)from human;


このようにCONCAT関数を使用することで、 last_name と first_name の文字列を結合することができた

REPLACE関数を使用すると、文字と文字を置き換えることができる

(引数に指定した文字列のなかに含まれる指定の文字列を新しい文字列に置き換えることができる)

構文

REPLACE (指定の文字列, 置き換える文字列, 置き換え後の文字列)

では実際に新しいテーブルを作成するところから始める。

create table staff (id int(10), name varchar(20), department varchar(20));

今回は、idカラム、 nameカラム、 departmentカラムを作成

データを追加する

insert into staff (id, name, department) values (1, '佐藤吾郎', '営業部'), (2, '鈴木一郎', '営業部'), (3, '佐藤花子', '総務部'), (4, '小泉京子', '経理部'), (5, '平山一雄', '開発部'), (6, '大石正明', '開発部'), (7, '田沢英二', '営業部');


staffテーブルを作成できた

この中の「開発部」を「システム開発部」に変更するとする。

select id, name, replace(department, '開発部', 'システム開発部') from staff;


このようにREPLACE関数を使用することで、「開発部」の項目を全て「システム開発部」に置き換えることができた。

INSERT関数を使用すると、引数に指定した文字列の中の指定した位置から指定した長さの部分を別の文字列に置き換えることができる

実際に新しいテーブルを作成する

create table user (name varchar(20), area varchar(20));

nameカラムと、 areaカラムを作成

データを追加する

insert into user (name, area) values ('鈴木次郎', '静岡県静岡市葵区'), ('佐藤花子', '東京都文京区御徒町'), ('平山悟', '埼玉県さいたま市 南区'), ('大石正明', '大阪府堺市');


userテーブルを作成

ではここで areaカラムの 東京都文京区御徒町を変更する

変更後

東京都台東区御徒町

今回変更するのは、4文字目から3文字分(文京区)を新しい文字列(台東区)に置き換えて取得する

select insert('東京都文京区御徒町', 4, 3, '台東区');


指定の文字列を変更することができた

ROUND関数を使用すると、引数に指定した値を四捨五入、切り捨て、切り上げ、または偶数の丸めを行った値を取得できる。

実際にROUND関数を操作していく。

select round(5.7), round(5.1);


四捨五入をした値を取得することができた。

今度は丸めを行う小数点の位置を変更して操作してみる。

select round(8.357, 0), round(8.357, 1), round(8.357, 2);


丸めの桁数が1の時は、 小数第一を四捨五入。

丸めの桁数が2の時は、小数第2位を四捨五入。

このように四捨五入したい桁数を指定するには、第二引数を使用する

四捨五入で整数を指定する場合は、第二引数にマイナスを指定する。

select round(178.77, -1);


このように第二引数にマイナスを指定することで整数にすることができた

FLOOR関数は、引数で指定した数値を切り下げて整数を求める関数。

実際に操作してみる

select floor(7.9), floor(-5.3), floor(6.7), floor(-8.3);


このようにFLOOR関数を使用することで、引数で指定した数値を切り下げて整数を求めることができた

RAND関数を使用すると、0以上1.0未満の範囲でランダムな浮動小数点を生成することができる

select rand();


10以上15未満の乱数を生成する場合

select floor(10 + rand() * 5);



このように何回か実行すると、ランダムに生成されていることがわかる

MySQLでDATE_FORMAT関数を使用すると引数に指定した日付を表す値を指定のフォーマットで整形した文字列を取得できる。

日付型データには3種類ある。

・DATE型
→ 日付部分の値だけを取得

・DATETIME型
→ 日付と時間の両方の部分を含む値を取得

・TIMESTAMP型
→ 現在の日付および時間の更新を取得

日付に関するフォーマット (よく使うフォーマットのみ紹介)

%Y
→ 年、数字、4桁

%y
→ 年、数字、2桁

%M
→ 月名 (January..December)

%m
→ 月、数字(00…12)

日付に関するフォーマットの続き

%d
→ 日にち、数字(00….31)

%W
→ 曜日名 (Sunday…..Saturday)

%w
→ 曜日 (0=Sunday….6=Saturday)

時刻に関して指定できるフォーマット(よく使うフォーマットのみ紹介)

%h
→ 時間(01….12)

%i
→ 分、数字(00….59)

%s
→ 秒 (00…59)

実際に日付を取得してみる

%Y(年、数字、4桁) %m(月名) %d(00….31)

select date_format('2022-06-03', '%Y年%m月%d日');


DATE_FORMAT関数を使用することで、2022-06-03 を 2022年6月3日に変換することができた。

%Y(年、数字、4桁) %M(月名(January…December) %d(日、数字(00…31))に整形する

select date_format('2022-06-03', '%Y %M %d');


このようにDATE_FORMAT関数を使用することで、元になる日付と時刻の値からフォーマットにしたがって、値を取得することができる