EXCEPT ;
Ici nous nous concentrons sur SELECT et FROM.
===== Syntaxe instruction SELECT =====
<code sql>
SELECT
select_list
FROM
table_name;
</code>
* "select_list" peut ... on souhaite obtenir les information;
La clause FROM est optionnelle. Si les données demandées ne font pas parti d'une table la clause FROM peut être omise.
La clause FROM est évaluée a
in both result sets.
* Except – return the rows from the first query that do not appear in the outpu... ll learn how to import and export PostgreSQL data from and to CSV file format using the copy command.
... ate table as– shows you how to create a new table from the result set of a query.
Auto-increment c... rgument equals the second one.
CAST – convert from one data type into another e.g., from a string
la clause WHERE.
<code sql>
SELECT
select_list
FROM
table_name
WHERE
condition
ORDER BY
sort_... La clause WHERE est placée juste après la clause FROM de l'instruction SELECT.
* La clause WHERE ut... PostgreSQL évalue la clause WHERE après la clause FROM mais **avant** les clauses SELECT et ORDER BY.
... |
<code sql>
SELECT
last_name,
first_name
FROM
customer
WHERE
first_name = 'Jamie';
--
r exemple :
<code sql>
SELECT
film_id,
title
FROM
film
WHERE
film_id in (1, 2, 3);
-- Requête équivalente
SELECT
film_id,
title
FROM
film
WHERE
film_id = 1
OR film_id = 2
O... es :
<code sql>
SELECT
first_name,
last_name
FROM
actor
WHERE
last_name IN ('Allen', 'Chase',... ql>
SELECT
payment_id,
amount,
payment_date
FROM
payment
WHERE
payment_date::date IN ('2007-
mmence par "Jen"
SELECT
first_name,
last_name
FROM
customer
WHERE
first_name LIKE 'Jen%';
</co... contiennent "er"
SELECT
first_name,
last_name
FROM
customer
WHERE
first_name LIKE '%er%'
ORDER... ce pas par "Jen"
SELECT
first_name,
last_name
FROM
customer
WHERE
first_name NOT LIKE 'Jen%'
O... es :
<code sql>
SELECT
first_name,
last_name
FROM
customer
WHERE
first_name ILIKE 'BAR%';
</c
ECT.
<code sql>
SELECT column_name AS alias_name
FROM table_name;
-- équivalent
SELECT column_name alias_name
FROM table_name;
</code>
L'objectif de l'alias est ... T
first_name || ' ' || last_name AS full_name
FROM
customer;
-- Les alias de colonnes ave... CT
first_name || ' ' || last_name "full name"
FROM
customer;
</code>
===== Résumé =====
*
clause ORDER BY.
<code sql>
SELECT
select_list
FROM
table_name
ORDER BY
sort_expression1 [ASC |... tion, si un alias existe dans la clause SELECT ou FROM, il peut être utilisé dans la clause ORDER BY.
<code sql>
SELECT
first_name,
last_name
FROM
customer
ORDER BY
first_name ASC;
</code>
...
SELECT
first_name,
LENGTH(first_name) AS len
FROM
customer
ORDER BY
len DESC;
</code>
Comme
par la requête.
<code sql>
SELECT
select_list
FROM
table_name
ORDER BY
sort_expression
LIMIT
... FSET après LIMIT
<code sql>
SELECT
select_list
FROM
table_name
ORDER BY
sort_expression
LIMIT
... de sql>
SELECT
film_id,
title,
release_year
FROM
film
ORDER BY
film_id
LIMIT
5;
-- Usage ... offset
SELECT
film_id,
title,
release_year
FROM
film
ORDER BY
film_id
LIMIT 4 OFFSET 3;
</c
w OR value > high
</code>
En pratique on utilise fréquemment l'opérateur ''BETWEEN'' dans la clause W... DELETE.
<code sql>
SELECT
payment_id,
amount
FROM
payment
WHERE
payment_id BETWEEN 17503 AND ...
-- hors intervalle
SELECT
payment_id,
amount
FROM
payment
WHERE
payment_id NOT BETWEEN 17503 ... ql>
SELECT
payment_id,
amount,
payment_date
FROM
payment
WHERE
payment_date BETWEEN '2007-02
mier enregistrement
SELECT
film_id,
title
FROM
film
ORDER BY
title
FETCH FIRST ROW ONL... rements avec offset
SELECT
film_id,
title
FROM
film
ORDER BY
title
OFFSET 10 ROWS
FETC
