Plan du site  
pixel
pixel

Articles - Étudiants SUPINFO

The fundamentals of SQL : part 2

Par Maxime FEVRIER Publié le 06/02/2019 à 19:12:58 Noter cet article:
(0 votes)
Avis favorable du comité de lecture

Introduction

In a first article, we could see an introduction of SQL with an example table to make different with this one. In addition, we also saw two main commands, the SELECT command and the INSERT INTO command that will serve you to select and insert datasets into a table.

To continue in this direction, I will present in this article two other commands. At first I will show you how to update a dataset then, in a second step, I will show you how to delete data from a table. To do this, we will reuse the example table of the first article.

The UPDATE command

Highly used in the world of SQL, the UPDATE command will allow you to make changes to your tables in a very fast and easy way. This command is very often associated with other commands that we can see later.

UPDATE table_name SET column_name = new_value;

First of all, to modify a column in a general way on our table then we will use the UPDATE command associated with the SET command which will serve you to define the new value of our column.

Let's take an example with our table above. For example, to modify the "profession" column and set all the values it contains to "Other", we will use the following command :

Table 1. UPDATE users SET profession = 'Other';

id firstname lastname email country city profession
1 John Doe john.doe@test.com Etats-Unis Houston Other
2 Gerd Muller gerd.muller@test.com Allemagne Berlin Other
3 Gordon Banks gordon.banks@test.com Angleterre Londres Other
4 Lionel Messi lionel.messi@test.com Argentine Buenos Aires Other
5 Just Fontaine just.fontaine@test.com France Paris Other
6 Franck Beckenbauer franck.beckenbauer@test.com Allemagne Berlin Other
7 Michel Platini michel.platini@test.com France Paris Other
8 Chuck Norris chuck.norris@test.com Etats-Unis Ryan Other
9 Kylian Mbappe kylian.mbappe@test.com France Bondy Other
10 Cristiano Ronaldo cristiano.ronaldo@test.com Portugal Lisbonne Other
11 Neymar Santos neymar.santos@test.com Bresil Rio de Janeiro Other
12 Emmanuel Neuer emmanuel.neuer@test.com Allemagne Berlin Other


As you cn see from the table above, the profession has thus changed from the previous value, which was defined either on "Footballer" or "Actor", to thus pass to the value "Other" thanks to this simple command.

This command is will used to redefine all the values of the same column but it can be interesting also to modify only a certain data of our table, it is what we will see thereafter.

UPDATE table_name SET column_name = new_value WHERE id = value;

The WHERE condition will permit you to redefine the value of a particular data item on our table. For example, with this command you will be able to redifine the profession "Footballer" for the id which will be equal to 4. The result will then be the following :

Table 2. UPDATE users SET profession = 'Footballer' WHERE id = '4';

id firstname lastname email country city profession
1 John Doe john.doe@test.com Etats-Unis Houston Other
2 Gerd Muller gerd.muller@test.com Allemagne Berlin Other
3 Gordon Banks gordon.banks@test.com Angleterre Londres Other
4 Lionel Messi lionel.messi@test.com Argentine Buenos Aires Footballer
5 Just Fontaine just.fontaine@test.com France Paris Other
6 Franck Beckenbauer franck.beckenbauer@test.com Allemagne Berlin Other
7 Michel Platini michel.platini@test.com France Paris Other
8 Chuck Norris chuck.norris@test.com Etats-Unis Ryan Other
9 Kylian Mbappe kylian.mbappe@test.com France Bondy Other
10 Cristiano Ronaldo cristiano.ronaldo@test.com Portugal Lisbonne Other
11 Neymar Santos neymar.santos@test.com Bresil Rio de Janeiro Other
12 Emmanuel Neuer emmanuel.neuer@test.com Allemagne Berlin Other


UPDATE table_name SET column_name_1 = new_value_1, column_name_2 = new_value_2, column_name_3 = new_value_3 WHERE id = value;

A little in the same style as seen previously, we can also modify an entire line by specifying for each column the associated value.

In this example, the UPDATE command will only update the data on the firstname, lastname, and email for the id that will be 5 :

Table 3. UPDATE users SET firstname = 'Antoine', lastname = 'Griezmann', email = 'antoine.griezmann@test.com' WHERE id = '5';

id firstname lastname email country city profession
1 John Doe john.doe@test.com Etats-Unis Houston Other
2 Gerd Muller gerd.muller@test.com Allemagne Berlin Other
3 Gordon Banks gordon.banks@test.com Angleterre Londres Other
4 Lionel Messi lionel.messi@test.com Argentine Buenos Aires Footballer
5 Antoine Griezmann antoine.griezmann@test.com France Paris Other
6 Franck Beckenbauer franck.beckenbauer@test.com Allemagne Berlin Other
7 Michel Platini michel.platini@test.com France Paris Other
8 Chuck Norris chuck.norris@test.com Etats-Unis Ryan Other
9 Kylian Mbappe kylian.mbappe@test.com France Bondy Other
10 Cristiano Ronaldo cristiano.ronaldo@test.com Portugal Lisbonne Other
11 Neymar Santos neymar.santos@test.com Bresil Rio de Janeiro Other
12 Emmanuel Neuer emmanuel.neuer@test.com Allemagne Berlin Other


The DELETE command

Now that we've been able to see how to update data, we will see how to delete it. The DELETE command will allow us to do that. It can also be associated with the WHERE command as seen above to select only certain data from our table.

The delete action is irreversible so if you have a doubt check the order before launching it on your table. In our case it's only an example so there is no major issue but if you use it in business for example, then I would advise you to take certain precautions such as making a backup of the table.

DELETE FROM table_name WHERE id = value;

To delete a line in our table, just indicate the line with the WHERE condition.

With the command that we can see below, we will be able to delete the line whose id is equal to 12.

Table 4. DELETE FROM users WHERE id = '12';

id firstname lastname email country city profession
1 John Doe john.doe@test.com Etats-Unis Houston Actor
2 Gerd Muller gerd.muller@test.com Allemagne Berlin Footballer
3 Gordon Banks gordon.banks@test.com Angleterre Londres Footballer
4 Lionel Messi lionel.messi@test.com Argentine Buenos Aires Footballer
5 Just Fontaine just.fontaine@test.com France Paris Footballer
6 Franck Beckenbauer franck.beckenbauer@test.com Allemagne Berlin Footballer
7 Michel Platini michel.platini@test.com France Paris Footballer
8 Chuck Norris chuck.norris@test.com Etats-Unis Ryan Actor
9 Kylian Mbappe kylian.mbappe@test.com France Bondy Footballer
10 Cristiano Ronaldo cristiano.ronaldo@test.com Portugal Lisbonne Footballer
11 Neymar Santos neymar.santos@test.com Bresil Rio de Janeiro Footballer


DELETE FROM table_name WHERE column_name = value;

In addition, you can delete specific data in a column. For example, you can delete with the command you find below, the profession whose value is "Actor" to keep only the profession "Footballer" in your table.

Table 5. DELETE FROM users WHERE profession = 'Actor';

id firstname lastname email country city profession
2 Gerd Muller gerd.muller@test.com Allemagne Berlin Footballer
3 Gordon Banks gordon.banks@test.com Angleterre Londres Footballer
4 Lionel Messi lionel.messi@test.com Argentine Buenos Aires Footballer
5 Just Fontaine just.fontaine@test.com France Paris Footballer
6 Franck Beckenbauer franck.beckenbauer@test.com Allemagne Berlin Footballer
7 Michel Platini michel.platini@test.com France Paris Footballer
9 Kylian Mbappe kylian.mbappe@test.com France Bondy Footballer
10 Cristiano Ronaldo cristiano.ronaldo@test.com Portugal Lisbonne Footballer
11 Neymar Santos neymar.santos@test.com Bresil Rio de Janeiro Footballer

DELETE FROM table_name;

Finally, you can delete all the data in a table and this very simply.

As an example, to delete the data from the user table then you just have to make the following command : DELETE FROM users;

Thus, the user table will no longer contain any data.

Conclusion

In this article, we were able to see how to update a table's data as well as delete it, both in a general way and in a more specific way.

Reference

Link : https://fr.wikipedia.org/wiki/Structured_Query_Language

Supinfo courses

A propos de SUPINFO | Contacts & adresses | Enseigner à SUPINFO | Presse | Conditions d'utilisation & Copyright | Respect de la vie privée | Investir
Logo de la société Cisco, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société IBM, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sun-Oracle, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Apple, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sybase, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Novell, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Intel, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Accenture, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société SAP, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Prometric, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Toeic, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo du IT Academy Program par Microsoft, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management

SUPINFO International University
Ecole d'Informatique - IT School
École Supérieure d'Informatique de Paris, leader en France
La Grande Ecole de l'informatique, du numérique et du management
Fondée en 1965, reconnue par l'État. Titre Bac+5 certifié au niveau I.
SUPINFO International University is globally operated by EDUCINVEST Belgium - Avenue Louise, 534 - 1050 Brussels