updated MySQL.md

1 files changed, 0 insertions, 471 deletions

diff --git a/SI/Resource/Data Science/SQL/MySQL/MySQL.md b/SI/Resource/Data Science/SQL/MySQL/MySQL.md
deleted file mode 100644
index 59a56a1..0000000
--- a/SI/Resource/Data Science/SQL/MySQL/MySQL.md
+++ /dev/null
@@ -1,471 +0,0 @@
----
-id: "2023-12-19"
-aliases:
-  - December 19
-  - "2023"
-  - MySQL
-tags:
-  - link-note
-  - MySQL
-  - Data-Science
-  - SQL
----
-
-# MySQL
-
-## Variables
-
-```sql
-INT
-DECIMAL(3,2) -- gpa 3.70 -> 3 digits and 2 decimal
-VARCHAR(10)
-BLOB -- Binary Large Object, stores large data, e.g., images
-DATE -- YYYY-MM-DD
-TIMESTAMP -- YYYY-MM-DD HH:MM:SS
-```
-
-## Table
-
-- View tables
-
-```sql
-.tables -- sqlite
-TABLES -- mysql
-```
-
-- Describe tables
-
-```sql
-DESCRIBE table_name;
-
-DESC table_name;
-```
-
-## DDL (Data Definition Language) - CREATE, ALTER, DROP, TRUNCATE
-
-- Create tables
-
-```sql
-CREATE TABLE table_name (
--- attributes variables (KEY types) (options)
-	id INT PRIMARY KEY AUTO_INCREMENT, -- (Optional) default
-	name VARCHAR(46) NOT NULL,
-	major VARCHAR(10) DEFAULT value,
-	column_name VARCHAR(10) UNIQUE,
-);
-```
-
-- Drop tables
-
-```sql
-DROP TABLE table_name;
-```
-
-- ALTER columns
-
-```sql
-ALTER TABLE table_name ADD column_name DECIMAL(3,2);
-
-ALTER TABLE table_name DROP COLUMN column_name;
-```
-
-- ALTER ... RENAME table
-
-```sql
-ALTER TABLE table_name
-RENAME TO new_table_name;
-```
-
-- ALTER ... ADD column
-
-```sql
-ALTER TABLE table_name
-ADD COLUMN new_column_name DATA_TYPE;
-```
-
-- ALTER CHANGE column_name
-
-```sql
-ALTER TABLE table_name
-CHANGE COLUMN column_name (DATA_TYPE);
-```
-
-- ALTER DROP column_name
-
-```sql
-ALTER TABLE table_name
-DROP COLUMN column_name;
-```
-
-## DML (Data Manipulation Language): UPDATE commands (UPDATE, INSERT, DELETE) & Query command (SELECT)
-
-### UPDATE commands
-
-- UPDATE table
-
-```sql
-UPDATE table_name
-SET 1_column_name = new_value, (2_column_name = new_value);
-(WHERE other_column_name = value;)
-```
-
-- INSERT INTO data
-
-```sql
-INSERT INTO table_name VALUES(data);
-
-INSERT INTO table_name(column_name) VALUES(data);
-```
-
-- DELETE FROM data
-
-```sql
-DELETE FROM table_name;
-WHERE a_column_name = value;
-```
-
-### Query command
-
-- SELECT data (Select Expression: \*, column names - alias: AS)
-
-```sql
-SELECT *
-FROM table_name;
-
-SELECT column_name (AS col_name)
-FROM table_name;
-```
-
-#### FROM clause
-
-##### ORDER BY
-
-- ORDER BY ... ASCending & DESCending
-
-```sql
-SELECT *
-FROM table_name;
-ORDER BY column_name ASC / DESC;
-```
-
-##### LIMIT
-
-- LIMIT int
-
-```sql
-SELECT *
-FROM table_name;
-LIMIT int;
-```
-
-##### WHERE clause: Condition / Predicates
-
-- condition (cmp: <, <=, >, >=, =, <>)
-
-```sql
-SELECT *
-FROM table_name;
-WHERE column_name cmp value;
-```
-
-- OR, AND
-
-```sql
-SELECT *
-FROM table_name;
-WHERE column_name
-			OR/AND
-			condition;
-```
-
-- (NOT) IN (condition_1, condition_2, ...)
-
-```sql
-SELECT *
-FROM table_name;
-WHERE column_name IN (column_name_1, column_name_2, ...);
-```
-
-- BETWEEN ... AND ...
-
-```sql
-SELECT *
-FROM table_name;
-WHERE column_name BETWEEN int AND int;
-```
-
-###### Filtering
-
-- LIKE (target: char%, %char, %str%, _char%, char_%\_%, char%char)
-
-```sql
-SELECT *
-FROM table_name;
-WHERE column_name LIKE target;
-```
-
-- CASE WHEN condition(cmp) ELSE condition(cmp) END
-
-```sql
-SELECT *
-			 CASE WHEN condition THEN result
-						WHEN condition THEN result
-						ELSE result
-						END
-FROM table_name;
-```
-
-#### Functions
-
-- Calculation (fn: SUM(), COUNT(), MIN(), MAX(), AVG())
-
-```sql
-SELECT fn(column_name) variable
-FROM table_name;
-```
-
-- Digit (fn: TRUNCATE(digit, n), ROUND(digit, n), CEIL(), FLOOR(), ABS(), SIGN())
-
-```sql
-SELECT fn()
-```
-
-- String (fn: LENGTH(), TRIM(), UPPER(), LOWER(), LEFT(str, n), RIGHT(str, n), REPLACE(), LPAD(), RPAD(), SUBSTRING(), CONCAT(), CONCAT_WS())
-
-```sql
-SELECT column_name, LENGHT(column_name)
-FROM table_name;
-
-SELECT *
-FROM table_name;
-WHERE column_name TRIM(str);
-
-SELECT UPPER/LOWER(column_name)
-FROM table_name;
-
-SELECT LEFT/RIGHT(column_name, int)
-FROM table_name;
-
-SELECT REPLACE(column_name, target, result)
-FROM table_name;
-
-SELECT column_name, LPAD/RPAD(column_name, target, result)
-FROM table_name;
-
-SELECT SUBSTRING(column_name, target, number_of_result)
-FROM table_name;
-
-SELECT CONCAT(str_1, str_2, ...)
-FROM table_name;
-```
-
-##### Date
-
-- Date & Time (fn: CURDATE(), CURTIME(), NOW(), YEAR(), MONTH(), DAY(), LAST_DAY(), WEEKDAY(), DAYNAME())
-
-```sql
-SELECT fn()
-FROM table_name;
-```
-
-- fn: ADDDATE(target, int), SUBDATE(target, int), DATEDIFF(target_1, target_2), TIMEDIFF(target_1, target_2)
-
-```sql
-SELECT column_name
-			 fn(target, int)
-FROM table_name;
-```
-
-###### Date Format
-
-- DATE_FORMAT (fm: %Y, %y, %M, %b, %W, %a, %i, %T, %m, %c, %d, %e, %I, %H, %r, %S)
-
-```sql
-SELECT column_name, DATE_FORMAT(column_name, fm_1, fm_2, ...)
-FROM table_name;
-```
-
-#### GROUP BY
-
-- GROUP BY column_name_1, column_name_2, ..., Calculation_1, Calculation_2, ...
-
-```sql
-SELECT column_name, fn()
-FROM table_name;
-GROUP BY column_name;
-```
-
-- HAVING condition (cmp) after GROUP BY
-
-```sql
-SELECT column_name
-FROM table_name;
-GROUP BY column_name;
-HAVING condition;
-```
-
-#### Division & Analysis
-
-- RANK()/DENSE_RANK() OVER (ORDER BY column_name)
-
-```sql
-SELECT *
-			 RANK()/DENSE_RANK() OVER(ORDER BY column_name (ASC/DESC))
-FROM table_name;
-```
-
-##### Division
-
-- PARTITION BY
-
-```sql
-SELECT *
-			 RANK()/DENSE_RANK()(PARTITION BY column_name ORDER BY column_name (ASC/DESC))))))))))
-FROM table_name
-```
-
-##### Before/After
-
-- LAG(column_name, int): before /LEAD(column_name, int): after
-
-```sql
-SELECT *
-			 LAG(column_name, int)/LEAD(column_name, int) OVER(PARTITION BY ORDER BY column_name)
-FROM table_nam e
-ORDER BY column_name
-```
-
-##### Border
-
-- ROWS (FRAME: CURRENT ROW, int PRECEDING, int FOLLOWING, UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING) AND (FRAME)
-
-```sql
-SELECT column_name_1, column_name_2, column_name_3
-			 AVG(column_name_3) OVER (PARTITION BY column_name_1 ORDER BY column_name_2
-															ROWS BETWEEN int PRECEDING AND CURRENT ROW) new_column_name
-			 AVG(column_name_3) OVER (PARTITION BY column_name_1 ORDER BY column_name_2
-															ROWS BETWEEN int FOLLOWING AND CURRENT ROW) new_column_name
-FROM table_name;
-ORDER BY column_name_1, column_name_2;
-
-SELECT column_name_1, column_name_2, column_name_3
-			 AVG(column_name_3) OVER (PARTITION BY column_name_1 ORDER BY column_name_2
-															ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) new_column_name
-			 AVG(column_name_3) OVER (PARTITION BY column_name_1 ORDER BY column_name_2
-															ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW) new_column_name
-FROM table_name;
-ORDER BY column_name_1, column_name_2;
-```
-
-##### Merge
-
-- (INNER) JOIN
-
-```sql
-SELECT */(alias_1.)column_name_1, (alias_2.)column_name_2
-FROM table_name_1 (as alias_1) JOIN table_name_2 (as alias_2);
-ON (alias_1/)table_name_1.column_name_1 = (alias_2/)table_name_2.column_name_2;
-```
-
-- LEFT/RIGHT JOIN
-
-```sql
-SELECT *
-FROM table_name_1 as tb1 LEFT/RIGHT JOIN table_name_2 as tb2;
-ON tb1.column_name_1 = tb2.column_name_2;
-ORDER BY column_name;
-```
-
-- CROSS JOIN
-
-```sql
-SELECT *
-FROM table_name_1 CROSS
-		JOIN table_name_2;
-		(JOIN table_name_...)
-	  (...)
-```
-
-#### Sub Query / Temporary Table
-
-##### Multi Columns
-
-- ()
-
-```sql
-SELECT *
-FROM
-(
-	SELECT *, RANK() OVER(PARTITION BY column_name_1 ORDER BY column_name_2 DESC)
-	FROM table_name;
-) alias;
-(WHERE condition;)
-```
-
-##### Uni Column & Uni Data
-
-- IN
-
-```sql
-SELECT *
-FROM table_name_1;
-		(JOIN column_name_1 column_name_2
-		 ON column_name_1 = column_name_2;)
-WHERE column_name_2 IN
-		(SELECT column_name_2
-		 FROM table_name_2;
-		 WHERE condition;);
-
-SELECT *
-FROM table_name_1;
-WHERE condition =(/IN)
-		(SELECT column_name_1
-		 FROM table_name_2;)
-```
-
-##### VIEW / WITH
-
-- Create
-
-```sql
-CREATE VIEW temp_column_name as alias_1
-SELECT *
-FROM table_name_1;
-```
-
-- Drop
-
-```sql
-DROP VIEW alias_1;
-```
-
-- WITH
-
-```sql
-WITH temp_table_name as(
-		SELECT *,
-				RANK() OVER(PARITION BY column_name_1 ORDER BY column_name_2 DESC) new_column_name;
-		FROM table_name
-)
-SELECT *
-FROM table_name
-(WHERE condition)
-```
-
-#### Custom Function
-
-- Nominal Return Value
-
-```sql
-DELIMITER $$
-CREATE FUNCTION fn_name(
-			num1 INT,
-			num2 INT,
-) RETURN INT
-BEGIN
-			DECLARE sum_ INT;
-			SET sum_ = num1 + num2;
-			RETURN sum_;
-end $$
-DELIMITER ;
-```