Posted at

MySQLを少しかじるPart2

More than 1 year has passed since last update.


MySQL

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)