Partagez plusieurs instructions SQL pratiques dans MySQL

Lors de l'écriture de SQL, vous utilisez souvent certaines compétences en rédaction d'instructions SQL pour simplifier considérablement la logique du programme. La réduction du nombre d'interactions entre le programme et la base de données est bénéfique pour la haute disponibilité de la base de données et permet également à vos compétences SQL de se démarquer auprès de vos collègues.

SQL pratique

1. Insérer ou remplacer

Si nous voulons insérer un nouveau Enregistrement (INSÉRER), mais si l'enregistrement existe déjà, supprimez d'abord l'enregistrement d'origine, puis insérez le nouvel enregistrement.

Exemple de scénario : ce tableau stocke les dernières informations sur les commandes de transaction de chaque client. Il est nécessaire de garantir que les données d'un seul utilisateur ne sont pas saisies à plusieurs reprises et que l'efficacité d'exécution est la plus élevée, avec le moins d'interaction avec la base de données. , et prend en charge la haute disponibilité de la base de données.

À ce stade, vous pouvez utiliser l'instruction "REPLACE INTO", afin de ne pas avoir à interroger d'abord puis à décider s'il faut d'abord supprimer puis insérer.

• L'instruction "REPLACE INTO" est basée sur un index unique ou une clé primaire pour déterminer l'unicité (si elle existe).

• L'instruction "REPLACE INTO" est basée sur un index unique ou une clé primaire pour déterminer l'unicité (si elle existe).

• L'instruction "REPLACE INTO" est basée sur un index unique ou une clé primaire pour déterminer l'unicité (si elle existe).

Remarque : Comme indiqué dans le SQL suivant, un index unique (Unique) doit être établi sur le champ du nom d'utilisateur et le paramètre transId peut être incrémenté.

-- 20点充值
REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) 
   VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '会员充值');
 
-- 21点买皮肤
REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) 
   VALUES (null, 'chenhaha', 100, '2020-06-11 21:00:00', '购买盲僧至高之拳皮肤');

Si l'enregistrement de username='chenhaha' n'existe pas, l'instruction REPLACE insérera un nouvel enregistrement (première recharge), sinon, l'enregistrement actuel de username='chenhaha' sera supprimé, puis un nouvel enregistrement sera inséré.

Ne donnez pas de valeur spécifique pour id, sinon cela affectera l'exécution de SQL, sauf si l'entreprise a des besoins particuliers.

2. Insérer ou mettre à jour

Si nous voulons insérer un nouvel enregistrement (INSÉRER), mais si l'enregistrement existe déjà, mettez à jour l'enregistrement, à ce moment, nous pouvons utiliser l'instruction "INSERT INTO ... ON DUPLICATE KEY UPDATE ..." :

Exemple de scénario : Ce tableau stocke le montant de recharge historique de l'utilisateur. Si l'utilisateur recharge pour la première fois, une nouvelle donnée sera enregistrée. être ajouté. Si l'utilisateur recharge, Si le montant historique de la recharge est accumulé, il est nécessaire de s'assurer que les données d'un seul utilisateur ne sont pas saisies à plusieurs reprises.

À ce stade, vous pouvez utiliser l'instruction "INSERT INTO ... ON DUPLICATE KEY UPDATE ...".

Remarque : Comme ci-dessus, l'instruction "INSERT INTO ... ON DUPLICATE KEY UPDATE ..." est basée sur un index unique ou une clé primaire pour déterminer l'unicité (si elle existe). Comme le montre le SQL suivant, un index unique (Unique) doit être établi sur le champ du nom d'utilisateur et le paramètre transId peut être incrémenté.

-- 用户陈哈哈充值了30元买会员
INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) 
   VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '充会员') 
   ON DUPLICATE KEY UPDATE  total_amount=total_amount + 30, last_transTime='2020-06-11 20:00:20', last_remark ='充会员';
 
-- 用户陈哈哈充值了100元买瞎子至高之拳皮肤
INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) 
   VALUES (null, 'chenhaha', 100, '2020-06-11 20:00:20', '购买盲僧至高之拳皮肤') 
   ON DUPLICATE KEY UPDATE total_amount=total_amount + 100, last_transTime='2020-06-11 21:00:00', last_remark ='购买盲僧至高之拳皮肤';

Si l'enregistrement avec username='chenhaha' n'existe pas, l'instruction INSERT insérera un nouvel enregistrement. Sinon, l'enregistrement actuel avec username='chenhaha' sera mis à jour et le mis à jour. les champs sont spécifiés par UPDATE.

3. Insérer ou ignorer

Si nous voulons insérer un nouvel enregistrement (INSÉRER), mais si l'enregistrement existe déjà, ignorez-le et ne faites rien. , vous pouvez utiliser l'instruction INSERT IGNORE INTO... : Il existe de nombreux scénarios, je ne leur donnerai donc aucun exemple.

Remarque : Comme ci-dessus, l'instruction "INSERT IGNORE INTO..." est basée sur un index unique ou une clé primaire pour déterminer l'unicité (si elle existe) et un index unique (Unique) doit être établi sur le champ du nom d'utilisateur. Le paramètre transId peut être incrémenté par lui-même.

-- 用户首次添加
INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) 
   VALUES (null, 'chenhaha', '男', 12, 0, '2020-06-11 20:00:20');
 
-- 二次添加，直接忽略
INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) 
   VALUES (null, 'chenhaha', '男', 12, 0, '2020-06-11 21:00:20');

Si l'enregistrement avec username='chenhaha' n'existe pas, l'instruction INSERT insérera un nouvel enregistrement, sinon aucune opération ne sera effectuée.

4. Instruction de jugement if-else en SQL

Comme nous le savons tous, le jugement if-else est utile partout, dans les instructions SQL, "CASE WHEN. .. Les instructions THEN ... ELSE ... END" peuvent être utilisées dans différents types d'instructions d'ajout, de suppression, de modification et de requête.

Donnez-moi un scénario : grande récompense pour la Journée de la femme, nouveaux utilisateurs enregistrés en 2020, tous les comptes féminins adultes recevront une enveloppe rouge de 10 yuans, et les autres utilisateurs recevront une enveloppe rouge de 5 yuans, qui sera automatiquement rechargée .

Des exemples de phrases sont les suivants :

-- 送红包语句
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 >= '2020-01-01'

* Scénario 2 : Il existe un tableau des résultats de l'examen d'entrée à l'université d'un étudiant et les notes doivent être répertoriées. Un score de 650 ou plus est une clé. université, 600-650 est un livre, 500- Un score de 600 signifie deux livres, un score de 400-500 signifie trois livres et un score collégial inférieur à 400

Les données originales du test sont les suivantes :

Instruction de requête :

SELECT *,case when total_score >= 650  THEN '重点大学' 
              when total_score >= 600 and total_score <650 THEN &#39;一本&#39;
              when total_score >= 500 and total_score <600 THEN &#39;二本&#39;
              when total_score >= 400 and total_score <500 THEN &#39;三本&#39;        
              else &#39;大专&#39; end as status_student 
              from student_score;

5.

Si vous souhaitez prendre un instantané d'une table, faites-en une copie. Pour transférer les données de la table actuelle vers une nouvelle table, vous pouvez combiner CREATE TABLE et SELECT :

-- 对class_id=1（一班）的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM student WHERE class_id=1;

La structure de table nouvellement créée est exactement la même que la structure de table utilisée par SELECT.

6. Écrire le jeu de résultats de la requête

Si le jeu de résultats de la requête doit être écrit dans la table, vous pouvez combiner INSERT et SELECT pour insérer directement le jeu de résultats de l'instruction SELECT à la table spécifiée.

Par exemple, créez un tableau de statistiques pour enregistrer le score moyen de chaque classe :

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

Ensuite, nous pouvons utiliser une instruction pour écrire le score moyen de chaque classe :

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果：

SELECT * FROM statistics;

+----+----------+--------------+
| id | class_id | average      |
+----+----------+--------------+
|  1 |        1 |        475.5 |
|  2 |        2 | 473.33333333 |
|  3 |        3 | 488.66666666 |
+----+----------+--------------+
3 rows in set (0.00 sec)

7.强制使用指定索引

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。例如：

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

指定索引的前提是索引idx_class_id必须存在。

心得体会：

MySQL路漫漫，其修远兮。永远不要眼高手低，一起加油，希望本文能对你有所帮助。

推荐教程： 《mysql教程》

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!