# SQL

## 資料庫創建

CREATE DATABASE mydb;

## 表格

CREATE TABLE myTable (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

- id :欄位名稱
- INT :資料型態
- PRIMARY KEY:主鍵
- name :欄位名稱
- VARCHAR(100), : 最多存100個字符的不固定長度字串
- age :欄位名稱

## 插入資料

INSERT INTO myTable (id, name, age) VALUES (1, 'John Doe', 30);

## 查詢資料
### 全部

SELECT * FROM myTable;

### 特定列

SELECT name, age FROM myTable;

## 更新資料

UPDATE myTable SET age = 31 WHERE id = 1;

## 刪除資料

UPDATE myTable SET age = 31 WHERE id = 1;

## 聚合函數和分組(EXCEL是你?)

SELECT department, COUNT(*) AS num_employees, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

COUNT(), SUM(), AVG(), MAX(), 和MIN()
## 連接(n個拼湊)

SELECT orders.id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

INNER JOIN, LEFT JOIN, RIGHT JOIN, 和FULL OUTER JOIN。

## 子查詢(套娃)

SELECT name, (SELECT AVG(salary) FROM employees) AS avg_salary
FROM departments;

## LeetCode
### 175. Combine Two Tables

SELECT
    P.firstName,
    P.lastName,
    A.city,
    A.state
FROM 
    Person P
LEFT JOIN 
    Address A ON P.personId = A.personId;

從P選firstName,lastName; 從 A選city,state
接著在P的左邊拼一個A在personId
當然，反著做也行

SELECT
    P.firstName,
    P.lastName,
    A.city,
    A.state
FROM 
    Address A
RIGHT JOIN 
    Person P ON A.personId = P.personId;