Upload
abdelmonem-naamane
View
962
Download
0
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
AbdelMonem NAAMANE
isammnaamanecom
MEMO MYSQL