Update Date: 2024-03-05
Query Statement
Select
1
2
3
4
| -- select all columns
select * from tableName;
-- select specific columns
select column1, column2, ..., columnN from tableName;
|
As
1
2
3
4
5
6
| -- rename column
select column1 as 'columnA',
column2 as 'columnB',
column3 as 'columnC'
from tableName
|
Distinct
1
| select distinct column1 from tableName;
|
This returns all unique values in column1.
Where
1
2
3
| select column1 from tableName
where condition
-- like year = 2017; year < 2017; year > 2017
|
Like & Between
1
2
3
| -- like
select * from tableName
where column1 like pattern
|
- pattern: a string composed of ‘%’ (matches zero to any number of arbitrary characters) or ‘_’ (maches a single arbitrary characters)
- e.g. ‘_H%’ matches ‘AHEAD’, ‘WHERE’, ‘THE’ but not “HOUSE” or “BREATH”
1
2
3
| -- between
select * from tableName
where column1 between value1 and value2
|
This returns rows that column1 >= value1 and column1 <= value2. between can still be used for characters:
1
2
| select * from tableName
where column1 between char1 and char2
|
This returns rows that column1’s string starts with char1 and column1’s string starts with char2.
For example: If char1 = ‘A’ and char2 = ‘B’. This returns rows with column1 starting with ‘A’ (A, Absorb, A…, Azure) and end with ‘B’ (‘B’ only). The words starting with ‘B’ will not be included but ‘B’.
And & Or
1
2
3
4
5
| -- And
select * from tableName
where condition1 and condition2
-- Or
where condition1 or condition2
|
ORDER BY
1
2
3
4
5
6
| -- in an ascending order (default)
select * from tableName
order by column1 ASC
-- in a descending order
select * from tableName
order by column1 DESC
|
ORDER BY always goes after WHERE (if WHERE is present).
LIMIT
1
2
| select * from tableName
LIMIT value
|
This shows only [value] rows. limit should always goes at the very end of query and not supported by all SQL databases.
CASE
1
2
3
4
5
6
7
| select *
case
when case conditionA then a
when case conditionB then b
else c
end -- as columnName
from tableName
|
Add as columnName can rename these a, b, c strings to a new column name.
