SQLを書くとき、多くの場合、柔軟にいくつかのSQLステートメントを書くテクニックを使用して、プログラムのロジックを大幅に簡素化することができます。プログラムとデータベース間の相互作用の数を減らし、データベースの可用性を高めることができます。
ディレクトリ
ハイテンション注意、辛口MySQL技術記事です、いつか必ず使います、収集は忘れずに！ -- 長年技術マネージャにこっぴどくやられてきたプログラマからのアドバイス
実践SQL
挿入または交換
新しいレコードを挿入したいが、そのレコードがすでに存在する場合は、元のレコードを削除してから新しいレコードを挿入します。
- **シナリオ例：**このテーブルは、各顧客の最新のトランザクションの注文情報を格納し、要件は、単一のユーザーのデータがエントリに重複していないことを確認し、最高の効率、データベースとの最小限の相互作用の実装では、データベースの高可用性をサポートするために。
この場合、"REPLACE INTO "ステートメントを使用することで、最初にクエリを実行し、挿入する前に削除するかどうかを決定する必要がなくなります。
REPLACE INTO" 文は、一意のインデックスまたは主キーに基づいて一意になります。
REPLACE INTO" 文は、一意のインデックスまたは主キーに基づいて一意です。
REPLACE INTO" 文は、一意のインデックスまたは主キーに基づいて一意です。
注意: 以下のSQLに示すように、usernameフィールドに一意なインデックスを作成し、transIdをセルフインクリメントに設定する必要があります。
-- 20
REPLACE INTO last_transaction (transId,username,amount,trans_time,remark)
VALUES (null, 'chenhaha', 30, ' ', 'メンバーシップ・チャージ');
-- 21タップしてスキンを購入する
REPLACE INTO last_transaction (transId,username,amount,trans_time,remark)
VALUES (null, 'chenhaha', 100, ' ', 'ブラインドモンク最高拳スキンを購入する');
username='chenhaha'のレコードが存在しない場合は、REPLACE文によって新しいレコードが挿入され、そうでない場合はusername='chenhaha'の現在のレコードが削除され、新しいレコードが挿入されます。
idに特定の値を指定しないでください。そうしないとSQLの実行に影響を与えます。
挿入または更新
新しいレコードを挿入したいが、既にレコードが存在する場合は更新したい場合は、"INSERT INTO ..." を使用します。 重複キーの更新..."ステートメントを使います：
- **シナリオ例：**このテーブルには、新しいデータ上の最初の再充電は、ユーザーが累積履歴再充電量に充電している場合は、個々のユーザデータがエントリ内で重複していないことを確認する必要がある場合、ユーザの履歴再充電量を格納します。
これを行うには、"INSERT INTO ..." を使用します。 を重複キーで更新..."ステートメントを使用します。
注：上記と同じ、"INSERT INTO .... を重複キーで更新..."文は、一意なインデックスまたは主キーに基づいて一意性を決定します。以下のSQLに示すように、usernameフィールドに一意インデックスを作成し、transIdをセルフインクリメントに設定するだけです。
-- ユーザーChanhahaはメンバーシップのために$ 30をチャージした
INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark)
VALUES (null, 'chenhaha', 30, ' ', ' ')
ON DUPLICATE KEY UPDATE total_amount=total_amount + 30, last_transTime=' ', last_remark =' ';
-- ユーザーchan hahaはブラインド至高拳皮のために100ドルをトッピングした
INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark)
VALUES (null, 'chenhaha', 100, ' ', 'ブラインドモンク最高拳スキンを購入する')
ON DUPLICATE KEY UPDATE total_amount=total_amount + 100, last_transTime=' ', last_remark ='ブラインドモンク最高拳スキンを購入する';
username='chenhaha'のレコードが存在しない場合、INSERT文は新しいレコードを挿入します。そうでない場合、username='chenhaha'の現在のレコードはUPDATEで指定されたフィールドで更新されます。
挿入または無視
あなたは、新しいレコードを挿入したいが、レコードがすでに存在する場合は、何も直接無視を行うには、この時点で、使用することができますINSERT IGNORE INTO ...ステートメント：多くのシナリオは、これ以上の例があります。
注：上記のように、"INSERT IGNORE INTO ..."ステートメントは、一意のインデックスまたは一意のキーを決定するために基づいている、あなたはユーザー名フィールドに一意のインデックスを作成する必要があり、transIdは、自己インクリメントすることができます設定します。
-- 最初にユーザーが
INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time)
VALUES (null, 'chenhaha', ' ', 12, 0, ' ');
-- 二次加算、直接無視される
INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time)
VALUES (null, 'chenhaha', ' ', 12, 0, ' ');
username='chenhaha'のレコードが存在しない場合、INSERT文は新しいレコードを挿入します。
SQLのif-else判定文
if-else判定がSQL文のいたるところで役に立つことはよく知られています。 THEN ... ELSE ... END "ステートメントは、追加、削除、変更のあらゆる種類のステートメントで使用できます。
- **シナリオを与える：**女性の日のギフトバック、2020年に登録された新規ユーザー、すべての成人女性のアカウントは10ドルのボーナスを取得し、他のすべてのユーザーは5ドルのボーナスを取得し、オートリチャージ。
サンプルは以下の通りです：
-- 赤い封筒の明細書を送る
UPDATE users_info u
SET u.balance = CASE WHEN u.sex =' ' and u.age > 18 THEN u.balance + 10
ELSE u.balance + 5 end
WHERE u.create_time >= ''
- シナリオ2：生徒の高校の成績表があり、成績を記載する必要があります。650以上は主要大学、600～650は小学校、500～600は中等学校、400～500は高等学校、400以下は高等学校です；
クエリ文：
SELECT *,case when total_score >= 650 THEN 'キー・ユニバーシティ'
when total_score >= 600 and total_score <650 THEN ' '
when total_score >= 500 and total_score <600 THEN ' '
when total_score >= 400 and total_score <500 THEN ' '
else ' ' end as status_student
from student_score;
データスナップショットまたはバックアップの指定
テーブルのスナップショットを取得したい場合、つまり、現在のテーブルのデータを新しいテーブルにコピーしたい場合は、CREATE TABLEとSELECTを組み合わせることができます：
-- class_id=1レコードはスナップショットされ、新しいテーブルとして保存される。students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM student WHERE class_id=1;
新しく作成されたテーブル構造はSELECTで使用されているものと同じです。
クエリ結果セットの書き込み
クエリの結果セットをテーブルに書き込む必要がある場合は、INSERTとSELECTを組み合わせて、SELECT文の結果セットを指定したテーブルに直接挿入することができます。
例えば、各クラスの平均評点を記録する統計テーブルを作成します：
CREATE TABLE statistics (
id BIGINT NOT NULL AUTO_INCREMENT,
class_id BIGINT NOT NULL,
average DOUBLE NOT NULL,
PRIMARY KEY (id)
);
そうすれば、各クラスの平均評点を1つの文章で書くことができます：
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
INSERT文の列とSELECT文の列が1対1で対応していることを確認すると、クエリの結果を統計テーブルに直接保存することができます：
SELECT * FROM statistics;
+----+----------+--------------+
| id | class_id | average |
+----+----------+--------------+
| 1 | 1 | 475.5 |
| 2 | 2 | 333 |
| 3 | 3 | 666 |
+----+----------+--------------+
3 rows in set (0.00 sec)
指定インデックスの強制使用
クエリ時に、データベースシステムは自動的にクエリステートメントを分析し、最適なインデックスを選択します。しかし、データベースシステムの照会最適化機能は必ずしも最適なインデックスを使用するとは限りません。インデックスの選択方法を知っていれば、FORCE INDEXを使用して、指定されたインデックスを使用するように照会を強制することができます。例
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;
インデックスを指定するには、インデックス idx_class_id が存在する必要があります。
洞察力
私はその年、私はまだ子供ですが、私は最初の需要が統計的なインターフェイスを行うことです覚えて、クエリほぼ2時間ごとに5分の期間のサイトへの訪問者の数は、JSONArrayは24の値の合計を返し、その後、ああ料理、インターフェイスのループ24回を書いた、チェックするために24のSQLを送信するため、そのインターフェイスのため、技術マネージャーは嘲笑された〜〜〜彼は私が米を食べたよりも多くのSQLを書いたと述べました。山東省の人々は基本的に米を食べませんが、私はまだ恥ずかしいです。
チューバの皆さん、MySQLの道は長く遠いです。この記事がお役に立てれば幸いです。
------------------------------------------------------------- Gorgeous dividing line ---------------------------------------------------------
コメント欄で問題点をフィードバック
今週はコメントエリアのコメントやプライベートメッセージでいくつかの友人があり、SQLを書く方法であることを私に尋ね、マネージャが書く方法です。知識のための皆の熱意がとても高い見て、私は少しオープンソースにする必要があり、この投稿では、その時点でSQLを実行します。
要件レビュー：時間は「2020-01-14 00:00:00」～「2020-01-15 00:00:00」で、1日のグループ化されたデータを10分間隔でカウントします。
その年のマネージャーのSQL
時間は要件に合わせて変更され、私は1日分のテストデータしか作成しなかったので、それを例として使用します。
SELECT DATE_FORMAT(p.createTime, '%H') as date_str,count(*) as count
from data_timing p
where DATE_FORMAT(p.createTime, '%Y-%m-%d %H:%i:%S') >= DATE_FORMAT(' ', '%Y-%m-%d %H:%i:%S')
AND DATE_FORMAT(p.createTime, '%Y-%m-%d %H:%i:%S') < DATE_FORMAT(' ', '%Y-%m-%d %H:%i:%S')
GROUP BY HOUR(p.createTime)
このSQLクエリの結果のマネージャーは、基本的に年の元のバージョンを保持し、次のとおりです〜〜この意味は、"2020-01-14 "この日24時間ごとの時間カウントを取ることです。仕事を終えた後、彼は子供を迎えに行かなければならないと言って、彼は去ると言いました。私は言いました： "お兄ちゃん、ああ、食事の半分を食べるのはやめましょう、私にもう少しください。"
修正最終SQL
その後、SQLを修正すると次のようになります：
SELECT concat( date_format( p.createTime, '%Y-%m-%d %H:' ) , floor( date_format( p.createTime, '%i' ) /10 )*10 ) as date_str
, count(*) as count from data_timing p
where DATE_FORMAT(p.createTime, '%Y-%m-%d %H:%i:%S') < DATE_FORMAT(' ', '%Y-%m-%d %H:%i:%S')
AND DATE_FORMAT(p.createTime, '%Y-%m-%d %H:%i:%S') >= DATE_FORMAT(' ', '%Y-%m-%d %H:%i:%S')
GROUP BY concat( date_format( p.createTime, '%Y-%m-%d %H:' ) , floor( date_format( p.createTime, '%i' ) /10 ))
一見するとかなり複雑ではありませんが、より複雑ではなく、関数の内容のほんの一部がより多くのです。例えば、山積みのconcat（）、およびdate_formatそれら。最初に結果を見てください。後であなたとクエリの原理について：
SQL最終原則
前述したように10分ごとだったので、その時点では以下のように1分ごとに時間帯をグループ化するSQLしか思いつきませんでした。
SELECT concat( date_format( p.createTime, '%Y-%m-%d %H:' ) , floor( date_format( p.createTime, '%i' ) )) as date_str
, count(*) as count from data_timing p
where DATE_FORMAT(p.createTime, '%Y-%m-%d %H:%i:%S') < DATE_FORMAT(' ', '%Y-%m-%d %H:%i:%S')
AND DATE_FORMAT(p.createTime, '%Y-%m-%d %H:%i:%S') >= DATE_FORMAT(' ', '%Y-%m-%d %H:%i:%S')
GROUP BY MINUTE(p.createTime)
クエリの結果
それを10分単位でやるには？インターネットでいろいろ調べても、あまりにも嘘が多い。また、これは不発弾ではありません書くために本当に有用な方法を見つけなかった、と私はそれについて考えました。良いアイデアを考え、ここで "yyyy-MM-dd HH:m "の特性の10分ごとに、私は関係なく、文字列の単位のために "10分 "レベルを切り出す場合は、 "1分10分」単位で文字列を切り出すと、「1分」に関係なくグループマッチができないなんて、おっと、ちょっと自画自賛してしまいました。
1分ごとにグループ化されたSQLと、上記の10分ごとにグループ化されたSQLの違いはここにあります：
分'%i'を10で割って、小数点以下を切り捨てて、グルーピングして、最終的に表示するときに、ちょっと扱うときに 表示用に"*10 **"を付けたり 、もちろん"***10 "を使わなければいいだけ で、結果は以下のようになります。少し変形して見づらいですが、使用には差し支えありません。
さて、あなたはすべてされていることを約束しました。慎重なパートナーは、あなたが0をチェックしない場合、あなたは、このデータは、どのようにそれを表示しないことがわかりますか？たとえば、午前1時以上、表示されません。これはまだ完全に構成するために背景に対処する必要がありますか？この問題は、コメント欄にメッセージを残すための良い方法がある場合は、あなたに委ねられて、私は常に古い鉄の人々のダイナミック〜〜〜に注意を払うでしょう。
