ITコーディネータのシュウです。

11月も残りわずかになってきました。安倍首相の決断により、衆議院が解散されました。解散については賛否両論があり、今、激しく論争が展開されていますね。世界の先がよく読めないご時世ですから、このような時は、政治のリーダーがとても大切だと思います。私も、12月14日の総選挙には、しっかりと日本のことを考えて投票するつもりです。
また、先日、高倉健さんが逝去されましたね。私のとても好きな俳優さんの一人でした。最後の映画作品「あなたへ」をテレビで見ました。とても感動しました。「健さん、ありがとう！」と思わず言いたくなりました。ご冥福を心よりお祈りいたします。

＜本日の題材＞
MySQLのINSERT

前回、MySQLについて取り上げたので、今回もMySQLのことをもう少し取り上げてみようと思います。MySQLのINSERT文には興味深い機能がありましたので、取り上げてみました。

●一括INSERT
テーブルに複数のレコードを追加する場合に、OracleやSQL Serverなどでは１行１行INSERT文を記述すると思いますが、MySQLの場合には、以下のような書式で複数のデータをまとめて登録することができます。

INSERT INTO tbl_name (col_name1, col_name2, ...)
VALUES (value1, value2, ...), (value1, value2, ...), (value1, value2, ...);

試してみます。dbsuserユーザでsampledbに接続します。
mysql –u dbsuser –p
パスワードを入力
use sampledb

前回作成した「item_mst」に、追加でデータを3行登録します。Item_mst のテーブル定義を再度確認します。
DESC item_mst;

これはOracleとの互換のためにサポートされているようです。
他にも以下の方法などでテーブルの定義を確認できるようです。
・SHOW FULL COLUMNS FROM item_mst;
・SHOW CREAT TABLE item_mst;

それでは、今回は商品として野菜を3レコード登録します。
INSERT INTO item_mst(item_cd, item_name, price) values('Y0001', 'キャベツ', 160), ('Y0002', '玉ねぎ', 200), ('Y0003', '人参', 150);

「item_mst」テーブルのレコードを確認します。
SELECT item_cd, item_name, price
FROM item_mst
ORDER BY item_cd;

新しく野菜の3行がまとめて登録されてることが確認できました。

●IGNOREオプション
主キー制約や一意性制約を設定した列には、重複した値は制約違反のため挿入することができません。エラーが返ってきます。しかし、IGNOREオプションを指定した場合はエラーを返さずに（データは挿入されず）、終了させることができます。

通常のINSERT文では重複データの登録時にはエラーが返ってきます。
INSERT INTO item_mst(item_cd, item_name, price) values('K0005', 'マンゴー', 300);

ここで、IGNOREオプションを付けてINSERTを実行します。
INSERT IGNORE INTO item_mst(item_cd, item_name, price) values('K0005', 'マンゴー', 300);

エラーを出さずに終了しました。ただエラーが発生していないだけでデータは追加されていません。確認してみます。

SELECT item_cd, item_name, price
FROM item_mst
ORDER BY item_cd;

item_cdが「K0005」は、もともとの「もも」のままであることが確認できます。

●ON DUPLICATE KEY UPDATE句
ON DUPLICATE KEY UPDATE を使うと、テーブルの PRIMARY KEY 、もしくは UNIQUE インデックスの値と、INSERT文で挿入しようとしているデータの値が異なれば INSERTを行うが、同じであれば、ON DUPLICATE KEY UPDATE句で指定した値でUPDATE を実行します。

INSERT INTO item_mst(item_cd, item_name, price) values('K0005', 'キウイ', 100)
ON DUPLICATE KEY UPDATE item_name = VALUES(item_name), price = VALUES(price);

結果を確認してみます。
SELECT item_cd, item_name, price
FROM item_mst
ORDER BY item_cd;

主キーである「item_cd」が「K0005」で一致していたので、item_name, price がON DUPLICATE KEY UPDATE句で設定された（今回はINSERT文で指定した値）で更新されていることが確認されました。
※更新する値を指定する部分でVALUES(col_name)を使用すると、引数のカラムに対して新規でデータが追加する場合に格納するはずの値を参照できます。

なお、ON DUPLICATE KEY UPDATE句を使う際には、いくつか下記のような制限はあるようです。
・テーブルに主キーか、UNIQUEインデックスがないと、ON DUPLICATE KEY UPDATE は利用できない
・キーが複数行に一致する場合、MySQL は1行のみ更新する
・ON DUPLICATE KEY UPDATE を使用する場合は DELAYED オプションは無視される

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

最近は、朝晩はとても冷え込んできました。そろそろ暖房器具を出している方も多いようです。風邪を引かないように注意しないと。。。

＜本日の題材＞
MySQLとdbSheetClient

今回は、ちょっと大きく内容を変えて、MySQLについて取り上げたいと思います。 MySQLは、世界でもっとも普及しているオープンソース・データベースとして知られていて、ウィキペディアの情報では、市場シェアでは他のオープンソース・データベースを圧倒しているとのこと。
元はスウェーデンの「MySQL AB」という企業が開発していましたが、2008年2月26日にサン・マイクロシステムズに買収され、さらに2010年1月27日、サン・マイクロシステムズがオラクルに買収されたため、現在はOracleの製品ですね。
今まであまり試したことはなかったのですが、そのMySQLを、私のほうで普段仕事で使用しているdbSheetClientというツールで使えるということなので、ちょっと簡単に試してみました。

MySQLをテストのためWindows仮想サーバにインストールした直後、サンプル用のデータベースを作成：

MySQLの対話型インターフェースで、OracleのSQL*Plusのようなツールがあるので、それを使ってみます。

まず、rootユーザで接続します。
mysql –u root –p
(※MySQLではユーザーをユーザー名＋接続ホスト名で管理していますので、本来はユーザー名には「root@localhost」と指定する必要があるのですが、ホスト名が「localhost」の場合に限って省略することが可能)
そうすると、パスワードを聞いてきますので、インストール時に設定したパスワードを入力します。

接続できました。

次に、サンプル用のデータベース「sampledb」を作成します。
Create database sampled;

データベースを確認してみると
Show databases;

Sampledbが作成されたのが確認できます。

ユーザを確認してみると
SELECT user, host FROM mysql.user;

rootユーザのみです。
dbSheetClientでアクセスして使うためのユーザ dbsuser (sampledbへのすべての権限(ALL)を付与)を作成：
GRANT ALL ON sampledb.* TO dbsuser@localhost IDENTIFIED BY ‘passwd’;

(CREATE USER文でもユーザは作成できますが、上記のGRANT文にすると権限を与える文でユーザを作成することが可能です) 簡単にデータベース、ユーザの作成ができました。

さて、普段業務で使用しているExcelシートをそのまま利用しつつ、データはデータベース化して管理し、複数のメンバーで共有しながらセキュリティ対策も可能なシステムがわりと簡単に作成できるツールがあればいいと思いませんか？
ここで、私が普段使用しているツールで、上記で説明したようなシステムを作成できるツールとして、dbSheetClientというものがあるのですが、それを使ってMySQLのデータを扱えるという内容を、今回は紹介してみたいと思います。

dbsuserでログインしてから、データベース「sampledb」に接続します。
use sampledb;

テーブル「item_mst」を作成します。

CREATE TABLE item_mst(
item_cd VARCHAR(10)
, item_name VARCHAR(20)
, price int
, DBS_STATUS VARCHAR(2) default '1'
,DBS_CREATE_USER VARCHAR(60) default 'SYSTEM'
,DBS_CREATE_DATE VARCHAR(30)
, DBS_UPDATE_USER VARCHAR(60) default 'SYSTEM'
, DBS_UPDATE_DATE VARCHAR(30)
, CONSTRAINT PK_item_mst PRIMARY KEY (item_cd));

(DBS_STATUS以降の項目は、dbSheetClientの制御用フィールドになり、更新をdbSheetの機能で行う際には必要な項目となります)

さて、dbSheetClientというツールを起動して、データベースの設定を行います。開発版を起動して、「DB」の設定のところで、下記のように MySQLの設定をします。

その後、「IOTG」というdbSheetClientの機能を使うと、先ほどのテーブル「item_mst」をEXCEL上に読込んだり、更新したりできるプログラムを簡単に作成できます。

その後、作成したプログラムをメニューから起動して、「読込」ボタンを押下します。最初はデータは1件もないので、データは表示されません。

では、この画面で、EXCEL上から項目 item_cd, item_name, price の値を5件ほど入力してみましょう。りんご、みかん、なし、かき、ももとそれぞれデータを入力します。その後、「更新」ボタンを押して更新を実行します。

OKを押して更新を行うと、MySQLデータベース上のデータが更新できます。 MySQL側でデータを確認：

select item_cd, item_name, price
from item_mst
order by item_cd;

MySQL側でExcelで登録したデータが確認できました。

通常、dbSheetClientは商用のＤＢであるOracle、SQL Serverを利用されることが多いですが、オープン・ソースデータベースであるMySQLについても対応しているようですね。 dbSheetClientの詳細については、以下のＨＰを確認してみてください http://www.newcom07.jp/dbsheetclient/index.html

今日は以上まで

にほんブログ村