<<住所の新規登録：addr_new.txt>>
-- 1: 住所の存在確認(SELECT:count)
SELECT count(*) AS S_1 FROM m_juusho WHERE m_prefectures_id=5 AND shikuchouson_cd=201 AND ooaza_cd=338 AND aza_cd=1;
SELECT count(*) AS S_2 FROM m_juusho WHERE m_prefectures_id=5 AND shikuchouson_cd=201 AND ooaza_cd=338 AND aza_cd=2;
SELECT count(*) AS S_3 FROM m_juusho WHERE m_prefectures_id=5 AND shikuchouson_cd=201 AND ooaza_cd=338 AND aza_cd=3;
SELECT count(*) AS S_4 FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=201 AND ooaza_cd=172 AND aza_cd=1;
SELECT count(*) AS S_5 FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=201 AND ooaza_cd=172 AND aza_cd=2;
SELECT count(*) AS S_6 FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=201 AND ooaza_cd=172 AND aza_cd=3;
SELECT count(*) AS S_7 FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=201 AND ooaza_cd=174 AND aza_cd=1;
SELECT count(*) AS S_8 FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=201 AND ooaza_cd=174 AND aza_cd=2;
SELECT count(*) AS S_9 FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=201 AND ooaza_cd=174 AND aza_cd=3;
SELECT count(*) AS S_10 FROM m_juusho WHERE m_prefectures_id=12 AND shikuchouson_cd=208 AND ooaza_cd=85 AND aza_cd=1;
SELECT count(*) AS S_11 FROM m_juusho WHERE m_prefectures_id=12 AND shikuchouson_cd=208 AND ooaza_cd=85 AND aza_cd=2;
SELECT count(*) AS S_12 FROM m_juusho WHERE m_prefectures_id=12 AND shikuchouson_cd=208 AND ooaza_cd=85 AND aza_cd=3;
SELECT count(*) AS S_13 FROM m_juusho WHERE m_prefectures_id=24 AND shikuchouson_cd=201 AND ooaza_cd=289 AND aza_cd=1;
SELECT count(*) AS S_14 FROM m_juusho WHERE m_prefectures_id=24 AND shikuchouson_cd=201 AND ooaza_cd=289 AND aza_cd=2;
SELECT count(*) AS S_15 FROM m_juusho WHERE m_prefectures_id=24 AND shikuchouson_cd=201 AND ooaza_cd=289 AND aza_cd=3;
SELECT count(*) AS S_16 FROM m_juusho WHERE m_prefectures_id=24 AND shikuchouson_cd=201 AND ooaza_cd=289 AND aza_cd=4;
SELECT count(*) AS S_17 FROM m_juusho WHERE m_prefectures_id=24 AND shikuchouson_cd=201 AND ooaza_cd=289 AND aza_cd=5;
SELECT count(*) AS S_18 FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=1;
SELECT count(*) AS S_19 FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=2;
SELECT count(*) AS S_20 FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=3;
SELECT count(*) AS S_21 FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=4;
SELECT count(*) AS S_22 FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=5;
SELECT count(*) AS S_23 FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=6;
SELECT count(*) AS S_24 FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=7;
SELECT count(*) AS S_25 FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=8;

-- 2: 住所の検索＆表示(SELECT)
SELECT * FROM m_juusho WHERE m_prefectures_id=5 AND shikuchouson_cd=201 AND ooaza_cd=338 AND aza_cd=1;
SELECT * FROM m_juusho WHERE m_prefectures_id=5 AND shikuchouson_cd=201 AND ooaza_cd=338 AND aza_cd=2;
SELECT * FROM m_juusho WHERE m_prefectures_id=5 AND shikuchouson_cd=201 AND ooaza_cd=338 AND aza_cd=3;
SELECT * FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=201 AND ooaza_cd=172 AND aza_cd=1;
SELECT * FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=201 AND ooaza_cd=172 AND aza_cd=2;
SELECT * FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=201 AND ooaza_cd=172 AND aza_cd=3;
SELECT * FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=201 AND ooaza_cd=174 AND aza_cd=1;
SELECT * FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=201 AND ooaza_cd=174 AND aza_cd=2;
SELECT * FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=201 AND ooaza_cd=174 AND aza_cd=3;
SELECT * FROM m_juusho WHERE m_prefectures_id=12 AND shikuchouson_cd=208 AND ooaza_cd=85 AND aza_cd=1;
SELECT * FROM m_juusho WHERE m_prefectures_id=12 AND shikuchouson_cd=208 AND ooaza_cd=85 AND aza_cd=2;
SELECT * FROM m_juusho WHERE m_prefectures_id=12 AND shikuchouson_cd=208 AND ooaza_cd=85 AND aza_cd=3;
SELECT * FROM m_juusho WHERE m_prefectures_id=24 AND shikuchouson_cd=201 AND ooaza_cd=289 AND aza_cd=1;
SELECT * FROM m_juusho WHERE m_prefectures_id=24 AND shikuchouson_cd=201 AND ooaza_cd=289 AND aza_cd=2;
SELECT * FROM m_juusho WHERE m_prefectures_id=24 AND shikuchouson_cd=201 AND ooaza_cd=289 AND aza_cd=3;
SELECT * FROM m_juusho WHERE m_prefectures_id=24 AND shikuchouson_cd=201 AND ooaza_cd=289 AND aza_cd=4;
SELECT * FROM m_juusho WHERE m_prefectures_id=24 AND shikuchouson_cd=201 AND ooaza_cd=289 AND aza_cd=5;
SELECT * FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=1;
SELECT * FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=2;
SELECT * FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=3;
SELECT * FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=4;
SELECT * FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=5;
SELECT * FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=6;
SELECT * FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=7;
SELECT * FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=231 AND ooaza_cd=12 AND aza_cd=8;

-- 3: 住所の新規追加(INSERT INTO)
INSERT INTO m_juusho(zipcode, m_prefectures_id, shikuchouson_cd, ooaza_cd, aza_cd, shikuchouson, ooaza, aza, ken) VALUES 
('0101420',5,201,338,1,'秋田市','大住南','１丁目','秋田県'),
('0101420',5,201,338,2,'秋田市','大住南','２丁目','秋田県'),
('0101420',5,201,338,3,'秋田市','大住南','３丁目','秋田県'),
('3501159',11,201,172,1,'川越市','中台','１丁目','埼玉県'),
('3501159',11,201,172,2,'川越市','中台','２丁目','埼玉県'),
('3501159',11,201,172,3,'川越市','中台','３丁目','埼玉県'),
('3501150',11,201,174,1,'川越市','中台南','１丁目','埼玉県'),
('3501150',11,201,174,2,'川越市','中台南','２丁目','埼玉県'),
('3501150',11,201,174,3,'川越市','中台南','３丁目','埼玉県'),
('2780057',12,208,85,1,'野田市','光葉町','１丁目','千葉県'),
('2780057',12,208,85,2,'野田市','光葉町','２丁目','千葉県'),
('2780057',12,208,85,3,'野田市','光葉町','３丁目','千葉県'),
('5142222',24,201,289,1,'津市','豊が丘','１丁目','三重県'),
('5142222',24,201,289,2,'津市','豊が丘','２丁目','三重県'),
('5142222',24,201,289,3,'津市','豊が丘','３丁目','三重県'),
('5142222',24,201,289,4,'津市','豊が丘','４丁目','三重県'),
('5142222',24,201,289,5,'津市','豊が丘','５丁目','三重県'),
('8111203',40,231,12,1,'那珂川市','片縄北','１丁目','福岡県'),
('8111203',40,231,12,2,'那珂川市','片縄北','２丁目','福岡県'),
('8111203',40,231,12,3,'那珂川市','片縄北','３丁目','福岡県'),
('8111203',40,231,12,4,'那珂川市','片縄北','４丁目','福岡県'),
('8111203',40,231,12,5,'那珂川市','片縄北','５丁目','福岡県'),
('8111203',40,231,12,6,'那珂川市','片縄北','６丁目','福岡県'),
('8111203',40,231,12,7,'那珂川市','片縄北','７丁目','福岡県'),
('8111203',40,231,12,8,'那珂川市','片縄北','８丁目','福岡県');


<<住所の更新：addr_renew.txt>>
-- 1: 更新住所の確認(SELECT:count)
SELECT count(*) AS S_1 FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=208 AND ooaza_cd=12 AND aza_cd=101;
SELECT count(*) AS S_2 FROM m_juusho WHERE m_prefectures_id=17 AND shikuchouson_cd=209 AND ooaza_cd=32 AND aza_cd=101;
SELECT count(*) AS S_3 FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=6 AND aza_cd=101;
SELECT count(*) AS S_4 FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=25 AND aza_cd=1;
SELECT count(*) AS S_5 FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=25 AND aza_cd=2;
SELECT count(*) AS S_6 FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=25 AND aza_cd=3;
SELECT count(*) AS S_7 FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=25 AND aza_cd=4;

-- 2: 更新住所の検索＆表示(SELECT)
SELECT * FROM m_juusho WHERE m_prefectures_id=11 AND shikuchouson_cd=208 AND ooaza_cd=12 AND aza_cd=101;
SELECT * FROM m_juusho WHERE m_prefectures_id=17 AND shikuchouson_cd=209 AND ooaza_cd=32 AND aza_cd=101;
SELECT * FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=6 AND aza_cd=101;
SELECT * FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=25 AND aza_cd=1;
SELECT * FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=25 AND aza_cd=2;
SELECT * FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=25 AND aza_cd=3;
SELECT * FROM m_juusho WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=25 AND aza_cd=4;

-- 3: 更新住所のUPDATE
UPDATE m_juusho SET Choson='大字亀ケ谷',Aza='字亀寿',PostCD
='3590014
' WHERE m_prefectures_id=11 AND shikuchouson_cd=208 AND ooaza_cd=12 AND aza_cd=101;
UPDATE m_juusho SET Choson='浜北',Aza='壱号',PostCD
='9291174
' WHERE m_prefectures_id=17 AND shikuchouson_cd=209 AND ooaza_cd=32 AND aza_cd=101;
UPDATE m_juusho SET Choson='大字酒殿',Aza='字老ノ木',PostCD
='8112303
' WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=6 AND aza_cd=101;
UPDATE m_juusho SET Choson='戸原北',Aza='１丁目',PostCD
='8112322
' WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=25 AND aza_cd=1;
UPDATE m_juusho SET Choson='戸原北',Aza='２丁目',PostCD
='8112322
' WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=25 AND aza_cd=2;
UPDATE m_juusho SET Choson='戸原北',Aza='３丁目',PostCD
='8112322
' WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=25 AND aza_cd=3;
UPDATE m_juusho SET Choson='戸原北',Aza='４丁目',PostCD
='8112322' WHERE m_prefectures_id=40 AND shikuchouson_cd=349 AND ooaza_cd=25 AND aza_cd=4;