第9回 データベース(MySQL)

この内容、あるサイトの解説記事からパクってます。順次更新しないと…

目次

MySQLのインストール

MySQLのダウンロード

MySQLのインストール

  1. たぶん(笑)、以下のようにしてインストールできます。
    $ su
    # rpm -ivh ファイルその1.rpm
    # rpm -ivh ファイルその2.rpm
    以下同様…
  2. 設定ファイルを/etc/にコピーします。
    $ su
    # cp /usr/share/mysql/my-small.cnf /etc/my.cnf
  3. 設定ファイルを編集します。
    # vi /etc/my.cnf
  4. [mysqld]という部分に、以下の内容を書き加えます。
    [mysqld]
    default-character-set=ujis

MySQLの起動

  1. MySQLのサーバが起動しているか確認します。
    $ mysqlshow
  2. もし起動していたら、設定を有効にするためにいったん止めます。
    $ su
    # /etc/init.d/mysql stop
  3. MySQLサーバを起動します。
    # /etc/init.d/mysql start
  4. 管理ユーザー root にパスワードを設定しておきます。
    $ mysqladmin -u root password '管理パスワード'
    $ mysql_install_db

データベースの準備

データベースの作成

  1. ユーザが使うためのデータベースを作っておきます。 ここでは、データベース名を user_db としておきます。
    $ mysqladmin -u root -p create user_db
    Enter password: 管理パスワード
  2. 現在サーバ上にあるデータベースは、mysqlshow または、以下のコマンドで確認できます。
    $ mysql -u root -p
    Enter password: 管理パスワード
    
    mysql> show databases;
    +----------+
    | Database |
    +----------+
    | mysql    |
    | test     |
    | user_db  |
    +----------+
    3 rows in set (0.00 sec)
    
    mysql> exit
    Bye
  3. 次に、普段データベースを利用するための一般ユーザを作ります。 ここでは、ユーザ名を db_user とします。
    $ mysql -u root -p
    Enter password: 管理パスワード
    
    mysql> grant all on *.* to db_user@localhost identified by 'ユーザパスワード';
    mysql> flush privileges;
    mysql> exit
    Bye

データベースへの接続

  1. MySQLはサーバですので、ユーザとしてログインして利用します。作成した一般ユーザのアカウント(ユーザ名 db_user)を使います。
    $ mysql -u db_user -p
    Enter password: ユーザパスワード
    
    mysql> 
  2. 「 mysql> 」という文字を、プロンプト といい、 MySQLが命令を待っている状態です。 この後に、SQL文を打ち込み、データベースを操作します。
  3. まずは、あらかじめ作成したデータベースを選択します(データベース名はuser_db)。
    mysql> use user_db;
    Database changed
    mysql> 
  4. 以上でデータベースが操作できるようになりました。今後、「MySQLにSQL文を発行する」時は、この手順で接続しておきます。なお、データベースとの接続を切断(終了)する時は以下のようにタイプします。
    mysql> exit
    Bye

データベースの設計

郵便番号データベース

「旧番号, 新番号, 都道府県名, 市区郡町村名, 町村字名」という レコードを持つ郵便番号データベースを作成してみましょう。

  1. まず、日本全国の郵便番号データをダウンロードします。
  2. 「head yubin_euc.csv」や「tail yubin_euc.csv」というコマンドで内容を確認してみましょう。
    "907-14","907-1432","沖縄県","八重山郡竹富町","古見"
    "907-15","907-1543","沖縄県","八重山郡竹富町","崎山"
    "907-14","907-1431","沖縄県","八重山郡竹富町","高那"
    "907-11","907-1101","沖縄県","八重山郡竹富町","竹富"
    "907-14","907-1434","沖縄県","八重山郡竹富町","南風見"
    "907-14","907-1433","沖縄県","八重山郡竹富町","南風見仲"
    "907-17","907-1751","沖縄県","八重山郡竹富町","波照間"
    "907-15","907-1544","沖縄県","八重山郡竹富町","鳩間"
    "907-18","907-1800","沖縄県","八重山郡与那国町","以下に掲載がない場合"
    "907-18","907-1801","沖縄県","八重山郡与那国町","与那国"

テーブルの設計

関係データベースは、「テーブル」(表)から構成されています。テーブルには「カラム」または「フィールド」と呼ばれる縦の欄(項目)があり、ひとつひとつのデータは横一列の「レコード」(行)として並びます。

今回は以下のようなテーブルを作ります。「旧番号」「新番号」「都道府県名」などの欄がカラムであり、「060, 060-0041, 北海道, 札幌市中央区, 大通東」のようなひとまとまりのデータがレコードです。

旧番号新番号都道府県名市区郡町村名町村字名
060060-0041北海道札幌市中央区大通東
154154-0002東京都世田谷区下馬
468468-0039愛知県名古屋市天白区西入町
907-18907-1801沖縄県八重山郡与那国町与那国

テーブルの設計ではカラム(各欄)の型を定義します。「型」というのは、そのカラムに入るデータの形式のことで必ず定義します。今回は以下のようになります。テーブルの設計はデータベース構築の肝です。興味があれば、「正規化」と呼ばれるデータベースの論理設計手法を勉強してみてください。

zipcode
名称内容属性
oldzip旧番号text
newzip新番号text空欄不可
pref都道府県名text
city市区郡町村名text
addr町字名text空欄不可

SQLによるテーブル操作

SQLは標準のデータベース言語であり、 MySQLをはじめ AccessからOracleまで、ほとんどのデータベースは SQLによる命令文で動きます。

テーブルを作成する

【基本構文】 create table テーブル名 (カラム名1 カラム型1, カラム名2 カラム型2, ...);

  1. データベースにテーブルを作ります。上記の構成でテーブルを作成するには、以下のSQLコマンドをタイプします。SQL文は、セミコロン(;)を打つまでが1行と認識されます。 例のように、長い命令を途中で改行して入力することが可能です。
    mysql> use user_db;
    
    mysql> create table zipcode(
        ->   oldzip text,
        ->   newzip text not null,
        ->   pref text,
        ->   city text,
        ->   addr text not null
        -> );
    Query OK, 0 rows affected (0.00 sec)
  2. SQLでコマンドを打つことを、「SQL文を発行する」といいます。正しくテーブルが作られたか、確認してみましょう。
    mysql> show fields from zipcode;
    +---------+------+------+-----+---------+-------+
    | Field   | Type | Null | Key | Default | Extra |
    +---------+------+------+-----+---------+-------+
    | oldzip  | text | YES  |     | NULL    |       |
    | newzip  | text |      |     |         |       |
    | pref    | text | YES  |     | NULL    |       |
    | city    | text | YES  |     | NULL    |       |
    | addr    | text |      |     |         |       |
    +---------+------+------+-----+---------+-------+
    5 rows in set (0.00 sec)

(テーブルの削除のしかた)

【基本構文】 drop table テーブル名;

データを作成する - INSERT文

【基本構文】 insert into テーブル名 (カラム名1, カラム名2, ...) values (カラム値1, カラム値2, ...);

  1. テーブルにデータ(レコード)を作成するには 「インサート(INSERT)文」を使います。
    mysql> insert into zipcode
        ->   (oldzip,newzip,pref,city,addr)
        -> values('154','154-0002','東京都','世田谷区','下馬');
    
    Query OK, 1 rows affected (0.03 sec)
  2. 同様にして、以下のデータを登録してください。
    旧番号新番号都道府県名市区郡町村名町村字名
    060060-0041北海道札幌市中央区大通東
    154154-0002東京都世田谷区下馬
    468468-0039愛知県名古屋市天白区西入町
    907-18907-1801沖縄県八重山郡与那国町与那国

データを表示する - SELECT文

【基本構文】 select フィールド名 from テーブル名 ;

  1. 前項で登録したデータを表示してみます。テーブルからレコードを取り出すには、「セレクト(SELECT)文」を使います。
  2. 郵便番号と都道府県のみ選択して表示します。
    mysql> select newzip,pref from zipcode;
    +----------+--------+
    | newzip   | pref   |
    +----------+--------+
    | 060-0041 | 北海道 |
    | 154-0002 | 東京都 |
    | 468-0039 | 愛知県 |
    | 907-1801 | 沖縄県 |
    +----------+--------+
    4 rows in set (0.00 sec)
  3. すべてのフィールドを表示する場合は、*記号で代用できます。
    mysql> select * from zipcode;

データの一括投入

  1. 郵便番号データは、各県数千件のデータになるので、INSERT文での手作業での登録は現実的ではありません。
  2. そこで一気に流し込みを行ないます。ファイルは、適当な場所に yubin_euc.csv として置いておきます。
    mysql> load data infile '/置いてある場所/yubin_euc.csv'
        -> into table zipcode fields terminated by ',';
    Query OK, 121622 rows affected (0.78 sec)
    Records: 121622 Deleted: 0 Skipped: 0 Warnings: 0
    • エラーが出る場合は、yubin_euc.csv が読めるようになっているか、さらにはそれが置いてあるディレクトリが読めるようになっているか、チェックしてください。 なっていない場合には、chmod でパーミッションを与えてください。
  3. レコードの登録件数は、以下のSQLコマンドで確認できます。
    mysql> select count(*) from zipcode;
    +----------+
    | count(*) |
    +----------+
    | 121622   |
    +----------+
    1 row in set (0.00 sec)

データの検索

テーブル操作のSQL文を使ってみます。データベースの肝の部分です。 あらかじめMySQLにログインしてからデータベースを選択しておいてください。

データを検索する - WHERE句

テーブルからデータ(レコード)を検索するには 「セレクト(SELECT)文」を使います。WHERE句で条件文をしているすることにより、条件にマッチするデータのみを表示できます。

【基本構文】 select フィールド名 from テーブル名 where 条件式 ;

複数の行に分けて書くこともできます。

select フィールド名
from テーブル名
where 条件式 ; 
  1. 古い郵便番号が「468」の、すべてのフィールドを表示する。
    select * from zipcode where oldzip='468' ;
  2. 市区名が「世田谷区」の、新番号と町村名を表示する。
    select newzip,addr
    from zipcode
    where city='世田谷区' ;
  3. 以下のようにすると、条件にマッチするレコードの件数が分かります。
    select count(*)
    from zipcode
    where pref='鳥取県' ;
  4. 以下のように「リミット(LIMIT)句」を使うと、表示する件数を限定できます。 県名が「鳥取県」の、すべてのフィールドを、50件表示する。
    select *
    from zipcode
    where pref='鳥取県'
    limit 50;

ちょっと高度な検索

以下のSQLを理解し、結果を記録してください。

  1. 都道府県名が「滋賀県」 かつ 市区郡町村名が「草津市」
    select *
    from zipcode
    where pref='滋賀県' && city='草津市' ;
  2. 市区郡町村名が「渋谷区」 または 「新宿区」
    select *
    from zipcode
    where city='渋谷区' || city='新宿区' ;
  3. 市区郡町村名に「日高」が含まれる(likeは文字列比較、%はワイルドカード)
    select *
    from zipcode
    where city like '%日高%';

課題

【問1】 上記の「ちょっと高度な検索」の実行結果をまとめてください。検索結果が大量の場合には、ちゃんとできたことがわかる程度に適当な量を抜粋してください(「中略」とか「途中省略」とか)。

【問2】 下記問題を解いて、解答のSQL文と実行結果をまとめてください。検索結果が大量の場合には、ちゃんとできたことがわかる程度に適当な量を抜粋してください(「中略」とか「途中省略」とか)。

  1. 「東京都町田市」の郵便番号の一覧を検索しなさい。
  2. 「東京都町田市玉川学園」の郵便番号を検索しなさい。
  3. 旧郵便番号が「223」だった地区の住所を検索しなさい。
  4. 町字名(addr)が、「霞ヶ関」(または、自分の好きな地名何でもいい。結果が出てくるようにしてください)である地区の一覧を検索しなさい。
  5. 住所のどこかに「中田」の入っている地名の一覧を検索しなさい。

【提出】


おしまい。
今日はレポートはありません。


トップ   編集 凍結 差分 履歴 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2005-06-22 (水) 14:17:35