path: root/SI/Resource/Data Science/SQL/MySQL/MySQL.md

---
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 ;
```