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;