wikinotes

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
{{tag>tutotriel sql postgresql}}

Notes et traduction du tutoriel [[https://neon.tech/postgresql/tutorial|PostgreSQL Tutorial]]

====== Tutoriel : fondamentaux PostgreSQL ======

D'abord vous apprendrez à récupérer des données dans une table en utilisant les techniques élémentaires telles que la selection, le tri et le filtrage des enregistrements.

Ensuite vous plongerez dans les requêtes avancées joignant plusieurs tables, les opération "set" et la construction de requêtes imbriquées.

Pour terminer vous apprendrez comment gérer les tables, les créer ou modifier la structure de tables existantes.


===== Récupérer des données =====

  * [[110_instruction_select|SELECT]] – Extraire les données d'une table.
  * [[115_alias_colonnes|Alias]] – Assigner un nom temporaire à une colonne ou une expression dans une requête.
  * [[120_instruction_order_by|ORDER BY]] – Trier l'ensemble résultat retourné par la requête.
  * [[125_instruction_select_distinct|SELECT DISTINCT]]  – Retirer les doublons d'un ensemble résultat.

===== Filtrer les données =====

  * [[210_clause_where|WHERE]] – Filtrer les enregistrements selon des conditions.
  * [[215_operateur_and|AND]] – Évalue deux expressions booléennes et retourne vrai si les deux expressions sont vraies.
  * [[220_operateur_or|OR]] – Évalue deux expressions booléennes et retourne faux si les deux expressions sont fausses.
  * [[225_clause_limit|LIMIT]] – Récupère un sous ensemble des enregistrements résultant d'une requête.
  * [[230_clause_fetch|FETCH]] – Limiter les enregistrements retournés par une requête.
  * [[235_operateur_in|IN]] – Sélectionner les données correspondant à l'une des valeurs d'une liste.
  * [[240_between|BETWEEN]] – Sélectionner les données comprises dans un intervalle.
  * [[245_like|LIKE]] – Filtrer les données en s'appuyant sur une correspondance à un motif.
  * [[250_is_null|IS NULL]] – Vérifier si une valeur est NULL.

===== Joindre les tables =====

  * [[310_jointures_postgres|Les Jointures]] – Brève présentation des jointures dans PostgreSQL.
  * [[315_alias_table|Alias de tables]] – Comment utiliser les alias de tables dans les requêtes.
  * [[320_postgres_inner_join|INNER JOIN]] – Sélectionner les enregistrements d'une table ayant une correspondance dans une autre table.
  * [[325_postgres_left_join|LEFT JOIN]] – Sélectionner les enregistrements d'une table ayant ou non des correspondance dans une autre table.
  * [[330_postgres_self_join|SELF JOIN]] – Joindre une table à elle même en la comparant à elle même.
  * [[335_postgres_full_outer_join|Full OUTER JOIN]] – Trouver les enregistrements d'une table n'ayant pas de correspondance dans une autre table.
  * [[340_postgres_cross_join|CROSS JOIN]] – Produit cartésien des enregistrement entre deux tables ou plus.
  * [[345_postgres_natural_join|NATURAL JOIN]] – Joindre deux tables ou plus en utilisant des jointures implicite s’appuyant sur les noms de colonnes.

===== Grouper les données =====

  * Group By – divide rows of a result set into groups and optionally apply an aggregate function to each group.
  * Having – apply conditions to groups, which allow you to filter groups.

===== Section 5. Set Operations =====

  * Union – combine result sets of multiple queries into a single result set.
  * Intersect – combine the result sets of two or more queries and return a single result set containing rows that appear in both result sets.
  * Except – return the rows from the first query that do not appear in the output of the second query.

===== Section 6. Grouping sets, Cubes, and Rollups =====

  * Grouping Sets  – generate multiple grouping sets in reporting.
  * Cube – define multiple grouping sets that include all possible combinations of dimensions.
  * Rollup – generate reports that contain totals and subtotals.

===== Section 7. Subquery =====

  * Subquery – write a query nested inside another query.
  * Correlated Subquery – show you how to use a correlated subquery to perform a query that depends on the values of the current row being processed.
  * ANY  – retrieve data by comparing a value with a set of values returned by a subquery.
  * ALL – query data by comparing a value with a list of values returned by a subquery.
  * EXISTS  – check for the existence of rows returned by a subquery.

===== Section 8. Common Table Expressions =====

    PostgreSQL CTE – introduce you to PostgreSQL common table expressions or CTEs.
    Recursive query using CTEs – discuss the recursive query and learn how to apply it in various contexts.

===== Section 9. Modifying Data =====

In this section, you will learn how to insert data into a table with the INSERT statement, modify existing data with the UPDATE statement, and remove data with the DELETE statement. Additionally, you will learn how to use the UPSERT statement to merge data.

    Insert – guide you on how to insert a single row into a table.
    Insert multiple rows – show you how to insert multiple rows into a table.
    Update – update existing data in a table.
    Update join – update values in a table based on values in another table.
    Delete – delete data in a table.
    Upsert – insert or update data if the new row already exists in the table.

===== Section 10. Transactions =====

    PostgreSQL Transactions – show you how to handle transactions in PostgreSQL using BEGIN, COMMIT, and ROLLBACK statements.

===== Section 11. Import & Export Data =====

You will learn how to import and export PostgreSQL data from and to CSV file format using the copy command.

    Import CSV file into Table – show you how to import CSV file into a table.
    Export PostgreSQL Table to CSV file – show you how to export tables to a CSV file.

===== Section 12. Managing Tables =====

In this section, you will begin exploring the PostgreSQL data types and learn how to create new tables and modify the structure of the existing ones.

    Data types – cover the most commonly used PostgreSQL data types.
    Create a table – guide you on how to create a new table in the database.
    Select Into & Create table as– shows you how to create a new table from the result set of a query.
    Auto-increment column with SERIAL – uses SERIAL to add an auto-increment column to a table.
    Sequences – introduce you to sequences and describe how to use a sequence to generate a sequence of numbers.
    Identity column – show you how to use the identity column.
    Alter table – modify the structure of an existing table.
    Rename table – change the name of the table to a new one.
    Add column – show you how to add one or more columns to an existing table.
    Drop column – demonstrate how to drop a column of a table.
    Change column data type – show you how to change a column’s data.
    Rename column – illustrate how to rename one or more table columns.
    Drop table – remove an existing table and all of its dependent objects.
    Truncate table – remove all data in a large table quickly and efficiently.
    Temporary table – show you how to use the temporary table.
    Copy a table – show you how to copy a table to a new one.

===== Section 13. PostgreSQL Constraints =====

    Primary key – illustrate how to define a primary key when creating a table or adding a primary key to an existing table.
    Foreign key – show you how to define foreign key constraints when creating a new table or adding foreign key constraints for existing tables.
    DELETE CASCADE – show you how to automatically delete rows in child tables when the corresponding rows in the parent table are deleted.
    CHECK constraint – add logic to check value based on a Boolean expression.
    UNIQUE constraint – ensure that values in a column or a group of columns are unique across the table.
    NOT NULL constraint – ensure values in a column are not NULL.
    DEFAULT constraint – specify a default value for a column using the DEFAULT constraint.

===== Section 14. PostgreSQL Data Types in Depth =====

    Boolean – store TRUE and FALSEvalues with the Boolean data type.
    CHAR, VARCHAR, and TEXT – learn how to use various character types including CHAR, VARCHAR, and TEXT.
    NUMERIC – show you how to use NUMERIC type to store values that precision is required.
    DOUBLE PRECISION – learn to store inexact, variable-precision numbers in the database. The DOUBLE PRECISION type is also known as the FLOAT type.
    REAL – guide you on how to use single-precision floating-point numbers in the database.
    Integer – introduce you to various integer types in PostgreSQL including SMALLINT, INT and BIGINT.
    DATE  – introduce the DATE data type for storing date values.
    Timestamp – understand timestamp data types quickly.
    Interval – show you how to use interval data type to handle a period effectively.
    TIME – use the TIME datatype to manage the time of day values.
    UUID – guide you on how to use UUID datatype and how to generate UUID values using supplied modules.
    Array – show you how to work with arrays and introduce you to some handy functions for array manipulation.
    hstore – introduce you to the hstore data type, a set of key/value pairs stored in a single value in PostgreSQL.
    JSON – illustrate how to work with JSON data type and use some of the most important JSON operators and functions.
    User-defined data types – show you how to use the CREATE DOMAIN and CREATE TYPE statements to create user-defined data types.
    Enum – learn how to create an enum type that defines a list of fixed values for a column.
    XML – show you how to store XML documents in the database using the XML data type.
    BYTEA – learn how to store binary strings in the database.
    Composite Types – show you how to define a composite type that consists of multiple fields.

===== Section 15. Conditional Expressions & Operators =====

    CASE – show you how to form conditional queries with CASE expression.
    COALESCE – return the first non-null argument. You can use it to substitute NULL by a default value.
    NULLIF – return NULL if the first argument equals the second one.
    CAST – convert from one data type into another e.g., from a string into an integer, from a string into a date.

===== Section 16. PostgreSQL Utilities =====

    psql commands – show you the most common psql commands that help you interact with psql faster and more effectively.

===== Section 17. PostgreSQL Recipes =====

    How to compare two tables – describe how to compare data in two tables in a database.
    How to delete duplicate rows in PostgreSQL – show you various ways to delete duplicate rows from a table.
    How to generate a random number in a range  – illustrate how to generate a random number in a specific range.
    EXPLAIN statement– guide you on how to use the EXPLAIN statement to return the execution plan of a query.
    PostgreSQL vs. MySQL – compare PostgreSQL with MySQL in terms of functionalities.
wikinotes

Outils pour utilisateurs

Outils du site

Outils de la page
