SYNTHESE DES INSTRUCTION SQL

Syntaxe : avec time : HH-MM-SS-HH-MM

avec timestamp : YYYY-MM-DD-HH-MM-SS-F..F-HH-MM

Interval year(precision)

interval month(precision)

inteval Year(prec.)to month

Collection de 1 ou plusieurs éléments (par ex-champs) un champ à un nom et un data type (idem colonnes de table).adresse Row(rue CHAR(20) ville CHAR(20))

Attribut composite.

Change le nom de la colonne.Entre « » donne des espace vide

Alias crée une colonne virtuelle par ex: Select Firstname, Year_result,(year_result/20)*100 as New_result crée une 3ième colonne new_result

Affiche la colonne_cible1, colonne_cible2 mais uniquement avec le filtre spécifié.

nn and nn spécifié après between sont deux chiffres précisant la portée. Nn & nn peuvent aussi être des dates à utiliser pour tout intervalle de valeur.

La condition IN peut être utilisée avec n'importe quel types de data.

Signifie est égal à na OU égal à nb OU égal à nc

AND.

OR

AND retourne vrai lorsque les deux conditions sont vraies.

OR retourne vrai lorsque une des deux conditions est vraie.

SELECT SELECT colonne_cible FROM Table_cible

ORDER BY colonne_cible DESC [ASC]

Affiche les colonnes selon un ordre ASCendant ou DESCendant par rapport à la colonne_cible spécifié après order by,

Peut-être ordonné sur un alias.

SELECT SELECT colonne_cible1,colonne_cible2,colonne_cible3 FROM Table_cible

ORDER BY colonne_cible1 DESC [ASC],colonne_cible2 DESC [ASC]

Affiche les colonnes selon un ordre ASCendant ou DESCendant par rapport aux colonne_cible1, colonne_cible2 spécifié après order by.

Triées 1ere sur la colonne_cible1 ensuite sur la colonne_cible2.

Toujours en fin d'écriture.

Extract

Datepart (sql server)

EXTRACT (champs_datetime FROM valeur_datetime)

(champs_datetime FROM valeur_interval)

(Champs_timezone FROM valeur_datetime)

Isole un simple champ d'un datetime ou d'un interval et le converti en nombre. Les paramètres sont : YEAR, MONTH, DAY, HOUR, MINUTE, SECONDE, FRACTION.

Ex:extract (year from date '08/12/1953') retourne 1953

Avec datepart – yyyy – mm – dd

(yyyy, champs_datetime)

Lenght

Len (sql server)

CHAR_length(chaine_de caratère) ou character_length(chaine_de caratère) ou

Octet_length(chaine_de caratère)

Retourne le nombre de caractères d'octet.ex: select char_length('kim bassinger') retourne 12

un espace = 1 caractère.

Mod

% (sql server)

Mod(dividende, diviseur)

Nom_de_colone %diviseur

Retourne le reste de la division.

Select MOD (27,4) retourne 3

Retourne une sous chaîne (bit ou caractère) d'une source

select SUBSTRING ('kim FROM 2 FOR 2) retourne im

Retourne la chaîne amputée du caractère.

Both : supprime tous les caractères désignés.Leading: supprime tout les caractères désignés a gauche de la chaîne

Trailing : supprime tous les caractères désignés à droite de la chaîne.

Current

Getdate()

(SQL server)

Retourne la date, time (heure), ou timestamp (date & heure) courant.

Local

LOCALTIME[(precision)] ou LOCALTIMESTAMP[(precision)]

Retourne time, ou timestamp courant de la zone horaire

Retourne le total du nbr. de ligne ou de valeurs d'une colonne qui ne contient pas null.distinct : sans les doublons

all (défaut): tout.* tout avec les null

Case

....

End

CASE

WHEN Condition de recherche1 THEN résultat1

Retourne une valeur dans coalesce si ce n'est pas NULL.

Retourne la première valeur trouvée.

SELECT colonne_cible1, colonne_cible2 FROM Table_cible WHERE colonne_cible = xxx

GROUP BY

Fait un regroupement, spécifie comment les rangées devront être groupées dans la table.

Les colonnes spécifiées dans le SELECT qui ne sont pas dans la ‘’group function’’ doivent être dans la clause GROUP BY

Les différents groupes sont :

colonne simple, colonnes multiples, nested, having

SELECT colonne_cible1, colonne_cible2 FROM Table_cible WHERE colonne_cible = xxx

GROUP BY colonne_cible1

Divise la table en sets

Souvent combiné avec des fonctions d'agrégat

Pas de Texte, Byte, Clob,Blob dans une clause

SELECT colonne_cible1, colonne_cible2 FROM Table_cible WHERE colonne_cible = xxx

GROUP BY colonne_cible1, colonne_cible2

Nested

SELECT Nom_de_colonne1,max(avg(nom_de_colonnenumérique)) FROM table_cible GROUP BY nom_de_colonne1

Retourne la ligne avec la moyenne maximale.

Se fait en deux phases :

1. Calcule le résultat moyen.

2. sélectionne le résultat moyen le plus haut

SELECT ...... FROM ......

GROUP BY ....... HAVING (nom_de_colonne) condition

Rollup

Group by.... With Rollup

(sql server)

SELECT table1,colonne1,table2.colonne1 FROM table1,table2

Where table1.colonne1= table2.colonne1

Les conditions de jointure sont dans la clause where. Évite les ambiguïtés en préfixant le nom des colonnes avec le nom des tables, permet l'utilisation d'alias pour simplifier les recherches.

SELECT s.name,s.year,g.grade

FROM Grade g, Student s

WHERE s.year BETWEEN

g.lower-bound AND g.upper_bound

Cross join

(Pas supporté par SQL server)

Très rarement utilisé.

Retourne un produit cartésien comme résultat

Natural join

(Pas supporté par SQL server)

Jointure basée sur les colonnes des tables partageant le même nom.

Les valeurs non-identiques sont éliminées

Inner join

SELECT * FROM t1 join (t2 join t3 on t2.c3 = t3.c2) on t1.c1=t2 .c2

SELECT * FROM t1 INNER JOIN t2 on t1.c1= t2.c2

Jointure entre 3 tables la t2 est une table de liaison avec les FK comprenant les PK de T1 et T3.

Affiche les données des champs qui sont dans l'intersection des deux bases.

SELECT * from T1

UNION

SELECT * FROM T2

SELECT * from T1

UNION ALL

SELECT * FROM T2

SELECT * from T1

UNION ALL

SELECT * FROM T2

SELECT * from T1

INTERSECT

SELECT * FROM T2

SELECT * from T1

EXCEPT

SELECT * FROM T2

SELECT select_list

From table

WHERE expr operator

(SELECT select_list FROM table)

Scellé dans la clause d'un autre select.

Peuvent être utilisé dans WHERE, HAVING, FROM.

Toujours entre parenthèse.Désigné sous outer query

Atomic (single row subquery)

Row - Table value (multiple row subquery)

SELECT section_id,AVG(year_result) as Moyenne FROM student

GROUP BY section_id

HAVING AVG( Year_result) = (SELECT AVG(year_result) FROM student)

Requête à cardinalité multiple

Multiple-row IN

Multiple-row ANY

SELECT last_name,year_result FROM student

WHERE Year_result IN (SELECT MAX(year_result)

FROM student)

GROUP BY section_id

SELECT last_name,year_result FROM student

WHERE Year_result <= ANY (SELECT MAX(year_result)

FROM student)

GROUP BY section_id

IN égale à un des membres présent dans la liste

ANY compare la valeur à chaque valeur retournée dans la liste.

Donne comme résultat tout les étudiants qui ont au minimum la valeur la plus petite de la liste

SELECT last_name,year_result FROM student

WHERE Year_result >= ALL (SELECT MAX(year_result)

FROM student)

GROUP BY section_id

Utilisé pour les recherches de ligne à ligne.

Chaque sous-recherche est exécutée pour chaque ligne de la OUTER query.

Les informations retournées dépendent des data qui ont été traitées dans la OUTER query

SELECT last_name,section_id year_result FROM student AS OUTER

WHERE Year_result >

(SELECT AVG(year_result) FROM student WHERE section_id = OUTER,section_id)

Affiche les x premiers résultats d’une table.Affiche les y résultats d’une table en commencant par x

Affiche les x premiers résultats d’une table en acces

ALTER TABLE table

ADD (colonne datatype [default expr]

);

SqLite :

ALTER TABLE table

ADD COLUMN colonne datatype [default expr]

;

Ajoute une nouvelle colonne.

Describe permet d’afficher la structure de la table.

Pas de parenthèse pour sqlite

ALTER TABLE table

MODIFY (colonne datatype [default expr]

);

ALTER TABLE table

DROP (colonne datatype [default expr]

);

Drop une colonne existante

Supprime tout les datas et les structures d'une table.Ne permet pas de retour en arrière

Enlève toutes les lignes d'une table. Supprime uniquement les données.

Libère les storages utilisés par la table.Ne permet pas de retour en arrière

Ajoute un commentaire à une table ou à une colonne.

Utiliser text=' ' pour vider le comment.

Renforce les règles au niveau de la table

Empêche la suppression de la table s'il y a des dépendances.

Peut-être créé soit à la création de la table soit après au niveau de la colonne ou de la table.

Not null

(contrainte de colonne)

CREATE TABLE employe (

id_empl NUMBER(6),

Last_name VARCHAR(25) NOT NULL

.......);

Ne permet pas que la colonne ait la valeur NULL

Disponible seulement au niveau de la colonne.

Unique

(contrainte de table)

CREATE TABLE employe (

id_empl NUMBER(6),

Last_name VARCHAR(25) NOT NULL

email VARCHAR2(25)

.......

CONSTRAINT emp_email UNIQUE(email));

Requière que chaque valeur dans la colonne ou dans un set de colonne (clé) soit unique.

Disponible au niveau de la BD ou de la colonne.

Si la contrainte est décrite au niveau de la table l'on peut alors l'utiliser dans le management de la BD.

CREATE TABLE employe (

id_empl NUMBER(6),

Last_name VARCHAR(25) NOT NULL

.......

CONSTRAINT id_dept_pk PRIMARY KEY(id_empl));

TSQL primarykey(id.empl)

Créer la clé primaire de la table.

Disponible au niveau de la BD ou de la colonne.

Implique un index (physique) dans la table.

FOREIGN KEY : Défini la colonne dans la chaîne fille au niveau de la contraint de table.

REFERENCES: identifie la table et la colonne de la table parent.

ON DELETE CASCADE: supprime les lignes dépendantes dans la table fille lorsqu'une ligne de la table mère est supprimée.

ON DELETE SET NULL: converti les valeurs des foreign key dépendante à NULL.

......,salary NUMBER(2)

CONSTRAINT emp_salary_min CHECK (salary > 1000)

Défini une condition que chaque colonne doit satisfaire les expressions suivantes n'est pas autorisées :

CURRVAL,NEXTVAL,LEVEL & ROWNUM

Calls to SYSDATE,UID,USER, & USERENV functions

Recherche qui réfère à d'autres valeurs dans d'autres lignes.

Il faut utiliser l'ALTER TABLE pour :

Ajouter ou supprimer une contrainte sans modifier la structure.

Permettre ou mettre hors d'action une contrainte.

Ajouter une contrainte NOT NULL en utilisant la clause MODIFY.

ALTER TABLE employe

ADD CONSTRAINT emp_manager_fk

FOREIGN KEY (manager_id)

REFERENCES employe(employe_id);

ALTER TABLE employe

DROP CONSTRAINT emp_manager_fk;

ALTER TABLE employe

DISABLE CONSTRAINT emp_emp_id_pk CASCADE

Exécute la clause DISABLE du statement ALTER TABLE pour désactiver une contrainte d'intégrité.

Applique l'option CASCADE pour désactiver les contraintes d'intégrité dépendantes.

SELECT constraint_name,constraint_type,search_condition

FROM user_constraint

WHERE table_name='empoye'

SELECT constraint_name,column_name

FROM user_cons_column

WHERE table_name = 'employe'

INSERT INTO table [(colonne [,colonne...])] VALUES(value[,value...])

INSERT INTO table [(colonne [,colonne...])] (select value[,value...] from…)

Insère un enregistrement dans une table. Un enregistrement à la fois. Si la liste des colonnes n'est pas utilisée, la liste doit suivre l'ordre des colonnes. Les caractères et les dates doivent être mise entre simple cote. Les valeurs oubliées sont traitées comme NULL .

Lorsque il y a l’utilisation d’un select pour l’encodage des valeurs ->il ne faut pas utiliser VALUES

UPDATE table SET colonne = valeur [,colonne=valeur,...]

[WHERE condition];

Modifie la valeur d'une ou de plusieurs colonnes dans un ou plusieurs enregistrements.

Peut-être utilisé avec les conditions de recherche ou des sous-requêtes requêtes.

L'absence de condition where signifie tous les enregistrements.

DELETE FROM table

[WHERE condition];

MERGE INTO table_name

USING(table|view|subquery)

ON (joint condition)

WHEN MATCHED THEN

UPDATE SET

col1=col_val1,

col2=col_val2

WHEN NOT MATCHED THEN

INSERT (colomn_list)

VALUES (column_value);

Utilisé lorsque l’on veut fusionner des bases de données secondaires dans une base de données principale (par ex : BD de délégué sur la route et BD de la société).Update et insert des datas dans une table conditionnellement.

Update si l'enregistrement existe.Insert si c'est un nouvel enregistrement.

Principes :

Présente des subsets logique ou des combinaisons de datas

Peut-être basée sur des tables ou des vues. Ne stocke pas de data mais permet aux datas de changer à travers la vue.

Stockée comme un select dans le data dictionnary

Utilisée pour restreindre les accès aux datas, faire de recherche complexe plus facilement, présente différentes vue des même datas.

CREATE VIEW studentVU1010

AS SELECT student_id,first_name,year_result

FROM student

WHERE section_id='1010'

Les datas proviennent d'une seule table.

NO functions

NO group of datas

Permets les DML à travers la vue.

CREATE VIEW complVU (name,minres,maxres,avgr)

AS SELECT se.section_name,min(st.year_result),max((st.year_result),avg(st.year_result)

FROM student st,section se

WHERE st.section_id=se.section_id

GROUP BY se section_nam

Vue complexe :

-Les datas proviennent d'une ou plusieurs tables.-Functions.

-Groupes de data

-Permets les DML à travers la vue (sous réserve)

CREATE [OR REPLACE]

[FORCE|NOFORCE] View vue [(alias[,alias]...)

AS subquery

[WITH CHECK OPTION[ CONSTRAINT contrainte]]

[WITH READ ONLY CONSTRAINT contrainte]]

ON REPLACE recrée la vue si elle existe déjà.

FORCE créer la vue sans se soucier que la table existe.

NO FORCE créer la vue seulement si la table existe.

Vue nom de la vue.

Subquery est un select complet

WITH CHECK OPTION spécifie que seulement les lignes accessibles par la vue peuvent être insérée ou mise à jour.

WITH READ ONLY empêche toute action de DML sur la vue.

Contrainte est le nom assigné à CHECK OPTION ou à READ ONLY.

SELECT *

FROM studentVU1010

WHERE result >50

Removing row

Adding Data

Interdit dans les vues simples contenant :

Group functions

A GROUP BY clause

le mot clé DISTINCT

Un pseudo colonne ayant comme mot-clé ROWNUM.

Colonne définies pas des expressions.Colonne NOT NULL dans les tables non sélectionnée dans la vue

CREATE OR REPLACE VIEW studVu

AS SELECT *

FROM studen

WHERE section_id =1010

WITH CHECK OPTION CONSTRAINT studvu_ck

Empêche les changements dans la section_id pour chaque row autrement il y aurait violation de la contrainte with check option

Utilisation limitée pour protéger l'intégrité des data.

Cette option spécifie que les inserts et les update exécutés à travers la vue ne peuvent créer des rows que la vue ne peut sélectionner en affichant une erreur avec le nom de la contrainte.

CREATE OR REPLACE VIEW studVu

AS SELECT *

FROM studen

WHERE section_id =1020

WITH READ ONLY CONSTRAINT studvu_ck