SQL 備忘單：基本命令快速指南

作為程序員或數據分析師，您經常會發現自己要處理大量數據。SQL（結構化查詢語言）是您可以使用的最強大的工具之一。

此 SQL 速查表為您提供了最常見的 SQL 對象和命令，包括數據類型、DML 和 DDL 語句、聚合函數、字符串和日期函數以及子查詢。

它旨在成為您在與關係數據庫交互時隨時可以使用的快速方便的參考。

如果您和我們一樣，想要下載並打印備忘單或將備忘單存儲在您的計算機上，您可以在下方下載。

SQL備忘單下載

目錄

• SQL 數據類型
  • 1. 數值類型
  • 2.日期和時間類型
  • 3.字符串類型
• 選擇語句
• WHERE 子句
• ORDER BY條款
• 在 SQL 中連接多個表
  • 1.內連接
  • 2.左連接
  • 3.正確加入
  • 4.完全連接
• SQL 聚合函數
  • 1.計數功能
  • 2.求和功能
  • 3.AVG函數
  • 4.MAX功能
  • 5.最小函數
• 常用字符串函數
• 常用數值函數
• 常用日期函數
• 分組並擁有
  • 1. Group By子句
  • 2.有從句
  • 快速提示
• 子查詢
  • 1.IN運算符
  • 2. EXISTS 運算符
  • 3. 任何運營商
  • 4. ALL 運算符
• 數據操作 (DML)
  • 1. INSERT語句
  • 2.更新聲明
  • 3.刪除語句
• 使用 DDL 進行數據庫管理
  • 1.創建聲明
  • 2. ALTER語句
    • 添加新列
    • 修改列的數據類型
    • 刪除專欄
    • 添加唯一約束
    • 在表之間添加外鍵
  • 3. DROP 語句
• 交易
  • 1. 開始
  • 2.提交
  • 3.回滾
• 最後的想法

SQL 數據類型

特定數據庫管理系統 (DBMS) 的數據類型可能不同（例如Microsoft SQL Server 與MySQL）。但是，在大多數係統上都可以找到幾個。您可以將這些分為三類：

1. 數字

2. 日期和時間

3. 細繩

1. 數值類型

這些是最常見的數字類型：

• INTEGER : 沒有小數點的整數。

• SMALLINT：較小範圍的整數

• BIGINT：更大範圍的整數。

• DECIMAL(p, s) 或 NUMERIC(p, s)：例如，decimal(5,2) 適合 123.45。

• REAL：浮點數，精度至少為 6 位小數。

• FLOAT(n)：一個浮點數，精度至少為 n 位。

2.日期和時間類型

• DATE：日期值，通常採用“YYYY-MM-DD”格式。

• TIME：時間值，通常採用“HH:MM:SS”格式。

• DATETIME 或 TIMESTAMP：日期和時間值的組合。

3.字符串類型

• CHAR(n)：具有 n 個字符的固定長度字符串。

• VARCHAR(n) 或 CHARACTER VARYING(n)：可變長度字符串。

• TEXT：可變長度字符串，其最大長度由 DBMS 確定。

選擇語句

SELECT 語句用於從一個或多個表中檢索數據。您可以指定要檢索的列以及從哪個表中檢索。基本的 SELECT 語句如下所示：

SELECT column1, column2
FROM table;

要從“city”表的“name”和“country_id”列中檢索所有記錄，您的 SQL 查詢如下所示：

SELECT name, country_id
FROM city;

WHERE 子句

WHERE 子句允許您根據特定條件過濾 SELECT 語句的結果。

SELECT column1, column2
FROM table
WHERE condition;

要從 'population' 大於 1,000,000 的 'city' 表中檢索記錄，您的查詢如下所示：

SELECT name, population
FROM city
WHERE population > 1000000;

ORDER BY條款

ORDER BY 子句允許您按一列或多列對 SELECT 語句的結果進行排序。您可以按升序 (ASC) 或降序 (DESC) 對結果進行排序：

SELECT column1, column2
FROM table
ORDER BY column1 ASC, column2 DESC;

例如，要從按“人口”降序排列的“城市”表中檢索記錄，您的查詢如下所示：

SELECT name, population
FROM city
ORDER BY population DESC;

在 SQL 中連接多個表

SQL中常用的連接有四種：

1. 內部聯接

2. 左連接

3. 正確加入

4. 全連接

1.內連接

INNER JOIN 檢索在兩個表中具有匹配值的記錄。

讓我們以一個藝術家和專輯的數據庫為例，你想找到所有的藝術家和專輯組合。這是內部連接：

SELECT *
FROM artists AS a
INNER JOIN albums AS b
ON a.artist_id = b.artist_id;

使用 INNER JOIN，結果中只會返回指定字段中具有匹配值的行。

2.左連接

LEFT JOIN 也稱為 LEFT OUTER JOIN。它返回左表中的所有記錄和右表中匹配的記錄。如果右表中沒有匹配項，則結果將包含 NULL 值。

例如，要獲取所有藝術家及其各自專輯（如果有的話）的列表，您可以使用 LEFT JOIN：

SELECT *
FROM artists AS a
LEFT JOIN albums AS b
ON a.artist_id = b.artist_id;

This query will return all artists, even if they don’t have any albums associated with them in the albums table.

3. RIGHT JOIN

A RIGHT JOIN is also known as a RIGHT OUTER JOIN. It returns all records from the right table and the matched records from the left table. If there is no match in the left table, the result will contain NULL values.

For example, to get information about all albums and their associated artists (if they exist), you would use a RIGHT JOIN:

SELECT *
FROM artists AS a
RIGHT JOIN albums AS b
ON a.artist_id = b.artist_id;

This query will return all albums, even if they don’t have associated artists in the artists table.

4. FULL JOIN

A FULL JOIN is also known as a FULL OUTER JOIN. It combines the results of both LEFT and RIGHT joins. In other words, it returns all rows from the left and right tables and fills in the missing values with NULLs when there is no match.

Here’s an example using the artists and albums tables:

SELECT *
FROM artists AS a
FULL JOIN albums AS b
ON a.artist_id = b.artist_id;

This query returns all rows from both tables, filling in NULLs where there is no match in either table.

SQL Aggregate Functions

Aggregate functions are used to compute a single result from a set of input values. They’re called “aggregate” because they take multiple inputs and return a single output. The most common are:

1. COUNT

2. SUM

3. AVG

4. MAX

5. MIN

1. COUNT Function

The COUNT function allows you to count the number of rows in a query result. You can use this aggregate function to determine the total number of records in a table or the number of records that match specific criteria.

Here’s an example:

SELECT COUNT(*) FROM employees;

This query will return the total number of employees in the ’employees’ table. Keep in mind that adding a WHERE clause can refine your results:

SELECT COUNT(*) FROM employees WHERE department = 'HR';

2. SUM Function

The SUM function calculates the total sum of a numeric column. It’s useful when you need to calculate the total value of a particular numeric field. For example, this query returns the total sum of all employee salaries:

SELECT SUM(salary) FROM employees;

3. AVG Function

The AVG function computes the average value of a numeric column. This function is helpful when you want to find the average of a particular numeric field. For instance, this query returns the average salary of all employees:

SELECT AVG(salary) FROM employees;

4. MAX Function

The MAX function finds the maximum value of a column. This is often used to find the highest value in a numeric field or the most recent date in a datetime field. For example, this query returns the highest salary:

SELECT MAX(salary) FROM employees;

5. MIN Function

Lastly, the MIN function helps you find the minimum value of a column. For example, this query returns the lowest salary:

SELECT MIN(salary) FROM employees;

Remember, you can use WHERE clauses in these queries and JOIN with multiple tables.

Common String Functions

Here are the most common string functions that are found in most SQL dialects (the exact syntax can vary):

1. LEN or LENGTH(string): Returns the length of a string.

2. UPPER(string): Converts a string to upper case.

3. LOWER(string): Converts a string to lower case.

4. SUBSTR or SUBSTRING(string, start, length): Extracts a portion from a string.

5. TRIM(string): Removes leading and trailing spaces from a string.

6. LTRIM(string): Removes leading spaces from a string.

7. RTRIM(string): Removes trailing spaces from a string.

Common Numeric Functions

Here are the most common numeric functions that are found in most SQL dialects (the exact syntax can vary):

1. ABS(number): Returns the absolute value of a number.

2. ROUND(number, decimal_places): Rounds a number to a certain number of decimal places.

3. FLOOR(number): Rounds down the number to the nearest integer.

4. CEIL or CEILING(number): Rounds up the number to the nearest integer.

5. RAND(): Returns a random float value from 0 to 1.

6. MOD(n, m): Returns the remainder of n divided by m.

7. POWER(base, exponent): Raises a number to the power of another number.

8. LOG(number): Returns the natural logarithm of a number.

Common Date Functions

Here are the most common date functions that are found in most SQL dialects (the exact syntax can vary):

1. NOW(): Returns the current date and time.

2. DATE(datetime): Extracts the date part of a date or datetime expression.

3. TIME(datetime): Extracts the time part of a date or datetime expression.

4. YEAR(date): Returns the year part.

5. MONTH(date): Returns the month part.

6. DAY(date): Returns the day of the month part.

7. HOUR(time): Returns the hour part from a time.

8. MINUTE(time): Returns the minute part from a time.

9. SECOND(time): Returns the second part from a time.

GROUP BY And HAVING

When working with SQL queries, you may want to further summarize and filter your aggregated data. The GROUP BY and HAVING clauses provide this functionality.

1. Group By Clause

The GROUP BY clause allows you to group rows that share the same values in specified columns. It is commonly used with aggregate functions. This is the syntax:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2;

For example, if you want to calculate the total sales amount for each product category, this is the query:

SELECT product_category, SUM(sales_amount)
FROM sales_data
GROUP BY product_category;

TIP: Combining GROUP BY and COUNT is a good way of finding duplicate values.

2. Having Clause

If you want to filter the aggregated results further, you can use the HAVING clause. The syntax is:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition;

If you want to find product categories with total sales of more than $1,000,000, you would write:

SELECT product_category, SUM(sales_amount)
FROM sales_data
GROUP BY product_category
HAVING SUM(sales_amount) > 1000000;

Quick Tips

• Always use the GROUP BY clause before the HAVING clause.

• The SELECT statement can only contain specified column names, aggregate functions, constants, and expressions.

• When using the HAVING clause, filter conditions should be applied to the aggregate functions rather than directly to the grouped columns.

By understanding and properly applying the GROUP BY and HAVING clauses, you can better organize and analyze your data using SQL.

Subqueries

A subquery is also known as an inner or nested query. This is a query embedded within another SQL statement (such as a SELECT statement) or even inside another subquery.

Subqueries allow you to retrieve data based on the output of another query. The most common operators used with subqueries are:

1. IN

2. EXISTS

3. ANY

4. ALL

1. IN Operator

The IN operator tests if a value is within a set of values generated by the inner query. The syntax for using the IN operator with a subquery is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM other_table);

This returns rows from the outer query where the specified column value matches any of the values provided by the subquery.

Suppose you have an employee table and a departments table. You want to find employees who work in departments based at the head office. Here is a sample query

SELECT first_name, last_name
FROM employee
WHERE department IN (SELECT department FROM departments
                     WHERE location = "HQ");

For a more in-depth look, check out our article on the SQL WHERE IN syntax.

2. EXISTS Operator

The EXISTS operator checks if there is at least one row resulting from the subquery. You can use the EXISTS operator to filter rows based on the existence of related data in another table. This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT column_name FROM other_table WHERE condition);

When the subquery returns at least one row, the EXISTS operator returns true, and the relevant rows from the outer query are included in the result.

3. ANY Operator

The ANY operator is used to compare a value to any value in a set of values provided by a subquery. It’s commonly used with comparison operators like =, <, >, <=, or >=.

This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY (SELECT column_name FROM other_table WHERE condition);

This will return rows from the outer query where the specified column value meets the condition against any value from the subquery.

4. ALL Operator

The ALL operator compares a value to all values within a set of values provided by a subquery. The conditions must be true for every value in the subquery’s result. This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL (SELECT column_name FROM other_table WHERE condition);

This returns rows from the outer query only if the specified column value satisfies the condition against all values in the subquery’s output.

Data Manipulation (DML)

數據操作語言 (DML) 是 SQL 中用於管理和更新數據的子語言。最常見的陳述是：

1. 插入

2. 更新

3. 刪除

1. INSERT語句

INSERT 語句允許您將行插入表中。這是基本語法：

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

例如，如果要將新行插入到包含“id”、“name”和“email”列的“users”表中，您可以使用以下查詢：

INSERT INTO users (id, name, email)
VALUES (1, 'John Doe', '[email protected]');

2.更新聲明

UPDATE 語句允許您修改表中現有的行數據。這是語法：

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

例如，如果您想要更新“用戶”表中 ID 為“1”的用戶的電子郵件地址，您的查詢將如下所示：

UPDATE users
SET email = '[email protected]'
WHERE id = 1;

提示：請記住包含一個 WHERE 子句以避免錯誤地更新表中的所有行。

3.刪除語句

DELETE 語句允許您從表中刪除行。語法如下：

DELETE FROM table_name
WHERE condition;

例如，如果您要從“用戶”表中刪除 ID 為“1”的用戶，您的查詢將如下所示：

DELETE FROM users
WHERE id = 1;

提示：始終包含一個 WHERE 子句來指定要刪除的行並避免刪除表中的所有行。

使用 DDL 進行數據庫管理

數據定義語言 (DDL) 是用於創建和更改表以及數據庫本身的 SQL 子語言。最常見的 DDL 語句是：

1. 創造

2. 改變

3. 降低

1.創建聲明

CREATE 語句允許您創建新的數據庫對象，例如新表、視圖或索引。創建新表時，您需要定義列、它們的數據類型和任何約束。

以下是創建訂單表的示例：

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    product VARCHAR(255) NOT NULL,
    customer_id INT NOT NULL
);

提示：選擇適當的數據類型和約束以確保表中的數據完整性。

如需更詳細的了解，請查看我們關於基本 SQL 表操作的文章。

2. ALTER語句

ALTER 語句幫助您修改現有的數據庫對象。常見用途包括：

• 添加、修改或刪除列。

• 在現有表中添加或刪除約束。

• 添加主鍵和外鍵。

添加新列

ALTER TABLE users ADD COLUMN age INTEGER;

修改列的數據類型

ALTER TABLE users ALTER COLUMN age TYPE FLOAT;

刪除專欄

ALTER TABLE users DROP COLUMN age;

添加唯一約束

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE(email);

在表之間添加外鍵

ALTER TABLE users ADD FOREIGN KEY (country_id) REFERENCES Country(country_id);

3. DROP 語句

DROP 語句允許您刪除數據庫對象，如表、視圖或索引。請謹慎使用它，因為它會永久刪除指定的對象及其所有數據。

這是一個例子：

DROP TABLE users;

提示：確保在執行 DROP 語句之前有適當的備份。

如果您想了解有關數據建模的更多信息，請觀看此視頻：

交易

事務在維護數據庫完整性方面起著至關重要的作用，尤其是當多個相關操作並發執行時。處理事務有三個基本操作：

1. 開始

2. 犯罪

3. 回滾

1. 開始

BEGIN 語句表示事務的開始。執行此命令後，您就為您的 SQL 語句集建立了一個起點。

BEGIN;

2.提交

要完成更改並將它們保存在數據庫中，請使用 COMMIT 語句。這確保事務中的所有操作都成功且永久地執行。

COMMIT;

以下是使用賬戶間資金轉移經典示例的完整交易示例：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

3.回滾

在處理事務時，了解在發生錯誤時如何撤消更改也很重要。ROLLBACK 語句撤銷自事務開始以來所做的所有更改：

ROLLBACK;

下面是一個使用 ROLLBACK 進行錯誤處理的事務示例：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
IF @@ERROR <> 0
    ROLLBACK;
ELSE
    COMMIT;

最後的想法

正如您在此 SQL 速查表中所見，SQL 提供了大量命令和函數，使您可以在關係數據庫中創建、操作和查詢數據。

從 SELECT、INSERT、UPDATE 和 DELETE 等基本命令，到 JOIN 和子查詢等更複雜的結構，再到我們介紹的聚合函數，SQL 提供了處理各種數據任務的靈活性。

繼續查看此備忘單以加快您的SQL 學習之旅！