Getting started with SQL: creating and manipulating your first database

Would you like an introduction to SQL? Then you've come to the right place, as I'm offering an introduction to SQL for beginners! Although there are other database languages, such as NoSQL, SQL is surely the most popular!

1. What is SQL?

SQL (Structured Query Language) is a programming language used to interact with relational databases. This means you can use SQL to create, modify and query databases and their corresponding data, as well as to control access to these databases (rights management).

SQL is widely used in software and website development. If you want to get started in website development or data analysis, it's important to know SQL.

SQL is based on simple yet powerful commands that allow you to manipulate data in a variety of ways.

There are many different implementations of SQL, each with its own commands and features. However, most SQL implementations share a basic syntax: creating, modifying and deleting tables and values, managing rights, creating views and so on.

In short, SQL is an essential language for anyone working with relational databases.

2. SQL-compatible RDBMS

To use SQL, you need to set up a RDBMS one System Gestion of Base of Données Relations. There are many different types on the market, with varying degrees of performance, some open source, others proprietary...

Here is a non-exhaustive list of RDBMSs that support the SQL language:

1. MySQL : c’est l’un des SGBDR les plus populaires et il est largement utilisé dans le développement de logiciels et de sites Web.
2. Oracle Database : c’est un SGBDR commercial qui offre de nombreuses fonctionnalités avancées et une grande scalabilité.
3. Microsoft SQL Server : c’est un SGBDR commercial et propriétaire développé par Microsoft qui est populaire dans l’environnement Windows, bien que l’on puisse l’installer sous Linux également.
4. PostgreSQL : c’est un SGBDR open source qui offre de nombreuses fonctionnalités avancées et une grande flexibilité.
5. SQLite : c’est un SGBDR léger et open source qui est souvent utilisé dans les applications mobiles et les sites Web. Certaines applications de bureau l’utilisent également.
6. MariaDB : c’est un fork de MySQL qui a été créé par les développeurs originaux de MySQL et qui offre une compatibilité avec MySQL.

There are many other RDBMSs that support the SQL language, each with its own features and benefits. It's important to choose an RDBMS that meets your specific needs in terms of performance, functionality and cost.

3. Create a SQL database

Let's move on to the practical part, where I'll assume that you've already set up an RDBMS in such a way as to be able to execute the commands that follow. For example, MySQL on Debian 11 (see link at end of article for installation).

Here's how to create a database and associated user using SQL :

It's good practice to create a user dedicated to a database. In this way, this user is used in the application/website and only has rights to this database. The root (or administrator) account has full rights.

1. Se connecter au serveur SQL en tant que root (administrateur) :

mysql -u root -p

2. Créer la base de données « personnes » :

CREATE DATABASE people;

Replace " people "To do this, type the name of your database. For your information, MySQL instructions are not case-sensitive.

3. Créer l’utilisateur « personnes_user » dans le but de lui ajouter les droits sur la base par la suite :

CREATE USER 'personnes_user'@'localhost' IDENTIFIED BY 'P@ssword';

Replace " people_user "by the desired user name, " localhost "by the SQL server address (if everything is local, you don't need to change it) and " P@ssword "Enter the desired password.

4. Accordez les privilèges à l’utilisateur « personnes_user » sur la base de données « personnes » :

GRANT ALL PRIVILEGES ON personnes.* TO 'personnes_user'@'localhost';

The GRANT command is used to assign rights to a database or table. Here, specifying " GRANT ALL PRIVILEGES "This means that we give this user full rights to the targeted database.

5. Rafraîchissez les privilèges pour que les modifications soient prises en compte :

FLUSH PRIVILEGES;

You should now have created a database and an associated user who can connect to and access this database! Congratulations on your first steps!

4. Add tables to the database

For the moment, the database is empty! We're going to add tables, which means creating a blank table and adding columns corresponding to the properties to be stored in it. Let's imagine the table " identities "with the following columns:

• ID
• Nom
• Prenom
• Age
• Ville

This corresponds to 5 columns that we can later feed to create an identity list. The "ID" column is special, as it will be our primary key (PRIMARY KEY).

A primary key in SQL is a column or set of columns in a table that are used to uniquely identify each row in the table. A primary key cannot be empty, and each row in the table must have a different value for the primary key. In the same spirit, you can learn more about foreign keys (this concept is not covered here).

From the MySQL prompt, follow these instructions.

1. Se connecter au serveur SQL en tant que root (administrateur) :

mysql -u root -p

2. Sélectionnez la base de données dans laquelle vous souhaitez créer la table, donc ici « personnes » :

USE people;

The message " Database changed "must be visible in the console.

3. Créez la table en utilisant l’instruction CREATE TABLE :

The SQL command " CREATE TABLE The "Create table" field is used to create the table, and the individual columns are declared between brackets. Each column must have an appropriate data type according to the data to be inserted (for example, INTEGER, VARCHAR, DATE, etc.).

You can also use other clauses such as " NOT NULL "to indicate that the column cannot be empty and " DEFAULT We'll use the "Default" statement to define a default value for the column. Here, we'll use the " PRIMARY KEY "to declare our primary key id" .

Here is an example of a complete query that creates a table named " identities "The columns mentioned above:

CREATE TABLE identities (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  firstname VARCHAR(255) NOT NULL,
  age INTEGER NOT NULL,
  city VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

Bear in mind that auto-incrementing (AUTO_INCREMENT) is only available on INTEGER columns, and that you cannot define multiple columns as auto-incrementing in the same table. The auto-incremented "id" primary key is used as the unique identifier for each row in the table.

Note: you can use the ALTER TABLE statement to add, modify or delete columns in an existing table.

4. Add data to an SQL table

Now we're going to add data to this table. identities We've just created a "MySQL instance". Each time I remind you of the command to connect to the MySQL instance, but it's not necessary if you're already connected. It's especially useful for people who only look at one part of the article 😉

1. Se connecter à la base de données en utilisant les informations de connexion appropriées :

mysql -u personnes_user -p

Here, we use the user account created earlier. But you can also use "root".

2. Sélectionnez la base qui contient la table à laquelle vous souhaitez ajouter des données :

USE people;

3. Ajoutez les données à la table en utilisant l’instruction SQL « INSERT INTO » :

INSERT INTO identities (last name, first name, age, city) VALUES ("Musk", "Elon",53,"Hillsborough");

Here, we add an entry to the database corresponding to our dear Elon Musk, 53 years old and living in Hillsborough. So, we add a value for each column of the table.

Make sure you use the right data type (e.g. integer, string, date, etc.) for each column, and respect table constraints such as primary and foreign keys. Here, the ID is self-incrementing, so we don't specify it. You can add several values at the same time by doing the following:

You can also use the INSERT INTO statement to add several rows at once, using similar syntax:

INSERT INTO identities (last name, first name, age, city) VALUES ("Musk", "Elon",53,"Hillsborough"),("Musk", "Elon",53,"Hillsborough");

5. Display SQL database data

Here are some examples of SQL queries you can use if you are a beginner and want to learn how to work with relational databases:

1. Afficher toutes les informations contenues dans la table « identites » :

SELECT * FROM identites;

This query selects all the columns and rows in the "identites" table. Here's the result:

You can also select specific columns by replacing the " * "The column names are separated by commas. For example:

SELECT lastname,firstname FROM identites;

We can see the difference in the returned result:

2. Filtrage de données avec l’aide d’une condition :

SELECT * FROM identites WHERE ;

This query selects all the columns and rows of the " " table. identities "For example, if you want to select all rows where the For example, if you want to select all rows where the column " name "has value Musk" you can use the following query:

SELECT * FROM identites WHERE name = "Musk";

3. Tri des données :

SELECT * FROM identites ORDER BY nom ASC;

This query selects all the columns and rows of the " " table. identities and sort them in ascending order according to the value of the " " column. name" . You can also use " DESC Click on "Sort" to sort data in descending order. Conversely, " ASC "This is used to sort in ascending order.

6. Learn more about SQL language

There are many online resources that can help you learn SQL, beyond the introduction I've provided in this article! You can visit other websites to read courses and tutorials. Here are a few ideas:

1. Le site Web de la documentation de MySQL : cette documentation fournit une référence complète pour le langage SQL et l’utilisation de MySQL, ainsi que des exemples de requêtes et des tutoriels.
2. Le site Web W3Schools : ce site Web populaire propose des tutoriels interactifs sur de nombreux langages de programmation, y compris SQL. Vous pouvez exécuter des requêtes en ligne et voir les résultats immédiatement.
3. YouTube : vous pouvez trouver de nombreux tutoriels vidéo sur YouTube qui vous montrent comment utiliser le langage SQL et comment travailler avec des bases de données.
4. Livres et manuels

It's important to practice using a database server and to work on real projects to really master SQL.

7. Conclusion

With this article from the Computer Tutorials box, you can Getting started with SQL by creating your first database, your first table and your first row in that table! To better understand the concept of databases, I invite you to read up on the MCD and MLD models.

Resources :

• Installer MySQL sur Debian 11
• Sauvegarder et restaurer une base de données SQL