アナログCPU:5108843109

ゲームと音楽とプログラミング(酒と女とロックンロールのノリで)

('ω') < まだ5月のくせにあっつい

SQL

ざっくりSQL入門 #11:トランザクション

※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。今回はトランザクションについて。 トランザクションとは 「分けることのできない複数の処理をまとめた単位」です。 (データベースに限った意味合いではありませんが…

ざっくりSQL入門 #10:一時テーブルを活用する

※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。今回は一時テーブルについて。一時テーブルとは、普段使用しているテーブルとは異なり、その名の通り、一時的に保持されるテーブルです。 作成した一時テーブルはその…

ざっくりSQL入門 #9:よく使う関数

※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。今回は、(個人的に)よく使う関数を記載しておきます。 ただし集計関数についてはこちら。

ざっくりSQL入門 #8:クエリを書く<データの削除編>

※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。今回は、DELETEやTRUNCATEの構文を用いて、レコードの削除を行う方法について。 DELETEとは 指定したレコードを削除します。 指定方法はおなじみWHERE句。 DELETE FRO…

ざっくりSQL入門 #7:クエリを書く<INSERT&UPDATE編>

※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。今回は、INSERTやUPDATEの構文を用いて、レコードの追加や更新を行う方法について。 INSERT・UPDATEとは? 新しくレコードを追加するときは「INSERT」、 既存レコード…

ざっくりSQL入門 #6:クエリを書く<サブクエリ&UNION編>

※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。今回は、サブクエリやUNIONを用いて、複数のクエリの結果を組み合わせる方法について。 サブクエリとUNION、その違いについて サブクエリは「あるSELECTクエリをテー…

順位付けを行うクエリ

SQL

例えば以下のテーブル「user」について。 name value alex 6 brigid 3 cath 9 darlton 6 elenore 4 これをvalueの高い順に並び替え、順位を付けたいとします。 rank name value 1 cath 9 2 alex 6 2 darlton 6 4 elenore 4 5 brigid 3 以下のようなクエリで…

ざっくりSQL入門 #5:クエリを書く<テーブル結合編>

※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。今回はテーブル結合のやりかたについて。 テーブル結合とは? 文字通り、二つのテーブルを結合します。ここでは例えばツイッター的なサービスを想像してみましょう。 …

ざっくりSQL入門 #4:クエリを書く<集計処理編>

※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。今回は集計系の処理について。 集計処理とは 簡単に言うと「○○ごとの○○」を集計することです。 例えば以下のようなケース。 商品テーブルについて、カテゴリごとの商…

ざっくりSQL入門 #3:クエリを書く<SELECT編>

※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。今回はSELECTクエリについて。 SELECTクエリとは データベースに入っているレコードを抜き出すクエリです。ただそれだけ。 基本の書き方 構文 SELECT 【カラム名(複…

SQLでビット演算

SQL

※MySQLを前提としています// 結局実務に使うことはなく中途半端。気が向いたら加筆します 例えば「販売商品のカテゴリを複数設定可能にしたい」という場合。 みかん(生鮮/果物) 冷凍ミカン(冷凍/果物) みかん缶詰(缶詰/果物) いわし缶詰(缶詰/魚) 冷…

結果サイズによるインデックスの効き具合

SQL

MySQL、InnoDBにて。以下のようなテーブル「test」があるとします。 カラム名 型 インデックス id int(11) PRIMARY ymd date ymd ymdにはインデックスが張ってあります。 また、レコードは約1万8千件。これに対して、以下4つのクエリのEXPLAINを比較してみま…

テーブル名を入れ替える

SQL

※MySQLを前提として書いています二つのテーブル名を入れ替えたいことがたまにあるのですが、 極力ダウンタイムを短くしたいのでいろいろ調べているとひとつのクエリで書けることが分かったのでメモ。 RENAME TABLE `テーブル名①` TO `tmp_table` ,`テーブル…

日時を扱ういろいろ MySQL版

SQL

※MySQLを前提として書いています 現在日時の取得 -- YYYY-MM-DD hh:mm:ss SELECT NOW() 月の最終日の取得 -- 2013-06-10 を渡すと 2013-06-30 SELECT LAST_DAY(日付) フォーマットして取得 SELECT DATE_FORMAT(日付, フォーマット) フォーマット部分 %Y 西暦…

文字列のトリム(左端・右端にある特定の文字列を削除 )

SQL

以下MySQLで確認していますが、Oracle・PostgreSQLでも同じ書き方である模様。例えば都道府県欄に「○○県」と入っており「県」は除いて表示したい場合など、は以下のようにします。 SELECT TRIM(オプション 削除する文字列 FROM フィールド名) FROM テーブル…

頭文字検索

SQL

※MySQLを前提として書いています 用語集みたいなやつを作っていて、「あ行」「か行」…「英数字」という頭文字検索があったので、 調べてみたところLIKEかREGEXPが使えそうかなと思ったのですが…LIKE →複雑な正規表現は使用不可 →日本語に対応REGEXP →正規表…

フィールドの順序を変更する

SQL

※MySQLを前提として書いています あるテーブルで、フィールドが「field_a」「field_b」がこの順に存在し、これを入れ替えたいときは以下のようにします。 ALTER TABLE テーブル名 MODIFY COLUMN `field_a` 型名 AFTER `field_b`;

テーブルの再構築

※MySQLを前提として書いています いつもは速いクエリが突然やたらと遅くなったので調べてみると、何故か適切なインデックスが使用されなくなっていた模様。 とりあえずインデックスを一旦削除して張り直すと戻りました。 (FORCE INDEX なんかも知ってはいま…

複雑な条件でGROUP BYする

SQL

※MySQLを前提として書いています 下記Aのテーブルについて、 「idが4未満と4以上のグループに分けて、それぞれで一番小さいvalueをSELECT」 してBの結果が欲しいとき…(A)test_table id value 1 68 2 15 3 32 4 19 5 73 6 59 (B) 15 19 苦し紛れに以下のよう…

データベースごとのサイズを調べる方法

SQL

※MySQLを前提として書いています データ部分、インデックス部分、合計について、それぞれMG単位とGB単位。 SELECT `TABLE_SCHEMA` ,ROUND(SUM(`DATA_LENGTH`) / 1024 / 1024, 2) AS `data_MB` ,ROUND(SUM(`DATA_LENGTH`) / 1024 / 1024 / 1024, 2) AS `data_…

ユニークなインデックスが存在しないテーブルを求める

SQL

※MySQLを前提として書いています 先日弊社のphpMyAdminを3.x系から4.x系にアップデートしまして 「PRIMARY/UNIQUEのいずれのインデックスも張られていないテーブルのレコードは編集/削除不可」という問題まっとうな仕様変更にぶち当たったので、 そのとき影…

特定のデータベースが存在するかどうか確認する

※MySQLを前提として書いていますクエリ一発で確認できます。 SHOW DATABASES LIKE 'DB名' DB名指定しなければ一覧取れます。 SHOW DATABASES さらに、PHPでユーザ入力値の名前のデータベースが存在するかどうか確認したかったんですが、 // クエリ生成 $quer…

複数の条件のCOUNT値を取るときのSQL速度検証

SQL

※MySQLを前提として書いていますまあ前にも似たようなことをやってるんですが 以下のテーブル「table」があるとします。 id a b 1 1 3 2 2 4 3 1 5 4 2 NULL 5 2 NULL ここから、 「aが1であるレコードの数」 「aが2であるレコードの数」 「bが3であるレコー…

2つのデータベース間の差分を求めるクエリ

SQL

※MySQLを前提として書いています本番環境と開発環境に違いがないか確認するのが面倒でテンプレクエリ作ったのでメモ。 テーブル構成の差分を求める 以下クエリで、「database_1」「database_2」を比較し、 いずれか片方にしかないカラムを取得することができ…

ランダムで取得&速度検証

SQL

※MySQLを前提として書いています`table` テーブルよりランダムで1件取得したいという場合、次のように書きます。 SELECT * FROM `table` ORDER BY RAND() LIMIT 1 ランダムなので当然なのですが、実行のたびに結果が変わります。 つまり、ページャーを用いる…

GROUP句で「n件ごと」にまとめる

SQL

※MySQLを前提として書いていますややタイトル詐欺。1000件ごとの統計情報(件数、平均値、合計値…など)を出したいことがあったので以下のようなクエリで解決。 -- ざっくり1000件ごとの件数、平均、合計、最小値、最大値 SELECT MIN(`id`) AS `id_min` ,COU…

NULLの場合に別の値を使用する

SQL

NULLを別の値に置き換える方法。 SQLServerとMySQLで異なっていたので両方記載。 -- SQLServer SELECT ISNULL(`hoge`, 0) FROM `table`; -- MySQL SELECT COALESCE(`hoge`, 0) FROM `table`; これで「tableテーブルのhoge列を取得(ただしNULLなら0とする)…

データ処理の件数を制限する

SQL

データを1件だけSelectやDeleteしたいときの書き方。SQLServerとMySQLで異なっていたので両方メモ。 -- SQLServer SELECT TOP(1) * FROM `table` ORDER BY `column`; DELETE TOP(1) FROM `table` ORDER BY `column`; -- MySQL SELECT * FROM `table` ORDER B…

重複レコードを削除

SQL

※MySQLを前提として書いています 移転前の記事で書いてたクエリがめちゃくちゃ頭悪かったので完全に書き直し。 やりたいこと テーブル `table` があるとする。 フィールドは `id`, `hoge` の2つで、`id` がプライマリキー。`hoge`の値が一意でない場合、重複…

【未解決】ワイルドカードとエスケープ

※MySQLを前提として書いています ※この記事では、MySQLのバージョンは5.6です ※以下、「\」となっているのは半角バックスラッシュです。SQLのワイルドカードには「%」と「_」があります。 -- 「hoge」で前方一致検索 SELECT * FROM `table` WHERE `column` L…