当使用SQL时,常用的函数包括文本函数、日期和时间函数、数学函数、条件函数以及聚合函数。以下是这些函数的一些示例说明:
1. 文本函数:
- CONCAT:将两个或多个字符串连接在一起。
```sql
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
```
- LENGTH:获取字符串的长度。
```sql
SELECT LENGTH(product_description) AS description_length FROM products;
```
2. 日期和时间函数:
- NOW:获取当前日期和时间。
```sql
SELECT NOW() AS current_datetime;
```
- DATE_FORMAT:格式化日期为指定的格式。
```sql
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date FROM orders;
```
3. 数学函数:
- ABS:返回数值的绝对值。
```sql
SELECT ABS(-15) AS absolute_value;
```
- ROUND:四舍五入数字。
```sql
SELECT ROUND(3.678, 1) AS rounded_number;
```
4. 条件函数:
- IF:根据条件返回不同的值。
```sql
SELECT IF(grade >= 60, 'Pass', 'Fail') AS pass_status FROM student_scores;
```
- CASE:执行多条件分支操作。
```sql
SELECT
CASE
WHEN age < 18 THEN 'Minor'
WHEN age >= 18 AND age < 65 THEN 'Adult'
ELSE 'Senior'
END AS age_group
FROM persons;
```
5. 聚合函数:
- COUNT:计算行数。
```sql
SELECT COUNT(*) AS total_records FROM customers;
```
- AVG:计算平均值。
```sql
SELECT AVG(salary) AS average_salary FROM employees;
```