建表陳述句

后續所有內容建立在這些SQL陳述句上，資料根據需要自行補充

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  product_name VARCHAR(50),
  price DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25);
INSERT INTO users (id, name, age) VALUES (2, 'Bob', 30);
INSERT INTO users (id, name, age) VALUES (3, 'Charlie', 35);

INSERT INTO orders (id, user_id, product_name, price) VALUES (1, 1, 'Product A', 10.99);
INSERT INTO orders (id, user_id, product_name, price) VALUES (2, 1, 'Product B', 15.99);
INSERT INTO orders (id, user_id, product_name, price) VALUES (3, 2, 'Product C', 20.99);
INSERT INTO orders (id, user_id, product_name, price) VALUES (4, 2, 'Product A', 10.99);
INSERT INTO orders (id, user_id, product_name, price) VALUES (5, 3, 'Product B', 15.99);

學習要點

知識點學習

查詢單個列的資料

SELECT column_name FROM table_name;

查詢多個列的資料

SELECT column1, column2 FROM table_name;

帶條件查詢資料

SELECT column1, column2 FROM table_name WHERE condition;

插入資料

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

更新資料

UPDATE table_name SET column1=value1, column2=value2, ... WHERE condition;

洗掉資料

DELETE FROM table_name WHERE condition;

根據某列升序排序：

SELECT * FROM table_name ORDER BY column_name ASC;

根據某列降序排序：

SELECT * FROM table_name ORDER BY column_name DESC;

計算某列的總和：

SELECT SUM(column_name) FROM table_name;

計算某列的平均值：

SELECT AVG(column_name) FROM table_name;

按某列分組：

SELECT column_name, COUNT(column_name) FROM table_name GROUP BY column_name;

聯結

內連接是一種用于聯接兩個或多個表的方法，它只回傳滿足連接條件的行，也就是兩個表中具有相同值的行，內連接使用關鍵字"INNER JOIN"來連接兩個或多個表，它可以包含一個或多個連接條件，

例如，假設有兩個表A和B，表A中有列a，表B中有列b，通過內連接在表A和表B之間進行連接，只會回傳那些同時滿足"a = b"條件的行，內連接可以幫助我們從兩個或多個相關表中獲取相關資料，

SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2 ON table1.id = table2.id;

左外連接兩個表：

左外連接是一種用于聯接兩個或多個表的方法，在MySQL中使用"LEFT JOIN"關鍵字來實作，左外連接回傳兩個表中滿足連接條件的所有行，以及左表中不滿足連接條件的行，

假設有兩個表A和B，通過左外連接在表A和表B之間進行連接，查詢將回傳滿足連接條件的行以及左表A中不滿足連接條件的行，右表B中不滿足連接條件的行的值將被設定為NULL，

這個查詢將回傳表A中所有行的a列的值，以及在表B中具有相同值的b列的值，如果在表B中沒有具有相同值的行，b列的值將為NULL，

左外連接適用于在兩個表中尋找關聯資料，即使在右表中沒有匹配的行時也可以回傳左表的資料，這對于獲取主表所有資料以及與之關聯的次要表的資料很有用，

SELECT table1.column1, table2.column2 FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

右外連接兩個表：

右外連接是一種用于聯接兩個或多個表的方法，在MySQL中使用"RIGHT JOIN"關鍵字來實作，右外連接回傳右表中滿足連接條件的所有行，以及右表中不滿足連接條件的行，

假設有兩個表A和B，通過右外連接在表A和表B之間進行連接，查詢將回傳滿足連接條件的行以及右表B中不滿足連接條件的行，左表A中不滿足連接條件的行的值將被設定為NULL，

以下是一個右外連接的示例查詢：

SELECT table1.column1, table2.column2 FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

這個查詢將回傳表B中所有行的b列的值，以及在表A中具有相同值的a列的值，如果在表A中沒有具有相同值的行，a列的值將為NULL，

右外連接適用于在兩個表中尋找關聯資料，即使在左表中沒有匹配的行時也可以回傳右表的資料，它可以用于獲取右表所有資料以及與之關聯的主表的資料，在實際應用中，左外連接更常見，而右外連接往往用左外連接完成同樣的任務，

子查詢

使用子查詢：

SELECT column_name1 FROM table_name WHERE column_name2 IN (SELECT column_name3 FROM table_name2 WHERE condition);

案例列舉

問題1: 統計每個用戶的訂單總數

需要統計每個用戶的訂單總數，以便了解用戶的購買情況，

SELECT users.id AS user_id, users.name AS user_name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id;

以上SQL陳述句中，首先通過左外連接將用戶表和訂單表關聯起來，然后使用GROUP BY子句按用戶進行分組，使用COUNT聚合函式統計每個用戶的訂單數量，

問題2: 查詢每個用戶的最高訂單金額

需要查詢每個用戶的最高訂單金額，以了解用戶的購買能力，

SELECT users.id AS user_id, users.name AS user_name, MAX(orders.price) AS max_order_amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id;

以上SQL陳述句中，通過左外連接將用戶表和訂單表關聯起來，然后使用GROUP BY子句按用戶進行分組，使用MAX聚合函式找到每個用戶的最高訂單金額，

問題3: 查詢訂單數量最多的用戶

需要找出訂單數量最多的用戶，以了解誰是最活躍的用戶，

SELECT users.id AS user_id, users.name AS user_name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id
ORDER BY order_count DESC
LIMIT 1;

以上SQL陳述句中，通過左外連接將用戶表和訂單表關聯起來，然后使用GROUP BY子句按用戶進行分組，并使用COUNT函式統計每個用戶的訂單數量，最后使用ORDER BY子句將結果按訂單數量降序排序，并使用LIMIT限制只回傳第一條結果，即訂單數量最多的用戶，

問題4: 查詢最近一個月內的訂單數量

需要查詢最近一個月內的訂單數量，以了解近期的訂單情況，

SELECT COUNT(id) AS order_count
FROM orders
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);

以上SQL陳述句中，使用COUNT函式統計最近一個月內的訂單數量，通過將當前日期減去一個月的時間間隔來計算出一個月前的日期，并使用WHERE子句篩選出指定日期范圍內的訂單，

問題5: 查詢購買最多的產品

需要查詢購買數量最多的產品，以了解最受歡迎的產品，

SELECT product_name, COUNT(id) AS sales_count
FROM orders
GROUP BY product_name
ORDER BY sales_count DESC
LIMIT 1;

以上SQL陳述句中，使用COUNT函式統計每個產品的銷售數量，并使用GROUP BY子句按產品名稱進行分組，最后使用ORDER BY子句將結果按銷售數量降序排序，并使用LIMIT限制只回傳第一條結果，即銷售數量最多的產品，

問題6: 查詢每個用戶的平均訂單金額

需要查詢每個用戶的平均訂單金額，以了解用戶的平均消費水平，

SELECT users.id AS user_id, users.name AS user_name, AVG(orders.price) AS avg_order_amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id;

以上SQL陳述句中，通過左外連接將用戶表和訂單表關聯起來，然后使用GROUP BY子句按用戶進行分組，使用AVG聚合函式找到每個用戶的平均訂單金額，

問題7: 查詢未購買任何產品的用戶

需要查詢未購買任何產品的用戶，以了解哪些用戶還沒有進行購物，

SELECT users.id, users.name
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;

以上SQL陳述句中，通過左外連接將用戶表和訂單表關聯起來，然后使用WHERE子句篩選出未購買任何產品的用戶，即訂單ID為空的記錄，

問題8: 查詢訂單金額超過平均訂單金額的用戶

需要查詢訂單金額超過平均訂單金額的用戶，以了解哪些用戶的消費能力高于平均水平，

SELECT users.id AS user_id, users.name AS user_name, orders.price AS order_amount
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.price > (SELECT AVG(price) FROM orders);

以上SQL陳述句中，使用子查詢找到訂單表中的平均訂單金額，然后通過JOIN將用戶表和訂單表連接起來，使用WHERE子句篩選出訂單金額超過平均訂單金額的用戶，

問題9: 查詢每個用戶的首次購買日期

需要查詢每個用戶的首次購買日期，以了解用戶的注冊后多久開始購物，

SELECT users.id AS user_id, users.name AS user_name, MIN(orders.date) AS first_purchase_date
FROM users
JOIN orders ON users.id = orders.user_id
GROUP BY users.id;

以上SQL陳述句中，通過JOIN將用戶表和訂單表連接起來，然后使用GROUP BY子句按用戶進行分組，使用MIN函式找到每個用戶的最早購買日期，

問題10: 查詢購買過某個產品的用戶

需要查詢購買過某個產品的用戶，以了解對于某個指定的產品，都有哪些用戶購買過，

SELECT users.id AS user_id, users.name AS user_name
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.product_name = '指定產品名稱';

以上SQL陳述句中，通過JOIN將用戶表和訂單表連接起來，然后使用WHERE子句篩選出購買過指定產品的用戶，即產品名稱與指定名稱匹配的記錄，

標籤：MySQL

上一篇：Spark使用Python開發和RDD

下一篇：返回列表