| select * | Displays all rows in a table. |
>SELECT * FROM TABLENAME |
| select * (multiple tables) | Displays all the rows of the cartesian product of the two tables |
>SELECT * FROM TABLENAME1,TABLENAME2; |
| select columns | Select particular columns from table(s) |
>SELECT COLUMN1,COLUMN2 FROM TABLENAME; |
| select with condition | Displays rows based on a particular condition |
> SELECT * FROM TABLENAME WHERE CONDITION |
| select with multiple conditions(AND) | Displays rows only when both the conditions are satisfied. |
> SELECT * FROM TABLENAME WHERE CONDITION1 AND CONDITION2. |
| select with multiple conditions(OR) | Displays rows only when either of the conditions are satisfied. |
> SELECT * FROM TABLENAME WHERE CONDITION1 OR CONDITION2. |
| select with condition(NOT) | Displays rows based on negation of a particular condition. |
>SELECT * FROM TABLENAME WHERE NOT CONDITION. |
| select with group by | Displays rows that have same values into summary rows |
> SELECT .. FROM .. WHERE… GROUP BY COLUMN3; |
| select with having | Used instead of where for aggregate functions. |
>SELECT COUNT(COLUMN1) FROM TABLENAME ORDER BY COLUMN2 HAVING COUNT(COLUMN1)>3; |
| select distinct | Display all unique rows discarding duplicate ones. |
>SELECT DISTINCT (COLUMN1) FROM TABLENAME; |
| order by | Used to sort results in ascending order or descending order |
> SELECT … FROM TABLENAME ORDER BY COLUMN1 ASC|DESC; |
| column alias | Changes the output of the name of the column. |
> SELECT COLUMN1 AS NEWNAME FROM TABLENAME; |
| like | Used to search for a specific pattern. |
> SELECT COLUMN1 FROM TABLENAME WHERE COLUMN1 LIKE ‘%PATTERN%’; |
| insert record | Adds a new row to an existing table. |
> INSERT INTO TABLENAME (COLUMN1,COLUMN2…) VALUES (VALUE1,VALUE2…); |
| insert record(multiple) | Adds multiple records into an existing table. |
> INSERT INTO TABLENAME (COLUMN1,COLUMN2…) VALUES (VALUE1A,VALUE2A…),(VALUE1B,VALUE2B,...); |
| delete | Deletes all records in a table. |
> DELETE FROM TABLENAME; |
| delete with where | Deletes specific records |
>DELETE FROM TABLENAME WHERE CONDITION; |
| between | Selects values in a given range |
>SELECT * FROM TABLENAME WHERE AGE BETWEEN 25 AND 30. |
| in | Used instead of multiple OR operators. |
> SELECT * FROM TABLENAME WHERE COLUMN2 IN (V1,V2…); |
| exists | Tests for existence of a certain record. Returns a boolean value. |
> SELECT * FROM TABLE NAME WHERE EXIST (SUB QUERY); |
| update table | Modifies data in existing tables. |
> UPDATE TABLENAME SET COLUMNNAME=VALUE WHERE CONDITION; |
| inner join | Selects records that have the same values in two same or distinct tables. |
> SELECT COLUMN(S) FROM TABLENAME1 INNER JOIN TABLENAME2 ON TABLENAME1.COLUMNAME=TABLENAME2.COLUMNAME; |
| left join | Selects all the records from the left table and matching records from the right table. |
>SELECT COLUMN(S) FROM TABLENAME1 LEFT JOIN TABLENAME2 ON TABLENAME1.COLUMNAME=TABLENAME2.COLUMNAME; |
| right join | Selects all the records from the right table and matching records from the left table. |
>SELECT COLUMN(S) FROM TABLENAME1 RIGHT JOIN TABLENAME2 ON TABLENAME1.COLUMNAME=TABLENAME2.COLUMNAME; |
| cross join | Selects rows from cartesian product of both the tables. |
>SELECT COLUMN(S) FROM TABLE1 CROSS JOIN TABLE2; |
| full outer join | Selects all records with a match on table1 or table2. |
>SELECT COLUMN(S) FROM TABLENAME1 FULL OUTER JOIN TABLENAME2 ON TABLENAME1.COLUMNAME=TABLENAME2.COLUMNAME WHERE CONDITION; |
| union | Combines the result of two select statements. |
>SELECT * FROM TABLENAME1
UNION SELECT * FROM TABLENAME2 |
| union all | Similar to Union but allows duplicate values |
>SELECT * FROM TABLENAME1
UNION ALL SELECT * FROM TABLENAME2 |
| concat() | Combines two or more columns together. |
>SELECT CONCAT(COLUMN1, " ", POSTALCODE, " ", COLUMN2) AS NEWCOL
FROM TABLENAME; |
