_ _
___ | |__ __ _ _______ | |_
/ _ \| '_ \ / _` |_ / _ \| __|
| (_) | | | | (_| |/ / (_) | |_
\___/|_| |_|\__,_/___\___/ \__|
sql —
queries and usage
- select * will show all fields
- select distinct means it will only show unique records
| Select all records from table |
: select * from TABLE; |
| Select unique records |
: select distinct * from TABLE; |
| Show only specified fields for all records |
: select FIELD1, FIELD2 from TABLE; |
| Count records from table |
: select count(1) from TABLE; |
| Select only records matching condition |
: select * from TABLE where FIELD = VALUE; |
| All records matching multiple conditions |
: select * from TABLE where FIELD1 = VALUE1 and FIELD2
= VALUE2; |
| All matching records from multiple tables |
: select * from TABLE1, TABLE2 where TABLE1.FIELD1 =
TABLE2.FIELD2; |
| Set table alias to simplify use |
: select * from TABLE1 ALIAS1; |
| Use the alias when selecting |
: select T1.FIELD1, T1.FIELD2 from TABLE1
T1; |
(back to top)
| - Select record count by group: Ta Li select FIELD, count(FIELD) from
TABLE group by FIELD; |
| - Select record count by group where count is greater than 1: Ta Li
select FIELD, count(FIELD) from TABLE group by FIELD having count(FIELD)
> 1; |
Grouping functions include: count, avg, min, max, sum. Example:
select FIELD1, sum(FIELD2) from TABLE group by FIELD1;
(back to top)
| Limit displayed records |
: select * from TABLE limit 10; |
| Select 10 records starting from the 5th record |
: select * from TABLE limit 10 offset 5; |
(back to top)
Joins data from multiple tables using [INNER] JOIN. JOIN implies
INNER JOIN. Displays records matching all specified tables.
select
T1.FIELD1, T1.FIELD2,
T2.FIELD1, ALIAs2.FIELD2
from TABLE1 T1
join TABLE2 T2 on T2.FIELD = T1.FIELD;
Displays records matching any of the joined tables. Possible
values are [LEFT|RIGHT|FULL] [OUTER] JOIN. Specifying OUTER is not necessary
when using LEFT, RIGHT, FULL as it's implied.
Displays all records from the left table and matching records from
the right table. Right is similar only showing records from right and
matching from left.
select * from TABLE1 T1
right join TABLE2 T2 on T2.FIELD = T1.FIELD;
Shows all records from both tables.
(back to top)
Specify action based on CASE condition.
select
case
when CONDITION1 then RESULT1
when CONDITION2 then RESULT2
else RESULT3
end
from TABLE;
If field name is unspecified it will show as the query. To specify a field name:
select
case
CONDITIONS
end as FIELD_NAME
from TABLE;