第２章　MySQLデータベースの作成　（前半）

①MySQLにロードするデータを作成
■Excel、AccessからデータをCSVにエクスポート
　　　　テスト用データとして都道府県コード表をExcelにて作成して、CSVにエクスポートしました。
　　　　フィールド名が日本語と英語の２種類を作成しましたが、
　　　　これは、この後で各種のプログラムにおける"日本語"の取扱いについての違いを知ってもらう為です。
　　　　結論から言うと、データベース名・フィールド名は英数半角にした方が良いです。

　図2-1　CSVファイルの作成
図2-1
　　
②SQLのコーディング、TEXTファイルにて保存　【英語版】
　 1)　CREATE DATABASE　　 TEST_DBという名前のデータベースを作成
net start mysql
mysql -u root -p
SHOW DATABASES;
SET CHARACTER SET SJIS;
CREATE DATABASE TEST_DB;
SHOW DATABASES; 　　

　 2)　CREATE TABLE　　 T_Prefectureという名前のテーブルを作成
USE TEST_DB;
DROP TABLE IF EXISTS T_Prefecture;
CREATE TABLE T_Prefecture (
C_Prefecture DECIMAL(2) NOT NULL ,
N_Prefecture VARCHAR(10) NOT NULL ,
N_PrefectualCapital VARCHAR(10) NOT NULL ,
Date_Regisrated DATETIME ,
PRIMARY KEY (C_Prefecture)
) ENGINE=InnoDB DEFAULT CHARSET=SJIS; 　　

　 3)　LOAD DATA　　 テーブルにデータ格納
　　　　　　　　　　　　　　　事前にD:\MySQL\data\Test_Db\ に
　　　　　　　　　　　　　　　ロードするデータPrefecture.csv を複写しておく
USE TEST_DB;
DELETE FROM T_Prefecture;
LOAD DATA INFILE "Prefecture.csv"
INTO TABLE T_Prefecture FIELDS TERMINATED BY ","
LINES TERMINATED BY "\r\n";
DESCRIBE TEST_DB.T_Prefecture; 　　

③SQLのコーディング、TEXTファイルにて保存　【日本語版】
　 内容は英語版のテーブル名とフィールド名を日本語としたものです。
USE TEST_DB;
DROP TABLE IF EXISTS T_都道府県;
CREATE TABLE T_都道府県 (
都道府県コード DECIMAL(2) NOT NULL ,
都道府県名 VARCHAR(10) NOT NULL ,
県庁所在地 VARCHAR(10) NOT NULL ,
登録更新日時 DATETIME ,
PRIMARY KEY (都道府県コード)
) ENGINE=InnoDB DEFAULT CHARSET=SJIS;

DELETE FROM T_都道府県;
LOAD DATA INFILE "Prefecture.csv"
INTO TABLE T_都道府県 FIELDS TERMINATED BY ","
LINES TERMINATED BY "\r\n"; 　　

④ユーザを作成してデータベースに権限付与
　 今回はテストの為全権限(ALL)を付与しました。
　　 ユーザ名はuser03、パスワードはpassword03としました。
　　 このユーザ名とパスワードはこの後各種のプログラムで使用します。
GRANT ALL ON TEST_DB.* TO user03@localhost IDENTIFIED BY 'password03';

SELECT USER FROM mysql.user;
SHOW GRANTS FOR user03@localhost; 　　

⑤データベースの作成
■MySQLコンソール（CLI）を使用して、DB、TABLE作成。CSVデータをロード。
　 事前に作成しておいたSQLのテキストファイルを開き、
　 コマンドプロンプト画面に貼付けてSQLを実行します。
　 ；(セミコロン)までを１つの実行単位として下さい。
　　
　図2-2　MySQLコンソール（CLI）によるDB、TABLE、USERの作成
図2-2
　　

　 ■以下は実行結果のLogです（英語版と日本語版）
【英語版】（太字部分がテキストファイルから貼り付けた所です）

※注意：テキストファイルからDOSプロンプトのコンソールにSQL文を貼り付ける際、欲張って長い行を一度に貼り付けると、テキストが崩れてしまい、Syntax Errorになるので、10行程度に区切って、数回に分けて貼り付けて下さい

mysql> SHOW DATABASES;
+---------------------+
| Database　　　　　　|
+---------------------+
| information_schema　|
| equipment　　　　　 |
| mysql　　　　　　　 |
| test　　　　　　　　|
+---------------------+
4 rows in set (0.07 sec)
mysql> SET CHARACTER SET SJIS;
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE DATABASE TEST_DB;
Query OK, 1 row affected (0.04 sec)

mysql> SHOW DATABASES;
+---------------------+
| Database　　　　　　|
+---------------------+
| information_schema　|
| equipment　　　　　 |
| mysql　　　　　　　 |
| test　　　　　　　　|
| test_db　　　　　　 |
+---------------------+
5 rows in set (0.01 sec)

mysql> USE TEST_DB;
Database changed

mysql> DROP TABLE IF EXISTS T_Prefecture;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CREATE TABLE T_Prefecture (
　　-> C_Prefecture DECIMAL(2) NOT NULL ,
　　-> N_Prefecture VARCHAR(10) NOT NULL ,
　　-> N_PrefectualCapital VARCHAR(10) NOT NULL ,
　　-> Date_Regisrated DATETIME ,
　　-> PRIMARY KEY (C_Prefecture)
　　-> )　 ENGINE=InnoDB DEFAULT CHARSET=SJIS;
Query OK, 0 rows affected (0.61 sec)

mysql> DELETE FROM T_Prefecture;
Query OK, 0 rows affected (0.07 sec)

mysql> LOAD DATA INFILE "Prefecture.csv"
　　-> INTO TABLE T_Prefecture FIELDS TERMINATED BY ","
　　-> LINES TERMINATED BY "\r\n";
Query OK, 47 rows affected (0.48 sec)
Records: 47 Deleted: 0 Skipped: 0 Warnings: 0

mysql> SELECT * FROM TEST_DB.T_Prefecture;
+--------------+--------------+---------------------+---------------+
| C_Prefecture | N_Prefecture | N_PrefectualCapital | Date_Regisrated |
+--------------+--------------+---------------------+---------------+
| 1 | kCｹ | Dy | 2009-01-01 14:23:00 |
| 2 | ﾂXｧ | ﾂX | 2009-01-01 14:23:00 |
| 3 | ｧ | ｷｪ | 2009-01-01 14:23:00 |
| 4 | {ｧ | | 2009-01-01 14:23:00 |
| 5 | Hcｧ | Hc | 2009-01-01 14:23:00 |

★この後の行は省略しました★

mysql> GRANT ALL ON TEST_DB.* TO user03@localhost IDENTIFIED BY 'password03';
Query OK, 0 rows affected (0.15 sec)

mysql> SELECT USER FROM mysql.user;
+--------+
| user |
+--------+
| root |
| user03 |
+--------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR user03@localhost;
+-------------------------------------------------------------------+
| Grants for user03@localhost
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user03'@'localhost' IDENTIFIED BY PASSWORD '*C832685DD0
D5288076724F36696AE4A280C64139' |
| GRANT ALL PRIVILEGES ON `test_db`.* TO 'user03'@'localhost'
+-------------------------------------------------------------------+
3 rows in set (0.00 sec)
 　　

【日本語版】（太字部分がテキストファイルから貼り付けた所です）

mysql> DROP TABLE IF EXISTS T_都道府県;
ERROR 1300 (HY000): Invalid utf8 character string: '都道・

mysql> SET CHARACTER SET SJIS;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS T_都道府県;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T_都道府県 (
　　-> 都道府県コード DECIMAL(2) NOT NULL ,
　　-> 都道府県名 VARCHAR(10) NOT NULL ,
　　-> 県庁所在地 VARCHAR(10) NOT NULL ,
　　-> 登録更新日時 DATETIME ,
　　-> PRIMARY KEY (都道府県コード)
　　-> 　)　 ENGINE=InnoDB DEFAULT CHARSET=SJIS;
Query OK, 0 rows affected (0.20 sec)

mysql> DELETE FROM T_都道府県;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE "Prefecture.csv"
　　-> INTO TABLE T_都道府県 FIELDS TERMINATED BY ","
　　-> LINES TERMINATED BY "\r\n";
Query OK, 47 rows affected (0.12 sec)
Records: 47 Deleted: 0 Skipped: 0 Warnings: 0

mysql> SELECT * FROM TEST_DB.T_都道府県;
+---------------------------+-------------------------+------------+
| 都道府県コード | 都道府県名 | 県庁所在地 | 登録更新日時 |
+---------------------------+-------------------------+------------+
| 1 | kCｹ | Dy | 2009-01-01 14:23:00 |
| 2 | ﾂXｧ | ﾂX | 2009-01-01 14:23:00 |
| 3 | ｧ | ｷｪ | 2009-01-01 14:23:00 |
| 4 | {ｧ | | 2009-01-01 14:23:00 |
| 5 | Hcｧ | Hc | 2009-01-01 14:23:00 |

★この後の行は省略しました★
 　　

第２章後半へ


ページ終