技術文章 » 【技術積累】Mysql中的SQL語言【一】

【技術積累】Mysql中的SQL語言【一】

2023-06-29 12:01:46

建表語句

後續所有內容建立在這些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子句篩選出購買過指定產品的使用者，即產品名稱與指定名稱匹配的記錄。