[MySQL] 個人的 逆引きリファレンス

※普段使用している構文や関数だけなので数は少ないです

※逆引き、つまり「したいこと」から探す一覧でリンク先はなるべく公式(おもにMySQL5.6)ですが、見つからない場合は諸先輩方のサイトです

※想定と挙動が異なる場合は、MySQLのバージョン違いが原因の可能性も視野に入れて下さい。

※リンクの「記事」は当サイトの別記事です

 

 

WHEREにつけるSELECT文→サブクエリー構文

関数や演算子

データ型

 

テーブルの作成、削除、インデックスの作成など→データ定義ステートメント

SELECT, INSERT, DELETE, UPDATEなど→データ操作ステートメント

データの並べ替え→ORDER BY *2つのキーを使う場合など

日付の計算

 

結果のレコードをまとめる→ GROUP BYDISTINCT

GROUP BYの解説より:「値が選択されたあとに結果セットのソートが発生します」

 

 

SQL文で使用できる関数

今日→ NOW()

今日の一か月前→DATE_ADD( NOW(), INTERVAL -1 MONTH )

日付および時間関数

 

他のサンプル

・DELETE FROM MyTable WHERE ( add_time < DATE_SUBCURDATE(), 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()

 

あいまい検索をして「一致しないレコード」→NOT LIKE

 

Q.「TRUNCATE TABLE」でテーブル内のデータを全て削除した時、AUTO_INCREMENTの値は?

A.自動でリセットされ「1」になっていました。(公式)よって、ALTER TABLE文による値のリセットや再設定(公式の中段)は不要と思います。

 

 

Q. 文字列項目で値がNULLかどうか?空かどうか?

A. =” や<>” も使える場合がありますが、IS NULL、IS NOT NULLの方がスマートかもしれません。

IS NULL、IS NOT NULL→NULL 値の操作

 

Q.グループ化で集約しカウント。カウントした値が3より大きいものだけを抽出し、降順で並べ替えしたい

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()関数を使用

1つの値

WHERE parameter = 100

2つの値

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)

 

 

TRUNCATE TABLE の特徴

公式から抜粋

・切り捨て操作はテーブルを削除して再作成するため、特に大きなテーブルの場合は、行を 1 つずつ削除するよりはるかに高速です。

・ロールバックできません。

・ テーブル形式ファイル tbl_name.frm が有効であるかぎり、データまたはインデックスファイルが破損した場合でも、TRUNCATE TABLE を使用してテーブルを空のテーブルとして再作成できます。

・AUTO_INCREMENT 値はすべて、その開始値にリセットされます。

・パーティション化されたテーブルで使用された場合、TRUNCATE TABLE はそのパーティション化を保持します。つまり、データおよびインデックスファイルが削除されて再作成されるのに対して、パーティション定義 (.par) ファイルは影響を受けません。

 

●SQL文のサンプル

2つのテーブルから項目を抽出する

その1もその2もほぼ同じ内容です。この記載は「こういう記述方法もある」というもので、正解ではありません。目的のSQL文を考えるためのヒント文です。

 

目的

主テーブルと副テーブルをマッチングし、副テーブルでマッチングできない(主の)レコードはNULLで返す。結果に関係なく、主テーブルは全件出力したい。

その1

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

その2

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のサンプル

タッチで拡大します

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します。

この記事のQRコード
[ MySQL 関係の記事 ]へもどる


10633
amazonでMySQLと検索した結果
*該当しない場合があります