SQL / Progate

 

 

- query

 

SELECT columnName, …

FROM tableName;

// ; at the very end

 

SELECT *

// selects all columns

 

SELECT DISTINCT(column_name)

// no duplicate rows

 

- WHERE

 

WHERE category = "category_a"

 

WHERE category <= "date" | number

>= | > | <

 

- LIKE

 

WHERE category LIKE some_string;

WHERE category LIKE "%some_string%";

// wild card, %

 

WHERE category LIKE "some_string%";

// some_string, some_string+other_string 

 

WHERE category LIKE "%some_string";

// some_string, other_string+some_string

 

 

- NOT

 

WHERE NOT condition

 

 

- IS NULL

 

WHERE category IS NULL;

WHERE category IS NOT NULL;

 

 

- AND

 

WHERE condition1

AND condtion2;

 

WHERE condition1

OR condtion2;

 

 

- ORDER BY

ORDER BY coloumn_name order_method

 

ASC // ascending

DESC // descending

 

 

- LIMIT

LIMIT number-of-rows;

// must come at the very end

 

 

- omitting duplicates

 

SELECT DISTINCT( column_name )

 

 

 

- aggregate

 

SUM(column_name)

AVG(column_name)

COUNT(column_name)

COUNT(*) // including null

MAX(column_name)

MIN(column_name)

 

 

- grouping

 

GROUP BY column_name, …

 

WHERE condition

GROUP BY column_name, …

 

GROUP BY column_name

HAVING condition

 

 

- subqueries

 

SELECT CCC

FROM BBB

WHERE AAA ~~ (

SELECT AAA

FROM BBB

WHERE CCC = "~~"

);

 

SELECT ~~ AS "~"

// create temporary labels for column names and tables

 

 

- multiple tables

 

SELECT

FROM table_a

JOIN table_b

ON condition

// table_a.column_name = table_b.column_name

 

table_name.colum_name

// syntax which working with multiple tables.

 

LEFT JOIN table_b

// retrun all rows from the left (1st) table