The clauses MUST appear in the order shown above.
Code Example:
1 USE world;
2 SELECT name
3 FROM city
4 WHERE CountryCode = “AFG”
5 ORDER BY name
6 LIMIT 3
Results:
Let us break the statement line by line:
USE world;
SELECT name
FROM city
ORDER BY name
LIMIT 5;
Table 1. Column Specifications
Source | Option | Syntax |
---|---|---|
Base Table Value |
Show all columns |
|
Base Table Value |
Column Name |
Comma separated list of column names |
Calculated Value |
Calculation result |
Arithmetic expression |
Calculated Value |
Calculation result |
Functions |
Table 2. LIKE Keyword
LIKE Symbol | Description |
---|---|
% |
Match any string of characters to the left of the symbol |
_ |
Match a single character |
Code Example:
USE world;
SELECT name
FROM country
WHERE name LIKE ‘A%’
Results:
Table 3. REXEXP Keyword
REGEXP Characters | Description |
---|---|
^ |
Match the pattern to the beginning of the value being tested. |
$ |
Match the pattern to the end of the value being tested. |
. |
Matches any single character. |
[charlist] |
Matches any single character listed within the brackets. |
[char1 – char2] |
Matches any single character within the given range. |
| |
Separates two string patterns and matches either one |
Code Example:
USE world;
SELECT name
FROM country
WHERE name REGEXP 'g[o,u]';
Results:
Table 4. Operators and precendence order
Operator | Name | Order of Precedence |
---|---|---|
* |
Multiplication |
1 |
/ |
Division |
1 |
DIV |
Integer Division |
1 |
% (MOD) |
Modulo (remainder) |
1 |
+ |
Addition |
2 |
- |
Subtraction |
2 |
Code Example:
USE world;
SELECT name, population / SurfaceArea
AS "People per square mile"
FROM country;
Results:
In the previous example, we created a new column that was a calculated value. The problem is that the column header is now population / SurfaceArea. However we can rename the column header to something cleaner be create a column alias. Look at the code snippet below.
Code Example:
SELECT name, population / SurfaceArea
AS “People per square mile”
FROM country;
We used the AS keyword then in quotes we put the new column alias of “People per square mile.” Which changes the column header as seen show below.
Results:
Table 5. Comparison Operators
Operator |
Description |
---|---|
= |
Equal |
< |
Less than |
> |
Greater than |
<= |
Less than or equal to |
>= |
Greater than or equal to |
<> |
Not equal |
!= |
Not equal |
Code Example:
USE world;
SELECT name, population
FROM country
WHERE population > 1000000;
Results:
Code Example:
SELECT name, IndepYear
FROM country
WHERE IndepYear IS NULL;
Results:
Code Example:
USE world;
SELECT name, IndepYear
FROM country
WHERE name BETWEEN "Aruba" and "Bahamas";
Results:
Code Example:
USE world;
SELECT name
FROM country
WHERE name IN ('Aruba', 'Barbados', 'Cuba', 'Bahamas')
ORDER BY population ASC;
Results:
Table 6. Logical Operators
Operator |
Description |
Order of Evaluation |
---|---|---|
NOT |
( a NOT b ) – a must be present but b must NOT be present to be included |
1 |
AND |
( a AND b ) –If both a and b are present, item is included |
2 |
OR |
( a OR b ) – If either a OR b is present item is included |
3 |
Example:
USE world;
SELECT name, population
FROM country
WHERE region = 'caribbean'
AND population > 100000
ORDER BY population ASC;
Results:
Table 7. DISTINCT Keyword
Keyword |
Description |
Order of Evaluation |
---|---|---|
DISTINCT |
Eliminates duplicate rows |
1 |
Example:
SELECT DISTINCT continent, name
FROM country
ORDER BY continent;
Results:
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/learning_mysql/how_to_retrieve_data.