３－８・関数を使ってデータを取り出す 　　2004年6月3日(木)　０１T6010F 　　　　　　　　　　　　　　大北高広.

Presentation on theme: "３－８・関数を使ってデータを取り出す 　　2004年6月3日(木)　０１T6010F 　　　　　　　　　　　　　　大北高広."— Presentation transcript:

1 ３－８・関数を使ってデータを取り出す 　　2004年6月3日(木)　０１T6010F 　　　　　　　　　　　　　　大北高広

2 　　関数とは、「引数」を呼ばれる値（数値もしくは文字列）に計算などの処理を行い、｢戻り値」である結果データを表示させるための処理を実行する式のようなものです。結果を表示させることは、結果を「返す」ともいいます。

3 ◆関数とは 集計関数をDBで使用する場合、引数にカラム名を指定することで、そのカラムに所属するすべてのデータを対象に処理を行えます。
　　　関数は　関数名（引数） で用います。 　　　関数の中には、（　）内に引数を2つ以上指定することもあります。一方、引数を取らない関数は、（　）内にはなにも入れません。 　　　関数の処理を行い、その戻り値を表示させる場合にもselectコマンドを使用します。

4 ◆集計関数 ・平均値：　avg() 　　avg()という関数は、引数の数値もしくはカラムに所属するすべての数値の平均値を出します。そのカラムの数値を全部足して、Null値ではないレコード数で割る、という処理を行います。 mysql> select avg(AGE_C) from FRIENDS_T2; | avg(AGE_C) | | | 1 row in set (0.00 sec)

5 カラム名の場所に関数が表示されていますが、asを使うことで、エイリアス名で表示させることもできます。
mysql> select avg(AGE_C) as ”平均年齢” from FRIENDS_T2; | 平均年齢 | | | 1 row in set (0.00 sec) 　　特定の条件に合うデータを対象にした関数計算は、where句を使って条件の設定をします。where以下の条件によるデータが取り出されたあと、そのデータに対して関数による計算がおこなわれます。 　mysql> select avg(AGE_C) from FRIENDS_T2 where AGE_C >= 30; | avg(AGE_C) | | | 1 row in set (0.00 sec)

6 ・最大値：max()、 最小値：min()
　　テーブルの中から年齢の最大値を取り出します。selectのあとに、年齢を引数とし、最大数を出す関数を記述します。 mysql> select max(AGE_C) from FRIENDS_T2; | max(AGE_C) | | | 1 row in set (0.00 sec) 　　年齢の最小値を取り出します。 mysql> select min(AGE_C) from FRIENDS_T2; | min(AGE_C) | | | 1 row in set (0.00 sec)

7 ・レコード数を集計：count() レコード数を数える関数です。（＊を用いているのでNull値だけを持つレコードもカウントされます。）
mysql> select count(*) from FRIENDS_T2; | count(*) | | | 1 row in set (0.00 sec) 　　指定のカラム名(NAME_C)から、Null値ではないデータを数えます。 mysql> select count(NAME_C) from FRIENDS_T2; | count(NAME_C) | | | 1 row in set (0.00 sec)

8 ・カラムの数値を集計：sum() 指定のカラムの数値を足した合計を返します。
　　指定のカラムの数値を足した合計を返します。 mysql> select sum(AGE_C) from FRIENDS_T2; | sum(AGE_C) | | | 1 row in set (0.02 sec)

9 ◆group by/ having句と集計関数
・平均値：　avg() 　　group byを使ってレコードをグループ化し、その中で集計関数を行います。 mysql> select BLOOD_C, avg(AGE_C) from FRIENDS_T2 group -> by BLOOD_C; | BLOOD_C | avg(AGE_C) | | AB型　　　　 | | | A型 | | | B型 | | | O型 | | 4 rows in set (0.01 sec)

10 　　条件で絞り込んだデータをグループ化し、そのあと、集計関数で計算をします。where句で年齢のカラムが30以上を指定し、その結果セットに対して血液型でグループ化して各グループでの平均年齢を出します。
mysql> select BLOOD_C, avg(AGE_C) from FRIENDS_T2 group where -> AGE_C >= 30 group by BLOOD_C; | BLOOD_C | avg(AGE_C) | | AB型　　　　 | | | A型 | | | B型 | | | O型 | | 4 rows in set (0.00 sec)

11 　　グルーピングしたデータを集計関数で計算し、その結果を基に特定の条件で絞り込みします。関数の戻り値にはwhere句は使えませんので、having句を使用します。
mysql> select BLOOD_C, avg(AGE_C) from FRIENDS_T2 group -> by BLOOD_C having avg(AGE_C) >= 30; | BLOOD_C | avg(AGE_C) | | AB型　　　　 | | | A型 | | | O型 | | 3 rows in set (0.00 sec)

12 ・最大値：max() レコードをグループ化し、最大値を求める関数max()を使います。使い方はavg()と同じです。
mysql> select BLOOD_C, max(AGE_C) from FRIENDS_T2 group -> by BLOOD_C; | BLOOD_C | max(AGE_C) | | AB型　　　　 | | | A型 | | | B型 | | | O型 | | 4 rows in set (0.00 sec)

13 having句の中ではwhere句と同様、andやorを使うことができます。
mysql> select BLOOD_C, max(AGE_C) from FRIENDS_T2 -> group by BLOOD_C having BLOOD_C like “A%” -> and max(AGE_C) >= 50; | BLOOD_C | max(AGE_C) | | A型 | | 1 row in set (0.00 sec) 　andの代わりにorを使うと、「どちらかの条件を満たす」の意味になります。 mysql> select BLOOD_C, max(AGE_C) from FRIENDS_T2 -> group by BLOOD_C having BLOOD_C like “A%” -> or max(AGE_C) >= 50; | BLOOD_C | max(AGE_C) | | AB型 | | | A型 | | 2 rows in set (0.00 sec)

14 ・レコードの集計：count() 　　レコードをグループ化したうえで、count()を使って、グループ化されたレコードの数を数えます。このとき、selectしたカラムがNull値のレコードはカウントされません。 mysql> select BLOOD_C, count(*) from FRIENDS_T2 -> group by BLOOD_C; | BLOOD_C | count(*) | | AB型　　　　 | | | A型 | | | B型 | | | O型 | | 4 rows in set (0.00 sec)

15 mysql> update FRIENDS_T2 set AGE_C = null
-> where ID_C = 2; Query OK, 1 row affected(0.00 sec) rows matched: 1 Changed: 1 Warnings: 0 mysql> select BLOOD_C, count(AGE_C) from FRIENDS_T2 -> group by BLOOD_C; | BLOOD_C | count(AGE_C) | | AB型　　　　 | | | A型 | | | B型 | | | O型 | | 4 rows in set (0.00 sec)

16 having句を使った条件で絞り込むこともできます。エイリアスを作成してhaving句の中でそのエイリアス名を使用します。
mysql> select BLOOD_C, count(AGE_C) as “人数” from -> FRIENDS_T2 group by BLOOD_C having 人数 >= 3; | BLOOD_C | avg(AGE_C) | | AB型　　　　 | | | O型 | | 2 rows in set (0.00 sec)

17 ・カラムの数値を集計：sum() グループ化したデータに対して、そのグループごとに所属する値の合計値を出します。
　　　グループ化したデータに対して、そのグループごとに所属する値の合計値を出します。 mysql> select BLOOD_C, sum(AGE_C) from FRIENDS_T2 group -> by BLOOD_C; | BLOOD_C | sum(AGE_C) | | AB型　　　　 | | | A型 | | | B型 | | | O型 | | 4 rows in set (0.00 sec)

18 関数を２つ使った例として、sum()とcount()を使って血液型ごとの平均年齢を出します。年齢の合計を数で割ると、平均年齢がでます。
mysql> select BLOOD_C, sum(AGE_C) / count(AGE_C) as -> “平均年齢” from FRIENDS_T2 group by BLOOD_C; | BLOOD_C | 平均年齢　 | | AB型　　　　 | | | A型 | | | B型 | | | O型 | | 4 rows in set (0.00 sec)

19 ◆文字列関数 ・大文字、小文字の操作：upper(),lower() 　 引数には数値だけでなく、文字列を指定して処理を行うこともあります。文字列が引数となる場合には必ず“　”で括ります。upper()とlower()は、引数の中のアルファベットの小文字を大文字に、大文字を小文字にする関数です。 mysql> select NAME_C, lower(BLOOD_C) from FRIENDS_T2; | NAME_C | lower(BLOOD_C) | | 村何友好　 | o型 | | 友竹大雪 | a型 | | 友崎亜吐夢　| a型 | | 来伴行友 | b型 | | 沢山友人 | o型 | ・・・・・・・・・・ 10 rows in set (0.00 sec)

20 selectコマンドにBLOOD_Cを追加すると、大文字の血液型も表示されます。
mysql> select NAME_C, lower(BLOOD_C), BLOOD_C from -> FRIENDS_T2; | NAME_C | lower(BLOOD_C) | BLOOD_C | | 村何友好　 | o型 | O型 | | 友竹大雪 | a型 | A型 | | 友崎亜吐夢　| a型 | A型 | ・・・・・・・・・・ 　　関数を使って表示させるということは、テーブル中の値が小文字の値に置き換えられるわけではなく、データを小文字に変換して表示しているだけである。

21 ◆算術演算子を使った計算 ・加減乗除 算術演算子を使って、取り出した数値データになんらかの計算を行って結果を表示させることが可能です。
　　　算術演算子を使って、取り出した数値データになんらかの計算を行って結果を表示させることが可能です。 + 足し算(加算） - 引き算（減算） * 掛け算（乗算） / 割り算（除算） % 割り算の余り（剰余） 　剰余演算に関しては、小数を使用する場合、整数に変換（四捨五入）してから行います。

22 テーブル中の１つのカラムのデータで計算を行い、その計算結果を別のカラムを設けて表示させます。
　　テーブル中の１つのカラムのデータで計算を行い、その計算結果を別のカラムを設けて表示させます。 mysql> select ID_C, NAME_C, AGE_C, AGE_C+10 as “10年後” -> from FRIENDS_T2; | ID_C | NAME_C | AGE_C | 10年後 | | 　 10 | 村何友好 | | | | | 友竹大雪 | | | | | 友崎亜吐夢 | | | | | 来伴行友 | | | | | 沢山友人 | | | | | 是田良友 | | | | | 出雲友子 | | | | | 友田智香 | | | | | 小友達夫 | | | | | 大友達子 | | | 10 rows in set (0.00 sec)

23 西暦2000年から年齢を引いて、何年生まれか計算します。
　西暦2000年から年齢を引いて、何年生まれか計算します。 mysql> select ID_C, NAME_C, AGE_C, 2000-AGE_C as “誕生年” -> from FRIENDS_T2; | ID_C | NAME_C | AGE_C | 誕生年 | | 　 10 | 村何友好 | | | | | 友竹大雪 | | | | | 友崎亜吐夢 | | | | | 来伴行友 | | | | | 沢山友人 | | | | | 是田良友 | | | | | 出雲友子 | | | | | 友田智香 | | | | | 小友達夫 | | | | | 大友達子 | | | 10 rows in set (0.00 sec)

24 割り算の例として、34.3という平均年齢で年齢を割り、対平均年齢の比率を出してみます。
　　割り算の例として、34.3という平均年齢で年齢を割り、対平均年齢の比率を出してみます。 mysql> select ID_C, NAME_C, AGE_C, AGE_C / 34.3 as -> “対平均年齢” from FRIENDS_T2; | ID_C | NAME_C | AGE_C | 対平均年齢 | | 　 10 | 村何友好 | | | | | 友竹大雪 | | | | | 友崎亜吐夢 | | | | | 来伴行友 | | | | | 沢山友人 | | | | | 是田良友 | | | ・・・・・・・ 10 rows in set (0.00 sec) 　34.3という数字ではなく、avg(AGE_C)を使って計算すると、group byを使用していないので、エラーになります。

25 算術演算子は、where句の中やhaving句の中でも使うことができます。「計算結果がある条件を満たすかどうか」という指定が可能です。
　　　10年後の年齢が40歳以下の人を取り出します。where句の中にAGE_C <= 40を記述します。この場合、selectあとにAGE_C+10を記述しなければ、結果セットに10を足した数値のカラムを表示することはできません。 mysql> select ID_C, NAME_C, AGE_C, from FRIENDS_T2 where -> AGE_C+10 <= 40; | ID_C | NAME_C | AGE_C | | | 友竹大雪 | | | | 沢山友人 | | | | 友田知香 | | | | 大友達子 | | | 4 rows in set(0.00)

26 条件句の中で計算結果を利用する方法ですが、今度は2つの条件を設定します。これらはandを使って結びつけます。
　　　 現在30歳以上で、5年後に40歳以下の人を取り出します。where句で30歳以下を指定し、その結果に対して5を足したものが40以下であるレコードを結果セットとします。 mysql> select NAME_C, AGE_C, from FRIENDS_T2 -> where AGE_C >= 30 and AGE_C+5 <= 40; | NAME_C | AGE_C | | 来伴行友 | | | 是田良友 | | | 2 rows in set(0.00 sec)

27 ◆Null値の計算 算術演算子や関数を使って、Null値を含むデータを計算した場合は、Null値が返ってきます。
mysql> select null+31; | null+31 | | NULL | 1 row in set(0.00 sec) mysql> select sin(null); | sin(null) | | NULL |

28 ◆計算結果をカラムに入力する方法 計算結果をカラムに追加します。
　　　計算結果をカラムに追加します。 mysql> alter table FRIENDS_T2 add AGE10_C int; Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> update FRIENDS_T2 set AGE10_C = AGE_C+10; Query OK, 10 rows affected (0.00 sec) Rows matched: 10 Changed: 10 Warnings: 0 mysql> select * from FRIENDS_T2; | ID_C | NAME_C | ADD_C | AGE_C | BLOOD_C | AGE10_C | | | 村何友好 | 愛媛県愛媛市何処町 | | O型 | | | | 友竹大雪 | 大阪府大阪市何処町 | | A型 | | ・・・・・・ 10 rows in set(0.00 sec)