Lazy loaded image
技术分享
Basic SQL Keywords & Functions
Words 400Read Time≈ 2 min
Nov 5, 2021
Apr 6, 2025
type
status
date
slug
summary
tags
category
icon
password

1. Data Selection & Filtering

Keyword / Function
Meaning
Example
SELECT
Choose columns to display
SELECT name FROM students;
FROM
Specify the table to query
SELECT * FROM orders;
WHERE
Filter rows by condition
WHERE age > 18
DISTINCT
Return only unique values
SELECT DISTINCT city FROM users;
LIMIT
Limit number of returned rows
LIMIT 10
TOP (SQL Server)
Return the top N rows
SELECT TOP 5 * FROM orders;
OFFSET
Skip N rows (for pagination)
OFFSET 10 ROWS

2. Sorting & Grouping

Keyword
Meaning
Example
ORDER BY
Sort the result
ORDER BY score DESC
ASC / DESC
Ascending / Descending order
ORDER BY age ASC
GROUP BY
Group rows by one or more columns
GROUP BY category
HAVING
Filter grouped results
HAVING AVG(score) > 80

3. Aggregation Functions

Function
Meaning
Example
COUNT(*)
Count total rows
SELECT COUNT(*) FROM orders;
SUM()
Total of values
SUM(price)
AVG()
Average value
AVG(age)
MAX()
Maximum value
MAX(score)
MIN()
Minimum value
MIN(score)

4. Calculations & Aliases

Symbol / Keyword
Meaning
Example
+ - * /
Arithmetic operations
price * quantity
AS
Rename a column
SUM(price) AS total_amount

5. Conditions & Logic

Keyword / Function
Meaning
Example
IN
Match a list of values
WHERE country IN ('USA', 'UK')
BETWEEN
Range condition
WHERE age BETWEEN 18 AND 25
LIKE
Pattern matching
WHERE name LIKE 'A%'
IS NULL / IS NOT NULL
Check for (non) empty values
WHERE phone IS NOT NULL
AND / OR / NOT
Logical operators
WHERE age > 20 AND gender = 'Male'
CASE WHEN
Conditional logic
SELECT name, CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END AS grade FROM students;
 
Here's a random sales database with columns representing order information:
OrderID
CustomerName
Product
Quantity
Price
Country
OrderDate
TotalAmount
1
David
Monitor
2
476.00
Canada
2023-10-04
952.01
2
Eva
Laptop
2
994.73
Australia
2023-09-06
1989.47
3
Charlie
Monitor
2
217.13
Canada
2023-06-13
434.26
4
Eva
Phone
3
67.17
Australia
2023-12-23
201.51
5
Eva
Laptop
3
519.20
USA
2023-07-11
1557.60
1. Data Selection & Filtering

SELECT

Explanation: Selects Product and TotalAmount from the sales where Country is 'USA'.

DISTINCT

Explanation: Returns only the unique products in the sales database.

LIMIT

Explanation: Returns the first 5 rows from the sales table.

OFFSET

Explanation: Skips the first 10 rows and returns the next 5 rows.

2. Sorting & Grouping

ORDER BY

Explanation: Sorts the sales by TotalAmount in descending order.

GROUP BY

Explanation: Groups the sales by Product and calculates the total amount for each product.

HAVING

Explanation: Filters the products where the total amount is greater than 1000.

3. Aggregation Functions

COUNT(*)

Explanation: Counts the total number of orders from the USA.

SUM()

Explanation: Sums up the total amount from all sales.

AVG()

Explanation: Calculates the average of TotalAmount in the sales table.

MAX()

Explanation: Returns the maximum TotalAmount in the sales.

MIN()

Explanation: Returns the minimum TotalAmount in the sales.

4. Calculations & Aliases

Arithmetic Operation

Explanation: Calculates the total revenue by multiplying Quantity and Price for each order.

AS

Explanation: Renames the sum of TotalAmount as TotalSales.

5. Conditions & Logic

IN

Explanation: Selects orders from the USA and Canada.

BETWEEN

Explanation: Selects orders made between January 1, 2023, and June 30, 2023.

LIKE

Explanation: Selects orders from customers whose name starts with 'A'.

IS NULL / IS NOT NULL

Explanation: Selects all orders where the OrderDate is not null.

AND / OR

Explanation: Selects orders where the quantity is greater than 2 and the country is the USA.

CASE WHEN

Explanation: Classifies customers into spending categories based on their TotalAmount.
 
上一篇
Pandas
下一篇
EMPTY-ARTICLE
Catalog