SQL et MySQL

Jean-Marie Renouard LightPath 2014©

LightPath 2014© - http://www.jmrenouard.fr 1

Le logo PHP est du domaine public http://commons.wikimedia.org/wiki/File:PHP-logo.svg

Ce document est licencié sous licence ◦ Attribution-NonCommercial-ShareAlike

◦ CC BY-NC-SA

Plus de détails: http://creativecommons.org/licenses/by-nc-sa/3.0/fr/


http://commons.wikimedia.org/wiki/File:PHP-logo.svg



Langage d'interrogation de données.

Langage de manipulation de données.

Les types de données.

Création de tables.

Opérateurs, fonctions et conversions.

Les différents types d'index.


SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY] select_expression

STRAIGHT_JOIN : Jointure ordonnée de colonne. SQL_SMALL_RESULT : Indication de résultats peu nombreux. SQL_BIG_RESULT : Indication de résultats très nombreux. SQL_BUFFER_RESULT : Stockage en table temporaire. SQL_CACHE | SQL_NO_CACHE : Stockage en cache. SQL_CALC_FOUND_ROWS: Activation du comptage de tuples. HIGH_PRIORITY : Priorité augmentée à l'exécution.


[INTO {OUTFILE | DUMPFILE} 'nom_fichier' export_options]

[FROM table_references

[WHERE where_definition]

[GROUP BY {unsigned_integer | nom_de_colonne | formula} [ASC | DESC], ...

[HAVING where_definition]

[ORDER BY {unsigned_integer | nom_de_colonne | formula} [ASC | DESC] ,...]

[LIMIT [offset,] lignes]

[PROCEDURE procedure_name(argument_list)]

[FOR UPDATE | LOCK IN SHARE MODE]]

FOR UPDATE et LOCK IN SHARE MODE ◦ Options de verrouillage des lignes.


Requête simple ◦ select * from user;

Fusion tuple à tuple ◦ select * db, user;

Jointure ◦ select u.user , db.db from user u, db db where

a.user=db.user group by user;


Agrégation ◦ select a, count(b.db) ◦ from user a, db b ◦ where a.user=b.user ◦ group by a.user;

Requêtes imbriquées ◦ select tu.user ◦ from user tu ◦ where NOT EXISTS ( ◦ select * from db td where td.user=tu.user ◦ ) ;


INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ...


Insertion simple ◦ INSERT INTO USER VALUES (1, « Paul »,

« DUPONT »);

Insert-Affectation ◦ INSERT INTO USER SET USER=‘JEAN’;

Insertion par requête

◦ INSERT INTO TABLE SELECT * FROM TMP_USER;


TINYINT[(M)] [UNSIGNED] [ZEROFILL] : Entier (1 octet ). BIT/BOOL/ BOOLEAN : Alias de TinyInt. SMALLINT[(M)] [UNSIGNED] [ZEROFILL] : Entier ( 2 octets ) MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] : Entier ( 3 octets ). INT[(M)] [UNSIGNED] [ZEROFILL] : Entier ( 4 octets ). INTEGER[(M)] [UNSIGNED] [ZEROFILL] : Alias de INT. BIGINT[(M)] [UNSIGNED] [ZEROFILL] : Entier ( noctets ). FLOAT(precision) [UNSIGNED] [ZEROFILL]: Nombre à virgule flottante. FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] : Nombre à virgule flottante.


DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] ◦ Nombre à virgule flottante double p.

DOUBLE PRECISION[(M,D)] ◦ Nombre flottant double précision.

REAL[(M,D)] [UNSIGNED] [ZEROFILL] : Alias de DOUBLE

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] ◦ Nombre à virgule :littéral

DEC[(M[,D])] [UNSIGNED] [ZEROFILL] : Alias de DECIMAL NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]: Alias de DECIMAL FIXED[(M[,D])] [UNSIGNED] [ZEROFILL] : Alias de DECIMAL


DATE: Date sur l'intervalle de '1000-01-01' à '9999-12-31' .

DATETIME : Combinaison de date et heure.

TIMESTAMP[(M)] : Timestamp.

TIME: Heure sur l'intervalle de +/-838:59:59.

YEAR[(2|4)] : Année au format 2 ou 4 chiffres.


[NATIONAL] CHAR/CHARACTER(M) [BINARY | ASCII | UNICODE]: ◦ Une chaîne de caractère de taille fixe (255

caractères)

[NATIONAL] VARCHAR(M) [BINARY] ◦ Une chaîne de taille dynamique.

CHAR : Alias de CHAR(1) . VARCHAR: Alias de CHARACTER VARYING .


TINYBLOB/TINYTEXT ◦ Données de 2^8 octets.

BLOB/TEXT ◦ Données de 2^16 octets.

MEDIUMBLOB/MEDIUMTEXT ◦ Données de 2^24 octets.

LONGBLOBLONGTEXT ◦ Données de 2^32 octets.

ENUM('value1','value2',...) ◦ Une énumération de 65535 valeurs distinctes.

SET('value1','value2',...) ◦ Un ensemble de 64 membres maximum.


CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)]

[table_options] [select_statement]


[(] LIKE old_tbl_name [)];


Requête SQL


[(create_definition,...)]

[table_options] [select_statement]


[(] LIKE old_tbl_name [)];

CREATE TABLE t (

c1 CHAR(20) CHARACTER SET utf8,

c2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_bin

);


create_definition:

column_definition

| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

| KEY [index_name] [index_type] (index_col_name,...)

| INDEX [index_name] [index_type] (index_col_name,...)

| [CONSTRAINT [symbol]] UNIQUE [INDEX]

[index_name] [index_type] (index_col_name,...)

| [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)

| [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition]

| CHECK (expr)


index_col_name:

col_name [(length)] [ASC | DESC]

reference_definition:

REFERENCES tbl_name [(index_col_name,...)]

[MATCH FULL | MATCH PARTIAL]

[ON DELETE reference_option]

[ON UPDATE reference_option]


table_option

{ENGINE|TYPE} = {BDB|HEAP|ISAM|InnoDB|MERGE|MRG_MYISAM|MYISAM}

| AUTO_INCREMENT = value | AVG_ROW_LENGTH = value | CHECKSUM = {0 | 1}

| COMMENT = 'string' | MAX_ROWS = value | MIN_ROWS = value

| PACK_KEYS = {0 | 1 | DEFAULT} | PASSWORD = 'string' | DELAY_KEY_WRITE = {0 | 1}

| ROW_FORMAT = { DEFAULT | DYNAMIC | FIXED | COMPRESSED }

| RAID_TYPE = { 1 | STRIPED | RAID0 } RAID_CHUNKS = value RAID_CHUNKSIZE = value

| UNION = (tbl_name[,tbl_name]...) | INSERT_METHOD = { NO | FIRST | LAST }

| DATA DIRECTORY = 'absolute path' | INDEX DIRECTORY = 'absolute path'

| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]


Opérateurs logiques

AND, OR, NOT IN, BETWWEN

Opérateurs de comparaison

<, >, <=, >=, =, !=, <>

Opérateurs mathématiques

+,-,*,/, %,^, ||/, |/, !, @, &, |, ~, >>, <<

Les opérateurs


ABS(X) : Valeur absolue de X .

ACOS(X) : Arccosinus de X.

ASIN(X) : Arcsinus de X.

ATAN(X) : Arctangente de X.

ATAN(Y,X) : Arctangente (Y,X)

ATAN2(Y,X) : Arctangente des variables de Y / X

CEILING(X)/CEIL(X) : Valeur entière supérieure de X .

COS(X) : Cosinus de X , où X est donné en radian.

COT(X) : Cotangente de X .

Fonctions mathématiques


CRC32(expr) : Somme de contrôle - entier 32 bits non-signé.

DEGREES(X) :Conversion de radian en degré.

EXP(X) : Exponentiel x : e^x

FLOOR(X) : Valeur entière inférieure de X .

LN(X) : Logarithme naturel de X (népérien).

LOG(X)/LOG(B,X) : Logarithme de X en base B sinon LN(X) .

LOG2(X) : Logarithme en base 2 de X .

LOG10(X) : Logarithme en base 10 de X .

MOD(N,M) : Reste de la division de N par M .



PI() : Valeur de Pi.

POW(X,Y)/POWER(X,Y) : Valeur de X élevée à la puissance Y.

RADIANS(X) : Conversion de degré en radian.

RAND()/RAND(N) : Nombre aléatoire à virgule flottante.

ROUND(X)/ROUND(X,D) : Arrondi à un nombre à D décimales.

SIGN(X) : Signe de l'argument sous la forme -1 , 0 , ou 1.

SIN(X) : Sinus de X , où X est donnée en radian.

SQRT(X) : Racine carrée de X.

TAN(X) : Tangente de X en radian.

TRUNCATE(X,D) : Troncature à D décimales.



ASCII(str) : Code ASCII du premier caractère de la chaîne.

BIN(N) : Chaîne représentant la valeur binaire de N

BIT_LENGTH(str) : Nombre de bits de la chaîne de caractères str .

CHAR(N,...) : Chaîne identifiée par leur code ASCII.

CHAR_LENGTH(str) : Nombre de caractères de la chaîne str

CHARACTER_LENGTH(str): Alias de CHAR_LENGTH.

COMPRESS(string_to_compress) : Compression de la chaîne.

CONCAT(str1,str2,...) : Concaténation des arguments.

CONCAT_WS(separator, str1, str2,...) : Idem avec un séparateur.

Fonctions sur chaîne de caractère


CONV(N,from_base,to_base) : Conversion de N entre différentes bases.

ELT(N,str1,str2,str3,...) : Élement N+1 de la liste d'argument

EXPORT_SET(bits,on,off,[séparateur,[nombre_de_bits]]) :

Conversion de bits en chaîne.

FIELD(str,str1,str2,str3,...) : Index de la chaîne str dans les arguments.

FIND_IN_SET(str,strlist) : Index de la chaîne la liste strlist

H.EX(N_or_S) : Conversion chaîne/héxadécimal.

INSERT(str,pos,len,newstr) : Chaîne de caractères avec newstr insérée.

INSTR(str,substr) : Index de la première occurrence de la chaîne.



LCASE(str) : Alias de Lower()

LEFT(str,len) : len premiers caractères

LENGTH(str) : Taille de la chaîne str , mesurée en octets.

LOAD_FILE(file_name) : Contenu sous la forme d'une chaîne de caractères.

LOCATE(substr,str)/LOCATE(substr,str,pos) : Position de la première occurrence.

LOWER(str) : Chaîne str avec tous les caractères en minuscules

LPAD(str,len,padstr) : Chaîne de caractères str , complétée à gauche.

LTRIM(str) : Chaîne de caractères str sans les espaces initiaux.

MAKE_SET(bits,str1,str2,..) : Découpage de la chaîne en liste.



MID(str,pos,len) : Alias de SUBSTRING(str,pos,len) .

OCT(N) : Chaîne de caractère représentant la valeur octal.

OCTET_LENGTH(str) : Alias de OCTET_LENGTH()

ORD(str) : Code du premier caractère de la chaîne str.

POSITION(substr IN str) : Alias de LOCATE(substr,str) .

QUOTE(str) : Échappement les caractères spéciaux d'une chaîne.

REPEAT(str,count) : Répetition de la chaîne.

REPLACE(str,from_str,to_str) : Substitution de chaîne.

REVERSE(str) : Chaîne inverse de la chaîne str.



RIGHT(str,len) : len derniers caractères de la chaîne str.

RPAD(str,len,padstr) : Complétion droite de la chaîne str.

RTRIM(str) : Chaîne de caractères str sans les espaces finaux.

SOUNDEX(str) : Valeur soundex de la chaîne.

SPACE(N) : Chaîne de N espaces.

SUBSTRING(str,pos) : Sous-chaîne.

SUBSTRING(str FROM pos) : Alias de SUBSTRING(str, pos).

SUBSTRING(str,pos,len) : Idem avec une longueur définie.

SUBSTRING(str FROM pos FOR len) : Alias de SUBSTRING(str,pos,len).



SUBSTRING_INDEX(str,delim,count) : Intervalle de chaîne

TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) : Suppression de

caractère en début et/ou en fin de chaîne.

UCASE(str)/UPPER(str) : Mise en majuscule de la chaîne str.

UNCOMPRESS(string_to_uncompress) : Décompression de la chaîne

UNCOMPRESSED_LENGTH(compressed_string) : Taille de la chaîne.

UNHEX(str) : Chaîne héxadécimale en ascii.



ADDDATE(date,INTERVAL expr type) : Ajout de date.

ADDDATE(expr,days) : Ajout de date.

ADDTIME(expr,expr2) : Ajout d'heure.

CURDATE() : Date actuelle.

CURRENT_DATE() : Date actuelle.

CURTIME() : Heure actuelle.

CURRENT_TIME : Heure actuelle.

YEAR(date) : Année de la date.

YEARWEEK(date) : Numéro de la semaine de la date.

Fonctions sur le temps


CURRENT_TIMESTAMP : Date et heure actuelle.

DATE(expr) : Extraction de la date.

DATEDIFF(expr,expr2) : Nombre de jour d'écart.

DATE_ADD(date,INTERVAL expr type) : Ajout de dates.

DATE_SUB(date,INTERVAL expr type) : Soustraction de dates.

DATE_FORMAT(DATE, format) : Date formatée.

DAY(date) : Alias de dayofthemonth().

DAYNAME(date) : Nom du jour.



DAYOFMONTH(date) : Jour du mois (1 à 31).

DAYOFWEEK(date) : Jour de la semaine (1 : dimanche).

DAYOFYEAR(date) : Jour de l'année.

EXTRACT(type FROM date) : Extraction d'information de temps.

FROM_DAYS(N) : Nombre de jours depuis N.

FROM_UNIXTIME(unix_timestamp): Conversion d'un timestamp.

GET_FORMAT(type) : Format du type de date.

HOUR(time) : Extraction du nombre d'heure.

LAST_DAY(date) : Dernier jour du mois courant.



LOCALTIME() : Heure locale.

LOCALTIMESTAMP : Date et heure locale.

MAKEDATE(year, nbDay) : Date à partir de l'année et du nombre de jour

MAKETIME(hour,minute,second) : Heure à partie de heure, minute et seconde.

MICROSECOND(expr) : Nombre de micro-seconde.

MINUTE(time) : Nombre de minute.

MONTH(date) : Mois de la date.

MONTHNAME(date) : Nom du mois.

NOW() : Date actuelle.



PERIOD_ADD(P,N) : Ajout de période.

PERIOD_DIFF(P1,P2) : Différence entre période.

QUARTER(date) : Trimestre de la date.

SECOND(time) : Nombre de seconde de la date.

SEC_TO_TIME(seconds) : Date à partir d'un nombre de s.

STR_TO_DATE(str,format) : Date à partir d'une chaîne.

SUBDATE(date,INTERVAL expr type) : Alias de DATE_SUB.

SUBDATE(expr,days) : Soustraction de 2 dates.

SUBTIME(expr,expr2) : Soustraction de 2 heures.



SUBTIME(exp1, exp2) : Soustraction de date.

SYSDATE() : Alias de Now().

TIME(expr) : Extraction de l'heure.

TIMEDIFF(expr,expr2) : Soustraction de 2 heures.

TIMESTAMP(expr) : Timestamp à partir d'une chaîne.

TIMESTAMPADD(in,expr,expr2) : Ajout de timestamp.

TIMESTAMPDIFF(in,expr1,expr2) : Soustraction de timestamp.

TIME_FORMAT(time,format) : Affichage de l'heure au format.

TIME_TO_SEC(time) : Conversion en seconde.



TO_DAYS(date) : Nombre de jours depuis la date.

UNIX_TIMESTAMP() : Timestamp Unix actuelle.

UNIX_TIMESTAMP(date) : Timestamp Unix de la date.

UTC_DATE : Date au format UTC.

UTC_TIME : Heure au format UTC.

UTC_TIMESTAMP : Timestamp au format UTC.

WEEK(date [,mode]) : Numéro de la semaine.

WEEKDAY(date) : Jour de la semaine de la date.

WEEKOFYEAR(date) : Jour de l'année de la date.



AVG(expr) : Moyenne des valeurs.

BIT_AND(expr) : ET bit à bit des valeurs.

BIT_OR(expr) : OU bit à bit des valeurs.

BIT_XOR(expr) : OU EXCLUSIF bit à bit des valeurs.

COUNT(expr) : Nombre de valeurs.

COUNT(DISTINCT expr,[expr...]) : Nombre de valeurs distinctes.

Fonctions d’agrégat


•GROUP_CONCAT(expr) : Concaténation des valeurs.

•MIN(expr) : Valeur minimum.

•MAX(expr) : Valeur maximum.

•STD(expr) / STDDEV(expr) : Dérivation standard entre les valeurs.

•SUM(expr) : Somme des valeurs.

•VARIANCE(expr) : Variance des valeurs.

Fonctions d’agrégat


Fonctions de chiffrements.

Fonctions d'informations.

Fonctions diverses.

Les autres fonctions


Index simple colonne

CREATE INDEX idx on table_price ( price);

Index multi-colonne

CREATE INDEX idx_price_cat in table_price (price, cat);

Pas d'index sur expression

SELECT * FROM test1 WHERE lower(col1) = 'value';

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

Pas d'index partiel

CREATE INDEX low_price_idx on table_price(price) where price

BETWEEN 1 AND 10;

Les indexes


Index BTREE

Utile quand la granularité des données est forte.

Ex : pour l'indexation d'identifiant unique.

Index HASH

Utile quand la granularité des données est faible.

Ex : La couleur d'une voiture.

Les indexes


Index RTREE

Utile pour les données spatiales.

Ex : Coordonnées d'une étoile dans l'univers.

Index FULLTEXT

Utile pour les recherche dans du texte.

Ex : recherche d'un texte à partir d'une citation.

Les indexes


Documentation de MySQL http://dev.mysql.com/


LightPath: ◦ Société de conseil et d’ingénierie

◦ Formations, Conseil, Audit et mise en œuvre

◦ [email protected]

Jean-Marie RENOUARD ◦ [email protected]

◦ Twitter: @jmrenouard

◦ http://www.jmrenouard.fr


mailto:[email protected]

mailto:[email protected]

SQL et MySQL

Engineering

Transcript of SQL et MySQL