MySQL Reference Sheet: Query Structure
The term query in MySQL refers to any interaction with the database and not just requesting information. This reference sheet will focus on the anatomy of the SELECT query and explore the following clauses:
- SELECT
- WHERE
- ORDER BY
- LIKE
- IN
SELECT query
This query will read a table or tables and return a specific record or records that are determined by parameters set within the query.
Syntax:
Select x FROM y;
- y = what you are looking for
- y = where you want to get it from
Example:
mysql> SELECT menu_title FROM courses_table;
WHERE clause
This parameter will filter or restrict the number records returned.
Syntax:
Select x FROM y WHERE z;
- z = what condition must be satisfied
Example:
mysql> SELECT menu_title FROM courses_table WHERE visible = 1;
The WHERE clause can be simple of complex (WHERE x = y, WHERE x = y AND y = x, etc…)
ORDER BY
This parameter will order the record set returned by a SELECT query. Typically you order by a column name, the records set will then be ordered by the contents of that column in an ascending fashion by default. State DESC to order in a descending fashion
Syntax:
SELECT x FROM y WHERE z ORDER BY n;
- n = the column to be ordered by
Example:
mysql> SELECT menu_title FROM courses_table WHERE visible = 1 ORDER BY id DESC;
LIKE
Typically used with WHERE to match values in another value.
Syntax:
SELECT x FROM y WHERE z LIKE ‘m’ ORDER BY n;
This will return records that contain m.
Example:
mysql> SELECT menu_title FROM courses_table WHERE name LIKE ‘%data%’ ORDER BY id DESC;
This will return records where the string ‘data’ exits within the name field.
IN
This is typically used with WHERE to select results that match values in a list.
Syntax:
SELECT x FROM y WHERE z IN (‘a’, ‘b’) ORDER BY n;
Example:
mysql> SELECT menu_title FROM courses_table WHERE Name IN (‘Andrew’, ‘Annette’) ORDER BY id DESC;
This will return every Andrew and Annette in the table but but not Abe or Ali, etc.