MySQLについて　四

<<<back

MySQLについて　四

<<< MySQLについて　参

レコードの追加と削除

　テーブルができたら後はレコード（データ・アイテム）をぶち込んでいきましょう。
　データの挿入はinsertコマンドを使います。
　構文は、

insert into テーブル名 values (値1, 値2...);

あるいは、特定のフィールド名を指定して、

insert into テーブル名 (フィールド名1, フィールド名2...) values (値1, 値2...);

とします。
　web_tbテーブルに新規にレコードを挿入し、その内容を確認してみましょう。

insert into web_tb values('', 'MySQL徹底入門: ウェブに最適な高速フリー・データベース・サーバー', '日本MySQLユーザ会', '翔泳社', '2001', '373p.', '4881359770', '\3200+税', '', 'CD-ROM付き');

　ここでは、シリーズ名はnull値としていますが、idはauto_incrementですので、null値のままで自動的に数値がふられます。

mysql> insert into web_tb values('', 'MySQL徹底入門: ウェブに最適な高速フリー・データベース・サーバー', '日本MySQLユーザ会', '翔泳社', '2001', '373p.', '4881359770', '\3200+税', '', 'CD-ROM付き');[Enter Key]
Query OK, 1 row affected (0.00 sec)
mysql> select * from web_tb;

+----+-------------------------------------------------------------+ | id | title 　 | +----+-------------------------------------------------------------+ | 1 | MySQL徹底入門: ウェブに最適な高速フリー・データベース・サーバー | +----+-------------------------------------------------------------+ -------------------+-----------+------+-------+------------+ author | publisher | py | page | isbn | -------------------+-----------+------+-------+------------+ 日本MySQLユーザ会 | 翔泳社 | 2001 | 373p. | 4881359770 | -------------------+-----------+------+-------+------------+ ---------+--------+------------+ price | series | note | ---------+--------+------------+ 3200+税 | | CD-ROM付き | ---------+--------+------------+
1 row in set (0.00 sec)

　レコードを削除するのはdeleteコマンドです。

delete from web_tb where id=1;

とすれば入力したばかりのレコードは削除されます。

mysql> delete from web_tb where id=1;[Enter Key]
Query OK, 1 row affected (0.00 sec)
mysql> select * from web_tb;[Enter Key]
Empty set (0.00 sec)
mysql>

　レコードの更新（変更）を行うのはupdateコマンドです。
　レコード1のauthorフィールドに、監修者であるとみたまさひろ氏とSoft Agencyを加えてみます。
　ここでは、著者、編者、訳者などの責任表示が複数存在する場合は、;（セミコロン）で区切ることにします。

update web_tb set author='日本MySQLユーザ会; とみたまさひろ; Soft Agency' where id=1;

と入力した後に、selectコマンドでどのように変わったか確認しておきましょう。

mysql> update web_tb set author='日本MySQLユーザ会; とみたまさひろ; Soft Agency' where id=1;[Enter Key]
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select author from web_tb;[Enter Key]

+------------------------------------------------+ | author | +------------------------------------------------+ | 日本MySQLユーザ会; とみたまさひろ; Soft Agency | +------------------------------------------------+
1 row in set (0.00 sec)
mysql>

　insertとupdateの両方の機能を兼ね備えたreplaceコマンドもあります。
　replaceは、データの挿入を行いながら、重複するレコードが存在すれば上書きしていきます。
　構文は、

replace into テーブル名 values (値1, 値2...);

あるいは、 replace into テーブル名 (フィールド1, フィールド2...) values (値1, 値2...);

です。
　また、insert, replaceの際に、select文の結果を挿入することも可能です。
　構文は、

insert into テーブル名 (フィールド名) value(値) select文;

replace into テーブル名 (フィールド名) value(値) select文;

などとなります。

　レコードの挿入・削除・更新に関するコマンドを以下に示しておきます。

操作内容	構文
レコードの追加	insert into [table] values('[value1]', '[value2]'...);
レコードの追加フィールドの指定	insert into [table] ([field1], [field2]...) values('[value1]', '[value2]'...);
レコードの追加 select文の併用	insert into [table] values('[value1]', '[value2]'...) select...;
レコードの追加・置換	replace into [table] values('[value1]', '[value2]'...);
レコードの追加・置換フィールドの指定	repalce into [table] ([field1], [field2]...) values('[value1]', '[value2]'...);
レコードの追加・置換 select文の併用	replace into [table] values('[value1]', '[value2]'...) select...;
レコードの更新	update [table] set [field]='[value]' where [field]='[value]';
レコードの削除	delete from [table] where [field]='[value]';

ファイルの読み込み

　レコードを入力するのに、パコパコと一件ずつ入力していたのでは日が暮れてしまいます。
　通常は、別にデータ・ファイルを作っておいて、あるいは、既存のデータ・ファイルからテーブルにレコードを一気に読み込むことの方が多いでしょう。
　そのための方法として、load data infile文があります。
　基本構文は、

load data infile 'ファイル名' into table テーブル名 fields terminated by '区切り文字' lines terminated by '改行記号';

です。
　load data infile文を実行するには、読み込まれるべきファイルがMySQLサーバのあるマシンと同じマシン上になければなりません。
　ファイル名はフルパスで指定する必要がありますが、面倒であれば、当該テーブルが格納されているのと同じディレクトリ内（この場合は、c:\mysql\data\web_db）にファイルを置いておけば、ファイル名だけ入力すればOKです。
　区切り文字はデフォルトではタブ区切りとなっていますので、それ以外の区切り文字を使用する場合は、field terminated byオプションで指定して下さい。
　また、改行記号はデフォルトでは\n（LF）となっています。
　Windowsの改行文字は\r\n（CRLF）ですので、必ず、

line terminated by '\r\n'

と指定しておかなければなりません。

　ここでは、data.txtというファイルを読み込みます。
　このファイルは大東文化大学図書館の所蔵レコードの一部を整形して抜き出していただいたものです。
　一部、データが壊れていたりします。

　個人による学習・教育・研究のための利用の場合のみ複製を許諾いたしますが、それ以外の目的での利用や、有償・無償を問わず再頒布に関してはお断りしていますのでヨロシク。

　このファイルにはカンマ区切りの6,595件の書誌レコードが書き込まれています。
　但し、さきほど設計したweb_tbテーブルとは構造が異なりますので、ここでは、改めて、bib_tbというテーブルを作成しました。
　bib_tbテーブルの構造とフィールドの定義は以下の通りとしました。

フィールド名	データ型	備考
id	int	ID番号, 主キー, not null, auto_increment
title	varchar(255)	タイトル, not null
author	varchar(255)	著者名
pp	varchar(50)	出版地
publisher	varchar(50)	出版者
py	int	出版年
page	varchar(50)	ページ数
subject	varchar(255)	件名
ndc	varchar(50)	NDC分類記号

　次に、この設計図通りbib_tbテーブルを作成します。

create table bib_tb (id int unsigned auto_increment not null primary key, title varchar(255) not null, author varchar(255), pp varchar(50), publisher varchar(50), py int, page varchar(50), subject varchar(255), ndc varchar(50));

　ここでは、idフィールドをunsignedのint型にしています。
　unsignedがついた場合のint型は、4バイトの正の整数（0～4294967295）となります。
　また、create tableの段階で、idにauto_incrementとprimary keyを指定しています。

　後はload data infile文を使ってデータを読み込むだけです。
　ここでは、読み込むテーブルと同じ場所にあるファイル（c:\mysql\data\web_db\data.txt）を読み込みますので、フルパスではなくファイル名のみを指定すればＯＫです。
　コマンドは、

load data infile 'data.txt' into table bib_tb fields terminated by ',' lines terminated by '\r\n';

となります。

mysql> load data infile 'data.txt' into table bib_tb fields terminated by ',' lines terminated by '\r\n';[Enter Key]
Query OK, 6595 rows affected (0.20 sec)
Records: 6595 Deleted: 0 Skipped: 0 Warnings: 6639
mysql>

　これでデータが読み込まれました。
　警告が6639も出てしまいましたが、あまり気にしないでおきましょう。
　ちなみに、外部データ・ファイルの読み込みはload data infile文だけではなく、mysqlimportコマンドでも可能です。
　bib_tbテーブルに何件のレコードが読み込まれているのかを確認するには、

select count(*) from bib_tb;

と入力します。
　するとcount関数が実行され、件数が表示されます。

mysql> select count(*) from bib_tb;
+----------+ | count(*) | +----------+ | 6595 | +----------+
1 row in set (0.00 sec)

ファイルへの書き出し

　一方、読み込みがあれば書き出しもあります。
　書き出しはselectコマンドを使います。
　構文は以下の通りです。

select フィールド名 into outfile 'ファイル名' fields terminated by '区切り文字' lines terminated by '改行記号' from テーブル名 where フィールド名='値';

　ここで、検索条件指定のwhere フィールド名='値'は、完全一致の場合ですが、部分一致とする場合は、like演算子を用いて、 where フィールド名 like '%値%'

とします。
　また、出力ファイルはMySQLサーバのあるマシンに書き出されます。
　とくにパスを指定しなければ、やはり、当該テーブルを持つデータベースのディレクトリにファイルが作成されます（この場合は、c:\mysql\data\web_db）。
　例えば、authorフィールド中に部分文字列'池内'を含むレコードを検索して、それを出力ファイル（output.txt）に書き出すならば、以下のように入力すればＯＫです。

select * into outfile 'output.txt' fields terminated by ',' lines terminated by '\r\n' from bib_tb where author like '%池内%';

mysql> select * into outfile 'output.txt' fields terminated by ',' lines terminated by '\r\n' from bib_tb where author like '%池内%';[Enter Key]
Query OK, 5 rows affected (0.00 sec)

mysql>

　全部で5件が検索されました。
　出力されたoutput.txtの中身を確認しておきましょう。

MySQLについて　伍 >>>