[MySQL] 個人的 逆引きリファレンス
トップページへ
※普段使用している構文や関数だけなので数は少ないです
※逆引き、つまり「したいこと」から探す一覧でリンク先はなるべく公式（おもにMySQL5.6）ですが、見つからない場合は諸先輩方のサイトです
※想定と挙動が異なる場合は、MySQLのバージョン違いが原因の可能性も視野に入れて下さい。
※リンクの「記事」は当サイトの別記事です
■備忘録ページも
とくに便利な関数
文字連結 concat
文字列項目に文字列を連結していく
CONCAT(‘ABC’,’DE’,’F’)
→結果 ABCDEF
項目 work にカンマを付加
「CONCAT(work, ‘,’ , :c)」 :cは変数パラメタといった使い方も可能
→結果：abc, def, ghi, ・・・
文字の置換 replace
REPLCAE(対象,変換前文字列,後文字列)
それら以外
WHEREにつけるSELECT文→サブクエリー構文
Q.WordPressで「記事」の文字数が少ない記事を特定したい。
A. 例えば、文字数が500バイト以下で、公開中の記事の場合
SELECT * FROM 記事テーブル WHERE (length(`post_content`) < 500) and (`post_status`='publish')
使用場所：phpMyAdmin のようなデータベース管理ツール
SQLの文字列関数 length
SELECT文を使ってレコード件数を知りたい→ Count(*)
Q.PHP(楽天市場で探す ・ amazonで探す)のPDOを使った場合で、件数が知りたい
テーブルの作成、削除、インデックスの作成など→データ定義ステートメント
SELECT, INSERT, DELETE, UPDATEなど→データ操作ステートメント
データの並べ替え→ORDER BY *2つのキーを使う場合など
結果のレコードをまとめる→ GROUP BY, DISTINCT
GROUP BYの解説より：「値が選択されたあとに結果セットのソートが発生します」
SQL文で使用できる関数
今日→ NOW()
今日の一か月前→DATE_ADD( NOW(), INTERVAL -1 MONTH )
e.g. MONTH(date) など
他のサンプル
・DELETE FROM MyTable WHERE ( add_time < DATE_SUB( CURDATE(), INTERVAL 7 DAY) )
Q. 日付型（MySQL内のデータ型では datetime ）でマッチングしたい
A.とどのつまり、例 “2022/04/28 10:24” を”2022-04-28 10:24″の書式にする。
$w_a = str_replace(‘/’,’-‘,$kou_day);
// 秒を付加したい場合。秒がなくてもマッチングできる場合あり
//ex. 2022-04-28 10:24:00
$w_a .= ‘:00’;
$stmt->bindParam(‘:a’, $w_a, PDO::PARAM_STR);
Q.集計したい
A. SUM()
Q. PHP処理で、MySQLのあいまい検索をする場合の注意点
A. この例では titleをあいまい検索するとした場合、SQL文ではなく、bindParamで値（ここでは別テーブルの値 $row[‘relation’]）をセットする前に、その値を%で囲むのがポイントです。
SELECT `p_date`,`url` FROM `abc` WHERE ( `p_date` < :key3 ) and ( `title` LIKE :key )
$w_rela = ‘%’ .$row[‘rela’] .’%’;
$stmt->bindParam(‘:key’, $w_rela, PDO::PARAM_STR);
あいまい検索をして「一致しないレコード」→NOT LIKE
Q. 文字列項目で値がNULLかどうか？空かどうか？
A. =” や<>” も使える場合がありますが、IS NULL、IS NOT NULLの方がスマートかもしれません。
IS NULL、IS NOT NULL→NULL 値の操作
Q.グループ化で集約しカウント。カウントした値が３より大きいものだけを抽出し、降順で並べ替えしたい
A.サンプル
SELECT abc, count(*) as c_nt, def FROM table GROUP BY def HAVING c_nt > 3 ORDER BY c_nt DESC
*GROUP BYで集約した場合など、WHERE が使えない時は HAVING で代用できる場合があります
Q. 1つの値ではなく、2つ値のどちらかに該当するレコードが欲しい
A. IN()関数を使用
例
１つの値
WHERE parameter = 100
２つの値
WHERE parameter in (100, 200)
Q. 別テーブルから INSERT するサンプル
A.あくまでも標準的な例です 公式
MyTable2 のデータを cd順にソートしたものを MyTable1 に挿入する
INSERT INTO MyTable1(`cd`, `han_day`,`kakaku`) SELECT `cd`, `han_day`, `kakaku` FROM MyTable2 ORDER by MyTable2.cd
Q. 別テーブルの値でUPDATEするサンプル
A. 見た目は悪いです。MyTable1の kakaku に、同じ商品コードを持つ MyTable2の kakakuをセットする。
UPDATE MyTable1 as t1 SET kakaku = (SELECT kakaku FROM MyTable2 WHERE t1.cd = MyTable2.cd)
Q.数字を文字列、文字列を数字に
A.MySQLの「12.2 式評価での型変換」
文字列の付加（追加）
Q.最後から３０件目のレコード（降順にして上から３０件目のレコード）が欲しい。
A.私は LIMITとOFFSET（公式）を使います。公式の下の方にあるようにOFFSET句は明記しないものの LIMIT ?,? と数字を2つ明記します。
・公式のサンプル
SELECT * FROM tbl LIMIT ?, ?
・私のサンプル
SELECT * FROM table ORDER BY `no` DESC LIMIT 29,1;
*他の方法もあるようです
TRUNCATE TABLE の特徴
公式から抜粋
・切り捨て操作はテーブルを削除して再作成するため、特に大きなテーブルの場合は、行を 1 つずつ削除するよりはるかに高速です。
・ロールバックできません。
・ テーブル形式ファイル tbl_name.frm が有効であるかぎり、データまたはインデックスファイルが破損した場合でも、TRUNCATE TABLE を使用してテーブルを空のテーブルとして再作成できます。
・パーティション化されたテーブルで使用された場合、TRUNCATE TABLE はそのパーティション化を保持します。つまり、データおよびインデックスファイルが削除されて再作成されるのに対して、パーティション定義 (.par) ファイルは影響を受けません。
・AUTO_INCREMENT 値はすべて、その開始値にリセットされます。
Q.「TRUNCATE TABLE」でテーブル内のデータを全て削除した時、AUTO_INCREMENTの値は？
A.自動でリセットされ「1」になっていました。（公式）よって、ALTER TABLE文による値のリセットや再設定（公式の中段）は不要と思います。
●SQL文のサンプル
２つのテーブルから項目を抽出する
その１もその２もほぼ同じ内容です。この記載は「こういう記述方法もある」というもので、正解ではありません。目的のSQL文を考えるためのヒント文です。
目的
主テーブルと副テーブルをマッチングし、副テーブルでマッチングできない（主の）レコードはNULLで返す。結果に関係なく、主テーブルは全件出力したい。
その１
SELECT t1.*, (SELECT t2.syozoku FROM 副テーブル AS t2 WHERE ID = t1.ID ) as w_syozoku, (SELECT t2.name FROM 副テーブル AS t2 WHERE ID = t1.ID ) as w_name FROM 主テーブル AS t1
その２
SELECT 主テーブル.*, syozoku, name FROM 主テーブル LEFT JOIN 副テーブル ON 主テーブル.ID = 副テーブル.ID
| ID | hometown |
| 1 | fukushima |
| 2 | nagano |
| 3 | kagawa |
| 5 | tottori |
| ID | syozoku | name |
| 1 | p’office | todoroki |
| 2 | technical | saotome |
| 5 | sales | hashimoto |
結果：副テーブルにはIDの3のデータがないので、syozokuやnameはNULLとなる
SELECT LEFT JOINのサンプル
タッチで拡大します
画像の説明で上から順に
A のテーブル：replace_test
B のテーブル：replace_test_new
の時
SQL文
SELECT * FROM replace_test
LEFT JOIN replace_test_new ON replace_test.cd = replace_test_new.cd
WHERE replace_test_new.cd IS NULL
を実行した 結果(C)です。
単純な事例ですが、「CDが一致しないレコードを表示」しているだけです。
私が好む用途としては
原本テーブルと新テーブルを比較し「CDが一致しないレコード＝削除済みレコード」と判断し、DELETE文で「削除」。
その後 INSERT … ON DUPLICATE KEY UPDATEを実行し、新テーブルのレコードを追加、既存のレコードをUPDATEします。
項目の文字列を置換するSQLの文字列関数「REPLACE」のサンプル
サンプル文
UPDATE `テーブル名A` SET post_content = REPLACE(post_content, ‘tweet.in-jpn.com’, ‘in-jpn.com’)
これはWordPress内の文字列を置換したサンプルです。
正直、WordPressのテーブルを直接編集するのはオススメしませんが、あくまでもSQLのREPLACE関数の例として掲載しました。
テーブルAにある項目「post_content」の中に「tweet.in-jpn.com」が含まれていたら、それを「in-jpn.com」に置換してUPDATE文で更新しました。
含まれていなかったら、何もされません。