アナログCPU:5108843109

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

('ω') < イザユケエンジニャー

SQL

FIND_IN_SETでカンマ区切りの文字列から簡単検索

SQL

以下のような testtable テーブルについて、 カンマ区切りになっている文字列 strings を検索したい、と思うケースがあります。 あくまでカンマで区切ったひとつひとつをデータとして扱い、例えば「a」ではヒットさせたくないけど「abc」でヒットさせたい、…

PostgreSQLで配列を使いこなす

SQL

配列型、テーブル定義にはあんま使いたくないけど 複雑なクエリ書くときは便利~。以下のようなテーブル「hoge」について考えます。 type code 1 a 1 b 2 c 2 a 3 b 3 c 実テーブル作るのめんどいからとりあえず一時テーブル。 CREATE TEMPORARY TABLE "hoge…

PostgreSQLのエスケープ

SQL

めっちゃくちゃどうでもいいんですけど、 いつも何故だか「エスケープ」と「エンコード」という単語がごっちゃになる。しかも大体の場合、 エスケープについて調べたいときは 「なんだっけ…あの…エンコードじゃないやつ…」となり エンコードについて調べたい…

Postgresqlの数値文字列変換

SQL

まだうまく飲み込めてないのでなんともですが、 PostgresqlはMySQLに比べると暗黙の型変換をしてくれるところが少ないような気が。 その割に型変換するのに若干クセがあって忘れそうなので、いろいろ試してみたのをメモ。 数値→文字列 たぶん一番よく使うや…

各グループの中から、特定条件に合致するレコードを抽出する

SQL

例えば以下のようなテーブル「users」があるとして。 id type name age 1 1 Alice 17 2 1 Billy 20 3 2 Cathy 38 4 2 David 59 5 2 Elena 27 typeごとにageの最大値を求めるのは簡単です。 type age 1 20 2 59 SELECT "type", MAX("age") AS "age" FROM "use…

PostgreSQLでSQL_CALC_FOUND_ROWS的なことをしたい

SQL

MySQLだと、 SELECT SQL_CALC_FOUND_ROWS `hoge` ,`fuga` FROM `piyopiyo_table` WHERE `hoge` > 10 LIMIT 0, 5 というふうに「SQL_CALC_FOUND_ROWS」をくっつけたSELECT文を発行した直後に SELECT FOUND_ROWS(); とやってやれば、LIMITで制限されていない全…

PostgreSQLでgroup_concat的なことをする

SQL

まさかPostgreSQL触り始めた途端にgroup_concatが必要になる局面が訪れるとは思わんかった。 MySQLやってた5年間でもロクに使わんかったというのに。以下のようなテーブル names があるとして type name 1 Andley 1 Bill 2 Cathy 1 Darlton 2 Emily 以下のよ…

ざっくり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であるレコー…