ohazot

       _                    _
  ___ | |__   __ _ _______ | |_
 / _ \| '_ \ / _` |_  / _ \| __|
| (_) | | | | (_| |/ / (_) | |_
 \___/|_| |_|\__,_/___\___/ \__|

sql — queries and usage.

QUERIES

select * will show all fields
select distinct means it will only show unique records

SELECT

Select all records from table	: `select * from TABLE;`
Select unique records	: `select distinct * from TABLE;`
Select all records, showing only specified fields	: `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;`

Multiple tables

Select all record matching multiple conditions :
select * from TABLE where FIELD1 = VALUE1 and FIELD2 = VALUE2;
Select all matching records from multiple tables :
select * from TABLE1, TABLE2 where TABLE1.FIELD1 = TABLE2.FIELD2;

alias

Set table alias to simplify use:
select * from TABLE1 ALIAS1;
Use the alias when selecting:
select T1.FIELD1, T1.FIELD2 from TABLE1 T1;

GROUPING

Select record count by group:
select FIELD, count(FIELD) from TABLE group by FIELD;
Select record count by group where count is greater than 1 :
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;

LIMITS

Limit displayed records	: `select * from TABLE limit 10;`
Select 10 records starting from the 5th record	: `select * from TABLE limit 10 offset 5;`

JOINS

Joins data from multiple tables using [INNER] JOIN. JOIN implies INNER JOIN.

select
T1.FIELD1, T1.FIELD2,
T2.FIELD1, ALIAs2.FIELD2
from TABLE1 T1
join TABLE2 T2 on T2.FIELD = T1.FIELD;

outer join

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.

left

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;

full

Shows all records from both tables.

AUTHORS

ohazot | about | ohazot.com <admin@ohazot.com>