general.txt

==========[MYSQL NOTES]==========================================

- SQL => STRUCTURED QUERY LANGUAGE

- SQL IS USED TO WRITE QUERIES TO THE DATABASE

- MYSQL IS A DBMS THAT RUNS ON A SERVER


=====[COMMON TABLE EXPRESSIONS]================================

CTE => COMMON TABLE EXPRESSION

- A CTE IS A TEMPORARILY NAMED RESULT SET 
THAT EXISTS WITHIN THE SCOPE OF A SINGLE QUERY
STATEMENT AND IT CANE BE REFEREED TO LATER ON 
IN THE QUERY STATEMENT, POSSIBLY MULTIPLE TIMES


=====[STORED ROUTINES]==========================================

- CREATE ROUTINE => CREATE A STORED ROUTINE
- ALTER ROUTINE => ALTER OR DROP A STORED ROUTINE
- EXECUTE => EXECUTE A STORED ROUTINE

- THESE ARE ESSENTIALLY FUNCTIONS THAT 
ARE STORED IN THE DATABASE

- IT IMPROVES SYSTEM PERFORMANCE AND REDUCES 
THE AMOUNT OF DATA THAT HAS TO BE TRANSFERRED 
BETWEEN THE CLIENT AND THE SERVER

- LOAD DATA INFILE CANNOT BE USED IN A 
STORED ROUTINE


RESOURCE: https://rsc.anu.edu.au/~rsccu/manuals/mySQL/refman-5.0-en.html-chapter/stored-procedures.html#:~:text=Stored%20routines%20cannot%20use%20LOAD,other%20statements%20such%20as%20EXPLAIN%20.

===[EXAMPLE OF CREATING A STORED ROUTINE]========================

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
    
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
    
func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statement