Seele

Data Insertion, Deletion, Modification#

1. Insertion
   Copy

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

2. Deletion
   Copy

    DELETE FROM table_name WHERE column_name = value
   

3. Modification
   Copy

    UPDATE table_name SET column_name = New_value WHERE column_name = Old_value
   

4. Query
   Copy

    SELECT column_name FROM table_name WHERE column_name = value
   

Simple Query, Join Query#

1. Simple Query

   Copy

   SELECT column_name(s) FROM table_name
   

2. Join Query

   mysql Union

   Copy

   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

3. Nested Subquery

   Copy

   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#

1. Sorting

   Copy

   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 the value after like can replace any character
   
   

2. 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:

   1. Difference between MAX and GREATEST functions
   2. Standard deviation is the square root of variance.