課題への挑戦

インストールほやほやのDBで遊ぶ。
実にためになるサイトが♪勉強させていただきますー
http://shinjuku-vipper.cocolog-nifty.com/blog/2008/03/mysql_7a04.html

取り込むCSVを取り込み先データベースディレクトリ以下に配置。

$ ls -la /opt/local/var/db/mysql5/hoge/ | grep csv
-rw-r--r--   1 root    _mysql       156  1 15 22:53 code.csv
-rw-r--r--   1 root    _mysql        72  1 15 22:53 master.csv

以下の通りSQLを実行。

$ mysql5 -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.67 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database hoge;
Query OK, 1 row affected (0.00 sec)

mysql> use hoge;
Database changed

mysql> create table hoge.master( 1st varchar(10), 2nd varchar(10), 3rd varchar(10) );
Query OK, 0 rows affected (0.00 sec)

mysql> create table hoge.code( 1st varchar(10), 2nd varchar(10), 3rd varchar(10) );
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_hoge |
+----------------+
| code           | 
| master         | 
+----------------+
2 rows in set (0.00 sec)

mysql> show columns from master;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| 1st   | varchar(10) | YES  |     | NULL    |       | 
| 2nd   | varchar(10) | YES  |     | NULL    |       | 
| 3rd   | varchar(10) | YES  |     | NULL    |       | 
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show columns from code;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| 1st   | varchar(10) | YES  |     | NULL    |       | 
| 2nd   | varchar(10) | YES  |     | NULL    |       | 
| 3rd   | varchar(10) | YES  |     | NULL    |       | 
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> load data infile 'master.csv' into table hoge.master fields terminated by ',';
Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data infile 'code.csv' into table hoge.code fields terminated by ',';
Query OK, 6 rows affected, 6 warnings (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 6

mysql> select * from master m, code c where m.1st = c.1st into outfile "result.csv" fields terminated by ','; 
Query OK, 6 rows affected (0.00 sec)

mysql> exit
Bye

$ sudo cat /opt/local/var/db/mysql5/hoge/result.csv
111,ggg,hhh,111,test1,test_test
123,aaa,bbb,123,test2,test_test
123,ccc,ddd,123,test2,test_test
123,eee,fff,123,test2,test_test
456,iii,jjj,456,test4,test_test
789,kkk,mmm,789,test6,test_test

PostgreSQLのコピー文での書き方も試したいのと、各種DBDで言語から接続できるようにならないとだ。