Prsentation du langage SQL
La base exemple
Langage SQL
DDL - Langage de dfinition des donnes
CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE
DML - Langage de manipulation des donnes
SELECT, INSERT, UPDATE, DELETE
DCL - Langage de contrle des donnes
CREATE USER, GRANT SELECT.
Langage SQL
DDL - Langage de dfinition des donnes
CREATE TABLE: Cration dune nouvelle table
ALTER TABLE: la mise jour dune table existante
DROP TABLE: la suppression dune table existante
TRUNCATE TABLE: Mettre zro une table existante
Langage SQL
DML - Langage de manipulation des donnes
SELECT: affiche le contenu de lensemble des colonnes choisie
INSERT: Insrer des donnes dans la table
UPDATE : mise jour des lignes
DELETE : suppression des lignes
Langage SQL
DCL - Langage de contrle des donnes
CREATE USER: cration dun utilisateur pour le serveur
GRANT SELECT: accorder des droits aux utilisateurs
Commande Create table
CREATE TABLE <NOM DE LA TABLE>(
[id] [int] IDENTITY (1,1),
[ATTRIBUT 1] [type ] NOT NULL,
[ATTRIBUT 2] [type ] NULL,
[ATTRIBUT n] [type ] NULL
)
Commande Create table
CREATE TABLE Etudiant (
[id] [int] IDENTITY (1,1),
[nom] [type ] NOT NULL,
[prenom] [nvarchar(50) ] NULL,
[classe] [nvarchar (50)] NULL,
[module] [nvarchar (50)] NULL,
[moyenne] [nvarchar (50)] NULL,
[ATTRIBUT n] [type ] NULL
)
Commande SELECT
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] )
]]
* | expression [ AS nom_d_affichage ] [, ...]
FROM lments_from [, ...]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING
oprateur ] [, ...] ]
Commande SELECT
SELECT * FROM fonds
SELECT nom_fonds, isin FROM fonds ORDER BY
nom_fonds DESC
Commande SELECT oprateur
DISTINCT
SELECT devise, nom_inst FROM instruments
SELECT DISTINCT devise FROM instruments
Commande SELECT oprateur AS
SELECT nom_inst AS libell, toto as
nouvelle_colonne FROM instruments
Commande SELECT oprateur
COUNT()
SELECT count(*) FROM prix
SELECT count(distinct id_inst) FROM prix
Commande SELECT Fonctions
dagrgats
SUM (COLONNE)
SUM distinct (COLONNE)
Max (COLONNE)
Min (COLONNE)
AVG (COLONNE)
AVG distinct (COLONNE)
Clause WHERE
Oprateurs de
comparaison
= <> < <= > >=
Connecteurs logiques
{OR | AND}
Oprateur de ngation
NOT
Oprateurs
mathmatiques
+-*/
Comparaison logique
IS [NOT] {TRUE | FALSE | UNKNOWN}
Comparaison avec
valeur
IS [NOT] NULL
Intervalle
valeur BETWEEN borne_bas AND
borne_haut
Comparaison partielle
valeur LIKE motif [ESCAPE
echappement]
Comparaison une liste valeur [NOT] IN (liste)
Clause WHERE
SELECT * FROM instruments WHERE devise =
EUR
SELECT * FROM prix WHERE id_inst = 134 AND datobs
BETWEEN CAST('19280825' as datetime) and
CAST('19280828' as datetime)
Clause WHERE
SELECT * FROM instruments WHERE devise IN
(EUR,USD)
SELECT * FROM instruments WHERE nom_inst
LIKE '%T%'
SELECT sur plusieurs tables
On veut trouver la liste des grants associs aux
fonds
Table
FONDS
Table
GERANTS
Plusieurs mthodes possibles : Produit cartsien,
Jointure interne, Jointure Externe
SELECT sur plusieurs tables
Produit cartsien
SELECT nom_gerant, nom_fonds
FROM fonds, gerants
SELECT sur plusieurs tables
Jointure interne
SELECT g.nom_gerant, f.nom_fonds
FROM fonds f, gerants g
WHERE f.id_fonds = [Link]
Clause GROUP BY
SELECT id_inst, MIN(datobs) as datemin
FROM prix
GROUP BY id_inst
Clause Having
SELECT id_inst, MIN(datobs) as datemin
FROM prix
GROUP BY id_inst
Having <condition>
Les sous-requtes
SELECT nom_inst, prix
FROM instruments i, prix p,
(SELECT id_inst, MAX(datobs) as
datemax
FROM prix GROUP BY id_inst) d
WHERE i.id_inst = d.id_inst
AND [Link] = [Link]
AND p.id_inst = i.id_inst
Les sous-requtes
SELECT nom_inst, prix
FROM instruments i, prix p
WHERE i.id_inst = p.id_inst
and [Link] = ( SELECT MAX(datobs)
FROM prix q
WHERE q.id_inst = p.id_inst )
Commande INSERT
INSERT INTO table [ ( colonne [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression |
DEFAULT } [, ...] ) [, ...] | requte }
[ RETURNING * | expression_sortie [ AS
nom_sortie ] [, ...] ]
Commande INSERT
INSERT INTO prix ( prix, id_inst, datobs )
VALUES (432.15, 1,
convert(datetime,20080926,112))
Oracle : INSERT INTO prix ( prix, id_inst, datobs )
VALUES (432.15, 1, to_date(20080926,yyyymmdd))
Commande INSERT
INSERT INTO prix ( id_inst, datobs, prix )
SELECT 9, [Link], [Link]
FROM prix S
WHERE S.id_inst = 1
AND [Link] < cast('20080831' as datetime)
Commande UPDATE
UPDATE [ ONLY ] table [ [ AS ] alias ]
SET { colonne = { expression | DEFAULT } |
( colonne [, ...] ) = ( { expression |
DEFAULT } [, ...] ) } [, ...]
[ FROM liste_from ]
[ WHERE condition ]
Commande UPDATE
Avant :
UPDATE devises
SET nom_devise = US Dollar
WHERE iso = USD
Aprs :
Commande UPDATE
Avant :
UPDATE instruments
SET nom_inst = nom_inst + '-' + d.nom_devise
FROM devises d
WHERE devise = [Link]
Aprs :
Commande DELETE
DELETE FROM [ ONLY ] table [ [ AS ] alias ]
[ USING liste_using ]
[ WHERE condition ]
[ RETURNING * | expression_sortie [ AS
output_name ] [, ...] ]
