MySQL

目的

csvファイルからデータを取り込む
- データベースsampledbを作成して
- 4つのテーブルshachikuhyo, branchtb, torihiki, kenkosindantbをcsvファイルから取り込む
procedure（ストアドプロシージャ）の基本操作
1. BMIを求めるプロシージャを作る
2. BMIを基準に「やせ型・適正・肥満」に分類するプロシージャを作る
復習もかねて
1. BMIとともにshachiku_idも一覧表示
2. sumを使ったプロシージャ

csvファイルからデータを取り込む

データベースsampledbを作成して

mysql> create database sampledb;

4つのテーブルshachikuhyo, branchtb, torihiki, kenkosindanをcsvファイルから取り込む

mysql> use sampledb;

create_tb_shachikuhyo.mysql

/* create_tb_shachikuhyo.mysql */
use testdb;
CREATE table shachikuhyo(
    shachiku_id CHAR(4) PRIMARY KEY,
    shachiku_name CHAR(15),
    birth_year INT(4),
    branch_id CHAR(3)
);

create_tb_branchtb.mysql

/* create_tb_branchtb.mysql */
use testdb;
CREATE table branchtb(
    branch_id CHAR(3) PRIMARY KEY,
    branch_name CHAR(10),
    jusho CHAR(30)
);

create_tb_torihiki.mysql

/* create_tb_torihiki.mysql */
use testdb;
CREATE table torihiki(
    torihiki_id CHAR(4) PRIMARY KEY,
    uriage CHAR(4),
    tantou CHAR(4)
);

create_tb_kenkosindatadb.mysql

/* create_tb_kenkosindatadb.mysql */
use testdb;
create table kenkosindantb(
shainid char(4) primary key,
height int(3),
weight int(3)
);

以下ではcsvファイルをテーブルに格納している。
shachikuhyo.csvファイル ==> shachikuhyo
branch.csvファイル ==> branchtb
torihiki.csvファイル ==> torihiki
kenkosinan.csvファイル ==> kenkosindantb

mysql> load data infile 'shachikuhyo.csv'   into table shachikuhyo      fields terminated by ',' lines terminated by x'0D0A'
mysql> load data infile 'branch.csv'        into table branchtb         fields terminated by ',' lines terminated by x'0D0A'
mysql> load data infile 'torihiki.csv'      into table torihiki         fields terminated by ',' lines terminated by x'0D0A'
mysql> load data infile 'kenkosindan.csv'   into table kenkosindantb    fields terminated by ',' lines terminated by x'0D0A'

これによりデータは格納できた

procedure（ストアドプロシージャ）の基本操作

1. BMIを求めるプロシージャを作る

1.sql

/* 1.sql */
use sampledb;
delimiter //
create procedure calculate_bmi(in id char(4) )
    begin
    select 
    kenkosindantb.shachiku_id, weight*10000/height/height from kenkosindantb 
    where id=kenkosindantb.shachiku_id;
    end
    //
    delimiter ;

mysql> source 1.sql
Database changed
Query OK, 0 rows affected (0.00 sec)

mysql> call calculate_bmi('a001');
+-------------+----------------------------+
| shachiku_id | weight*10000/height/height |
+-------------+----------------------------+
| a001        |                18.02595738 |
+-------------+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

2. BMIを基準に「やせ型・適正・肥満」に分類するプロシージャを作る

2.sql

/* 2.sql */
use sampledb;
create procedure kadai2(in id char(4))
begin
    select
    A.shachiku_id,
    (
        case
        when weight*10000/height/height < 18.5
            then 'Yasegata'
        when weight*10000/height/height < 25
            then 'Tekisei'
        else 'Himan'
        end 
    ) as doai
    from kenkosindantb A
    where id=A.shachiku_id;
end
//
delimiter ;

mysql> source 2.sql
Database changed
Query OK, 0 rows affected (0.00 sec)

mysql> call tekiseido('a001');
+-------------+----------+
| shachiku_id | doai     |
+-------------+----------+
| a001        | Yasegata |
+-------------+----------+
1 row in set (0.00 sec)

復習もかねて

3. BMIとともにshachiku_idも一覧表示

3.sql

/* 3.sql */
use sampledb;
delimiter //
create procedure show3(in id char(4))
begin
    select
    A.shachiku_id,
    B.shachiku_name, 
    (
        case
        when weight*10000/height/height < 18.5
            then 'Yasegata'
        when weight*10000/height/height < 25
            then 'Tekisei'
        else 'Himan'
        end 
    ) as doai
    from kenkosindantb A join shachikuhyo B on A.shachiku_id=B.shachiku_id
    where id=A.shachiku_id;
end
//
delimiter ;

mysql> source 3.sql
Database changed
Query OK, 0 rows affected (0.00 sec)

mysql> call show3('a001');
+-------------+---------------+----------+
| shachiku_id | shachiku_name | doai     |
+-------------+---------------+----------+
| a001        | Ai            | Yasegata |
+-------------+---------------+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

4. sumを使ったプロシージャ

/* 4.sql */
use sampledb;
delimiter //
create procedure show4()
begin
    select
    A.shachiku_id,
    sum(B.uriage) as torihikigaku
    from shachikuhyo as A
    join torihiki as B on A.shachiku_id=B.tantou
    group by A.shachiku_id
    ;
end
//
delimiter ;

mysql> source 4.sql
Database changed
Query OK, 0 rows affected (0.00 sec)

mysql> call show4();
+-------------+--------------+
| shachiku_id | torihikigaku |
+-------------+--------------+
| a001        |          700 |
| a002        |          300 |
| a003        |          300 |
| a004        |          300 |
| a005        |          500 |
| a006        |          600 |
| a009        |          900 |
+-------------+--------------+
7 rows in set (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

4_2.sql

/* 4_2.sql */
use sampledb;
delimiter //
create procedure show4_2()
begin
    select
    A.shachiku_id,
    sum(B.uriage) as torihikigaku
    from shachikuhyo as A
    left outer join torihiki as B on A.shachiku_id=B.tantou
    group by A.shachiku_id
    ;
end
//
delimiter ;

mysql> source 4_2.sql
Database changed
Query OK, 0 rows affected (0.00 sec)

mysql> call show4_2();
+-------------+--------------+
| shachiku_id | torihikigaku |
+-------------+--------------+
| a001        |          700 |
| a002        |          300 |
| a003        |          300 |
| a004        |          300 |
| a005        |          500 |
| a006        |          600 |
| a007        |         NULL |
| a008        |         NULL |
| a009        |          900 |
+-------------+--------------+
9 rows in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

MySQLを少しかじるPart2