SQL cheat sheet. If there’s one thing that almost no application can live without, it’s the database. The pillar that holds the data, the ultimate source of the data conflict resolution, the storage that survives power outages. Working with a database correctly is the key to successful application design. That is why we’re dedicating this cheat sheets to the important topic of SQL. In the past we’ve released many other cheat sheets including: Now it’s time to turn our attention to the crown jewel of the declarative programming languages: SQL. And while we realize that it’s hard to fit everything you need to know about SQL on a single A4 page, we’ve tried to incorporate some of the essential information you will need to reference again and again. Download and print out this cheat sheet so you can use it whenever you need it. I’d like to thank Lukas Eder for early feedback on this cheat sheet, including advice what to include and telling me that it’s actually a good idea to make an SQL cheat sheet.
MySQL Uppercase Words (or ucfirst & ucwords) I recently needed to clean up a MySQL Table which contained people's names. Upon searching the MySQL commands, I was surprised to find there was no equivalent of PHP's ucfirst or ucwords. There were commands to convert entire strings into upper or lower case, but not just the first letter.
However, I quickly found a simple script to make a word uppercase: UPDATE table SET field=CONCAT(UCASE(SUBSTRING(field, 1, 1)),LCASE(SUBSTRING(field, 2))); What if the name needs two capitals? But then I found an issue; what if someone has a hyphen in their name (like O'Reily) or have a double barreled name (like Smith-John)? I did some more searching and have ended up writing the following two MySQL functions (tested in MySQL 5.1). Capitalize any given string This function is a clone of the ucfirst function in PHP. DROP FUNCTION IF EXISTS UC_FIRST;CREATE FUNCTION UC_FIRST(oldWord VARCHAR(255)) RETURNS VARCHAR(255) RETURN CONCAT(UCASE(SUBSTRING(oldWord, 1, 1)),SUBSTRING(oldWord, 2)); Which should produce.
MySQL 5.7 Reference Manual :: 14.2.5 INSERT Syntax. INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name,...)] [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] Or: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name,...)] SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name,...)] [(col_name,...)] INSERT inserts new rows into an existing table. When inserting into a partitioned table, you can control which partitions and subpartitions accept new rows. In MySQL 5.7, the DELAYED keyword is accepted but ignored by the server. You can use REPLACE instead of INSERT to overwrite old rows.
Tbl_name is the table into which rows should be inserted. You can provide a comma-separated list of column names following the table name. Calculs dans une base de données MySQL. Cet article présente les différentes façon de faire des calculs dans le langage MySQL. Ces fonctions de calcul sont appelées agrégation des données. Ainsi, une commande permet de connaître le nombre de valeurs dans une colonne spécifiée, la moyenne (par exemple sur une colonne de commande, la moyenne des commandes), la somme, l’écart-type… Les principaux calculs en langage MySQL : 1 – Moyenne2 – Effectifs3 – Écart-type4 – Minimum5 – Maximum6 – Somme Avant de commencer, vous devez vous connecter sur la base de données dans laquelle vous souhaitez effectuer les opérations. 1 – Calculer la moyenne dans MySQL Pour calculer la moyenne dans SQL, il faut utiliser la fonction avg(nom_de_la_colonne) dans votre requête : SELECT avg(nom_de_la_colonne) FROM `nom_de_la_table`; Le résultat ressemblera à celui ci-dessous : 2 – Calculer des effectifs dans MySQL La fonction permettant de calculer des effectifs, donc le nombre de valeurs non NULL dans la colonne sélectionnée est COUNT(nom_de_la_colonne).
MySQL 5.7 Reference Manual :: 13.5 String Functions. MySQL 5.7 Reference Manual :: 13.5.1 String Comparison Functions. Expr LIKE pat [ESCAPE 'escape_char'] Pattern matching using an SQL pattern. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL. The pattern need not be a literal string. For example, it can be specified as a string expression or table column. MySQL 5.7 Reference Manual :: 14.2.9 SELECT Syntax. A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. For example: SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable ORDER BY full_name; The AS keyword is optional when aliasing a select_expr with an identifier. The preceding example could have been written like this: SELECT CONCAT(last_name,', ',first_name) full_name FROM mytable ORDER BY full_name; However, because the AS is optional, a subtle problem can occur if you forget the comma between two select_expr expressions: MySQL interprets the second as an alias name.
For example, in the following statement, columnb is treated as an alias name: SELECT columna columnb FROM mytable; For this reason, it is good practice to be in the habit of using AS explicitly when specifying column aliases. Guide utilisateur — documentation phpMyAdmin 4.7.0-dev. 13.2.5 INSERT Syntax. INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] Or: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)]
SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] INSERT inserts new rows into an existing table. The INSERT ... You can use REPLACE instead of INSERT to overwrite old rows. Tbl_name is the table into which rows should be inserted. You can provide a comma-separated list of column names following the table name. Column values can be given in several ways: If you are not running in strict SQL mode, any column not explicitly given a value is set to its default (explicit or implicit) value. If you use an INSERT ... Cours et Tutoriels sur le Langage SQL. DB Browser for SQLite. Injection SQL. Un article de Wikipédia, l'encyclopédie libre. Exemple[modifier | modifier le code] Considérons un site web dynamique (programmé en PHP dans cet exemple) qui dispose d'un système permettant aux utilisateurs possédant un nom d'utilisateur et un mot de passe valides de se connecter. Ce site utilise la requête SQL suivante pour identifier un utilisateur : SELECT uid FROM Users WHERE name = '(nom)' AND password = '(mot de passe hashé)'; L'utilisateur Dupont souhaite se connecter avec son mot de passe « truc » hashé en MD5.
SELECT uid FROM Users WHERE name = 'Dupont' AND password = '45723a2af3788c4ff17f8d1114760e62'; Attaquer la requête[modifier | modifier le code] Utilisateur : Dupont' --Mot de passe : n'importe lequel La requête devient : SELECT uid FROM Users WHERE name = 'Dupont' -- ' AND password = '4e383a1918b432a9bb7702f086c56596e'; SELECT uid FROM Users WHERE name = 'Dupont'; L'attaquant peut alors se connecter sous l'utilisateur Dupont avec n'importe quel mot de passe. SELECT ... MySQL :: MySQL 5.7 Reference Manual :: 13.6 Numeric Functions and Operators.
MySQL :: MySQL 5.7 Reference Manual :: 13.5 String Functions.