データベースについて(mysql)

質問

プログラミング経験がほとんどないのですが、最近mysqlに携わり、検索時のSQL文の記述方法で分からないことがあるので誰か教えてください。

【説明】
地域テーブル、県テーブル、町テーブル、住所テーブル、存在管理テーブルの合計５つのテーブルが存在します。
地域テーブルには地域名と地域コードのカラムがあります。
県テーブルには県名と県コードのカラムがあります。
町テーブルには町テーブルと町コードのカラムがあります。
住所テーブルには地域コード、県コード、町コードと建物の名前のカラムがあります。
存在管理テーブルには、削除フラグというカラムがあります。
ここで削除フラグは、存在しない住所になると削除フラグに１を設定し、検索時には削除フラグに１が立っているものは検索できないようにするためのものです。普段は削除フラグは０になるように設定しています。
地域と県名と町名はプルダウンで選択できるようになっています。

【質問内容】
地域のプルダウンにある「関西」を選択すると選択された地域に該当する県名や町名、建物名が検索される。
また県名、町名についても同様に検索される。
地域、県名に「関西」「大阪」選択すると「関西」「大阪」に該当する町名、建物名が検索される。
さらに建物の名前の前方一致検索もできる。
ちなみにこれを１文で書きたいです。

以上のSQL文はどのようになるでしょうか。
mysqlのバージョンは４．０です。
どなたかお教えください。

回答No.1

mpro-gram · ベストアンサー

テーブル構成の正規化がうまくいってません。地域コードとは、複数県をまとめるコードなら、県テーブル側でコードを持たせて結合すべきで、住所テーブルでいちいち設定する形にすると、データ編集時の不整合発見が面倒です。
テーブル変更例 ※　カラム型は適当にいれました
地域テーブル (地域名 varchar(255),地域コード varchar(10) primary key);
県テーブル (県名 varchar(255),県コード varchar(10) primary key ,地域コード varchar(10));
町テーブル (町名 varchar(255),町コード varchar(10) primary key ,県コード varchar(10) );
※ 県が違って、町名がかち合っても、町名コードで区別可能にする。同一県内なら名前も区別付くように付けるべき　※
住所テーブル (建物名 varchar(255) , 建物コード varchar(10) primary key, 町コード varchar(10), 削除フラグ byte not null default 0
index indname(建物名) );
※　存在管理テーブル　→　これは、住所テーブルのカラムとすべき。　住所テーブルとの連結用カラムもないようでは、管理できないし。町名も合併変更などで使われなくなったのを、削除フラグ管理する場合もそれぞれのテーブルにフラグをもうけるべきと思うが、、、
※　住所テーブルの県コード、地域コードは、町名コードから連鎖結合で検索可能。同じ町名コードなのに県や地域コードのデータに不整合があっても困る。

建物全件一覧用SQL文
select l.地域名,p.県名,t.町名 , j.建物名 , j.建物コード
from 住所テーブル as j left join 町テーブル as t on j.町コード = t.町コード
left join 県テーブル as p on p.県コード = t.県コード
left join 地域テーブル as l on l.地域コード = p.地域コード
where j.削除フラグ = 0
order by l.地域コード, p.県コード, t.町コード, j.建物名 ;
※　並び順をコード順にするか名前順（＝文字コード順）にするかは、適宜。読みのあいうえお順にしたいなら「ふりがな」カラムがそれぞれのテーブルに必要。
※　あとは、where 条件に and で、必要な条件を付ければ、検索したい情報が選択される。

Q１：地域のプルダウンにある「関西」を選択すると選択された地域に該当する県名や町名、建物名が検索される。
※県名一覧用
select p.県名 from 県テーブル as p
left join 地域テーブル as l on l.地域コード = p.地域コード
where l.地域名 = '関西'

※もしかして建物一覧に出てこない県は表示させたくない？
select distinct p.県名
from 住所テーブル as j left join 町テーブル as t on j.町コード = t.町コード
left join 県テーブル as p on p.県コード = t.県コード
left join 地域テーブル as l on l.地域コード = p.地域コード
where j.削除フラグ = 0 and l.地域名 = '関西'
order by p.県コード

建物一覧　例１
select j.建物名 , j.建物コード, t.町名, p.県名, l.地域名
from 住所テーブル as j left join 町テーブル as t on j.町コード = t.町コード
left join 県テーブル as p on p.県コード = t.県コード
left join 地域テーブル as l on l.地域コード = p.地域コード
where j.削除フラグ = 0 and l.地域名 = '関西'
order by l.地域コード, p.県コード, t町コード, j建物名 ;

Q2また県名、町名についても同様に検索される。
Q3 地域、県名に「関西」「大阪」選択すると「関西」「大阪」に該当する町名、建物名が検索される。
プログラム側で、条件文を適宜作って検索する
→　プログラム側で、条件文を適宜追加して検索する

Q4 さらに建物の名前の前方一致検索もできる。
= 比較ではなく like比較にしてワイルドカードを付ける

お礼

説明が下手にもかかわらず、回答ありがとうございました！！
それぞれ、地域、県、町に空白が存在するかのチェックを行い、その条件に従ってSQL文を発行するという形で解釈してもいいでしょうか。
DB設計については、それぞれ地域、県、町でマスメンを考えていたので、住所テーブルにコードを持たせて設計したのですが、マスメンの昨日の設計、プログラミングも時間がかかってしまったので、今後は提案してくださった内容で少しでもメンテナンスが減る設計で考えていきたいです☆

回答No.2

mpro-gram

「空白が存在するかのチェック」について
検索用select文は、left join での連結で、連結カラムの空白チェックはしてないので、住所テーブルの町コードが入ってなかったり、町テーブルに存在しないコードだったりすると、全件表示用のSQL文では、地域名、県名、町名すべてnullとして出力されます。検索条件で、地域や県、町を追加指定したときは、null データは検索されません。
よって、住所テーブルデータ登録時には、町コードもその存在をチェックして、未登録であれば、県の指定も求めて、それを町テーブルに登録するような作りが必要です。県と地域は、行数も50行以内と限られてるので、先に全行登録しておくのがよいです。
もしも、県がどの地域に属するかが変更されたときは、県テーブルの地域コードだけ変更すれば、住所テーブルにも即座に反映されます。住所テーブルの複数行を探して編集する必要がなくなるのが正規化の醍醐味。
どういった運用なのか不明ですが、建築計画みたいに町名は決まってないけど、県は決まっているような状況はあり得るのでしょうか？であれば、「町名未確定」といった町名にして、町コードを作っておくなどが良いかと思います。確定後に登録行の編集をするときも未確定で検索し易いかとも思うし。既に存在するものだけなら、町名が決まれば、県も、地域も自動的に決まりますよね。

入力の手間を減らすことを考えると、プログラムコードの方でたくさんやることは増えますが、系統的にプログラミングすると、新規用と編集用とでデータ整合性チェック部分などは少しの変更で使い回しできたりしますので、データ構成を（各コードの決め方も含めて）十分に構想を練ってみてください。

あ、削除フラグカラムのbyte型は、MySQLでは　BIT(1) でした。tinyintでも、ハードディスクの記憶容量は変わらないようですけど。フラグデータもカラム型で記憶容量が違ってくるので、何型にするかとか、defaultと、フラグ状態との値を何にするかも、一考してみる価値はあるでしょう。

お礼

回答ありがとうございました☆
参考になりました！！