はじめに

（裏技は）ないです。ただのメモです。
長過ぎるので右側の見出しとかにジャンプできる機能使ってください。

前準備

mysql> use test

(testという名前のDBを使用するという宣言)

mysql> create table samplep (id int primary key, name VARCHAR(20), age int);

(table作成は後述するが、とりあえずサンプル用にsampleというテーブルを作る。)

行の操作

1.テーブルに対して1行追加するパターン

insert samplep values(1,"negipoyoc",18);

当然ながら：
・テーブル定義の絡むと対応(同一の順番、個数)
・何も入力したくない時はNULLが入れられる。
・Insert Intoとしてもよい。
・insert samplep (id,name,age) values(2,"cst",18);でも同じことしてる。(カラム名の指定)
・文字列は"でも'でも許される。

2.複数行追加するパターン

insert samplep values (3,'tohno',24), (4,'tadokoro',24);
・values () , () のようにデータのまとまりごとに括弧で分けてコロンで分離する。

3.重複挿入エラー無視のInsert

主キーやUniqキーでは重複した要素の挿入が禁止されている。
そこでIgnoreオプションを挟むと挿入自体が行われなくなる。

insert ignore into samplep values(4,'tadano',45);

→Query OK, 0 rows affected (0.00 sec)

※普通にやったらエラーが出るだけだしいいじゃないかという気持ちになるし実際にその通り。

※こういうSQL文を.sqlなどに書いてまとめて発行した時にいちいちエラーが出るとうるさい。
そういうエラーを防ぐ目的でこういうオプションが存在する。

4.追加できなければ更新

追加するデータの主キーやUniqキーが重なっている場合追加せず更新する
→追加しないのは重複で存在できるデータではないから。

insert samplep values(4,"go",15) on duplicate key update name = "suzuki",age = 32  ;

これは主キー(id)の4が被ってしまっているので、テーブルにあったデータをtadokoro->suzuki , age=24-> 32に変更している。

※注意
・values()の中身はInsertしたいものを書く（当たり前）
・もしvalues()で指定した主キー(もしくはuniqキー)で、既にテーブル上にあった場合
　→そのデータに対してupdate以下の値をその被った主キーのデータに対して上書きする。

・ややこしいけど、主キーが被ってたら意味を成すもの。
・主キーが被るとvalues()の中身が意味なくなる。その場合on duplicate key update以下のものに意味が出てくる。
・values()を全部書くのはもし被ってなかったらそのままInsertできるから

・個人的に理解が難しかった割に使いみちあるのかなと思ったけど、誰かがデータベースを変更してた時にエラーを出さず更新できるという利点がある。

行の置き換え

テーブルに対して1行置き換えを行う。

Replace samplep values (4,'tadokoro',24); 

さっきの4,"suzuki",32を4,tadokoro,24に直した。

※注意
・やってることはDELETE->INSERTなので、どちらの権限も必要となる。

値の更新

テーブル中の値を更新する。

update samplep set age = age *2 where name like "%t%";

set句でどのように更新するかを決める。,で区切って複数回一気に更新することも可能。
ここでは、nameにtが含まれてるユーザのageを2倍にする文を発行した。

※注意点：
・where句を使わないとすべてのデータに対してSETが適用される。（100万レコードとかに対してはあまりやらない）
・LIMIT Nをつけると先頭から指定されたN行が更新されるのでそれを使う。

補足：

mysql起動時に

$ mysql --safe-updates

とやると、UPDATEやDELETEでWhere句がないときに更新、削除を禁止させることができて安全になる。
my.cnfに書くと良さそうだけどどうなんですかね？

テーブル操作

テーブル作成

CREATE TABLE

mysql> create table table1(id int primary key, str varchar(20), lang varchar(29));

カラムは,で区切って作成する。

・create table if not exists table1 ~~~とするともしデータベースがなかったら作るという保険を効かせながらテーブルを作るみたいなことをする。

他のテーブル定義をパクる

create table table2 select * from table1;

テーブルの削除

Drop table

> drop table table2;

・例によってif existsというオプションが使える。スクリプトからsqlをいじる用ですね。

テーブルの変更

・リネーム

alter table table1 rename to  RenamedTable 

table1からRenamedTableに名前を変更している。

・定義追加

alter table RenamedTable add (email varchar(30));

RenamedTableというテーブルにemailという新たなカラムを付加している。

・定義の変更

alter table RenamedTable modify lang varchar(30)

langカラムの型を変えてる。

・括弧いらん。
→RDBMSによって違いがある。
( http://www.i2kt.com/dbms/sqlref/ddl/sql_01020104.html )

・定義の削除

alter table RenamedTable drop email

RenamedTableというテーブルからemailというカラムを削除する。

テーブル作成時のオプション

・NOT NULL
→NULLを許容しない。

・PRIMARY KEY
→主キー

・UNIQUE KEY
→ユニークキー/Uniqキー
・主キーとは違い、NULLを許容する。
・かつ、表の NULL 以外の各行を一意に制約する

・FOREIGN KEY
→外部キー

・DEFAULT (value)
→そのカラムに対して何も入力がなかった場合に（value)を採用するというオプション

トランザクション

トランザクションができる条件

InnoDB,BerkelyDB・・・トランザクションがサポートされてる
ISAM,MyISAM,Heap,Marge・・・サポートされてない。
どのエンジンを使ってるかは
show table statusで見られる。

autocommitの無効化

切っておかないと検証ができないので切る。
set autocommit=0;
これもmy.cnfに書けば良さそう。

トランザクション処理について

START TRANSACTIONでスタート
↓
いくつか処理を書く→ROLLBACK;ここまでのトランザクション処理を取り消す。
↓
COMMIT;これでトランザクション処理を確定

デッドロックの回避方法

・トランザクション開始時に更新対象の行すべてのロックを獲得して他のトランザクションにロックをさせないようにする。
・例えばidの小さい順で更新するようにするなどのルールを統一する。
・SELECT ~~　FOR UPDATEを使ってSelect文発行に付随してWriteLockをかける。