Comment créer des requêtes de données dans PostgreSQL à l'aide de la commande Select
Qu'est-ce que PostgreSQL ?
PostgreSQL est un système de gestion de base de données open source qui utilise le langage d'interrogation SQL. PostgreSQL, ou simplement "Postgres", est un outil très utile sur un serveur VPS car il peut gérer les besoins de stockage de données des sites Web et d'autres applications.
Dans ce guide, nous examinerons comment interroger une base de données PostgreSQL. Cela nous permettra de demander à Postgres de renvoyer toutes les données qu'il gère et qui correspondent aux critères que nous recherchons.
Ce tutoriel suppose que vous avez installé Postgres sur votre machine. Nous utiliserons Ubuntu 12.04, mais toute distribution Linux moderne devrait fonctionner.
Connectez-vous à PostgreSQL
Nous allons télécharger un exemple de base de données avec laquelle travailler à partir d'Internet.
Tout d'abord, connectez-vous à l'utilisateur Postgres par défaut avec la commande suivante :
sudo su - postgres
Nous allons acquérir le fichier de base de données en tapant :
wget http://pgfoundry.org/frs/download.php/527/world-1.0.tar.gz
Extrayez l'archive gzippée et accédez au répertoire de contenu :
tar xzvf world-1.0.tar.gz cd dbsamples-0.1/world
Créez une base de données pour importer la structure du fichier dans :
createdb -T template0 worlddb
Enfin, nous utiliserons le fichier .sql comme entrée dans la base de données nouvellement créée :
psql worlddb < world.sql
Nous sommes maintenant prêts à nous connecter à notre environnement nouvellement créé :
psql worlddb
Comment afficher des données dans PostgreSQL
Avant de commencer, essayons d'avoir une idée du type de données que nous venons d'importer. Pour voir la liste des tables, nous pouvons utiliser la commande suivante :
\d+
List of relations Schema | Name | Type | Owner | Size | Description --------+-----------------+-------+----------+--------+------------- public | city | table | postgres | 264 kB | public | country | table | postgres | 48 kB | public | countrylanguage | table | postgres | 56 kB | (3 rows)
Nous avons trois tables ici. Si nous voulons voir les colonnes qui composent la table "ville", nous pouvons émettre cette commande :
\d city
Table "public.city"
Column | Type | Modifiers
-------------+--------------+-----------
id | integer | not null
name | text | not null
countrycode | character(3) | not null
district | text | not null
population | integer | not null
Indexes:
"city_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "country" CONSTRAINT "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)
Nous pouvons voir des informations sur chacune des colonnes, ainsi que la relation de cette table avec d'autres ensembles de données.
Comment interroger des données avec Select dans PostgreSQL
Nous interrogeons (demandons) des informations à Postgres en utilisant des instructions "select". Ces instructions utilisent cette syntaxe générale :
SELECT columns_to_return FROM table_name;
Par exemple, si nous émettons "\d pays", nous pouvons voir que la table "pays" comporte de nombreuses colonnes. Nous pouvons créer une requête qui répertorie le nom du pays et le continent sur lequel il se trouve avec ce qui suit :
SELECT name,continent FROM country;
name | continent ----------------------------------------------+--------------- Afghanistan | Asia Netherlands | Europe Netherlands Antilles | North America Albania | Europe Algeria | Africa American Samoa | Oceania Andorra | Europe . . .
Pour afficher toutes les colonnes d'une table particulière, nous pouvons utiliser le caractère générique astérisque (*). Cela signifie "correspondre à toutes les possibilités" et, par conséquent, renverra chaque colonne.
SELECT * FROM city;
id | name | countrycode | district | population
------+-----------------------------------+-------------+-------------------------------+------------
1 | Kabul | AFG | Kabol | 1780000
2 | Qandahar | AFG | Qandahar | 237500
3 | Herat | AFG | Herat | 186800
4 | Mazar-e-Sharif | AFG | Balkh | 127800
5 | Amsterdam | NLD | Noord-Holland | 731200
6 | Rotterdam | NLD | Zuid-Holland | 593321
7 | Haag | NLD | Zuid-Holland | 440900
. . .
Here, we see the "city" table in its entirety.
Trier les résultats des requêtes dans PostgreSQL
Vous pouvez organiser les résultats de votre requête en utilisant la clause « trier par ». Cela vous permet de spécifier un ordre de tri pour les données renvoyées.
La clause "order by" vient après l'instruction select normale. Voici la syntaxe générale :
SELECT columns FROM table ORDER BY column_names [ ASC | DESC ];
Si nous voulions sélectionner le pays et le continent dans le tableau des pays, puis trier par continent, nous pouvons donner ce qui suit :
SELECT name,continent FROM country ORDER BY continent;
name | continent ----------------------------------------------+--------------- Algeria | Africa Western Sahara | Africa Madagascar | Africa Uganda | Africa Malawi | Africa Mali | Africa Morocco | Africa Côte d\u0092Ivoire | Africa . . .
Comme vous pouvez le voir, par défaut, l'instruction de commande organise les données par ordre croissant. Cela signifie qu'il commence au début des organisations en lettres et au nombre le plus bas pour les recherches numériques.
Si nous voulons inverser l'ordre de tri, nous pouvons taper "desc" après la déclaration de colonne "order by":
SELECT name,continent FROM country ORDER BY continent DESC;
name | continent ----------------------------------------------+--------------- Paraguay | South America Bolivia | South America Brazil | South America Falkland Islands | South America Argentina | South America Venezuela | South America Guyana | South America Chile | South America . . .
Nous pouvons également choisir de trier sur plusieurs colonnes. Nous pouvons avoir un champ de tri principal, puis des champs de tri supplémentaires qui sont utilisés si plusieurs enregistrements ont la même valeur dans le champ de tri principal.
Par exemple, nous pouvons trier par continent, puis par pays pour obtenir une liste alphabétique des enregistrements de pays dans chaque continent :
SELECT name,continent FROM country ORDER BY continent,name;
name | continent ----------------------------------------------+--------------- Angola | Africa Burundi | Africa Benin | Africa Burkina Faso | Africa Botswana | Africa Central African Republic | Africa Côte d\u0092Ivoire | Africa Cameroon | Africa Congo, The Democratic Republic of the | Africa . . .
Nous avons maintenant un tri alphabétique sur deux colonnes.
Filtrage des résultats de requête dans PostgreSQL
Nous avons appris à sélectionner uniquement certaines informations d'une table en spécifiant les colonnes souhaitées, mais Postgres fournit des mécanismes de filtrage plus fins.
Nous pouvons filtrer les résultats en incluant une clause "où". Une clause where est suivie d'une description des résultats que nous aimerions recevoir.
Par exemple, si nous voulions sélectionner toutes les villes des États-Unis, nous pourrions dire à Postgres de renvoyer le nom des villes où le code de pays à trois chiffres est "USA":
SELECT name FROM city WHERE countrycode = 'USA';
name ------------------------- New York Los Angeles Chicago Houston Philadelphia Phoenix San Diego Dallas San Antonio . . .
Les valeurs de chaîne, comme USA ci-dessus, doivent être placées entre guillemets simples pour être interprétées correctement par Postgres.
La requête précédente utilisait "=" pour comparer si la valeur de la colonne correspond exactement à la valeur donnée à droite de l'expression. Nous pouvons cependant effectuer une recherche plus flexible avec l'opérateur de comparaison "like".
L'opérateur similaire peut utiliser "_" comme caractère générique pour correspondre à un seul caractère et "%" comme caractère générique correspondant à zéro ou plusieurs caractères.
Nous pouvons également combiner les termes de filtrage avec "et" ou "ou". Combinons quelques filtrages pour trouver des villes aux États-Unis dont le nom commence par "N":
SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%';
name -------------------- New York Nashville-Davidson New Orleans Newark Norfolk Newport News Naperville New Haven North Las Vegas Norwalk New Bedford Norman (12 rows)
We can, of course, sort these results just like with regular, unfiltered select data.
SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%' ORDER BY name;
name -------------------- Naperville Nashville-Davidson Newark New Bedford New Haven New Orleans Newport News New York Norfolk Norman North Las Vegas Norwalk (12 rows)
Opérations de sélection avancées dans PostgreSQL
Nous allons examiner des requêtes plus complexes. Considérer ce qui suit:
SELECT country.name AS country,city.name AS capital,continent FROM country JOIN city ON country.capital = city.id ORDER BY continent,country;
country | capital | continent ---------------------------------------+-----------------------------------+--------------- Algeria | Alger | Africa Angola | Luanda | Africa Benin | Porto-Novo | Africa Botswana | Gaborone | Africa Burkina Faso | Ouagadougou | Africa Burundi | Bujumbura | Africa Cameroon | Yaoundé | Africa Cape Verde | Praia | Africa Central African Republic | Bangui | Africa Chad | N´Djaména | Africa . . .
Cette requête comporte plusieurs parties différentes. Commençons par la fin et travaillons à l'envers.
La section "commander par" de la déclaration (ORDER BY continent,pays) doit être familière.
Cette section de la déclaration indique à Postgres de trier d'abord en fonction du continent, puis de trier les entrées avec les valeurs de continent correspondantes par la colonne de pays.
Pour expliquer la partie suivante, la spécification de table, nous allons en apprendre davantage sur les jointures de table.
Sélection de données à partir de plusieurs tables dans PostgreSQL avec jointure
Postgres vous permet de sélectionner des données dans différentes tables liées à l'aide de la clause "join". Les tables sont liées si elles ont chacune une colonne qui peut faire référence aux mêmes données.
Dans notre exemple de base de données, nos tables "pays" et "ville" partagent certaines données. On peut voir que la table "pays" fait référence à la table "ville" en tapant :
\d country
. . .
. . .
Foreign-key constraints:
"country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)
. . .
. . .
Cette déclaration nous indique que la colonne "capital" dans la table "country" est une référence à la colonne "id" dans la table "city". Cela signifie que nous pouvons presque traiter ces deux tables comme une table géante en faisant correspondre les valeurs de ces colonnes.
Dans notre requête, la sélection de table indique "FROM country JOIN city ON country.capital = city.id".
Dans cette déclaration, nous disons à Postgres de renvoyer les informations des deux tables. L'instruction "join" spécifie la jointure par défaut, également appelée "jointure interne".
Une jointure interne renverra les informations présentes dans les deux tables. Par exemple, si nous faisions correspondre des colonnes qui n'étaient pas explicitement liées en tant que clés étrangères, nous pourrions nous retrouver dans une situation où une table avait des valeurs qui ne correspondaient pas à l'autre table. Ceux-ci ne seraient pas retournés à l'aide d'une jointure régulière.
La section après le mot-clé "on" spécifie les colonnes que les tables partagent afin que Postgres sache comment les données sont liées. Cette information est donnée en précisant :
table_name.column_name
Dans notre cas, nous sélectionnons des enregistrements qui ont des valeurs correspondantes dans les deux tables, où la colonne capitale de la table pays doit être comparée à la colonne id de la table ville.
Nommer les critères de sélection pour les jointures de table dans PostgreSQL
Nous sommes maintenant au début de notre instruction de requête. La partie sélectionne les colonnes. Cette partie devrait être assez simple à déchiffrer sur la base de la dernière section.
Nous devons maintenant nommer la table dans laquelle se trouvent les colonnes si nous essayons de sélectionner un nom de colonne présent dans les deux tables.
Par exemple, nous avons sélectionné la colonne "nom" à la fois dans la table des pays et dans la table des villes. Si nous devions omettre le "table_name." partie de la sélection, la correspondance serait ambiguë et Postgres ne saurait pas quelles données renvoyer.
Nous contournons ce problème en étant explicite sur la table à partir de laquelle sélectionner en cas de collisions de noms. Il est parfois utile de nommer les tables indépendamment du fait que les noms soient uniques afin de maintenir la lisibilité.,/p>
Conclusion
Vous devriez maintenant avoir une idée de base sur la façon de formuler des requêtes. Cela vous donne la possibilité de renvoyer des données spécifiques à partir de diverses sources. Ceci est utile pour créer ou utiliser des applications et des pages Web interactives autour de cette technologie.
