郵便番号データベース MySQL ビュー View

萩原賢二のコーディングストック > プログラミング > 郵便番号データベース MySQL ビュー View

2019.07.27

前の投稿「郵便番号データベース」を元に郵便番号検索ツールで mysqlのViewについての学習。
データそのものを保存するのではなく、テーブルの結合等でさも新しいテーブルのような存在です。
Viewにyり、必要なデータが複数のテーブルにまたがる場合などに便利です。

郵便番号辞書の構造(ER図）
CREATE VIEW文によるビューの作成

作成されたビューおよびテーブルの一覧を　show tables で確認します。

作成したViewを使って実際のデータにアクセスしてみます。

おまけに作成したビューを利用した郵便番号検索Webアプリのサンプル

メニューに戻る

郵便番号辞書の構造(ER図）

今までの投稿で作成した「郵便番号辞書」の構造をER図として「MysqlWorkBench」で
作成してみました。
このテーブルだけでは複雑に紐ずいてリレーションで求める内容が解りづらいですね。
新しくテーブルを用意する方法もありますが「View」を利用して求める内容を解りやすく
表現してみます。

メニューに戻る

CREATE VIEW文によるビューの作成

CREATE VIEW ビュー名（＜列名1＞, ＜列名2＞, …） AS ＜SELECT 文＞
作り方として、まず通常の＜SELECT 文＞を書いてみます。
うまく表現できたらら、create view文でViewを作成します。文章にすると解りずらいので実際に作成し、その使い方のサンプルをみると理解しやすいと思います。

郵便番号テーブル、都道府県テーブル、市町村テーブルを結合して
郵便番号、県名、都道府県名、町域名、およびそのカナ名を表現するView
地域名テーブル、都道府県テーブルを結合した地域が含まれる都道府県を取得するView
地域と都道府県の多対多の中間テーブルよりそのキーが所有するテーブルからキーの名称を取得するView

// ビューの作成
-- create view 郵便番号テーブル、都道府県テーブル、市町村テーブルを結合
create view zipView as
select
zipcode.post_code,
prefecture.pref_name,
city.city_name,
zipcode.town_name,
prefecture.pref_kana,
city.city_kana,
zipcode.town_kana
from
zipcode 
inner join city on zipcode.city_code = city.city_code
inner join prefecture on city.pref_no = prefecture.pref_no

-- create view 地域名テーブル、都道府県テーブルを結合
create view prefareaView as
select area.area_name,prefecture.pref_no,prefecture.pref_name 
from areapref 
inner join area on areapref.area_no = area.area_no
inner join prefecture on areapref.pref_no = prefecture.pref_no

-- create view 地域と都道府県の多対多の中間テーブルよりそのキーが所有するテーブルからキーの名称を取得
create view areaView as
select area.area_no,area.area_name,prefecture.pref_no,prefecture.pref_name from areapref 
inner join area on areapref.area_no = area.area_no
inner join prefecture on areapref.pref_no = prefecture.pref_no;

// ビューの作成

-- create view 郵便番号テーブル、都道府県テーブル、市町村テーブルを結合

create view zipView as

select

zipcode.post_code,

prefecture.pref_name,

city.city_name,

zipcode.town_name,

prefecture.pref_kana,

city.city_kana,

zipcode.town_kana

from

zipcode

inner join city on zipcode.city_code = city.city_code

inner join prefecture on city.pref_no = prefecture.pref_no

-- create view 地域名テーブル、都道府県テーブルを結合

create view prefareaView as

select area.area_name,prefecture.pref_no,prefecture.pref_name

from areapref

inner join area on areapref.area_no = area.area_no

inner join prefecture on areapref.pref_no = prefecture.pref_no

-- create view 地域と都道府県の多対多の中間テーブルよりそのキーが所有するテーブルからキーの名称を取得

create view areaView as

select area.area_no,area.area_name,prefecture.pref_no,prefecture.pref_name from areapref

inner join area on areapref.area_no = area.area_no

inner join prefecture on areapref.pref_no = prefecture.pref_no;

メニューに戻る

作成されたビューおよびテーブルの一覧を　show tables で確認します。

// 作成された ビューおよびテーブルの一覧を　show tables で確認しま
mysql> show tables;
+--------------------+
| Tables_in_postcode |
+--------------------+
| area               |
| areapref           |
| areaview           |
| city               |
| prefareaview       |
| prefecture         |
| zipcode            |
| zipview            |
+--------------------+
8 rows in set (0.00 sec)

// 作成されたビューおよびテーブルの一覧を　show tables で確認しま

mysql> show tables;

+--------------------+

| Tables_in_postcode |

+--------------------+

| area |

| areapref |

| areaview |

| city |

| prefareaview |

| prefecture |

| zipcode |

| zipview |

+--------------------+

8 rows in set (0.00 sec)

メニューに戻る

作成したViewを使って実際のデータにアクセスしてみます。

// view による郵便番号検索
mysql> show fields from zipView;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| post_code | int(11)     | NO   |     | NULL    |       |
| pref_name | varchar(30) | NO   |     | NULL    |       |
| city_name | varchar(30) | NO   |     | NULL    |       |
| town_name | text        | YES  |     | NULL    |       |
| pref_kana | varchar(30) | NO   |     | NULL    |       |
| city_kana | varchar(30) | NO   |     | NULL    |       |
| town_kana | text        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> select * from zipView where post_code=1000001\G
*************************** 1. row ***************************
post_code: 1000001
pref_name: 東京都
city_name: 千代田区
town_name: 千代田
pref_kana: トウキョウト
city_kana: チヨダク
town_kana: チヨダ
1 row in set (0.23 sec)

// view による郵便番号検索

mysql> show fields from zipView;

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

| post_code | int(11) | NO | | NULL | |

+-----------+-------------+------+-----+---------+-------+

7 rows in set (0.00 sec)

mysql> select * from zipView where post_code=1000001\G

*************************** 1. row ***************************

post_code: 1000001

pref_name: 東京都

city_name: 千代田区

town_name: 千代田

pref_kana: トウキョウト

city_kana: チヨダク

town_kana: チヨダ

1 row in set (0.23 sec)

// 地域の都道府県一覧
mysql> show fields from areaView;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| area_no   | int(11)     | NO   |     | NULL    |       |
| area_name | varchar(32) | NO   |     | NULL    |       |
| pref_no   | int(11)     | NO   |     | NULL    |       |
| pref_name | varchar(30) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> select * from areaView where area_no=3;
+---------+-----------+---------+--------------+
| area_no | area_name | pref_no | pref_name    |
+---------+-----------+---------+--------------+
|       3 | 関東      |       8 | 茨城県       |
|       3 | 関東      |       9 | 栃木県       |
|       3 | 関東      |      10 | 群馬県       |
|       3 | 関東      |      11 | 埼玉県       |
|       3 | 関東      |      12 | 千葉県       |
|       3 | 関東      |      13 | 東京都       |
|       3 | 関東      |      14 | 神奈川県     |
+---------+-----------+---------+--------------+
7 rows in set (0.01 sec)

// 地域の都道府県一覧

mysql> show fields from areaView;

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

| area_no | int(11) | NO | | NULL | |

| pref_no | int(11) | NO | | NULL | |

+-----------+-------------+------+-----+---------+-------+

4 rows in set (0.01 sec)

mysql> select * from areaView where area_no=3;

+---------+-----------+---------+--------------+

+---------+-----------+---------+--------------+

| 3 | 関東 | 8 | 茨城県 |

| 3 | 関東 | 9 | 栃木県 |

| 3 | 関東 | 10 | 群馬県 |

| 3 | 関東 | 11 | 埼玉県 |

| 3 | 関東 | 12 | 千葉県 |

| 3 | 関東 | 13 | 東京都 |

| 3 | 関東 | 14 | 神奈川県 |

+---------+-----------+---------+--------------+

7 rows in set (0.01 sec)

//エリア、都道府県 多対多中間テーブルで検索
mysql> show fields from prefareaView;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| area_name | varchar(32) | NO   |     | NULL    |       |
| pref_no   | int(11)     | NO   |     | NULL    |       |
| pref_name | varchar(30) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from prefareaView where pref_no=13;
+-----------+---------+-----------+
| area_name | pref_no | pref_name |
+-----------+---------+-----------+
| 関東      |      13 | 東京都    |
+-----------+---------+-----------+
1 row in set (0.00 sec)

//エリア、都道府県多対多中間テーブルで検索

mysql> show fields from prefareaView;

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

| pref_no | int(11) | NO | | NULL | |

+-----------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> select * from prefareaView where pref_no=13;

+-----------+---------+-----------+

| area_name | pref_no | pref_name |

+-----------+---------+-----------+

| 関東 | 13 | 東京都 |

+-----------+---------+-----------+

1 row in set (0.00 sec)

メニューに戻る

おまけに作成したビューを利用した郵便番号検索Webアプリのサンプル

Webアプリケーション郵便番号検索サンプルサイト

//サンプルWebアプリケーションサーバー側PHPスクリプト
<?php
$dsn  = 'mysql:dbname=postcode;host=localhost';
$user = "dbuser";
$password = "xxxxxxxxxxxx";
try{
    $pdo = new PDO($dsn, $user, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	if( $_REQUEST["postcode"]){
		$sql = "select * from zipView where post_code=". $_REQUEST["postcode"];
	    $stmt = $pdo->query($sql );
	    $answercity = $stmt->fetchAll(PDO::FETCH_ASSOC);
		if( count( $answercity )){
		    echo "<pre>";
		    print_r( $answercity );	    
		    	    
		    echo "</pre>";
		}
		else{
			echo "<h1>該当がありません</h1>";
		}	    
	}
	else{
		$sql = "select * from areaView where area_no=". $_REQUEST["area_no"];
	    $stmt = $pdo->query($sql );
	    $answercity = $stmt->fetchAll(PDO::FETCH_ASSOC);
	    
	    echo "<table>";
	    echo "<tr><th>地方</th><th>都道府県コード</th><th>都道府県名</th></tr>";
	    foreach( $answercity as $city ){
		    echo "<tr><td>{$city[area_name]}</td>",
		    		"<td>{$city[pref_no]}</td>",
		    		"<td>{$city[pref_name]}</td></tr>";
	    }
		echo "</table>";	    
	}
}catch (PDOException $e){
    print('Error:'.$e->getMessage());
    die();
}

//サンプルWebアプリケーションサーバー側PHPスクリプト

<?php

$dsn = 'mysql:dbname=postcode;host=localhost';

$user = "dbuser";

$password = "xxxxxxxxxxxx";

try{

$pdo = new PDO($dsn, $user, $password);

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if( $_REQUEST["postcode"]){

$sql = "select * from zipView where post_code=". $_REQUEST["postcode"];

$stmt = $pdo->query($sql );

$answercity = $stmt->fetchAll(PDO::FETCH_ASSOC);

if( count( $answercity )){

echo "<pre>";

print_r( $answercity );

echo "</pre>";

}

else{

echo "<h1>該当がありません</h1>";

}

else{

$sql = "select * from areaView where area_no=". $_REQUEST["area_no"];

$stmt = $pdo->query($sql );

$answercity = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "<table>";

echo "<tr><th>地方</th><th>都道府県コード</th><th>都道府県名</th></tr>";

foreach( $answercity as $city ){

echo "<tr><td>{$city[area_name]}</td>",

"<td>{$city[pref_no]}</td>",

"<td>{$city[pref_name]}</td></tr>";

}

echo "</table>";

}

}catch (PDOException $e){

print('Error:'.$e->getMessage());

die();

}

// jQuery側のAjax処理"
$(function(){
	var target_url = 'findZipRequest.php';
	//ボタンのクリックイベント
	$('#btn').on('click',getData);
	$('#areaselect').on('change',changefunc);
	function changefunc(){
		var area = $('option:selected').val();
		//Ajax処理
		$.ajax({
			type:'POST',
			data:{
				'area_no':area
			},
			url: target_url
		}).done(function(data, textStatus, jqXHR){
			$("#result").html(data);
		});
	}
	//Ajaxを実行するユーザー定義関数
	function getData() {
		var strpost = $("#postcode").val();
		if( !isNaN(strpost) && 
			strpost.length == 7 ){
		//ターゲットとなるファイルのURL
			var target_url = 'findZipRequest.php';
			//Ajax処理
			$.ajax({
				type:'POST',
				data:{
					'postcode':$("#postcode").val()
				},
				url: target_url
			}).done(function(data, textStatus, jqXHR){
				$("#result").html(data);
			});
		}
		else{
			alert("半角数字７桁で入力してください");
		}
	}
});

// jQuery側のAjax処理"

$(function(){

var target_url = 'findZipRequest.php';

//ボタンのクリックイベント

$('#btn').on('click',getData);

$('#areaselect').on('change',changefunc);

function changefunc(){

var area = $('option:selected').val();

//Ajax処理

$.ajax({

type:'POST',

data:{

'area_no':area

url: target_url

}).done(function(data, textStatus, jqXHR){

$("#result").html(data);

});

}

//Ajaxを実行するユーザー定義関数

function getData() {

var strpost = $("#postcode").val();

if( !isNaN(strpost) &&

strpost.length == 7 ){

//ターゲットとなるファイルのURL

var target_url = 'findZipRequest.php';

//Ajax処理

$.ajax({

type:'POST',

data:{

'postcode':$("#postcode").val()

url: target_url

}).done(function(data, textStatus, jqXHR){

$("#result").html(data);

});

}

else{

alert("半角数字７桁で入力してください");

}

});

メニューに戻る

投稿者: khagiwara
プログラミング

郵便番号データベースで多対多のリレーション

MySQLによる条件分岐とサブクエリー

Webサイトデザイン、プログラミング、サーバー構築運用の記録