From 06a383c4e78d6b4acf777ad6e951f2dec8a376bf Mon Sep 17 00:00:00 2001
From: TheSiahxyz <164138827+TheSiahxyz@users.noreply.github.com>
Date: Thu, 26 Dec 2024 01:20:09 +0900
Subject: updated MySQL.md

---
 SI/Resource/Data Science/SQL/MySQL.md       | 471 ++++++++++++++++++++++++++++
 SI/Resource/Data Science/SQL/MySQL/MySQL.md | 471 ----------------------------
 2 files changed, 471 insertions(+), 471 deletions(-)
 create mode 100644 SI/Resource/Data Science/SQL/MySQL.md
 delete mode 100644 SI/Resource/Data Science/SQL/MySQL/MySQL.md

(limited to 'SI/Resource/Data Science/SQL')

diff --git a/SI/Resource/Data Science/SQL/MySQL.md b/SI/Resource/Data Science/SQL/MySQL.md
new file mode 100644
index 0000000..59a56a1
--- /dev/null
+++ b/SI/Resource/Data Science/SQL/MySQL.md	
@@ -0,0 +1,471 @@
+---
+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 ;
+```
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 ;
-```
-- 
cgit v1.2.3