[mysql/チートシート]よく使う＆忘れがちなQueryまとめ

よく使うSQLと忘れがちなSQLをまとめておきます。
mongodbなどのnosql環境が出てきたもののまだまだ使うシーンも多いはず・・・

mysqlで動作確認して書いてますが、他の処理系も共通するところはあるかと思います。

初回のみ系

データベース作成

create database [データベース名];

例(create database)

charset(文字コード)も指定しておいたほうがベターですね

create database test_db charset utf8;

テーブル作成

create table [テーブル名] (
 カラム定義・・・・
);

例(create table)

create table user (
    id bigint not null auto_increment,
    name varchar(128),
    address varchar(256),
    birthday datetime,
    age int,
    primary key (id)
);

インデックス作成

インデックス追加

ALTER TABLE [テーブル名] ADD INDEX [インデックス名]([カラム名]);

例(add index)

ALTER TABLE user ADD INDEX idx_user_name(name);

複合インデックスを貼る場合は、カラム名をカンマで区切って渡す。

ALTER TABLE user ADD INDEX idx_user_name(id, name);

インデックス削除

例(drop index)

ALTER TABLE user DROP INDEX idx_user_name;

よく使ってる系SQL

データ追加

insert into user (カラム名, ・・・) values (値, ・・・);

例(insert)

insert into user (name, address, birthday, age) values ('山田太郎', '福岡県博多区xxx', '1989-01-01', '31');

データ検索

全件取得

select * from [テーブル名];

例(select)

select * from user;

条件で抽出

select * from [テーブル名]　where [条件文];

例(select where)

select * from user where name like '%山田%';

その他
・id = XX;
・id in (1 ,2 ,3);
・name = ‘名前’;

などなどさまざま。

データ更新

update [テーブル名] set [カラム名] = [入力値]　where [条件文];

条件文がない場合は、全カラムに同じ入力値が入る。

例(一括update)

update user set address = 'address test';

mysqlでワークベンチから上記を流すと、エラーが発生します。

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

SET SQL_SAFE_UPDATES = 0; で一時的にsafe updateを無効にしておくと効くようになります。

例(update where)

update user set name = '田中太郎' where id = 2;

データ削除

レコード削除(delete where)

delete from [テーブル名] where [条件文];

全レコード削除(delete)

delete from [テーブル名];

update時と同じでsafe updateが有効になっていると1175エラーになる。

全レコード削除(truncate)

truncate table [テーブル名];

deleteとの違いはauto incrimentの数値が元に戻る。