MySQL/dbonline/insert

Last-modified: 2010-10-22 (金) 11:46:41

INSERT関連

データの挿入

　・特定のカラムだけにデータを挿入するなら　

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

　・すべてのカラムにデータを指定するなら、省略記法が使える

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

　・複数データを挿入するなら、VALUESを複数指定するだけ

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

　※複数データ挿入の際、VALUES( (挿入データ) ); のように、
　　VALUESの外側を括ってしまうとエラーになるので注意　　

　※use db_nameしておけば、（ｒｙ

　※VALUESの中で、value1, value2,の代わりにカラム名を用いると、
　　カラム名の値を抽出することが可能。

　　例）

　　mysql> insert into seiseki values(82, 73,
　　　　-> suugaku + eigo, round((suugaku + eigo) / 2));

　　数学と英語の成績を入れた後、それらを参照して、
　　合計得点と平均点を出している。

INSERT IGNORE ～ を使うと、挿入時のエラーを無視するが、使わないだろう

DUPLICATE KEY UPDATEは、ユニークキーが重複した場合、

　無理やり、データを上書きしてしまうものだが使うだろうか？

デフォルトの参照

　INSERTの省略記法を使うとき、「そこはデフォルト値でおｋ」
　というとき、明示的に値を指定すると、間違える可能性がある。
　そこで、defalut(カラム名)という関数を使うとよい

　例）

　mysql> insert into seiseki
　　　-> values(3, '井上', default(goukaku), 76, 69, 89, 83, 70);

　この用途では、defaultだけでもＯＫ（カラムの位置から判断してくれる）
　カラムの場所を間違えないという意味で、こちらのほうがいいかも。

　mysql> insert into seiseki
　　　-> values(3, '井上', default, 76, 69, 89, 83, 70);

他のテーブルのデータを追加 

　INSERT INTO tbl_name (col_name1, col_name2, ...) SELECT ...;

　データの追加法であって、カラムの追加法ではない
　したがって、SELECT文で指定するカラム数は、格納するテーブルのカラム数と等しくなければならない

UPDATE関連

where句やLike句、group by句などはSELECT関連の項目もチェック

データの更新（UPDATE）

書式 

　　UPDATE db_name.tbl_name SET col_name1=expr1 [, col_name2=expr2 ...]
　 　 [WHERE where_condition];

　　例）'Apple'のcountを5プラス
　　mysql> update uriage set count = count + 5 where name = 'Apple';

　　※where以降を忘れると、当該カラムが全部変わってしまうので注意

指定した行数だけ処理

　　LIMITの後、行数を入れると、指定した行数分しか実行しない

　　UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2 ...]
　 　 WHERE where_condition
　 　 LIMIT row_count;

昇順または降順に処理（ORDER BY) 

　　UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2 ...]
　　 WHERE where_condition
　　 ORDER BY col_name [ASC | DESC];

　　※ASCとDESCの指定を省略した場合は、昇順ASCになる。

　　※注意：データが昇順に更新されるのではない！
　　　　　　内部的に昇順で処理するか、降順で処理するか
　　　　　　ということ。

　　　データの処理が終わった後は、元通りの順番でカラムに格納される

　　・用途としては、where文において、LIMIT句と併せて使うことが多く、
　　　LIMIT 5などと組み合わせて、「小さいほうから５番目まで処理しろ」
　　　という使い方をする。

　　・その他、1->2, 2->3, 3->4というデータの更新をしたいときに、
　　　1->2にしたときにユニーク制約に引っかかるといったケースで
　　　降順で処理させるために使ったりもする。
　　　（3->4, 2->3, 1->2の順でやれば、ユニーク制約に反しない）

削除関連

データの削除 

DELETE FROM db_name.tbl_name [WHERE where_condition];

　※where以降を忘れると、全部消えてしまう
　※deleteの場合もlimitやorder byを一緒に使うことができる
　　deleteの場合は、order byはlimitと一緒に使うぐらいしか用途がない

TRUNCATE TABLE tbl_name;

　　DELETE from tbl_nameとの違いは、
　　１）deleteは１行ずつ消すが、truncateは一括で消すため高速
　　２）deleteは消すだけだが、truncateはテーブルを作り直す
　　　　→AUTO_INCREMENTはtruncateは再び１から始まるが、deleteは値が残っている
　　３）外部参照テーブルの場合の扱いは、InnoDBとMyISAMの場合で異なる
　　　　http://www.dbonline.jp/mysql/insert/index12.html
　　　　を参照すべし

SELECT関連

selectで特定のカラムを抽出するとき、

　select (suugaku, eigo) from seiseki;
　のように( )でくくるとエラーになるので注意
　select suugaku, eigo from seiseki;でおｋ

データの取得

　通常は、

SELECT col_name1, col_name2, ... FROM tbl_name;

　が基本

　カラム名は複数回指定することも可能
　mysql> select id, name, id, old from personal;

　　カラム名は複数回指定することも可能
　　mysql> select id, name, id, old from personal;

エイリアス名 

　　SELECT col_name AS alias_name, ... FROM tbl_name;

　　とすると、抽出後の表では、col_nameの代わりにalias_nameが使われる。
　　計算をcol_nameに入れて、alias_nameでわかりやすく表示したり、
　　計算結果をorder by alias_nameで並べ替えたりするのに使う

　　しかし、WHERE句中で使うことはできないらしい

WHERE句

　　
　　特記することはないが、日付などに対しても使える
　
　　例）
　　mysql> select * from products where releasedate >= '2009-01-01';
　
　　BETWEENを使うと比較演算子がスッキリする
　　WHERE col_name NOT BETWEEN min AND max
　
　　INを使うと、論理演算子がスッキリする

　　SELECT * FROM personal WHERE pref IN('Tokyo', 'Chiba', 'Kanagawa');

　　　→NOT INも便利でよく使う

　　この辺りは、以前の比較演算子を参照

サブクエリ＋where 

　SELECT col_name1 FROM tbl_name1
　 WHERE col_name1 = (SELECT col_name2 FROM tbl_name2);

　ただし、取得するデータは１個かつ１カラムである必要があり、
　よく、LIMIT 1と併せて使われる

　※UPDATEの中でも同様に使える

どれかにヒットすればＯＫ（ANY） 

　SELECT col_name1 FROM tbl_name1
　 WHERE col_name1 = ANY (SELECT col_name2 FROM tbl_name2);

　カラムは同じく１つだが、データは複数指定できる

すべてと比較する（ALL） 

　SELECT col_name1 FROM tbl_name1
　 WHERE col_name1 = ALL (SELECT col_name2 FROM tbl_name2);

　「すべてと等しくなる」はあり得ないので、
　<>（等しくない）とか <= とかの比較演算子を使うことが多い

データが存在すればTRUE（EXISTS） 

　SELECT col_name1 FROM tbl_name1
　 WHERE EXISTS (SELECT * FROM tbl_name2 WHERE ...);

　存在するかを判断するだけなので、何かを比較したりということはない。
　（EXISTSの中のselect文でwhereを使うことは可能）

LIKE句

　　パターンにマッチさせる方法

　　例）
　　SELECT * FROM books WHERE name LIKE '%java%';

　　ワイルドカードは以下のように指定する

　　% 任意の0文字以上の文字列

　　_ 任意の1文字

　　応用例）

　　___ 3文字の任意の文字列

　　a__b aで始まりbで終わる4文字の文字列

　　a%b aで始まりbで終わる任意の長さの文字列

　　a_%b aで始まりbで終わる3文字以上の任意の長さの文字列

エスケープ

　　エスケープする処理をするには

　　\%

　　のように￥を付ける

　　WHERE col_name LIKE pattern ESCAPE 'escape_char'

　　をつかうと、\以外をエスケープ文字に指定できる

　　また、MySQLは大文字と小文字を区別しないので、
　　区別したい場合は、バイナリで比較する

　　WHERE col_name LIKE BINARY pattern

正規表現(REGEXP / RLIKE句)

　　REGEXPとRLIKEは同じコマンド（別名）。正規表現が使える。

　　例）

　　SELECT * FROM menulist WHERE menu REGEXP '^(Hot|Ice)';

　　「menu」カラムの値の先頭が'hot' か 'ice'から始まるデータを取得
　　先頭にないHotやIceにはヒットしない
　　

同じカラムをグループ化して処理する（group by） 

　　SELECT AVG(price) FROM goodslist GROUP BY category;

　　とすると、同じcategoryカラムを持つ値をまとめて、その平均をとる。
　　たとえば、categoryに米、酒、菓子という種類があったら、
　　米のデータの平均値、酒のデータの平均値、菓子のデータの平均値の３つのデータとして出力される

　　※GROUP BYはソートにも使われる

　　SELECT col_name, ... FROM tbl_name ORDER BY col_name [ASC | DESC], ... ;

group byをさらに絞り込む（HAVING） 

　　SELECT col_name, ... FROM tbl_name
　　 GROUP BY col_name, ... HAVING where_condition;

　　WHEREは、WHERE　→　GROUP BYの順で効くが、
　　HAVINGは、GROUP BY → HAVINGの順で効く

LIMIT句

　　
　　UPDATEや、DELETEでもLIMITは使われていたが、
　　SELECTでは、オフセットを指定することも可能である。
　　※オフセットは0～

　　SELECT col_name, ... FROM tbl_name LIMIT [offset,] row_count;

　　例）
　　SELECT * FROM orderdata LIMIT 3, 10;
　　４番目のデータから10個取得

重複行を除外してselect（DISTINCT） 

　　SELECT DISTINCT col_name, ... FROM tbl_name;

取得データの結合（UNION） 

　SELECT col_name1, ... FROM tbl_name1
　 UNION [ALL | DISTINCT] SELECT col_name2, ... FROM tbl_name2
　 UNION [ALL | DISTINCT] SELECT col_name3, ... FROM tbl_name3;

　単純に縦につなげる。
　したがって、col_name1,...とcol_name2,...とcol_name3,...は、
　カラムの数が同じである必要がある。

　※表示されるときのカラム名は、col_name1,...に一致する

　※UNIONした結果に対して、ORDER BYやLIMITも併せて使えるが、
　　単純に末尾につけると、最後のSELECT文にだけ
　　ORDER BYとかが効いてしまうので、（　）で括って以下のようにする。

　(SELECT col_name1, ... FROM tbl_name1)
　 UNION (SELECT col_name2, ... FROM tbl_name2)
　 UNION (SELECT col_name3, ... FROM tbl_name3)
　 ORDER BY col_name [ASC | DESC], ...
　 LIMIT [offset,] row_count;

テーブルデータの結合

内部結合(INNER）

　テーブルのカラムの値が等しいところをつなげて、１つのテーブルとして出力
　※一致するものがなかった行は、出力されない

　具体例は以下
　http://www.dbonline.jp/mysql/select/index14.html

　SELECT table_name.col_name, ... FROM tbl_name1
　 INNER JOIN tbl_name2 ON table_name1.col_name1 = table_name2.col_name2;

　例）

　SELECT * FROM goods INNER JOIN cate ON goods.cateid = cate.id;

外部結合（LEFT/RIGHT JOIN） 

　SELECT table_name.col_name, ... FROM tbl_name1
　 LEFT/RIGHT JOIN tbl_name2

　　ON table_name1.col_name1 = table_name2.col_name2;

　内部結合との違いは、テーブルのカラムが一致しなかった部分も
　テーブルのデータとして出力すること。

　一致しなかった部分は、メインとなるテーブルをそのままコピーし、不明な部分をNULLとするが、
　どちらのテーブルをメインとするかが、LEFT/RIGHTが分かれている
　(FROMの後のテーブルならLEFT, JOINの後のテーブルならRIGHT）

自然結合

　どのカラム同士を結合するかを指定しなくても、
　２つのテーブルで同じカラム名が入っていた場合に自動で結合する

　SELECT table_name.col_name, ... FROM tbl_name1
　 NATURAL LEFT JOIN tbl_name2;

自分自身のテーブルのカラム同士で結合する場合

　新しい文法ではなく、既存の内容の応用。

　・書き方

　　SELECT alias1.col_name, ... FROM tbl_name1 AS alias1
　 　 INNER JOIN tbl_name2 AS alias2
　 　 ON alias1.col_name1 = alias2.col_name2;

　　自分自身を２回参照するので、別名を使う

　・使い方
　　あるテーブルにid, name, parentとあり、
　　parentがidに相当する場合に、parentのidを名前に置換するなど
　
　
　
　
　
　
　

　

}}