26
Un BON exemple vaut mieux qu'un long discours AbdelMonem NAAMANE [email protected] MEMO MY SQL

Cours SQL Master Image Numérique ISAMM

Embed Size (px)

DESCRIPTION

cours sous forme d'exemple, mémo, sur les fonctionnalités fondamentales du SQL en prenant MySQL comme exemple.

Citation preview

Page 1: Cours SQL Master Image Numérique ISAMM

Un BON exemple vaut mieux quun

long discours

AbdelMonem NAAMANEisammnaamanecom

MEMO MYSQL

Manipulation des bases de donneacutees

DROP DATABASE nom_base

CREATE DATABASE nom_base

shellgt mysql -h host -u user -p

mysqlgt USE mabase

mysqlgt QUIT

mysqlgt show databases

AbdelMonem NAAMANEisammnaamanecom

Creacuteation de table

AbdelMonem NAAMANEisammnaamanecom

Types de donneacutees les plus utiliseacutesINTEGER (INT) Entier positif ou neacutegatif 4 octet (-2sup3sup2 2sup3sup2-1 )rarr

DATE Une date de 1000-01-01 agrave 9999-12-31 au maximum

FLOAT Un nombre agrave virgule flottante 4 octet(-+117E-38 -+340E+38)rarr

VARCHAR Chaicircne de caractegravere (1 255 rarr caractegraveres)

DATETIME Date et Heure de 1000-01-01 000000 agrave 9999-12-31 235959

ENUM (valeur1valeur2) Liste de 65 535 valeurs au maximum

TIME Heure de 000000 agrave 235959

TEXT Chaicircne de caractegravere max 2sup1 -1 octet⁶

SET (valeur1valeur2) Liste de 65 535 valeurs au maximum AbdelMonem NAAMANE

isammnaamanecom

Creacuteation de tableCREATE TABLE Etudiant (id_etudiant INT(6) nom VARCHAR(255) NOT NULL note FLOAT(72) DEFAULT 0 classe INT cin INT(6)CONSTRAINT PK_etudiant PRIMARY KEY(identifiant) CONSTRAINT UNIQUE (cin)

CONSTRAINT FK_etu_classe FOREIGN KEY (classe) REFERENCES Classe(id_classe)

ON DELETE CASCADE)

RENAME TABLE Etudiant TO eleves

AbdelMonem NAAMANEisammnaamanecom

Manipulation de la

structureAbdelMonem NAAMANE

isammnaamanecom

Manipulation de la structure 12

DROP TABLE nom_table

ALTER TABLE Etudiant DROP PRIMARY KEY

ALTER TABLE client DROP tel

ALTER TABLE Etudiant MODIFY nom_eleve varchar(40) NOT NULL

ALTER TABLE Etudiant RENAME COL1 TO COL3

AbdelMonem NAAMANEisammnaamanecom

Manipulation de la structure 22

ALTER TABLE etudiant ADD (Ville VARCHAR(255) DEFAULT Tunis Pays VARCHAR(30) )

ALTER TABLE Etudiant ADD CONSTRAINT FOREIGN KEY (classe) REFERENCES Classe(id_classe))

ALTER TABLE Etudiant ADD UNIQUE cin

AbdelMonem NAAMANEisammnaamanecom

Insertion de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Insertion de donneacutees 12

INSERT INTO nom_de_table VALUES(415NAAMANE 2011-11-11)

INSERT INTO nom_de_table (col1col2col3) VALUES (123)(456)

INSERT INTO nom_de_table SET col3 = 1 col5 = 2011-11-11

INSERT INTO table1 (clonne1 colonne2) SELECT nom age FROM table2 WHERE age gt 18

AbdelMonem NAAMANEisammnaamanecom

Insertion de donneacutees 22

LOAD DATA LOCAL IN FILE fichiertxtINTO etudiantsFIELDS TERMINATED BY LINES TERMINATED BY rnIGNORE 1 LINES

REPLACE INTO nom_de_table (col1col2col3) VALUES (123)(456)

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

UPDATE clients SET prenom =Mohamed nom =NAAMANEville = Bizerte enfants = 2 WHERE id gt 10ORDER BY note DESCLIMIT 20

UPDATE clients SET note = note+4

UPDATE clientsSET classe = REPLACE( classeMINMaster IM)

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 2: Cours SQL Master Image Numérique ISAMM

Manipulation des bases de donneacutees

DROP DATABASE nom_base

CREATE DATABASE nom_base

shellgt mysql -h host -u user -p

mysqlgt USE mabase

mysqlgt QUIT

mysqlgt show databases

AbdelMonem NAAMANEisammnaamanecom

Creacuteation de table

AbdelMonem NAAMANEisammnaamanecom

Types de donneacutees les plus utiliseacutesINTEGER (INT) Entier positif ou neacutegatif 4 octet (-2sup3sup2 2sup3sup2-1 )rarr

DATE Une date de 1000-01-01 agrave 9999-12-31 au maximum

FLOAT Un nombre agrave virgule flottante 4 octet(-+117E-38 -+340E+38)rarr

VARCHAR Chaicircne de caractegravere (1 255 rarr caractegraveres)

DATETIME Date et Heure de 1000-01-01 000000 agrave 9999-12-31 235959

ENUM (valeur1valeur2) Liste de 65 535 valeurs au maximum

TIME Heure de 000000 agrave 235959

TEXT Chaicircne de caractegravere max 2sup1 -1 octet⁶

SET (valeur1valeur2) Liste de 65 535 valeurs au maximum AbdelMonem NAAMANE

isammnaamanecom

Creacuteation de tableCREATE TABLE Etudiant (id_etudiant INT(6) nom VARCHAR(255) NOT NULL note FLOAT(72) DEFAULT 0 classe INT cin INT(6)CONSTRAINT PK_etudiant PRIMARY KEY(identifiant) CONSTRAINT UNIQUE (cin)

CONSTRAINT FK_etu_classe FOREIGN KEY (classe) REFERENCES Classe(id_classe)

ON DELETE CASCADE)

RENAME TABLE Etudiant TO eleves

AbdelMonem NAAMANEisammnaamanecom

Manipulation de la

structureAbdelMonem NAAMANE

isammnaamanecom

Manipulation de la structure 12

DROP TABLE nom_table

ALTER TABLE Etudiant DROP PRIMARY KEY

ALTER TABLE client DROP tel

ALTER TABLE Etudiant MODIFY nom_eleve varchar(40) NOT NULL

ALTER TABLE Etudiant RENAME COL1 TO COL3

AbdelMonem NAAMANEisammnaamanecom

Manipulation de la structure 22

ALTER TABLE etudiant ADD (Ville VARCHAR(255) DEFAULT Tunis Pays VARCHAR(30) )

ALTER TABLE Etudiant ADD CONSTRAINT FOREIGN KEY (classe) REFERENCES Classe(id_classe))

ALTER TABLE Etudiant ADD UNIQUE cin

AbdelMonem NAAMANEisammnaamanecom

Insertion de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Insertion de donneacutees 12

INSERT INTO nom_de_table VALUES(415NAAMANE 2011-11-11)

INSERT INTO nom_de_table (col1col2col3) VALUES (123)(456)

INSERT INTO nom_de_table SET col3 = 1 col5 = 2011-11-11

INSERT INTO table1 (clonne1 colonne2) SELECT nom age FROM table2 WHERE age gt 18

AbdelMonem NAAMANEisammnaamanecom

Insertion de donneacutees 22

LOAD DATA LOCAL IN FILE fichiertxtINTO etudiantsFIELDS TERMINATED BY LINES TERMINATED BY rnIGNORE 1 LINES

REPLACE INTO nom_de_table (col1col2col3) VALUES (123)(456)

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

UPDATE clients SET prenom =Mohamed nom =NAAMANEville = Bizerte enfants = 2 WHERE id gt 10ORDER BY note DESCLIMIT 20

UPDATE clients SET note = note+4

UPDATE clientsSET classe = REPLACE( classeMINMaster IM)

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 3: Cours SQL Master Image Numérique ISAMM

Creacuteation de table

AbdelMonem NAAMANEisammnaamanecom

Types de donneacutees les plus utiliseacutesINTEGER (INT) Entier positif ou neacutegatif 4 octet (-2sup3sup2 2sup3sup2-1 )rarr

DATE Une date de 1000-01-01 agrave 9999-12-31 au maximum

FLOAT Un nombre agrave virgule flottante 4 octet(-+117E-38 -+340E+38)rarr

VARCHAR Chaicircne de caractegravere (1 255 rarr caractegraveres)

DATETIME Date et Heure de 1000-01-01 000000 agrave 9999-12-31 235959

ENUM (valeur1valeur2) Liste de 65 535 valeurs au maximum

TIME Heure de 000000 agrave 235959

TEXT Chaicircne de caractegravere max 2sup1 -1 octet⁶

SET (valeur1valeur2) Liste de 65 535 valeurs au maximum AbdelMonem NAAMANE

isammnaamanecom

Creacuteation de tableCREATE TABLE Etudiant (id_etudiant INT(6) nom VARCHAR(255) NOT NULL note FLOAT(72) DEFAULT 0 classe INT cin INT(6)CONSTRAINT PK_etudiant PRIMARY KEY(identifiant) CONSTRAINT UNIQUE (cin)

CONSTRAINT FK_etu_classe FOREIGN KEY (classe) REFERENCES Classe(id_classe)

ON DELETE CASCADE)

RENAME TABLE Etudiant TO eleves

AbdelMonem NAAMANEisammnaamanecom

Manipulation de la

structureAbdelMonem NAAMANE

isammnaamanecom

Manipulation de la structure 12

DROP TABLE nom_table

ALTER TABLE Etudiant DROP PRIMARY KEY

ALTER TABLE client DROP tel

ALTER TABLE Etudiant MODIFY nom_eleve varchar(40) NOT NULL

ALTER TABLE Etudiant RENAME COL1 TO COL3

AbdelMonem NAAMANEisammnaamanecom

Manipulation de la structure 22

ALTER TABLE etudiant ADD (Ville VARCHAR(255) DEFAULT Tunis Pays VARCHAR(30) )

ALTER TABLE Etudiant ADD CONSTRAINT FOREIGN KEY (classe) REFERENCES Classe(id_classe))

ALTER TABLE Etudiant ADD UNIQUE cin

AbdelMonem NAAMANEisammnaamanecom

Insertion de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Insertion de donneacutees 12

INSERT INTO nom_de_table VALUES(415NAAMANE 2011-11-11)

INSERT INTO nom_de_table (col1col2col3) VALUES (123)(456)

INSERT INTO nom_de_table SET col3 = 1 col5 = 2011-11-11

INSERT INTO table1 (clonne1 colonne2) SELECT nom age FROM table2 WHERE age gt 18

AbdelMonem NAAMANEisammnaamanecom

Insertion de donneacutees 22

LOAD DATA LOCAL IN FILE fichiertxtINTO etudiantsFIELDS TERMINATED BY LINES TERMINATED BY rnIGNORE 1 LINES

REPLACE INTO nom_de_table (col1col2col3) VALUES (123)(456)

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

UPDATE clients SET prenom =Mohamed nom =NAAMANEville = Bizerte enfants = 2 WHERE id gt 10ORDER BY note DESCLIMIT 20

UPDATE clients SET note = note+4

UPDATE clientsSET classe = REPLACE( classeMINMaster IM)

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 4: Cours SQL Master Image Numérique ISAMM

Types de donneacutees les plus utiliseacutesINTEGER (INT) Entier positif ou neacutegatif 4 octet (-2sup3sup2 2sup3sup2-1 )rarr

DATE Une date de 1000-01-01 agrave 9999-12-31 au maximum

FLOAT Un nombre agrave virgule flottante 4 octet(-+117E-38 -+340E+38)rarr

VARCHAR Chaicircne de caractegravere (1 255 rarr caractegraveres)

DATETIME Date et Heure de 1000-01-01 000000 agrave 9999-12-31 235959

ENUM (valeur1valeur2) Liste de 65 535 valeurs au maximum

TIME Heure de 000000 agrave 235959

TEXT Chaicircne de caractegravere max 2sup1 -1 octet⁶

SET (valeur1valeur2) Liste de 65 535 valeurs au maximum AbdelMonem NAAMANE

isammnaamanecom

Creacuteation de tableCREATE TABLE Etudiant (id_etudiant INT(6) nom VARCHAR(255) NOT NULL note FLOAT(72) DEFAULT 0 classe INT cin INT(6)CONSTRAINT PK_etudiant PRIMARY KEY(identifiant) CONSTRAINT UNIQUE (cin)

CONSTRAINT FK_etu_classe FOREIGN KEY (classe) REFERENCES Classe(id_classe)

ON DELETE CASCADE)

RENAME TABLE Etudiant TO eleves

AbdelMonem NAAMANEisammnaamanecom

Manipulation de la

structureAbdelMonem NAAMANE

isammnaamanecom

Manipulation de la structure 12

DROP TABLE nom_table

ALTER TABLE Etudiant DROP PRIMARY KEY

ALTER TABLE client DROP tel

ALTER TABLE Etudiant MODIFY nom_eleve varchar(40) NOT NULL

ALTER TABLE Etudiant RENAME COL1 TO COL3

AbdelMonem NAAMANEisammnaamanecom

Manipulation de la structure 22

ALTER TABLE etudiant ADD (Ville VARCHAR(255) DEFAULT Tunis Pays VARCHAR(30) )

ALTER TABLE Etudiant ADD CONSTRAINT FOREIGN KEY (classe) REFERENCES Classe(id_classe))

ALTER TABLE Etudiant ADD UNIQUE cin

AbdelMonem NAAMANEisammnaamanecom

Insertion de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Insertion de donneacutees 12

INSERT INTO nom_de_table VALUES(415NAAMANE 2011-11-11)

INSERT INTO nom_de_table (col1col2col3) VALUES (123)(456)

INSERT INTO nom_de_table SET col3 = 1 col5 = 2011-11-11

INSERT INTO table1 (clonne1 colonne2) SELECT nom age FROM table2 WHERE age gt 18

AbdelMonem NAAMANEisammnaamanecom

Insertion de donneacutees 22

LOAD DATA LOCAL IN FILE fichiertxtINTO etudiantsFIELDS TERMINATED BY LINES TERMINATED BY rnIGNORE 1 LINES

REPLACE INTO nom_de_table (col1col2col3) VALUES (123)(456)

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

UPDATE clients SET prenom =Mohamed nom =NAAMANEville = Bizerte enfants = 2 WHERE id gt 10ORDER BY note DESCLIMIT 20

UPDATE clients SET note = note+4

UPDATE clientsSET classe = REPLACE( classeMINMaster IM)

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 5: Cours SQL Master Image Numérique ISAMM

Creacuteation de tableCREATE TABLE Etudiant (id_etudiant INT(6) nom VARCHAR(255) NOT NULL note FLOAT(72) DEFAULT 0 classe INT cin INT(6)CONSTRAINT PK_etudiant PRIMARY KEY(identifiant) CONSTRAINT UNIQUE (cin)

CONSTRAINT FK_etu_classe FOREIGN KEY (classe) REFERENCES Classe(id_classe)

ON DELETE CASCADE)

RENAME TABLE Etudiant TO eleves

AbdelMonem NAAMANEisammnaamanecom

Manipulation de la

structureAbdelMonem NAAMANE

isammnaamanecom

Manipulation de la structure 12

DROP TABLE nom_table

ALTER TABLE Etudiant DROP PRIMARY KEY

ALTER TABLE client DROP tel

ALTER TABLE Etudiant MODIFY nom_eleve varchar(40) NOT NULL

ALTER TABLE Etudiant RENAME COL1 TO COL3

AbdelMonem NAAMANEisammnaamanecom

Manipulation de la structure 22

ALTER TABLE etudiant ADD (Ville VARCHAR(255) DEFAULT Tunis Pays VARCHAR(30) )

ALTER TABLE Etudiant ADD CONSTRAINT FOREIGN KEY (classe) REFERENCES Classe(id_classe))

ALTER TABLE Etudiant ADD UNIQUE cin

AbdelMonem NAAMANEisammnaamanecom

Insertion de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Insertion de donneacutees 12

INSERT INTO nom_de_table VALUES(415NAAMANE 2011-11-11)

INSERT INTO nom_de_table (col1col2col3) VALUES (123)(456)

INSERT INTO nom_de_table SET col3 = 1 col5 = 2011-11-11

INSERT INTO table1 (clonne1 colonne2) SELECT nom age FROM table2 WHERE age gt 18

AbdelMonem NAAMANEisammnaamanecom

Insertion de donneacutees 22

LOAD DATA LOCAL IN FILE fichiertxtINTO etudiantsFIELDS TERMINATED BY LINES TERMINATED BY rnIGNORE 1 LINES

REPLACE INTO nom_de_table (col1col2col3) VALUES (123)(456)

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

UPDATE clients SET prenom =Mohamed nom =NAAMANEville = Bizerte enfants = 2 WHERE id gt 10ORDER BY note DESCLIMIT 20

UPDATE clients SET note = note+4

UPDATE clientsSET classe = REPLACE( classeMINMaster IM)

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 6: Cours SQL Master Image Numérique ISAMM

Manipulation de la

structureAbdelMonem NAAMANE

isammnaamanecom

Manipulation de la structure 12

DROP TABLE nom_table

ALTER TABLE Etudiant DROP PRIMARY KEY

ALTER TABLE client DROP tel

ALTER TABLE Etudiant MODIFY nom_eleve varchar(40) NOT NULL

ALTER TABLE Etudiant RENAME COL1 TO COL3

AbdelMonem NAAMANEisammnaamanecom

Manipulation de la structure 22

ALTER TABLE etudiant ADD (Ville VARCHAR(255) DEFAULT Tunis Pays VARCHAR(30) )

ALTER TABLE Etudiant ADD CONSTRAINT FOREIGN KEY (classe) REFERENCES Classe(id_classe))

ALTER TABLE Etudiant ADD UNIQUE cin

AbdelMonem NAAMANEisammnaamanecom

Insertion de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Insertion de donneacutees 12

INSERT INTO nom_de_table VALUES(415NAAMANE 2011-11-11)

INSERT INTO nom_de_table (col1col2col3) VALUES (123)(456)

INSERT INTO nom_de_table SET col3 = 1 col5 = 2011-11-11

INSERT INTO table1 (clonne1 colonne2) SELECT nom age FROM table2 WHERE age gt 18

AbdelMonem NAAMANEisammnaamanecom

Insertion de donneacutees 22

LOAD DATA LOCAL IN FILE fichiertxtINTO etudiantsFIELDS TERMINATED BY LINES TERMINATED BY rnIGNORE 1 LINES

REPLACE INTO nom_de_table (col1col2col3) VALUES (123)(456)

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

UPDATE clients SET prenom =Mohamed nom =NAAMANEville = Bizerte enfants = 2 WHERE id gt 10ORDER BY note DESCLIMIT 20

UPDATE clients SET note = note+4

UPDATE clientsSET classe = REPLACE( classeMINMaster IM)

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 7: Cours SQL Master Image Numérique ISAMM

Manipulation de la structure 12

DROP TABLE nom_table

ALTER TABLE Etudiant DROP PRIMARY KEY

ALTER TABLE client DROP tel

ALTER TABLE Etudiant MODIFY nom_eleve varchar(40) NOT NULL

ALTER TABLE Etudiant RENAME COL1 TO COL3

AbdelMonem NAAMANEisammnaamanecom

Manipulation de la structure 22

ALTER TABLE etudiant ADD (Ville VARCHAR(255) DEFAULT Tunis Pays VARCHAR(30) )

ALTER TABLE Etudiant ADD CONSTRAINT FOREIGN KEY (classe) REFERENCES Classe(id_classe))

ALTER TABLE Etudiant ADD UNIQUE cin

AbdelMonem NAAMANEisammnaamanecom

Insertion de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Insertion de donneacutees 12

INSERT INTO nom_de_table VALUES(415NAAMANE 2011-11-11)

INSERT INTO nom_de_table (col1col2col3) VALUES (123)(456)

INSERT INTO nom_de_table SET col3 = 1 col5 = 2011-11-11

INSERT INTO table1 (clonne1 colonne2) SELECT nom age FROM table2 WHERE age gt 18

AbdelMonem NAAMANEisammnaamanecom

Insertion de donneacutees 22

LOAD DATA LOCAL IN FILE fichiertxtINTO etudiantsFIELDS TERMINATED BY LINES TERMINATED BY rnIGNORE 1 LINES

REPLACE INTO nom_de_table (col1col2col3) VALUES (123)(456)

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

UPDATE clients SET prenom =Mohamed nom =NAAMANEville = Bizerte enfants = 2 WHERE id gt 10ORDER BY note DESCLIMIT 20

UPDATE clients SET note = note+4

UPDATE clientsSET classe = REPLACE( classeMINMaster IM)

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 8: Cours SQL Master Image Numérique ISAMM

Manipulation de la structure 22

ALTER TABLE etudiant ADD (Ville VARCHAR(255) DEFAULT Tunis Pays VARCHAR(30) )

ALTER TABLE Etudiant ADD CONSTRAINT FOREIGN KEY (classe) REFERENCES Classe(id_classe))

ALTER TABLE Etudiant ADD UNIQUE cin

AbdelMonem NAAMANEisammnaamanecom

Insertion de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Insertion de donneacutees 12

INSERT INTO nom_de_table VALUES(415NAAMANE 2011-11-11)

INSERT INTO nom_de_table (col1col2col3) VALUES (123)(456)

INSERT INTO nom_de_table SET col3 = 1 col5 = 2011-11-11

INSERT INTO table1 (clonne1 colonne2) SELECT nom age FROM table2 WHERE age gt 18

AbdelMonem NAAMANEisammnaamanecom

Insertion de donneacutees 22

LOAD DATA LOCAL IN FILE fichiertxtINTO etudiantsFIELDS TERMINATED BY LINES TERMINATED BY rnIGNORE 1 LINES

REPLACE INTO nom_de_table (col1col2col3) VALUES (123)(456)

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

UPDATE clients SET prenom =Mohamed nom =NAAMANEville = Bizerte enfants = 2 WHERE id gt 10ORDER BY note DESCLIMIT 20

UPDATE clients SET note = note+4

UPDATE clientsSET classe = REPLACE( classeMINMaster IM)

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 9: Cours SQL Master Image Numérique ISAMM

Insertion de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Insertion de donneacutees 12

INSERT INTO nom_de_table VALUES(415NAAMANE 2011-11-11)

INSERT INTO nom_de_table (col1col2col3) VALUES (123)(456)

INSERT INTO nom_de_table SET col3 = 1 col5 = 2011-11-11

INSERT INTO table1 (clonne1 colonne2) SELECT nom age FROM table2 WHERE age gt 18

AbdelMonem NAAMANEisammnaamanecom

Insertion de donneacutees 22

LOAD DATA LOCAL IN FILE fichiertxtINTO etudiantsFIELDS TERMINATED BY LINES TERMINATED BY rnIGNORE 1 LINES

REPLACE INTO nom_de_table (col1col2col3) VALUES (123)(456)

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

UPDATE clients SET prenom =Mohamed nom =NAAMANEville = Bizerte enfants = 2 WHERE id gt 10ORDER BY note DESCLIMIT 20

UPDATE clients SET note = note+4

UPDATE clientsSET classe = REPLACE( classeMINMaster IM)

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 10: Cours SQL Master Image Numérique ISAMM

Insertion de donneacutees 12

INSERT INTO nom_de_table VALUES(415NAAMANE 2011-11-11)

INSERT INTO nom_de_table (col1col2col3) VALUES (123)(456)

INSERT INTO nom_de_table SET col3 = 1 col5 = 2011-11-11

INSERT INTO table1 (clonne1 colonne2) SELECT nom age FROM table2 WHERE age gt 18

AbdelMonem NAAMANEisammnaamanecom

Insertion de donneacutees 22

LOAD DATA LOCAL IN FILE fichiertxtINTO etudiantsFIELDS TERMINATED BY LINES TERMINATED BY rnIGNORE 1 LINES

REPLACE INTO nom_de_table (col1col2col3) VALUES (123)(456)

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

UPDATE clients SET prenom =Mohamed nom =NAAMANEville = Bizerte enfants = 2 WHERE id gt 10ORDER BY note DESCLIMIT 20

UPDATE clients SET note = note+4

UPDATE clientsSET classe = REPLACE( classeMINMaster IM)

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 11: Cours SQL Master Image Numérique ISAMM

Insertion de donneacutees 22

LOAD DATA LOCAL IN FILE fichiertxtINTO etudiantsFIELDS TERMINATED BY LINES TERMINATED BY rnIGNORE 1 LINES

REPLACE INTO nom_de_table (col1col2col3) VALUES (123)(456)

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

UPDATE clients SET prenom =Mohamed nom =NAAMANEville = Bizerte enfants = 2 WHERE id gt 10ORDER BY note DESCLIMIT 20

UPDATE clients SET note = note+4

UPDATE clientsSET classe = REPLACE( classeMINMaster IM)

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 12: Cours SQL Master Image Numérique ISAMM

Mise agrave jour des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Mise agrave jour des donneacutees

UPDATE clients SET prenom =Mohamed nom =NAAMANEville = Bizerte enfants = 2 WHERE id gt 10ORDER BY note DESCLIMIT 20

UPDATE clients SET note = note+4

UPDATE clientsSET classe = REPLACE( classeMINMaster IM)

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 13: Cours SQL Master Image Numérique ISAMM

Mise agrave jour des donneacutees

UPDATE clients SET prenom =Mohamed nom =NAAMANEville = Bizerte enfants = 2 WHERE id gt 10ORDER BY note DESCLIMIT 20

UPDATE clients SET note = note+4

UPDATE clientsSET classe = REPLACE( classeMINMaster IM)

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 14: Cours SQL Master Image Numérique ISAMM

Suppression des donneacutees

AbdelMonem NAAMANEisammnaamanecom

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 15: Cours SQL Master Image Numérique ISAMM

Suppression des donneacutees

DELETE FROM clientsWHERE age = 20AND ville LIKE uniORDER BY note DESClimit 10

TRUNCATE clients

AbdelMonem NAAMANEisammnaamanecom

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 16: Cours SQL Master Image Numérique ISAMM

Selection de

donneacuteesAbdelMonem NAAMANE

isammnaamanecom

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 17: Cours SQL Master Image Numérique ISAMM

Selection de donneacutees simple

SELECT FROM etudiant

SELECT nom prenom note date_naissance FROM etudiantORDER BY note DESC [ASC | DESC]

SELECT nom prenom cin noteFROM etudiantWHERE ville = TunisAND note gt 10OR ville = BizerteAND nom = A AND prenom LIKE Mo

AbdelMonem NAAMANEisammnaamanecom

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 18: Cours SQL Master Image Numérique ISAMM

SELECT 1+2 bon Jour

SELECT CONCAT(prenom nom) AS nom_completFROM etudiants

SELECT FROM etudiantsWHERE age IS NULL

SELECT FROM etudiantsWHERE age IS NOT NULL

Selection fonctions utililes 1

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 19: Cours SQL Master Image Numérique ISAMM

SELECT FROM etudiantsWHERE date_naissance gt 1980-12-15AND date_naissance lt 2005-05-10

SELECT FROM etudiantsWHERE MONTH(date_naissance) = 2AND DAYOFMONTH(date_naissance) = 29

SELECT FROM etudiantsWHERE nom LIKE AAND prenom LIKE __bN

Selection fonctions utililes 2

AbdelMonem NAAMANEisammnaamanecom

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 20: Cours SQL Master Image Numérique ISAMM

SELECT DISTINCT ville FROM etudiants

SELECT COUNT() COUNT(ville) FROM etudiants

SELECT COUNT(DISTINCT ville) FROM etudiants

Selection fonctions utililes 3

AbdelMonem NAAMANEisammnaamanecom

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 21: Cours SQL Master Image Numérique ISAMM

SELECT FROM etudiantsWHERE age IN (1112131415)

SELECT DISTINCT nom_livre FROM livreWHERE type_livre NOT IN

(SELECT type_livre FROM anciens_livre)

SELECT FROM etudiantsORDER BY RAND()LIMIT 1

Selection fonctions utililes 4

AbdelMonem NAAMANEisammnaamanecom

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 22: Cours SQL Master Image Numérique ISAMM

Fonctions pour date

NOW() 2012-11-10 334421rarrCURDATE() 2012-11-10rarrCURTIME() 201109rarr

DATE_ADD(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

DATE_SUB(2000-11-16 3 YEAR) rarr MONTH | DAY | HOUR | MINUTE | SECOND

YEAR() MONTH() DAY() HOUR() MINUTE() SECOND()

AbdelMonem NAAMANEisammnaamanecom

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 23: Cours SQL Master Image Numérique ISAMM

GroupementSELECT MAX(note) MIN(note) SUM(note)AVG(note) COUNT(note) COUNT() FROM etudiant

SELECT article sum(montant) as MFROM venteGROUP BY article

SELECT article sum(montant)FROM venteWHERE date_achat gt 2011-01-01GROUP BY article

SELECT article sum(montant) FROM venteGROUP BY articleHAVING sum(montant) gt 100) AbdelMonem NAAMANE

isammnaamanecom

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 24: Cours SQL Master Image Numérique ISAMM

Groupement 2

SELECT nom_article sum(montant) max(montant) max(date_vente) AS date_derniere_vente

FROM venteWHERE date_achat gt 2011-01-01AND nom_article = A AND prenom LIKE ch

GROUP BY articleHAVING sum(montant) gt 100

ORDER BY date_derniere_vente DESClimit 5

AbdelMonem NAAMANEisammnaamanecom

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 25: Cours SQL Master Image Numérique ISAMM

Jointure

SELECT FROM etudiant classe WHERE etudiantclasse = classeid_classeAND classeniveau_classe gt= 2

SELECT FROM classeLEFT JOIN etudiant ON etudiantclasse = classeid_classeWHERE classeniveau_classe gt= 2

AbdelMonem NAAMANEisammnaamanecom

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26
Page 26: Cours SQL Master Image Numérique ISAMM

AbdelMonem NAAMANE

isammnaamanecom

MEMO MYSQL

  • Diapo 1
  • Diapo 2
  • Diapo 3
  • Diapo 4
  • Diapo 5
  • Diapo 6
  • Diapo 7
  • Diapo 8
  • Diapo 9
  • Diapo 10
  • Diapo 11
  • Diapo 12
  • Diapo 13
  • Diapo 14
  • Diapo 15
  • Diapo 16
  • Diapo 17
  • Diapo 18
  • Diapo 19
  • Diapo 20
  • Diapo 21
  • Diapo 22
  • Diapo 23
  • Diapo 24
  • Diapo 25
  • Diapo 26