init

1 files changed, 433 insertions, 0 deletions

diff --git a/SI/Resource/Data Science/SQL/MySQL/MySQL.md b/SI/Resource/Data Science/SQL/MySQL/MySQL.md
new file mode 100644
index 0000000..cc23029
--- /dev/null
+++ b/SI/Resource/Data Science/SQL/MySQL/MySQL.md
@@ -0,0 +1,433 @@
+---
+id: 2023-12-19
+aliases:
+  - December 19
+  - "2023"
+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
+```
+
+## Basic
+### Table
+- Create tables
+
+```sql
+CREATE TABLE table_name (
+-- attributes variables (KEY types) (options)
+	id INT PRIMARY KEY AUTO_INCREMENT,
+	name VARCHAR(46) NOT NULL,
+	major VARCHAR(10) DEFAULT value,
+	column_name VARCHAR(10) UNIQUE,
+);
+```
+
+- Drop tables
+
+```sql
+DROP TABLE table_name;
+```
+
+- Describe tables
+
+```sql
+DESCRIBE table_name;
+
+DESC table_name;
+```
+
+- RENAME table
+
+```sql
+ALTER TABLE table_name
+RENAME TO new_table_name;
+```
+
+- UPDATE table
+
+```sql
+UPDATE table_name
+SET 1_column_name = new_value, (2_column_name = new_value);
+(WHERE other_column_name = value;)
+```
+
+### Column
+- ALTER columns
+
+```sql
+ALTER TABLE table_name ADD column_name DECIMAL(3,2);
+
+ALTER TABLE table_name DROP COLUMN column_name;
+```
+
+- ADD column
+
+```sql
+ALTER TABLE table_name
+ADD COLUMN new_column_name DATA_TYPE;
+```
+
+- CHANGE column_name
+
+```sql
+ALTER TABLE table_name
+CHANGE COLUMN column_name (DATA_TYPE);
+```
+
+- DROP column_name
+
+```sql
+ALTER TABLE table_name
+DROP COLUMN column_name;
+```
+
+### Data
+- INSERT INTO data
+
+```sql
+INSERT INTO table_name VALUES(data);
+
+INSERT INTO table_name(column_name) VALUES(data);
+```
+
+- SELECT */data
+
+```sql
+SELECT *
+FROM table_name;
+
+SELECT column_name (AS col_name)
+FROM table_name;
+```
+
+- DELETE FROM data
+
+```sql
+DELETE FROM table_name;
+WHERE a_column_name = value;
+```
+
+### Condition
+- ORDER BY ... ASCending & DESCending
+
+```sql
+SELECT *
+FROM table_name;
+ORDER BY column_name ASC / DESC;
+```
+
+- LIMIT int
+
+```sql
+SELECT *
+FROM table_name;
+LIMIT int;
+```
+
+- 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 ;
+```