Un aide mémoire des commandes SQL
Requêtes SQL |
SELECT * # Sélection des colonnes FROM table # Nom d'une ou plusieurs tables WHERE condition # Obtenir les résultats selon la condition GROUP BY expression # Grouper les tables en groupe HAVING condition # Condition sur un groupe { UNION | INTERSECT | EXCEPT } # Unir plusieurs requêtes ORDER BY expression # Trier les résultats LIMIT count # Limiter à N enregistrments OFFSET start # Débuter à partir N enregistrement |
SELECT * FROM table INNER JOIN table2 ON table.id = table2.id # Jointures de 2 tables |
SELECT LAST_INSERT_ID() as new # Retourner l'ID du dernier INSERT |
SELECT COUNT(*) FROM table # Retourner le nombre de lignes |
INSERT INTO table VALUES ('valeur 1', 'valeur 2', …) # Insérer un enregistrement |
UPDATE table SET nom_colonne_1 = 'nouvelle valeur' WHERE condition # Modifier un enregistrement |
DELETE FROM table WHERE condition # Supprimer un enregistrement |
Types de données | ||
Colonnes numériques | Colonnes de texte | Colonnes temporelles |
TINYINT | CHAR | DATE |
SMALLINT | VARCHAR | DATETIME |
MEDIUMINT | TINYTEXT, TINYBLOB, | TIMESTAMP |
INT, INTEGER | TEXT, BLOB | TIME |
BIGINT | LONGTEXT,LONGBLOB | YEAR |
FLOAT | ENUM |
|
DOUBLE PRECISION, REAL | SET |
|
DECIMAL |
|
|
Fonctions GROUP BY | |
AVG | GROUP_CONCAT |
COUNT | STD |
MAX | STDDEV_POP |
MIN | STDDEV_SAMP |
SUM | STDDEV |
BIT_AND | VAR_POP |
BIT_OR | VAR_SAMP |
BIT_XOR | VARIANCE |
Fonctions | ||||
Maths | Dates et heures | Chaînes de caractères | ||
ABS | ADDDATE | MONTH | ASCII | MATCH |
ACOS | ADDTIME | MONTHNAME | BIN | MID |
ASIN | CONVERT_TZ | NOW | BIT_LENGTH | OCT |
ATAN | CURDATE | PERIOD_ADD | CHAR_LENGTH | OCTET_LENGTH |
CEIL | CURRENT_DATE | PERIOD_DIFF | CHAR | ORD |
CEILING | CURTIME | QUARTER | CHARACTER_LENGTH | POSITION |
CONV | CURRENT_TIME | SEC_TO_TIME | CONCAT_WS | QUOTE |
COS | CURRENT_TIMESTAMP | SECOND | CONCAT | REPEAT |
COT | DATE_ADD | STR_TO_DATE | ELT | REPLACE |
CRC32 | DATE_FORMAT | SUBDATE | EXPORT_SET | REVERSE |
DEGREES | DATE_SUB | SUBTIME | FIELD | RIGHT |
EXP | DATE | SYSDATE | FIND_IN_SET | RPAD |
FLOOR | DATEDIFF | TIME_FORMAT | FORMAT | RTRIM |
LN | DAYNAME | TIME_TO_SEC | HEX | SOUNDEX |
LOG10 | DAYOFMONTH, DAY | TIME | INSERT | SPACE |
LOG2 | DAYOFWEEK | TIMEDIFF | INSTR | STRCMP |
LOG | DAYOFYEAR | TIMESTAMP | LCASE | SUBSTR |
MOD | EXTRACT | TIMESTAMPADD | LEFT | SUBSTRING_INDEX |
PI | FROM_DAYS | TIMESTAMPDIFF | LENGTH | SUBSTRING |
POW | FROM_UNIXTIME | TO_DAYS | LIKE | TRIM |
POWER | GET_FORMAT | UNIX_TIMESTAMP | LOAD_FILE | UCASE |
RADIANS | HOUR | UTC_DATE | LOCATE | UNHEX |
RAND | LAST_DAY | UTC_TIME | LOWER | UPPER |
SIGN | LOCALTIME | UTC_TIMESTAMP | LPAD |
|
SIN | LOCALTIMESTAMP | WEEK | LTRIM |
|
SQRT | MAKEDATE | WEEKDAY | MAKE_SET |
|
TAN | MAKETIME | WEEKOFYEAR |
|
|
TRUNCATE | MICROSECOND | YEAR |
|
|
| MINUTE | YEARWEEK |
|
|
Visits: 23