SQL Data Manipulation Language

Data Insertion, Deletion, Modification#

Insertion

INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

Deletion

 DELETE FROM table_name WHERE column_name = value

Modification

 UPDATE table_name SET column_name = New_value WHERE column_name = Old_value

Query

 SELECT column_name FROM table_name WHERE column_name = value

Simple Query, Join Query#

Simple Query
```
SELECT column_name(s) FROM table_name
```
Join Query

mysql Union
```
SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2 
// UNION DISTINCT combines two or more queries into one result set and removes duplicate data (keeping one copy),
// equivalent to UNION without DISTINCT
// UNION ALL keeps all data (then why use UNION)
```
Tips:
1. The "*" symbol can replace fields
2. Where contains conditions after it
3. Conditions can be connected using AND or OR
4. LIMIT can be used to limit the length of the result set
5. OFFSET can be used to specify the offset of the result set (where to start)
6. COUNT(*) can retrieve the number of rows in the result set

Nested Subquery

SELECT column_name(s) FROM table_name WHERE column_name(s) 
IN 
(
    SELECT column_name(s) FROM table_name WHERE column_name(s) = value
 )

Sorting, Calculating, Grouping Query Results#

Sorting

SELECT column_name(s) FROM table_name ORDER BY column_name(s) [ASC|DESC]
// ASC ascending (default), DESC descending
WHERE column_name(s) 
 LIKE    value
//% in value after like can replace any character

Calculating

Function	Description	Example
AVG	Calculate the average value	AVG(column_name(s))
COUNT	Calculate the total number of query records	COUNT(column_name(s))
MAX	Calculate the maximum value	MAX(column_name(s))
MIN	Calculate the minimum value	MIN(column_name(s))
SUM	Calculate the sum	SUM(column_name(s))
MOD	Calculate the remainder	MOD(column_name(s),value)
STD	Calculate the standard deviation	STD(column_name(s))
RAND	Calculate random number	RAND()
ROUND	Calculate rounding	ROUND(column_name(s),value)
GREATEST	Calculate the maximum value	GREATEST(column_name(s),column_name(s))
LEAST	Calculate the minimum value	LEAST(column_name(s),column_name(s))

Tips:

Difference between Max and Greatest functions
Standard deviation is the square root of variance